四时宝库

程序员的知识宝库

2024年10月22日

2周零基础搞定SQL——多表查询（sql数据库多表查询示例）



在上一篇文章里，我们学习了SQL的复杂查询，但是依然只是针对一个表的，但在实际工作中，我们需要的数据，往往分布在多个表中，所以为了更好的解决工作中的实际问题，今天我们一起来学习如何用SQL进行多表查询吧！

1.表的加法

在之前的学习中，我们建了一张表course，大家还记得吗？

接下来我们再数据库中再添加一张表course1，如下：

如果大家忘记了如何新建表并向表中插入数据，就回到何书365：轻松搞定数据分析之MySQL——零基础入门里面再复习一下吧！

当然你也可以按如下步骤操作来添加course1：

右键，复制表，结构和数据，得到复制的表course_copy

重命名，将表名course_copy重命名为course1

右键，打开表，对数据进行修改并保存。

添加完course1这张表，现在在school数据库中，我们就有了两张关于课程信息的表。大家可以看到course和course1这两张表的结构是一样的，不同的是两张表内在图片红框标出的地方存放的数据是不一样的。

下面，我们用这两张表来学习表的加法，来把这两张表中的数据组合在一起。表的加法，我们要用UNION来实现。

UNION的作用 UNION运算符用于组合两个或更多SELECT语句的结果集。

UNION使用前提UNION中的每个SELECT语句必须具有相同的列数

  • 这些列也必须具有相似的数据类型
  • 每个SELECT语句中的列也必须以相同的顺序排列

UNION 语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

下面，我们以上面两张课程表为栗子，看一下union的用法：

SELECT 课程号,课程名称
from course
UNION
select 课程号,课程名称
FROM course1;

从结果表中可以看到，course和course1两张表中不同的数据合并在一起了（红框内的数据），而且绿框中为两张表中重复的数据，SQL会把重复值删除，只保留一个值。

如果允许重复值，请使用 UNION ALL。

UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

※ 注：UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名。

SELECT 课程号,课程名称
from course
UNION ALL
select 课程号,课程名称
FROM course1;

如结果所示，两个表中的共有的数据“0001，语文”的重复数据都被保留下来了。

总结：

  • UNION就是将多段功能类似的SQL连接起来，并且可以去掉重复的行，有DISTINCT的功能。
  • UNION ALL则只是单纯的将多段类似SQL连接起来而且。他们的好处是可以将复杂SQL按不同的功能或作用拆分成一小段SQL进行拼接，可以有效提高查询效率。

2.表的联结

学习完了表的加法，接下来我们来学习表的联结。

关系型数据库是由多张表组成的，如下图，即为school数据库中的四张表：

看到这四张表，你能发现他们之间有什么关系吗？

还记得我们在之前的文章里面学习的主键的概念吗？我们来看student表和score表两表之间的关系，两张表就是通过学号这个主键关联起来的。为了更清楚的表现这两张表的关系，图片里我用相同颜色将同一个学生的信息框选起来。

假如我想知道学号为0001的学生的成绩是多少，我就可以在score表中查找到学号为0001的学生的课程号和成绩（一共有3行数据）。所以关系就是表之间可以根据主键进行对应的匹配，在关系型数据库中，这种关系的学名叫做联结（join）。

school数据库中四张表联结关系图

JOIN连接的作用

JOIN 连接用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

不同的 JOIN

在我们继续讲解实例之前，我们先列出可以使用的不同的 SQL JOIN 类型：

  • CROSS JOIN（交叉联结）：没有条件的join，返回被连接的两个表所有数据行的笛卡尔积；
  • INNER JOIN（内联结）：如果表中有至少一个匹配，则返回行；
  • LEFT JOIN（左联结）：即使右表中没有匹配，也从左表返回所有的行；
  • RIGHT JOIN（右联结）：即使左表中没有匹配，也从右表返回所有的行；
  • FULL JOIN（全联结）：只要其中一个表中存在匹配，则返回行。

CROSS JOIN（交叉联结）

返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

SELECT * FROM A CROSS JOIN B

