数据库－数据更新和视图

--(一)使用相应SQL语句,完成如下数据更新操作
--1.1根据订单信息更新orderInfo表中的payment(订单总金额)字段的值
UPDATE orderInfo
SET payment=(SELECT SUM(quantity*price)
	FROM orderBook,book 
	WHERE orderInfo.orderID=orderBook.orderID 
		AND orderBook.bookID=book.bookID)
--1.2新建图书订购情况统计表bookstas(包含图书编号,图书名称,图书类别,图书价格和订购次数,数据类型自定),并根据数据库的订单情况将人文社科类图书的订购情况插入表中
CREATE TABLE bookstas
(
bookID INT PRIMARY KEY,
title VARCHAR(20) NOT NULL UNIQUE,
categoryID INT NOT NULL,
price INT NOT NULL,
orderAmount INT 
);

INSERT INTO bookstas
SELECT book.bookID,title,categoryID,price,orderAmount=count(*)
FROM book,orderInfo,orderBook
WHERE orderInfo.orderID = orderBook.orderID
AND orderBook.bookID = book.bookID
AND categoryID = 2
GROUP BY book.bookID,title,categoryID,price

--1.3新建用户订购统计表userstas(包含用户编号,用户名,订单数量,总金额,数据类型自定),并根据数据库的订单情况将活跃用户(正常使用,锁定)的情况插入表中
CREATE TABLE userstas
(
userID INT PRIMARY KEY,
userName VARCHAR(20) NOT NULL,
orderAmount INT,
sumPrice INT
)
INSERT INTO userstas
SELECT userInfo.userID,userName,COUNT(*),SUM(quantity*price)
FROM userInfo,orderInfo,orderBook,book
WHERE userInfo.userID=orderInfo.userID
AND orderInfo.orderID=orderBook.orderID
AND orderBook.bookID=book.bookID
AND (userState = '正常使用'
OR userState = '锁定')
GROUP BY userInfo.userID,userName,userState
--1.4给用户表增加一字段level(等级),初始值为0,根据各用户订单总金额来给予评级(1:100~200元,2;200~300元,3:300元以上)
ALTER TABLE userInfo
ADD level INT DEFAULT 0
UPDATE userInfo SET level = 0

UPDATE userInfo
SET level = (SELECT SUM(quantity*price)/100
	FROM  orderInfo,orderBook,book
	WHERE userInfo.userID=orderInfo.userID
	AND orderInfo.orderID=orderBook.orderID
	AND orderBook.bookID=book.bookID
	)
--1.5用户102通过提交了一订单购买1001号图书1本,请问如何在数据库中完成上述操作?
INSERT orderInfo
VALUES(2016008,102,(SELECT price*1 FROM book WHERE bookID = 1001),DATENAME(YYYY,GETDATE()),'已完成')

UPDATE book
SET stockAmount=stockAmount-1
WHERE bookID=1001

UPDATE userstas
SET sumPrice=sumPrice+(SELECT price*1 FROM book WHERE bookID = 1001)
WHERE userID=102

UPDATE userInfo
SET level=(SELECT sumPrice/100 FROM userstas WHERE userID=102)
WHERE userID=102

--1.6给数据中库存在100本以上的图书增加库存,其中人文社科类图书分别增加200本,理工类图书分别增加300本
UPDATE book
SET stockAmount=stockAmount+300
WHERE stockAmount>100
AND categoryID=1

UPDATE book
SET stockAmount=stockAmount+200
WHERE stockAmount>100
AND categoryID=2

--1.7将数据库中处于锁定状态的用户的状态改为正常使用,同时重新设定其密码为"1001"
UPDATE userInfo
SET userState='正常使用',passord='0000'
WHERE userState='锁定'

--1.8将数据库中订单状态为'未提交'的订单状态修改为'已提交',同时给予该订单九折优惠.
UPDATE orderInfo
SET orderState='已提交',payment=payment*0.9
WHERE orderState='未提交'

--1.9将数据库中已经停用的用户及其订单信息全部删除
DELETE FROM userInfo WHERE userState='停用'

--(二)使用相应SQL语句,完成如下视图操作
--2.1创建视图View1,查看各订单的订单号,下单用户,订单总金额和订单状态
CREATE VIEW View1
AS 
	SELECT orderID,userID,payment,orderState
	FROM orderInfo
--2.2创建视图View2,查看各图书的编号,作者,名称,出版社,价格,订购次数
CREATE VIEW View2
AS 
	SELECT book.bookID,author,title,press,price,COUNT(*) AS orderAmount
	FROM book,orderBook
	WHERE book.bookID=orderBook.bookID
	GROUP BY book.bookID,author,title,press,price
--2.3创建视图View3,查看人文社科类图书的基本情况,并要求进行修改,插入和删除操作时仍能保证该视图满足图书类别为"人文社科类"这个条件.
CREATE VIEW View3
AS 
	SELECT bookID,title,author,press,price,categoryID,stockAmount
	FROM book
	WHERE categoryID=2
	WITH CHECK OPTION
--2.4通过视图View1,查看订单总额在100元以上(含100元)且状态为已完成的订单号,下单用户,订单总金额.
SELECT orderID,userID,payment
FROM View1
WHERE payment >=100
AND orderState='已完成'
--2.5通过视图View2,查看订购册数在平均值以上的图书编号,作者,名称,出版社,价格,订购册数,并按订购册数降序排列.
SELECT bookID,author,title,press,orderAmount
FROM View2
WHERE orderAmount > (SELECT AVG(orderAmount) FROM View2)
ORDER BY orderAmount DESC
--2.6通过视图View3,将库存在200本以上的图书的库存分别减少50本.
UPDATE View3
SET stockAmount=stockAmount-50
WHERE stockAmount>200
--2.7往View3中插入数据(内容自定),观察什么样的数据才可以成功插入
INSERT INTO View3 VALUES(1007,'人性的弱点','卡耐基','燕山出版社',32,1,100)
--2.8删除视图View1
DROP VIEW View1