MySQL笔记6多表查询

多表查询

表与表的关系

1. 一对多
   • 一个班级有多个学生
   • 一个学生对应一个班级
2. 多对多
   • 一个学生可以选多门课
   • 一门课程可以被多个学生选择
3. 一对一
   • 学生与学生详细信息表
   • 多用与单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在另一张表中

表结构在文章末尾
我们在查询学生和其所在班级两张表时如果直接使用

SELECT * FROM student , class;

我们会发现，返回的结果数是student中条数乘以class中的条数。
这种现象我们称之为笛卡尔积。
因此我们需要消除无用数据，并保留有效数据。

查询方式的分类

查询A、B两个表的交集部分数据，叫做内连接。
查询A的全部数据，并查询出A、B的交集部分数据，叫做左外连接
同理还有B的右外连接
若表与自身的连接查询，叫做自连接。

注意：自连接由于都是这一个表，所有我们需要给它起别名

内连接

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件…;

案例：使用隐式内连接的查询学生名称和其班级名称

SELECT student.name,class.name FROM student , class WHERE student.class_id = class.id;

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件…;

同样的案例我们使用显式内连接的方式

SELECT student.name,class.name FROM student INNER JOIN class ON student.class_id = class.id;

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;

案例：查询所有学生及其所在班级的名称，即使某些学生没有分配到班级

SELECT student.name FROM student LEFT OUTER JOIN class ON student.class_id = class.id;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON条件…;

• 注意：
  在MySQL中，实际上并不直接支持右外连接RIGHT OUTER JOIN。但是，你可以通过调整查询中的表顺序并使用左外连接LEFT OUTER JOIN来模拟右外连接的效果。

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件…;

案例：查找哪些学生在同一个班级

SELECT 
	s1.name AS student1, 
	s2.name AS student2 
FROM student s1 
JOIN student s2 
ON s1.class_id = s2.class_id
WHERE s1.id < s2.id;--避免重复和自身匹配

联合查询（UNION查询）

概念

允许将两个或更多的SELECT语句的结果集组合成一个结果集。
每个SELECT语句在UNION运算符之前，并且所有SELECT语句必须拥有相同列。

分类

• UNION ALL会将全部的数据直接合并在一起
• UNION会对合并之后的数据去重。

案例：我们想要查询所有学生的名字和所有班级的名字，并将这两个结果集合并成一个单一的列表

-- 查询所有学生的名字  
SELECT name  
FROM student  
UNION  
-- 查询所有班级的名字  
SELECT name  
FROM class;

子查询/嵌套查询

–外部可以是INSERT UPDATE SELECT
SELECT * FROM 表1 WHERE 字段1 = (SELECT 字段1 FROM 表2);

案例:

-- 找出至少有一个学生所在的班级  
SELECT name  
FROM class  
WHERE id IN (  
    SELECT DISTINCT class_id  
    FROM student  
);

根据子查询结果不同又分为

• 标量子查询(子查询结果为单个值)
• 列子查询(子查询结果为一列)
• 行子查询(子查询结果为一行)
• 表子查询(子查询结果为多行多列列)

操作符

• ANY(SOME) 子查询返回列表中，有一个满足即可
• ALL必须满足所有

以上所有的案例都基于此表

CREATE TABLE student (  
    id INT PRIMARY KEY,  
    name VARCHAR(50),  
    class_id INT,  
    -- 其他字段...  
    FOREIGN KEY (class_id) REFERENCES class(id)  
);
CREATE TABLE class (  
    id INT PRIMARY KEY,  
    name VARCHAR(50),  
    -- 其他字段...  
);