实验二   SQL基本表的插入、修改和删除


一、实验目的及要求：
熟练掌握基本表的插入、删除与修改，为后继学习作准备。
二、实验任务：
1．了解并掌握SQL Server Management Studio(或SQL查询分析器及企业管理器)的使用;
2．掌握基本表的插入、删除与修改。
三、操作要点：
1.启动SQL Server Management Studio(或SQL查询分析器）
2.掌握插入、删除与修改，命令关键词分别是INSERT、DELETE和UPDATE 
四、注意事项：
1.注意每次对表的修改，命令执行完后要查看表的变动刷新表。
2.特别注意在执行插入、删除与修改等更新操作时，要保证数据的完整性。
五、实验学时：2学时

六、实验重点及难点
1．T-SQL语句对基本表的定义进行删除与修改，并插入基本表数据。
2．T-SQL语句对数据库操作的灵活控制功能。
七、实验步骤：
1．启动SQL Server Management Studio（SQL查询分析器）；
2．选择SQL SERVER后，按确认；
3．选择University数据库；
4．验证如下例题：

4.1 修改基本表（注：DDL命令）
①向基本表Students中增加“入学时间”属性列，其属性名为Sentrancedate，数据类型为DATETIME型。
进入数据库:
use University                --进入 University数据库
alter table Students
add Sentrancedate DATETIME    --修改Students表，向表中插入列名Sentrancedate(入学时间)

修改后:

②将Sage(年龄)的数据类型改为SMALLINT型。
alter table Students --修改表Students
alter column Sage Smallint --修改列名为 Sage的属性 类型为短整型

修改后:

③删除Sname(姓名)必须取唯一值的约束。
先查询该表中的所存在的约束：
exec sp_helpconstraint Students --查找该表中的约束名

通过查询的约束名删除唯一约束:
删除后:

④将属性列Sentrancedate从Students表中删除。
alter table Students --删除列名为Sentrancedate的列
drop column Sentrancedate --column表示列

说明：⑴ 请同学们举一反三，通过增加、删除、修改其它字段，以及修改约束条件来了解修改基本表语句的用法，并写出代码。
如果要向表中通过增加、删除、修改其它字段，格式如下:
向表中添加列
ALTER TABLE Students（表名）
ADD(添加) column(列) Sage 后面跟类型

向表中已存在的列修改其类型
ALTER TABLE Students（表名）
ALTER(修改) column(列) Sage 后面跟类型

删除表中的某一列(如果该列存在约束需要先删除约束)
ALTER TABLE Students（表名）
DROP(删除) column(列) Sage

4.2 删除Students表（注：此条为DDL命令）。
注意：此处对比truncate table命令的使用，以及与后面delete命令的差别，写出相应代码。

说明：此表删除后，请立即将其建立起来，并恢复原有数据，以便后面的例子使用。如删除时出现问题，应分析问题原因，并找出解决办法。

用DDL就是用DROP TABLE 表名
此条语句是数据定义语言，会把表的结构删除
因为该表的主键是另外一个表Reports表的外键，所以需要先删除Reports表的外键Sno字段引用

先删除Reports的外键引用:

删除后:

删除外键引用后执行truncate table Students 与drop tableStudents 命令成功

数据操作语言DML:
truncate table Student

Delete from Student
Where 后面跟所要执行的条件

代码如下:
delete from Students
where Sname=‘罗兆’

truncate table Students

数据定义DDL:
代码如下:
drop table Students

truncate table和delete都只是删除表的数据,是DML语言都是数据操纵语言，
但是truncate table语句是一次性删除表中所有行，且被删除的数据不能找回，delete则为一条一条逐行删除，这个操作会放到rollback、segement中，事务提交之后才生效；如果有相应的trigger，执行的时候将被触发(例如级联删除，作业二中也有体现若有删除一个门，则教师表中相应的数据则一起删除)，删除后的数据可以通过日志文件进行恢复

4.3 向表中添加元组
①将一个学生元组(S01，李大平，男，20，机械)添加到基本表Students中，观察执行结果。如不能成功，分析原因，并找出解决办法。
代码如下:

insert into Students values(‘S01’,‘李太平’,‘男’,20,‘机械’);
SELECT * FROM Students

②现有课程C08，名称为“大学物理”，学分为4，没有先行课，请向Courses表插入相应元组。
代码如下:
insert into Courses values(‘C08’,‘大学物理’,NULL,4)
SELECT * FROM Courses

③现有学号S04,课程号为C07，成绩未出，请将其添加到基本表Reports中。

4.4 数据修改
①将学号为S04且课程号为C07的成绩改为80分。

②所有男生年龄加一岁。
修改前:

修改后:

③将范林军的学号改成S01。如修改不成功，分析原因。
因为在Studens表中Sno为主键且，表中已经有Sno为S01的元祖所以必定不成功违反主键约束
4.5 数据删除
①在Students表中，将学号为S02的元组删除。如删除不成功，分析原因，并找出解决办法。
删除肯定不成功，因为Report表中主键引为Students中外键，要想删除成功S02，首先要确定Report中没有S02学生的记录
一、可以通过删除Students表中的外键约束，再进行删除
alter table Students
drop CONSTRAINT 外键约束名

再通过delete from Students --delete
where Sno=‘S02’

二、可以通过创建表结构是通过设置外键约束时候添加级联更新和级联删除
alter table Reports
add constraint F1_01 foreign key(Sno)references Students
on delete cascade

三、可以通过先把Reports表中所有S02数据删除后再进行删除Students表中的S02学生

再删除Students表中数据:

②将Reports表中低于80分的成绩记录删除。

附加拓展题：七台河市2010年公务员招考面试资格确认名单

考号 姓名 部门代码 部门名称 职位代码 职位名称 总分
102100816 刘宇光 40039 七台河市中级人民法院 01 计算机管理 122.5
102100906 陈纯悦 40040 七台河市检察院 01 计算机管理 122.5
102100701 钱旭 40039 七台河市中级人民法院 01 计算机管理 119.75
102100516 张新 40040 七台河市检察院 02 财会人员 115.75
102100810 高荣 40039 七台河市中级人民法院 02 财会人员 115.25
102100524 肖德庆 40039 七台河市中级人民法院 02 财会人员 110.5
102101122 米含玉 40040 七台河市检察院 03 聘任制书记员 112.75
102100528 姜海楠 40039 七台河市中级人民法院 03 聘任制书记员 110.25
102100727 周琦 40039 七台河市中级人民法院 03 聘任制书记员 105

1）请根据以上表格，参考教材79-80页的表，将表格拆分成关系表，并建表。
2）请用1）建立的表，使用表连接或嵌套查询，显示表格的全部信息。如果不能全部显示，查找原因。
3）
解:根据表中所给信息综上所述
每个人可以报选2个单位，每个单位可以报选3个工作岗位，共有6中可能，所以
关系模式应该为下图

只有当考生号和部门代码，职位代码、成绩共同出现时候才能唯一确定该生是否录取，且考生号和部门代码，职位代码，三个属性才能唯一确定该元祖,在成绩表中一同作为主键，和外键，
共拆分为4张表，分别为考生信息表ExamineeInfor_table属性为Eid(主键)、exname
部门表Dept_table 属性为Did(主键)、dept_name
职位表Post_table 属性为 Pid(主键)、post_name
成绩表Score_table 属性为 Did、Did、Pid、Grade
(Did、Did、Pid)为主键，(Did、Did、Pid)为外键，分别参考ExamineeInfor_table、Dept_table、Post_table

通过连接查询把不同表中的字段显示出来: