CREATE DATABASE school_db;
USE school_db;
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)
);
CREATE TABLE Course (
Cno CHAR(5) NOT NULL PRIMARY KEY,
Cname VARCHAR(8) NOT NULL,
Tno CHAR(3) NOT NULL
);
CREATE TABLE Score (
Sno CHAR(3) NOT NULL,
Cno CHAR(5) NOT NULL,
Degree DECIMAL(4,1),
PRIMARY KEY (Sno, Cno)
);
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
);
CREATE INDEX idx_student_sname ON Student (Sname);
CREATE INDEX idx_course_cname ON Course (Cname);
CREATE INDEX idx_score_degree ON Score (Degree);
CREATE INDEX idx_teacher_tname ON Teacher (Tname);
INSERT INTO Student (Sno, Sname, Ssex, Sbirthday, Class)
VALUES ('001', '张三', '男', '2000-01-01 00:00:00', '一班');
INSERT INTO Course (Cno, Cname, Tno)
VALUES ('C001', '数学', 'T01');
INSERT INTO Score (Sno, Cno, Degree)
VALUES ('001', 'C001', 85.5);
INSERT INTO Teacher (Tno, Tname, Tsex, Tbirthday, Prof, Depart)
VALUES ('T01', '李老师', '男', '1980-01-01 00:00:00', '教授', '数学系');
ALTER TABLE Student
ADD COLUMN Political VARCHAR(20);
ALTER TABLE Teacher
ADD COLUMN Political VARCHAR(20);