文章目录

  • 一、基本查询
    • 1.1 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为J
    • 1.2 按照部门号升序且员工工资降序进行排序
    • 1.3 使用年薪（月薪*12+奖金）进行降序排序
  • 二、简单子查询
    • 2.1 查找最高工资员工及其所在的部门
    • 2.2 显示高于平均工资的员工信息
  • 三、聚合查询
    • 3.1 显示每个部门的平均工资和最高工资
    • 3.2 显示平均工资低于2000的部门号和它的平均工资
    • 3.3 显示每种岗位的雇员总数及其平均工资
  • 四、多表查询
    • 4.1 显示员工名、员工工资及其所在的部门名字
    • 4.2 显示部门号为10的部门名，员工名和工资
    • 4.3 显示各个员工的姓名，工资，及工资级别
  • 五、自连接
  • 六、复杂自查询
    • 6.1 在where子句子查询（条件判断）
      • 6.1.1 单行子查询
      • 6.1.2多行子查询
      • 6.1.3 多列子查询
    • 6.2 在from子句中使用子查询
      • 6.2.1 显示每个高于自己部门平均工资的员工的姓名、部门
      • 6.2.2 显示每个高于自己部门平均工资的员工的姓名、部门、部门地点
      • 3.2.3 查找每个部门工资最高的人的姓名、工资、部门、最高工资
      • 3.2.4 显示每个部门的信息（部门名，编号，地址）和人员数量
  • 七、合并查询
    • 7.1 union（并集+去重）
    • 7.2 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.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)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)

1.2 按照部门号升序且员工工资降序进行排序

mysql> select * from emp order by empno ASC, sal DESC;+--------+--------+-----------+------+---------------------+---------+---------+--------+| 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 ename, empno, sal from emp order by empno, sal DESC;+--------+--------+---------+| ename| empno| sal |+--------+--------+---------+| SMITH| 007369 |800.00 || ALLEN| 007499 | 1600.00 || WARD | 007521 | 1250.00 || JONES| 007566 | 2975.00 || MARTIN | 007654 | 1250.00 || BLAKE| 007698 | 2850.00 || CLARK| 007782 | 2450.00 || SCOTT| 007788 | 3000.00 || KING | 007839 | 5000.00 || TURNER | 007844 | 1500.00 || ADAMS| 007876 | 1100.00 || JAMES| 007900 |950.00 || FORD | 007902 | 3000.00 || MILLER | 007934 | 1300.00 |+--------+--------+---------+14 rows in set (0.00 sec)

1.3 使用年薪（月薪*12+奖金）进行降序排序

mysql> select ename, sal, commfrom emp order by (sal*12 + ifnull(comm, 0)) DESC;+--------+---------+---------+| ename| sal | comm|+--------+---------+---------+| KING | 5000.00 |NULL || SCOTT| 3000.00 |NULL || FORD | 3000.00 |NULL || JONES| 2975.00 |NULL || BLAKE| 2850.00 |NULL || CLARK| 2450.00 |NULL || ALLEN| 1600.00 |300.00 || TURNER | 1500.00 |0.00 || MARTIN | 1250.00 | 1400.00 || MILLER | 1300.00 |NULL || WARD | 1250.00 |500.00 || ADAMS| 1100.00 |NULL || JAMES|950.00 |NULL || SMITH|800.00 |NULL |+--------+---------+---------+14 rows in set (0.00 sec)# 把年薪显示出来mysql> select ename, sal, comm, (sal * 12 + ifnull(comm, 0)) as 年薪 from emp order by 年薪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)

二、简单子查询

2.1 查找最高工资员工及其所在的部门

可以在SQL语句内部进行子查询，先执行内部SQL，再执行外部SQL。

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

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

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)

三、聚合查询

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

mysql> select deptno, avg(sal) 平均, max(sal) 最高 from emp group by deptno;+--------+-------------+---------+| deptno | 平均| 最高|+--------+-------------+---------+| 10 | 2916.666667 | 5000.00 || 20 | 2175.000000 | 3000.00 || 30 | 1566.666667 | 2850.00 |+--------+-------------+---------+3 rows in set (0.00 sec)# 为了美观可以保留精度mysql> select deptno, format(avg(sal),2) 平均, format(max(sal), 2) 最高 from emp group by depptno;+--------+----------+----------+| deptno | 平均 | 最高 |+--------+----------+----------+| 10 | 2,916.67 | 5,000.00 || 20 | 2,175.00 | 3,000.00 || 30 | 1,566.67 | 2,850.00 |+--------+----------+----------+3 rows in set (0.00 sec)

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

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

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

