sqlserver基础知识

什么是触发器：
???????触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。本节将介绍触发器的基本概念，以及创建和管理触发器的方法。

???????触发器是一个在修改指定表值的数据时执行的存储过程，不同的是执行存储过程要使用EXEC语句来调用，而触发器的执行不需要使用EXEC语句来调用，通过创建触发器可以保证不同表中的逻辑相关数据的引用完整性或一致性。

??????? 触发器与普通存储过程的不同之处在于：触发器的执行是由事件触发的，而普通存储过程是由命令调用执行的。


触发器的优点：
触发器是自动的。当对表中的数据做了任何的修改之后可以立即被激活。
触发器可以通过数据可中的相关表进行层叠更改。
触发器可以强制限制。这些限制比CHECK约束所定义的更复杂，与CHECK约束不同的是，触发器可以应用其他表中的列。


触发器的作用：

??????? 触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，能提供比CHECK约束更复杂的数据完整性，并自定义错误信息。触发器的主要作用有以下几个方面：
强制数据库之间的应用完整性。
级联修改数据库中所有相关的表，自动触发其他与之相关的操作。
跟踪变化，撤销或回滚违法操作，防止非法修改数据。
返回自定义的错误信息，约束无法返回信息，而触发器可以。
触发器可以调用更多的存储过程。

触发器的分类：

SQL Server提供了两种触发器选项，即DML触发器和DDL触发器。

数据操作语言（DML——DataManipulation Language）触发器：

DML触发器是对SQLServer 2000触发器的继承，它包括以下3种类型：

AFTER触发器?在执行了INSERT、UPDATE或DELETE语句操作之后执行AFTER触发器。指定AFTER与指定FOR相同，而后者是SQL Server早期版本中唯一可使用的选项。AFTER触发器只能在表上指定。

INSTEAD OF触发器?执行INSTEAD OF触发器以代替引发触发器的数据库操作。

CLR触发器?可以是AFTER触发器或INSTEADOF触发器，还可以是DDL触发器。CLR触发器将执行在托管代码（在.NET Framework中创建并在SQL Server中上载的程序集的成员）中编写的方法，而不用执行Transact-SQL存储过程。


数据定义语言（DDL——Data Definition Language）触发器：

DDL触发器响应数据定义语言（DDL）语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。

DML触发器是针对INSERT、UPDATE和DELETE等数据库操作语句进行触发，而DDL则是针对CREATE、ALTER和DROP等数据库定义语句进行触发。

创建触发器：

可以使用CREATE TRIGGER语句来创建触发器。它的基本语法结构如下：

CREATETRIGGER<触发器名>

ON{ <表名>| <视图名>}

[WITH ENCRYPTION ]

{

????{ { FOR | AFTER | INSTEAD OF } {[INSERT ] [,] [ UPDATE ] }

????????AS

????????<SQL语句>[ ...n]

????}

}

1.<触发器名>必须符合标识符规则，并且在数据库中必须唯一。可以选择是否指定触发器所有者名称。

2.<表名>| <视图名>是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。可以选择是否指定表或视图的所有者名称。

3.WITH ENCRYPTION可以对触发器进行加密处理。

4.AFTER指定触发器只有在触发SQL语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。如果仅指定FOR关键字，则AFTER是默认设置。不能在视图上定义AFTER触发器。

5.INSTEAD OF指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。

在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEADOF触发器。

6.{ [DELETE] [,] [INSERT] [,] [UPDATE] }是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。

7.AS指定触发器要执行的操作。

8.<SQL语句>是触发器的条件和操作。触发器条件指定其他准则，以确定DELETE、INSERT或UPDATE语句是否导致执行触发器操作。当尝试DELETE、INSERT或UPDATE操作时，Transact-SQL语句中指定的触发器操作将生效。

例：

在表Employees中创建一个INSERT触发器，如果插入记录的Dep_Id值在表Departments中不存在，则不执行插入操作，并提示用户，具体代码如下：

USE HrSystem

GO

CREATE TRIGGER insert_Employees ON Employees

FOR INSERT

AS

--从表inserted中获取新插入记录的部门编号

DECLARE @depidint

DECLARE @depname varchar(100)

SELECT @depid = Dep_Id FROM inserted

--判断插入的部门编号是否存在

SELECT @depname=Dep_name FROM Departments WHERE Dep_id=@depid

IF @depname IS NULL

BEGIN

