1.1查询库存表中的书号和库存状态列，要求消除重复行

mysql> use librarydb;

Database changed

mysql> select distinct 书号,库存状态 from 库存表;

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

| 书号  | 库存状态 |

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

| A0120 | 在馆     |

| A0120 | 借出     |

| A0134 | 在馆     |

| A0134 | 借出     |

| B1101 | 在馆     |

| C1269 | 丢失     |

| C1269 | 借出     |

| C3256 | 借出     |

| C3256 | 在馆     |

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

9 rows in set (0.03 sec)

1.2 查询读者表中的姓名和单位列，显示的列标题改为姓名显示为“name”，单位显示为“college”

mysql> select 姓名 as name,单位 as college from 读者表;

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

| name   | college  |

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

| 张小东 | 软件学院 |

| 苏明   | 财经学院 |

| 梁小红 | 软件学院 |

| 赵明敏 | 传媒学院 |

| 李丰年 | 计财处   |

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

5 rows in set (0.03 sec)

1.3查询图书表中每种书的书名和金额

mysql> select 书名,数量*单价 as 金额 from 图书表;

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

| 书名           | 金额   |

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

| 庄子           |  92.50 |

| 唐诗三百首     | 280.00 |

| 西方经济学史   | 318.40 |

| 商业博弈       | 585.00 |

| 数据结构       | 580.00 |

| 品牌策划与推广 | 252.00 |

| C语言程序设计  | 404.80 |

| MySQL数据库    | 261.00 |

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

8 rows in set (0.05 sec)

1.4 查询库存表中员工的条码和库存状态，要求库存状态值为在馆时显示为“1”，借出时显示为“0”，丢失时显示为“-1”

mysql> select 条码,

    -> case

    -> when 库存状态='借出' then '0'

    -> when 库存状态='在馆' then '1'

    -> else '-1'

    -> end as 库存状态

    -> from 库存表;

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

| 条码   | 库存状态 |

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

| 123412 | 1        |

| 123413 | 0        |

| 223410 | 1        |

| 223411 | 0        |

| 311231 | 1        |

| 321123 | -1       |

| 321124 | 0        |

| 411111 | 0        |

| 411112 | 0        |

| 411113 | 1        |

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

10 rows in set (0.04 sec)

2.1 查询图书表中数量大于10本的图书书名、数量和出版社

mysql> select 书名,数量,出版社 from 图书表 where 数量>10;

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

| 书名          | 数量 | 出版社         |

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

| 商业博弈      |   15 | 北京大学出版社 |

| 数据结构      |   20 | 高等教育出版社 |

| C语言程序设计 |   11 | 高等教育出版社 |

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

3 rows in set (0.07 sec)

2.2查询库存表的存放位置含有“A”且库存状态为“借出” 的图书信息

mysql> select * from 库存表 where 库存状态='借出' and 存放位置 like '%A%';

1054 - Unknown column '存放位置' in 'where clause'

mysql> select * from 库存表 where 库存状态='借出' and 位置 like '%A%';

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

| 条码   | 书号  | 位置   | 库存状态 |

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

| 123413 | A0120 | 1-A-57 | 借出     |

| 321124 | C1269 | 3-A-13 | 借出     |

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

2 rows in set (0.09 sec)

2.3 查询图书表中财经和文学类图书数量大于5本的图书信息

mysql> select * from 图书表 where 类别='财经' or 类别='文学' and 数量>5;

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

| 书号  | 书名         | 类别 | 作者   | 出版社         | 单价  | 数量 |

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

| A0134 | 唐诗三百首   | 文学 | 李平   | 安徽科学出版社 | 28.00 |   10 |

| B1101 | 西方经济学史 | 财经 | 莫竹芩 | 海南出版社     | 39.80 |    8 |

| B2213 | 商业博弈     | 财经 | 孔英   | 北京大学出版社 | 39.00 |   15 |

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

3 rows in set (0.08 sec)

