数据库原理实验指导书Word下载.docx

1、年龄sdept15系名 course（课程表）：cnoChar4课程号cname20课程名称cpno先行课号ccredit学分 sc（学生选课表）：小数外码参照关系FkstudentcoursegradeDecimal510x100成绩2. 在spjdb数据库中利用查询分析器创建以下4个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）： S（供应商信息表）：供应商号供应商名称status大于0供应商状态city所在城市 P（零件信息表）：pno零件号pname零件名称color颜色weight重量 J（工程项目表）：jno工程项目号jname工程项目名称SPJ（供应情

2、况表）：SPJqtyx数量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）禁

3、止启用Student表的“Sdept”的CHECK约束ck_student。4.分别建立以下索引（如果不能成功建立,请分析原因）（1） 在student表的sname列上建立普通降序索引.（2） 在course表的cname列上建立唯一索引.（3） 在sc表的sno列上建立聚集索引.（4） 在spj表的sno（升序）, pno（升序）和jno（降序）三列上建立一个普通索引.提 示: 1. 建立表, 修改表, 建立索引需具有create table的权限. 2. 创建基本表的SQL语句是CREATE TABLE . 简单语法格式:CREATE TABLE 表名 （ 列名 数据类型 default

4、 缺省值 not null ，列名 数据类型 default 缺省值 not null ，primary key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件） ）;一般语法格式:CREATE TABLE database_name.owner. table_name（| column_name | ,n ）ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT :=column_name data_typeDEFAULT constant_expres

5、sion| IDENTITY （seed, increment ） ROWGUIDCOL n参数说明：database_name.owner. table_name：定义表的名字，表名的长度不得超过128个字节，如果是临时表，则表名不能超过116个字符。：列的定义。column_name：列的名字。列的命名必须遵守有关数据库对象的命名规则。data_type：列的数据类型。DEFAULT constant_expression：定义该列的默认值。IDENTITY：定义该列是一个标识列。当一个新的数据行插入表中的时候，SQL Server2000为标识列提供一个唯一的、递增的数值。在一张表格中，

6、只能定义一个标识列。在定义标识列时，必须同时定义起始值和增量。Seed：定义标识列的起始值。所谓起始值就是插入表的第一行的数据的标识列的值。Increment：定义标识列的增量。所谓增量就是插入表的最近一行相对与前一行标识列的数据值的增量。ROWGUIDCOL：定义该列是一个行全局唯一的标识列。在一张表中只有一个唯一标识符列可以被定义为ROWGUIDCOL。column_constraint：定义与列相关联的约束。table_constraint：定义对表的约束。ON filegroup | DEFAULT ：定义将表存储在某一个指定的文件组中，DEFAULT表示将表存储在默认文件组中。TEX

7、TIMAGE_ON filegroup | DEFAULT ：如果表中有ntext、text或image类型的数据，则将这些数据存储在某一个指定的文件组里。 3. 修改表结构的SQL语句是ALTER TABLE, 修改的内容包括：修改、增加、删除列或约束、使约束和触发器无效等。简单语法格式:alter table add / 增加新列 drop / 删除约束 drop column / 删除列 alter column ; /修改列定义 其中列级完整性约束包括:空值约束 NOT NULL和NULL主关键字约束 PRIMARY KEY唯一性约束 UNIQUE参照完整性约束 FOREIGN KEY

8、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 |ENABLE |DISABLE TRIGGER ALL |trigger_name ,

9、nALTER 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表

10、中的约束salary_cap失效。ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_capENABLE |DISABLE TRIGGER ALL |trigger_name ,n：使所有或部分触发器有效或无效。使用T_SQL语句对学生表进行各种修改。向表中添加新的字段：在学生表中添加一个“class”字段，数据类型为字符型。ALTER TABLE student add class char（10）删除表中的旧列：将学生表中的“class”字段删除。ALTER TABLE student DROP column class更改表中的约束：删除

11、某列的约束：将SC表中的外码约束FK_sc_sno删除。ALTER TABLE sc DROP FK_sc_sno将Student表中的主码约束PK_student_sno删除。ALTER TABLE student DROP PK_student_sno为某列添加约束：为Student表的“Ssex”字段创建一个缺省约束，缺省值为男。ALTER TABLE student ADD constraint def_ssex DEFAULT 男 FOR ssex更改Student表中的“sno”字段的宽度为10及非空约束。ALTER TABLE student ALTER COLUMN sno c

12、har（10） NOT NULL为Student表添加一个主码约束PK_student_sno。ALTER TABLE student ADD CONSTRAINT PK_student_sno PRIMARY KEY（sno）为SC表添加一个外码约束FK_sc_sno。ALTER TABLE sc ADD CONSTRAINT FK_sc_sno FOREIGN KEY （sno） REFERENCES student（sno） 给学生表增加“grade”字段并加上CHECK约束，让其不可以大于100。ALTER TABLE student ADD grade int Constraint

13、ch_grade check（grade100）Exec sp_help student给学生表中添加“birthday”字段，并且这个日期不能在录入当天的日期之后。Alter table student Add birthday DATETIME NULL CONSTRAINT ch_birthday CHECK （birthdaygetdate（）添加具有默认值的可为空的列：在学生表中加入“matriculationday”字段，并且这一字段的默认值为录入当天的日期。Alter table student Add matriculationday smalldatetime NULL Co

