订正SQL+第三周

第一周的word评讲

NOT NULL 漏写NOT 

对于多对多的关系，应该写m对n而不能写两个相同的

可能有别的

---

第三周作业

4.⑤

USE BookStore
SELECT CustomerCode AS 客户号,SUM(TotalPrice) AS 订单总额,
CASE
WHEN SUM(TotalPrice)>=200 THEN '强'
WHEN SUM(TotalPrice)>=100 THEN '较强'
WHEN SUM(TotalPrice)>=50 THEN '一般'
WHEN SUM(TotalPrice)<50 THEN '较弱'
END AS 购买力
FROM [Order]
GROUP BY CustomerCode

语法：CASE

 因为一个客户可能多次购买，所以SUM(TotalPrice)是专门计算一个个客户的。

必须写GROUP BY ,它的功能是分类汇总，把具有相同值的记录合并成一条记录

⑥

USE BookStore
DECLARE @totalmoney money, @text varchar(100)
SET @totalmoney = (SELECT SUM(TotalPrice) FROM [Order] WHERE CustomerCode='1201')
IF @totalmoney > 0
   SET @text = '1201号客户订购的图书总金额是：'+CONVERT(varchar(12),@totalmoney,0)
ELSE 
   SET @text = '1201号客户没有订购图书'
Print @text

不明白为什么[Order]表一定有[]

5.多表连接查询

①

USE BookStore
SELECT Customer.CustomerCode,Name,VIPClass,CustomerEvaluation.EvaluateDate
FROM Customer JOIN CustomerEvaluation
ON Customer.CustomerCode=CustomerEvaluation.CustomerCode

②查询《神曲》的销售总量及销售总额

USE BookStore
SELECT Book.BookName AS 书名,SUM(OrderDetail.Amount) AS 销售总量,SUM(TotalPrice) AS 销售总额
FROM Book JOIN OrderDetail JOIN [Order]
ON OrderDetail.OrderCode=[Order].OrderCode
ON Book.BookCode=OrderDetail.BookCode
GROUP BY BookName
HAVING Book.BookName='神曲'

FROM ... JOIN...

ON的顺序不可颠倒，否则报错,必须从后往前

好像懂了GROUP BY ... HAVING 的用意

错误版本：

USE BookStore
SELECT Book.BookName AS 书名,SUM(OrderDetail.Amount) AS 销售总量,SUM(TotalPrice) AS 销售总额
FROM Book JOIN OrderDetail JOIN [order]
ON Book.BookCode=OrderDetail.BookCode
ON 
WHERE Book.BookName='神曲'

因为找不到【order】跟其他表共有的属性 

③

USE BookStore
SELECT Customer.Name AS 客户,[Order].OrderCode AS 订单号,BookName AS 图书名,Amount AS 册数,
Price AS 单价,Amount*Book.Price AS 总价,OrderTime AS 订购时间
FROM Customer INNER JOIN [Order] JOIN OrderDetail JOIN Book 
ON OrderDetail.BookCode=Book.BookCode
ON [Order].OrderCode=OrderDetail.OrderCode
ON Customer.CustomerCode=[Order].CustomerCode
WHERE Name='刘炎林' ORDER BY Amount*Price

首先如果SELECET的属性独一无二，那么不需要前缀；

这种方法INNER JOIN

最后的是WHERE / ORDER BY 而不是之前的GROUP BY / HAVING ...

猜测原因：上面②需要进行计算--整合！

④

USE BookStore
SELECT Customer.CustomerCode,Name,[Order].OrderCode,OrderTime
FROM Customer LEFT JOIN [Order]
ON Customer.CustomerCode=[Order].CustomerCode

⑤

USE BookStore
SELECT Book.BookCode,BookName,SUM(Amount) AS 销售总量
FROM Book LEFT JOIN OrderDetail
ON Book.BookCode=OrderDetail.BookCode
GROUP BY Book.BookCode,BookName

错误：跟 报错提示+答案 比对，加个BookName就可以

所以为什么一定要写，感觉不写也可以

6.复制数据表

①

USE BookStore
SELECT Name,BookName,Amount INTO CustomerBuyBook
FROM Book JOIN OrderDetail JOIN [Order] JOIN Customer
ON [Order].CustomerCode=Customer.CustomerCode
ON OrderDetail.OrderCode=[Order].OrderCode
ON Book.BookCode=OrderDetail.BookCode

重点：单纯只有Book,OrderDetail和Customer的话不够连接所以JOIN得时候多加了【Order】

②

USE BookStore
SELECT Customer.CustomerCode,Name,Hometown,VIPClass INTO CustomerClass
FROM Customer JOIN CustomerEvaluation
ON Customer.CustomerCode=CustomerEvaluation.CustomerCode
WHERE Sex='女'

7.①

USE BookStore
INSERT INTO Publisher
values ('66','群众出版社','0321-76584391')

②

USE BookStore
INSERT INTO OrderDetail
values ('08110801','0701','3')

③

USE BookStore
INSERT INTO Customer(CustomerCode,Name,Sex,Telephone)
VALUES ('6001','王岚','女','87654390')

④

USE BookStore
INSERT INTO CustomerClass(CustomerCode,Name,VIPClass)
SELECT Customer.CustomerCode,Name,VIPClass

FROM Customer JOIN CustomerEvaluation
ON Customer.CustomerCode=CustomerEvaluation.CustomerCode
WHERE Name LIKE '杨%'

 

7.UPDATE修改

①

USE BookStore
UPDATE Publisher
SET Telephone='010-79797979'
WHERE PublisherCode='01'

②

USE BookStore
UPDATE Book
SET Price=Price*1.1,Discount=0.8
WHERE BookSort='外语'

③

USE BookStore
UPDATE CustomerEvaluation
SET VIPClass='C' ,EvaluateDate='2023-10-1'
WHERE VIPClass='D' AND Year(EvaluateDate)<2008

④

USE BookStore
UPDATE [Order] 
SET TotalPrice=(SELECT SUM(OrderDetail.Amount*Book.Price*Book.Discount*	PrivilegeStandard.Discount)
FROM PrivilegeStandard JOIN CustomerEvaluation
ON PrivilegeStandard.VIPClass = CustomerEvaluation.VIPClass
JOIN [Order] ON CustomerEvaluation.CustomerCode = [Order].CustomerCode
JOIN OrderDetail ON OrderDetail .OrderCode=[Order].OrderCode
JOIN Book ON OrderDetail.BookCode=Book.BookCode
GROUP BY [Order].OrderCode
HAVING [Order].OrderCode='08110801')
WHERE [Order].OrderCode='08110801'

有PrivilegeStandard.Discount

嵌套，不明白已经限制了订单号，这样子应该只有一个了呀

为什么需要再HAVING处加)

⑤

USE BookStore
UPDATE CustomerEvaluation
SET VIPClass='B'
WHERE CustomerCode IN 
(SELECT CustomerCode FROM [Order]
GROUP BY CustomerCode
HAVING SUM(TotalPrice) BETWEEN 100 AND 200)

9.①

USE BookStore
DELETE FROM Publisher
WHERE Publisher='群众出版社'

②

USE BookStore
DELETE FROM CustomerBuyBook
WHERE Amount<7

③

USE BookStore
DELETE FROM CustomerClass
WHERE Name LIKE '刘%' OR Name LIKE '杨%'

④

USE BookStore
DELETE FROM CustomerEvaluation
WHERE CustomerCode NOT IN
(SELECT DISTINCT CustomerCode FROM [Order])