sql语句实现功能模块： 1.客房基本信息的录入 修改和删除 2.对客人信息进行录入 删除 修改 3.客房信息查询 4.营业额计算 5.对空闲客房查询和修改 6利用触发器 实现输入入住离店时间自动计算订单价格 6.用触发器实现 删除订单信息的同时 把客房信息中的满员修改为空闲 7.数据库维护

以下是针对您提出的问题的 SQL 语句实现：

1. 客房基本信息的录入、修改和删除

创建客房信息表：

CREATE TABLE room_info (
    room_id INT PRIMARY KEY,
    room_type VARCHAR(50),
    room_price FLOAT,
    room_status VARCHAR(20)
);

插入客房基本信息：

INSERT INTO room_info (room_id, room_type, room_price, room_status) VALUES (101, '标准单人间', 200, '空闲');
INSERT INTO room_info (room_id, room_type, room_price, room_status) VALUES (102, '标准双人间', 300, '空闲');
INSERT INTO room_info (room_id, room_type, room_price, room_status) VALUES (103, '豪华单人间', 400, '空闲');
INSERT INTO room_info (room_id, room_type, room_price, room_status) VALUES (104, '豪华双人间', 500, '空闲');

修改客房信息：

UPDATE room_info SET room_price = 250 WHERE room_id = 101;
UPDATE room_info SET room_status = '已预订' WHERE room_id = 102;

删除客房信息：

DELETE FROM room_info WHERE room_id = 103;

2. 对客人信息进行录入、删除和修改

创建客人信息表：

CREATE TABLE guest_info (
    guest_id INT PRIMARY KEY,
    guest_name VARCHAR(50),
    guest_phone VARCHAR(20),
    guest_email VARCHAR(50)
);

插入客人信息：

INSERT INTO guest_info (guest_id, guest_name, guest_phone, guest_email) VALUES (1, '张三', '13812345678', 'zhangsan@abc.com');
INSERT INTO guest_info (guest_id, guest_name, guest_phone, guest_email) VALUES (2, '李四', '13987654321', 'lisi@abc.com');

修改客人信息：

UPDATE guest_info SET guest_phone = '13612345678' WHERE guest_id = 1;
UPDATE guest_info SET guest_email = 'lisi@def.com' WHERE guest_name = '李四';

删除客人信息：

DELETE FROM guest_info WHERE guest_name = '张三';

3. 客房信息查询

查询所有客房信息：

SELECT * FROM room_info;

查询空闲客房：

SELECT * FROM room_info WHERE room_status = '空闲';

查询标准双人间的价格：

SELECT room_price FROM room_info WHERE room_type = '标准双人间';

4. 营业额计算

创建订单信息表：

CREATE TABLE order_info (
    order_id INT PRIMARY KEY,
    guest_id INT,
    room_id INT,
    checkin_date DATE,
    checkout_date DATE,
    order_price FLOAT,
    FOREIGN KEY (guest_id) REFERENCES guest_info(guest_id),
    FOREIGN KEY (room_id) REFERENCES room_info(room_id)
);

插入订单信息：

INSERT INTO order_info (order_id, guest_id, room_id, checkin_date, checkout_date, order_price) VALUES (1, 1, 101, '2021-01-01', '2021-01-03', 400);
INSERT INTO order_info (order_id, guest_id, room_id, checkin_date, checkout_date, order_price) VALUES (2, 2, 102, '2021-01-05', '2021-01-07', 600);

计算营业额：

SELECT SUM(order_price) FROM order_info;

5. 对空闲客房查询和修改

查询空闲客房：

SELECT * FROM room_info WHERE room_status = '空闲';

修改客房状态：

UPDATE room_info SET room_status = '已预订' WHERE room_id = 101;

6. 利用触发器实现输入入住离店时间自动计算订单价格

创建触发器：

CREATE TRIGGER calculate_price
BEFORE INSERT ON order_info
FOR EACH ROW
BEGIN
    DECLARE days INT;
    SET days = DATEDIFF(NEW.checkout_date, NEW.checkin_date);
    SET NEW.order_price = days * (SELECT room_price FROM room_info WHERE room_id = NEW.room_id);
END;

插入订单信息：

INSERT INTO order_info (order_id, guest_id, room_id, checkin_date, checkout_date) VALUES (3, 2, 103, '2021-01-10', '2021-01-12');

查询订单信息：

SELECT * FROM order_info WHERE order_id = 3;

订单价格会自动计算并插入 order_price 字段。

7. 用触发器实现删除订单信息的同时把客房信息中的满员修改为空闲

创建触发器：

CREATE TRIGGER update_room_status
AFTER DELETE ON order_info
FOR EACH ROW
BEGIN
    UPDATE room_info SET room_status = '空闲' WHERE room_id = OLD.room_id;
END;

删除订单信息：

DELETE FROM order_info WHERE order_id = 2;

客房信息中对应的房间状态会自动修改为“空闲”。

8. 数据库维护

备份数据库：

mysqldump -u username -p database_name > backup.sql

恢复数据库：

mysql -u username -p database_name < backup.sql

优化数据库：

OPTIMIZE TABLE table_name;