mysql实验二:图书管理系统之SQL查询语言案例

声明:本博文如存在问题，欢迎各位dalao指正！！！

目的与任务：

要求熟练掌握SQL查询语言的基本查询、条件查询、多表查询、统计查询。

这里将所有sql查询语句都放在一起了，直接全部复制到navicat中既可，另外实验一中只有表格没有数据，下面将数据附上。

链接：https://pan.baidu.com/s/14YjOhxUud8-dje3lk2bXKg 
提取码：tsvt

-- 1、从USER表中查询所有用户的姓名和单位。
		SELECT lname,unitName
			FROM Users;

-- 2、查询所有图书的信息。
		SELECT *
			FROM bookinfo;

-- 3、查询单位为“计算机学院”的用户的全部信息。
		select * 
			from Users 
			where unitName = '计算机学院';

-- 4、查询已经预约但还没有借出的书的信息。
		select * 
			from Books 
			where bstatus='3';

-- 5、查询借书超过60天且还没有归还的借阅证号、书号。
		select loanNo,bookNo
			from Loan
			where datediff(now(), borrowDate)>60;

-- 6、查询“清华大学出版社”出版的所有中文书的书名、作者、价格。
		select bname,author,price 
			from BookInfo 
			where press = '清华大学出版社' && language ='中文';

-- 7、查询在流通总库或者是属于计算机学院资料室的尚未借出的书。
		select *
			from Books
			where location = '流通总库' && bstatus = '0';

-- 8、查询价格在30元到50元之间的书。
		select *
			from BookInfo
			where price between 30 and 50;

-- 9、查询2019年的借阅历史情况。
		select *
			from LoanHist
			where year(borrowDate)='2019';

-- 10、查询作者为兰苓、孙海涛、刘明编写的书的书名、作者、出版社和价格。
		select bname,author,press,price
			from BookInfo
			where author in('兰苓','孙海涛','刘明');

-- 11、查询书名包含“数据库”的所有书名、作者、出版社和价格。
		select bname,author,press,price*1.5 as price
			from BookInfo
			where bname like '%数据库%'
			order by bname;

-- 12、查询书名包含“数据库”的所有书名、作者、出版社及价格*1.5 ,并将最后一列重命名为price。
		SELECT bname,author,press,price*1.5 AS price
			FROM bookinfo
			WHERE bname LIKE '%数据库%';

-- 13、将上题的结果按书名排序。
		SELECT bname,author,press,price*1.5 AS price
			FROM bookinfo
			WHERE bname LIKE '%数据库%'
			ORDER BY bname;

-- 14、查询收费情况，结果先按日期降序排序，同一天的按金额排序。
		SELECT *
			FROM money
			ORDER BY billdate DESC,amount;

-- 15、查询书价最高的前5种的书名、作者、出版社和定价。
		select bname,author,press,price
			from BookInfo
			order by price desc limit 5;

-- 16、查询买过哪些出版社的书。
		SELECT bookinfo.press,COUNT(*)
			FROM books,bookinfo
			WHERE books.ISBN=bookinfo.ISBN
			GROUP BY press;

-- 17、查询借阅用户总人数。
		SELECT COUNT(*)
			FROM users;
					
-- 18、查询当前正借有书的用户总人数。
		select count(distinct loanNo)
			from Loan;

-- 19、查询办证押金的总金额。
		select sum(amount)
			from Money
			where reason = '办证押金';

-- 20、查询用户对书的平均借阅时间：按照用户分组，查询每个用户的平均借阅天数。
		select AVG(DATEDIFF(returnDate,borrowDate)) 平均借阅时间
			from LoanHist
			GROUP by loanNo;

-- 21、查询书的最高价格和最低价格。
		select max(price),min(price)
			from BookInfo;

-- 22、查询出版社及从各个出版社购进的书各有多少种。
		select press,count(*)
			from BookInfo
			group by press;

-- 23、查询每种图书的书名和其库存量，并对结果按库存量排序。
		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 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 reason,sum(amount)
			from Money
			where year(billdate)= 2019
			group by reason
			having sum(amount) > 50;

-- 26、查询当前借书用户的借阅证号、姓名、书号、借书日期。
		select a.loanno,b.lname,a.bookno,a.borrowdate
			from Loan a,Users b 
			where a.loanNo = b.loanNo;


-- 27、查询当前借书过期还没归还的用户的借阅证号、姓名、书名、借书日期、过期天数，并按借阅证号排序。
   select a.loanno,b.lname,c.bname,a.borrowdate
		from Loan a,Users b,BookInfo c,Class_User d,Books e
		where a.loanNo = b.LoanNo && a.bookNo = e.bookNo && e.ISBN=c.ISBN && b.classNo=d.classNo && datediff(now(),a.borrowdate)> d.term
		order by a.loanNo;