文章目录
1.group by语句
select COUNT(*),publish from book group by publish
select COUNT(book_id),reader_ID from Borrow group by reader_ID
select publish from book group by publish having AVG(price)>30
2.嵌套子查询
select * from Reader where birthdate = (select birthdate from Reader where name='王旭')
SELECT book_ID FROM Borrow WHERE reader_ID IN (
SELECT reader_ID FROM Reader WHERE
name='王旭')
select name from book where book_id in (select book_id from Borrow where reader_ID =(select reader_ID from Reader where name = '王旭'))
select * from book where price >some(select price from book)--价钱大于某一个肯定不是最低的
第二种方法
select * from book where price >(select MIN(price) from book )
SELECT * FROM Book WHERE NOT( price>SOME(SELECT price FROM Book))
SELECT * FROM Book WHERE price >=all(select price from book)
第二种方法
select * from book where price >=(select max(price) from book )