2021-11-08 基于音乐商店NetMusicShop的复杂查询（一）

先声明：本博客仅为个人作业不为标准答案，且复杂查询思路很多不同思路写出来的代码也不同，我写的也有可能漏洞百出，仅能作为参考哈！

先列一下知识点：

（一）等值、非等值连接

格式：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

<比较运算符>主要有：=、>、<、>=、<=、!=

当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接

例子：

SELECT SC.Sno,Sname,Grade FROM Student,SC,Course WHERE Cname='离散数学' AND Student.Sno=SC.Sno AND SC.Cno=Course.Cno;

如果出现了来自多个表的相同的列名，在列名前要加上表名作为前缀以示区分

自然连接必须给出连接条件，否则为笛卡尔运算

通常n个表相连，有n-1个连接条件

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接

注意：自身链接不能这么写：

SELECT Sno FROM SC WHERE Cno = '1024' AND Cno = '1136';

要这么写：

SELECT a.Sno FROM SC a , SC b WHERE a.Sno=b.Sno AND a.Cno = '1024' AND b.Cno = '1136';

（二）内、外连接查询

格式：

FROM <表名1> INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN <表名2> ON <连接条件>;

INNER JOIN——内连接，表示结果中只包括两个表中相匹配的记录（默认）

LEFT OUTER JOIN——左外连接，表示结果中包括所创建关系左边表中所有记录和右边表中与连接条件相匹配的记录

RIGHT OUTER JOIN——右外连接，表示结果中包括所创建关系右边表中所有记录和左边表中与连接条件相匹配的记录

FULL OUTER JOIN——完全外连接，表示结果中包括所创建关系的两个表中的所有记录

例子：

SELECT Sname,Cname,Grade FROM Student LEFT JOIN SC ON Student.Sno=SC.Sno LEFT JOIN Course ON SC.Cno=Course.Cno

（三）嵌套查询（子查询）

将一个查询块嵌套在另一个查询块的WHERE子句、HAVING条件或其他位置中的查询称为嵌套查询

嵌套查询一般的求解方法是由里向外处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件

引出子查询的谓词：

(1) IN | NOT IN

格式：

WHERE <属性名> [NOT] IN ( <集合>|<子查询> )

(2) 比较运算符（=、>、>=、<、<=、!= 等）

格式：

WHERE <属性名> <比较运算符>（子查询）

(3) 比较运算符SOME | ALL

格式：

WHERE <属性名> <比较符> ALL|SOME|(子查询)

比较符+谓词 语义

>SOME 大于子查询结果中的某个值

>ALL 大于子查询结果中的所有值

< SOME 小于子查询结果中的某个值

<ALL 小于子查询结果中的所有值

>= SOME 大于等于子查询结果中的某个值

>=ALL 大于等于子查询结果中的所有值

<= SOME 小于等于子查询结果中的某个值

<=ALL 小于等于子查询结果中的所有值

=ALL 等于子查询结果中的所有值

!= SOME 不等于子查询结果中的某个值

!=ALL 不等于子查询结果中的任何一个值

用聚集函数实现子查询通常比直接用SOME或ALL查询效率要高，对应关系为：

             =      !=           <           <=           >           >=

SOME IN       -        <MAX   <=MAX    >MIN     >=MIN

ALL       -   NOT IN   <MIN    <=MIN     >MAX    >=MAX

(4) EXISTS | NOT EXISTS

格式：

WHERE  [NOT] EXISTS (子查询)

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”

使用EXISTS后,若内层查询结果非空，则外层的WHERE子句返回真值,否则返回假值

由EXISTS引出的子查询，其目标列表达式通常都用*，因为子查询只返回.T.或.F.，给出列名无实际意义

（5）嵌套查询小结

不相关嵌套查询：

1.从里向外求解，即每个子查询在上一级查询处理前求解，其结果作为父查询的查询条件

2.子查询的查询条件不依赖于父查询

3.子查询只执行一次

4.子查询可以单独运行

相关嵌套查询：

1.从外向里求解，子查询使用父查询带入的相关属性值，其结果作为父查询的查询条件

2.子查询的查询条件依赖于父查询

3.子查询执行多次，执行次数与父查询的元组数有关

4.子查询不可以单独运行

例子1：查询2000014号学生没选的课程

不相关嵌套：

SELECT *
FROM Course x
WHERE Cno NOT IN
(SELECT Cno
FROM SC
WHERE Sno='2000014');

相关嵌套:

SELECT *
FROM Course x
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno='2000014' AND Cno=x.T.Cno);

例子2：查询选修了全部课程的学生姓名

