8.MySQL复合查询

复合查询

前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。我们还需要了解符合查询

基本查询回顾

我们操作的表还是基于orcale 9i的经典测试表

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

2.按照部门号升序而雇员的工资降序排序

3.使用年薪进行降序排序

4.显示工资最高的员工的名字和工作岗位
最高工资可以聚合函数得出，得到最高工资，再去找这些员工是谁

这样写太粗糙了，不简洁，所以还有一种写法：
sql语法允许在sql语句的where子句的表达式中，再执行一条select查询，这叫做子查询。先执行内部的（），再执行外部的语句

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

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

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

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

多表查询 - 笛卡尔积

实际开发中往往数据来自不同的表，所以需要多表查询，以前我们from是跟一张表，实际上可以跟多张表。本节我们用一个简单的公司管理系统，有三张表EMP，DEPT，SALGRADE来演示如何进行多表查询

示例：

1.显示雇员名、雇员工资以及所在部门的名字
因为上面的数据来自EMP和DEPT表，因此要联合查询（笛卡尔积），什么是笛卡尔积？
a.从第一张表中选出第一条记录，和第二个表的所有记录进行组合
b.然后从第一张表中取第二条记录，和第二张表中的所有记录组合
c.不加过滤条件，得到的结果称为笛卡尔积

如何做笛卡尔积？

笛卡尔积本质就是将两张表变为了一张表，未来做数据查询的时候，还是可以和单表查询的方式进行查询。

我们发现，SMITH是20号部门的，但出现了10号部门的信息，这些的信息对于SMITH是没有用的，所以就可以将我们认为有意义的数据筛选出来

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

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

自连接

上面讲的是将两个不同的表做笛卡尔积，那能不能将两个相同的表做笛卡尔积呢？

这里报错的原因是两个名字是相同的，后续操作这个表时，无法区别一个数据到底是哪个表里的数据，所以需要对表进行重命名。

对同一张表做笛卡尔积的行为叫做自连接。

示例：

显示员工FORD的上级领导的编号和姓名(mgr是员工领导的编号 – empno)
解决方案一：使用where子查询

解决方案二：自连接

子查询

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

单行子查询

单行子查询是指子查询只返回单列，单行数据。

示例：

显示SMITH同一个部门的员工

多行子查询

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

示例：

1.查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
in关键字：某一列是否在某一个集合中

2.显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
all关键字：某一列与集合中的所有元素进行比较

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
any关键字：某一列与集合中的任意元素进行比较

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

在from中使用子查询

截止目前为止，全部的子查询都是在where子句中充当判断条件，接下来子查询语句会出现在from子句中。

示例：

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

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

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

合并查询

在实际应用中，为了合并多个select的执行效果，可以使用集合操作符union，union all，这并不是笛卡尔积，这比笛卡尔积的效率要高，笛卡尔积是两个表的穷举，而这个就是单纯的属性相同的合并。

union

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

示例：

将工资大于2500或职位是MANAGER的人找出来

union all

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

表的内连和外连

内连接

截止目前为止，我们前面学习的都是内连接。内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选。

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

这语法和我们之前做笛卡尔积 + where筛选是一样的，笛卡尔积本质上就是内连接的一种

示例：

显示SMITH的名字和部门名称

外连接

左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接

语法：

select 字段名 from 表名1 left join 表名2 on 连接条件;

建两张表

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

右外连接

如果联合查询，右侧的表完全显示我们就说是右外连接

语法：

select 字段 from 表名1 right join 表名2 on 连接条件;

示例：

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来