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

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

先列一下知识点：

（一）集合查询

（1）并操作

格式：

<查询块> UNION <查询块>

参加UNION操作的各结果表的列数必须相同，对应项的数据类型也必须相同

例子：查询选修了1024或1136课程的学生

SELECT Sno
FROM SC
WHERE Cno= '1024'
UNION
SELECT Sno
FROM SC
WHERE Cno= '1136'

（2）交操作

格式：

<查询块> INTERSECT <查询块>

参加INTERSECT操作的各结果表的列数必须相同，对应项的数据类型也必须相同

例子：查询同时选修了1024和1136课程的学生

SELECT Sno
FROM SC 
WHERE Cno= '1024'
INTERSECT
SELECT Sno
FROM SC 
WHERE Cno= '1136'

（3）差操作

格式：

<查询块> EXCEPT <查询块>

参加EXCEPT操作的各结果表的列数必须相同，对应项的数据类型也必须相同

例子：查询选修了1024但没有选修1136课程的学生

SELECT Sno
FROM SC 
WHERE Cno= '1024'
EXCEPT
SELECT Sno
FROM SC 
WHERE Cno= '1136'

（二）视图

视图是从一个或几个基本表（或视图）导出的表，视图中的数据改变基表的数据也会改变，基表中的数据发生变化，从视图中查询出的数据也随之改变

（1）定义格式：

CREATE VIEW <视图名>[(<列名>[, <列名>]...)] AS <子查询>

说明：

<子查询>可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语

（除非在SELECT子句中添加TOP n关键字）（这里在后面实例题中有更详细说明）

（2）视图的种类：

单表视图：一个基本表的行列子集

多表视图：多个基本表的行列子集

视图的视图：建立在一个或多个已定义好的视图上的视图

虚拟列视图：带表达式/计算视图

分组视图：含GROUP子句及聚集函数

（3）删除视图

格式：

DROP VIEW <视图名>

（4）视图的作用

1.视图能够简化用户的操作

2.视图使用户能以多种角度看待同一数据，适应数据库共享的需要

3.视图对重构数据库提供了一定程度的逻辑独立性

4.视图能够对机密数据提供安全保护，隐藏 了底层的表结构，用户只能看到视图提供的数据

5.适当的利用视图可以更清晰的表达查询

（三）数据更新

（1）批量插入数据

格式：

INSERT INTO <表名> (<属性列1> [, <属性列2>...] ) <子查询>

例子：每一个学生 都要选修 高等数学（1128）课，将选课信息加入表SC中

INSERT INTO SC(Sno, Cno)
SELECT Sno, '1128'
FROM Student

（2）批量修改数据

格式：

UPDATE <表名>
SET <列名>=<子查询> [，<列名>=<表达式> ]
[ WHERE <含子查询的条件> ]

例子：将计算机系全体学生的“数据库原理”成绩修改为空值

UPDATE SC
SET Grade = NULL
WHERE Cno IN
(SELECT Cno 
FROM Course
WHERE Cname='数据库原理')
AND Sno IN
(SELECT Sno 
FROM Student
WHERE Sdept = '计算机');

（3）批量删除数据

格式：

DELETE FROM <表名> [WHERE <含子查询的条件>] 

例子：删除计算机系所有学生的选课记录

DELETE FROM SC 
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='计算机');

---

之后记录题目：

1、商家开展“买200赠100”的促销活动，为一次购买某专辑总价格在200元（含）以上的用户的会员卡（字段：User表的UserAdvancePayment）充值100元。（只充值一次）

UPDATE users SET UserAdvancePayment=0;
UPDATE users SET UserAdvancePayment=UserAdvancePayment+100
WHERE UserName IN 
(SELECT UserName 
FROM sales,orders
WHERE sales.OrderID=orders.OrderID AND TotalPrice>=200);

2、新建“专辑销售统计表”Sales_Statis，其结构能存放专辑名、销售量和销售额。将各个专辑销售量和销售额的统计结果存入Sales_Statis表中

