SQL用法总结.doc

《SQL用法总结.doc》由会员分享，可在线阅读，更多相关《SQL用法总结.doc（10页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL用法总结SQL用法总结-=checkspacesandcleandata=-checkalldbsspacesp_helpdb-checkalltablesspacecreatetable#test(namevarchar(50),rowsint,reservedvarchar(20),datavarchar(20),index_sizevarchar(20),unusedvarchar(20)insertinto#testexecsp_MSforeachtableexecsp_spaceused?select*from#testorderbycast(replace(reserved,

2、KB,)asint)descdroptable#test-cleanalltabledata/*execsp_MSforeachtabledeletefrom?execsp_MSforeachtabletruncatetable?*/-=处理日志=-由于SQL2022对文件和日志管理进行了优化,所以以下语句在SQL2022中可以运行但在SQL2022中已经被取消:-SQL2022/*BackupLogDNNamewithno_logDumpTransactionDNNamewithno_logDBCCSHRINKFILE(2)*/-SQL2022-在SQL2022中清除日志就必须在简单模式下进

3、行,等清除动作完毕再调回到完全模式.ALTERDATABASEDNNameSETRECOVERYSIMPLE-WITHNO_WAIT简单模式DBCCSHRINKFILE(DNName_Log,EMPTYFILE,TRUNCATEONLY)-DBCCSHRINKFILE(DNName_Log,1,TRUNCATEONLY)ALTERDATABASEDNNameSETRECOVERYFULL-WITHNO_WAIT还原为完全模式-优点:此清除日志所运行消耗的时间短,90GB的日志在分钟左右即可清除完毕,做完之后做个完全备份在分钟内即可完成.-缺点:不过此动作最好不要经常使用,因为它的运行会带来系统

4、碎片.普通状态下LOG和DIFF的备份即可截断日志.-此语句使用的恰当环境:当系统的日志文件异常增大或者备份LOG时间太长可能影响生产的情况下使用.-=Rankingwindowfunctions=-runonAdventureWorks-UsingtheOVERclausewithaggregatefunctionsSELECTSalesOrderID,ProductID,OrderQty,SUM(OrderQty)OVER(PARTITIONBYSalesOrderID)ASTotal,AVG(OrderQty)OVER(PARTITIONBYSalesOrderID)ASAvg,COUN

5、T(OrderQty)OVER(PARTITIONBYSalesOrderID)ASCount,MIN(OrderQty)OVER(PARTITIONBYSalesOrderID)ASMin,MAX(OrderQty)OVER(PARTITIONBYSalesOrderID)ASMax,CAST(1.*OrderQty/SUM(OrderQty)OVER(PARTITIONBYSalesOrderID)*100ASDECIMAL(5,2)ASPercentbyProductIDFROMSales.SalesOrderDetailWHERESalesOrderIDIN(43659,43664)-

6、UsingtheOVERclausewiththeROW_NUMBERfunctionSELECTc.FirstName,c.LastName,ROW_NUMBER()OVER(partitionbyPostalCodeORDERBYSalesYTDDESC)ASRowNumber,s.SalesYTD,a.PostalCodeFROMSales.SalesPersonsINNERJOINPerson.ContactcONs.SalesPersonID=c.ContactIDINNERJOINPerson.AddressaONa.AddressID=c.ContactIDWHERETerrit

7、oryIDISNOTNULLANDSalesYTD0SELECTc.FirstName,c.LastName,ROW_NUMBER()OVER(ORDERBYa.PostalCode)ASRowNumber,RANK()OVER(ORDERBYa.PostalCode)ASRank,DENSE_RANK()OVER(ORDERBYa.PostalCode)ASDenseRank,NTILE(4)OVER(ORDERBYa.PostalCode)ASQuartile,s.SalesYTD,a.PostalCodeFROMSales.SalesPersonsINNERJOINPerson.Cont

8、actcONs.SalesPersonID=c.ContactIDINNERJOINPerson.AddressaONa.AddressID=c.ContactIDWHERETerritoryIDISNOTNULLANDSalesYTD0-=CreatePartitionondatabase=-1.AddfilegroupsandfilestodatabaseALTERDATABASETestADDfilegrouptestFg1ALTERDATABASETestADDFILE(NAME=test1,FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQL1

9、0.MSSQLSERVERMSSQLDATAtest1.ndf,SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)TOFILEGROUPtestFgALTERDATABASETestADDfilegrouptestFg2ALTERDATABASETestADDFILE(NAME=test1,FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQL10.MSSQLSERVERMSSQLDATAtest2.ndf,SIZE=3MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)TOFILEGROUPtestF

10、g2GO-2.createpartitionfunctionandschemeCREATEPARTITIONFUNCTIONmyRangePF1(int)ASRANGERIGHTFORVALUES(20220101,20220101,20220101);GOCREATEPARTITIONSCHEMEmyRangePS1ASPARTITIONmyRangePF1TO(PRIMARY,testFg1,testFg2,testFg3)-3.applypartitionontable/*CREATETABLEPartitionTable(col1int,col2char(10)ONmyRangePS1

11、(col1)select*fromsys.partition_functionsselect*fromsys.partition_schemesselect*fromsys.partition_range_values-checkrowsoneverypartitionselect*fromsys.partitionswhereobject_id=*/BEGINTRANSACTIONCREATECLUSTEREDINDEXClusteredIndex_on_myRangePS1ONdbo.factCharge(DateKey)WITH(SORT_IN_TEMPDB=OFF,IGNORE_DUP

12、_KEY=OFF,DROP_EXISTING=OFF,ONLINE=OFF)ONmyRangePS1(DateKey)DROPINDEXClusteredIndex_on_myRangePS1ONdbo.factChargeBakWITH(ONLINE=OFF)COMMITTRANSACTION-=SQLCDC=USEAdventureWorksDW;GOEXECUTEsys.sp_cdc_enable_db;-启用数据库对CDC的支持GOEXECsys.sp_cdc_enable_tabledbo,FactInternetSales,role_name=NULL,supports_net_c

13、hanges=0;-启用某个表对CDC的支持GOSELECTname,is_tracked_by_cdcFROMsys.tablesWHEREnameLIKE(fact%)INSERTINTOFactInternetSalesVALUES(484,1127,1139,1134,18759,1,100,6,SO75124,1,1,1,21.9800,21.9800,0,0,8.2205,8.2205,21.9800,1.7584,0.5495,NULL,NULL)INSERTINTOFactInternetSalesVALUES(486,1127,1139,1134,18759,1,100,6,

14、SO75125,1,1,1,21.9800,21.9800,0,0,8.2205,8.2205,21.9800,1.7584,0.5495,NULL,NULL)UPDATEFactInternetSalesSETPromotionKey=2WHERESalesOrderNumber=SO75124DELETEFROMFactInternetSalesWHERESalesOrderNumber=SO75125SELECT*FROMcdc.dbo_FactInternetSales_CT;DECLAREbegin_timedatetime,end_timedatetime,from_lsnbina

15、ry(10),to_lsnbinary(10);-Obtainthebeginningofthetimeinterval.SETbegin_time=GETDATE()-1SETend_time=GETDATE()-Mapthetimeintervaltoachangedatacapturequeryrange.SELECTfrom_lsn=sys.fn_cdc_map_time_to_lsn(smallestgreaterthanorequal,begin_time)SELECTto_lsn=sys.fn_cdc_map_time_to_lsn(largestlessthanorequal,

16、end_time)printbegin_timeprintend_timeprintfrom_lsnprintto_lsnSELECT*FROMcdc.dbo_FactInternetSales_CTWHERE_$start_lsnBETWEENfrom_lsnANDto_lsn-撤销SQLServer2022CDCEXECsys.sp_cdc_disable_tabledbo,FactInternetSales,AllEXECsys.sp_cdc_disable_db-=Convertinttomoney=SELECTREVERSE(SUBSTRING(REVERSE(CONVERT(var

17、char,CONVERT(money,123456789),1),4,30)RMB扩展阅读：SQL的一些用法小结SQL的一些用法小结（精彩实用）1.模糊查询:select*from表名where查询字段like%biao_%通配符-匹配任意字符_通配符-匹配单个字符2.字符串的连接(|):select字段1|,|字段2别名from表名输出:字段1,字段23.查找非空项:select*from表名where字段isnotnull4.集合查询:union返回两个查询的结果并去除其中的重复部分:select字段1from表1unionselect字段1from表2unionall与union一样对表

18、进行了合并但是它不去掉重复的记录:select字段1from表1unionallselect字段1from表2intersect(相交)返回两个表中共有的行:select字段1from表1intersectselect字段1from表2mimus(相减)返回的记录是存在于第一个表中但不存在于第二个表中的记录例:select字段1from表1mimusselect字段1from表25.从属运算(in,between)select*from表where字段in(值1,值2,-)-括号内是数字则不需要用引号select*from表where字段betweenMinandMax二.函数1.count:

19、该函数将返回满足WHERE条件子句中记录的个数selectcount(*)from表where条件2.sum返回某一列的所有数值的和selectsum(字段)from表3.avg可以返回某一列的平均值selectavg(字段)from表4.max(min)取得某一列中的最大(小)值selectmax(字段)from表select*from表where字段=min(字段)_(太多了,不一一列举了,弄些自己喜欢的_)-5.user该函数返回当前使用数据库的用户的名字selectuserfrom表三.子句1.groupby用于分组selectsum(字段1)from表groupby字段2having

20、条件-按照字段2进行分组显示2.having在分组中设置条件四.子查询(子查询的条件可以关联主表和子表)select*from表1where字段=(select字段from表2条件)-子查询的结果必须是唯一select*from表1where字段in(select字段from表2条件)-子查询的结果不唯一select*from表1whereexists(select.)-exists返回trueorfalse五.精彩语句:insertinto表1(字段1,字段2.)select字段1,字段2.from表2条件-复制表六.创建和操作表1.创建表:CREATETABLEtable_name(fie

21、ld1datatypeNOTNULLfield2datatypeNOTNULLfield3datatypeNOTNULL.)2.ALTERTABLE语句可以帮助你做两件事-加入一列到已经存在的表中-修改已经存在的表中的某一列ALTERTABLE语句的语法如下ALTERTABLEtable_name-修改一列3.删除表和数据库:DROPTABLEtable_nameDROPDATABASEdatabase_name4.创建临时表:createtable#table_name(field1datatype,.fieldndatatype七,存贮过程:创建存贮过程的语法:createprocedur

22、eprocedure_name(parameter_namedatatype(length)|(precision,scale)=defaultoutput-参数,parameter_namedatatype(length)|(precision,scale)=defaultoutput.)withrecompileasSQL_statements运行存贮过程的EXECUTE命令的语法:executereturn_status=procedure_nameparameter_name=value|-参数值parameter_name=variableoutput.withrecompile八.

23、SQLSERVER提供的全局变量:在使用存储过程的时候你可以自己定义全局变量是非常有用的SQLSERVER也提供了几种系统全局变量对于数据库的系统用户来说它可能是有用的下表中给出了这些变量的全部清单你可以在SQLSERVERSYSTEM10的文档中找到它变量名作用char_convert如果字符转换成功时其值为0client_csid客户机所使用字符集的IDclient_csname客户机的字符集的名字connections从SQLServer启动以来的登录次数cpu_busy从SQLServer启动以来的CPU忙的时间总数error错误的状态identity插入到确定列中的最后一个值idle

24、从SQLServer启动以来的总时间数io_busySQLServer用于I/O操作的时间isolation当前的Transact-SQL程序的隔离级别langid定义了本地语言的ID号language定义了本地语言的名称maxcharlen字符的最大长度max_connections可与SQLSERVER进行连接的最大数量ncharsizeAveragelengthofanationalcharacter.nestlevel当前进程的嵌套级别pack_received从SQLServer启动以来的读入的数据包的数量pack_sent从SQLServer所发出的输出包的数量packet_err

25、ors从SQLServer启动以来产生错误的数量procid当前正在运行的存储过程的ID号rowcount上一个命令所涉及的行数servername本地localSQLServer的名字spid当前正在处理的进程ID号sqlstatus存储状态信息textsize由SELECT语句所返回的文本映像的最大长度thresh_hysteresisChangeinfreespacerequiredtoactivateathreshold.timeticksNumberofmicrosecondspertick.total_errors在读写过程中产生的错误数total_read在SQLServer启动以来读磁盘的次数total_write在SQLServer启动以来写磁盘的次数tranchained在Transact-SQL程序中当前事务的模式trancount事务的嵌套级别transtate当一个语句运行后当前事务的状态version当前SQLServer的版本日期第 10 页 共 10 页