sql 减法_运用SQL对数据进行集合运算

最新推荐文章于 2021-04-04 15:51:57 发布

weixin_39951929

最新推荐文章于 2021-04-04 15:51:57 发布

阅读量8k

点赞数 1

文章标签： sql 减法 sql减法语句 sql相加运算

一、集合运算

集合在数学领域表示“(各种各样的)事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。因此，可以将集合运算理解为对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。

二、表的加减法

2.1 表的加法(语法：UNION/INTERSECT)

在学习具体的使用方法之前，首先添加一张表，该表的结构与之前的classes表相同，只是表名变为classes2：

CREATE TABLE classes2(
id VARCHAR(15) PRIMARY KEY,
c_name VARCHAR(15),
t_id VARCHAR(15)
);

接下来，添加数据到classes2表：

INSERT INTO `classes2`(id, c_name, t_id) VALUES('0001', 'chinese', '0002');
INSERT INTO `classes2`(id, c_name, t_id) VALUES('0004', 'computer', '0004');
INSERT INTO `classes2`(id, c_name, t_id) VALUES('0005', 'database', '0005');

至此，完成了全部的准备工作。

2.1.1 两表相加取并集(语法：UNION)

SELECT id, c_name FROM classes
UNION
SELECT id, c_name FROM classes2;

注意：运用UNION等集合运算符通常都会除去重复的记录。

如果希望输出的结果中包含重复的数据，则需要使用UNION ALL对两个表加总：

SELECT id, c_name FROM classes
UNION ALL
SELECT id, c_name FROM classes2;

UNION ALL 类似于 FULL JOIN。

集合运算符 UNION 为了排除掉重复行，默认地会发生排序，而加上可选项 ALL 之后，就不会再排序，所以性能会有提升。这是非常有效的用于优化查询性能的方法，所以如果不关心结果是否存在重复行，或者确定结果里不会产生重复行，加上可选项 ALL 会更好些。

应用：检查集合相等性

在迁移数据库的时候，或者需要比较备份数据和最新数据的时候，我们需要调查两张表是否相等。这里说的“相等”指的是行数和列数以及内容都相同，即“是同一个集合”的意思。例如，下面的表 tbl_A 和 tbl_B，虽然名称不同，但是是同一个集合。

名字不同但内容相同的两张表

SELECT 
 CASE WHEN COUNT(*) = (SELECT COUNT(*) FROM tbl_A)
	  AND  COUNT(*) = (SELECT COUNT(*) FROM tbl_B)
      THEN 'all_the_same'
      ELSE 'has_difference' END AS 'check'
FROM
 (SELECT * FROM tbl_A
  UNION
  SELECT * FROM tbl_B)
AS x;

2.1.2 两表相加取交集(语法：INTERSECT)

SELECT id, c_name FROM classes 
INTERSECT 
SELECT id, c_name FROM classes2;

与使用AND可以选取出一张表中满足多个条件的公共部分不同，INTERSECT应用于两张表，选取出它们当中的公共记录。

INTERCEPT 类似于 INNER JOIN。

2.1.3 注意事项

作为运算对象的记录的列数必须相同
作为运算对象的记录中列的类型必须一致
可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次

2.2 记录的减法(语法：EXCEPT)

SELECT id, c_name FROM classes 
EXCEPT 
SELECT id, c_name FROM classes2;

该代码含义为：只包含classes表中记录除去classes2表中记录之后的剩余部分。

EXCEPT有一点与UNION和INTERSECT不同，那就是在减法运算中减数和被减数的位置不同，所得到的结果也不相同。4 + 2 和 2 + 4 的结果相同，但是 4 - 2 和 2 - 4 的结果却不一样。因此，我们将之前 SQL 中的classes和classes2互换，也会得到不同的结果。

注意：MySQL 不支持 EXCEPT

EXCEPT 类似于外联结(LEFT/RIGHT JOIN) + 取 NULL 值。

标准 SQL 规定，INTERSECT 比 UNION 和 EXCEPT 优先级更高。因此，当同时使用 UNION 和 INTERSECT，又想让 UNION 优先执行时，必须用括号明确地指定运算顺序。

三、表的联结(JOIN)

