【郑州轻工业大学|数据库】数据库课设-酒店管理系统

该数据课设是一个基于酒店管理系统的数据库设计

建库语句

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

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈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