编辑代码

CREATE DATABASE IF NOT EXISTS niu CHARACTER SET utf8mb4;
 
USE niu;
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    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 (student_no, id_card)
 ) ;
 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 '学期'
 ) ;
 CREATE TABLE score (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '成绩ID',
    regular_score DECIMAL(5,2) COMMENT '平时成绩',
    exam_score DECIMAL(5,2) COMMENT '考试成绩',
    total_score DECIMAL(5,2) COMMENT '总评成绩');
    INSERT INTO student (student_no, name, gender, age, id_card, class) VALUES
 ('S20230001', '张三', DEFAULT, 18, '110101200001011234', '高三(1)班'),
 ('S20230002', '李四', '女', 17, '110101200101021235', '高三(2)班'),
 ('S20230003', '王五', DEFAULT, 19, '110101199912311236', '高三(1)班'),
 ('S20230004', '赵六', '女', 18, '110101200002011237', '高三(3)班'),
 ('S20230005', '陈七', DEFAULT, 17, '110101200103021238', '高三(2)班');
 INSERT INTO course (name, teacher, classroom, semester) VALUES
 ('高等数学', '张教授', '逸夫楼201', '2023秋季'),
 ('大学物理', '李教授', '实验楼102', '2023秋季'),
 ('数据结构', '王副教授', '信息楼303', '2023秋季'),
 ('计算机网络', '赵讲师', '工程馆404', '2023秋季'),
 ('数据库原理', '钱教授', '计算中心105', '2023秋季');
 INSERT INTO score (regular_score, exam_score, total_score) VALUES
(90.5, 88.0, 90.5*0.3 + 88.0*0.7),
 (85.0, 92.5, 85.0*0.3 + 92.5*0.7),
 (88.0, 85.5, 88.0*0.3 + 85.5*0.7),
 (92.0, 95.0, 92.0*0.3 + 95.0*0.7),
 (90.0, 93.5, 90.0*0.3 + 93.5*0.7),
 (95.0, 98.0, 95.0*0.3 + 98.0*0.7);
 SELECT * FROM student;
 SELECT * FROM score;
 SELECT * FROM course;