oracle教学示例表mysql改进版

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `deptno` int(0) NULL DEFAULT NULL,
  `dname` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `loc` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `leaderno` int(0) NULL DEFAULT NULL COMMENT '负责人工号'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (10, '会计部', '纽约', 7369);
INSERT INTO `dept` VALUES (20, '研究部', '达拉斯', 7839);
INSERT INTO `dept` VALUES (30, '销售部', '芝加哥\n\n', 7499);
INSERT INTO `dept` VALUES (40, '操作部', '波士顿', 7369);

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `empno` int(0) NOT NULL,
  `phone` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ename` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `cname` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `education` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '教育程度|0|无|1|中专|2|大专|3|本科|4|博士及以上',
  `job` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `mgr` int(0) NULL DEFAULT NULL,
  `hiredate` date NULL DEFAULT NULL,
  `sal` double(7, 2) NULL DEFAULT NULL,
  `comm` double(7, 2) NULL DEFAULT NULL,
  `deptno` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (7369, '15928555774', 'SMITH', '许安', '0', '职员', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, '19900000001', 'ALLEN', '宋良', '1', '销售', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, '19900000002', 'WARD', '万兰', '2', '销售', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, '19900000003', 'JONES', '俞明', '2', '经理', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, '19900000004', 'MARTIN', '谢雯', '3', '销售', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7698, '19900000005', 'BLAKE', '康青', '3', '经理', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, '19900000006', 'CLARK', '赵婵', '4', '经理', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, '19900000007', 'SCOTT', '刘芸', '3', '化验', 7566, '1982-12-09', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, '19900000008', 'KING', '白鹄', '4', '总裁', NULL, '1981-11-17', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, '19900000009', 'TURNER', '贾彩', '1', '销售', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, '19900000010', 'ADAMS', '马丽', '0', '职员', 7788, '1983-01-12', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, '19900000011', 'JAMES', '秦栋', '1', '职员', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, '19900000012', 'FORD', '张三', '3', '化验', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, '19900000013', 'MILLER', '李四', NULL, '职员', 7782, '1982-01-23', 1300.00, NULL, 10);

-- ----------------------------
-- Table structure for report_dept
-- ----------------------------
DROP TABLE IF EXISTS `report_dept`;
CREATE TABLE `report_dept`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `belong_date` date NULL DEFAULT NULL COMMENT '统计日期',
  `empno` int(0) NOT NULL,
  `cname` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `education` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `deptno` int(0) NULL DEFAULT NULL,
  `DNAME` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `leaderno` int(0) NULL DEFAULT NULL COMMENT '负责人工号',
  `leader_name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of report_dept
-- ----------------------------
INSERT INTO `report_dept` VALUES (1, '2022-02-27', 7369, '许安', '-', 20, '研究部', 7839, '白鹄', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (2, '2022-02-27', 7499, '宋良', '中专', 30, '销售部', 7499, '宋良', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (3, '2022-02-27', 7521, '万兰', '大专', 30, '销售部', 7499, '宋良', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (4, '2022-02-27', 7566, '俞明', '大专', 20, '研究部', 7839, '白鹄', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (5, '2022-02-27', 7654, '谢雯', '本科', 30, '销售部', 7499, '宋良', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (6, '2022-02-27', 7698, '康青', '本科', 30, '销售部', 7499, '宋良', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (7, '2022-02-27', 7782, '赵婵', '博士及以上', 10, '会计部', 7369, '许安', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (8, '2022-02-27', 7788, '刘芸', '本科', 20, '研究部', 7839, '白鹄', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (9, '2022-02-27', 7839, '白鹄', '博士及以上', 10, '会计部', 7369, '许安', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (10, '2022-02-27', 7844, '贾彩', '中专', 30, '销售部', 7499, '宋良', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (11, '2022-02-27', 7876, '马丽', '-', 20, '研究部', 7839, '白鹄', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (12, '2022-02-27', 7900, '秦栋', '中专', 30, '销售部', 7499, '宋良', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (13, '2022-02-27', 7902, '张三', '本科', 20, '研究部', 7839, '白鹄', '2022-02-27 13:04:38');
INSERT INTO `report_dept` VALUES (14, '2022-02-27', 7934, '李四', '(空值)', 10, '会计部', 7369, '许安', '2022-02-27 13:04:38');

SET FOREIGN_KEY_CHECKS = 1;