Mysql练习题

Zeus_11

已于 2023-12-25 15:29:43 修改

阅读量991

点赞数 31

文章标签： mysql 数据库

于 2023-12-15 19:10:10 首次发布

本文链接：https://blog.csdn.net/Zeus_11/article/details/135021697

版权

1.建表、插入数据

create database library;

use library;


set foreign_key_checks=0;

-- ----------------------------
-- table structure for book
-- ----------------------------
create table `book` (
`bno` char(20) not null,
`bname` varchar(50) default null,
`author` varchar(30) default null,
`publish` varchar(50) default null,
`price` float(255,0) default null, primary key (`bno`)
) engine=innodb default charset=utf8;

-- ----------------------------
-- records of book 
-- ----------------------------
insert into `book` values ('111100', '科学出版社历史丛书', '科学', '科学出版社', '108');
insert into `book` values ('111111', '数据库编程', '张作家', '机械工业出版社', '56');
insert into `book` values ('222222', '数据库开发', '西红柿', '清华大学出版社', '66');
insert into `book` values ('333333', '猛兽岛大逃亡', '梦里水乡', '机械工业出版社', '55');
insert into `book` values ('444444', 'sql数据库案例', '茶香', '科学出版社', '12');
insert into `book` values ('555555', '思维导论', 'jison', '机械工业出版社', '65');
insert into `book` values ('666666', '算法设计', 'jim', '清华大学出版社', '22');
insert into `book` values ('777777', 'mysql数据库入门', 'kimi', '机械工业出版社', '96');
insert into `book` values ('888888', '疯狂英语', 'katy', '科学出版社', '33');
insert into `book` values ('999999', '世界地图', '位居士大夫', '机械工业出版社', '88');

-- ----------------------------
-- table structure for borrow 
-- ----------------------------
create table `borrow` (
`rno` char(8) not null,
`bno` char(20) not null,
`bdate` char(8) not null,
`rdate` char(8) default null, key `rno` (`rno`),
key `bno` (`bno`),
constraint `bno` foreign key (`bno`) references `book` (`bno`), constraint `rno` foreign key (`rno`) references `reader` (`rno`)
) engine=innodb default charset=utf8;

-- ----------------------------
-- records of borrow
-- ----------------------------
insert into `borrow` values ('0001', '111111', '20150403', '20160406');
insert into `borrow` values ('0001', '333333', '20150206', '20160407');
insert into `borrow` values ('0002', '222222', '20150207', '20160408');
insert into `borrow` values ('0002', '555555', '20150208', '20160409');
insert into `borrow` values ('0003', '444444', '20150209', '20160410');
insert into `borrow` values ('0008', '444444', '20171012', null);
insert into `borrow` values ('0009', '999999', '20171215', null);
insert into `borrow` values ('0002', '222222', '20171116', null);
insert into `borrow` values ('0003', '666666', '20184545', null);
insert into `borrow` values ('0003', '888888', '20171141', null);
insert into `borrow` values ('0002', '888888', '20170678', null);

-- ----------------------------
-- table structure for reader
-- ----------------------------
create table `reader` (
`rno` char(10) not null,
`rname` char(8) not null,
`sex` char(2) not null,
`tel` char(8) default null,
`department` varchar(30) default null,
`address` varchar(30) default null, primary key (`rno`)
) engine=innodb default charset=utf8;

-- ----------------------------
-- records of reader 
-- ----------------------------
insert into `reader` values ('0001', '张三', '1', '87818112', '软件', '尚品书院'); 
insert into `reader` values ('0002', '李建 ', '0', '87818283', '网络', null); 
insert into `reader` values ('0003', '王小平', '0', '88320701', '游戏', '尚品书院'); 
insert into `reader` values ('0004', '王祝福', '1', '88320701', '游戏', null);
insert into `reader` values ('0005', '高多多', '1', '87818998', '会计', '华软1号楼'); 
insert into `reader` values ('0006', '瑞安', '0', '88320701', '游戏', null);
insert into `reader` values ('0007', '斯琴', '1', '88320701', '游戏', '绿映楼');
insert into `reader` values ('0008', '迪迪', '0', '88320701', '游戏', '蓝楹楼');
insert into `reader` values ('0009', '热吧', '1', '87818282', '工商', '蓝楹楼');
insert into `reader` values ('0010', '李四四', '1', '8789', '软件', '蓝楹楼');
insert into `reader` values ('0011', '张四', '1', '8989741', '软件', '尚品书院');

2.查询练习

cmd界面注意补齐

别名 as

中文所需引号 " "

语句结束 ;

-- 1、查询全部图书的图书号、作者、出版社和单价

-- 1、 查询全部图书的图书号、作者、出版社和单价
select book.bno 图书号,book.author 作者,book.publish 出版社,book.price 单价 from book;

