【MySQL】复合查询

最新推荐文章于 2025-05-20 10:09:32 发布
最新推荐文章于 2025-05-20 10:09:32 发布
阅读量620 收藏 12
点赞数 18
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Sweet_0115/article/details/148064549
版权

目录

一、基本查询

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

2、按照部门号升序而雇员额工资降序排序

3、按照年薪进行降序排序

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

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

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

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

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

二、多表查询

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

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

3、显示各个雇员的姓名、工资以及工资级别

三、自连接

1、显示员工FORD的上级领导的编号和姓名

四、子查询

（一）单行子查询

（二）多行子查询

1、in关键字

2、all关键字：

3、any关键字

（三）多列子查询

（四）在from子句中使用子查询

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

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

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

五、合并查询

（一）union

（二）union all

一、基本查询

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)

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

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)

2、按照部门号升序而雇员额工资降序排序

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)

3、按照年薪进行降序排序

mysql> select * ,sal * 12 as 年薪 from emp order by sal * 12 desc;
+--------+--------+-----------+------+---------------------+---------+---------+--------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | 年薪     |
+--------+--------+-----------+------+---------------------+---------+---------+--------+----------+
| 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 | 19200.00 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 | 18000.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 | 15000.00 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 | 15000.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.01 sec

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

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

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

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)

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

mysql> select deptno, format(avg(sal), 2), max(sal) from emp group by deptno;
+--------+---------------------+----------+
| deptno | format(avg(sal), 2) | max(sal) |
+--------+---------------------+----------+
|     10 | 2,916.67            |  5000.00 |
|     20 | 2,175.00            |  3000.00 |
|     30 | 1,566.67            |  2850.00 |
+--------+---------------------+----------+
3 rows in set (0.00 sec)

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

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

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

mysql> select job, count(*), avg(sal) from emp group by job;
+-----------+----------+-------------+
| job       | count(*) | avg(sal)    |
+-----------+----------+-------------+
| ANALYST   |        2 | 3000.000000 |
| CLERK     |        4 | 1037.500000 |
| MANAGER   |        3 | 2758.333333 |
| PRESIDENT |        1 | 5000.000000 |
| SALESMAN  |        4 | 1400.000000 |
+-----------+----------+-------------+
5 rows in set (0.00 sec)

二、多表查询

        在开发场景中，单表查询可能并不能满足需求，因此需要扩展到多表查询。

        笛卡尔积是指在多表查询时，未指定任何连接条件的情况下，数据库将两个或多个表中的每一行进行无限制组合，生成所有可能的行对的操作。

        例如：当查询雇员基本信息外还想知道该雇员所在部门名，因此单个 emp 表并不能满足需求，因此要进行联合查询。

mysql> select * from emp, dept;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     30 | SALES      | CHICAGO  |
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     40 | OPERATIONS | BOSTON   |
..........

        以上的SQL语句只是将两个表进行笛卡尔积，会产出很多无效的结果。例如SMITH是部门20的，但在表中却分别和部门10，20，30和40进行穷举组合，其中与部门10，30和40对应的记录为无效数据。因此需要后面必定紧跟一个where子句来确定多张表共同的字段，筛掉多张表中排列组合出来的无效数据，仅留下有效数据供程序员排查。

mysql> select * from emp, dept where emp.deptno = dept.deptno;
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
14 rows in set (0.00 sec)

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

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)

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

mysql> select ename, dname, sal from emp, dept where emp.deptno = dept.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)

3、显示各个雇员的姓名、工资以及工资级别

mysql> select ename, sal, grade from emp, salgrade where losal < sal and sal < 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 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
12 rows in set (0.00 sec)

三、自连接

        自连接是指在同一张表连接查询。对同一张表做重命名，在同一张表连接查询：

