用sql写一个酒店管理系统
时间: 2023-11-12 20:08:50 浏览: 49
好的,下面是一个简单的酒店管理系统的 SQL 示例:
创建表格:
```
CREATE TABLE guests (
guest_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(100),
phone_number VARCHAR(20),
email VARCHAR(50)
);
CREATE TABLE rooms (
room_number INT PRIMARY KEY,
type VARCHAR(20) NOT NULL,
price DECIMAL(10,2) NOT NULL,
is_available BOOLEAN NOT NULL DEFAULT true
);
CREATE TABLE bookings (
booking_id INT PRIMARY KEY,
guest_id INT NOT NULL,
room_number INT NOT NULL,
check_in_date DATE NOT NULL,
check_out_date DATE NOT NULL,
FOREIGN KEY (guest_id) REFERENCES guests(guest_id),
FOREIGN KEY (room_number) REFERENCES rooms(room_number)
);
```
插入数据:
```
INSERT INTO guests (guest_id, name, address, phone_number, email)
VALUES
(1, '张三', '北京市海淀区', '12345678901', 'zhangsan@example.com'),
(2, '李四', '上海市浦东新区', '12345678902', 'lisi@example.com'),
(3, '王五', '广州市天河区', '12345678903', 'wangwu@example.com');
INSERT INTO rooms (room_number, type, price)
VALUES
(101, '单人间', 200.00),
(102, '双人间', 300.00),
(103, '豪华套房', 500.00);
INSERT INTO bookings (booking_id, guest_id, room_number, check_in_date, check_out_date)
VALUES
(1, 1, 101, '2022-01-01', '2022-01-04'),
(2, 2, 102, '2022-01-02', '2022-01-05'),
(3, 3, 103, '2022-01-03', '2022-01-06');
```
查询数据:
```
-- 查询所有客房信息
SELECT * FROM rooms;
-- 查询所有可用的客房
SELECT * FROM rooms WHERE is_available = true;
-- 查询所有客人的信息以及他们预订的客房信息
SELECT guests.name, guests.address, rooms.room_number, rooms.type, bookings.check_in_date, bookings.check_out_date
FROM guests
INNER JOIN bookings ON guests.guest_id = bookings.guest_id
INNER JOIN rooms ON bookings.room_number = rooms.room_number;
-- 查询某个客人的信息以及他/她预订的客房信息
SELECT guests.name, guests.address, rooms.room_number, rooms.type, bookings.check_in_date, bookings.check_out_date
FROM guests
INNER JOIN bookings ON guests.guest_id = bookings.guest_id
INNER JOIN rooms ON bookings.room_number = rooms.room_number
WHERE guests.name = '张三';
-- 查询某个客房的预订情况
SELECT rooms.room_number, bookings.check_in_date, bookings.check_out_date
FROM rooms
INNER JOIN bookings ON rooms.room_number = bookings.room_number
WHERE rooms.room_number = 101;
```
更新数据:
```
-- 将客房 101 的价格改为 220.00
UPDATE rooms SET price = 220.00 WHERE room_number = 101;
-- 将客房 101 的可用状态改为 false
UPDATE rooms SET is_available = false WHERE room_number = 101;
-- 将预订编号为 1 的预订信息的离店日期改为 2022-01-05
UPDATE bookings SET check_out_date = '2022-01-05' WHERE booking_id = 1;
```
删除数据:
```
-- 删除客人编号为 3 的客人以及他/她的预订信息
DELETE FROM guests WHERE guest_id = 3;
-- 删除客房 103 的预订信息
DELETE FROM bookings WHERE room_number = 103;
```