-- 2、查询全体图书的信息，其中单价打8折，并设置该列的别名为“打折价”

-- 2、 查询全体图书的信息，其中单价打8折，并设置该列的别名为“打折价”
select *,book.price*0.8 打折价 from book

-- 3、显示所有借过书的借阅者的读者号、并去掉重复行

-- 3、 显示所有借过书的借阅者的读者号、并去掉重复行
select distinct reader.rno from reader,borrow where reader.rno = borrow.rno

ps:

1.等号连接reader 与 borrow 相同字段

2.distinct 去除重复行
-- 4、查询所有单价在20-30元之间的图书信息；

-- 4、 查询所有单价在20-30元之间的图书信息；
select * from book where book.price between 20 and 30

ps:

关键字 between and
-- 5、查询所有单价不在20-30元之间的图书信息

-- 5、 查询所有单价不在20-30元之间的图书信息
select * from book where book.price < 20 || book.price > 30

ps:

|| = or
-- 6、查询机械工业出版社、科学出版社、人民邮电出版社的图书信息

-- 6、 查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
select * from book where publish in ("机械工业出版社","科学出版社","人民邮电出版社")

ps:

关键字 in
-- 7、查询既不是机械工业出版社也不是科学出版社出版的图书信息

-- 7、 查询既不是机械工业出版社也不是科学出版社出版的图书信息
select * from book where publish <> "机械工业出版社" && publish <> "科学出版社"

ps:

1.<> --不等于

2.&& = and
-- 8、查询姓名的第二个字符是‘建’并且只有两个字符的读者的读者号及姓名

-- 8、 查询姓名的第二个字符是‘建’并且只有两个字符的读者的读者号及姓名
select reader.rno,reader.rname from reader where rname like "_建"

ps:

1.关键字 like

2._匹配任意一个字符
-- 9、查找姓名以‘王’开头的所有读者的读者号及姓名

-- 9、 查找姓名以‘王’开头的所有读者的读者号及姓名
select reader.rno,reader.rname from reader where rname like "王%"

ps:

% 任意匹配多个字符
-- 10、查找姓名以王、张、或李开头的所有读者的读者号及姓名

-- 10、查找姓名以王、张、或李开头的所有读者的读者号及姓名
select reader.rno,reader.rname from reader 
where rname like "王%"|| rname like "张%"|| rname like "李%"

-- 11、查询无归还日期的借阅信息

-- 11、查询无归还日期的借阅信息
select distinct * from borrow where borrow.rdate is null

rdate值为空,即没归还
-- 12、查询有归还日期的借阅信息

-- 12、查询有归还日期的借阅信息
select distinct * from borrow where borrow.rdate is not null

ps:

由上同理可得
-- 13、查询单价在50元以上、60元以下的机械工业出版社出版的图书名及单价

-- 13、查询单价在50元以上、60元以下的机械工业出版社出版的图书名及单价
select book.bname 图书名,book.price 单价 from book 
where book.publish in ("机械工业出版社") 
and book.price between 50 and 60

ps:

1.关键字 in

2.关键字 between and
-- 14、查询机械工业出版社或科学出版社出版的图书名、出版社及单价

-- 14、查询机械工业出版社或科学出版社出版的图书名、出版社及单价
select book.bname 图书名,book.publish 出版社,book.price 单价 from book 
where book.publish in ("机械工业出版社","科学出版社")

-- 15、查询读者的总人数

-- 15、查询读者的总人数
select count(rname) 总人数 from reader

ps:

count() 统计函数
-- 16、查询借阅了图书的读者的总人数

-- 16、查询借阅了图书的读者的总人数
select count(rno) 总人数 from reader 
where rno in (select distinct rno from borrow)

ps:

子查询借书的人数
-- 17、查询机械工业出版社图书的平均价格、最低价、最高价

-- 17、查询机械工业出版社图书的平均价格、最低价、最高价
select avg(price) 平均价格,min(price) 最低价,max(price) 最高价 
from book where publish in ("机械工业出版社")

ps:

1.avg() 平均值函数

2.min() 最小值函数

3.max() 最大值函数
-- 18、查询借阅图书数超过2本的读者号、总本书，并按照借阅本数从大到小排序

-- 18、查询借阅图书数超过2本的读者号、总本书，并按照借阅本数从大到小排序
select rno 读者号,count(rno) 总本数 from borrow 
group by rno having count(rno) > 2 order by count(rno) desc

ps:

1.关键字 group by 分组

2.关键字 having 筛选

3.关键字 order by 排序

4.关键字 desc 降序
-- 19、查询读者的基本信息及其借阅情况（借阅情况指历史借阅数量，在借数量）

-- 19、查询读者的基本信息及其借阅情况（借阅情况指历史借阅数量，在借数量）
select reader.*,count(borrow.rno) 历史借阅数量,
count(borrow.rno)-count(borrow.rdate) 在借数量 
from reader 
join borrow on reader.rno = borrow.rno 
group by borrow.rno