UNION、INTERSECT等集合运算的特征就是以行方向为单位进行操作。通俗地说，就是进行这些集合运算时，会导致记录行数的增减。使用UNION会增加记录行数，而使用INTERSECT或者EXCEPT会减少记录行数，但是这些运算不会导致列数的改变。作为集合运算对象的表的前提就是列数要一致。因此，运算结果不会导致列的增减。

相反，联结(JOIN)运算是将其他表中的列添加过来，进行“添加列”的运算。该操作通常用于无法从一张表中获取期望数据(列)的情况。在实际工作中，期望得到的数据往往会分散在不同的表之中。使用联结就可以从多张表中选取数据了。

表的联结可具体细分为交叉联结(CROSS JOIN)，内联结(INNER JOIN)，左联结(LEFT JOIN)，右联结(RIGHT JOIN) 和全联结(FULL JOIN)。

3.1 交叉联结

3.1.1 语法: CROSS JOIN

交叉联结是对两张表中的全部记录进行交叉组合，又称为笛卡尔积，其结果中的记录数通常是两张表中行数的乘积。

交叉联结是所有联结运算的基础，但是这种联结在实际业务中并不会使用，这其中有两个原因：一是其结果没有实用价值；二是由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。

SELECT [column name1], ... [column nameN] FROM [table name 1] AS a 
CROSS JOIN [table name 2] AS b;

3.1.2 在交叉表里制作嵌套式表侧栏

例如：下表 TblPop 是一张按照县、年龄层级和性别统计的人口分布表:

TblPop表

此外，还有 TblAge 和 TblSex 两张表：

TblAge表

TblSex表

要求根据表 TblPop 生成如下“包含嵌套式表侧栏”的交叉表：

查询结果

SELECT b.age_range, b.sex,
SUM(CASE WHEN a.pref_name IN ('秋田', '青森') THEN a.population ELSE NULL END) AS 'pop_tohoku',
SUM(CASE WHEN a.pref_name IN ('东京', '千叶') THEN a.population ELSE NULL END) AS 'pop_kanto'
FROM TblPop AS a
RIGHT JOIN
(SELECT age_range, age_class, sex_cd, sex
 FROM TblAge CROSS JOIN TblSex) AS b 
ON a.age_class = b.age_class AND a.sex_cd = b.sex_cd
GROUP BY b.age_range, b.sex
ORDER BY b.age_range, b.sex;

注意：使用如下外联结代码查询出来的结果是错误的：

SELECT b.age_range, c.sex,
SUM(CASE WHEN a.pref_name IN ('秋田', '青森') THEN a.population ELSE NULL END) AS 'pop_tohoku',
SUM(CASE WHEN a.pref_name IN ('东京', '千叶') THEN a.population ELSE NULL END) AS 'pop_kanto'
FROM TblPop AS a
RIGHT JOIN TblAge AS b ON a.age_class = b.age_class
RIGHT JOIN TblSex AS c ON a.sex_cd = c.sex_cd
GROUP BY b.age_range, c.sex
ORDER BY b.age_range, c.sex;

查询结果：

通过如上结果我们可以发现，ageclass = 2 的情况没有显示在查询结果中。虽然 ageclass = 2 可以通过外连接从表 TblAge 中获取，但是在表 TblPop 里，与之相应的 “sex_cd” 列却是NULL。因为表 TblPop 里本来就没有 ageclass = 2 的数据，自然也没有相应的sex_cd = m 或 sex_cd =f 的信息，于是 “sex_cd” 列只能是 NULL。因此最终结果里永远不会出现 ageclass = 2 的数据，即使改变两次外连接的先后顺序，结果也还是一样的。

3.2 内联结(语法：INNER JOIN)

查找出同时存在于两张表中的数据并将它们交叉联结。

SELECR [column name1], ... [column nameN] FROM [table name 1] AS a
INNER JOIN [table name 2] AS b
ON a.[column name] = b.[column name];

3.3 外联结

内联结包含左联结 (LEFT JOIN) 和右联结 (RIGHT JOIN)。左外连接和右外连接没有功能上的区别。用作主表的表写在运算符左边时用左外连接，写在运算符右边时用右外连接。

3.3.1 语法：LEFT/RIGHT JOIN

左联结：将左侧表中的数据全部取出来，右侧表只取出和左侧表相对应的数据，最后进行交叉联结。

右联结：与左联结相反，将右侧表中的数据全部取出来，左侧表只取出和右侧表相对应的数据，最后进行交叉联结。

