图书管理系统： 1、图书表 create table book( id int primary key, name varchar(20), author vachar(20), price decimal(11,2), category_id int, foreign key(caregory_id) references category(id) ) 2、学生表 create table student( id int primary key, name varchar(20) ) 3、图书分类表 create table category( id int primary key, name varchar(20) ); 4、图书借阅信息表(中间表) create table borrow_info( id int primary key, book_id int, student_id int, start_time timestamp null, end_time timestamp null, foreign key (book_id) references book(id), foreign key (student_id) references student(id) )
问题： 1、查询某个分类下的图书信息，“计算机” select b.id,b.name,b.author,b.price,c.`name`,c.id from book b join category c on b.category_id=c.id where c.name='计算机';
2、查询借阅信息 ‘计算机’： select b.id,b.Name book_name,b.author,b.price,c.Name category_name,c.id category_id,bi.start_time,bi.end_time from book b join category c on b.category_id=c.id join borrow_info bi on bi.book_id=b.id where C.Name='计算机';
3、查询'艺术'分类下，价格在30—40元价格的图书借阅信息 select b.id,b.Name book_name,b.author,b.price,c.Name category_name,c.id category_id,bi.start_time,bi.end_time from book b join category c on b.category_id=c.id join borrow_info bi on bi.book_id=b.id where c.name='艺术' and b.price>30 and b.price<40;
4.查询在2019.7.1之后的图书借阅信息 select b.id,b.Name book_name,b.author,b.price,c.Name category_name,c.id category_id,bi.start_time,bi.end_time from book b join category c on b.category_id=c.id join borrow_info bi on bi.book_id=b.id where bi.start_time>'2019-07-01 00:00:00';
5.查询在某个周期内的图书借阅信息 endtime1某书的还书时间 starttime1某书的借阅时间 endtime2规定周期的还书时间 starttime2 规定的周期借阅时间 则应： endtime1>starttime2 starttime1<endtime2 例： select b.id,b.Name book_name,b.author,b.price,c.Name category_name,c.id category_id,bi.start_time,bi.end_time from book b join category c on b.category_id=c.id join borrow_info bi on bi.book_id=b.id where bi.start_time<'2019-09-01 00:00:00' and bi.end_time>'2019-07-01 00:00:00';
6、查询重复图书信息（重复图书：book_name 和author相同） select name,author from book group by name,author having count(0)>1;
