SQL SERVER 2005 T_SQL新的特性以及解决并发-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

SQL SERVER 2005 T_SQL新的特性以及解决并发

简介:
None.gif一.修改语句的增强
None.gifUPDATE test SET tname.WRITE('one hundred and two', 9, 3) WHERE tid = 102
None.gif
None.gif在2005中增强了update方法,这是修改test表的列tname WRITE方法是把tname这个列中从字符串9开始 把3个长的字符串改为one hundred and two
None.gif
None.gif二、异常的捕获
None.gif可以捕获过去会导致批处理终止和事务的错误,但是不能处理连接中断错误和硬件错误等
None.gif--Using the try..catch.. construct and invoking a run-time error
None.gif
SET XACT_ABORT of 这个打开捕获异常的开关 ,默认是关闭的
None.gifBEGIN TRY
None.gifBEGIN TRAN
None.gifINSERT INTO score VALUES (102,78)
ExpandedBlockStart.gifContractedBlock.gifINSERT INTO score VALUES (107, 76) /**//* Foreign Key Error */
None.gifINSERT INTO score VALUES (103,81)
None.gifCOMMIT TRAN
None.gifPRINT 'Transaction committed'
None.gifEND TRY
None.gifBEGIN CATCH
None.gifROLLBACK
None.gifPRINT 'Transaction rolled back'
None.gifSELECT ERROR_NUMBER() AS ErrorNumber,
None.gif ERROR_SEVERITY() AS ErrorSeverity,
None.gif ERROR_STATE() as ErrorState,
None.gif ERROR_MESSAGE() as ErrorMessage;
None.gifEND CATCH
None.gifGO
None.gif三、快照隔离
None.gif写入程序不会影响读取程序,可对事务冲突的检测
None.gifcreate database demo2 建立数据库
None.gifgo
None.gifuse demo2
None.gifalter database demo2 set allow_snapshot_isolation on 打开快照隔离开关,默认是关闭的
None.gifcreate table test
None.gif( tid int not null primary key,
None.gif tname varchar(50) not null
None.gif)
None.gifinsert into test values(1,'version1')
None.gifinsert into test values(2,'version2')
None.gif
None.gif--connection 1
None.gif

