社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
设有一个图书馆数据库,其中包括三个表,即图书表、读者表和借阅表。三个表的结构和数据如下:
图书表book(书号bno,书名bname,作者author,出版社publish,单价price)
读者表reader(读者号rno,姓名rname,性别sex,电话tel,部门department,地址address)
借阅表borrow(读者号rno,书号bno,借出日期bdate,归还日期rdate)
完成下列问题的SQL语句,并截图。
select *from reader;
select rno,rname,department from reader where rname like '张%';
select rname as '姓名',sex as '性别', department as '部门' from reader where department='信息工程学院';
select sex as '性别',count(sex) from reader group by sex;
select reader.rname,reader.department from reader,book,borrow where reader.rno=borrow.rno and book.bno=borrow.bno and book.bname like '数据库原理' ;
select * from book where price not between 20 and 30;
select distinct reader.rno 读者号,rname 姓名,department 部门
from reader,borrow where reader.rno=borrow.rno and
bno in(Select bno from borrow
where rno in(select rno from reader
where rname="谢爽")) and rname !="谢爽";
select rno,rname from reader where rname like '_志_';
select rno,rname from reader where rname regexp '.志.';
select * from book where publish not in ('机械工业出版社','科学出版社');
select rno,rname from reader where rname like '王%' or rname like'张%' or rname like'李%';
select rno as '读者号',count(bno) as '总本数'
from borrow
group by rno
having count(bno)>2
order by count(bno) desc;
select reader.rno 读者号,rname 姓名,bname 图书名,bdate 借出日期,rdate 归还日期
from reader,book,borrow
where book.bno=borrow.bno and reader.rno=borrow.rno
order by bdate;
select reader.rno 读者号,rname 姓名,bname 书名,publish 出版社,bdate 借出日期,rdate 归还日期
from borrow join book on book.bno=borrow.bno
join reader on borrow.rno=reader.rno
where publish='清华大学出版社' and bname like '%数据%';
select bno 书号,bname 书名,publish 出版社
from book
where price<(select avg(price) from book);
select * from book
where bno in (select bno
from borrow
where bdate is not null and rdate is not null);
select*from book
where bno not in (select bno from borrow );
select*from book
where bno in (select bno from borrow where rdate is null );
select department "借书最多部门",count(borrow.rno) "借阅图书总数"
from reader,borrow
where reader.rno=borrow.rno
group by department
order by count(department) desc limit 2;
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!