SELECR [column name1], ... [column nameN] FROM [table name 1] AS a 
LEFT/RIGHT JOIN [table name 2] AS b 
ON a.[column name] = b.[column name];

3.3.2 运用外联结进行行列转换 (将行转化为列)

例如：下面有一张Courses表格，记录了某公司各员工所上课程的情况：

Courses表

现在，我希望将该表转化为如下格式的交叉表：

查询结果

方法一：外联结 + CASE 表达式

SELECT a.name,
CASE WHEN b.name IS NULL THEN NULL ELSE 'O' END AS 'SQL 入门',
CASE WHEN c.name IS NULL THEN NULL ELSE 'O' END AS 'UNIX 基础',
CASE WHEN d.name IS NULL THEN NULL ELSE 'O' END AS 'Java 中级'
FROM (SELECT DISTINCT name FROM Courses) AS a
LEFT JOIN (SELECT name FROM Courses WHERE course = 'SQL 入门') AS b ON a.name = b.name
LEFT JOIN (SELECT name FROM Courses WHERE course = 'UNIX 基础') AS c ON a.name = c.name
LEFT JOIN (SELECT name FROM Courses WHERE course = 'Java 中级') AS d ON a.name = d.name;

方法二：标量子查询 + CASE 表达式

SELECT name,
CASE WHEN 
(SELECT name FROM Courses AS b 
 WHERE course = 'SQL 入门' AND a.name = b.name) 
IS NULL THEN NULL ELSE 'O' END AS 'SQL 入门',
CASE WHEN 
(SELECT name FROM Courses AS c 
 WHERE course = 'UNIX 基础' AND a.name = c.name) 
IS NULL THEN NULL ELSE 'O' END AS 'UNIX 基础',
CASE WHEN 
(SELECT name FROM Courses AS d 
 WHERE course = 'Java 中级' AND a.name = d.name) 
IS NULL THEN NULL ELSE 'O' END AS 'Java 中级'
FROM (SELECT DISTINCT name FROM Courses) AS a

方法三：标量子查询

SELECT a.name,
(SELECT 'O' FROM Courses AS b
 WHERE course = 'SQL 入门'
 AND a.name = b.name) AS 'SQL 入门',
(SELECT 'O' FROM Courses AS c
 WHERE course = 'UNIX 基础'
 AND a.name = c.name) AS 'UNIX 基础', 
(SELECT 'O' FROM Courses AS d
 WHERE course = 'Java中级'
 AND a.name = d.name) AS 'Java 中级'
FROM (SELECT DISTINCT name FROM Courses) AS a;

方法四：嵌套 CASE 表达式

SELECT name,
CASE WHEN SUM(CASE WHEN course = 'SQL 入门' THEN 1 ELSE NULL END) = 1 THEN 'O' ELSE NULL END AS 'SQL 入门',
CASE WHEN SUM(CASE WHEN course = 'UNIX 基础' THEN 1 ELSE NULL END) = 1 THEN 'O' ELSE NULL END AS 'UNIX 基础',
CASE WHEN SUM(CASE WHEN course = 'Java 中级' THEN 1 ELSE NULL END) = 1 THEN'O'ELSE NULL END AS 'Java 中级'
FROM Courses 
GROUP BY name;

3.3.3 运用外联结进行行列转换 (将列转化为行) -- 汇总重复项于一列

例如：下面有一张 Personnel 表格，记录了某公司各员工拥有子女的情况：

Personnel表

现在，我希望将该表转化为如下格式：

查询结果

首先生成一个存储子女列表的视图：

CREATE VIEW ChildrenN(child) AS 
SELECT child_1 FROM Personnel
UNION
SELECT child_2 FROM Personnel 
UNION
SELECT child_3 FROM Personnel;

接下来以 Personnel 为主表进行外连接操作：

SELECT a.employee, b.child
FROM Personnel AS a
LEFT JOIN childrenn AS b
ON b.child IN (a.child_1, a.child_2, a.child_3);

注意：使用 UNION ALL/ UNION 进行表的加法得到的都不是如上查询结果。

接下来，统计每个员工的孩子数：

SELECT a.employee, COUNT(b.child) FROM
Personnel AS a
LEFT JOIN childrenn AS b
ON b.child IN (a.child_1, a.child_2, a.child_3)
GROUP BY a.employee;

