Day31 Oracle 2 多表查询和子查询

SQL99的多表连接查询

SQL1999规范中规定的连接查询语法

select 字段列表
from table1
[cross join table2] | //1：交叉连接
[natural join table2] | //2：自然连接
[join table2 using (字段名)] | //3：using子句
[join table2 on (table1.column_name
= table2.column_name)] | //4：on子句
[(left | right | full outer) join table2
on (table1.column_name = table2.column_name)]; //5：左/右/满外连接

交叉连接

Cross join产生了一个笛卡尔集，其效果等同于在两个表进行连接时未使用WHERE子句限定连接条件;
可以使用where条件从笛卡尔集中选出满足条件的记录。

用法举例
select dept.deptno,dname,ename,job
from dept cross join emp;

自然连接

Natural join基于两个表中的全部同名列建立连接
从两个表中选出同名列的值均对应相等的所有行
如果两个表中同名列的数据类型不同，则出错
不允许在参照列上使用表名或者别名作为前缀
自然连接的结果不保留重复的属性

举例：
select empno, ename, sal, deptno, dname
from emp natural join dept
where deptno = 10;

Using子句

如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列（参照列）名。

using子句引用的列在sql任何地方不能使用表名或者别名做前缀

举例：
select e.ename,e.ename,e.sal,deptno,d. dname
from emp e join dept d
using(deptno)
where deptno=20

On子句

自然连接的条件是基于表中所有同名列的等值连接
为了设置任意的连接条件或者指定连接的列，需要使用ON子句
连接条件与其它的查询条件分开书写
使用ON 子句使查询语句更容易理解

select ename,dname
from emp join dept on emp.deptno=dept.deptno
where emp.deptno=30;
select empno, ename, sal, emp.deptno, dname
from emp join dept
on (emp.deptno = dept.deptno and job=’SALESMAN);

外连接

左外联接
两个表在连接过程中除返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接称为左外联接。
右外联接
两个表在连接过程中除返回满足连接条件的行以外，还返回右表中不满足条件的行，这种连接称为右外联接。
满外联接
两个表在连接过程中除返回满足连接条件的行以外，还返回两个表中不满足条件的所有行，这种连接称为满外联接。

内连接:在SQL99规范中，内连接只返回满足连接条件的数据

外连接举例
左外连接
select deptno, dname,empno,ename,job
from dept left join emp
using(deptno);
右外连接
select deptno, dname,empno,ename,job
from dept right join emp
using(deptno);
满外连接
select deptno, dname,empno,ename,job
from dept full join emp
using(deptno);

三表连接使用on创建连接

创建城市表City，部门表Dept1，员工表EMP1
部门表城市id参考城市表，员工表部门编号参考部门表

显示所有员工的编号，姓名和部门名称,城市编号

select e.empno,e.ename,d.dname,c.name
from emp1 e
join dept1 d on e.deptno = d.deptno –连接条件
join city c on d.loc = c.id –连接条件
where empno > 7800

查询每个员工的部门名称和工资等级
select e.empno,e.ename,e.sal,e.deptno,d.dname,sg.*
from emp e
join dept d
on d.deptno = e.deptno
join salgrade sg
on e.sal between sg.losal and sg.hisal

自连接

使用举例：查询每个员工的工号、姓名、经理姓名
select * from emp e join emp m on(e.mgr = m.empno)

select e.empno,e.ename,e.mgr,m.empno,m.ename from emp e join emp m on(e.mgr = m.empno)
–没有上级的员工也显示
select e.empno,e.ename,e.mgr,m.empno,m.ename from emp e left join emp m on(e.mgr = m.empno)
–没有员工的上级（不就是底层员工吗）也显示，还是别显示了
select e.empno,e.ename,e.mgr,m.empno,m.ename from emp e right join emp m on(e.mgr = m.empno)

SQL92的多表连接查询

语法规则：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
特点
在 WHERE 子句中写入连接条件
当多个表中有重名列时，必须在列的名字前加上表名作为前缀
连接的类型：
等值连接 – Equijoin
非等值连接 – Non-equijoin
外连接 – Outer join
左外连接 右外连接
自连接 – Self join

笛卡尔集

select * from dept;//4条记录
select * from emp; ;//14条记录
select * from dept,emp; ;//4*14=56条记录

