/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 50528
 Source Host           : localhost:3306
 Source Schema         : hotel2

 Target Server Type    : MySQL
 Target Server Version : 50528
 File Encoding         : 65001

 Date: 25/12/2021 11:00:33
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for guests
-- ----------------------------
DROP TABLE IF EXISTS `guests`;
CREATE TABLE `guests`  (
  `guest_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '顾客ID',
  `guest_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '顾客姓名',
  `guest_gander` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '顾客性别',
  `guest_idcard` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '顾客身份证号码',
  `guest_phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '顾客手机号码',
  `guest_del` int(1) NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`guest_id`, `guest_idcard`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of guests
-- ----------------------------
INSERT INTO `guests` VALUES (1, '周迹', '女', '100000000000000001', '12345678910', 0);
INSERT INTO `guests` VALUES (2, '闫凯飞', '女', '100000000000000002', '12345678910', 0);
INSERT INTO `guests` VALUES (3, '章舟', '女', '100000000000000004', '12345678910', 0);
INSERT INTO `guests` VALUES (4, '赵敏杰', '男', '100000000000000005', '12345678910', 1);
INSERT INTO `guests` VALUES (5, '刘一❀', '女', '100000000000000006', '12345678910', 0);
INSERT INTO `guests` VALUES (6, '小赵', '男', '100000000000000007', '12345678910', 0);
INSERT INTO `guests` VALUES (7, '小闫', '男', '100000000000000008', '12345678910', 0);
INSERT INTO `guests` VALUES (8, '张三', '女', '100000000000000011', '12345678910', 0);
INSERT INTO `guests` VALUES (12, '王五', '男', '100000000000000012', '12345678910', 0);
INSERT INTO `guests` VALUES (13, '赵六', '女', '100000000000000013', '12345678910', 0);
INSERT INTO `guests` VALUES (14, '大迹', '女', '100000000000000014', '12345678910', 0);
INSERT INTO `guests` VALUES (15, '小敏杰', '男', '100000000000000015', '12345678910', 0);
INSERT INTO `guests` VALUES (16, '大闫', '女', '100000000000000016', '12345678910', 0);
INSERT INTO `guests` VALUES (17, '大闫', '男', '1000000000000000015', '12345678910', 0);
INSERT INTO `guests` VALUES (18, '赵敏杰', '男', '1000000000000000016', '12345678910', 0);
INSERT INTO `guests` VALUES (19, '周迹', '女', '1000000000000000017', '12345678910', 0);

-- ----------------------------
-- Table structure for record
-- ----------------------------
DROP TABLE IF EXISTS `record`;
CREATE TABLE `record`  (
  `record_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `guest_id` int(11) NULL DEFAULT NULL COMMENT '顾客ID',
  `room_id` int(11) NULL DEFAULT NULL COMMENT '房间ID',
  `enter_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '进入房间时间',
  `exit_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '待定' COMMENT '退房时间，默认“待定”',
  `booking_duration` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '预定时长',
  `cost` double(10, 2) NULL DEFAULT NULL COMMENT '消费金额',
  `record_del` int(255) NULL DEFAULT 0 COMMENT '是否删除，默认0，0为未删除',
  PRIMARY KEY (`record_id`) USING BTREE,
  INDEX `guest_id`(`guest_id`) USING BTREE,
  INDEX `room_id`(`room_id`) USING BTREE,
  CONSTRAINT `record_ibfk_1` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`guest_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `record_ibfk_2` FOREIGN KEY (`room_id`) REFERENCES `room` (`room_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of record
-- ----------------------------
INSERT INTO `record` VALUES (2, 12, 2, '2021-12-24 21:53', '2021-12-25 21:53', '1天，4小时', 170.00, 0);
INSERT INTO `record` VALUES (3, 12, 2, '2021-12-24 21:53', '2021-12-25 21:53', '1天，4小时', 170.00, 0);
INSERT INTO `record` VALUES (4, 13, 1, '2021-12-24 21:53', '2021-12-25 21:53', '1天，4小时', 340.00, 0);
INSERT INTO `record` VALUES (5, 14, 5, '2021-12-24 21:53', '待定', '1天，4小时', 340.00, 0);
INSERT INTO `record` VALUES (6, 15, 7, '2021-12-24 21:53', '待定', '1天，4小时', 340.00, 0);
INSERT INTO `record` VALUES (7, 16, 8, '2021-12-24 21:53', '待定', '1天，4小时', 500.00, 0);
INSERT INTO `record` VALUES (8, 17, 12, '2021-12-24 21:53', '待定', '1天，4小时', 700.00, 0);
INSERT INTO `record` VALUES (9, 18, 11, '2021-12-24 21:53', '待定', '1天，4小时', 250.00, 0);
INSERT INTO `record` VALUES (10, 19, 11, '2021-12-24 21:53', '待定', '1天，4小时', 250.00, 0);

-- ----------------------------
-- Table structure for room
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room`  (
  `room_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '房间ID',
  `room_no` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '房间号',
  `room_type_id` int(11) NULL DEFAULT NULL COMMENT '房间类型ID',
  `room_picture` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '房间图片URL',
  `room_status` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '空房' COMMENT '房间状态，默认为“空房”',
  `room_del` int(255) NULL DEFAULT 0 COMMENT '是否已删除，默认为0，0为没删除',
  PRIMARY KEY (`room_id`) USING BTREE,
  INDEX `room_type_id`(`room_type_id`) USING BTREE,
  CONSTRAINT `room_ibfk_1` FOREIGN KEY (`room_type_id`) REFERENCES `room_type` (`room_type_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of room
-- ----------------------------
INSERT INTO `room` VALUES (1, '201', 2, NULL, '已入住', 0);
INSERT INTO `room` VALUES (2, '202', 4, NULL, '空房', 0);
INSERT INTO `room` VALUES (3, '203', 4, NULL, '未打扫', 0);
INSERT INTO `room` VALUES (4, '204', 3, NULL, '未打扫', 0);
INSERT INTO `room` VALUES (5, '205', 2, NULL, '已入住', 0);
INSERT INTO `room` VALUES (6, '206', 7, NULL, '空房', 0);
INSERT INTO `room` VALUES (7, '207', 2, NULL, '已入住', 0);
INSERT INTO `room` VALUES (8, '208', 3, NULL, '已入住', 0);
INSERT INTO `room` VALUES (9, '209', 2, NULL, '空房', 0);
INSERT INTO `room` VALUES (10, '210', 3, NULL, '空房', 0);
INSERT INTO `room` VALUES (11, '301', 4, NULL, '已入住', 0);
INSERT INTO `room` VALUES (12, '302', 5, NULL, '已入住', 0);
INSERT INTO `room` VALUES (13, '303', 6, NULL, '空房', 0);
INSERT INTO `room` VALUES (14, '304', 6, NULL, '空房', 0);
INSERT INTO `room` VALUES (15, '305', 8, NULL, '空房', 0);
INSERT INTO `room` VALUES (16, '306', 5, NULL, '空房', 0);

-- ----------------------------
-- Table structure for room_type
-- ----------------------------
DROP TABLE IF EXISTS `room_type`;
CREATE TABLE `room_type`  (
  `room_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '房间类型ID',
  `room_type_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '房间类型名字',
  `room_type_description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '房间类型描述',
  `day_price` double(10, 2) NULL DEFAULT NULL COMMENT '一天的价格',
  `hour_price` double(10, 2) NULL DEFAULT NULL COMMENT '钟点房的价格',
  `room_type_del` int(255) NULL DEFAULT 0 COMMENT '是否删除，默认为0，0为没有删除',
  PRIMARY KEY (`room_type_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of room_type
-- ----------------------------
INSERT INTO `room_type` VALUES (1, '待定', NULL, 0.00, 0.00, 0);
INSERT INTO `room_type` VALUES (2, '普通单人间', NULL, 95.00, 65.00, 0);
INSERT INTO `room_type` VALUES (3, '豪华大床房', NULL, 110.00, 75.00, 0);
INSERT INTO `room_type` VALUES (4, '普通双人间', NULL, 200.00, 140.00, 0);
INSERT INTO `room_type` VALUES (5, '豪华套间', NULL, 499.00, 399.00, 0);
INSERT INTO `room_type` VALUES (6, '商务间', NULL, 340.00, 200.00, 0);
INSERT INTO `room_type` VALUES (7, '行政间', NULL, 499.00, 399.00, 0);
INSERT INTO `room_type` VALUES (8, '豪华婚房', '舒适，温馨，大气，浪漫', 699.00, 699.00, 0);
INSERT INTO `room_type` VALUES (9, '高配双人房', '', 289.00, 189.00, 0);
INSERT INTO `room_type` VALUES (10, '浪漫情侣房', 'romantic spouse home', 1314.00, 520.00, 0);
INSERT INTO `room_type` VALUES (12, '总统套房', '高级，舒适', 1299.00, 799.00, 0);

-- ----------------------------
-- Table structure for system_info
-- ----------------------------
DROP TABLE IF EXISTS `system_info`;
CREATE TABLE `system_info`  (
  `id` int(1) NULL DEFAULT NULL,
  `hotel_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '酒店名称',
  `system_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '系统名称',
  `welcome_words` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '系统欢迎词'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of system_info
-- ----------------------------
INSERT INTO `system_info` VALUES (1, '温情小筑', 'HRMS 酒店管理系统', '梦见四季，四季有家，欢迎入住，温情小筑');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID，可以自增长',
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `user_status` int(1) NULL DEFAULT 0 COMMENT '状态，默认为0，0为正常',
  `user_del` int(1) NULL DEFAULT 0 COMMENT '是否删除，默认为0，0为没有删除',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'admin', '123123', 0, 0);
INSERT INTO `user` VALUES (2, 'zhao', '123123', 1, 0);

-- ----------------------------
-- Table structure for vip
-- ----------------------------
DROP TABLE IF EXISTS `vip`;
CREATE TABLE `vip`  (
  `vip_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'vipID',
  `guest_id` int(11) NULL DEFAULT NULL COMMENT '顾客ID',
  `vip_card` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'vip卡号',
  `vip_integral` int(255) UNSIGNED NULL DEFAULT 0 COMMENT '积分',
  `vip_del` int(1) NULL DEFAULT NULL COMMENT '逻辑删除',
  PRIMARY KEY (`vip_id`) USING BTREE,
  INDEX `guest_id`(`guest_id`) USING BTREE,
  CONSTRAINT `vip_ibfk_1` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`guest_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of vip
-- ----------------------------
INSERT INTO `vip` VALUES (1, 1, '164032105701', 20, 0);
INSERT INTO `vip` VALUES (2, 2, '164032162602', 200, 0);
INSERT INTO `vip` VALUES (3, 8, '164032171508', 0, 0);
INSERT INTO `vip` VALUES (5, 12, '164033842312', 0, 0);
INSERT INTO `vip` VALUES (6, 7, '164033988307', 0, 0);

-- ----------------------------
-- View structure for record_info
-- ----------------------------
DROP VIEW IF EXISTS `record_info`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `record_info` AS SELECT
guests.guest_id,
guests.guest_name,
guests.guest_gander,
guests.guest_idcard,
guests.guest_phone_number,
guests.guest_del,
record.record_id,
record.room_id,
record.enter_time,
record.exit_time,
record.booking_duration,
record.cost,
record.record_del,
room.room_no,
room.room_picture,
room.room_status,
room.room_del,
room_type.room_type_id,
room_type.room_type_name,
room_type.room_type_description,
room_type.day_price,
room_type.hour_price,
room_type.room_type_del
FROM
guests
INNER JOIN record ON record.guest_id = guests.guest_id
INNER JOIN room ON record.room_id = room.room_id
INNER JOIN room_type ON room.room_type_id = room_type.room_type_id
ORDER BY
record.record_id ASC ;

-- ----------------------------
-- View structure for room_info
-- ----------------------------
DROP VIEW IF EXISTS `room_info`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `room_info` AS SELECT
room.room_id,
room.room_no,
room.room_type_id,
room.room_picture,
room.room_status,
room.room_del,
room_type.room_type_name,
room_type.room_type_description,
room_type.day_price,
room_type.hour_price,
room_type.room_type_del
FROM
room
INNER JOIN room_type ON room.room_type_id = room_type.room_type_id
ORDER BY
room.room_id ASC ;

-- ----------------------------
-- View structure for vip_info
-- ----------------------------
DROP VIEW IF EXISTS `vip_info`;
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `vip_info` AS SELECT
vip.vip_id,
vip.guest_id,
vip.vip_card,
vip.vip_integral,
vip.vip_del,
guests.guest_name,
guests.guest_gander,
guests.guest_idcard,
guests.guest_phone_number,
guests.guest_del
FROM
vip
INNER JOIN guests ON vip.guest_id = guests.guest_id
ORDER BY
vip.vip_id ASC ;

SET FOREIGN_KEY_CHECKS = 1;