随笔 - 54,  文章 - 0,  评论 - 0,  阅读 - 13074

导入所用到的数据:

复制代码
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;

 

posted on   心有所信方能行远  阅读(1528)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示