None.gifuse demo2
None.gifbegin tran
None.gifupdate test set tname='version3' where tid=2
None.gifselect * from test
None.gif
None.gif--connection 2
None.gif
use demo2
None.gifset transaction isolation level snapshot
None.gifselect * from test
None.gif
None.gif--it will ok, you can see it
None.gif
四 、top语句的增强
None.gif可以是数字表达式,一返回要通过通过查询影响的行输或者百分比还可以是更具情况使用变量和子查询
None.gif可以在delete、update和insert中使用top选项,更好的代替set rowcount选项,使之更为有效。
None.gif--create a table and insert some data
None.gif
use demo
None.gifgo
None.gifCREATE TABLE toptest (column1 VARCHAR(150))
None.gifgo
None.gifINSERT INTO toptest VALUES('t1')
None.gifINSERT INTO toptest VALUES('t2')
None.gifINSERT INTO toptest VALUES('t3')
None.gifINSERT INTO toptest VALUES('t4')
None.gifINSERT INTO toptest VALUES('t5')
None.gifINSERT INTO toptest VALUES('t6')
None.gifINSERT INTO toptest VALUES('t7')
None.gifINSERT INTO toptest VALUES('t8')
None.gifselect * from toptest
None.gifgo
None.gif
None.gifCREATE TABLE toptest2 (column2 VARCHAR(150))
None.gifgo
None.gifINSERT INTO toptest2 VALUES('c1')
None.gifINSERT INTO toptest2 VALUES('c2')
None.gif
None.gif--declare 3 variables
None.gif
DECLARE @a INT
None.gifDECLARE @b INT
None.gifDECLARE @c INT
None.gif--set values
None.gif
SET @a = 10
None.gifSET @b = 5
None.gifSELECT @c = @a/@b
None.gif--use the calculated expression
None.gif
SELECT TOP(@c) * FROM toptest
None.gif--use a SELECT statement as expression
None.gif
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
None.gifFROM toptest
None.gif
None.gif--DML top
None.gif
DELETE TOP(2) toptest where column1>'t6'
None.gif--this sets 't1' and 't2' to 'hi'
None.gif
UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'
None.gif
None.gifSELECT * FROM toptest
None.gif五、output
None.gif引入一个新的output字句 可以使您从修改语句(elete、update和insert)中将数据返回到表变量中
None.gif语法:output <dml_select_list> into @table_variable
None.gif可以通过应用插入的表和删除的表来访问被修改的行的旧/新映象,起方式于访问触发器类似,在insert语句中,只能访问插入的表,update和delete也一样。访问临时表
None.gif--create table and insert data
None.gif
use demo
None.gifgo
None.gifCREATE TABLE tt
None.gif(id INT IDENTITY, c1 VARCHAR(15))
None.gifgo
None.gifINSERT INTO tt VALUES ('r1')
None.gifINSERT INTO tt VALUES ('r2')
None.gifINSERT INTO tt VALUES ('r5')
None.gifINSERT INTO tt VALUES ('r6')
None.gifINSERT INTO tt VALUES ('r7')
None.gifINSERT INTO tt VALUES ('r8')
None.gifINSERT INTO tt VALUES ('r9')
None.gifINSERT INTO tt VALUES ('r10')
None.gif
None.gif--make a table variable to hold the results of the OUTPUT clause
None.gif
DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))
None.gifDELETE tt
None.gifOUTPUT DELETED.id, DELETED.c1 INTO @del
None.gifWHERE id < 3
None.gifSELECT * FROM @del
None.gifGO
None.gif
None.gif六、函数
None.gifROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序
None.gifSELECT orderid,qty,
None.gif ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
None.gif RANK() OVER(ORDER BY qty) AS rank,
None.gif DENSE_RANK() OVER(ORDER BY qty) AS denserank
None.gifFROM rankorder
None.gifORDER BY qty
None.gif七、通用表达式 cte 临时命名的结果集
None.gifUSE AdventureWorks
None.gifGO
None.gifWITH SalesCTE(ProductID, SalesOrderID)
None.gifAS
None.gif(
None.gifSELECT ProductID, COUNT(SalesOrderID)
None.gifFROM Sales.SalesOrderDetail
None.gifGROUP BY ProductID
None.gif)
None.gifSELECT * FROM SalesCTE
None.gif----
None.gif

