（一）数据库建表、修改表和索引实验练习

一、实验目的与要求

(1)理解数据库SQL语句，能够熟练运用SQL语句创建基本表，并修改和删除基本表；

(2)熟悉数据库的数据更新操作，能够运用SQL语句执行对数据库插做简单的插入、修改、删除操作。

二、实验重点、难点

(1)创建数据库、基本表；

(2)插入、修改和删除数据的SQL语句；

(3)部分内容课本没有涉及到，需要自学。

三、实验内容

1、建立基本表：在【学生课程数据库】中建立3张表：Student、Course和SC。

(1)创建学生表Student

create  table  Student(

Sno  char(12)  primary  key,

Sname  char(8)  unique,

Age  smallint，

Sex  char(2))

 (2)创建课程表course
create  table  course(

Cno   char(6)

Cname  char(20)  not  null,

Teacher  char(8))   /*授课教师*/

(3)创建学生选课表sc
create  table  SC(

Sno  char(12),

Cno   char（4）,

Grade  smallint,

Primary  key(sno,cno));

2、修改基本表
  (1)在student表中加入属性Sdept（char型）

(2)在SC表中，建立两个外码约束，Sno是外码，被参照表是Student（sno）；Cno是外码，被参照表是Course（Cno）。

(3)在Student表中插入以下记录，验证【实体完整性】是否起作用。PS:插入、删除语句需要运行至消息框提示：“1行受影响”为止。

insert   into  student  values(’2022010201’,'张天',20,'男','计算机科学与技术')

insert   into  student  values(’2022010201’,'高磊',20,'男','计算机科学与技术')

然后依次输入’2022010202’、’2022010203’ 、

’2022010204’ 、’2022010205’这4条记录。

(4)在SC表中插入

(’2022010201’,‘1001’,85) (’2022010201’,‘1002’,90) (’2022010206’,‘1001’,78)这3条记录，验证【参照完整性】是否起作用。

(5)如果在student表中删除’2022010201’这条记录呢？

(6)在Student表中增加”学生入学”列，其数据类型是日期型；

(7)将Sex的数据类型由字符型修改成整型；

(8)增加Course表中的Cname的UNIQUE约束；

(9)将SC表中的Grade字段定义为百分制；

(10)删除Course表中的UNIQUE约束、SC表中的CHECK约束。

(11)试着删除Course表中的PRIMARY  KEY约束，能否删掉？

3、建立索引
  (1) 在student表上建立关于属性Sno的唯一索引。
  (2)在course表上建立关于Cno的唯一索引。

解答：

1.创建学生表Student

（1）建立基本表

CREATE  TABLE  Student(
Sno  CHAR(12)  PRIMARY KEY,
Sname  CHAR(8)  UNIQUE,
Age  SMALLINT，
Sex  CHAR(2));

（2）创建课程表course

CREATE TABLE course(
Cno CHAR(6),
Cname CHAR(20) NOT NULL,
Teacher CHAR(8));

（3）创建学生选课表SC

CREATE TABLE SC(
Sno CHAR(12),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno));

2.修改基本表

(1)在student表中加入属性Sdept（char型）

ALTER TABLE student ADD Sdept CHAR;

(2)在SC表中，建立两个外码约束，Sno是外码，被参照表是Student（sno）；Cno是外码，被参照表是Course（Cno）

ALTER TABLE SC
ADD CONSTRAINT fk_SC_Student
FOREIGN KEY (Sno) REFERENCES Student (Sno);
ALTER TABLE SC
ADD CONSTRAINT fk_SC_Course
FOREIGN KEY (Cno) REFERENCES Course (Cno);

(3)在Student表中插入以下记录，验证【实体完整性】是否起作用。

insert   into  student  values(’2022010201’,'张天',20,'男','计算机科学与技术')

insert   into  student  values(’2022010201’,'高磊',20,'男','计算机科学与技术')

然后依次输入’2022010202’、’2022010203’ 、

’2022010204’ 、’2022010205’这4条记录。

INSERT INTO Student VALUES('2022010201', '高磊', 20, '男', '计算机科学与技术');
INSERT INTO Student VALUES('2022010202', '姓名1', 21, '男', '专业1');
INSERT INTO Student VALUES('2022010203', '姓名2', 22, '女', '专业2');
INSERT INTO Student VALUES('2022010204', '姓名3', 23, '男', '专业3');
INSERT INTO Student VALUES('2022010205', '姓名4', 24, '女', '专业4');

(4)在SC表中插入

(’2022010201’,‘1001’,85) (’2022010201’,‘1002’,90) (’2022010206’,‘1001’,78)这3条记录，验证【参照完整性】是否起作用。

INSERT INTO SC VALUES('2022010201', '1001', 85);
INSERT INTO SC VALUES('2022010201', '1002', 90);
INSERT INTO SC VALUES('2022010206', '1001',78);

(5)如果在student表中删除’2022010201’这条记录呢？

DELETE FROM student
WHERE Sno='2022010201';

(6)在Student表中增加”学生入学”列，其数据类型是日期型；

ALTER TABLE Student 
ADD AdmissionDate DATE;

(7)将Sex的数据类型由字符型修改成整型；

ALTER TABLE Student 
MODIFY COLUMN Sex INT;

(8)增加Course表中的Cname的UNIQUE约束；

LTER TABLE Course 
ADD CONSTRAINT UNIQUE (Cname);

(9)将SC表中的Grade字段定义为百分制；

ALTER TABLE SC MODIFY Grade DECIMAL(3, 2); -- 这个命令将修改 SC 表中的 Grade 列，使其成为一个 DECIMAL 类型，总共可以有3位数字，其中2位是小数。

