Mysql 连接查询和子查询

连接查询

在实际开发中，大部分情况都不是从单表中查询数据，一般都是多个表联合查询获取最终的结果，比如学生和班级一般都会分为两张表，连接查询就是多个表连接查询。

连接查询根据表的连接方式来划分，可以分为：

  - 内连接
     - 等值连接
     - 非等值连接
     - 自连接
  - 外连接
     - 左外连接（左连接）
     - 右外连接（右连接）
  - 全连接

笛卡尔积现象

当两张表连接查询时，如果没有任何条件进行限制，最终的结果条数是两张表记录条数的乘积。

例如：分别由 emp (员工表, 14 条记录) 和 dept (部门表, 4条记录)。

-- 查询员工名称和部门名称
$ select ename, dname from emp, dept;

上面的结果为 56 条（14 * 4），因为查询的语句中没有任何限制条件，emp 表中的每一条数据都可以和 dep 表中的每一条数据匹配上，所以就是 56 条。

避免笛卡尔积现象就要给连接查询增加条件限制，但是增加条件限制并不会减少查询的次数，只不过最后的结果是条件匹配有效的数据，去掉了匹配失败的数据。

连接查询语句:
    $ select 字段1, 字段2 from 表名 join 表名 on 查询条件;
 例如：
     -- 查询员工名称和对应的部门名称
  $ select ename, dname from emp as e join dept as d on e.deptno = d.deptno;
  /*
  * 注: 上面查询中给两张表都起了别名, 这是推荐方式, 这样就可以知道对应的字段在那个表了（通过别名.字段名）,
  * 因为有可能两张表出现同名字段, 如果不指定别名会查询两张表, 如果指定了别名就直接去指定的表了.
  */

内连接 - 等值连接

等值连接就是连接查询的条件是相等的，如 表A.bId = 表B.bId;

 -- 查询员工名称和对应的部门名称
  $ select ename, dname from emp as e join dept as d on e.deptno = d.deptno;

内连接 - 非等值连接

非等值连接就是连接查询的条件是不相等的，如 表A.bId != 表B.bId;

-- 查询每个员工的薪水、名称、薪水等级.
    $ select ename, sal, grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;

内连接 - 自连接

自连接就是把一张表看成两张表，自己连接查询自己。

-- 查询每个员工的领导名称, 显示员工名称和领导名称
    $ select a.ename as '员工', b.ename as '领导' from emp as a join emp as b on a.mgr = b.empno;
 /*
 * 在上面的查询语句中通过 emp 表中的 mrg 字段 = empno 字段, 进行了自连接查询, 把 mrg = empno 的数据看成为一张领导表
 * 把 empno 看成一张员工表, 这样就是员工表连接查询领导表了, 通过 mrg = empno 进行了条件限制, 因为是一张表
 * 所以必须要起别名, 不然语句就会发生错误, 因为字段名重复, 不知道改取那个表中的了.
 */

自连接可以进行等值查询也可以进行非等值查询。

内连接补充 inner :

-- 在内连接中其实还有一个 inner 语句, 但是可以省略不写, 所以上面都没有写上, 下面加上 inner 的语句效果和上面相同
$ select ename, dname from emp as e inner join dept as d on e.deptno = d.deptno;
$ select ename, sal, grade from emp as e inner join salgrade as s on e.sal between s.losal and s.hisal;
$ select a.ename as '员工', b.ename as '领导' from emp as a inner join emp as b on a.mgr = b.empno;

外连接

外连接分为左外连接（左连接）、右外连接（右连接）。
左连接：指的是连接查询中左边的表为主表，右边的表为副表。
右连接：指的是连接查询中右边的表为主表，左边的表为副表。

优先查询出主表中的所有数据，捎带着查询出副表中判断条件正确的数据。
如果没有使用外连接，那么两张表是相等的关系，没有主副之分，只会查询出两种表中判断条件正确的数据，不符合则就直接放弃了。