2.3

mysql> select * from 图书表 where (类别='财经' or 类别='文学') and 数量>5;

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

| 书号  | 书名         | 类别 | 作者   | 出版社         | 单价  | 数量 |

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

| A0134 | 唐诗三百首   | 文学 | 李平   | 安徽科学出版社 | 28.00 |   10 |

| B1101 | 西方经济学史 | 财经 | 莫竹芩 | 海南出版社     | 39.80 |    8 |

| B2213 | 商业博弈     | 财经 | 孔英   | 北京大学出版社 | 39.00 |   15 |

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

3 rows in set (0.07 sec)

2.4 查询借阅表中还书日期为空 的记录

mysql> select * from 借阅表 where 还书日期 is null;

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

| 借阅号 | 条码   | 读者编号 | 借阅日期   | 还书日期 | 借阅状态 |

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

| 100001 | 123413 | 0001     | 2020-11-05 | NULL     | 借阅     |

| 100003 | 321123 | 1001     | 2020-07-01 | NULL     | 过期     |

| 100005 | 321124 | 0001     | 2020-10-15 | NULL     | 借阅     |

| 100006 | 223411 | 2001     | 2020-10-16 | NULL     | 借阅     |

| 100008 | 411111 | 0001     | 2020-09-25 | NULL     | 借阅     |

| 100009 | 411111 | 1001     | 2020-10-08 | NULL     | 借阅     |

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

6 rows in set (0.09 sec)

3.1查询“张小东”的基本状况和图书借阅情况

mysql> select * from 读者表,借阅表 where 读者表.读者编号=借阅表.读者编号 and 读者表.姓名='张小东';

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

| 读者编号 | 姓名   | 类别号 | 单位     | 有效性 | 借阅号 | 条码   | 读者编号 | 借阅日期   | 还书日期 | 借阅状态 |

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

| 0001     | 张小东 | 1      | 软件学院 | 有效   | 100001 | 123413 | 0001     | 2020-11-05 | NULL     | 借阅     |

| 0001     | 张小东 | 1      | 软件学院 | 有效   | 100005 | 321124 | 0001     | 2020-10-15 | NULL     | 借阅     |

| 0001     | 张小东 | 1      | 软件学院 | 有效   | 100008 | 411111 | 0001     | 2020-09-25 | NULL     | 借阅     |

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

3 rows in set (0.08 sec)

3.2查询借阅状态为“借阅”的图书的书号和条码

mysql> select 库存表.书号,借阅表.条码 from 库存表,借阅表  where 借阅表.条码=库存表.条码 and 借阅表.借阅状态='借阅';

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

| 书号  | 条码   |

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

| A0120 | 123413 |

| C1269 | 321124 |

| A0134 | 223411 |

| C3256 | 411111 |

| C3256 | 411111 |

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

5 rows in set (0.09 sec)

3.3查询每位读者的姓名、单位、可借天数和可借数量

mysql> select 读者表.姓名,读者表.单位,读者类型表.可借天数,读者类型表.可借数量 from 读者表,读者类型表 where 读者表.类别号=读者类型表.类别号;

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

| 姓名   | 单位     | 可借天数 | 可借数量 |

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

| 张小东 | 软件学院 |       30 |       10 |

| 苏明   | 财经学院 |       30 |       10 |

| 梁小红 | 软件学院 |       60 |       20 |

| 赵明敏 | 传媒学院 |       60 |       20 |

| 李丰年 | 计财处   |       20 |       15 |

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

5 rows in set (0.09 sec)

3.4查询每个读者的借阅信息、包括读者姓名、书名、借阅日期、借阅状态

mysql> select 读者表.姓名,图书表.书名,借阅表.借阅日期,借阅表.借阅状态 from 读者表 join 借阅表 on 读者表.读者编号=借阅表.读者编号 join 库存表 on 借阅表.条码=库存表.条码  join 图书表 on 库存表.书号=图书表.书号;

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

