sql a 表 若包含b表 则a 表 列显示_SQL多表查询

摘要

表的加法#1

表的联结#2

联结应用案例#3

case表达式#4

表的加法#1

集合在数学领域表示“（各种各样的）事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。

集合运算，就是对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

表的加法——使用UNION集合运算符进行记录加法运算，通常都会除去重复的记录。

练习：查询课程号、课程名称

select 课程号,课程名称
from course
union
select 课程号,课程名称
from course1;

利用文氏图有助于理解集合运算以及表的不同类型的联结

若想保留重复的记录，在union后加all

练习：查询课程号、课程名称，保留所有记录

select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course1;

集合运算的注意事项：

1、作为运算对象的记录的列数必须相同；

2、作为运算对象的记录中列的类型必须一致；

3、可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次

表的联结#2

各表之间的关系，观察学生表、成绩表、课程表、教师表这4张表是通过什么字段联结起来的，下面的练习来自4张表中的数据

联结（join）：交叉联结、内联结、左联结、右联结、全联结

交叉联结（笛卡尔积、cross join）：结果的行数是两张表中行数的乘积，交叉联结的结果太多，实际操作中较少用到，但交叉联结是所有联结运算的基础。他们是在交叉联结的基础上加了其他过滤条件。

对满足相同规则的表进行交叉联结的集合运算符是CROSS JOIN（笛卡儿积）。进行交叉联结时无法使用内联结和外联结中所使用的ON子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。

内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”。

交叉联结没有应用到实际业务之中的原因有两个。一是其结果没有实用价值，二是由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。

内联结（inner join）：内联结的运行顺序是先在联结的表中取出符合条件的行，然后进行交叉联结。

代码运行顺序

数据联结过程

在使用过程中需要遵循一定的书写规范，不同子句的注意事项如下：

1、关于FROM子句，之前的FROM子句中只有一张表，而这次我们同时使用了student和score两张表。

使用关键字INNER JOIN就可以将两张表联结在一起了。可使用AS为两张表设置别名，虽然别名并不是必需，但由于表名太长会影响SQL语句的可读性，因此应养成使用别名的习惯。

2、关于ON子句，ON后面是联结条件。可以在ON之后指定两张表联结所使用的列（联结键），也就是说，ON是专门用来指定联结条件的，它能起到与WHERE相同的作用。需要指定多个键时，同样可以使用AND、OR。在进行内联结时ON子句是必不可少的（如果没有ON会发生错误），并且ON必须书写在FROM和WHERE之间。联结条件也可以使用“=”来记述。在语法，还可以使用<=和BETWEEN等谓词。实际应用中九成以上都可以用“=”进行联结，使用“=”将联结键关联起来，就能够将两张表中满足相同条件的记录进行“联结”了。

3、关于SELECT子句，在SELECT子句中指定的列。使用“< 表的别名 >.< 列名 >”的形式来指定列。和使用一张表时不同，由于多表联结时，某个列到底属于哪张表比较容易混乱，因此采用了这样的防范措施。

练习：使用inner join联结学生表、成绩表、课程表

-- 查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号,a.姓名,c.课程号,c.课程名称
from student as a inner join score as b on a.学号=b.学号
inner join course c on b.课程号=c.课程号;

通过上图可以看到学生表、成绩表、课程表的联结键分别是：学生表.学号、成绩表.学号、成绩表.课程号、课程表.课程号，通过这四个联结键把这三张表联结起来。根据内联结的运行顺序，成绩表中的学号与课程号分别与学生表中的学号，课程表中的课程号一一对应，因此，最终输出结果是笛卡尔积：1*8*1=8行数据。

左联结（left join）、右联结（right join）：

两个联结均属于外联结（OUTER JOIN），通过ON子句的联结键把两张表进行联结，并从两张表中同时选取相应的列。外联接与内联结的使用方法并没有什么不同，只是结果却有所不同。

在实际的业务中，外联接生成固定行数的数据，如果使用内联结，根据SELECT语句执行时的状况不同，结果的行数也会发生改变，生成的数据的版式也会受到影响，而使用外联结能够得到固定行数的结果。

外联结名称的由来也跟NULL有关，即“结果中包含原表中不存在（在原表之外）的信息”。相反，只包含表内信息的联结也就被称为内联结了。

外联结还有一点非常重要，那就是要把哪张表作为主表。最终的结果中会包含主表内所有的数据。指定主表的关键字是LEFT和RIGHT。顾名思义，使用LEFT时FROM子句中写在左侧的表是主表，使用RIGHT时右侧的表是主表。

练习：以学生表作为主表，联结成绩表

select a.学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号;

以下为左联结（left join）运行顺序，数据有所删减，但原理一致。

练习：以学生表作为主表，联结成绩表，查找成绩表中为空值的行

select a. 学号,a.姓名,b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is null;

以下为左联结运行过程，数据有所删减，但原理是一样的，使用where指定成绩表中的联结键‘学号’为空值后，得出的结果如文氏图显示：左边圈不与右边圈重叠的红色部分。

练习：查询所有学生的学号、姓名、选课数、总成绩,