交叉联结在实际工作中运用的比较少，因为返回的结果行数太多了，表太大，计算量大，效率非常低，不建议使用。但交叉联结是所有联结的基础，接下来学习的几种联结都是在交叉联结的基础上加了特定的条件。

INNER JOIN（内联结）

从多个表中返回满足 JOIN 条件的所有行，语法如下

SELECT column_name(s)FROM table1
INNER JOIN table2 ON 
table1.column_name = table2.column_name;

举颗栗子：

对于这两个表，内联结就是查找出同时存在与这两张表中的数据（绿框中数据），并返回所有行，红框中数据在score表中没有，则不返回。

SELECT a.学号,a.姓名,b.课程号,b.成绩 -- 3 取出表a的学号、姓名列，取出b表的课程号、成绩两列。
from student as a INNER JOIN score AS b -- 1 同时使用两张表，并重命名
on
a.学号=b.学号; -- 2 两个表的联结条件

LEFT JOIN（左联结）

SQL左链接LEFT JOIN关键字返回左表（表1）中的所有行，即使在右表（表2）中没有匹配。如果在正确的表中没有匹配，结果是NULL。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或（在一些数据库中，LEFT JOIN称为LEFT OUTER JOIN）

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

栗子：

查询学生表中所有学生的课程号和成绩

SELECT a.学号,a.姓名,b.课程号,b.成绩
from student as a LEFT JOIN score AS b
ON
a.学号=b.学号;

我们发现“0004，王思聪”没有对应的课程号和成绩，是因为他们没有在score表中存在，没有匹配上他们的信息。但是由于是左连接，就把主表student的信息全部显示出来了，就是对应上图的table1。

那么我们如何实现下图的效果呢？也就是如何在左联结的基础之上去掉两个表中共同的部分。

很简单，就是在上面的基础上添加一个where子句，来筛选出表2中没有匹配的信息，再返回主表信息。

SELECT a.学号,a.姓名,b.课程号,b.成绩
from student as a LEFT JOIN score AS b
ON
a.学号=b.学号
WHERE b.学号 is NULL;

RIGHT JOIN（右联结）

SQL右链接 RIGHT JOIN 关键字返回右表（table2）的所有行，即使在左表（table1）上没有匹配。如果左表没有匹配，则结果为NULL。

SELECT column_name(s) FROM table1
RIGHT JOIN table2 ON 
table1.column_name = table2.column_name;

在一些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

右连接与左连接的主表刚好相反，会将table2中的数据完全显示，如果table1中没有匹配上的就不显示。

这里我们就不举栗子啦。那么你可能会问左右联结有何区别呢？又如何选择呢？

右联结和左联结本质上是相同的，只是指定的主表会有所差别，究竟是左联结还是右联结应该根据实际需求来决定，作为主表，需要做到的是，要覆盖所有查询主体（什么是查询主体，比如，我们要查询所有学生的成绩，课程号，.......，这个里面的主体就是学生），如果各表覆盖的主体个数没有差异，左右联结和内联结没有区别。

通俗的讲，就是选哪个为主表，就用什么联结：

  • left以 left join 左侧的表为主表
  • right 以 right join 右侧表为主表
  • inner join 查找的数据是左右两张表共有的。

FULL JOIN（全联结）

当左（表1）或右（表2）表记录匹配时，FULL OUTER JOIN关键字将返回所有记录。 注意： FULL OUTER JOIN可能会返回非常大的结果集！且MySQL不支持全联结。但可以通过union来实现。

SELECT column_name(s) FROM table1
FULL OUTER JOIN table2 ON 
table1.column_name = table2.column_name;

mysql> select * from A left join B on B.name = A.name 
    -> union 
    -> select * from A right join B on B.name = A.name;

全连接就是将table1和table2的内容完全显示，不管有没有匹配上。

好啦，到这里，SQL的所有联结就讲完啦！是不是有点方？不怕！下面一张图，我们把前面学的联结来一个总结：

忘的时候看一看，SQL联结So easy！

JOIN在SQL中占有重要的地位，通过JOIN我们可以将有匹配关系的两张表或更多表进行关联，来获取我们想要的数据。关联的方式也比较灵活，常用的就是INNER JOIN，请务必掌握它。接下来，我们就来学以致用吧！

