数据库原理及应用课程上机实验 - 实验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"
                        );