mysql> select * from dept as d1, dept as d2;
+--------+------------+----------+--------+------------+----------+
| deptno | dname      | loc      | deptno | dname      | loc      |
+--------+------------+----------+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |     10 | ACCOUNTING | NEW YORK |
|     30 | SALES      | CHICAGO  |     10 | ACCOUNTING | NEW YORK |
|     40 | OPERATIONS | BOSTON   |     10 | ACCOUNTING | NEW YORK |
|     10 | ACCOUNTING | NEW YORK |     20 | RESEARCH   | DALLAS   |
|     20 | RESEARCH   | DALLAS   |     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |     20 | RESEARCH   | DALLAS   |
|     40 | OPERATIONS | BOSTON   |     20 | RESEARCH   | DALLAS   |
|     10 | ACCOUNTING | NEW YORK |     30 | SALES      | CHICAGO  |
|     20 | RESEARCH   | DALLAS   |     30 | SALES      | CHICAGO  |
|     30 | SALES      | CHICAGO  |     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |     30 | SALES      | CHICAGO  |
|     10 | ACCOUNTING | NEW YORK |     40 | OPERATIONS | BOSTON   |
|     20 | RESEARCH   | DALLAS   |     40 | OPERATIONS | BOSTON   |
|     30 | SALES      | CHICAGO  |     40 | OPERATIONS | BOSTON   |
|     40 | OPERATIONS | BOSTON   |     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+--------+------------+----------+
16 rows in set (0.00 sec)

1、显示员工FORD的上级领导的编号和姓名

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

四、子查询

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

（一）单行子查询

        返回一行记录的子查询。

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

（二）多行子查询

        返回多行记录的子查询

1、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 |
+-------+---------+---------+--------+
| 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)

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)

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)

（三）多列子查询

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

        案例：查询和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.01 sec)

（四）在from子句中使用子查询

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

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

mysql> select ename, emp.deptno, sal, AVG from emp, (select avg(sal) as AVG, deptno from emp group by deptno) as
tmp where emp.sal > tmp.AVG and emp.deptno = tmp.deptno;
+-------+--------+---------+-------------+
| ename | deptno | sal     | AVG         |
+-------+--------+---------+-------------+
| KING  |     10 | 5000.00 | 2916.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| FORD  |     20 | 3000.00 | 2175.000000 |
| ALLEN |     30 | 1600.00 | 1566.666667 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)

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

mysql> select ename, sal, emp.deptno, MAX from emp, (select deptno, max(sal) as MAX from emp group by deptno) astmp where emp.deptno = tmp.deptno and emp.sal = tmp.MAX;
+-------+---------+--------+---------+
| ename | sal     | deptno | MAX     |
+-------+---------+--------+---------+
| 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.01 sec)

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

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

五、合并查询

（一）union

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

        案例：显示工资大于2500或职位是 MANGER 的人的信息。

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)

（二）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)

