数据库——数据查询

--1 使用相应SQl语句，完成如下基本查询操作
--查询所有用户的用户ID和姓名
SELECT userID,userName
FROM userInfo

--查询年龄最3位用户的用户ID，姓名和年龄
SELECT top(3)userID,userName,2019-year(birthdate) age
FROM userInfo
ORDER BY age

--查询库存量小于50本的所有图书信息
SELECT*FROM book
WHERE stockAmount < 50

--查询清华大学出版社的所有图书信息

SELECT*FROM book
WHERE press = '清华大学出版社'

--查询价格在50-100元的所有图书的书名
SELECT title
FROM book
WHERE price>=50
AND price<=100

--查询姓“张”或姓“王”或姓“李”且单名的用户情况
SELECT*
FROM userInfo
WHERE userName like '张_' 
OR userName like '王_'
OR userName like '李_'

--查询所有图书的书名，出版社及价格，要求出版社升序排列，出版社相同时，按照价格从高到底进行排序
SELECT title,press,price
FROM book
ORDER　BY press,price DESC

--查询所有有订单记录的用户ID
SELECT distinct userID
FROM orderInfo

--2、使用相应的SQL语句，完成如下数据汇总操作
--查询理工类图书的最高价格，最低价格及平均价格
SELECT MAX(price) MAN_price,MIN(price) MIN_price,AVG(price) AVG_price
FROM book
WHERE book.categoryID=1

--查询所有理工类图书的库存总量
SELECT SUM(stockAmount) SUM_stockAmount
FROM book
WHERE book.categoryID=1

--查询“1001”号图书被订购的次数
SELECT SUM(quantity) SUM_stockAmount
FROM orderbook
WHERE bookID = 1001

--查询不同状态订单的数量
SELECT orderState,SUM(quantity) AS itemCount
FROM orderInfo,orderbook
WHERE orderInfo.orderID=orderbook.orderID
GROUP BY orderState

--查询各类别图书的库存总量
SELECT categoryID,SUM(stockAmount) AS Amount
FROM book
GROUP BY categoryID

--查询被订购2次以上(含2次)的图书ID、订购次数，并按照订购次数从高到底进行排序
SELECT bookID,SUM(quantity) AS orderAmount
FROM orderbook
GROUP BY bookID 
HAVING SUM(quantity)>=2
ORDER BY SUM(quantity) DESC

--3 使用相应SQL语句，完成如下连接查询操作
--1 查询购买过“1001”号图书的全部用户的用户ID，姓名和状态
SELECT userInfo.userID,userInfo.userName,userInfo.userState
FROM orderbook,orderInfo,userInfo
WHERE orderbook.orderID=orderInfo.orderID
AND userInfo.userID= orderInfo.userID
AND bookID = 1001

--2查詢购买过“1001”号图书的用户名，性别及购买时间，并按照购买时间降序排列。
SELECT userInfo.userName,userInfo.sex,orderTime
FROM orderbook,orderInfo,userInfo
WHERE orderbook.orderID=orderInfo.orderID
AND userInfo.userID= orderInfo.userID
AND bookID = 1001
ORDER BY orderTime DESC

--3查询性别为“男”且购买过人文社科类图书的用户ID，用户名及状态
SELECT userInfo.userID,userInfo.userName,userInfo.userState
FROM userInfo,orderInfo,orderBook,book
WHERE userInfo.userID = orderInfo.userID
AND orderInfo.orderID = orderBook.orderID
AND orderBook.bookID = book.bookID
AND sex = '男'
AND categoryID = '2'

--4查询价格在37元以上(含37元)且被购买过2次以上的图书名称，价格，出版社及购买次数，并按照购买次数降序排列。
SELECT
title,price,press,
(SELECT COUNT(quantity) FROM orderBook WHERE orderBook.bookID=book.bookID ) AS orderAmount
FROM book
WHERE price >= 37
AND　EXISTS
(SELECT orderBook.bookID FROM orderBook
WHERE orderBook.bookID=book.bookID
GROUP BY orderBook.bookID
HAVING COUNT(quantity)>=2)
ORDER BY orderAmount DESC

--5查询用户ID为“102”的所有订单号，下单日期及状态
SELECT DISTINCT orderInfo.orderID,orderTime,orderState
FROM orderInfo,orderBook
WHERE orderInfo.orderID = orderBook.orderID
AND orderInfo.userID='102'

--6查询订单状态为“已支付”的所有订单的订单号，下单用户，图书名称，图书类别，数量信息，并按照订单号排序
SELECT orderInfo.orderID,userID,title,categoryID,quantity
FROM orderInfo,orderBook,book
WHERE orderInfo.orderID = orderBook.orderID
AND orderBook.bookID = book.bookID
AND orderInfo.orderState = '已支付'
ORDER　BY　orderInfo.orderID

