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 dwhere 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 con 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代碼練習(十二)