酒店管理数据库 MySQL 代码介绍（课程设计）

最新推荐文章于 2024-04-25 23:11:56 发布

叽里咕噜qiu

最新推荐文章于 2024-04-25 23:11:56 发布

阅读量1.2k

点赞数 2

文章标签： mysql 数据库课程设计

本文链接：https://blog.csdn.net/weixin_63783406/article/details/131253545

版权

酒店管理数据库 MySQL 代码介绍（课程设计）

本文介绍了一个 MySQL 数据库的代码片段，包含了创建表、插入数据、创建视图、存储过程以及触发器等功能。以下是对每个部分的详细介绍：（可视化界面见其他文章）

数据库结构

表结构

在代码中，定义了多个表，包括 CUSTOMER、EMPLOYEE、EMPLOYEE_ROLE_TYPE、EMPLOYEEPAYROLL、FINANCE、HOTELORDER、ORDER_TYPE、ROOM、ROOM_STATE、ROOM_STATE_TYPE和USER。

每个表都包含了列的定义，例如表 CUSTOMER 包含了 customerId、customerName、customerEmail、customerPhoneNumber和uuid等列。

下面是表结构的示例：

表名	列名	类型	默认值	主键	外键
CUSTOMER	customerId	int	-	Yes	-
	customerName	varchar(255)	-	-	-
	customerEmail	varchar(255)	-	-	-
	customerPhoneNumber	varchar(255)	-	-	-
	uuid	int	-	-	-
EMPLOYEE	employeeId	int	-	Yes	-
	employeeName	varchar(255)	-	-	-
	employeeRoleId	int	-	-	EMPLOYEE_ROLE_TYPE (employeeRoleId)
	employeePhoneNumber	varchar(255)	-	-	-
	uuid	varchar(255)	-	-	-
EMPLOYEE_ROLE_TYPE	employeeRoleId	int	-	Yes	-
	employeeRoleName	varchar(255)	-	-	-
EMPLOYEEPAYROLL	employeeId	int	-	Yes	EMPLOYEE (employeeId)
	amount	varchar(255)	-	-	-
	transactionDate	varchar(255)	-	-	-
FINANCE	financeId	int	-	Yes	-
	orderId	int	-	-	HOTELORDER (orderId)
	transactionDate	datetime	-	-	-
	amount	varchar(255)	-	-	-
HOTELORDER	orderId	int	-	Yes	-
	roomId	int	-	-	ROOM (roomId)
	customerId	int	-	-	CUSTOMER (customerId)
	orderTypeId	int	-	-
ORDER_TYPE (orderTypeId)
	amount	int	-	-	-
ORDER_TYPE	orderTypeId	int	-	Yes	-
	orderTypeName	varchar(255)	-	-	-
ROOM	roomId	int	-	Yes	-
	roomName	varchar(255)	-	-	-
	roomStateId	int	-	-	ROOM_STATE (roomStateId)
ROOM_STATE	roomStateId	int	-	Yes	-
	roomStateName	varchar(255)	-	-	-
ROOM_STATE_TYPE	roomStateTypeId	int	-	Yes	-
	roomStateTypeName	varchar(255)	-	-	-
USER	userId	int	-	Yes	-
	userName	varchar(255)	-	-	-
	userRoleId	int	-	-	EMPLOYEE_ROLE_TYPE (userRoleId)

视图

代码中还定义了多个视图，包括 customerview、employeefinanceview、employeeview、orderview和roomstateview。这些视图根据不同的需求从表中检索数据，并将其组合成新的视图。

下面是视图的示例：

customerview

CREATE VIEW customerview AS
SELECT customerId, customerName, customerEmail
FROM CUSTOMER;

employeefinanceview

CREATE VIEW employeefinanceview AS
SELECT E.employeeId, E.employeeName, F.amount
FROM EMPLOYEE AS E
JOIN EMPLOYEEPAYROLL AS EP ON E.employeeId = EP.employeeId
JOIN FINANCE AS F ON EP.employeeId = F.employeeId;