总结
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
检索出的列的数目将是第一个表中的列数加上第二个表中的列数
应该保证所有联结都有where子句，不然数据库返回比想要的数据多得多的数据

等值查询

select * from dept,emp where dept.deptno=emp.deptno;
select * from dept d,emp e where d.deptno=e.deptno;
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno;
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and d.deptno=10
select d.deptno,dname,loc,empno,ename,job from dept d,emp e where d.deptno=e.deptno and loc=’DALLAS’;

当被连接的多个表中存在同名字段时，须在该字段前加上”表名.”前缀
可使用AND 操作符增加查询条件；
使用表别名可以简化查询
使用表名（表别名）前缀可提高查询效率；

SQL92的语法规则的缺点：

语句过滤条件和表连接的条件都放到了where子句中 。
当条件过多时，联结条件多，过滤条件多时，就容易造成混淆
SQL99修正了整个缺点，把联结条件，过滤条件分开来，包括以下新的TABLE JOIN的句法结构：
交叉连接（Cross join）
自然连接（Natural join）
使用Using子句建立连接
使用On子句建立连接
外连接（ Outer join ）
左外连接
右外连接
全外连接

子查询

子查询
问题引入
如何查得所有比“CLARK”工资高的员工的信息
select * from emp
where sal>(select sal from emp where ename=’CLARK’);
思考：查询工资高于平均工资的雇员名字和工资。
思考：查询和SCOTT同一部门且比他工资低的雇员名字和工资。
语法格式：
select 字段列表 from table
where 表达式 operator (select 字段列表 from table);
特点
子查询在主查询前执行一次
主查询使用子查询的结果

使用子查询注意事项

在查询是基于未知值时应考虑使用子查询
子查询必须包含在括号内
建议将子查询放在比较运算符的右侧，以增强可读性。

除非进行Top-N 分析，否则不要在子查询中使用ORDER BY 子句。

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

单行子查询

单行子查询只返回一行记录
对单行子查询可使用单行记录比较运算符
< 、 > 、 = 、 >=、 <= 、 <>

举例：
select * from emp
where sal > (select sal from emp where empno = 7566);

思考：查询工资最高的雇员名字和工资。

查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
SELECT empno, ename, job FROM emp
WHERE job =(SELECT job FROM emp WHERE empno=7788)
AND hiredate < (SELECT hiredate FROM emp WHERE empno=7788);

查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的编号和名字
select empno,ename,sal,hiredate
from emp
where sal>(select sal from emp where ename=’SCOTT’) or hiredate<(select hiredate from emp where ename=’SCOTT’)

多行子查询

多行子查询返回多行记录
对多行子查询只能使用多行记录比较运算符
ALL 和子查询返回的所有值比较
ANY 和子查询返回的任意一个值比较
IN 等于列表中的任何一个

查询工资低于任何一个“CLERK”的工资的雇员信息。
查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
查询部门20中职务同部门10的雇员一样的雇员信息。

SELECT empno, ename, job,sal
FROM emp
WHERE sal < ANY (SELECT sal FROM emp WHERE job = ‘CLERK’) AND job <> ‘CLERK’;

SELECT empno, ename,sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE job= ‘SALESMAN’);

SELECT empno, ename, job FROM emp
WHERE job IN (SELECT job FROM emp WHERE deptno=10)
AND deptno =20;

不相关子查询

子查询不需要用到外查询的数据；子查询可以独立运行；自内而外

相关子查询

子查询需要用到外查询的数据；子查询不可以独立运行；自外而内

查询本部门最高工资的员工
–方法一，使用嵌套子查询（非关联子查询）
select * from emp a where (a.deptno,a.sal) in (select deptno,max(sal) from emp group by deptno)
–方法二，使用关联子查询/9-*********
select * from emp a where a.sal=(select max(sal) from emp where deptno=a.deptno)

查询在雇员中有哪些人是经理人
分析：有哪些人的empno号在mgr这个字段中出现过，应首先查询mgr中有哪些号 码， 然后再看有哪些人的雇员号码在此出现
select empno, ename from emp
where empno in (
select distinct mgr from emp
);
找出部门编号为20的所有员工中收入最高的职员
select * from emp
where sal >= all(
select sal from emp where deptno = 20)
and deptno = 20