PRINT '指定部门不存在，请选择具体部门！'?? -- 提示错误信息

ROLLBACK TRANSACTION? -- 回滚操作

END

GO

?

CREATEPROCEDURE 语句基本语法如下：

CREATEPROCEDURE [schema_name] procedure_name [;number]

{@parameter data type}

[VARYING ] [=default ] [OUT] [OUTPUT] [READONLY]

[ WITH<ENCRYPTION] | [ RECOMPILE] | [ EXECUTE AS Clause ] > ]

[ FORREPLICATION ]

AS <sql_statement>

?

procedure_name：新存储过程的名称，并且在架构中必须唯一。可在procedure_name前面使用一个数字符号“#”来创建局部临时过程，使用两个“#”来创建全局临时过程。对于CLR存储过程，不能指定临时名称。

number:可选整数，用于对同名的过程分组。例如称为orders的应用程序可能使用orderproc;1、orderproc;2等过程，dropprocedure orderproc语句将删除整个组。

@parameter：存储过程中的参数。在CREATEPROCEDURE 语句中可以声明一个或多个参数。

data_type：指定数据类型。

default：存储过程中参数的默认值。

output：指示参数的输出参数。此选项可返回给调用EXECUTE。

RECOMPILE：表明SQLServer 不会保存该存储过程的执行计划，该存储过程没执行一次都要重新编译。

ENCRYPTION：表示加密后的syscomments表，该表的text字段的包含createprocedure语句的存储过程文本。使用ENCRYPTION关键字无法通过查看syscomments表来查看存储过程的内容。

Forreplicaation：用于指定不能在订阅服务器上执行为复制创建的存储过程。

AS：用于指定该存储过程要招待的操作。

sql_statement:是存储过程中要包含的任意数目和类型的Transact-SQL语句。

?

例如：

创建查看test数据库中fruits表的存储过程，输入语句如下：

USEtest；

Go

createprocedure SelProc

AS

select* from fruits；

Go

???????SQL Server 2012中的存储过程是使用T-SQL代码编写的代码段。在存储过程中可以声明变量、执行条件判断语句等其他编程功能。SQLServer 2012 中有多种类型的存储过程，总的可以分为如下3类：系统储存过程、用户存储过程和扩张储存过程。

系统存储过程：

??????系统存储过程是由SQL Server系统自身提供的存储过程，可以作为命令执行各种操作。存储过程主要用来从系统中获取信息，使用系统存储过程完成数据库服务器的管理工作，并且以sp_开头，系统存储过程定义在系统定义和用户定义的数据库中，在调用时不必在存储过程前加数据库限定名。

??????例如，sp_rename系统存储过程可以更改当前数据库中用户创建对象的名称；sp_helptext存储过程可以显示规则、默认值或视图的文本信息。SQLServer 服务器中许多的管理工作都是通过执行系统存储过程来完成的，许多系统信息也可以通过执行系统存储过程来获得。

??????系统存储过程创建并存放于系统数据可master中，一些系统存储过程只能由系统管理员使用，而有些系统存储过程通过授权可以被其他用户使用。

?

自定义存储过程：

??????自定义存储过程即用户使用T-SQL语句编写的、为了实现某一特定业务需求，在用户数据库中编写的T-SQL语句集合，用户存储过程可以接受输入参数、向客户端返回结果和信息、返回输出参数等。创建自定义存储过程时，存储过程名前”上##“表示创建了一个临时的全局存储过程；存储过程名前加上”#“表示创建局部临时存储过程。局部临时存储过程只能在创建它的会话中使用，会话结束的将被删除。这两种存储过程都被存储在tempdb数据库中。

用户定义存储过程可以分为两类：Transact-SQL和CLR。

Transact-SQL存储过程是指保存的Transact-SQ语句集合，可以接受接受和返回用户提供的参数。存储过程也可能从数据库向客户端应用程序返回数据。

CLR存储过程是指应用Microsoft.NET Framework 公共语言方法的储存过程，可以接受和返回用户提供的参数，他们在.NETFramework程序集中是作为类的公共静态方法实现的。

?

扩展存储过程：

??????扩展存储过程是以在SQLServer 环境外执行的动态链接库来实现的，可以加载到SQLServer 实例运行的地址空间执行，扩展存储过程可以使用SQLServer 扩展存储过程API完成编程。扩展存储过程以前缀”xp_“来标识，对于用户来说，扩展存储过程和普通存储过程一样，可以用相同的方式来执行。

