一、实验目的与要求
(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中注释:
- 从‘#’字符到结尾;
- 从‘- -’序列到结尾(‘- -’要求第二个破折号后面至少跟一个空格);
- 从
/*
到*/
,可跨行;
在Navicat Premium 15上运行结果截图:
五、实验总结
在数据库建表、修改表和索引的实验中,我学到了很多关于数据库管理的实用技能。以下是我对这三个方面的实验总结:
1.建表:在建表实验中,我学习了如何设计数据库表的结构,包括定义表名、字段名、数据类型、约束等。通过这个实验,我理解了表是数据库的基础单元,它的设计直接影响到数据库的性能和易用性。建表时需要考虑数据的完整性和安全性,因此约束和索引是非常重要的。同时,合适的数据类型也可以减少数据存储的空间和提高查询效率。
2.修改表:在修改表实验中,我学习了如何添加、删除和修改表中的字段。我还了解了如何修改表的结构,例如添加约束、更改数据类型等。这个实验让我明白了数据库表的修改是一项经常性的任务,它能帮助我们在保证数据完整性的同时,适应业务需求的变化。
3.索引:在索引实验中,我学习了如何为表中的字段创建索引,以及如何优化索引策略。通过这个实验,我理解了索引是提高数据库查询性能的重要工具。正确地使用索引可以大大提高查询速度,特别是在大数据量的情况下。然而,过多的索引可能会增加数据的写入操作的时间和空间开销。因此,需要根据实际需求和场景来权衡。
总的来说,通过这些实验,我深入理解了数据库建表、修改表和索引的原理和方法。这些技能对于在实际项目中管理和优化数据库至关重要。在未来的工作中,我将继续学习和实践,以提高我的数据库管理和优化技能。