SQL：实验五数据库的完整性与数据的插入、修改、删除

我叫木十八 2019-12-08

1825

实验五数据库的完整性与数据的插入、修改、删除

一、实验目的

1．掌握Management Studio的使用。

2．掌握SQL中INSERT、UPDATE、DELETE命令的使用。

3．理解SQL中的标识列、计算列和各种约束。

4．掌握使用SQL命令修改表的定义、删除表。

5．熟悉通过SQL对数据进行完整性控制。熟练掌握数据库三类完整性约束（实体完整性、用户自定义完整性、参照完整性）

6．了解SQL SERVER 的违反完整性处理措施；了解主键（PRIMARY KEY）约束、外键（FOREIGN KEY）约束、唯一性（UNIQUE）约束、检查（CHECK）约束、DEFAULT 约束、允许空值约束。

二、实验内容

使用SQL对数据进行完整性控制（三类完整性、CHECK短语、CONSTRAIN字句）。用实验证实，当操作违反了完整性约束条件时，系统是如何处理的。

（一）、认真学习三类完整性、CHECK短语、CONSTRAIN字句的使用，完成下列内容。

（1）先创建一TEST数据库，然后根据上述要求，在TEST数据库中建立数据表Table1。

首先创建一个全新的数据库test ;接着在创建一个新的table表。

刷新验证是否创建成功。

--shiyan5
--问题一
--1
create database TEST
on(
name='TEST_data',
filename='G:\MySQL File\DB实验\实验5\TEST.mdf',
size=12,
maxsize=36
)
log on
(
name='TEST_log',
filename='G:\MySQL File\DB实验\实验5\TEST.ldf',
size=5
)




use Test
create table Table_1
(
class_id Varchar(6) not null,
class_name Varchar(20) not null,
director Varchar(4) ,
monitor int,
primary key (class_id)--特别注意primary主键的创建是要在空格里面的。
)

（2）在TEST数据库中创建Table2，只含各个基本列（即：包括列名和数据类型，标识种子和计算列）

--2
create table table_2
(
Stu_id int not null primary key,--特别注意什么是识别种子
Stu_name varchar(20) not null unique,
stu_sex varchar(2),
Brithday smalldatetime,
Age as year(getdate())-year(brithday),
Phone varchar(8),
Class_id Varchar(6)
)

由于题目要求的只是基本的列，没有说要将用户自定义完整性附加上去所以就没有将stu_sex 默认为女孩。其他的自定义同理。

（3）Table1中的外键能否在创建Table1表时建立？如不能，该何时、如何创建？请写出相应的SQL代码。

不能，table1表创建时候所需要的table2外键仍未创建好。在table2创建好之后再

创建外键。通过alter替换语句进行操作。

--3
alter table table_1
add constraint c1 foreign key(monitor) references table_2(stu_id)

明白其中几个关键词constraint约束，约束条件就是外键语句foreign key...references;

（4）通过ALTER TABLE语句对Table2进行表定义修改，为其增加各个约束。（注意：空值约束应单独添加）

--4 完善之前的2中的约束条件
--直接从性别开始因为学号和姓名我在2中就已经完成约束条件
alter table table_2
add constraint c2 default '女' for stu_sex




alter table table_2
add constraint c3 check (phone like ('3935[0,9][0,9][0,9][0,9]'))




alter table table_2
add constraint c4 foreign key(class_id) references table_1(class_id)

参考一下老师的代码：

ALTER table table2
  Add constraint c2 PRIMARY KEY(Stu_id)




ALTER table table2 alter column stu_name varchar (10) not null
ALTER table table2
  Add constraint c3  unique(Stu_name)
--not null 单独添加，并且在unique之前








ALTER table table2
  Add constraint c5 DEFAULT '女'  for Stu_sex
ALTER table table2
  Add constraint c6 CHECK(Phone LIKE '3935[0-9][0-9][0-9][0-9]')
ALTER table table2
  Add constraint c7 foreign key(Class_id) references Table1(Class_id)

在这里面需要特别注意的是默认值default 和检查函数check的使用还有关键词like的用法。

（二）、验证数据库约束

本实验主要是通过对这两张表的操作来验证数据库约束的相关概念。

1、准备好（一）中要求的两个数据表Table1，Tabel2。

2、验证主键（PRIMARY KEY）约束

试运行下面代码：

insert into Table1(class_id,class_name,director)
 values('0101', '医学智能级班', 'J001')

请再一次insert into Table1(class_id,class_name,director) values('0101','医学智能级班', 'J001')，请问能否顺利执行？如不能，出现什么问题？原因是什么？该如何解决？

不能，因为primary key主码是唯一表示的。第一次运行该语句是可以操作的但是第二次运行时在table-1中已经存在这个信息而且唯一的primary是0101，再次运行就会产生冲突。

将‘0101’修改为‘0102’就可以运行。因为primary key不发生冲突。

3、验证外键（FOREIGN）约束

试运行下面两段代码：

-- 允许将显式值插入表的标识列中 ON-允许 OFF-不允许

--语法：SET IDENTITY_INSERT [ database.[ owner.] ] { table } { ON | OFF }

 set identity_insert table2 on
insert into Table2(stu_id,stu_name,stu_sex,birthdate,phone,class_id)
values('2011001', '张三', '男','1990-9-23','39357887','0101')
set identity_insert table2 off