动态链接库（Dynamic Link Library或者Dynamic-linklibrary，缩写为DLL），又称为动态链接库，是微软公司在微软视窗操作系统中实现共享函数库概念的一种实作方式。这些库函数的扩展名是.DLL、.OCX（包含ActiveX控制的库）或者.DRV（旧式的系统驱动程序)。

所谓动态链接，就是把一些经常会共用的代码（静态链接的OBJ程序库）制作成DLL档，当可执行文件调用到DLL档内的函数时，windows操作系统才会把DLL档加载存储器内，DLL档本身的结构就是可执行文件，当程序需求函数才进行链接。通过动态链接方式，存储器浪费的情形将可大幅降低。

事务的含义：

?????? 事务要有非常明确的开始和结束点，SQL Server 中的每一条数据操作语句，例如SELECT、INSERT、UPDATE和DELETE都是隐式事务的一部分。即使只有一条语句，系统也会把这条语句当做一个事务，要么执行所有的语句，要么什么都不执行。

??????? 事务开始之后，事务所有的操作都会写到事务日志中，写到日志中的事务，一般有两种：一是针对数据的操作，例如插入、修改和删除，这些操作的对象是大量的数据；另一种是针对任务的操作，例如创建索引。当取消这些事务操作时，系统自动执行这些操作的反操作，保证系统的一致性。系统自动生成一个检查点机制，这个检查点周期的检查事务日志。如果在事务日志中事务全部完成，那么检查点事务日志中的事务提交到数据库中，并且在事务日志中做一个检查点提交标识；如果在事务日志中，事务没有完成，那么检查点不会将事务日志中的事务提交到数据库中，并且在事务日志中做一个检查点未提交的标识。事务的恢复及检查点保证了系统的完整和可恢复。

?

事务的属性：

??????? 事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性，称为原子性（Atomic）、?? 一致性（Consistent）、隔离性（Isolated）、持久性（Durable），简称ACID属性，只有这样才能构成一个事务。

?

原子性:

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性:

在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性:

两个事务的执行是互不干扰的，一个事务不可能看到其他事务运行时，中间某一时刻的数据。

持久性:

在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

由于一项操作通常会包含许多子操作，而这些子操作可能会因为硬件的损坏或其他因素产生问题，要正确实现ACID并不容易。ACID建议数据库将所有需要更新以及修改的资料一次操作完毕，但实际上并不可行。

事务管理的常用语句：

BEGIN TRANSACTION——建立一个事务

COMMITTRANSACTION——提交事务

ROLLBACK TRANSACTION——事务失败时执行回滚操作

SAVE TRANSACTION——保存事务

BEGIN TRANSACTION 和COMMIT TRANSACTION同时使用，用来表示事务的开始和结束。

?

事务的隔离级别：

??????? 事务具有隔离性，不同事务中所使用的时间必须要和其他事务进行隔离，在同一时间可以有很多个事务正在处理数据，但是每个数据在同一时刻只能有一个事务进行操作。如果将数据锁定，使用数据的事务就必须要排队等待，这样可以防止多个事务互相影响。但是如果有几个事务因为锁定了自己的数据，同时又在等待其他事务释放数据，则造成死锁。

??????? 为了提高数据的并发使用效率，可以为事务在读取数据时设置隔离状态，SQL Server 中的事务隔离状态由低到高分为4个级别。

未授权读取

也称为读未提交（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

授权读取

，也称为读提交（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

可重复读取

可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

序列化

序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

创建索引：

（1）在SQL Server Management Studio中，选择并右击要创建索引的表，从弹出菜单中选择“设计”，打开表设计器。右键单击表设计器，从弹出菜单中选择“索引/键”命令，打开“索引/键”对话框。对话框中列出了已经存在的索引，如下图所示。

（2）单击“添加”按钮。在“选定的主/唯一键或索引”框显示系统分配给新索引的名称。

（3）在“列”属性下选择要创建索引的列。可以选择多达16列。为获得最佳性能，最好只选择一列或两列。对所选的每一列，可指出索引是按升序还是降序组织列值。

（4）如果要创建唯一索引，则在“是唯一的”属性中选择“是”。

（5）设置完成后，单击“确定”按钮。

（6）当保存表时，索引即创建在数据库中。

使用CREATE INDEX语句创建索引：

