自学内容网 自学内容网
自学内容网 > 正文

【MySQL】复合查询

🕗 发布于 2024-10-10 07:04 mysql  数据库  

目录

前言

前面介绍的都是针对单张表进行查询，但在实际开发中是远远不够的，很多情况下需要进行多表查询

1. 基本查询回顾

示例表如下：

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)

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

mysql> select * 
    -> from emp
    -> where (sal > 500 or job = ucase('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.01 sec)

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

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

mysql> select * 
    -> from emp
    -> order by deptno asc, 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.00 sec)

使用年薪进行降序排序：

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.02 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 deeptno;
+--------+--------------+--------------+
| deptno | 平均工资     | 最高工资     |
+--------+--------------+--------------+
|     20 |  2175.000000 |      3000.00 |
|     30 |  1566.666667 |      2850.00 |
|     10 |  2916.666667 |      5000.00 |
+--------+--------------+--------------+
3 rows in set (0.01 sec)

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

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

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

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

2. 多表查询

实际开发中往往数据来自不同的表，所以需要多表查询，通过如下三张表来演示多表查询：

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)

2.1 笛卡尔积

显示雇员名、雇员工资以及所在部门的名字：
在这里插入图片描述
前两个属性在emp表，后一个在dept表，所以from要连接两个表，但是输出的结果貌似有些问题，emp表中的每条记录都与dept表中的记录进行了组合，这种不带过滤条件的，把前一个表中的所有记录依次与后一个表中的所有记录进行组合得到的结果叫做笛卡尔积

也就是两个表中的记录做乘法，假设两个表分别有m和n条记录，经过笛卡尔积之后得到的这个表的记录条数为m * n

可以把多表合并的一个笛卡尔积表当作成一个新表，然后对这个新表进行单表查询

2.2 查询

因此若要满足要求需要带上条件：

mysql> select ename, sal, 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 dname, ename, sal
    -> from dept, emp
    -> where emp.deptno = dept.deptno 
    -> and emp.deptno = 10;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| ACCOUNTING | CLARK  | 2450.00 |
| ACCOUNTING | KING   | 5000.00 |
| ACCOUNTING | MILLER | 1300.00 |
+------------+--------+---------+
3 rows in set (0.00 sec)

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

mysql> select ename, sal, grade 
-> from emp, salgrade 
-> where 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)

3. 自连接

多张表可以进行笛卡尔积运算，而对于同一张表也是可以进行笛卡尔积的，所以称之为表的自连接

需要在from子句中给同一张表进行重命名才可以进行笛卡尔积：

mysql> select * from salgrade, salgrade;
ERROR 1066 (42000): Not unique table/alias: 'salgrade'

mysql> select * from salgrade t1, salgrade t2;
+-------+-------+-------+-------+-------+-------+
| grade | losal | hisal | grade | losal | hisal |
+-------+-------+-------+-------+-------+-------+
|     5 |  3001 |  9999 |     1 |   700 |  1200 |
|     4 |  2001 |  3000 |     1 |   700 |  1200 |
|     3 |  1401 |  2000 |     1 |   700 |  1200 |
|     2 |  1201 |  1400 |     1 |   700 |  1200 |
|     1 |   700 |  1200 |     1 |   700 |  1200 |
|     5 |  3001 |  9999 |     2 |  1201 |  1400 |
|     4 |  2001 |  3000 |     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |     2 |  1201 |  1400 |
|     2 |  1201 |  1400 |     2 |  1201 |  1400 |
|     1 |   700 |  1200 |     2 |  1201 |  1400 |
|     5 |  3001 |  9999 |     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |     3 |  1401 |  2000 |
|     3 |  1401 |  2000 |     3 |  1401 |  2000 |
|     2 |  1201 |  1400 |     3 |  1401 |  2000 |
|     1 |   700 |  1200 |     3 |  1401 |  2000 |
|     5 |  3001 |  9999 |     4 |  2001 |  3000 |
|     4 |  2001 |  3000 |     4 |  2001 |  3000 |
|     3 |  1401 |  2000 |     4 |  2001 |  3000 |
|     2 |  1201 |  1400 |     4 |  2001 |  3000 |
|     1 |   700 |  1200 |     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |     5 |  3001 |  9999 |
|     4 |  2001 |  3000 |     5 |  3001 |  9999 |
|     3 |  1401 |  2000 |     5 |  3001 |  9999 |
|     2 |  1201 |  1400 |     5 |  3001 |  9999 |
|     1 |   700 |  1200 |     5 |  3001 |  9999 |
+-------+-------+-------+-------+-------+-------+
25 rows in set (0.00 sec)

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno），第一种方式是使用子查询：

