MySql练习题001

CREATE DATABASE Book_Sys;
-- 新建学生表
CREATE TABLE student(
stuID VARCHAR(10) PRIMARY KEY,
stuName VARCHAR(10),
major VARCHAR(50)
);

-- 新建图书表
CREATE TABLE book(
BID VARCHAR(10) PRIMARY KEY,
title VARCHAR(50),
author VARCHAR(20)
);

-- 新建借书表
CREATE TABLE borrow (
borrowID VARCHAR(10) PRIMARY KEY,
stuID VARCHAR(10),
BID VARCHAR(10),
T_time DATETIME ,
B_time DATETIME,
CONSTRAINT stu_fk1 FOREIGN KEY (stuID) REFERENCES student (stuID),
CONSTRAINT bok_fk2 FOREIGN KEY (BID) REFERENCES book (BID)
);

-- 图书表插入数据
INSERT INTO book VALUES
('B001','人生若只如初见','安茹伊'),
('B002','入学那天遇见你','晴空'),
('B003','感谢折磨你的人','如娜'),
('B004','我不是叫你诈','刘墉'),
('B005','英语四级','白雪');

-- 学生表插入数据
INSERT INTO student VALUES
('1001','林林','计算机'),
('1002','白杨','计算机'),
('1003','虎子','英语'),
('1004','北漂的雪','工商管理'),
('1005','五月','数学');

-- 借阅表插入数据
INSERT INTO borrow VALUES
('T001','1001','B001','2017-12-26 00:00:00',NULL),
('T002','1004','B003','2018-01-05 00:00:00',NULL),
('T003','1005','B001','2017-10-08 00:00:00','2017-12-25 00:00:00'),
('T004','1005','B002','2017-12-16 00:00:00','2018-01-07 00:00:00'),
('T005','1002','B004','2017-12-22 00:00:00',NULL),
('T006','1005','B005','2018-01-06 00:00:00',NULL),
('T007','1002','B001','2017-09-11 00:00:00',NULL),
('T008','1005','B004','2017-12-10 00:00:00',NULL),
('T009','1004','B005','2017-10-16 00:00:00','2017-12-18 00:00:00'),
('T010','1002','B002','2017-09-15 00:00:00','2018-01-05 00:00:00'),
('T011','1004','B003','2017-12-28 00:00:00',NULL),
('T012','1002','B003','2017-12-30 00:00:00',NULL);

-- 第一题
-- 查询“计算机”专业学生在“2017-12-15”至“2018-1-8”时间段
-- 内借书的学生编号、 学生名称、图书编号、图书名称、借出日期；
SELECT 
	s.`stuID` 学生编号,s.`stuName` 学生名称,bk.`BID` 图书编号,bk.`title` 图书名称,bw.`T_time` 借出日期
FROM
	student s,book bk,borrow bw
WHERE
	s.`stuID`=bw.`stuID` AND
	bk.`BID`=bw.`BID` AND
	s.`major`='计算机' AND
	bw.`T_time` BETWEEN '2017-12-15' AND '2018-1-8';


 -- 第二题	
 -- 查询所有借过图书的学生编号、学生名称、专业；
 -- 要求采用两种方式实现
 
 -- 方式1 
 SELECT 
	stu.`stuID` 学生编号,stu.`stuName` 学生名称,stu.`major` 专业
 FROM 
	student stu,borrow bw 
 WHERE 
	stu.`stuID`=bw.`stuID`
 GROUP BY 
	stu.`stuID`;
	
 -- 方式2
 SELECT 
	stu.`stuID` 学生编号,stu.`stuName` 学生名称,stu.`major` 专业
 FROM 
	student stu 
 JOIN  
	borrow bw 
 ON 
	stu.`stuID`=bw.`stuID`
 GROUP BY 
	stu.`stuID`;

 -- 第三题	
 -- 查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；
 
 SELECT
	stu.`stuName` 学生姓名,bk.`title` 图书名称,bw.`T_time` 借出日期,bw.`B_time` 归还日期
 FROM
	student stu,book bk,borrow bw
 WHERE 
	stu.`stuID`=bw.`stuID` AND
	bk.`BID` = bw.`BID`  AND
	bk.`author`= '安意如';
	
 -- 第四题 	
 -- 查询目前借书但未归还图书的学生名称及未还图书数量；
 --  参考查询结果如下图所示：
 
 SELECT
	stu.`stuName` 学生名称,COUNT(*) 未还数量
 FROM
 student stu,borrow bw
 WHERE 
	stu.`stuID`=bw.`stuID` AND
	bw.`B_time` IS NULL
 GROUP BY stu.`stuID`;