-- select语句中的学号需要标明是哪个表的，不然无法识别会导致报错
select a.学号,姓名,count(课程号)as 选课数,sum(成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;

练习：使用right join以成绩表作为主表，查看学号、姓名、及课程号，观察与左联结的区别

select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号;

练习：使用right join以成绩表作为主表，查看学号、姓名、及课程号，使用where指定学生表中的联结键‘学号’为空值，观察与左联结有什么区别

select a.学号,a.姓名,b.课程号
from student as a right join score as b
on a.学号=b.学号
-- student表中没有空值，因此添加“where a.学号 is null”条件后，结果显示空值
where a.学号 is null;

使用where指定学生表中的联结键‘学号’为空值后，得出的结果如文氏图显示：不与左边圈重叠的红色部分。

全联结（full join）：得到的结果如文氏图中包括重叠与不重叠的红色部分。

通过文氏图理解不同的sql联结模式

联结应用案例#3

如何用sql解决业务问题，同样是分三步：

1）翻译成大白话

2）写出分析思路

3）写出对应的sql子句

练习：查询所有学生的学号、姓名、选课数、总成绩

1）翻译成大白话

2）写出分析思路

3）写出对应的sql子句

select s1.学号,s1.姓名,count(课程号)as 选课数,sum(成绩)as 总成绩
from student as s1 left join score as s2 on s1.学号=s2.学号
group by s1.学号;

练习： 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

翻译成大白话并写出分析思路：

1）查询出所有学生的学号，姓名，平均成绩

学号，姓名（在学生表student），

平均成绩（每个学生的平均成绩：在成绩表score，）

按学号分组，平均成绩：avg（成绩）

2）平均成绩>85

写出对应的sql子句：

select a.学号,a.姓名,round(avg(b.成绩),2)as 平均成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号
having avg(b.成绩)>85;

练习：查询所有学生的学号、姓名、平均成绩

-- 若没有指定哪个表的学号、姓名、成绩，将会报错
select a.学号,a.姓名,avg(b.成绩)as 平均成绩
from student as a left join score as b
on a.学号=b.学号
group by a.学号;

练习：查询学生的选课情况：学号、姓名、课程号、课程名称

select s1.学号,s1.姓名,s2.课程号,课程名称
from student as s1 left join score as s2 on s1.学号=s2.学号
left join course as c on s2.课程号=c.课程号
ORDER BY s1.学号,s2.课程号

学生表、成绩表、课程表的联结示意图

以下练习来自SQLZOO《The JOIN operation》：

The JOIN operation/zh​sqlzoo.net

表格信息

SQLZOO练习8：找非德國球員的入球，德國可以在賽事中作team1 隊伍１（主）或team2隊伍２（客）。 你可以用teamid!='GER' 來防止列出德國球員。 你可以用DISTINCT來防止球員出現兩次以上。（使用distinct防止球员出现两次以上，teamid!='GER' 则可以防止列出德国球员）

SELECT distinct(player)
  FROM game left JOIN goal ON matchid = id
WHERE teamid!='GER' and
(team1='GER' or team2='GER');

SQLZOO练习11：每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

SELECT matchid,mdate,count(matchid)
  FROM game JOIN goal ON matchid = id
where team1='POL' or team2='POL'
group by matchid,mdate;

SQLZOO练习12：每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

select matchid,mdate,count(teamid)
from game join goal on id=matchid
where (team1='GER' or team2='GER')
and teamid='GER'
group by matchid,mdate;

case表达式#4

CASE表达式分为简单CASE表达式和搜索CASE表达式两种。搜索CASE表达式包含简单CASE表达式的全部功能。

虽然CASE表达式中的ELSE子句可以省略，但为了让SQL语句更加容易理解，建议不要省略。

CASE表达式中的END不能省略。使用CASE表达式能够将SELECT语句的结果进行组合。

虽然有些DBMS提供了各自特有的CASE表达式的简化函数，例如Oracle中的DECODE和MySQL中的IF，等等，但由于它们并非通用的函数，功能上也有些限制，因此有些场合无法使用。

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。但是，由于搜索CASE表达式包含了简单CASE表达式的全部功能，因此只需大致了解简单CASE表达式。

ELSE子句也可以省略不写，这时会被默认为ELSE NULL。但为了防止有人漏读，还是希望大家能够显示地写出ELSE子句。

搜索CASE表达式

CASE WHEN <判断表达式> THEN <表达式>

WHEN <判断表达式> THEN <表达式>

WHEN <判断表达式> THEN <表达式>

. . .

ELSE <表达式>END

WHEN子句中的“< 判断表达式 >”就是类似“列=值”这样，返回值为真值（TRUE/FALSE/UNKNOWN）的表达式。我们也可以将其看作使用=、!=或者LIKE、BETWEEN等谓词编写出来的表达式。

CASE表达式会从对最初的WHEN子句中的“< 判断表达式 >”进行求值开始执行。所谓求值，就是要调查该表达式的真值是什么。如果结果为真（TRUE），那么就返回THEN子句中的表达式，CASE表达式的执行到此为止。如果结果不为真，那么就跳转到下一条WHEN子句的求值之中。如果直到最后的WHEN子句为止返回结果都不为真，那么就会返回ELSE中的表达式，执行终止。

从CASE表达式名称中的“表达式”我们也能看出来，上述这些整体构成了一个表达式。并且由于表达式最终会返回一个值，因此CASE表达式在SQL语句执行时，也会转化为一个值。虽然使用分支众多的CASE表达式编写几十行代码的情况也并不少见，但是无论多么庞大的CASE表达式，最后也只会返回单一简单的值。

练习：查询学号、课程号、成绩，以及显示是否及格

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

运行顺序：

练习：查询出每门课程及格与不及格的人数

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 课程号;

练习：使用分段【100-85】、【85-70】、【70-60】、【<60】来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

select a.课程号,b.课程名称,
sum(case when 成绩 between 85 and 100
then 1 else 0 end)as '【100-85】',
sum(case when 成绩 >=70 and  成绩<85
then 1 else 0 end)as '【85-70】',
sum(case when 成绩 >=60 and  成绩<70
then 1 else 0 end)as '【70-60】',
sum(case when 成绩 <60 then 1 else 0 end)as '【<60】'
from score as a right join course as b
on a.课程号=b.课程号
-- 当用多个列来分组时，这几个列的值全部相同才算一组
group by a.课程号,b.课程名称

以下练习来自SQLZOO《The JOIN operation》：

The JOIN operation/zh​sqlzoo.net

SQLZOO练习13：Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT mdate,team1,
sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END )as score1,
team2,
sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END )as score2
-- 这里必须用左外连接，因为可能会有一场比赛双方都没有进球的情况，使用sum运算符时如果没有记录将不会显示该条信息
FROM game left JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2;