注意：如果使用 COUNT(*) 则返回的是错误答案。

3.3.4 求差集

SELECT [column name1], ... [column nameN] FROM [table name 1] AS a 
LEFT JOIN [table name 2] AS b
ON a.[column name] = b.[column name]
WHERE b.[column name] IS NUll;

3.4 全联结(语法：FULL JOIN)

将左右表全部数据取出，匹配进行交叉联结，如果没有匹配数据，对应的数据用空值来填充。

举例：下面有两张表，ClassA 和 Class_B，里面分别记录了 A 班和 B 班学生的学号和姓名：

Class_A 表

Class_B 表

我希望通过 SQL 语句得到如下结果：

查询结果

方法一：全联结

SELECT COALESCE(A.id, B.id) AS id,
A.name AS A_name,
B.name AS B_name
FROM Class_A A FULL JOIN Class_B B
ON A.id = B.id;

方法二：UNION

SELECT A.id AS id, A.name, B.name
FROM Class_A A LEFT OUTER JOIN Class_B B ON A.id = B.id
UNION
SELECT B.id AS id, A.name, B.name
FROM Class_A A RIGHT OUTER JOIN Class_B B
ON A.id = B.id;

注意：MySQL不支持全联结，因此，在 MySQL 中只能使用 UNION 来实现该目的。

3.4.1 求异或集

方法一：全联结

SELECT COALESCE(A.id, B.id) AS id, COALESCE(A.name , B.name ) AS name
FROM Class_A A FULL JOIN Class_B B ON A.id = B.id
WHERE A.name IS NULL OR B.name IS NULL;

方法二：(A UNION B) EXCEPT (A INTERCEPT B)

(SELECT id, name FROM Class_A 
 UNION
 SELECT id, name FROM Class_B)
EXCEPT
(SELECT id, name FROM Class_A 
 INTERCEPT
 SELECT id, name FROM Class_B)

方法三：(A EXCEPT B) UNION (B EXCEPT A)

(SELECT id, name FROM Class_A 
 EXCEPT
 SELECT id, name FROM Class_B)
UNION
(SELECT id, name FROM Class_B 
 EXCEPT
 SELECT id, name FROM Class_A)

3.5 练习

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

方法一：联结

SELECT a.id, a.name, COUNT(b.c_id) AS num_of_class, 
SUM(CASE WHEN b.score IS NOT NULL THEN b.score
	 ELSE 0
	 END) AS total_score FROM students AS a
LEFT JOIN scores AS b
ON a.id =b.id
GROUP BY a.id;

方法二：子查询

SELECT id, name, 
(CASE WHEN (SELECT COUNT(c_id) FROM scores AS b WHERE a.id = b.id GROUP BY a.id) IS NULL THEN 0
     ELSE (SELECT COUNT(c_id) FROM scores AS b WHERE a.id = b.id GROUP BY a.id)
     END) AS num_of_class,
(CASE WHEN (SELECT SUM(score) FROM scores AS c WHERE a.id = c.id GROUP BY a.id) IS NULL THEN 0
     ELSE (SELECT SUM(score) FROM scores AS c WHERE a.id = c.id GROUP BY a.id)
     END) AS total_score 
FROM students AS a

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

SELECT a.id, a.name, AVG(b.score) AS 'average_score' 
FROM students AS a
INNER JOIN scores AS b
ON a.id = b.id
GROUP BY a.id
HAVING AVG(b.score) > 85;

注意，如果这里使用子查询，则结果如下

students表中全部的学生id、姓名信息都将出现在查询结果中，即使不满足平均分条件。因此，针对该习题，只可以使用联结。

练习三：查询学生的学号、姓名、课程号和课程名称

SELECT a.id, a.name, b.c_id , c.c_name 
FROM students AS a
LEFT JOIN scores AS b
ON a.id = b.id
INNER JOIN classes AS c
ON b.c_id = c.id;

四、练习

来源：SQLZOO

【JOIN 练习题】

地址：https://sqlzoo.net/wiki/The_JOIN_operation

数据表：2012年欧洲足球锦标赛数据表

表1：比赛信息表

列名含义：

id：比赛编号

mdate：比赛日期

stadium：比赛地点

team1：对战双方（球队编号1）

team2：对战双方（球队编号2）

表2：进球信息表

列名含义：

matchid：比赛编号

teamid：球队编号

