乐趣区

关于mysql:SQL-基础之多表查询

jiezi

一、前言

SQL 最弱小的性能之一就是能在数据检索查问的执行中联结表。联结是利用 SQL 的 SELECT 执行的最重要的操作，很好地了解联结及其语法是学习 SQL 的一个极为重要的组成部分。

二、E-R 图与班级学生表

E-R 图如下：

学生表如下，如果学生曾经调配了班级，class_id 列则为调配班级的编号，否则为 NULL（意为未调配）：

班级表如下：

三、内联结

内联结将两个表中满足指定联结条件的记录联结成新的后果集，并舍弃所有不满足联结条件的记录。

（一）两个表之间的联结

语法：

SELECT 
    ... 
FROM tb_1 INNER JOIN tb_2 
ON tb_1.f_1 = tb_2.f_1;

// 1. tb_1 INNER JOIN tb_2 意为 tb_1 内联 tb_2，INNER 可省略：tb_1 JOIN tb_2。// 2. ON 前面接的是联结条件。

例子：如果我要查看所有曾经调配班级的学生信息，以及对应的班级信息。

SELECT 
    student_id,
    student_no,
    student_name,
    class_no,
    class_name 
FROM students AS s JOIN classes AS c 
ON s.class_id = c.class_id;

后果如下：

（二）多个表的联结

SQL 对一条 SELECT 语句中能够联结的表的数目没有限度。
语法：

SELECT 
    ...
FROM tb_1,tb_2,tb_3
WHERR tb_1.f_1 = tb_2.f_1 AND tb_1.f_1 = tb_3.f_1;

四、外联结

（1）左联结
左表的行肯定会列出，右表如果没有匹配的行，那么列值就为 null。
特地须要留神的是如果右表有多行和左表匹配，那么左表雷同的行会呈现屡次。
语法：

SELECT 
    ... 
FROM tb_1 LEFT JOIN tb_2 
ON tb_1.f_1=tb_2.f_1

// tb_1 LEFT JOIN tb_2，tb_1 左联结 tb_2，意味着查问后果集中必须蕴含 tb_1 的全副记录。// 而后按指定的连贯条件与 tb_2 进行连贯。// 若 tb_2 中没有满足连贯条件的记录，则后果集中 tb_2 相应的字段填入 NULL。

例如：如果要检索出所有学生对应的班级信息。

SELECT 
    student_id,
    student_no,
    student_name,
    class_no,
    class_name 
FROM students AS s LEFT JOIN classes AS c 
ON s.class_id = c.class_id;

后果如下：

后果剖析：
students LEFT JOIN classes：students 左联结 classes，会列出 students 表的所有学生。
由 students 表可知，赵八是没有调配班级的，即 class_id 为 NULL，而 classes 表中没有满足连贯条件的记录，故后果集中 classes 相应的字段填入 NULL。
（二）右联结
和左联结相似，只不过以右表为主表而已，左联结和右联结能够互相转化。
例子：如果要检索出所有班级对应的学生信息。

SELECT 
    student_id,
    student_no,
    student_name,
    class_no,
    class_name 
FROM students AS s RIGHT JOIN classes AS c 
ON s.class_id = c.class_id;

后果如下：

后果剖析：
students AS s RIGHT JOIN classes：students 右联结 classes，会列出 classes 表的所有班级。
由 classes、students 表可知，自动化和日语班级的编号并没有呈现在 students 表，故后果集中 students 相应的字段填入 NULL。
因为班级和学生的关系是一对多，所以左表有多行和右表匹配，故右表雷同的行呈现了屡次。

五、自联结

就是一个表本人和本人联结，个别用来代替子查问。
例如：如果要检索出张三丰所在班级的所有学生。

SELECT 
    s1.student_id,
    s1.student_no,
    s1.student_name,
    s1.class_id
FROM students AS s1 JOIN students AS s2
ON s1.class_id = s2.class_id 
AND s2.student_name = '张三丰';

后果如下：

有的人可能会好奇，为什么是 AND s2.student_name = '张三丰';，而非 AND s1.student_name = '张三丰';？让咱们来看一下，把 s2 换成 s1 会失去什么后果？

在上述 SQL 语句中，只把 s2 换成 s1，却没有失去想要的后果。

既然把不同表的 name 字段当做过滤条件会造成后果的不统一，那么就先不加该条件，并且把所有字段都找进去，看看会失去什么样的后果。

SELECT 
    *
FROM students AS s1 JOIN students AS s2
ON s1.class_id = s2.class_id;

后果如下：

在此基础上，别离测试加 s2.student_name = '张三丰';、s1.student_name = '张三丰';条件看看会失去什么样的后果：

// 加 s1.student_name = '张三丰' 条件
SELECT 
    *
FROM students AS s1 JOIN students AS s2
ON s1.class_id = s2.class_id AND s1.student_name = '张三丰';

// 加 s2.student_name = '张三丰' 条件
SELECT 
    *
FROM students AS s1 JOIN students AS s2
ON s1.class_id = s2.class_id AND s1.student_name = '张三丰';

后果如下：

有点感觉了，咱们再来尝试把字段中的 s1 换成 s2，把 name 条件的表换成 s1：

SELECT 
    s2.student_id,
    s2.student_no,
    s2.student_name,
    s2.class_id
FROM students AS s1 JOIN students AS s2
ON s1.class_id = s2.class_id 
AND s1.student_name = '张三丰';

后果如下：

失去了预期的后果。
总结：在应用自联结时，查问字段的表名与过滤字段的表名必须不同。例如，在本例中查问字段为 s1.student_id 时，过滤字段的表名就必须为 s2，即 s2.name='张三丰'。

如果想理解更多，返回参考 2 查看更多细节。

六、组合查问

少数 SQL 查问都只蕴含一个或多个表中返回数据的单条 SELECT 语句，MySQL 也容许多条 SELECT 语句，并将后果作为单个查问后果集返回。

（一）应用状况

须要应用组合查问的两种根本状况：

  • 在单个查问中从不同的表返回相似构造的数据。
  • 对单个表执行多个查问，按单个查问返回数据。
（二）根本语法

语法，UNION 关键字：

SELECT ... FROM tb_1
UNION
SELECT ... FROM tb_2;

例如，如果要检索出张三丰和李四的个人信息：

SELECT 
    * 
FROM students
WHERE student_name = '张三丰'
UNION
SELECT 
    * 
FROM students
WHERE student_name = '李四';

后果如下：

留神：UNION 中的每个查问必须蕴含雷同的列、表达式或聚合函数（不过各个列不须要按雷同的秩序列出）。

（三）蕴含或勾销反复的行

UNION 会主动去除后果集中反复的行（默认去重）。

如果想返回所有匹配行，能够应用 UNION ALL。

（四）对组合查问进行排序

在用 UNION 组合查问时，只能应用一条 ORDER BY 字句，它必须呈现在最初一条 SELECT 语句之后。

SELECT ... FROM tb_1
UNION
SELECT ... FROM tb_2
ORDER BY field;

参考

  1. 《MySQL 必知必会》
  2. 《mysql 的自联结、天然联结、外部联结、等值联结、不等值联结、内部联结、穿插联结等替你整顿好了，爱看不看。》
  3. 《如何了解 SQL 中的自连贯？》
  4. 《请问数据库中的自连贯怎么了解？》
退出移动版