编辑代码

-- 创建数据库（以姓名拼音命名）
CREATE DATABASE zhouyikuan CHARACTER SET utf8mb4;
USE zhouyikuan;

-- 创建学生表（包含所有题目要求的字段和约束）
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_no VARCHAR(20) NOT NULL COMMENT '学号',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    gender CHAR(1) DEFAULT '男' COMMENT '性别',
    age INT COMMENT '年龄',
    id_card VARCHAR(18) NOT NULL COMMENT '身份证号',
    class VARCHAR(30) COMMENT '班级',
    enroll_date DATE DEFAULT CURRENT_DATE COMMENT '入学日期',
    UNIQUE KEY uk_student_unique (student_no, id_card) COMMENT '学号和身份证联合唯一约束'
) COMMENT '学生信息表';

-- 创建课程表
CREATE TABLE course (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID',
    name VARCHAR(50) NOT NULL COMMENT '课程名称',
    teacher VARCHAR(20) COMMENT '授课教师',
    classroom VARCHAR(20) COMMENT '教室',
    semester VARCHAR(20) COMMENT '学期'
) COMMENT '课程信息表';

-- 创建成绩表（添加了必要的外键关联）
CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '成绩ID',
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    regular_score DECIMAL(5, 2) COMMENT '平时成绩',
    exam_score DECIMAL(5, 2) COMMENT '考试成绩',
    total_score DECIMAL(5, 2) COMMENT '总评成绩',
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE,
    UNIQUE KEY uk_score_unique (student_id, course_id) COMMENT '学生和课程联合唯一约束'
) COMMENT '学生成绩表';

-- 插入学生测试数据（5条记录）
INSERT INTO student (student_no, name, gender, age, id_card, class, enroll_date) VALUES
('20230101', '张明', '男', 20, '110101200301011234', '计算机2101班', '2023-09-01'),
('20230102', '李华', '女', 19, '110101200401021235', '计算机2101班', '2023-09-01'),
('20230103', '王强', '男', 21, '110101200201031236', '计算机2102班', '2023-09-01'),
('20230104', '赵雪', '女', 20, '110101200302041237', '计算机2102班', '2023-09-01'),
('20230105', '刘芳', '女', 22, '110101200103051238', '计算机2103班', '2023-09-01');

-- 插入课程测试数据（5门课程）
INSERT INTO course (name, teacher, classroom, semester) VALUES
('数据库系统原理', '张教授', '逸夫楼A101', '2023-2024学年第一学期'),
('数据结构与算法', '李教授', '逸夫楼A201', '2023-2024学年第一学期'),
('操作系统', '王教授', '逸夫楼B101', '2023-2024学年第一学期'),
('计算机网络', '赵教授', '逸夫楼B201', '2023-2024学年第一学期'),
('软件工程', '刘教授', '逸夫楼C101', '2023-2024学年第一学期');

-- 插入成绩测试数据（每个学生3门课程成绩）
INSERT INTO score (student_id, course_id, regular_score, exam_score, total_score) VALUES
-- 学生1的3门课程成绩
(1, 1, 85.50, 90.00, 88.50),
(1, 2, 78.00, 82.50, 80.00),
(1, 3, 92.00, 88.50, 90.00),
-- 学生2的3门课程成绩
(2, 2, 88.00, 91.50, 90.00),
(2, 3, 76.50, 82.00, 80.00),
(2, 4, 90.00, 85.50, 87.00),
-- 学生3的3门课程成绩
(3, 1, 82.00, 78.50, 80.00),
(3, 3, 91.00, 88.00, 89.00),
(3, 5, 85.50, 90.50, 88.00),
-- 学生4的3门课程成绩
(4, 1, 90.00, 92.50, 91.50),
(4, 4, 87.00, 85.00, 86.00),
(4, 5, 78.50, 82.00, 80.50),
-- 学生5的3门课程成绩
(5, 2, 85.00, 88.00, 87.00),
(5, 3, 92.00, 90.50, 91.00),
(5, 5, 88.50, 91.00, 90.00);

-- 查询验证数据
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM score;