一、连接查询

1.连接查询概述

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询出最终的结果。
在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。采用多张表分别存储各自的信息，给它们间建立相应的连接，可以减少这种数据冗余，即关系型数据库。

2.连接查询分类

根据语法出现的年代来划分的话，包括：

• SQL92（一些老的DBA可能还在使用这种语法，DBA: DataBase Administrator，数据库管理员）
• SQL99(比较新的语法)

根据表的连接方式来划分，包括：

• 内连接：又可分为等值连接、非等值连接和自连接。
• 外连接：又可分为左外连接（左连接）和右外连接（右连接）。
• 全连接（这个不讲，很少使用，简单了解即可）

  3.连接查询原理和笛卡尔积现象

  3.1 笛卡尔积现象

  示例：找出每一个员工的部门名称，要求显示员工名和部门名。
  
  

  select ename,dname from emp,dept;//(笛卡尔积现象)

  一共56条结果
  笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

  3.2 表的别名

  使用as关键字或空格可以给别取别名。表的别名有什么好处？
  第一：执行效率高。
  第二：可读性好。

  select e.ename ,d.dname from emp e,dept d;//使用表的别名

  3.3 怎么避免笛卡尔积现象？

  加条件进行过滤可以避免笛卡尔积现象，会减少记录的匹配次数吗？
  答：不会，匹配次数还是56次，只不过显示的有效记录减少了。
  案例：找出每一个员工的部门名称，要求显示员工名和部门名

  select 
  e.ename,d.dname
  from
  emp e,dept d
  where //加限制条件
  e.deptno=d.deptno; //SQL92,以后不用

  4.内连接

  4.1 等值连接

  最大特点是：条件是等量关系。
  案例：查询每个员工的部门名称，要求显示员工名和部门名。 ```sql //SQL92(不使用了) select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and xxx and xxx; //结构不清晰,连接条件和过滤条件在一起

//SQL99(常用的) select e.ename,d.dname from emp e inner join //inner可以省略，省略默认为内连接，带着inner目的是可读性好一些。 dept d on 连接条件 where 数据过滤条件

SQL99语法机制更清晰一些：表的连接条件和后来的where过滤条件分离了。
```sql
//SQL99语法机制
select
    ...
from 
    A
inner join   //inner可以省略，带着inner目的是可读性好一些。
    B
on
    连接条件
where
    数据过滤条件

4.2 非等值连接

最大的特点是：连接条件中的关系是非等量关系。
示例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select 
    e.ename,e.sal,s.grade
from 
    emp e
inner join 
    salgrade s
on
    e.sal between s.losal and s.hisal;

4.5 自连接

最大的特点是：一张表映射为两张表，两张表进行连接，即自己连接自己。
示例：找出每个员工的上级领导，要求显示员工名和对应的领导名

思路：7369号SMITH员工的领导编号为7902，将7369与7902连接，即需要在同一张表中建立连接关系，连接关系为员工的领导编号=领导的员工编号。

select
    a.ename,b.ename
from 
    emp a
inner join 
    emp b
on 
    a.mgr=b.empno;

注：这里的连接关系同样也可以为非等量关系。

5.外连接

5.1 什么是外连接，和内连接有什么区别？

• 内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。

• 外连接：假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当附表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

  5.2 外连接的分类

• 左外链接（左连接）：表示左边的这张表是主表

• 右外链接（右链接）：表示右边的这张表是主表

左连接有右链接的写法，右链接也会有相应的左连接的写法。

5.3 示例

示例1：找出每个员工的上级领导（所有员工都必须查出来，包括老板KING）

//错误--内连接
select 
    a.ename '员工' , b.bname '领导'
from 
    emp a
（inner）join //inner 可以省略
    emp b
on 
    a.mgr=b.empno;

错误方式，老板KING的上级领导为NULL，在使用内连接方式查询时，无法查询到老板KING的上级领导，会被忽略，导致查询结果丢失了一条记录。