set identity_insert table2 on
insert into Table2(stu_id,stu_name,stu_sex,birthdate,phone,class_id)
values('2011002', '李四', '男','1991-4-23','39357887','0102')
set identity_insert table2 off

请问：上述两段代码能否顺利执行？如不能，出现什么问题？原因是什么？该如何解决？

不可以，出现table_2没有标示的反馈。

而且第二段代码中class_id(0102)原本是没有的，所以也不能顺利进行。

试运行下面代码：

delete from table1 where class_id='0101'

请问：上述代码能否顺利执行？如不能，出现什么问题？原因是什么？

可以顺利执行。

只剩下我在之前自己所采纳的class-id 0102的班级。

备注：创建基本表时可指定on delete cascade，则为级联删除。那么，系统认为删除table1记录时，table2中相关的记录也会同时删除

如果不指定on delete cascade时默认为受限删除。

4、验证唯一性（UNIQUE）约束

试运行下面代码：

set identity_insert table2 on
insert into Table2(stu_id,stu_name,stu_sex,birthdate,phone,class_id)
values('2011003', '张三', '男','1990-9-23','39357887','0101')
set identity_insert table2 off

请问：上述代码能否顺利执行？如不能，出现什么问题？原因是什么？

不能

张三重复——姓名是唯一的unique的。

5、验证检查（CHECK）约束

试运行下面代码：





insert into Table2(stu_name,stu_sex,birthdate,phone,class_id)
values( '张历', '男','1990-9-23','79357887','0101')

请问：上述代码能否顺利执行？如不能，出现什么问题？原因是什么？

不能，因为stu_id是不能为空的。

（三）、数据的增删改

用SQL语句完成下列功能。使用数据库为SCHOOL数据库。

l、新开设一门课程，名叫网络安全与防火墙，学时40，编号为“0118”，主要介绍网络的安全与主要的防火墙软件。

--1
insert into course
values('0118','网络安全与防火墙','40','主要介绍网络的安全与主要的防火墙软件')

在course表中存储成功！

2、先建立monitor表，其结构与student表大致一样．包含student表的学号、姓名、性别和班级编号，然后把各班班长的个人信息插入到monitor表中。

首先创建一个monitor表

--建立monitor表
create table monitor
(
stu_id varchar(9),
stu_name varchar(8),
stu_sexvarchar(2),
class_id varchar(4)  primary key
)

这是我的连接方式是通过student 和class连接回馈给我——违反约束？！

use school
insert into monitor
select Stu_id,Stu_name,Stu_sex,student.Class_id
from student left join class on(Class.Monitor=student.stu_id and Student.Class_id=Class.Class_id)

老师给的代码可以运行——这就让我有点小迷糊！！

老师的代码：

insert into monitor
select Stu_id,Stu_name,Stu_sex,class.Class_id
from Class left join student on(Monitor=Stu_id and class.Class_id=student.Class_id)

3、更新所有职称为“助教”的教师职称为“助理教师”。

--3
update Teacher
set TechPost='助理老师'
where TechPost='助教'

4、在所有经济系班级的名称前加上“经济系”三个字。（class上的触发器可能造成不能正确更新，怎么办？）





update class
set Class_name='经济系'+Class_name
where Depar_id in(select Depar_id
from Deparment
where Depar_name='经济系')

反馈问题给我？！

似乎是要关闭触发器！！！

删除触发器后可以正常使用该语句！

5、学号为“980101005”的学生的“数据结构”课程成绩改为80分。

update StudentGrade
set Grade=80
where Stu_id ='980101005' and Course_id = (select Course_id
from course
where Course_name='数据结构')

6、删除studentgrade表中所有成绩不及格的记录。





--6
delete
from StudentGrade
where grade<60

7、删除studentgrade表中学号以“99”开头的学生选修课程的记录。





--7
delete
from StudentGrade
where Stu_id like '99%'

8、删除课程名为“C语言”的课程信息和所有这门课的选课信息。（不用级联删除，因级联需在创建表时定义。）

参考老师的代码。回顾一下级联删除的概念课本P86

delete from StudentGrade
where Course_id=(select Course_id
                                     from course    
                                     where Course_name='C语言')
delete from CourseTeacher
where Course_id=(select Course_id
                                     from course    
                                     where Course_name='C语言')




delete from course
where Course_name='C语言'

三、实验小结

1．思考Alter与Update，Drop与Delete的区别

参考资料：数据库update和alter之间的区别

https://blog.csdn.net/f2006116/article/details/50978329

参考资料：

https://blog.csdn.net/weixin_38052444/article/details/81407738

简单来说，

Alter是修改整个表的定义结构；update是专门对表中的元组进行修改更新。用一个比喻的手法表示就是alter是表这个建筑的总设计师而update是里面的施工员，对于建筑里的一些细节形象进行施工。同理，drop是删除结构，delete是删除数据——drop就是爆破建筑的总设计师，而delete就是拆墙的小工。

2．对数据进行插入、修改、删除时需要注意哪些问题？

插入需要注意数据的完整尤其是对于主码来说。修改删除都要留意一下是否会影响级联等等方面

数据库

文章转载自我叫木十八，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL：实验五 数据库的完整性与数据的插入、修改、删除

评论

SQL：实验五数据库的完整性与数据的插入、修改、删除