player：进球球员的姓名

gtime：从开始比赛到进球多长时间，单位：分钟

表3：球队信息表

列名含义：

id：球队编号

teamname：球队名称

coach：教练

练习一：显示德国球队(teamid = 'GER')进球的比赛编号和进球球员姓名

Show the matchid and player name for all goals scored by Germany.

SELECT matchid, player FROM goal 
WHERE teamid = 'GER';

练习二：显示比赛编号为1012的比赛地点和对战双方

Show id, stadium, team1, team2 for just game 1012.

SELECT id,stadium, team1, team2 FROM game
WHERE id = 1012;

练习三：显示德国队进球球员姓名，球队编号，比赛地点以及比赛日期

Show the player, teamid, stadium and mdate for every German goal.

SELECT b.player, b.teamid, a.stadium, a.mdate FROM game AS a
INNER JOIN goal AS b
ON a.id= b.matchid
WHERE b.teamid = 'GER';

练习四：显示姓名中以Mario开头的进球球员的名称及其参加比赛的对战双方

Show the team1, team2 and player for every goal scored by a player called Mario.

SELECT a.team1, a.team2, b.player FROM game AS a
INNER JOIN goal AS b
ON a.id= b.matchid
WHERE b.player LIKE 'Mario%';

练习五：显示比赛开始10分钟内进球的球员姓名、球队id，教练名称以及比赛开始至进球市场

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes.

SELECT a.player, a.teamid, b.coach, a.gtime FROM goal AS a
INNER JOIN eteam AS b 
ON a.teamid = b.id
WHERE gtime<=10;

练习六：显示Fernando Santos在team1执教的比赛日期和其执教队名

List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

SELECT mdate, teamname FROM game AS a
INNER JOIN eteam AS b
ON a.team1 = b.id
WHERE b.coach = 'Fernando Santos';

练习七：显示在比赛地点'National Stadium, Warsaw'进球的球员姓名

List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'.

SELECT player FROM goal AS a
INNER JOIN game AS b
ON a.matchid = b.id
WHERE stadium = 'National Stadium, Warsaw';

练习八：显示射入德国球门的球员姓名

Show the name of all players who scored a goal against Germany.

自己写的时候忽略了DISTINCT

方法一：

SELECT DISTINCT player FROM game AS a
INNER JOIN goal AS b
ON a.id = b.matchid 
WHERE (a.team1 = 'GER' AND a.team2 = b.teamid)
OR (a.team2 = 'GER' AND a.team1 = b.teamid);

方法二：

SELECT DISTINCT player FROM goal AS a
INNER JOIN game AS b
ON a.matchid = b.id
WHERE a.teamid <> 'GER' AND
(b.team1 = 'GER' OR b.team2 = 'GER');

练习九：显示每个球队进球的次数

Show teamname and the total number of goals scored.

SELECT b.teamname, COUNT(a.matchid) AS '#_of_goals' FROM goal AS a
LEFT JOIN eteam AS b 
ON a.teamid = b.id
GROUP BY b.teamname;

练习十：显示在每个球场的总进球次数

Show the stadium and the number of goals scored in each stadium.

SELECT b.stadium, COUNT(a.matchid) AS '#_of_goals' FROM goal AS a
RIGHT JOIN game AS b 
ON a.matchid = b.id
GROUP BY b.stadium;

练习十一：显示有波兰球队'POL'参加的比赛编号，比赛日期，对应这场比赛的进球数

For every match involving 'POL', show the matchid, date and the number of goals scored.

SELECT a.id, a.mdate, COUNT(a.id) AS '#_of_goals' FROM game AS a
LEFT JOIN goal AS b
ON a.id = b.matchid 
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY a.id, a.mdate;

练习十二：显示德国队'GER'得分的每场比赛的比赛编号，比赛日期和德国队的总进球数

For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'.

SELECT a.matchid, b.mdate, COUNT(a.matchid) FROM goal AS a
INNER JOIN game AS b
ON a.matchid = b.id
WHERE a.teamid = 'GER'
GROUP BY a.matchid;

练习十三：显示所有比赛的日期以及每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数

List every match with the goals scored by each team as shown. Sort your result by mdate, matchid, team1 and team2.

