• backend
  • database
  • mysql
  • MySQL 基础（多表查询）

MySQL 基础（多表查询）

JOIN 把表连起来

多表查询又称为连接查询。指的是多张表联合起来查询。多表连接需要用到 join 关键字（99 年语法）将表连接起来。

语法结构：

sql
1select
2  ...
3from
4  a
5join
6  b
7on
8  a 和 b 的连接条件
9join
10  c
11on
12  a 和 c 的连接条件
13where
14  附加条件
15;

多表查询有外连接 outer、内连接 inner、全连接（极少用）。

内连接的特点：完全能够匹配上这个条件的数据查询出来。

外连接的特点：不能匹配上的数据也要查询出来。

内连接查询

内连接查询分等值查询、非等值查询、自连接。

在内连接中，多张表的关系是平等关系，不含主次之分。它只会将多表能够互相匹配上的数据查询出来，而忽略没有匹配到的。

sql
1-- 多表查询（连接查询）
2-- 当两张表没有任何限制条件，最终查询结果条数，是两张表对应字段条数的乘积
3SELECT ENAME,DNAME FROM EMP,DEPT;
4-- 附加条件后的查询，根据两张表的deptno 查询 dname,此时匹配次数没减少，依然是两张表对应字段条数的乘积 92年语法
5SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME DESC;
6-- 效果高一点的查询方法==>加上表名，指定两表查找的字段 92年语法
7SELECT EMP.ENAME,DEPT.DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO ORDER BY DNAME DESC;
8-- 表起别名.92年语法
9SELECT e.ENAME,d.DNAME from EMP AS e,DEPT as d WHERE e.DEPTNO=d.DEPTNO ORDER BY d.DNAME DESC;
10-- 内连接之等值连接 99年新语法
11SELECT e.ENAME,d.DNAME from EMP AS e JOIN DEPT as d ON e.DEPTNO=d.DEPTNO ORDER BY d.DNAME DESC;
12-- 99年新语法的特点：on 用来做连接条件，where 做筛选条件.完整语句要加上 inner
13SELECT e.ENAME,d.DNAME from EMP AS e INNER JOIN DEPT as d ON e.DEPTNO=d.DEPTNO ORDER BY d.DNAME DESC;
14-- 内连接之非等值连接：显示员工姓名、员工薪资和员工薪资等级，其中等级由sal 在LOSAL、HISAL的区间决定
15SELECT e.ENAME,e.SAL,s.GRADE FROM EMP AS e INNER JOIN SALGRADE as s ON e.SAL BETWEEN s.LOSAL AND s.HISAL ORDER BY s.GRADE ASC;
16-- 内连接之自连接，一张表看成两张表：查询员工的上级领导，要求显示员工名和对应的领导名
17SELECT e.ENAME,m.ENAME as mgrName FROM EMP e JOIN EMP m ON e.MGR = m.EMPNO;

外连接查询

外连接查询分左外连接和右外连接。

左右的意思是将左边或者右边的表当做是主表进行全部查询，捎带着查副表。

这是有主次关系的，关键字 left 或者 right 指向的表就是主表，会将主表的内容全部查询出来，捎带着查询能够匹配上的副表。没有查询到的都会显示 null。

外连接的查询结果一定是大于或等于内连接的。

sql
1-- 内连接查询。只会查询出完全能够匹配的结果，以下语句匹配出14条
2SELECT e.ENAME,d.DNAME from EMP e JOIN DEPT d ON e.DEPTNO=d.DEPTNO;
3-- 外连接查询之右外连接。可以将没有完全匹配的结果也查出来，以下语句匹配出15条,right是右边部分为主表
4SELECT e.ENAME,d.DNAME from EMP e RIGHT JOIN DEPT d ON e.DEPTNO=d.DEPTNO;
5-- 外连接查询之左外连接。可以将没有完全匹配的结果也查出来，以下语句匹配出15条,left是左边部分为主表
6SELECT e.ENAME,d.DNAME from DEPT d LEFT JOIN EMP e ON e.DEPTNO=d.DEPTNO;
7-- 外连接查询的完整写法，加上关键字 outer。外连接的查询条数一定大于等于内连接的查询条数。
8SELECT e.ENAME,d.DNAME from DEPT d LEFT OUTER JOIN EMP e ON e.DEPTNO=d.DEPTNO;
9-- 外连接查询每个员工的领导，要求显示所有员工的领导名。  员工名是主表
10SELECT e.ENAME,ee.ENAME as mangerName FROM EMP e LEFT JOIN EMP ee ON e.MGR = ee.EMPNO;

三表、四表等多表连接

三、四表连接可以混合使用内连接和外连接。

sql
1-- 三四表连接
2-- 找出每个员工的员工名、部门名称、薪资和薪资等级
3SELECT d.DNAME,e.ENAME,e.SAL,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;
4-- 找出每个员工的员工名、部门名称、薪资和薪资等级以及每个员工的上级领导名
5SELECT d.DNAME,e.ENAME,e.SAL,ee.ENAME as managerName,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 LEFT JOIN EMP ee ON e.MGR=ee.EMPNO;

