图书馆借书信息库结构

use day14;DROP TABLE student;DROP TABLE book;DROP TABLE borrow;-- 1. 创建学生表信息CREATE TABLE student(	stuID CHAR(10) PRIMARY KEY,    stuName VARCHAR(10),    major VARCHAR(50));CREATE TABLE book(	BID CHAR(10) PRIMARY KEY,    title CHAR(50),    author CHAR(20));CREATE TABLE borrow(	borrowID CHAR(10) PRIMARY KEY,    stu_ID CHAR(10),    book_ID CHAR(10),    T_time DATETIME,    B_time DATETIME,    CONSTRAINT FK_stu_ID FOREIGN KEY(stu_ID) REFERENCES student(stuID),    CONSTRAINT FK_book_ID FOREIGN KEY(book_ID) REFERENCES book(BID));    SHOW TABLES;#学生信息表中插入数据INSERT INTO student(stuID,stuName,major)VALUES(‘1001‘,‘林林‘,‘计算机‘);INSERT INTO student(stuID,stuName,major)VALUES(‘1002‘,‘白杨‘,‘计算机‘);INSERT INTO student(stuID,stuName,major)VALUES(‘1003‘,‘虎子‘,‘英语‘);INSERT INTO student(stuID,stuName,major)VALUES(‘1004‘,‘北漂的雪‘,‘工商管理‘);INSERT INTO student(stuID,stuName,major)VALUES(‘1005‘,‘五月‘,‘数学‘);#图书信息表中插入数据--INSERT INTO book(BID,title,author)VALUES(‘B001‘,‘人生若只如初见‘,‘安意如‘);INSERT INTO book(BID,title,author)VALUES(‘B002‘,‘入学那天遇见你‘,‘晴空‘);INSERT INTO book(BID,title,author)VALUES(‘B003‘,‘感谢折磨你的人‘,‘如娜‘);INSERT INTO book(BID,title,author)VALUES(‘B004‘,‘我不是教你诈‘,‘刘庸‘);INSERT INTO book(BID,title,author)VALUES(‘B005‘,‘英语四级‘,‘白雪‘);#借书信息表中插入数据INSERT INTO borrow VALUES(‘T001‘,‘1001‘,‘B001‘,‘2007-12-26‘,null);INSERT INTO borrow VALUES(‘T002‘,‘1004‘,‘B003‘,‘2008-1-5‘,null);INSERT INTO borrow VALUES(‘T003‘,‘1005‘,‘B001‘,‘2007-10-8‘,‘2007-12-25‘);INSERT INTO borrow VALUES(‘T004‘,‘1005‘,‘B002‘,‘2007-12-16‘,‘2008-1-7‘);INSERT INTO borrow VALUES(‘T005‘,‘1002‘,‘B004‘,‘2007-12-22‘,null);INSERT INTO borrow VALUES(‘T006‘,‘1005‘,‘B005‘,‘2008-1-6‘,null);INSERT INTO borrow VALUES(‘T007‘,‘1002‘,‘B001‘,‘2007-9-11‘,null);INSERT INTO borrow VALUES(‘T008‘,‘1005‘,‘B004‘,‘2007-12-10‘,null);INSERT INTO borrow VALUES(‘T009‘,‘1004‘,‘B005‘,‘2007-10-16‘,‘2007-12-18‘);INSERT INTO borrow VALUES(‘T010‘,‘1002‘,‘B002‘,‘2007-9-15‘,‘2008-1-5‘);INSERT INTO borrow VALUES(‘T011‘,‘1004‘,‘B003‘,‘2007-12-28‘,null);INSERT INTO borrow VALUES(‘T012‘,‘1002‘,‘B003‘,‘2007-12-30‘,null);-- 查询表中信息select * from book;select * from student;select * from borrow;-- 1)	查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、-- 学生名称、图书编号、图书名称、借出日期；参考查询结果如SELECT s.stuID as 学生编号, s.stuName as 学生名称, b.BID as 图书编号, b.title as 图书名称, borr.T_time as 借出日期  FROM student s inner join book b inner join borrow borr  on s.stuID=borr.stu_ID and b.BID=borr.book_ID where s.major=‘计算机‘  and T_time between ‘2007-12-15‘ and ‘2008-1-8‘;-- 2)	查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示select distinct s.stuID as 学生编号, s.stuName 学生名称, s.major 学生专业  from student s inner join borrow borr on s.stuID=borr.stu_ID;

select s.stuID as 学生编号, s.stuName 学生名称, s.major 学生专业 

 from student s where s.stuID in (select stu_ID from borrow);

