10.MySQL数据库之复合查询，精讲，超细节，超重点，必看

目录

基本查询回顾

多表查询

​编辑

自连接

子查询

单行子查询

多行子查询

多列子查询

合并查询

---

 

我们在前面讲解的mysql的查询都是对一张表进行查询，表在实际开发中这远远不够。

基本查询回顾

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

select *from emp where (sal>500 or job="MANAGER") and ename like "J%";

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

select *from emp order by empno asc ,sal desc;

• 使用年薪进行降序排序

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

• 由于NULL与任何值做计算得到的结果都是NULL，因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金，这样可能导致得到的年薪为NULL值。
• 在计算每个员工的年薪时，应该通过ifnull函数判断员工的奖金是否为NULL，如果不为NULL则ifnull函数返回员工的奖金，如果为NULL则ifnull函数返回0，避免让NULL值参与计算

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

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

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

select * from emp where sal>(select avg(sal) from emp);

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

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

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

select deptno,format(avg(sal),0) from emp 
group by deptno 
having avg(sal) <2000;

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

select job,count(*), format(avg(sal),2) from emp group by job;

多表查询

下面给出三张表，分别是员工表（emp）、部门表（dept）和工资等级表（salgrade）。
后续所要进行的查询操作都将以这三张表作为数据源，包括基本查询和复合查询。
员工表（emp）中包含如下字段：

• 雇员编号（empno）。
• 雇员姓名（ename）。
• 雇员职位（job）。
• 雇员领导编号（mgr）。
• 雇佣时间（hiredate）。
• 工资月薪（sal）。
• 奖金（comm）。
• 部门编号（deptno）。

员工表（emp）中的内容如下：

部门表（dept）中包含如下字段：

• 部门编号（deptno）。
• 部门名称（dname）。
• 部门所在地点（loc）。

部门表（dept）中的内容如下：

工资等级表（salgrade）中包含如下字段：

• 等级（grade）。
• 此等级最低工资（losal）。
• 此等级最高工资（hisal）。

工资等级表（salgrade）中的内容如下：

• 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询

1. 从第一张表中选出一条记录，和第二张表的所有记录进行组合。
2. 然后从第一张表中取出第二条记录，和第二张表中所有记录记性组合。
3. 不加过滤条件，得到的结果成为笛卡尔积。

所谓的对多张表取笛卡尔积，就是得到这多张表的记录的所有可能有序对组成的集合，比如下面对员工表和部门表进行多表查询，由于查询语句中没有指明筛选条件，因此最终得到的结果便是员工表和部门表的笛卡尔积。

如果两张表笛卡尔积就会得到下面的结果：

筛选一下

需要注意的是，对多张表取笛卡尔积后得到的数据并不都是有意义的，比如对员工表和部门表取笛卡尔积时，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合，而实际一个员工只有和自己所在的部门信息进行组合才是有意义的，因此需要从笛卡尔积中筛选出员工的部门号和部门的编号相等记录。

emp.deptno= dept.deptno

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

select ename,sal,dname from emp,dept 
where emp.deptno=dept.deptno and dept.deptno=10;

解释一下：

由于部门名部门表中才有，员工名，员工表中才有，我们需要将这两张表连接起来。

通过两张表中公有的部门号连接

• 显示各个员工的姓名，工资，以及工资级别

我们发现sal只在emp中，工资级别在salgrade中，两张表有关联的字段只有sal

那我们可以通过sal进行牵线了

我们先看看两张表具体什么意思

select ename,sal,grade from emp,salgrade

 where emp.sal between losal and hisal;

自连接

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

案例：

• 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

select mgr,empno from emp 
where emp.empno=(select mgr from emp where ename ='FORD');

子查询

子查询是嵌入在其他SQL语句的select语句，也叫嵌套查询。

单行子查询

• 显示SMITH同一部门的员工

多行子查询

返回多行记录的子查询

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

select ename,job,sal,deptno from emp 
where job 
in 
(select distinct job from emp where deptno = 10) 
and 
deptno <>10;

• all关键字；显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号（包含自己部门的员工）

select ename,sal,deptno from emp 
where sal > 
all
(select sal from emp where deptno = 30);

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

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

select ename from emp where (deptno,job)=(select deptno,job from emp where ename ='SMITH') and ename <>"SMITH";

多列子查询

单行子查询是子查询的只返回单列，单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多行子查询则是指查询返回多个列数据的子查询语句。

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

select ename from emp where (deptno,job)=(select deptno,job from emp where ename ='SMITH') and ename <>"SMITH";

在from字句中使用子查询

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

案例：

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

合并查询

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

案例：将工资大于 2500 或职位是 MANAGER 的人找出来

mysql> select ename, sal, job from emp where sal>2500 union
    -> select ename, sal, job from emp where job='MANAGER';