子查询

子查询指的是在一个 SELECT 语句中嵌套 SELECT，被嵌套的 SELECT 就是子查询

子查询可以出现在以下位置

sql
1SELECT...(SELECT) FROM ...(SELECT) WHERE ...(SELECT)

下面是案例

sql
1-- 子查询 SELECT中嵌套 SELECT ，被嵌套的 SELECT 语句就是子查询.
2-- 子查询出现的位置
3-- SELECT ...(SELECT) FROM ...(SELECT) WHERE ...(SELECT)
4-- 找出比最低工资高的员工姓名和员工工资，where 后面的子查询，可以创建一个条件
5SELECT e.ENAME,e.SAL FROM EMP e WHERE e.SAL > (SELECT MIN(SAL) FROM EMP);
6-- from后面的子查询，可以将子查询的查询结果当成一张临时表
7-- 找出每个岗位的平均工资
8SELECT JOB,AVG(SAL) as avgSal FROM EMP GROUP BY JOB;
9-- 找出每个岗位的平均工资的对应薪资等级
10SELECT s.GRADE,a.avgSal,a.JOB FROM (SELECT JOB,AVG(SAL) as avgSal FROM EMP GROUP BY JOB) a JOIN SALGRADE s ON a.avgSal BETWEEN s.losal AND s.HISAL;

UNION 连接

对于表连接来说，每连接一次新表，则匹配次数按照笛卡尔乘积，成倍翻

union 可以减少匹配的次数，在减少匹配次数的情况下，完成两个结果集的连接

比如 a 表记录 10 条，b 表记录 10 条，c 表记录 10 条，普通方式查询 101010=1000 次

a-b 使用 union 拼接 a-c，只用 1010+1010=200 次

以下是语法实例

sql
1-- union 要求进行结果集合并时，两个结果集的列数相同，且严格模式下还要求列和列的数据类型也相同
2SELECT ENAME,JOB FROM EMP WHERE JOB ='MANAGER' UNION SELECT ENAME, JOB FROM EMP WHERE JOB='SALESMAN';

分页 limit

在项目开发中，考虑到用户的体验，所以会筛选部分结果展示，这就会用到分页。limit 分页查询，limit 将结果的一部分取出来

语法：limit startIndex,length。

sql
1-- limit 分页查询，limit将结果的一部分取出来
2-- 语法：limit startIndex,length。startIndex从0开始
3-- limit的书写顺序在 order by之后，同时也在 order by之后执行。
4-- 按照薪资降序，取出排名在前5名的员工，简化写法,省略startIndex
5SELECT ENAME,SAL FROM EMP WHERE SAL ORDER BY SAL DESC LIMIT 5 ;
6-- 按照薪资降序，取出排名在前5名的员工，完整写法
7SELECT ENAME,SAL FROM EMP WHERE SAL ORDER BY SAL DESC LIMIT 0,5 ;
8-- 按照薪资降序，取5-9名的员工
9SELECT ENAME,SAL FROM EMP WHERE SAL ORDER BY SAL ASC LIMIT 4,5 ;
10-- 通用分页怎么做？
11-- 下面以pageSize为3条为例
12-- pageNo SQL语句    data
13-- 第1页  limit 0,3 [0,1,2]
14-- 第2页  limit 3,3 [3,4,5]
15-- 第3页  limit 6,3 [6,7,8]
16-- 换算得出计算公式 limit (pageNo-1)*pageSize,pageSize

DQL 语句总结

语句格式

select ... from ... where ...group by ...having...orger by...limit...

执行顺序

  1. from

  2. where

  3. group by

  4. having

  5. select

  6. order by

  7. limit

注意事项

数据库的查询语句设计得非常口语化，虽然多表查询更多是基于对数据表的理解，但还是能从中取得一点口诀，以下是我的分享：

  • select 后面无脑排列你想要的数据，多表查询撰写格式为 table.field

  • 分清主次、从辅关系，主要展示的数据在哪个表则哪个表为主表。（以免外查询时分不出 left 还是 right）

  • 区分内连接还是外连接时，一定要确认自己要查的数据的依赖关联字段是否可能为 null。比如说案例中查询自己领导是谁时，有些人领导为 null，那么使用内连接会少一些数据（因为内连接是完全匹配，不匹配的不展示）。

  • 自连接就是把一张表当成两张表来看。

  • 外连接的查询结果一定大于或等于内连接。

  • 多行处理函数需要先分组再使用，如果没有分组（group by），默认整张表为一组

  • 分组函数不能直接使用于 where 条件查询中，一定要 group by

  • 在一条 select 语句中，如果有 group by 语句，select 后面一律只能跟参加分组的字段、分组函数和分组函数内的字段