CREATE TABLE Sales_Statis(
AlbumName varchar(64) not null,
Quantity int null,
TotalPrice numeric(10, 2) null
);
INSERT INTO Sales_Statis
SELECT AlbumName,Quantity,TotalPrice
FROM albums,
(SELECT AlbumID,SUM(Quantity) AS Quantity,SUM(TotalPrice) AS TotalPrice 
FROM sales
GROUP BY AlbumID) AS s
WHERE albums.AlbumID=s.AlbumID;

3、创建专辑名为“三十而立”（可自定义其他名称）的歌曲视图SongsByAlbumName，该视图包括“歌曲表”中的所有列以及专辑名称，并按歌曲顺序号由小到大排列

4、将此视图中选择任意SongId，修改歌曲名称为“我就想改改名字看看能不能成功”，看看是否成功？

5、将此视图中选择任意SongId，修改歌曲名称为“我就想改改名字看看能不能成功”，专辑名称修改为“我还想改一下专辑试试”，看看是否成功？

6、对于视图是否能修改成功，相关的因素有哪些？请一一列举并举例说明，附上测试结果

CREATE VIEW SongsByAlbumName 
AS 
SELECT TOP 100 PERCENT songs.*,albums.AlbumName
FROM songs,albums 
WHERE songs.AlbumID=albums.AlbumID AND albums.AlbumName="三十而立"
ORDER BY songs.SongID ASC;

注意：select + order by 的组合返回的是游标而非table，直接select传给view再update的时候会报错Incorrect usage of UPDATE and ORDER BY，因而必须加上 top 100 percent转化成table，但是mysql不支持TOP语句，mysql中可以用LIMIT语句来代替，

格式为：

SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset;

第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目，若值为5，10，则检索记录行6-15，但是LIMIT没法像TOP那样选中全部数据，只能设定一个比较大的区间，但LIMIT可以选择位于中间区段的数据，本题可以写为：

CREATE VIEW SongsByAlbumName 
AS 
SELECT songs.*,albums.AlbumName
FROM songs,albums 
WHERE songs.AlbumID=albums.AlbumID AND albums.AlbumName="三十而立"
ORDER BY songs.SongID ASC LIMIT 0,100;

但这样写会报错The target table SongsByAlbumName of the UPDATE is not updatable

当视图中包含有如下内容时，视图的更新操作将不能被执行：
（1）视图中不包含基表中被定义为非空的列
（2）在定义视图的SELECT语句后的字段列表中使用了数学表达式
（3）在定义视图的SELECT语句后的字段列表中使用聚合函数
（4）在定义视图的SELECT语句中使用了DISTINCT、UNION、TOP、GROUP BY、ORDER BY或HAVING子句

所以最好还是不要对视图进行排序了，题目也要求的是ASC，视图默认排列顺序也是ASC，直接这样写：

CREATE VIEW SongsByAlbumName 
AS 
SELECT songs.*,albums.AlbumName
FROM songs,albums 
WHERE songs.AlbumID=albums.AlbumID AND albums.AlbumName="三十而立";

如果想要查询倒序的视图，使用以下语句：

SELECT * FROM SongsByAlbumName ORDER BY SongID DESC;

按照以上语句写的就可以进行update了：

UPDATE SongsByAlbumName
SET SongTitle ="我就想改改名字看看能不能成功"
WHERE SongID=8;

当想要同时更改两个变量，如下所写：

UPDATE SongsByAlbumName
SET SongTitle ="我就想改改名字看看能不能成功",AlbumName="我还想改一下专辑试试"
WHERE SongID=6;

就会报错：Can not modify more than one base table through a join view 'songalbum.songsbyalbumname'

因此基表要分开一个个更新，如下所写：

UPDATE SongsByAlbumName
SET SongTitle ="我就想改改名字看看能不能成功"
WHERE SongID=8;
UPDATE SongsByAlbumName
SET AlbumName="我还想改一下专辑试试"
WHERE SongID=8;

这样就可以成功运行了，此时原表中的歌曲名和专辑名就会改变，视图表因为原表中专辑名改变会清空

---

本次记录就到这，下次见~