'''书名 作者 出版社 价格 出版日期(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;
+----+-----------------+----------+--------------------------------+---------+--------------+
| id | b_name | b_author | b_press | b_price | publish_date |
+----+-----------------+----------+--------------------------------+---------+--------------+
| 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;
+----+-----------------+----------+--------------------------------+---------+--------------+
| id | b_name | b_author | b_press | b_price | 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';
+--------------------------------+----------+--------------+---------+
| b_press | b_author | b_name | b_price |
+--------------------------------+----------+--------------+---------+
| 人民音乐不好听出版社 | alex | 九阳神功 | 5.00 |
+--------------------------------+----------+--------------+---------+
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;
+----+-----------------+----------+--------------------------------+---------+--------------+
| id | b_name | b_author | b_press | b_price | publish_date |
+----+-----------------+----------+--------------------------------+---------+--------------+
| 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;
+----+-----------------+----------+--------------------------------+---------+--------------+
| id | b_name | b_author | b_press | b_price | publish_date |
+----+-----------------+----------+--------------------------------+---------+--------------+
| 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;
+----+-----------------+----------+--------------------------------+---------+--------------+
| id | b_name | b_author | b_press | b_price | publish_date |
+----+-----------------+----------+--------------------------------+---------+--------------+
| 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 |
| 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;
+----+-----------------+----------+--------------------------------+---------+--------------+
| id | b_name | b_author | b_press | b_price | publish_date |
+----+-----------------+----------+--------------------------------+---------+--------------+
| 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)'''