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 |
+——–+———-+———-+