--4.使用相应SQL语句，完成如下嵌套查询操作：
--1查询订购次数在平均购买次数以上的图书ID，书名，价格及订购次数，并按订购次数排序
SELECT book.bookID,title,price,COUNT(quantity) orderCount
	FROM book,orderBook
	WHERE book.bookID=orderBook.bookID
	GROUP BY book.bookID,title,price
		HAVING COUNT(quantity)> 
		(SELECT COUNT(quantity)/COUNT(DISTINCT bookID) FROM orderBook)

--2查询至少包含“2016003”号订单包含的图书的订单号，下单用户，下单日期及订单状态
SELECT orderInfo.orderID,userID,orderTime,orderState
	FROM orderInfo
	WHERE NOT EXISTS
	(SELECT * FROM orderBook A WHERE A.orderID = 2016003
	AND NOT EXISTS 
	(SELECT * FROM orderBook B WHERE A.bookID = B.bookID AND B.orderID=orderInfo.orderID))

--3查询与王丽购买过同种书籍的所有人的信息
SELECT DISTINCT userInfo.*
	FROM userInfo,orderInfo,orderBook
	WHERE orderInfo.orderID=orderBook.orderID
		AND orderInfo.userID=userInfo.userID 
		AND bookID IN
		(SELECT bookID FROM userInfo,orderInfo,orderBook
			WHERE orderInfo.orderID=orderBook.orderID
				AND orderInfo.userID=userInfo.userID
				AND userName='王丽')
--4找出每个客户超过他购买的书的平均价格的图书信息。
SELECT userID,book.*
	FROM orderInfo,orderBook,book
	WHERE orderInfo.orderID=orderBook.orderID
		AND orderBook.bookID=book.bookID
		AND price>
			( SELECT AVG(price)
				FROM orderInfo A,orderBook B,book C
				WHERE A.orderID=B.orderID
					AND B.bookID=C.bookID
					AND A.userID=orderInfo.userID)
--5查询购买张三购买了的全部书籍的客户信息
SELECT A1.*
	FROM userInfo A1
	WHERE NOT EXISTS
	(SELECT * FROM userInfo B1,orderInfo B2,orderBook B3 
	    WHERE B1.userID=B2.userID AND B2.orderID=B3.orderID
		AND B1.userName='张三'	AND NOT EXISTS
		(SELECT * FROM orderInfo C2,orderBook C3 
			WHERE C2.orderID=C3.orderID 
			AND C2.userID=A1.userID AND C3.bookID=B3.bookID))
--5 使用相应SQL语句，完成如下集合查询操作
--1查询性别为“男”且购买过人文社科类图书或性别为“女”且购买过理工类图书的用户ID，用户名及状态。
SELECT DISTINCT userInfo.userID,userName,userState
	FROM userInfo,orderInfo,orderBook,book
	WHERE userInfo.userID=orderInfo.userID
	AND orderInfo.orderID=orderBook.orderID
	AND orderBook.bookID=book.bookID
	AND userInfo.sex='男'
	AND book.categoryID=2
UNION
SELECT DISTINCT userInfo.userID,userName,userState
	FROM userInfo,orderInfo,orderBook,book
	WHERE userInfo.userID=orderInfo.userID
	AND orderInfo.orderID=orderBook.orderID
	AND orderBook.bookID=book.bookID
	AND userInfo.sex='女'
	AND book.categoryID=1
--2查询性别为“男”且购买过人文社科类图书的用户ID，用户名及状态。
SELECT DISTINCT userInfo.userID,userName,userState
	FROM userInfo,orderInfo,orderBook,book
	WHERE userInfo.userID=orderInfo.userID
	AND orderInfo.orderID=orderBook.orderID
	AND orderBook.bookID=book.bookID
	AND userInfo.sex='男'
	AND book.categoryID=2
--3查询购买过人文社科类图书但不包含下单次数为1次的用户ID，用户名及状态。
SELECT DISTINCT userInfo.userID,userName,userState
	FROM userInfo,orderInfo,orderBook,book
	WHERE userInfo.userID=orderInfo.userID
	AND orderInfo.orderID=orderBook.orderID
	AND orderBook.bookID=book.bookID
	AND book.categoryID=2
EXCEPT
SELECT userInfo.userID,userName,userState
	FROM userInfo,orderInfo,orderBook,book
	WHERE userInfo.userID=orderInfo.userID
	AND orderInfo.orderID=orderBook.orderID
	AND orderBook.bookID=book.bookID
	AND book.categoryID=2
	GROUP　BY　userInfo.userID,userName,userState
	HAVING COUNT(userInfo.userID)<2