11春数据库实验3 表的创建、管理跟数据操纵4学时

实验三表的创建、管理和数据操纵(4学时)

【实验目的】
了解SQL语言的使用，进一步理解关系运算，巩固数据库的基础知识。

【实验要求】
利用SQL语言进行数据库表的各种操作：
1．数据库表的创建、修改和删除操作。

2．向表中进行数据的插入、删除和修改操作。

【实验内容】
1.利用数据定义语句在实验一创建的stu_DB库中建立学生管理系统的三个表：Student、Course、SC。

2．利用INSERT、UPDATE和DELETE语句对上述三张表的数据进行插入、更新和删除操作。

3．在stu_DB库中添加Teacher和TC表，对已建立的学生管理系统中的表添加充足的数据(每个表不少于20条)，以便完成本实验的后继操作。

【实验步骤】

一、数据定义

(一)表的创建
在实验一创建的数据库stu_DB中分别用企业管理器和查询分析器，按下面的表结构创

列名
表3-1Student表结构

说明 约束建学生管理系统的表。

Sage 年龄整数15~45

Sdept 所在系 字符串，长度为20 默认为’计算机系’

表3-2 Course表结构

表3-3 SC表结构

1．利用企业管理器创建表

（1）打开企业管理器。

（2）选中实验一创建好的数据库stu_DB，单击数据库中的表对象，然后右击窗口右侧选择新建表，弹出如图所示窗体。

建了一个次要组，可以在属性窗体的表文件组中为此表选择文件组。应该把一些竞争使用的

表放于不同的文件组中，并且让文件组属于不同磁盘，这样可以在表竞争读写时提高并发性

能。

（5）创建完成后，单击磁盘图标，并为表取一个名字。注意，应该为表取一个有意义的名字。

2．用Transact_SQL语句创建表

语法为：

CREATETABLE
[database_name.[owner].] table_name
({column_name data_type [DEFAULT ‘default_value’]|
[CONSTRAINTCONSTRAINT_name]
},[…n] [IDENTITY [(seed, increment )]]
)
[ON{ filegroup | DEFAULT }]
[TEXTIMAGE_ON{filegroup | DEFAULT }]
【例1】生成一个表名为student的表

ssex CHAR (2) check (ssex='男'or ssex='女'),

sage smallint,

sdept CHAR (20) default ('计算机系'),

)

图3-2查询分析器窗体——创建表

