各类复合查询和笛卡尔积、内外链接的操作

1.基础查询

在之前的MySQL的CURD基本操作博客中，讲述了一部分查询的语法，但是在实际的生产活动中，那种最基础的查询方式不足以满足复杂查询条件时的要求，所以在普通查询的基础上，MySQL还提供了功能更强大的复合查询，本文就让我们一起来学学复合查询的操作吧！

依旧是使用scott提供的这张表，来进行复合查询的操作。

在那之前，先复习一下普通查询的基本操作吧！

查询工资高于500或者岗位为MANAGER的成员，同时他们的姓名首字母为大写J

1	select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

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

1	select * from emp order by deptno, sal desc;

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

1	select * from emp where sal > (select avg(sal) from emp);

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

1	select deptno, avg(sal), max(sal) from emp group by deptno;

显示每种岗位的雇员数量和平均工资

select job,count(*),avg(sal) from emp group by job;
-- 默认情况下会保留6位小数，可以用format函数保留到2位
select job,count(*),format(avg(sal),2) from emp group by job;
-- 格式化后的输出结果如下
+-----------+----------+--------------------+
| job       | count(*) | format(avg(sal),2) |
+-----------+----------+--------------------+
| ANALYST   |        2 | 3,000.00           |
| CLERK     |        4 | 1,037.50           |
| MANAGER   |        3 | 2,758.33           |
| PRESIDENT |        1 | 5,000.00           |
| SALESMAN  |        4 | 1,400.00           |
+-----------+----------+--------------------+
5 rows in set (0.000 sec)

2.多表查询

基础查询的操作就复习上面几个就差不多了，接下来我们先看复合查询的第一个大点，多表查询。

所谓多表查询，就是将一个表和另外一个表合并，再在这个合并了之后的表里面进行查询；

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

因为雇员名是在emp表里面，部门名字是在dept表里面，所以我们就需要进行多表的联合查询才能一次取到结果（一次查询的效率优于两次独立查询）

其基本语句就是在from后面添加多个表，并使用表名.表中字段名来指定某一个表中的字段；如果这个字段只有一个表中有，那就可以不用指定表名（但依旧建议指定表名避免出错）

1	select * from 表1,表2,...;

2.1 笛卡尔积

先来一个全列查询，看看效果

1	select * from emp,dept;

可以看到，dept表直接拼接在了emp表之后，而且emp表中的每一个值都被根据dept中不同的部门ID重复了4次。这是因为在MySQL中，他并不知道你需要什么数据，所以进行数据拼接的时候，是采用穷举的方式来拼接的！

这个不加任何过滤条件，穷举拼接得到的结果被称为笛卡尔积！

2.2 指定条件

接下来再回到上面提到的问题：显示雇员名、雇员工资以及所在部门的名字；

1	select emp.ename,emp.sal,dept.dname from emp,dept;

虽然我们选中了我们需要的列来显示，但依旧没有解决笛卡尔积中穷举出来的无效数据；

这时候就需要添加上筛选条件，来剔除掉无效的数据。

和当前员工的部门编号不同的部门数据就是无效的，我们添加上员工部门编号和部门表中的部门编号相同的条件，就可以剔除掉无效的数据。

1	select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno;

这时候筛选出来的数据，才是正确的每个员工和其对应的部门名字的数据；每个员工也不在会重复4次了。

MariaDB [scott]> select emp.ename,emp.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.004 sec)

再来做两个小练习

-- 显示部门号为10的部门名字，员工名字和员工工资；
MariaDB [scott]> select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno and dept.deptno = 10;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
3 rows in set (0.001 sec)

-- 显示各个部门员工的姓名，工资，工资级别
MariaDB [scott]> select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.001 sec)

3.自链接

所谓自连接，是在同一张表中进行链接和查询

比如显示员工FORD上级领导的员工编号和姓名

-- 使用子查询
select empno,ename from emp where emp.empno=(select mgr from emp where
ename='FORD');
-- 使用多表查询（其实是给emp表起两个不同的别名）
select leader.empno,leader.ename from emp leader, emp worker where
leader.empno = worker.mgr and worker.ename='FORD';

这种在一张表里面进行笛卡儿积的查询操作，就叫做自链接。最终都可以获取到正确结果

+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.001 sec)

4.子查询

子查询值得是，将一个查询语句作为子操作，嵌入到一个sql语句中；

我们可以把子查询的结果也当作是一张表来进行处理，这样一想就能理解子查询是怎么玩的了。

4.1 单行子查询

显示和SMITH同一个部门的员工

1	select * from emp where deptno = (select deptno from emp where ename='smith');

在这个查询中，子查询语句是(select deptno from emp where ename='smith')，这个语句返回的结果只有一行，所以被称为单行子查询；

4.2 多行子查询

有单行查询，自然也有多行查询。但是如果一个语句返回了多条结果，将其直接作为判据肯定是不行的，这会导致判据不唯一，MySQL不知道你到底想要比较的是那一部分的数据。这时候就需要用下面几个关键字来帮忙了

in：查询存在于某某结果里面的数据
all：查询比这个结果中所有值都大/小的数据
any：查询比这个结果中某一个值大/小的数据

以下是几个示例

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

-- 显示部门工资比30号部门所有员工工资都高的员工姓名、工资、部门号
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.001 sec)