SQL 基础之多表查询

一、前言

SQL 最强大的功能之一就是能在数据检索查询的执行中联结表。联结是利用 SQL 的 SELECT 执行的最重要的操作,很好地理解联结及其语法是学习 SQL 的一个极为重要的组成部分。

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

E-R 图如下:

SQL 基础之多表查询

学生表如下,如果学生已经分配了班级,class_id 列则为分配班级的编号,否则为 NULL(意为未分配):

SQL 基础之多表查询

班级表如下:

SQL 基础之多表查询

三、内联结

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

(一)两个表之间的联结

语法:


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 基础之多表查询

(二)多个表的联结

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;

结果如下:

SQL 基础之多表查询

结果分析:

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;

结果如下:

SQL 基础之多表查询

结果分析:

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 = '张三丰';

结果如下:

SQL 基础之多表查询

有的人可能会好奇,为什么是 AND s2.student_name = '张三丰';,而非 AND s1.student_name = '张三丰';?让我们来看一下,把 s2 换成 s1 会得到什么结果?

SQL 基础之多表查询

在上述 SQL 语句中,只把 s2 换成 s1,却没有得到想要的结果。

既然把不同表的 name 字段当做过滤条件会造成结果的不一致,那么就先不加该条件,并且把所有字段都找出来,看看会得到什么样的结果。


SELECT

    *

FROM students AS s1 JOIN students AS s2

ON s1.class_id = s2.class_id;

结果如下:

SQL 基础之多表查询

在此基础上,分别测试加 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 = '张三丰';

结果如下:

SQL 基础之多表查询

有点感觉了,我们再来尝试把字段中的 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 = '张三丰';

结果如下:

SQL 基础之多表查询

得到了预期的结果。

总结:在使用自联结时,查询字段的表名与过滤字段的表名必须不同。例如,在本例中查询字段为 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 = '李四';

结果如下:

SQL 基础之多表查询

注意: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. 《请问数据库中的自连接怎么理解?》

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!