mysql> select job, count(ename) 雇员总数, avg(sal) 平均工资 from emp group by job;+-----------+--------------+--------------+| job | 雇员总数 | 平均工资 |+-----------+--------------+--------------+| 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)

四、多表查询

实际上很多时候我们要查的数据可能不在同一张表，所以需要多表查询。
比方说一张表里面有2行信息，另一个表里面有3行信息，如果直接select * form 表1,表2那么就会在表中进行穷举组合（得到的结果称为笛卡尔积），生成6行信息。


可以看到SMITH这个人明明是20号部门的，但是也还是会拼接上其他部门的无效信息。

使用笛卡尔积后，后面必定紧跟一个where子句来确定多张表共同的字段，筛掉多张表中排列组合出来的无效数据，仅留下有效数据供程序员排查。
这样，多表查询问题就转化成了单表查询。

mysql> select * from emp as e, dept as d where e.deptno=d.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)

4.1 显示员工名、员工工资及其所在的部门名字

mysql> select ename,sal,dname from emp as e, dept as d where e.deptno=d.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)

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

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

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

mysql> select ename,sal,grade from emp,salgrade where sal>losal 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 salgrade s1, salgrade s2;+-------+-------+-------+-------+-------+-------+| grade | losal | hisal | grade | losal | hisal |+-------+-------+-------+-------+-------+-------+| 1 | 700 |1200 | 1 | 700 |1200 || 2 |1201 |1400 | 1 | 700 |1200 || 3 |1401 |2000 | 1 | 700 |1200 || 4 |2001 |3000 | 1 | 700 |1200 || 5 |3001 |9999 | 1 | 700 |1200 || 1 | 700 |1200 | 2 |1201 |1400 || 2 |1201 |1400 | 2 |1201 |1400 || 3 |1401 |2000 | 2 |1201 |1400 || 4 |2001 |3000 | 2 |1201 |1400 || 5 |3001 |9999 | 2 |1201 |1400 || 1 | 700 |1200 | 3 |1401 |2000 || 2 |1201 |1400 | 3 |1401 |2000 || 3 |1401 |2000 | 3 |1401 |2000 || 4 |2001 |3000 | 3 |1401 |2000 || 5 |3001 |9999 | 3 |1401 |2000 || 1 | 700 |1200 | 4 |2001 |3000 || 2 |1201 |1400 | 4 |2001 |3000 || 3 |1401 |2000 | 4 |2001 |3000 || 4 |2001 |3000 | 4 |2001 |3000 || 5 |3001 |9999 | 4 |2001 |3000 || 1 | 700 |1200 | 5 |3001 |9999 || 2 |1201 |1400 | 5 |3001 |9999 || 3 |1401 |2000 | 5 |3001 |9999 || 4 |2001 |3000 | 5 |3001 |9999 || 5 |3001 |9999 | 5 |3001 |9999 |+-------+-------+-------+-------+-------+-------+25 rows in set (0.00 sec)

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

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

首先找到FORD领导的编号，然后再利用编号自查寻领导的姓名

# 子查询mysql> select ename from emp where empno=(select mgr from emp where ename = 'FORD');+-------+| ename |+-------+| JONES |+-------+1 row in set (0.00 sec)# 自连接mysql> select e2.ename from emp as e1, emp as e2 where e1.ename='FORD' and e1.mgr=e2.empno;+-------+| ename |+-------+| JONES |+-------+1 row in set (0.00 sec)

六、复杂自查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
子查询在where子句中使用，充当判断条件，在from子句中出现，充当笛卡尔积。

6.1 在where子句子查询（条件判断）

6.1.1 单行子查询

显示SMITH同一部门的员工

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)

6.1.2多行子查询

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

in关键字： 用来判断一个列值是否在集合当中。

mysql> select distinct 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)
  • 显示工资比部门30的所有员工的工资高的员工信息

all： >all()就表示比括号里面的所有数据都大

思路是先找到部门30的最高工资，再查找比他高的。

mysql> select * from emp where sal > (select max(sal) from emp where deptno=30);+--------+-------+-----------+------+---------------------+---------+------+--------+| empno| ename | job | mgr| hiredate| sal | comm | deptno |+--------+-------+-----------+------+---------------------+---------+------+--------+| 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 || 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 |+--------+-------+-----------+------+---------------------+---------+------+--------+4 rows in set (0.00 sec)mysql> select * from emp where sal > all(select sal from emp where deptno=30);+--------+-------+-----------+------+---------------------+---------+------+--------+| empno| ename | job | mgr| hiredate| sal | comm | deptno |+--------+-------+-----------+------+---------------------+---------+------+--------+| 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 || 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 |+--------+-------+-----------+------+---------------------+---------+------+--------+4 rows in set (0.00 sec)
  • 显示工资比部门30的任意（最低）员工的工资高的员工信息

