自学内容网 自学内容网
自学内容网 > 正文

MYSQL_深入理解自连接_图书借阅情况(2/2)

🕗 发布于 2024-11-15 07:09 数据库  mysql  

光说不练假把式。这就开门见山——引出我们的自连接实例：图书借阅情况。

题目：

 这是一道笔试题目：如果限时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 Number
                FROM Book
                WHERE BN = '大学计算机基础');
#过滤条件：只保留书名是“大学计算机基础”的记录，最后在结果集里面选择Number值并返回

所以第二题(子查询)代码:

SELECT BNo,BN
FROM Book
WHERE Number > (SELECT Number
                FROM Book
                WHERE 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 Number
                FROM Book
                WHERE 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;

原文地址：https://blog.csdn.net/2301_80682565/article/details/143770145

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024