-- 3)	查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期；参考查询结果如下图所示：select s.stuName as 学生姓名, b.title as 图书名称, borr.T_time as 借出日期, borr.B_time from student s inner join book b inner join borrow borr on s.stuID=borr.stu_ID and b.BID=borr.book_ID where b.author=‘安意如‘;-- 4)	查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所示：select s.stuName as 学生姓名, count(*) from student s, borrow borr where s.stuID=borr.stu_ID and B_time is null group by s.stuName;

 

建立关于系/学生/班级的数据库

create database school;use school;#系DEPARTMENT（系号did，系名dname）create table DEPARTMENT( did char(4), dname char(8), primary key(did));insert into DEPARTMENT values(‘001‘,‘数学‘); insert into DEPARTMENT values(‘002‘,‘计算机‘); insert into DEPARTMENT values(‘003‘,‘化学‘); insert into DEPARTMENT values(‘004‘,‘中文‘);insert into DEPARTMENT values(‘005‘,‘经济‘);-- 班CLASS（班号cid，专业名major，系名did，入学年份entrydate）create table CLASS( cid char(6), major char(12), did char(8), entrydat char(6), primary key(cid), constraint FK_class_did foreign key(did) references DEPARTMENT(did));insert into CLASS values(‘101‘,‘软件‘,‘002‘,‘1995‘); insert into CLASS values(‘102‘,‘微电子‘,‘002‘,‘1996‘); insert into CLASS values(‘111‘,‘无机化学‘,‘003‘,‘1995‘); insert into CLASS values(‘112‘,‘高分子化学‘,‘003‘,‘1996‘); insert into CLASS values(‘121‘,‘统计数学‘,‘001‘,‘1995‘);insert into CLASS values(‘131‘,‘现代语言‘,‘004‘,‘1996‘); insert into CLASS values(‘141‘,‘国际贸易‘,‘005‘,‘1997‘); insert into CLASS values(‘142‘,‘国际金融‘,‘005‘,‘1996‘);-- 学生STUDENT（学号 sid，姓名sname，年龄sage，班号cid）create table STUDENT( sid char(6), sname char(6) not null, sage char(2), cid char(6), primary key(sid), constraint FK_student_classid foreign key(cid)references CLASS(cid));insert into STUDENT values(‘8101‘,‘张三‘,‘18‘,‘101‘); insert into STUDENT values(‘8102‘,‘钱四‘,‘16‘,‘121‘); insert into STUDENT values(‘8103‘,‘王玲‘,‘17‘,‘131‘); insert into STUDENT values(‘8105‘,‘李飞‘,‘19‘,‘102‘); insert into STUDENT values(‘8109‘,‘赵四‘,‘18‘,‘141‘); insert into STUDENT values(‘8110‘,‘李可‘,‘20‘,‘142‘); insert into STUDENT values(‘8201‘,‘张飞‘,‘18‘,‘111‘); insert into STUDENT values(‘8302‘,‘周瑜‘,‘16‘,‘112‘); insert into STUDENT values(‘8203‘,‘王亮‘,‘17‘,‘111‘); insert into STUDENT values(‘8305‘,‘董庆‘,‘19‘,‘102‘); insert into STUDENT values(‘8409‘,‘赵龙‘,‘18‘,‘101‘); insert into STUDENT values(‘8510‘,‘李丽‘,‘20‘,‘142‘);select * from STUDENT ;select * from DEPARTMENT; select * from CLASS;/*（1）找出所有姓李的学生，并按其年龄由小到大排序。*/ select sname 姓名, sage 年龄 from STUDENT WHERE sname like ‘李%‘ order by sage asc;/*（2）列出所有开设超过两个专业的系的名字。*/ select dname 系名 from CLASS c inner join DEPARTMENT d	where c.did=d.did group by d.did having count(d.did)>=2;/*（3）列出每个班学生人数大于等于2的系的编号和名称。*/ select d.did 系编号, d.dname 系名称, count(s.sid) 学生人数, c.major 专业名 from DEPARTMENT d inner join STUDENT s inner join CLASS c	on s.cid=c.cid and c.did=d.did group by d.did having count(s.sid)>=2;/*（4）学校又新增加了一个物理系，编号为006.*/ -- insert into DEPARTMENT values(‘006‘,‘物理系‘);select * from DEPARTMENT where did=‘006‘;/*（5）学生张三转到化学系111班，请更新相关的表。*/ -- UPDATE STUDENT set cid=‘111‘ where sname=‘张三‘;select s.sname 姓名, s.cid 班号, c.major 专业名	from STUDENT s inner join CLASS c on s.cid=c.cid where s.sname=‘张三‘;

JavaWeb之Mysql代码练习(十二)