MySQL复合查询的具体使用_Mysql

1. 复合查询

前面所介绍的查询都是对 mysql 一张表进行查询，但在实际应用中，我们往往需要从多张表中获取数据，这时就需要使用复合查询

1.1 基本查询回顾

查询工资高于 500 或岗位信息为 “manager” 的雇员，同时还要满足他们的姓名首字母为大写的 “j”

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>

按照部门号升序，雇员工资降序排序整张表

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>

使用年薪进行降序排序

mysql> select *,sal*12+ifnull(comm,0) as annual_sal from emp order by annual_sal desc;
+--------+--------+-----------+------+---------------------+---------+---------+--------+------------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | annual_sal |
+--------+--------+-----------+------+---------------------+---------+---------+--------+------------+
| 007839 | king   | president | null | 1981-11-17 00:00:00 | 5000.00 |    null |     10 |   60000.00 |
| 007788 | scott  | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    null |     20 |   36000.00 |
| 007902 | ford   | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    null |     20 |   36000.00 |
| 007566 | jones  | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    null |     20 |   35700.00 |
| 007698 | blake  | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    null |     30 |   34200.00 |
| 007782 | clark  | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    null |     10 |   29400.00 |
| 007499 | allen  | salesman  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |   19500.00 |
| 007844 | turner | salesman  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |   18000.00 |
| 007654 | martin | salesman  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |   16400.00 |
| 007934 | miller | clerk     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    null |     10 |   15600.00 |
| 007521 | ward   | salesman  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |   15500.00 |
| 007876 | adams  | clerk     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    null |     20 |   13200.00 |
| 007900 | james  | clerk     | 7698 | 1981-12-03 00:00:00 |  950.00 |    null |     30 |   11400.00 |
| 007369 | smith  | clerk     | 7902 | 1980-12-17 00:00:00 |  800.00 |    null |     20 |    9600.00 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+------------+
14 rows in set (0.00 sec)

mysql>

注：comm 列表示佣金，全称 commission

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

mysql> select ename,job from emp where sal = (select max(sal) from emp);
+-------+-----------+
| ename | job       |
+-------+-----------+
| king  | president |
+-------+-----------+
1 row in set (0.00 sec)

mysql>

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

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>

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

mysql> select deptno,avg(sal) as avg_sal,max(sal) as max_sal from emp group by deptno;
+--------+-------------+---------+
| deptno | avg_sal     | max_sal |
+--------+-------------+---------+
|     20 | 2175.000000 | 3000.00 |
|     30 | 1566.666667 | 2850.00 |
|     10 | 2916.666667 | 5000.00 |
+--------+-------------+---------+
3 rows in set (0.00 sec)

mysql>

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

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

mysql>

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

mysql> select job,count(*) as total,avg(sal) as avg_sal from emp group by job;
+-----------+-------+-------------+
| job       | total | avg_sal     |
+-----------+-------+-------------+
| clerk     |     4 | 1037.500000 |
| salesman  |     4 | 1400.000000 |
| manager   |     3 | 2758.333333 |
| analyst   |     2 | 3000.000000 |
| president |     1 | 5000.000000 |
+-----------+-------+-------------+
5 rows in set (0.00 sec)

mysql>

1.2 多表查询

在进行多表查询的时候，其实本质上和单表查询是一样的，以下图中的 emp 和 dept 为例：

从第一张表中选出第一条记录，和第二张表中的所有记录进行组合
从第一张表中选取第二条记录，和第二张表中的所有记录进行组合
重复上述过程，直到第一张表中的所有记录都和第二张表的所有记录组合完毕
得到最终的组合记录表，现在就已经将两张表连接起来了
前面我们说过，“一切皆表” 的思维模式，在多表查询这里也同样适用，将多张表连接起来，看作是一张表
如果有过滤条件，按照条件对组合后的表进行筛选
显示最终结果

这个组合的过程和数学上的笛卡尔积类似

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

很明显，雇员名和雇员工资在 emp 表中，部门名字在 dept 表中，需要通过连接查询来实现

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 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)

mysql>

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

mysql> select dept.dname,ename,sal from emp,dept 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>

显示各个雇员的姓名、工资和工资级别，并按照级别升序排序

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)

mysql> select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal order by grade;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| smith  |  800.00 |     1 |
| adams  | 1100.00 |     1 |
| james  |  950.00 |     1 |
| ward   | 1250.00 |     2 |
| martin | 1250.00 |     2 |
| miller | 1300.00 |     2 |
| allen  | 1600.00 |     3 |
| turner | 1500.00 |     3 |
| jones  | 2975.00 |     4 |
| blake  | 2850.00 |     4 |
| clark  | 2450.00 |     4 |
| scott  | 3000.00 |     4 |
| ford   | 3000.00 |     4 |
| king   | 5000.00 |     5 |
+--------+---------+-------+
14 rows in set (0.00 sec)

mysql>

注：

在将多个表连接的时候，一定要有连接条件，就是两张表之间的关联字段
如果两张表都有的字段，要以表名.字段名的方式明确指定，避免歧义

1.3 自连接

自连接是指一张表与其自身进行连接查询

案例：

显示员工 “ford” 的上级领导编号和姓名

使用子查询

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

mysql>

使用多表查询(自查询)

mysql> select leader.ename,leader.deptno from emp leader,emp worker where leader.empno = worker.mgr and worker.ename = 'ford';
+-------+--------+
| ename | deptno |
+-------+--------+
| jones |     20 |
+-------+--------+
1 row in set (0.00 sec)

mysql>

