[计算机]SQL高级.doc

SQL Server一 子查询在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询。子查询分为嵌套子查询和相关子查询两种。嵌套子查询的求解方法是由里向外处理，即每个子查询在其上一级查询处理之前求解，子查询的结果作为其父查询的查询条件。子查询只执行一次，且可以单独执行；嵌套子查询使得可以用一系列简单查询构成复杂的查询，从而明显增强了SQL的查询处理能力。a) 带有比较运算符的嵌套子查询（要求子查询返回的值为一行一列）b) In的嵌套子查询（子查询返回的值为多行一列）c) EXISTS的嵌套子查询：如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false)。d) 相关子查询：子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。子查询不能单独执行。二 视图1. 视图的概念 视图是另一种查看数据库中一个或多个表中的数据的方法。视图基于实际的数据表（基表）或别的视图而创建。视图是一种虚拟表，也就是说，视图中并不存储实际的数据。任何对视图的操作，都会转换为到基表的操作。2. 视图的用途：l 筛选表中的行l 防止未经许可的用户访问敏感数据l 将多个物理数据表抽象为一个逻辑数据表3. 创建视图语法：CREATE VIEW view_nameAS4. 对视图的编辑：因为视图中的数据来源基表，因此对视图进行增删改操作时，其命令中只能包含一个基表的数据。因此通过视图来修改数据有许多的限制，所以在实际开发中一般视图仅作查询使用。三 事务1. 事务的概念事务是一种机制、一个操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或者撤消操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。它特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。2. 事务的种类： 自动提交事务（默认模式）：将每条单独的T-SQL语句视为一个事务。 显式事务：由用户管理的事务T-SQL使用下列语句来管理事务：开始事务：BEGIN TRANSACTION提交事务：COMMIT TRANSACTION回滚事务：ROLLBACK TRANSACTION3. 使用显式事务：-创建变量来对错误编号进行累计declare error intset error = 0-开启事务begin tran-执行相应的SQL操作-每执行一个操作，都需要对错误编号进行累加set error = error + error-执行操作set error = error + error-所有命令执行完后，-如果累加的错误编号等于0，表示所有的命令都执行成功,提交事务-否则回滚事务if (error = 0)commit tranelserollback tran四 存储过程 1. 存储过程概述存储过程是一组TSQL语句，存成一定名称，作为一个工作单元执行。存储过程可以从另一存储过程中调用，从客户机应用程序中调用或者从TSQL命令中调用，进行预定的操作。存储过程有如下的优点： 允许模块化的程序设计： 存储过程一旦创建完成并存储于数据库中，即可在我们的程序中反复调用。另外，通过将业务逻辑和策略编写在存储过程中，不仅可以让不同的应用程序共享，也可以要求所有的客户端去使用相同的存储过程来达到数据访问与更新的一致性。 更快的执行速度： 当执行批处理和T-SQL程序代码时，SQL Server必须先检查语法是否正确，接着加以编译、优化，然后再执行它，因此如果我们要执行的T-SQL代码非常庞大，那么执行前的处理操作是会耗掉一些时间的。对于存储过程而言，当它们被创建时就已经检索过语法的正确性、编译，并加以优化，因此当执行存储过程时，可以立即直接执行，自然速度会比较快； 存储过程在它第一次被执行后会保留在内存中，因此以后的调用并不需要再将存储过程从磁盘中加载。 有效降低网络流量： 假设某一项操作需要数百行的T-SQL代码来完成，如果我们是从客户端将这些代码传送到后端的SQL Server来执行，则在网络上传输的将是程序程序代码的数千或数万个字符；而如果我们事先将这些代码编写成一个存储过程，则只需从客户端调用该存储过程的名称即可执行它，它时在网络上传输的仅仅是存储过程名称的几个字符。 较好的安全机制： 如果我们不希望某一位用户有权限直接去访问某个数据表，但是又必须要求他针对该表执行特定的操作。如果想达到此目的，我们可以将该用户所能针对表执行的操作编写成一个存储过程，并赋予他执行该存储过程的权限，如此一来，虽然该用户没有权限直接访问表，但仍然可以通过执行存储过程来完成所需的操作。2. 存储过程类型 系统存储过程SP_ 扩展存储过程XP_ 用户自定义存储过程3. 创建和调用存储过程创建|修改存储过程的基本语法：CREATE|ALTER PROCEDURE procedure_name=defaultoutput,.nASsql_statement创建|修改存储过程的最复杂部分是生成内容（sql_statement部分），所有的智能都在这里 简单存储过程-创建简单存储过程CREATE PROCEDURE myProc1ASSELECT * FROM Students-执行上述存储过程：EXECUTE myProc1 带输入参数的存储过程-创建带输入参数的存储过程CREATE PROCEDURE myProc2Sid char(2)ASSELECT * FROM Students WHERE Sid=Sid-执行上述存储过程：EXECUTE myProc2 01 带输入输出参数的存储过程-创建带输入和输出参数的存储过程CREATE PROCEDURE myProc3Uid char(2),Pwd varchar(20),Result char(4) outputASIF EXISTS(SELECT * FROM UserInfo WHERE Uid=Uid AND Pwd=Pwd)SET Result=合法ELSESET Result=非法-执行上述存储过程DECLARE Result char(4)EXECUTE myProc3 01,abc,Result outputSELECT Result 带返回值的存储过程-创建有返回值的存储过程CREATE PROCEDURE myProc4Uid char(2),Pwd varchar(20)ASIF EXISTS(SELECT * FROM UserInfo WHERE Uid=Uid AND Pwd=Pwd)RETURN 0ELSERETURN 1-执行上述存储过程DECLARE Result intEXECUTE Result=myProc4 01,abcSELECT Result五 自定义函数自定义函数类似存储过程，将一组SQL命令封装起来，区别在于自定义函数只能通过返回值的方式来返回结果。因此可以将自定义函数放置在任何SQL命令中。例如通过自定义函数进行复杂计算，然后将内容做为查询条件提供给查询语句。语法：CREATE|ALERT FUNCTION funName(parament_name data_type,.n) returns data_typeASBEGINSQL命令RETURN 值END创建一个函数的示例：create FUNCTION GetName(mID int) returns NVARCHAR(20)ASBEGINDECLARE name(16);select name = name from tableName where id = idRETURN nameEND举例一(标量值函数)create function Volume(w int,l int,h int)returns intasbeginreturn (w*l*h) endselect dbo.Volume(3,2,1)必须提供拥有者和函数名举例二(表值函数)create function RUtable(m money)returns tableasreturn (select* from books where UnitPricem)select * from RUtable(80)六 游标SQL中查询语句查询的结果是一个数据集，为了能够对数据集中的数据进行逐行计算，可以使用游标。不过要注意的是，游标的效率不高，所有只有在必要的时候才使用游标。declare fname varchar(20),lname varchar(20)-根据一个查询语句创建一个游标对象declare myCursor cursor for select fname,lname from employee-打开游标open myCursor-执行第一次取数操作,通过游标逐行读取数据，并将读取的数据赋值给变量fetch next from myCursor into fname,lname-判断游标状态，是否存在下一行，如果有的话通过循环的方式读取每一条记录while fetch_status = 0beginprint fname + . + lnamefetch next from myCursor into fname,lnameend-关闭游标close myCursor-释放游标对象deallocate myCursorSQL2005触发器Microsoft SQL Server 2005 提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。触发器是一种特殊的存储过程，它在执行语言事件执行时自动生效。SQL Server 包括两大类触发器：DML 触发器和 DDL 触发器。当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。DML 触发器在以下方面非常有用：DML 触发器可通过数据库中的相关表实现级联更改。不过，通过级联引用完整性约束可以更有效地进行这些更改。 DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。 与 CHECK 约束不同，DML 触发器可以引用其他表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其他操作，如修改数据或显示用户定义错误信息。 DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。 一个表中的多个同类 DML 触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的操作来响应同一个修改语句。 DDL 触发器DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。像常规触发器一样，DDL 触发器将激发存储过程以响应事件。但与 DML 触发器不同的是，它们不会为响应针对表或视图的 UPDATE、INSERT 或 DELETE 语句而激发。相反，它们会为响应多种数据定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。如果要执行以下操作，请使用 DDL 触发器：要防止对数据库架构进行某些更改。 希望数据库中发生某种情况以响应数据库架构中的更改。 要记录数据库架构中的更改或事件 仅在运行触发 DDL 触发器的 DDL 语句后，DDL 触发器才会激发。DDL 触发器无法作为 INSTEAD OF 触发器使用。以下示例说明了如何使用 DDL 触发器来防止数据库中的任一表被修改或删除：一般语法如下:create trigger 触发器名on 数据表名for insert,update,delete /*在删除,更新,插入时触发器触发执行SQL语句实例1:提示成果插入create trigger person_inserton personfor insertasprint(ok,已经成功插入数据)实例2:限制一定的数据不能删除如电话记录不可以删除create trigger person_deleteon personfor deleteasdeclare phone char(15)select phone=phone from deletedif phonebegin print(该记录不可以删除.) rollback transaction endelse print(ok,已经成功删除.)goCreate trigger table_triggeron table1 for insertasbegindeclare int varchar(10)print (insert)select int = id from insertedprint intendDDL 触发器以下示例说明了如何使用 DDL 触发器来防止数据库中的任一表被修改或删除：所谓DML触发器，针对的是insert、delete、update，但drop,alter,create操作怎么办呢？SQL 2005中引入了DDL触发器的概念，专门针对drop,alter,create这三个操作，这个更简单了，先写下语法：create trigger 触发器名on ALL Server | databasefor | after 事件类型as其中all server代表所有数据库，database代表当前数据库事件类型可以是drop_table,alert_table,create_table这三种。使用 inserted 和 deleted 表触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。inserted 和 deleted 表主要用于触发器中： 扩展表间引用完整性。 在以视图为基础的基表中插入或更新数据。 检查错误并基于错误采取行动。 找到数据修改前后表状态的差异，并基于此差异采取行动。 Deleted 表用于存储 DELETE 和