oracle学习（一）

1、Oracle登录

Oracle连接有三种客户端工具

• 使用sqlplus工具连接
• 使用sqldeveloper图形界面连接
• 使用第三方工具

Oracle登录有两种方式

• 用户名和密码
• 使用用户名和角色登录

>sqlplus / as sysdba

Oracle安装好时已经创建提供了许多用户

用户名：sys  角色：dba    这个用户是活的，即可以直接使用(其他用户需要解锁使用)用户名：scott用户名：HR  ...

解锁其他普通用户，并设置密码

SQL> alter user scott account unlock;SQL> alter user scott identified by tiger01;

普通用户登录

> sqlplus scott/tiger01

当出现密码忘记情况，需要使用管理员超级用户来修改
三次出错时拒绝登录

> sqlplus / as sysdba> alter user scott identified by abc123;

普通用户修改自己的代码

SQL> password更改 SCOTT 的口令旧口令:新口令:重新键入新口令:口令已更改

显示当前用户名

SQL> show user;USER 为 "SCOTT"

2、Oracle基本使用

查询scott用户下所有对象

select * from tab;

查询表结构

desc emp;

修改表的字段显示宽度
Oracle一共有三种类型，varchar2、 number、 date

-- 字符类型 a12表示12位column ename format a12;-- 数字类型9999代表四位column empno format 9999;column mgr format 9999;

/执行上一条执行过的sql语句
host cls;清屏

NVL(a,b)函数，当a为null时，使用b值代替，如果是非null就不用b代替直接返回a值

select NVL(null,10) from dual;

NVL2(a,b,c)，当a不为空时使用b，当a为空时使用c

使用别名，可以不使用双引号，但别名中不能有空格，

select empno AS 编号 from emp;select empno AS "编号" from emp;

获取时间，默认只显示日期

select sysdate from dual;

||管道符，拼接字段，常量字符串使用单引号，将两个字段查询结果拼接在一起

> select 'hello' || ' word' "结果" from dual;> select ename || '的薪水是' || sal " 结果" from emp; 结果-------------------SMITH的薪水是800ALLEN的薪水是1600WARD的薪水是1250JONES的薪水是2975MARTIN的薪水是1250BLAKE的薪水是2850CLARK的薪水是2450SCOTT的薪水是3000KING的薪水是5000TURNER的薪水是1500ADAMS的薪水是1100

保存所有的命令及结果

spool e:/oracle.sql;...spool off;

从外部读入sql脚本并执行

@ e:/oracle.sql;

单行注释--，多行注释/* */

--select * from dual;/*    123*/

SQL语句关键字大小写不敏感，字段名即列名大小写不敏感，必须使用；结束
单引号中的字符串大小写敏感

3、查询

-- 查询select * from emp where deptno = 20;-- 注意单引号中的字符串大小写敏感select * from emp where ename='SMITH';select * from emp where sal>1500;-- 不等于可以使用!=也可以使用<>select * from emp where sal!=1500;select * from emp where sal>=1300 and sal<=1600;-- between a and b 可以使用在数字型和日期型select * from emp where sal between 1300 and 1600;select * from emp where sal not between 1300 and 1600;select * from emp where hiredate between '20-2月-81' and '23-2月-82';-- 使用in 来替换orselect * from emp where (deptno==20) or (deptno==30);select * from emp where deptno in (20,30);select * from emp where deptno not in (20,30);-- 查询大写字母S开员工，%表示0个，1个或多个字符-- 精确查询使用=，不精确查询使用like，称为模糊查询,mysql与Oracle都一样select * from emp where ename like 'S%';-- 使用_表示一个任意字符select * from emp where ename like '__I__';-- 转义字符\，查询名字含有_的员工select * from emp where ename like 'ac\_' escape '\';-- 插入姓名含有'单引号的，注意Oracle中字符串使用单引号 insert into emp(empno,ename) values(100,'''''');-- null加减乘除一个数还为null，null不能参与精确查询-- 注意不能使用like或=select * from emp where comm is null;select * from emp where comm is not null;-- 按照升序出结果，不写asc默认升序select * from emp order by sal asc;-- 降序select * from emp order by sal desc;-- 佣金非空升序排列select * from emp where comm is not null order by comm asc;-- 当排序使用多个字段，当第一个一样时 第二个才起作用select * from emp order by comm,sal asc;

4.单行函数

单行函数：只有一个参数输入一个参数输出
多行函数：可以有多个函数输入，一个输出
单行函数

-- 转小写select lower('ABC') from dual;-- 转大写select upper('abc') from dual;-- 字符串拼接select concat('hello','你好') from dual;select concat('hello','你好','abc') from dual;错误select concat('hello',concat('你好','abc')) from dual;select 'hello' || '你好' || 'abc' from dual;-- 取子串select substr('helloab你好cdef',5,3) from dual;-- 从左到右第一次出现的位置instr函数 大小写敏感，第一个结果0，第二个结果5SQL> select instr('helloworld','O') from dual;SQL> select instr('helloworld','o') from dual;-- lpad/rpad：不足指定位数，补位指定字符，lpad在左边补，rpad在右边补select lpad('hello',10,'#') from dual;select rpad('hello',10,'#') from dual;-- trim去掉两边的指定字符 结果：abcooodefselect trim('o' from 'oooabcooodefooo') from dual;-- replace 替换select replace('hello','l','LL') from dual;-- round，保留指定位小数，四舍五入select round(3.1415,3) from dual;-- trunc，保留指定位小数，不四舍五入select trunc(3.1415,3) from dual;-- mod,求余select mod(10,3) from dual;

计算时间的单行函数

-- 昨天，今天，明天select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;-- 上个月的今天select add_months(sysdate,-1) from dual;-- 上个星期三是多少号select next_day(sysdate,'星期三') from dual;-- 上上一个星期三select next_day(next_day(sysdate,'星期三') ,'星期三') from dual;-- 本月最后一天select last_day(sysdate) from dual;

5.Oracle类型转换

Oracle三大类型
（1）varchar2是边长的，char固定长度，隐式转换：varchar2->date
（2）number—>varchar2/char
（3）date—–>varchar2/char

-- to_char('日期','格式')将日期转换为字符串select to_char(sysdate,'yyyy mm dd day') from dual;select to_char(sysdate,'yyyy "年" mm "月" dd "日" day') from dual;select to_char(sysdate,'yyyy mm dd day hh24:mi:ss') from dual;select to_char(sysdate,'yyyy mm dd day hh12:mi:ss AM') from dual;-- to_char(数字,"格式")select to_char(12345,'$999,999,999') from dual;-- to_date('字符串'，'格式')select * from emp where hiredate = to_date('1980年12月7日','yyyy"年"mm"月"dd"日"');-- to_number('123')将字符串转为数字型select to_number('123') from dual;-- 字符串隐式转换为数字select '123'+123 from dual;-- 数字隐式转换为字符串select '123'||123 from dual;