11春数据库实验3 表的创建管理和数据.docx
《11春数据库实验3 表的创建管理和数据.docx》由会员分享，可在线阅读，更多相关《11春数据库实验3 表的创建管理和数据.docx（18页珍藏版）》请在冰豆网上搜索。
11春数据库实验3表的创建管理和数据
实验三表的创建、管理和数据操纵（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表结构
列名
说明
数据类型
约束
Sno
学号
字符串，长度为8
主码
Sname
姓名
字符串，长度为10
非空
Ssex
性别
字符串，长度为2
’男’或’女’
Sage
年龄
整数
15~45
Sdept
所在系
字符串，长度为20
默认为’计算机系’
表3-2Course表结构
createtableCourse
（
Cnochar（8）primarykey,
CnameCHAR（20）notnull,
CpnoCHAR（8）,
Ccreditintcheck（Ccreditbetween1and6）,
Semsterintcheck（Semsterbetween1and8）,
Periodintcheck（Period>=0）,
）
列名
说明
数据类型
约束
Cno
课程号
字符串，长度为8
主码
Cname
课程名
字符串，长度为20
非空
Cpno
先修课程号
字符串，长度为8
Ccredit
学分
整数
取值：
1~6
Semster
学期
整数
取值：
1~8
Period
学时
整数
取值：
≥0
表3-3SC表结构
CreatetableSC1
（
Snochar（8）,
Cnochar（8）,
Gradeintcheck（Gradebetween0and100）,
）
列名
说明
数据类型
约束
Sno
学号
字符串，长度为8
主码，引用student的外码
Cno
课程号
字符串，长度为8
主码，引用course的外码
Grade
成绩
整数
取值：
0~100
1．利用企业管理器创建表
（1）打开企业管理器。
（2）选中实验一创建好的数据库stu_DB，单击数据库中的表对象，然后右击窗口右侧选择新建表，弹出如图所示窗体。
图3-1新建表窗体
（3）在这个窗体中，列名列就示表的字段名，可以在这个窗体中为字段选择数据类型和长度以及是否可以为空值。
（4）在此处可以为表选择文件组，在表中单击右键，选择属性，如果已经为数据库创建了一个次要组，可以在属性窗体的表文件组中为此表选择文件组。
应该把一些竞争使用的表放于不同的文件组中，并且让文件组属于不同磁盘，这样可以在表竞争读写时提高并发性能。
（5）创建完成后，单击磁盘图标，并为表取一个名字。
注意，应该为表取一个有意义的名字。
2．用Transact_SQL语句创建表
语法为：
CREATETABLE
[database_name.[owner].]table_name
（{column_namedata_type[DEFAULT‘default_value’]|
[CONSTRAINTCONSTRAINT_name]
},[…n][IDENTITY[（seed,increment）]]
）
[ON{filegroup|DEFAULT}]
[TEXTIMAGE_ON{filegroup|DEFAULT}]
【例1】生成一个表名为student的表
createtablestudent
（
snoCHAR（8）primarykey,
snameCHAR（10）notnull,
ssexCHAR
（2）check（ssex='男'orssex='女'）,
sagesmallint,
sdeptCHAR（20）default（'计算机系'）,
）
图3-2查询分析器窗体——创建表
【例2】创建一张名为Ta1的表，此表中有3列，第一列Pid定义为主键，并且自动增长。
第二列Name默认值为Unknown，第三列定义一个约束（日期不能大于输入当天的日期），插入一条记录并进行查询，显示如下图。
CREATETABLEta1
（
pidintidentity（1,1）primarykey,
[name]CHAR（10）default（'unknow'）,
birthdaydatetimeCHECK（birthday）
图3-3查询分析器窗体——创建表
请参考例1和例2，完成以下操作：
（1）按前面所给的表结构创建表Course和SC。
（2）自行设计表的结构，用SQL语句建立第二章习题5中的4个表。
（二）表的修改
1．用企业管理器修改表
⑴右键点击所要修改的学生表，选择“设计表”，会弹出如图3-4所示的窗体，在这个窗体中可以更改数据表的字段。
在空白处点右键，选择“属性”，弹出另一窗体，在此可以更改约束，也可以增加约束。
图3-4修改表窗体
（2）利用Transact-SQL语句修改表
●向表中添加新的字段：
在学生表中添加一个“班级”字段，数据类型为字符型。
ALTERTABLEstudentADDclassCHAR（6）
●删除表中的旧列：
将学生表中的“Sdept”字段删除。
ALTERTABLEstudentDROPCOLUMNSdept
●更改表以添加具有约束的列。
给学生表的增加“grade”字段并加上CHECK约束，让其不可以大于100。
ALTERTABLEstudentADDgradeintCONSTRAINTch_gradeCHECK（grade<100）
EXECsp_helpch_grade
给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。
ALTERTABLEstudentADDbirthdayDATETIMENULL
CONSTRAINTch_birthdayCHECK（birthday●添加具有默认值的可为空的列：
在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。
ALTERTABLEstudentADDmatriculationdaysmalldatetimeNULL
CONSTRAINTadddatefltDEFAULTgetdate（）
请参考上面所给的例子：
（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
semail
电子邮箱
变长字符串，长度为40
表3-5Teacher表结构
列名
说明
数据类型
约束
tno
工号
字符串，长度为8
主码
tname
姓名
字符串，长度为10
非空
tsex
性别
字符串，长度为2
’男’或’女’
tdept
所在系
字符串，长度为20
默认为’计算机系’
tbirth
出生日期
日期
自定义
tposition
职称
字符串，长度为12
tsalary
工资
整数
取值：
0~10000
texperience
简历
变长字符串，长度为200
tamount
工作量
整数
取值：
0~1000
tphone
电话
字符串，长度为15
temail
电子邮箱
变长字符串，长度为40
taddress
家庭住址
变长字符串，长度为40
表3-6TC表结构
列名
说明
数据类型
约束
Tno
工号
字符串，长度为8
主码，引用Teacher的外码
Cno
课程号
字符串，长度为8
主码，引用Course的外码
Classroom
教室
变长字符串，长度为30
altertableStudentaddSdeptchar
（2）default（'计算机系'）
altertableStudentdropcolumnSdept
altertableStudentaddSbirthdatetime,Sremarkchar（20）,
Saddresschar（100）,Szipcodechar（6）,Sphonechar（15）,
Semailchar（40）
createtableTeacher
（
Tnochar（8）primarykey,
Tnamechar（10）notnull,
Tsexchar
（2）check（Tsex='男'orTsex='女'）,
Tdeptchar（20）default（'计算机系'）,
Tbirthdatetime,
Tpositionchar（12）,
Tsalaryintcheck（Tsalarybetween0and10000）,
Texperiencevarchar（200）,
Tamountintcheck（Tamountbetween0and1000）,
Tphonechar（15）,
Temailvarchar（40）,
Taddressvarchar（40）,
）
createtableTC
（
Tnochar（8）,
Cnochar（8）,
Classroomvarchar（30）,
）
CREATETABLEta1
（
pidintidentity（1,1）primarykey,
[name]CHAR（10）default（'unknow'）,
birthdaydatetimeCHECK（birthday）
droptableta1
（三）表的删除
可以在企业管理器中选择要删除的表直接删除，也可以通过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_addtypenewtype2,‘CHAR（6）’,‘notnull’
3.命名用户自定义的数据类型
⑴使用系统存储过程sp_rename将自定义数据类型newtype1重新命名为a1。
EXECsp_renamenewtype1,a1
⑵使用企业管理器再将自定义数据类型a1重新命名为newtype1。
4．删除用户自定义数据类型
⑴使用系统存储过程sp_droptype来删除用户自定义的数据类型。
EXECsp_droptypenewtype1
⑵使用企业管理器删除用户自定义数据类型
进入企业管理器，进入stu_DB数据库，单击“用户定义的数据类型”后，右边窗口将出现所有用户定义的数据类型，再用鼠标右键单击newtype2，在弹出的快捷菜单上单击“删除”按钮即可。
注意：
正在被表或其他数据库对象使用的用户定义类型能不能删除？
请进行实验验证。
（二）数据的更新
1.利用企业管理器更新数据
打开企业管理器，右键点击所要修改的表，选择“打开表”，单击“返回所有行”，会弹出如图3-5所示的窗体，在该窗体中可以修改表中的数据内容。
这里的修改内容是自动存储的，修改完之后，直接关闭该窗体，数据内容即修改成功。
图3-5数据修改窗体
2.利用查询分析器更新数据
在关系数据库中，常见的数据更新的语句有三条：
INSERT、UPDATE和DELETE
⑴INSERT语句
向student表中插入一行数据，具体数据如下：
学号：
04265005,姓名：
刘辉,性别：
男,年龄：
21,系别：
计算机系
USEstu_DB
INSERTINTOstudent（Sno,Sname,Ssex,Sage,Sdept）
VALUES（‘04265005’,‘刘辉’,‘男’,21,‘计算机系’）
INSERTINTOstudent（Sno,Sname,Ssex,Sage）
VALUES（‘04265006’,‘李慧’,‘女’,21）
向ta1表中插入数据如下：
INSERTta1（[name],birthday）values（‘lan’,’1977-03-02’）
INSERTta1values（‘lan’,’1977-03-02’）
INSERTta1（birthday）values（‘1977-03-02’）
请根据上述例子，自行设计不同情况下的数据插入语句（不少于10个语句），并进行实验验证，验证要求如下：
●不满足唯一性约束的数据的插入。
●不满足用户自定义约束的数据的插入。
●不满足外键约束的数据的插入。
●利用缺省值的数据的插入。
●不合理的数据是否能进入数据库。
⑵UPDATE语句
更改（学生）表数据，将学号为‘04265005’的学生的所在系改成‘机械工程系’。
UPDATEstudentSETSdept=‘机械系’WHERESno=‘04265005’
请根据上述例子，自行设计数据修改语句（不少于10个语句），并进行实验验证，验证要求同INSERT语句。
⑶DELETE语句删除行
删除student表中的学号为‘04265005’的记录。
DELETEFROMstudentWHERESno=‘04265005’
如果想清除表中的所有数据但不删除这个表，可以使用TRUNCATETABLE语句。
该语句相当于没有条件的DELETE语句，并且该语句不记录日志。
请根据上述例子，自行设计不同情况的数据删除语句（不少于10条语句），并进行实验验证不满足外键约束的数据删除操作。
对数据定义中创建的学生管理系统（Student，Course，SC，Teacher，TC表）的每个表添加充足的数据（每个表不少于20条），以便完成本实验的后继操作。
具体表信息可参考如下：
Student表：
Sno
sname
Sbirthday
Sdept
04261001
王冬梅
1982.02.07
计算机系
……
04261002
王田田
1982.03.04
计算机系
……
04261003
吴畏
1982.06.06
计算机系
……
04261004
马丽
1982.04.03
计算机系
……
04261005
杨乾坤
1982.03.23
计算机系
……
04262001
刘乐
1983.01.16
计算机系
……
04262002
夏天
1983.02.08
计算机系
……
04262003
杨致远
1982.07.22
计算机系
……
04561001
陶然
1982.01.12
数学系
……
04563001
姚远
1982.03.21
数学系
……
……
……
……
……
……
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表：
Sno
Cno
Score
04261001
C001
90
04261002
C001
85
04261003
C001
73
04261004
C001
98
04261005
C001
96
04262001
C001
97
04262002
C001
83
04262003
C001
85
04561001
C007
92
04563001
C007
97
……
……
……
teacher表：
Tno
Tname
Tsex
Department
T001
石云丹
女
计算机系
……
T002
罗莉
女
计算机系
……
T003
王国强
男
计算机系
……
T004
吴栋
男
计算机系
……
T005
高鸿轩
男
数学系
……
T006
张怀良
男
数学系
……
T007
刘晓伟
男
数学系
……
T108
马莉莲
女
物电学院
……
……
……
……
……
TC表：
Tno
cno
classroom
T001
C005
22号楼405
T002
C007
7号楼406
T003
C001
17号楼504
T004
C006
17号楼401
T005
C004
8号楼201
T006
C002
8号楼202
……
……
……
【思考题】
1.表Student、Course、SC应按何种次序删除？
为什么？
2.正在被表或其他数据库对象使用的用户定义类型能不能删除？
为什么？
升级会员 