[计算机]sql高级

[计算机]sql高级

SQLServer一．子查询在SQL语言中，一个SELECT…FROM…WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句中的查询称为子查询。子查询分为嵌套子查询和相关子查询两种。嵌套子查询的求解方法是由里向外处理，即每个子查询在其上一级查询处理之前求解，子查询的结果作为其父查询的查询条件。子查询只执行一次，且可以单独执行；嵌套子查询使得可以用一系列简单查询构成复杂的查询，从而明显增强了SQL的查询处理能力。a)带有比较运算符的嵌套子查询（要求子查询返回的值为一行一列）b)In的嵌套子查询（子查询返回的值为多行一列）c)EXISTS的嵌套子查询：如果子查询的结果非空，即记录条数1条以上，则EXISTS（子查询）将返回真（true），否则返回假(false)。d)相关子查询：子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。子查询不能单独执行。二．视图1.视图的概念视图是另一种查看数据库中一个或多个表中的数据的方法。视图基于实际的数据表（基表）或别的视图而创建。视图是一种虚拟表，也就是说，视图中并不存储实际的数据。任何对视图的操作，都会转换为到基表的操作。2.视图的用途：l筛选表中的行l防止未经许可的用户访问敏感数据l将多个物理数据表抽象为一个逻辑数据表3.创建视图语法：CREATEVIEWview_nameAS4.对视图的编辑：因为视图中的数据来源基表，因此对视图进行增删改操作时，其命令中只能包含一个基表的数据。因此通过视图来修改数据有许多的限制，所以在实际开发中一般视图仅作查询使用。三．事务1.事务的概念事务是一种机制、一个操作序列，它包含了一组数据库操作命令，并且所有的命令作为\n一个整体一起向系统提交或者撤消操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。它特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。1.事务的种类：Ø自动提交事务（默认模式）：将每条单独的T-SQL语句视为一个事务。Ø显式事务：由用户管理的事务T-SQL使用下列语句来管理事务：开始事务：BEGINTRANSACTION提交事务：COMMITTRANSACTION回滚事务：ROLLBACKTRANSACTION2.使用显式事务：--创建变量来对错误编号进行累计declare@errorintset@error=0--开启事务begintran--执行相应的SQL操作--每执行一个操作，都需要对错误编号进行累加set@error=@error+@@error--执行操作set@error=@error+@@error--所有命令执行完后，--如果累加的错误编号等于0，表示所有的命令都执行成功,提交事务--否则回滚事务if(@error=0)committranelserollbacktran一．存储过程1.存储过程概述存储过程是一组T－SQL语句，存成一定名称，作为一个工作单元执行。存储过程可以从另一存储过程中调用，从客户机应用程序中调用或者从T－SQL命令中调用，进行预定的操作。存储过程有如下的优点：Ø允许模块化的程序设计：²存储过程一旦创建完成并存储于数据库中，即可在我们的程序中反复调用。另外，通过将业务逻辑和策略编写在存储过程中，不仅可以让不同的应用程序共享，也可以要求所有的客户端去使用相同的存储过程来达到数据访问与更新的一致性。Ø更快的执行速度：²当执行批处理和T-SQL程序代码时，SQL\nServer必须先检查语法是否正确，接着加以编译、优化，然后再执行它，因此如果我们要执行的T-SQL代码非常庞大，那么执行前的处理操作是会耗掉一些时间的。对于存储过程而言，当它们被创建时就已经检索过语法的正确性、编译，并加以优化，因此当执行存储过程时，可以立即直接执行，自然速度会比较快；²存储过程在它第一次被执行后会保留在内存中，因此以后的调用并不需要再将存储过程从磁盘中加载。Ø有效降低网络流量：²假设某一项操作需要数百行的T-SQL代码来完成，如果我们是从客户端将这些代码传送到后端的SQLServer来执行，则在网络上传输的将是程序程序代码的数千或数万个字符；而如果我们事先将这些代码编写成一个存储过程，则只需从客户端调用该存储过程的名称即可执行它，它时在网络上传输的仅仅是存储过程名称的几个字符。Ø较好的安全机制：²如果我们不希望某一位用户有权限直接去访问某个数据表，但是又必须要求他针对该表执行特定的操作。如果想达到此目的，我们可以将该用户所能针对表执行的操作编写成一个存储过程，并赋予他执行该存储过程的权限，如此一来，虽然该用户没有权限直接访问表，但仍然可以通过执行存储过程来完成所需的操作。1.存储过程类型Ø系统存储过程SP_Ø扩展存储过程XP_Ø用户自定义存储过程2.创建和调用存储过程创建|修改存储过程的基本语法：CREATE|ALTERPROC[EDURE]procedure_name[<@parameter_namedata_type>[=default][output][,...n]]ASsql_statement创建|修改存储过程的最复杂部分是生成内容（sql_statement部分），所有的智能都在这里Ø简单存储过程--创建简单存储过程CREATEPROCEDUREmyProc1ASSELECT*FROMStudents--执行上述存储过程：EXECUTEmyProc1Ø带输入参数的存储过程--创建带输入参数的存储过程CREATEPROCEDUREmyProc2@Sidchar(2)\nASSELECT*FROMStudentsWHERESid=@Sid--执行上述存储过程：EXECUTEmyProc2'01'Ø带输入输出参数的存储过程--创建带输入和输出参数的存储过程CREATEPROCEDUREmyProc3@Uidchar(2),@Pwdvarchar(20),@Resultchar(4)outputASIFEXISTS(SELECT*FROMUserInfoWHEREUid=@UidANDPwd=@Pwd)SET@Result='合法'ELSESET@Result='非法'--执行上述存储过程DECLARE@Resultchar(4)EXECUTEmyProc3'01','abc',@ResultoutputSELECT@ResultØ带返回值的存储过程--创建有返回值的存储过程CREATEPROCEDUREmyProc4@Uidchar(2),@Pwdvarchar(20)ASIFEXISTS(SELECT*FROMUserInfoWHEREUid=@UidANDPwd=@Pwd)RETURN0ELSERETURN1--执行上述存储过程DECLARE@ResultintEXECUTE@Result=myProc4'01','abc'SELECT@Result一．自定义函数自定义函数类似存储过程，将一组SQL命令封装起来，区别在于自定义函数只能通过返回值的方式来返回结果。因此可以将自定义函数放置在任何SQL命令中。例如通过自定义函数进行复杂计算，然后将内容做为查询条件提供给查询语句。语法：CREATE|ALERTFUNCTIONfunName(@parament_namedata_type,...n)returnsdata_typeASBEGINSQL命令\nRETURN值END创建一个函数的示例：createFUNCTIONGetName(@mIDint)returnsNVARCHAR(20)ASBEGINDECLARE@name(16);select@name=namefromtableNamewhereid=@idRETURN@nameEND举例一(标量值函数)createfunctionVolume(@wint,@lint,@hint)returnsintasbeginreturn(@w*@l*@h)endselectdbo.Volume(3,2,1)—必须提供拥有者和函数名举例二(表值函数)createfunctionRUtable(@mmoney)returnstableasreturn(select*frombookswhereUnitPrice>@m)select*fromRUtable(80)一．游标SQL中查询语句查询的结果是一个数据集，为了能够对数据集中的数据进行逐行计算，可以使用游标。不过要注意的是，游标的效率不高，所有只有在必要的时候才使用游标。declare@fnamevarchar(20),@lnamevarchar(20)--根据一个查询语句创建一个游标对象declaremyCursorcursorforselectfname,lnamefromemployee--打开游标openmyCursor--执行第一次取数操作,通过游标逐行读取数据，并将读取的数据赋值给变量fetchnextfrommyCursorinto@fname,@lname--判断游标状态，是否存在下一行，如果有的话通过循环的方式读取每一条记录while@@fetch_status=0\nbeginprint@fname+'.'+@lnamefetchnextfrommyCursorinto@fname,@lnameend--关闭游标closemyCursor--释放游标对象deallocatemyCursorSQL2005触发器MicrosoftSQLServer2005提供了两种主要机制来强制执行业务规则和数据完整性：约束和触发器。触发器是一种特殊的存储过程，它在执行语言事件执行时自动生效。SQLServer包括两大类触发器：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触发器\nDDL触发器是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。像常规触发器一样，DDL触发器将激发存储过程以响应事件。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发。相反，它们会为响应多种数据定义语言(DDL)语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。如果要执行以下操作，请使用DDL触发器：要防止对数据库架构进行某些更改。希望数据库中发生某种情况以响应数据库架构中的更改。要记录数据库架构中的更改或事件仅在运行触发DDL触发器的DDL语句后，DDL触发器才会激发。DDL触发器无法作为INSTEADOF触发器使用。以下示例说明了如何使用DDL触发器来防止数据库中的任一表被修改或删除：一般语法如下:createtrigger触发器名on数据表名for[insert,update,delete]  /*在删除,更新,插入时触发器触发执行SQL语句实例1:提示成果插入createtriggerperson_insertonpersonforinsertasprint('ok,已经成功插入数据')实例2:限制一定的数据不能删除如电话为13545173242的记录不可以删除createtriggerperson_deleteonpersonfordeleteasdeclare@phonechar(15)select@phone=phonefromdeletedif@phone='13545173242'    \nbegin      print('该记录不可以删除.')      rollbacktransaction    endelse    print('ok,已经成功删除.')goCreatetriggertable_triggerontable1forinsertasbegindeclare@intvarchar(10)print('insert')select@int=idfrominsertedprint@intendDDL触发器以下示例说明了如何使用DDL触发器来防止数据库中的任一表被修改或删除：所谓DML触发器，针对的是insert、delete、update，但drop,alter,create操作怎么办呢？SQL2005中引入了DDL触发器的概念，专门针对drop,alter,create这三个操作，这个更简单了，先写下语法：createtrigger触发器名on{ALLServer|database}{for|after}事件类型as其中allserver代表所有数据库，database代表当前数据库事件类型可以是drop_table,alert_table,create_table这三种。使用inserted和deleted表触发器语句中使用了两种特殊的表：deleted表和inserted表。inserted和deleted表主要用于触发器中：·扩展表间引用完整性。·在以视图为基础的基表中插入或更新数据。·检查错误并基于错误采取行动。\n·找到数据修改前后表状态的差异，并基于此差异采取行动。Deleted表用于存储DELETE和UPDATE语句所影响的行的复本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传输到deleted表中。Deleted表和触发器表通常没有相同的行。Inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。Inserted表中的行是触发器表中新行的副本。更新事务类似于在删除之后执行插入；首先旧行被复制到deleted表中，然后新行被复制到触发器表和inserted表中。在设置触发器条件时，应当为引发触发器的操作恰当使用inserted和deleted表。虽然在测试INSERT时引用deleted表或在测试DELETE时引用inserted表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。1.insert：执行insert操作之后，系统将插入的新行存储到表inserted中2.update：执行update操作之前,系统先将要修改的行（即受修改影响行的原值）存入deleted表中；执行update操作之后，系统将修改后的行（即受修改影响行的新值）存入inserted表中。3.delete：执行delete操作之后，系统将删除的行放入deleted表中。说明  如果触发器操作取决于一个数据修改所影响的行数，应该为多行数据修改（基于SELECT语句的INSERT、DELETE或UPDATE）使用测试（如检查@@ROWCOUNT），然后采取相应的对策。