《数据库原理与应用》实验指导书.docx
《《数据库原理与应用》实验指导书.docx》由会员分享，可在线阅读，更多相关《《数据库原理与应用》实验指导书.docx（50页珍藏版）》请在冰点文库上搜索。
《数据库原理与应用》实验指导书
《数据库原理与应用》实验指导书
万雪勇
安全管理系
2011.9
目录
实验1数据库和表的建立及数据完整性设置
实验2SQL查询操作
实验3SQL数据操作与视图
实验4数据库的安全性管理
实验5 存储过程和触发器
实验6数据库的备份与恢复
实验7数据库应用系统综合设计
实验8事务并发控制操作
实验9简单的Web数据库应用（选做）
实验1数据库和表的建立及数据完整性设置
一、实验目的：
1.掌握表（关系）的建立方法；
2.掌握表结构（关系模式）的修改方法；
3.实践DBMS提供的数据完整性功能，加深对数据完整性的理解。
二、实验内容：
1.在studentdb数据库中利用查询分析器创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：
student（学生信息表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
sno
char
9
N
学号
sname
char
10
N
姓名
ssex
char
2
Y
性别
sage
smallint
Y
不小于12
年龄
sdept
char
15
Y
系名
course（课程表）：
主码
列名
数据类型
宽度
小数位
空否
备注
Pk
cno
Char
4
N
课程号
cname
Char
20
Y
课程名称
cpno
Char
4
Y
先行课号
ccredit
smallint
Y
学分
sc（学生选课表）：
主码
列名
数据类型
宽度
小数
空否
外码
参照关系
取值范围
备注
Pk
sno
Char
9
N
Fk
student
学号
cno
Char
4
N
Fk
course
课程号
grade
Decimal
5
1
Y
0≤x≤100
成绩
2.在spjdb数据库中利用查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：
S（供应商信息表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
sno
char
2
N
供应商号
sname
char
10
N
供应商名称
status
smallint
Y
大于0
供应商状态
city
char
10
Y
所在城市
P（零件信息表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
pno
char
2
N
零件号
pname
char
10
N
零件名称
color
char
2
Y
颜色
weight
smallint
Y
大于0
重量
J（工程项目表）：
主码
列名
数据类型
宽度
小数位
空否
取值范围
备注
Pk
jno
char
2
N
工程项目号
jname
char
10
N
工程项目名称
city
char
10
Y
所在城市
SPJ（供应情况表）：
主码
列名
数据类型
宽度
小数
空否
外码
参照关系
取值范围
备注
Pk
sno
Char
2
N
Fk
S
供应商号
pno
Char
2
N
Fk
P
零件号
jno
Char
2
N
Fk
J
工程项目号
qty
smallint
Y
x>0
数量
3.修改表结构，具体要求如下：
（1）将表course的cname列的数据类型改为varchar（40）.
（2）为表student增加一个新列:
birthday（出生日期）,类型为datetime,默认为空值.
（3）将表sc中的grade列的取值范围改为小于等于150的正数.
（4）为Student表的“Ssex”字段创建一个缺省约束，缺省值为’男’
（5）为“Sdept”字段创建一个检查约束，使得所在系必须是’CS’、’MA’或’IS’之一。
（6）为Student表的“Sname”字段增加一个唯一性约束
（7）为SC表建立外键,依赖于Student表的fk_S_c约束。
（8）禁止启用Student表的“Sdept”的CHECK约束ck_student。
4.分别建立以下索引（如果不能成功建立,请分析原因）
（1）在student表的sname列上建立普通降序索引.
（2）在course表的cname列上建立唯一索引.
（3）在sc表的sno列上建立聚集索引.
（4）在spj表的sno（升序）,pno（升序）和jno（降序）三列上建立一个普通索引.
三、提示:
1.建立表,修改表,建立索引需具有createtable的权限.
2.创建基本表的SQL语句是CREATETABLE.
简单语法格式:
CREATETABLE表名
（列名数据类型[default缺省值][notnull]
[，列名数据类型[default缺省值][notnull]]
……
[，primarykey（列名[，列名]…）]
[，foreignkey（列名[，列名]…）references表名（列名[，列名]…）]
[，check（条件）]）;
一般语法格式:
CREATETABLE[database_name.[owner].]table_name
（{|column_name|}[,…n]）
[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]
:
:
={column_namedata_type}[DEFAULTconstant_expression]
|[IDENTITY[（seed,increment）]][ROWGUIDCOL][][…n]
参数说明：
①[database_name.[owner].]table_name：
定义表的名字，表名的长度不得超过128个字节，如果是临时表，则表名不能超过116个字符。
②：
列的定义。
③column_name：
列的名字。
列的命名必须遵守有关数据库对象的命名规则。
④data_type：
列的数据类型。
⑤DEFAULTconstant_expression：
定义该列的默认值。
⑥IDENTITY：
定义该列是一个标识列。
当一个新的数据行插入表中的时候，SQLServer2000为标识列提供一个唯一的、递增的数值。
在一张表格中，只能定义一个标识列。
在定义标识列时，必须同时定义起始值和增量。
⑦Seed：
定义标识列的起始值。
所谓起始值就是插入表的第一行的数据的标识列的值。
Increment：
定义标识列的增量。
所谓增量就是插入表的最近一行相对与前一行标识列的数据值的增量。
⑧ROWGUIDCOL：
定义该列是一个行全局唯一的标识列。
在一张表中只有一个唯一标识符列可以被定义为ROWGUIDCOL。
column_constraint：
定义与列相关联的约束。
⑨table_constraint：
定义对表的约束。
⑩ON{filegroup|DEFAULT}：
定义将表存储在某一个指定的文件组中，DEFAULT表示将表存储在默认文件组中。
[TEXTIMAGE_ON{filegroup|DEFAULT}]：
如果表中有ntext、text或image类型的数据，则将这些数据存储在某一个指定的文件组里。
3.修改表结构的SQL语句是ALTERTABLE,修改的内容包括：
修改、增加、删除列或约束、使约束和触发器无效等。
简单语法格式:
altertable<表名>
[add<列名><数据类型>[<列级完整性约束>]]//增加新列
[drop<完整性约束名>]//删除约束
[dropcolumn<列名>]//删除列
[altercolumn<列名><数据类型>[<列级完整性约束>]];//修改列定义
其中列级完整性约束包括:
·空值约束NOTNULL和NULL
·主关键字约束PRIMARYKEY
·唯一性约束UNIQUE
·参照完整性约束FOREIGNKEY
一般语法格式:
ALTERTABLEtable
{[ALTERCOLUMNcolumn_name{new_data_type[NULL|NOTNULL]|{ADD|DROP}ROWGUIDCOL}]
|ADD{[]|column_nameAScomputed_column_expression}[,…n]
|ADD{}[,…n]
|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,…n]
|{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,…n]
|{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,…n]}
参数说明：
①ALTERCOLUMN：
修改已经存在的列的属性。
②{ADD|DROP}ROWGUIDCOL：
将指定列定义成ROWGUIDCOL，或者删除该列的ROWGUIDCOL属性。
③ADD{[]|column_nameAScomputed_column_expression}[,…n]：
增加新的列。
④ADD{}[,…n]:
定义新的表约束。
⑤DROP{[CONSTRAINT]constraint_name|COLUMNcolumn}[,…n]：
删除约束或删除列。
⑥{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,…n]}：
使所有约束或者指定的约束有效或失效。
例：
使cnst_example表中的约束salary_cap失效。
ALTERTABLEcnst_exampleNOCHECKCONSTRAINTsalary_cap
⑦{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,…n]}：
使所有或部分触发器有效或无效。
例：
使用T_SQL语句对学生表进行各种修改。
①向表中添加新的字段：
在学生表中添加一个“class”字段，数据类型为字符型。
ALTERTABLEstudentaddclasschar（10）
②删除表中的旧列：
将学生表中的“class”字段删除。
ALTERTABLEstudentDROPcolumnclass
③更改表中的约束：
删除某列的约束：
将SC表中的外码约束FK_sc_sno删除。
ALTERTABLEscDROPFK_sc_sno
将Student表中的主码约束PK_student_sno删除。
ALTERTABLEstudentDROPPK_student_sno
为某列添加约束：
为Student表的“Ssex”字段创建一个缺省约束，缺省值为’男’。
ALTERTABLEstudentADDconstraintdef_ssexDEFAULT'男'FORssex
更改Student表中的“sno”字段的宽度为10及非空约束。
ALTERTABLEstudentALTERCOLUMNsnochar（10）NOTNULL
为Student表添加一个主码约束PK_student_sno。
ALTERTABLEstudentADDCONSTRAINTPK_student_snoPRIMARYKEY（sno）
为SC表添加一个外码约束FK_sc_sno。
ALTERTABLEscADDCONSTRAINTFK_sc_snoFOREIGNKEY（sno）REFERENCESstudent（sno）
④给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。
ALTERTABLEstudentADDgradeintConstraintch_gradecheck（grade<100）
Execsp_helpstudent
⑤给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。
AltertablestudentAddbirthdayDATETIMENULLCONSTRAINTch_birthdayCHECK（birthday⑥添加具有默认值的可为空的列：
在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。
AltertablestudentAddmatriculationdaysmalldatetimeNULLConstraintadddatefltDefaultgetdate（）
4.建立索引的SQL语句是CREATEINDEX,语法格式:
CREATE[UNIQUE][CLUSTERED]INDEX<索引名>
ON<表名>（<列名>[ASC|DESC][,<列名>[ASC|DESC]…]）
其中UNIQUE指出是唯一索引,CLUSTERED指出是聚集索引。
实验2SQL查询操作
一、实验目的：
1.熟练掌握SELECT语句，能够运用该语句完成各种查询；
2.观察查询结果,体会SELECT语句实际应用；
3．要求学生能够在查询分析器中使用SELECT语句进行单表查询和多表查询。
二、实验内容：
完成如下查询：
1.参照教材P82表中的数据，完成以下查询：
（1）查询course表中所有学分大于2并且序号小于5的课程信息。
（2）查询学分界于2和8之间的课程的信息。
（3）从course表中查询出课程号为1或4或7的课程的课程号、课程名称、以及学分。
（4）从course表中查询学分大于3的课程信息，并按课程号升序排列。
（5）查询每个学生及其课程的平均分情况。
（6）查询选修2号课程且成绩在80分以上的学生信息。
（7）查询每个学生的学号、姓名、选修的课程及成绩。
（8）查询所有计算机系学生的学号、选修课程号以及分数。
（使用IN谓词）。
（9）查询选修了课程名为“操作系统”的学生学号和姓名。
（10）查询所有计算机系学生的学号、选修课程号以及分数（使用EXISTS谓词）。
2.（选做）参照教材P75表中的数据，完成P127习题5中
（1）~（7）的查询。
三、提示:
1.SELECT语句的语法格式如下：
基本格式：
SELECT[ALL|DISTINCT]<表达式1>[,<表达式2>…]
FROM<表名1>[,<表名2>…]
[WHERE<逻辑表达式>]
[GROUPBY<列名>[,<列名>…][HAVING<谓词|条件>]]
[ORDERBY<列名>[ASC|DESC][,<列名>[ASC|DESC]…]]
一般格式：
SELECT[ALL|DISTINCT][TOPn[PERCENT][WITHTIES]]
[INTO]
[FROM{}[,...n]]
[WHERE|]
[GROUPBY[ALL]group_by_expression[,...n] [WITH{CUBE|ROLLUP}] ]
[HAVING]
:
:
={*|{table_name|view_name|table_alias}.*|{column_name}
[[AS]column_alias]}[,…n]
:
:
= table_name[[AS]table_alias][WITH（[,...n]）]
|view_name[[AS]table_alias][WITH（[,...n]）]
|rowset_function[[AS]table_alias]
|user_defined_function[[AS]table_alias]
|derived_table[AS]table_alias[（column_alias[,...n]）]
|
:
:
=ON
|CROSSJOIN
|[（）[]]
:
:
= [INNER|{{LEFT|RIGHT|FULL}[OUTER]}]
[] JOIN
:
:
= column_name{*=|=*}column_name
参数说明：
（1）ALL：
默认设置，指定在结果集中可以显示重复行。
（2）DISTINCT：
指定在结果集中只能显示唯一行，空值被认为相等。
（3）TOPn：
指定只从查询结果集中输出前n行。
N是介于0～4294967295之间的整数。
（4）PERCENT：
从结果集中输出前百分之几行，当指定时带PERCENT，n的值必须介于0～100之间。
（5）WITHTIES：
指定从结果集中返回附加的行，这些行包含与出现在TOPn（PERCENT）行最后的ORDERBY列中的值相等的值。
如果指定了ORDERBY子句，则只能指定TOP…WITHTIES。
（6）：
为结果选择的列。
选择列表是以逗号分隔的一系列表达式。
（7）INTO：
创建新表并将结果行从查询插入新表中。
用户若要执行带INTO子句的SELECT语句，必须在目的数据库内具有CREATETABLE权限。
SELECT...INTO不能与COMPUTE子句一起使用。
new_table的格式通过对选择列表中的表达式进行取值来确定。
new_table中的列按选择列表指定的顺序创建。
new_table中的每列有与选择列表中的相应表达式相同的名称、数据类型和值。
（8）FROM:
指定要在Transact-SQL语句中使用的表或视图（带或不带别名均可）。
可在语句中使用多达256个表。
可将table变量指定为表源。
中的参数说明见（19）。
（9）:
使用不标准的产品专用语法和WHERE子句指定外联接。
*=运算符用于指定左向外联接，=*运算符用于指定右向外联接。
注：
这种语法具有潜在的二义性解释并且不标准，因此不提倡使用这种语法进行外联接。
而应在FROM子句中使用联接运算符指定联接。
不能在同一语句中同时使用这两种方法。
（10）：
通过使用谓词限制结果集内返回的行。
对搜索条件中可以包含的谓词数量没有限制。
（11）*：
指定在FROM子句内返回所有表和视图内的所有列，列按FROM子句所指定的由表或视图返回，并按他们在表或视图中的顺序返回。
（12）column_name：
要返回的列名，必要时限定column_name以避免二义性引用，当FROM子句中的两个表内包含重复名的列时会出现该情况。
（13）column_alias：
是查询结果集内替换列名的可选名。
（14）GROUPBYALL:
包含所有组和结果集，甚至包含那些任何行都不满足WHERE子句指定的搜索条件的组和结果集。
如果指定了ALL，将对组中不满足搜索条件的汇总列返回空值。
不能用CUBE或ROLLUP运算符指定ALL。
如果访问远程表的查询中有WHERE子句，则不支持GROUPBYALL操作。
（15）group_by_expression:
是对其执行分组的表达式。
group_by_expression也称为分组列。
group_byexpression可以是列或引用列的非聚合表达式。
在选择列表内定义的列的别名不能用于指定分组列。
说明 text、ntext和image类型的列不能用于group_by_expression。
对于不包含CUBE或ROLLUP的GROUPBY子句，group_by_expression的项数受查询所涉及的GROUPBY列的大小、聚合列和聚合值的限制。
该限制从8,060字节的限制开始，对保存中间查询结果所需的中间级工作表有8,060字节的限制。
如果指定了CUBE或ROLLUP，则最多只能有10个分组表达式。
（16）CUBE:
指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。
在结果集内返回每个可能的组和子组组合的GROUPBY汇总行。
GROUPBY汇总行在结果中显示为NULL，但可用来表示所有值。
使用GROUPING函数确定结果集内的空值是否是GROUPBY汇总值。
结果集内的汇总行数取决于GROUP
升级会员 