mysql代码
时间: 2023-07-02 19:16:31 浏览: 50
以下是在MySQL中创建上述表格的代码:
1. 创建客房表(room)
```
CREATE TABLE room (
room_number INT NOT NULL PRIMARY KEY,
room_type VARCHAR(50) NOT NULL,
room_status VARCHAR(50) NOT NULL
);
```
2. 创建客人表(guest)
```
CREATE TABLE guest (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
id_number VARCHAR(50) NOT NULL,
phone_number VARCHAR(50) NOT NULL,
check_in_time DATETIME NOT NULL,
check_out_time DATETIME NOT NULL,
room_number INT NOT NULL,
FOREIGN KEY (room_number) REFERENCES room (room_number)
);
```
3. 创建订单表(order)
```
CREATE TABLE order (
order_number INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
create_time DATETIME NOT NULL,
total_price DECIMAL(10,2) NOT NULL,
guest_name VARCHAR(50) NOT NULL,
guest_phone VARCHAR(50) NOT NULL,
room_number INT NOT NULL,
check_in_time DATETIME NOT NULL,
check_out_time DATETIME NOT NULL,
FOREIGN KEY (room_number) REFERENCES room (room_number)
);
```
4. 创建房间类型表(room_type)
```
CREATE TABLE room_type (
room_type VARCHAR(50) NOT NULL PRIMARY KEY,
bed_type VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
```
5. 创建房间价格表(room_price)
```
CREATE TABLE room_price (
room_type VARCHAR(50) NOT NULL,
date DATE NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (room_type, date),
FOREIGN KEY (room_type) REFERENCES room_type (room_type)
);
```
6. 创建服务项目表(service)
```
CREATE TABLE service (
service_name VARCHAR(50) NOT NULL PRIMARY KEY,
price DECIMAL(10,2) NOT NULL
);
```
7. 创建订单服务表(order_service)
```
CREATE TABLE order_service (
order_number INT NOT NULL,
service_name VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_number, service_name),
FOREIGN KEY (order_number) REFERENCES order (order_number),
FOREIGN KEY (service_name) REFERENCES service (service_name)
);
```
以上是创建表格的基本代码,可以根据需要进行调整和扩展。注意,在实际应用中,需要根据具体业务需求添加适当的索引和约束来保证数据的完整性和查询性能。