单表查询

    1. 0.建表book，并向表中插入数据
    2. CREATE TABLE book (
    3. bk_name CHAR ( 20 ) NOT NULL,
    4. author CHAR ( 15 ),
    5. press CHAR ( 20 ),
    6. price FLOAT ( 6, 2 ),
    7. pub_date date 
    8. );
    10. insert into book values('倚天屠龙记','egon','北京工业地雷出版社','70','2019-7-1');
    11. insert into book values('九阳神功','alex','人民音乐不好听出版社','5','2018-7-4');
    12. insert into book values('九阴真经','yuan','北京工业地雷出版社','62','2017-7-12');
    13. insert into book values('九阴白骨爪','jinxin','人民音乐不好听出版社','40','2019–8-7');
    14. insert into book values('独孤九剑','alex','北京工业地雷出版社','12','2019-9-1');
    15. insert into book values('降龙十巴掌','egon','知识产权没有用出版社','20','2019-7-5');
    16. insert into book values('葵花宝典','yuan','知识产权没有用出版社','30','2019-8-2');
    18. 1.查询egon写的所有书和价格
    19. select bk_name,price from book where author = 'egon';
    21. 2.找出最贵的图书的价格
    22. select max(price) from book;
    23. select price from book order by price desc limit 1;
    25. 3.求所有图书的均价
    26. select avg(price) as book_avg_price from book 
    28. 4.将所有图书按照出版日期排序
    29. select * from book order by pub_date;
    31. 5.查询alex写的所有书的平均价格
    32. select avg(price) as avg_price from book group by author having author = 'alex';
    34. select avg(price) avg_price from book where author = 'alex';
    36. 6.查询人民音乐不好听出版社出版的所有图书
    37. select bk_name from book where press = '人民音乐不好听出版社';
    39. 7.查询人民音乐出版社出版的alex写的所有图书和价格
    40. select bk_name,price from book where press = '人民音乐不好听出版社' and author = 'alex';
    42. 8.找出出版图书均价最高的作者
    43. select author,avg(price) as avg_price from book group by author order by avg(price) desc limit 1;
    45. 9.找出最新出版的图书的作者和出版社
    46. select author,press from book order by pub_date desc limit 1;
    48. 10.显示各出版社出版的所有图书
    49. select press,group_concat(bk_name) as book_name from book group by press
    51. 11.查找价格最高的图书，并将它的价格修改为50元
    52. begin
    53. select max(price) from book;
    54. update book set price=50 where price = 62;
    55. commit
    57. 12.删除价格最低的那本书对应的数据
    58. begin;
    59. select min(price) from book;
    60. delete from book where price = 5;
    61. commit;
    63. 13.将所有alex写的书作业修改成alexsb
    64. update book set author = 'alexsb' where author = 'alex';
    66. 14.select year(publish_date) from book
    67. 自己研究上面sql语句中的year函数的功能，完成需求：
    68. 将所有2017年出版的图书从数据库中删除
    69. select bk_name,year(pub_date) as yearc from book where year(pub_date) = '2017';
    70. delect from book where year(pub_date) = '2017';
    72. 15.有文件如下，请根据[链接](https://www.cnblogs.com/Eva-J/articles/9772614.html)自学pymysql模块，使用python写代码将文件中的数据写入数据库
    73. 学python从开始到放弃|alex|人民大学出版社|50|2018-7-1
    74. 学mysql从开始到放弃|egon|机械工业出版社|60|2018-6-3
    75. 学html从开始到放弃|alex|机械工业出版社|20|2018-4-1
    76. 学css从开始到放弃|wusir|机械工业出版社|120|2018-5-2
    77. 学js从开始到放弃|wusir|机械工业出版社|100|2018-7-30
    79. 详见py文件