mysql 表的crud_MySQL表的CRUD及多表查询-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42610010/article/details/113257150

本文通过一系列实际案例展示了如何使用SQL语句对图书信息数据库进行增删改查等操作，包括查询特定作者的作品、按价格排序及修改记录等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

'''书名作者出版社价格出版日期(publish_date)

倚天屠龙记 egon 北京工业地雷出版社 70 2019-7-1

九阳神功 alex 人民音乐不好听出版社 5 2018-7-4

九阴真经 yuan 北京工业地雷出版社 62 2017-7-12

九阴白骨爪 jin 人民音乐不好听出版社 40 2019–8-7

独孤九剑 alex 北京工业地雷出版社 12 2017-9-1

降龙十巴掌 egon 知识产权没有用出版社 20 2019-7-5

葵花宝典 yuan 知识产权没有用出版社 33 2019–8-2

0.建表book，并向表中插入数据

1.查询egon写的所有书和价格

2.找出最贵的图书的价格

3.求所有图书的均价

4.将所有图书按照出版日期排序

5.查询alex写的所有书的平均价格

6.查询人民音乐不好听出版社出版的所有图书

7.查询人民音乐出版社出版的alex写的所有图书和价格

8.找出出版图书均价最高的作者

9.找出最新出版的图书的作者和出版社

10.显示各出版社出版的所有图书

11.查找价格最高的图书，并将它的价格修改为50元

12.删除价格最低的那本书对应的数据

13.将所有alex写的书作业修改成alexsb

14.select year(publish_date) from book

自己研究上面sql语句中的year函数的功能，完成需求：

将所有2017年出版的图书从数据库中删除'''

#create table book(id int primary key auto_increment,#b_name char(16),#b_author char(8),#b_press varchar(24),#b_price float(5,2),#publish_date date#);

#insert into book(b_name,b_author,b_press,b_price,publish_date) values#('倚天屠龙记','egon','北京工业地雷出版社',70,'2019-7-1'),#('九阳神功','alex','人民音乐不好听出版社',5,'2018-7-4'),#('九阴真经','yuan','北京工业地雷出版社',62,'2017-7-12'),#('九阴白骨爪','jin','人民音乐不好听出版社',40,'2019-8-7'),#('孤独九剑','alex','北京工业地雷出版社',12,'2017-9-1'),#('降龙十八掌','egon','知识产权没有用出版社',20,'2019-7-5'),#('葵花宝典','yuan','知识产权没有用出版社',33,'2019-8-2');

'''mysql> select * from book;

+----+-----------------+----------+--------------------------------+---------+--------------+

+----+-----------------+----------+--------------------------------+---------+--------------+

| 1 | 倚天屠龙记 | egon | 北京工业地雷出版社 | 70.00 | 2019-07-01 |

| 2 | 九阳神功 | alex | 人民音乐不好听出版社 | 5.00 | 2018-07-04 |

| 3 | 九阴真经 | yuan | 北京工业地雷出版社 | 62.00 | 2017-07-12 |

| 4 | 九阴白骨爪 | jin | 人民音乐不好听出版社 | 40.00 | 2019-08-07 |

| 5 | 孤独九剑 | alex | 北京工业地雷出版社 | 12.00 | 2017-09-01 |

| 6 | 降龙十八掌 | egon | 知识产权没有用出版社 | 20.00 | 2019-07-05 |

| 7 | 葵花宝典 | yuan | 知识产权没有用出版社 | 33.00 | 2019-08-02 |

+----+-----------------+----------+--------------------------------+---------+--------------+

7 rows in set (0.00 sec)'''

#select b_name,b_price from book where b_author='egon';

'''mysql> select b_name,b_price from book where b_author='egon';

+-----------------+---------+

| b_name | b_price |

+-----------------+---------+

| 倚天屠龙记 | 70.00 |

| 降龙十八掌 | 20.00 |

+-----------------+---------+

2 rows in set (0.01 sec)'''

#select max(b_price) from book;

