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

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

Outer Join Queries

查阅各部门名称及各部门所开设的所有课程名称：

SELECT name, title
FROM DEPARTMENT JOIN COURSE
ON name = offered_by

如果存在没有开设任何课程的部门和没有分配给任何部门的课程怎么办？

JOIN 操作可以从两个参数中删除不能与另一个参数中的任何行进行连接的行。

有时候，我们希望在查询结果中包含不能连接的行，我们可以使用Left outer join queries。

LEFT OUTER JOIN And RIGHT OUTER JOIN

查找各系的名称以及各系提供的所有课程的名称，并包括完全不提供课程的系的名称:

SELECT name, title
FROM DEPARTMENT LEFT OUTER JOIN COURSE
ON name = offered_by;

LEFT OUTER JOIN的将该操作的“左”参数的所有行包含到结果中,在上面的例子中，左边的参数是DEPARTMENT。

如果操作的“左”参数中的一行不能与操作的“右”参数中的任何一行连接，那么它将被扩展为null并附加到结果中。

同理，RIGHT OUTER JOIN 将该操作的“右”参数的所有行包含到结果中并且如果操作的“右”参数中的一行不能与操作的“左”参数中的任何一行连接，那么它将被扩展为null并附加到结果中。

Full outer join queries

Full outer join queries的SQL查询等同于left outer join and right outer join结果的并集。

SELECT name, title
FROM DEPARTMENT FULL OUTER JOIN COURSE
ON name = offered_by;

如果你的SQL不支持Full outer join queries，你可以这么写：

SELECT name, title
FROM DEPARTMENT LEFT OUTER JOIN COURSE
ON name = offered_by
UNION
SELECT name, title
FROM DEPARTMENT RIGHT OUTER JOIN COURSE
ON name = offered_by;

嵌套查询

嵌套查询是在WHERE、SELECT、FROM语句中嵌入另一个查询的查询。

举个例子：
找到由Peter主持的部门所开设的课程名称：

SELECT title
FROM COURSE
WHERE offered_by IN ( SELECT name
				FROM DEPARTMENT
				WHERE chair = 'Peter' );

关联嵌套查询

找到那些提供12个学分课程的部门主任：

SELECT chair
FROM DEPARTMENT
WHERE EXISTS ( SELECT *
			   FROM COURSE
			   WHERE credits = 12 AND offered_by = DEPARTMENT.name );

Relational views

关系视图是一个虚拟关系表(派生关系表)，它不占用任何存储空间，每次在SELECT语句中使用它时，都会从头开始计算它。

关系视图被数据库管理系统作为一对存储(视图的名称，定义视图的结构和内容的SELECT语句)。

例如，创建一个关系视图，其中包含所有部门的名称以及每个部门提供的课程总数：

CREATE VIEW VDEPT( name, total_courses ) AS
( SELECT name, count(cnum)
FROM DEPARTMENT LEFT OUTER JOIN COURSE
ON DEPARTMENT.name = COURSE.offered_by
GROUP BY name );

然后，view VDEPT可以用来实现查询，查找提供1个以上课程的部门名称：

SELECT name
FROM VDEPT
WHERE total_courses > 1;

关系视图可用于降低SELECT语句的复杂性：

例如下面的语句跟上面的是等同的。

SELECT name, count(cnum)
FROM DEPARTMENT LEFT OUTER JOIN COURSE
ON DEPARTMENT.name = COURSE.offered_by
GROUP BY name
HAVING count(cnum) > 1;

我们可以把一个长的复杂的SELECT拆解成多步的relational view，借此成功降低了SELECT语句的复杂性。

高级DML语句

高级数据操作语句使用DML语句中的SELECT语句实现的子查询。

删除由彼得领导的部门开设的所有课程：

DELETE FROM COURSE
WHERE offered_by = ( SELECT name
					FROM DEPARTMENT
					WHERE chair = 'Peter' )

在所有开设超过20门课程的部门增加5名员工：

UPDATE DEPARTMENT
SET total_staff_number = total_staff_number + 5
WHERE name IN ( SELECT offered_by
FROM COURSE
GROUP BY offered_by
HAVING COUNT(cnum) > 20 );

在DEPARTMENT表中添加一个列，其中包含每个部门提供的课程总数，并在该列中插入正确的值：

ALTER TABLE DEPARTMENT ADD ( total_courses DECIMAL(2) );
UPDATE DEPARTMENT
SET total_courses = ( SELECT COUNT(title)
					  FROM COURSE
					  WHERE COURSE.offered_by = DEPARTMENT.name );

References

1. T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapters 6.3.7 Multi-table Queries, Chapter 6.3.5 Subqueries, Chapter 6.3.8 EXISTS and NOT EXISTS, Chapter 7.4.1 Creating a View Pearson Education Ltd, 2015.
2. D. Darmawikarta, Oracle SQL : A Beginner’s Tutorial Brainy Software Inc. First Edition: June 2014.