14、nstraint adddateflt Default getdate（）4.建立索引的SQL语句是CREATE INDEX, 语法格式: CREATE UNIQUE CLUSTERED INDEX ON （ ASC | DESC , ASC | DESC ）其中 UNIQUE指出是唯一索引, CLUSTERED指出是聚集索引。实验2 SQL数据操作1. 向实验1建立的表中添加数据（元组）, 掌握INSERT语句的用法；2. 修改基本表中的数据, 掌握UPDATE语句的用法；3. 删除基本表中的数据，掌握DELETE语句的用法;4. 体会数据完整性约束的作用, 加深对数据完整性及其约束的理解。

15、5. 通过触发器实现更复杂的数据完整性约束，掌握CREATE TRIGGER语句的用法。1. 将教材P59表中的数据添加到数据库STUDENTDB中.2. 将教材P80-81表中的数据添加到数据库SPJDB中. 体会执行插入操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.3. 设计几个删除操作，体会执行删除操作时检查参照完整性规则的效果.4. 设计一组更新操作, 体会执行更新操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.5. 设计一组更新操作, 它需要另外一个表中的数据作为更新条件（如将选修了“信息系统”课程的成绩均提高15% ）。6. 设计一个删除操

16、作，它需要另外一个表中的数据作为删除条件（如将“刘晨”的选课记录删除 ）。提示:1.插入语句INSERT的一般格式: 格式1: 一次插入一个元组. INSERT INTO （ , ） VALUES（ ） 格式2: 一次插入多个元组（子查询结果）., 从一个关系中选择一些元组插入到另一个关系中（当然相应属性要出自同一个域）。2.数据更新语句UPDATE的一般格式: UPDATE SET = , FROM WHERE 使用说明: 使用WHERE子句指定条件, 以更新满足条件的一些元组的属性值, 并且一次可以更新多个属性; 更新条件可以与其他的表相关（使用FROM指定）; 如果没有WHERE子句，则

17、更新全部元组。 例: 将所有选修“数据库”课程的成绩提高10% update SC set grade=grade*1.1 from COURSE where SC.cno=COURSE.cno and cname=数据库3.删除操作语句DELETE的一般格式： DELETE FROM FROM 如果没有指定删除条件则删除全部元组; 删除条件可以与其他的表相关（使用FROM指定）; DELETE语句只删除表中的元组（数据）, 保留表结构. 删除计算机系学生的选课记录. delete from SC from STUDENT where SC.sno=STUDENT.sno and sdept=

18、CS4. 用TRUNCATE TABLE清空表格TRUNCATE TABLE语句可以删除表格中所有的数据，只留下一个表格的定义。该操作要比DELETE语句快，因为TRUNCATE TABLE是不记录日志的操作。TRUNCATE TABLE将释放表的数据和索引所占据的所有空间。语法如下：TRUNCATE table-name。删除表格course中的数据。 TRUNCATE course注意：由于TRUNCATE TABLE操作不进行日志的记录，所以删除数据后无法恢复，同DROP TABLE一样，只有数据库的拥有者可以执行TRUNCATE TABLE命令，而且权力无法转让。5.在执行INSERT

19、、UPDATE和DELETE操作时可能会受到关系完整性的约束，这种约束可以保证数据库中的数据是正确的。6. 建立触发器的语句CREATE TRIGGER一般格式：创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft SQL Server 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。语法：CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH

20、APPEND NOT FOR REPLICATION AS IF UPDATE （ 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：是在其上执行触发器的表或视图，有时称为触发器表或

21、触发器视图。可以选择是否指定表或视图的所有者名称。WITH ENCRYPTION加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server 复制的一部分发布。AFTER指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，则 AFTER 是默认设置。不能在视图上定义 AFTER 触发器。INSTEAD OF指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。在表或视

22、图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。INSTEAD OF 触发器不能在 WITH CHECK OPTION 的可更新视图上定义。如果向指定了 WITH CHECK OPTION 选项的可更新视图添加 INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器。 DELETE , INSERT , UPDATE 是指定在表或视图上执行哪些数据修改语句时将激活触发器

23、的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。WITH APPEND指定应该添加现有类型的其它触发器。只有当兼容级别是 65 或更低时，才需要使用该可选子句。如果兼容级别是 70 或更高，则不必使用 WITH APPEND 子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CREATE TRI

24、GGER 的默认行为）。有关更多信息，请参见 sp_dbcmptlevel。WITH APPEND 不能与 INSTEAD OF 触发器一起使用，或者，如果显式声明 AFTER 触发器，也不能使用该子句。只有当出于向后兼容而指定 FOR 时（没有 INSTEAD OF 或 AFTER），才能使用 WITH APPEND。以后的版本将不支持 WITH APPEND 和 FOR（将被解释为 AFTER）。NOT FOR REPLICATION表示当复制进程更改触发器所涉及的表时，不应执行该触发器。AS是触发器要执行的操作。sql_statement是触发器的条件和操作。触发器条件指定其它准则，以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。当尝试 DELETE、INSERT 或 UPDATE 操作时，Transact-SQL语句中指定的触发器操作将生效。触发器可以包含任意数量和种类的 Transact-SQL 语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的 Transact-