数据库MySQL代码

-- 创建车辆表
CREATE TABLE vehicles (
veh_reg_no VARCHAR(8) NOT NULL, -- 车辆登记号码
category ENUM('car', 'truck') NOT NULL DEFAULT 'car', -- 车辆类别
brand VARCHAR(30) NOT NULL DEFAULT '', -- 车辆品牌
description VARCHAR(256) NOT NULL DEFAULT '', -- 车辆描述
daily_rate DECIMAL(6, 2) NOT NULL DEFAULT 9999.99, -- 每日租金
PRIMARY KEY (veh_reg_no) -- 主键
);

-- 创建客户表
CREATE TABLE customers (
customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT, -- 客户ID
name VARCHAR(30) NOT NULL DEFAULT '', -- 客户姓名
address VARCHAR(80) NOT NULL DEFAULT '', -- 客户地址
phone VARCHAR(15) NOT NULL DEFAULT '', -- 客户电话号码
discount DOUBLE NOT NULL DEFAULT 0.0, -- 客户折扣
PRIMARY KEY (customer_id), -- 主键
UNIQUE INDEX (phone) -- 电话号码唯一索引
);

-- 创建租赁记录表
CREATE TABLE rental_records (
rental_id INT UNSIGNED NOT NULL AUTO_INCREMENT, -- 租赁记录ID
veh_reg_no VARCHAR(8) NOT NULL, -- 车辆登记号码
customer_id INT UNSIGNED NOT NULL, -- 客户ID
start_date DATE NOT NULL, -- 租赁开始日期
end_date DATE NOT NULL, -- 租赁结束日期
lastUpdated TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 最后更新时间
PRIMARY KEY (rental_id), -- 主键
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT ON UPDATE CASCADE, -- 客户外键
FOREIGN KEY (veh_reg_no) REFERENCES vehicles(veh_reg_no) ON DELETE RESTRICT ON UPDATE CASCADE -- 车辆外键
);

-- 为租赁记录表创建索引以提高查询速度
CREATE INDEX idx_veh_reg_no ON rental_records(veh_reg_no);
CREATE INDEX idx_customer_id ON rental_records(customer_id);
CREATE INDEX idx_start_date ON rental_records(start_date);
CREATE INDEX idx_end_date ON rental_records(end_date);

-- 插入示例车辆数据
INSERT INTO vehicles (veh_reg_no, category, brand, description, daily_rate) VALUES
('SBA1111A', 'car', 'NISSAN SUNNY 1.6L', '4 Door Saloon, Automatic', 99.99),
('SBB2222B', 'car', 'TOYOTA ALTIS 1.6L', '4 Door Saloon, Automatic', 99.99),
('SBC3333C', 'car', 'HONDA CIVIC 1.8L', '4 Door Saloon, Automatic', 119.99),
('GA5555E', 'truck', 'NISSAN CABSTAR 3.0L', 'Lorry, Manual', 89.99),
('GA6666F', 'truck', 'OPEL COMBO 1.6L', 'Van, Manual', 69.99);

-- 插入示例客户数据
INSERT INTO customers (customer_id, name, address, phone, discount) VALUES
(1001, 'Tan Ah Teck', '1 Kg Java', '55555555', 0.15),
(1002, 'Mohammed Ali', '5 Serangoon Road', '66666666', 0.0),
(1003, 'Kumar', '2 Sunset Boulevard', '77777777', 0.2),
(1004, 'Kevin Jones', '2 Sunset Boulevard', '22222222', 0.2);

-- 练习 1: 为 'Tan Ah Teck' 插入租赁记录
INSERT INTO rental_records (veh_reg_no, customer_id, start_date, end_date)
VALUES
('SBA1111A', (SELECT customer_id FROM customers WHERE name = 'Tan Ah Teck'), CURDATE(), DATE_ADD(CURDATE(), INTERVAL 10 DAY));

-- 练习 2: 为 'Kumar' 插入租赁记录
INSERT INTO rental_records (veh_reg_no, customer_id, start_date, end_date)
VALUES
('GA5555E', (SELECT customer_id FROM customers WHERE name = 'Kumar'), DATE_ADD(CURDATE(), INTERVAL 1 DAY), DATE_ADD(CURDATE(), INTERVAL 3 MONTH));

-- 练习 3: 列出所有租赁记录及车辆和客户详情
SELECT
r.start_date AS Start_Date,
r.end_date AS End_Date,
r.veh_reg_no AS Vehicle_No,
v.brand AS Vehicle_Brand,
c.name AS Customer_Name
FROM rental_records AS r
JOIN vehicles AS v ON r.veh_reg_no = v.veh_reg_no
JOIN customers AS c ON r.customer_id = c.customer_id
ORDER BY v.category, r.start_date;