【例2】创建一张名为Ta1的表，此表中有3列，第一列Pid定义为主键，并且自动增长。第二列Name默认值为Unknown，第三列定义一个约束(日期不能大于输入当天的日

CREATE TABLE ta1 ( 期)，插入一条记录并进行查询，显示如下图。

注意该列有什

么特殊性？

图3-3查询分析器窗体——创建表

请参考例1和例2，完成以下操作：

(1)按前面所给的表结构创建表Course和SC。

(2)自行设计表的结构，用SQL语句建立第二章习题5 中的4个表。

(二)表的修改

1．用企业管理器修改表

⑴右键点击所要修改的学生表，选择“设计表”，会弹出如图3-4所示的窗体，在这个窗体中可以更改数据表的字段。在空白处点右键，选择“属性”，弹出另一窗体，在此可以更改约束，也可以增加约束。

ALTER TABLE student ADD class CHAR(6)

? 删除表中的旧列：将学生表中的“Sdept”字段删除。 ALTERTABLEstudent DROPCOLUMN Sdept

? 更改表以添加具有约束的列。

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

ALTER

TABLE

student

ADD

grade

int

CONSTRAINT

ch_grade

CHECK(grade<100)
EXECsp_help ch_grade

给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。ALTERTABLEstudent ADDbirthday DATETIMENULL
CONSTRAINTch_birthday CHECK(birthday<getdate())

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

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

请参考上面所给的例子：

(1)自行设计表结构修改操作（不少于8个语句），并进行实验验证。

(2)修改表student，结果如表3-4。

(3)创建表3-5、表3-6，为后续实验建立基础。

表3-4 更新后的student表结构

列名 说明数据类型 约束

Sno学号 字符串，长度为8 主码

Sname 姓名 字符串，长度为10 非空

Ssex性别字符串，长度为2 ’男’或’女’

Sdept 所在系 字符串，长度为20 默认为’计算机系’

Sbirth 出生日期日期 自定义

sclass 班级 字符串，长度为20

sremark评语 变长字符串，长度为100

saddress 家庭住址 变长字符串，长度为40

szipcode 邮编 字符串，长度为6

sphone 电话 字符串，长度为15

列名
表3-5 Teacher表结构

说明 约束semail电子邮箱

tdept所在系 字符串，长度为20默认为’计算机系’

tbirth 出生日期 日期 自定义

tposition 职称字符串，长度为12

tsalary 工资 整数取值：0~10000

texperience 简历 变长字符串，长度为200

tamount工作量 整数 取值：0~1000

tphone 电话字符串，长度为15

temail 电子邮箱变长字符串，长度为40

taddress 家庭住址 变长字符串，长度为40

表3-6 TC表结构

(三)表的删除

可以在企业管理器中选择要删除的表直接删除，也可以通过Transact-SQL语句DROP

删除表的定义及表中的所有数据、索引、出发器、约束和权限规范。

如要删除ta1表，语句如下：DROPTABLEta1

请参考上面的例子完成对表Student、Course、SC的删除操作，它们删除的次序如何？

并进行实验验证。

二、数据操纵

（一）查看、重命名及删除用户定义的数据类型

1．使用企业管理器创建一个名为newtype1、长度为6、可变长字符、允许为空的自定

义数据类型。

进入企业管理器，进入stu_DB数据库，用鼠标右键单击“用户定义的数据类型”后选

择“操作”菜单中的“新建用户定义数据类型”或点击“新建”按扭，将弹出下图所示窗体：

图3-4数据类型定义窗体

输入要定义的数据名称newtype1，选择数据类型varCHAR，输入长度为6，在“允许NULL

值”复选框中打个“√”，单击“确定”按钮即可。

2.使用T-SQL语句创建一个名为newtype2，数据长度为6，定长字符型，不允许为空的

自定义数据类型。

USEstu_DB

EXECsp_addtype newtype2, ‘CHAR(6)’, ‘not null’

3.命名用户自定义的数据类型

⑴使用系统存储过程sp_rename将自定义数据类型newtype1重新命名为a1。

EXECsp_rename newtype1, a1

⑵使用企业管理器再将自定义数据类型a1重新命名为newtype1。

⑵使用企业管理器删除用户自定义数据类型
进入企业管理器，进入stu_DB数据库，单击“用户定义的数据类型”后，右边窗口将出现所有用户定义的数据类型，再用鼠标右键单击newtype2，在弹出的快捷菜单上单击“删除”按钮即可。

注意：正在被表或其他数据库对象使用的用户定义类型能不能删除？请进行实验验证。

（二）数据的更新
1.利用企业管理器更新数据
打开企业管理器，右键点击所要修改的表，选择“打开表”，单击“返回所有行”，会弹出如图3-5所示的窗体，在该窗体中可以修改表中的数据内容。这里的修改内容是自动存储的，修改完之后，直接关闭该窗体，数据内容即修改成功。

图3-5数据修改窗体
2.利用查询分析器更新数据

⑴INSERT 语句向student表中插入一行数据，具体数据如下：
在关系数据库中，常见的数据更新的语句有三条：INSERT、UPDATE和DELETE

学号：04265005, 姓名：刘辉,性别：男,年龄：21,系别：计算机系
INSERTINTO student (Sno, Sname, Ssex, Sage)
VALUES (‘04265006’,‘李慧’, ‘女’,21)

向ta1表中插入数据如下：
INSERTta1([name],birthday) values (‘lan’,’1977-03-02’)
INSERTta1 values (‘lan’,’1977-03-02’)
INSERTta1(birthday) values (‘1977-03-02’)

请根据上述例子，自行设计不同情况下的数据插入语句（不少于10个语句），并进行实

验验证，验证要求如下：

? 不满足唯一性约束的数据的插入。

? 不满足用户自定义约束的数据的插入。
? 不满足外键约束的数据的插入。

? 利用缺省值的数据的插入。

? 不合理的数据是否能进入数据库。

请根据上述例子，自行设计数据修改语句（不少于10个语句），并进行实验验证，验证要求同INSERT语句。

⑶DELETE语句删除行
删除student表中的学号为‘04265005’的记录。

DELETEFROM studentWHERESno=‘04265005’
如果想清除表中的所有数据但不删除这个表，可以使用TRUNCATETABLE语句。该语句相当于没有条件的DELETE语句，并且该语句不记录日志。

请根据上述例子，自行设计不同情况的数据删除语句（不少于10条语句），并进行实验验证不满足外键约束的数据删除操作。

对数据定义中创建的学生管理系统（Student，Course，SC，Teacher，TC表）的每个表添加充足的数据(每个表不少于20条)，以便完成本实验的后继操作。具体表信息可参考如下：
Student表：

course表：

Cno	Cname	Cpno	Credit
C001	数据库原理	C005	3	……
C002	高等数学		4	……
C003	管理信息系统	C001	2	……
C004	操作系统	C006	3	……
C005	数据结构	C007	3	……
C006	计算机文化基础		2	……
C007	C 语言程序设计	C006	3	……
……	……	……	……	……

score表：

04261005	C001	96
04262001	C001	97
04262002	C001	83
04262003	C001	85
04561001	C007	92
04563001	C007	97
……	……	……

teacher表：

【思考题】
1.表Student、Course、SC应按何种次序删除？为什么？

2.正在被表或其他数据库对象使用的用户定义类型能不能删除？为什么？