Javaweb's MySQL code exercise (12)

Structure of library borrowing information bank

Use Day14;drop table student;drop table Book;drop table borrow;--1. Creating Student table information CREATE TABLE student (Stuid CHAR (PRIMARY) KEY, Stuname varchar (TEN), major varchar (50)); CREATE TABLE Book (BID char) PRIMARY KEY, title CHAR (+), author char (20)); CREATE TABLE Borrow (Borrowid char (PRIMARY) KEY, stu_id char (Ten), book_id char (Ten), 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 ', ' Forest ', ' computer '); INSERT into student (Stuid, Stuname,major) VALUES (' 1002 ', ' poplar ', ' computer '); INSERT into student (stuid,stuname,major) VALUES (' 1003 ', ' no ', ' English '); insert into student (stuid,stuname,major) VALUES (' 1004 ', ' Snow of the North ', ' Business Administration '); INSERT into student (stuid,stuname,major) VALUES (' 1005 ', ' May ', ' mathematics '), #图书信息表中插入数据--insert into book (bid,title,author) VALUES (' B001 ', ' If life is just like the beginning ', ' Ann Yi '); INSERT into book ( Bid,title,author) VALUES (' B002 ', ' meet you on the day of school ', ' Clear sky '); insert into book (Bid,title,author) VALUES (' B003 ', ' thanks to the person who tortured you ', ' like Na '); insert into book (BID, Title,author) VALUES (' B004 ', ' I'm not teaching you to cheat ', ' Liu Yan '); INSERT into book (Bid,title,author) VALUES (' B005 ', ' English level Four ', ' Snow '); Insert data from the Library information sheet 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 (' T0' 1002 ', ' B003 ', ' 2007-12-30 ', null);--Query table for information SELECT * FROM Book;select * from Student;select * from borrow;--1) query "computer" specifically Students who borrow books in the "2007-12-15" to "2008-1-8" period 、--student's name, book number, name of the book, loan date; Reference query results such as the Select S.stuid as student number, s.stuname as student name, B.bid As book number, b.title as book name, Borr. T_time as loan date from student s inner JOIN book B inner join borrow Borr on s.stuid=borr.stu_id and b.bid=borr.book_id whe Re s.major= ' computer ' and t_time between ' 2007-12-15 ' and ' 2008-1-8 ';--2) Check the student number, student name and major of all borrowed books; Refer to query results as shown in select distinct S.st UID as student number, S.stuname student name, S.major student Professional from student s inner join borrow Borr on s.stuid=borr.stu_id;

From student s where S.stuid in (select stu_id from Borrow);

--3) The name of the student, the name of the book, the date of the loan and the date of the return of the book borrowed by the author as "Ann Yi"; reference query results as shown in: Select S.stuname as student name, B.title as book name, Borr. T_time as loan date, Borr. B_time from student s inner joins book B inner joins borrow Borr on s.stuid=borr.stu_id and b.bid=borr.book_id where B.autho R= ';--4) inquire about the names of students who have borrowed books but have not returned them, and the number of books that have not yet been paid; reference query results as shown in: Select S.stuname as Student name, COUNT (*) from student S, borrow Borr where S.stu ID=BORR.STU_ID and B_time is a null group by S.stuname;

Establish a database of departments/Students/classes

Create Database School;use School; #系DEPARTMENT (did, department name dname) CREATE TABLE DEPARTMENT (did char (4), Dname char (8), Primary key (did); INSERT into DEPARTMENT values (' 001 ', ' math '); INSERT into DEPARTMENT values (' 002 ', ' computer '); INSERT into DEPARTMENT values (' 003 ', ' chemistry '); INSERT into DEPARTMENT values (' 004 ', ' Chinese '); INSERT into DEPARTMENT values (' 005 ', ' economy ');-Class (CID), professional name major, department name did,  Year of entry EntryDate) CREATE TABLE CLASS (CID char (6), major char (+), did char (8), Entrydat char (6), primary key (CID), constraint Fk_class_did foreign Key (did) references DEPARTMENT (do)); INSERT into class values (' 101 ', ' Software ', ' 002 ', ' 1995 '); INSERT into CLASS values (' 102 ', ' Microelectronics ', ' 002 ', ' 1996 '); INSERT into CLASS values (' 111 ', ' inorganic chemistry ', ' 003 ', ' 1995 '); INSERT into CLASS values (' 112 ', ' macromolecular chemistry ', ' 003 ', ' 1996 '); INSERT into class values (' 121 ', ' statistical math ', ' 001 ', ' 1995 '); INSERT into class values (' 131 ', ' Modern Language ', ' 004 ', ' 1996 '); INSERT into CLASS values (' 141 ', ' International trade ', ' 005 ', ' 1997 '); INSERT into CLASS values (' 142 ', ' International finance ', ' 005 ', ' 1996 ');--Student student (SID, nameSname, age sage, Class 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 ', ' Zhang San ', ' 18 ', ' 101 ‘); INSERT into STUDENT values (' 8102 ', ' money Four ', ' 16 ', ' 121 '); INSERT into STUDENT values (' 8103 ', ' Wang Ling ', ' 17 ', ' 131 '); INSERT into STUDENT values (' 8105 ', ' Li Fei ', ' 19 ', ' 102 '); INSERT into STUDENT values (' 8109 ', ' Zhao Si ', ' 18 ', ' 141 '); INSERT into STUDENT values (' 8110 ', ' Rico ', ' 20 ', ' 142 '); INSERT into STUDENT values (' 8201 ', ' Zhang Fei ', ' 18 ', ' 111 '); INSERT into STUDENT values (' 8302 ', ' Zhou Yu ', ' 16 ', ' 112 '); INSERT into STUDENT values (' 8203 ', ' Wang Liang ', ' 17 ', ' 111 '); INSERT into STUDENT values (' 8305 ', ' Dong Qing ', ' 19 ', ' 102 '); INSERT into STUDENT values (' 8409 ', ' Zhao ', ' 18 ', ' 101 '); INSERT into STUDENT values (' 8510 ', ' Lily ', ' a ', ' 142 '); select * from STUDENT; select * from DEPARTMENT; SELECT * from class;/* (1) Find all students surnamed Li and sort by their age from small to large. */Select Sname name, Sage age from STUDENT WHERE sname like ' Li% ' order by Sage asc;/* (2) lists theThere are more than two professional names in the department. */Select Dname system name from CLASS C INNER join DEPARTMENT dwhere c.did=d.did GROUP by D.did have Count (d.did) >=2;/* (3) lists each Number and name of the department with a class number greater than or equal to 2. */Select D.did department number, d.dname name, COUNT (S.SID) Number of students, C.major professional name 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 have Count (s.sid) >=2;/* (4) The school has added a new physics department, numbered 006.*/--insert INTO DEPARTM ENT values (' 006 ', ' Physics Department '); select * from DEPARTMENT where did= ' 006 ';/* (5) Student Zhang San transfer to Chemistry Department 111, please update the relevant table. */--UPDATE STUDENT set cid= ' 111 ' where Sname= ' Zhang San '; Select S.sname name, S.cid class, C.major professional name from STUDENT s inner join CLAS S c on S.cid=c.cid where S.sname= ' Zhang San ';

Javaweb MySQL code exercise (12)