一、Oracle数据库的登陆

(1)一般用户：使用Oracle自带的sqlplus，cmd--sqlplus scott/密码；

(2)管理员用户：cmd--sqlplus sys/密码 as sysdba

注意：客户端登陆的方法略

二、sqlplus的一些使用技巧

（1）host cls 清屏---屏幕信息太多

（2）spool D:\笔记.txt--可以用来保存在sqlplus中的的屏幕信息(包含自己的操作)

（3）spool off ---结束控制台的录制

注意：每次文件不能重名，否则会覆盖

（4）show user---显示当前用户

补充：在oracle中表属于某个用户，用户属于数据库 ；

（5）elect * from tab---显示当前用户下的所有的表名称

注意：不是table而是tab

需求1：查出来后表的显示格式不太好看 ，想调整

[sql] view plain copy

-- 设置行宽 show linesize; --先显示下行宽 -- 设置行宽 set linesize 150 --设置行宽为150个字符 -- 设置列宽 col ename format a8 -- 说明：表示设置ename这个字段的宽度为8个字符,a表示一个字符 -- 设置列宽 col sal format 9999 -- 说明：表示设置工资这个字段的宽度为4位,因为工资是数字所以用9表示一位数字 需求2：修改语句中的错误单词

[sql] view plain copy

select ename,sal form emp; -- 这里的from 敲错了 --(1)执行后出现的错误 --第 1 行出现错误: --ORA-00923: 未找到要求的 FROM 关键字 --(2)修改错误 2 -- 输入出现错误的行号 c /form/from -- c就是change的意思(改变sql语句的错误部分) / --再次执行 需求3：计算员工当月的收入

[sql] view plain copy

-- null的问题, null参与运算结果都为null select ename,sal,comm,sal+comm as 当月总收入 from emp; 说明：收入=基本工资+奖金

注意：null参与运算结果都为null

分析：上面结果有误--如果奖金comm是null值得话，和工资相加就变为null了，我们想要如果奖金为null就认为它是0

解决办法：用 nvl(comm,0) 来解决null值问题

[sql] view plain copy

select ename,sal,comm,sal+nvl(comm,0) as 当月总收入 from emp; 特点：是null的话就默认为0，否则还是其本身

需求4：修改sql语句

[sql] view plain copy

-- （1）ed 命令 将上一条sql 语句 自动粘贴到文本文件中,供我们进行再次编辑修改,然后保存 -- （2）/执行此语句 需求5：去除重复记录(distinct)

[sql] view plain copy

select distinct deptno from emp; select distinct job,deptno from emp; -- 两个列组合起来看是没有重复得(好好体会) 说明：distinct作用于后面所有得列

---

三、Oracle中常用的知识

（1）伪表 dual

特点：像一张虚拟得表，它的存在只是为了sql语句语法完整。

[sql] view plain copy

select 3+2;--会报错 select 3+2 from dual;--成功执行 说明：select 3+2虽然跟表没关系,但是在Orcal中select语句后面必须要有from某张表，不写语法就报错,用伪表来代替一张表。（2）注意：日期和字符串用单引号括起来，列的别名用双引号(或者不写)括起来

（3）关于处理字符串的一些函数(单行)

[sql] view plain copy

/

0.拼接字符串 concat()

例如 select concat('ab','cd') from dual;

1.转小写 lower()

例如 select lower('ABC') from dual;

2.转大写 upper()

例如 select upper('abc') from dual;

3.首字母大写 initcap()

例如 select initcap('abc') from dual;

4.截取字符串 substr('要截取的字符串','2');

说明：参1:要截取的字符串, 参2 从第二个字符开始截取到末尾

例如 select substr('abdccccc',2) from dual;

注意：参数2是1开头的不是0

5.截取字符串指定一段范围 substr('要截取的字符串',2,3);

说明：参数1:要截取的字符串、参数2:开始的位置(默认是1)、参数3:截取的长度

例如 SELECT substr('abcdef',2,3) from dual;

6.获取字符的个数 length('abc')

例如:select length('abc') from dual;

7.获取字符的字节数 lengthb('ab');

例如: select length('a') from dual;

8.根据字符查找索引 instr('abc','a')

需求:查找a在abc中的索引

例如 select instr('abc','a') from dual;

注意:索引从1开始数

9.左填充 lpad() 在字符串左端补上指定个数的字符

例如 select lpad('abc',5,'h') from dual; 注意这个数是加上原来字符的个数

10.右填充 rpad() 在字符串有端补上指定个数的字符

例如 select rpad('abc',5,'h') from dual; 注意这个数是加上原来字符的个数

11.去掉字符串两端指定的字符 trim();

例如 select trim('a' from 'aabbaa') from dual; 注意语法格式

12.替换字符串 replace('abcd','c','aaaa')

需求：把abcd中的c 替换成 aaaa

例如 select replace('abcd','c','aaaa') from dual;

/ 说明：select 函数名(参数) from dual 来执行函数

（4）关于数字的处理

[sql] view plain copy

/

