朋友们、伙计们，我们又见面了，本期来给大家带来数据库复合查询知识点，如果看完之后对你有一定的启发，那么请留下你的三连，祝大家心想事成！
C 语 言 专 栏：C语言：从入门到精通
数据结构专栏：数据结构
个 人 主 页 ：stackY、
C + + 专 栏 ：C++
Linux 专 栏 ：Linux
目录
之前学习过的查询都是针对于单表进行查询，这在实际中是远远不够的，所以还需要了解多表的查询以及筛选。
1. 基本查询的回顾
我们还是使用之前的雇员信息表
-- 数据展示 mysql> show tables; +-----------------+ | Tables_in_scott | +-----------------+ | dept | | emp | | salgrade | +-----------------+ 3 rows in set (0.00 sec) -- 部门信息 mysql> select * from dept; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec) -- 员工信息 mysql> select * from emp; +--------+--------+-----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+--------+-----------+------+---------------------+---------+---------+--------+ | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +--------+--------+-----------+------+---------------------+---------+---------+--------+ 14 rows in set (0.00 sec) -- 薪资等级 mysql> select * from salgrade; +-------+-------+-------+ | grade | losal | hisal | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ 5 rows in set (0.01 sec)
① 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写J
-- 使用where子句进行筛选 mysql> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%'; +--------+-------+---------+------+---------------------+---------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+-------+---------+------+---------------------+---------+------+--------+ | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | +--------+-------+---------+------+---------------------+---------+------+--------+ 2 rows in set (0.00 sec)
② 按照部门号升序而雇员的工资降序排序
-- 直接使用order by进行排序 mysql> select * from emp order by deptno, sal desc; +--------+--------+-----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+--------+-----------+------+---------------------+---------+---------+--------+ | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | +--------+--------+-----------+------+---------------------+---------+---------+--------+ 14 rows in set (0.01 sec)
③ 使用年薪进行降序排序
因为任何值和null进行运行都为null，所以不能直接加上comm-- 先计算年薪 mysql> select ename, sal*12+comm 年薪 from emp; +--------+----------+ | ename | 年薪 | +--------+----------+ | SMITH | NULL | | ALLEN | 19500.00 | | WARD | 15500.00 | | JONES | NULL | | MARTIN | 16400.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 18000.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+----------+ 14 rows in set (0.00 sec)当comm为null时我们加上0即可，不需要加上null，所以就要用ifnull()来区别：
mysql> select ename, sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc; +--------+----------+ | ename | 年薪 | +--------+----------+ | KING | 60000.00 | | SCOTT | 36000.00 | | FORD | 36000.00 | | JONES | 35700.00 | | BLAKE | 34200.00 | | CLARK | 29400.00 | | ALLEN | 19500.00 | | TURNER | 18000.00 | | MARTIN | 16400.00 | | MILLER | 15600.00 | | WARD | 15500.00 | | ADAMS | 13200.00 | | JAMES | 11400.00 | | SMITH | 9600.00 | +--------+----------+ 14 rows in set (0.01 sec)
④ 显示工资最高的员工的名字和工作岗位
-- 可以在where子句中继续嵌套select查询 mysql> select ename,job from emp where sal = (select max(sal) from emp); +-------+-----------+ | ename | job | +-------+-----------+ | KING | PRESIDENT | +-------+-----------+ 1 row in set (0.04 sec)
⑤ 显示每种岗位的雇员总数，平均工资
-- 使用count统计人数 mysql> select job,count(*), format(avg(sal),2) 平均工资 from emp group by job; +-----------+----------+--------------+ | job | count(*) | 平均工资 | +-----------+----------+--------------+ | ANALYST | 2 | 3,000.00 | | CLERK | 4 | 1,037.50 | | MANAGER | 3 | 2,758.33 | | PRESIDENT | 1 | 5,000.00 | | SALESMAN | 4 | 1,400.00 | +-----------+----------+--------------+ 5 rows in set (0.02 sec)
2. 多表查询
案例：显示雇员名、雇员工资以及所在部门的名字
因为上面的数据来自两个不同的表，所以需要将两个表联合在一起进行查询
两张表进行联合查询就是将数据进行穷举组合，得到的结果就叫做笛卡尔积。
但是将两张表联合起来之后会发现，里面的数据有好多都是无关紧要的，我们只需要对应的部门编号相同的数据，所以还需要进一步筛选：
-- 两张表进行笛卡尔积再筛选 mysql> select ename,sal,dept.dname from emp,dept where emp.deptno=dept.deptno; +--------+---------+------------+ | ename | sal | dname | +--------+---------+------------+ | SMITH | 800.00 | RESEARCH | | ALLEN | 1600.00 | SALES | | WARD | 1250.00 | SALES | | JONES | 2975.00 | RESEARCH | | MARTIN | 1250.00 | SALES | | BLAKE | 2850.00 | SALES | | CLARK | 2450.00 | ACCOUNTING | | SCOTT | 3000.00 | RESEARCH | | KING | 5000.00 | ACCOUNTING | | TURNER | 1500.00 | SALES | | ADAMS | 1100.00 | RESEARCH | | JAMES | 950.00 | SALES | | FORD | 3000.00 | RESEARCH | | MILLER | 1300.00 | ACCOUNTING | +--------+---------+------------+ 14 rows in set (0.00 sec)
② 显示部门号为10的部门名，员工名和工资
mysql> select ename, sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno = 10;; +--------+---------+------------+ | ename | sal | dname | +--------+---------+------------+ | CLARK | 2450.00 | ACCOUNTING | | KING | 5000.00 | ACCOUNTING | | MILLER | 1300.00 | ACCOUNTING | +--------+---------+------------+ 3 rows in set (0.00 sec)
③ 显示各个员工的姓名，工资，及工资级别
mysql> select ename,sal,grade,losal,hisal from emp,salgrade where emp.sal between losal and hisal; +--------+---------+-------+-------+-------+ | ename | sal | grade | losal | hisal | +--------+---------+-------+-------+-------+ | SMITH | 800.00 | 1 | 700 | 1200 | | ALLEN | 1600.00 | 3 | 1401 | 2000 | | WARD | 1250.00 | 2 | 1201 | 1400 | | JONES | 2975.00 | 4 | 2001 | 3000 | | MARTIN | 1250.00 | 2 | 1201 | 1400 | | BLAKE | 2850.00 | 4 | 2001 | 3000 | | CLARK | 2450.00 | 4 | 2001 | 3000 | | SCOTT | 3000.00 | 4 | 2001 | 3000 | | KING | 5000.00 | 5 | 3001 | 9999 | | TURNER | 1500.00 | 3 | 1401 | 2000 | | ADAMS | 1100.00 | 1 | 700 | 1200 | | JAMES | 950.00 | 1 | 700 | 1200 | | FORD | 3000.00 | 4 | 2001 | 3000 | | MILLER | 1300.00 | 2 | 1201 | 1400 | +--------+---------+-------+-------+-------+ 14 rows in set (0.00 sec)
3. 自连接
自连接是指在同一张表连接查询（同一张表进行笛卡尔积）
案例：显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）
如果直接使用自连接是无法成功的，必须先给表取别名，再进行自连接；
-- 给自己的表起别名，因为要先做笛卡尔积，所以别名可以先识别 mysql> select * from emp leader,emp worker;再通过where子句进行筛选：
mysql> select leader.empno,leader.ename from emp leader, emp worker where leader.empno=worker.mgr and worker.ename = 'FORD'; +--------+-------+ | empno | ename | +--------+-------+ | 007566 | JONES | +--------+-------+ 1 row in set (0.00 sec)
4. 子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询；
例如前面我们嵌套在where子句中的select语句。
4.1 单行子查询
返回一行记录的子查询
示例：显示于SMITH同一部门的员工-- 单行子查询、嵌套where子句中 mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH') ; +--------+-------+---------+------+---------------------+---------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+-------+---------+------+---------------------+---------+------+--------+ | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | +--------+-------+---------+------+---------------------+---------+------+--------+ 5 rows in set (0.00 sec)
4.2 多行子查询
返回多行记录的子查询
4.2.1 in关键字某一列是否在一个集合中；
例如：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
-- 使用in关键字先查询全部 ------------------------------------------ job是否在()中的岗位 mysql> select ename,job,sal,deptno from emp where job in (select job from emp where deptno = 10); +--------+-----------+---------+--------+ | ename | job | sal | deptno | +--------+-----------+---------+--------+ | JONES | MANAGER | 2975.00 | 20 | | BLAKE | MANAGER | 2850.00 | 30 | | CLARK | MANAGER | 2450.00 | 10 | | KING | PRESIDENT | 5000.00 | 10 | | SMITH | CLERK | 800.00 | 20 | | ADAMS | CLERK | 1100.00 | 20 | | JAMES | CLERK | 950.00 | 30 | | MILLER | CLERK | 1300.00 | 10 | +--------+-----------+---------+--------+ 8 rows in set (0.00 sec) -- 再次筛选不包含10号部门的 mysql> select ename,job,sal,deptno from emp where job in (select job from emp where deptno = 10) and deptno <> 10; +-------+---------+---------+--------+ | ename | job | sal | deptno | +-------+---------+---------+--------+ | JONES | MANAGER | 2975.00 | 20 | | BLAKE | MANAGER | 2850.00 | 30 | | SMITH | CLERK | 800.00 | 20 | | ADAMS | CLERK | 1100.00 | 20 | | JAMES | CLERK | 950.00 | 30 | +-------+---------+---------+--------+ 5 rows in set (0.00 sec)4.2.2 all关键字
将某一列于某个集合中所有元素比较（满足或不满足）；
示例：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
-- 查询emp表中只要大于30部门所有人工资即可被筛选 mysql> select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30); +-------+---------+--------+ | ename | sal | deptno | +-------+---------+--------+ | JONES | 2975.00 | 20 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | FORD | 3000.00 | 20 | +-------+---------+--------+ 4 rows in set (0.00 sec)4.2.3 any关键字
将某一列与集合中任意一列比较；
示例：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门
的员工）-- 将sal与集合中任意一组数据比较 mysql> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30); +--------+---------+--------+ | ename | sal | deptno | +--------+---------+--------+ | ALLEN | 1600.00 | 30 | | WARD | 1250.00 | 30 | | JONES | 2975.00 | 20 | | MARTIN | 1250.00 | 30 | | BLAKE | 2850.00 | 30 | | CLARK | 2450.00 | 10 | | SCOTT | 3000.00 | 20 | | KING | 5000.00 | 10 | | TURNER | 1500.00 | 30 | | ADAMS | 1100.00 | 20 | | FORD | 3000.00 | 20 | | MILLER | 1300.00 | 10 | +--------+---------+--------+ 12 rows in set (0.00 sec)
4.3 多列子查询
单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
例如：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人-- 使用（）将所要查询的多列放进去 mysql> select * from emp where (deptno,job) = (select deptno,job from emp where ename = 'SMITH') and ename <> 'SMITH'; +--------+-------+-------+------+---------------------+---------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+-------+-------+------+---------------------+---------+------+--------+ | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | +--------+-------+-------+------+---------------------+---------+------+--------+ 1 row in set (0.00 sec)
4.4 在form中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。
案例：① 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
-- 获取各个部门的平均工资，将其看作临时表 mysql> select ename, deptno, sal, format(asal,2) from emp, (select avg(sal) asal, deptno dt from emp group by deptno) tmp where emp.sal > tmp.asal and emp.deptno=tmp.dt; +-------+--------+---------+----------------+ | ename | deptno | sal | format(asal,2) | +-------+--------+---------+----------------+ | KING | 10 | 5000.00 | 2,916.67 | | JONES | 20 | 2975.00 | 2,175.00 | | SCOTT | 20 | 3000.00 | 2,175.00 | | FORD | 20 | 3000.00 | 2,175.00 | | ALLEN | 30 | 1600.00 | 1,566.67 | | BLAKE | 30 | 2850.00 | 1,566.67 | +-------+--------+---------+----------------+ 6 rows in set (0.00 sec)② 查找每个部门工资最高的人的姓名、工资、部门、最高工资
mysql> select emp.ename, emp.sal, emp.deptno, ms from emp, (select max(sal) ms, deptno from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal=tmp.ms; +-------+---------+--------+---------+ | ename | sal | deptno | ms | +-------+---------+--------+---------+ | BLAKE | 2850.00 | 30 | 2850.00 | | SCOTT | 3000.00 | 20 | 3000.00 | | KING | 5000.00 | 10 | 5000.00 | | FORD | 3000.00 | 20 | 3000.00 | +-------+---------+--------+---------+ 4 rows in set (0.00 sec)③ 显示每个部门的信息（部门名，编号，地址）和人员数量
-- 对emp表进行人员统计 mysql> select count(*), deptno from emp group by deptno; +----------+--------+ | count(*) | deptno | +----------+--------+ | 3 | 10 | | 5 | 20 | | 6 | 30 | +----------+--------+ 3 rows in set (0.00 sec) -- 将上面的表看做临时表 mysql> select dept.deptno, dname, mycnt, loc from dept, (select count(*) mycnt, deptno from emp group by deptno) tmp where dept.deptno=tmp.deptno; +--------+------------+-------+----------+ | deptno | dname | mycnt | loc | +--------+------------+-------+----------+ | 10 | ACCOUNTING | 3 | NEW YORK | | 20 | RESEARCH | 5 | DALLAS | | 30 | SALES | 6 | CHICAGO | +--------+------------+-------+----------+ 3 rows in set (0.00 sec)
5. 合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all
5.1 union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
示例：将工资大于2500或职位是MANAGER的人找出来mysql> select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER'; -- 去掉了重复记录 +-------+---------+-----------+ | ename | sal | job | +-------+---------+-----------+ | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | CLARK | 2450.00 | MANAGER | +-------+---------+-----------+ 6 rows in set (0.00 sec)5.2 union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：将工资大于2500或职位是MANAGER的人找出来
mysql> select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER'; +-------+---------+-----------+ | ename | sal | job | +-------+---------+-----------+ | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | +-------+---------+-----------+ 8 rows in set (0.00 sec)
朋友们、伙计们，美好的时光总是短暂的，我们本期的的分享就到此结束，欲知后事如何，请听下回分解~，最后看完别忘了留下你们弥足珍贵的三连喔，感谢大家的支持！
扫一扫
3327
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
