租赁系统营业时间内可租赁时间预约mysql

表设计：

1. rentals表，记录每个租赁项的信息，包括租赁编号、租赁类型、租赁开始时间、租赁结束时间、租赁价格等。
2. reservation表，记录每个预约的信息，包括预约编号、预约租赁编号、预约开始时间、预约结束时间、预约人姓名、联系方式等。

SQL语句：

创建rentals表：

CREATE TABLE rentals (
id INT(11) NOT NULL AUTO_INCREMENT,
type VARCHAR(50) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建reservation表：

CREATE TABLE reservation (
id INT(11) NOT NULL AUTO_INCREMENT,
rental_id INT(11) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
name VARCHAR(50) NOT NULL,
contact VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查询某个时间段内可租赁的项：

SELECT * FROM rentals WHERE start_time >= '2022-03-01 00:00:00' AND end_time <= '2022-03-31 23:59:59';

查询某个时间段内已预约的项：

SELECT * FROM reservation WHERE start_time >= '2022-03-01 00:00:00' AND end_time <= '2022-03-31 23:59:59';

查询某个时间段内还未预约的项：

SELECT * FROM rentals WHERE start_time >= '2022-03-01 00:00:00' AND end_time <= '2022-03-31 23:59:59' AND id NOT IN (SELECT rental_id FROM reservation WHERE start_time >= '2022-03-01 00:00:00' AND end_time <= '2022-03-31 23:59:59');