实验二 数据操纵语言实验

实验二数据操纵语言实验

一、实验目的

?熟练掌握数据表的修改；

?熟练掌握数据库输入数据、修改数据和删除数据的操作；

?熟练掌握向数据库插入单个数据、插入成批数据；

?熟练掌握修改单个数据、修改成批数据；

?熟练掌握删除单个数据、删除成批数据。

二、实验内容

1、针对上节课做的数据进行如下操作。

2、分别使用Microsoft SQL Server Management Studio和T-SQL修改和删除表

4、在Microsoft SQL Server Management Studio中插入、修改和删除表数据。

5、使用T-SQL重复上述操作

三、实验步骤

1、表的修改

⑴利用Microsoft SQL Server Management Studio修改表的步骤如下：

①从树型结构上，展开要修改定义的表所在的数据库。

②选中该数据库节点下的表节点，则企业管理器显示出该数据库下全部的表格。

③选择要修改定义的表，如右键点击所要修改的表，选择【设计】，则弹出如下图所示的对话框，在这个窗体中可以进行增加列、删除列和修改列属性等操作。

⑵利用T_SQL语句修改表：可以在更大的范围内修改表格的定义，修改的内容包括：修改、增加、删除列或约束等。

例：使用T_SQL语句对学生表进行各种修改。

①向表中添加新的字段：在学生表中添加一个“class”字段，数据类型为字符型。

ALTER TABLE student add class char(10)

②删除表中的旧列：将学生表中的“class”字段删除。

ALTER TABLE student DROP column class

③更改表中的约束：

为某列添加约束：为Student表的“Ssex”字段创建一个缺省约束，缺省值为’男’。

ALTER TABLE student ADD constraint def_ssex DEFAULT '男' FOR ssex

更改Student表中的“sno”字段的宽度为10及非空约束。

ALTER TABLE student ALTER COLUMN sno char(10) NOT NULL

为Student表添加一个主码约束PK_student_sno。

ALTER TABLE student ADD CONSTRAINT PK_student_sno PRIMARY KEY(sno)

④给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。

ALTER TABLE student ADD grade int Constraint ch_grade check(grade<100)

Exec sp_help student

⑤给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。

