MySQL复合查询

文章目录

一、前情回顾
二、多表查询
三、自连接
四、子查询
- 1.单行子查询
- 2.多行子查询
- 3.多列子查询
- 4.在from子句中使用子查询
五、合并查询

在前面表的基础上进行操作：

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

mysql> select * from emp where (sal > 500 || 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, 1 warning (0.00 sec)

或者

mysql> select * from emp where (sal > 500 || job = 'MANAGER') and substring(ename, 1, 1) = '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, 1 warning (0.00 sec)

按照部门号升序排序，当部门号相同时按雇员的工资降序排序

mysql> select ename, sal+——–+———+——–+
| ename | sal | deptno +——–+———+——–+
| KING | 5000.00 | | CLARK | 2450.00 | | MILLER | 1300.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | ADAMS | 1100.00 | | SMITH | 800.00 | | BLAKE | 2850.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | JAMES | 950.00 | +——–+———+——–+
14 rows in set punctuation">, deptno from emp order by deptno, sal desc;
/> operator">|
/> 10 |
10 |
10 |
20 |
20 |
20 |
20 |
20 |
30 |
30 |
30 |
30 |
30 |
30 |
/> (0.00 sec)

使用年薪进行降序排序

注意：NULL与任意值相加都为NULL

mysql> select ename, sal, comm, sal*12+ifnull(comm,0) 年薪 from emp order by sal*12+ifnull(comm,0) desc;
+——–+———+———+———-+
| ename | sal | comm | 年薪 |
+——–+———+———+———-+
| KING | 5000.00 | NULL | 60000.00 |
| SCOTT | 3000.00 | NULL | 36000.00 |
| FORD | 3000.00 | NULL | 36000.00 |
| JONES | 2975.00 | NULL | 35700.00 |
| BLAKE | 2850.00 | NULL | 34200.00 |
| CLARK | 2450.00 | NULL | 29400.00 |
| ALLEN | 1600.00 | 300.00 | 19500.00 |
| TURNER | 1500.00 | 0.00 | 18000.00 |
| MARTIN | 1250.00 | 1400.00 | 16400.00 |
| MILLER | 1300.00 | NULL | 15600.00 |
| WARD | 1250.00 | 500.00 | 15500.00 |
| ADAMS | 1100.00 | NULL | 13200.00 |
| JAMES | 950.00 | NULL | 11400.00 |
| SMITH | 800.00 | NULL | 9600.00 |
+——–+———+———+———-+
14 rows in set (0.00 sec)

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

两条语句：

mysql> select max(sal) from emp;
+———-+
| max(sal) |
+———-+
| 5000.00 |
+———-+
1 row in set (0.00 sec)

mysql> select * from emp where sal = 5000;
+——–+——-+———–+——+———————+———+——+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——–+——-+———–+——+———————+———+——+——–+
| 007839 | KING | PRESIDENT | NULL | 1981–11–17 00:00:00 | 5000.00 | NULL | 10 |
+——–+——-+———–+——+———————+———+——+——–+
1 row in set (0.00 sec)

一条语句：执行时，先执行（）内的子查询

mysql> select * from emp where sal = (select max(sal) from emp);
+——–+——-+———–+——+———————+———+——+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——–+——-+———–+——+———————+———+——+——–+
| 007839 | KING | PRESIDENT | NULL | 1981–11–17 00:00:00 | 5000.00 | NULL | 10 |
+——–+——-+———–+——+———————+———+——+——–+
1 row in set (0.00 sec)

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

mysql> select * from emp where sal > (select avg(sal) from emp);
+——–+——-+———–+——+———————+———+——+——–+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+——–+——-+———–+——+———————+———+——+——–+
| 007566 | JONES | MANAGER | 7839 | 1981–04–02 00:00:00 | 2975.00 | NULL | 20 |
| 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 |
| 007902 | FORD | ANALYST | 7566 | 1981–12–03 00:00:00 | 3000.00 | NULL | 20 |
+——–+——-+———–+——+———————+———+——+——–+
6 rows in set (0.00 sec)

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

mysql> select deptno, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno;
+——–+————–+————–+
| deptno | 平均工资 | 最高工资 |
+——–+————–+————–+
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
| 10 | 2916.666667 | 5000.00 |
+——–+————–+————–+
3 rows in set (0.00 sec)

更美观一些：

mysql> select deptno, format(avg(sal), 2) 平均工资, max(sal) 最高工资 from emp group by deptno;
+——–+————–+————–+
| deptno | 平均工资 | 最高工资 |
+——–+————–+————–+
| 20 | 2,175.00 | 3000.00 |
| 30 | 1,566.67 | 2850.00 |
| 10 | 2,916.67 | 5000.00 |
+——–+————–+————–+
3 rows in set (0.00 sec)

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

mysql> select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资 < 2000;
+——–+————–+
| deptno | 平均工资 |
+——–+————–+
| 30 | 1566.666667 |
+——–+————–+
1 row in set (0.00 sec)

为什么格式化了之后不对呢？

mysql> select deptno, format(avg(sal), 2) 平均工资 from emp group by deptno having 平均工资 < 2000;
+——–+————–+
| deptno | 平均工资 |
+——–+————–+
| 20 | 2,175.00 |
| 30 | 1,566.67 |
| 10 | 2,916.67 |
+——–+————–+
3 rows in set, 3 warnings (0.00 sec)

因为：由于使用了FORMAT函数，平均工资被格式化为带有逗号的字符串（例如2,175.00）。在HAVING子句中，将这个字符串与数字2000比较时，MySQL会尝试将字符串转换为数字。但是，由于字符串中包含逗号，转换可能会失败，实际上MySQL会截取字符串中直到第一个非数字字符（逗号）之前的数字部分，所以"2,175.00"会被转换为2，因此条件“平均工资 < 2000”对于所有行都成立

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

mysql> select job, format(avg(sal), 2), count(*) 员工人数 from emp group by job;
+———–+———————+————–+
| job | format(avg(sal), 2) | 员工人数 |
+———–+———————+————–+
| CLERK | 1,037.50 | 4 |
| SALESMAN | 1,400.00 | 4 |
| MANAGER | 2,758.33 | 3 |
| ANALYST | 3,000.00 | 2 |
| PRESIDENT | 5,000.00 | 1 |
+———–+———————+————–+
5 rows in set (0.00 sec)

二、多表查询

当数据来自不同的表时，我们需要用到多表查询

显示雇员名、雇员工资以及所在部门的名字

因为上面的数据来自EMP和DEPT表，因此要多表查询

在这里插入图片描述

多表查询的本质是两个表之间将数据进行穷举组合即笛卡尔积，即emp表中的第一条信息分别与dept表中的每一条信息进行整合，然后emp表中第二条信息分别与dept表中的每一条信息进行整合…以此类推

注意: 笛卡尔积之后的结果有一些数据是无效的，因此我们需要加上过滤条件

在这里插入图片描述

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

mysql> select emp.deptno, ename, sal, dname from emp, dept where emp.deptno = dept.deptno && emp.deptno = 10;
+——–+——–+———+————+
| deptno | ename | sal | dname |
+——–+——–+———+————+
| 10 | CLARK | 2450.00 | ACCOUNTING |
| 10 | KING | 5000.00 | ACCOUNTING |
| 10 | MILLER | 1300.00 | ACCOUNTING |
+——–+——–+———+————+
3 rows in set, 1 warning (0.00 sec)

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

需要将emp表与salgrade表结合

mysql> select ename, sal, grade from emp, salgrade where emp.sal between losal and hisal;
+——–+———+——-+
| ename | sal | grade |
+——–+———+——-+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+——–+———+——-+
14 rows in set (0.00 sec)

三、自连接

自连接是将同一张表做笛卡尔积，此时至少给一张表起别名

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

使用子查询：

使用自连接：

四、子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

1.单行子查询

显示SMITH同一部门的员工

2.多行子查询

返回多行记录的子查询

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

mysql> select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;
+——-+———+———+——–+
| ename | job | sal | deptno |
+——-+———+———+——–+
| SMITH | CLERK | 800.00 | 20 |
| JONES | MANAGER | 2975.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| ADAMS | CLERK | 1100.00 | 20 |
| JAMES | CLERK | 950.00 | 30 |
+——-+———+———+——–+
5 rows in set (0.00 sec)

all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号即比部门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)

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

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)

