数据库系统及应用实验指导书.doc

数据库系统及应用实验指导书实验教学中心名称：商学实验中心课程名称：数据库系统及应用课程编号：ab08224203课程类别：专业教育课程面向专业：信息管理与信息系统一、实验项目一1、实验项目名称：数据库、表、索引和数据完整性2、实验项目编号：ab08224203013、实验性质：必做4、实验类型：基础5、实验目的：（1）掌握数据库的建立方法、步骤。 （2）掌握查看数据库相关信息的方法。 （3）掌握数据库启动与关闭的方法。（4）掌握表的建立、修改、查看、删除操作。 （5）掌握索引的建立、修改、查看、删除操作。6、实验内容：（1） 分别利用命令行方式将下面各表建立到教学管理数据库中。表的创建存在先后顺序，在实际操作中要注意体会。 学生表(student)，采用命令行的创建方式。 字段名代码类型约束学号s_nochar(6)主键姓名s_namechar(10)非空性别s_sexchar(2)只取男、女出生日期s_birthdaydatetime入学成绩s_scoreNumeric(5,1)附加分s_addfNumeric(5,1)班级编码class_nochar(5)与班级表中class_no外键关联班级表(class)，采用命令行创建方式。 字段名代码类型约束班级编码class_nochar(5)主键班级名class_namechar(10)非空所属系部dept_nochar(2)与系部表中dept_no外键关联系部表(department)，采用命令行方式。 字段名代码类型约束系部编码dept_nochar(2)主键系部名dept_namechar(10)非空课程表(course) ，采用命令行方式。 字段名代码类型约束课程编码course_nochar(5)主键课程名course_namechar(20)非空教师表(teacher) ，采用命令行创建方式。 字段名代码类型约束教师编码t_nochar(6)主键教师姓名t_namechar(8)非空性别t_sexchar(2)只取男、女出生日期t_birthdaydate职称tech_titlechar(10)选修表(choice) ，采用命令行方式。 字段名代码类型约束学生编码s_nochar(6)主键,与学生表中s_no外键关联课程编码course_nochar(5)主键,与课程表中course_no外键关联成绩scorenumber(5,1)授课表(teaching) ，采用命令行创建方式。 字段名代码类型约束教师编码t_nochar(6)主键,与教师表中t_no外键关联课程编码course_nochar(5)主键,与课程表中course_no外键关联（2）利用命令行方式查看这些基本表的各种信息。 （3）为某基本表创建索引，查看其信息，然后删除它。 （4）利用表数据维护方法向教师表中添加数据（5）修改学生表（student），增加联系电话（phone）属性（6）为学生表（student）按出生日期字段建立索引，并练习删除索引 7、主要设备名称、规格型号实验环境需要PC机（处理器500MHz以上），windows xp以上操作系统，SQL SERVER 2000或20058、仪器台(套)数量需要PC机50台左右9、每组人数：50人10、学时：2二、实验项目二1、实验项目名称：SQL数据基本操作命令2、实验项目编号：ab08224203023、实验性质：必做4、实验类型：基础5、实验目的：熟练掌握SQL数据查询和数据操纵基本命令的使用方法。 6、实验内容：（1） 向教学管理系统的每个表中录入5条记录。录入时注意体会外键约束。 （2） 创建教师表2(teacher2)，向表中录入5条不同的记录，注意与教师表中数据必须不同，主要体现在主键上。 （3）用多行数据录入的方法将教师表2中数据录入到教师表。 （4）利用表间数据复制的方法创建教师表3(teacher3)，查看教师表3的内容，体会表间数据复制的含义。 （5）删除教师表3中所有数据，删除教师表3。 （6）永久删除教师表2中数据，删除教师表2。 （7）显示所有学生情况。 （8）显示职称是讲师的职工号、姓名、出生日期。 （9）显示与某个学生某门课程成绩相同的选修该门课程的所有学生的学号、姓名。 Select b.s_no,student.s_nameFrom choice a,choice b,studentWhere a.s_no=240711and b.course_no=a.course_no and b.score=a.score and b.s_no=student.s_noselect distinct b.s_no,student.s_namefrom choice a,choice b,studentwhere a.course_no=b.course_no and a.s_no=240712 and a.course_no=02 and a.score=b.score and student.s_no=b.s_no（10）显示成绩在60分以上的学生姓名、教师姓名和课程名称。 select student.s_name,teacher.t_name,course.course_namefrom student,teacher,course,choice,teachingwhere choice.score80 and choice.s_no=student.s_no and choice.course_no=course.course_no and teaching.course_no=course.course_no and teaching.t_no=teacher.t_no（11）将某个学生所学课程成绩低于60分的课程列出来。 select course.course_namefrom course,choicewhere choice.score85 and choice.course_no=course.course_no（12）将某门课程成绩低于60分的学生的成绩改为60分。 update choiceset score=60where score2)select distinct class.*from class,studentwhere student.class_no=class.class_no group by class.class_no,class_name,class.dept_nohaving count(*)1（7）对以上的数据查询语句进行优化（8）将所有物理成绩低于60的学生信息删除7、主要设备名称、规格型号实验环境需要PC机（处理器500MHz以上），windows xp以上操作系统，SQL SERVER 2000或20058、仪器台(套)数量需要PC机50台左右9、每组人数：50人10、学时：4四、实验项目四CREATE PROCEDURE创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。也可以创建在 Microsoft SQL Server 启动时自动运行的存储过程。语法CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 参数procedure_name新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。有关更多信息，请参见使用标识符。要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (#procedure_name)。完整的名称（包括 # 或 #）不能超过 128 个字符。指定过程所有者的名称是可选的。;number是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。parameter过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。使用 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息，请参见 EXECUTE。 data_type参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型。 CREATE TRIGGER创建触发器，触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。Microsoft SQL Server 允许为任何给定的 INSERT、UPDATE 或 DELETE 语句创建多个触发器。语法CREATE TRIGGER trigger_name ON table | view /与表绑定 WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH 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是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。WITH ENCRYPTION加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。使用 WITH ENCRYPTION 可防止将触发器作为 SQL Server 复制的一部分发布。AFTER指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定 FOR 关键字，则 AFTER 是默认设置。不能在视图上定义 AFTER 触发器。INSTEAD OF指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。在表或视图上，每个 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 是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。WITH APPEND指定应该添加现有类型的其它触发器。只有当兼容级别是 65 或更低时，才需要使用该可选子句。如果兼容级别是 70 或更高，则不必使用 WITH APPEND 子句添加现有类型的其它触发器（这是兼容级别设置为 70 或更高的 CREATE TRIGGER 的默认行为）。有关更多信息，请参见 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-SQL 语句常常包含控制流语言。CREATE TRIGGER 语句中使用几个特殊的表： deleted 和 inserted 是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值。例如，若要检索 deleted 表中的所有值，请使用： SELECT * FROM deleted 如果兼容级别等于 70，那么在 DELETE、INSERT 或 UPDATE 触发器中，SQL Server 将不允许引用 inserted 和 deleted 表中的 text、ntext 或 image 列。不能访问 inserted 和 deleted 表中的 text、ntext 和 image 值。若要在 INSERT 或 UPDATE 触发器中检索新值，请将 inserted 表与原始更新表联接。当兼容级别是 65 或更低时，对 inserted 或 deleted 表中允许空值的text、ntext 或 image 列，将返回空值；如果这些列不可为空，则返回零长度字符串。 当兼容级别是 80 或更高时，SQL Server 允许在表或视图上通过 INSTEAD OF 触发器更新 text、ntext 或 image 列。n是表示触发器中可以包含多条 Transact-SQL 语句的占位符。对于 IF UPDATE (column) 语句，可以通过重复 UPDATE (column) 子句包含多列。IF UPDATE (column)测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。因为在 ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名。若要测试在多个列上进行的 INSERT 或 UPDATE 操作，请在第一个操作后指定单独的 UPDATE(column) 子句。在 INSERT 操作中 IF UPDATE 将返回 TRUE 值，因为这些列插入了显式值或隐性 (NULL) 值。BEGIN TRANSACTION标记一个显式本地事务的起始点。BEGIN TRANSACTION将 TRANCOUNT 加 1。语法BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description 参数transaction_name是给事务分配的名称。transaction_name 必须遵循标识符规则，但是不允许标识符多于 32 个字符。仅在嵌套的 BEGIN.COMMIT 或 BEGIN.ROLLBACK 语句的最外语句对上使用事务名。tran_name_variable是用户定义的、含有有效事务名称的变量的名称。必须用 char、varchar、nchar 或 nvarchar 数据类型声明该变量。WITH MARK description指定在日志中标记事务。Description 是描述该标记的字符串。如果使用了 WITH MARK，则必须指定事务名。WITH MARK 允许将事务日志还原到命名标记。注释BEGIN TRANSACTION 代表一点，由连接引用的数据在该点是逻辑和物理上都一致的。如果遇上错误，在 BEGIN TRANSACTION 之后的所有数据改动都能进行回滚，以将数据返回到已知的一致状态。每个事务继续执行直到它无误地完成并且用 COMMIT TRANSACTION 对数据库作永久的改动，或者遇上错误并且用 ROLLBACK TRANSACTION 语句擦除所有改动。BEGIN TRANSACTION 为使用本语句的连接启动一个本地事务。根据当前事务隔离级别的设置情况，为支持该连接所发出的 Transact-SQL 语句而获取的许多资源被该事务锁定，直到此事务以 COMMIT TRANSACTION 或 ROLLBACK TRANSACTION 语句完成。长时间处于等待处理状态的事务会阻止其他用户访问锁定的资源。虽然 BEGIN TRANSACTION 启动一个本地事务，但是在应用程序接下来执行了一个必须记录的操作（如执行 INSERT、UPDATE 或 DELETE 语句）之前，它并不被记录在事务日志中。应用程序能执行一些操作，例如为了保护 SELECT 语句的事务隔离级别而获取锁，但是直到应用程序执行一个修改操作日志中才有记录。在一系列嵌套的事务中用一个事务名给多个事务命名对该事务没有什么影响。系统仅登记第一个（最外部的）事务名。回滚到其它任何名字（有效的保存点名除外）都会产生错误。事实上，任何在回滚之前执行的语句都没有在错误发生时回滚。这些语句仅当外层的事务回滚时才会进行回滚。BEGIN TRANSACTION 启动一个本地事务。如果在事务提交或回滚之前执行了如下操作，本地事务将升级到分布式事务： 执行了一个 INSERT、DELETE 或 UPDATE 语句，该语句引用了连接的服务器上的一个远程表。如果用于访问连接服务器的 OLE DB 提供程序不支持 ItransactionJoin 接口，该 INSERT、UPDATE 或 DELETE 语句则会失败。 当启用了 REMOTE_PROC_TRANSACTIONS 选项时，将调用远程存储过程。 SQL Server 的本地复本成为事务控制器并且使用 MS DTC 来管理该分布式事务。1、实验项目名称：视图、存储过程与触发器的应用2、实验项目编号：ab08224203043、实验性质：必做4、实验类型：基础5、实验目的：熟练掌握视图、存储过程与触发器的建立、查询、修改、删除与使用方法。 6、实验内容：（1） 创建显示学生成绩的视图，显示结果包含学生学号、学生姓名、课程号、课程名称、教师编号、教师姓名、成绩。修改该视图使其只查询出成绩不及格的相关信息。create view 学生成绩 asselect distinct student.s_no,student.s_name,course.course_no,course.course_name,teacher.t_no,teacher.t_name,choice.scorefrom student,course,teacher,choice,teachingwhere student.s_no=choice.s_no and course.course_no=choice.course_no and teaching.t_no=teacher.t_no and teaching.course_no=course.course_noselect * from 学生成绩drop view 学生成绩修改该视图使其只查询出成绩不及格的相关信息。 alter view 学生成绩as select distinct student.s_no,student.s_name,course.course_no,course.course_name,teacher.t_no,teacher.t_name,choice.scorefrom student,course,teacher,choice,teachingwhere student.s_no=choice.s_no and course.course_no=choice.course_no and teaching.t_no=teacher.t_no and teaching.course_no=course.course_no and score=100select * from 学生成绩 （2） 建一存储过程，利用传入参数传入教师性别，显示教师表中不同性别的教师数量，执行它。 (1)create procedure techparameter char(2)as select t_sex,count(*) 数量from teacherwhere t_sex=parametergroup by t_sex execute tech 男 drop proc tech （2）create procedure techparameter char(2)as select count(*) 数量from teacherwhere t_sex=parameterexecute tech 男 drop proc tech（3） 建一级联删除表级、行级触发器，当删除某个学生的信息时，将成绩表中该学生的成绩一并删除，比较表级与行级触发器的结果。 create trigger alongon studentinstead of deleteas delete from choice where choice.s_no in(select s_no from deleted)delete from student where student.s_no in(select s_no from deleted)select * from studentselect * from cho