SELECT a.mdate,
  a.team1,
  SUM(CASE WHEN b.teamid = a.team1 THEN 1 ELSE 0 END) AS score1,
  a.team2,
  SUM(CASE WHEN b.teamid = a.team2 THEN 1 ELSE 0 END) AS score2
  FROM game AS a
LEFT JOIN goal AS b
ON a.id = b.matchid
GROUP BY a.id, a.mdate, a.team1, a.team2
ORDER BY a.mdate, b.matchid, a.team1, a.team2;

【More Join练习题】

地址：https://sqlzoo.net/wiki/More_JOIN_operations

数据表：电影信息数据表

表1：电影信息表

列名含义：

id：电影编号

title：电影名称

yr：电影首映时间

director：与actor表中id相对应

budget：电影制作成本

gross：电影票房

表2：演员表

列名含义：

id：演员id

name：演员姓名

表3：剧组表

列名含义：

movieid：电影编号

actorid：演员编号

ord：演员咖位

练习一：显示1962年拍摄的电影的id及名字

List the films where the yr is 1962 [Show id, title].

SELECT id, title FROM movie
WHERE yr = 1962;

练习二：显示电影Citizen Kane首映的时间

Give year of 'Citizen Kane'.

SELECT yr FROM movie
WHERE title = 'Citizen Kane';

练习三：显示全部的Star Trek电影的id，名字以及首映年份

List all of the Star Trek movies, include the id, title and yr(all of these movies include the words Star Trek in the title). Order results by year.

SELECT id, title, yr FROM movie
WHERE title LIKE '%Star Trek%'
ORDER BY yr;

练习四：显示演员Glenn Close的id

What id number does the actor 'Glenn Close' have?

SELECT id FROM actor
WHERE name = 'Glenn Close';

练习五：显示电影Casablanca的id

What is the id of the film 'Casablanca'.

SELECT id FROM movie
WHERE title = 'Casablanca';

练习六：显示电影Casablanca的全部参演人员

Obtain the cast list for 'Casablanca'.

SELECT a.name FROM actor AS a
INNER JOIN casting AS b
ON a.id = b.actorid
WHERE b.movieid = (SELECT id FROM movie WHERE title = 'Casablanca');

练习七：显示电影Alien的全部参演人员

Obtain the cast list for the film 'Alien'.

SELECT a.name FROM actor AS a
INNER JOIN casting AS b
ON a.id = b.actorid
WHERE b.movieid = (SELECT id FROM movie WHERE title = 'Alien');

练习八：显示Harrison Ford参演的电影

List the films in which 'Harrison Ford' has appeared.

SELECT a.title FROM movie AS a
INNER JOIN casting AS b ON a.id = b.movieid
INNER JOIN actor AS c ON b.actorid = c.id
WHERE c.name = 'Harrison Ford';

练习九：显示Harrison Ford作为非主演参演的电影

List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role].

SELECT a.title FROM movie AS a
INNER JOIN casting AS b ON a.id = b.movieid
INNER JOIN actor AS c ON b.actorid = c.id
WHERE c.name = 'Harrison Ford' 
AND b.ord <> 1;

练习十：显示1962年的每场电影及其主演

List the films together with the leading star for all 1962 films.

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

练习十一：显示Rock Hudson一年拍摄超过两部电影的年份及当年拍摄的电影数

Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT a.yr, COUNT(b.movieid) AS num FROM movie AS a
INNER JOIN casting AS b ON a.id = b.movieid
INNER JOIN actor AS c ON b.actorid = c.id
WHERE c.name = 'Rock Hudson'
GROUP BY a.yr
HAVING COUNT(b.movieid) >2;

练习十二：显示Julie Andrews参演的电影及其主演

List the film title and the leading actor for all of the films 'Julie Andrews' played in.

SELECT a.title, c.name FROM movie AS a
INNER JOIN casting AS b ON a.id = b.movieid
INNER JOIN actor AS c ON b.actorid = c.id
WHERE a.id IN
(SELECT id FROM movie WHERE id IN 
(SELECT movieid FROM casting AS a
 INNER JOIN actor AS b ON a.actorid = b.id
 WHERE b.name = 'Julie Andrews')) AND
 b.ord = 1;

练习十三：显示参演过15部及以上电影的演员姓名及参演电影数目，按演员姓名升序排序

Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.

SELECT b.name FROM casting AS a
INNER JOIN actor AS b ON a.actorid = b.id
WHERE a.ord = 1
GROUP BY a.actorid, b.name
HAVING COUNT(a.movieid) >= 15
ORDER BY b.name;