-- 练习 4: 列出所有过期的租赁记录
SELECT *
FROM rental_records
WHERE end_date < CURDATE();

-- 练习 5: 列出在 '2022-01-10' 租出的车辆
SELECT
r.veh_reg_no AS Vehicle_No,
c.name AS Customer_Name,
r.start_date AS Start_Date,
r.end_date AS End_Date
FROM rental_records AS r
JOIN customers AS c ON r.customer_id = c.customer_id
WHERE '2022-01-10' BETWEEN r.start_date AND r.end_date;

-- 练习 6: 列出今天租出的车辆
SELECT
r.veh_reg_no AS Vehicle_No,
c.name AS Customer_Name,
r.start_date AS Start_Date,
r.end_date AS End_Date
FROM rental_records AS r
JOIN customers AS c ON r.customer_id = c.customer_id
WHERE r.start_date = CURDATE() OR r.end_date = CURDATE();

-- 练习 7: 列出在 '2022-01-03' 到 '2022-01-18' 之间租出的车辆
SELECT
r.veh_reg_no AS Vehicle_No,
c.name AS Customer_Name,
r.start_date AS Start_Date,
r.end_date AS End_Date
FROM rental_records AS r
JOIN customers AS c ON r.customer_id = c.customer_id
WHERE (r.start_date BETWEEN '2022-01-03' AND '2022-01-18')
OR (r.end_date BETWEEN '2022-01-03' AND '2022-01-18')
OR (r.start_date < '2022-01-03' AND r.end_date > '2022-01-18');

-- 练习 8: 列出在 '2022-01-10' 可租的车辆
SELECT *
FROM vehicles
WHERE veh_reg_no NOT IN (
SELECT veh_reg_no
FROM rental_records
WHERE '2022-01-10' BETWEEN start_date AND end_date
);

-- 练习 9: 列出在 '2022-01-03' 到 '2022-01-18' 之间可租的车辆
SELECT *
FROM vehicles
WHERE veh_reg_no NOT IN (
SELECT veh_reg_no
FROM rental_records
WHERE (start_date BETWEEN '2022-01-03' AND '2022-01-18')
OR (end_date BETWEEN '2022-01-03' AND '2022-01-18')
OR (start_date < '2022-01-03' AND end_date > '2022-01-18')
);

-- 练习 10: 列出从今天起 10 天内可租的车辆
SELECT *
FROM vehicles
WHERE veh_reg_no NOT IN (
SELECT veh_reg_no
FROM rental_records
WHERE (start_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY))
OR (end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 10 DAY))
OR (start_date < CURDATE() AND end_date > DATE_ADD(CURDATE(), INTERVAL 10 DAY))
);

-- 创建支付记录表
CREATE TABLE payments (
payment_id INT UNSIGNED NOT NULL AUTO_INCREMENT, -- 支付记录ID
rental_id INT UNSIGNED NOT NULL, -- 租赁记录ID
amount DECIMAL(8, 2) NOT NULL DEFAULT 0, -- 支付金额
mode ENUM('cash', 'credit card', 'check'), -- 支付方式
type ENUM('deposit', 'partial', 'full') NOT NULL DEFAULT 'full', -- 支付类型
remark VARCHAR(255), -- 备注
created_date DATETIME NOT NULL, -- 创建日期
created_by INT UNSIGNED NOT NULL, -- 创建者的员工ID
last_updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 最后更新时间
last_updated_by INT UNSIGNED NOT NULL, -- 更新者的员工ID
PRIMARY KEY (payment_id), -- 主键
INDEX (rental_id), -- 租赁ID索引
FOREIGN KEY (rental_id) REFERENCES rental_records(rental_id) ON DELETE CASCADE -- 外键约束
);

-- 外键测试: 尝试删除有匹配子记录的父记录
DELETE FROM vehicles WHERE veh_reg_no = 'GA6666F'; -- 由于存在租赁记录，这将失败

-- 外键测试: 尝试更新有匹配子记录的父记录
UPDATE vehicles SET veh_reg_no = 'GA9999F' WHERE veh_reg_no = 'GA6666F'; -- 这将更新租赁记录中的外键

-- 从数据库中移除 'GA6666F'
DELETE FROM rental_records WHERE veh_reg_no = 'GA6666F'; -- 先从子表中移除
DELETE FROM vehicles WHERE veh_reg_no = 'GA6666F'; -- 然后从父表中移除