导入所用到的数据:
create database library; use library; 接着直接复制下⾯的代码输进MySQL终端 SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for book -- ---------------------------- DROP TABLE IF EXISTS `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 -- ---------------------------- DROP TABLE IF EXISTS `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 -- ---------------------------- DROP TABLE IF EXISTS `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', '软件', '尚品书院');
1、 查询全部图书的图书号、作者、出版社和单价
select bno as 图书号, author as 作者, publish as 出版社, price as 单价 from book;
2、 查询全体图书的信息,其中单价打8折,并设置该列的别名为“打折价”
select *, price*0.8 as 打折价 from book;
3、 显示所有借过书的借阅者的读者号、并去掉重复行
select distinct rno from borrow;
4、 查询所有单价在20-30元之间的图书信息;
select * from book where price between 20 and 30;
5、 查询所有单价不在20-30元之间的图书信息
select * from book where price<20 or price>30;
6、 查询机械工业出版社、科学出版社、人民邮电出版社的图书信息
select * from book where publish='机械工业出版社' or publish='科学出版社' or publish='人民邮电出版社';
7、 查询既不是机械工业出版社也不是科学出版社出版的图书信息
select * from book where publish!='机械工业出版社' and publish!='科学出版社';
8、 查询姓名的第二个字符是‘建’并且只有两个字符的读者的读者号及姓名
select rno, rname from reader where rname like '_建';
9、 查找姓名以‘王’开头的所有读者的读者号及姓名
select rno, rname from reader where rname regexp '^王';
10、查找姓名以王、张、或李开头的所有读者的读者号及姓名
select rno, rname from reader where rname like '王%' or rname like '李%' or rname like '张%';
11、查询无归还日期的借阅信息
select * from borrow where rdate is null;
12、查询有归还日期的借阅信息
select * from borrow where rdate is not null;
13、查询单价在50元以上、60元以下的机械工业出版社出版的图书名及单价
select bname, price from book where publish='机械工业出版社' and price between 50 and 60;
14、查询机械工业出版社或科学出版社出版的图书名、出版社及单价
select bname, publish, price from book where publish='机械工业出版社' or publish='科学出版社';
15、查询读者的总⼈数
select count(1) from reader;
注意:select count (*)和select count(1)和select count(字段)比较。
一般情况下,select count (*)和select count(1)两者返回结果是一样的;
如果表中没有主键 ,使用count(1)比count(*)快;
如果有主键,那么count(主键)最快;
如果表只有一个字段的话,那么count(*)就是最快的。
另外,count(*)和count(1)的结果一样,都包括对NULL的统计,而count(字段) 不包括NULL的统计;
实操中,选择使用 count(1)的情况比较多。
16、查询借阅了图书的读者的总⼈数
select count(1) from reader where rno in (select rno from borrow);
17、查询机械工业出版社图书的平均价格、最⾼价、最低价
select avg(price) as 平均价格, max(price) as 最高价, min(price) as 最低价 from book where publish='机械工业出版社';
18、查询借阅图书数超过2本的读者号、总本书,并按照借阅本数从大到小排序
select rno, count(1) from borrow group by rno having count(1)>2 order by count(1) desc;
19、查询读者的基本信息及其借阅情况(借阅情况指历史借阅数量,在借数量)
select r.rno as 读者号, r.rname as 姓名, count(1) as 历史借阅量, count(1)-count(b.rdate) as 在借数量 from borrow as b inner join reader as r on r.rno=b.rno group by b.bno;
20、查询读者的读者号、姓名、借阅的图书名、借出日期及归还日期,并按照读者号作升序排序
select r.rno as 读者号, r.rname as 姓名, bk.bname, b.rdate, b.bdate from borrow as b inner join reader as r on r.rno=b.rno inner join book as bk on bk.bno=b.bno order by b.rno;
21、查询借阅了机械工业出版社,并且书名包含‘数据库’三个字的图书的读者,并显示读者号、姓名、书名、出版社、借出日期及归还日期;
select r.rno as 读者号, r.rname as 姓名, bk.bname, bk.publish, b.bdate, b.rdate from borrow as b inner join reader as r on r.rno=b.rno inner join book as bk on bk.bno=b.bno where bk.publish='机械工业出版社';
22、 查询至少借阅过1本机械工业出版社的图书的读者的读者号、姓名、借阅本数,并按借阅本数多少排序
select r.rno as 读者号, r.rname as 姓名, count(1) as 历史借阅量 from borrow as b inner join reader as r on r.rno=b.rno inner join book as bk on bk.bno=b.bno where bk.publish='机械工业出版社' group by b.rno having count(1)>=1;
23、查询与‘王小平’的办公电话相同的读者的姓名(王小平本人不再列出)
select rname from reader where rname!='王小平' and tel in(select tel from reader where rname='王小平');
24、查询办公电话为‘88320701’的所有读者的借阅情况,要求包含借阅了图书的读者和没有借阅的读者,显示他们的读者号、姓名、书名及借阅日期
select reader.rno as 读者号, reader.rname as 姓名, book.bname, b.rdate from borrow as b right join reader using(rno) left join book using(bno) where reader.tel='88320701';
25、查询所有单价小于平均单价的图书的书号、书名及出版社
select bno, bname, publish from book where price<(select avg(price) from book);
26、查询‘科学出版社’的图书的单价比‘机械工业出版社’最高单价还高的图书书名及单价
select bname, price from book where publish='科学出版社' and price>(select max(price) from book where publish='机械工业出版社');
27、查询已经被借阅过并已经归还的图书信息
select * from book where bno in (select bno from borrow where bdate is not null and rdate is not null);
28、查询从未被借阅过的图书信息
select * from book where bno not in (select bno from borrow);
29、查询正在被借阅的图书信息
select * from book where bno in (select bno from borrow where rdate is null);
30、查询工商系借了书还没有还的读者号、姓名和书名
select distinct r.rno as 读者号, r.rname as 姓名, bk.bname as 书名 from borrow as b inner join reader as r on r.rno=b.rno inner join book as bk on bk.bno=b.bno where b.rdate is null and r.department='工商';
31、查询借阅图书总数最多的宿舍楼
select reader.address from reader left join borrow using(rno) group by reader.address order by count(borrow.bno) desc limit 1;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战