在上面的示例中使用自查询的时候，需要注意对表起别名，因为要区分两张表

mgr 是雇员的上级领导编号
empno 是雇员编号

1.4 子查询

子查询是指在另一个查询语句的 where 子句中出现的查询语句，也叫做嵌套查询(前面我们已经用过)

1.4.1 单行子查询

返回一行记录的子查询

显示 “smith” 同一部门的雇员

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)

mysql>

1.4.2 多行子查询

返回多行记录的子查询

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

mysql> select ename,job,sal,deptno from emp where deptno != 10 and job in (select job from emp where 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)

mysql>

显示工资比部门 30 的所有员工的工资高的雇员姓名、工资和部门号，不包含 30 号部门的员工

mysql> select ename,sal,deptno from emp where deptno != 30 and 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)

mysql>

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

mysql>

1.4.3 多列子查询

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

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

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

mysql>

1.4.4 在 from 子句中使用子查询

子查询语句出现在 from 子句中，这时候子查询的结果会被当作一个临时表来使用，再次体现了 “一切皆表” 的思维模式

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

mysql> select ename,deptno,sal,dept_avg_sal from emp,
    -> (select deptno my_deptno,avg(sal) dept_avg_sal from emp group by deptno) tmp
    -> where emp.sal > tmp.dept_avg_sal and emp.deptno = tmp.my_deptno;
+-------+--------+---------+--------------+
| ename | deptno | sal     | dept_avg_sal |
+-------+--------+---------+--------------+
| allen |     30 | 1600.00 |  1566.666667 |
| jones |     20 | 2975.00 |  2175.000000 |
| blake |     30 | 2850.00 |  1566.666667 |
| scott |     20 | 3000.00 |  2175.000000 |
| king  |     10 | 5000.00 |  2916.666667 |
| ford  |     20 | 3000.00 |  2175.000000 |
+-------+--------+---------+--------------+
6 rows in set (0.00 sec)

mysql> select t1emp.ename,t1emp.deptno,t1emp.sal,t2emp.dept_avg_sal from
    -> (select ename,deptno,sal from emp) t1emp,
    -> (select deptno,avg(sal) dept_avg_sal from emp group by deptno) t2emp
    -> where t1emp.sal > t2emp.dept_avg_sal and t1emp.deptno = t2emp.deptno;
+-------+--------+---------+--------------+
| ename | deptno | sal     | dept_avg_sal |
+-------+--------+---------+--------------+
| allen |     30 | 1600.00 |  1566.666667 |
| jones |     20 | 2975.00 |  2175.000000 |
| blake |     30 | 2850.00 |  1566.666667 |
| scott |     20 | 3000.00 |  2175.000000 |
| king  |     10 | 5000.00 |  2916.666667 |
| ford  |     20 | 3000.00 |  2175.000000 |
+-------+--------+---------+--------------+
6 rows in set (0.00 sec)

mysql>

查找每个部门工资最高的雇员姓名、工资、部门和最高工资

mysql> select ename,sal,deptno,dept_max_sal from emp,
    -> (select max(sal) dept_max_sal,deptno tdeptno from emp group by deptno) tmp
    -> where sal = dept_max_sal and deptno = tdeptno;
+-------+---------+--------+--------------+
| ename | sal     | deptno | dept_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>

显示每个部门的信息(部门名、编号和地址)和人员数量

mysql> select dname,dept.deptno,loc,tmp.total from dept, (select count(*) total,deptno from emp group by deptno) tmp where dept.deptno = tmp.deptno;

+------------+--------+----------+-------+
| dname      | deptno | loc      | total |
+------------+--------+----------+-------+
| accounting |     10 | new york |     3 |
| research   |     20 | dallas   |     5 |
| sales      |     30 | chicago  |     6 |
+------------+--------+----------+-------+
3 rows in set (0.01 sec)

mysql>

1.4.5 合并查询

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

1.4.5.1 union

union 可以获得两个结果集的并集，会自动去掉结果集中的重复行

查询工资大于 2500 或职位是 “manager” 的雇员

mysql> select * from emp where sal > 2500 union select * from emp where job = 'manager';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 | null |     20 |
|  7698 | blake | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 | null |     30 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 | null |     20 |
|  7839 | king  | president | null | 1981-11-17 00:00:00 | 5000.00 | null |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 | null |     20 |
|  7782 | clark | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 | null |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

mysql>

1.4.5.2 union all

union all 可以获取两个结果集的并集，不会去掉重复行

查询工资大于 2500 或职位是 “manager” 的雇员

mysql> select * from emp where sal > 2500 union all select * from emp where job = 'manager';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empno | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | jones | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 | null |     20 |
|  7698 | blake | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 | null |     30 |
|  7788 | scott | analyst   | 7566 | 1987-04-19 00:00:00 | 3000.00 | null |     20 |
|  7839 | king  | president | null | 1981-11-17 00:00:00 | 5000.00 | null |     10 |
|  7902 | ford  | analyst   | 7566 | 1981-12-03 00:00:00 | 3000.00 | null |     20 |
|  7566 | jones | manager   | 7839 | 1981-04-02 00:00:00 | 2975.00 | null |     20 |
|  7698 | blake | manager   | 7839 | 1981-05-01 00:00:00 | 2850.00 | null |     30 |
|  7782 | clark | manager   | 7839 | 1981-06-09 00:00:00 | 2450.00 | null |     10 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
8 rows in set (0.00 sec)

mysql>

到此这篇关于mysql复合查询的具体使用的文章就介绍到这了,更多相关mysql复合查询内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！