MySQL的增删改查3（图书管理系统）

我们用四张表来表示一个图书管理系统
图书管理系统：
1.图书表
2.学生表
3.图书分类表
4.图书借阅信息表

图书管理系统
==============================
--1.图书表
create table book(
	id int primary key,
	name varchar(20),
	author varchar(20),
	price decimal(11,2),
	catagory_id int,
	foreign key(catagory_id) references 
	catagory(id)
);
insert into book values
(1,'深入理解Java虚拟机', '周志明',57.90,3),
(2,'西游记','吴承恩',30.68,5),
(3,'儒林外史','吴敬梓',18.8,5),
(4,'史记','司马光',524.00,1),
(5,'诗经','孔子',22.2,2),
(6,'高等数学','李晓',10.2,4)；
==============================
--2.学生表
create table student(
	id int primary key,
	name varchar(20)
);
insert into student values
(1,'小李'),(2,'小豪'),(3,'小许'),(4,'小宜');
==============================
--3.图书分类表                                  分类表1，图书表多
create table catagory(
	id int primary key,
	name varchar(20)
);


insert into catagory values 
(1,'历史'),(2,'艺术'),(3,'计算机'),(4,'数学'),(5,'小说');
==============================
--4.图书借阅信息表                          是图书表和学生表的关系
create table borrow_info(
	id int primary key,
	start_time timestamp null,
	end_time timestamp null,
	book_id int,
	student_id int,
	foreign key (book_id) references
	book(id),
	foreign key(student_id) references
	student(id)
);

insert into borrow_info values
(1,'2018-11-07','2018-12-07',1,1),
(2,'2018-1-07','2018-2-07',5,1),
(3,'2019-11-08','2019-12-08',2,2),
(4,'2018-3-07','2018-4-07',3,3),
(5,'2019-2-25','2018-3-27',4,3),
(6,'2019-4-12','2019-4-30',6,4);

查询某个分类下的图书借阅信息

例如我们这里查询小说分类的图书
这里就是关联查询
这里我们需要关联图书表（book）和分类表（catagory）

↓↓内连接

mysql> select * from book b join catagory c 
		on  b.catagory_id = c.id where c.name = '小说';
+----+--------------+-----------+-------+-------------+----+--------+
| id | name         | author    | price | catagory_id | id | name   |
+----+--------------+-----------+-------+-------------+----+--------+
|  2 | 西游记       | 吴承恩    | 30.68 |           5 |  5 | 小说   |
|  3 | 儒林外史     | 吴敬梓    | 18.80 |           5 |  5 | 小说   |
+----+--------------+-----------+-------+-------------+----+--------+

mysql> select b.id,b.name,b.author,b.price,c.name,c.id
    ->  from book b join catagory c on  b.catagory_id = c.id where c.name = '小说';
+----+--------------+-----------+-------+--------+----+
| id | name         | author    | price | name   | id |
+----+--------------+-----------+-------+--------+----+
|  2 | 西游记       | 吴承恩    | 30.68 | 小说   |  5 |
|  3 | 儒林外史     | 吴敬梓    | 18.80 | 小说   |  5 |
+----+--------------+-----------+-------+--------+----+

↑↑内连接

↓↓查询借阅信息：

mysql> select b.id,b.name,b.author,b.price,c.name,c.id,bi.start_time,bi.end_time
    ->  from book b
    -> join catagory c on  b.catagory_id = c.id
    -> join borrow_info bi on bi.book_id = b.id
    -> where c.name = '小说';
+----+--------------+-----------+-------+--------+----+---------------------+---------------------+
| id | name         | author    | price | name   | id | start_time          | end_time            |
+----+--------------+-----------+-------+--------+----+---------------------+---------------------+
|  2 | 西游记       | 吴承恩    | 30.68 | 小说   |  5 | 2019-11-08 00:00:00 | 2019-12-08 00:00:00 |
|  3 | 儒林外史     | 吴敬梓    | 18.80 | 小说   |  5 | 2018-03-07 00:00:00 | 2018-04-07 00:00:00 |
+----+--------------+-----------+-------+--------+----+---------------------+---------------------+

这里解释一下，查询某个分类的借阅信息，b（book）和分类表（catagory）是多对一，需要先内连接方便找出分类
紧接着内连接到bi（book_info),找到借阅信息
↑↑

↓↓
查询“小说“分类下，价格在30-40元之间价格的图书借阅信息。

mysql> select b.id,b.name book_name,
b.author,b.price,c.name catagory_name,c.id,bi.start_time,bi.end_time
    ->  from book b
    -> join catagory c on  b.catagory_id = c.id
    -> join borrow_info bi on bi.book_id = b.id
    -> where c.name = '小说' and b.price >= 30 and b.price<= 40;

↑↑

👇👇👇
查询在某个时间之后的图书借阅信息。

select b.id,b.name book_name,b.author,b.price,c.name catagory_name,c.id,bi.start_time,bi.end_time
    ->  from book b
    -> join catagory c on  b.catagory_id = c.id
    -> join borrow_info bi on bi.book_id = b.id
    -> where
    -> bi.start_time >'2018-9-1';
+----+---------------------------+-----------+--------+---------------+----+---------------------+---------------------+
| id | book_name                 | author    | price  | catagory_name | id | start_time          | end_time            |
+----+---------------------------+-----------+--------+---------------+----+---------------------+---------------------+
|  1 | 深入理解Java虚拟机        | 周志明    |  57.90 | 计算机        |  3 | 2018-11-07 00:00:00 | 2018-12-07 00:00:00 |
|  2 | 西游记                    | 吴承恩    |  30.68 | 小说          |  5 | 2019-11-08 00:00:00 | 2019-12-08 00:00:00 |
|  4 | 史记                      | 司马光    | 524.00 | 历史          |  1 | 2019-02-25 00:00:00 | 2018-03-27 00:00:00 |
|  6 | 高等数学                  | 李晓      |  10.20 | 数学          |  4 | 2019-04-12 00:00:00 | 2019-04-30 00:00:00 |
+----+---------------------------+-----------+--------+---------------+----+---------------------+---------------------+

👆👆👆

👇👇👇
在某个范围内查找图书借阅信息
例如我们要查询2018-9-1到2019-3-1的图书借阅信息，我们应该这么写

mysql> select b.id,b.name book_name,b.author,b.price,c.name catagory_name,c.id,bi.start_time,bi.end_time
    ->  from book b
    -> join catagory c on  b.catagory_id = c.id
    -> join borrow_info bi on bi.book_id = b.id
    -> where
    -> bi.start_time <'2019-3-1' and
    -> bi.end_time > '2018-9-1';
+----+---------------------------+-----------+-------+---------------+----+---------------------+---------------------+
| id | book_name                 | author    | price | catagory_name | id | start_time          | end_time            |
+----+---------------------------+-----------+-------+---------------+----+---------------------+---------------------+
|  1 | 深入理解Java虚拟机        | 周志明    | 57.90 | 计算机        |  3 | 2018-11-07 00:00:00 | 2018-12-07 00:00:00 |
+----+---------------------------+-----------+-------+---------------+----+---------------------+---------------------+

👆👆👆