Sql server 第三章

在数据库BookDB里进行如下操作：

3.1 查询1991年出生的读者姓名、工作单位和身份证号。

SELECT readerName,workUnit,identitycard
FROM Reader
WHERE CONVERT(int,SUBSTRING(identitycard,7,4))=1991

涉及到的函数用法：

(1)convert函数：convert(data_type,expression[,style])

convert函数的日期转换用法：conver(data_type(length),data_to_be_converted,style)

其中data_type(length)是 规定目标数据类型（带有可选的长度）。data_to_be_converted是 含有需要转换的值。style是 规定日期/时间的输出格式。

style的值具体详见下图：

 例如：

convert(char(10),birthday,120)convert(char(10),birthday,120)

该代码可将生日列查询结果以yyyy-mm-dd的形式输出

(2)substring函数：用来截取一个字段的其中一部分，括号中数字‘7’表示截取的起始位置是从该字符串第七个字符开始，‘4’表示截取后得到的字符串长度为4个字符。（身份证号的7~11位为出生年份）。这是最基础的语法，适用于截取字符位置固定的类型。

当我们的需求变得比较复杂，例如以下例子：

我们只想要得到’roomno’中的房间号，发现起始字符位置并不是固定的，而且，我们需要的房间号长度也不固定。此时，需要运用‘charindex’函数 。具体详见http://t.csdn.cn/3b0pW

3.2 查询在信息管理学院工作的读者编号、姓名和性别。

SELECT readerNo,readerName,sex=CASE 
     sex 
       WHEN 'M' THEN '男' 
       WHEN 'F' THEN '女' 
     END
FROM Reader
WHERE workUnit='信息管理学院'

3.3 查询图书名中含有“数据库”的图书的详细信息。

SELECT *
FROM Book
WHERE bookName LIKE '%数据库%'

3.4 查询在2015-2016年之间入库的图书编号、出版时间、入库时间和图书名称，并按入库时间排序输出。

SELECT bookNo,publishingDate,shopDate,bookName
FROM Book
WHERE YEAR(shopDate) BETWEEN 2015 AND 2016
ORDER BY shopDate desc

3.5 查询读者喻自强借阅的图书编号、图书名称、借书日期和归还日期。

SELECT Book.bookNo,bookName,borrowDate,returnDate
FROM Book,Borrow
WHERE Book.bookNo=Borrow.bookNo
	AND readerNo IN(
		SELECT readerNo
		FROM Reader
		WHERE readerName='喻自强'
	)

3.6 查询借阅了清华大学出版社出版的图书的读者编号、读者姓名、图书名称、借书日期和归还日期。

SELECT Reader.readerNo,readerName,bookName,borrowDate,returnDate
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
	AND Borrow.bookNo=Book.bookNo
	AND publishingName='清华大学出版社'

3.7 查询会计学院没有归还所借图书的读者编号、读者姓名、图书名称、借书日期和应归还日期。

SELECT Reader.readerNo,readerName,bookName,borrowDate,shouldDate
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
	AND Borrow.bookNo=Book.bookNo
	AND workUnit='会计学院' AND returnDate IS NULL

简便方法为将三张表合并创建一张大表v_rb

3.8 查询借书时间在2015-2016年之间借阅但未归还图书的读者编号、读者姓名以及借阅未归还的图书的图书编号、图书名称、借书日期。

SELECT Reader.readerNo,readerName,Book.bookNo,bookName,borrowDate
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
	AND Borrow.bookNo=Book.bookNo
	AND YEAR(borrowDate) BETWEEN 2015 AND 2016
	AND returnDate is null

3.9 查询每种类别的图书分类号、分类名称、最高价格和平均价格，并按最高价格的升序输出。

SELECT b.classNo,className,MAX(price) AS maxPrice,AVG(price) AS avgPrice
FROM Book b,BookClass
GROUP BY b.classNo,className
ORDER BY MAX(price)

3.11 查询在借图书的总价在50元以上的读者编号、读者姓名和所借图书的总价。

SELECT Reader.readerNo,readerName,SUM(price) AS money
FROM Reader,Borrow,Book
WHERE Reader.readerNo=Borrow.readerNo
	AND Borrow.bookNo=Book.bookNo
	AND returnDate IS NULL
GROUP BY Reader.readerNo,readerName
HAVING SUM(price)>=50

3.14 查询没有借阅图书的读者姓名和工作单位（分别使用IN子查询和存在量词子查询表达）。

--use IN
SELECT readerName，workUnit
FROM Reader
WHERE readerNo NOT IN(
	SELECT readerNo
	FROM Borrow
	WHERE returnDate IS NULL
)
--use EXISTS
SELECT readerName，workUnit
FROM Reader
WHERE NOT EXISTS(
	SELECT *
	FROM Borrow
	WHERE Reader.readerNo=Borrow.readerNo
		AND returnDate IS NULL
)

3.20 查询至少借阅过读者“张小娟”所借阅过的所有图书的读者编号、读者姓名和工作单位。

SELECT readerNo,readerName,workUnit
FROM Reader r
WHERE NOT EXISTS(
	SELECT *
	FROM Borrow b1
	WHERE readerNo IN(
		SELECT readerNo
		FROM Reader
		WHERE readerName='张小娟'
	) AND returnDate IS NULL
		AND NOT EXISTS(
		SELECT *
		FROM Borrow b2
		WHERE b1.bookNo=b2.bookNo
			AND readerNo=r.readerNo
			AND returnDate IS NULL
	)
)
AND readername<>'张小娟'