简单但有用的SQL脚本.doc

1. 行列互转createtabletest(idint,namevarchar(20),quarterint,profileint)insertintotestvalues(1,a,1,1000)insertintotestvalues(1,a,2,2000)insertintotestvalues(1,a,3,4000)insertintotestvalues(1,a,4,5000)insertintotestvalues(2,b,1,3000)insertintotestvalues(2,b,2,3500)insertintotestvalues(2,b,3,4200)insertintotestvalues(2,b,4,5500)select*fromtest-行转列selectid,name,1as一季度,2as二季度,3as三季度,4as四季度,5as5fromtestpivot(sum(profile)forquarterin(1,2,3,4,5)aspvtcreatetabletest2(idint,namevarchar(20),Q1int,Q2int,Q3int,Q4int)insertintotest2values(1,a,1000,2000,4000,5000)insertintotest2values(2,b,3000,3500,4200,5500)select*fromtest2-列转行selectid,name,quarter,profilefromtest2unpivot(profileforquarterin(Q1,Q2,Q3,Q4)asunpvt2. sql替换字符串 substring replace-例子1：updatetbPersonalInfosetTrueName=replace(TrueName,substring(TrueName,2,4),*)whereID=1-例子2：updatetbPersonalInfosetMobile=replace(Mobile,substring(Mobile,4,11),*)whereID=1-例子3：updatetbPersonalInfosetEmail=replace(Email,chinamobile,*)whereID=13. SQL查询一个表内相同纪录 having如果一个ID可以区分的话，可以这么写select*from表whereIDin(selectIDfrom表groupbyIDhavingsum(1)1)如果几个ID才能区分的话，可以这么写select*from表whereID1+ID2+ID3in(selectID1+ID2+ID3from表groupbyID1,ID2,ID3havingsum(1)1)其他回答：数据表是zy_bho,想找出ZYH字段名相同的记录-方法1：SELECT*FROMzy_bhoaWHEREEXISTS(SELECT1FROMzy_bhoWHEREPKa.PKANDZYH=a.ZYH)-方法2：selecta.*fromzy_bhoajoinzy_bhobon(a.pkb.pkanda.zyh=b.zyh)-方法3：select*fromzy_bbowherezyhin(selectzyhfromzy_bbogroupbyzyhhavingcount(zyh)1)-其中pk是主键或是unique的字段。4. 把多行SQL数据变成一条多列数据，即新增列SelectDeptName=O.OUName,9G=Sum(CaseWhenPersonalGrade=9Then1Else0End),8G=Sum(CaseWhenPersonalGrade=8Then1Else0End),7G4=Sum(CaseWhenPersonalGrade=7ANDJobGrade=4Then1Else0End),7G3=Sum(CaseWhenPersonalGrade=7ANDJobGrade=3Then1Else0End),6G=Sum(CaseWhenPersonalGrade=6Then1Else0End),5G3=Sum(CaseWhenPersonalGrade=5ANDJobGrade=3Then1Else0End),5G2=Sum(CaseWhenPersonalGrade=5ANDJobGrade=2Then1Else0End),4G=Sum(CaseWhenPersonalGrade=4Then1Else0End),3G2=Sum(CaseWhenPersonalGrade=3ANDJobGrade=2Then1Else0End),3G1=Sum(CaseWhenPersonalGrade=3ANDJobGrade=1Then1Else0End),2G=Sum(CaseWhenPersonalGrade=2Then1Else0End),1G=Sum(CaseWhenPersonalGrade=1Then1Else0End),-未定级=Sum(CaseWhenPersonalGrade=NULLThen1Else0End)5. 复制表insertintoPhoneChange_Num(IMSI,Num)SELECTIMSI,count(IMEI)asnumFROMTest.dbo.PhoneChangegroupbyIMSIorderbynumdesc语法1：Insert INTO table(field1,field2,.) values(value1,value2,.)语法2：Insert into Table2(field1,field2,.) select value1,value2,. from Table1（要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。）语法3：SELECT vale1, value2 into Table2 from Table1（要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。）语法4：使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】，那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内存爆了？它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的，你刷新一下mdf文件就知道了。 6. 利用带关联子查询Update语句更新数据-方法1：UpdateTable1setc=(selectcfromTable2wherea=Table1.a)wherecisnull-方法2：updateAsetnewqiantity=B.qiantityfromA,BwhereA.bnum=B.bnum-方法3：update(selectA.bnum,A.newqiantity,B.qiantityfromAleftjoinBonA.bnum=B.bnum)ASCsetC.newqiantity=C.qiantitywhereC.bnum=XX7. 连接远程服务器-方法1：select*fromopenrowset(SQLOLEDB,server=192.168.0.67;uid=sa;pwd=password,SELECT*FROMBCM2.dbo.tbAppl)-方法2：select*fromopenrowset(SQLOLEDB,192.168.0.67;sa;password,SELECT*FROMBCM2.dbo.tbAppl)8. 查询时间不带世纪数位 (yy) (1)带世纪数位 (yyyy)标准输入/输出 (3)-0 或 100 (1,2)默 认mon dd yyyy hh:miAM（或 PM）1101美 国mm/dd/yyyy2102ANSIyy.mm.dd3103英 国/法国dd/mm/yyyy4104德 国dd.mm.yy5105意 大利dd-mm-yy6106(1)-dd mon yy7107(1)-mon dd, yy8108-hh:mi:ss-9 或 109 (1,2)默 认设置 + 毫秒mon dd yyyy hh:mi:ss:mmmAM（或 PM）10110美 国mm-dd-yy11111日 本yy/mm/dd12112ISOyymmddyyyymmdd-13 或 113 (1,2)欧 洲默认设置 + 毫秒dd mon yyyy hh:mi:ss:mmm(24h)14114-hh:mi:ss:mmm(24h)-20 或 120 (2)ODBC 规范yyyy-mm-dd hh:mi:ss(24h)-21 或 121 (2)ODBC 规范（带毫秒）yyyy-mm-dd hh:mi:ss.mmm(24h)-126 (4)ISO8601yyyy- mm-ddThh:mi:ss.mmm（无空格）-127(6, 7)带时区 Z 的 ISO8601。yyyy-mm-ddThh:mi:ss.mmmZ（无 空格）-130 (1,2)回历 (5)dd mon yyyy hh:mi:ss:mmmAM-131 (2)回历 (5)dd/mm/yy hh:mi:ss:mmmAM-语句及查询结果：SELECTCONVERT(varchar(100),GETDATE(),0):0516200610:57AMSELECTCONVERT(varchar(100),GETDATE(),1):05/16/06SELECTCONVERT(varchar(100),GETDATE(),2):06.05.16SELECTCONVERT(varchar(100),GETDATE(),3):16/05/06SELECTCONVERT(varchar(100),GETDATE(),4):16.05.06SELECTCONVERT(varchar(100),GETDATE(),5):16-05-06SELECTCONVERT(varchar(100),GETDATE(),6):160506SELECTCONVERT(varchar(100),GETDATE(),7):0516,06SELECTCONVERT(varchar(100),GETDATE(),8):10:57:46SELECTCONVERT(varchar(100),GETDATE(),9):0516200610:57:46:827AMSELECTCONVERT(varchar(100),GETDATE(),10):05-16-06SELECTCONVERT(varchar(100),GETDATE(),11):06/05/16SELECTCONVERT(varchar(100),GETDATE(),12):060516SELECTCONVERT(varchar(100),GETDATE(),13):1605200610:57:46:937SELECTCONVERT(varchar(100),GETDATE(),14):10:57:46:967SELECTCONVERT(varchar(100),GETDATE(),20):2006-05-1610:57:47SELECTCONVERT(varchar(100),GETDATE(),21):2006-05-1610:57:47.157SELECTCONVERT(varchar(100),GETDATE(),22):05/16/0610:57:47AMSELECTCONVERT(varchar(100),GETDATE(),23):2006-05-16SELECTCONVERT(varchar(100),GETDATE(),24):10:57:47SELECTCONVERT(varchar(100),GETDATE(),25):2006-05-1610:57:47.250SELECTCONVERT(varchar(100),GETDATE(),100):0516200610:57AMSELECTCONVERT(varchar(100),GETDATE(),101):05/16/2006SELECTCONVERT(varchar(100),GETDATE(),102):2006.05.16SELECTCONVERT(varchar(100),GETDATE(),103):16/05/2006SELECTCONVERT(varchar(100),GETDATE(),104):16.05.2006SELECTCONVERT(varchar(100),GETDATE(),105):16-05-2006SELECTCONVERT(varchar(100),GETDATE(),106):16052006SELECTCONVERT(varchar(100),GETDATE(),107):0516,2006SELECTCONVERT(varchar(100),GETDATE(),108):10:57:49SELECTCONVERT(varchar(100),GETDATE(),109):0516200610:57:49:437AMSELECTCONVERT(varchar(100),GETDATE(),110):05-16-2006SELECTCONVERT(varchar(100),GETDATE(),111):2006/05/16SELECTCONVERT(varchar(100),GETDATE(),112):20060516SELECTCONVERT(varchar(100),GETDATE(),113):1605200610:57:49:513SELECTCONVERT(varchar(100),GETDATE(),114):10:57:49:547SELECTCONVERT(varchar(100),GETDATE(),120):2006-05-1610:57:49SELECTCONVERT(varchar(100),GETDATE(),121):2006对上面进行动态生成字符串：declaresql1nvarchar(200),sql2nvarchar(200)declarecountnvarchar(100);setsql1=SELECTCONVERT(varchar(100),GETDATE(),0)setsql2=SELECTcount=CONVERT(varchar(100),GETDATE(),0)execsp_executesqlsql2,Ncountnvarchar(50)out,countoutprintsql1+：+count-SQLServer仅保证往返转换（即从原始数据类型进行转换后又返回原始数据类型的转换）在各版本间产生相同值。DECLAREmyvaldecimal(5,2)SETmyval=193.57SELECTCAST(CAST(myvalASvarbinary(20)ASdecimal(10,5)-Or,usingCONVERTSELECTCONVERT(decimal(10,5),CONVERT(varbinary(20),myval)-输出193.57000-输出193.57000-bigint数据类型的字段截取（其它类型也一样）selectsubstring(CONVERT(varchar(15),字段名),11,9)from表名selectsubstring(cast(字段名asvarchar(50),6,9)from表名9. SQL中的相除-SQL中的相除SELECTCASEWHENISNULL(A+B,0)0THENLTRIM(CONVERT(DEC(18,2),A*100.0/(A+B)+%ELSEENDAS百分数FROMTB-百分比的不同格式selectLTRIM(CONVERT(DEC(18,2),42*100.0/96)+%AS百分数string,-DEC=decimalCONVERT(decimal(10,2),42*100.0/96)AS百分数dec,-100与100.0是不一样的CONVERT(decimal(10,2),42*100/96)AS没有保留到小数点-fromTborderby百分数decdesc-43.75%43.7543.00-方法二：Select(Convert(varchar(50),Round(42*100.0/96,3)+%)as百分比-fromA-43.750000%10. 四舍五入/*ROUND(numeric_expression,length,function)function必须为tinyint、smallint或int。如果省略function或其值为0（默认值），则将舍入numeric_expression。如果指定了0以外的值，则将截断numeric_expression。*/SELECTROUND(150.45648,2);-保留小数点后两位，需要四舍五入SELECTROUND(150.45648,2,0);-保留小数点后两位，0为默认值，表示进行四舍五入SELECTROUND(150.45648,2,1);-保留小数点后两位，不需要四舍五入，这里除0以外都是有同样的效果SELECTROUND(150.45648,2,2);-保留小数点后两位，不需要四舍五入，这里除0以外都是有同样的效果-150.46000-150.45000-150.45000-150.4500011. 对字段出现NULL值的处理-判断某些字段是否为空-caseselectcasewhen字段名isnullthenNelseconvert(varchar(20),字段名)endasNewNameselectcasewhennullisnullthenNelseconvert(varchar(20),null)endasNewName-SQLServer2005：coalesceselectcoalesce(字符串类型字段,N)asNewNameselectcoalesce(convert(varchar(20),非字符串类型字段),N)asNewNameselectcoalesce(convert(varchar(20),null),N)asNewName-coalesce,返回其参数中的第一个非空表达式selectCoalesce(null,null,1,2,null)unionselectCoalesce(null,11,12,13,null)unionselectCoalesce(111,112,113,114,null)12. count的几种情况-第一种selectcount(*)fromtablename-第二种selectcount(ID)fromtablename-第三种,1换成其它值也是可以的selectcount(1)fromtablename/*-第四种,这个不存在性能问题idint表ID(如果indid=0或255)。否则为索引所属表的IDIndidsmallint索引ID：0表1聚簇索引1非聚簇索引255具有text或image数据的表条目。rowsint基于indid=0和indid=1地数据级行数，该值对于indid1重复。如果indid=255，rows设置为0。当表没有聚簇索引时，Indid=0否则为1。*/selectrows,indidfromsysindexeswhereid=object_id(tablename)andindidin(0,1)13. 查看数据库缓存的SQL-适用MSSQL2000、MSSQL2005usemasterdeclaredbidintSelectdbid=dbidfromsysdatabaseswherename=Test-修改成数据库的名称selectdbid,UseCounts,RefCounts,CacheObjtype,ObjType,DB_Name(dbid)asDatabaseName,SQLfromsyscacheobjectswheredbid=dbidorderbydbid,useCountsdesc,objtype14. 删除计划缓存-删除整个数据库的计划缓存DBCCFREEPROCCACHE-删除某个数据库的计划缓存USEmasterDECLAREdbidINTSELECTdbid=dbidFROMsysdatabasesWHERENAME=表名DBCCFLUSHPROCINDB(dbid)15. 导出时加入特殊字符情况一：全部字段都需要加字符，在这里设置【文本限定符】就可以了。情况二：-某些特殊的