3.多列子查询

单行子查询是指子查询只返回单列，单行数据；

多行子查询是指返回单列多行数据，都是针对单列而言的;

多列子查询是指查询返回多个列数据的子查询语句;

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

mysql> select ename from emp where (deptno, job)=(select deptno, job from emp where ename='SMITH') and ename <> 'SMITH';
+——-+
| ename |
+——-+
| ADAMS |
+——-+
1 row in set (0.00 sec)

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

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

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

mysql> select ename, deptno, sal, format(sal,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(sal,2) |
+——-+——–+———+—————+
| FORD | 20 | 3000.00 | 3,000.00 |
| SCOTT | 20 | 3000.00 | 3,000.00 |
| JONES | 20 | 2975.00 | 2,975.00 |
| BLAKE | 30 | 2850.00 | 2,850.00 |
| ALLEN | 30 | 1600.00 | 1,600.00 |
| KING | 10 | 5000.00 | 5,000.00 |
+——-+——–+———+—————+
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)

显示每个部门的信息（部门名，编号，地址）和人员数量使用多表：

mysql> select dept.dname, dept.deptno, dept.loc, count(*) 部门人数 from emp, dept
–> where emp.deptno=dept.deptno group by dept.deptno, dept.dname, dept.loc;
+————+——–+———-+————–+
| dname | deptno | loc | 部门人数 |
+————+——–+———-+————–+
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
| ACCOUNTING | 10 | NEW YORK | 3 |
+————+——–+———-+————–+
3 rows in set (0.00 sec)

使用子查询：

#1.对emp表进行人员统计
mysql> select count(*), deptno from emp group by deptno ;
+———-+——–+
| count(*) | deptno |
+———-+——–+
| 5 | 20 |
| 6 | 30 |
| 3 | 10 |
+———-+——–+
3 rows in set (0.00 sec)

#2.将上面的表看作临时表
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)

五、合并查询

为了合并多个select的执行结果，可以使用集合操作符union，union all

union操作符; 将工资大于2500或职位是MANAGER的人找出来该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

union all操作符; 将工资大于25000或职位是MANAGER的人找出来该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

文章目录

一、前情回顾

二、多表查询

三、自连接

四、子查询

1.单行子查询

2.多行子查询

3.多列子查询

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

五、合并查询

相关推荐

评论抢沙发

评论前必须登录！

热门标签

置顶推荐

热门文章

最新文章

文章目录

一、前情回顾

二、多表查询

三、自连接

四、子查询

1.单行子查询

2.多行子查询

3.多列子查询

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

五、合并查询

相关推荐

评论 抢沙发

评论前必须登录！

热门标签

置顶推荐

热门文章

最新文章

评论抢沙发