3.联结应用案例

在开始案例实操之前再回忆一下用SQL解决业务问题的步骤，时刻谨记，不要忘了哦！

a).把业务问题解读成通俗易懂的大白话；

b).写出分析思路（按步骤分解）；

c).写出对应的SQL子句。

栗子1：查询所有学生的学号、姓名、选课数、总成绩。

a).把业务问题解读成通俗易懂的大白话：

查询学生的学号、姓名，对每个学生所选的课程号计数，并对每个学生的成绩求和。

b).写出分析思路（按步骤分解）：

  • 提取关键字：学号，姓名，选课数，总成绩
  • 学生姓名、学号——>student表
  • 选课数——>每个学生的选课数：score表，按学号分组group by，并对课程号计数count
  • 总成绩——>每个学生的总成绩：score表，按学号分组group by，并对成绩求和sum
  • 学生是查询主体，student 和score表通过学号联结，且为左联结。

c).写出对应的SQL子句:

SELECT s1.学号,s1.姓名,count(课程号) as 选课数,SUM(成绩) as 总成绩
from student as s1  LEFT JOIN score AS s2
ON
s1.学号=s2.学号
GROUP BY s1.学号;

栗子2：查询平均成绩大于85的所有学生的学号，姓名和平均成绩

a).把业务问题解读成通俗易懂的大白话：

查询学生的学号、姓名、平均成绩，对每个学生的成绩求平均值，并选出大于85的数据。

b).写出分析思路（按步骤分解）：

  • 提取关键字：学号，姓名，平均成绩
  • 学生姓名、学号——>student表
  • 平均成绩——>每个学生的平均成绩：score表，按学号分组group by，并求对成绩求平均值avg
  • 学生是查询主体，student 和score表通过学号联结，且为左联结。

c).写出对应的SQL子句:

SELECT s1.学号,s1.姓名,AVG(s2.成绩) as 平均成绩
from student as s1  LEFT JOIN score AS s2
ON
s1.学号=s2.学号
GROUP BY s1.学号
HAVING 平均成绩>85;

栗子3：查询学生的选课情况：学号，姓名，课程号，课程名称

a).把业务问题解读成通俗易懂的大白话：

查询学生的学号、姓名、课程号，课程名称。

b).写出分析思路（按步骤分解）：

  • 提取关键字：学号、姓名、课程号，课程名称
  • 学生姓名、学号——>student表
  • 课程号，课程名称——>course表
  • 课程号——>引入score表。因为student表和course表没有共同关键字，无法产生联结。
  • 学生是查询主体，student 和score表通过学号左联结，score表和course表通过课程号左联结。

c).写出对应的SQL子句:

SELECT s1.学号,s1.姓名,c1.课程号,c1.课程名称
from student as s1  LEFT JOIN score AS s2
ON
s1.学号=s2.学号
LEFT JOIN course as c1
ON
s2.课程号=c1.课程号;

这三颗栗子都是非常常见的面试题，宝宝们一定要亲自操作一遍哦！完成后，我们继续学习！

4.case表达式

SQL中的case表达式的作用是用来对“某个变量”进行某种转化，使用case可以实现不同条件的统计。

case表达式很像我们的if else的作用，在发现为真的WHEN子句时，CASE表达式的真假值判断就会中止，执行then语句中的命令，而剩余的WHEN子句会被忽略。如果所有的WHEN子句都为假值，则执行else子句。

CASE具有两种格式，简单CASE函数和CASE搜索函数。这两种方式，大部分情况下可以实现相同的功能。

简单case函数

CASE column 
WHEN <condition> THEN value 
WHEN <condition> THEN value
......
ELSE value END

CASE搜索函数

CASE 
WHEN <condition> [,<condition>] THEN value 
WHEN <condition> [,<condition>] THEN value
......
ELSE value
END

需要注意的是：

* else这句非必须但最好带上，如果when中没有符合条件的，且有没有else控制，那么，这样的case会返回null，null不是个好东西，所以你一定要尽量控制减少返回null的情况；

*end子句不可以省略不写；

* 简单点说，在能写列名和常量的地方，都可以写 CASE 表达式，因为它返回的是一个标量值，通常我们在select子句中使用。

