Loading

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:根据数据集的特点,对某些查询条件会做些许更改;有的查询结果为了便于观察,多返回了一些字段。

  1. 通过作者名查询该作者的详情信息
-- 查询"吴小红"的详情信息
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 |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
  1. 通过作者的电话查询对应该作者的基本信息
-- 查询手机号是"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 |
+----+------+-----+--------+-----------+----+------------+-------------+----------------------------------+

  1. 通过书籍名查询其对应的出版社
-- 查询"马其顿旅游景点探秘"的出版社信息
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 |
+------------+--------------------------+
  1. 通过出版社名称查询旗下出版的书籍信息
-- 查询"广州出版社"出版的所有书籍信息
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)    |
+------------+-----------------------------+
| 广州出版社 | 土耳其旅游指南,苏丹美食探索 |
+------------+-----------------------------+
  1. 查询名字为xxx作者的手机号和年龄
-- 查询"吴小红"的手机号和年龄
SELECT 
  authordetail.tel,
  author.age
FROM author INNER JOIN authordetail ON author.detail_id=authordetail.id
WHERE author.name="吴小红";
+-------------+-----+
| tel         | age |
+-------------+-----+
| 13743395855 |  49 |
+-------------+-----+
  1. 查询xxx书籍是哪个出版社出版的
-- 查询"马其顿旅游景点探秘"的出版社信息
SELECT 
  book.title,
  publisher.name
FROM book LEFT JOIN publisher ON book.publish_id=publisher.id
WHERE book.title="马其顿旅游景点探秘";
+--------------------+------------+
| title              | name       |
+--------------------+------------+
| 马其顿旅游景点探秘 | 关岭出版社 |
+--------------------+------------+
  1. 查询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 |
+------+-----+-------+
  1. 修改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 |
+----+--------+-----+--------+-----------+----+------------+-------------+--------------------------------+
  1. 修改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 |
+----+--------------------+--------+------------+------------+----+------------+--------------------------+
  1. 为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学习手册 | 赵淑珍,申雷               |
+----------------+---------------------------+
  1. 删除一个作者

首先,在生产中,请谨慎使用删除语句!!数据无价!!!!

-- 要是在绑定外键时,没有指定级联删除,那么删除时,会报错,因为第三张表有记录行引用了当前要删除的作者id
-- 添加数据不容易,这里就不真正的执行删除语句了,只需要掌握删除语句结构即可
-- DELETE FROM author WHERE id=10;
  1. 删除一本书
-- 套路跟删除作者一样,这里不再多表,也不真正的执行删除语句了
-- DELETE FROM book WHERE title="python学习手册";
  1. 查询所有书籍的总数
SELECT COUNT(id) FROM book;
+-----------+
| COUNT(id) |
+-----------+
|         6 |
+-----------+
  1. 查询所有书籍的平均价格
SELECT AVG(price) FROM book;
+------------+
| AVG(price) |
+------------+
|  74.531667 |
+------------+
  1. 查询每个出版社出版书籍的平均价格
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       |
+------------+-----------------+------------------------------------------------------+--------------------------+
  1. 查询每个作者出版书的最高价格
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 | 土耳其旅游指南     |
+--------+--------------------------------------------------+--------------------------+-----------------+--------------------+
  1. 查询"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 | 合山出版社 |
+--------------------+--------+------------+
  1. 查询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 |
+----+----------------+-------+------------+------------+
  1. 查询价格为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 | 合山出版社 |
+--------------------+--------+------------+
  1. 查询价格在100~200之间的所有书籍名称及其价格
SELECT 
  book.title,
  book.price
FROM book WHERE price BETWEEN 100 AND 200;
+--------------------+--------+
| title              | price  |
+--------------------+--------+
| python核心编程     | 100.00 |
| 马其顿旅游景点探秘 | 150.00 |
+--------------------+--------+
  1. 查询"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学习手册     | 申雷 |
+--------+--------------------+------+
  1. 查询作者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              |
+--------+--------+--------------------+-----------------+--------------------------------------------------+--------------------------+
  1. 查询作者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 |
+--------+--------+--------------------+-----------------+
  1. 查询每个作者出版的书的最高价格的平均值
-- 查询每个作者出版的书的最高价格
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 |
+--------------------+
  1. 每个作者出版的所有书的最高价格以及最高价格的那本书的名称
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 |
+--------+--------------------+----------------------+--------------------------------------------------+---------------------+
  1. 统计每一个出版社的最便宜的书
-- 统计每个出版社出版的最便宜的那本书
-- 思路是先把每个出版社出版的书籍信息求出来,然后通过子查询和连表返回想要的字段信息
-- 这里要一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核心编程 |
+------------+-----------+------------------------------------------------------+--------------------+------------+----------------+
  1. 统计每一本书的作者个数
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 | 申雷                      |
+--------------------+-------------------------------+---------------------------+
  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 | 李英,申雷                 |
+--------------------+-------------------------------+---------------------------+
  1. 统计不止一个作者的图书
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 | 张玉,赵淑珍               |
+--------------------+-------------------------------+---------------------------+
  1. 查询各个作者出的书的总价格
-- 思路是以作者分组,然后就能拿到每个作者写了那些书,每本书的价格,最后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:

mysql 按年月查询

posted @ 2017-06-12 15:40  听雨危楼  阅读(1623)  评论(0编辑  收藏  举报