1、基本查询回顾

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

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

1.3、使用年薪进行降序排序

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

我们把select聚合统计出来的最高薪资作为另一个select的子句。

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

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

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

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

---

2、多表查询

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

2.1、显示雇员名、雇员工资以及所在部门的名字。因为上面的数据来自EMP和DEPT表，因此要联合查询。

首先我们来看在from后面跟两个表名emp和dept获取的结果是怎么得到的。
我们可以发现是拿着emp的第一行跟dept的每一行进行组合，接着再拿emp的第二行给dept的每一行进行组合，以此类推。也就是将数据进行穷举组合，我们称之为笛卡尔积。
这就是将两张表组合成一张表，所以本质上还是单表查询。

由于会进行笛卡尔积组合，但是如果部门编号不同的组合就没有意义，所以加上where子句让它们两张表中的部门编号相等。最后我们只需要获取雇员姓名、雇员薪资、部门名称即可，如果有冲突需要在前面加上表明.的方式获取，没有直接写属性名即可。

2.2、显示部门号为10的部门名，员工名和工资。

2.3、显示各个员工的姓名，工资，及工资级别。

---

3、自连接

自连接是指在同一张表连接查询。

直接对两张同样的表做笛卡尔积是不行的，需要进行重命名否则无法辨认。同时可以发现两张表的组合跟上面不同表的组合一样。并且注意到，这里是后面的表和前面的表进行组合的，在老版本MySQL中是前面的表和后面的表进行组合。

3.1、显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno）
有两种做法，第一种做法使用子查询：

第二种做法，使用多表查询：

虽然根据上面查询结果我们发现组合方式发生了变化，但是只要SQL语句是正确的，照样可以返回结果。

---

4、子查询

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

4.1、单行子查询

单行子查询：返回一行记录的子查询。
如：显示SMITH同一部门的员工

4.2、多行子查询

多行子查询：返回一行记录的子查询。

4.2.1、in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

使用in来判断，job是否在返回的多行子查询中。
如果我们还需要知道部门的名字呢？

MySQL一切皆表，我们上面的查询结果也是一个表结构，我们把上面的查询结果另一个查询的表，并与表dept进行笛卡尔积。
子查询不仅可以出现在where子句中，还可以出现在from后面。

4.2.2、all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。
可以查询部门30最高工资是多少，然后将其结果作为另一个查询的子查询：

也可以使用下面这种做法：

all在这里表示，sal要大于子查询结果里的所有sal。

4.2.3、any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

4.3、多列子查询

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

案例：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人。

4.4、在from子句中使用子查询

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

首先分组聚合计算出每个部门的平均薪资，然后让emp和该表进行笛卡尔积。

拓展：我要的是这些人办公地点在哪里

4.4.2、查找每个部门工资最高的人的姓名、工资、部门、最高工资
思路：先分组聚合统计出每个部门最高薪资，然后将该表和emp表进行笛卡尔积，筛选出部门相等和薪资相等的信息，最后需要什么数据拿什么数据即可。

4.4.3、显示每个部门的信息（部门名，编号，地址）和人员数量
多表查询，首先根据部门号聚合统计出每个部门的人数，然后将临时表和dept表做笛卡尔积，筛选条件为部门编号相等，然后将需要的信息拿出来即可。

另一种做法，不推荐！

解决多表问题的本质：想办法将多表转换成单表，MySQL中所有select的问题都可以转换成单表问题。

4.5、合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符union，union all。

4.5.1、union该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
案例：将工资大于2500或职位是MANAGER的人找出来。

4.5.2、union all该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：将工资大于2500或职位是MANAGER的人找出来。

注意：使用union/union all必须保证两个查询的列数是相同的。

---

5、表的内连和外连

5.1、内连接

表的连接分为内连和外连。
内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

案例：显示SMITH的名字和部门名称。
采用之前的写法：

采用上面的语法：

5.2、外连接

外连接分为左外连接和右外连接。

5.2.1、左外连接

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

首先创建两张表方便我们进行测试：

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

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

如图，左侧全部显示，右侧如果没有匹配的就会补NULL。

5.2.2、右外连接

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

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

列出部门名称和这些部门的员工信息，同时列出没有员工的部门。

或者采用右外连接：