光说不练假把式。这就开门见山——引出我们的自连接实例:图书借阅情况。
题目:
这是一道笔试题目:如果限时5min内完成,同学们可以测试一下自己对于SQL语句的熟练程度。
题目分析:
可以看见这个数据库有三个实体(可称为表):Book,Reader,Borrower。
第一题:
注重考察模糊查询。(关于模糊查询的集中讲解我们放在了:初写MySQL四张表:(4/4)_mysql数据模板-CSDN博客)
第二题: 出题人还是很善良的,不藏着掖着——怕我们纠结选择查询方式,索性两种方式都使用。(这可是我们现学现卖的重点)
第三题:
看见:“借阅人数”,这题十有八九就是考聚合函数和一个排序关键字。
查询书名(BN)以“计”开头的图书信息。
分析:
‘图书信息’,(这Book单词您得认识),正是Book表里面所含记录。可得我们查询得到的结果集正是来源于Book,它要我们显示‘图书信息’,眼下之意就是包含图书的所有属性。
可得:
SELECT *
FROM Book
它要求查询出来的图书有个条件: 这时候需要我们设置过滤条件,WHERE子句走起
以‘计’为开头的书名(BN),意味着这本书可以就叫《计》,或者“计......”,要实现匹配计后面0个或无数个字符,我们使用通配符%。
模糊查询:关键字LIKE(或者NOT LIKE)搭配 通配符。
WHERE BN LIKE '计%';
所以本题最终代码:
SELECT *
FROM Book
WHERE BN LIKE '计%';
查询比《大学计算机基础》库存量高的图书号(BNo)和书名(BN)(请用子查询和自身连接两种方式)。
子查询:
我们最后在结果集里面,选中的字段是BNo和BN,来源于Book,可写出以下代码:
SELECT BNo,BN
FROM Book
现在就来考虑考虑过滤条件:比某本书的库存量高的书。
子查询的核心,就是找出谁是被先查询的。反推的话,我们要的结果集是——比某本书的库存量高的书中的书们;不难推测,子查询先查的就是——“比某本书的库存量高的书”中的某本书的库存量
正着理解谁是需要先被查询的,现在未知量有两组:《大学计算机基础》的库存量(Number)和比这个Number大的书们。
很明显在Book表里,我们知道了一本书的书名,很容易就在那一条记录(行)中对照着就找着这本书的库存量了。而知道了这本书的库存量,集中那一列(Number)一一比较,就才能知道哪些库存量较大、较小,然后选出较大库存量的记录,选中目的字段再最后返回结果集。
可知,先知道《大学计算机基础》的库存量(Number),问题才能按逻辑进行。
那么过滤条件:
WHERE Number > (《大学计算机基础》的库存量(Number));
括号里面的查询语句,先执行,这就是子查询。子查询的查询结果作为父查询的查询条件(过滤条件)。
WHERE Number > (SELECT NumberFROM BookWHERE BN = '大学计算机基础');
#过滤条件:只保留书名是“大学计算机基础”的记录,最后在结果集里面选择Number值并返回
所以第二题(子查询)代码:
SELECT BNo,BN
FROM Book
WHERE Number > (SELECT NumberFROM BookWHERE BN = '大学计算机基础');
自连接:
自连接,就是表和自己做连接操作,包括:内连接、外连接和笛卡尔积集连接。实现连接操作通过取别名,代表同一个表不同的实例。
这里总结一个一般规律:JOIN...ON...关键字(内外连接)通常用于表示两个表之间的等价关系(即连接条件)。就正如我们上一小节所谈到的,一张表同时有员工的id号以及他/她的经理的id,同时经理本身也算是公司的员工。这时候在查询每个员工及其经理名字的时候,需要进行表自连接操作,这时候的连接条件是e1.manager_id = e2.employee_id。
这种连接条件,而非比较操作,我们使用JOIN...ON...。说人话,就是匹配条件是等号用JOIN...ON..,其他比较符号可以使用笛卡尔积集查询。
比如此处:
我们不妨令b1、b2分别为Book的别名。我们打算从b1中得到最后的结果集。那b2做什么用?
SELECT b1.BNo,b1.BN
FROM Book b1,Book b2
WHERE
我们注意看题干,查询比《大学计算机基础》库存量高的图书号(BNo)和书名(BN),现在结果集体现在b1中,但是《大学计算机基础》的库存量在代码中还没有呈现。
可以得到,要构成题意的过滤条件,我们的逻辑还是需要先明确“《大学计算机基础》库存量”再做查询。子查询中,我们使用同一张表,将这个逻辑分了先后。
在自连接查询中,我们选择将这两个任务分配给了两张表,(一张表的两个实例):一个查《大学计算机基础》的库存量,另一张表查询比《大学计算机基础》库存量高的图书号(BNo)和书名(BN)
子查询注重厘清逻辑先后,而自连接查询需要分工明确。
WHERE b1.Number > b2.Number AND b2.BN = '大学计算机基础';
前者是b1要做的,选中该表库存量(Number)大于b2.Number这个值的记录,b2.Number这个值也不是b2表里随便哪个值,而是书名为《大学计算机基础》的Number。
故第二题(自连接)代码:
SELECT b1.BNo,b1.BN
FROM Book b1,Book b2
WHERE b1.Number > b2.Number AND b2.BN = '大学计算机基础';
查询所有图书的借阅情况,包括书号(BNo)和借阅人数,并且按照借阅人数降序排列。
我们对照题目给出的三张表进行分析:
需求:查询所有图书的借阅情况,包括书号(BNo)和借阅人数,并且按照借阅人数降序排列。
这最好下手的前两句,SELECT ... FROM ... ,我们看一遍题目就知道。
要求显示书号BNo和'借阅人数',在Book表和Borrower表里,都有BNo字段,那问题来了:到底BNo的前缀是Book还是Borrower呢?
再细细看题:显示“所有图书”的借阅情况。如果要说哪张表记录的书最完整,那必属Book表。
SELECT Book.BNo, 借阅人数
FROM Book
还有一个字段—— 借阅人数,三张表都没有一个字段直接表示“借阅人数”。出错题了?不是,此时我们应该把目光放在最接近真相的表上,借阅表(Borrower)
借阅表,展示了读者和图书的借阅关系。在借阅表中,读者体现在“RNo”(读者编号)上;图书体现在“BNo”(书号)上。由于:书名和人名都会有重名的情况,而书号和读者编号不一样,具有唯一标识性,故用这二者作为读者和图书,可构成一张数据不错误、不冗余的借阅表。
要获得每本图书的借阅人数,我们采用聚合函数。这里选用的聚合函数是COUNT();
将每一本书作为一个组,组内数出有多少个读者id号,这样得到的就是每本图书的借阅人数。故COUNT()里面的参数应该为Borrower.RNo。
SELECT Book.BNo,COUNT(Borrower.RNo) AS 借阅人数
#题上要求显示的字段名,叫“借阅人数”,需要取别名
FROM
分析到这里,我们发现这两个字段都来自不同的表,这不免就会涉及到连接操作。到底是什么连接呢?外连接,内连接亦或者笛卡尔积集连接。
再看一眼题:显示“所有图书”的借阅情况,意味着就算在借阅表里面没有这本书的记录,也应该显示。
一个“所有图书”,就暗示我们谁是主表,主表这个概念存在于外连接中。
那么,代码继续写:
SELECT Book.BNo,COUNT(Borrower.RNo) AS 借阅人数
FROM Book
LEFT JOIN Borrower
ON
观察Book表和Borrower表是否含有相同字段构成连接条件。这种用等价关系匹配的连接,也呼应上前文说的使用JOIN...ON...连接 。
那么连接条件就是关键的BNo。
SELECT Book.BNo,COUNT(Borrower.RNo) AS 借阅人数
FROM Book
LEFT JOIN Borrower
ON Book.BNo = Borrower.BNo
此时查询语句就匹配好了,形成了一个外连接结果集,但是“借阅人数”这个聚合函数还没完成。前面分析提到:
将每一本书作为一个组,组内数出有多少个读者id号,这样得到的就是每本图书的借阅人数。
分组的关键字“GROUP BY”,用Book.BNo代表一本书。
SELECT Book.BNo,COUNT(Borrower.RNo) AS 借阅人数
FROM Book
LEFT JOIN Borrower
ON Book.BNo = Borrower.BNo
GROUP BY Book.BNo
GROUP BY子句后面的参数成为分组依据,好理解。注意:参数应包含SELECT语句中的非聚合列。(比如此处的Book.BNo)
在SELECT
语句中,除了聚合函数计算的列外,其他所有列都应该出现在GROUP BY
子句中。否则,查询可能会返回错误,因为非聚合列的值在组内不是唯一的。
最后一步:“并且按照借阅人数降序排列。”
ORDER BY SCOUNT(Borrower.RNo) DESC;
所以,第三题代码:
SELECT Book.BNo,COUNT(Borrower.RNo) AS 借阅人数
FROM Book
LEFT JOIN Borrower
ON Book.BNo = Borrower.BNo
GROUP BY Book.BNo
ORDER BY COUNT(Borrower.RNo) DESC;
三道题的参考代码如下:
SELECT *
FROM Book
WHERE BN LIKE '计%';
SELECT BNo,BN
FROM Book
WHERE Number > (SELECT NumberFROM BookWHERE BN = '大学计算机基础');
SELECT Book.BNo,COUNT(Borrower.RNo) AS 借阅人数
FROM Book
LEFT JOIN Borrower
ON Book.BNo = Borrower.BNo
GROUP BY Book.BNo
ORDER BY COUNT(Borrower.RNo) DESC;