---

以下练习来自SQLZOO《More JOIN operations》：

More JOIN operations/zh​sqlzoo.net

《SQLZOO——More JOIN operation》练习题8：顯示電影異型'Alien' 的演員清單。大于两张表查询时可以通过建立子查询来查询其他表的信息

SELECT name
FROM actor
WHERE id IN (
  SELECT actorid
  FROM movie as m JOIN casting as c ON m.id = c.movieid
  WHERE id = (
    SELECT id
    FROM movie
    WHERE title LIKE 'Alien'))

-- 通过两个join也能实现3个表的链接
SELECT name
  FROM actor as a 
left join casting as c ON a.id = c.actorid
left join movie as m on c.movieid=m.id
WHERE m.title LIKE 'Alien'

练习题10：列出演員夏里遜福 'Harrison Ford' 曾演出的電影，但他不是第1主角。

select title
from movie
where id in(select movieid
from casting as c left join actor as a
on c.actorid=a.id
where name =(select name
from actor
where name='Harrison Ford')
and ord<>1)

练习题11：列出1962年首影的電影及它的第1主角。

SELECT m.title, a.name
FROM casting c
JOIN movie m ON (m.id= c.movieid)
JOIN actor a ON (a.id= c.actorid)
WHERE c.ord = 1 AND yr = 1962;

练习题12：尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) FROM
-- from子句里的子查询需要使用括号引用，并添加as+子查询名称
(SELECT yr,COUNT(title) AS c FROM
   movie JOIN casting ON movie.id=movieid
         JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr) AS t
)

练习题13：列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。

是否列了電影 "Little Miss Marker"兩次?

她於1980再參與此電影Little Miss Marker. 原作於1934年,她也有參與。 電影名稱不是獨一的。在子查詢中使用電影編號。

select title,name
from actor join casting on actor.id=casting.actorid
join movie on casting.movieid=movie.id
where ord='1' and movieid in(
SELECT movieid FROM casting
WHERE actorid IN (
  SELECT id FROM actor
  WHERE name='Julie Andrews'))

练习题14：列出按字母順序，列出哪一演員曾作30次第1主角。

SELECT DISTINCT(name) /* DISTINCT 去重 */
FROM casting c
JOIN actor a ON (a.id= c.actorid)
WHERE c.actorid IN (
  SELECT actorid  /* 获取30次第1主角 */
  FROM casting c
  WHERE ord = '1'
  GROUP BY actorid  /* GROUP BY + COUNT 统计次数 */
  HAVING COUNT(ord) >= 30)  /* HAVING 获取大于30的次数 */
  ORDER BY name

练习题15：列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。

方法一：

select title,count(actorid) from
movie as m join casting as c on(m.id=c.movieid)
where yr='1978'
group by title
order by count(actorid) desc,title

方法二：

select title,name_sum from
movie as m join (
select movieid,count(name) as name_sum/* 首先制作movieid和角色数目的表 */
from casting as c join actor as a on(c.actorid=a.id)
join movie as m on(c.movieid=m.id)
where yr='1978'
group by movieid)as b
on m.id=b.movieid
order by name_sum desc,title

练习题16：列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名。

select name from
actor as a join casting as c on(a.id=c.actorid)
where movieid in(
select movieid
from casting as c join actor as a on(c.actorid=a.id)
where name='Art Garfunkel')
and name<>'Art Garfunkel'