MySQL多表查询--基础（一）

多表关系

在项目开发中，根据业务需求及业务模块得不同，在众多数据库表中，会存在各种联系。基本分为：

• 一对一关系
• 一对多（多对一）关系
• 多对多关系

一对一关系

• 例如：学生与学生信息得关系
• 实现方式：单表拆分，将主字段放在一张表中，其他非主字段放入另一张表中。在任意一方加入外键，关联另外一方得主键，并且设置外键为唯一的

一对多（多对一）

• 例如：班级与学生的关系；一个学生对应一个班级，一个班级对应多名学生
• 实现方式：在多的一方建立外键，指向唯一的一方的主键。（如：该班的学生表有多个，该班的班级表只有一个）

多对多

• 例如：学生与课程的关系；一个学生可以学习多门课程，一门课程也可以接受多名学生
• 实现方式：建立第三张表，作为中间表，中间表至少包含两个外键，分别关联存在关系的两表中的主键。

多表查询

笛卡尔积

        笛卡尔积，数学中表示两个集合中第一个对象是X，第二个对象是Y，展示二者之间所有可能的组合情况，也就是并集关系。在多表查询中容易出现数据重复的情况，也就是无效数据。在多表查询中，需要做到清除无效的笛卡尔积。要用到特定的查询方式达到效果。

多表查询分类

连接查询

• 内连接（Inner Join）：相当于查询X，Y中交集部分的数据
• 外连接
  • 左外连接（左连接 Left Join）：查询左表所有数据，以及两张表交集部分数据
  • 右外连接（右连接 Right Jion）：查询右表所有数据，以及两张表交集部分数据
• 自连接（Self Jion）：当前表与自身相关联的数据

子查询

内连接

• 内连接两张表交集的部分
  • 内连接查询语法

    • 隐式内连接：SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
    • 显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

• 演示

-- 查询每一个学生的姓名，及关联的班级
-- 表结构：class(班级表),stu(学生表)
-- 连接方式：stu.class_id = class.id（学生表的班级id = 班级的主键id）

-- 隐式内连接演示
SELECT stu.name,class.name FROM stu,class WHERE stu.class_id = class.id;

-- 显式内连接演示
SELECT stu.name,class.name FROM stu INNER JOIN class ON stu.class_id = class.id;

外连接

• 左外连接：相当于查询左表的所有数据，包含左表和右表交集部分(也就是左右表都满足条件的部分)的数据，另外左边剩下的，不满足右边条件的部分用null表示。

  • 左外连接语法：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

• 右外连接：相当于查询右表的所有数据，包含右表和左表交集部分(也就是左右表都满足条件的部分)的数据，另外右边剩下的，不满足左边条件的部分用null表示。

  • 右外连接语法：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

• 演示

-- 还是以班级和学生为例，查询学生的所有数据，和对应的班级信息

-- 左外连接演示
SELECT stu.*, class.name FROM stu LEFT JOIN class ON stu.class_id = stu.id;
--可简写为
SELECT * FROM stu s LEFT class c ON s.class_id = c.id;

-- 右外连接演示
SELECT stu.*, class.name FROM stu RIGHT JOIN class ON stu.class_id = stu.id;
--可简写为
SELECT * FROM stu s RIGHT class c ON s.class_id = c.id;

• 重点：左右表连接返回的结果和记录条数不一定相等，因为左右连接是以左表或者右表为标准，对表中每一个数据都进行一次比较并返回结果。以左连接为例，就是以左表为准，那么在没有其他操作的情况下，查询结果总记录条数应该与左表的记录条数相等。

自连接

• 自连接是一种物理上存在于同一张表的连接关系，但是逻辑上确实多表查询关系。最常见的是表中存在于上下级和父子级的关系。例如学校职工表，其中包含所有的职工，身份包括校长，教导主任，学年组长，班主任，科任老师等。为了清晰的表示之间的层级关系，可以增加一个直接领导id的字段，教导主任指向校长id，学年组长指向教导主任，班主任指向学年组长等。

  • 自连接语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

• 演示

-- 查询教师职工 及其 所属直接领导的名字
-- 表结构：emp(职工表)

-- 自连接演示(结果为交集)
SELECT e.name,m.name FROM emp e, emp m WHERE e.id = m.manger_id;

-- 如果存在员工暂时没有领导, 同时也需要查询出时(可采用左右连接，不存在领导的领导为null)
SELECT e.name,m.name FROM emp e LEFT JOIN emp m ON e.id = m.manger_id;

子查询

• SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个。

  • 子查询语法：SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);