八.MySQL复合查询

一.基本查询回顾

分组统计 group by

函数	作用	示例语句	说明
count(*)	统计记录条数	select deptno, count(*) from emp group by deptno;	每个部门有多少人？
sum(sal)	某字段求和	select deptno, sum(sal) from emp group by deptno;	每个部门总工资
avg(sal)	求平均值	select deptno, avg(sal) from emp group by deptno;	每个部门平均工资
max(sal)	最大值	select job, max(sal) from emp group by job;	每个职位最高工资
min(sal)	最小值	select job, min(sal) from emp group by job;	每个职位最低工资

1.查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

(sal>500 or job=MANAGER)and(ename like'J%')

2.按照部门号升序而雇员的工资降序排序

order by depton asc , sal desc

3.使用年薪进行降序排序

年薪=sal*12+comm  comm有的为NULL，任何值加上NULL都为NULL 但我们运算时遇到NULL要把他视为0

select ename,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;

4.显示工资最高的员工的名字和工作岗位

        1.先找出最高工资为多少

        2.再根据最高工资筛选出 符合条件的

select max(sal) from emp;

select ename,job form emp where sal=(select max(sal) from emp);

5.显示工资高于平均工资的员工信息

6.显示每个部门的平均工资和最高工资

1.平均薪资 select avg(sal) from emp;

2.最高新增 select max(sal) from emp;

3. 根据部门分组  select * from emp group by(deptno);

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

format(值，保留几位小数)

7.显示平均工资低于2000的部门号和它的平均工资

        1.group by 对表按部门号进行分组 

        2.having 对分组聚合后的结果进行筛选

        3.select avg(sal) 输出平均薪资 

select deptno,avg(sal) 平均薪资 from EMP group by deptno  having 平均薪资<2000;

8.显示每种岗位的雇员总数，平均工资

        1.按岗位分组

        2.分完组 count(*)计算每个组的行数  avg(sal)计算每个组的平均薪资

        3.select 输出

 select  job,count(*) 岗位数,format(avg(sal),2) 平均薪资 from EMP group by job;

二.多表查询

实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。
案例：
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询

DEPT 表（部门表）

字段	含义
deptno	部门编号
dname	部门名称
loc	部门所在地

EMP 员工表 这是员工信息表， deptno 是外键，对应 DEPT 表中的主键。

字段	含义
empno	员工编号
ename	员工姓名
job	岗位
mgr	上级编号
hiredate	入职日期
sal	工资
comm	奖金
deptno	所属部门编号

select * from EMP,DEPT;

没有添加任何连接条件时，MySQL 会把 EMP 表的每一行和 DEPT 表的每一行全部组合，形成 笛卡尔积。

假设：

• EMP 表有 14 条记录

• DEPT 表有 4 条记录

那么结果是：14 × 4 = 56 条记录

每一条记录是：EMP 表中一名员工 + DEPT 表中任一部门的组合。这个结果毫无实际意义！

联表：1.where 添加连接条件

where e.deptno=d.deptno

把员工分配到他们真正所在的部门，避免出现无意义的组合（也就是避免笛卡尔积）。

1.显示部门号为10的部门名，员工名和工资

select dname,ename,sal,e.deptno from EMP e,DEPT d

where e.deptno=d.deptno and e.deptno=10;

2.显示各个员工的姓名，工资，及工资级别

select ename,sal,grade from EMP e,SALGRADE s where sal between losal and hisal;

between 最小值 and 最大值 是否在该范围内（含边界值）

between ... and ... 是 SQL 中的一个 范围判断操作符，它表示某个值是否在两个值之间（包含边界）。

联表：2.join 表 on 添加连接条件

JOIN 是 SQL 中用于 多表连接查询 的语法，它通过指定的条件把多张表的数据连接成一张大表。

类型	作用说明
INNER JOIN	两表匹配成功才显示（最常用）
LEFT JOIN	左表全保留，右表能对上就显示，不能对上为 NULL
RIGHT JOIN	右表全保留，左表不能对上为 NULL
FULL JOIN	两边都保留，不匹配的补 NULL（MySQL 不支持）

项目	JOIN	WHERE 条件连接
写法	FROM A JOIN B ON 条件	FROM A, B WHERE A.id = B.id
推荐程度	✅ 推荐，更清晰、规范	🟡 可用，老写法，容易出错
逻辑结构	显式地指定连接方式	模糊：连接条件和筛选条件混在一起
支持多种连接	✅ 支持 INNER、LEFT、RIGHT 等	🚫 只能做 INNER JOIN 效果
可读性	✅ 强	🟡 较弱