mysql> select empno, ename 
-> from emp 
-> where empno = 
-> (select mgr from emp where ename = 'ford');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec

第二种方式就是使用自连接：

mysql> select t2.empno, t2.ename
    -> from emp t1, emp t2
    -> where t1.ename = 'ford' and t1.mgr = t2.empno;
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

当前表中的某列字段的值可能是其它列的值，这时可以使用自连接查询

4. 子查询

子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询，也就是根据子查询的结果再在主查询中继续查询

4.1 单行子查询

即返回单行单列记录的子查询

显示SMITH同一部门的员工：

mysql> select ename 
    -> from emp
    -> where deptno = 
    -> (select deptno from emp where ename = 'SMITH');
+-------+
| ename |
+-------+
| SMITH |
| JONES |
| SCOTT |
| ADAMS |
| FORD  |
+-------+
5 rows in set (0.00 sec)

4.2 多行子查询

即返回多行一列记录的子查询

4.2.1 in关键字

查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含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 |
+-------+---------+---------+--------+
| 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)

由于子查询的结果是多行的，所以要用in关键字，判断job列的值是否在这个子查询筛选的集合中，只要在就筛选

4.2.2 all关键字

显示工资比部门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)

all关键字的作用是与查询出的所有结果进行一一比较，这里的其实就只要比较这组值中的最大值即可，因此使用max(sal)也可以

4.2.3 any关键字

显示工资比部门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)

4.3 多列子查询

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

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

mysql> select *  
-> from emp 
-> where job = 
-> (select job from emp where ename = 'smith') 
-> and deptno = 
-> (select deptno 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)

合并写法：

mysql> select * 
-> from emp 
-> where (deptno, job) = 
-> (select deptno, job from emp where ename = 'smith') 
-> and ename != 'smith';

也可以把=换成in，虽然是多列，但实际上可以把多列组合成一个值，然后这样就变成单列了，可以使用in判断，当前组合值是否在这个组合值集合中

4.4 from子句中使用子查询

到目前为止，所有的子查询都是在where子句中充当判断条件的，除此之外，from后面也可以进行子查询，把子查询得到的结果看作一张新表，然后对这个新表进行进一步操作，实际上充当的是笛卡尔积表

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

mysql> select ename, deptno, sal, format(t2.asal, 2) 
-> from emp, 
(select avg(sal) asal, deptno dt from emp group by deptno) t2 
-> where sal > asal and deptno = dt;
+-------+--------+---------+--------------------+
| ename | deptno | sal     | format(t2.asal, 2) |   
+-------+--------+---------+--------------------+
| FORD  |     20 | 3000.00 | 2,175.00           |
| SCOTT |     20 | 3000.00 | 2,175.00           |
| JONES |     20 | 2975.00 | 2,175.00           |
| BLAKE |     30 | 2850.00 | 1,566.67           |
| ALLEN |     30 | 1600.00 | 1,566.67           |
| KING  |     10 | 5000.00 | 2,916.67           |
+-------+--------+---------+--------------------+
6 rows in set (0.00 sec)

需要对查询出的逻辑表进行重命名，否则报错

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

mysql> select ename, sal, deptno, max_sal 
-> from emp, (select max(sal) max_sal, deptno dt from emp group by deptno) tmp 
-> where emp.sal = max_sal and emp.deptno = tmp.dt;
+-------+---------+--------+---------+
| ename | sal     | deptno | max_sal |
+-------+---------+--------+---------+
| 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.deptno, dept.dname, dept.loc, 人数 
-> from dept, (select deptno
dt, count(*) 人数 from emp group by dt) tmp 
-> where dept.deptno = dt;
+--------+------------+----------+--------+
| deptno | dname      | loc      | 人数   |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |      3 |
|     20 | RESEARCH   | DALLAS   |      5 |
|     30 | SALES      | CHICAGO  |      6 |
+--------+------------+----------+--------+
3 rows in set (0.00 sec)

4.5 合并查询

为了合并多个select的执行结果，可以使用集合操作符 union，union all，也就是把多个select语句的结果拼接起来

4.5.1 union与union all

union操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行，而另一个则是不去掉重复记录

语法如下：

select... union [all] select...

5. 总结

在解决实际问题时，想办法把多表合并成一张表，然后对这一张表进行操作，所以在MySQL中，所有的select问题都可以转换成单表问题


原文地址：https://blog.csdn.net/weixin_66672501/article/details/142643504

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024