MySQL基础学习5-多表连接与子查询

一、3张表或4张表怎么连接？

语法：
select ***
from 
    a join b on a和b的连接条件
    join c on a和c的连接条件
    join d on a和d的连接条件
    
一条SQL中内连接和外连接可以混合

案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级

 select a.ename '员工名',b.dname '部门名',a.sal '薪资',c.grade '薪资等级' from emp a join dept b
    -> on b.deptno = a.deptno join salgrade c on a.sal between c.losal and c.hisal;
+--------+------------+---------+----------+
| 员工名 | 部门名     | 薪资    | 薪资等级 |
+--------+------------+---------+----------+
| SMITH  | RESEARCH   |  800.00 |        1 |
| ALLEN  | SALES      | 1600.00 |        3 |
| WARD   | SALES      | 1250.00 |        2 |
| JONES  | RESEARCH   | 2975.00 |        4 |
| MARTIN | SALES      | 1250.00 |        2 |
| BLAKE  | SALES      | 2850.00 |        4 |
| CLARK  | ACCOUNTING | 2450.00 |        4 |
| SCOTT  | RESEARCH   | 3000.00 |        4 |
| KING   | ACCOUNTING | 5000.00 |        5 |
| TURNER | SALES      | 1500.00 |        3 |
| ADAMS  | RESEARCH   | 1100.00 |        1 |
| JAMES  | SALES      |  950.00 |        1 |
| FORD   | RESEARCH   | 3000.00 |        4 |
| MILLER | ACCOUNTING | 1300.00 |        2 |
+--------+------------+---------+----------+
14 rows in set (0.00 sec)

案例升级：找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级

select a.ename '员工名',b.dname '部门名',a.sal '薪资',c.grade '薪资等级',l.ename from emp a join dept b
    -> on b.deptno = a.deptno join salgrade c on a.sal between c.losal and c.hisal
    -> left join emp l on a.mgr = l.empno;
+--------+------------+---------+----------+-------+
| 员工名 | 部门名     | 薪资    | 薪资等级 | ename |
+--------+------------+---------+----------+-------+
| SMITH  | RESEARCH   |  800.00 |        1 | FORD  |
| ADAMS  | RESEARCH   | 1100.00 |        1 | SCOTT |
| JAMES  | SALES      |  950.00 |        1 | BLAKE |
| WARD   | SALES      | 1250.00 |        2 | BLAKE |
| MARTIN | SALES      | 1250.00 |        2 | BLAKE |
| MILLER | ACCOUNTING | 1300.00 |        2 | CLARK |
| ALLEN  | SALES      | 1600.00 |        3 | BLAKE |
| TURNER | SALES      | 1500.00 |        3 | BLAKE |
| JONES  | RESEARCH   | 2975.00 |        4 | KING  |
| BLAKE  | SALES      | 2850.00 |        4 | KING  |
| CLARK  | ACCOUNTING | 2450.00 |        4 | KING  |
| SCOTT  | RESEARCH   | 3000.00 |        4 | JONES |
| FORD   | RESEARCH   | 3000.00 |        4 | JONES |
| KING   | ACCOUNTING | 5000.00 |        5 | NULL  |
+--------+------------+---------+----------+-------+

二、子查询

select语句嵌套select语句，被嵌套的语句被称为子查询

1.where子句中的子查询

案例：找出比最低工资高的员工姓名和工资

// 1.查询最低工资是多少
select min(sal) from emp
// 2.找出>800的
where sal > (select min(sal) from emp)
// 3.合并
select ename,sal from emp
    -> where sal > (select min(sal) from emp);

2.from子句中的子查询

from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

案例：找出每个岗位的平均工资的薪资等级。

// 算出平均工资(按照岗位分组求平均值)
select job '工作岗位', avg(sal) '平均工资' from emp group by job; 
// 找出每个岗位的平均薪资等级
select a.grade from salgrade a join * b on b.sal between a.losal and a.hisal
// 合并
select b.*, a.grade from  
	(select job, avg(sal) avgsal from emp group by job) b  // 把结果当成一张表
	join
	salgrade a 
	on b.avgsal between a.losal and a.hisal;
// 结果
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| ANALYST   | 3000.000000 |     4 |
| CLERK     | 1037.500000 |     1 |
| MANAGER   | 2758.333333 |     4 |
| PRESIDENT | 5000.000000 |     5 |
| SALESMAN  | 1400.000000 |     2 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

三、union

合并查询结果集

**union的效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻。**但union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接。

案例：查询工作岗位是MANAGER和SALESMAN的员工？

select ename,job from emp where job in ('MANAGER','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

union

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

注意事项：union在进行结果集合并的时候，列要相同

 select ename,job from emp where job = 'MANAGER'
    -> union
    -> select ename from emp where job = 'SALESMAN';
ERROR 1222 (21000): The used SELECT statements have a different number of columns

类型不一样都不报错？、？？
mysql> select ename,empno from emp where job = 'MANAGER'
    -> union
    -> select ename,job from emp where job = 'MANAGER'
    -> ;
+-------+---------+
| ename | empno   |
+-------+---------+
| JONES | 7566    |
| BLAKE | 7698    |
| CLARK | 7782    |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+-------+---------+

四、limit(重要)

将查询结果集的一部分取出来，通常使用在分页查询当中（把搜索结果分页展示）

百度默认一页显示10条记录，一页一页的翻页看

limit怎么用？

完整：

limit  startIndex,length	//  startIndex：起始下标（从0开始）、length长度

mysql> select ename,sal from emp
    -> order by sal desc
    -> limit 5,7;
// 如果length越界了，则取到最后一个为止

+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
7 rows in set (0.00 sec)

缺省：

limit  5 // 取前五

例：按照薪资降序，取出排名在前5的员工

select ename,sal from emp
	order by sal desc
	limit 5;	// 
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

例：取出工资排名在[5-9]名的员工？

select ename, sal from emp
    -> order by sal desc
    -> limit 4,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

五、分页

假设每页显示3条记录。

第1页：limit [ 0 , 3]

第2页：limit [ 3, 3]

第3页：limit [ 6 , 3]

第4页：limit [ 9, 3]
$$规律：（页码-1）\ast pagesize$$
我需要页码和每页显示的记录条数

记公式：
$$limit(pageNo-1)\ast pageSize,pageSize$$

# 五、分页

假设每页显示3条记录。

第1页：limit [ 0 , 3]

第2页：limit [ 3,  3]

第3页：limit [ 6 , 3]

第4页：limit [ 9,  3]
$$
规律：（页码 - 1）* pagesize
$$
**我需要页码和每页显示的记录条数**

 [外链图片转存中...(img-GAW7mV6B-1688611329137)]

记公式：
$$
limit(pageNo - 1) * pageSize , pageSize
$$