数据库原理及应用课程上机实验-实验4：数据库表查询（2学时）

一、实验目的

1、理解SQL的特点；

2、掌握SELECT命令的使用；

3、掌握单表查询、集合查询、连接查询和嵌套查询；

二、实验环境

装有软件MySQL5.7或更高版本的PC电脑一台；

三、实验步骤及实验要求

按照下面的实验内容要求完成作业，将作业结果的每一步截图粘贴到word文档中即可。每一个实验都必须编写实验报告，要求如下：

1、实验报告用word编写；

2、word文件命名的格式统一要求：为以“杨健”同学19号为例，他所做的第4次实验的实验报告应该命令为：“DB实验4-- 19号--杨健”（即格式是：实验序号—课内序号--姓名）；课内序号现在是一个人一门课一个号，同一个人在不同课序号会不同，回头我会将课程名单发到群里，自己查阅你自己在本门课中的序号。

3、实验报告用统一的封面，封面模板到时发给大家；

4、报告中截取每题主要步骤结果的截图、实验结果截图；

5、实验报告最后要加上实验总结，总结部分必须写出自己的切身体会；

6、实验报告如有雷同、抄袭现象，后果自负；

7、实验报告上交截止时间：上机后一周之内；

8、实验上交方式：由学委收集齐全后，统一交付老师：

四、实验内容

1、针对上述建立的“简易图书管理数据库”BooksDB，用SQL命令完成下述功能：

1）单表查询

（1）查询所有读者的编号和姓名；

SELECT rdID,rdName
FROM   reader_3;

（2）查询所有读者的编号、姓名和单位，要求修改查询结果的列名；

SELECT rdID '编号',rdName '姓名',rdDept '单位'
FROM   reader_3;

（3）查询Reader表的全部列

SELECT *
FROM   reader_3;

SELECT rdID,rdType,rdName,rdDept,rdQQ,rdBorrowQty
FROM   reader_3;

（4）查询借阅过图书的读者的编号；

SELECT rdID
FROM   reader_3
WHERE rdBorrowQty > 0;

（5）查询单价大于30元的图书的书号和书名；

SELECT *
FROM   book_3;

SELECT bkID,bkName
FROM   book_3
WHERE bkPrice > 30;

（6）查询单价不在30至40元之间的图书的书号、书名和作者；

SELECT bkID,bkName,bkAuthor#,bkPrice
FROM   book_3
WHERE bkPrice < 30 or bkPrice >40;

（7）查询既不是管理学院、也不是物理学院的读者的姓名和QQ；

SELECT *
FROM   reader_3;

SELECT rdName,rdQQ
FROM   reader_3
WHERE rdDept != "管理学院" AND rdDept != "物理学院";

（8）分别使用like 和 regexp查询所有姓“王”的读者的姓名、单位和QQ；

SELECT *
FROM   reader_3;

SELECT rdName,rdDept,rdQQ
FROM 	reader_3
WHERE	rdName LIKE '王%';

SELECT rdName,rdDept,rdQQ
FROM 	reader_3
WHERE	rdName REGEXP '^王';

#like(模糊匹配)
“%”：匹配0个或多个字符。
“_”：只能匹配一个字符。

#regexp(匹配字符串)
（1）‘^’匹配以该字符后面的字符开头的字符串。
（2）‘$’匹配以该字符前面的字符结尾的字符串。
（3）‘.’匹配任何一个单字符。
（4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
（5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

（9）分别使用like 和 regexp查询查询所有不姓“王”的读者的姓名、单位和QQ；

SELECT *
FROM   reader_3;


SELECT rdName,rdDept,rdQQ
FROM 	reader_3
WHERE	rdName NOT LIKE '王%';

SELECT rdName,rdDept,rdQQ
FROM 	reader_3
WHERE	rdName NOT REGEXP '^王';

（10）查询所有全名只有两个字的读者的姓名、单位和QQ；

SELECT *
FROM   reader_3;

SELECT rdName,rdDept,rdQQ
FROM 	reader_3
WHERE	rdName LIKE '__';

（11）查询所有图书还未归还的借阅信息；

SELECT *
FROM   reader_3;

SELECT *
FROM   borrow_3;

SELECT DISTINCT r.rdID,r.rdType,r.rdName,r.rdDept,r.rdQQ,r.rdBorrowQty
FROM 	reader_3 r,borrow_3 b
WHERE	r.rdID = b.rdID;