练习十四：显示1978年首映的电影名称及每部电影的参演人数。按参演人数降序排序，其次按电影名称排序

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

SELECT title, COUNT(actorid) AS num_of_casting FROM movie AS a
INNER JOIN casting AS b ON a.id = b.movieid
WHERE yr = 1978
GROUP BY title
ORDER BY COUNT(actorid) DESC, title;

练习十五：显示与Art Garfunkel合作过的演员姓名

List all the people who have worked with 'Art Garfunkel'.

SELECT name FROM actor AS a
INNER JOIN casting AS b ON a.id = b.actorid
WHERE movieid IN
(SELECT movieid FROM casting WHERE actorid = (SELECT id FROM actor 
 WHERE name = 'Art Garfunkel'))
AND name <> 'Art Garfunkel';

【Using Null 练习题】

地址：https://sqlzoo.net/wiki/Using_Null

数据表：教师及其所在部门信息表

表1：教师信息表

列名含义：

id：教师编号

dept：教师所在部门编号

name：教师姓名

phone：教室电话号

mobile：教师手机号

表2：部门信息表

列名含义：

id：部门编号

name：部门名称

练习一：显示没有所属部门的教师姓名

List the teachers who have NULL for their department.

SELECT name FROM teacher 
WHERE dept IS NULL;

练习二：显示教师姓名及所属部门均为非空值的情况

Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id);

练习三：显示全部教师的姓名及其所属部门

Use a different JOIN so that all teachers are listed.

SELECT teacher.name, dept.name FROM teacher
LEFT JOIN dept ON teacher.dept = dept.id;

练习四：显示全部部门及各部门下的教师名称

Use a different JOIN so that all departments are listed.

SELECT teacher.name, dept.name FROM teacher
RIGHT JOIN dept ON teacher.dept = dept.id;

练习五：显示教师名称及其手机号码，若手机号为空值则显示'07986 444 2266'

Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'.

SELECT name, COALESCE(mobile, '07986 444 2266') AS mobile 
FROM teacher;

练习六：显示全部教师及其所属部门，若部门为空值，则显示'None'

Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.

SELECT teacher.name, COALESCE(dept.name, 'None') FROM teacher
LEFT JOIN dept ON teacher.dept = dept.id;

练习七：显示教师数及教师手机号的数目

Use COUNT to show the number of teachers and the number of mobile phones.

SELECT COUNT(name) AS num_teacher, COUNT(mobile) AS num_mobile
FROM teacher;

练习八：显示全部部门及各部门下的教师数

Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

SELECT dept.name, COUNT(teacher.name) FROM teacher
RIGHT JOIN dept ON teacher.dept = dept.id
GROUP BY dept.name;

练习九：显示全部教师姓名及其部门所属领域，部门编号为1或2，则属于'Sci'领域，否则属于’Art‘领域

Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.

SELECT name, 
 CASE WHEN dept IN (1,2) THEN 'Sci' ELSE 'Art' END AS dept 
FROM teacher;

练习十：显示全部教师姓名及其部门所属领域，部门编号为1或2，则属于'Sci'领域，部门编号为3则属于'Art'领域，否则属于’Art‘领域

Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.

SELECT name, CASE WHEN dept IN (1,2) THEN 'Sci' 
                  WHEN dept = 3 THEN 'Art'
                  ELSE 'None' END AS dept 
FROM teacher;

【Self Join 练习题】

地址：https://sqlzoo.net/wiki/Self_join

数据表：公交路线信息表

表1：公交站信息表

列名含义：

id：公交站编号

name：公交站名称

表2：路线信息表

列名含义：

num：公交车号

company：公交车所属公司

pos：每一站对应的数字

stop：公交站名称

练习一：显示公交站的数目

How many stops are in the database.

SELECT COUNT(id) AS num FROM stops;

练习二：显示Craiglockhart站id

Find the id value for the stop 'Craiglockhart'.

SELECT id FROM stops WHERE name = 'Craiglockhart';

练习三：显示LRT公司4路公交车经停站的公交站id及姓名

Give the id and the name for the stops on the '4' 'LRT' service.

SELSELECT a.id, a.name FROM stops AS a
INNER JOIN route AS b
ON a.id = b.stop
WHERE company = 'LRT' AND
num = '4';