查询每个部门平均薪水的等级
分析：首先将每个部门平均薪水求出来，然后把结果当成一张表，再用这张结果表和salgrade表做连接，以此求得薪水等级

先求出每个部门平均薪水的表t。
将t和salgrade进行关联查询就可以了。

select * from 
salgrade s, (select deptno,avg(sal) avg_sal 
        from emp group by deptno) t
where  t.avg_sal between s.losal and s.hisal;

小结

SQL99的多表连接查询

• 交叉连接
• 自然连接
• Using连接
• On连接
• 外连接
• 自连接

SQL92的多表连接查询
子查询

• 单行子查询和多行子查询
• 相关子查询和不相关子查询

连接查询-内连接

--SQL99多表连接查询
--查询员工信息及其部门信息
select * from emp  --只知道了部门的编号，不知道部门的其他信息：名称、地址

select * from emp
select * from dept where deptno = 20
select * from dept where deptno = 30  --目的达到了，但是是通过多条SQL语句实现的，效率低下

--解决方案：通过一条SQL语句查询多张表，得到一个结果  效率高  多表连接（join）查询

--交叉连接 cross join 结果没有实际意义，方便我们理解连接查询的原理 笛卡尔积
select * from dept  --4
delete from dept where deptno = 50 or deptno = 60;
select * from emp  -- 14
select * from emp  cross join dept  --56 =4*14
select * from dept cross join emp
--自然连接
--前提：必须有同名列
--操作过程：自动按照【所有的】同名列进行匹配，同名列只显示一次
--优点：简单
select * from emp natural join dept

select deptno, empno,ename,job,dname,loc from emp natural join dept
select deptno, empno,ename,job,dname,loc from emp natural join dept where job = 'MANAGER'
--建议给列名指定表名，直接定位，提高性能
select deptno,emp.empno,emp.ename,emp.job,dept.dname,dept.loc from emp natural join dept
where job = 'MANAGER'
--！！！同名列不能使用表名前缀
--缺点：直接使用表名，有些繁琐，表名可能太长。
--解决：可以使用别名
select deptno,e.empno,e.ename,e.job,d.dname,d.loc
from emp e
natural join dept d
where job ='MANAGER'

--using子句
--自然连接会自动按照所有的同名列进行匹配，如果只希望按照某个同名列进行匹配该怎么办？
--using中同名列还是只显示一次，不能使用前缀
select deptno,e.empno,e.ename,e.job,d.dname,d.loc
from emp e
join dept d
using(deptno)
where loc = 'CHICAGO'

--on子句
--万一两个表之间有关联字段，但是名称不同,此时就只能使用on子句
select * from dept
select * from emp

select * 
from emp e
join dept d
on(d.deptno = e.deptno)
--on子句中，关联列名称可能不同，必须指定前缀
select d.deptno,e.empno,e.ename,e.job,d.dname,d.loc
from emp e
join dept d
on(d.deptno = e.deptno)
where ename like '%A%'

--总结
--1.连接查询的好处：效率高  一条语句同时查询多个表
--2.连接查询多种形式（cross、natural、using、on），都离不开join
--3.实际开发中使用on最多，适应情况最广（不管是否有同名列），可读性高
--4.连接条件：cross、natural、using、on
--  筛选条件：where having
--SQL99优点：连接条件和筛选条件分开，更清晰

连接查询-外连接和三表连接

--外连接查询
select * from dept
select * from emp;
--有的部门是没有员工的：40
--有的员工的没有部门的：7788 scott
update emp set deptno = null where empno = 7788

--连接查询：查询员工及其部门信息
select *
from emp e
inner join dept d
on (e.deptno = d.deptno)

--只显示了匹配的数据，没有显示不匹配的数据（40部门 、scott员工）
--左外连接：在显示所有匹配数据的前提下，不匹配数据只显示没有部门的员工
select *
from emp e   
left outer join dept d
on (e.deptno = d.deptno)

--右外连接：在显示所有匹配数据的前提下，不匹配数据只显示没有员工的部门
select *
from emp e
right outer join dept d
on (e.deptno = d.deptno)


--全外连接：在显示所有匹配数据的前提下，不匹配数据两者都显示（没有部门的员工，没有员工的部门）
select *
from emp e
full outer join dept d
on (e.deptno = d.deptno)