这么说起来貌似有点抽象，我们还是上栗子吧！

栗子1：判断学生的成绩是否及格（>=60及格，<60不及格）

SELECT 学号,课程号,成绩,
(CASE when 成绩>=60 then '及格'
      when 成绩<60 then '不及格'
      else NULL
end ) as 是否及格
from score;

上面的case语句执行逻辑：若第一个WHEN子句为真，则CASE表达式的真假值判断就会中止，执行then语句中的命令，而剩余的WHEN子句会被忽略。若第一个WHEN子句为假值，则执行第二个WHEN子句，以此类推。如果所有的WHEN子句均为假值，则执行ELSE子句。

栗子2：查询出每门课程的及格人数和不及格人数

SELECT 课程号,
SUM(CASE when 成绩>=60 then 1
    else 0
end) as 及格人数,
SUM(CASE when 成绩<60 then 1
    else 0
end)as 不及格人数
from score
GROUP BY 课程号;

先运行group by对数据进行分组，再运行case表达式，得到每一行的及格人数：如果这一行的成绩>=60，就将这一行的及格人数值设置为1，否则则设置为0。表示这一行及格人数为1，不及格人数为0；最后通过sum函数，对及格人数进行求和，同理可得不及格人数。

栗子3：使用分段{100-85}、{85-70},{70-60}，{<60}来统计各科成绩，根据课程号、课程名称统计各分段人数。

a).把业务问题解读成通俗易懂的大白话：

按课程号和课程名称分组，统计成绩在{100-85}、{85-70},{70-60}，{<60}的分别有多少人。

b).写出分析思路（按步骤分解）：

  • 提取关键字：课程号、课程名称、各分段成绩
  • 课程号，课程名称——>course表
  • 课程号、成绩——>score表
  • 课程（课程号、课程名称）是查询主体，course表和score表通过课程号左联结（这里设定course表在左，为主表，所以是左联结。左右是相对于主表的概念，灵活选择即可）。
  • 成绩分为{100-85}、{85-70},{70-60}，{<60}几个区间，为针对具体行数据（记录）的非常规分组，使用case语句。
  • 各科成绩——>根据课程号/课程名称进行常规分组，group by语句。

c).写出对应的SQL子句:

SELECT s1.课程号,c1.课程名称,
sum(case when 成绩 between 85 and 100 then 1
		else 0
		end ) as '{100,85}',
sum(case when 成绩 between 70 and 85 then 1
		else 0
		end ) as '{85,70}',
sum(case when 成绩 between 60 and 70 then 1
		else 0
		end ) as '{70,60}',
sum(case when 成绩 < 60 then 1
		else 0
		end ) as '{<60}'
from course as c1 LEFT JOIN score as s1
ON
c1.课程号=s1.课程号
GROUP BY s1.课程号,c1.课程名称;

这里面需要注意的是凡是在group by后面出现的字段，必须同时在select后面出现；凡是在select后面出现的、同时未在聚合函数中出现的字段，必须同时出现在group by后面。

由于查询的结果要求有课程名称，所以group by中也要加上课程名称。group by里面加上课程名称的前提是不影响分组结果。这里面课程号和课程名称是一一对应的，所以group by子句中添加课程名称对分组结果是没有影响的。因为对于group by 子句，当用多个列来分组时，这几个列的值全部相同才算一组。

总结：CASE是我们在日常工作中使用非常频繁的一个功能，可以很好的将我们需要的数据单独的显示在一列里面，有助于对数据有个比较清晰的掌握。与Excel的转置有点类似，但是其功能的多样性又比Excel更强一点。

好啦，到这里，今天的学习内容就全部结束啦！关于多表查询你掌握了多少？接下来，例行的饭后甜点又来啦！

SQLzoo练习

欢迎来到SQLZOO开始今天的练习：https://sqlzoo.net/

好啦，今天的内容就学到这里啦！最后依然是一张思维导图帮助你搭建知识体系：

祝你早安，午安，晚安！See you next time！

标签：sqlwhere多个条件 

作者:sishi88 , 分类:技术教程 , 浏览:7 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言
    友情链接