数据库酒店客房管理系统 建表代码
时间: 2023-07-29 12:07:43 浏览: 50
好的,以下是一个简单的酒店客房管理系统的建表代码示例:
```
CREATE TABLE hotel (
id INT PRIMARY KEY,
name VARCHAR(255),
address VARCHAR(255)
);
CREATE TABLE room (
id INT PRIMARY KEY,
number VARCHAR(10),
type VARCHAR(50),
price DECIMAL(10,2),
hotel_id INT,
FOREIGN KEY (hotel_id) REFERENCES hotel(id)
);
CREATE TABLE booking (
id INT PRIMARY KEY,
room_id INT,
guest_name VARCHAR(255),
check_in DATE,
check_out DATE,
FOREIGN KEY (room_id) REFERENCES room(id)
);
```
该系统包括三个表:酒店(hotel)、客房(room)和预定(booking)。每个酒店有一个唯一的ID、名称和地址。每个客房有一个唯一的ID、房间号、房间类型、价格和所属酒店的ID。每个预定有一个唯一的ID、客房的ID、客人的姓名、入住和退房日期。为了确保数据完整性,客房和预定表中的酒店ID和客房ID都引用了酒店表中的ID。
相关问题
数据库客房管理系统sql代码
以下是一个简单的酒店客房管理系统的 SQL 数据库创建代码:
```sql
-- 创建客房信息表
CREATE TABLE Room (
RoomID INT PRIMARY KEY,
RoomNumber VARCHAR(50),
RoomType VARCHAR(50),
RoomRate DECIMAL(10,2),
RoomStatus VARCHAR(50)
);
-- 创建客户信息表
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(50),
Phone VARCHAR(50),
Address VARCHAR(50)
);
-- 创建预定信息表
CREATE TABLE Reservation (
ReservationID INT PRIMARY KEY,
CustomerID INT,
RoomID INT,
ReservationDate DATETIME,
CheckInDate DATETIME,
CheckOutDate DATETIME,
ReservationStatus VARCHAR(50),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (RoomID) REFERENCES Room(RoomID)
);
-- 创建员工信息表
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(50),
Phone VARCHAR(50),
Address VARCHAR(50),
JobTitle VARCHAR(50),
Salary DECIMAL(10,2)
);
```
以上是一个简单的酒店客房管理系统的 SQL 数据库创建代码,可以根据实际需求进行修改和扩展。
酒店客房管理系统数据库SQL语句
以下是一个简单的酒店客房管理系统数据库的 SQL 语句示例:
创建客房表:
```
CREATE TABLE Room (
RoomNumber INT PRIMARY KEY,
RoomType VARCHAR(50),
MaxOccupancy INT,
Price DECIMAL(8,2),
Availability BIT
);
```
创建客户表:
```
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(50),
PhoneNumber VARCHAR(20)
);
```
创建预订表:
```
CREATE TABLE Reservation (
ReservationID INT PRIMARY KEY,
RoomNumber INT,
CustomerID INT,
CheckInDate DATETIME,
CheckOutDate DATETIME,
CONSTRAINT fk_room_number FOREIGN KEY (RoomNumber) REFERENCES Room(RoomNumber),
CONSTRAINT fk_customer_id FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
```
查询可用客房:
```
SELECT RoomNumber, RoomType, MaxOccupancy, Price
FROM Room
WHERE Availability = 1;
```
查询已预订客房:
```
SELECT RoomNumber, Customer.FirstName, Customer.LastName, Reservation.CheckInDate, Reservation.CheckOutDate
FROM Reservation
INNER JOIN Room ON Reservation.RoomNumber = Room.RoomNumber
INNER JOIN Customer ON Reservation.CustomerID = Customer.CustomerID;
```
添加预订记录:
```
INSERT INTO Reservation (RoomNumber, CustomerID, CheckInDate, CheckOutDate)
VALUES (101, 1, '2021-07-01', '2021-07-05');
```
更新客房信息:
```
UPDATE Room
SET Availability = 0
WHERE RoomNumber = 101;
```
删除客户记录:
```
DELETE FROM Customer
WHERE CustomerID = 1;
```