SELECT Sname /*Sno号学生不存在未被他选中的课程，即该学生选了全部课*/
FROM Student y
WHERE NOT EXISTS
(SELECT * /*所有未被Sno号学生选中的课程*/
FROM Course x
WHERE NOT EXISTS
(SELECT * /*被当前Sno号学生选中的某课程Cno*/
FROM SC
WHERE Sno=y.Sno AND Cno=x.Cno));

例子3：查询没有选修全部课程（即至少有一门课未选）的学生。

SELECT  Sname /*Sno号学生存在未被他选中的课程*/
FROM Student y
WHERE EXISTS
(SELECT * /*所有未被Sno号学生选中的课程*/
FROM Course x
WHERE NOT EXISTS
(SELECT * /*被Sno号学生选中的某课程Cno*/
FROM SC
WHERE Sno=y.Sno AND Cno=x.Cno));

---

之后记录题目：

1、查找收藏歌曲在2首及以上的用户及其收藏的歌曲数

SELECT UserName,COUNT(SongID) 
FROM collections 
GROUP BY UserName 
HAVING COUNT(SongID)>2;

2、查找所包含的歌曲数大于等于10首的专辑，显示专辑名和所包含的歌曲数

SELECT AlbumName,COUNT(songs.AlbumID) 
FROM albums,songs 
WHERE albums.AlbumID=songs.AlbumID 
GROUP BY AlbumName 
HAVING COUNT(songs.AlbumID)>=10;

3、查找最近10年来（now（））的专辑销售情况，列出专辑ID、专辑名称、总销售额，按总销售从高到低排名

SELECT albums.AlbumID,AlbumName,TotalPrice 
FROM albums,sales 
WHERE albums.AlbumID=sales.AlbumID AND year(now())-year(AlbumIssueDate)<10 
GROUP BY albums.AlbumID
ORDER BY TotalPrice DESC;

4、查询没有被收藏过的歌曲

嵌套查询：

SELECT *
FROM songs 
WHERE SongTitle NOT IN 
(SELECT SongTitle 
FROM songs RIGHT OUTER JOIN collections ON (songs.SongID=collections.SongID));

SELECT *
FROM songs
WHERE NOT EXISTS
(SELECT *
FROM users
WHERE EXISTS
(SELECT *
FROM collections
WHERE UserName=users.UserName AND SongID=songs.SongID));

非嵌套查询：

SELECT *
FROM songs LEFT OUTER JOIN collections ON (songs.SongID=collections.SongID)
WHERE collections.SongID IS NULL;

5、在购买了歌手刘欢专辑的客户中查询一次购买数量最多的客户的用户名

SELECT UserName AS UserBuyMost 
FROM orders,sales,albums 
WHERE orders.OrderID=sales.OrderID AND albums.AlbumID=sales.AlbumID AND albums.AlbumSinger="刘欢" 
GROUP BY sales.AlbumID
HAVING MAX(Quantity);

 用单值子查询：

SELECT UserName
FROM orders
WHERE OrderID IN(
SELECT OrderID
FROM albums,sales
WHERE albums.AlbumID=sales.AlbumID AND AlbumSinger="刘欢"
AND Quantity >= All(SELECT MAX(Quantity)
FROM sales
WHERE AlbumID IN
(SELECT AlbumID
FROM albums
WHERE AlbumSinger="刘欢")));

用多值子查询：

SELECT UserName
FROM orders
WHERE OrderID IN(
SELECT OrderID
FROM albums,sales
WHERE albums.AlbumID=sales.AlbumID AND AlbumSinger="刘欢"
AND Quantity>=ALL(SELECT Quantity
FROM sales,albums
WHERE albums.AlbumID=sales.AlbumID
AND AlbumSinger="刘欢"));

6、查找被所有用户收藏的歌曲（两种实现方式：相关嵌套查询、不相关嵌套查询）

不相关嵌套查询：

SELECT *
FROM songs
WHERE SongID IN
(SELECT SongID 
FROM collections
GROUP BY SongID
HAVING COUNT(*)=(SELECT COUNT(*)FROM users));

相关嵌套查询：

SELECT *
FROM songs
WHERE NOT EXISTS
(SELECT *
FROM users
WHERE NOT EXISTS
(SELECT *
FROM collections
WHERE UserName=users.UserName AND SongID=songs.SongID));

7、查找一首歌曲都没有收藏的用户（两种实现方式：相关嵌套查询、连接查询）

相关嵌套查询：

SELECT *
FROM users
WHERE NOT EXISTS
(SELECT *
FROM songs
WHERE EXISTS
(SELECT *
FROM collections
WHERE UserName=users.UserName AND SongID=songs.SongID));

连接查询：

SELECT *
FROM users LEFT OUTER JOIN collections ON (users.UserName=collections.UserName)
WHERE collections.UserName IS NULL;

---

本次记录就到这~