--内连接和外连接
--内连接：inner join 只显示匹配的数据
--外连接：outer join 除了显示匹配的数据，还可以显示部分或者全部不匹配的数据
            --左外连接、右外连接、全外连接


--三张表的连接
--hr用户解除锁定
alter  user hr account unlock;
--修改密码
alter user hr identified by hr

select * from regions  --大区
select * from countries; --国家
select * from locations where country_id= 'CN' --所在地
select * from departments; --部门
select * from employees;--员工
select * from jobs;--岗位
select * from job_history where employee_id = 101  --岗位经历


--查询一个员工的编号、姓名、薪水和部门编号
select * from employees;
select employee_id,first_name,last_name,salary,department_id from employees

--查询一个员工的编号、姓名、薪水和部门编号、部门的名称、部门所在地的编号
select * from departments

select employee_id,first_name,last_name,salary,d.department_id,d.department_name,d.location_id
from employees e
join departments d
on (d.department_id = e.department_id)


--查询一个员工的编号、姓名、薪水和部门编号、部门的名称、部门所在地的编号,所在地名称
select * from locations

select employee_id,first_name,last_name,salary,d.department_id,
       d.department_name,d.location_id,l.street_address,l.country_id,c.country_name
from employees e
join departments d
on (d.department_id = e.department_id)
join locations l
on(d.location_id = l.location_id)
join countries c
on l.country_id = c.country_id
where e.salary > 10000 and d.department_name = 'Executive'

连接查询-自连接和SQL92连接

--三表连接查询示例2：查询工资等级，部门名称
--删除表
delete from emp2  --删除表中的数据
truncate table emp2;--删除表中的数据
drop table emp2;
drop table emp3;
drop table emp4;

select * from dept;
select * from emp;
select * from salgrade;
--查询员工的编号、姓名、工资、部门编号
select e.empno,e.ename,e.sal,e.deptno
from emp e
--查询员工的编号、姓名、工资、部门编号、部门的名称
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e
join dept d
on (e.deptno = d.deptno)

--查询员工的编号、姓名、工资、部门编号、部门的名称，工资等级
select e.empno,e.ename,e.sal,e.deptno,d.dname,sg.grade
from emp e
left join dept d
on (e.deptno = d.deptno)
join salgrade sg
on(e.sal between sg.losal and sg.hisal)


--自连接
select * from emp;

--查询员工的编号，姓名，上级编号
select empno,ename,mgr from emp

--查询员工的编号，姓名，上级编号,上级的姓名
select e.empno,e.ename,e.mgr,m.empno,m.ename
from emp e
join emp m
on (e.mgr = m.empno) 

--没有上级的员工也显示出来（将董事长也显示出来）
select e.empno,e.ename,e.mgr,m.empno,m.ename
from emp e
left join emp m
on (e.mgr = m.empno) 


--SQL99的连接查询
--分类1（连接条件）：交叉连接、自然连接，using子句、on子句
--分类2（是否显示匹配数据）：内连接  外连接
--分类3：自连接、非自连接

--优点：连接条件和筛选条件分离，join连接



--SQL92的连接查询

--查询员工的编号、姓名、薪水，部门编号、部门名称
select *
from dept d,emp e

select e.empno,e.ename,e.sal,d.deptno,d.dname
from dept d,emp e
where d.deptno = e.deptno and sal > 2800

--查询员工的编号、姓名、薪水，部门编号、部门名称、工资等级
select e.empno,e.ename,e.sal,d.deptno,d.dname,sg.grade
from dept d,emp e,salgrade sg
where d.deptno = e.deptno and e.sal between sg.losal and sg.hisal 
      and sal > 2800
order by sal 

--SQL92:连接条件和筛选条件都在where子句中，没有分开

不相关子查询

--子查询
--1.单行子查询: 子查询的结果只有一条记录
  --1.1查得所有比“BLAKE”工资高的员工的信息
   select * from emp;
   select sal from emp where ename = 'BLAKE'
   select * from emp where sal > 2850

   select * from emp where sal > (select sal from emp where ename = 'BLAKE')

  --1.2查询工资高于平均工资的雇员名字和工资。
   select avg(sal) from emp
   select * from emp where sal > (select avg(sal) from emp)

  --1.3查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
  select job from emp where ename = 'scott' 
  select hiredate from emp where ename = 'scott' 
  select * from emp where job = (select job from emp where ename = 'scott') and hiredate < (select hiredate from emp where ename = 'scott' )



