从查询到操作：MySQL实战训练进阶指南（141-160题精讲）

1. 图书借阅管理系统实战场景搭建

在开始讲解具体的SQL查询之前，我们先来搭建一个完整的图书借阅管理系统数据库场景。这个系统包含三个核心表：图书表、读者表和借阅表。图书表存储图书的基本信息，读者表记录读者资料，借阅表则管理借还书记录。

图书表的结构设计如下：

CREATE TABLE 图书 ( 条形码 VARCHAR(20) PRIMARY KEY, 书名 VARCHAR(100) NOT NULL, 作者 VARCHAR(50), 出版社 VARCHAR(50), 出版日期 DATE, 售价 DECIMAL(10,2) );

读者表的结构包含读者账号、姓名等关键信息：

CREATE TABLE 读者 ( 账号 VARCHAR(10) PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL, 性别 CHAR(1), 会员等级 VARCHAR(10), 联系电话 VARCHAR(15), 注册日期 DATE );

借阅表作为关联表，记录借阅行为：

CREATE TABLE 借阅 ( 借阅ID INT AUTO_INCREMENT PRIMARY KEY, 账号 VARCHAR(10), 条形码 VARCHAR(20), 借书日期 DATE, 还书日期 DATE, FOREIGN KEY (账号) REFERENCES 读者(账号), FOREIGN KEY (条形码) REFERENCES 图书(条形码) );

在实际项目中，这样的表结构设计能够满足基本的图书借阅管理需求。我建议在本地MySQL环境中先创建这些表，并插入一些测试数据，这样后续的查询练习才能有实际效果。

2. 基础查询操作精讲

2.1 简单条件查询

最简单的查询就是使用WHERE子句筛选特定条件的记录。比如查询售价在50到70元之间的图书：

SELECT * FROM 图书 WHERE 售价 BETWEEN 50 AND 70;

这个查询使用了BETWEEN运算符，它等同于：

SELECT * FROM 图书 WHERE 售价 >= 50 AND 售价 <= 70;

在实际项目中，我经常使用BETWEEN来查询日期范围或价格区间的数据，它比使用两个比较运算符更简洁明了。

2.2 模糊查询与正则表达式

当我们需要查询特定模式的字符串时，可以使用LIKE运算符或正则表达式。例如查询条形码以"TP3"开头的图书：

SELECT * FROM 图书 WHERE 条形码 REGEXP '^TP3';

这里使用了REGEXP运算符进行正则匹配，'^TP3'表示以TP3开头。相比LIKE 'TP3%'的写法，正则表达式功能更强大，但性能稍差。在数据量大的表中，我建议优先使用LIKE，除非需要复杂的模式匹配。

2.3 多条件组合查询

实际业务中经常需要组合多个条件进行查询。例如查询科学出版社出版且价格超过50元的图书：

SELECT 书名,作者,出版社,售价 FROM 图书 WHERE 售价 > 50 AND 出版社 = '科学出版社';

这里使用了AND逻辑运算符连接两个条件。当需要满足任一条件时，可以使用OR运算符：

SELECT * FROM 图书 WHERE 书名 = 'C语言程序设计' OR 书名 = 'VB程序设计';

在编写复杂查询时，我习惯用括号明确优先级，避免逻辑混淆。例如：

SELECT * FROM 图书 WHERE (出版社 = '科学出版社' OR 出版社 = '人民邮电出版社') AND 售价 > 50;

3. 高级查询技巧实战

3.1 排序与分页

查询结果的排序在实际应用中非常重要。例如按条形码升序排列图书：

SELECT 条形码,书名 FROM 图书 ORDER BY 条形码;

对于大数据量的表，我通常会加上分页限制：

SELECT 条形码,书名 FROM 图书 ORDER BY 条形码 LIMIT 20 OFFSET 0;

多列排序也很常见，比如先按出版社升序，再按出版日期降序：

SELECT 条形码,书名,出版社,出版日期 FROM 图书 ORDER BY 出版社, 出版日期 DESC;

3.2 聚合函数与分组统计

统计功能是数据库查询的核心能力之一。例如统计各出版社的图书数量：

SELECT 出版社, COUNT(*) AS 图书数目 FROM 图书 GROUP BY 出版社 ORDER BY 图书数目 DESC;

这里使用了COUNT聚合函数和GROUP BY分组子句。其他常用聚合函数还包括：

SELECT MAX(售价) AS 最高售价, MIN(售价) AS 最低售价, AVG(售价) AS 平均售价 FROM 图书;

在实际项目中，我经常使用HAVING子句对分组结果进行筛选：

SELECT 出版社, COUNT(*) AS 图书数目 FROM 图书 GROUP BY 出版社 HAVING COUNT(*) > 5 ORDER BY 图书数目 DESC;

3.3 去重与日期函数

查询不重复的出版社列表：

SELECT DISTINCT 出版社 FROM 图书;

日期函数在查询中也很实用，例如查询2018年出版的图书数量：

SELECT COUNT(*) AS 2018年出版的图书数目 FROM 图书 WHERE YEAR(出版日期) = 2018;

4. 多表连接与数据操作

4.1 表连接查询

图书借阅系统的核心功能需要多表连接查询。例如查询图书借阅情况：

SELECT 账号, book.条形码, 书名, 借书日期 FROM 图书 AS book, 借阅 AS borrow WHERE book.条形码 = borrow.条形码;

更规范的写法是使用JOIN语法：

SELECT b.账号, 姓名, a.条形码, 书名, 借书日期, 还书日期 FROM 图书 a JOIN 借阅 b ON a.条形码 = b.条形码 JOIN 读者 c ON b.账号 = c.账号;

在实际项目中，我建议总是使用显式的JOIN语法，它更清晰且易于维护。

4.2 数据插入操作

向图书表插入新记录：

INSERT INTO 图书 VALUES ('TP211.3', '狼图腾', '姜戎', NULL, NULL, 44.5);

更安全的写法是指定列名：

INSERT INTO 图书 (条形码, 书名, 作者, 售价) VALUES ('TP211.3', '狼图腾', '姜戎', 44.5);

4.3 数据更新与删除

更新科学出版社图书价格上涨5%：

UPDATE 图书 SET 售价 = 售价 * 1.05 WHERE 出版社 = '科学出版社';

删除特定图书记录：

DELETE FROM 图书 WHERE 条形码 = 'TP204.2';

在执行UPDATE和DELETE操作前，我通常会先用SELECT语句确认影响范围，避免误操作。例如：

SELECT * FROM 图书 WHERE 出版社 = '科学出版社'; -- 确认无误后再执行UPDATE