数据库原理与应用 构建音乐商店，实现复杂查询和批量操作及视图练习

构建音乐商店NetMusicShop，并实现复杂查询 

/*查找收藏歌曲在2首及以上的用户及其收藏的歌曲数*/
SELECT UserRealName,count(collections.SongID)
FROM users,collections,songs
WHERE users.UserName=collections.UserName
AND songs.SongID=collections.SongID
GROUP BY collections.UserName
HAVING COUNT(collections.SongID)>=2;

结果： 

 

/*查找所包含的歌曲数大于等于10首的专辑，显示专辑名和所包含的歌曲数*/
SELECT AlbumName,count(songs.AlbumID)
FROM albums,songs
WHERE albums.AlbumID=songs.AlbumID
GROUP BY songs.AlbumID
HAVING count(songs.AlbumID)>=10;

结果：

 

/*查找最近10年来（now（））的专辑销售情况，列出专辑ID、专辑名称、总销售额，按总销售从高到低排名*/
SELECT albums.AlbumID,AlbumName,sales.TotalPrice
FROM albums,sales
WHERE (now()-albums.AlbumIssueDate)<10
ORDER BY sales.TotalPrice DESC;

 

/*查询没有被收藏过的歌曲*/
SELECT DISTINCT SongTitle
FROM songs,collections
WHERE songs.SongID NOT IN(SELECT SongID
FROM collections);

 

/*在购买了歌手"香港杂锦合辑"(刘欢)专辑的客户中查询一次购买数量最多的客户的用户名*/
SELECT orders.ReceiverName
FROM albums,sales,orders
WHERE albums.AlbumSinger='香港杂锦合辑'
AND orders.OrderID=sales.OrderID
AND albums.AlbumID=sales.AlbumID
GROUP BY sales.AlbumID
HAVING MAX(sales.Quantity);

 

 

/*查找被所有用户收藏的歌曲（两种实现方式，相关嵌套查询、不相关嵌套查询）*/
SELECT songTitle  /*相关嵌套查询*/
FROM songs y
WHERE NOT EXISTS
(SELECT *
FROM users x
WHERE NOT EXISTS
(SELECT *
FROM collections
WHERE collections.SongID=y.SongID AND collections.UserName=x.UserName));

 

SELECT DISTINCT SongTitle  /*不相关嵌套查询*/
FROM songs
WHERE SongID IN(SELECT SongID FROM collections
	              GROUP BY SongID HAVING COUNT(UserName)=(SELECT COUNT(*) FROM users));

 

/*查找一首歌曲都没有收藏的用户（两种实现方式：相关嵌套查询、连接查询）*/
SELECT DISTINCT UserRealName  /*嵌套查询*/
FROM collections,users
WHERE users.UserName NOT IN(SELECT UserName
FROM collections);

 

SELECT DISTINCT UserRealName   /*连接查询*/
FROM users
LEFT JOIN collections ON users.UserName=collections.UserName
WHERE collections.UserName IS NULL;

 

批量操作及视图练习

/*商家开展“买200赠100”的促销活动，为一次购买某专辑总价格在200元（含）以上的用户的会员卡（字段：User表的UserAdvancePayment）充值100元。（只充值一次）*/
UPDATE users SET UserAdvancePayment='0';
UPDATE users SET UserAdvancePayment=+100	
WHERE UserAdvancePayment IN
(SELECT UserAdvancePayment
FROM sales,orders
WHERE sales.OrderID=orders.OrderID
AND users.UserName=orders.UserName
AND sales.TotalPrice>200);

 

/*新建“专辑销售统计表”Sales_Statis，其结构能存放专辑名、销售量和销售额。将各个专辑销售量和销售额的统计结果存入Sales_Statis表中*/
CREATE TABLE Sales_Statis(
AlbumID int auto_increment primary key,
AlbumName varchar(64) not null,
Quantity int,
TotalPrice numeric(6,2));

INSERT
INTO Sales_Statis(AlbumName,Quantity,TotalPrice)
SELECT AlbumName,SUM(Quantity),SUM(TotalPrice)
FROM albums,sales
WHERE albums.AlbumID=sales.AlbumID
GROUP BY AlbumName;

 

/*创建专辑名为“EyeFever演唱会09”（可自定义其他名称）的歌曲视图SongsByAlbumName，该视图包括“歌曲表”中的所有列以及专辑名称，并按歌曲顺序号由小到大排列*/
CREATE VIEW SongsByAlbumName AS
SELECT s.*,a.AlbumName
FROM albums a,songs s
WHERE AlbumName='Eye Fever演唱会09'
AND s.AlbumID=a.AlbumID;

/*将此视图中选择任意SongId，修改歌曲名称为“我就想改改名字看看能不能成功”，看看是否成功？*/
UPDATE SongsByAlbumName SET SongTitle='我就想改改名字看看能不能成功' WHERE SongID=1;
能成功

/*将此视图中选择任意SongId，修改歌曲名称为“我就想改改名字看看能不能成功”，专辑名称修改为“我还想改一下专辑试试”，看看是否成功？*/
UPDATE SongsByAlbumName SET AlbumName='我还想改一下专辑试试' WHERE SongID=1;
不能成功