-- 20、查询读者的读者号、姓名、借阅的图书名、借出日期及归还日期，并按照读者号作升序排序

-- 20、查询读者的读者号、姓名、借阅的图书名、借出日期及归还日期，
并按照读者号作升序排序
select reader.rno 读者号,reader.rname 姓名,book.bname 书名,
borrow.bdate 借出日期,borrow.rdate 归还日期 
from borrow,reader,book 
where reader.rno = borrow.rno 
and borrow.bno = book.bno order by 读者号

-- 21、查询借阅了机械工业出版社，并且书名包含‘数据库’三个字的图书的读者，并显示读者号、姓名、书名、出版社、借出日期及归还日期；

-- 21、查询借阅了机械工业出版社，并且书名包含‘数据库’三个字的图书的读者，
并显示读者号、姓名、书名、出版社、借出日期及归还日期；
select reader.rno 读者号,reader.rname 姓名,book.bname 书名,
book.publish 出版社,borrow.bdate 借出日期,borrow.rdate 归还日期 
from borrow,reader,book 
where reader.rno = borrow.rno 
and borrow.bno = book.bno 
and book.bname like "%数据库%" 
and book.publish = "机械工业出版社"

-- 22、查询至少借阅过1本机械工业出版社的图书的读者的读者号、姓名、借阅本数，并按借阅本数多少排序

-- 22、 查询至少借阅过1本机械工业出版社的图书的读者的
读者号、姓名、借阅本数，并按借阅本数多少排序
select reader.rno 读者号,reader.rname 姓名,count(reader.rno) 借阅本书  
from reader,borrow,book 
where reader.rno = borrow.rno 
and borrow.bno = book.bno 
and book.publish = "机械工业出版社" 
group by reader.rno having count(reader.rno) >= 1
order by count(reader.rno)

-- 23、查询与‘王小平’的办公电话相同的读者的姓名（王小平本人不再列出）

-- 23、查询与‘王小平’的办公电话相同的读者的姓名（王小平本人不再列出）
select reader.rname 姓名 from reader 
where reader.tel in (select tel from reader where reader.rname = "王小平") 
and reader.rname <> "王小平"

-- 24、查询办公电话为‘88320701’的所有读者的借阅情况，要求包含借阅了图书的读者和没有借阅的读者，显示他们的读者号、姓名、书名及借阅日期

-- 24、查询办公电话为‘88320701’的所有读者的借阅情况，
要求包含借阅了图书的读者和没有借阅的读者，显示他们的读者号、姓名、书名及借阅日期
select reader.rno 读者号,reader.rname 姓名,book.bname 书名,borrow.bdate 借阅日期 
from reader left join borrow on reader.rno = borrow.rno 
left join book on borrow.bno = book.bno 
where tel = "88320701"

-- 25、查询所有单价小于平均单价的图书的书号、书名及出版社

-- 25、查询所有单价小于平均单价的图书的书号、书名及出版社
select book.bno 书号,book.bname 书名,book.publish 出版社 
from book where book.price < (select avg(book.price) from book)

-- 26、查询‘科学出版社’的图书的单价比‘机械工业出版社’最高单价还高的图书书名及单价

-- 26、查询‘科学出版社’的图书的单价比‘机械工业出版社’最高单价还高的图书书名及单价
select book.bname 书名,book.price 单价 from book 
where book.publish = "科学出版社" 
and book.price > (select max(book.price) 
from book where book.publish = "机械工业出版社")

-- 27、查询已经被借阅过并已经归还的图书信息

-- 27、查询已经被借阅过并已经归还的图书信息
select book.* from book join borrow on book.bno = borrow.bno 
where borrow.rdate is not null

ps:

is not null 不为空
-- 28、查询从未被借阅过的图书信息

-- 28、查询从未被借阅过的图书信息
select * from book where book.bno not in (select borrow.bno from borrow )

-- 29、查询正在被借阅的图书信息

-- 29、查询正在被借阅的图书信息
select * from book where book.bno in (select borrow.bno from borrow )

-- 30、查询工商系借了书还没有还的读者号、姓名和书名

-- 30、查询工商系借了书还没有还的读者号、姓名和书名
select reader.rno 读者号,reader.rname 姓名,book.bname 书名 from borrow 
join book on borrow.bno = book.bno 
join reader on borrow.rno = reader.rno 
where reader.department = "工商" 
and borrow.rdate is null

-- 31、查询借阅图书总数最多的宿舍楼

-- 31、查询借阅图书总数最多的宿舍楼
select reader.address 宿舍楼 from reader 
join borrow on reader.rno = borrow.rno 
group by reader.address 
order by count(borrow.bno) 
desc limit 1