(10)删除Course表中的UNIQUE约束

ALTER TABLE Course
DROP INDEX Cname;

(11)试着删除Course表中的PRIMARY  KEY约束，能否删掉？

如果想删除course表中的主键，可以使用DROP PRIMARY KEY语句。但是需要注意，如果该主键被其他表的外键所依赖，删除主键会失败。如果只是想删除表中的数据，可以使用DELETE语句或TRUNCATE语句，但需要注意自增长列的值的变化。

3.建立索引

（1）在student表上建立关于属性Sno的唯一索引。

CREATE UNIQUE INDEX idx_unique_sno ON Student (Sno);

(2)在course表上建立关于Cno的唯一索引。

CREATE UNIQUE INDEX idx_unique_cno ON Course (Cno);

完整代码：

DROP TABLE sc;
DROP TABLE Student;
DROP TABLE course;-- 如果没有删掉表，第二次无法建表成功，所以每次运行需要将表删掉
CREATE  TABLE  Student(
Sno  CHAR(12)  PRIMARY KEY,
Sname  CHAR(8)  UNIQUE,
Age  SMALLINT,
Sex  CHAR(2));-- 创建student表
CREATE TABLE course(
Cno CHAR(6),
Cname CHAR(20) NOT NULL,
Teacher CHAR(8)); -- 创建课程表course
CREATE TABLE SC(
Sno CHAR(12),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY(Sno,Cno));-- 创建学生选课表sc
ALTER TABLE Student
ADD Sdept CHAR(20);-- 在student表中加入属性Sdept（char型）
ALTER TABLE Student
ADD AdmissionDate DATE;-- 在Student表中增加”学生入学”列，其数据类型是日期型；
ALTER TABLE Student
MODIFY COLUMN Sex INT;-- 将Sex的数据类型由字符型修改成整型；
ALTER TABLE Course
ADD CONSTRAINT UNIQUE (Cname);-- 增加Course表中的Cname的UNIQUE约束；
ALTER TABLE Course
DROP INDEX Cname;-- 删除Course表中的UNIQUE约束
ALTER TABLE SC
ADD CONSTRAINT fk_SC_Student
FOREIGN KEY (Sno) REFERENCES Student (Sno);-- 在SC表中，建立两个外码约束，Sno是外码，被参照表是Student（sno）；Cno是外码，被参照表是Course（Cno）。
/*ALTER TABLE SC
ADD CONSTRAINT fk_SC_Course
FOREIGN KEY (Cno) REFERENCES Course (Cno);*/
/*INSERT INTO Student VALUES('2022010201', '高磊', 20, '男', '计算机科学与技术');
INSERT INTO Student VALUES('2022010202', '姓名1', 21, '男', '专业1');
INSERT INTO Student VALUES('2022010203', '姓名2', 22, '女', '专业2');
INSERT INTO Student VALUES('2022010204', '姓名3', 23, '男', '专业3');
INSERT INTO Student VALUES('2022010205', '姓名4', 24, '女', '专业4');
INSERT INTO SC VALUES('2022010201', '1001', 85);
INSERT INTO SC VALUES('2022010201', '1002', 90);
INSERT INTO SC VALUES('2022010206', '1001',78);*/ -- 这个是无法直接插入的，其中含有汉字，只是大概的方法是这样插入，由于无法在Navicat Premium 15里面运行成功，所以注释掉了。
DELETE FROM student
WHERE Sno='2022010201';-- 在student表中删除’2022010201’这条记录呢？
ALTER TABLE SC MODIFY Grade DECIMAL(3, 2);-- 将SC表中的Grade字段定义为百分制；
CREATE UNIQUE INDEX idx_unique_sno ON Student (Sno);-- 在student表上建立关于属性Sno的唯一索引。
CREATE UNIQUE INDEX idx_unique_cno ON Course (Cno);-- 在course表上建立关于Cno的唯一索引。

MySQL数据库支持3中注释：

1. 从‘#’字符到结尾；
2. 从‘- -’序列到结尾（‘- -’要求第二个破折号后面至少跟一个空格）；
3. 从/*到*/，可跨行；

在Navicat Premium 15上运行结果截图：

五、实验总结

在数据库建表、修改表和索引的实验中，我学到了很多关于数据库管理的实用技能。以下是我对这三个方面的实验总结：

1.建表：在建表实验中，我学习了如何设计数据库表的结构，包括定义表名、字段名、数据类型、约束等。通过这个实验，我理解了表是数据库的基础单元，它的设计直接影响到数据库的性能和易用性。建表时需要考虑数据的完整性和安全性，因此约束和索引是非常重要的。同时，合适的数据类型也可以减少数据存储的空间和提高查询效率。

2.修改表：在修改表实验中，我学习了如何添加、删除和修改表中的字段。我还了解了如何修改表的结构，例如添加约束、更改数据类型等。这个实验让我明白了数据库表的修改是一项经常性的任务，它能帮助我们在保证数据完整性的同时，适应业务需求的变化。

3.索引：在索引实验中，我学习了如何为表中的字段创建索引，以及如何优化索引策略。通过这个实验，我理解了索引是提高数据库查询性能的重要工具。正确地使用索引可以大大提高查询速度，特别是在大数据量的情况下。然而，过多的索引可能会增加数据的写入操作的时间和空间开销。因此，需要根据实际需求和场景来权衡。

总的来说，通过这些实验，我深入理解了数据库建表、修改表和索引的原理和方法。这些技能对于在实际项目中管理和优化数据库至关重要。在未来的工作中，我将继续学习和实践，以提高我的数据库管理和优化技能。