--
-- Host: 127.0.0.1    Database: staff_ms
-- ------------------------------------------------------


--
-- Current Database: `staff_ms`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `staff_ms` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `staff_ms`;

--
-- Table structure for table `department`
--

CREATE TABLE `department` (
  `dept_id` int NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(50) NOT NULL,
  `location` varchar(100) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gb2312 COMMENT='部门信息表';

--
-- Dumping data for table `department`
--

INSERT INTO `department` (`dept_id`, `dept_name`, `location`, `create_time`, `update_time`) VALUES (1,'人力资源部','总部大楼3楼','2025-04-22 08:48:53','2025-04-22 08:48:53'),(2,'财务部','总部大楼3楼','2025-04-22 08:48:53','2025-04-22 08:48:53'),(3,'技术研发部','科技园A座','2025-04-22 08:48:53','2025-04-22 08:48:53'),(4,'市场营销部','总部大楼3楼','2025-04-22 08:48:53','2025-04-22 08:48:53'),(5,'客户服务部','总部大楼6楼','2025-04-22 08:48:53','2025-04-27 08:20:25');

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
  `emp_id` int NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(50) NOT NULL,
  `gender` enum('男','女') DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `hire_date` date NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  `manager_id` int DEFAULT NULL COMMENT '所属领导',
  `salary` decimal(10,2) DEFAULT NULL,
  `status` enum('在职','离职') DEFAULT NULL COMMENT '在职、离职',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `phone` (`phone`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gb2312 COMMENT='员工信息表';

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`emp_id`, `emp_name`, `gender`, `birth_date`, `hire_date`, `email`, `phone`, `dept_id`, `manager_id`, `salary`, `status`, `create_time`, `update_time`) VALUES (1,'张伟','男','1985-05-15','2015-06-10','zhangwei@company.com','13800138001',1,NULL,22000.00,'在职','2025-04-22 08:48:54','2025-04-22 08:48:54'),(2,'李娜','女','1990-08-22','2018-03-15','lina@company.com','13800138002',3,1,15000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(3,'王芳','女','1992-11-05','2019-07-20','wangfang@company.com','13800138003',2,1,8500.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(4,'赵勇','男','1988-03-30','2016-09-12','zhaoyong@company.com','13800138004',3,1,18000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(5,'钱敏','女','1995-07-18','2020-01-05','qianmin@company.com','13800138005',4,1,7500.00,'离职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(6,'孙强','男','1983-12-10','2014-11-08','sunqiang@company.com','13800138006',5,1,25000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(7,'周雪','女','1991-09-25','2017-04-30','zhouxue@company.com','13800138007',3,1,12000.00,'离职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(8,'吴刚','男','1993-02-14','2019-08-15','wugang@company.com','13800138008',1,1,6500.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(9,'郑丽','女','1987-06-08','2015-12-01','zhengli@company.com','13800138009',4,1,28000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(10,'王磊','男','1994-04-20','2020-05-18','wanglei@company.com','13800138010',2,2,9500.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(11,'李静','女','1989-10-12','2016-07-22','lijing@company.com','13800138011',3,2,16000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:22'),(12,'张强','男','1996-01-30','2021-02-14','zhangqiang@company.com','13800138012',5,2,7000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:21'),(13,'刘洋','女','1986-07-07','2014-09-01','liuyang@company.com','13800138013',3,2,30000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:21'),(14,'周一本','男','1997-03-15','2022-06-10','chenming@company.com','13800138014',NULL,2,11000.00,'在职','2025-04-22 08:48:54','2025-04-23 07:48:21'),(15,'杨雪','女','1998-08-08','2023-01-05','yangxue@company.com','13800138015',1,2,5800.00,'离职','2025-04-22 08:48:54','2025-04-23 07:48:22');

--
-- Temporary view structure for view `employee_info`
--

/*!50001 DROP VIEW IF EXISTS `employee_info`*/;
/*!50001 CREATE VIEW `employee_info` AS SELECT 
 1 AS `emp_id`,
 1 AS `emp_name`,
 1 AS `dept_name`*/;

--
-- Table structure for table `salary_grade`
--

CREATE TABLE `salary_grade` (
  `grade_id` int NOT NULL AUTO_INCREMENT,
  `grade_name` varchar(20) NOT NULL,
  `min_salary` decimal(10,2) NOT NULL,
  `max_salary` decimal(10,2) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`grade_id`),
  CONSTRAINT `chk_salary_range` CHECK ((`max_salary` > `min_salary`))
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gb2312 COMMENT='薪资等级表';

--
-- Dumping data for table `salary_grade`
--

INSERT INTO `salary_grade` (`grade_id`, `grade_name`, `min_salary`, `max_salary`, `description`, `create_time`) VALUES (1,'一级',20000.00,50000.00,'高级职位薪资范围','2025-04-22 08:48:54'),(2,'二级',10000.00,19999.99,'中级职位薪资范围','2025-04-22 08:48:54'),(3,'三级',5000.00,9999.99,'初级职位薪资范围','2025-04-22 08:48:54');

--
-- Dumping routines for database 'staff_ms'
--
/*!50003 DROP PROCEDURE IF EXISTS `calculate_sum` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `calculate_sum`(IN n INT, OUT sum INT)
BEGIN
    DECLARE i INT DEFAULT 1; 
    SET sum = 0;
    WHILE i <= 50 DO
        SET  sum = sum + i;
        SET i = i + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `even_number_sum` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `even_number_sum`(IN n INT)
BEGIN
    DECLARE result INT DEFAULT 0;
    sum:LOOP
        IF n < 0 THEN
            LEAVE sum;
        END IF;
        IF n % 2 = 0 THEN
            SET result = result + n;
            SET n := n - 2;
        ELSE
            SET n := n - 1;
        END IF;
    END LOOP sum;
    SELECT result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `even_number_sum2` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `even_number_sum2`(IN n INT)
BEGIN
    DECLARE result INT DEFAULT 0;
    sum:LOOP
        IF n < 0 THEN
            LEAVE sum;
        END IF;






        IF n % 2 = 0 THEN
            SET result = result + n;
            SET n := n - 1;
        ELSE
            ITERATE sum;
        END IF;
    END LOOP sum;
    SELECT result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `even_number_sum3` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `even_number_sum3`(IN n INT)
BEGIN
    DECLARE result INT DEFAULT 0;
    sum:LOOP
        IF n <= 0 THEN
            LEAVE sum;
        END IF;
        IF n % 2 = 0 THEN
            SET result = result + n;
        ELSE
            ITERATE sum;
        END IF;
        SET n := n - 1;
    END LOOP sum;
    SELECT result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `even_number_sum4` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `even_number_sum4`(IN n INT)
BEGIN
    DECLARE result INT DEFAULT 0;
    sum:LOOP
        IF n <= 0 THEN
            LEAVE sum;
        END IF;







        IF n % 2 = 1 THEN
            SET n := n - 1;
            iterate sum;
        END IF;
        SET result := result + n;
        SET n := n - 1;
    END LOOP sum;
    SELECT result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p1` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `p1`(n INT)
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    REPEAT
        SET result := result + i;
        SET i := i + 1;
    UNTIL i < n  END REPEAT;
    SELECT result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p2` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `p2`(n INT)
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    REPEAT
        SET result := result + i;
        SET i := i + 1;
    UNTIL i > n  END REPEAT;
    SELECT result;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `p3` */;
DELIMITER ;;
CREATE DEFINER=`jeanwaljean`@`%` PROCEDURE `p3`(IN n INT)
BEGIN





    DECLARE result INT DEFAULT 0;
    sum:LOOP
        IF n < 1 THEN
            LEAVE sum;
        END IF;
        SET result := result + n;
        SET n := n - 1;
    END LOOP sum;
    SELECT result;
END ;;
DELIMITER ;

--
-- Current Database: `staff_ms`
--

USE `staff_ms`;

--
-- Final view structure for view `employee_info`
--

/*!50001 DROP VIEW IF EXISTS `employee_info`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `employee_info` AS select `e`.`emp_id` AS `emp_id`,`e`.`emp_name` AS `emp_name`,`d`.`dept_name` AS `dept_name` from (`employee` `e` join `department` `d` on((`e`.`dept_id` = `d`.`dept_id`))) */;


