SQL高级应用

一、触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。它可与特定表上的INSERT、UPDATE、DELETE操作或组合相关联。

与存储过程不一样，触发器与单个表相关联。

CREATE TRIGGER customer_stateON CustomersFOR INSERT,UPDATEASUPDATE CustomersSET cust_state=UPPER(cust_state)WHERE Customers.cust_id=inserted.cust_id;#创建一个触发器，它对所有的INSERT UPDATE操作，将Customers表中的cust_state列转换为大写

触发器由事件触发，而不是程序调用或手工启动。使用触发器可以用来保证数据的完整性和有效性，完成比约束更复杂的数据约束。

根据SQL语句的不同，触发器可分为两类：DML触发器和DLL触发器  

DML触发器是当数据库服务器发生数据操作语言事件时执行的存储过程，有After和Instead Of两种触发器。After触发器是在记录改变之后被激活触发的一种触发器，Instead Of触发器是在记录变更之前，去执行触发器本身所定义的操作，而不是执行原来SQL语句的操作。

DLL触发器是在响应数据定义语言事件时执行的存储过程。

二、索引

索引是提高数据库查询速度的机制，它是在一个数据库的表或视图上按照某个关键字段的值，升序或降序排列创建的对象。索引用来排序数据以加快搜索和排序操作的速度，主键数据总是排序的，所以按照主键检索特定行总是一种快速有效的操作。

可以在一个或多个列上定义索引,使DBMS保存其内容的一个排过序的列表，DBMS搜索排过序的索引，找到匹配的位置，然后检索这些行。

CREATE INDEX prod_name_indON Products(prod_name);

索引的类型有聚焦索引和非聚焦索引。聚焦索引是表中的行的物理顺序与键值的逻辑顺序一样，一个表只能有一个聚焦索引。与非聚焦索引相比，聚焦索引一般情况下可以获得更多的数据访问速度。非聚焦索引是数据存储与索引存储不在同一个地方。

尽管索引可以有效的提高下旬效率，但是过多的索引也会带来许多不利的问题。首先，创建索引和维护索引要耗费时间、空间；其次数据表占据空间之外，每一个索引还需要占据一定的物理空间；最后，当对表中数据进行增加、删除和修改的时候，索引也要动态的维护，从而降低了数据的维护速度。

三、回滚

为了保证在应用程序、数据库或系统出错后，数据库能够被还原，以保证数据库的完整性，所以需要进行回滚。回滚（ROLLBACK)就是在事务提交之前将数据库恢复到事务修改之前数据库数据状态。

回退到保留点的语法：ROLLBACK TRANSACTION 保留点
（不同DBMS不同） ROLLBACK TO 保留点

注意回滚与撤销的区别。回滚就是在事务提交之前将数据库恢复到事务修改之前数据库数据状态，其中可能会使用UNDO日志进行回滚。撤销是一种记录日志的方式，并不是主要服务于事务回滚，而是主要用于系统从故障中恢复。

四、游标

有时需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可根据需要滚动或浏览其中的数据。它实际是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标由结果集和结果集中指向特定记录的游标位置组成。游标允许对结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作。

DECLARE CustCursor CURSOR --创建游标FORSELECT *FROM CustomersWHERE cust_email IS NULL;

OPEN CURSOR CustCursor;--执行查询，存储检索出的数据以供浏览和滚动FETCH CustCursor INTO CustRecord;--检索当前行（自动从第一行开始），放到声明的变量中，不做任何处理CLOSE CustCursor;--关闭游标

使用游标的优点：

（1）在使用游标的表中，对行提供删除和更新的能力

（2）游标将面向集合的数据库管理系统和面向行的程序设计连接了起来

五、约束

约束：管理如何插入或处理数据库数据的规则。DBMS通过在数据库表上施加约束来实施引用完整性，大多数约束是在表定义中定义的，还可使用CONSTRAINT语法在表定义外约束。

CREATE TABLE Vendors( vend_id char(20) NOT NULL PRIMARY KEY,--主键约束  vend_name char(50) NOT NULL,  cust_id char(10) NOT NULL REFRENCES Customers(cust_id),--外键约束  vend_address char(50) NULL UNIQUE,--唯一约束  quantity INTEGER NOT NULL CHECK(quantity>0) --检查约束）；

主键：保证一列或一组列中的值是唯一的，而且永不改动

外键：表中的一列，其值必须列在另一个表的主键中

唯一约束：用来保证一列或一组列中的数据是唯一的，但与主键有区别

检查约束：用来保证一列或一组列中的数据满足一组指定的条件

约束是用来实施引用完整性的重要部分，索引可以改善数据检索的性能，触发器可用来执行运行前后的处理，安全选项可用来管理数据访问。