90 lines
4.5 KiB
SQL
90 lines
4.5 KiB
SQL
-- 切换到test01_db数据库
|
||
USE stu01_db;
|
||
|
||
-- 1. 用户表（sys_user）：存储登录账号、密码、权限类型
|
||
DROP TABLE IF EXISTS sys_user;
|
||
CREATE TABLE sys_user (
|
||
id BIGINT AUTO_INCREMENT COMMENT '用户ID（主键）' PRIMARY KEY,
|
||
username VARCHAR(50) NOT NULL COMMENT '登录账号（唯一）',
|
||
password VARCHAR(50) NOT NULL COMMENT '登录密码（无需加密，测试用）',
|
||
user_type TINYINT NOT NULL COMMENT '权限类型：1-学生 2-教师 3-管理员',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
real_name VARCHAR(50) NOT NULL COMMENT '真实姓名'
|
||
) COMMENT '用户表' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 添加账号唯一索引
|
||
CREATE UNIQUE INDEX idx_sys_user_username ON sys_user(username);
|
||
|
||
-- 插入测试数据（管理员账号：admin/123456；学生账号：stu001/123456；教师账号：tea001/123456）
|
||
INSERT INTO sys_user (username, password, user_type) VALUES
|
||
('admin', '123456', 3),
|
||
('stu001', '123456', 1),
|
||
('tea001', '123456', 2);
|
||
|
||
-- 2. 学生表（student）：存储学生基础信息
|
||
DROP TABLE IF EXISTS student;
|
||
CREATE TABLE student (
|
||
id BIGINT AUTO_INCREMENT COMMENT '学生ID（主键）' PRIMARY KEY,
|
||
stu_no VARCHAR(8) NOT NULL COMMENT '学号（8位纯数字，唯一）',
|
||
name VARCHAR(20) NOT NULL COMMENT '学生姓名',
|
||
major VARCHAR(30) NOT NULL COMMENT '专业（仅允许：计算机/数学/英语）',
|
||
user_id BIGINT NOT NULL COMMENT '关联用户ID',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
-- 外键关联用户表
|
||
CONSTRAINT fk_student_user_id FOREIGN KEY (user_id) REFERENCES sys_user(id)
|
||
) COMMENT '学生表' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 添加学号唯一索引
|
||
CREATE UNIQUE INDEX idx_student_stu_no ON student(stu_no);
|
||
|
||
-- 插入测试数据（关联学生账号stu001的user_id，需先查询sys_user中stu001的id再替换，示例中假设id=2）
|
||
INSERT INTO student (stu_no, name, major, user_id) VALUES ('20250001', '张三', '计算机', 2);
|
||
|
||
-- 3. 教师表（teacher）：存储教师基础信息
|
||
DROP TABLE IF EXISTS teacher;
|
||
CREATE TABLE teacher (
|
||
id BIGINT AUTO_INCREMENT COMMENT '教师ID（主键）' PRIMARY KEY,
|
||
tea_no VARCHAR(8) NOT NULL COMMENT '教师编号（8位纯数字，唯一）',
|
||
name VARCHAR(20) NOT NULL COMMENT '教师姓名',
|
||
user_id BIGINT NOT NULL COMMENT '关联用户ID',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
-- 外键关联用户表
|
||
CONSTRAINT fk_teacher_user_id FOREIGN KEY (user_id) REFERENCES sys_user(id)
|
||
) COMMENT '教师表' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 添加教师编号唯一索引
|
||
CREATE UNIQUE INDEX idx_teacher_tea_no ON teacher(tea_no);
|
||
|
||
-- 插入测试数据（关联教师账号tea001的user_id，示例中假设id=3）
|
||
INSERT INTO teacher (tea_no, name, user_id) VALUES ('20250001', '李老师', 3);
|
||
|
||
-- 4. 课程表（course）：存储课程信息，关联授课教师
|
||
DROP TABLE IF EXISTS course;
|
||
CREATE TABLE course (
|
||
id BIGINT AUTO_INCREMENT COMMENT '课程ID（主键）' PRIMARY KEY,
|
||
course_name VARCHAR(50) NOT NULL COMMENT '课程名称（仅允许中文/数字/字母，长度2-20）',
|
||
tea_id BIGINT NOT NULL COMMENT '关联教师ID',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
-- 外键关联教师表（用于教师删除时的关联拦截）
|
||
CONSTRAINT fk_course_tea_id FOREIGN KEY (tea_id) REFERENCES teacher(id)
|
||
) COMMENT '课程表' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 插入测试数据（关联李老师的teacher_id，示例中假设id=1）
|
||
INSERT INTO course (course_name, tea_id) VALUES ('Java编程', 1);
|
||
|
||
-- 5. 学分表（score）：存储学生课程成绩，关联学生和课程
|
||
DROP TABLE IF EXISTS score;
|
||
CREATE TABLE score (
|
||
id BIGINT AUTO_INCREMENT COMMENT '学分ID（主键）' PRIMARY KEY,
|
||
stu_id BIGINT NOT NULL COMMENT '关联学生ID',
|
||
course_id BIGINT NOT NULL COMMENT '关联课程ID',
|
||
score INT NOT NULL COMMENT '分数（0-100）',
|
||
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
-- 外键关联学生表和课程表
|
||
CONSTRAINT fk_score_stu_id FOREIGN KEY (stu_id) REFERENCES student(id),
|
||
CONSTRAINT fk_score_course_id FOREIGN KEY (course_id) REFERENCES course(id)
|
||
) COMMENT '学分表' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
||
-- 插入测试数据（关联张三的student_id=1，Java编程的course_id=1）
|
||
INSERT INTO score (stu_id, course_id, score) VALUES (1, 1, 90);
|