西电_2022春_数据库原理_实验一

实验内容及要求

1. 使用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为外码

   

2. 对各表进行增加、删除、修改属性操作

   ①　添加操作：

   对学生表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。

3. 建立索引

   为学生表Student在student_name上建立名为sname_index 的索引；为学院表College在college_id字段上按学院编号降序建立名为dept_index的索引。

4. 删除表操作

   删除开课表Course以及学习表Study，然后重新执行查询再次建立两表。

5. 添加数据

   将本学年自己课表的课程信息添加到该数据库中，每个表至少包含5条记录（如Student表(01, Zhang san, male, 20)）。

6. 查看每个表的详细信息以及所有记录信息。

实验步骤

运行环境：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');