1.四舍五入 round(3.14,2);

参数2:表示从小数点"四舍五入"后保留的位数

例如 select round(2.145,2) from dual;

2.小数点后面保留几位数字 trunc(3.1415926,3)

参数：表示保留小数点位数

例如 select trunc(3.1415926,2) from dual;

/ 注意：二者的区别！！！

（5）获取系统当前日期 sysdate

[sql] view plain copy

select sysdate from dual; 说明：默认格式是 06-2月-18 特点：没有时分秒,如果想要时分秒,可以指定日期格式 （6）需求：按照指定格式输出时间(格式化日期)

[sql] view plain copy

--格式化日期(必须转成字符串的形式) to_char() select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual;-- 24小时制 补充：日期的指定格式的处理

（6）日期可以运算可以加上天数

[sql] view plain copy

select sysdate-1 from dual; --昨天时间(单位是天) select sysdate+1 from dual; --明天时间 注意：并不能改变系统的时间！！！

（7）计算一下员工入职时间

[sql] view plain copy

select ename,hiredate,(sysdate-hiredate)/365 as 工龄 from emp; 说明：可以根据需求保留指定的位数

注意：日期跟日期可以相减，但不能相加。

（8）杂乱(工作中常用)

[sql] view plain copy

/* 需求1：计算两个日期相差的月数 months_between() 例如 select ename,hiredate,months_between(sysdate,hiredate) from emp;

需求2：添加月份 add_months(sysdate,12);

例如 select add_months(sysdate,12) from dual;--一年13的月发工资吧！

需求3：计算下一个星期几是几号

例如 select next_day(sysdate,'星期一') from dual;--日期格式 select to_char(next_day(sysdate,'星期一'),'yyyy-mm-dd') from dual;--字符串格式

需求4：计算这个月的最后一天是几号 last_day(sysdate)

例如 select last_day(sysdate) from dual;

需求5：对日期的年、月进行四舍五入(不知道有啥用处？？？)

round(sysdate,'year'); round(sysdate,'month'); 例如 select round(sysdate,'month') from dual;

需求6：格式化日期字符串 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day')

例如: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"是"day') from dual; --自定义格式 注意： "是" 字要加双引号

需求7： 把一个日期字符串转成日期 to_date()

例如: select to_date('2018-05-12','yyyy-mm-dd') from dual; 注意：字符串的日期,跟日期格式要对应

需求8：格式化数字 to_char(sal,'L9,999.99')

说明：三位一隔(999-表示三位小数),保留两位小数('.'),L是货币代码(人民币) 例如 select to_char(sal,'L9,999.99') from emp;

需求9：把一个字符串数字格式化成数字 to_number()

例如: select to_number('1520.99','9999.99') from dual;--参数2：转换后的格式

需求10：通用函数nvl2(a,b,c)

特点：当a=null 时返回c,否则返回b 例如 select ename,sal,comm,sal+nvl2(comm,comm,0) from emp;--回忆nvl(字段,0)

需求11：nullif(a,b)

特点：当a=b的时候返回null,否则返回a 例如 select nullif('abc','abc') from dual; 用处：可以判断两个值是否相等

需求11：从左往右找到第一个不是null的值 coalesce(comm,sal)

例如: select comm,sal,coalesce(comm,sal) as "第一个不为空的值" from emp; 注意：字段别名要用双引号引起来 */ （9）需求--如给不同职位的人加不同的工资

方式1：用 case  when  then end  语句来完成条件选择

[sql] view plain copy

select ename,sal as 涨前, case job when 'PRESIDENT' then sal+1000 when 'MANAGER' then sal+800 ELSE sal+300 -- 注意这里的写法 end as 加后 from emp; 说明：SQL99的规范，条件语句语法，以case打头，以 end结尾

方式2：采用 decode()函数来实现条件选择

[sql] view plain copy