'''mysql> select max(b_price) from book;

+--------------+

| max(b_price) |

+--------------+

| 70.00 |

+--------------+

1 row in set (0.01 sec)'''

#select avg(b_price) as avg_price from book;

'''mysql> select avg(b_price) as avg_price from book;

+-----------+

| avg_price |

+-----------+

| 34.571429 |

+-----------+

1 row in set (0.00 sec)'''

#select * from book order by publish_date;

'''mysql> select * from book order by publish_date;

+----+-----------------+----------+--------------------------------+---------+--------------+

+----+-----------------+----------+--------------------------------+---------+--------------+

| 3 | 九阴真经 | yuan | 北京工业地雷出版社 | 62.00 | 2017-07-12 |

| 5 | 孤独九剑 | alex | 北京工业地雷出版社 | 12.00 | 2017-09-01 |

| 2 | 九阳神功 | alex | 人民音乐不好听出版社 | 5.00 | 2018-07-04 |

| 1 | 倚天屠龙记 | egon | 北京工业地雷出版社 | 70.00 | 2019-07-01 |

| 6 | 降龙十八掌 | egon | 知识产权没有用出版社 | 20.00 | 2019-07-05 |

| 7 | 葵花宝典 | yuan | 知识产权没有用出版社 | 33.00 | 2019-08-02 |

| 4 | 九阴白骨爪 | jin | 人民音乐不好听出版社 | 40.00 | 2019-08-07 |

+----+-----------------+----------+--------------------------------+---------+--------------+

7 rows in set (0.01 sec)'''

#select b_author,avg(b_price) from book where b_author='alex';

'''mysql> select b_author,avg(b_price) from book where b_author='alex';

+----------+--------------+

| b_author | avg(b_price) |

+----------+--------------+

| alex | 8.500000 |

+----------+--------------+

1 row in set (0.00 sec)'''

#select b_press,group_concat(b_name) from book where b_press='人民音乐不好听出版社' group by b_press;

'''+--------------------------------+------------------------------+

| b_press | group_concat(b_name) |

+--------------------------------+------------------------------+

| 人民音乐不好听出版社 | 九阳神功,九阴白骨爪 |

+--------------------------------+------------------------------+

1 row in set (0.00 sec)'''

#select b_press,b_author,b_name,b_price from book where b_press='人民音乐不好听出版社' and b_author='alex';

'''mysql> select b_press,b_author,b_name,b_price from book where b_press='人民音乐不好听出版社' and b_author='alex';

+--------------------------------+----------+--------------+---------+

+--------------------------------+----------+--------------+---------+

+--------------------------------+----------+--------------+---------+

1 row in set (0.00 sec)'''

#select b_author,avg(b_price) from book group by b_author having avg(b_price) order by avg(b_price) desc limit 1;

'''mysql> select b_author,avg(b_price) from book group by b_author having avg(b_price) order by avg(b_price) desc limit 1;

+----------+--------------+

| b_author | avg(b_price) |

+----------+--------------+

| yuan | 47.500000 |

+----------+--------------+

1 row in set (0.00 sec)'''

#select b_press,group_concat(b_name)as press_books from book group by b_press;

'''mysql> select b_press,group_concat(b_name)as press_books from book group by b_press;

+--------------------------------+-------------------------------------------+

| b_press | press_books |

+--------------------------------+-------------------------------------------+

| 人民音乐不好听出版社 | 九阳神功,九阴白骨爪 |

| 北京工业地雷出版社 | 倚天屠龙记,九阴真经,孤独九剑 |

| 知识产权没有用出版社 | 降龙十八掌,葵花宝典 |

+--------------------------------+-------------------------------------------+

3 rows in set (0.00 sec)'''

#select max(b_price) from book;#update book set b_price=50 where b_price=70.0 ;#update book set b_price=50 order by b_price desc limit 1;#( 只能设置一个)

