数据库原理实验指导书Word下载.docx
《数据库原理实验指导书Word下载.docx》由会员分享，可在线阅读，更多相关《数据库原理实验指导书Word下载.docx（36页珍藏版）》请在冰豆网上搜索。
年龄
sdept
15
系名
course（课程表）：
cno
Char
4
课程号
cname
20
课程名称
cpno
先行课号
ccredit
学分
sc（学生选课表）：
小数
外码
参照关系
Fk
student
course
grade
Decimal
5
1
0≤x≤100
成绩
2.在spjdb数据库中利用查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）：
S（供应商信息表）：
供应商号
供应商名称
status
大于0
供应商状态
city
所在城市
P（零件信息表）：
pno
零件号
pname
零件名称
color
颜色
weight
重量
J（工程项目表）：
jno
工程项目号
jname
工程项目名称
SPJ（供应情况表）：
S
P
J
qty
x>
数量
3.修改表结构，具体要求如下：
（1）将表course的cname列的数据类型改为varchar（40）.
（2）为表student增加一个新列:
birthday（出生日期）,类型为datetime,默认为空值.
（3）将表sc中的grade列的取值范围改为小于等于150的正数.
（4）为Student表的“Sex”字段创建一个缺省约束，缺省值为’男’
（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_definition>
|column_name|<
table_constraint>
}[,…n]）
[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]
<
:
={column_namedata_type}[DEFAULTconstant_expression]
|[IDENTITY[（seed,increment）]][ROWGUIDCOL][<
column_constraint>
][…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{<
TABLE_CONSTRAINT>
}[,…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<
getdate（））
⑥添加具有默认值的可为空的列：
在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。
AltertablestudentAddmatriculationdaysmalldatetimeNULLConstraintadddatefltDefaultgetdate（）
4.建立索引的SQL语句是CREATEINDEX,语法格式:
CREATE[UNIQUE][CLUSTERED]INDEX<
索引名>
ON<
（<
[ASC|DESC][,<
[ASC|DESC]…]）
其中UNIQUE指出是唯一索引,CLUSTERED指出是聚集索引。
实验2SQL数据操作
1.向实验1建立的表中添加数据（元组）,掌握INSERT语句的用法；
2.修改基本表中的数据,掌握UPDATE语句的用法；
3.删除基本表中的数据，掌握DELETE语句的用法;
4.体会数据完整性约束的作用,加深对数据完整性及其约束的理解。
5.通过触发器实现更复杂的数据完整性约束，掌握CREATETRIGGER语句的用法。
1.将教材P59表中的数据添加到数据库STUDENTDB中.
2.将教材P80-81表中的数据添加到数据库SPJDB中.体会执行插入操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.
3.设计几个删除操作，体会执行删除操作时检查参照完整性规则的效果.
4.设计一组更新操作,体会执行更新操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.
5.设计一组更新操作,它需要另外一个表中的数据作为更新条件（如将选修了“信息系统”课程的成绩均提高15%）。
6.设计一个删除操作，它需要另外一个表中的数据作为删除条件（如将“刘晨”的选课记录删除）。
提示:
1.插入语句INSERT的一般格式:
格式1:
一次插入一个元组.
INSERT[INTO]<
[（<
[,<
…]）]
VALUES（<
表达式>
…]）
格式2:
一次插入多个元组（子查询结果）.
[,<
子查询>
从一个关系中选择一些元组插入到另一个关系中（当然相应属性要出自同一个域）。
2.数据更新语句UPDATE的一般格式:
UPDATE<
SET<
=<
[,<
…]
[[FROM<
]WHERE<
逻辑表达式>
]
使用说明:
使用WHERE子句指定条件,以更新满足条件的一些元组的属性值,并且一次可以更新多个属性;
更新条件可以与其他的表相关（使用FROM指定）;
如果没有WHERE子句，则更新全部元组。
例:
将所有选修“数据库”课程的成绩提高10%
updateSC
setgrade=grade*1.1
fromCOURSEwhereSC.cno=COURSE.cnoandcname=’数据库’
3.删除操作语句DELETE的一般格式：
DELETEFROM<
[[FROM<
如果没有指定删除条件则删除全部元组;
删除条件可以与其他的表相关（使用FROM指定）;
DELETE语句只删除表中的元组（数据）,保留表结构.
删除计算机系学生的选课记录.
deletefromSC
fromSTUDENTwhereSC.sno=STUDENT.snoandsdept=’CS’
4.
用TRUNCATETABLE清空表格
TRUNCATETABLE语句可以删除表格中所有的数据，只留下一个表格的定义。
该操作要比DELETE语句快，因为TRUNCATETABLE是不记录日志的操作。
TRUNCATETABLE将释放表的数据和索引所占据的所有空间。
语法如下：
TRUNCATEtable-name。
删除表格course中的数据。
TRUNCATEcourse
注意：
由于TRUNCATETABLE操作不进行日志的记录，所以删除数据后无法恢复，同DROPTABLE一样，只有数据库的拥有者可以执行TRUNCATETABLE命令，而且权力无法转让。
5.在执行INSERT、UPDATE和DELETE操作时可能会受到关系完整性的约束，这种约束可以保证数据库中的数据是正确的。
6.建立触发器的语句CREATETRIGGER一般格式：
创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。
Microsoft®
SQLServer™允许为任何给定的INSERT、UPDATE或DELETE语句创建多个触发器。
语法：
CREATETRIGGERtrigger_name
ON{table|view}
[WITHENCRYPTION]
{
{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}
[WITHAPPEND]
[NOTFORREPLICATION]
AS
[{IFUPDATE（column）
[{AND|OR}UPDATE（column）]
[...n]
|IF（COLUMNS_UPDATED（）{bitwise_operator}updated_bitmask）
{comparison_operator}column_bitmask[...n]
}]
sql_statement[...n]
}
}
trigger_name：
是触发器的名称。
触发器名称必须符合标识符规则，并且在数据库中必须唯一。
可以选择是否指定触发器所有者名称。
Table|view：
是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。
可以选择是否指定表或视图的所有者名称。
WITHENCRYPTION
加密syscomments表中包含CREATETRIGGER语句文本的条目。
使用WITHENCRYPTION可防止将触发器作为SQLServer复制的一部分发布。
AFTER
指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。
所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。
如果仅指定FOR关键字，则AFTER是默认设置。
不能在视图上定义AFTER触发器。
INSTEADOF
指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。
在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器。
然而，可以在每个具有INSTEADOF触发器的视图上定义视图。
INSTEADOF触发器不能在WITHCHECKOPTION的可更新视图上定义。
如果向指定了WITHCHECKOPTION选项的可更新视图添加INSTEADOF触发器，SQLServer将产生一个错误。
用户必须用ALTERVIEW删除该选项后才能定义INSTEADOF触发器。
{[DELETE][,][INSERT][,][UPDATE]}
是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。
必须至少指定一个选项。
在触发器定义中允许使用以任意顺序组合的这些关键字。
如果指定的选项多于一个，需用逗号分隔这些选项。
对于INSTEADOF触发器，不允许在具有ONDELETE级联操作引用关系的表上使用DELETE选项。
同样，也不允许在具有ONUPDATE级联操作引用关系的表上使用UPDATE选项。
WITHAPPEND
指定应该添加现有类型的其它触发器。
只有当兼容级别是65或更低时，才需要使用该可选子句。
如果兼容级别是70或更高，则不必使用WITHAPPEND子句添加现有类型的其它触发器（这是兼容级别设置为70或更高的CREATETRIGGER的默认行为）。
有关更多信息，请参见sp_dbcmptlevel。
WITHAPPEND不能与INSTEADOF触发器一起使用，或者，如果显式声明AFTER触发器，也不能使用该子句。
只有当出于向后兼容而指定FOR时（没有INSTEADOF或AFTER），才能使用WITHAPPEND。
以后的版本将不支持WITHAPPEND和FOR（将被解释为AFTER）。
NOTFORREPLICATION
表示当复制进程更改触发器所涉及的表时，不应执行该触发器。
AS
是触发器要执行的操作。
sql_statement
是触发器的条件和操作。
触发器条件指定其它准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。
当尝试DELETE、INSERT或UPDATE操作时，Transact-SQL语句中指定的触发器操作将生效。
触发器可以包含任意数量和种类的Transact-SQL语句。
触发器旨在根据数据修改语句检查或更改数据；
它不应将数据返回给用户。
触发器中的Transact-
升级会员 