mysql

--高级查询综合实训

--问题：有一个图书借阅系统

--数据库：BookStore

--三张数据表:

--分类表：categories(cid,cname)

--出版商：publishers(pid,pname,address,postcode,linkman)

--图书表：books(bid,bname,author,price,cid,pid)

--读者表：readers(rid,rname,identityid,gender)

--借阅表：lends(rid,bid,lenddate,returndate)

create database BookStore

go

use bookstore

go

if(exists(select * from sysobjects where name='categories' and type='u'))

drop table categories

go

create table categories

(

cid char(3),

cname char(20)

)

go

insert into categories values('001','计算机')

insert into categories values('002','医学')

insert into categories values('003','经济')

insert into categories values('004','会计')

insert into categories values('005','法律')

if(exists(select * from sysobjects where name='publishers' and type='u'))

drop table publishers

go

create table publishers 

(

pid char(3),

pname char(20),

address char(40),

postcode char(6),

linkmand char(10)

)

go

insert into publishers values('001','人民邮电出版社','北京','100000','赵青山')

insert into publishers values('002','清华大学出版社','北京','201200','张超')

insert into publishers values('003','电子工业出版社','北京','410012','刘玉芬')

insert into publishers values('004','人民出版社','上海','140020','王永生')

insert into publishers values('005','人民教育出版社','北京','302122','陈陆祥')

go

--图书表：books(bid,bname,author,price,cid,pid)

if(exists(select * from sysobjects where name='books' and type='u'))

drop table books

go    

create table books 

(

bid char(5),

bname char(40),

author char(20),

price float,

cid char(3),

pid char(3)

)

go

insert into books values('00001','sql server实训','张玉祥',60.5,'001','001')

insert into books values('00002','新手学sql sever 2000','赵海山',25.3,'001','002')

insert into books values('00003','atuocad 2000i','张学',51,'001','003')

insert into books values('00004','电工基础','蔡玉生',10,'001','001')

insert into books values('00005','审计','周玫',74,'004','005')

go

--读者表：readers(rid,rname,identityid,gender)

if(exists(select * from sysobjects where name='readers' and type='u'))

drop table readers

go

create table readers 

(

rid char(6),

rname char(40),

identityid char(18),

gender char(2) default '男'

)

go

insert into readers values('010001','张三','440255196502051234','男')

insert into readers values('010002','李四','440255199201021289','男')

insert into readers values('010003','赵小光','440255198106063692','男')

insert into readers values('010004','蔡波','440255197405087451','男')

insert into readers values('010005','黄梦迷','440255198506302356','女')

go

--借阅表：lends(rid,bid,lenddate,returndate)

if(exists(select * from sysobjects where name='lends' and type='u'))

drop table lends

go

create table lends 

(

rid char(6),

bid char(5),

lenddate datetime,

returndate datetime

)

go

insert into lends values('010001','00001','2/20/2010','')

insert into lends values('010002','00002','4/20/2010','')

insert into lends values('010003','00001','5/1/2009','')

insert into lends values('010004','00001','3/25/2010','4/24/2010')

insert into lends values('010005','00005','3/26/2010','4/10/2010')

go

----完成如下查询内容：

--***********1.查询books表中全部记录；

select *

from books

--***********2.查询readers表中全部记录；

select *

from readers

--***********3.查询所有读者的借阅情况，只显示rid,bid字段;

select rid,bid

from lends

--4.查询所有读者的借阅情况，显示rid,rname,bid,bname,lenddate,returndate字段;

select readers.rid,rname,books.bid,bname,lenddate,returndate

from lends,readers,books

where readers.rid=lends.rid and lends.bid=books.bid 

--5.统计每位读者的借书本数;

select rid,COUNT(bid)

from lends

group by rid

--***********6.查询含有“sql”的书籍记录,显示书籍所有字段信息；

select  *from

books where bname like '%sql%'

--7.查询借阅书名中含有"sql"字样的书籍的读者的rid,rname,bid,bname；

select readers.rid,rname,books.bid,bname

from readers,books,lends

where readers.rid=lends.rid and books.bid=lends.bid and bname like '%sql%'

--***********8.查询每类书籍的本数，显示cid,cname和本数;

select categories.cid,cname,COUNT(*)

from categories,books

where categories.cid=books.cid

group by categories.cid,cname

--***********9.查询每类书籍的平均价格，显示cid,cname和平均价格;

select categories.cid,cname,AVG(price)

from categories,books

where categories.cid=books.cid

group by categories.cid,cname

--***********10.查询未借书的读者的rid,rname;

select rid,rname from readers

where rid not in(select rid from lends)

--11.查询每类图书被借阅的本数,显示cid,cname和被借阅的本数;

select *

from categories,books,lends

where categories.cid=books.cid and lends.bid=books.bid

group by categories.cid,cname

--***********12.查询未被借阅的图书bid,bname;

select *from books

where bid not in

(

select bid from lends

)

--13.查询借阅图书最多的读者的rid,rname和借阅本数;

--***********14.删除“张三”、“李四”两位读者的借阅记录；

delete from lends 

where  rid in

(select rid from readers where rname in('张三','李四'))

--15.查询每类图书最贵的两本bid,bname,price,cid,cname;

--***********16.查询每本书的被借阅的次数；

select rid,count(*) 

from lends group by rid

--***********17.查询至少借了两本书的读者的rid;

select rid from lends

group by rid

having COUNT(*)>=2

--18.查询所有读者都借阅的书籍rid,rname;

--19.查询没有借阅“人民出版社”图书的读者rid,rname;

select rid,rname from readers where rid not in

(

select rid

from lends,books,publishers

where lends.bid=books.bid and publishers.pid=books.pid and pname='人民出版社'

)

--20.查询“计算机”类图书价格低于50元的图书的全部信息，按价格降序排序；

select *from categories,books

where categories.cid=books.cid and cname='计算机' and price<50

order by price desc

--***********21.删除读者“010001”借阅"00001"图书的记录。

delete from lends where rid='010001' and bid='00001'