1 查询图书表中的书名、作者、出版社
lect name,author,publish from book
2 查询每本书 减去10元之后的价格
lect price-10 from book
3 将读者表中的列更名
4 查询价格小于20元的图书信息
lect * from book where price<20
5 查询所有男读者的信息
lect * from reader where x='男'
6 查询图书价格介于20到30元之间的图书信息
lect * from book where price
谢谢你一直都在
回雪between 20 and 30
7 查询图书价格不介于20到30元之间的图书信息
lect * from book where
price not between 20 and 30
8 查询“清华大学出版社”出版的图书信息
lect * from book where
publish='清华'
9 查询姓“张”的读者的信息
lect * from reader where name like'张%'
10 查询名字中包含“丽”的读者的信息
lect * from reader where
name like'%li%'
11 查询出生日期为空的读者的信息
lect * from reader
where birthdate is null
12 查询读者的信息按性别的升序显示
lect * from reader order by x
13 查询读者的信息按性别升序、出生日期降序显示
lect * from reader
order by x asc,birthdate desc
14 查询最低的图书价格
lect min(price) from book
15 查询图书的总数目
lect count(*) from book
16 查询最晚的出生日期
lect max(birthdate) from reader
17 查询最早的借阅日期
lect min(borrowdate) from borrow
北京人遗址18 查询图书的平均价格
lect avg(price) from book
19 统计每个出版社出版图书的最高价格
19.2统计每个人所借图书的最晚日期
20 统计每个人所借图书的最早日期
20.1统计每本书被借阅的次数
20.2统计每个读者借书的次数
21 统计出版图书最低价格高于
20元的出版社
22 统计被借阅次数多于2次的图书编号
23 查询出版图书多余3本的出版社名称
和出版图书数目
24 查询王旭的借书日期
lect borrowdate from borrow where
reader_id in (lect reader_id from reader where name='王旭')
25 查询计算机文化基础的借阅日期
lect borrowdate from borrow where
book_id in(lect book_id from book where name='计算机文化基础')
26 查询与c语言在同一出版社的书名
lect name from book where publish in
(lect publish from book where name='cyuyan')
27 查询清华大学出版社出版图书的借阅日期
lect borrodate from borrow where
book_id in(lect book_id from book
where publish='清华大学出版社')
28 查询王旭所借图书的书名
lect name from book where book_id in(lect book_id from borrow where
reader_id in(lect reader_id from reader where name='王旭'))
29 查询借阅计算机文化基础的读者姓名
lect name from reader where reader_id in(lect reader_id from borrow where book_id in(lect book_id from book where name='计算机文化基础'))
30：查询借书价格在20-40之间的读者的姓名
lect name from reader where reader_id in
(lect
reader_id from borrow where book_id in
(lect book_id from book where price
between 20 and 40))马约翰
31：查询’胡晓丽’借阅‘计算机文化基础’的日期
lect borrodate from borrow where
reader_id in(lect reader_id from reader where name='胡晓力')
and
book_id in(lect book_id from book where name='计算机文化基础')
30 查询不是最早借阅的图书编号
lect book_id from borrow
where reader_id in
(lect reader_id from reader
where name='王旭')
【例2.61】查询'王旭'所借的图书的名称
1 在reader表查王旭的编号
2 在borrow表通过王旭的编号查他借书的
编号
3 在book表中通过借书的编号查询书名
lect name from book
where book_id in
(lect book_id from borrow
where reader_id in
(lect reader_id from reader
where name='王旭'))
【例2.63】被借出的图书的信息
lect * from book where exists(lect * from borrow where borrow.book_id =book.book_id)
1。查询平均书价最高的图书的出版社信息
lect publish from book
group by publish
having avg(price)>=all(lect avg(price) from book group by publish)
2。
3。借阅图书最多的读者的信息
lect * from reader where reader_id in (lect reader_id from borrow group by reader_id having count(*)>=all(lect count(*) from borrow group by reader_id))
怎么降温4 借阅数据库原理最早的读者的信息
lect * from reader where reader_id in(lect reader_id from borrow where borrowdate=
(lect min(borrowdate) from borrow where book_id in(lect book_id from book where name='数据库原理'))
and book_id in(lect book_id from book where name='数据库原理')
查询王旭所借的图书的编号
lect book_id from reader join borrow ader_ader_id
where name='王旭'
查询2005年1月20日所借出的图书的信息
lect * from book join borrow
on book.book_id=borrow.book_id
where borrowdate='2005-1-20'
练习：查询‘2005-6-7’借阅图书的名称
lect name from book join borrow
on book.book_id=borrow.book_id
where borrowdate='2005-6-7'
练习：查询胡晓丽的借书的数量
lect count(*) from reader join borrow ader_ader_id
where name='huxiaoli'
查询’王旭’所借图书的书名
lect book.name from book join borrow on borrow.book_id=book.book_id join reader ader_ader_id
where name='王旭'
多表连接
24 查询王旭的借书日期
lect borrowdate from borrow,reader
ader_ader_id
and name='王旭'
25 查询计算机文化基础的借阅日期
lect borrowdate from borrow join book on borrow.book_id=book.book_id where name='计算机文化挤出'
26 查询与c语言在同一出版社的书名
lect a.name from book a join book b
on a.publish=b.publish
where a.name='c语言' and
a.name=!b.name
27 查询清华大学出版社出版图书的借阅日期
lect borrowdate from borrow join book on borrow.b
ook_id=book.book_id
where publish='清华大学出版社'
28 查询王旭所借图书的书名
lect book.name from book join borrow on book.book_id=borrow.book_id
join reader ader_ader_id
where reader.name='王旭'
29 查询借阅计算机文化基础的读者姓名
lect reader.name from reader join borrow ader_ader_id join book on book.book_id=borrow.book_id where
book.name='计算机文化基础'
晚会直播30查询借书价格在20-40之间的读者的姓名
lect reader.name from reader join borrow ader_ader_id
join book on book.book_id=borrow.book_id
where price between 20 and 40
31查询’胡晓丽’借阅‘计算机文化基础’的日期
lect borrowdate from borrow join book on borrow.book_id=book.book_id
join reader ader_ader_id
where book.name='计算机' and reader.name='胡晓力'
32借阅了多余两本书的读者的信息以及图书信息
lect book.*,reader.* from reader join borrow ader_ader_id
join book on borrow.book_id=book.book_id
ader_id in
(lect reader_id from borrow
group by reader_id
having count(*)>2)
课后习题
29 lect a.sname,a.sdept from student a join student b on a.sdept=b.sdept where a.name='刘臣'
水资源论文and a.name!=b.name
1。借了同一本书的读者的姓名
lect name from reader
where reader_id in
(lect reader_id from borrow a join borrow b on a.book_id=b.book_id ader_id!=b.reader_id)
2。借了同一本书的读者的姓名以及书名
lect book.name,reader.name from
book,reader,borrow
where book.book_id=borrow.book_id ader_ader_id and
(lect reader_id from borrow a join borrow b on a.book_id=b.book_id ader_id!=b.reader_id)
lect * from reader left join borrow
ader_ader_id
Reader表和Borrow进行左外连接，显示读者编号，读者姓名，图书编号
Borrow表和book表进行右外连接，显示读者编号，图书编号，图书名称
lect reader_id,book.book_id,name from borrow right join on borrow.book_id=book.book_id
lect * from reader full join borrow ader_ader_id full join book on book.book_id=borrow.book_id
查询读者嘚借书数量，并显示读者姓名
lect name,count(book_id) from reader left join borrow ader_ader_id
查询图书的借阅次数，并显是图书编号
lect book.book_id ,count(reader_id)
from book left join borrow on
borrow.book_id=book.book_id
励志早安心语正能量group by book.book_id