any： 表示任意

mysql> select * from emp where sal > any(select distinct sal from emp where deptno=30);+--------+--------+-----------+------+---------------------+---------+---------+--------+| empno| ename| job | mgr| hiredate| sal | comm| deptno |+--------+--------+-----------+------+---------------------+---------+---------+--------+| 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 || 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 |+--------+--------+-----------+------+---------------------+---------+---------+--------+12 rows in set (0.00 sec)

6.1.3 多列子查询

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

  • 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
mysql> select ename from emp where (deptno, job)=(select deptno, job from emp where ename='SMITHH') and ename!= 'SMITH';+-------+| ename |+-------+| ADAMS |+-------+1 row in set (0.00 sec)

6.2 在from子句中使用子查询

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

# 先筛选出每个部门的平均工资mysql> select deptno, avg(sal) from emp group by deptno;+--------+-------------+| deptno | avg(sal)|+--------+-------------+| 10 | 2916.666667 || 20 | 2175.000000 || 30 | 1566.666667 |+--------+-------------+3 rows in set (0.00 sec)# 多表查询mysql> select * from emp, (select deptno, avg(sal) from emp group by deptno) tmp;………………# 转变成单表信息mysql> select * from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) 临时表 wheere +--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+| empno| ename| job | mgr| hiredate| sal | comm| deptno | deptno | avg(sal)|+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+| 007369 | SMITH| CLERK | 7902 | 1980-12-17 00:00:00 |800.00 |NULL | 20 | 20 | 2175.000000 || 007499 | ALLEN| SALESMAN| 7698 | 1981-02-20 00:00:00 | 1600.00 |300.00 | 30 | 30 | 1566.666667 || 007521 | WARD | SALESMAN| 7698 | 1981-02-22 00:00:00 | 1250.00 |500.00 | 30 | 30 | 1566.666667 || 007566 | JONES| MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 |NULL | 20 | 20 | 2175.000000 || 007654 | MARTIN | SALESMAN| 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | 1566.666667 || 007698 | BLAKE| MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 |NULL | 30 | 30 | 1566.666667 || 007782 | CLARK| MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 |NULL | 10 | 10 | 2916.666667 || 007788 | SCOTT| ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 |NULL | 20 | 20 | 2175.000000 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |NULL | 10 | 10 | 2916.666667 || 007844 | TURNER | SALESMAN| 7698 | 1981-09-08 00:00:00 | 1500.00 |0.00 | 30 | 30 | 1566.666667 || 007876 | ADAMS| CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 |NULL | 20 | 20 | 2175.000000 || 007900 | JAMES| CLERK | 7698 | 1981-12-03 00:00:00 |950.00 |NULL | 30 | 30 | 1566.666667 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 |NULL | 20 | 20 | 2175.000000 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 |NULL | 10 | 10 | 2916.666667 |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+-------------+14 rows in set (0.00 sec)# 单表查询mysql> select ename, emp.deptno from emp, (select deptno, avg(sal) 平均工资 from emp group by deeptno) 临时表 where emp.deptno=临时表.deptno and emp.sal > 平均工资;+-------+--------+| ename | deptno |+-------+--------+| ALLEN | 30 || JONES | 20 || BLAKE | 30 || SCOTT | 20 || KING| 10 || FORD| 20 |+-------+--------+6 rows in set (0.00 sec)

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

这一问比上一问多了一个部门地点，涉及到多表查询。

做笛卡尔积然后筛选有效信息

