【跟晓月学数据库】基于book库的mysql进阶实战

前言

上篇文章中，我们已经导入了book库，如果你还没有导入book库，参考：【跟晓月学数据库】使用MySQLdump 对数据导入导出

这篇文章，主要是基于book库的操作，希望对你有用。

🏠个人主页：我是沐风晓月
🧑个人简介：大家好，我是沐风晓月，阿里云社区专家博主😉😉
💕 座右铭： 先努力成长自己，再帮助更多的人 ，一起加油进步🍺🍺🍺
💕欢迎大家：这里是CSDN，我总结知识的地方，喜欢的话请三连，有问题请私信😘

查看已经导入的book库的信息

[root@mufeng41 ~]# mysql -p'Root!2#admin'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| book               |
| mysql              |
| performance_schema |
| sys                |
| test01             |
| test_db            |
+--------------------+
7 rows in set (0.00 sec)

mysql> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

mysql> use book
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from category;
+---------+---------------+
| bTypeId | bTypeName     |
+---------+---------------+
|       1 | windows应用   |
|       2 | 网站          |
|       3 | 3D动画        |
|       4 | linux学习     |
|       5 | Delphi学习    |
|       6 | 黑客          |
|       7 | 网络技术      |
|       8 | 安全          |
|       9 | 平面          |
|      10 | AutoCAD技术   |
+---------+---------------+
10 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

查看表结构

mysql> desc books;
+------------+------------------------------------------------+------+-----+---------+----------------+
| Field      | Type                                           | Null | Key | Default | Extra          |
+------------+------------------------------------------------+------+-----+---------+----------------+
| bId        | int(4)                                         | NO   | PRI | NULL    | auto_increment |
| bName      | varchar(255)                                   | YES  |     | NULL    |                |
| bTypeId    | enum('1','2','3','4','5','6','7','8','9','10') | YES  |     | NULL    |                |
| publishing | varchar(255)                                   | YES  |     | NULL    |                |
| price      | int(4)                                         | YES  |     | NULL    |                |
| pubDate    | date                                           | YES  |     | NULL    |                |
| author     | varchar(30)                                    | YES  |     | NULL    |                |
| ISBN       | varchar(255)                                   | YES  |     | NULL    |                |
+------------+------------------------------------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)


mysql> desc category;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| bTypeId   | int(4)      | NO   | PRI | NULL    | auto_increment |
| bTypeName | varchar(40) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

案例实战

1. 查询价格大于50的图书名称和作者。

mysql> SELECT bName, author
    -> FROM books
    -> WHERE price > 50;
1
2
3

2. 查询出版社为"电脑爱好者杂志社"的图书信息，包括图书名称、作者和出版日期。

SELECT bName, author, pubDate
FROM books
WHERE publishing = '电脑爱好者杂志社';
1
2
3

3. 查询类别为"网站"的图书数量。

SELECT COUNT(*) AS book_count
FROM books
WHERE bTypeId IN (SELECT bTypeId FROM category WHERE bTypeName = '网站');
1
2
3

4. 查询每个类别的平均价格，并按照平均价格降序排列。

SELECT c.bTypeName, AVG(b.price) AS avg_price
FROM category c
JOIN books b ON c.bTypeId = b.bTypeId
GROUP BY c.bTypeName
ORDER BY avg_price DESC;
1
2
3
4
5

5. 查询发布日期在2022年1月1日到2022年12月31日之间的图书信息，包括图书名称和发布日期。

SELECT b.bName, b.pubDate, c.bTypeName
FROM books b
JOIN category c ON b.bTypeId = c.bTypeId
WHERE b.pubDate BETWEEN '2021-01-01' AND '2021-12-31' AND c.bTypeName = '网站';
1
2
3
4

6. 查询价格在指定范围内（40-50），并且出版社为"航空工业出版社"的图书信息，包括图书名称、价格和出版社。

mysql> SELECT bName, price, publishing
    -> FROM books
    -> WHERE price BETWEEN 40 AND 50 AND publishing = '航空工业出版社';
+-----------------------+-------+-----------------------+
| bName                 | price | publishing            |
+-----------------------+-------+-----------------------+
| 黑客与网络安全        |    41 | 航空工业出版社        |
+-----------------------+-------+-----------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

7. 插入信息：
   要向 “books” 表中增加一行数据，符合以下要求：

bTypeId：4
bTypeName：linux技术
bName=linux大全
publishing：互联网老辛出品
author：沐风晓月
pubDate：2023-03-27
price：88
ISBN：200001

INSERT INTO books (bTypeId, bName, publishing, price, pubDate, author, ISBN)
VALUES (4, 'linux大全', '互联网老辛出品', 88, '2023-03-27', '沐风晓月', '200001');
1
2

8. 查询某个特定作者的图书信息：

mysql> SELECT bName, price, pubDate
    -> FROM books
    -> WHERE author = '沐风晓月';
+--------------+-------+------------+
| bName        | price | pubDate    |
+--------------+-------+------------+
| 填写书名     |    88 | 2023-03-27 |
| linux大全    |    88 | 2023-03-27 |
+--------------+-------+------------+
2 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11

9. 查询所有图书的信息及其对应的出版社，包括图书名称、作者和出版社名称。如果某个图书没有对应的出版社，则显示为null。

SELECT b.bName, b.author, p.publishing
FROM books b
RIGHT JOIN publishing p ON b.publishingId = p.publishingId;
1
2
3

总结

关于MySQL的进阶实战，你学会了吗，欢迎评论区留言一起探讨。