MYSQL编程：SQL Server 2005中的T-SQL加强

由于在MySQL中有如此众多的额外功能可选，诸如存储引擎等，你可以选择最适合你公司的一个，或者尝试选用多个引擎。MySQL开始非常小巧，但是可以随着公司的成长而不断地变强大。server丰厚的数据范例RicherDataTypes
1、varchar(max)、nvarchar(max)和varbinary(max)数据范例最多能够保留2GB的数据，能够代替text、ntext或image数据范例。
CREATETABLEmyTable
(
idINT,
contentVARCHAR(MAX)
)
2、XML数据范例
XML数据范例同意用户在SQLServer数据库中保留XML片断或文档。
毛病处置ErrorHandling
1、新的非常处置布局
2、能够捕捉和处置已往会招致批处置停止的毛病
条件是这些毛病不会招致毗连中止（一般是严峻水平为21以上的毛病，比方，表或数据库完全性可疑、硬件毛病等等。）。
3、TRY/CATCH机关
SETXACT_ABORTON
BEGINTRY
<corelogic>
ENDTRY
BEGINCATCHTRAN_ABORT
<exceptionhandlinglogic>
ENDTRY
@@errormaybequiredasfirststatementinCATCHblock
4、演示代码
USEdemo
GO
--创立事情表
CREATETABLEstudent
(
stuidINTNOTNULLPRIMARYKEY,
stunameVARCHAR(50)
)
CREATETABLEscore
(
stuidINTNOTNULLREFERENCESstudent(stuid),
scoreINT
)
GO
INSERTINTOstudentVALUES(101,zhangsan)
INSERTINTOstudentVALUES(102,wangwu)
INSERTINTOstudentVALUES(103,lishi)
INSERTINTOstudentVALUES(104,maliu)
--挪用一个运转时毛病
SETXACT_ABORTOFF
BEGINTRAN
INSERTINTOscoreVALUES(101,90)
INSERTINTOscoreVALUES(102,78)
INSERTINTOscoreVALUES(107,76)/*外键毛病*/
INSERTINTOscoreVALUES(103,81)
INSERTINTOscoreVALUES(104,65)
COMMITTRAN
GO
SELECT*FROMstudent
SELECT*FROMscore
--利用TRY...CATCH机关，并挪用一个运转时毛病
SETXACT_ABORTOFF
BEGINTRY
BEGINTRAN
INSERTINTOscoreVALUES(101,90)
INSERTINTOscoreVALUES(102,78)
INSERTINTOscoreVALUES(107,76)/*外键毛病*/
INSERTINTOscoreVALUES(103,81)
INSERTINTOscoreVALUES(104,65)
COMMITTRAN
PRINT事件提交
ENDTRY
BEGINCATCH
ROLLBACK
PRINT事件回滚
SELECTERROR_NUMBER()ASErrorNumber,
ERROR_SEVERITY()ASErrorSeverity,
ERROR_STATE()asErrorState,
ERROR_MESSAGE()asErrorMessage;
ENDCATCH
GO
SELECT*FROMscore
GO
快照断绝SnapshotIsolation
1、写进程序不会拦阻读取程序
2、SnapshotisolationmustbeenabledforDB
ALTERDATABASE数据库SETallow_snapshot_isolationON
3、Snapshotisolationmustbeenabledforconnection
Settransactionisolationlevelsnapshot
4、UPDATEtransactionskeepoldversionsofdatainalinkedlist
5、新的断绝级别供应了以下长处：
　1)进步了只读使用程序的数据可用性
　2)同意在OLTP情况中实行非制止读取操纵
　3)可对写进事件举行主动的强迫抵触检测
6、演示代码
CREATEDATABASEdemo2
GO
USEdemo2
ALTERDATABASEdemo2SETallow_snapshot_isolationON
CREATETABLEtest
(
tidINTNOTNULLprimarykey,
tnameVARCHAR(50)NOTNULL
)
INSERTINTOtestVALUES(1,version1)
INSERTINTOtestVALUES(2,version2)
--毗连一
USEdemo2
BEGINTRAN
UPDATEtestSETtname=version3WHEREtid=2
SELECT*FROMtest
--毗连二
USEdemo2
SETtransactionisolationlevelsnapshot
SELECT*FROMtest
TOP加强功效
1、TOP加强
能够指定一个数字表达式，以前往要经由过程查询影响的行数或百分比，还能够依据情形利用变量或子查询。
能够在DELETE、UPDATE和INSERT查询中利用TOP选项。
2、更好地交换SETROWCOUNT选项，使之更加无效。
OUTPUT
1、SQLServer2005引进一个新的OUTPUT子句，以使您能够冲修正语句(INSERT、UPDATE、DELETE)中将数据前往到表变量中。
2、新的OUTPUT子局的语法为：
OUTPUT<dml_select_list>INTO@table_variable
能够经由过程援用拔出的表或删除的表来会见被修正的行的旧/新记忆，其体例与会见触发器相似。在INSERT语句中，只能会见拔出的表。在DELETE语句中，只能会见删除的表。在UPDATE语句中，能够会见拔出的表和删除的表。
3、代码演示
USEdemo
GO
CREATETABLEtt
(
idINTIDENTITY,
c1VARCHAR(15)
)
GO
INSERTINTOttVALUES(r1)
INSERTINTOttVALUES(r2)
INSERTINTOttVALUES(r5)
INSERTINTOttVALUES(r6)
INSERTINTOttVALUES(r7)
INSERTINTOttVALUES(r8)
INSERTINTOttVALUES(r9)
INSERTINTOttVALUES(r10)
DECLARE@delASTABLE(deletedIdINT,deletedValueVARCHAR(15))
DELETEtt
OUTPUTDELETED.id,DELETED.c1INTO@del
WHEREid<3
SELECT*FROM@del
GO
-----------------------------------------------
USEdemo
GO
CREATETABLEtoptest(column1VARCHAR(150))
GO
INSERTINTOtoptestVALUES(t1)
INSERTINTOtoptestVALUES(t2)
INSERTINTOtoptestVALUES(t3)
INSERTINTOtoptestVALUES(t4)
INSERTINTOtoptestVALUES(t5)
INSERTINTOtoptestVALUES(t6)
INSERTINTOtoptestVALUES(t7)
INSERTINTOtoptestVALUES(t8)
SELECT*FROMtoptest
GO
CREATETABLEtoptest2(column2VARCHAR(150))
GO
INSERTINTOtoptest2VALUES(c1)
INSERTINTOtoptest2VALUES(c2)
--声明3个变量
DECLARE@aINT
DECLARE@bINT
DECLARE@cINT
--赋值
SET@a=10
SET@b=5
SELECT@c=@a/@b
--利用盘算表达式
SELECTTOP(@c)*FROMtoptest
--利用SELECT语句作为前提
SELECTTOP(SELECTCOUNT(*)FROMtoptest2)*
FROMtoptest
--指出top
DELETETOP(2)toptestwherecolumn1>t6
--更新top
UPDATETOP(2)toptestSETcolumn1=hiwherecolumn1<=t2
SELECT*FROMtoptest
排序函数RankingFunctions
1、SQLServer引进几个新的排序函数：如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您能够无效地剖析数据和向查询的了局行供应排序值。
2、排序函数都遵守相似的语法形式：
()OVER
([PARTITIONBY]
ORDERBY)
该函数只能在查询的两个子句中指定-在SELECT子句或ORDERBY子句中。以下具体会商分歧的函数。
3、ROW_NUMBER
ROW_NUMBER是了局集的按次,而不是数据库中记录寄存的原始按次
USEdemo
GO
CREATETABLErankorder
(
orderidINT,
qtyINT
)
GO
INSERTrankorderVALUES(30001,10)
INSERTrankorderVALUES(10001,10)
INSERTrankorderVALUES(10006,10)
INSERTrankorderVALUES(40005,10)
INSERTrankorderVALUES(30003,15)
INSERTrankorderVALUES(30004,20)
INSERTrankorderVALUES(20002,20)
INSERTrankorderVALUES(20001,20)
INSERTrankorderVALUES(10005,30)
INSERTrankorderVALUES(30007,30)
INSERTrankorderVALUES(40001,40)
GO
SELECTorderid,qty,
ROW_NUMBER()OVER(ORDERBYqty)ASrownumber,
RANK()OVER(ORDERBYqty)ASrank,
DENSE_RANK()OVER(ORDERBYqty)ASdenserank
FROMrankorder
ORDERBYqty
通用表表达式CommonTableExpressions
通用表表达式（CTE）是一个能够由界说语句援用的一时表定名的了局集。在他们的复杂情势中，您能够将CTE视为相似于视图和派生表夹杂功效的改善版本。在查询的FROM子句中援用CTE的体例相似于援用派生表和视图的体例。只须界说CTE一次，便可在查询中屡次援用它。在CTE的界说中，能够援用在统一批处置中界说的变量。可是CTE的真正能力在于它们的递回功效，即CTE能够包括对它们本身的援用。
视图、派生表和CTE外部的查询的一样平常情势
1、视图
CREATEVIEW<view_name>(<column_aliases>)AS<view_query>
2、派生表
SELECT*FROM(<derived_table)query>)AS<dericed_table_alias>(<column_aliases>)
3、CTE
WITH<cte_alias>(<column_aliases>)
AS
{
<cte_query>
)
SELECT*FROM<cte_alias]>
在关头字WITH以后，为CTE供应一一般名，而且为它的了局列供应一个可选的别号列表；编写CTE的主体；然后从内部查询中援用它。
4、演示代码
USEAdventureWorks
GO
WITHSalesCTE(ProductID,SalesOrderID)
AS
(
SELECTProductID,COUNT(SalesOrderID)
FROMSales.SalesOrderDetail
GROUPBYProductID
)
SELECT*FROMSalesCTE
RecursiveCTEs递回的通用表表达式
递回的CTE是依据最少两个查询（大概称为两个成员）构建的，一个长短递回查询，同样成为流动成员，只能挪用一次，别的一个是递回查询，同样成为递回成员（RM），能够重复挪用，直到查询不再前往行。查询由UNIONALL运算符毗连为一个独自的CTE。
--利用递回的通用表表达式
USEdemo
GO
CREATETABLECarParts
(
CarIDINTNOTNULL,
PartVARCHAR(15),
SubPartVARCHAR(15),
QtyINT
)
GO
INSERTCarPartsVALUES(1,Body,Door,4)
INSERTCarPartsVALUES(1,Body,TrunkLid,1)
INSERTCarPartsVALUES(1,Body,CarHood,1)
INSERTCarPartsVALUES(1,Door,Handle,1)
INSERTCarPartsVALUES(1,Door,Lock,1)
INSERTCarPartsVALUES(1,Door,Window,1)
INSERTCarPartsVALUES(1,Body,Rivets,1000)
INSERTCarPartsVALUES(1,Door,Rivets,100)
INSERTCarPartsVALUES(1,Door,Mirror,1)
GO
SELECT*FROMCarParts
GO
WITHCarPartsCTE(SubPart,Qty)
AS
(
--流动成员(AM):
--SELECT查询无需参考CarPartsCTE
SELECTSubPart,Qty
FROMCarParts
WHEREPart=Body
UNIONALL
--递回成员(RM):
--SELECT查询参考CarPartsCTE
SELECTCarParts.SubPart,CarPartsCTE.Qty*CarParts.Qty
FROMCarPartsCTE
INNERJOINCarPartsONCarPartsCTE.SubPart=CarParts.Part
WHERECarParts.CarID=1
)
--内部查询
SELECTSubPart,SUM(Qty)ASTotalNUM
FROMCarPartsCTE
GROUPBYSubPart
新的干系运算符PIVOT/UNPIVOT/APPLY
1、PIVOT
PIVOT运算符将行扭转为列，而且大概同时实行聚合。利用PIVOT运算符时要注重的主要一点是，必要为它供应一个查询表达式，表达式利用视图、派生表大概是CTE只前往所存眷的列。
2、UNPIVOT
UNPIVOT运算符实行与PIVOT运算符相反的操纵；他将列扭转为行了。
3、APPLY
APPLY干系运算符同意您对内部表的每一个行挪用指定的表值函数一次。您能够在查询的FROM子句中指定APPLY，其体例与利用JOIN干系运算符相似。APPLY具有两种情势：CROSSAPPLY和OUTERAPPLY。
演示：
USEdemo
GO
CREATETABLEorders
(
CustomerVARCHAR(10)NOTNULL,
productVARCHAR(20)NOTNULL,
quantityINTNOTNULL
)
GO
INSERTordersVALUES(Mike,Bike,3)
INSERTordersVALUES(Mike,Chain,2)
INSERTordersVALUES(Mike,Bike,5)
INSERTordersVALUES(Lisa,Bike,3)
INSERTordersVALUES(Lisa,Chain,3)
INSERTordersVALUES(Lisa,Chain,4)
INSERTordersVALUES(Lisa,Bike,2)
SELECT*FROMorders
SELECT*FROMorders
PIVOT(SUM(quantity)FORproductIN([Bike],[Chain]))ASa
USEdemo
GO
CREATETABLESALES1
(
[Year]INT,
QuarterCHAR(2),
AmountFLOAT
)
GO
INSERTINTOSALES1VALUES(2001,Q1,80)
INSERTINTOSALES1VALUES(2001,Q2,70)
INSERTINTOSALES1VALUES(2001,Q3,55)
INSERTINTOSALES1VALUES(2001,Q3,110)
INSERTINTOSALES1VALUES(2001,Q4,90)
INSERTINTOSALES1VALUES(2002,Q1,200)
INSERTINTOSALES1VALUES(2002,Q2,150)
INSERTINTOSALES1VALUES(2002,Q2,40)
INSERTINTOSALES1VALUES(2002,Q2,60)
INSERTINTOSALES1VALUES(2002,Q3,120)
INSERTINTOSALES1VALUES(2002,Q3,110)
INSERTINTOSALES1VALUES(2002,Q4,180)
GO
SELECT*FROMSALES1
PIVOT
(SUM(Amount)--利用SUM聚合数目列
FOR[Quarter]--PIVOTQuarter列
IN(Q1,Q2,Q3,Q4))--利用季候
ASP
GO
SELECT*INTOtemp1FROMorders
PIVOT(sum(quantity)FORproductIN([Bike],[Chain]))ASa
SELECT*FROMtemp1
SELECTcustomer,product,quantity
FROMtemp1
UNPIVOT(quantityFORproductIN([Bike],[Chain]))ASa
----------------------------------------------------
USEdemo
GO
CREATETABLEArrays
(
aidINTNOTNULLIDENTITYPRIMARYKEY,
arrayVARCHAR(7999)NOTNULL
)
GO
INSERTINTOArraysVALUES()
INSERTINTOArraysVALUES(10)
INSERTINTOArraysVALUES(20,40,30)
INSERTINTOArraysVALUES(-1,-3,-5)
GO
CREATEFUNCTIONfunction1(@arrASVARCHAR(7999))
RETURNS@tTABLE(posINTNOTNULL,valueINTNOTNULL)
AS
BEGIN
DECLARE@endASINT,@startASINT,@posASINT
SELECT@arr=@arr+,,@pos=1,
@start=1,@end=CHARINDEX(,,@arr,@start)
WHILE@end>1
BEGIN
INSERTINTO@tVALUES(@pos,SUBSTRING(@arr,@start,@end-@start))
SELECT@pos=@pos+1,
@start=@end+1,@end=CHARINDEX(,,@arr,@start)
END
RETURN
END
--测试
SELECT*FROMfunction1(200,400,300)
GO
SELECTA.aid,F.*
FROMArraysASA
CROSSAPPLYfunction1(array)ASF
GO
SELECTA.aid,F.*
FROMArraysASA
OUTERAPPLYfunction1(array)ASF
GO
DDL触发器DDLTriggers
SQLServer2005能够就全部服务器或数据库的某个局限为DDL事务界说触发器。也能够为单个DDL语句（比方：CREAT_TABLE、DROP_TABLE等）大概为一组语句（比方：指定DDL_DATABASE_LEVEL_EVENTS想要触发器触发数据库一切DDL事务）界说DDL触发器。
在DDL触发器外部，能够经由过程会见eventdata()函数取得与引发该触发器的事务有关的数据。该eventdata()函数前往有关事务的xml数据。
DDL触发器出格有效的计划包含DDL变动的完全性反省、考核计划和其他计划。
代码演示：
USEdemo
GO
CREATETRIGGERprevent_drop_tableONDATABASEFORDROP_TABLE
AS
RAISERROR(没有删除表的权限.,10,1)
PRINT实验在数据库+DB_NAME()+中删除表.
PRINTCONVERT(nvarchar(1000),EventData())
ROLLBACK
GO
--测试
CREATETABLETestDROP(col1INT)
GO
INSERTINTOTestDROPVALUES(1)
DROPTABLEtestdrop
--Server
CREATETRIGGERaudit_ddl_loginsONALLSERVER
FORCREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN
AS
PRINT产生DDLLOGIN.
PRINTCONVERT(nvarchar(1000),EventData())
GO
--测试
CREATELOGINlogin1WITHPASSWORD=123
ALTERLOGINlogin1WITHPASSWORD=xyz
DROPLOGINlogin1
总结
SQLServer2005中的Transaction-SQL加强功效进步了用户在编写查询时的表达才能，利用户能够改良代码的功能，而且扩大了毛病处置才能。
SQLServer2005在Transaction-SQL上所做的改善反应了其更好地满意了ANSI-99SQL标准的请求和客户的需求。
在Transaction-SQL和托管代码之间的选择。
与其他数据库相比，MySQL易学易用。

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

至于淘汰的问题，只能说在你的项目周期之内，微软应该都不会倒闭。

如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。

记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识