把员工和他们所在的部门名对应起来

1.where:

from 引入两个关联的表  where 添加连接条件

2.join on:

from 引入一个表

join 再引入关联的表  on  连接条件

JOIN 负责“怎么连”，WHERE 负责“连完之后怎么筛”。

三.自连接

自连接是指在同一张表连接查询.

用于处理 表中记录之间有层级关系 的场景，例如：

• 员工表中，mgr 字段是上级的 empno（员工编号）

• 我们需要通过员工的 mgr 去找到他上级是谁（也在同一张表里）

查出“FORD的上级领导编号和姓名”

1.先找到FORD的mgr领导的编号  2.再根据mgr领导的编号找到领导的信息

        1.子查询方式：

select empno,ename,job,mgr from EMP

where (select mgr from EMP where ename='FORD')=empno;

查找ename=‘FORD’员工的mgr领导编号select mgr from EMP where ename='FORD'

        2.自连接

select leader.empno,leader.ename from EMP leader, EMP worker

where leader.empno = worker.mgr and worker.ename='FORD';

where leader.empno = worker.mgr 找到每个员工对应的领导   and worker.ename='FORD';找到指定员工的领导

注意：要显示领导的信息select leader.empno,leader.ename

四.子查询

1.单列子查询

返回一行记录的子查询
1.显示SMITH同一部门的员工

2.多行子查询

1.in在结果集合中匹配任意一个

查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

        1.10号部门的工资岗位种类有什么

select distinct job from EMP where deptno=10；distinct去重

        2.找属于该工作岗位表中的员工 

job in (工作岗位表)

        3.去掉10号部门人

deptno!=10

 

2.all 大于所有返回值才成立

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

方法1:

        1.部门30员工的最大值

max(als) where deptno=30;

        2.大于部门30最大值的员工

>max

select ename, sal, deptno from EMP

where sal > (select max(sal) from EMP where deptno=30);

比30号部门的最大值大

方法2:

select ename, sal, deptno from EMP

where sal > all(select sal from EMP where deptno=30);

比30号部门all所有值大

3.any 只要大于其中一个值就行

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门
的员工）

select ename, sal, deptno from EMP where sal > any(select sal from EMP where deptno=30);

3.多列子查询

多行子查询 都是根据一个字段来进行查询，如果多个字段呢？

eg.查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

4.在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

1.显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

        1.group by + avg() 得到部门+部门平均工资的表

        2.将员工表，部门工资表进行笛卡儿积 where 连接条件为部门号相同。这样融合后的表就有所有员工的薪资+每个部门的平均薪资

select ename, t1.deptno, sal, format(sal,2) from EMP t1,

(select deptno,avg(sal) 部门资 from EMP group by deptno) t2

where t1.deptno=t2.deptno and  t1.sal>t2.部门资 ;   

//注意一定要添加连接条件 t1.deptno=t2.deptno 取掉无效笛卡儿积

where 连接条件+筛选条件

2.查找每个部门工资最高的人的姓名、工资、部门、最高工资

        1.group by+max() 部门+部门最高薪资

        2.连接 where+连接条件(部门相同)+筛选条件(最高薪资相同)

select ename,t1.deptno,sal,t2.最高薪资 from EMP t1,

(select deptno,max(sal) 最高薪资 from EMP group by deptno) t2

where t1.deptno=t2.deptno and t1.sal=t2.最高薪资;

3.显示每个部门的信息（部门名，编号，地址）和人员数量

        1.group by+count(*) 部门号+每个部门人数

        2.from DEPT连接表包含部门信息

select DEPT.deptno, dname, mycnt, loc from DEPT,

(select deptno,count(*) mycnt  from EMP group by deptno) t

where DEPT.deptno=t.deptno;

五.合并查询（union/ union all）

1.union （去重）

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

eg.将工资大于2500或职位是MANAGER的人找出来

2.union all（不去重）

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

eg.将工资大于2500或职位是MANAGER的人找出来

注意：

• UNION 和 UNION ALL 合并的 列数必须相同，数据类型必须兼容。

• 默认按第一个 SELECT 的列名作为最终输出表头。

• 如果你要排序，必须加在最后一个 SELECT 之后：