mysql数据库练习题(涵盖几乎所有知识点)

# 建立表
create database library;
use library;
create table booktype(
  typeid int primary key,
  typename varchar(20)
 
);
 
create table readerType(
  # 如果这个字段需要进行链接外键，那么这个也没有要求主键约束，那么我们只能弄一个唯一约束
  retypeid int unique not null,
  readerName varchar(20) not null,
  borrowquantity int not null,
  borrowDay int
 
);
 
create table book(
  bookid char(10) PRIMARY key,
  bookName varchar(20) not null,
  typeid int,
  bookauthor varchar(20),
  bookPublisher varchar(20),
  bookPrice double,
  borrowsum int,
  FOREIGN key (typeid) REFERENCES booktype(typeid)
 
);
 
create table bookstorage(
  bookbarcode char(20) PRIMARY key,
  bookId char(10) not null,
  bookInTime datetime,
  bookStatus varchar(4),
  FOREIGN key (bookId) REFERENCES book(bookId)
 
 
);
 
 
 
create table reader(
  readerId char(10) PRIMARY key,
  readerName varchar(20) not null,
  readerPass varchar(20) not null,
  retypeId int not null,
  readerDate dateTime,
  readerStatus varchar(4),
  FOREIGN key (reTypeId) REFERENCES readerType(reTypeId)
 
);
 
create table bookBorrow(
  borrowId char(10) PRIMARY key,
  bookBarCode char(20) not null,
  readerId char(10) not null,
  borrowTime datetime,
  returnTime datetime,
  borrowStatus varchar(4),
  FOREIGN key (bookBarCode) REFERENCES bookStorage(bookBarCode),
  Foreign key (readerId) REFERENCES reader(readerId)
 
);
 
 
 
# 插入数据
 
insert into booktype VALUES
(1, "自然科学"),
(2, "数学"),
(3, "计算机"),
(4, "建筑水利"),
(5, "旅游地理"),
(6, "励志/自我实现"),
(7, "工业技术"),
(8, "基础医学"),
(9, "室内设计"),
(10, "人文景观");
 
insert into book values
('TP39/1712','Java程序设计',3,'陈永红','机械工业出版社',35.5,30),
('013452','离散数学',2,'张小新','机械工业出版社',45.5,10),
('TP/3452','JSP程序设计案例',3,'刘城清','电子工业出版社',42.8,8),
('TH/2345','机械设计手册',7,'黄明凡','人民邮电出版社',40,10),
('R/345677','中医的故事',8,'李奇德','国防工业出版社',20.0,5);
 
insert into bookstorage values
('132782','TP39/1712','2009-08-10 00:00:00','在馆'),
('132789','TP39/1712','2009-08-10 00:00:00','借出'),
('145234','013452','2008-12-06 00:00:00','借出'),
('145321','TP/3452','2007-11-04 00:00:00','借出'),
('156833','TH/2345','2009-12-04 00:00:00','借出'),
('345214','R/345677','2008-11-03 00:00:00','在馆');
 
insert into readertype values
(1,'学生',10,30),
(2,'教师',20,60),
(3,'管理员',15,30),
(4,'职工',15,20);
 
insert into reader values
('0016','苏小东',123456,1,'1999-09-09 00:00:00','有效'),
('0017','张明',123456,1,'2010-09-10 00:00:00','有效'),
('0018','梁君红',123456,1,'2010-09-10 00:00:00','有效'),
('0021','赵清远',123456,2,'2010-07-01 00:00:00','有效'),
('0034','李瑞清',123456,3,'2009-08-03 00:00:00','有效'),
('0042','张明月',123456,4,'1997-04-23 00:00:00','有效');
 
insert into bookborrow values
('001328','132789','0017','2011-01-24 00:00:00','2011-02-28 00:00:00','已还'),
('001356','145234','0018','2011-02-12 00:00:00','2011-02-27 00:00:00','已还'),
('001432','132782','0016','2011-03-04 00:00:00','2011-04-05 00:00:00','已还'),
('001435','145321','0021','2011-08-09 00:00:00','2011-09-02 00:00:00','已还'),
('001578','156833','0034','2011-10-01 00:00:00','2011-11-01 00:00:00','未还'),
('001679','345214','0042','2011-02-21 00:00:00','2011-03-05 00:00:00','未还');
 
 
 

