图书管理系统
图书表
create table book(
id int primary key,
name varchar(20),
author varchar(20),
price decimal(11,2),
category_id int,
foreign key (category_id) references category(id)
);
学生表
create table student(
id int primary key,
name varchar(20)
);
图书分类表
create table category(
id int primary key,
name varchar(20)
);
图书借阅信息表
create table borrow_info(
id int primary key,
start_time timestamp null,
end_time timestamp null,
book_id int,
foreign key(book_id) references book(id),
student_id int,
foreign key(student_id) references student(id)
);
//加null 是允许该项为空(约束类型)
查询某个分类下的图书借阅记录
内连接
select * from book b join category c on b.category_id=c.id where c.name='计算机';
查询所有书籍,不管他有没有分类;
select * from book b left join category c on b.category_id=c.id where c.name='计算机';
关联三张表 查阅借阅信息
select * 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='计算机';
select b.name book_name, b.id book_id ,c.id c_id ,
c.name c_name,bi.start_time,bi.end_time bi_id 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='计