实验内容及要求
-
使用SQL语句建立学生管理数据库（包括以下6个表），同时完善各表的相关完整性约束（其中标红的属性为主码，标蓝的属性为外码）
学院表College (college_id，college_name)
学生表Student (student_id，student_name，sex，college_id)
教师表Teacher (teacher_id，teacher_name，college_id)
课程表Course (course_id, course_name)
学习表Study (student_id，course_id，grade)
开课表Class (teacher_id，course_id)，teacher_id为外码，course_id为外码
-
对各表进行增加、删除、修改属性操作
① 添加操作：
对学生表Student添加date_of_birth和address字段，教师表Teacher增加sex字段，date_of_birth字段，对课程表Course增加先修课cpno和学分 credit字段。
② 删除操作：
删除学生表Student的address字段，删除教师表Teacher的date_of_birth字段。
③ 修改操作：
修改课程表Course中cpno字段名称为pre_course_id，修改学生表Student中student_name字段数据类型为字符串，长度为20。 -
建立索引
为学生表Student在student_name上建立名为sname_index 的索引；为学院表College在college_id字段上按学院编号降序建立名为dept_index的索引。 -
删除表操作
删除开课表Course以及学习表Study，然后重新执行查询再次建立两表。 -
添加数据
将本学年自己课表的课程信息添加到该数据库中，每个表至少包含5条记录（如Student表(01, Zhang san, male, 20)）。 -
查看每个表的详细信息以及所有记录信息。
实验步骤
运行环境：macOS Monterey 12.3.1
MySQL版本：8.0.28
终端：item2
1、 使用SQL语法建立基本表，并完善相关约束性条件。
CREATE TABLE College
(college_id CHAR(20) PRIMARY KEY,
college_name CHAR(20)
);
CREATE TABLE Student
(student_id CHAR(20) PRIMARY KEY,
student_name CHAR(20),
sex CHAR(2),
college_id CHAR(20),
FOREIGN KEY (college_id) REFERENCES College(college_id)
);
CREATE TABLE Teacher
(teacher_id CHAR(20) PRIMARY KEY,
teacher_name CHAR(20),
college_id CHAR(20),
FOREIGN KEY (college_id) REFERENCES College(college_id)
);
CREATE TABLE Course
(course_id CHAR(20) PRIMARY KEY,
course_name CHAR(20)
);
CREATE TABLE Study
(student_id CHAR(20) PRIMARY KEY,
course_id CHAR(20),
grade INT,
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
CREATE TABLE Class
(teacher_id CHAR(20),
course_id CHAR(20),
PRIMARY KEY(teacher_id, course_id),
FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
2、 对各表进行增加、删除、修改属性操作
ALTER TABLE Student ADD date_of_birth DATE;
ALTER TABLE Student ADD address CHAR(20);
ALTER TABLE Teacher ADD date_of_birth DATE;
ALTER TABLE Teacher ADD sex CHAR(2);
ALTER TABLE Course ADD cpno CHAR(20);
ALTER TABLE Course ADD credit SMALLINT;
ALTER TABLE Student DROP address;
ALTER TABLE Teacher DROP date_of_birth;
ALTER TABLE Course CHANGE cpno pre_course_id CHAR(20);
ALTER TABLE Student MODIFY COLUMN student_name CHAR(20);
3、 建立索引
CREATE UNIQUE INDEX sname_index ON Student(student_name);
CREATE UNIQUE INDEX dept_index ON College(college_id DESC);
经过以上操作之后的各个基本表的信息如下：
4、 删除表
DROP TABLE Class;
DROP TABLE Study;
5、 插入数据并且查看每个表的详细信息以及所有记录信息。
INSERT INTO College
VALUES ('1', 'CS');
INSERT INTO College
VALUES ('2', 'MA');
INSERT INTO College
VALUES ('3', 'IM');
INSERT INTO College
VALUES ('4', 'SCE');
INSERT INTO College
VALUES ('5', 'PI');
INSERT INTO Student
VALUES ('20009200281', 'PigStars', 'M', '1', '2002-06-01');
INSERT INTO Student
VALUES ('20009200282', 'Zhang', 'M', '2', '2002-06-01');
INSERT INTO Student
VALUES ('20009200283', 'Liu', 'F', '3', '2002-06-01');
INSERT INTO Student
VALUES ('20009200284', 'Sun', 'M', '4', '2002-06-01');
INSERT INTO Student
VALUES ('20009200285', 'Lin', 'F', '5', '2002-06-01');
INSERT INTO Teacher
VALUES ('1', 'Wang', '1', 'M');
INSERT INTO Teacher
VALUES ('2', 'Li', '2', 'F');
INSERT INTO Teacher
VALUES ('3', 'Ge', '3', 'M');
INSERT INTO Teacher
VALUES ('4', 'Zhao', '4', 'F');
INSERT INTO Teacher
VALUES ('5', 'Qian', '5', 'M');
INSERT INTO Course
VALUES ('1', 'C_program', '4', '5');
INSERT INTO Course
VALUES ('2', 'Art', '1', '5');
INSERT INTO Course
VALUES ('3', 'Java', '4', '5');
INSERT INTO Course
VALUES ('4', 'Python', '4', '5');
INSERT INTO Course(course_id, course_name, credit)
VALUES ('5', 'Math', '4');
INSERT INTO Study
VALUES ('20009200281', '1', '85');
INSERT INTO Study
VALUES ('20009200282', '2', '90');
INSERT INTO Study
VALUES ('20009200283', '3', '83');
INSERT INTO Study
VALUES ('20009200284', '4', '90');
INSERT INTO Study
VALUES ('20009200285', '5', '100');
INSERT INTO Class
VALUES ('1', '1');
INSERT INTO Class
VALUES ('2', '2');
INSERT INTO Class
VALUES ('3', '3');
INSERT INTO Class
VALUES ('4', '4');
INSERT INTO Class
VALUES ('5', '5');
