编辑代码

-- 创建数据库
CREATE DATABASE school_db;
-- 使用数据库
USE school_db;
-- 创建Student表
CREATE TABLE Student (
    Sno CHAR(3) NOT NULL PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    Ssex CHAR(2) NOT NULL,
    Sbirthday DATETIME,
    Class CHAR(5)
);

-- 创建Course表
CREATE TABLE Course (
    Cno CHAR(5) NOT NULL PRIMARY KEY,
    Cname VARCHAR(8) NOT NULL,
    Tno CHAR(3) NOT NULL
);

-- 创建Score表
CREATE TABLE Score (
    Sno CHAR(3) NOT NULL,
    Cno CHAR(5) NOT NULL,
    Degree DECIMAL(4,1),
    PRIMARY KEY (Sno, Cno)
);

-- 创建Teacher表
CREATE TABLE Teacher (
    Tno CHAR(3) NOT NULL PRIMARY KEY,
    Tname CHAR(4) NOT NULL,
    Tsex CHAR(2) NOT NULL,
    Tbirthday DATETIME,
    Prof CHAR(6),
    Depart VARCHAR(10) NOT NULL
);
-- 为Student表的Sname字段创建索引
CREATE INDEX idx_student_sname ON Student (Sname);

-- 为Course表的Cname字段创建索引
CREATE INDEX idx_course_cname ON Course (Cname);

-- 为Score表的Degree字段创建索引
CREATE INDEX idx_score_degree ON Score (Degree);

-- 为Teacher表的Tname字段创建索引
CREATE INDEX idx_teacher_tname ON Teacher (Tname);
-- 向Student表插入记录
INSERT INTO Student (Sno, Sname, Ssex, Sbirthday, Class)
VALUES ('001', '张三', '男', '2000-01-01 00:00:00', '一班');

-- 向Course表插入记录
INSERT INTO Course (Cno, Cname, Tno)
VALUES ('C001', '数学', 'T01');

-- 向Score表插入记录
INSERT INTO Score (Sno, Cno, Degree)
VALUES ('001', 'C001', 85.5);

-- 向Teacher表插入记录
INSERT INTO Teacher (Tno, Tname, Tsex, Tbirthday, Prof, Depart)
VALUES ('T01', '李老师', '男', '1980-01-01 00:00:00', '教授', '数学系');
-- 修改Student表,增加Political字段
ALTER TABLE Student
ADD COLUMN Political VARCHAR(20);

-- 修改Teacher表,增加Political字段
ALTER TABLE Teacher
ADD COLUMN Political VARCHAR(20);