--decode(['参数1','参数2','参数3']...); --参1 判断的字段名，条件的值是什么，你想干什么，条件的值是什么，你想干什么，else select ename,job,sal as 涨前, decode('job','PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) as 涨后 from emp; （10）组合数

[sql] view plain copy

/组函数(相当于MySQL中的聚合函数)

max(sal) 求最大值

min(sal) 求最小值

count() 统计个数 --注意：指定字段与不指定字段的区别！！！ sum(sal) 求和 avg(sal) 求平均值*/ 注意：组函数一般会自动忽略null值；

（11） groupby 和 having的分组查询

需求1：查询每个部门的总工资

[sql] view plain copy

select deptno,sum(sal) as 总工资 from emp group by deptno; 思路：先分组，再查询

需求2：查询每个部门的平均工资在两千以上的部门

[sql] view plain copy

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; 思路：先分组，再查询，最后过滤

需求3：查询每个部门中不同岗位的总工资

[sql] view plain copy

select deptno,job,sum(sal) from emp group by deptno,job; 语法要求：select后面要查询的字段(除了组函数字段)，必须在 group by 后面也要有，不然语法报错

说明：分组条件可以有多个 ，这里是按部门和岗位分组的

需求4：查询 10 部门的平均工资

方式1：用where来筛选(查询时就指定条件)

[sql] view plain copy

select deptno,avg(sal) from emp where deptno=10 group by deptno; 方式2：先分组，再用having 筛选

[sql] view plain copy

select deptno,avg(sal) from emp group by deptno having deptno=10; 注意： where 和 having的区别是where 是在分组之前进行筛选, 而having 是对分组之后的结果集 进行再次筛选

需求5：按照部门统计各部门不同工种的工资情况按照下图的格式输出(group by 语句增强 )

思路：

[sql] view plain copy

--(1)求各部门每个工种的总工资 select deptno,job,sum(sal) from emp group by deptno,job; --(2)求每个所有部门的总工资 select deptno,sum(sal) from emp group by deptno; --(3)求总工资 select sum(sal) from emp; 增强：

[sql] view plain copy

select deptno,job,sum(sal) from emp group by rollup(deptno,job); -- 思路：先按(deptno,job)分组，然后deptno分组，最后null分组 -- 逐次递减 结果：

需求：那上面的样子不好看我们可以排版，用下面一条命令

[sql] view plain copy

break on deptno skip 2； -- break on deptno 表示相同的部门号只显示一次, skip 2 每个部门之间,相隔两个空行 / --斜杠表示执行上一条语句 （12）多表查询

（1）笛卡尔积(非条件)

需求1：查询员工的信息--员工编号、员工名称、工资、部门名称

[sql] view plain copy

select emp.empno,emp.ename,emp.sal,dept.deptno from emp,dept; 结果(未列出全表，部门展示)

特点：多张表没有条件的组合在一块，查出的数据(交叉组合)不准确也没有意义

（2）条件查询(需求同上--等值连接)

说明：员工信息在emp表，部门名称在dept表，所以需要两张表连接条件是：两张表的deptno 相等；

注意：连接条件个数=表的数量-1

（3）需求2：查询员工的工资级别(不等值连接)

分析：如果一个员工的工资大于等于下限， 小于等于上限，我们就可以知道工资的级别

[sql] view plain copy

--方式1 select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal>=salgrade.losal and emp.sal<=salgrade.hisal; --方式2(between and) select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal; 结果：

说明：用到两张表emp 表和 salgrade表

（4）需求3：查询部门号、部门名称以及这个部门有多少个人(内连接)

分析：要统计多少个人肯定要查emp表，就需要查询 emp 表和dept表

[sql] view plain copy

select dept.deptno,dept.dname,count(emp.empno) from dept,emp where dept.deptno=emp.deptno group by dept.deptno,dept.dname; 结果：

思考：注意观察上面的查询结果，发现结果值罗列出了10、20、30 号3个部门的信息，但是一个40 号部门没列出来？原因：因为40号部门没有人内连接的特点：就是不符合条件的就不会显示出来

需求：即使这个部门没有人，我也要把部门编号和部门名称显示出来，人数以0显示

做法：那么我们就需要用外连接

（5）外连接(左外连接、右外连接)

需求1：对于某些不成立的记录（40号部门），任然希望包含在最后的结果中

左连接：

[sql] view plain copy

--写法：where e.deptno=d.deptno(+);当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中 右连接：

[sql] view plain copy

--写法: where e.deptno(+)=d.deptno;--右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中 说明：'+'号在左边表示是右连接，反之亦然

结果：

说明：上面的只是一种简便形式

方式2：

[sql] view plain copy

-- 采用 left outer join 表名 on 两张表的连接条件 outer 可以省略不写 select dept.deptno,dept.dname,count(emp.empno) as 人数 from dept left join emp on dept.deptno=emp.deptno group by dept.deptno,dept.dname; 方式1和方式2的部分对比：

[sql] view plain copy

--from dept,emp where dept.deptno=emp.deptno(+) --from dept left join emp on dept.deptno=emp.deptno --说明：摘取部分(两种方式等价) （6）需求：要查询员工姓名对应的老板姓名(自连接查询)

特点：通过表的别名给一张表起两个别名，将他视为两张表来进行查询

分析：因为这些信息都在一张表 emp中， 比如员工号7369的SMITH它对应的老板编号是(MGR)7902，而7902 又是员工FORD(7902) 那FORD对应的老板编号又是 7566，所以说一个员工既是某几个员工的老板，他也有自己的老板，所以我要查询这个员工的所对应的老板就可以使用自连接查询。

假设：有两张表一张员工表、一张老板表，如果员工的老板号=老板的员工号就表示这个员工是另外一个员工的老板

[sql] view plain copy

select e.ename as 员工姓名,b.ename as 老板姓名 from emp e,emp b where e.mgr=b.empno; --注意这里给表起别名时尽量不要写as 结果：

注意：自连接不适合数据量大的表，因为自连接实际上是将一张表看成了两张表 ,那么两张表关联起来查询形成笛卡尔积那么查询的条数就很多

（7）层次查询(需求同上)

回头补充

说明：层次查询的是一张表，比用自连接查询要好

---