| 姓名   | 书名        | 借阅日期   | 借阅状态 |

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

| 张小东 | 庄子        | 2020-11-05 | 借阅     |

| 张小东 | 数据结构    | 2020-10-15 | 借阅     |

| 张小东 | MySQL数据库 | 2020-09-25 | 借阅     |

| 苏明   | 唐诗三百首  | 2020-09-28 | 已还     |

| 梁小红 | 数据结构    | 2020-07-01 | 过期     |

| 梁小红 | MySQL数据库 | 2020-10-08 | 借阅     |

| 赵明敏 | MySQL数据库 | 2020-09-01 | 已还     |

| 李丰年 | 数据结构    | 2020-10-09 | 已还     |

| 李丰年 | 唐诗三百首  | 2020-10-16 | 借阅     |

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

9 rows in set (0.10 sec)

3.5查询库存表中每本书的条码、位置和借阅的读者编号。没有借阅的，读者编号用null表示

mysql> select 库存表.条码,库存表.位置,借阅表.读者编号,借阅表.借阅状态 from 库存表 left join 借阅表 on (库存表.条码=借阅表.条码);

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

| 条码   | 位置   | 读者编号 | 借阅状态 |

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

| 123412 | 1-A-56 | NULL     | NULL     |

| 123413 | 1-A-57 | 0001     | 借阅     |

| 223410 | 2-B-01 | NULL     | NULL     |

| 223411 | 2-B-02 | 0002     | 已还     |

| 223411 | 2-B-02 | 2001     | 借阅     |

| 311231 | 2-C-23 | NULL     | NULL     |

| 321123 | 3-A-12 | 1001     | 过期     |

| 321124 | 3-A-13 | 2001     | 已还     |

| 321124 | 3-A-13 | 0001     | 借阅     |

| 411111 | 3-B-01 | 1002     | 已还     |

| 411111 | 3-B-01 | 0001     | 借阅     |

| 411111 | 3-B-01 | 1001     | 借阅     |

| 411112 | 3-B-02 | NULL     | NULL     |

| 411113 | 3-B-03 | NULL     | NULL     |

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

14 rows in set (0.10 sec)

4.1 按单位统计出该单位的读者人数

mysql> select 单位,count(*) from 读者表 group by 单位;

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

| 单位     | count(*) |

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

| 软件学院 |        2 |

| 财经学院 |        1 |

| 传媒学院 |        1 |

| 计财处   |        1 |

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

4 rows in set (0.08 sec)

4.2查找读者数量在2人及以上的部门名称和读者人数

mysql> select 单位,count(*) as 读者人数 from 读者表 group by 单位 having count(*)>=2;

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

| 单位     | 读者人数 |

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

| 软件学院 |        2 |

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

1 row in set (0.02 sec)

4.3分别统计各出版社的图书平均单价和总金额

mysql> select 出版社,avg(单价) as '平均单价',sum(单价*数量) as '总金额' from 图书表 group by 出版社;

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

| 出版社         | 平均单价  | 总金额 |

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

| 吉林大学出版社 | 18.500000 |  92.50 |

| 安徽科学出版社 | 28.000000 | 280.00 |

| 海南出版社     | 39.799999 | 318.40 |

| 北京大学出版社 | 39.000000 | 585.00 |

| 高等教育出版社 | 32.900000 | 984.80 |

| 人民邮电出版社 | 42.000000 | 252.00 |

| 电子工业出版社 | 29.000000 | 261.00 |

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

7 rows in set (0.05 sec)

4.4对借阅表先按照读者编号吗，再按照条码统计图书的借阅次数，并显示小计

mysql> select 读者编号,条码,count(*) as '借阅次数' from 借阅表 group by 读者编号,条码 order by count(*);

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

| 读者编号 | 条码   | 借阅次数 |

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

| 0002     | 223411 |        1 |

| 2001     | 321124 |        1 |

| 2001     | 223411 |        1 |