'''mysql> select * from book;

+----+-----------------+----------+--------------------------------+---------+--------------+

+----+-----------------+----------+--------------------------------+---------+--------------+

| 1 | 倚天屠龙记 | egon | 北京工业地雷出版社 | 50.00 | 2019-07-01 |

| 2 | 九阳神功 | alex | 人民音乐不好听出版社 | 5.00 | 2018-07-04 |

| 3 | 九阴真经 | yuan | 北京工业地雷出版社 | 62.00 | 2017-07-12 |

| 4 | 九阴白骨爪 | jin | 人民音乐不好听出版社 | 40.00 | 2019-08-07 |

| 5 | 孤独九剑 | alex | 北京工业地雷出版社 | 12.00 | 2017-09-01 |

| 6 | 降龙十八掌 | egon | 知识产权没有用出版社 | 20.00 | 2019-07-05 |

| 7 | 葵花宝典 | yuan | 知识产权没有用出版社 | 33.00 | 2019-08-02 |

+----+-----------------+----------+--------------------------------+---------+--------------+

7 rows in set (0.00 sec)'''

#select min(b_price) from book;#delete from book where b_price=5;

'''mysql> select * from book;

+----+-----------------+----------+--------------------------------+---------+--------------+

+----+-----------------+----------+--------------------------------+---------+--------------+

| 1 | 倚天屠龙记 | egon | 北京工业地雷出版社 | 50.00 | 2019-07-01 |

| 3 | 九阴真经 | yuan | 北京工业地雷出版社 | 62.00 | 2017-07-12 |

| 4 | 九阴白骨爪 | jin | 人民音乐不好听出版社 | 40.00 | 2019-08-07 |

| 5 | 孤独九剑 | alex | 北京工业地雷出版社 | 12.00 | 2017-09-01 |

| 6 | 降龙十八掌 | egon | 知识产权没有用出版社 | 20.00 | 2019-07-05 |

| 7 | 葵花宝典 | yuan | 知识产权没有用出版社 | 33.00 | 2019-08-02 |

+----+-----------------+----------+--------------------------------+---------+--------------+

6 rows in set (0.00 sec)'''

#update book set b_author='alexsb' where b_author='alex';

'''mysql> select * from book;

+----+-----------------+----------+--------------------------------+---------+--------------+

+----+-----------------+----------+--------------------------------+---------+--------------+

| 1 | 倚天屠龙记 | egon | 北京工业地雷出版社 | 50.00 | 2019-07-01 |

| 3 | 九阴真经 | yuan | 北京工业地雷出版社 | 62.00 | 2017-07-12 |

| 4 | 九阴白骨爪 | jin | 人民音乐不好听出版社 | 40.00 | 2019-08-07 |

| 5 | 孤独九剑 | alexsb | 北京工业地雷出版社 | 12.00 | 2017-09-01 |

| 6 | 降龙十八掌 | egon | 知识产权没有用出版社 | 20.00 | 2019-07-05 |

| 7 | 葵花宝典 | yuan | 知识产权没有用出版社 | 33.00 | 2019-08-02 |

+----+-----------------+----------+--------------------------------+---------+--------------+

6 rows in set (0.00 sec)'''

#select year(publish_date) from book

'''mysql> select year(publish_date) from book;

+--------------------+

| year(publish_date) |

+--------------------+

| 2019 |

| 2017 |

| 2019 |

| 2017 |

| 2019 |

+--------------------+

6 rows in set (0.00 sec)'''

#delete from book where year(publish_date)=2017;

'''mysql> delete from book where year(publish_date)=2017;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from book;

+----+-----------------+----------+--------------------------------+---------+--------------+

+----+-----------------+----------+--------------------------------+---------+--------------+

| 1 | 倚天屠龙记 | egon | 北京工业地雷出版社 | 50.00 | 2019-07-01 |

| 4 | 九阴白骨爪 | jin | 人民音乐不好听出版社 | 40.00 | 2019-08-07 |

| 6 | 降龙十八掌 | egon | 知识产权没有用出版社 | 20.00 | 2019-07-05 |

| 7 | 葵花宝典 | yuan | 知识产权没有用出版社 | 33.00 | 2019-08-02 |

+----+-----------------+----------+--------------------------------+---------+--------------+

4 rows in set (0.00 sec)'''