SQL数据管理实验指导书

实验一 SQL Server 基础一实验目的1了解安装、启动和终止SQL Server的方法。2了解“服务管理器” 、“企业管理器”及“查询分析器”基本使用方法。3掌握SQL Server 中数据库、及数据表的建立与管理方法。 二、SQL Server简介SQL Server是一种基于客户/服务器的关系型数据库管理系统，它使用Transact-SQL语言在服务器和客户机之间传送请求和答复。SQL Server使用客户/服务器体系结构把所有的工作负荷分解成在服务顺上的任务和在客户机上的任务。客户机应用程序负责商业逻辑和向用户提供数据，一般运行在一个或者多个客户机上，但是也可以运行在服务器上。服务器管理数据库和分配可用的服务器资源，例如内存、网络带宽和磁盘操作。客户机应用程序界面允许应用程序在单独的客户机上运行，并且通过网络与服务器通讯。SQL Server可以在多种操作系统上运行。SQL Server的服务器环境可以是Windows NT或者Windows 9x，其客户机环境可以是WindowsNT、Windows9x、第三方平台和Internet浏览器三三、SqlServer2000的安装购买SQL Server2000的软件安装盘，参照下列示意图：第1步：选择“安装SQLSERVER2000组件”第2步：选择“安装数据库服务器”第3步：在欢迎界面里，点击下一步第4步：计算机名界面中，选择“本地计算机”，点击下一步第5步：在“安装选择”中，选择“创建新的SQLSERVER实例，或安装客户端工具”，点击下一步第6步：输入你的姓名如“WWL“，点击下一步第7步：在安装定义里边，选择“服务器和客户端工具“，点击下一步第8步：在“实例名”界面里，选择默认，点击下一步第9步：在“安装类型”中，选择你安装的路径（点击浏览按钮），点击下一步第10步：照图选择各项，点击下一步第11步：照图选择各项，然后在输入密码里，输入你的密码（自己一定要记住），点击下一步第12步：点击下一步第13步：在许可模式里，选择“每客户”，100设备，点击继续完成安装！SQL Server包括3个服务，即MSSQLServer、SQL Server Agent和Microsoft.Distributed Transaction Coordinator（MS DTC）服务。MSSQLServer服务就是SQL Server的RDBMS（关系数据库管理系统）；SQL Server Agent服务用于管理任务、警报和操作员；MS DTC服务是分布式事务协处理器，用于保证在多个服务器之间事务同时完成或者取消，协调多服务器之间的事务一致性。本课程实验主要介绍其中的MSSQLServer服务。在SQL Server系统中，有两种类型的数据库：系统数据库和用户数据库。系统数据库是系统提供的数据库，包含了记录系统中所有系统信息的系统表。用户数据库就是用户自己创建的数据库，包含了用户的所有数据。系统安装之后，生成了四个系统数据库和两个样板数据库。这四个系统数据库分别是master、model、msdb和tempdb。Master数据库是最重要的系统数据库，它记录了系统中所有系统级的信息。Model数据库是一个模板数据库，它包含了用户数据库中应该包含的所有系统表的结构。当创建用户数据库时，系统自动把Model数据库中的内容拷贝到新建的用户数据库中。Msdb数据库记录了有关SQL Server Agent服务的信息，例如作业、警报、操作员等信息。Tempdb数据库是一个临时数据库，当某些操作有中间数据时，这些中间数据就保存在该数据库中。两个样板数据库是pubs和Northwind。Pubs数据库记录了一个虚构的出版公司的数据信息，Northwind数据库保存了一个虚构的贸易公司的数据信息。一个数据库中，有许多数据对象。各对象的种类和相应描述如下表所示：数据库对象描述表由行和列组成，是存储数据的地方数据类型限制输入到表中的数据类型约束有主键、外键、唯一键、缺省和检查五种类型的约束缺省自动插入的常量值规则限制表中列的取值范围索引加快检索数据的方式视图查看一个式者多个表的一种方式存储过程一组预编译的SQL语句，可以完成指定的操作触发器一种特殊类型的存储过程，当某个操作影响到它保护的数据时，它就会自动触发执行SQL Server安装之后，其目录和文件的默认位置是：安装路径下Microsoft SQL ServerMSSQL，其中几个主要目录是：BACKUP（备份文件的默认位置）、Data（数据库文件）、LOG（错误日志文件）。SQL Server的详细介绍请查阅SQL Server使用手册或帮助。四、操作步骤1SQL Server服务管理器 SQL Server服务管理器（Service Manager）是在服务器端实际工作时最有用的实用程序。SQL Server服务管理器的主要作用是用来启动数据库服务器的实时服务、暂停和停止正在运行的服务，或在暂停服务后继续服务。 从SQL Server 2000程序组中启动“服务管理器”，弹出如下对话框，在“服务器”列表框中，选择输入相应的服务器名称。在“服务”列表框中，选择输入相应的服务，此处可选择“SQL Server服务”。单击启动/继续可以启动相应服务，单击停止可停止相应服务。如图1-1：图1-12企业管理器（Enterprise Manager）企业管理器是SQL Server中最重要的一个管理工具。用户和系统管理员可以使用它来管理网络、计算机、服务和其它系统组件。企业管理器不仅能够配置系统环境和管理SQL Server，而且由于它能够以层叠列表的形式来显示所有的SQL Server对象，因而所有SQL Server对象的建立与管理都可以通过它来完成。 为了管理SQL Server必须使用“企业管理器”注册服务器。方法如下： 从SQL Server 2000程序组中启动“企业管理器”。如图1-2 在窗口左栏中右击某一个服务器组，单击新建SQL Server注册。 按提示执行以下三项操作：选择服务器选择身份验证模式（SQL Server身份验证，指定登录名和密码）指定SQL Server组。注册了服务器后，通过右击目录树中的服务器，然后从弹出的快捷菜单中选择“属性”，可以设置大多数服务器常用的选项。详细设置参数参阅SQL Server系统管理手册或帮助。如图1-3图1-2图1-34查询分析器介绍SQL Server 2000的查询分析器（Query Analyzer）可以使用户交互式地输入和执行各种Transact-SQL语句，并且迅速地查看这些语句的执行结果，来完成对数据库中的数据的分析和处理。“查询分析器” ，可以通过图形界面执行SQL语句。“查询分析器”是一种可以完成多种工作的多用途工具，在其中可以交互式地输入和执行各种SQL语句，并且可以在一个窗口中同时查看SQL语句和其结果集；可以在同时执行多个SQL语句，也可以执行脚本文件中的部分语句；此外还提供了一种图形化分析查询语句执行规则的方法，可以报告由“查询分析器”选择的数据检索方法，并且可以根据查询规划调整查询语句的执行等。打开方法：单击工具/SQL查询分析器。如下图1-5所示，可在工具栏中选择要操作的数据库（如master），并在查询窗口中输入相应的SQL语句（select * from sysfiles），点击工具条的绿色小三角运行你输入的SQL语句。此时在查询窗口的下部会显示执行结果。图1-5查询结果的显示方式，可通过查询菜单设置：文本显示结果、以表格显示结果或结果保存为文件。5查看数据库及浏览表中记录查看已建立的数据库运行企业管理器，在窗口左栏中选择一个已注册的数据库服务器。单击左侧的+号，选择“数据库”选项，可以看到在数据库服务器中已建立的数据库。注意：如右击“数据库”并选择“新建数据库” ，可建立新的数据库。 浏览表中记录 选择某数据库并选择数据库中的“表”选项 。选择要浏览的表，按右键并选择：打开表/返回所有行 ，即可浏览表中记录。注意：可右击“表”选项，在弹出的菜单中选择“新建表” ，可建立新表。6、数据库建立与管理（1）启动、并打开SQL Server。 从SQL Server 2000程序组中启动SQL Server，在弹出的对话框中选择输入:服务器名称和相应服务，单击启动/继续。选择打开已注册的数据库服务器。（2）使用图形界面创建数据库和表 使用图形界面（企业管理器）并依据内容1中的关系模型创建数据库和表。创建数据库 运行企业管理器，单击窗口左栏树下面的，直到出现“数据库”选项。 右击“数据库”选项，点击“新建数据库”，在弹出对话框中输入你要创建的数据库名(例如mybatabase)后直接按确定键。这时系统就会创建一个名为mydatabse的数据库。 创建表 在企业管理器中，选择数据库。右击“表”选项。 选择“新建表” ，输入：列名、数据类型、长度等，并存盘。注意：通过选择“保存更改脚本”，可将建表过程保存为脚本文件，供以后用查询分析器建表。（3）使用查询分析器创建表. 从SQL Server程序组中启动“查询分析器”，出现如下图2-1登录界面，图2-1输入SQL Server名，登录名sa，密码：无然后按确定。. 在出现的“查询窗口”中选择你要操作的数据库，然后就可以在下面的编辑框中输入SQL语句建立相关表。试用此方法建立：学生-课程数据库，基本表见教材59页。. 点击工具条的绿色小三角（或按F5）就可以运行你输入的SQL语句。 此处，也可打开并执行上题中保存的脚本文件来实现建表。7. 数据库的备份与还原数据库的备份： 运行企业管理器，右击你所要备份的数据库，选择“所有任务”中的“备份数据库”。 输入备份数据库的名称，点击“添加”后输入备份数据库存放的目录和文件名。按确定按钮，备份完成。数据库的还原：一旦数据库遭到破坏，可用备份的数据库进行恢复。 运行企业管理器，右击“数据库”，在“所有任务”中选“还原数据库”。 在“选项”页中选中“在现有数据库上强制还原”。 在“常规”页中输入恢复的数据库名，选择“从设备” 还原，点击“选择设备”后，再点击“添加”按钮选择你所要恢复的数据库备份文件名。点击确定按钮完成恢复。 如果原有数据库被删除，恢复异常。则可先建立一个同名数据库，再在数据库上进行强制还原即可。8、数据的导入与导出9、数据库的分离与附加实验二 SQL编程一实验目的1. 熟练掌握SQL编程方法2. 掌握Transact-SQL基本语法3. 掌握SQL-查询分析器的基本用法4. 掌握SQL中常用统计函数的用法5. 掌握游标的基本用法二实验要求1. 回顾SQL语法2. 熟悉SQL Server2000的基本操作3. 预习Transact-SQL基本语法4. 预习游标用法三实验内容1. 用统计函数sum、avg、min、max、count等进行查询2. 统计函数结合group by进行查询3. 实现Transact-SQL中的判断和循环结构编程4. 用游标实现一个统计功能四实验指导1. 启动SQL-查询分析器，可以从开始菜单中启动，也可以从企业管理器中启动。1). 从开始菜单中启动，如下图2-1所示，之后出现图2-2所示的界面，根据实际情况选择各选项，点击确定即可。图2-1图2-22). 从企业管理器中启动，先启动企业管理器，然后选择则“工具”“SQL-查询分析器”即可，如下图2-3。图2-32. SQL-查询分析器的使用。1). SQL-查询分析器有三个窗口：对象浏览器(可选)、查询窗口和结果显示窗口，如下图2-4所示。其中对象浏览器窗口中显示的是各数据库中的对象与一些公共对象；查询窗口是一个输入窗口，用来接受用户输入各查询语句，可以输入多条查询语句，选择执行其中的一条或是多条；结果显示窗口用于显示执行后的结果信息，也可用于显示查询的执行计划信息，另外可以选择将查询后的结果显示为表格形式或是文本形式，也可以将查询的结果以文件的方式保存下来。图2-42). 使用之前可先设定好各参数，如所使用的数据库(在图2-4所显示的下拉窗口中选择即可)、查询结果的显示方式等。在查询窗口中输入查询语句，可以按下Ctrl+F5键或是点击图2-4中所示的蓝色对勾按钮进行语句的语法分析，也可以直接按下F5键或是点击图2-4中所示的绿色三角按钮执行其中选中的一条或是多条语句，若无语法错误，结果将显示在查询窗口下面的结果显示窗口中。 3. 查询及函数举例 select stuid,name from studentdrop table student1-排序：select * from student order by stuidselect distinct adressfrom student-计数：select adress,count(adress) 人数 from student group by adress-求最大值：select max(age)from student-select name from student where age=(select max(age) from student where sex=m) -平均值：select * from studentwhere age(select avg(age) from student) order by age desc4. 游标的使用3）. 声明游标在这一步中，需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。形式1（ANSI92）DECLAREcursor_nameINSENSITIVESCROLLCURSORFORselect_statementFORREADONLY|UPDATEOFcolumn_list形式2DECLAREcursor_nameCURSORLOCAL|GLOBALFORWARD_ONLY|SCROLLSTATIC|KEYSET|DYNAMICREAD_ONLY|SCROLL_LOCKS|OPTIMISTICFORselect_statementFORREADONLY|UPDATEOFcolumn_listINSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。如果忽略该选项，则游标只能向前滚动（next）。Select_statement指明SQL语句建立的结果集。TransactSQL语句COMPUTE、COMPUTEBY、FORBROWSE和INTO在游标声明的选择语句中不允许使用。READONLY指明在游标结果集中不允许进行数据修改。UPDATE关键字指明游标的结果集可以修改。OFcolumn_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。FORWARD_ONLY指明游标只能向前滚动。STATIC的游标与INSENSITIVE的游标是相同的。KEYSET指明选取的行的顺序。SQLServer将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。DYNAMIC指明游标将反映所有对结果集的修改。SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。注意：如果在SELECT语句中使用了DISTINCT、UNION、GROUPBY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。如果SELECT语句包含了ORDERBY，而被ORDERBY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQLANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。ii.打开游标打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：OPENGLOBALcursor_name|cursor_variable_nameGLOBAL指明一个全局游标。Cursor_name是被打开的游标的名称。Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。在游标被打开之后，系统变量cursor_rows可以用来检测结果集的行数。cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果cursor_rows为，则绝对值为）为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。iii.从游标中取值在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name,nNEXT指明从当前行的下一行取值。PRIOR指明从当前行的前一行取值。FIRST是结果集的第一行。LAST是结果集的最后一行。ABSOLUTEn表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。RELATIVEn表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。INTOcursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。每一次FETCH的执行都存储在系统变量fetch_status中。如果FETCH成功，则fetch_status被设置成0。fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。fetch_status可以用来构造游标处理的循环。例如：DECLAREinamechar(20),fnamechar(20)OPENauthor_curFETCHFIRSTFROMauthor_curINTOiname,fnameWHILEfetch_status=0BEGINIFfname=AlbertPRINT“FoundAlbertRinger”ELSEPrint“OtherRinger”FETCHNEXTFROMauthor_curINTOiname,fnameENDiv.关闭游标CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：CLOSEGLOBALcursor_name|cursor_variable_namev.释放游标游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：DEALLOCATEGLOBALcursor_name|cursor_variable_name下面给出游标的一个完整的例子：USEmasterGOCREATEPROCEDUREsp_BuildIndexesASDECLARETableNamesysname,msgvarchar(100),cmdvarchar(100)DECLAREtable_curCURSORFORSELECTnameFROMsysobjectsWHEREtype=uOPENtable_curFETCHNEXTFROMtable_curINTOTableNameWHILEfetch_status=0BEGINIFfetch_status=-2CONTINUESELECTmsg=“Buildingindexesfortable”+TableName+”PRINTmsgSELECTcmd=“DBCCDBREINDEX(”+TableName+”)”EXEC(cmd)PRINT“FETCHNEXTFROMtable_curINTOTableNameENDDEALLOCATEtable_curGO下面的脚本将为PUBS数据库执行sp_BuildIndexesUSEpubsGOEXECap_BuildIndexes注意：上面也是创建用户定义的系统存储过程的示例。5. 按如下所给要求实现各查询，查询中所使用的数据库为Northwind。1). 查询表Products中供货商代号为2 的产品的平均价格2). 查询表Suppliers中各国家的供应商数目3). 查询表Orders中到目前为止所用的所有运费4). 查询表Suppliers中供应商数目最多和最少的国家名称5). 将表Products中库存大于100的产品价格下调30%、50到100之间的下调20%、余下的下调10%。6). 利用游标将表Suppliers中各国家的供应商名称分别以字符串的形式输出。实验三 存储过程与触发器一实验目的6. 了解存储过程和触发器的基本作用7. 掌握存储过程和触发器的创建方法8. 掌握存储过程和触发器的运行机制9. 了解常见的系统存储过程二实验要求5. 熟悉SQL语法6. 熟练掌握企业管理器管理对象的方法7. 掌握SQL查询分析器的用法8. 了解存储过程和触发器的相关知识三实验内容5. 创建一个存储过程6. 创建一个触发器四实验指导3. 存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。创建存储过程的语法及参数如下：1). 语法 CREATE PROCEDURE procedure_name ;number parameter data_type VARYING=defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n2). 参数procedure_name:新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。number:是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。parameter:过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。使用 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。data_type:参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。VARYING:指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。default:参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、 和 ）。OUTPUT:表明参数是返回参数。该选项的值可以返回给 EXECUTE。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。4. 触发器是一种特殊的存储过程，在用户试图对指定的表执行指定的数据修改语句时自动执行。创建触发器的语法及参数如下：1). 语法CREATE TRIGGER trigger_name ON table|view WITH ENCRYPTION FOR|AFTER|INSTEAD OFINSERT,UPDATEWITH APPENDNOT FOR REPLICATIONASIF UPDATE(column)AND|ORUPDATE(column) .n |IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.n sql_statement .n 2). 参数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:指定应该添加现有类型的其它触发器。WITH APPEND 不能与INSTEAD OF触发器一起使用，或者，如果显式声明AFTER触发器，也不能使用该子句。只有当出于向后兼容而指定FOR时（没有INSTEAD OF或AFTER），才能使用WITH APPEND。NOT FOR REPLICATION:表示当复制进程更改触发器所涉及的表时，不应执行该触发器。AS:是触发器要执行的操作。sql_statement:是触发器的条件和操作。触发器条件指定其它准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。当尝试DELETE、INSERT或UPDATE操作时，Transact-SQL语句中指定的触发器操作将生效。触发器可以包含任意数量和种类的 Transact-SQL语句。触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的Transact-SQL语句常常包含控制流语言。CREATE TRIGGER语句中使用几个特殊的表：deleted和 inserted是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；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)值。column:是要测试INSERT或UPDATE操作的列名。该列可以是SQL Server支持的任何数据类型。 IF (COLUMNS_UPDATED():测试是否插入或更新了提及的列，仅用于 INSERT或UPDATE触发器中。COLUMNS_UPDATED返回varbinary 位模式，表示插入或更新了表中的哪些列。COLUMNS_UPDATED函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含8列以上，则COLUMNS_UPDATED返回多个字节，最左边的为最不重要的字节。在INSERT操作中 COLUMNS_UPDATED将对所有列返回TRUE 值，因为这些列插入了显式值或隐性(NULL)值。bitwise_operator:是用于比较运算的位运算符。updated_bitmask:是整型位掩码，表示实际更新或插入的列。例如，表 t1 包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE 触发器，若要检查列C2、C3和C4是否都有更新，指定值14；若要检查是否只有列C2有更新，指定值2。comparison_operator:是比较运算符。使用等号(=)检查updated_bitmask 中指定的所有列是否都实际进行了更新。使用大于号()检查updated_bitmask中指定的任一列或某些列是否已更新。column_bitmask:是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列。5. 使用企业管理器创建和管理存储过程。1). 选择要创建存储过程的数据库结点并展开，右键点击点击存储过程从弹出的菜单中选择“新建存储过程(S)”即可如下图3-1所示，之后出现图3-2所示的界面。图3-1图3-2 在图3-2所示的“文本(T)”框中填写创建存储过程的语句，之后必须进行语法检查。也可以在权限对话框中选择允许哪些用户执行此存储过程，如图3-2所示，点击确定按钮退出创建对话框。图3-32). 企业管理器中也可以修改已有的存储过程，选择数据库中的“存储过程”结点并展开，企业管理器中右边的会列出此数据库中所有的存储过程，选中要修改的存储过程，右键点击选择弹出菜单中的“属性”选项(如图3-4所示)即可出现图3-2所示的窗口，余下过程类似于存储过程的创建。图3-43. 使用企业管理器创建和管理触发器。7). 选中数据库结点并展开，找到要创建触发器的表或视图，右键点击，从弹出的菜单中选择“所有任务(K)”的子菜单中选择“管理触发器(T)”命令，如图3-5所示，系统将弹出如图3-6所示的对话框。图3-5图3-6 在图3-6所示的“文本(T)”框中填写创建触发器的语句，之后必须进行语法检查。也可以删除已有的触发器，点击确定按钮退出创建对话框。8). 企业管理器中也可以修改或删除已有的触发器，在图3-6中的“名称(N)”下拉框中选择要修改或删除的触发器，修改过后的触发器必须进行语法检查，之后点击“应用”按钮即可，若要删除直接点击“删除(D)”按钮即可。4. 在查询在SQL-查询分析器中创建存储过程和触发器。按照前面所给的语法要求输入SQL语句即可。6. 存储过程和触发器的运行必须在SQL语句中调用存储过程才可以运行之，可以使用“EXE(CTUE)”命令来运行存储过程，触发器的运行是由系统自动完成的，无需用户的参与，当某些事件被触发是，系统就调用相应的触发器。7. 按照如下所给的要求实现存储过程和触发器，并运行之。1). 创建一个存储过程，查询表Orders中在某一段日期内所有交易的公司、产品信息及平均交易额。2). 创建一个触发器，使得当删除了表Suppliers中的某一供应商时，与其相关的数据都被删除。实验四 SQL约束管理一实验目的1掌握SQL Server 中数据库约束的管理方法。 2. 掌握数据库三类完整性，在SQL 中的实现二实验内容数据库的完整性描述为数据库内容的完整性约束集合，其中完整性约束指数据库的一个状态是否合理。数据库系统检查数据的状态和状态转换，判定它们是否合理，是否应予接受。对一个数据库操作，要判定其是否符合完整性约束，全部判定无矛盾时才可以执行。数据完整性包括实体完整性、域完整性、引用完整性、用户定义完整性。 （一）.用户自定义约束1、DEFAULT 约束DEFAULT通过指定列的默认值，每个列中只能有一个DEFAULT约束。如果列已有默认值，必须除去旧默认值后才能添加新默认值。DEFAULT约束只用于INSERT INTO 语句，当在INSERT 语句中没有指定一个值，DEFAULT约束在列中自动输入一个值。 CONSTRAINT contraint_name DEFAULT contraint_expression作业：为学生表中的年龄字段设置默认约束，默认年龄为20岁2、CHECK约束CHECK约束是通过限制可输入到一列或多列中的可能值来强制实现域的完整性。可用于INSERT 和 UPDATE语句。实现语法：CONSTRAINT contraint_nameCHECK logic_expression作业：在学生表中增加一个邮政编码字段，要求输入的数据为六位的数字3、UNIQUE约束使用 UNIQUE 约束确保在非主键列中不输入重复值。在一个表内可以定义多个 UNIQUE 约束，在允许空值的列上可以定义 UNIQUE 约束。例如在职员表中，职员代码是主键，另有一列职员身份证，如果想使职员的身份证号也不重复，就需要建立一个UNIQUE约束。作业：设定学生表的姓名字段具有唯一性 （二）实体完整性 PRIMARY KEY约束 表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。如果 PRIMARY KEY 约束定义在不止一列上，则一列中的值可以重复，但 PRIMARY KEY 约束定义中的所有列的组合的值必须唯一。当向表中的现有列添加 PRIMARY KEY 约束时，SQL Server检查列中现有的数据以确保现有数据遵从主键的规则，要确保该列中无空值并且没有重复值。作业为学生表、课程表成绩表设定主码约束（三）参照完整性（FOREIGN KEY约束）实现语法：CONSTRAINT constraint_name FOREIGN KEY ( column ,.n ) REFERENCES ref_table ( ref_column ,.n ) 为成绩表设定主码和外码约束（四）约束的级联操作 通过级联引用完整性约束，可以使删除或更新外键所指向的键时，级联删除或更新外键所在的行。在CREATE TABLE 和 ALTER TABLE 语句的 REFERENCES 子句中，可以定义级联引用完整性约束。实现语法： ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION 1、尝试删除学生表中的与成绩表中有关联的记录2、使用级联引用完整性约束，删除学生表中的记录，同时删除该学生的所有成绩3、使用级联修改，实现类似上题的情况，并比较与触发器操作的区别实验五 SQL的安全管理一实验目的1掌握SQL Server 中数据库安全性管理的策略与方法。 2、掌握SQL Server中数据库备份与还原的方法二实验内容数据库的安全主要包括几个方面的内容：安全模式,SQL Server服务器安全, SQL Server登录帐号, 角色, SQL Server用户和许可等内容. 安全模式包括如下内容:(1) 安全规划:(2) 选择安全的形式:指选择用户登录的登录认证方式.1认证模式 认证是指当用户访问数据库系统时，系统对该用户的帐户和口令的确认过程。SQL Server可以识别两种类型的登录认证机制。 SQL Server认证机制 Windows NT认证机制当使用SQL Server认证机制时，SQL Server系统管理员定义SQL Server的登录帐户和口令。当用户连接SQL Server时，必须提供登录帐户和口令。当使用Windows NT认证机制时，由Windows NT帐户控制用户对SQL Server系统的访问。这时用户不必提供SQL Server的登录帐户和口令就能连接到系统上，但是，在该用户连接之前，SQL Server系统管理员必须将Windows NT帐户定义为SQL Server的有效登录帐户（请参考Windows NT帐户管理方法）。用户可以使用SQL Server企业管理器来设置服务器的认证模式。设置方法参见实验一。2登录管理 登录帐户是基于SQL Server服务器使用的用户名，可控制用户对数据库服务器的访问。在SQL Server中有一个特殊的登录帐户：sa，是系统管理员的简称，它在SQL Server系统和所有数据库中有全部的权限，不能被删除。设置sa帐号密码: 单击相应服务器属下的 “Login”;右单击右窗格的用户名,选择 “属性”.增加登录帐户的两种方法：一是：从Windows NT组和用户中创建登录帐户和创建新的SQL Server登录帐户（参考Windows NT帐户管理方法）。二是：使用SQL Server企业管理器创建登录帐户，方法如下：在SQL Server企业管理器选择一服务器，选择“安全性”文件夹，在“安全性”文件夹中，右击“登录”，选择“新建登录”，则出现如下窗口：在常规选项卡中输入登录名和口令；在服务器角色选项卡中选择相应服务器角色复选框；在数据库访问选项卡中选择要访问的数据库及指定该帐户所属的数据库角色。最后按确定即可。用刚建立的登录帐户登录服务器：先右击服务器，选择断开，再右击服务器，选择连接。按提示输入帐户名和口令即可登录。如果选择连接时不提示输入帐户/口令，可选择强制用户登录时验证帐户和口令（参见5.） (3)配置安全角色 角色：SQL Server2000服务器操作和数据库访问许可的管理单位，角色可以把各个用户汇集成一个单元，以便进行许可管理。SQL Server提供了用于通常管理工作的预定义服务器角色、和数据库角色，以简化对某一个用户授予一些管理许可的工作。可在建立登录帐户时设置。系统管理员给适当用户分配相应的角色是SQL Server服务器和数据库安全的关键之一.SQL Server2000服务器系统管理员或数据库所有者在设置数据库访问许可时,应先创建新的角色并将访问许可集中授予角色,然后再将需要拥有这一许可权限的用户加入到角色中, 加入到角色中的所有用户自动具有角色所拥有的访问许可权限.常用固定服务角色如下角色许可系统管理员(system administrator)执行任意活动数据库创建者(database creator)创建和修改数据库磁盘管理员(disk administrator)管理磁盘文件进程管理员(process administrator)管理系统进程安全管理员(security administrator)管理和审核服务器登录服务器管理员(server administrator)配置服务器端设置安装管理员(setup administrator)安装复制 固定的数据库角色及许可常用固定数据库角色及其许可角色许可Public维护全部默认的许可Db_owner执行任何数据库角色的操作Db_accessadmin增加或者删除数据库用户、组和角色Db_ddladmin增加、修改或者删除数据库对象Db_securityadmin执行语句和对象许可Db_backupoperator备份和恢复数据库Db_datareader读取任意表中的数据Db_datawriter增加、修改或者删除全部表中的数据Db_denydatareader不能读取任一个表中的数据Db_denydatawriter不能修改任一个表中的数据创建角色方法:用户只能创建数据库角色,只在其所在的数据库中有效,对其他数据库无效. 右单击 “角色”授权：授予用户，用户组以及角色某种或某些许可权限，权限：一种对数据库对象的操作权力。用户登录到SQLServer服务器后，其所充当的角色和用户的许可，就决定了它们对数据库对象所能执行的操作。创建应用