外连接

核心：通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中

左外连接：当连接条件不成立的时候，等号左边的表仍然被包含

右外连接：当连接条件不成立的时候，等号右边的表仍然被包含

注意：左右外连接的叫法和写法是相反的。如：若是右外连接，则在等号的左边加上(+)符合，左外连接则在等号的右边加上(+)符号。

select d.deptno,d.dname,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;

--#案例三：按部门统计员工人数(已知员工入职年份）

select count(*) from emp;

select HIREDATE from emp;

select count(*) Total,

       sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",

       sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",

       sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",

       sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"

from emp;--函数方式

-------------------------

select 

(select count(*) from emp) Total,

(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",

(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",

(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",

(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"

from dual;--子查询方式

----------

--创建表

desc pn_ci;

desc PM_STU;

create table pn_ci

(CI_ID varchar2(20) NOT NULL,

 STU_IDS  varchar2(100));--选课表

-------------------

create table pm_stu

(STU_ID varchar2(20) NOT NULL,

 STU_NAME  varchar2(20));--学生表

------------------

INSERT into pn_ci values('1','1,2,3,4');

INSERT into pn_ci values('2','14');

select * from pn_ci;

---------------------

INSERT into pm_stu values('1','张三');

INSERT into pm_stu values('2','李四');

INSERT into pm_stu values('3','王五');

INSERT into pm_stu values('4','赵六');

select * from pm_stu;

--#案例三：按部门统计员工人数(已知员工入职年份）

select count(*) from emp;

select HIREDATE from emp;

select count(*) Total,

       sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",

       sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",

       sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",

       sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"

from emp;--函数方式

-------------------------

select 

(select count(*) from emp) Total,

(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",

(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",

(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",

(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"

from dual;--子查询方式

----------

--创建表

desc pn_ci;

desc PM_STU;

create table pn_ci

(CI_ID varchar2(20) NOT NULL,

 STU_IDS  varchar2(100));--选课表

-------------------

create table pm_stu

(STU_ID varchar2(20) NOT NULL,

 STU_NAME  varchar2(20));--学生表

------------------

INSERT into pn_ci values('1','1,2,3,4');

INSERT into pn_ci values('2','14');

select * from pn_ci;

---------------------

INSERT into pm_stu values('1','张三');

INSERT into pm_stu values('2','李四');

INSERT into pm_stu values('3','王五');

INSERT into pm_stu values('4','赵六');

select * from pm_stu;

设置报表样式

第五章 ：解决实际问题

案例一：分页查询显示员工信息：显示员工号，姓名，月薪

--每页显示四条记录

--显示第二页的员工

--按照月薪降序排列

select empno,ename,sal from emp order by sal desc;

--rownum 行号（永远从1开始），只能使用<,<=,不能使用>,>=

select r,empno,ename,sal

from (select rownum r,empno,ename,sal 

   from (select rownum,empno,ename,sal from emp order by sal desc) e1

 where rownum<=8) e2

 where r>=5;--r是e2表的第一列，变成不是伪列的值

案例2：找到员工表中薪水大于本部门平均薪水的员工

select * from emp ;

--部门平均薪水

select deptno,avg(sal) from emp  group by deptno;

----------------------------------------------------------

explain plan for

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal

from emp e

where sal>(select avg(sal) from emp where deptno=e.deptno);

select * from table (dbms_xplan.display);

----------------------------------------------------------

explain plan for--解释计划

select * 

from emp e ,(select deptno,avg(sal) avgsal from emp  group by deptno) d

where e.deptno=d.deptno and e.sal>d.avgsal;--多表查询，嵌套子查询

--查看执行计划

select * from table (dbms_xplan.display);--占用CPU更少

简单的报表实现，统计部门工资，并作出总和

break on  deptno  skip 2   将相同的部门号不显示，并且跳过两行预留空白

group  by  语句增强  例子

group by 语句增强

使用列转行listagg函数

第四章：子查询

1.子查询案例

示例：查询工资比员工scott高的员工信息

select * from emp 

where sal>(select sal from emp where ename='SCOTT');

2.子查询注意的10个问题

• 子查询语法中的小括号

• 子查询的书写风格

• 可以使用子查询的位置：where，select，having，from

select empno,ename,sal,(select job from emp where empno=7839) 第四列

from emp ;

select deptno,avg(sal) from emp group by deptno

having avg(sal)>(select max(sal) from emp where deptno=30);--having与where区别，having可以跟group by，where不可以。

• 不可以使用子查询的位置：group by

--按照部门查询部门平均工资

select deptno,avg(sal) from emp group by deptno;--正确

select deptno,avg(sal) from 

emp group by (select deptno from emp);--错误

• 强调：from后面的子查询

--from后面的子查询，看做新的表

--示例1：查询员工信息：员工号，姓名，月薪

select empno,ename,sal from emp;

--示例1：查询员工信息：员工号，姓名，月薪,年薪

select empno,ename,sal,(select sal*12 from emp) from emp;

select empno,ename,sal 月薪,sal*12 年薪 from emp;

select * from (select empno,ename,sal 月薪,sal*12 年薪 from emp);

• 主查询和子查询可以不是同一张表

--主查询和子查询可以不是同一张表

--示例：查询部门名称是SALES的员工信息

select * from dept;

select * from emp 

  where deptno=

  (select deptno from DEPT where dname='SALES');

select e.* from emp e,dept d

  where e.deptno=d.deptno 

  and d.dname='SALES';

  --结果一致，理论上，下面的好与上面的，实际中，子查询好于多表查询，因为笛卡尔集很大

• 一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序

--一般不在子查询中进行排序

--Top-N分析中要排序

--示例：找到员工表中工资最高的前三名

--rownum 行号 伪列  (查询语句中必须写出它）

--行号需要注意的两个问题：行号永远按照默认顺序生成，行号只能使用<.<=;不能使用>,>=

select rownum,empno,ename,sal from

(select * from emp order by sal desc)

where rownum<=3;--正确排序

select  rownum,empno,ename,sal from emp  

where rownum<=3

order by sal desc;

--只是前三条记录，没有按照薪水排序

• 一般先执行子查询，再执行主查询，但相关子查询例外

--示例：找到员工表中薪水大于本部门平均薪水的员工

select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal 

from emp e 

where sal>(select avg(sal) from emp where deptno=e.deptno);

• 单行子查询只能使用单行操作符（查询语句返回一条记录）；

  多行子查询只能使用多行操作符（查询语句返回多条记录）

--子查询只返回一条记录叫单行子查询，返回两条及以上记录叫多行操作符

--单行子查询只能使用单行操作符，多行子查询只能使用多行操作符

--单行操作符：=,>,>=,<,<=,<>;多行操作符：in，any，all

--查询员工信息，要求：职位与7566员工一样，薪水大于7782员工的薪水

select * from emp 

where job=(select job from emp where empno=7566) --单行子查询，

and sal>(select sal from emp where empno=7782);

--单行子查询只能使用单行操作符，一个查询语句中可以有多个单行子查询

--单行子查询

--查询工资最低的员工信息

select * from emp 

where sal in (select min(sal) from emp); 

select * from emp 

where sal = (select min(sal) from emp); --单行子查询

--查询最低工资,大于10号部门最低工资,的部门号和部门的最低工资

select deptno,min(sal) from emp group by deptno;--所有部门的最低工资

(select min(sal) from emp where deptno=20) ;--10号部门最低工资

select * from (select deptno,min(sal) b  from emp group by deptno) 

where b>(select min(sal) from emp where deptno=20);--不使用having

select deptno,min(sal) from emp group by deptno

having min(sal)>(select min(sal) from emp where deptno=20) ;

--分组函数后面不能跟where，可以跟having代替where

--非法使用单行子查询

select empno,ename from emp

where sal = (select min(sal) from emp group by deptno);--子查询中是多行的集合，非法

--多行操作符in

--查询部门名称是SALES和ACCOUNTING的员工

select emp.* from emp,dept where emp.DEPTNO=dept.DEPTNO --多表查询

and dept.dname IN('SALES','ACCOUNTING');

select emp.* from emp,dept where emp.DEPTNO=dept.DEPTNO --多表查询

and (dept.dname  ='SALES' or dept.dname ='ACCOUNTING');--优先级先and，后or

select * from emp --子查询

where deptno in 

(select deptno from dept where dname ='SALES' or dname ='ACCOUNTING');

--多行操作符ANY

--查询工资比30号部门任意一个员工高的员工信息

select * from emp where sal>(select min(sal) from emp where deptno=30);

select * from emp where sal >any(select sal from emp where deptno=30);

--多行操作符ALL

select * from emp where sal >ALL(select sal from emp where deptno=30);

• 注意：子查询中是null值问题

--单行子查询中的null值问题，判断一个值等不等与null值不能用=或<>，因为永远是不等的，

--判断一个值等不等与null值，只能用IN 或is not

select * from emp 

  where job=

  (select job from emp where ename='TOM');--子查询不返回任何行

--多行子查询中的空值

--查询不是老板的员工

select * from emp where empno  in (select MGR from emp);

select * from emp where empno not in (select MGR from emp);--空值

--只要子查询中返回值包含null值，不要使用not in，因为not in 等同与<> ALL（不等于所有值）

select * from emp 

where empno not in 

  (select MGR from emp where mgr is not null);--加上不为空的限制

第三章：多表连接

 1. 多表查询

 多表查询：从多个表中获取数据

 查询员工信息与部门信息，员工表与部门表

 2. 笛卡尔集

 多表查询的基础

表1*表2的笛卡尔集：列数=两张表相加，行数两张表相乘

连接条件N-1个，N代表多少张表连接

 3. 等值连接

  等值连接:连接条件中是等号

  示例：查询员工信息，要求显示：员工号，姓名，月薪，部门名称

  试验：select empno,ename,sal,dname from emp 

             left join DEPT on emp.deptno=dept.deptno;

   标准：select e.empno,e.ename,e.sal,d.dname

                from emp e，dept d where e.deptno=d.deptno;

             （习惯上重命名表）

 4. 不等值连接

     不等值连接：连接条件不是等号

     示例：查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别

 select * from emp;

select * from salgrade;

select e.empno,e.ename,e.sal,s.grade 

from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;--（具体问题，具体分析）注意大于等于下线，和小于上线

select e.empno,e.ename,e.sal,s.grade 

from emp e,salgrade s where e.sal between s.losal and s.hisal;--（between and包含两边边界值）

 5. 外连接

select * from emp;

select * from dept;

select d.deptno,d.dname,count(e.empno)

from dept d left join emp e on d.deptno=e.deptno

group by d.deptno,d.dname;

select d.deptno,d.dname,count(e.empno)

from emp e left join dept d  on e.deptno=d.deptno

group by d.deptno,d.dname;

 7. 自连接

查询员工姓名和对应老板姓名

select * from emp ;

select e1.ename，e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;--普通表连接自动顾略空值（14*14=196条记录）--如果一亿条数据表，则是平方关系

select e1.ename，e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;

 8. 层次查询（解决自连接问题）

自连接存在的问题：不适合操作大表

解决办法：层次查询（单表查询）

原理：

条件：上层员工号是下层员工的老板号

          上一层的员工号=老板号

select level,empno,ename,sal,mgr from emp 

connect by prior empno=mgr

start with empno =7893;

select level,empno,ename,sal,mgr from emp

connect by prior empno=mgr

start with empno is null

order by 1;（根节点）

select d.deptno 部门号，d.dname部门名称，count(e.empno)人数

from emp e,dept d  where e.deptno=d.deptno  group by d.deptno,d.dname;

select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;