None.gif
None.gif-- Using CTEs Recursively
None.gif
use demo
None.gifgo
None.gifCREATE TABLE CarParts
None.gif(
None.gif CarID int NOT NULL,
None.gif Part varchar(15),
None.gif SubPart varchar(15),
None.gif Qty int
None.gif)
None.gifGO
None.gifINSERT CarParts
None.gifVALUES (1, 'Body', 'Door', 4)
None.gifINSERT CarParts
None.gifVALUES (1, 'Body', 'Trunk Lid', 1)
None.gifINSERT CarParts
None.gifVALUES (1, 'Body', 'Car Hood', 1)
None.gifINSERT CarParts
None.gifVALUES (1, 'Door', 'Handle', 1)
None.gifINSERT CarParts
None.gifVALUES (1, 'Door', 'Lock', 1)
None.gifINSERT CarParts
None.gifVALUES (1, 'Door', 'Window', 1)
None.gifINSERT CarParts
None.gifVALUES (1, 'Body', 'Rivets', 1000)
None.gifINSERT CarParts
None.gifVALUES (1, 'Door', 'Rivets', 100)
None.gifINSERT CarParts
None.gifVALUES (1, 'Door', 'Mirror', 1)
None.gifgo
None.gifselect * from CarParts
None.gifgo
None.gif
None.gifWITH CarPartsCTE(SubPart, Qty)
None.gifAS
None.gif(
None.gif-- Anchor Member (AM):
None.gif
-- SELECT query that doesn’t refer back to CarPartsCTE
None.gif
SELECT SubPart, Qty
None.gifFROM CarParts
None.gifWHERE Part = 'Body'
None.gifUNION ALL
None.gif-- Recursive Member (RM):
None.gif
-- SELECT query that refers back to CarPartsCTE
None.gif
SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
None.gifFROM CarPartsCTE
None.gifINNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
None.gifWHERE CarParts.CarID = 1
None.gif)
None.gif--outer query
None.gif
SELECT SubPart, SUM(Qty) AS TotalNUM
None.gifFROM CarPartsCTE
None.gifGROUP BY SubPart
None.gif八、新的关系运算符
None.gifpivot 把行转回为列
None.gifunpivot 把列转回为行
None.gifuse demo
None.gifgo
None.gif
None.gifcreate table orders
None.gif(Customer varchar(10) not null,
None.gif product varchar(20) not null,
None.gif quantity int not null)
None.gifgo
None.gifinsert orders values('Mike', 'Bike',3)
None.gifinsert orders values('Mike','Chain',2)
None.gifinsert orders values('Mike','Bike',5)
None.gifinsert orders values('Lisa','Bike',3)
None.gifinsert orders values('Lisa','Chain',3)
None.gifinsert orders values('Lisa','Chain',4)
None.gifinsert orders values('Lisa','Bike',2)
None.gif
None.gifselect * from orders
None.gif
None.gifselect * from orders
None.gifpivot (sum(quantity) for product in ([Bike],[Chain])) as a
None.gifuse demo
None.gifgo
None.gifCREATE TABLE SALES1
None.gif(
None.gif[Year] INT,
None.gifQuarter CHAR(2),
None.gifAmount FLOAT
None.gif)
None.gifGO
None.gifINSERT INTO SALES1 VALUES (2001, 'Q1', 80)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q2', 70)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q3', 55)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q3', 110)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q4', 90)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q1', 200)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q2', 150)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q2', 40)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q2', 60)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q3', 120)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q3', 110)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q4', 180)
None.gifGO
None.gif
None.gifSELECT * FROM SALES1
None.gifPIVOT
None.gif(SUM (Amount) --Aggregate the Amount column using SUM
None.gif
FOR [Quarter] --Pivot the Quarter column into column headings
None.gif
IN (Q1, Q2, Q3, Q4)) --use these quarters
None.gif
AS P
None.gifGO
None.gif
None.gifselect * into temp1 from orders
None.gifpivot (sum(quantity) for product in ([Bike],[Chain])) as a
None.gif
None.gifselect * from temp1
None.gif
None.gifselect customer, product,quantity
None.giffroam temp1
None.gifunpivot(quantity for product in ([Bike],[Chain])) as a
None.gif九、ddl触发器
None.gifuse demo
None.gifgo
None.gifCREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
None.gifAS
None.gifRAISERROR('Not allowed to drop tables.', 10, 1)
None.gifPRINT 'DROP TABLE attempt in database ' + DB_NAME() + '.'
None.gifPRINT CONVERT (nvarchar (1000),EventData())
None.gifROLLBACK
None.gifGO
None.gif-- test
None.gif
CREATE TABLE TestDROP(col1 INT)
None.gifgo
None.gifINSERT INTO TestDROP VALUES(1)
None.gif
None.gifdrop talbe testdrop
None.gif
None.gif-- Server
None.gif
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
None.gifFOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
None.gifAS
None.gifPRINT 'DDL LOGIN took place.'
None.gifPRINT CONVERT (nvarchar (1000),EventData())
None.gifGO
None.gif
None.gif-- test
None.gif
CREATE LOGIN login1 WITH PASSWORD = '123'
None.gifALTER LOGIN login1 WITH PASSWORD = 'xyz'
None.gifDROP LOGIN login1
None.gif丰富的数据类型 Richer Data Types
None.gif
None.gif1varchar(max)、nvarchar(max)和varbinary(max)数据类型最多可以保存2GB的数据,可以取代text、ntext或image数据类型。
None.gifCREATE TABLE myTable
None.gif(
None.gif id INT,
None.gif content VARCHAR(MAX)
None.gif)
None.gif
None.gif2、XML数据类型
None.gifXML数据类型允许用户在SQL Server数据库中保存XML片段或文档。
None.gif
None.gif错误处理 Error Handling
None.gif
None.gif1、新的异常处理结构
None.gif
None.gif2、可以捕获和处理过去会导致批处理终止的错误
None.gif前提是这些错误不会导致连接中断(通常是严重程度为21以上的错误,例如,表或数据库完整性可疑、硬件错误等等。)。
None.gif
None.gif3、TRY/CATCH 构造
None.gifSET XACT_ABORT ON
None.gifBEGIN TRY
None.gif<core logic>
None.gifEND TRY
None.gifBEGIN CATCH TRAN_ABORT
None.gif<exception handling logic>
None.gifEND TRY
None.gif
None.gif@@error may be quired as first statement in CATCH block
None.gif
None.gif4、演示代码
None.gifUSE demo
None.gifGO
None.gif--创建工作表
None.gif

