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

注：插入数据详见sql.zip压缩包

链接: https://pan.baidu.com/s/1LQ1J5kvgNyCo1VPAV1cmgg提取码: 5crf 
 

2.查询

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

SELECT 
	a.AlbumName,
    COUNT(s.AlbumID) AS '包含歌曲数'
FROM
    Songs s
	JOIN Albums a ON a.AlbumID = s.AlbumID
GROUP BY s.AlbumID HAVING COUNT(s.AlbumID) >= 10;

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

SELECT 
	a.AlbumID,
    a.AlbumName,
    SUM(s.TotalPrice) AS TotalPrice
FROM Sales s        
JOIN Albums a ON a.AlbumID = s.AlbumID     
WHERE year(now())-year(a.AlbumIssueDate) < 10
GROUP BY s.AlbumID 
ORDER BY s.TotalPrice DESC;

遇到的问题：

1. 无可用数据以验证查询语句
2. 查询时报错（Erro Code 1055）

解决方法：

1. 插入新数据，插入了2021的一张专辑及其相关数据（数据文件见文章开头）
2. 查询相关资料，执行以下语句：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','');

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

SELECT *
FROM Songs s
WHERE s.SongID NOT IN(SELECT SongID
						FROM Collections c);

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

SELECT o.UserName
FROM Sales s
LEFT JOIN Albums a ON a.AlbumID = s.AlbumID
LEFT JOIN Orders o ON o.OrderID = s.OrderID
WHERE a.AlbumSinger = '刘欢' AND s.Quantity IN (SELECT MAX(s.Quantity)
													 FROM Sales s
													 LEFT JOIN Albums a ON a.AlbumID = s.AlbumID
                                                     WHERE a.AlbumSinger = '刘欢');

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

-- 相关嵌套查询
SELECT SongTitle
FROM songs s
WHERE NOT EXISTS
(SELECT *
FROM users u
WHERE NOT EXISTS
(SELECT *
FROM collections
WHERE collections.SongID = s.SongID AND collections.UserName= u.UserName));

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

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

-- 连接查询1（左外连接）
SELECT DISTINCT UserRealName 
FROM users u
LEFT JOIN collections c ON u.UserName=c.UserName
WHERE c.UserName IS NULL;

-- 连接查询2（右外连接）
SELECT DISTINCT
    u.UserName
FROM
    Collections c
    RIGHT JOIN Users u ON u.UserName = c.UserName
WHERE c.UserName IS null

-- 相关嵌套
SELECT 
    u.UserName
FROM
    Users u
    WHERE NOT EXISTS (SELECT c.UserName
									FROM Collections c
                                    WHERE c.Username = u.Username)