菜菜小蒙
关注
MySQL复合查询(重点)
wangsir的博客
07-13 1845
MySQL复合查询详细讲解
MySQL篇七：复合查询
忘忧.
07-10 1521
前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。
MySQL复合查询
dong132697的博客
01-15 1141
前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。下面我们来学习一些MySQL中更复杂的查询。下面的表还是来自oracle 9i的经典测试表。EMP员工表DEPT部门表SALGRADE工资等级表查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J可以看到内置函数在where子句中也是可以使用的。按照部门号升序而雇员的工资降序排序使用年薪进行降序排序。
[MySQL] MySQL复合查询（多表查询、子查询）
weixin_67596609的博客
12-08 3845
前面我们学习了MySQL简单的单表查询。但是我们发现，在很多情况下单表查询并不能很好的满足我们的查询需求。本篇文章会重点讲解MySQL中的多表查询和一些复杂查询。希望本篇文章会对你有所帮助。
MySQL：复合查询
小白成长之路
12-12 1014
本文讲解MySQL中的复合查询，干货满满，快来看看吧！！！
MySQL复合查询(多表查询)
分享学习的知识，记录我的技术成长道路
10-17 1231
通常当进行自查询的时候，为了避免冲突，通常需要为表取别名，由于from是在select之前就执行了的，因此select中可以使用from中起的别名。多列子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。书写复杂的查询过程，一定要先明确执行顺序，子查询是第一个执行的，from语句是第二个执行的，where是第三个执行的，select是最后执行的。在了解复合查询之前，我们需要先复习一下在单表中的查询：这些查询都是在oracle标准表中进行的。使用关键字进行比较。
mysql 复合查询
TH_NUM的博客
03-09 3184
MySQL 的复合查询或者嵌套查询，有表两张，要以 clrTheme 表两张为表列，将 clrColor 横向列出，故选择嵌套查询。 MySQL 复合嵌套查询命令如下 select * from clrTheme as t1, ( (select * from clrColor where `sort` = 0) as c1, (select * from clrColor...
MySQL--复合查询
m0_74099572的博客
05-28 1121
之前学过了基本的查询，虽然已经够80%的使用场景了，但是依旧需要了解剩下的20%。
【MySQL 08】复合查询
2301_76618602的博客
10-04 1376
数学上，有两个集合A={a,b},B={1,2,3},则两个集合的笛卡尔积={{a,1}, {a,2}, {a,3}, {b,1}, {b,2}, {b,3}} 列出所有情况,一共是2*3=6条记录；
MySQL复合查询（灰常重要o）
01-19
1 基本查询回顾 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%'; 按照部门号升序但雇员的工资降序排序 ...
MySQL 复合查询
m0_74317866的博客
01-08 990
基本查询、多表查询、自连接、子查询、合并查询
网上体育商城的设计与实现（有商家端）（SpringBoot+Vue+MySQL）
欧阳小白闯天涯
05-20 558
本文介绍了一个基于互联网的网上体育商城系统的设计与实现。该系统旨在解决传统实体店的地域限制和库存管理问题，为用户提供便捷的购物体验。系统分为前台和后台两部分，前台支持商品浏览、搜索、购买等功能，后台则提供商品管理、订单管理、用户管理等功能。系统采用SpringBoot、Vue.js和MySQL等技术栈，具备良好的扩展性和维护性。文章详细分析了系统的需求、功能模块、技术可行性、市场可行性、经济可行性和法律法规可行性，并提供了功能代码示例。通过该系统，用户可以高效完成购物任务，提升购物体验。
【MySQL】表的约束
Sweet_0115的博客
05-16 920
【MySQL】表的约束
Windows系统下MySQL 8.4.5压缩包安装详细教程
最新发布
一起coding，一起嗨。
05-20 454
Windows系统下MySQL 8.4.5压缩包安装详细教程
Win11下轻松搭建wiki.js，Docker.desktop部署指南（mysql+elasticsearch+kibana+wiki.js）
wodongx123的博客
05-17 1325
系统环境：win11配置要求：能跑得动文中服务总和的内存，8gb至少软件环境：Docker desktop。wiki.js作为一个维基站点，他同时需要两个数据库的支持。正常的关系型数据库，用于用户信息等东西的存储，这里我们用Mysql。一个专门用于海量数据查询的数据库，因为wiki内的数据量很大没用正常的数据库查询会很卡，所以需要用专门用于大数据查询的数据库，也就是ElasticSearch了。
MySQL高可用
2502_91448199的博客
05-17 1312
目前MySQL已经成为市场上主流数据库之一,考虑到业务的重要性,MySQL数据库单点问题已成为企业网站架构中最大的隐患。随着技术的发展,MHA的出现就是解决MySQL单点的问题。另外随着企业数据量越来越庞大大,数据库的压力又成为企业的另一个瓶颈,MySQL多主多从架构的出现可以减轻MySQL本身的压力。本章将主要围绕"MySQL主主复制+Keepalived+HAProxy"这一经典高可用架构展开,通过理论结合实践,解析如何通过开源工具实现数据库的故障自动转移、负载均衡和读写分离。
python+fastapi将csv数据导入mysql
cf8833的博客
05-16 147
import_student_scores：将CSV文件中的数据导入到数据库表中。generate_test_csv：生成包含随机学生成绩的CSV文件。create_student_table：在数据库中创建学生成绩表。python+fastapi将csv数据导入mysql。亲测可运行，成功执行，测试通过。
MySQL 8.0 OCP 1Z0-908 121-130题
数据库小学生
05-16 1004
C)Node ic3 becomes the new primary and is ignored until any backlog of transactions is completed//节点 ic3 成为新的主节点，并在完成所有积压的事务之前被忽略。
mysql复合条件查询
08-19
在MySQL中，你可以使用逻辑运算符和比较运算符来进行复合条件查询。 下面是一个示例，展示了如何在MySQL中进行复合条件查询： ```sql SELECT * FROM table_name WHERE condition1 AND condition2; ``` 在上面的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值