/*
 Navicat Premium Dump SQL

 Source Server         : local-mysql8
 Source Server Type    : MySQL
 Source Server Version : 80039 (8.0.39)
 Source Host           : localhost:3306
 Source Schema         : dept_info

 Target Server Type    : MySQL
 Target Server Version : 80039 (8.0.39)
 File Encoding         : 65001

 Date: 24/05/2025 10:52:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for DEPT
-- ----------------------------
DROP TABLE IF EXISTS `DEPT`;
CREATE TABLE `DEPT` (
  `id` int NOT NULL COMMENT '部门ID',
  `name` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '部门名称',
  `workaddress` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '工作地点',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of DEPT
-- ----------------------------
BEGIN;
INSERT INTO `DEPT` (`id`, `name`, `workaddress`) VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `DEPT` (`id`, `name`, `workaddress`) VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `DEPT` (`id`, `name`, `workaddress`) VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `DEPT` (`id`, `name`, `workaddress`) VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

-- ----------------------------
-- Table structure for EMP
-- ----------------------------
DROP TABLE IF EXISTS `EMP`;
CREATE TABLE `EMP` (
  `id` int NOT NULL COMMENT '员工号',
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '员工名称',
  `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '职位',
  `managerid` int DEFAULT NULL COMMENT '上级员工号',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `salary` double(7,2) DEFAULT NULL COMMENT '月薪',
  `deptid` int DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of EMP
-- ----------------------------
BEGIN;
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (1234, 'TOM', 'CLERK', NULL, NULL, 2000.00, 50);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (1235, 'MARRY', 'CLERK', NULL, NULL, 4000.00, 50);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (1236, 'KIM', 'CLERK', NULL, NULL, 3000.00, 50);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, 20);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 30);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 30);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, 20);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 30);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, 30);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, 10);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19 00:00:00', 3000.00, 20);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, 10);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 30);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100.00, 20);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, 30);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, 20);
INSERT INTO `EMP` (`id`, `name`, `job`, `managerid`, `birthday`, `salary`, `deptid`) VALUES (7934, 'MILLER', 'MANAGER', 7782, '1982-01-23 00:00:00', 1300.00, 10);
COMMIT;

-- ----------------------------
-- Table structure for SALGRADE
-- ----------------------------
DROP TABLE IF EXISTS `SALGRADE`;
CREATE TABLE `SALGRADE` (
  `GRADE` int DEFAULT NULL COMMENT '职级',
  `LOSAL` int DEFAULT NULL COMMENT '最低月薪',
  `HISAL` int DEFAULT NULL COMMENT '最高月薪'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of SALGRADE
-- ----------------------------
BEGIN;
INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`) VALUES (1, 700, 1200);
INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`) VALUES (2, 1201, 1400);
INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`) VALUES (3, 1401, 2000);
INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`) VALUES (4, 2001, 3000);
INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`) VALUES (5, 3001, 9999);
COMMIT;

-- ----------------------------
-- View structure for dept_emp_info
-- ----------------------------
DROP VIEW IF EXISTS `dept_emp_info`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `dept_emp_info` AS select `emp`.`deptid` AS `deptid`,`emp`.`job` AS `job`,json_arrayagg(`emp`.`name`) AS `json_arrayagg(name)`,avg(`emp`.`salary`) AS `avg(salary)` from `emp` group by `emp`.`deptid`,`emp`.`job`;

SET FOREIGN_KEY_CHECKS = 1;