use library ;
-- 查询数据library
show create database library ;
 
-- 1.查询book表的书号, 书名和借出数量
select bookid, bookname , borrowsum from book; 
 
-- 2.用别名查询book表的书号, 书名和借出数量
select bookid as "书号" ,bookname as "书名" , borrowsum as "借出数量" from book;
 
-- 3.查询类型是学生的所有读者的信息
select * from reader where retypeid = (select retypeid from readertype where readername = '学生');
 
-- 4.查询借出时间在2011年3月1日和2011年10月1日之间的图书
select * from bookborrow where borrowtime between '2011-01-01' and '2011-10-01';
 
-- 5.查询借出时间在2011年3月1日之后并且还书时间在2011年10月1日之前的图书
 
# select bookBarcode from bookborrow where borrowTime >= "2011-03-01" and returnTime <= "2011-10-01";
 
 
--6. in关键字查询类型是老师或者学生的读者信息
 
# select * from reader where retypeid in ((select retypeid from readertype where readerName = "学生"),(select retypeid from readertype where readerName = "教师"));
 
 
 
--7. 查询书名包含程序的图书信息
# select * from book where bookName like "%程序%";
 
 
 
-- 8.查询借出数量排名前3的图书
# select * from book order by borrowSum desc limit 3;
 
 
--9. 按图书借出数量从高到低查询, 如果借出数量相同, 再按价格高低排序
# select * from book order by borrowSum desc, bookprice desc;
 
 
--10. 查找图书表中从第2条记录开始的5条记录的名称和价格
# select bookname, bookprice from book limit 1,5;
 
 
-- 11.按照读者类型分组查询借出图书的数量
# select * from bookborrow as b1 left join reader as b2 on b1.readerid = b2.readerid ;
# select count(bookBarcode) as sumbook from bookborrow as b1 left join reader as b2 on b1.readerid = b2.readerid GROUP BY retypeid;
 
 
 
--12. 查询各个出版社的图书的平均价格
# select avg(bookprice) as "平均价格" from book GROUP BY bookpublisher; 
 
 
 
-- 13.查询读者编号是0021的读者借书的信息,包括读者名, 图书号, 借出时间和归还时间
# reader, bookborrow, bookstorage
# 把三张表全部链接在一起
# select readerName, bookid, borrowTime, returnTime from bookborrow as b1 left join reader as b2 on b1.readerid = b2.readerid LEFT JOIN bookstorage as b3 on b1.bookbarcode = b3.bookbarcode where b1.readerId = "0021";
 
--14. 查询所有读者的借书信息, 包括读者名, 图书名, 借出时间和归还时间 
# select readername, bookname, borrowTime, returnTime from bookborrow as b1 left join reader as b2 on b1.readerid = b2.readerid LEFT JOIN bookstorage as b3 on b1.bookbarcode = b3.bookbarcode LEFT JOIN book as b4 on b3.bookid = b4.bookid;
 
--15. 查询借出数量大于书籍编号为TP/3452的借出数量的图书信息
 
select readername, bookname, borrowTime, returnTime from bookborrow as b1 left join reader as b2 on b1.readerid = b2.readerid LEFT JOIN bookstorage as b3 on b1.bookbarcode = b3.bookbarcode LEFT JOIN book as b4 on b3.bookid = b4.bookid where borrowsum > (select borrowsum from book where bookid = TP/3452);
 
-- 16.查询已借了图书的读者信息
# bookborrow, reader, bookstorage
select * from bookstorage as a1 LEFT JOIN bookborrow as a2 on a1.bookbarcode = a2.bookbarcode LEFT JOIN reader as a3 on a2.readerid = a3.readerid where bookstatus = "借出";