练习四：显示经过 London Road站或 Craiglockhart站两次的公交车所属公司、公交车号、以及经过次数

The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

SELECT company, num, COUNT(*) AS count
FROM route WHERE stop = 149 OR stop = 53
GROUP BY company, num
HAVING COUNT(*) = 2;

练习五：显示从53号公交站到149号公交站的公交车所属公司、公交车号以及两个车站的id

Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

SELECT a.company, a.num, a.stop, b.stop FROM route a 
INNER JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND b.stop = 149;

练习六：显示从Craiglockhart公交站到London Road公交站的公交车所属公司、公交车号以及两个车站的名称

The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'

SELECT a.company, a.num, stopa.name, stopb.name FROM route a INNER JOIN route b 
ON(a.company=b.company AND a.num=b.num)
INNER JOIN stops stopa ON (a.stop=stopa.id)
INNER JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' 
AND stopb.name = 'London Road';

练习七：显示连接115号公交站和137号公交站的公交车所属公司以及公交车号

Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith').

SELECT DISTINCT a.company, a.num FROM route AS a
INNER JOIN route AS b 
ON (a.company = b.company AND a.num = b.num)
WHERE (a.stop = 115 AND b.stop = 137)
OR (a.stop = 137 AND b.stop = 115);

练习八：显示连接Craiglockhart公交站和Tollcross公交站的公交车所属公司以及公交车号

Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'.

SELECT DISTINCT a.company, a.num FROM route AS a
INNER JOIN route AS b 
ON (a.company = b.company AND a.num = b.num)
INNER JOIN stops AS stopa ON a.stop = stopa.id
INNER JOIN stops AS stopb ON b.stop = stopb.id
WHERE (stopa.name = 'Craiglockhart' AND stopb.name = 'Tollcross')
OR (stopa.name = 'Tollcross' AND stopb.name = 'Craiglockhart');

练习九：显示LRT公司的公交车，能够从Craiglockhart站乘坐一辆公交车到达的全部车站。要求列出车站名称、公交车所属公司名称以及公交车号

Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services.

SELECT DISTINCT stopb.name, a.company, a.num FROM route AS a
INNER JOIN route AS b
ON (a.company = b.company AND a.num = b.num)
INNER JOIN stops AS stopa
ON stopa.id = a.stop
INNER JOIN stops AS stopb
ON stopb.id = b.stop
WHERE a.company = 'LRT'
AND stopa.name = 'Craiglockhart';

练习十：显示必须乘坐两辆公交车才能从Craiglockhart 到 Lochend的情况。要求显示第一辆公交车的所属公司、第一辆公交车的车号、中转站名称、第二辆公交车所属公司以及第二辆公交车的车号。按照列的顺序升序排列。

Find the routes involving two buses that can go from Craiglockhart to Lochend. Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no. and company for the second bus.

SET SQL_BIG_SELECTS=1;
SELECT m.*, n.num, n.company FROM
(SELECT a.num, a.company, stopb.name FROM route AS a
INNER JOIN route AS b
ON (a.company = b.company AND a.num = b.num)
INNER JOIN stops AS stopa
ON a.stop =stopa.id
INNER JOIN stops AS stopb
ON b.stop = stopb.id
WHERE stopa.name = 'Craiglockhart' AND stopb.name <> 'Lochend') AS m
INNER JOIN
(SELECT c.num, c.company, stopc.name FROM route AS c
INNER JOIN route AS d
ON (c.company = d.company AND c.num = d.num)
INNER JOIN stops AS stopc
ON c.stop =stopc.id
INNER JOIN stops AS stopd
ON d.stop = stopd.id
WHERE stopc.name <> 'Craiglockhart' AND stopd.name = 'Lochend') AS n
ON m.name = n.name
ORDER BY m.num, m.company, m.name, n.num, n.company;

weixin_39951929

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
sql 减法_运用SQL对数据进行集合运算

一、集合运算集合在数学领域表示“(各种各样的)事物的总和”，在数据库领域表示记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。因此，可以将集合运算理解为对满足同一规则的记录进行的加减等四则运算。通过集合运算，可以得到两张表中记录的集合或者公共记录的集合，又或者其中某张表中的记录的集合。像这样用来进行集合运算的运算符称为集合运算符。二、表的加减法2.1 表的加法(语法：UNION/...
复制链接

扫一扫