None.gifCREATE TABLE student
None.gif (
None.gif stuid INT NOT NULL PRIMARY KEY,
None.gif stuname VARCHAR(50)
None.gif )
None.gif
None.gifCREATE TABLE score
None.gif (
None.gif stuid INT NOT NULL REFERENCES student(stuid),
None.gif score INT
None.gif )
None.gifGO
None.gif
None.gifINSERT INTO student VALUES (101,'zhangsan')
None.gifINSERT INTO student VALUES (102,'wangwu')
None.gifINSERT INTO student VALUES (103,'lishi')
None.gifINSERT INTO student VALUES (104,'maliu')
None.gif
None.gif--调用一个运行时错误
None.gif
SET XACT_ABORT OFF
None.gifBEGIN TRAN
None.gifINSERT INTO score VALUES (101,90)
None.gifINSERT INTO score VALUES (102,78)
ExpandedBlockStart.gifContractedBlock.gifINSERT INTO score VALUES (107, 76) /**//* 外键错误 */
None.gifINSERT INTO score VALUES (103,81)
None.gifINSERT INTO score VALUES (104,65)
None.gifCOMMIT TRAN
None.gifGO
None.gif
None.gifSELECT * FROM student
None.gifSELECT * FROM score
None.gif
None.gif--使用TRYdot.gifCATCH构造,并调用一个运行时错误
None.gif
SET XACT_ABORT OFF
None.gifBEGIN TRY
None.gifBEGIN TRAN
None.gifINSERT INTO score VALUES (101,90)
None.gifINSERT INTO score VALUES (102,78)
ExpandedBlockStart.gifContractedBlock.gifINSERT INTO score VALUES (107, 76) /**//* 外键错误 */
None.gifINSERT INTO score VALUES (103,81)
None.gifINSERT INTO score VALUES (104,65)
None.gifCOMMIT TRAN
None.gifPRINT '事务提交'
None.gifEND TRY
None.gifBEGIN CATCH
None.gifROLLBACK
None.gifPRINT '事务回滚'
None.gifSELECT ERROR_NUMBER() AS ErrorNumber,
None.gif ERROR_SEVERITY() AS ErrorSeverity,
None.gif ERROR_STATE() as ErrorState,
None.gif ERROR_MESSAGE() as ErrorMessage;
None.gifEND CATCH
None.gifGO
None.gif
None.gifSELECT * FROM score
None.gifGO
None.gif
None.gif快照隔离 Snapshot Isolation
None.gif
None.gif1、写入程序不会阻碍读取程序
None.gif2、Snapshot isolation must be enabled for DB
None.gifALTER DATABASE 数据库 SET allow_snapshot_isolation ON
None.gif3、Snapshot isolation must be enabled for connection
None.gifSet transaction isolation level snapshot
None.gif4UPDATE transactions keep old versions of data in a linked list
None.gif5、新的隔离级别提供了以下优点:
None.gif  1) 提高了只读应用程序的数据可用性
None.gif  2) 允许在OLTP环境中执行非阻止读取操作
None.gif  3) 可对写入事务进行自动的强制冲突检测
None.gif6、演示代码
None.gifCREATE DATABASE demo2
None.gifGO
None.gifUSE demo2
None.gifALTER DATABASE demo2 SET allow_snapshot_isolation ON
None.gifCREATE TABLE test
None.gif (
None.gif tid INT NOT NULL primary key,
None.gif tname VARCHAR(50) NOT NULL
None.gif )
None.gifINSERT INTO test VALUES(1,'version1')
None.gifINSERT INTO test VALUES(2,'version2')
None.gif
None.gif--连接一
None.gif