# 做笛卡尔积mysql> select * from dept, (select ename, emp.deptno from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) 临时表 where emp.deptno=临时表.deptno and emp.sal> 平均工资) t1;+--------+------------+----------+-------+--------+| deptno | dname| loc| ename | deptno |+--------+------------+----------+-------+--------+| 10 | ACCOUNTING | NEW YORK | ALLEN | 30 || 20 | RESEARCH | DALLAS | ALLEN | 30 || 30 | SALES| CHICAGO| ALLEN | 30 || 40 | OPERATIONS | BOSTON | ALLEN | 30 || 10 | ACCOUNTING | NEW YORK | JONES | 20 || 20 | RESEARCH | DALLAS | JONES | 20 || 30 | SALES| CHICAGO| JONES | 20 || 40 | OPERATIONS | BOSTON | JONES | 20 || 10 | ACCOUNTING | NEW YORK | BLAKE | 30 || 20 | RESEARCH | DALLAS | BLAKE | 30 || 30 | SALES| CHICAGO| BLAKE | 30 || 40 | OPERATIONS | BOSTON | BLAKE | 30 || 10 | ACCOUNTING | NEW YORK | SCOTT | 20 || 20 | RESEARCH | DALLAS | SCOTT | 20 || 30 | SALES| CHICAGO| SCOTT | 20 || 40 | OPERATIONS | BOSTON | SCOTT | 20 || 10 | ACCOUNTING | NEW YORK | KING| 10 || 20 | RESEARCH | DALLAS | KING| 10 || 30 | SALES| CHICAGO| KING| 10 || 40 | OPERATIONS | BOSTON | KING| 10 || 10 | ACCOUNTING | NEW YORK | FORD| 20 || 20 | RESEARCH | DALLAS | FORD| 20 || 30 | SALES| CHICAGO| FORD| 20 || 40 | OPERATIONS | BOSTON | FORD| 20 |+--------+------------+----------+-------+--------+24 rows in set (0.00 sec)# 筛选有效信息mysql> select t1.ename, dept.loc, t1.deptno from dept, (select ename, emp.deptno from emp, (select deptno, avg(sal) 平均工资 from emp group by deptno) 临时表 where emp.deptnoo=临时表.deptno and emp.sal > 平均工资) t1 where dept.deptno=t1.deptno;+-------+----------+--------+| ename | loc| deptno |+-------+----------+--------+| ALLEN | CHICAGO| 30 || JONES | DALLAS | 20 || BLAKE | CHICAGO| 30 || SCOTT | DALLAS | 20 || KING| NEW YORK | 10 || FORD| DALLAS | 20 |+-------+----------+--------+6 rows in set (0.01 sec)

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

# 通过group by聚合找出各部门的最高工资mysql> select deptno,max(sal) from emp group by deptno;+--------+----------+| deptno | max(sal) |+--------+----------+| 10 |5000.00 || 20 |3000.00 || 30 |2850.00 |+--------+----------+3 rows in set (0.00 sec)# 做笛卡尔积并筛选有效信息mysql> select * from emp, (select deptno,max(sal) from emp group by deptno) 部门最高工资 where emp.deptno=部门最高工资.deptno;+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+| empno| ename| job | mgr| hiredate| sal | comm| deptno | deptno | max(sal) |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+| 007369 | SMITH| CLERK | 7902 | 1980-12-17 00:00:00 |800.00 |NULL | 20 | 20 |3000.00 || 007499 | ALLEN| SALESMAN| 7698 | 1981-02-20 00:00:00 | 1600.00 |300.00 | 30 | 30 |2850.00 || 007521 | WARD | SALESMAN| 7698 | 1981-02-22 00:00:00 | 1250.00 |500.00 | 30 | 30 |2850.00 || 007566 | JONES| MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 |NULL | 20 | 20 |3000.00 || 007654 | MARTIN | SALESMAN| 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 |2850.00 || 007698 | BLAKE| MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 |NULL | 30 | 30 |2850.00 || 007782 | CLARK| MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 |NULL | 10 | 10 |5000.00 || 007788 | SCOTT| ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 |NULL | 20 | 20 |3000.00 || 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |NULL | 10 | 10 |5000.00 || 007844 | TURNER | SALESMAN| 7698 | 1981-09-08 00:00:00 | 1500.00 |0.00 | 30 | 30 |2850.00 || 007876 | ADAMS| CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 |NULL | 20 | 20 |3000.00 || 007900 | JAMES| CLERK | 7698 | 1981-12-03 00:00:00 |950.00 |NULL | 30 | 30 |2850.00 || 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 |NULL | 20 | 20 |3000.00 || 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 |NULL | 10 | 10 |5000.00 |+--------+--------+-----------+------+---------------------+---------+---------+--------+--------+----------+14 rows in set (0.00 sec)# 在添加工资等于最高工资的条件mysql> select ename, sal, emp.deptno, maxsal from emp, (select deptno,max(sal) maxsal from emp group by deptno) 部门最高工资 where emp.deptno=部门最高工资.deptno and 部门.高工资.maxsal=emp.sal;+-------+---------+--------+---------+| ename | sal | deptno | maxsal|+-------+---------+--------+---------+| 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)

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

  • 多表方法

1️⃣ 先统计每个部门有多少人

mysql> select deptno, count(*) dep_num from emp group by deptno;+--------+---------+| deptno | dep_num |+--------+---------+| 10 | 3 || 20 | 5 || 30 | 6 |+--------+---------+3 rows in set (0.00 sec)

2️⃣ 对上面的临时表进行笛卡尔积并过滤无效信息

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

3️⃣ 提取想要打印的信息

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

七、合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。注意左右两边的select所查询的字段要相同。

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

7.2 union all（并集+不去重）

  • 将工资大于25000或职位是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.01 sec)