MySQL特别特别基础题

-- 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
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,author,publish,price from book;
2、 查询全体图书的信息，其中单价打8折，并设置该列的别名为“打折价”；
select bno,author,publish,price ,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 not between 20 and 30;
6、 查询机械工业出版社、科学出版社、人民邮电出版社的图书信息；
select *
from book 
where publish in ('机械工业出版社','科学出版社','人民邮电出版社');

7、 查询既不是机械工业出版社也不是科学出版社出版的图书信息；
select *
from book 
where publish not in ('机械工业出版社','科学出版社');
8、 查询姓名的第二个字符是‘建’并且只有两个字符的读者的 读者号及姓名
select rno,rname
from reader
where rname like '_建';
9、 查找姓名以‘王’开头的所有读者的读者号及姓名；
select rno,rname
from reader
where rname like '王%';
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、 查询单价在20元以上、30元以下的机械工业出版社出版的图书名及单价；
select bname,price 
from book
where price >20 and price < 30 and publish = '机械工业出版社';
14、 查询机械工业出版社或科学出版社出版的图书名、出版社及单价；
select bname,publish,price 
from book
where  publish in  ('机械工业出版社','科学出版社');
15、 查询读者的总人数；
select count(*)
from reader;
16、 查询借阅了图书的读者的总人数；
select count(distinct rno)
from borrow;
17、 查询机械工业出版社图书的平均价格、最高价、最低价；
select avg(price),max(price),min(price)
from book
where publish = '机械工业出版社';
18、 查询借阅图书数超过2本的读者号、总本书，并按照借阅本数从大到小排序；
select rno,count(bno) as cn
from borrow 
group by rno
having count(bno) >2
order by cn desc;
19、 查询读者的基本信息及其借阅情况（借阅情况指历史借阅数量，在借数量）；
select *
from reader a
left join borrow b on a.rno = b.rno;

20、 查询读者的读者号、姓名、借阅的图书名、借出日期及归还日期，并按照读者号作升序排序；
select a.rno,rname,bname,bdate,rdate
from reader a
join borrow b on a.rno = b.rno 
join book c on b.bno = c.bno
order by rno;
21、 查询借阅了机械工业出版社，并且书名包含‘数据库’三个字的图书的读者，并显示读者号、姓名、书名、出版社、借出日期及归还日期；
select r.rno as '读者号',r.rname as '姓名',bk.bname,bk.publish,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
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 tel = (select tel from reader where rname = '王小平') and 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);

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 ‘姓名’
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;
31、 查询借阅图书总数最多的宿舍楼
select reader.address from reader
left join borrow using(rno)
GROUP BY reader.address ORDER BY count(borrow.bno) desc LIMIT 1;