None.gifUSE demo2
None.gifBEGIN TRAN
None.gifUPDATE test SET tname='version3' WHERE tid=2
None.gifSELECT * FROM test
None.gif
None.gif--连接二
None.gif
USE demo2
None.gifSET transaction isolation level snapshot
None.gifSELECT * FROM test
None.gif
None.gifTOP 增强功能
None.gif
None.gif1TOP 增强
None.gif可以指定一个数字表达式,以返回要通过查询影响的行数或百分比,还可以根据情况使用变量或子查询。
None.gif可以在DELETE、UPDATE和INSERT查询中使用TOP选项。
None.gif
None.gif2、更好地替换SET ROWCOUNT选项,使之更为有效。
None.gif
None.gifOUTPUT
None.gif
None.gif1、SQL Server 2005引入一个新的OUTPUT子句,以使您可以冲修改语句(INSERTUPDATEDELETE)中将数据返回到表变量中。
None.gif
None.gif2、新的OUTPUT子局的语法为:
None.gifOUTPUT <dml_select_list> INTO @table_variable
None.gif可以通过引用插入的表或删除的表来访问被修改的行的旧/新影象,其方式与访问触发器类似。在INSERT语句中,只能访问插入的表。在DELETE语句中,只能访问删除的表。在UPDATE语句中,可以访问插入的表和删除的表。
None.gif
None.gif3、代码演示
None.gifUSE demo
None.gifGO
None.gifCREATE TABLE tt
None.gif(
None.gif id INT IDENTITY,
None.gif c1 VARCHAR(15)
None.gif)
None.gifGO
None.gif
None.gifINSERT INTO tt VALUES ('r1')
None.gifINSERT INTO tt VALUES ('r2')
None.gifINSERT INTO tt VALUES ('r5')
None.gifINSERT INTO tt VALUES ('r6')
None.gifINSERT INTO tt VALUES ('r7')
None.gifINSERT INTO tt VALUES ('r8')
None.gifINSERT INTO tt VALUES ('r9')
None.gifINSERT INTO tt VALUES ('r10')
None.gif
None.gifDECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))
None.gifDELETE tt
None.gifOUTPUT DELETED.id, DELETED.c1 INTO @del
None.gifWHERE id < 3
None.gifSELECT * FROM @del
None.gifGO
None.gif-----------------------------------------------
None.gif
USE demo
None.gifGO
None.gifCREATE TABLE toptest (column1 VARCHAR(150))
None.gifGO
None.gifINSERT INTO toptest VALUES('t1')
None.gifINSERT INTO toptest VALUES('t2')
None.gifINSERT INTO toptest VALUES('t3')
None.gifINSERT INTO toptest VALUES('t4')
None.gifINSERT INTO toptest VALUES('t5')
None.gifINSERT INTO toptest VALUES('t6')
None.gifINSERT INTO toptest VALUES('t7')
None.gifINSERT INTO toptest VALUES('t8')
None.gifSELECT * FROM toptest
None.gifGO
None.gif
None.gifCREATE TABLE toptest2 (column2 VARCHAR(150))
None.gifGO
None.gifINSERT INTO toptest2 VALUES('c1')
None.gifINSERT INTO toptest2 VALUES('c2')
None.gif
None.gif--声明3个变量
None.gif
DECLARE @a INT
None.gifDECLARE @b INT
None.gifDECLARE @c INT
None.gif
None.gif--赋值
None.gif
SET @a = 10
None.gifSET @b = 5
None.gifSELECT @c = @a/@b
None.gif
None.gif--使用计算表达式
None.gif
SELECT TOP(@c) * FROM toptest
None.gif
None.gif--使用SELECT语句作为条件
None.gif
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
None.gifFROM toptest
None.gif
None.gif--指出top
None.gif
DELETE TOP(2) toptest where column1>'t6'
None.gif
None.gif--更新top
None.gif
UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'
None.gif
None.gifSELECT * FROM toptest
None.gif
None.gif排序函数 Ranking Functions
None.gif
None.gif1、SQL Server引入几个新的排序函数:如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。
None.gif
None.gif2、排序函数都遵循类似的语法模式:
None.gif()OVER
None.gif([PARTITION BY]
None.gifORDER BY)
None.gif该函数只能在查询的两个子句中指定 - 在SELECT子句或ORDER BY子句中。以下详细讨论不同的函数。
None.gif
None.gif3、ROW_NUMBER
None.gifROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序
None.gifUSE demo
None.gifGO
None.gifCREATE TABLE rankorder
None.gif(
None.gif orderid INT,
None.gif qty INT
None.gif)
None.gifGO
None.gifINSERT rankorder VALUES(30001,10)
None.gifINSERT rankorder VALUES(10001,10)
None.gifINSERT rankorder VALUES(10006,10)
None.gifINSERT rankorder VALUES(40005,10)
None.gifINSERT rankorder VALUES(30003,15)
None.gifINSERT rankorder VALUES(30004,20)
None.gifINSERT rankorder VALUES(20002,20)
None.gifINSERT rankorder VALUES(20001,20)
None.gifINSERT rankorder VALUES(10005,30)
None.gifINSERT rankorder VALUES(30007,30)
None.gifINSERT rankorder VALUES(40001,40)
None.gifGO
None.gifSELECT orderid,qty,
None.gif ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
None.gif RANK() OVER(ORDER BY qty) AS rank,
None.gif DENSE_RANK() OVER(ORDER BY qty) AS denserank
None.gifFROM rankorder
None.gifORDER BY qty
None.gif
None.gif通用表表达式 Common Table Expressions
None.gif
None.gif通用表表达式(CTE)是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中,您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次,即可在查询中多次引用它。在CTE的定义中,可以引用在同一批处理中定义的变量。但是CTE的真正威力在于它们的递归功能,即CTE可以包含对它们自身的引用。
None.gif
None.gif视图、派生表和CTE内部的查询的一般形式
None.gif
None.gif1、视图
None.gifCREATE VIEW <view_name>(<column_aliases>) AS <view_query>
None.gif
None.gif2、派生表
None.gifSELECT * FROM (<derived_table)query>) AS <dericed_table_alias>(<column_aliases>)
None.gif
None.gif3、CTE
None.gifWITH <cte_alias>(<column_aliases>)
None.gifAS
None.gif{
None.gif<cte_query>
None.gif)
None.gifSELECT * FROM <cte_alias]>
None.gif在关键字WITH之后,为CTE提供一个别名,并且为它的结果列提供一个可选的别名列表;编写CTE的主体;然后从外部查询中引用它。
None.gif
None.gif4、演示代码
None.gifUSE AdventureWorks
None.gifGO
None.gifWITH SalesCTE(ProductID, SalesOrderID)
None.gifAS
None.gif(
None.gifSELECT ProductID, COUNT(SalesOrderID)
None.gifFROM Sales.SalesOrderDetail
None.gifGROUP BY ProductID
None.gif)
None.gifSELECT * FROM SalesCTE
None.gif
None.gifRecursive CTEs 递归的通用表表达式
None.gif
None.gif递归的CTE是根据至少两个查询(或者称为两个成员)构建的,一个是非递归查询,也成为固定成员,只能调用一次,另外一个是递归查询,也成为递归成员(RM),可以反复调用,直到查询不再返回行。查询由UNION ALL运算符连接为一个单独的CTE。
None.gif
None.gif--使用递归的通用表表达式
None.gif
USE demo
None.gifGO
None.gifCREATE TABLE CarParts
None.gif(
None.gif CarID INT NOT NULL,
None.gif Part VARCHAR(15),
None.gif SubPart VARCHAR(15),
None.gif Qty INT
None.gif)
None.gifGO
None.gifINSERT CarParts VALUES (1, 'Body', 'Door', 4)
None.gifINSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
None.gifINSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
None.gifINSERT CarParts VALUES (1, 'Door', 'Handle', 1)
None.gifINSERT CarParts VALUES (1, 'Door', 'Lock', 1)
None.gifINSERT CarParts VALUES (1, 'Door', 'Window', 1)
None.gifINSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
None.gifINSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
None.gifINSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
None.gifGO
None.gifSELECT * FROM CarParts
None.gifGO
None.gif
None.gifWITH CarPartsCTE(SubPart, Qty)
None.gifAS
None.gif(
None.gif-- 固定成员 (AM):
None.gif
-- SELECT查询无需参考CarPartsCTE
None.gif
SELECT SubPart, Qty
None.gifFROM CarParts
None.gifWHERE Part = 'Body'
None.gifUNION ALL
None.gif-- 递归成员 (RM):
None.gif
-- SELECT查询参考CarPartsCTE
None.gif
SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
None.gifFROM CarPartsCTE
None.gifINNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
None.gifWHERE CarParts.CarID = 1
None.gif)
None.gif-- 外部查询
None.gif
SELECT SubPart, SUM(Qty) AS TotalNUM
None.gifFROM CarPartsCTE
None.gifGROUP BY SubPart
None.gif
None.gif新的关系运算符 PIVOT/UNPIVOT/APPLY
None.gif
None.gif1、PIVOT
None.gifPIVOT运算符将行旋转为列,并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是,需要为它提供一个查询表达式,表达式使用视图、派生表或者是CTE只返回所关注的列。
None.gif
None.gif2、UNPIVOT
None.gifUNPIVOT运算符执行与PIVOT运算符相反的操作;他将列旋转为行了。
None.gif
None.gif3、APPLY
None.gifAPPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY,其方式与使用JOIN关系运算符类似。APPLY具有两种形式:CROSS APPLY和OUTER APPLY。
None.gif
None.gif演示:
None.gif
None.gifUSE demo
None.gifGO
None.gif
None.gifCREATE TABLE orders
None.gif(
None.gif Customer VARCHAR(10) NOT NULL,
None.gif product VARCHAR(20) NOT NULL,
None.gif quantity INT NOT NULL
None.gif)
None.gifGO
None.gifINSERT orders VALUES('Mike', 'Bike',3)
None.gifINSERT orders VALUES('Mike','Chain',2)
None.gifINSERT orders VALUES('Mike','Bike',5)
None.gifINSERT orders VALUES('Lisa','Bike',3)
None.gifINSERT orders VALUES('Lisa','Chain',3)
None.gifINSERT orders VALUES('Lisa','Chain',4)
None.gifINSERT orders VALUES('Lisa','Bike',2)
None.gif
None.gifSELECT * FROM orders
None.gif
None.gifSELECT * FROM orders
None.gifPIVOT (SUM(quantity) FOR product IN ([Bike],[Chain])) AS a
None.gifUSE demo
None.gifGO
None.gifCREATE TABLE SALES1
None.gif(
None.gif[Year] INT,
None.gif Quarter CHAR(2),
None.gif Amount FLOAT
None.gif)
None.gifGO
None.gifINSERT INTO SALES1 VALUES (2001, 'Q1', 80)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q2', 70)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q3', 55)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q3', 110)
None.gifINSERT INTO SALES1 VALUES (2001, 'Q4', 90)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q1', 200)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q2', 150)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q2', 40)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q2', 60)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q3', 120)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q3', 110)
None.gifINSERT INTO SALES1 VALUES (2002, 'Q4', 180)
None.gifGO
None.gif
None.gifSELECT * FROM SALES1
None.gifPIVOT
None.gif(SUM (Amount) --使用SUM聚合数量列
None.gif
FOR [Quarter] --PIVOT Quarter 列
None.gif
IN (Q1, Q2, Q3, Q4)) --使用季节
None.gif
AS P
None.gifGO
None.gif
None.gifSELECT * INTO temp1 FROM orders
None.gifPIVOT (sum(quantity) FOR product IN ([Bike],[Chain])) AS a
None.gif
None.gifSELECT * FROM temp1
None.gif
None.gifSELECT customer, product,quantity
None.gifFROM temp1
None.gifUNPIVOT(quantity FOR product IN ([Bike],[Chain])) AS a
None.gif----------------------------------------------------
None.gif
USE demo
None.gifGO
None.gifCREATE TABLE Arrays
None.gif(
None.gif aid INT NOT NULL IDENTITY PRIMARY KEY,
None.gif array VARCHAR(7999) NOT NULL
None.gif)
None.gifGO
None.gifINSERT INTO Arrays VALUES('')
None.gifINSERT INTO Arrays VALUES('10')
None.gifINSERT INTO Arrays VALUES('20,40,30')
None.gifINSERT INTO Arrays VALUES('-1,-3,-5')
None.gifGO
None.gifCREATE FUNCTION function1(@arr AS VARCHAR(7999))
None.gifRETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)
None.gifAS
None.gifBEGIN
None.gifDECLARE @end AS INT, @start AS INT, @pos AS INT
None.gifSELECT @arr = @arr + ',', @pos = 1,
None.gif@start = 1, @end = CHARINDEX(',', @arr, @start)
None.gifWHILE @end > 1
None.gifBEGIN
None.gifINSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))
None.gif
None.gifSELECT @pos = @pos + 1,
None.gif@start = @end + 1, @end = CHARINDEX(',', @arr, @start)
None.gifEND
None.gifRETURN
None.gifEND
None.gif
None.gif--测试
None.gif
SELECT * FROM function1('200,400,300')
None.gifGO
None.gif
None.gifSELECT A.aid, F.*
None.gifFROM Arrays AS A
None.gifCROSS APPLY function1(array) AS F
None.gifGO
None.gifSELECT A.aid, F.*
None.gifFROM Arrays AS A
None.gifOUTER APPLY function1(array) AS F
None.gifGO
None.gif
None.gifDDL触发器 DDL Triggers
None.gif
None.gifSQL Server 2005可以就整个服务器或数据库的某个范围为DDL事件定义触发器。也可以为单个DDL语句(例如:CREAT_TABLE、DROP_TABLE等)或者为一组语句(例如:指定DDL_DATABASE_LEVEL_EVENTS想要触发器触发数据库所有DDL事件)定义DDL触发器。
None.gif
None.gif在DDL触发器内部,可以通过访问eventdata()函数获得与激发该触发器的事件有关的数据。该eventdata()函数返回有关事件的xml数据。
None.gif
None.gifDDL触发器特别有用的方案包括DDL更改的完整性检查、审核方案以及其他方案。
None.gif
None.gif代码演示:
None.gif
None.gifUSE demo
None.gifGO
None.gifCREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE
None.gifAS
None.gifRAISERROR('没有删除表的权限.', 10, 1)
None.gifPRINT '尝试在数据库' + DB_NAME() + '中删除表.'
None.gifPRINT CONVERT (nvarchar (1000),EventData())
None.gifROLLBACK
None.gifGO
None.gif-- 测试
None.gif
CREATE TABLE TestDROP(col1 INT)
None.gifGO
None.gifINSERT INTO TestDROP VALUES(1)
None.gif
None.gifDROP TABLE testdrop
None.gif
None.gif-- Server
None.gif
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
None.gifFOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
None.gifAS
None.gifPRINT '发生DDL LOGIN.'
None.gifPRINT CONVERT (nvarchar (1000),EventData())
None.gifGO
None.gif
None.gif-- 测试
None.gif
CREATE LOGIN login1 WITH PASSWORD = '123'
None.gifALTER LOGIN login1 WITH PASSWORD = 'xyz'
None.gifDROP LOGIN login1
None.gifSQL Server 2005 在Transaction-SQL上所做的改进反映了其更好地满足了ANSI-99 SQL规范的要求以及客户的需求。
None.gif
None.gif
None.gifcreate proc [dbo].[Name_Add]
None.gif@Name varchar(50)
None.gifas
None.gifbegin
None.gif
None.gifbegin tran
None.gifinsert Names (Name)
None.gifselect (@Name) where not exists (select NameId from Names with(HOLDLOCK) where Name = @Name)
None.gifcommit tran
None.gif
None.gifselect NameId,Name from Names with(nolock) where Name = @Name
None.gifend
None.gif
None.gif 要点:检查,加锁,插入值在一句sql中完成.这样再大的并发也不怕了.
None.gif



本文转自高海东博客园博客,原文链接:http://www.cnblogs.com/ghd258/archive/2005/10/19/257914.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: