SQL常用知识点总纲

Level关键字说明备注示例1Primarykey主键概念理解1ForeignKey外键概念理解1Index索引/聚焦索引/唯一索引1、索引分类及区别2、索引与优化3、索引与死锁1Check约束/唯一约束1Identity自增长1ComputeField计算字段1、固定值2、计算公式3Cursor游标2@Table表变量2#Table临时表1、创建2、生命周期1Convert/Cast类型转换函数1NOLOCK忽略锁1、概念2、何时需要3、与锁的关系1DECLARE申明变量1、初始值不可少DECLARE@sNVARCHAR(MAX)=''1View视图1、单表视图2、多表视图3、视图的索引穿透性1SELECT查询1、单表查询2、多表查询3、子查询4、基表概念1Where条件1、In2、Like3、Where条件与索引的关系1SELECTINTO1、IDENTITY2、identity重复问题2CREATETABLE@temp创建表变量/临时表1Update新增1、单表更新2、多表更新3、Update中的@变量应用1Delete删除1、单表删除2、多表删除2BeginTran/CommitTranRollBackTran事务1、概念理解2、事务与锁1NULL空10x空值1ISNULL1NULLIF1CASE2UNION/UNIONALL联合1、两者的区别2Timestamp1、概念理解2、并发处理3CheckSum1DateDiff日期相减一、知识点总纲*SQL常用知识点总纲*2014年9月12日18:00分区开发文档的第1页1DateDiff日期相减1DatePart日期部分1DateAdd日期相加3EXEC动态执行SQL3Sp_executesql动态执行SQL1、output参数1INNERJOIN关联Join与On的关系1LEFTJOIN左连接1GroupBy汇总1Having汇总后条件1OrderBY排序1MIN取最小值1MAX取最大值1SUM取汇总值1AVG取平均值1@@RowCount全局上一影响行数1IN/NOTIN不在…范围中3Exists/NOTExists不存在3CROSSAPPLY3Outerapply3RowNumber行号排序计算3Rank行号排序计算4Pivot纵转横4UnPivot横转纵3fnpbConvertStringToTable字符串转表3fnpbConvertStringToTwoFieldsTable字符串转表(两字段)3fnpbConcatString字符串联3fnpbConcatStringEx字符串联3sppbNewIden生成Iden3sppbNewBillNo生成单据号5ForxmlSQL生成XML5Xml.nodes5Xml.Query5Xml.value6with递归查询6常规递归查询5#Table逐行Update2GOTO2TRY..Catch3Sp_rename对象更名3ExtendProperty扩展属性3dvColumns字段列表3dvColumnsEx字段列表3dvTables表列表3dvIndex索引列表3dvCheck约束列表3dvForeignKeys外键列表1OBJECT_ID对象ID3vwpbLockInfo死锁监测3Lock死锁1、死锁的形成机制分区开发文档的第2页3Lock死锁1、死锁的形成机制2、锁的类型3、锁与索引的关系4、如何规避死锁2RAISERROR3FormatString3UniqueValidate字段唯一校验5sppbPivot纵转横6SQL.debugSQL代码调试3COALESCE返回非空值COALESCE(a,b,c,d)5Grant/Revoke/Deny授权5sppbQueryDBUserGrantRight查询授权5sppbBackupDBObjectGrantRight备份授权5sppbRestoreDBObjectGrantRight还原授权5FileStream排序a.正序排序：SELECT*FROMdbo.saUserA(NOLOCK)ORDERBYA.sUserNob.倒序排序：SELECT*FROMdbo.saUserA(NOLOCK)ORDERBYA.sUserNoDESCc.混合排序：SELECT*FROMdbo.saUserA(NOLOCK)ORDERBYA.iClassIdDESC,A.sUserNod.代表按返回的第二列的值倒序排序+第三列的值正序排序i.简写语法，不允许出现在正式代码中，仅调试时可使用ii.简写语法：SELECT*FROMdbo.saUserA(NOLOCK)ORDERBY2DESC,3e.OrderBy1.设定字段为自增长a.IDENTITY(0,1)代表从０开始，每次递增１i.IDENTITY(1,1)代表从１开始，每次递增１ii.参数说明：参数１为初始值，参数２为递增量b.CREATETABLE#Order(iIdenINTIDENTITY(0,1))创建表/临时表时i.SELECTiRowId=Identity(INT,0,1),A.*INTO#UserFROMdbo.saUserA(NOLOCK)WHEREA.bUsable=1SELECTINTOii.单个表中只允许出现一个Identity字段1)上例中如果saUser表中原本就有Identity字段，则会报错，上面sql需修改为如下：2)--iIden原本为Identity，所以需转换后才不与iRowId冲突SELECTiRowId=Identity(INT,0,1),iIden=Convert(INT,A.iIden),A.sUserNo,A.sUserNameINTO#UserFROMdbo.saUserA(NOLOCK)WHEREA.bUsable=1注意：iii.适用场景：c.Identity2.UPDATEdbo.saUseri.SETbUsable=1WHEREiIden=1UPDATEAii.SETbUsable=1FROMdbo.saUserAWHEREA.iIden=1正式代码中统一用方式２语法格式iii.单表Updatea.多表Updateb.Update3.二、示例代码与说明分区开发文档的第3页UPDATEAi.SETbUsable=1FROMdbo.saUserAJOINdbo.pbCompanyB(NOLOCK)ONB.iIden=A.iCompanyIdWHEREB.iIden=1ANDA.sUserNo='caizh'UPDATEAii.SETsUserName=B.sNameFROMdbo.saUserAJOINdbo.pbUserB(NOLOCK)ONB.sUserNo=A.sUserNoWHEREA.bUsable=1ANDB.bUsable=1注意：多表更新时，需要更新的表不添加NOLOCK，其它查询表要添加NOLOCKiii.多表Updateb.Update语法中的SET中允许使用@变量，执行顺序是先执行@变量的计算，再执行字段的写入a.UPDATEAi.SETiAge+=1FROMdbo.StudentAWHEREA.iAge20UPDATEAii.SET@i=A.iAge,iAge=@i+1FROMdbo.saUserAWHEREA.iAge20UPDATEAiii.SETiAge=@i+1,@i=A.iAgeFROMdbo.saUserAWHEREA.iAge20注意：以上三段ＳＱＬ效果完全等同iv.示例:要求将年龄大于２０岁的学生的年龄+１b.SELECTsFile='D:\Work\DB\A.sql'i.INTO#Fileii.INSERTINTO#Fileiii.SELECTsFile='D:\Work\DB\Procedure\B.cs'iv.要求使用一条Update语句，去除文件名中的路径，执行后表中两行数据分别为：A.sql,B.csv.DECLARE@iINT=0,@sNVARCHAR(MAX)=''UPDATE#FileSET@s=REVERSE(sFile),@i=CHARINDEX('\',@s),@s=REVERSE(LEFT(@s,@i-1)),sFile=@s答案如下：vi.问题1：请取出#File表中的文件名的串联c.问题２：出库摊销问题d.UPDATE中变量的应用4.游标提供一种数据的单行循环处理方式a.DECLARE@iIdenINT=0,@sUserNoNVARCHAR(50)='',@sUserNameNVARCHAR(50)=''DECLAREcurUserCURSORSELECTTOP5iIden,sUserNo,sUserNameFROMdbo.saUserA(NOLOCK)WHEREA.bUsable=1ORDERBYA.iIdenFOROPENcurUserFETCHNEXTFROMcurUserINTO@iIden,@sUserNo,@sUserNameWHILE@@FETCH_STATUS=0SELECT@iIden,@sUserNo,@sUserNameFETCHNEXTFROMcurUserINTO@iIden,@sUserNo,@sUserNameBEGINEND示例代码如下：b.Cursor游标5.分区开发文档的第4页ENDCLOSEcurUserDEALLOCATEcurUserDECLARE@iIdenINT=0,@sUserNoNVARCHAR(50)='',@sUserNameNVARCHAR(50)=''DECLARE@iINT=1,@iCountINT=0SELECTTOP5iRowId=IDENTITY(INT,1,1),A.iIden,A.sUserNo,A.sUserNameINTO#UserFROMdbo.saUserA(NOLOCK)WHEREA.bUsable=1ORDERBYA.iIdenSET@iCount=@@RowCountWHILE@i=@iCountSELECT@iIden=A.iIden,@sUserNo=A.sUserNo,@sUserName=A.sUserNameFROM#UserAWHEREA.iRowId=@iSELECT@iIden,@sUserNo,@sUserNameSET@i+=1BEGINENDDROPTABLE#User替代方法１：临时表+Identity的循环c.SET@sBillId+=','DECLARE@iStartINT=1DECLARE@iEndINT=CHARINDEX(',',@sBillId)WHILE@iEnd0SET@iBillId=Convert(INT,SUBSTRING(@sBillId,@iStart,@iEnd-@iStart))IF@iBillId0--循环内可取到单个值做任意处理--,@iUserId=@iUserId,@sUserNo=@sUserNo,@iBillTableId=@iBillTableId--EXECdbo.sppbBillOperate_SendToAudit@iBillTypeId=@iBillTypeId,@iBillId=@iBillIdBEGINENDSET@iStart=@iEnd+1SET@iEnd=CHARINDEX(',',@sBillId,@iStart)BEGINEND替代方法２：字符串联数据的循环，此方法仅适用于对单个字符串中的多个值进行循环处理的应用场景d.表变量其实就是一个临时的数据集，它只存在于内存中，速度比临时表更快，但同时占用内存也更多a.表变量与变量一样，是有生命周期的，它的生命周期就存在于定义此表变量的过程/函数中b.表变量的可见范围为当前过程，不能像变量一样传递到下一个过程中，或EXEC的动态SQL中，仅能在当前过程中使用c.DECLARE@UserTable(iIdenINT,sUserNoNVARCHAR(20))i.表变量定义