JavaWeb之Mysql代码练习(十二)
图书馆借书信息库结构
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=‘张三‘;
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。