sql
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50722
Source Host : localhost:3306
Source Schema : 教学实例
Target Server Type : MySQL
Target Server Version : 50722
File Encoding : 65001
Date: 25/11/2025 19:25:46
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- 创建 教学实例 数据库
-- ----------------------------
drop DATABASE if EXISTS 教学实例;
create database if not exists 教学实例 default charset utf8mb4 collate utf8mb4_bin;
use `教学实例`;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`Cno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`Cname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`Tno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
INSERT INTO `course` VALUES ('04', '计算机', NULL);
-- ----------------------------
-- Table structure for grades
-- ----------------------------
DROP TABLE IF EXISTS `grades`;
CREATE TABLE `grades` (
`grade_level` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`lowest_sc` int(11) NULL DEFAULT NULL,
`highest_sc` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of grades
-- ----------------------------
INSERT INTO `grades` VALUES ('优秀', 90, 100);
INSERT INTO `grades` VALUES ('良好', 75, 89);
INSERT INTO `grades` VALUES ('及格', 60, 74);
INSERT INTO `grades` VALUES ('不及格', 0, 59);
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`Sno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`Cno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`score` decimal(5, 1) NULL DEFAULT NULL,
PRIMARY KEY (`Sno`, `Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('001', '01', 80.0);
INSERT INTO `sc` VALUES ('001', '02', 90.0);
INSERT INTO `sc` VALUES ('001', '03', 99.0);
INSERT INTO `sc` VALUES ('002', '01', 70.0);
INSERT INTO `sc` VALUES ('002', '02', 60.0);
INSERT INTO `sc` VALUES ('002', '03', 80.0);
INSERT INTO `sc` VALUES ('003', '01', 80.0);
INSERT INTO `sc` VALUES ('003', '02', 80.0);
INSERT INTO `sc` VALUES ('003', '03', 80.0);
INSERT INTO `sc` VALUES ('004', '01', 50.0);
INSERT INTO `sc` VALUES ('004', '02', 30.0);
INSERT INTO `sc` VALUES ('004', '03', 20.0);
INSERT INTO `sc` VALUES ('005', '01', 76.0);
INSERT INTO `sc` VALUES ('005', '02', 87.0);
INSERT INTO `sc` VALUES ('006', '01', 31.0);
INSERT INTO `sc` VALUES ('006', '03', 34.0);
INSERT INTO `sc` VALUES ('007', '02', 89.0);
INSERT INTO `sc` VALUES ('007', '03', 98.0);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`Sno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`Sname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`Sbirth` date NULL DEFAULT NULL,
`IDNum` char(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`Ssex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`sage` tinyint(3) UNSIGNED NULL DEFAULT NULL,
`sbirthplace` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`class` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('001', '赵雷', '2008-01-01', '565726200801016857', '男', 16, '成都', '01');
INSERT INTO `student` VALUES ('002', '钱电', '2007-12-21', '947421200712215154', '男', 16, '武汉', '03');
INSERT INTO `student` VALUES ('003', '孙风', '2008-05-20', '974716200805204435', '男', 16, '北京', '03');
INSERT INTO `student` VALUES ('004', '李云', '2008-08-06', '483017200808067839', '男', 16, '上海', '01');
INSERT INTO `student` VALUES ('005', '周梅', '2009-12-01', '593405200912010547', '女', 14, '武汉', '03');
INSERT INTO `student` VALUES ('006', '吴兰', '2008-03-01', '899625200803013404', '女', 16, '广州', '03');
INSERT INTO `student` VALUES ('007', '郑竹', '2007-07-01', '750003200707015222', '女', 17, '武汉', '03');
INSERT INTO `student` VALUES ('008', '王菊', '2008-01-20', '645789200801203360', '女', 16, '北京', '03');
INSERT INTO `student` VALUES ('009', '张峰', '2008-08-01', '287892200808013057', '男', 16, '武汉', '01');
INSERT INTO `student` VALUES ('010', '刘嘉伦', '2007-07-06', '713358200707063251', '男', 17, '北京', '03');
INSERT INTO `student` VALUES ('011', '赵诗涵', '2007-08-06', '244734200708067082', '女', 17, '北京', '02');
INSERT INTO `student` VALUES ('012', '卢云熙', '2008-12-09', '636875200812092959', '男', 15, '东莞', '01');
INSERT INTO `student` VALUES ('013', '方安琪', '2009-09-14', '920042200909147807', '女', 15, '成都', '02');
INSERT INTO `student` VALUES ('014', '范云熙', '2009-03-09', '301819200903093370', '男', 15, '北京', '01');
INSERT INTO `student` VALUES ('015', '邵嘉伦', '2007-01-14', '613502200701149271', '男', 17, '中山', '01');
INSERT INTO `student` VALUES ('016', '彭杰宏', '2007-11-18', '573763200711183410', '男', 16, '东莞', '01');
INSERT INTO `student` VALUES ('017', '常宇宁', '2007-12-23', '218999200712239310', '男', 16, '上海', '02');
INSERT INTO `student` VALUES ('018', '江致远', '2006-10-19', '599509200610198651', '男', 18, '成都', '02');
INSERT INTO `student` VALUES ('019', '黄詩涵', '2007-08-28', '059310200708287766', '女', 17, '北京', '01');
INSERT INTO `student` VALUES ('020', '许杰宏', '2007-09-05', '801342200709050671', '男', 17, '中山', '03');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`Tno` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '教师ID',
`Tname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '教师姓名',
`entrydate` date NULL DEFAULT NULL COMMENT '教师入职时间',
PRIMARY KEY (`Tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张老师', '2017-08-30');
INSERT INTO `teacher` VALUES ('02', '李老师', '2023-07-25');
INSERT INTO `teacher` VALUES ('03', '王老师', '2021-06-11');
INSERT INTO `teacher` VALUES ('04', '赵老师', '2024-08-01');
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '部门名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '部门表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '研发部');
INSERT INTO `dept` VALUES (2, '市场部');
INSERT INTO `dept` VALUES (3, '财务部');
INSERT INTO `dept` VALUES (4, '销售部');
INSERT INTO `dept` VALUES (5, '总经办');
INSERT INTO `dept` VALUES (6, '人事部');
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`job` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '职位',
`salary` int(11) NULL DEFAULT NULL COMMENT '薪资',
`entrydate` date NULL DEFAULT NULL COMMENT '入职时间',
`managerid` int(11) NULL DEFAULT NULL COMMENT '直属领导ID',
`dept_id` int(11) NULL DEFAULT NULL COMMENT '部门ID',
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_emp_dept_id`(`dept_id`) USING BTREE,
CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '员工表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5);
INSERT INTO `emp` VALUES (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1);
INSERT INTO `emp` VALUES (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1);
INSERT INTO `emp` VALUES (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1);
INSERT INTO `emp` VALUES (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1);
INSERT INTO `emp` VALUES (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);
INSERT INTO `emp` VALUES (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3);
INSERT INTO `emp` VALUES (8, '周芷若', 19, '会计', 8000, '2006-06-02', 7, 3);
INSERT INTO `emp` VALUES (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3);
INSERT INTO `emp` VALUES (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2);
INSERT INTO `emp` VALUES (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2);
INSERT INTO `emp` VALUES (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2);
INSERT INTO `emp` VALUES (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2);
INSERT INTO `emp` VALUES (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4);
INSERT INTO `emp` VALUES (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4);
INSERT INTO `emp` VALUES (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4);
INSERT INTO `emp` VALUES (17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);
SET FOREIGN_KEY_CHECKS = 1;