1-MySQL - 图书管理系统练习题
before
图书管理系统(bms)有作者表、作者详情信息表、出版社表、书籍表、书籍和作者关系绑定关系表(第三张表)。
其关系：
- 一本书可以有多个作者联合出版
- 一个作者可以出版多本书籍
- 一本书只可以被一个出版社出版
- 一个出版社可以出版多本书籍
写SQL
-- 自主插入各项数据
-- 通过作者名查询该作者的详情信息
-- 通过作者的电话查询对应该作者的基本信息
-- 通过书籍名查询其对应的出版社
-- 通过出版社名称查询旗下出版的书籍信息
-- 查询名字为xxx作者的手机号和年龄
-- 查询xxx书籍是哪个出版社出版的
-- 查询xxx书籍的作者名，作者年龄和书籍价格
-- 修改xxx作者的手机号和年龄
-- 修改xxx书籍的出版社和价格
-- 为xxx书籍重新绑定一个作者
-- 删除一个作者
-- 删除一本书
-- 查询所有书籍的总数
-- 查询所有书籍的平均价格
-- 查询每个出版社出版书籍的平均价格
-- 查询每个作者出版书的最高价格
-- 查询"xxx"出版过的价格大于20的书籍信息
-- 查询xxxx年x月出版的所有以"py"开头的书籍名称
-- 查询价格为50,100,或者150的所有书籍名称及其出版社名称
-- 查询价格在100~200之间的所有书籍名称及其价格
-- 查询"xxxx"出版过的所有书籍的价格，并以价格降序排序和去重
-- 查询作者id大于2作者的姓名以及出版的书的最高价格
-- 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
-- 查询每个作者出版的书的最高价格的平均值
-- 每个作者出版的所有书的最高价格以及最高价格的那本书的名称
-- 统计每一个出版社的最便宜的书
-- 统计每一本书的作者个数
-- 统计每一本以py开头的书籍的作者个数
-- 统计不止一个作者的图书
-- 查询各个作者出的书的总价格
创建表结构和添加数据
登录到MySQL客户端，直接复制粘贴执行即可:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for author
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`age` int(11) NOT NULL,
`gender` enum('male','female') NOT NULL,
`detail_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `detail_id` (`detail_id`),
CONSTRAINT `author_detail_id_fk_authordetail_id` FOREIGN KEY (`detail_id`) REFERENCES `authordetail` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of author
-- ----------------------------
INSERT INTO `author` VALUES ('1', '吴小红', '49', 'female', '1');
INSERT INTO `author` VALUES ('2', '陈利', '37', 'male', '2');
INSERT INTO `author` VALUES ('3', '张玉', '15', 'male', '3');
INSERT INTO `author` VALUES ('4', '赵淑珍', '81', 'female', '4');
INSERT INTO `author` VALUES ('5', '李英', '82', 'female', '5');
INSERT INTO `author` VALUES ('6', '申雷', '70', 'male', '6');
INSERT INTO `author` VALUES ('7', '陈淑兰', '87', 'female', '7');
INSERT INTO `author` VALUES ('8', '梁桂芳', '82', 'female', '8');
INSERT INTO `author` VALUES ('9', '张阳', '25', 'male', '9');
INSERT INTO `author` VALUES ('10', '赵楠', '22', 'male', '10');
-- ----------------------------
-- Table structure for authordetail
-- ----------------------------
DROP TABLE IF EXISTS `authordetail`;
CREATE TABLE `authordetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`birthday` date NOT NULL,
`tel` varchar(32) NOT NULL,
`address` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of authordetail
-- ----------------------------
INSERT INTO `authordetail` VALUES ('1', '1995-05-18', '13743395855', '重庆市辉市怀柔南宁街C座 984923');
INSERT INTO `authordetail` VALUES ('2', '1980-03-16', '18236074336', '江西省玉华市高坪太原街x座 448405');
INSERT INTO `authordetail` VALUES ('3', '2003-08-30', '18690146454', '北京市成都市淄川重庆街X座 183988');
INSERT INTO `authordetail` VALUES ('4', '1981-08-03', '14533911089', '福建省兰州县长寿兴城街P座 376046');
INSERT INTO `authordetail` VALUES ('5', '2012-04-28', '13450057342', '内蒙古自治区淮安县梁平永安街X座 510052');
INSERT INTO `authordetail` VALUES ('6', '2007-09-27', '15918742364', '云南省合肥县龙潭辽阳街o座 357049');
INSERT INTO `authordetail` VALUES ('7', '1970-03-14', '18790430975', '青海省宜都市清浦江路D座 671374');
INSERT INTO `authordetail` VALUES ('8', '1970-02-28', '15503705535', '宁夏回族自治区西安市南湖黄路M座 980890');
INSERT INTO `authordetail` VALUES ('9', '2006-12-08', '18041382207', '黑龙江省秀华县平山贵阳路h座 702377');
INSERT INTO `authordetail` VALUES ('10', '1990-08-24', '15191689268', '江苏省丽丽县双滦海口路p座 446048');
-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(32) NOT NULL,
`price` decimal(6,2) NOT NULL,
`pub_date` date NOT NULL,
`publish_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `book_publish_id_fk_publisher_id` (`publish_id`),
CONSTRAINT `book_publish_id_fk_publisher_id` FOREIGN KEY (`publish_id`) REFERENCES `publisher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('1', 'python从入门到入狱', '85.40', '2021-07-13', '10');
INSERT INTO `book` VALUES ('2', '土耳其旅游指南', '62.91', '1997-07-01', '6');
INSERT INTO `book` VALUES ('3', '苏丹美食探索', '94.49', '2006-10-07', '6');
INSERT INTO `book` VALUES ('4', 'python学习手册', '50.00', '2021-07-09', '10');
INSERT INTO `book` VALUES ('5', 'python核心编程', '100.00', '2021-03-29', '4');
INSERT INTO `book` VALUES ('6', '马其顿旅游景点探秘', '16.84', '2011-01-24', '1');
-- ----------------------------
-- Table structure for book_authors
-- ----------------------------
DROP TABLE IF EXISTS `book_authors`;
CREATE TABLE `book_authors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`book_id` int(11) NOT NULL,
`author_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `book_authors_book_id_author_id_uniq` (`book_id`,`author_id`),
KEY `book_authors_author_id_fk_author_id` (`author_id`),
CONSTRAINT `book_authors_author_id_fk_author_id` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `book_authors_book_id_fk_book_id` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of book_authors
-- ----------------------------
INSERT INTO `book_authors` VALUES ('1', '1', '1');
INSERT INTO `book_authors` VALUES ('2', '1', '3');
INSERT INTO `book_authors` VALUES ('3', '2', '2');
INSERT INTO `book_authors` VALUES ('4', '3', '3');
INSERT INTO `book_authors` VALUES ('5', '3', '4');
INSERT INTO `book_authors` VALUES ('6', '4', '4');
INSERT INTO `book_authors` VALUES ('7', '5', '5');
INSERT INTO `book_authors` VALUES ('8', '5', '6');
INSERT INTO `book_authors` VALUES ('9', '6', '6');
-- ----------------------------
-- Table structure for publisher
-- ----------------------------
DROP TABLE IF EXISTS `publisher`;
CREATE TABLE `publisher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL,
`address` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of publisher
-- ----------------------------
INSERT INTO `publisher` VALUES ('1', '关岭出版社', '辽阳县高明张家港路U座 603428');
INSERT INTO `publisher` VALUES ('2', '哈尔滨出版社', '哈尔滨市长寿宜都街L座 730811');
INSERT INTO `publisher` VALUES ('3', '辽阳出版社', '永安市普陀李路I座 550634');
INSERT INTO `publisher` VALUES ('4', '南京出版社', '南京市友好佛山路J座 267702');
INSERT INTO `publisher` VALUES ('5', '深圳出版社', '秀荣县新城哈尔滨路H座 632513');
INSERT INTO `publisher` VALUES ('6', '广州出版社', '广州市双滦东莞路l座 884903');
INSERT INTO `publisher` VALUES ('7', '北镇出版社', '淮安市高港王路g座 677846');
INSERT INTO `publisher` VALUES ('8', '昆明出版社', '昆明市合川李路b座 501591');
INSERT INTO `publisher` VALUES ('9', '海门出版社', '沈阳市城北济南路W座 596583');
INSERT INTO `publisher` VALUES ('10', '合山出版社', '合山县涪城朱街G座 860869');
SQL参考示例
PS：根据数据集的特点，对某些查询条件会做些许更改;有的查询结果为了便于观察，多返回了一些字段。
- 通过作者名查询该作者的详情信息
-- 查询"吴小红"的详情信息
SELECT
*
FROM author INNER JOIN authordetail ON author.detail_id=authordetail.id
WHERE author.name="吴小红";
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
| id | name | age | gender | detail_id | id | birthday | tel | address |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
| 1 | 吴小红 | 49 | female | 1 | 1 | 1995-05-18 | 13743395855 | 重庆市辉市怀柔南宁街C座 984923 |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
- 通过作者的电话查询对应该作者的基本信息
-- 查询手机号是"18236074336"的详情信息
SELECT
*
FROM author INNER JOIN authordetail ON author.detail_id=authordetail.id
WHERE authordetail.tel="18236074336";
+----+------+-----+--------+-----------+----+------------+-------------+----------------------------------+
| id | name | age | gender | detail_id | id | birthday | tel | address |
+----+------+-----+--------+-----------+----+------------+-------------+----------------------------------+
| 2 | 陈利 | 37 | male | 2 | 2 | 1980-03-16 | 18236074336 | 江西省玉华市高坪太原街x座 448405 |
+----+------+-----+--------+-----------+----+------------+-------------+----------------------------------+
- 通过书籍名查询其对应的出版社
-- 查询"马其顿旅游景点探秘"的出版社信息
SELECT
publisher.name,
publisher.address
FROM book INNER JOIN publisher ON book.publish_id=publisher.id
WHERE book.title="马其顿旅游景点探秘";
+----+--------------------+-------+------------+------------+----+------------+------------------------------+
| id | title | price | pub_date | publish_id | id | name | address |
+----+--------------------+-------+------------+------------+----+------------+------------------------------+
| 6 | 马其顿旅游景点探秘 | 16.84 | 2011-01-24 | 1 | 1 | 关岭出版社 | 辽阳县高明张家港路U座 603428 |
+----+--------------------+-------+------------+------------+----+------------+------------------------------+
SELECT
publisher.name,
publisher.address
FROM book INNER JOIN publisher ON book.publish_id=publisher.id
WHERE book.title="马其顿旅游景点探秘";
+------------+--------------------------+
| name | address |
+------------+--------------------------+
| 合山出版社 | 合山县涪城朱街G座 860869 |
+------------+--------------------------+
- 通过出版社名称查询旗下出版的书籍信息
-- 查询"广州出版社"出版的所有书籍信息
SELECT
*
FROM publisher INNER JOIN book ON publisher.id=book.publish_id
WHERE publisher.name="广州出版社";
+----+------------+----------------------------+----+----------------+-------+------------+------------+
| id | name | address | id | title | price | pub_date | publish_id |
+----+------------+----------------------------+----+----------------+-------+------------+------------+
| 6 | 广州出版社 | 广州市双滦东莞路l座 884903 | 2 | 土耳其旅游指南 | 62.91 | 1997-12-19 | 6 |
| 6 | 广州出版社 | 广州市双滦东莞路l座 884903 | 3 | 苏丹美食探索 | 94.49 | 2006-10-07 | 6 |
+----+------------+----------------------------+----+----------------+-------+------------+------------+
SELECT
publisher.name,
GROUP_CONCAT(book.title)
FROM publisher INNER JOIN book ON publisher.id=book.publish_id
WHERE publisher.name="广州出版社";
+------------+-----------------------------+
| name | GROUP_CONCAT(book.title) |
+------------+-----------------------------+
| 广州出版社 | 土耳其旅游指南,苏丹美食探索 |
+------------+-----------------------------+
- 查询名字为xxx作者的手机号和年龄
-- 查询"吴小红"的手机号和年龄
SELECT
authordetail.tel,
author.age
FROM author INNER JOIN authordetail ON author.detail_id=authordetail.id
WHERE author.name="吴小红";
+-------------+-----+
| tel | age |
+-------------+-----+
| 13743395855 | 49 |
+-------------+-----+
- 查询xxx书籍是哪个出版社出版的
-- 查询"马其顿旅游景点探秘"的出版社信息
SELECT
book.title,
publisher.name
FROM book LEFT JOIN publisher ON book.publish_id=publisher.id
WHERE book.title="马其顿旅游景点探秘";
+--------------------+------------+
| title | name |
+--------------------+------------+
| 马其顿旅游景点探秘 | 关岭出版社 |
+--------------------+------------+
- 查询xxx书籍的作者名，作者年龄和书籍价格
-- 查询"马其顿旅游景点探秘"的作者名，作者年龄和书籍价格
SELECT
author.name,
author.age,
book.price
FROM book_authors LEFT JOIN book ON book_authors.book_id=book.id RIGHT JOIN author ON book_authors.author_id=author.id
WHERE book.title="马其顿旅游景点探秘";
+------+-----+-------+
| name | age | price |
+------+-----+-------+
| 申雷 | 70 | 16.84 |
+------+-----+-------+
- 修改xxx作者的手机号和年龄
-- 将"吴小红"的手机号和年龄进行修改, 原age=49;tel=13743395855; 修改为age=50;tel=13743395856
-- 因为要修改的字段分布再两个表中，所以思路由两种，第一种是分别修改两个表中的记录；另一种是先连表再修改，一个update语句即可，参考下面的示例
-- before
SELECT * FROM author INNER JOIN authordetail ON author.detail_id=authordetail.id WHERE author.name="吴小红";
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
| id | name | age | gender | detail_id | id | birthday | tel | address |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
| 1 | 吴小红 | 49 | female | 1 | 1 | 1995-05-18 | 13743395855 | 重庆市辉市怀柔南宁街C座 984923 |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
-- update
UPDATE
author INNER JOIN authordetail ON author.detail_id=authordetail.id
SET
author.age=50,
authordetail.tel=13743395856
WHERE author.name="吴小红";
-- after
SELECT * FROM author INNER JOIN authordetail ON author.detail_id=authordetail.id WHERE author.name="吴小红";
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
| id | name | age | gender | detail_id | id | birthday | tel | address |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
| 1 | 吴小红 | 50 | female | 1 | 1 | 1995-05-18 | 13743395856 | 重庆市辉市怀柔南宁街C座 984923 |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
- 修改xxx书籍的出版社和价格
-- 修改"马其顿旅游景点探秘"书籍的出版社和价格，原price=16.84;pulish=关岭出版社;修改为price=150;pulish=合山出版社，合山出版社id为10
-- 思路也是先连表再修改
-- before
SELECT * FROM book INNER JOIN publisher ON book.publish_id=publisher.id WHERE book.title="马其顿旅游景点探秘";
+----+--------------------+-------+------------+------------+----+------------+------------------------------+
| id | title | price | pub_date | publish_id | id | name | address |
+----+--------------------+-------+------------+------------+----+------------+------------------------------+
| 6 | 马其顿旅游景点探秘 | 16.84 | 2011-01-24 | 1 | 1 | 关岭出版社 | 辽阳县高明张家港路U座 603428 |
+----+--------------------+-------+------------+------------+----+------------+------------------------------+
-- update
UPDATE
book
SET
book.price=150,
publish_id=(SELECT id FROM publisher WHERE id=10)
WHERE book.title="马其顿旅游景点探秘";
-- after
SELECT * FROM book INNER JOIN publisher ON book.publish_id=publisher.id WHERE book.title="马其顿旅游景点探秘";
+----+--------------------+--------+------------+------------+----+------------+--------------------------+
| id | title | price | pub_date | publish_id | id | name | address |
+----+--------------------+--------+------------+------------+----+------------+--------------------------+
| 6 | 马其顿旅游景点探秘 | 150.00 | 2011-01-24 | 10 | 10 | 合山出版社 | 合山县涪城朱街G座 860869 |
+----+--------------------+--------+------------+------------+----+------------+--------------------------+
- 为xxx书籍重新绑定一个作者
-- 为"python学习手册"书籍绑定一个作者(申雷)，这里由于第三张表做了unique key约束，重复添加作者会报错，这点需要注意
-- before，先查看书籍的作者有哪些
SELECT
book.title,
GROUP_CONCAT(author.name)
FROM book RIGHT JOIN book_authors ON book.id=book_authors.book_id LEFT JOIN author ON author.id=book_authors.author_id
WHERE book.title="python学习手册";
+----------------+---------------------------+
| title | GROUP_CONCAT(author.name) |
+----------------+---------------------------+
| python学习手册 | 赵淑珍 |
+----------------+---------------------------+
-- insert
-- id为自增主键，所以这个字段可以忽略，再次提醒，注意重复添加报错：ERROR 1062 (23000): Duplicate entry '4-6' for key 'book_authors_book_id_author_id_uniq'
INSERT INTO book_authors(book_id, author_id) VALUES
(
(SELECT id FROM book WHERE book.title="python学习手册"),
(SELECT id FROM author WHERE author.name="申雷")
);
-- after
SELECT
book.title,
GROUP_CONCAT(author.name)
FROM book RIGHT JOIN book_authors ON book.id=book_authors.book_id LEFT JOIN author ON author.id=book_authors.author_id
WHERE book.title="python学习手册";
+----------------+---------------------------+
| title | GROUP_CONCAT(author.name) |
+----------------+---------------------------+
| python学习手册 | 赵淑珍,申雷 |
+----------------+---------------------------+
- 删除一个作者
首先，在生产中，请谨慎使用删除语句！！数据无价！！！！
-- 要是在绑定外键时，没有指定级联删除，那么删除时，会报错，因为第三张表有记录行引用了当前要删除的作者id
-- 添加数据不容易，这里就不真正的执行删除语句了，只需要掌握删除语句结构即可
-- DELETE FROM author WHERE id=10;
- 删除一本书
-- 套路跟删除作者一样，这里不再多表，也不真正的执行删除语句了
-- DELETE FROM book WHERE title="python学习手册";
- 查询所有书籍的总数
SELECT COUNT(id) FROM book;
+-----------+
| COUNT(id) |
+-----------+
| 6 |
+-----------+
- 查询所有书籍的平均价格
SELECT AVG(price) FROM book;
+------------+
| AVG(price) |
+------------+
| 74.531667 |
+------------+
- 查询每个出版社出版书籍的平均价格
SELECT
publisher.name,
AVG(book.price),
GROUP_CONCAT(book.title),
GROUP_CONCAT(book.price)
FROM publisher INNER JOIN book ON publisher.id=book.publish_id
GROUP BY publisher.id;
+------------+-----------------+------------------------------------------------------+--------------------------+
| name | AVG(book.price) | GROUP_CONCAT(book.title) | GROUP_CONCAT(book.price) |
+------------+-----------------+------------------------------------------------------+--------------------------+
| 南京出版社 | 100.000000 | python核心编程 | 100.00 |
| 广州出版社 | 78.700000 | 苏丹美食探索,土耳其旅游指南 | 94.49,62.91 |
| 合山出版社 | 95.133333 | python学习手册,python从入门到入狱,马其顿旅游景点探秘 | 50.00,85.40,150.00 |
+------------+-----------------+------------------------------------------------------+--------------------------+
- 查询每个作者出版书的最高价格
SELECT
author.name,
GROUP_CONCAT(book.title),
GROUP_CONCAT(book.price),
MAX(book.price),
book.title
FROM author INNER JOIN book_authors ON author.id=book_authors.author_id INNER JOIN book ON book_authors.book_id=book.id
GROUP BY author.name;
+--------+--------------------------------------------------+--------------------------+-----------------+--------------------+
| name | GROUP_CONCAT(book.title) | GROUP_CONCAT(book.price) | MAX(book.price) | title |
+--------+--------------------------------------------------+--------------------------+-----------------+--------------------+
| 吴小红 | python从入门到入狱 | 85.40 | 85.40 | python从入门到入狱 |
| 张玉 | python从入门到入狱,苏丹美食探索 | 85.40,94.49 | 94.49 | python从入门到入狱 |
| 李英 | python核心编程 | 100.00 | 100.00 | python核心编程 |
| 申雷 | python学习手册,python核心编程,马其顿旅游景点探秘 | 50.00,100.00,150.00 | 150.00 | python学习手册 |
| 赵淑珍 | 苏丹美食探索,python学习手册 | 94.49,50.00 | 94.49 | 苏丹美食探索 |
| 陈利 | 土耳其旅游指南 | 62.91 | 62.91 | 土耳其旅游指南 |
+--------+--------------------------------------------------+--------------------------+-----------------+--------------------+
- 查询"xxx"出版过的价格大于20的书籍信息
PS：总感觉这个题有歧义。
-- 查询作者"申雷"出版过的价格大于20的书籍信息
SELECT
author.name,
book.title,
book.price,
book.pub_date
FROM author LEFT JOIN book_authors ON author.id=book_authors.author_id INNER JOIN book ON book_authors.book_id=book.id
WHERE book.price > 20 AND author.name="申雷";
+------+--------------------+--------+------------+
| name | title | price | pub_date |
+------+--------------------+--------+------------+
| 申雷 | python核心编程 | 100.00 | 2021-03-29 |
| 申雷 | 马其顿旅游景点探秘 | 150.00 | 2011-01-24 |
| 申雷 | python学习手册 | 50.00 | 2021-07-09 |
+------+--------------------+--------+------------+
-- 查询"合山出版社"出版社出版的价格大于20的书籍信息
SELECT
book.title,
book.price,
publisher.name
FROM publisher LEFT JOIN book ON publisher.id=book.publish_id
WHERE publisher.name="合山出版社" AND book.price > 20;
+--------------------+--------+------------+
| title | price | name |
+--------------------+--------+------------+
| python从入门到入狱 | 85.40 | 合山出版社 |
| python学习手册 | 50.00 | 合山出版社 |
| 马其顿旅游景点探秘 | 150.00 | 合山出版社 |
+--------------------+--------+------------+
- 查询xxxx年x月出版的所有以"py"开头的书籍名称
-- 这个的重点就是原数据是年月日，现在要过滤到月，所以要对原数据进行处理，不要具体到天
-- 另外，对于个位数的日期和月份，都要补零，比如2021-01-01
SELECT * FROM book WHERE DATE_FORMAT(pub_date, '%Y-%m')='2021-07' AND title LIKE "py%";
+----+--------------------+-------+------------+------------+
| id | title | price | pub_date | publish_id |
+----+--------------------+-------+------------+------------+
| 1 | python从入门到入狱 | 85.40 | 2021-07-13 | 10 |
| 4 | python学习手册 | 50.00 | 2021-07-09 | 10 |
+----+--------------------+-------+------------+------------+
-- 延申，单独过滤年、月、日
-- 查询"2021"年出版的所有书籍，按年过滤
SELECT * FROM book WHERE YEAR(pub_date)='2021';
+----+--------------------+--------+------------+------------+
| id | title | price | pub_date | publish_id |
+----+--------------------+--------+------------+------------+
| 1 | python从入门到入狱 | 85.40 | 2021-07-13 | 10 |
| 4 | python学习手册 | 50.00 | 2021-07-09 | 10 |
| 5 | python核心编程 | 100.00 | 2021-03-29 | 4 |
+----+--------------------+--------+------------+------------+
-- 查询"7"月出版的所有书籍，按月过滤
SELECT * FROM book WHERE MONTH(pub_date)='07';
+----+--------------------+-------+------------+------------+
| id | title | price | pub_date | publish_id |
+----+--------------------+-------+------------+------------+
| 1 | python从入门到入狱 | 85.40 | 2021-07-13 | 10 |
| 2 | 土耳其旅游指南 | 62.91 | 1997-07-01 | 6 |
| 4 | python学习手册 | 50.00 | 2021-07-09 | 10 |
+----+--------------------+-------+------------+------------+
-- 查询"9"号出版的所有书籍，按天过滤
SELECT * FROM book WHERE DAY(pub_date)='09';
+----+----------------+-------+------------+------------+
| id | title | price | pub_date | publish_id |
+----+----------------+-------+------------+------------+
| 4 | python学习手册 | 50.00 | 2021-07-09 | 10 |
+----+----------------+-------+------------+------------+
- 查询价格为50、100或者150的所有书籍名称及其出版社名称
-- 法1，使用IN
SELECT
book.title,
book.price,
publisher.name
FROM book LEFT JOIN publisher ON book.publish_id=publisher.id
WHERE book.price IN (50, 100, 150);
+--------------------+--------+------------+
| title | price | name |
+--------------------+--------+------------+
| python学习手册 | 50.00 | 合山出版社 |
| python核心编程 | 100.00 | 南京出版社 |
| 马其顿旅游景点探秘 | 150.00 | 合山出版社 |
+--------------------+--------+------------+
-- 法2，使用OR
SELECT
book.title,
book.price,
publisher.name
FROM book LEFT JOIN publisher ON book.publish_id=publisher.id
WHERE book.price=50 OR book.price=100 OR book.price=150;
+--------------------+--------+------------+
| title | price | name |
+--------------------+--------+------------+
| python学习手册 | 50.00 | 合山出版社 |
| python核心编程 | 100.00 | 南京出版社 |
| 马其顿旅游景点探秘 | 150.00 | 合山出版社 |
+--------------------+--------+------------+
- 查询价格在100~200之间的所有书籍名称及其价格
SELECT
book.title,
book.price
FROM book WHERE price BETWEEN 100 AND 200;
+--------------------+--------+
| title | price |
+--------------------+--------+
| python核心编程 | 100.00 |
| 马其顿旅游景点探秘 | 150.00 |
+--------------------+--------+
- 查询"xxxx"出版过的所有书籍的价格，并以价格降序排序和去重
-- 查询"合山出版社"出版的所有书籍的价格，并以价格降序排序和去重
SELECT
DISTINCT book.price,
publisher.name
FROM book RIGHT JOIN publisher ON book.publish_id=publisher.id
WHERE publisher.name="合山出版社"
ORDER BY book.price DESC;
+--------+------------+
| price | name |
+--------+------------+
| 150.00 | 合山出版社 |
| 85.40 | 合山出版社 |
| 50.00 | 合山出版社 |
+--------+------------+
-- 查询作者"申雷"出版的所有书籍的价格，并以价格降序排序和去重
SELECT
DISTINCT book.price,
book.title,
author.name
FROM author RIGHT JOIN book_authors ON author.id=book_authors.author_id RIGHT JOIN book ON book_authors.book_id=book.id
WHERE author.name="申雷"
ORDER BY book.price DESC;
+--------+--------------------+------+
| price | title | name |
+--------+--------------------+------+
| 150.00 | 马其顿旅游景点探秘 | 申雷 |
| 100.00 | python核心编程 | 申雷 |
| 50.00 | python学习手册 | 申雷 |
+--------+--------------------+------+
- 查询作者id大于2作者的姓名以及出版的书的最高价格
SELECT
author.name,
author.gender,
book.title,
MAX(book.price),
GROUP_CONCAT(book.title),
GROUP_CONCAT(book.price)
FROM author RIGHT JOIN book_authors ON author.id=book_authors.author_id LEFT JOIN book ON book_authors.book_id=book.id
WHERE author.id > 2 GROUP BY author.name;
+--------+--------+--------------------+-----------------+--------------------------------------------------+--------------------------+
| name | gender | title | MAX(book.price) | GROUP_CONCAT(book.title) | GROUP_CONCAT(book.price) |
+--------+--------+--------------------+-----------------+--------------------------------------------------+--------------------------+
| 张玉 | male | python从入门到入狱 | 94.49 | python从入门到入狱,苏丹美食探索 | 85.40,94.49 |
| 李英 | female | python核心编程 | 100.00 | python核心编程 | 100.00 |
| 申雷 | male | python学习手册 | 150.00 | python学习手册,python核心编程,马其顿旅游景点探秘 | 50.00,100.00,150.00 |
| 赵淑珍 | female | 苏丹美食探索 | 94.49 | 苏丹美食探索,python学习手册 | 94.49,50.00 |
+--------+--------+--------------------+-----------------+--------------------------------------------------+--------------------------+
- 查询作者id大于2或者作者年龄大于等于20岁的女作者的姓名以及出版的书的最高价格
SELECT
author.name,
author.gender,
book.title,
MAX(book.price)
FROM author RIGHT JOIN book_authors ON author.id=book_authors.author_id LEFT JOIN book ON book_authors.book_id=book.id
WHERE (author.id > 2 OR author.age >= 20) AND author.gender="female"
GROUP BY author.name;
+--------+--------+--------------------+-----------------+
| name | gender | title | MAX(book.price) |
+--------+--------+--------------------+-----------------+
| 吴小红 | female | python从入门到入狱 | 85.40 |
| 李英 | female | python核心编程 | 100.00 |
| 赵淑珍 | female | 苏丹美食探索 | 94.49 |
+--------+--------+--------------------+-----------------+
- 查询每个作者出版的书的最高价格的平均值
-- 查询每个作者出版的书的最高价格
SELECT
author.name,
author.gender,
GROUP_CONCAT(book.title),
GROUP_CONCAT(book.price),
MAX(book.price)
FROM author RIGHT JOIN book_authors ON author.id = book_authors.author_id LEFT JOIN book ON book_authors.book_id = book.id
GROUP BY author.name;
+--------+--------+--------------------------------------------------+--------------------------+-----------------+
| NAME | gender | GROUP_CONCAT(book.title) | GROUP_CONCAT(book.price) | MAX(book.price) |
+--------+--------+--------------------------------------------------+--------------------------+-----------------+
| 吴小红 | female | python从入门到入狱 | 85.40 | 85.40 |
| 张玉 | male | python从入门到入狱,苏丹美食探索 | 85.40,94.49 | 94.49 |
| 李英 | female | python核心编程 | 100.00 | 100.00 |
| 申雷 | male | python学习手册,马其顿旅游景点探秘,python核心编程 | 50.00,150.00,100.00 | 150.00 |
| 赵淑珍 | female | python学习手册,苏丹美食探索 | 50.00,94.49 | 94.49 |
| 陈利 | male | 土耳其旅游指南 | 62.91 | 62.91 |
+--------+--------+--------------------------------------------------+--------------------------+-----------------+
-- 在上面SQL的基础上，对最大价格求个平均值
SELECT
AVG(tmp.max_price)
FROM (SELECT
author.name,
author.gender,
GROUP_CONCAT(book.title),
GROUP_CONCAT(book.price),
MAX(book.price) as max_price
FROM author RIGHT JOIN book_authors ON author.id = book_authors.author_id LEFT JOIN book ON book_authors.book_id = book.id
GROUP BY author.name) as tmp;
+--------------------+
| AVG(tmp.max_price) |
+--------------------+
| 97.881667 |
+--------------------+
- 每个作者出版的所有书的最高价格以及最高价格的那本书的名称
SELECT
tmp.au_name AS "作者",
book_obj.book_title AS "最高价格那本书",
book_obj.book_price AS "最高价格那本书的价格",
tmp.gcbt AS "出版的所有书",
tmp.gcbp AS "出版的所有书的价格"
FROM (SELECT
author.name AS au_name,
GROUP_CONCAT(book.title) AS gcbt,
GROUP_CONCAT(book.price) AS gcbp,
MAX(book.price) AS max_price
FROM author RIGHT JOIN book_authors ON author.id = book_authors.author_id LEFT JOIN book ON book_authors.book_id = book.id
GROUP BY author.name) AS tmp
LEFT JOIN (SELECT book.price AS book_price, book.title AS book_title FROM book) AS book_obj ON tmp.max_price=book_obj.book_price;
+--------+--------------------+----------------------+--------------------------------------------------+---------------------+
| 作者 | 最高价格那本书 | 最高价格那本书的价格 | 出版的所有书 | 出版的所有书的价格 |
+--------+--------------------+----------------------+--------------------------------------------------+---------------------+
| 吴小红 | python从入门到入狱 | 85.40 | python从入门到入狱 | 85.40 |
| 陈利 | 土耳其旅游指南 | 62.91 | 土耳其旅游指南 | 62.91 |
| 张玉 | 苏丹美食探索 | 94.49 | python从入门到入狱,苏丹美食探索 | 85.40,94.49 |
| 赵淑珍 | 苏丹美食探索 | 94.49 | python学习手册,苏丹美食探索 | 50.00,94.49 |
| 李英 | python核心编程 | 100.00 | python核心编程 | 100.00 |
| 申雷 | 马其顿旅游景点探秘 | 150.00 | python核心编程,python学习手册,马其顿旅游景点探秘 | 100.00,50.00,150.00 |
+--------+--------------------+----------------------+--------------------------------------------------+---------------------+
- 统计每一个出版社的最便宜的书
-- 统计每个出版社出版的最便宜的那本书
-- 思路是先把每个出版社出版的书籍信息求出来，然后通过子查询和连表返回想要的字段信息
-- 这里要一book表为主，因为有些出版社没有出版书籍
SELECT
*
FROM (SELECT
publisher.name,
MIN(book.price) AS min_price,
GROUP_CONCAT(book.title) AS gcbt,
GROUP_CONCAT(book.price) AS gcbp
FROM publisher RIGHT JOIN book ON publisher.id=book.publish_id
GROUP BY publisher.name) as tmp
LEFT JOIN (SELECT price as book_price, title as book_title FROM book) AS book_obj ON tmp.min_price=book_obj.book_price;
+------------+-----------+------------------------------------------------------+--------------------+------------+----------------+
| name | min_price | gcbt | gcbp | book_price | book_title |
+------------+-----------+------------------------------------------------------+--------------------+------------+----------------+
| 广州出版社 | 62.91 | 苏丹美食探索,土耳其旅游指南 | 94.49,62.91 | 62.91 | 土耳其旅游指南 |
| 合山出版社 | 50.00 | python学习手册,python从入门到入狱,马其顿旅游景点探秘 | 50.00,85.40,150.00 | 50.00 | python学习手册 |
| 南京出版社 | 100.00 | python核心编程 | 100.00 | 100.00 | python核心编程 |
+------------+-----------+------------------------------------------------------+--------------------+------------+----------------+
- 统计每一本书的作者个数
SELECT
book.title,
COUNT(book_authors.author_id)
FROM book INNER JOIN book_authors ON book.id=book_authors.book_id
GROUP BY book.title;
+--------------------+-------------------------------+
| title | COUNT(book_authors.author_id) |
+--------------------+-------------------------------+
| python从入门到入狱 | 2 |
| python学习手册 | 2 |
| python核心编程 | 2 |
| 土耳其旅游指南 | 1 |
| 苏丹美食探索 | 2 |
| 马其顿旅游景点探秘 | 1 |
+--------------------+-------------------------------+
-- 统计每一本书的作者个数和每个作者名字
SELECT
book.title,
COUNT(book_authors.author_id),
GROUP_CONCAT(author.name)
FROM book INNER JOIN book_authors ON book.id=book_authors.book_id INNER JOIN author ON book_authors.author_id=author.id
GROUP BY book.title;
+--------------------+-------------------------------+---------------------------+
| title | COUNT(book_authors.author_id) | GROUP_CONCAT(author.name) |
+--------------------+-------------------------------+---------------------------+
| python从入门到入狱 | 2 | 吴小红,张玉 |
| python学习手册 | 2 | 赵淑珍,申雷 |
| python核心编程 | 2 | 李英,申雷 |
| 土耳其旅游指南 | 1 | 陈利 |
| 苏丹美食探索 | 2 | 张玉,赵淑珍 |
| 马其顿旅游景点探秘 | 1 | 申雷 |
+--------------------+-------------------------------+---------------------------+
- 统计每一本以py开头的书籍的作者个数
SELECT
book.title,
COUNT(book_authors.author_id)
FROM book INNER JOIN book_authors ON book.id=book_authors.book_id
WHERE book.title LIKE "py%"
GROUP BY book.title;
+--------------------+-------------------------------+
| title | COUNT(book_authors.author_id) |
+--------------------+-------------------------------+
| python从入门到入狱 | 2 |
| python学习手册 | 2 |
| python核心编程 | 2 |
+--------------------+-------------------------------+
-- 统计每一本以py开头的书籍的作者个数和每个作者名字
SELECT
book.title,
COUNT(book_authors.author_id),
GROUP_CONCAT(author.name)
FROM book INNER JOIN book_authors ON book.id=book_authors.book_id INNER JOIN author ON book_authors.author_id=author.id
WHERE book.title LIKE "py%"
GROUP BY book.title;
+--------------------+-------------------------------+---------------------------+
| title | COUNT(book_authors.author_id) | GROUP_CONCAT(author.name) |
+--------------------+-------------------------------+---------------------------+
| python从入门到入狱 | 2 | 吴小红,张玉 |
| python学习手册 | 2 | 赵淑珍,申雷 |
| python核心编程 | 2 | 李英,申雷 |
+--------------------+-------------------------------+---------------------------+
- 统计不止一个作者的图书
SELECT
book.title,
COUNT(book_authors.author_id)
FROM book INNER JOIN book_authors ON book.id=book_authors.book_id
GROUP BY book.title
HAVING COUNT(book_authors.book_id) > 1;
+--------------------+-------------------------------+
| title | COUNT(book_authors.author_id) |
+--------------------+-------------------------------+
| python从入门到入狱 | 2 |
| python学习手册 | 2 |
| python核心编程 | 2 |
| 苏丹美食探索 | 2 |
+--------------------+-------------------------------+
-- 统计不止一个作者的图书和每个作者的名字
SELECT
book.title,
COUNT(book_authors.author_id),
GROUP_CONCAT(author.name)
FROM book INNER JOIN book_authors ON book.id=book_authors.book_id INNER JOIN author ON book_authors.author_id=author.id
GROUP BY book.title
HAVING COUNT(book_authors.book_id) > 1;
+--------------------+-------------------------------+---------------------------+
| title | COUNT(book_authors.author_id) | GROUP_CONCAT(author.name) |
+--------------------+-------------------------------+---------------------------+
| python从入门到入狱 | 2 | 吴小红,张玉 |
| python学习手册 | 2 | 赵淑珍,申雷 |
| python核心编程 | 2 | 李英,申雷 |
| 苏丹美食探索 | 2 | 张玉,赵淑珍 |
+--------------------+-------------------------------+---------------------------+
- 查询各个作者出的书的总价格
-- 思路是以作者分组，然后就能拿到每个作者写了那些书，每本书的价格，最后sum一下价格就完了
SELECT
author.name,
SUM(book.price),
GROUP_CONCAT(book.title),
GROUP_CONCAT(book.price)
FROM author RIGHT JOIN book_authors ON author.id=book_authors.author_id LEFT JOIN book ON book_authors.book_id=book.id
GROUP BY author.name;
+--------+-----------------+--------------------------------------------------+--------------------------+
| name | SUM(book.price) | GROUP_CONCAT(book.title) | GROUP_CONCAT(book.price) |
+--------+-----------------+--------------------------------------------------+--------------------------+
| 吴小红 | 85.40 | python从入门到入狱 | 85.40 |
| 张玉 | 179.89 | python从入门到入狱,苏丹美食探索 | 85.40,94.49 |
| 李英 | 100.00 | python核心编程 | 100.00 |
| 申雷 | 300.00 | python学习手册,马其顿旅游景点探秘,python核心编程 | 50.00,150.00,100.00 |
| 赵淑珍 | 144.49 | python学习手册,苏丹美食探索 | 50.00,94.49 |
| 陈利 | 62.91 | 土耳其旅游指南 | 62.91 |
+--------+-----------------+--------------------------------------------------+--------------------------+
that's all, see also:
