《简单实用SQL脚本.docx》由会员分享，可在线阅读，更多相关《简单实用SQL脚本.docx（16页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、行列互转create table test(id int,name varchar(20)r quarter intz profile int)insertintotestvalues (1,1,1000)insertintotestvalues (lf1 a,2,2000)insertintotestvalues(1,&,3,4000)insertintotestvalues(lza,4,5000)insertintotestvalues(2,b,1,3000)insertintotestvalues(2,b,2,3500)insertintotestvalues(2Zb,3,4200)in
2、sertintotestvalues(2Zb,4,5500)select* from test一行转列 select id,name, 1 as 一季度”， 2 as 二季度”， 3 as 三季度”， 4 as 四季度”， 5 as n5n from test pivot(sum(profile) for quarter in (1, 2, 3z 4, 5) ) as pvt create table test2(id int,name varchar(20)z QI intz Q2 int, Q3 int, Q4 int)insert into test2 values(1A * a 1z
3、1000,2000,4000,5000)insert into test2 values(2,1b1,3000,3500,4200,5500)select * from test2一列转行select id,name,quarter,profile fromtest2unpivot profilefor quarter in查看数据库缓存的SQL-适用 MSSQL2000、MSSQL2005 use masterdeclare dbid intSelect dbid = dbid from sysdatabases where name = Test一修改成数据库的名称 select dbid
4、,UseCounts ,RefCounts,CacheObjtype,ObjType, DB_Name(dbid) as DatabaseName,SQL from syscacheobjects where dbid=dbid order by dbid,useCounts desc,objtype删除计划缓存一删除整个数据库的计划缓存DBCC FREEPROCCACHE删除某个数据库的计划缓存USE masterDECLARE dbid INTSELECT dbid=dbid FROM sysdatabases WHERE NAME =表名DBCC FLUSHPROC工NDB (dbid)
5、导出时加入特殊字符情况一：全部字段都需要加字符，在这里设置【文本限定符】就可以了。格式：|带分隔符文本限定符f-r在第一个数据仃中显示列名称（短情况二：某些特殊的字段需要加单引号（或者其它符号）SELECTIDz * * 1 * +convert(varchar(25), tsr121) + 1 1 * as ts，l +convert (varchar(25), otherParty,121) + 1 1 * 1 as otherParty，l +convert (varchar(25), StartTimer 121) + 1 1T1 as StartTime,CcCauser RrCau
6、seFROM 表效果：74983006/2010-03-09 23：59：io.ooofnewid()的妙用一生成测试数据SET ANS工_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI PADDING ONGOCREATE TABLE dbo . test( name varchar(20) NULL, quarter int NULL, profile int NULL, dates smallint NULL, ID int IDENTITY(1,1) NOT NULL, CONSTRAINT PK_test PRIMARY KEY CLUSTE
7、REDID ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY )ON PRIMARYGOSET ANS1_PADD工NG OFFGOSET 工DENT工TY_工NSERT dbo.test ONINSERTdbo,test(name,quarter,profile,dates,ID)VALUES(N1al 1,1000, 421, 1)INSERTdbotest(name,q
8、uarter,profile,dates,ID)VALUES(N12000, 421, 2)INSERTINSERTdbo,test (name,quarter,profile,dates,ID)VALUES(N1AA， 3,4000, 421, 3)INSERT dbo.test (namezquarter,profile,dates,ID)VALUES(N1a, 4, 5000, 421, 4)INSERTdbo,test(name,quarter,profile,dates,ID)VALUES(N1b.，1,3000, 421, 5)INSERTdbotest(name,quarter,
9、profile,dates,ID)VALUES(N1b.，2,3500, 421, 6)INSERTdbo,test(name,quarter,profile,dates,ID)VALUES(N1bl 3,4200, 421, 7)INSERTdbo,test(name,quarter,profile,datestID)VALUES(Nfb.，4,5500, 421, 8)INSERTdbotest(name,quarter,profile,dates,ID)VALUES(N11,1000, 421, 9)SET 工DENT工TY_工NSERTdbo.test OFF一从数据库中随机取出N条记
10、录的方法:newid ()select top 5 * from testselect top 5 * fromtest order by newid():d110004211Aa220004212M340004213a450004214b130004215dates | IDname quarter profile12name |quarterprofiledatesIDJ_ Aa2200042122_a450004214_3_b342004217_4_b23500421_6J_a110004211534(图：效果图)查询时区分大小写生成测试数据SET ANS1_NULLS ONGOSET
11、QUOTED_IDENTIFIER ONGOSET ANS工_PADD1NG ONGOCREATE TABLE dbo . test(name varchar(20) NULL,quarter int NULL,profile int NULL,dates smallint NULL,ID int IDENTITY(1,1) NOT NULL,CONSTRAINT PK test PRIMARY KEY CLUSTEREDID ASCOFOF)WITH (PAD INDEX = OFF, STATISTICS NORECOMPUTE = OFF, IGNORE DUP KEYF, ALLOW
12、ROW LOCKS = ON, ALLOW PAGE LOCKS = ON) ON PRIMARY )ON PRIMARYGOSET ANSI PADDING OFFGOSET IDENTITY INSERTSET IDENTITY INSERTdbo.test ONINSERTdbo,test(name,quarter,profile,dates,ID)VALUES(N11000, 421, 1)INSERTINSERTdbo,test(name,quarter,profile,dates,ID)VALUES(N12000, 421, 2)INSERTINSERTdbotest (name,
13、quarter,profile,dates,ID)VALUES(N1AAl 3,4000, 421, 3)INSERTdbo,test(name,quarter,profile,dates,ID)VALUES(N1Al 4,5000, 421, 4)INSERTdbotest(name,quarter,profile,dates,ID)VALUES(N，bl 1,3000, 421, 5)INSERTdbotest(name,quarter,profile,dates,ID)VALUES(N，bl 2,3500, 421, 6)INSERTdbotest(name,quarter,profil
14、e,dates,ID)VALUES(N，bl 3,4200, 421, 7)INSERTdbotest(name,quarter,profile,dates,ID)VALUES(N，b， 4,5500, 421, 8)INSERTdbo,test(name,quarter,profile,dates,ID)VALUES(NfA, 1,1000, 421, 9)SET IDENTITY INSERTdbo.test OFF-sql server 查询区分大小写 collate Chinese_PRC_CS_AS_WSselect * from test where name = , A1 col
15、late Chinese_PRC_CS_AIascii(A)select * from test where ascii(name)（图：效果图）SQL换行SQL的换行制表符CHAR (9)换行符CHAR(IO)回车 CHAR (13)*/PRINT TTest!+CHAR(13)+1Name1PRINT Test +CHAR (10) 4-1 Age 1PRINT Test,+CHAR(9)+,Tel,以文本格式显示结果SELECT AAA+ CHAR(10)+BBB-AAA BBBSELECT AAA+ CHAR(13)+,BBB,-AAA BBBSELECT 1 AAA1 + CHAR
16、(10) + CHAR (13) + ，BBBAAA BBBTRUNCATE TABLE Table Name下面是对Truncate语句在MSSQLServer2000中用法和原理的说明：Truncate是SQL中的一个删除数据表内容的语句，用法是：Truncate table表名 速度快，而且效率高，因为：TRUNCATE TABLE在功能上与不带 WHERE子句的DELETE语句相同：二者均删除表 中的全部行。但TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源 少。DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TA BL
17、E通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。TRUNCATE TABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标 识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DELETEo如果要删除 表定义及其数据，请使用DROP TABLE语句。对于由FOREIGN KEY约束引用的表，不能使用TRUNCATE TABLE,而应使用不带 WHERE子句的DELETE语句。由于TRUNCATE TABLE不记录在日志中，所以它不能 激活触发器。TRUNCATE TABLE不能用于参与了索引视图的表。其它一查看内存状态dbcc memorys
18、tatus一查看哪个引起的阻塞，blkEXEC sp_who active一查看锁住了那个资源id, objidEXEC sp_lock一当前查询分析器SPID一查询分析器的状态栏中可以看到一比如sa(57),这就表示当前查询分析器SP工D为57,这样在使用profile的时候就可以指定当前窗体 进行监控一获取脚本的执行时间declare timediff datetime select timediff=getdate() select * from tablename print T1 耗时：+ convert (varchar (10 ) , datediff (ms, timedif
19、f, getdate ()(QI, Q2, Q3, Q4) ) as unpvtsql 替换字符串 substring replace例子i ：update tbPersonallnfo set TrueName = replace(TrueName,substring(TrueName,2, 4), * * 1 ) where ID = 1例子2 ：update tbPersonallnfo set Mobile = replace(Mobile,substring(Mobile,4Z11), * * *1) where ID = 1一例子3：update tbPersonallnfo se
20、t Email = replace(Email,1chinamobile1f *) where ID = 1SQL查询一个表内相同纪录having如果一个id可以区分的话，可以这么写select * from 表 where ID in (select ID from 表 group by ID having sum (1) 1)如果儿个ID才能区分的话，可以这么写select * from 表 where ID1+ID2+ID3 in(select ID1+ID2+ID3 from 表 group by ID1,ID2,ID3 having sum(1)1)其他回答：数据表是zy_bho,想
21、找出ZYH字段名相同的记录一方法1：SELECT *FROM zy_bho a WHERE EXISTS(SELECT 1 FROM zy_bho WHERE PK a.PK AND ZYH = a.ZYH)一方法2：select a.* from zy_bho a j oin zy_bho bon (a.pkb.pk and a.zyhb.zyh)一方法3：select * from zy_bbo where zyh in(select zyh from zy_bbo group by zyh having count(zyh)1)-其中pk是主键或是unique的字段。把多行SQL数据变成
22、一条多列数据，即新增列SelectDeptName=O . OUName,* 9G T =Sum(Case When PersonalGrades9 Then* 9G T =Sum(Case When PersonalGrades9 Then1 Else 0End),18G1=Sum(Case When PersonalGrade=8 Then1 Else 0End),17G4 * =Sum(Case When PersonalGrade7 ANDJobGrade=4 ThenElseEnd),17G3 , =Sum(Case When PersonalGrade=7 ANDJobGrade-
23、3 ThenElseEnd),16G T =Sum(Case When PersonalGrade=6 Then1 Else 0End),15G31=Sum(Case When PersonalGrade=5 ANDJobGrade=3 ThenElseEnd),15G21=Sum(Case When PersonalGrade=5 ANDJobGrade=2 ThenElseEnd),1 4G 1=Sum(Case When PersonalGrade=4 Then1 Else 0End) ry 3G2 , =Sum(Case When PersonalGrade=3 ANDJobGrade
24、-2 ThenElseEnd),13G1, =Sum(Case When PersonalGrade=3 ANDJobGrade=1 ThenElseEnd),y2G f =Sum(Case When PersonalGrade=2 Then1 Else 0End),*1G1=Sum(Case When PersonalGradel Then1 Else 0End) t1 Else 0 End)-1 未定级=Sum (Case When PersonalGrade=NULL Then表复制insert into PhoneChange_Num (IMSI,Num)SELECT IMSI,cou
25、nt(IMEI) as numFROM Test.dbo.PhoneChange group by IMSI order by num desc语法 1： Insert INTO table(fieldi,field2,.) values(valuei,value2v.)语法 2： Insert into Table2(fieldi,field2,.)select valuei,value2?. from Tablei (要求目标 表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Tablei的字段 外，还可以插入常量。)语法 3- SELECT valei, val
26、ue2 into Table2 from Tablei (要求目标表 Table2 不存在， 因为在插入时会自动创建表Table2,并将Tablei中指定字段数据复制到Table2中。)语法4：使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】， 那么在大数据表的复制就会有问题？因为复制到一定程度就不再动了，内存爆了？它也没有写 入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的，你刷新一下mdf文件就知道了。利用带关联子查询Update语句更新数据一方法1:Update Tablel set c = (select c from Table2 where a
27、= Tablel.a) where c is n ull一方法2：update Aset newqiantity=B.qiantityfrom A,Bwhere A.bnum=B.bnum一方法3：update(select A.bnum ,A.newqiantity,B.qiantity from A left join B on A.bnum=B. bnum) AS C set C.newqiantity = C.qiantity where C.bnum =XX连接远程服务器一方法1:select * from openrowset(1SQLOLEDB,server=192.168.0.
28、67;uid=sa;pwd=passwor d1, 1 SELECT * FROM BCM2.dbo.tbAppl1)一方法2：select * from openrowset(1SQLOLEDB ,T;1sa1;1 password1r T SELECr)Date和Time样式不带世纪数位(yy) (i)带世纪数位 (yyyy)标准输入/输出(3)0 或 100(1,2)默认mon dd yyyy hh:miAM (或 PM)i101美国mm/dd/yyyy2102ANSI3103英国/法国dd/mm/yyyy4104德国5105意大利dd-mm-yy6106(1)dd mon yy7107
29、(1)mon dd, yy8108hh:mi:ss9 或 109(1,2)默认设置十毫秒mon dd yyyy hh:mi:ss:mmmAM (或PM)10110美国mm-dd-yy11111日本yy/mm/dd12112ISOyymmddyyyymmdd13 或 113(1,2)欧洲默认设置+毫 秒dd mon yyyy hh:mi:ss:mmm(24h)14114hh:mi:ss:mmm(24h)20 或 120 (2)ODBC规范yyyy-mm-dd hh:mi:ss(24h)21或121ODBC规范（带毫秒）yyyy-mm-dd hh:mi:ss.mmm(24h)126 (4)ISO8
30、601yyyy- mm-ddThh:mi:ss.mmm (无空格)127(6, 7)带时区Z的 ISO8601oyyyy-mm-ddThh:mi:ss.mmmZ(无空格)130(1,2)回历(5)dd mon yyyy hh:mi:ss:mmmAM131回历(5)dd/mm/yy hh:mi:ss:mmmAM一语句及查询结果:SELECT CONVERT(varchar(100)z GETDATE(),0): 05 16 2006 10:57AMSELECT CONVERT(varchar(100), GETDATE(),1): 05/16/06SELECT CONVERT(varchar(1
31、00), GETDATE(),2): SELECT CONVERT(varchar(100)z GETDATE(),3): 16/05/06SELECT CONVERT(varchar(100)z GETDATE(),4): SELECT CONVERT(varchar(100)z GETDATE()z5): 16-05-06SELECT CONVERT(varchar(100), GETDATE()z6): 16 05 06SELECT CONVERT (varchar (100) , GETDATE。，7): 05 16, 06SELECT CONVERT(varchar(100), GE
32、TDATE(),8): 10:57:46SELECT CONVERT(varchar(100), GETDATE(),9): 05 16 2006 10:57:46:827AMSELECT CONVERT(varchar(100), GETDATE(),10): 05-16-06SELECT CONVERT (varchar (100) , GETDATE。，11): 06/05/16SELECT CONVERT(varchar(100), GETDATE()z12): 060516SELECT CONVERT(varchar(100), GETDATE(),13): 16 05 2006 1
33、0:57:46:937SELECT CONVERT(varchar(100), GETDATE(),14): 10:57:46:967SELECT CONVERT(varchar(100), GETDATE(),20): 2006-05-16 10:57:47SELECT CONVERT(varchar(100)z GETDATE()A21): 2006-05-16 10:57:47.157SELECT CONVERT(varchar(100), GETDATE()z22): 05/16/06 10:57:47 AMSELECT CONVERT(varchar(100), GETDATE()z
34、23): 2006-05-16SELECT CONVERT(varchar(100), GETDATE(),24): 10:57:47SELECT CONVERT(varchar(100), GETDATE(),25): 2006-05-16 10:57:47.250SELECT CONVERT(varchar(100), GETDATE(),100): 05 16 2006 10:57AMSELECT CONVERT(varchar(100)z GETDATE(),101): 05/16/2006SELECT CONVERT(varchar(100)z GETDATE(),102): SEL
35、ECT CONVERT(varchar(100), GETDATE()z103): 16/05/2006SELECT CONVERT(varchar(100), GETDATE(),104): SELECT CONVERT(varchar(100), GETDATE(),105): 16-05-2006SELECT CONVERT(varchar(100), GETDATE(),106): 16 05 2006SELECT CONVERT(varchar(100), GETDATE(),107): 05 16, 2006SELECT CONVERT (varchar (100) , GETDA
36、TE。，108): 10:57:49SELECT CONVERT(varchar(100), GETDATE()z109): 05 16 2006 10:57:49:437AMSELECT CONVERT(varchar(100)z GETDATE(),110): 05-16-2006SELECT CONVERT(varchar(100)z GETDATE(),111): 2006/05/16SELECT CONVERT(varchar(100),SELECT CONVERT(varchar(100),GETDATE()r 112): 20060516SELECT CONVERT(varcha
37、r(100),GETDATE()z 113): 16 05 2006 10:57:49:513SELECT CONVERT(varchar(100),GETDATE。，114) : 10:57:49:547SELECT CONVERT(varchar(100), GETDATE(),120): 2006-05-16 10:57:49SELECT CONVERT(varchar(100),GETDATE(),121): 2006对上面进行动态生成字符串:declare sqll nvarchar(200), sql2 nvarchar(200)declare count nvarchar(100
38、);set sqll = SELECT CONVERT(varchar(100), GETDATE()z 0)1set sql2 = ，SELECT count = CONVERT(varchar(100), GETDATE()z 0) exec sp_executesql sql2,Ncount nvarchar(50) out *,count out print sqll +： + count -SQL Server仅保证往返转换(即从原始数据类型进行转换后又返回原始数据类型的转换)在各版本 间产生相同值。DECLARE myval decimal (5, 2)SET myval = 19
39、3.57SELECT CAST(CAST(myval AS varbinary(20) AS decimal(10,5)- Or, using CONVERTSELECT CONVERT(decimal(10z 5), CONVERT(varbinary(20), myval)一输出 193.57000一输出 193.57000一bigint数据类型的字段截取(其它类型也一样)select substring (CONVERT (varchar (15),字段名)，11, 9) from 表名select substring (cast (字段名 as varchar (50) ,6,9) f
40、rom 表名SQL中的相除-SQL中的相除SELECT CASE WHEN 工SNULL(A+B,0)0 THENLTR 工 M (CONVERT (DEC (18,2) ,A*100.0/(A+B) ) +，%, ELSE 1 1 END AS ，百分数，FROM TB一百分比的不同格式 selectLTRIM (CONVERT (DEC (18,2) , 42*100.0/96) ) + 1 % 1 AS 百分数 string1 , -DEC=decimal CONVERT (decimal (10,2) z 42*100.0/96) AS 百分数 dec, -100 与 100.0 是不
41、一样的 CONVERT (decimal (10,2) ,42*100/96) AS ，没有保留至U小数点，-from Tborder by 百分数 dec desc 43.75%43.7543.00一方法二：Select (Convert (varchar (50) z Round (42 *100.0/96,3) J+1%1) as 百分比-from A -43.750000%四舍五入/*ROUND ( numeric_expression , length ,function ) function 必须为 tinyint smallint 或 into如果省略function或其值为0
42、(默认值)，则将舍入numeric_expressiono 如果指定了 0以外的值，则将截断numeric_expression0 */SELECT ROUND (150.45648, 2);一保留小数点后两位，需要四舍五入SELECT ROUND (150.45648, 2, 0保留小数点后两位，。为默认值，表示进行四舍五入SELECT ROUND (150.45648, 2, 1); 一保留小数点后两位，不需要四舍五入，这里除。以外都是有同样的效果 SELECT ROUND (150.45648, 2r 2);一保留小数点后两位，不需要四舍五入，这里除0以外都是有同 样的效果 -150.4
43、6000 -150.45000 -150.45000 -150.45000对字段出现NULL值的处理判断某些字段是否为空-caseselect case when ，字段名is null then f N1 else convert (varchar (20) r * ,) end as r NewName1select case when null is null then *N * else convert(varchar(20),null) end as 1NewName1- SQL Server 2005： coalesceselect coalsc字符串类型字段 I，N，)as * NewName 1select coal sc (convert (varchar (20) J H字符串类型字段，)，N，)as * NewName select coalesce (convert(varchar(20),null),