//正确--外连接
select
    a.ename '员工',b.ename '领导'
from
    emp a
left (outer) join //out可以省略
    emp b
on 
    a.mgr=b.empno;

外连接最重要的特点是：主表的数据无条件的全部查询出来。在实际开发中，外连接使用的很多。
示例2：找出那个部门没有员工？
员工表

部门表

select 
    d.*  //*代表所有字段,只需显示没有员工的部门
from 
    emp e
right join 
    dept d
on 
    e.deptno=d.deptno
where
    e.empno is null;//没有员工时，外连接会自动使用null匹配

6.全连接（了解即可）

全连接是结合左、右外连接的结果，连接表将包含的所有记录来自两个表，并使用NULL值作为两侧缺失匹配结果。

7.三表连接查询

示例1：找出每一个员工的部门名称以及工资等级。

    A
join
    B
on
    ...
join
    C
on
    ...
//表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。
//或者A表和B表连接后，作为整体再和C表连接

select 
    e.ename,d.dname,s.grade
from 
    emp e
join 
    dept d
on 
    e.deptno=d.deptno
join 
    salgrade s
on 
    e.sal between s.losal and s.hisal;

示例2：找出每一个员工的部门名称、工资等级、以及上级领导。（注意老板KING的上级领导为NULL，这里需要使用外连接）

select 
    e.ename '员工',d.dname,s.grade,e1.ename '领导'
from 
    emp e
join 
    dept d
on 
    e.deptno=d.deptno
join 
    salgrade s
on 
    e.sal between s.losal and s.hisal
left join
    emp e1
on 
    e.mgr=e1.empno;

二、子查询

1.什么是子查询？子查询都可以出现在哪里？

select语句当中嵌套select语句，被嵌套的select语句是子查询。子查询可以出现在哪里？

select
..(select)
From       
..(select)
where
..(select)

2.示例

2.1 where子句当中使用子查询

示例1：找出高于平均薪资的员工信息

select *from emp where sal>avg(sal) //错误，分组函数不能直接在where中使用

//第一步：找出平均薪资
select avg(sal) from emp;
//第二步：where 过滤
select * from emp where sal>2073;
//第一步和第二步合并：
select *from emp where sal>(select avg(sal) from emp);

2.2 from后面嵌套子查询

示例2：找出每个部门平均薪水的薪水等级

• 第一步：找出每个部门的平均薪水（按照部门编号分组，求sal的平均值）

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

• 第二步：将以上的查询结果当做临时表t，让t表和salgrade s 表连接，条件是：t.avgsal between s.losal and s.hisal;

  select 
    t.*,s.grade
  from 
    (select deptno ,avg(sal) as avgsal from emp group by deptno) t
  join 
    salgrade s
  on 
    t.avgsal between s.losal and s.hisal;

  
  示例3：找出每个部门平均的薪水等级

• 第一步：找出每个员工的薪水等级

  select 
    e.ename,e.sal,e.deptno,s.grade 
  from     
     emp e 
  join 
     salgrade s 
  on 
     e.sal between s.losal and s.hisal;

  

• 第二步：基于以上的结果，继续按照deptno分组，求grade平均值。

  select 
    e.deptno,avg(s.grade)
  from 
    emp e 
  join 
    salgrade s 
  on
    e.sal between s.losal and s.hisal
  group by
    e.deptno;

  
  注：不要都把查询结果当作临时表，然后使用嵌套进行查询，这样效率低。这里第一步的查询结果可以直接分组，然后再使用分组查询函数即可。

  2.3 select后面嵌套子查询（使用较少）

  示例1：找出每个员工所在的部门名称，要求显示员工名和部门名。

  //方式一
  select 
    e.ename,d.dname
  from 
    emp e
  join 
    dept d
  on 
    e.deptno=d.deptno;

  //方式二
  select 
    e.ename,e.deptno,
  (    
    select
        d.dname
    from
        dept d
    where
        e.deptno=d.deptno
  ) 
  as 
    dname
  from
    emp e;