CREATE[UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX索引名

????ON{表名|视图名} (列名[ ASC |DESC ] [,...n ] )

例：

在数据库HrSystem中为表Employees创建基于IDCard列的唯一索引IX_Employees，可以使用以下命令：

USEHrSystem

GO

CREATEUNIQUE NONCLUSTERED INDEX [IX_Employees]ONdbo.Employees(IdCard)

GO

例：

为表Employees创建基于列IDCard的唯一、聚集索引IX_Employees1，可以使用以下命令：

USEHrSystem

GO

CREATEUNIQUE CLUSTERED INDEX [IX_Employees1] ON [dbo].[Employees](IdCard)

GO

???????需要注意的是，在一个表中只允许存在一个聚集索引。因此，如果表Employees中已经存在一个聚集索引，则执行上面的语句时将会提示下面的错误信息。

消息1902，级别16，状态3，第1行

无法对表'dbo.Employees'创建多个聚集索引。请在创建新聚集索引前删除现有的聚集索引'PK__Employee__263E2DD300551192'。

例：

对表Employees的列Emp_name按照降序创建索引，可以使用以下命令：

USEHrSystem

GO

CREATENONCLUSTEREDINDEX [IX_Employees2] ON [dbo].[Employees]

(

? [Emp_name] DESC

)

GO

在CREATEINDEX语句中使用INCLUDE子句，可以在创建索引时定义包含的非键列，其语法结构如下：

CREATENONCLUSTERED?INDEX 索引名

????ON{ 表名| 视图名 } ( 列名 [ ASC| DESC ] [,...n ] )

?INCLUDE (<列名1>,<列名2>,[,… n])

例：

在表Employees上创建非聚集索引IX_Wage，索引中的键列为Wage，非键列为Emp_name、Sex和Title，具体语句如下：

USEHrSystem

GO

CREATENONCLUSTERED?INDEX IX_Wage

?ON Employees ( Wage )

?INCLUDE (Emp_name,Sex,Title)

GO

例：

在创建索引IX_Wage后，当表Employees中的数据量比较大时，执行下面的SELECT语句将会明显地改进查询效率。

USEHrSystem

GO

SELECTEmp_name,Title,Wage

FROMEmployees

WHEREWageBETWEEN 1000 AND 3000

GO

修改索引：

???????在SQL Server Management Studio中，选择并右击要创建索引的表，从弹出的菜单中选择“设计表”，打开表设计器。右键单击表设计器，从弹出菜单中选择“索引/键”命令，打开“索引/键”对话框，并查看已经存在的索引及修改索引的属性信息。

也可以使用ALTERINDEX语句修改索引，其基本语法如下：

ALTERINDEX { 索引名|ALL }

????ON<表名|视图名>

????{REBUILD ?| DISABLE??| REORGANIZE }[ ; ]

ALTERINDEX语句的参数比较复杂，这里只介绍它的基本使用情况。参数说明如下：

REBUILD指定重新生成索引。

DISABLE指定将索引标记为已禁用。

REORGANIZE指定将重新组织的索引叶级。

例：

要禁用索引IX_Employees，可以使用下面的语句：

USEHrSystem

GO

ALTERINDEXIX_Employees ON Employees DISABLE

GO

删除索引：
??????? 在SQLServer Management Studio中，选择并右击要创建索引的表，从弹出的菜单中选择“设计表”。打开表设计器。右键单击表设计器，从弹出菜单中选择“索引/键”命令，在打开的“索引/键”对话框中列出了已经存在的索引。单击“删除”按钮，即可删除索引信息。

?

? 不同数据库中提供了不同的索引类型，SQLServer中的索引有两种：聚集索引和非聚集索引。聚集索引和非聚集索引的区别是在物理数据的存储方式上。
聚集索引：
?????聚集索引基于数据行的键值，在表内排序和存储这些数据行。每个表只能有一个聚集索引，应为数据行本分只能按一个顺序存储。

??????? 在聚集索引中，表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。聚集索引通常可加快UPDATE和DELETE操作的速度，因为这两个操作需要读取大量的数据。创建或修改聚集索引可能要花很长时间，因为执行这两个操作时要在磁盘上对表的行进行重组。
非聚集索引：

??????? 因为一个表中只能有一个聚集索引，如果需要在表中建立多个索引，则可以创建为非聚集索引。表中的数据并不按照非聚集索引列的顺序存储，但非聚集索引的索引行中保存了非聚集键值和行定位器，可以快捷地根据非聚集键的值来定位记录的存储位置。

?

??????? 无论是聚集索引，还是非聚集索引，都可以是唯一索引。在SQL Server中，当唯一性是.数据本身的特点时，可创建唯一索引，但索引列的组合不同于表的主键。例如，如果要频繁查询表Employees（该表主键为列Emp_id）的列Emp_name，而且要保证姓名是唯一的，则在列Emp_name上创建唯一索引。如果用户为多个员工输入了相同的姓名，则数据库显示错误，并且不能保存该表。

SQL Server 中游标的基本操作

分类： SQLServer 2013-01-28 13:38 230人阅读 评论(0) 收藏 举报

sqlserverSQLSERVERSQL ServerSQLserver

对于游标的基本操作主要有以下内容：

声明游标

打开游标

读取游标数据

关闭游标

获取游标的状态和属性

修改游标结果集中的行

删除游标结果集中的行

删除游标


声明游标：

???????可以使用DECLARE CURSOR语句来声明Transact-SQL服务器游标和定义游标的特性，例如游标的滚动行为和结果集的查询方式等。DECLARECURSOR的语法结构如下：

DECLAREcursor_nameCURSOR

[LOCAL |GLOBAL ]

[FORWARD_ONLY| SCROLL ]

[STATIC| KEYSET | DYNAMIC | FAST_FORWARD ]

[READ_ONLY| SCROLL_LOCKS | OPTIMISTIC ]

[TYPE_WARNING]

FORselect_statement

[FORUPDATE [ OF column_name[,...n] ]]

例：

下面是定义游标的一个简单示例：

USEHrSystem

GO

DECLAREEmployee_Cursor CURSOR

FORSELECT * FROM Employees WHERE Sex = '男'

GO

游标结果集是表Employees中所有的男性员工。

打开游标：

OPEN语句的语法结构如下：

OPEN{ {[ GLOBAL ] cursor_name } |cursor_variable_name }

参数说明如下：

cursor_name已声明的游标的名称。如果指定了GLOBAL，cursor_name指的是全局游标，否则cursor_name指的是局部游标。

cursor_variable_name指定游标变量的名称。

例：

下面是打开游标的一个简单示例：

USEHrSystem

GO

DECLAREEmployee_Cursor CURSOR

FORSELECT * FROM Employees WHERE Sex = '男'

OPENEmployee_Cursor

GO

读取游标中的数据：
读取数据有三种方式：

1.FETCH语句

2.@@FETCH_STATUS函数

3.@@CURSOR_ROWS函数

FETCH:

FETCH语句的功能是从Transact-SQL服务器游标中检索特定的一行。它的语法结构如下：

FETCH

????????[[ NEXT | PRIOR | FIRST | LAST

????????????????|ABSOLUTE { n | @nvar }

????????????????|RELATIVE { n | @nvar }

????????????]

????????????FROM

????????]

{{ [GLOBAL ] 游标名称}| @游标变量名称}

[INTO@variable_name [,...n ] ]

例：

下面是读取游标数据的一个简单示例：

USEHrSystem

GO

DECLAREEmployee_Cursor CURSOR

FORSELECT * FROM Employees WHERE Sex = '男'

OPENEmployee_Cursor

FETCHNEXT FROM Employee_Cursor

GO

例：

以下是使用FETCHLAST读取最后一行数据的示例：

USEHrSystem

GO

DECLAREEmployee_Scroll_Cursor SCROLL CURSOR

FORSELECT * FROM Employees WHERE Sex = '男'

OPENEmployee_Scroll_Cursor

FETCHLAST FROM Employee_Scroll_Cursor

GO

@@FETCH_STATUS函数

可以使用@@FETCH_STATUS函数获取FETCH语句的状态。返回值等于0表示FETCH语句执行成功；返回值等于-1表示FETCH语句执行失败；返回值等于-2表示提取的行不存在。
例：

执行下面的语句可以使用游标获取表Employees中所有男性员工数据。

USEHrSystem

GO

DECLAREEmployee_Scroll_Cursor SCROLL CURSOR

FORSELECT * FROM Employees WHERE Sex = '男'

OPENEmployee_Scroll_Cursor

WHILE@@FETCH_STATUS = 0

BEGIN

FETCHFROM Employee_Scroll_Cursor

END

GO

@@CURSOR_ROWS函数