数据库系统之SQL SELECT语句-2

接上篇文章
SQL SELECT语句-1：
https://blog.csdn.net/Jifu_M/article/details/112681846

Set Algebra Queries

包含三种操作：
UNION, INTERSECTION 和DIFFERENCE。

用图可以更直接的表达三者的关系:

A UNION B:在A或在B中的所有元素
A INTERSECTION B: 同时在A和B中的所有元素
A DIFFERENCE B: 所有在A中，而不在B中的元素。

UNION Operation

查找所有由物理系或数学系提供的学分，并且不要列出重复的行：
有两种写法，第二种就是使用UNION的写法。

SELECT DISTINCT credits
FROM COURSE
WHERE (offered_by = 'Physics') OR (offered_by = 'Mathematics');


SELECT credits
FROM COURSE
WHERE offered_by = 'Physics'
UNION
SELECT credits
FROM COURSE
WHERE offered_by = 'Mathematics';

要注意的是UNION操作会自动消除重复的行，同时 A UNION B = B UNION A。

如果不想要UNION操作会自动消除重复的行，那么需要使用UNION ALL operation

SELECT credits
FROM COURSE
WHERE offered_by = 'Physics'
UNION ALL
SELECT credits
FROM COURSE
WHERE offered_by = 'Mathematics';

第一种写法的话去掉DISTINCT 语句即可。

INTERSECT Operation

查找所有由物理系和数学系共同管理的课程学分：

SELECT credits
FROM COURSE
WHERE offered_by = 'Physics'
INTERSECT
SELECT credits
FROM COURSE
WHERE offered_by = 'Mathematics';

INTERSECT操作也会自动消除重复的行，同时 A INTERSECT B = B INTERSECT A。

MINUS Operation

查找由物理系而不是由数学系负责的所有课程的学分：

SELECT credits
FROM COURSE
WHERE offered_by = 'Physics'
MINUS
SELECT credits
FROM COURSE
WHERE offered_by = 'Mathematics';

INTERSECT操作会自动消除左边参数的重复行，但是 A MINUS B ≠ B MINUS A。
要分清左右，这里的左就是Physics。

但是有的时候你的SQL不支持INTERSECT和 MINUS语句，那么你可以转换一下：
比如转换INTERSECT

SELECT DISTINCT credits
FROM COURSE
WHERE offered_by = 'Physics' AND
			credits IN (SELECT credits
			FROM COURSE
			WHERE offered_by = 'Mathematics');

Sorting

如果查询，排序将重新对结果中的行进行排序。

查找所有6个学分课程的编号和名称，按名称升序排列:

SELECT cnum, title
FROM COURSE
WHERE credits = 6
ORDER BY title ASC;

SELECT cnum, title
FROM COURSE
WHERE credits = 6
ORDER BY 2 ASC;

ASC代表升序，DESC代表降序。

ORDER BY 2 ASC这里的2代表SELECT里面的第二个title。

按学分升序查找所有课程的编号、名称和学分，按名称降序查找相同学分的所有课程：

SELECT cnum, title, credits
FROM COURSE
ORDER BY credits ASC, title DESC;

SELECT cnum, title, credits
FROM COURSE
ORDER BY 3 ASC, 2 DESC;

缺失值查询

SELECT title
FROM COURSE
WHERE offered_by IS NULL;

SELECT title
FROM COURSE
WHERE offered_by = NULL;

Grouping

Grouping在应用组函数之前对行进行分组.
.
GROUP BY根据GROUP中包含的属性对关系表进行排序,并在GROUP BY list中创建具有相同属性值的行组.

举例：

对于每个学分值，找到拥有相应学分的课程总数：

SELECT credits, count(*)
FROM COURSE
GROUP BY credits;

找到每个部门提供的学分总数：

SELECT offered_by, sum(credits)
FROM COURSE
GROUP BY offered_by;

Grouping With Selections

找出提供一门以上课程的所有系的名称：

SELECT offered_by, count(*)
FROM COURSE
GROUP BY offered_by;

从下表可以看出offered_by这个属性里的行被按照首字母顺序升序排列了。后面紧跟着是count的数量。

offered_by	count(*)
Computer Science	2
Mathematics	1
Physics	2

SELECT offered_by, count(*)
FROM COURSE
GROUP BY offered_by
HAVING count(*) > 1;

使用HAVING语句可以使用条件对SELECT出的结果进行再次筛选：

offered_by	count(*)
Computer Science	2
Physics	2

下篇文章：
SQL SELECT语句-3：
https://blog.csdn.net/Jifu_M/article/details/112690498
SQL SELECT语句-4：
https://blog.csdn.net/Jifu_M/article/details/112705789

References

1. T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapters 6.3.1 - 6.3.4 Data Manipulation, Pearson Education Ltd, 2015.
2. D. Darmawikarta, Oracle SQL : A Beginner’s Tutorial Brainy Software Inc. First Edition: June 2014.