MySQL --- 复合查询

目录

一、多表查询

二、自连接 

三、子查询

1、单行子查询

2、多行子查询

3、多列子查询

4、在from后面使用子查询

四、合并查询

1、union

2、union all

五、内连接

六、外连接

1、左外连接

2、右外连接

---

一、多表查询

我们需要的数据往往会来自不同的表，所以我们需要从多个表中查询数据，如何做？ 

用 emp 和 dept 两张表来举个例子，emp --- 记录员工的相关信息，dept --- 记录部门的相关信息

• 查看每个员工和其所在部门的所有信息  

为什么能这样查询？首先要明白下面这条语句的作用

select * from emp, depth;

显然，这条选择语句会让两个表的数据进行组合相乘(笛卡尔积)，得到一张新的表，我们查询的本质依旧是在这张新表中查找数据，只不过where的筛选条件需要带上表名，因为它们的列名一样。

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

二、自连接 

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

• 显示员工的上级领导的编号和姓名及其下级（mgr是员工领导的编号--empno）

注意：需要给表先取别名，两张表的名字一样数据库无法分辨

三、子查询

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

1、单行子查询

返回一行记录的子查询

• 显示Alice同一部门的员工

2、多行子查询

返回多行记录的子查询

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

• all关键字：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

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

3、多列子查询

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

• 查询和Alice的部门和岗位完全相同的所有雇员，不含Alice本人

4、在from后面使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用

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

四、合并查询

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

1、union

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

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

2、union all

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

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

注意：使用合并查询时，列数一定要相同。

五、内连接

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

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

• 查看每个员工和其所在部门的所有信息 

等价于下面这种写法，只是上面的写法更加的清晰

六、外连接

MySQL中的外连接（Outer Join）是一种特殊的SQL JOIN操作，它用于结合两个或多个表中的行，即使它们在连接条件上没有匹配项。外连接会返回至少一个表中的所有行，并且如果满足连接条件，则返回另一个表中的匹配行。如果某个表在连接条件上没有匹配项，则结果中该表的部分将包含NULL值。

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

1、左外连接

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

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

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

2、右外连接

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

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

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