【无标题】

前言

对mysql中的查询、连接相关知识进行梳理。

---

1. 子查询

通过一个案例来了解为什么要使用子查询？
案例:查询所有比"CLARK"工资高的员工信息

-- ①查询 "CLARK" 工资
select * from emp where ename = 'CLARK'; -- 2450
-- ②查询所有比"CLARK"工资高的员工
select * from emp where sal > 2450;

存在的问题:
两次命令才能解决一个问题,效率低下,第二个命令依托于第一个命令,第一个命令的结果会给第二个命令去使用,但是因为第一个命令的结果可能不确定要改,所以第二个命令也会导致修改。

解决办法：
将①和②合并 -> 子查询

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

1.1 不相关子查询

介绍：子查询可以独立运行，先运行子查询，再运行外查询。

1.1.1 单行子查询

【案例1】 查询工资高于平均工资的雇员名字和工资

select ename, sal from emp where sal > (select avg(sal) from emp);

【案例2】 查询和"CLARK"同一个部门且比他工资低的雇员名字和工资

select ename, sal
  from emp
 where deptno = (select deptno from emp where ename = 'CLARK')
   and sal < (select sal from emp where ename = 'CLARK');

【案例3】 查询职务和"SCOTT"相同，比"SCOTT"雇佣时间早的雇员信息

select *
  from emp
 where job = (select job from emp where ename = 'SCOTT')
   and hiredate < (select hiredate from emp where ename = 'SCOTT')

1.1.2 多行子查询

【案例1】查询部门20中职务同部门10的雇员一样的雇员信息

• 方法1

 
select *
   from emp
  where deptno = 20
    and job in (select job from emp where deptno = 10)

• 方法2

select *
from emp
where deptno = 20
and job = any(select job from emp where deptno = 10)

【案例2】查询工资比所有的"SALESMAN"都高的雇员的编号、名字和工资

• 法1 -多行子查询

select empno, ename, sal
   from emp
  where sal > all (select sal from emp where job = 'SALESMAN')

• 法2 -单行子查询

select empno, ename, sal
from emp
where sal > (select Max(sal) from emp where job = 'SALESMAN')

【案例3】查询工资低于任意一个"CLERK"的工资的雇员信息

• 法1 -多行子查询

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

• 法2 -单行子查询

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

1.1.3 小结

不相关子查询
好处：简单、功能强大（一些使用不相关子查询不能实现或者实现过程繁琐的子查询，可以用相关子查询来实现）
缺点：稍难理解

1.2 相关子查询

介绍：子查询不可以独立运行，必须先运行外查询，再运行子查询。
【案例1】查询部门最高工资的员工

• 法1 -不相关子查询
  缺点：sql较长， 并且具体部门种类可能未知

select *
  from emp
 where deptno = '10'
   and sal = (select max(sal) from emp where deptno = 10)
union

select *
  from emp
 where deptno = '20'
   and sal = (select max(sal) from emp where deptno = 20)
union
select *
  from emp
 where deptno = '30'
   and sal = (select max(sal) from emp where deptno = 30);

• 法2 -相关子查询

select *
  from emp e
 where sal = (select max(sal) from emp where deptno = e.deptno)
 order by deptno;

【案例2】查询工资高于其所在岗位的岗位平均工资的员工

• 法1 -不相关子查询

select *
  from emp
 where job = 'CLERK'
   and sal >= (select avg(sal) from emp where job = 'CLERK')
union
select *
  from emp
 where job = 'SALESMAN'
   and sal >= (select avg(sal) from emp where job = 'SALESMAN')
union
select *
  from emp
 where job = 'MANAGER'
   and sal >= (select avg(sal) from emp where job = 'MANAGER')
union
select *
  from emp
 where job = 'ANALYST'
   and sal >= (select avg(sal) from emp where job = 'ANALYST')
union
select *
  from emp
 where job = 'PRESIDENT'
   and sal >= (select avg(sal) from emp where job = 'PRESIDENT');

• 法2 -相关子查询

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

为什么要用大于等于呢？
例如：‘PRESIDENT’ 这个岗位只有一个人 平均工资和工资都是5000 5000>5000 是查询不出结果的，查询的结果集就会缺失数据。

2. 连接