-- 左/右连接语句, left/right 指的就是左右连接：
    $ select 字段名 from 表1 left/right join 表2 on 条件;
例如：
    -- 1. 查询出员工名称和其对应的领导名称
  $ select a.ename as '员工', b.ename as '领导' from emp as a left join emp as b on a.mgr = b.empno;
  -- 上面的写法才是正确的写法, 如果不使用外连接则会忽略上级领导 mgr = null 的员工, 数据就不对了.
  -- 也可以下面这样写, 调换表位置使用右连接.
  $ select a.ename as '员工', b.ename as '领导' from emp as b right join emp as a on a.mgr = b.empno;
  -- 2. 找出那个部门没有员工
  $ select t.deptno from dept t left join emp e on e.deptno = t.deptno where e.deptno is null;

多表连接查询

多张表连接查询就是多个 join on：

-- 多表连接查询：
    $ select 字段 from 表1 join 表2 on 条件 join 表3 on 条件 ...;
例如：
    -- 1. 找出每个员工的部门名称和工资等级
  $ select e.ename, e.deptno, s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and hisal;
  -- 2. 找出每个员工的部门名称、工资等级、上级领导
  $ select e.ename, e.deptno, s.grade, t.ename from emp e
   > join dept d on e.deptno = d.deptno
   > join salgrade s on e.sal between s.losal and hisal
   > left join emp t on e.mgr = t.empno;

子查询

子查询就是一条语句中嵌套多个 select 语句，被嵌套的 select 就是子查询：

-- 子查询可以用在下面的几个地方
select
    子查询
from
    子查询
where
    子查询

where 后面使用子查询

-- 子查询语句：
    $ select 字段 from 表名 where (select 子查询);
例如：
    -- 获取高于平均薪资的员工
    $ select ename from emp where sal > (select avg(sal) from emp);
  /*
  * 因为 where 后面不能跟分组函数, 所以使用子查询获取平均值.
  */

from 后面使用子查询

-- 子查询语句：
    $ select 字段 from (select 子查询);
例如：
    -- 1. 找出每个部门的平均薪资等级
  $ select t.deptno, s.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t
   > join salgrade s on t.avg_sal between s.losal and s.hisal;

select 后面使用子查询

-- 子查询语句
    $ select 字段, (select 子查询) from 表名;
 例如：
     -- 获取员工和对应的部门名称
  -- 平常可以这么写：
  $ select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;
  -- 使用子查询可以这么写：
  $ select e.ename, (select dname from dept d where e.deptno = d.deptno) dname from emp e;

union 的用法

union 可以将两次查询结果合并起来。

-- union 语句：
    $ select 字段 from 表名 union select 字段 from;
例如：
    -- 找出工作岗位为 MANAGER 和 SALESMAN 的员工：
  -- 一般会这样写：
  $ select ename, job from emp where job in ('MANAGER','SALESMAN');
  -- 使用 union 可以这样写:
     $ select ename, job from emp where job = 'MANAGER' union select ename, job from emp where job = 'SALESMAN';

limit

limit 可以用来实现分页效果：

-- limit 语句：
    $ select 字段 from 表名 limit startIndex(起始位置), length(条数);
例如:
    -- 获取第1页数据, 每页数据 10 条
  $ select ename, sal from emp limit 0, 10;
  -- 获取第2页数据, 每页数据 10 条
  $ select ename, sal from emp limit 10, 10;
  -- 获取第3页数据, 每页数据 10 条
  $ select ename, sal from emp limit 20, 10;
/*
* 根据上面的查询可以总结出: (pageIndex - 1) * pageSize, pageSize 就是 limit 后面需要的值：limit (pageIndex - 1) * pageSize, pageSize.
*/

limit 是 sql 执行的最后一个环节：

select        5
    ...
from            1
    ...
where            2
  ...
group by    3
    ...
having        4
    ...
order by    6
    ...
limit            7
    ...