--2.多行子查询：子查询的结果包括多条记录,需要借助ANY,ALL,IN进行相关比较
  --2.1查询工资低于任意一个“CLERK”的工资的雇员信息。

   --思路1：单行子查询，低于任意一个，可以理解为低于最高的
   select max(sal) from emp where job ='CLERK'

   select * from emp where sal < ( select max(sal) from emp where job ='CLERK') and job !='CLERK'

   --思路2：多行子查询
   select sal from emp where job = 'CLERK'

   select * from emp where sal < ANY(select sal from emp where job = 'CLERK')
  --2.2查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
  --思路1：单行子查询
  select max(sal) from emp where job = 'SALESMAN'  --1600
  select * from emp where sal >(select max(sal) from emp where job = 'SALESMAN')

  --思路2：多行子查询
  select sal from emp where job = 'SALESMAN'
  select * from emp where sal > ALL (select sal from emp where job = 'SALESMAN')
  --2.3查询部门20中职务同部门10的雇员一样的雇员信息。
  --查询雇员信息
  --查询部门20中雇员信息
  --查询部门20中[职务同部门10的雇员一样]雇员信息
  select job from emp where deptno = 20  -- CLERK  MANAGER  ANALYST
   select job from emp where deptno = 10  --MANAGER  PRESIDENT  CLERK

  --首先知道10号部门有哪些职务
 select job from emp where deptno = 10  --MANAGER  PRESIDENT  CLERK

 select * from emp where deptno = 20 and job = ANY('MANAGER',  'PRESIDENT',  'CLERK')
 select * from emp where deptno = 20 and job IN ('MANAGER',  'PRESIDENT',  'CLERK')

  --2.4查询在雇员中有哪些人是管理人（有下级）  含义：员工的编号出现在mgr列就是管理人
  select * from emp

  select distinct mgr from emp where mgr is not null  --所有管理人的编号 

  select * from emp where empno in (select distinct mgr from emp where mgr is not null)

  --不相关子查询（单行子查询、多行子查询）
         -- 子查询可以独立运行，先运行子查询，再运行外查询
  --相关子查询

相关子查询

--不相关子查询
  --子查询可以独立运行
  --先执行子查询，再执行外查询
--相关子查询
  --子查询不可以独立运行
  --先执行外查询，再执行子查询

--查询本部门最高工资的员工
--查询本部门（10，20，30，40....）最高工资的员工  
--查询本部门（10）最高工资的员工 
  select * from emp e where e.deptno = 10 and sal = (select max(sal) from emp e2 where e2.deptno = 10 )
  select * from emp e where e.deptno = 20 and sal = (select max(sal) from emp e2 where e2.deptno = 20 )
  select * from emp e where e.deptno = 30 and sal = (select max(sal) from emp e2 where e2.deptno = 30 )
  select * from emp e where e.deptno = 40 and sal = (select max(sal) from emp e2 where e2.deptno = 40 )

  select * from emp e where sal = (select max(sal) from emp e2 where e2.deptno = e.deptno )
  select * from emp e where sal = (select max(sal) from emp  where deptno = e.deptno )

--查询工资高于其所在部门的平均工资的那些员工
--查询10部门中工资高于10部门的平均工资的那些员工
  select * from emp where deptno = 10 and sal >(select avg(sal) from emp where deptno = 10)
  select * from emp where deptno = 20 and sal >(select avg(sal) from emp where deptno = 20)
  select * from emp where deptno =30 and sal >(select avg(sal) from emp where deptno = 30)

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

--子查询和连接查询结合使用
--查询每个部门平均薪水的等级

  --查询每个部门平均薪水
  select avg(sal) from emp

  select deptno,avg(sal)
  from emp
  where deptno is not null
  group by deptno
  --查询每个员工薪水的等级
  select empno,ename,sal,sg.*
  from emp e
  join salgrade sg
  on( e.sal between sg.losal and sg.hisal)


  --查询每个部门平均薪水的等级

 select deptno,asal,sg.*
  from (select deptno,avg(sal) asal   from emp   where deptno is not null   group by deptno) e
  join salgrade sg
  on( e.asal between sg.losal and sg.hisal)

--子查询分类
--相关子查询和不相关子查询
--单行子查询和多行子查询