数据库存储过程和触发器

1、数组小练习之高级查询目录:子查询存储过程系统存储过程视图触发器触发器MSDNA索引事务:全局变量1.经典案例_银行转账1.having 依赖于Group By也就是说不使用Group BY 就不能使用Having注:Having后通常跟聚合函数2. 数据库引擎对语句的执行顺序是:WhereSelectGroup By聚合函数HavingOrder BY3. 面试题:Where 和Having的区别:Where 可以独立使用而having只能跟在Group BY 的后面Having后面一般都是聚合函数!4. 内连接基于两张表的公共字段外连接:分为左外连接和右外连接左外连接:左表中的记录全部显示右

2、外连接:右表中的记录全部记录语法:内连接:T1 Inner Join T2 on T1.sno=T2.sno左外连接:T1 Left Join T2 on T1.sno=T2.sno右外连接:T1 Right Join T2 on T1.sno=T2.sno例子:select S.sno,sn,cn,Scorefrom sleftjoin scon s.sno=Sc.snoleftjoin Con So=C.cno自连接:5.子查询主表,从表:N字真经:主内,从外:外部查询用从表,内部查询用主表,而且内部查询只能查询一个字段,但可以有多个内部查询!use Northwindgoselect o

3、rderid,customers.customerid,companyname,orderdatefrom orders,customerswhere orders.customerid=customers.customerid-两者是等价的select orderid,customerid,orderdate,(select companyname from customers whe re orders.customerid=customers.customeridas companynamefrom ordersuse pubsgoselect title,price,avg(price

4、,(price-(select avg(pricefrom titlesas差额from titlesgroupby title,price-二者是等价的use pubsgoselect title,price,(select avg(pricefrom titlesas平均价格,(price-(select avg(pricefrom titlesas差额from titlesExists的作用:当存在量词存在时,查询的步骤是先执行内部查询,当内部查询执行完后,会返回一个BOOL数值,如果返回的值是False,那么外部查询不再执行!否则:执行外部查询!因此,使用存在量词可以提高查询的性能!6

5、. 存储过程:优点:因为存储过程是预先编译好的,因此使用存储过程可以减少网络流量,加快程序的执行速度,安全性较高,能够实现模块化编程存储过程与自定义函数的区别:存储过程可以返回一个集合而自定义函数返回一个标量值!存储过程是系统预编译好的use Dbstudentgocreateproc pMy2a int,b intoutputAsbeginselect*from stuInfos where stuseat>aselectb=count(*from stuInfos where stuseat>aenddropproc Pmy2declarex intexec pMy2a=2,b

6、=x outputprintxcreateproc pMy2a int,b intoutputAsbeginselect*from stuInfos where stuseat>aselectb=count(*from stuInfos where stuseat>aendcreateproc PmA intasdeclareb intsetb=(select count(*from StuInfos where stuseat>aprintbcreateproc PmAA int,b intoutputasselectb=count(*from StuInfos where

7、 stuseat>aprintbdropproc pmadeclareX intexec pma3,Xuse图书管理gocreateprocedure P_booknum出版社char(20,booknum intoutputassetbooknum=(select count(*from图书where出版社=出版社printbooknum陈丽alter proc Pdbo id int,count int output asselect * from t where id!=idset count=rowcountreturn 55declare Newcount intdeclare

8、 re intexec re=pdbo 2,Newcount outputprint Newcountprint re7. 系统存储过程:-列出当前系统中的数据库sp_databases-列出当前系统中的数据库的详细信息sp_helpdb-列出master数据库的详细信息sp_helpDB master-查询DBstudent中的所有表use DBstudentgosp_tables表名-查询DBstudent中表T的所有列use DBstudentgosp_columns t-查询DBstudent中表T的详细信息use DBstudentgosp_help t-创建一个唯一索引use DB

9、studentgocreateuniqueindex SCI on stuInfos(stuNo,stuname-创建一个聚集索引注:一个表中只能有一个聚集索引,当创建主键时,会自动生成一个聚集索引use DbstudentgocreateClusteredindex CLU on T(name查看位于表StuInfos中的索引注:表明必须在单引号内!use DBstudentgosp_helpindex'stuInfos'-查看对象(存储过程年度津贴的详细代码sp_helptext年度津贴-查看对象(存储过程年度津贴的详细信息sp_stored_procedures年度津贴-

10、查看所有的存储过程sp_stored_procedures- Microsoft SQL Server 为登录名添加或更改密码/*sp_password old = 'old_password' , new = 'new_password' , loginame = 'login' 参数 old = 'old_password'旧密码。old_password 的数据类型为sysname,默认值为NULL。 new = 'new_password'新密码。new_password 的数据类型为sysname,无默认

11、值。如果没有使用命名参数,则必须指定old_password。*/sp_password'','123456','sa'视图:如果视图是基于一个基表建立的,那么我们可以对视图进行修改Create View YYAsSelect * from S触发器:触发器是SQL Server 提供的一种高级约束机制,用于强制数据完整性的。触发器可以定义比用CHECK 约束更为复杂的约束;可以包含复杂的SQL语句,可以引用其它表中的列触发器通常用于在多表之间执行特殊的业务规则约束的;触发器是特殊类型的存储过程,它是由事件触发【激活】而自动执行的存储过程触发器的

12、执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行插入、更新或删除(insert,delete,update操作时就会触发【激活】触发器执行。触发器是一个事务,可回滚,具备事务的功能触发器的其它特点触发器与特定表或视图紧密相连, 表中数据发生变化时自动调用,可以进行层叠更改触发器的类型1.After触发器在执行了INSERT、UPDATE 或DELETE 语句之后触发;只能在表上指定2.Instead of 触发器执行INSTEAD OF 触发器代替通常的触发动作3.CLR触发器可以是AFTER 触发器、INSTEAD OF 触发器或DDL 触发器执行在托管代码中编写的方

13、法DDL 触发器DDL触发器是为响应多种数据定义语言(DDL 语句而激发的特殊存储过程。主要是以CREATE、ALTER 和DROP 开头的语句2.触发器触发时,系统自动在内存中创建deleted表或inserted表,只读不允许修改;触发器执行完成后自动删除3. 1.inserted 表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求如果不满足,则向用户报告错误消息,并回滚插入操作 2.deleted 表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求如果不满足,则向用户报告错误消息,并回滚插入操作注意:触发器中不能

14、有参数A索引:提高查询的速度,是SQL提高查询的方法,由系统调用。索引分为:聚集索引,非聚集索引,唯一索引,复合索引创建的索引包含两个或两个以上的列。例如:当主键为两个字段的组合时一张表中只能有一个聚集索引:“按照已经排好的顺序,将记录存储在表中”,可以有多个非聚集索引:“不会将排列的顺序存储在表中”和唯一索引!关键字:clustered<聚集索引>Nonclustered<非聚集索引>Unique<唯一索引>-创建一个唯一索引use DBstudentgocreateuniqueindex SCI on stuInfos(stuNo,stuname-创建一

15、个聚集索引注:一个表中只能有一个聚集索引,当创建主键时,会自动生成一个聚集索引use DbstudentgocreateClusteredindex CLU on T(name查看位于表StuInfos中的索引注:表明必须在单引号内!use DBstudentgosp_helpindex'stuInfos'createNonclusteredindex T on stuinfos(stuname组合索引createNonclusteredindex ASDF on stumarks(labexam,writtenexamdropindex表名.索引名下列情况适合使用索引:Whe

16、re 子句中对数据进行排序在Order BY 中使用的列频繁使用的列,经常被查询的列触发器MSDN:自动填充的现象出现啦: CREATETABLE BaseTable(PrimaryKey intPRIMARYKEYIDENTITY(1,1,Color nvarchar(10NOTNULL,Material nvarchar(10NOTNULL,ComputedCol AS (Color+MaterialGO-Create a view that contains all columns from the base table. CREATEVIEW InsteadViewASSELECT P

17、rimaryKey,Color,Material,ComputedColFROM BaseTableGO-Create an INSTEAD OF INSERT trigger on the view. CREATETRIGGER InsteadTrigger on InsteadViewINSTEADOFINSERTASBEGIN-Build an INSERT statement ignoring inserted.PrimaryKey and -inserted.ComputedCol.INSERTINTO BaseTableSELECT Color,MaterialFROM inser

18、tedENDGOdroptrigger InsteadTriggerINSERTINTO BaseTable(Color,MaterialVALUES (N'Red',N'Cloth'事务:是一种能够完成一般约束不能完成的高级约束,是一组要么全部执行,要么全部不执行的TSQL语句!2.先写核心语句3.加上开始结束标记经典案例_银行转账use S_studentgoCREATETABLE bank(customerName nvarchar(4notnull,/*顾客姓名*/currentMoney numeric(10, 2notnull check(curre

19、ntMoney>=1/*-当前余额*/ createtable transinfo(customerName nvarchar(4notnull,-交易人tranType nchar(2notnull,-支出还是存储tranMoney numeric(10, 2notnull check(tranMoney>=100,-交易金额TranDate datetime notnull-交易时间INSERTINTO bank(customerName,currentMoneyVALUES('张三',1000 INSERTINTO bank(customerName,curr

20、entMoneyVALUES('王二',1000 INSERTINTO bank(customerName,currentMoneyVALUES('李四',1000-如果执行成功,error的返回值为零declareerrorSum intseterrorSum=0INSERTINTO bank(customerName,currentMoneyVALUES('刘武',1000seterrorSum=errorSum+errorprinterrorSumiNSERTINTO bank(customerName,currentMoneyVALUES

21、('陈六',1000seterrorSum=errorSum+errorprinterrorSumdeletefrom bankbegintrandeclareerrorSum int=0update bankset currentMoney=currentmoney+200 where customerName='李四'selecterrorSum=errorupdate bankset currentMoney=currentmoney-200 where customerName='王三'selecterrorSum=errorSum+er

22、rorif(errorSum=0begincommittranprint'转账成功!'endelsebeginrollbacktransactionprint'转账失败!'endcreateproc Bank_MenMenfrom nchar(4,menTo nchar(4,Nmoney numeric(10, 2asbegintrandeclareerrorSum int=0update bankset currentMoney=currentmoney-Nmoney where customerName=Menfrom selecterrorSum=erro

23、rupdate bankset currentMoney=currentmoney+Nmoney where customerName=MenToselecterrorSum=errorSum+errorif(errorSum=0begincommittranprint'转账成功!'endelsebeginrollbacktransactionprint'转账失败!'endexec bank_men'王三','李四',2000Use NorthwindgoCREATETRIGGER Empl_Delete ON Employees

24、FORDELETEASIF (SELECT COUNT(*FROM Deleted> 1beginRAISERROR('不允许一次删除两条及两条以上记录',16,1 ROLLBACKTRANSACTIONendelsebeginprint'删除成功!'enddroptrigger Empl_Deleteuse S_studentgocreatetrigger YYU on bankfordeleteasif(select count(*from deleted>1beginRollbacktransactionprint'您无权删除数据

25、9;endelsebegincommittranprint'删除成功!'endupdate bank set currentMoney=currentMoney-500 where customerName='王二' insert transinfo values('王二','支出',500,getdate(-本触发器没有什么意义:createtrigger YYon bankforupdateasinsert transinfo values('王二','支出',1000,getdate(-当对B

26、ANK进行更新时,都会像transinfo表中添加记录-模拟银行取款系统_触发器createtrigger YYonbankforupdateasdeclaremNEw numeric(10, 2declaremOLD numeric(10, 2declarename nchar(4declarem numeric(10, 2declaretransTYPE nchar(2selectname=customerName,mOLD=currentMoney from deletedselectmNEW=currentMoney from insertedsetm=mOLD-mNEWif(m>

27、;0beginsettransType='支出'endelsebeginsettransType='存入'endinsertinto transinfo values (name,transType,abs(m,getdate(droptrigger YYupdate bank set currentMoney=currentMoney+200 where customerName='王二' deletefrom transinfocreatetrigger SAVEOUTon bankforupdateasdeclareUserName nch

28、ar(4declareTYPE nchar(2declareNewMoney numeric(10, 2declareoldMoney numeric(10, 2declareMoney numeric(10, 2selectUserName=customerName,oldmoney=CurrentMoney from deleted selectNewMoney=CurrentMoney from insertedsetmoney=Newmoney-oldMoneyif(money>0beginsettype='存入'endelsebeginsettype='支取'endinsertinto transinfo values(UserName,type,abs(Money,getdate(-模拟银行存取款系统-存储过程:查询账户交易信息createproc saveMoneyusername nchar(4