数据库实验
library实验
前言
题
1、从USER表中查询所有用户的姓名和单位。
2、查询所有图书的信息。
3、查询单位为“计算机学院”的用户的全部信息。
4、查询已经预约但还没有借出的书的信息。
5、查询借书超过60天且还没有归还的借阅证号、书号。
6、查询“清华大学出版社”出版的所有中文书的书名、作者、价格。
7、查询在流通总库或者是属于计算机学院资料室的尚未借出的书。
8、查询价格在30元到50元之间的书。
9、查询2019年的借阅历史情况。!
10、查询作者为兰苓、孙海涛、刘明编写的书的书名、作者、出版社和价格。
11、查询书名包含“数据库”的所有书名、作者、出版社和价格。
12、查询书名包含“数据库”的所有书名、作者、出版社及价格*1.5,并将最后一列重命名为price。
13、将上题的结果按书名排序。
14、查询收费情况,结果先按日期降序排序,同一天的按金额排序。
15、查询书价最高的前5种的书名、作者、出版社和定价。
16、 查询买过哪些出版社的书。
17、 查询借阅用户总人数。
18、 查询当前正借有书的用户总人数。
19、 查询办证押金的总金额。
20、 查询用户对书的平均借阅时间:按照用户分组,查询每个用户的平均借阅天数。
21、 查询书的最高价格和最低价格。
22、 查询出版社及从各个出版社购进的书各有多少种。
23、 查询每种图书的书名和其库存量,并对结果按库存量排序。
24、 对上题的查询结果只返回库存量在3本以上的书名和库存量。
25、 查询2019年各种情况收费的总数大于50元的金额和收费原因。
26、 查询当前借书用户的借阅证号、姓名、书号、借书日期。
27、 查询当前借书过期还没归还的用户的借阅证号、姓名、书名、借书日期、过期天数,并按借阅证号排序。
28、 内联接查询在流通总库的数据库类书的信息。
29、 左外联接查询分类为4的用户的姓名、单位、及借阅情况。
30、 使用子查询查询与借阅证号为“S06102”的用户在同一单位的所有用户的借阅证号和姓名。
31、 使用子查询查询所有借书预约成功的用户的姓名和E_mail,以便通知他们。
32、 使用子查询查询类别为“教师”的用户的借书情况。
33、 计算相关子查询查询借阅数量大于3本的用户的借阅证号、姓名、单位。
34、 查询所有曾经借过书号为“A04500049”这本书的所有用户的借阅证号和姓名。(只考虑loanhist里的用户)
35、 查询所有借过书的用户借阅证号。(包括loan和loanhist的用户)
36、 查询现在正借有书的用户但以前没有借过书的用户的借阅证号。
37、 查询当前所有借书信息,并将查询结果导出到’d:\loan.txt’文件中,字段之间用逗号分隔。!
38、 新建一个表loan_statics,包括ISBN和loancount(借阅次数)两个字段,通过查询将每类书的ISBN号和历史借阅次数添加到这个表中。
39、 在USER表中添加一个金额字段amount,并对每个用户的交费总额进行修改。
视图
索引
存储
函数
触发器
前言
写给自己:
本实验,是暑假的作业啊…写着挺痛苦的,主要是连接那一块吧…交叉乘积那一块,查询的时候会出现较多的重复数据,不能有效的通过where语句除去重复的东西…所以,难受
本实验用到的表以及作业的源文件如下
library_work (1).sql
homework.sql
数据库表_28.docx
有些地方参考了别人的博客,巧的是学长还是同一个大学的~
之后忍不住看了看他的博客,发现竟然是同一个专业的
wow~究竟是什么机缘巧合
1-27题
28-39题
写题找问题的时候又看到了一篇极强的博客。。。🥳
对数据库的各种操作
有很多都是渗入了自己的理解,可能…想法和题目不太一样吧,写题的时候总感觉找不到那个点
题
1、从USER表中查询所有用户的姓名和单位。
SELECT lname,unitName FROM users;
2、查询所有图书的信息。
SELECT * FROM bookinfo bi LEFT JOIN books b ON bi.ISBN = b.ISBN;
这里就是感觉所有图书的信息,,怎么说呢,是指啥啊,到底要不要加入连接😅
过关版本:
SELECT * FROM bookinfo;
3、查询单位为“计算机学院”的用户的全部信息。
SELECT * FROM users WHERE unitName = '计算机学院';
4、查询已经预约但还没有借出的书的信息。
SELECT bi.ISBN, bi.bname, bi.author, bi.press, bi.price, bi.`language`, bi.pages, res.rstatus FROM bookinfo bi JOIN reservation res ON bi.ISBN = res.ISBN WHERE rstatus = 'f';
也不知道自己在想什么,感觉写的特麻烦…
但是看到答案…就是没有选对表,导致查询比较麻烦
注意books里面的bstatus这个含义
SELECT * FROM books WHERE bstatus='3';
5、查询借书超过60天且还没有归还的借阅证号、书号。
SELECT l.loanNo, l.bookNo FROM loan l, users u WHERE DATEDIFF(now(),l.borrowDate) > 60 AND l.loanNo = u.loanNo;
好像有点多余,,,为什么要引进users表呢?
select loanNo,bookNo from Loan where datediff(now(), borrowDate)>60;
6、查询“清华大学出版社”出版的所有中文书的书名、作者、价格。
SELECT bi.bname, bi.author, bi.price FROM bookinfo bi WHERE bi.press = '清华大学出版社' AND bi.`language` = '中文' ;
7、查询在流通总库或者是属于计算机学院资料室的尚未借出的书。
SELECT * FROM books b WHERE b.bstatus = '0' AND b.location = '流通总库' OR b.location = '计算机学院资料室'; -- 注意状态和location的顺序,简直amazing!
select * from Books where location = '流通总库' && bstatus = '0';
8、查询价格在30元到50元之间的书。
SELECT * FROM bookinfo bi WHERE bi.price BETWEEN 30 AND 50;
9、查询2019年的借阅历史情况。!
SELECT * FROM loanhist lh WHERE YEAR(2019); -- 用函数year
select * from LoanHist where year(borrowDate)='2019';
对了,这还有个问题也没解决,year(2019)和year(billDate)= 2019,到底是相等的还是?
因为写到后面还有个题跟时间有关系,只能用 year(billDate)= 2019才能查出来,用year直接没有结果…
看着就一顿难受…
10、查询作者为兰苓、孙海涛、刘明编写的书的书名、作者、出版社和价格。
SELECT bi.bname, bi.author, bi.press, bi.price FROM bookinfo bi WHERE bi.author = '兰苓' OR bi.author = '孙海涛' OR bi.author = '刘明'; -- 记住是or 不是and
emm。。没有想到用in会简单很多
select bname,author,press,price from BookInfo where author in('兰苓','孙海涛','刘明');
11、查询书名包含“数据库”的所有书名、作者、出版社和价格。
SELECT bi.bname, bi.author, bi.press, bi.price FROM bookinfo bi WHERE bi.bname REGEXP '数据库'; --order by bname; 有些还有个排序
12、查询书名包含“数据库”的所有书名、作者、出版社及价格*1.5,并将最后一列重命名为price。
SELECT bi.bname, bi.author, bi.press, bi.price * 1.5 AS price FROM bookinfo bi WHERE bi.bname REGEXP '数据库';
13、将上题的结果按书名排序。
SELECT bi.bname, bi.author, bi.press, bi.price * 1.5 AS price FROM bookinfo bi WHERE bi.bname REGEXP '数据库' ORDER BY bi.bname;
14、查询收费情况,结果先按日期降序排序,同一天的按金额排序。
SELECT * FROM money m ORDER BY m.billdate DESC, m.amount ;
– woc,这还能直接在后面写m.amount,我找了各种方法就是出不来…
– 又是case when ,又是decode, wo 累了a…
15、查询书价最高的前5种的书名、作者、出版社和定价。
SELECT bname, author, press, price FROM bookinfo ORDER BY price DESC LIMIT 5;
16、 查询买过哪些出版社的书。
SELECT DISTINCT bi.press FROM bookinfo bi;
– DISTINCT 真好用…
SELECT bookinfo.press,COUNT(*) FROM books,bookinfo WHERE books.ISBN=bookinfo.ISBN GROUP BY press;
写着写着发现有些语句加上group by/order by就能有结果…
17、 查询借阅用户总人数。
SELECT COUNT(*) FROM users ;
这里我嫌弃那个名字太长了,改了一下名…复制的时候记得删了
19、 查询办证押金的总金额。
SELECT SUM(m.amount) AS sum FROM money m WHERE m.reason = '办证押金';
20、 查询用户对书的平均借阅时间:按照用户分组,查询每个用户的平均借阅天数。
SELECT AVG(DATEDIFF(lh.returnDate,lh.borrowDate)) AS avg FROM loanhist lh GROUP BY lh.loanNo;
21、 查询书的最高价格和最低价格。
SELECT MAX(price) AS max, MIN(price) AS min FROM bookinfo;
这个题印象深刻…因为表中的数据最小值是16.5,但是查询的结果出来有很多小数,然后打开设计表里面,发现是float型的,但是最大值75却没有多余的小数,这就很玄学…
22、 查询出版社及从各个出版社购进的书各有多少种。
SELECT press, COUNT(*) AS amount FROM bookinfo GROUP BY press;
select press,count(*) from BookInfo group by press;
23、 查询每种图书的书名和其库存量,并对结果按库存量排序。
SELECT b.ISBN, COUNT(b.ISBN) AS count FROM books b, bookinfo bi WHERE b.ISBN = bi.ISBN GROUP BY b.ISBN ORDER BY count; -- select a.ISBN,b.bname,count(*) -- from Books a,BookInfo b -- where a.ISBN=b.ISBN -- group by ISBN -- order by count(*), ISBN desc ;
24、 对上题的查询结果只返回库存量在3本以上的书名和库存量。
SELECT b.ISBN, COUNT(b.ISBN) AS count FROM books b, bookinfo bi WHERE b.ISBN = bi.ISBN GROUP BY b.ISBN HAVING count > 3 ORDER BY count; -- 排序放在最后
select a.ISBN,b.bname,count(*) from Books a,BookInfo b where a.ISBN=b.ISBN group by a.ISBN having count(*) > 2 order by count(*),a.ISBN desc;
25、 查询2019年各种情况收费的总数大于50元的金额和收费原因。
SELECT l.loanNo, u.lname, l.bookNo, l.borrowDate FROM loan l, users u WHERE l.loanNo = u.loanNo;
– 就这放在26题,欺骗感情…
27、 查询当前借书过期还没归还的用户的借阅证号、姓名、书名、借书日期、过期天数,并按借阅证号排序
SELECT l.bookNo, u.lname, bi.bname, l.borrowDate FROM loan l, users u, bookinfo bi, class_user cu, books b WHERE l.loanNo = u.loanNo && u.classNo = cu.classNo && b.ISBN = bi.ISBN && l.bookNo = b.bookNo && DATEDIFF(NOW(),l.borrowDate) > cu.term ORDER BY l.loanNo;
这题是真的不会,,要连接的太多了…而且不连接的话,很多很多重复的值…
下面是有关复杂查询以及各种连接了…
28、 内联接查询在流通总库的数据库类书的信息。
SELECT bi.ISBN, bi.bname, bi.author, bi.press FROM books b INNER JOIN bookinfo bi ON b.ISBN = bi.ISBN WHERE b.location = '流通总库' AND bi.bname REGEXP '数据库';
29、 左外联接查询分类为4的用户的姓名、单位、及借阅情况。
SELECT u.lname, u.unitName, l.bookNo, l.loanNo, l.borrowDate FROM users u LEFT JOIN loan l ON u.loanNo = l.loanNo WHERE u.classNo = 4;
30、 使用子查询查询与借阅证号为“S06102”的用户在同一单位的所有用户的借阅证号和姓名。
SELECT loanNo,lname FROM users WHERE unitName = (SELECT unitName FROM users WHERE loanNo = 'S06102' );
31、 使用子查询查询所有借书预约成功的用户的姓名和E_mail,以便通知他们。
SELECT lname,email FROM users WHERE loanNo IN (SELECT loanNo FROM reservation WHERE rstatus = 't');
– 注意是in,因为外面的表loanNo有很多,但是符合条件的在后面的select 中
32、 使用子查询查询类别为“教师”的用户的借书情况。
SELECT * FROM loan WHERE loanNo IN (SELECT loanNo FROM users WHERE classNo = 4); -- 注意到教师的类别为4
select bookNo,loanNo,borrowDate from Loan where loanNo in(select loanNo from Users where classNo = (select classNo from Class_User where cname = '教师'));
33、 计算相关子查询查询借阅数量大于3本的用户的借阅证号、姓名、单位。
SELECT u.loanNo,u.lname,u.unitName FROM users u JOIN (SELECT l.loanNo, COUNT(l.loanNo) AS cnt FROM loan l GROUP BY l.loanNo HAVING cnt > 3) b WHERE u.loanNo = b.loanNo; -- 注意count好像必须要有个group by(暂时自己这么以为的..等以后学了,发现不对了再改..)
select u.loanNo,u.lname,u.unitName from Users u INNER JOIN (select loanNo,count(*) from Loan group by loanNo having count(*)>2) d where u.loanNo=d.loanNo;
???为何是2?
34、 查询所有曾经借过书号为“A04500049”这本书的所有用户的借阅证号和姓名。(只考虑loanhist里的用户)
SELECT loanNo,lname FROM users WHERE loanNo IN (SELECT loanNo FROM loanhist WHERE bookNo = 'A04500049');
35、 查询所有借过书的用户借阅证号。(包括loan和loanhist的用户)
SELECT loanNo FROM loan UNION SELECT loanNo FROM loanhist;
36、 查询现在正借有书的用户但以前没有借过书的用户的借阅证号。
SELECT DISTINCT loanNo FROM loan WHERE loanNo NOT IN (SELECT loanNo FROM loanhist); -- 记得加 DISTINCT 除去重复的
37、 查询当前所有借书信息,并将查询结果导出到’d:\loan.txt’文件中,字段之间用逗号分隔。!
SELECT * FROM loan; -- INTO OUTFILE 'd:\loan.txt' -- FIELDS TERMINATED by '\,' -- 执行不了语句...
emm…后面两行我执行不了,直接报错,好像是什么进程不允许,那个值直接为null,然后百度,然后发现要改my.ini配置,当我把整个解决问题的流程已经总结好了之后,正准备去解决问题的时候。。发现自己是Navicat,好像没有这个配置文件,反正我没找到…也不知道因为什么…
38、 新建一个表loan_statics,包括ISBN和loancount(借阅次数)两个字段,通过查询将每类书的ISBN号和历史借阅次数添加到这个表中。
CREATE TABLE loan_statics(ISBN char(13),loancount int); INSERT into loan_statics (ISBN,loancount) SELECT ISBN,count(*) FROM books b,loanhist lh WHERE b.bookNo = lh.bookNo GROUP BY ISBN;
写这一题的时候,好像给我的文件没有处理干净,所以直接已经创建好了那个表,再次写的时候就记得先把他删了再来一遍create
39、 在USER表中添加一个金额字段amount,并对每个用户的交费总额进行修改。
ALTER TABLE users ADD amount DECIMAL; UPDATE users SET amount = 100 WHERE loanNo = 'X10463'; UPDATE users SET amount = 100 WHERE loanNo = 'Y00001'; UPDATE users SET amount = 100 WHERE loanNo = 'Y00003';
alter table Users add amount decimal; update Users set amount=100 where loanNo='S02151'; update Users set amount=110 where loanNo='S02152'; update Users set amount=100 where loanNo='S02153';
视图
/*1 建立“借书”视图V1_loan,要求显示所有用户的借书情况,显示借阅证号、用户姓名、单位、用户类别,用户所借图书的ISBN、书名 、借阅时间。要求同一种用户排列在一起,按用户类别编号升序排列,同类用户按照借书证号的升序排列,同一个用户按借阅时间降序排列*/ create view V1_loan(借阅证号,用户姓名,单位,用户类别,ISBN,书名,借阅时间) as select u.loanNo, u.lname, u.unitName, cu.cname, bi.ISBN, bi.bname,l.borrowDate from Users u, Books b,BookInfo bi, Loan l,Class_User cu where l.loanNo = u.loanNo and b.ISBN = bi.ISBN and b.bookNo = l.bookNo and cu.classNo = u.classNo order by u.classNo, u.loanNo , l.borrowDate desc with check option;
-- 2.建立“未还书”视图V2_NotReturnBooks:要求显示所有外借未归还的图书的ISBN、书号、书名、借阅人名称、单位、借阅时间,按照ISBN升序排列,ISBN相同则按书号升序排列。 create view V2_NotReturnBooks(ISBN,书号,书名,借阅人名称,单位,借阅时间) as select b.ISBN, b.bookNo, bi.bname, u.lname, u.unitName, l.borrowDate from Books b,BookInfo bi, Users u, Loan l where b.ISBN = bi.ISBN and u.loanNo = l.loanNo and b.bookNo = l.bookNo order by b.ISBN, b.bookNo with check option;
--3、建立“可借图书”视图V3_borrBooks:查询所有能够外借的图书的ISBN、书号及书名 create view V3_borrBooks(ISBN,书号,书名) as select b.ISBN, b.bookNo, bi.bname from Books b, BookInfo bi where b.ISBN = bi.ISBN and b.bstatus = 0;
-- 4、建立“未交罚款用户”视图V4_NotPayFine:要求显示所有有超期图书记录,但未交付罚款的用户借书证号、姓名、单位及Email地址,以及超期书号。 create view V4_NotPayFine(借阅证号,姓名,单位,E_mail,超期书号) as select u.loanNo,u.lname,u.unitName,u.email, l.bookNo from Users u, Class_User cu, Loan l where u.classNo = cu.classNo and u.loanNo = l.loanNo and datediff(now(),l.borrowDate) > cu.term;
-- 建立“金额统计”视图V5_MoneyCount:要求显示所有具有罚款记录的用户的姓名、单位、罚款总金额,结果按照罚款总金额数的降序排列 create view V5_MoneyCount(借阅人姓名,单位,罚款总金额) as select u.lname, u.unitName, sum(m.amount) from Users u, Money m where u.loanNo = m.loanNo and m.reason = "过期罚款" order by sum(m.amount) desc;
索引
-- 1. 为BookInfo表的书名字段,,创建一个普通索引。索引名称idx_BookName. create index idx_BookName on BookInfo(bname);
-- 在借阅人员信息表Users表上,基于“单位”和“借阅者姓名”组合属性,创建普通 索引idx_Unit_lname create index idx_Unit_lname on Users(unitName,lname);
存储
use library; drop procedure if exists p_isLoan; delimiter // create procedure p_isLoan(vloanno varchar(16), vbookno char(9), out vflag tinyint) begin declare status_ char(1); declare ctr_no_ tinyint; declare borrow_max tinyint; declare term_end tinyint; declare term_ tinyint; declare all_books_cnt tinyint; set vflag = 0; select bstatus, ctr_no into status_, ctr_no_ form Books where bookNo = vbookno; select cu.ceilingNum, cu.term into borrow_max, term_end from Users u, Class_User cu where u.classNo = cu.classNo and u.loanNo = vloanno; select count(datediff(now(), borrowDate) > term_end), count(*) into term_, all_books_cnt from Loan where loanNo = vloanno; if status_ = '1' || status_ = '2' || status_ = '3' then set vflag = 1; end if; if ctr_no_ = 0 || ctr_no_ = 6 then set vflag = 2; end if; -- 必须要有等于号 if all_books_cnt >= borrow_max then set vflag = 3; elseif term_ > 0 then set vflag = 4; end if; end // delimiter;
call P_IsLoan(vloanno,vbookno,vflag); if vflag = 0 then insert into Loan(bookNo,loanNo,borrowDate) values(vbookno,vloanno,now()); update Books set bstatus = '1' where bookNo = vbookno; end if;
注意,这里自己运行测试之后记得在运行第一关的代码,不然第一个样例通不过,因为他更新了表里面的内容…建议直接提交评测就行
函数
/* 创建一个函数,根据书号和借阅证号计算该用户的图书超期填数,若没有超过期限就返回0,超过了就返回超过的天数 */ use library; drop function if exists Count_v; create function Count_v(vloanno varchar(16), vbookno char(9)) returns tinytint begin declare cnt tinyint; declare term_v tinyint; select cu.term into term_v from Class_User cu, Users u where cu.classNo = u.classNo and u.loanNo = vloanno; select datediff(now(), borrowDate) into cnt from Loan where bookNo = vbookno and loanNo = vloanno; if (cnt > term_v) then return cnt - term_v; else return 0; end if; end
create procedure P_ReturnBook(vloanno varchar(16),vbookno char(9)) BEGIN declare days tinyint; declare borrowdate_ datetime default now(); set days = Count_voverdue(vloanno,vbookno); select borrowDate into borrowdate_ from Loan where loanNo = vloanno and bookNo = vbookno; if days > 0 then insert into Money(loanNo,bookNo,amount,reason,billdate) values(vloanno,vbookno,0.1 * days,'过期罚款',now()); end if; delete from Loan where bookNo = vbookno and loanNo = vloanno; insert into LoanHist(loanNo,bookNo,borrowDate,returnDate) values(vloanno,vbookno,borrowdate_,now()); update Books set bstatus = '0' where bookNo = vbookno; END
触发器
create trigger returnBook after delete on Loan for each row Begin declare isbn char(13); select ISBN into isbn from Books where bookNo = old.bookNo; insert into LoanHist(loanNo, bookNo, borrowDate, returnDate) values(old.loanNo, old.bookNo, old.borrowDate, now()); if exists(select * from Books b, Reservation r where r.ISBN = b.ISBN and b.bookNo = old.bookNo) then update Books set bstatus = '3' where bookNo = old.bookNo; update Reservation set rstatus = 'T' where ISBN = isbn; else update Books set bstatus = '0' where bookNo = old.bookNo; end if; END; //
🤦♂️注意:
1.最后需要//。因为前面有delimiter , 而评测begin*end外的代码中没有给。所以需要自己补上
2.如果第一个测试样例过不了就 把update Reservation set rstatus = ‘T’ where ISBN = isbn;直接改为 update Reservation set rstatus = ‘T’,别问,问就是面向样例编程(还没找到具体问题在哪💤)
create trigger tri after insert on Loan for each row Begin update Books set bstatus = '1' where bookNo = new.bookNo; if exists(select * from Reservation r, Books b where r.ISBN = b.ISBN and b.bookNo = new.bookNo then delete from Reservation where loanNo = new.loanNo; end if; end;
create trigger tri after delete on Loan for each row Begin declare cnt tinyint; declare term_ tinyint; select datediff(now(), old.borrowDate) into cnt; select cu.term into term_ from Class_User cu, Users u where cu.classNo = u.classNo and u.loanNo = old.loanNo; if cnt > term_ then insert into Money(loanNo, bookNo, amount, reason, billdate) values(old.loanNo, old.bookNo, 0.1 * (cnt - term_), '过期罚款', now()); end if; end; //