（12）查询借阅了书号“bk2017001”的读者的编号和借书日期，查询结果按借书日期降序排列；

SELECT *
FROM   borrow_3;

SELECT rdID,DateLendPlan
FROM 	borrow_3
WHERE	bkID = "bk2017001"
ORDER BY DateLendPlan DESC;

（13）查询读者的总人数；

SELECT *
FROM   reader_3;

SELECT COUNT(rdID)
FROM	  reader_3;

（14）查询借阅过图书的读者人数；

SELECT *
FROM   reader_3;

SELECT COUNT(rdID)
FROM	  reader_3
WHERE rdBorrowQty != 0;

（15）查询所有图书的最高单价；

SELECT *
FROM   book_3;

SELECT MAX(bkPrice)
FROM	  book_3;

（16）查询各单位名及该单位的读者人数；

SELECT *
FROM   reader_3;

SELECT DISTINCT rdDept,COUNT(rdDept)
FROM	  reader_3
GROUP BY rdDept;

（17）查询读者人数大于4的单位名及该单位的读者人数；

SELECT *
FROM   reader_3;

SELECT DISTINCT rdDept,COUNT(rdDept)
FROM	  reader_3
GROUP BY rdDept
HAVING COUNT(rdDept) > 4;

2）连接查询

（18）查询类别名“教师”的所有读者的姓名和单位；

SELECT *
FROM   reader_3;

SELECT *
FROM   readertype_3;

SELECT r.rdName,r.rdDept
FROM   reader_3 r,readertype_3 rt
WHERE r.rdType = rt.rdType 
AND rt.rdTypeName = "教师";

（19）查询管理学院所有读者的编号、姓名和可借书数量；

SELECT *
FROM   reader_3;

SELECT *
FROM   readertype_3;

SELECT r.rdID,r.rdName,rt.canLendQty
FROM   reader_3 r,readertype_3 rt
WHERE r.rdType = rt.rdType 
AND r.rdDept = "管理学院";

（20）查询借阅了书号为“bk2017001”的读者的姓名、可借书数量和可借书天数；

SELECT *
FROM   reader_3;

SELECT *
FROM   readertype_3;

SELECT *
FROM   borrow_3;

SELECT r.rdName,rt.canLendQty,rt.canLendDay
FROM   reader_3 r,readertype_3 rt,borrow_3 b
WHERE r.rdType = rt.rdType 
AND r.rdID = b.rdID
AND b.bkID = "bk2017001";

（21）查询每个读者及其借阅信息的情况（即使该读者没有借过书，也列出该读者的基本信息）；

SELECT *
FROM   reader_3;

SELECT *
FROM   borrow_3;

SELECT r.rdID,r.rdType,r.rdName,r.rdDept,r.rdQQ,r.rdBorrowQty,b.DateBorrow,b.DateLendPlan,b.DateLendAct
FROM   reader_3 r
LEFT JOIN borrow_3 b
ON r.rdID = b.rdID;

3）嵌套查询

（22）查询借阅了书名为“高等数学”的读者的编号和姓名；

SELECT *
FROM   reader_3;

SELECT *
FROM   book_3;

SELECT *
FROM   borrow_3;

SELECT r.rdID
FROM   borrow_3 b,reader_3 r,book_3 bk
WHERE b.rdID = r.rdID
AND b.bkID = bk.bkID
AND bk.bkName = "高等数学";

SELECT rdID,rdName
FROM   reader_3
WHERE rdID =
						(
							SELECT r.rdID
							FROM   borrow_3 b,reader_3 r,book_3 bk
							WHERE b.rdID = r.rdID
							AND b.bkID = bk.bkID
							AND bk.bkName = '高等数学'
						);

（23）分别使用“无关子查询”和“相关子查询”来查询所有没借阅过书号为“bk2017004”的读者姓名；

#无关子查询
SELECT *
FROM   reader_3;

SELECT *
FROM   borrow_3;

SELECT rdID
FROM   borrow_3
WHERE bkID = "bk2017004";

SELECT rdName
FROM   reader_3
WHERE rdID !=
						(
							SELECT rdID
							FROM   borrow_3
							WHERE bkID = "bk2017004"
						);

#相关子查询
SELECT rdName
FROM   reader_3 r
WHERE rdID NOT IN
						(
							SELECT rdID
							FROM   borrow_3
							WHERE rdID = r.rdID
							AND bkID = "bk2017004"
						);