MySQL复合查询

多表查询

将多张表关联起来进行查询

在进行多表查询的时候，把多张表的表名放在from字句后面用逗号隔开。这个时候MySQL就会对多张表进行取笛卡尔积作为多表查询的初始数据。

笛卡尔积

多张表记录的所有可能有序对的集合

假设A={a, b}，B={1, 2, 3}，则它们的笛卡尔积为{(a, 1), (a, 2), (a, 3), (b, 1), (b, 2), (b, 3)}。

比如：表一，部门表

表二，员工表

它们的笛卡尔积是：

需要注意的是对多张表取笛卡尔积得到的数据。并不是都有意义。比如上表中每个员工和每个部门进行组合实际上只有一个员工和所在部门信息组合才有。因此对笛卡尔积需要进行以下过滤筛选出有意义的数据。可以这么做：

加上条件dept.deptno=emp.deptno

查询案例

给定薪资表（losal和hisal表示对应工资等级的工资区间）：

根据上面三张表进行查询：

案例一：显示各个员工姓名、工资和工资级别

条件也可写成：sal between losal and hisal

案例二：显示员工SMITH的上级领导的编号和姓名

注：mgr表示上级领导的编号

解法一，使用子查询。先对员工表进行查询得到Smith领导编号。再根据领导编号在表中查询Smith领导姓名：

解法二：使用自连接

自连接意思是在同一张表取笛卡尔积，再对表进行查询。

因为表名一样，需要使用as进行重命名以对表进行区分。

子查询

子查询指的是嵌入在其他SQL语句当中的查询语句。也叫嵌套查询。

单行子查询

指的是返回单行单列数据的子查询（理解为where语句的结果就是返回单行单列数据）

案例一：显示SMITH同一部门的员工

方法一：先子查询smith所在部门，再查询同一部门的员工并排除smith。

方法二：使用自连接

将两表做笛卡尔积，筛选表一名字为smith且表一表二deptno相等，这样就得到了和smith部门相同的员工，并且表二ename不为smith，取结果中的表二的列即是答案。

多行子查询

子查询返回多行单列数据的查询。

案例一 显示和10号部门的工作岗位相同的员工的姓名、岗位、工资和部门号，但是不包含10号部门的员工

解法：使用in关键字进行查询
先子查询10部门的工作岗位（注意使用distinct去重排除相同部门的员工，再整个表查询员工岗位在查询结果的员工，并排除10号部门的员工：

案例二：显示工资比30号部门的所有员工的工资高的员工的姓名、工资和部门号

解法一：使用all关键字，进行多行子查询

解法二：使用max函数，进行单行子查询

案例三：显示工资比30号部门任意员工的工资高的员工的姓名、工资和部门号，包含30号部门的员工（注意：这里的任意是指比30号部门中一个或多个员工工资高）

解法一：使用any关键字，进行多行子查询

解法二：使用min函数，进行单行子查询

多列子查询

指的是返回多列数据的子查询。
在多列子查询中将查询条件匹配多列即可。

案例一：显示和ALLEN的部门和岗位完全相同的员工，不包括本人：

同时当多列子查询返回多行数据时，也可以使用in、all、any关键字。

在from子句中使用子查询

子查询语句出现在from子句中，其查询的结果将会被当中一个临时表，可被放在from子句中。

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

注意：在from子句使用子查询时必须给子查询得到的临时表取一个别名，否则会报错。

案例二：显示每个部门的部门名、部门编号、所在地址和人员数量

方法一：子查询 + 多表查询
先查询各个部门的人数，在联合部门表和查询结果的临时表即可。

方法二：多表查询
注意在使用多表查询时如果要显示对应的列，就要在group对应的条件中添加（虽然不加的意义也相同）。
（这是MySQL的规定的了属于是）

合并查询

将多个查询结果合并，可以使用的操作符有union和union all
两个操作符用于取得的两个查询结果的并集，区别是union会自动去掉结果集中的重复行，而union all不会。

案例一：显示工资大于2500或职位是MANAGER的员工

根据前面的查询容易写出：

也可以使用union关键字这么写：

注意使用union去联合两个查询结果时要保证两个查询结果的列的数量都是相同的。（但是列的属性可以不同，不过这样就没有意义了）

试试用union all：

使用distinct和子查询验证一下union all的结果可见是正确的：