MySQL基础篇 - 多表查询

01 多表关系

【1】概念：项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各表结构之间也存在着各种联系，基本上分为三种：一对多(多对一)，多对多(要建立中间表)，一对一。

02 多表查询概述

【1】概念：多表查询就是指在多张表中查询数据

【2】如果直接查询有多表关系的表：

         例如：select * from emp , dept; 则查询出来的结果将是两张表的笛卡尔积个数据。

         但其实我们只需要一些数据，则此时我们可以通过外键关系在where里面写上条件即可(消除无效笛卡尔积)：

         例如：select * from emp , dept where emp.dept_id = dept.id;

【3】多表查询的分类：

03 内连接

【1】内连接：查询两个表有外键关联的数据，如果子表中某个外键数据没有与父表主键关联的值则不会被查出来。

【2】分类：

   ① 隐式内连接：select 字段列表 from 表1 as 别名，表2 as 别名 where 外键关系；

   ② 显示内连接：select 字段列表 from 表1 as 别名 inner join 表2 as 别名 on 外键关系；

【说明】：在多表查询时，我们通常会给表取别名，注意：一旦我们给表取别名后不能再直接使用表名了。

04 外连接

【1】左外连接：查询表1的数据，以及表1和表2交集部分的数据。

【2】右外连接：查询表2的数据，以及表1和表2交集部分的数据。

【3】分类：外连接：如果子表中某个外键数据没有与父表主键关联的值也会被查出来。

   ① 左外连接：select 字段列表 form 表1 as 别名 left outer join 表2 as 别名 on 外键关系；

   ② 右外连接：select 字段列表 form 表1 as 别名 right outer join 表2 as 别名 on 外键关系；

【4】说明：下图案例中很好的说明了：

         内连接：如果子表中某个外键数据没有与父表主键关联的值则不会被查出来。

         外连接：如果子表中某个外键数据没有与父表主键关联的值也会被查出来。

05 自连接

【1】场景：表中的外键关联的是自己所在表的某个字段，例如：查询某个员工的领导，而领导也是员工。

【2】语法：其实就是利用取别名，把一张表看成两张表，可以内查询也可以外查询。

    ① 内连接(隐式写法)：select 字段列表 form 表一 别名，表一 别名 where 外键关系；

    ② 外连接(左外连接)：select 字段列表 from 表一 别名 left outer join 表一 别名 on 外键关系；

06 联合查询

【1】概念：union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

【2】注意：

   ① 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

       (比如查多张成绩单上满足某条件的数据，然后把它合并起来形成一张表)

   ② union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

【3】语法：

         select 字段列表 from 表a ...

         union all/union

         select 字段列表 from 表b ...

07 子查询

【1】概念：SQL语句中嵌套select语句，称为嵌套查询，即子查询。

【2】子查询外部的语句可以是insert/update/delete/select的任何一个，select会用得多一些。

【3】举例：select * from t1 where column1 = (select column1 from t2)

【4】分类：根据子查询的结果分为：

   ① 标量子查询（子查询结果为单个值）

   ② 列子查询（子查询结果为一列）

   ③ 行子查询（子查询结果为一行）

   ④ 表子查询（子查询结果为多行多列）

【5】根据子查询位置，分为：where之后，form之后，select之后。

07_01 标量子查询

【1】标量子查询：子查询返回的查询结果是单个值（数字，字符串，日期等）

【2】标量子查询中常用的操作符：=，<>，>，>=，<，<=

07_02 列子查询

【1】列子查询：子查询的结果是一列（可以是多行）

【2】列子查询中常用的操作符：in，not in，any，some，all

【3】in是只要满足其中的一种，any是只要=，<>，>，>=，<，<= 其中一种

07_03 行子查询

【1】行子查询：返回的结果是一行(可以是多列)

【2】行子查询中常用的操作符：=，<>，in，not in

【3】注意：a = 12 && b = 13 可以写成 （a, b）= (12, 13)，因此下图示例中的写法才是那样。

07_04 表子查询

【1】表子查询：返回的结果是多行多列的。

【2】表子查询常用的操作符：in