99语法： 筛选条件和查询条件分开
实际开发过程中需要针对两张甚至更多张的数据表进行操作，而这多张表之间需要使用主键和外键关联在一起，然后使用连接查询来查询多张表中满足要求的数据记录。
一条sql语句查询多个表，得到一个结果，包含多个表的数据。效率高。 在SQL99中，连接查询需要使用join关键字来实现
提供了多种连接查询类型： cross natural using on
交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡尔积操作，所谓笛卡尔积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。比如：有两个表，左表有m条数据记录，x个字段，右表有n条记录，y个字段，则执行交叉连接之后返回m*n条数据记录，x+y个字段。

2.1 交叉连接

【案例1】查询员工的编号，姓名，部门编号,部门名称

-- 笛卡尔积，没有实际意义，有理论意义
SELECT * FROM emp a CROSS JOIN dept b;
--在mysql中 cross 可以省略， Oracle 中不可以省略
SELECT * FROM emp a JOIN dept b; 

2.2 自然连接

优点：自动匹配所有的同名列， 同名列只展示一次就可以了

SELECT empno, ename, sal, dname FROM emp a NATURAL JOIN dept b; 

对上边的sql进行优化：

-- 优化1： 查询字段时，没有指定字段所属的数据库表，效率低
SELECT emp.empno, emp.ename, emp.sal, dept.dname
  FROM emp NATURAL
  JOIN dept;
-- 优化2： 当表名过长时，查询语句显得非常冗余
SELECT a.empno, a.ename, a.sal, b.dname FROM emp a NATURAL JOIN dept b; 

2.3 内连接

自然连接缺点：自动匹配表中所有的同名列， 但是有时候我们希望只匹配部分同名列
解决：内连接（inner可省略）using 子句

SELECT a.empno, a.ename, a.sal, b.dname
   FROM emp a
  INNER JOIN dept b
  USING (deptno);

自然连接缺点：在自然连接中，关联字段必须是同名的，这样就造成了很多的限制。
解决： on子句

SELECT a.empno, a.ename, a.sal, b.dname
  FROM emp a
 INNER JOIN dept b
    ON a.deptno = b.deptno;

2.4 外连接

内连接出现的问题：
1.没有40号部门的员工，没有显示在查询结果中
2.如果某个员工没有部门，没有显示在查询结果中
解决：使用外连接（outer可省略），除了显示匹配的数据之外，还可以显示一些不匹配的数据

• 左外连接（显示左表全部数据）：

SELECT * FROM emp a LEFT OUTER JOIN dept b ON a.deptno = b.deptno;

• 右外连接（显示右表全部数据）：

SELECT * FROM emp a RIGHT OUTER JOIN dept b ON a.deptno = b.deptno;

• 全外连接（显示左右表全部数据）：

在mysql中是不支持的，但是在oracle中是支持的：

SELECT * FROM emp a FULL OUTER JOIN dept b ON a.deptno = b.deptno;

解决mysql中不支持全外连接的问题 ：
使用 union 并集、去重、效率低：

SELECT *
FROM emp a
LEFT OUTER JOIN dept b
ON a.deptno = b.deptno
UNION 
SELECT *
FROM emp a
RIGHT OUTER JOIN dept b
ON a.deptno = b.deptno;

union all 并集、不去重、效率高：

SELECT *
  FROM emp a
  LEFT OUTER JOIN dept b
    ON a.deptno = b.deptno
UNION ALL
SELECT *
  FROM emp a
 RIGHT OUTER JOIN dept b
    ON a.deptno = b.deptno;

mysql 中对集合的支持比较弱， 只支持并集操作；交集、差集不支持；oracle支持。

2.5 三表查询

【案例】查询员工的编号、姓名、薪水、部门编号、部门名称、薪水评级

SELECT a.ename, a.sal, a.empno, a.deptno, b.dname, c.*
  FROM emp a
 RIGHT OUTER JOIN dept b
    ON a.deptno = b.deptno
 INNER JOIN salgrade c
    ON a.sal BETWEEN c.losal AND c.hisal

2.6 自连接查询

【案例】查询员工编号，姓名，上级编号，上级姓名

SELECT e1.empno 员工编号,
       e1.ename 员工姓名,
       e1.mgr   领导编号,
       e2.ename 员工领导
  FROM emp e1
  LEFT JOIN emp e2
    ON e1.mgr = e2.empno