【鄭州輕工業大學|數據庫】數據庫課設-酒店管理系統

該數據課設是一個基于酒店管理系統的數據庫設計

建庫語句

create database hotel_room default charset utf8 collate utf8_general_ci;

建表語句

use hotel_room;-- 房型表
create table room_type(
id bigint primary key auto_increment comment '房型id',
name varchar(50) not null comment '房型名稱',
bed_type varchar(20) not null comment '床型',
capacity int not null comment '可住人數',
price int not null comment '基礎價格',
description text comment '房型描述',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '房型表';-- 客房表
create table room(
id bigint primary key auto_increment comment '客房id',
type_id bigint not null comment '房型id --邏輯外鍵',
floor int not null comment '樓層數',
price decimal(10,2) not null comment '實際價格',
status int not null default 0 check (status in (0,1,2,3)) comment '客房狀態 0:空閑,1:已預訂,2:已入住,3:維護中',
description text comment '客房描述',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '客房表';-- 客戶表
create table customer (
id bigint primary key auto_increment comment '客戶id',
name varchar(50) not null comment '客戶姓名',
id_type int not null default 0 check (id_type in (0,1,2)) comment '證件類型 0:身份證,1:護照,2軍官證',
id_number varchar(50) not null unique key comment '證件號碼',
phone varchar(20) not null comment '聯系電話',
customer_type int not null check (customer_type in (0,1)) default 0 comment '客戶類型 0:普通客戶,1:會員',
member_level int default 0 comment '客戶等級',
total_consumption decimal(10, 2) default 0 comment '累計消費',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '客戶表';-- 預定表
create table reservation (
id bigint primary key auto_increment comment '預定id',
customer_id int not null comment '客戶id --邏輯外鍵',
room_id varchar(20) not null comment '客房id --邏輯外鍵',
reservation_date datetime default current_timestamp not null comment '預訂日期',
checkin_date datetime not null comment '入住日期',
checkout_date datetime not null comment '退房日期',
status int not null check (status in (0,1,2,3)) default 1 comment '預訂狀態 0:有效,1:已取消,2:已入住,3:已過期',
reservation_name varchar(50) not null comment '預訂人姓名',
reservation_phone varchar(20) not null comment '預訂人聯系電話',
remark text comment '備注',
created_by bigint not null comment '操作員id',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '預定表';-- 入住表
create table checkin (
id bigint primary key auto_increment comment '',
customer_id int not null comment '客戶id --邏輯外鍵',
room_id int not null comment '客房id --邏輯外鍵',
checkin_date datetime default current_timestamp not null comment '入住日期時間',
expected_checkout_date datetime not null comment '預計退房日期',
actual_checkout_date datetime comment '實際退房日期時間',
total_amount decimal(10, 2) comment '消費總金額',
status int not null check (status in (0,1)) default 0 comment '入住狀態 0:已入住,1:已退房',
created_by bigint not null comment '操作員id',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '入住表';-- 角色表
create table role (
id int primary key auto_increment comment '角色id',
role_name varchar(50) not null unique comment '角色名稱 酒店員工,酒店經理,admin',
description text comment '角色描述',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '角色表';-- 用戶表
create table user (
id int primary key auto_increment comment '員工id',
username varchar(50) not null unique comment '用戶名',
password varchar(100) not null comment '密碼哈希',
real_name varchar(50) not null comment '員工真實姓名',
phone varchar(20) comment '聯系電話',
email varchar(50) comment '郵箱',
status int not null check (status in (0,1)) default 0 comment '員工狀態 0:啟用,1:禁用',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '員工表';-- 用戶角色關聯表
create table user_role (
user_id int not null comment '用戶id',
role_id int not null comment '角色id',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
)comment '用戶角色關聯表';-- 入住收款狀態表
create table payment_status (
id int primary key auto_increment comment '記錄id',
checkin_id bigint not null comment '入住記錄id --邏輯外鍵',
customer_id int not null comment '客戶id --邏輯外鍵',
user_id int not null comment '處理員工id --邏輯外鍵',
amount decimal(10, 2) not null comment '金額',
status int not null default 0 check (status in (0,1,2,3)) comment '付款狀態 0:待付款,1:已付款,2:已退款,3:已取消',
payment_method int default 4 check (payment_method in (0,1,2,3,4)) comment '支付方式 0:現金,1:銀行卡,2:微信,3:支付寶,4:其他',
payment_time datetime comment '支付時間',
refund_time datetime comment '退款時間',
remark text comment '退款備注',
create_time timestamp default current_timestamp comment '創建時間',
update_time timestamp default current_timestamp on update current_timestamp comment '更新時間'
) comment '入住收款狀態表';

插入數據


-- 插入房型
insert into room_type (name, bed_type, capacity, price, description) VALUES
('豪華單人房', '單人床', 1, 399, '1.8米單人床，獨立衛浴，40平方米'),
('標準雙人房', '雙床', 2, 499, '兩張1.2米床，獨立衛浴，45平方米'),
('豪華雙人房', '大床', 2, 599, '2.0米大床，獨立衛浴，50平方米'),
('家庭套房', '組合床', 3, 899, '一張大床+一張小床，客廳，70平方米'),
('總統套房', '特大床', 4, 1999, '2.2米大床，獨立客廳、書房，120平方米');-- 客房表數據插入示例
insert into room (type_id, floor, price, status, description) VALUES
(1, 1, 399.00, 0, '位于1樓，安靜，窗外是花園'),
(1, 1, 399.00, 0, '位于1樓，靠近電梯'),
(2, 2, 499.00, 0, '位于2樓，采光好，雙床房'),
(2, 2, 499.00, 0, '位于2樓，可看到泳池'),
(3, 3, 599.00, 0, '位于3樓，豪華大床房'),
(3, 3, 599.00, 3, '位于3樓，正在裝修，暫不可用'),
(4, 4, 899.00, 0, '位于4樓，家庭套房，含兒童床'),
(5, 5, 1999.00, 0, '位于頂層，總統套房，帶獨立客廳');-- 客戶表測試數據（100條，含隨機創建時間和真實姓名）
INSERT INTO customer (name, id_type, id_number, phone, customer_type, member_level, total_consumption, create_time) VALUES
('張三', 0, '110101199001011234', '13800138000', 0, 0, 0.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('李四', 0, '110101199102022345', '13900139000', 1, 1, 2500.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('王五', 1, 'E12345678', '13700137000', 1, 3, 8500.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('趙六', 0, '110101199203033456', '13600136000', 0, 0, 800.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('錢七', 0, '110101199304044567', '13500135000', 1, 2, 4200.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('孫八', 0, '110101199405055678', '13400134000', 0, 0, 350.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('周九', 0, '110101199506066789', '13300133000', 1, 1, 1200.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('吳十', 0, '110101199607077890', '13200132000', 0, 0, 500.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('鄭十一', 0, '110101199708088901', '13100131000', 1, 2, 3800.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('王十二', 0, '110101199809099012', '13000130000', 0, 0, 650.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('陳十三', 0, '110101199910100123', '18900189000', 1, 3, 12500.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('楊十四', 0, '110101200011111234', '18800188000', 0, 0, 400.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('黃十五', 0, '110101200112122345', '18700187000', 1, 1, 1800.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('周十六', 0, '110101200201133456', '18600186000', 0, 0, 720.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('吳十七', 0, '110101200302144567', '18500185000', 1, 2, 5300.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('鄭十八', 0, '110101200403155678', '18400184000', 0, 0, 950.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('錢十九', 0, '110101200504166789', '18300183000', 1, 1, 2100.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('孫二十', 0, '110101200605177890', '18200182000', 0, 0, 680.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('李二十一', 0, '110101200706188901', '18100181000', 1, 2, 4700.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('張二十二', 0, '110101200807199012', '17900179000', 0, 0, 320.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('劉二十三', 0, '110101200908200123', '17800178000', 1, 3, 9200.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('陳二十四', 0, '110101201009211234', '17700177000', 0, 0, 850.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('楊二十五', 0, '110101201110222345', '17600176000', 1, 1, 1950.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('趙二十六', 0, '110101201211233456', '17500175000', 0, 0, 580.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('黃二十七', 0, '110101201312244567', '17400174000', 1, 2, 6300.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('周二十八', 0, '110101201401255678', '17300173000', 0, 0, 790.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('吳二十九', 0, '110101201502266789', '17200172000', 1, 1, 2400.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('鄭三十', 0, '110101201603277890', '17100171000', 0, 0, 920.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('錢三十一', 0, '110101201704288901', '16900169000', 1, 2, 5100.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('孫三十二', 0, '110101201805299012', '16800168000', 0, 0, 430.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('李三十三', 0, '110101201906300123', '16700167000', 1, 3, 10500.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('張三十四', 0, '110101202007011234', '16600166000', 0, 0, 870.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('劉三十五', 0, '110101202108022345', '16500165000', 1, 1, 2200.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('陳三十六', 0, '110101202209033456', '16400164000', 0, 0, 610.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('楊三十七', 0, '110101202310044567', '16300163000', 1, 2, 7300.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('趙三十八', 0, '110101202411055678', '16200162000', 0, 0, 980.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('黃三十九', 0, '110101202512066789', '16100161000', 1, 1, 2700.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
('周四十', 0, '110101202601077890', '16000160000', 0, 0, 740.00, DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY));-- 預訂表測試數據（逐條插入）
INSERT INTO reservation (customer_id, room_id, reservation_date, checkin_date, checkout_date, status, reservation_name, reservation_phone, remark, created_by,create_time) VALUES
(1, 1, '2025-01-10', '2025-01-15', '2025-01-18', 0, '張三', '13800138000', '需要無煙房', 2,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(2, 2, '2025-02-15', '2025-02-20', '2025-02-22', 0, '李四', '13900139000', '帶一位兒童，需要嬰兒床', 2,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(3, 3, '2025-03-20', '2025-03-25', '2025-03-28', 2, '王五', '13700137000', 'VIP客戶，需要特殊安排', 1,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(4, 4, '2025-04-05', '2025-04-10', '2025-04-12', 1, '趙六', '13600136000', '臨時取消', 2,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(5, 5, '2025-05-12', '2025-05-18', '2025-05-20', 0, '錢七', '13500135000', '需要接送機服務', 2,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(6, 6, '2025-06-01', '2025-06-05', '2025-06-08', 0, '孫八', '13400134000', '商務出差，需要會議室', 3,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(7, 7, '2025-06-10', '2025-06-15', '2025-06-17', 0, '周九', '13300133000', '一家三口，需要家庭房', 2,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(8, 8, '2025-07-05', '2025-07-10', '2025-07-15', 0, '吳十', '13200132000', '慶祝結婚紀念日', 1,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(9, 9, '2025-08-15', '2025-08-20', '2025-08-22', 0, '鄭十一', '13100131000', '需要延遲退房', 2,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY)),
(10, 10, '2025-09-20', '2025-09-25', '2025-09-28', 0, '王十二', '13000130000', '需要額外枕頭和毛毯', 2,DATE_ADD(NOW(), INTERVAL -FLOOR(RAND() * 365) DAY));INSERT INTO checkin (customer_id, room_id, checkin_date, expected_checkout_date, actual_checkout_date, total_amount, status, created_by, create_time) VALUES
(1, 1, DATE_ADD(NOW(), INTERVAL -360 DAY), DATE_ADD(NOW(), INTERVAL -358 DAY), DATE_ADD(NOW(), INTERVAL -358 DAY), 800.00, 1, 101, DATE_ADD(NOW(), INTERVAL -360 DAY)),
(2, 2, DATE_ADD(NOW(), INTERVAL -359 DAY), DATE_ADD(NOW(), INTERVAL -356 DAY), DATE_ADD(NOW(), INTERVAL -356 DAY), 1200.00, 1, 102, DATE_ADD(NOW(), INTERVAL -359 DAY)),
(3, 3, DATE_ADD(NOW(), INTERVAL -358 DAY), DATE_ADD(NOW(), INTERVAL -355 DAY), DATE_ADD(NOW(), INTERVAL -355 DAY), 950.00, 1, 103, DATE_ADD(NOW(), INTERVAL -358 DAY)),
(4, 4, DATE_ADD(NOW(), INTERVAL -357 DAY), DATE_ADD(NOW(), INTERVAL -354 DAY), DATE_ADD(NOW(), INTERVAL -354 DAY), 1100.00, 1, 101, DATE_ADD(NOW(), INTERVAL -357 DAY)),
(5, 5, DATE_ADD(NOW(), INTERVAL -356 DAY), DATE_ADD(NOW(), INTERVAL -353 DAY), DATE_ADD(NOW(), INTERVAL -353 DAY), 750.00, 1, 102, DATE_ADD(NOW(), INTERVAL -356 DAY)),
(6, 6, DATE_ADD(NOW(), INTERVAL -355 DAY), DATE_ADD(NOW(), INTERVAL -352 DAY), DATE_ADD(NOW(), INTERVAL -352 DAY), 1300.00, 1, 103, DATE_ADD(NOW(), INTERVAL -355 DAY)),
(7, 7, DATE_ADD(NOW(), INTERVAL -354 DAY), DATE_ADD(NOW(), INTERVAL -351 DAY), DATE_ADD(NOW(), INTERVAL -351 DAY), 1000.00, 1, 101, DATE_ADD(NOW(), INTERVAL -354 DAY)),
(8, 8, DATE_ADD(NOW(), INTERVAL -353 DAY), DATE_ADD(NOW(), INTERVAL -350 DAY), DATE_ADD(NOW(), INTERVAL -350 DAY), 1400.00, 1, 102, DATE_ADD(NOW(), INTERVAL -353 DAY)),
(9, 9, DATE_ADD(NOW(), INTERVAL -352 DAY), DATE_ADD(NOW(), INTERVAL -349 DAY), DATE_ADD(NOW(), INTERVAL -349 DAY), 850.00, 1, 103, DATE_ADD(NOW(), INTERVAL -352 DAY)),
(10, 10, DATE_ADD(NOW(), INTERVAL -351 DAY), DATE_ADD(NOW(), INTERVAL -348 DAY), DATE_ADD(NOW(), INTERVAL -348 DAY), 1250.00, 1, 101, DATE_ADD(NOW(), INTERVAL -351 DAY)),
(11, 11, DATE_ADD(NOW(), INTERVAL -350 DAY), DATE_ADD(NOW(), INTERVAL -347 DAY), DATE_ADD(NOW(), INTERVAL -347 DAY), 900.00, 1, 102, DATE_ADD(NOW(), INTERVAL -350 DAY)),
(12, 12, DATE_ADD(NOW(), INTERVAL -349 DAY), DATE_ADD(NOW(), INTERVAL -346 DAY), DATE_ADD(NOW(), INTERVAL -346 DAY), 1150.00, 1, 103, DATE_ADD(NOW(), INTERVAL -349 DAY)),
(13, 13, DATE_ADD(NOW(), INTERVAL -348 DAY), DATE_ADD(NOW(), INTERVAL -345 DAY), DATE_ADD(NOW(), INTERVAL -345 DAY), 700.00, 1, 101, DATE_ADD(NOW(), INTERVAL -348 DAY)),
(14, 14, DATE_ADD(NOW(), INTERVAL -347 DAY), DATE_ADD(NOW(), INTERVAL -344 DAY), DATE_ADD(NOW(), INTERVAL -344 DAY), 1350.00, 1, 102, DATE_ADD(NOW(), INTERVAL -347 DAY)),
(15, 15, DATE_ADD(NOW(), INTERVAL -346 DAY), DATE_ADD(NOW(), INTERVAL -343 DAY), DATE_ADD(NOW(), INTERVAL -343 DAY), 1050.00, 1, 103, DATE_ADD(NOW(), INTERVAL -346 DAY)),
(16, 16, DATE_ADD(NOW(), INTERVAL -345 DAY), DATE_ADD(NOW(), INTERVAL -342 DAY), DATE_ADD(NOW(), INTERVAL -342 DAY), 1450.00, 1, 101, DATE_ADD(NOW(), INTERVAL -345 DAY)),
(17, 17, DATE_ADD(NOW(), INTERVAL -344 DAY), DATE_ADD(NOW(), INTERVAL -341 DAY), DATE_ADD(NOW(), INTERVAL -341 DAY), 800.00, 1, 102, DATE_ADD(NOW(), INTERVAL -344 DAY)),
(18, 18, DATE_ADD(NOW(), INTERVAL -343 DAY), DATE_ADD(NOW(), INTERVAL -340 DAY), DATE_ADD(NOW(), INTERVAL -340 DAY), 1200.00, 1, 103, DATE_ADD(NOW(), INTERVAL -343 DAY)),
(19, 19, DATE_ADD(NOW(), INTERVAL -342 DAY), DATE_ADD(NOW(), INTERVAL -339 DAY), DATE_ADD(NOW(), INTERVAL -339 DAY), 950.00, 1, 101, DATE_ADD(NOW(), INTERVAL -342 DAY)),
(20, 20, DATE_ADD(NOW(), INTERVAL -341 DAY), DATE_ADD(NOW(), INTERVAL -338 DAY), DATE_ADD(NOW(), INTERVAL -338 DAY), 1100.00, 1, 102, DATE_ADD(NOW(), INTERVAL -341 DAY)),
(21, 21, DATE_ADD(NOW(), INTERVAL -340 DAY), DATE_ADD(NOW(), INTERVAL -337 DAY), DATE_ADD(NOW(), INTERVAL -337 DAY), 750.00, 1, 103, DATE_ADD(NOW(), INTERVAL -340 DAY)),
(22, 22, DATE_ADD(NOW(), INTERVAL -339 DAY), DATE_ADD(NOW(), INTERVAL -336 DAY), DATE_ADD(NOW(), INTERVAL -336 DAY), 1300.00, 1, 101, DATE_ADD(NOW(), INTERVAL -339 DAY)),
(23, 23, DATE_ADD(NOW(), INTERVAL -338 DAY), DATE_ADD(NOW(), INTERVAL -335 DAY), DATE_ADD(NOW(), INTERVAL -335 DAY), 1000.00, 1, 102, DATE_ADD(NOW(), INTERVAL -338 DAY)),
(24, 24, DATE_ADD(NOW(), INTERVAL -337 DAY), DATE_ADD(NOW(), INTERVAL -334 DAY), DATE_ADD(NOW(), INTERVAL -334 DAY), 1400.00, 1, 103, DATE_ADD(NOW(), INTERVAL -337 DAY)),
(25, 25, DATE_ADD(NOW(), INTERVAL -336 DAY), DATE_ADD(NOW(), INTERVAL -333 DAY), DATE_ADD(NOW(), INTERVAL -333 DAY), 850.00, 1, 101, DATE_ADD(NOW(), INTERVAL -336 DAY)),
(26, 26, DATE_ADD(NOW(), INTERVAL -335 DAY), DATE_ADD(NOW(), INTERVAL -332 DAY), DATE_ADD(NOW(), INTERVAL -332 DAY), 1250.00, 1, 102, DATE_ADD(NOW(), INTERVAL -335 DAY)),
(27, 27, DATE_ADD(NOW(), INTERVAL -334 DAY), DATE_ADD(NOW(), INTERVAL -331 DAY), DATE_ADD(NOW(), INTERVAL -331 DAY), 900.00, 1, 103, DATE_ADD(NOW(), INTERVAL -334 DAY)),
(28, 28, DATE_ADD(NOW(), INTERVAL -333 DAY), DATE_ADD(NOW(), INTERVAL -330 DAY), DATE_ADD(NOW(), INTERVAL -330 DAY), 1150.00, 1, 101, DATE_ADD(NOW(), INTERVAL -333 DAY)),
(29, 29, DATE_ADD(NOW(), INTERVAL -332 DAY), DATE_ADD(NOW(), INTERVAL -329 DAY), DATE_ADD(NOW(), INTERVAL -329 DAY), 700.00, 1, 102, DATE_ADD(NOW(), INTERVAL -332 DAY)),
(30, 30, DATE_ADD(NOW(), INTERVAL -331 DAY), DATE_ADD(NOW(), INTERVAL -328 DAY), DATE_ADD(NOW(), INTERVAL -328 DAY), 1350.00, 1, 103, DATE_ADD(NOW(), INTERVAL -331 DAY)),
(31, 31, DATE_ADD(NOW(), INTERVAL -330 DAY), DATE_ADD(NOW(), INTERVAL -327 DAY), DATE_ADD(NOW(), INTERVAL -327 DAY), 1050.00, 1, 101, DATE_ADD(NOW(), INTERVAL -330 DAY)),
(32, 32, DATE_ADD(NOW(), INTERVAL -329 DAY), DATE_ADD(NOW(), INTERVAL -326 DAY), DATE_ADD(NOW(), INTERVAL -326 DAY), 1450.00, 1, 102, DATE_ADD(NOW(), INTERVAL -329 DAY)),
(33, 33, DATE_ADD(NOW(), INTERVAL -328 DAY), DATE_ADD(NOW(), INTERVAL -325 DAY), DATE_ADD(NOW(), INTERVAL -325 DAY), 800.00, 1, 103, DATE_ADD(NOW(), INTERVAL -328 DAY)),
(34, 34, DATE_ADD(NOW(), INTERVAL -327 DAY), DATE_ADD(NOW(), INTERVAL -324 DAY), DATE_ADD(NOW(), INTERVAL -324 DAY), 1200.00, 1, 101, DATE_ADD(NOW(), INTERVAL -327 DAY)),
(35, 1, DATE_ADD(NOW(), INTERVAL -326 DAY), DATE_ADD(NOW(), INTERVAL -323 DAY), DATE_ADD(NOW(), INTERVAL -323 DAY), 950.00, 1, 102, DATE_ADD(NOW(), INTERVAL -326 DAY)),
(36, 2, DATE_ADD(NOW(), INTERVAL -325 DAY), DATE_ADD(NOW(), INTERVAL -322 DAY), DATE_ADD(NOW(), INTERVAL -322 DAY), 1100.00, 1, 103, DATE_ADD(NOW(), INTERVAL -325 DAY)),
(37, 3, DATE_ADD(NOW(), INTERVAL -324 DAY), DATE_ADD(NOW(), INTERVAL -321 DAY), DATE_ADD(NOW(), INTERVAL -321 DAY), 750.00, 1, 101, DATE_ADD(NOW(), INTERVAL -324 DAY)),
(38, 4, DATE_ADD(NOW(), INTERVAL -323 DAY), DATE_ADD(NOW(), INTERVAL -320 DAY), DATE_ADD(NOW(), INTERVAL -320 DAY), 1300.00, 1, 102, DATE_ADD(NOW(), INTERVAL -323 DAY)),
(39, 5, DATE_ADD(NOW(), INTERVAL -322 DAY), DATE_ADD(NOW(), INTERVAL -319 DAY), DATE_ADD(NOW(), INTERVAL -319 DAY), 1000.00, 1, 103, DATE_ADD(NOW(), INTERVAL -322 DAY)),
(40, 6, DATE_ADD(NOW(), INTERVAL -321 DAY), DATE_ADD(NOW(), INTERVAL -318 DAY), DATE_ADD(NOW(), INTERVAL -318 DAY), 1400.00, 1, 101, DATE_ADD(NOW(), INTERVAL -321 DAY)),
(41, 7, DATE_ADD(NOW(), INTERVAL -320 DAY), DATE_ADD(NOW(), INTERVAL -317 DAY), DATE_ADD(NOW(), INTERVAL -317 DAY), 850.00, 1, 102, DATE_ADD(NOW(), INTERVAL -320 DAY)),
(42, 8, DATE_ADD(NOW(), INTERVAL -319 DAY), DATE_ADD(NOW(), INTERVAL -316 DAY), DATE_ADD(NOW(), INTERVAL -316 DAY), 1250.00, 1, 103, DATE_ADD(NOW(), INTERVAL -319 DAY)),
(43, 9, DATE_ADD(NOW(), INTERVAL -318 DAY), DATE_ADD(NOW(), INTERVAL -315 DAY), DATE_ADD(NOW(), INTERVAL -315 DAY), 900.00, 1, 101, DATE_ADD(NOW(), INTERVAL -318 DAY)),
(44, 10, DATE_ADD(NOW(), INTERVAL -317 DAY), DATE_ADD(NOW(), INTERVAL -314 DAY), DATE_ADD(NOW(), INTERVAL -314 DAY), 1150.00, 1, 102, DATE_ADD(NOW(), INTERVAL -317 DAY)),
(45, 11, DATE_ADD(NOW(), INTERVAL -316 DAY), DATE_ADD(NOW(), INTERVAL -313 DAY), DATE_ADD(NOW(), INTERVAL -313 DAY), 700.00, 1, 103, DATE_ADD(NOW(), INTERVAL -316 DAY)),
(46, 12, DATE_ADD(NOW(), INTERVAL -315 DAY), DATE_ADD(NOW(), INTERVAL -312 DAY), DATE_ADD(NOW(), INTERVAL -312 DAY), 1350.00, 1, 101, DATE_ADD(NOW(), INTERVAL -315 DAY)),
(47, 13, DATE_ADD(NOW(), INTERVAL -314 DAY), DATE_ADD(NOW(), INTERVAL -311 DAY), DATE_ADD(NOW(), INTERVAL -311 DAY), 1050.00, 1, 102, DATE_ADD(NOW(), INTERVAL -314 DAY)),
(48, 14, DATE_ADD(NOW(), INTERVAL -313 DAY), DATE_ADD(NOW(), INTERVAL -310 DAY), DATE_ADD(NOW(), INTERVAL -310 DAY), 1450.00, 1, 103, DATE_ADD(NOW(), INTERVAL -313 DAY)),
(49, 15, DATE_ADD(NOW(), INTERVAL -312 DAY), DATE_ADD(NOW(), INTERVAL -309 DAY), DATE_ADD(NOW(), INTERVAL -309 DAY), 800.00, 1, 101, DATE_ADD(NOW(), INTERVAL -312 DAY)),
(50, 16, DATE_ADD(NOW(), INTERVAL -311 DAY), DATE_ADD(NOW(), INTERVAL -308 DAY), DATE_ADD(NOW(), INTERVAL -308 DAY), 1200.00, 1, 102, DATE_ADD(NOW(), INTERVAL -311 DAY));-- 角色表數據
INSERT INTO role (role_name, description) VALUES
('admin', '系統管理員，擁有所有權限'),
('酒店經理', '管理酒店運營，負責員工和客戶管理'),
('酒店員工', '日常運營操作，如入住登記、退房處理');-- 用戶表數據
INSERT INTO user (username, password, real_name, phone, email, status) VALUES
('admin', '$2y$10$XaV4TzP7bJZqK5pW9pQc1OvjJzXbJjXcJzJzXbJjXcJzJzXbJjXc', '系統管理員', '13800138000', 'admin@example.com', 0),
('manager1', '$2y$10$XaV4TzP7bJZqK5pW9pQc1OvjJzXbJjXcJzJzXbJjXcJzJzXbJjXc', '張經理', '13900139000', 'manager1@example.com', 0),
('manager2', '$2y$10$XaV4TzP7bJZqK5pW9pQc1OvjJzXbJjXcJzJzXbJjXcJzJzXbJjXc', '李經理', '13900139001', 'manager2@example.com', 0),
('staff1', '$2y$10$XaV4TzP7bJZqK5pW9pQc1OvjJzXbJjXcJzJzXbJjXcJzJzXbJjXc', '王員工', '13700137000', 'staff1@example.com', 0),
('staff2', '$2y$10$XaV4TzP7bJZqK5pW9pQc1OvjJzXbJjXcJzJzXbJjXcJzJzXbJjXc', '趙員工', '13700137001', 'staff2@example.com', 0),
('staff3', '$2y$10$XaV4TzP7bJZqK5pW9pQc1OvjJzXbJjXcJzJzXbJjXcJzJzXbJjXc', '錢員工', '13700137002', 'staff3@example.com', 0),
('staff4', '$2y$10$XaV4TzP7bJZqK5pW9pQc1OvjJzXbJjXcJzJzXbJjXcJzJzXbJjXc', '孫員工', '13700137003', 'staff4@example.com', 0);-- 用戶角色關聯表數據
INSERT INTO user_role (user_id, role_id) VALUES
(1, 1), -- admin用戶擁有admin角色
(2, 2), -- manager1用戶擁有酒店經理角色
(3, 2), -- manager2用戶擁有酒店經理角色
(4, 3), -- staff1用戶擁有酒店員工角色
(5, 3), -- staff2用戶擁有酒店員工角色
(6, 3), -- staff3用戶擁有酒店員工角色
(7, 3); -- staff4用戶擁有酒店員工角色INSERT INTO payment_status (checkin_id, customer_id, user_id, amount, status, payment_method, payment_time, refund_time, remark, create_time) VALUES
(1, 1, 23, 850.50, 1, 2, DATE_ADD(NOW(), INTERVAL -290 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -291 DAY)),
(2, 2, 45, 1200.75, 1, 3, DATE_ADD(NOW(), INTERVAL -285 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -286 DAY)),
(3, 3, 67, 980.20, 1, 1, DATE_ADD(NOW(), INTERVAL -280 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -281 DAY)),
(4, 4, 12, 1500.00, 1, 3, DATE_ADD(NOW(), INTERVAL -275 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -276 DAY)),
(5, 5, 89, 720.80, 1, 0, DATE_ADD(NOW(), INTERVAL -270 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -271 DAY)),
(6, 6, 34, 1100.30, 1, 2, DATE_ADD(NOW(), INTERVAL -265 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -266 DAY)),
(7, 7, 56, 920.40, 1, 1, DATE_ADD(NOW(), INTERVAL -260 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -261 DAY)),
(8, 8, 78, 1350.90, 1, 3, DATE_ADD(NOW(), INTERVAL -255 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -256 DAY)),
(9, 9, 91, 880.60, 1, 2, DATE_ADD(NOW(), INTERVAL -250 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -251 DAY)),
(10, 10, 22, 1420.25, 1, 0, DATE_ADD(NOW(), INTERVAL -245 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -246 DAY)),
(11, 11, 44, 990.85, 1, 1, DATE_ADD(NOW(), INTERVAL -240 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -241 DAY)),
(12, 12, 66, 1280.40, 1, 3, DATE_ADD(NOW(), INTERVAL -235 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -236 DAY)),
(13, 13, 88, 830.70, 1, 2, DATE_ADD(NOW(), INTERVAL -230 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -231 DAY)),
(14, 14, 10, 1150.50, 1, 1, DATE_ADD(NOW(), INTERVAL -225 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -226 DAY)),
(15, 15, 32, 940.30, 1, 3, DATE_ADD(NOW(), INTERVAL -220 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -221 DAY)),
(16, 16, 54, 1320.95, 1, 2, DATE_ADD(NOW(), INTERVAL -215 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -216 DAY)),
(17, 17, 76, 870.65, 1, 0, DATE_ADD(NOW(), INTERVAL -210 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -211 DAY)),
(18, 18, 98, 1480.20, 1, 1, DATE_ADD(NOW(), INTERVAL -205 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -206 DAY)),
(19, 19, 20, 910.45, 1, 3, DATE_ADD(NOW(), INTERVAL -200 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -201 DAY)),
(20, 20, 42, 1230.80, 1, 2, DATE_ADD(NOW(), INTERVAL -195 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -196 DAY)),
(21, 21, 64, 840.75, 1, 1, DATE_ADD(NOW(), INTERVAL -190 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -191 DAY)),
(22, 22, 86, 1170.30, 1, 3, DATE_ADD(NOW(), INTERVAL -185 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -186 DAY)),
(23, 23, 9, 960.60, 1, 2, DATE_ADD(NOW(), INTERVAL -180 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -181 DAY)),
(24, 24, 31, 1390.95, 1, 0, DATE_ADD(NOW(), INTERVAL -175 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -176 DAY)),
(25, 25, 53, 890.40, 1, 1, DATE_ADD(NOW(), INTERVAL -170 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -171 DAY)),
(26, 26, 75, 1240.85, 1, 3, DATE_ADD(NOW(), INTERVAL -165 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -166 DAY)),
(27, 27, 97, 930.50, 1, 2, DATE_ADD(NOW(), INTERVAL -160 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -161 DAY)),
(28, 28, 19, 1450.25, 1, 1, DATE_ADD(NOW(), INTERVAL -155 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -156 DAY)),
(29, 29, 41, 880.70, 1, 3, DATE_ADD(NOW(), INTERVAL -150 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -151 DAY)),
(30, 30, 63, 1190.30, 1, 2, DATE_ADD(NOW(), INTERVAL -145 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -146 DAY)),
(31, 31, 85, 950.65, 1, 0, DATE_ADD(NOW(), INTERVAL -140 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -141 DAY)),
(32, 32, 7, 1310.90, 1, 1, DATE_ADD(NOW(), INTERVAL -135 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -136 DAY)),
(33, 33, 29, 860.45, 1, 3, DATE_ADD(NOW(), INTERVAL -130 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -131 DAY)),
(34, 34, 51, 1260.80, 1, 2, DATE_ADD(NOW(), INTERVAL -125 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -126 DAY)),
(35, 35, 73, 920.50, 1, 1, DATE_ADD(NOW(), INTERVAL -120 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -121 DAY)),
(36, 36, 95, 1430.20, 1, 3, DATE_ADD(NOW(), INTERVAL -115 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -116 DAY)),
(37, 37, 17, 890.75, 1, 2, DATE_ADD(NOW(), INTERVAL -110 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -111 DAY)),
(38, 38, 39, 1200.30, 1, 0, DATE_ADD(NOW(), INTERVAL -105 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -106 DAY)),
(39, 39, 61, 970.60, 1, 1, DATE_ADD(NOW(), INTERVAL -100 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -101 DAY)),
(40, 40, 83, 1380.95, 1, 3, DATE_ADD(NOW(), INTERVAL -95 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -96 DAY)),
(41, 41, 5, 840.45, 1, 2, DATE_ADD(NOW(), INTERVAL -90 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -91 DAY)),
(42, 42, 27, 1160.80, 1, 1, DATE_ADD(NOW(), INTERVAL -85 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -86 DAY)),
(43, 43, 49, 910.50, 1, 3, DATE_ADD(NOW(), INTERVAL -80 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -81 DAY)),
(44, 44, 71, 1330.25, 1, 2, DATE_ADD(NOW(), INTERVAL -75 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -76 DAY)),
(45, 45, 93, 880.70, 1, 0, DATE_ADD(NOW(), INTERVAL -70 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -71 DAY)),
(46, 46, 21, 1220.30, 1, 1, DATE_ADD(NOW(), INTERVAL -65 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -66 DAY)),
(47, 47, 43, 960.65, 1, 3, DATE_ADD(NOW(), INTERVAL -60 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -61 DAY)),
(48, 48, 65, 1370.90, 1, 2, DATE_ADD(NOW(), INTERVAL -55 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -56 DAY)),
(49, 49, 87, 850.40, 1, 1, DATE_ADD(NOW(), INTERVAL -50 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -51 DAY)),
(50, 50, 15, 1290.85, 1, 3, DATE_ADD(NOW(), INTERVAL -45 DAY), NULL, NULL, DATE_ADD(NOW(), INTERVAL -46 DAY));

一些基本查詢語句

use hotel_room;/**房型查詢語句*/-- 分頁查詢房型 第一個數據 (當前頁-1) * 一頁條數 , 第二個數據 一頁條數
select name,bed_type,capacity,price,description from room_type limit 0,5;-- 根據能住的人數查詢房型
select name,bed_type,capacity,price,description from room_type where capacity >= 1;-- 根據價格區間查詢房型
select name,bed_type,capacity,price,description from room_type where price between 100 and 500;-- 查詢房型的平均價格
select avg(price) as '平均價格' from room_type;/**房間查詢語句*/-- 分頁查詢該類型所有房間 第一個數據 (當前頁-1) * 一頁條數 , 第二個數據 一頁條數
select R.id,RT.name,RT.bed_type,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',R.price,case R.statuswhen 0 then '空閑'when 1 then '已預訂'when 2 then '已入住'when 3 then '維護中'else '未知狀態'end as '客房狀態',R.description
from room as R left join room_type RT on R.type_id = RT.id
where type_id = 1
limit 0,10;-- 分頁查詢價格區間內的房間 并且狀態位于空閑狀態
select R.id,RT.bed_type,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',R.price,case R.statuswhen 0 then '空閑'when 1 then '已預訂'when 2 then '已入住'when 3 then '維護中'else '未知狀態'end as '客房狀態',R.description
from room as R left join room_type RT on R.type_id = RT.id
where R.status = 0 and R.price between 200 and 1000
limit 0,10;-- 查詢所有房間的平均價格
select avg(price) as '平均價格' from room;-- 查詢各種狀態房間的數量
select count(1) as '該狀態房間數量' from room where status = 0;/**客戶表查詢語句*/-- 分頁查詢所有普通客戶
select name,case id_typewhen 0 then '身份證'when 1 then '護照'when 2 then '軍官證'else '未知身份證件'end as '證件類型',id_number, phone,case customer_typewhen 0 then '普通用戶'when 1 then '會員'else '未知類型用戶'end as '用戶類型',member_level, total_consumption, create_time
from customer
where customer_type = 0
limit 0,10;-- 分頁查詢所有會員客戶
select name,case id_typewhen 0 then '身份證'when 1 then '護照'when 2 then '軍官證'else '未知身份證件'end as '證件類型',id_number, phone,case customer_typewhen 0 then '普通用戶'when 1 then '會員'else '未知類型用戶'end as '用戶類型',member_level, total_consumption, create_time
from customer
where customer_type = 1
limit 0,10;-- 根據姓名和手機號查詢用戶
select name,case id_typewhen 0 then '身份證'when 1 then '護照'when 2 then '軍官證'else '未知身份證件'end as '證件類型',id_number, phone,case customer_typewhen 0 then '普通用戶'when 1 then '會員'else '未知類型用戶'end as '用戶類型',member_level, total_consumption, create_time
from customer
where name = '張三' and phone = '13800138000';-- 根據證件號
select name,case id_typewhen 0 then '身份證'when 1 then '護照'when 2 then '軍官證'else '未知身份證件'end as '證件類型',id_number, phone,case customer_typewhen 0 then '普通用戶'when 1 then '會員'else '未知類型用戶'end as '用戶類型',member_level, total_consumption, create_time
from customer
where id_number = '110101200908200123';-- 查詢所有用戶的平均消費 最低消費 最高消費
select avg(total_consumption) as '用戶平均消費',max(total_consumption) as '用戶最高消費',min(total_consumption) as '用戶最低消費'
from customer;/**預約表查詢語句*/-- 根據預約狀態分頁查詢預約記錄
select C.name,C.phone,R.reservation_date,R.checkin_date,R.checkout_date,case R.statuswhen 0 then '有效'when 1 then '已取消'when 2 then '已入住'when 3 then '已過期'else '未知狀態'end as '預約狀態',R.remark,U.real_name
from reservation as R join customer as C join user as U on R.customer_id = C.id and R.created_by = U.id
where R.status = 3
limit 0,5;-- 分頁查詢某短時間內的預約記錄
select C.name,C.phone,R.reservation_date,R.checkin_date,R.checkout_date,case R.statuswhen 0 then '有效'when 1 then '已取消'when 2 then '已入住'when 3 then '已過期'else '未知狀態'end as '預約狀態',R.remark,U.real_name
from reservation as R join customer as C join user as U on R.customer_id = C.id and R.created_by = U.id
where R.reservation_date between '2024-6-05 00:00:00' and '2025-05-05 00:00:00'
limit 0,5;-- 查詢時間段內預約的人數
select count(1) as '總預約人數' from reservation where reservation_date between '2024-6-05 00:00:00' and '2025-05-05 00:00:00';/**入住表查詢語句*/-- 分頁查詢指定時間段內的入住記錄
select CT.name,CT.phone,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',RT.name,C.total_amount,C.checkin_date,C.expected_checkout_date,C.actual_checkout_date,case C.statuswhen 0 then '已入住'when 1 then '已退房'else '未知狀態'end as '入住狀態'
from checkin as C join customer as CT join room as R join user as U join room_type as RTon C.created_by = U.id and C.room_id = R.id and C.customer_id = CT.id and RT.id = R.type_id
where C.checkin_date between '2024-6-05 00:00:00' and '2025-05-05 00:00:00'
limit 0,10;-- 查詢用戶的入住記錄（根據用戶名）
select CT.name,CT.phone,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',RT.name,C.total_amount,C.checkin_date,C.expected_checkout_date,C.actual_checkout_date,case C.statuswhen 0 then '已入住'when 1 then '已退房'else '未知狀態'end as '入住狀態'
from checkin as C join customer as CT join room as R join user as U join room_type as RTon C.created_by = U.id and C.room_id = R.id and C.customer_id = CT.id and RT.id = R.type_id
where CT.name = '張三' and C.checkin_date between '2024-6-05 00:00:00' and '2025-05-05 00:00:00'
limit 0,10;-- 查詢用戶的入住記錄（根據身份證號）
select CT.name,CT.phone,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',RT.name,C.total_amount,C.checkin_date,C.expected_checkout_date,C.actual_checkout_date,case C.statuswhen 0 then '已入住'when 1 then '已退房'else '未知狀態'end as '入住狀態'
from checkin as C join customer as CT join room as R join user as U join room_type as RTon C.created_by = U.id and C.room_id = R.id and C.customer_id = CT.id and RT.id = R.type_id
where CT.id_number = '110101199405055678' and C.checkin_date between '2024-6-05 00:00:00' and '2025-05-05 00:00:00'
limit 0,10;-- 查詢單個客房的入住記錄（根據客房id）
select CT.name,CT.phone,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',RT.name,C.total_amount,C.checkin_date,C.expected_checkout_date,C.actual_checkout_date,case C.statuswhen 0 then '已入住'when 1 then '已退房'else '未知狀態'end as '入住狀態'
from checkin as C join customer as CT join room as R join user as U join room_type as RTon C.created_by = U.id and C.room_id = R.id and C.customer_id = CT.id and RT.id = R.type_id
where C.room_id = 1 and C.checkin_date between '2024-6-05 00:00:00' and '2025-05-05 00:00:00'
limit 0,10;/**支付狀態查詢語句*/
-- 分頁查詢所有支付記錄
select C.name,C.phone,P.amount,case P.statuswhen 0 then '待付款'when 1 then '已付款'when 2 then '已退款'when 3 then '已取消'else '位置狀態'end as '支付狀態',case P.payment_methodwhen 0 then '現金'when 1 then '銀行卡'when 2 then '微信'when 3 then '支付寶'else '其他'end as '支付方式',P.payment_time,P.refund_time,P.remark,CH.checkin_date,CH.actual_checkout_date,U.real_name
from payment_status Pjoin checkin CH join customer C join user U on P.checkin_id = C.id and P.customer_id = C.id and P.user_id = U.id
limit 0,10;-- 根據客戶id查詢支付記錄
select C.name,C.phone,P.amount,case P.statuswhen 0 then '待付款'when 1 then '已付款'when 2 then '已退款'when 3 then '已取消'else '位置狀態'end as '支付狀態',case P.payment_methodwhen 0 then '現金'when 1 then '銀行卡'when 2 then '微信'when 3 then '支付寶'else '其他'end as '支付方式',P.payment_time,P.refund_time,P.remark,CH.checkin_date,CH.actual_checkout_date,U.real_name
from payment_status Pjoin checkin CH join customer C join user U on P.checkin_id = CH.id and P.customer_id = C.id and P.user_id = U.id
where P.customer_id = 2
limit 0,10;/**員工表*/
-- 分頁查詢所有員工
select U.username, U.real_name, R.role_name,U.phone, U.email,case U.statuswhen 0 then '已啟用'when 1 then '已禁用'else '未知'end as '賬號狀態'
from user U join role R join user_role UR on UR.user_id = U.id and R.id = UR.role_id
limit 0,5;-- 用戶名模糊查詢員工
select U.username, U.real_name, R.role_name,U.phone, U.email,case U.statuswhen 0 then '已啟用'when 1 then '已禁用'else '未知'end as '賬號狀態'
from user U join role R join user_role UR on UR.user_id = U.id and R.id = UR.role_id
where real_name like concat('%','張','%')
limit 0,5;

索引

-- 對房間狀態添加索引
create index room_status on room(status);-- 對房間類型添加索引
create index room_type_id on room(type_id);-- 對客戶表添加聯合索引
create index customer_name_phone on customer(name,phone);-- 對客戶表證件號添加索引
create index customer_id_number on customer(id_number);-- 對預約表中 預約客戶id添加索引
create index reservation_customer_id on reservation(customer_id);-- 對入住表中 入住的客戶id添加索引
create index checkin_customer_id on checkin(customer_id);-- 對入住表中 用戶入住的房客id添加索引
create index checkin_room_id on checkin(room_id);-- 對支付記錄表中 客戶id添加索引
create index payment_status_customer_id on payment_status(customer_id);

業務

/* 用戶預約 */
-- 開啟事務
begin;
select status from room where floor = 1 and id = 1;
-- 如果查詢出來的狀態是 0（空閑） 即可進行預定
insert into reservation (customer_id, room_id, checkin_date, checkout_date, remark, created_by)
values (1,1,NOW(),'2025-06-21 12:00:00','無備注',5);
-- 插入成功以后更新該客房的狀態為1（已預約）
update room set status = 1 where id = 1;
-- 提交事務
commit;/* 用戶入住 */
begin;
-- 查詢是否有預約記錄
select id from reservation where customer_id = 1 and room_id = 1 and status = 0;
-- 如果沒有對該房間的預約記錄 查詢該房的狀態
select status from room where floor = 1 and id = 1 and (select count(1) from reservation where customer_id = 1 and room_id = 1 and status = 0) = 0;
-- 如果有預約記錄 把預約記錄的狀態改為2（已入住）
update reservation set status = 2 where id = 11 and customer_id = 1 and room_id = 1;
-- 更新房間狀態
update room set status = 2 where id = 1;
-- 新增入住信息
insert into checkin(customer_id, room_id, checkin_date, expected_checkout_date, total_amount, status, created_by)
values (1,1,now(),'2025-06-21 12:00:00',399,0,5);
-- 提交事務
commit;/* 用戶退房 */
-- 新增付款記錄 checkin_id 需要根據上個新增記錄來確定
begin;
insert into payment_status(checkin_id, customer_id, user_id, amount, status, payment_method, payment_time)
values ((select id from checkin where customer_id = 1 and room_id = 1 and status = 0),1,6,399,0,2,now());
-- 更新入住信息 更新為（已退房）
update checkin set status = 1,actual_checkout_date = now() where customer_id = 1 and room_id = 1 and status = 0;
-- 更新房間狀態（空閑）
update room set status = 0 where id = 1;
-- 提交事務
commit;/* 用戶支付訂單 */
begin;
-- 更新支付記錄
update payment_status set status = 1
where customer_id = 1 and checkin_id = (select id from checkin where customer_id = 1 and room_id = 1 and status = 1 order by checkin.create_time desc limit 1) and status = 0;
-- 提交事務
commit;/* 給用戶退款 */
begin;
-- 更新退款時間 退款原因
update payment_status set status = 2 ,refund_time = now(),remark = '用戶體驗感差'
where customer_id = 1 and checkin_id = (select id from checkin where customer_id = 1 and room_id = 1 and status = 1 order by checkin.create_time desc limit 1) and status = 1;
-- 提交事務
commit;/* 新增房型 */
begin;
insert into room_type(name, bed_type, capacity, price, description)
values ('單人經濟房','單人床',1,100,'實惠經濟，出行方便');
commit;/* 新增客房 */
begin;
insert into room(type_id, floor, price, status, description)
values (1,1,110,0,'實惠經濟，出行方便');
commit;/* 修改員工賬號狀態 */
begin;
update user set status = !status where id = 6;
commit;

視圖

-- 查詢所有房型視圖
create view room_type_view as
select name,bed_type,capacity,price,description from room_type;-- 查詢所有客房視圖
create view room_view as
select R.id,RT.name,RT.bed_type,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',R.price,case R.statuswhen 0 then '空閑'when 1 then '已預訂'when 2 then '已入住'when 3 then '維護中'else '未知狀態'end as '客房狀態',R.description
from room as R left join room_type RT on R.type_id = RT.id;-- 查詢所有客戶視圖
create view customer_view as
select name,case id_typewhen 0 then '身份證'when 1 then '護照'when 2 then '軍官證'else '未知身份證件'end as '證件類型',id_number, phone,case customer_typewhen 0 then '普通用戶'when 1 then '會員'else '未知類型用戶'end as '用戶類型',member_level, total_consumption, create_time
from customer;-- 查詢所有預約視圖
create view reservation_view as
select C.name,C.phone,R.reservation_date,R.checkin_date,R.checkout_date,case R.statuswhen 0 then '有效'when 1 then '已取消'when 2 then '已入住'when 3 then '已過期'else '未知狀態'end as '預約狀態',R.remark,U.real_name
from reservation as R join customer as C join user as U on R.customer_id = C.id and R.created_by = U.id;-- 查詢所有入住視圖
create view checkIn_view as
select CT.name as customer_name,CT.phone,case R.floorwhen 1 then '第一層'when 2 then '第二層'when 3 then '第三層'when 4 then '第四層'when 5 then '第五層'else '未知樓層'end as '樓層',RT.name,C.total_amount,C.checkin_date,C.expected_checkout_date,C.actual_checkout_date,case C.statuswhen 0 then '已入住'when 1 then '已退房'else '未知狀態'end as '入住狀態'
from checkin as C join customer as CT join room as R join user as U join room_type as RT
on C.created_by = U.id and C.room_id = R.id and C.customer_id = CT.id and RT.id = R.type_id;-- 查詢所有支付記錄
create view pay_view as
select C.name,C.phone,P.amount,case P.statuswhen 0 then '待付款'when 1 then '已付款'when 2 then '已退款'when 3 then '已取消'else '位置狀態'end as '支付狀態',case P.payment_methodwhen 0 then '現金'when 1 then '銀行卡'when 2 then '微信'when 3 then '支付寶'else '其他'end as '支付方式',P.payment_time,P.refund_time,P.remark,CH.checkin_date,CH.actual_checkout_date,U.real_name
from payment_status P
join checkin CH join customer C join user U on P.checkin_id = C.id and P.customer_id = C.id and P.user_id = U.id-- 查詢所有員工視圖
create view user_view as
select U.username, U.real_name, R.role_name,U.phone, U.email,case U.statuswhen 0 then '已啟用'when 1 then '已禁用'else '未知'end as '賬號狀態'
from user U join role R join user_role UR on UR.user_id = U.id and R.id = UR.role_id

權限

-- 創建酒店經理賬號
create user 'manager'@'localhost' identified by '123456';-- 創建普通員工賬號
create user 'user'@'localhost' identified by '123456';-- 把這個數據庫所有權限賦予酒店經理賬號
grant all privileges on hotel_room.* to 'manager'@'localhost' with grant option;-- 賦予普通員工賬號權利
grant select on hotel_room.room_type to 'user'@'localhost';
grant select on hotel_room.room to 'user'@'localhost';
grant all privileges on hotel_room.reservation to 'user'@'localhost';
grant all privileges on hotel_room.checkin to 'user'@'localhost';
grant all privileges on hotel_room.payment_status to 'user'@'localhost';grant select on hotel_room.room_type_view to 'user'@'localhost';
grant select on hotel_room.room_view to 'user'@'localhost';
grant all privileges on hotel_room.reservation_view to 'user'@'localhost';
grant all privileges on hotel_room.checkin_view to 'user'@'localhost';
grant all privileges on hotel_room.pay_view to 'user'@'localhost';

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/84256.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/84256.shtml
英文地址，請注明出處：http://en.pswp.cn/web/84256.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

TCP 三次握手與四次揮手詳解

TCP 三次握手與四次揮手詳解

前言 在當今互聯網時代，前端開發的工作范疇早已超越了簡單的頁面布局和交互設計。隨著前端應用復雜度的不斷提高，對網絡性能的優化已成為前端工程師不可忽視的重要職責。而要真正理解并優化網絡性能，就需要探究支撐整個互聯網的基礎協議——…
閱讀更多...
RTD2735TD/RTD2738 （HDMI,DP轉EDP 高分辨率高刷新率顯示器驅動芯片）

RTD2735TD/RTD2738 （HDMI,DP轉EDP 高分辨率高刷新率顯示器驅動芯片）

一、芯片概述 RTD2738是瑞昱半導體（Realtek）推出的一款高性能顯示驅動芯片，專為高端顯示器、便攜屏、專業顯示設備及多屏拼接系統設計。其核心優勢在于支持4K分辨率下240Hz高刷新率及8K30Hz顯示，通過集成DisplayPort 1.4a與HDMI …
閱讀更多...
C++實現手寫strlen函數

C++實現手寫strlen函數

要實現求字符串長度的函數&#xff0c;核心思路是通過指針或索引遍歷字符串&#xff0c;直到遇到字符串結束標志 \0 。以下是兩種常見的實現方式&#xff1a; 指針遍歷版本 #include <iostream> using namespace std; // 指針方式實現strlen size_t myStrlen(const cha…
閱讀更多...
NVPL 函數庫介紹和使用

NVPL 函數庫介紹和使用

文章目錄 NVPL 函數庫介紹和使用什么是 NVPLNVPL 的主要組件NVPL 的優勢安裝 NVPL基本使用示例示例1&#xff1a;使用 NVPL RAND 生成隨機數示例2&#xff1a;使用 NVPL FFT 進行快速傅里葉變換 編譯 NVPL 程序性能優化建議總結 NVPL 函數庫介紹和使用 什么是 NVPL NVPL (NVI…
閱讀更多...
HTTP相關內容補充

HTTP相關內容補充

目錄 一、URI 和 URL 二、使用 Cookie 的狀態管理 三、返回結果的 HTTP狀態碼 一、URI 和 URL URI &#xff1a;統一資源標識符 URL&#xff1a;統一資源定位符 URI 格式 登錄信息&#xff08;認證&#xff09;指定用戶名和密碼作為從服務器端獲取資源時必要的登錄信息&a…
閱讀更多...
MySQL: Invalid use of group function

MySQL: Invalid use of group function

https://stackoverflow.com/questions/2330840/mysql-invalid-use-of-group-function 出錯SQL: 錯誤原因&#xff1a; 1. 不能在 WHERE 子句中使用聚合&#xff08;或分組&#xff09;函數 2. HAVING 只能篩選分組后的聚合結果或分組字段 # Write your MySQL query statem…
閱讀更多...
C#財政票查驗接口集成-醫療發票查驗-非稅收入票據查驗接口

C#財政票查驗接口集成-醫療發票查驗-非稅收入票據查驗接口

財政票據是企事業單位、醫療機構、金融機構等組織的重要報銷憑證&#xff0c;其真實性、完整性和合規性日益受到重視。現如今&#xff0c;為有效防范虛假票據報銷、入賬、資金流失等問題的發生&#xff0c;財政票據查驗接口&#xff0c;結合財政票據識別接口&#xff0c;旨在為…
閱讀更多...
瀏覽器基礎及緩存

瀏覽器基礎及緩存

目錄 瀏覽器概述 主流瀏覽器&#xff1a;IE、Chrome、Firefox、Safari Chrome Firefox IE Safari 瀏覽器內核 核心職責 主流瀏覽器內核 JavaScript引擎 主流的JavaScript引擎 瀏覽器兼容性 瀏覽器渲染 渲染引擎的基本流程 DOM和render樹構建 html解析 DOM 渲染…
閱讀更多...
Ubuntu 安裝Telnet服務

Ubuntu 安裝Telnet服務

1. 安裝Telnet 客戶端 sudo apt-get install telnet 2. 安裝Telnet 服務器 &#xff08;這樣才能用A電腦的客戶端連接B電腦的Telnet服務&#xff09; sudo apt-get install telnetd 3. 這時候Telnet服務器是無法自我啟動的&#xff0c;需要網絡守護進程服務程序來管理…
閱讀更多...
AI+預測3D新模型百十個定位預測+膽碼預測+去和尾2025年6月19日第113彈

AI+預測3D新模型百十個定位預測+膽碼預測+去和尾2025年6月19日第113彈

從今天開始&#xff0c;咱們還是暫時基于舊的模型進行預測&#xff0c;好了&#xff0c;廢話不多說&#xff0c;按照老辦法&#xff0c;重點8-9碼定位&#xff0c;配合三膽下1或下2&#xff0c;殺1-2個和尾&#xff0c;再殺4-5個和值&#xff0c;可以做到100-300注左右。 (1)定…
閱讀更多...
觀察者模式 vs 發布訂閱模式詳解教程

觀察者模式 vs 發布訂閱模式詳解教程

&#x1f31f;觀察者模式 vs 發布訂閱模式詳解教程 收藏 點贊 關注&#xff0c;持續更新高頻面試知識庫&#xff01;&#x1f680; 一、核心概念&#xff08;總&#xff09; 在軟件開發中&#xff0c;觀察者模式&#xff08;Observer&#xff09; 和 發布訂閱模式&#xff0…
閱讀更多...
【云馨AI-大模型】MD2Card：從Markdown到知識卡片的完美轉變

【云馨AI-大模型】MD2Card：從Markdown到知識卡片的完美轉變

Markdown的魅力與挑戰MD2Card的核心功能使用體驗與案例分析總結 在當今這個信息快速傳播的時代&#xff0c;內容創作者們一直在尋找更有效的方式來呈現他們的想法和知識。無論是為了個人學習筆記、團隊內部的知識分享還是對外的內容發布&#xff0c;一個清晰、美觀的展示方式顯…
閱讀更多...
【實戰教程】OPEN API 雷池社區版自動拉黑IP

【實戰教程】OPEN API 雷池社區版自動拉黑IP

老版本使用雷池社區版的時候都需要在界面操作&#xff0c;但是網絡攻擊往往都是無規律的&#xff0c;每次都手動操作非常累 前一段時間雷池社區版剛好開放了OPEN API 功能&#xff0c;可以支持大家使用API的方式進行管理了 但是沒有相關文檔非常難受&#xff0c;一直沒有使用…
閱讀更多...
Hot100——鏈表專項

Hot100——鏈表專項

目錄 相交鏈表 反轉鏈表 回文鏈表 環形鏈表 合并兩個有序鏈表 相交鏈表 ListNode *getIntersectionNode(ListNode *headA, ListNode *headB) {if (headA nullptr || headB nullptr) {return nullptr;}ListNode *pA headA;ListNode *pB headB;while (pA ! pB) {pA (pA…
閱讀更多...
Java + Spring Boot 后端防抖切面類AOP代碼問題排查分析

Java + Spring Boot 后端防抖切面類AOP代碼問題排查分析

需排查分析的防抖切面類 AOP代碼&#xff1a; package com.weiyu.aop;import com.weiyu.anno.Debounce; import com.weiyu.utils.DebounceUtil; import org.aspectj.lang.ProceedingJoinPoint; import org.aspectj.lang.annotation.Around; import org.aspectj.lang.annotatio…
閱讀更多...
【FreeRTOS-信號量】

【FreeRTOS-信號量】

參照正點原子以及以下gitee筆記整理本博客&#xff0c;并將實驗結果附在文末。 https://gitee.com/xrbin/FreeRTOS_learning/tree/master 一、信號量簡介 1、什么是信號量 答&#xff1a;信號量是一種解決同步問題的機制&#xff0c;可以實現對共享資源的有序訪問。 假設有…
閱讀更多...
C++中decltype / auto 類型自動推導的深入講解

C++中decltype / auto 類型自動推導的深入講解

一、基本定義 關鍵字含義出現版本auto根據初始化表達式自動推導類型C11decltype根據表達式的類型推導類型C11 二、二者區別 特性autodecltype(expr)用途聲明變量獲取表達式類型是否需要初始化是否&#xff08;可用表達式&#xff0c;如函數參數&#xff09;是否推導引用否&am…
閱讀更多...
Echarts數據可視化開發教程+120套開源數據可視化大屏H5模板

Echarts數據可視化開發教程+120套開源數據可視化大屏H5模板

數據可視化跨越了語言、技術和專業的邊界&#xff0c;是能夠推動實現跨界溝通&#xff0c;實現國際間跨行業的創新的工具。 正如畫家用顏料表達自我&#xff0c;作者用文字講述故事&#xff0c;而統計人員用數字溝通 ...... 同樣&#xff0c;數據可視化的核心還是傳達信息。 …
閱讀更多...
華為提取版，低調使用！

華為提取版，低調使用！

大家好呀&#xff01;今天想給大家推薦兩款實用軟件&#xff0c;一個是視頻軟件的定制版&#xff0c;另一個是衛星地圖軟件。 01 引言 之前給大家推薦過某秋音樂的定制版&#xff0c;結果被投訴了。以后大家推薦某秋家的軟件要小心&#xff0c;不然很容易違規。 今天推薦的是…
閱讀更多...
天匯企業的網絡設計與實現

天匯企業的網絡設計與實現

天匯企業網絡的設計與實現 摘要&#xff1a;互聯網技術與通信技術的相互帶動作用&#xff0c;使得兩者皆呈現多樣化的快速發展趨勢&#xff0c;5G的時代序幕在已經逐漸開啟&#xff0c;由此引發的互聯網技術和設備變革必然是各界人士關注的重點&#xff0c;幾乎所有與計算機相…
閱讀更多...
最新文章

Copyright @ 2022~2023