存储过程

代码中包含了多个存储过程，例如 change_room_state、create_employee、create_hotel_order、update_employee_salary和update_finance_amount。这些存储过程用于执行特定的数据库操作，如更改房间状态、创建员工、创建酒店订单、更新员工工资和更新财务金额等。

下面是存储过程的示例：

change_room_state

DELIMITER //
CREATE PROCEDURE change_room_state(roomId INT, newStateId INT)
BEGIN
    UPDATE ROOM
    SET roomStateId = newStateId
    WHERE roomId = roomId;
END //
DELIMITER ;

create_employee

DELIMITER //
CREATE PROCEDURE create_employee(
    employeeName VARCHAR(255),
    employeeRoleId INT,
    employeePhoneNumber VARCHAR(255),
    uuid VARCHAR(255)
)
BEGIN
    INSERT INTO EMPLOYEE (employeeName, employeeRoleId, employeePhoneNumber, uuid)
    VALUES (employeeName, employeeRoleId, employeePhoneNumber, uuid);
END //
DELIMITER ;

触发器

代码中定义了多个触发器，用于在特定事件发生时自动执行操作。例如，tr_generate_customerId触发器在向 CUSTOMER 表

插入数据时自动生成 customerId。

下面是触发器的示例：

tr_generate_customerId

DELIMITER //
CREATE TRIGGER tr_generate_customerId BEFORE INSERT ON CUSTOMER
FOR EACH ROW
BEGIN
    SET NEW.customerId = UUID_SHORT();
END //
DELIMITER ;

总结

本文介绍了一个 MySQL 数据库的代码片段，包含了创建表、插入数据、创建视图、存储过程和触发器等功能。通过使用这些代码，可以轻松管理和操作数据库中的数据。希望这些示例能对你在开发 MySQL 数据库应用时有所帮助！