| 0001     | 411111 |        1 |

| 0001     | 123413 |        1 |

| 1001     | 321123 |        1 |

| 0001     | 321124 |        1 |

| 1002     | 411111 |        1 |

| 1001     | 411111 |        1 |

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

9 rows in set (0.09 sec)

4.5 将图书表按照数量从小到大排列

mysql> select * from 图书表 order by 数量 desc;

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

| 书号  | 书名           | 类别   | 作者   | 出版社         | 单价  | 数量 |

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

| C1269 | 数据结构       | 计算机 | 李刚   | 高等教育出版社 | 29.00 |   20 |

| B2213 | 商业博弈       | 财经   | 孔英   | 北京大学出版社 | 39.00 |   15 |

| C3182 | C语言程序设计  | 计算机 | 李学刚 | 高等教育出版社 | 36.80 |   11 |

| A0134 | 唐诗三百首     | 文学   | 李平   | 安徽科学出版社 | 28.00 |   10 |

| C3256 | MySQL数据库    | 计算机 | 孙季红 | 电子工业出版社 | 29.00 |    9 |

| B1101 | 西方经济学史   | 财经   | 莫竹芩 | 海南出版社     | 39.80 |    8 |

| C3121 | 品牌策划与推广 | 计算机 | 张晓红 | 人民邮电出版社 | 42.00 |    6 |

| A0120 | 庄子           | 文学   | 庄周   | 吉林大学出版社 | 18.50 |    5 |

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

8 rows in set (0.09 sec)

4.6将借阅表按照借阅状态排序，状态相同的再按照借阅日期从小到大排序

mysql> select 读者编号,条码,count(*) as '借阅次数' from 借阅表 group by 读者编号,条码 order by 借阅次数;

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

| 读者编号 | 条码   | 借阅次数 |

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

| 0002     | 223411 |        1 |

| 2001     | 321124 |        1 |

| 2001     | 223411 |        1 |

| 0001     | 411111 |        1 |

| 0001     | 123413 |        1 |

| 1001     | 321123 |        1 |

| 0001     | 321124 |        1 |

| 1002     | 411111 |        1 |

| 1001     | 411111 |        1 |

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

9 rows in set (0.07 sec)

mysql> select * from 借阅表 order by 借阅状态,借阅日期;

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

| 借阅号 | 条码   | 读者编号 | 借阅日期   | 还书日期   | 借阅状态 |

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

| 100008 | 411111 | 0001     | 2020-09-25 | NULL       | 借阅     |

| 100009 | 411111 | 1001     | 2020-10-08 | NULL       | 借阅     |

| 100005 | 321124 | 0001     | 2020-10-15 | NULL       | 借阅     |

| 100006 | 223411 | 2001     | 2020-10-16 | NULL       | 借阅     |

| 100001 | 123413 | 0001     | 2020-11-05 | NULL       | 借阅     |

| 100007 | 411111 | 1002     | 2020-09-01 | 2020-09-24 | 已还     |

| 100002 | 223411 | 0002     | 2020-09-28 | 2020-10-13 | 已还     |

| 100004 | 321124 | 2001     | 2020-10-09 | 2020-10-14 | 已还     |

| 100003 | 321123 | 1001     | 2020-07-01 | NULL       | 过期     |

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

9 rows in set (0.07 sec)

4.7对借阅表中读者按照类别组进行分组，同类别的再按照单位分别统计借阅次数，并按照次数从小到大排列

mysql> select  类别号,单位,count(*) as '借阅次数' from 借阅表,读者表 where 借阅表.读者编号=读者表.读者编号 group by 类别号,单位 order by 借阅次数 desc;

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

| 类别号 | 单位     | 借阅次数 |

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

| 1      | 软件学院 |        3 |

| 2      | 软件学院 |        2 |

| 3      | 计财处   |        2 |

| 1      | 财经学院 |        1 |

| 2      | 传媒学院 |        1 |

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