Alter table student Add birthday DATETIME NULL CONSTRAINT ch_birthday CHECK (birthday

⑥添加具有默认值的可为空的列：

在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。

Alter table student Add matriculationday smalldatetime NULL Constraint adddateflt Default getdate()

2、表的删除

⑴在Microsoft SQL Server Management Studio进行表的删除，步骤如下：

② 树型结构上，展开要删除的表所在的数据库。

②选择该数据库节点下的表节点，则企业管理器显示出该数据库下全部的表格，选择表格，可以同时按下Ctrl键或Shift键选择多个要执行操作的表格。

③鼠标右击，从弹出的快捷菜单中选择【删除】命令，弹出对话框。对话框中列出了所有将被删除的表格。可以选择其中的表格，然后单击【显示相关性】按钮，查看与该表格相关的数据库对象的信息。

④单击【全部除去】按钮删除所有选中的表。

注意：删除表格必须小心，因为表格一旦删除便无法恢复。而表格中原先包含的数据也将随着表格的删除而消失。要想挽回被无意中删除的数据的唯一方法是从原先的数据库中备份恢复。

⑵通过T_SQL语句DROP 删除表的定义及其中所有数据、索引、触发器、约束和权限规范。语法为：

DROP TABLE table_name

例：删除当前数据库中的一个名字为ta1的表。

DROP TABLE ta1

注意：删除表格的权力只属于表格拥有者，并且不能转移，不能使用DROP TABLE语句删除系统表。

Transact-SQL提示:

修改表:

alter table <表名>

[add <列名> <数据类型> [<列级完整性约束>]] // 增加新列

[drop <完整性约束名>] // 删除约束

[drop column <列名>] // 删除列

[alter column <列名> <数据类型> [<列级完整性约束>] ]; //修改列定义

其中列级完整性约束包括:

·空值约束NOT NULL和NULL

·主关键字约束PRIMARY KEY

·唯一性约束UNIQUE

·参照完整性约束FOREIGN KEY

一般语法格式:

ALTER TABLE table

{[ALTER COLUMN column_name { new_data_type [NULL| NOT NULL]|{ADD |DROP} ROWGUIDCOL }]

|ADD {[]| column_name AS computed_column_expression }[,…n]

|ADD {}[,…n]

|DROP {[CONSTRAINT] constraint_name |COLUMN column }[,…n]

|{CHECK |NOCHECK} CONSTRAINT {ALL | constraint_name [,…n]

参数说明：

①ALTER COLUMN：修改已经存在的列的属性。

②{ADD |DROP} ROWGUIDCOL：将指定列定义成ROWGUIDCOL，或者删除该列的ROWGUIDCOL

属性。

③ADD {[]| column_name AS computed_column_expression}[,…n]：增加新的列。

④ADD {}[,…n]:定义新的表约束。

⑤DROP { [CONSTRAINT] constraint_name |COLUMN column }[,…n]：删除约束或删除列。

⑥{CHECK |NOCHECK} CONSTRAINT {ALL | constraint_name [,…n]}：使所有约束或者指定的约束有效或失效。

例：使cnst_example表中的约束salary_cap失效。

ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap

数据更新：

1.插入语句INSERT的一般格式:

格式1: 一次插入一个元组.

INSERT [INTO] <表名>[(<列名> [,<列名>…] ) ]

V ALUES(<表达式> [,<表达式>…] )

格式2: 一次插入多个元组(子查询结果).

INSERT [INTO] <表名>[(<列名>[,<列名>…] ) ]

<子查询>

从一个关系中选择一些元组插入到另一个关系中（当然相应属性要出自同一个域）。

2.数据更新语句UPDA TE的一般格式:

UPDA TE <表名>

SET <列名> = <表达式> [, <列名> = <表达式>…]

[ [FROM <表名> ] WHERE <逻辑表达式> ]

使用说明: 使用WHERE子句指定条件, 以更新满足条件的一些元组的属性值, 并且一次可以更新多个属性; 更新条件可以与其他的表相关（使用FROM指定）; 如果没有WHERE子句，则更新全部元组。

例: 将所有选修“数据库”课程的成绩提高10%

update SC

set grade=grade*1.1

from C where SC.C#=C.c# and cname=’数据库’

3.删除操作语句DELETE的一般格式：

DELETE FROM <表名>

[ [ FROM <表名> ] WHERE <逻辑表达式> ]

使用说明: 如果没有指定删除条件则删除全部元组; 删除条件可以与其他的表相关(使用FROM指定); DELETE语句只删除表中的元组(数据), 保留表结构.

例:从学生关系S中删除学号为200403001的学生的信息。

DELETE FROM S

WHERE S# ='200403001';

四、实验练习题

1、修改表结构，具体要求如下：

(1) 把学生表S中的SBIRTHIN列删除。

ALTER TABLE S DROP SBIRTHIN

(2)为学生表S增加一个新列: birthday(出生日期), 类型为datetime, 默认为空值. Alter table S Add birthday DATETIME NULL

(3) 将表sc中的grade列的取值范围改为小于等于150的正数. ALTER TABLE S ADD grade int Constraint ch_grade check( grade

Exec sp_help student

(4) 为学生表S的“Sex”字段创建一个缺省约束，缺省值为’男’ ALTER TABLE S ADD constraint def_sex DEFAULT '男' FOR sex

(5)为学生表S的“Same”字段增加一个唯一性约束ALTER TABLE S ADD Constraint 12 UNIQUE(Same)

(6)为SC表建立外键,依赖于S表的fk_S_c约束。

2、教材的P155页的：

5-6 写出给学生关系表S和课程设置关系表CS中插入数据记录的插入语句。INSERT 5-7 写出满足下列要求的删除语句

（1）从学生关系表S中删除籍贯为“上海”的所有学生的记录。DELET FROM S （2）从学生关系表SC中删除“李建平”同学的所有课程成绩的记录。

5-8 写出满足下列要求的修改语句

（1）把学习关系表SC中“计算机网络”课的不及格成绩全部改为61分。

（2）在学习关系表SC中修改“数据结构”课程的成绩，若成绩低于该课程的平均成绩时，则将其成绩改为该平均成绩。