完整代码如下（放在sql文件直接导入）

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80031 (8.0.31)
 Source Host           : localhost:3306
 Source Schema         : hotel_anagement

 Target Server Type    : MySQL
 Target Server Version : 80031 (8.0.31)
 File Encoding         : 65001

 Date: 14/06/2023 20:02:44
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for CUSTOMER
-- ----------------------------
DROP TABLE IF EXISTS `CUSTOMER`;
CREATE TABLE `CUSTOMER` (
  `customerId` int NOT NULL,
  `customerName` varchar(255) DEFAULT NULL,
  `customerEmail` varchar(255) DEFAULT NULL,
  `customerPhoneNumber` varchar(255) DEFAULT NULL,
  `uuid` int DEFAULT NULL,
  PRIMARY KEY (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of CUSTOMER
-- ----------------------------
BEGIN;
INSERT INTO `CUSTOMER` (`customerId`, `customerName`, `customerEmail`, `customerPhoneNumber`, `uuid`) VALUES (1, '邓bb', 'sb@dsy.com', '18888888888', 20001);
INSERT INTO `CUSTOMER` (`customerId`, `customerName`, `customerEmail`, `customerPhoneNumber`, `uuid`) VALUES (2, '郑cc', 'zzt@qq.com', '12342423424', 20002);
INSERT INTO `CUSTOMER` (`customerId`, `customerName`, `customerEmail`, `customerPhoneNumber`, `uuid`) VALUES (3, '杨dd', 'yggggg@111.com', '17892784383', 20003);
INSERT INTO `CUSTOMER` (`customerId`, `customerName`, `customerEmail`, `customerPhoneNumber`, `uuid`) VALUES (4, '赖999', 'lllljh@qq.com', '12849288938', 20004);
COMMIT;

-- ----------------------------
-- Table structure for EMPLOYEE
-- ----------------------------
DROP TABLE IF EXISTS `EMPLOYEE`;
CREATE TABLE `EMPLOYEE` (
  `employeeId` int NOT NULL,
  `employeeName` varchar(255) DEFAULT NULL,
  `employeeRoleId` int DEFAULT NULL,
  `employeePhoneNumber` varchar(255) DEFAULT NULL,
  `uuid` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`employeeId`),
  KEY `employeeRoleId` (`employeeRoleId`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`employeeRoleId`) REFERENCES `EMPLOYEE_ROLE_TYPE` (`employeeRoleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of EMPLOYEE
-- ----------------------------
BEGIN;
INSERT INTO `EMPLOYEE` (`employeeId`, `employeeName`, `employeeRoleId`, `employeePhoneNumber`, `uuid`) VALUES (1, '何aa', 3, '1888888888', '10001');
COMMIT;

-- ----------------------------
-- Table structure for EMPLOYEE_ROLE_TYPE
-- ----------------------------
DROP TABLE IF EXISTS `EMPLOYEE_ROLE_TYPE`;
CREATE TABLE `EMPLOYEE_ROLE_TYPE` (
  `employeeRoleId` int NOT NULL,
  `employeeRoleName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`employeeRoleId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of EMPLOYEE_ROLE_TYPE
-- ----------------------------
BEGIN;
INSERT INTO `EMPLOYEE_ROLE_TYPE` (`employeeRoleId`, `employeeRoleName`) VALUES (0, '混子');
INSERT INTO `EMPLOYEE_ROLE_TYPE` (`employeeRoleId`, `employeeRoleName`) VALUES (1, '普通员工');
INSERT INTO `EMPLOYEE_ROLE_TYPE` (`employeeRoleId`, `employeeRoleName`) VALUES (2, '大堂经理');
INSERT INTO `EMPLOYEE_ROLE_TYPE` (`employeeRoleId`, `employeeRoleName`) VALUES (3, '管理员');
COMMIT;

-- ----------------------------
-- Table structure for EMPLOYEEPAYROLL
-- ----------------------------
DROP TABLE IF EXISTS `EMPLOYEEPAYROLL`;
CREATE TABLE `EMPLOYEEPAYROLL` (
  `employeeId` int NOT NULL,
  `amount` varchar(255) DEFAULT NULL,
  `transactionDate` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`employeeId`),
  CONSTRAINT `employeepayroll_ibfk_1` FOREIGN KEY (`employeeId`) REFERENCES `EMPLOYEE` (`employeeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of EMPLOYEEPAYROLL
-- ----------------------------
BEGIN;
INSERT INTO `EMPLOYEEPAYROLL` (`employeeId`, `amount`, `transactionDate`) VALUES (1, '9999', '2023-06-14 19:00:00');
COMMIT;

-- ----------------------------
-- Table structure for FINANCE
-- ----------------------------
DROP TABLE IF EXISTS `FINANCE`;
CREATE TABLE `FINANCE` (
  `financeId` int NOT NULL,
  `orderId` int DEFAULT NULL,
  `transactionDate` datetime DEFAULT NULL,
  `amount` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`financeId`),
  KEY `orderId` (`orderId`),
  CONSTRAINT `finance_ibfk_1` FOREIGN KEY (`orderId`) REFERENCES `HOTELORDER` (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of FINANCE
-- ----------------------------
BEGIN;
INSERT INTO `FINANCE` (`financeId`, `orderId`, `transactionDate`, `amount`) VALUES (1, 1, '2023-06-14 17:13:00', '555.00');
INSERT INTO `FINANCE` (`financeId`, `orderId`, `transactionDate`, `amount`) VALUES (2, 2, '2023-06-14 13:39:44', '233.00');
INSERT INTO `FINANCE` (`financeId`, `orderId`, `transactionDate`, `amount`) VALUES (3, 3, '2023-06-14 16:51:23', '455.00');
INSERT INTO `FINANCE` (`financeId`, `orderId`, `transactionDate`, `amount`) VALUES (4, 4, '2023-06-14 19:59:53', '455.00');
COMMIT;

-- ----------------------------
-- Table structure for HOTELORDER
-- ----------------------------
DROP TABLE IF EXISTS `HOTELORDER`;
CREATE TABLE `HOTELORDER` (
  `orderId` int NOT NULL,
  `roomId` int DEFAULT NULL,
  `customerId` int DEFAULT NULL,
  `orderTypeId` int DEFAULT NULL,
  `orderCreateDate` datetime DEFAULT NULL,
  `orderUpdateDate` datetime DEFAULT NULL,
  PRIMARY KEY (`orderId`),
  KEY `roomId` (`roomId`),
  KEY `customerId` (`customerId`),
  KEY `orderTypeId` (`orderTypeId`),
  CONSTRAINT `hotelorder_ibfk_1` FOREIGN KEY (`roomId`) REFERENCES `ROOM` (`roomId`),
  CONSTRAINT `hotelorder_ibfk_2` FOREIGN KEY (`customerId`) REFERENCES `CUSTOMER` (`customerId`),
  CONSTRAINT `hotelorder_ibfk_3` FOREIGN KEY (`orderTypeId`) REFERENCES `ORDER_TYPE` (`orderTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of HOTELORDER
-- ----------------------------
BEGIN;
INSERT INTO `HOTELORDER` (`orderId`, `roomId`, `customerId`, `orderTypeId`, `orderCreateDate`, `orderUpdateDate`) VALUES (1, 1, 1, 2, '2023-06-14 13:27:01', '2023-06-14 17:30:32');
INSERT INTO `HOTELORDER` (`orderId`, `roomId`, `customerId`, `orderTypeId`, `orderCreateDate`, `orderUpdateDate`) VALUES (2, 4, 4, 1, '2023-06-14 13:39:44', '2023-06-14 17:05:27');
INSERT INTO `HOTELORDER` (`orderId`, `roomId`, `customerId`, `orderTypeId`, `orderCreateDate`, `orderUpdateDate`) VALUES (3, 8, 4, 2, '2023-06-14 16:51:23', '2023-06-14 17:36:32');
INSERT INTO `HOTELORDER` (`orderId`, `roomId`, `customerId`, `orderTypeId`, `orderCreateDate`, `orderUpdateDate`) VALUES (4, 10, 1, 1, '2023-06-14 19:59:53', '2023-06-14 19:59:53');
COMMIT;

-- ----------------------------
-- Table structure for ORDER_TYPE
-- ----------------------------
DROP TABLE IF EXISTS `ORDER_TYPE`;
CREATE TABLE `ORDER_TYPE` (
  `orderTypeId` int NOT NULL,
  `orderTypeName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`orderTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of ORDER_TYPE
-- ----------------------------
BEGIN;
INSERT INTO `ORDER_TYPE` (`orderTypeId`, `orderTypeName`) VALUES (1, '预定');
INSERT INTO `ORDER_TYPE` (`orderTypeId`, `orderTypeName`) VALUES (2, '入住');
INSERT INTO `ORDER_TYPE` (`orderTypeId`, `orderTypeName`) VALUES (3, '完成');
COMMIT;

-- ----------------------------
-- Table structure for ROOM
-- ----------------------------
DROP TABLE IF EXISTS `ROOM`;
CREATE TABLE `ROOM` (
  `roomId` int NOT NULL,
  `roomName` varchar(255) DEFAULT NULL,
  `roomTypeId` int DEFAULT NULL,
  `roomPrice` int DEFAULT NULL,
  PRIMARY KEY (`roomId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of ROOM
-- ----------------------------
BEGIN;
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (1, '垃圾小破房', 0, 100);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (2, '豪华房', 1, 999);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (3, '翡翠套房', 2, 588);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (4, '蓝宝石豪华房', 3, 233);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (5, '金光双床房', 8, 999);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (6, '玫瑰别墅', 5, 218);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (7, '钻石奢华间', 2, 233);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (8, '舒适家庭房', 4, 455);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (9, '翡翠湖景房', 6, 988);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (10, '海洋套房', 3, 455);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (11, '蜜月天堂套房', 3, 2000);
INSERT INTO `ROOM` (`roomId`, `roomName`, `roomTypeId`, `roomPrice`) VALUES (12, '花园别墅', 3, 888);
COMMIT;

-- ----------------------------
-- Table structure for ROOM_STATE
-- ----------------------------
DROP TABLE IF EXISTS `ROOM_STATE`;
CREATE TABLE `ROOM_STATE` (
  `roomId` int NOT NULL,
  `roomStateId` int DEFAULT NULL,
  PRIMARY KEY (`roomId`),
  KEY `roomStateId` (`roomStateId`),
  CONSTRAINT `room_state_ibfk_1` FOREIGN KEY (`roomId`) REFERENCES `ROOM` (`roomId`),
  CONSTRAINT `room_state_ibfk_2` FOREIGN KEY (`roomStateId`) REFERENCES `ROOM_STATE_TYPE` (`roomStateId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of ROOM_STATE
-- ----------------------------
BEGIN;
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (2, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (3, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (5, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (6, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (7, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (9, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (11, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (12, 0);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (4, 1);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (10, 1);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (1, 2);
INSERT INTO `ROOM_STATE` (`roomId`, `roomStateId`) VALUES (8, 2);
COMMIT;

-- ----------------------------
-- Table structure for ROOM_STATE_TYPE
-- ----------------------------
DROP TABLE IF EXISTS `ROOM_STATE_TYPE`;
CREATE TABLE `ROOM_STATE_TYPE` (
  `roomStateId` int NOT NULL,
  `roomStateName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`roomStateId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of ROOM_STATE_TYPE
-- ----------------------------
BEGIN;
INSERT INTO `ROOM_STATE_TYPE` (`roomStateId`, `roomStateName`) VALUES (0, '空闲');
INSERT INTO `ROOM_STATE_TYPE` (`roomStateId`, `roomStateName`) VALUES (1, '预定');
INSERT INTO `ROOM_STATE_TYPE` (`roomStateId`, `roomStateName`) VALUES (2, '入住');
INSERT INTO `ROOM_STATE_TYPE` (`roomStateId`, `roomStateName`) VALUES (3, '退房');
COMMIT;

-- ----------------------------
-- Table structure for ROOM_TYPE
-- ----------------------------
DROP TABLE IF EXISTS `ROOM_TYPE`;
CREATE TABLE `ROOM_TYPE` (
  `roomTypeId` int NOT NULL,
  `roomTypeName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`roomTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of ROOM_TYPE
-- ----------------------------
BEGIN;
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (0, '茅厕垃圾无窗小房间');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (1, '标准间');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (2, '豪华间');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (3, '套房');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (4, '家庭房');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (5, '别墅');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (6, '湖景房');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (7, '海景房');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (8, '蜜月套房');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (9, '主题房');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (10, '执行套房');
INSERT INTO `ROOM_TYPE` (`roomTypeId`, `roomTypeName`) VALUES (99, '超级无敌豪华总统套房');
COMMIT;

-- ----------------------------
-- Table structure for USER
-- ----------------------------
DROP TABLE IF EXISTS `USER`;
CREATE TABLE `USER` (
  `uuid` char(255) DEFAULT NULL,
  `account` char(255) DEFAULT NULL,
  `password` char(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of USER
-- ----------------------------
BEGIN;
INSERT INTO `USER` (`uuid`, `account`, `password`) VALUES ('10001', 'admin', 'admin');
COMMIT;

-- ----------------------------
-- View structure for customerview
-- ----------------------------
DROP VIEW IF EXISTS `customerview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `customerview` AS select `customer`.`customerId` AS `customerId`,`customer`.`customerName` AS `customerName`,`customer`.`customerEmail` AS `customerEmail`,`customer`.`customerPhoneNumber` AS `customerPhoneNumber`,`customer`.`uuid` AS `uuid` from `customer`;

-- ----------------------------
-- View structure for employeefinanceview
-- ----------------------------
DROP VIEW IF EXISTS `employeefinanceview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `employeefinanceview` AS select `e`.`employeeId` AS `employeeId`,`e`.`employeeName` AS `employeeName`,`p`.`amount` AS `amount` from (`employee` `e` join `employeepayroll` `p` on((`e`.`employeeId` = `p`.`employeeId`)));

-- ----------------------------
-- View structure for employeeview
-- ----------------------------
DROP VIEW IF EXISTS `employeeview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `employeeview` AS select `e`.`employeeId` AS `employeeId`,`e`.`employeeName` AS `employeeName`,`e`.`employeePhoneNumber` AS `employeePhoneNumber`,`r`.`employeeRoleName` AS `employeeRoleName` from (`employee` `e` join `employee_role_type` `r` on((`e`.`employeeRoleId` = `r`.`employeeRoleId`)));

-- ----------------------------
-- View structure for orderview
-- ----------------------------
DROP VIEW IF EXISTS `orderview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `orderview` AS select `ho`.`orderId` AS `orderId`,`ho`.`roomId` AS `roomId`,`ho`.`customerId` AS `customerId`,`ho`.`orderCreateDate` AS `orderCreateDate`,`ho`.`orderUpdateDate` AS `orderUpdateDate`,`r`.`roomName` AS `roomName`,`c`.`customerName` AS `customerName`,`ot`.`orderTypeName` AS `orderTypeName`,`f`.`amount` AS `amount` from ((((`hotelorder` `ho` join `room` `r` on((`ho`.`roomId` = `r`.`roomId`))) join `customer` `c` on((`ho`.`customerId` = `c`.`customerId`))) join `order_type` `ot` on((`ho`.`orderTypeId` = `ot`.`orderTypeId`))) join `finance` `f` on((`ho`.`orderId` = `f`.`orderId`)));

-- ----------------------------
-- View structure for roomstateview
-- ----------------------------
DROP VIEW IF EXISTS `roomstateview`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `roomstateview` AS select `rs`.`roomId` AS `roomId`,`rst`.`roomStateName` AS `roomStateName` from (`room_state` `rs` join `room_state_type` `rst` on((`rs`.`roomStateId` = `rst`.`roomStateId`)));

-- ----------------------------
-- Procedure structure for change_room_state
-- ----------------------------
DROP PROCEDURE IF EXISTS `change_room_state`;
delimiter ;;
CREATE PROCEDURE `change_room_state`(IN pRoomId INT, IN pRoomStateId INT)
BEGIN
    DECLARE currentDate DATETIME;
    
    -- 获取当前日期和时间
    SET currentDate = CURRENT_TIMESTAMP;
    
    -- 更新ROOM_STATE表
    UPDATE ROOM_STATE
    SET roomStateId = pRoomStateId
    WHERE roomId = pRoomId;

    -- 更新HOTELORDER表，包括orderTypeId和orderUpdateDate
    UPDATE HOTELORDER
    SET orderTypeId = pRoomStateId,
        orderUpdateDate = currentDate
    WHERE roomId = pRoomId;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for create_employee
-- ----------------------------
DROP PROCEDURE IF EXISTS `create_employee`;
delimiter ;;
CREATE PROCEDURE `create_employee`(IN pEmployeeName VARCHAR(255), IN pEmployeePhoneNumber VARCHAR(255))
BEGIN
    DECLARE maxEmployeeId INT;
    DECLARE currentDate DATETIME;

    -- 获取当前最大的employeeId值
    SELECT MAX(employeeId) INTO maxEmployeeId FROM EMPLOYEE;

    -- 获取当前日期和时间
    SET currentDate = CURRENT_TIMESTAMP;

    -- 自增maxEmployeeId，并将其赋值给新插入的记录
    SET maxEmployeeId = IFNULL(maxEmployeeId + 1, 1);

    -- 插入员工信息到EMPLOYEE表
    INSERT INTO EMPLOYEE (employeeId, employeeName, employeePhoneNumber) VALUES (maxEmployeeId, pEmployeeName, pEmployeePhoneNumber);

    -- 插入员工薪资信息到EMPLOYEEPAYROLL表
    INSERT INTO EMPLOYEEPAYROLL (employeeId, amount, transactionDate) VALUES (maxEmployeeId, 0, currentDate);
		
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for create_hotel_order
-- ----------------------------
DROP PROCEDURE IF EXISTS `create_hotel_order`;
delimiter ;;
CREATE PROCEDURE `create_hotel_order`(IN p_roomId INT,
    IN p_customerId INT)
BEGIN
    DECLARE v_maxOrderId INT;
    DECLARE v_roomPrice DECIMAL(10, 2);
    DECLARE v_newOrderId INT;
    DECLARE v_roomStateId INT;
    
    -- 查询房间的roomStateId
    SELECT roomStateId INTO v_roomStateId
    FROM ROOM_STATE
    WHERE roomId = p_roomId;
    
    -- 检查房间是否已被占用
    IF v_roomStateId <> 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '房间已被占用';
    ELSE
        -- 获取当前HOTELORDER表中的最大orderId
        SET v_maxOrderId = (SELECT MAX(orderId) FROM HOTELORDER);
        
        -- 计算新的orderId
        SET v_newOrderId = IFNULL(v_maxOrderId, 0) + 1;
        
        -- 在HOTELORDER表中生成新订单
        INSERT INTO HOTELORDER (roomId, customerId)
        VALUES (p_roomId, p_customerId);
        
        -- 获取房间价格
        SET v_roomPrice = (SELECT roomPrice FROM ROOM WHERE roomId = p_roomId);
        
        -- 在FINANCE表中生成新记录
        INSERT INTO FINANCE (orderId, amount)
        VALUES (v_newOrderId, v_roomPrice);
        
        -- 更新ROOM_STATE表中的roomStateId
        UPDATE ROOM_STATE
        SET roomStateId = 1
        WHERE roomId = p_roomId;
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for update_employee_salary
-- ----------------------------
DROP PROCEDURE IF EXISTS `update_employee_salary`;
delimiter ;;
CREATE PROCEDURE `update_employee_salary`(IN pEmployeeId INT, IN pAmount DECIMAL(10,2))
BEGIN
    DECLARE currentDate DATETIME;

    -- 获取当前日期和时间
    SET currentDate = CURRENT_TIMESTAMP;

    -- 更新员工工资信息
    UPDATE EMPLOYEEPAYROLL
    SET amount = pAmount,
        transactionDate = currentDate
    WHERE employeeId = pEmployeeId;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for update_finance_amount
-- ----------------------------
DROP PROCEDURE IF EXISTS `update_finance_amount`;
delimiter ;;
CREATE PROCEDURE `update_finance_amount`(IN pFinanceId INT, IN pAmount DECIMAL(10,2))
BEGIN
    -- 更新金额和交易日期
    UPDATE FINANCE
    SET amount = pAmount,
        transactionDate = CURRENT_TIMESTAMP
    WHERE financeId = pFinanceId;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for update_order_type
-- ----------------------------
DROP PROCEDURE IF EXISTS `update_order_type`;
delimiter ;;
CREATE PROCEDURE `update_order_type`(IN pOrderId INT, IN pNewOrderTypeId INT)
BEGIN
    DECLARE currentDate DATETIME;
    
    -- 获取当前日期和时间
    SET currentDate = CURRENT_TIMESTAMP;
    
    -- 更新订单类型
    UPDATE HOTELORDER
    SET orderTypeId = pNewOrderTypeId,
        orderUpdateDate = currentDate
    WHERE orderId = pOrderId;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table CUSTOMER
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_generate_customerId`;
delimiter ;;
CREATE TRIGGER `tr_generate_customerId` BEFORE INSERT ON `CUSTOMER` FOR EACH ROW BEGIN
    DECLARE max_customerId INT;
    SET max_customerId = (SELECT MAX(customerId) FROM CUSTOMER);
    IF max_customerId IS NULL THEN
        SET max_customerId = 0;
    END IF;
    SET NEW.customerId = max_customerId + 1;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table CUSTOMER
-- ----------------------------
DROP TRIGGER IF EXISTS `tr_generate_uuid`;
delimiter ;;
CREATE TRIGGER `tr_generate_uuid` BEFORE INSERT ON `CUSTOMER` FOR EACH ROW BEGIN
    DECLARE max_uuid INT;
    SET max_uuid = (SELECT MAX(uuid) FROM CUSTOMER);
    IF max_uuid IS NULL THEN
        SET max_uuid = 20001;
    END IF;
    SET NEW.uuid = max_uuid + 1;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table EMPLOYEE
-- ----------------------------
DROP TRIGGER IF EXISTS `employee_increment_trigger`;
delimiter ;;
CREATE TRIGGER `employee_increment_trigger` BEFORE INSERT ON `EMPLOYEE` FOR EACH ROW BEGIN
    DECLARE maxEmployeeId INT;
    DECLARE maxUuid INT;
    
    -- 获取当前最大的employeeId值
    SELECT MAX(employeeId) INTO maxEmployeeId FROM EMPLOYEE;

    -- 获取当前最大的uuid值
    SELECT MAX(uuid) INTO maxUuid FROM EMPLOYEE;
    
    -- 自增maxEmployeeId，并将其赋值给新插入的记录
    SET NEW.employeeId = IFNULL(maxEmployeeId + 1, 1);

    -- 自增maxUuid，并将其赋值给新插入的记录
    SET NEW.uuid = IFNULL(maxUuid + 1, 1);
    
    -- 设置employeeRoleId的默认值为1
    IF NEW.employeeRoleId IS NULL THEN
        SET NEW.employeeRoleId = 1;
    END IF;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table FINANCE
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_generate_financeId`;
delimiter ;;
CREATE TRIGGER `trg_generate_financeId` BEFORE INSERT ON `FINANCE` FOR EACH ROW BEGIN
    DECLARE maxFinanceId INT;
    SET maxFinanceId = (SELECT MAX(financeId) FROM FINANCE);
    IF maxFinanceId IS NULL THEN
        SET NEW.financeId = 1;
    ELSE
        SET NEW.financeId = maxFinanceId + 1;
    END IF;
    SET NEW.transactionDate = CURRENT_TIMESTAMP;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table HOTELORDER
-- ----------------------------
DROP TRIGGER IF EXISTS `trg_generate_orderId`;
delimiter ;;
CREATE TRIGGER `trg_generate_orderId` BEFORE INSERT ON `HOTELORDER` FOR EACH ROW BEGIN
    DECLARE maxOrderId INT;
    SET maxOrderId = (SELECT MAX(orderId) FROM HOTELORDER);
    IF maxOrderId IS NULL THEN
        SET NEW.orderId = 1;
    ELSE
        SET NEW.orderId = maxOrderId + 1;
    END IF;
    SET NEW.orderCreateDate = CURRENT_TIMESTAMP;
    SET NEW.orderUpdateDate = CURRENT_TIMESTAMP;
    SET NEW.orderTypeId = 1;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

叽里咕噜qiu

关注

2
点赞
踩
23

收藏

觉得还不错? 一键收藏
打赏
1
评论
酒店管理数据库 MySQL 代码介绍（课程设计）

本文介绍了一个 MySQL 数据库的代码片段，包含了创建表、插入数据、创建视图、存储过程和触发器等功能。通过使用这些代码，可以轻松管理和操作数据库中的数据。希望这些示例能对你在开发 MySQL 数据库应用时有所帮助！
复制链接

扫一扫