SQL Server 常用SQL总结

1. order by NAME collate Chinese_PRC_Stroke_CS_AS_KS_WS  
2.   
3. /*sqlserver分组不能以text,ntext,image类型的字段作为分组依据*/    
4.     
5. --强制查询使用索引：     
6. select id from table_name  with(index(索引名)) where num=@num    
7.     
8. --全文检索(name like '%abc%')(substring(cal_name ,1,3)='abc')     
9. select id from t where charindex('abc',cal_name ) > 0     
10.     
11. --查看对象的定义     
12. sp_helptext name    
13.     
14. --中文汉字笔画从少到多排列     
15. select * from table_name order by cal_name collate chinese_prc_stroke_ci_as     
16.     
17. --任意前10条记录     
18. select top 10 * from table_name order by newid()    
19.     
20. --添加序号时必须有into语句     
21. select identity(int,1,1) id, * into #table_name  from table_name    
22.     
23. --钱10~20行数据     
24. select top 10 * from (select top 20 * from #table_name order by id asc) as new_tab_name order by id desc    
25.     
26. --随机取出10条数据   
27. select top 10 * from table_name order by newid()  
28.   
29. --將字串值重複指定的次數。     
30. SELECT REPLICATE ( 'K' ,5 ) --KKKKK   
31.   
32.     
33. --统计有多少个汉字     
34. select datalength('kk中国123')-len('kk中国123')    
35.     
36. select nullif('kk','kk') --相等为null，否则取第一个    
37. select isnull(null,'kk') --第一个值不为空取第一个,否则为第二个     
38. select coalesce(null,null,'kk','中国') --返回第一个非空值     
39.   
40. --小数取整   
41. select CEILING(12.7)    --[13];取大于12.7的最小整数   
42. select CEILING(12.2)    --[13];取大于12.2的最小整数   
43. select FLOOR(12.7)  --[12];取小于12.7的最大整数   
44. select FLOOR(12.2)  --[12];取小于12.2的最大整数   
45. select round(12.77,0)   --[13.00];四舍五入,0位小数   
46. select round(12.24,1)   --[12.20];四舍五入,1位小数   
47.   
48. --按位置替换   
49. select STUFF ( 'ABCDEFG' , 2 , 0 ,'-' ) --[A-BCDEFG];第二个位置,取字符长度为0,替换为-   
50. select STUFF ( 'ABCDEFG' , 2 , 1 ,'b' ) --[AbCDEFG];第二个位置,取字符长度为1,替换为b   
51. select STUFF ( 'ABCDEFG' , 2 , 2 ,'*' ) --[A*DEFG];第二个位置,取字符长度为2,替换为*   
52.   
53. --按相同字符替换   
54. select REPLACE('ABCDEFG','B','b')   --[AbCDEFG];将所有B对应替换为b   
55. select REPLACE('ABCDEFG-Bc','BC','*')   --[A*DEFG-*];将所有BC对应替换为一个*,不区分大小写   
56.   
57. --判断某字符存在   
58. select CHARINDEX('456','123456789')  
59. select CHARINDEX('1','235694526')   --[0];判断1是否存在   
60. select CHARINDEX('1','12314510215985')  --[1];1出现的位置   
61. select CHARINDEX('1','12314510215985',8)    --[10];从第八个字符查找,1在字符串中出现的位置   
62.   
63. --起始位置,支持匹配表达式   
64. select patindex('456', '123456789') --[0];   
65. select patindex('456%', '123456789')    --[0];   
66. select patindex('%456%', '123456789')   --[4];   
67. select patindex('12%', '123456789') --[1];   
68. select patindex('__3%', '123456789')    --[1];   
69. select patindex('%[js]%','hsdjgjsrgsdgfjt')--返回j或s中第一个字符出现的位置   
70. select patindex('%[^js]%','ssjjgjsrgsdgfjt')--返回不是j和s外第一个字符出现的位置   
71.   
72. print '开始执行输出'    
73. go    
74. waitfor delay '00:00:05'  --5秒后执行     
75. print '延时执行输出'    
76. go    
77. waitfor time '12:00:00'  --12点执行     
78. print '定时执行输出'    
79. go    
80.     
81.     
82. --远程连接     
83. select * from openrowset('sqloledb','servername';'username';'password',dbname.dbo.tablename)     
84. select * from opendatasource('sqloledb','data source=ip(or servername);user id=username;password=password').dbname.dbo.tablename     
85.     
86.     
87.     
88. --当前日期加一个月     
89. select convert(varchar(10),dateadd(m,1,getdate()),120)    
90. select dateadd(m,1,getdate())    
91.   
92.     
93. --取得当前年月的最后一天     
94. select dateadd(d,-1,convert(datetime,convert(varchar(7),dateadd(m,1,getdate()),120) + '-01'))     
95.   
96. --取得当前年月第一日     
97. select convert(datetime,convert(varchar(7),getdate(),120)+ '-01',120)    
98.     
99. --取得今年第一个月     
100. select convert(varchar(4),getdate(),120)+'-01'    
101.     
102. --取得当前年月的前一个月     
103. select convert(varchar(7),dateadd(m,-1,getdate()),120)    
104.     
105. --当前季度的第一天   
106. select dateadd(quarter, datediff(quarter,0,getdate()), 0)  
107.   
108. --本年的最后一天   
109. select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate())+1, 0))  
110.   
111. --判断是否闰年   
112. select case day(dateadd(mm, 2, dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate()), 0)))) when 28 then '平年' else '闰' end    
113. select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01')) when 28 then '平年' else' 闰年' end    
114.   
115.   
116. /*查看对象或表是否存在*/    
117.     
118. --查看与表相关的所有其他对象     
119. select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%tb%'    
120.     
121. --查看当前数据库所有表       
122. select name from sysobjects where xtype='u' and status>=0      
123. select * from information_schema.tables where table_type='base table'    
124.     
125. --查看指定表的所有数据列       
126. select name from syscolumns where id=object_id('tablename')      
127. select column_name from information_schema.columns where table_name='tablename'    
128.       
129.     
130. --查看当前数据库所有视图     
131. select * from information_schema.views --(有定义 )     
132. select * from dbo.sysobjects where  objectproperty(id, n'isview') = 1    
133. select * from dbo.sysobjects where  type='v'    
134.     
135. -- 判断数据库是否存在     
136. if  exists(select 1 from master.dbo.sysdatabases where name=n'ghhg')    
137. select * from master..sysdatabases --数据库     
138.     
139. -- 判断临时表是否存在     
140. if object_id(n'tempdb..[#temp_table]') is not null     
141. if exists (select * from tempdb.dbo.sysobjects where id = object_id(n'[tempdb].[dbo].[#temp_table]'))    
142.     
143. -- 判断作业是否存在     
144. if  exists (select job_id from msdb.dbo.sysjobs_view where name ='jobname')    

order by NAME collate Chinese_PRC_Stroke_CS_AS_KS_WS

/*sqlserver分组不能以text,ntext,image类型的字段作为分组依据*/  
  
--强制查询使用索引：  
select id from table_name  with(index(索引名)) where num=@num  
  
--全文检索(name like '%abc%')(substring(cal_name ,1,3)='abc')  
select id from t where charindex('abc',cal_name ) > 0   
  
--查看对象的定义  
sp_helptext name  
  
--中文汉字笔画从少到多排列  
select * from table_name order by cal_name collate chinese_prc_stroke_ci_as   
  
--任意前10条记录  
select top 10 * from table_name order by newid()  
  
--添加序号时必须有into语句  
select identity(int,1,1) id, * into #table_name  from table_name  
  
--钱10~20行数据  
select top 10 * from (select top 20 * from #table_name order by id asc) as new_tab_name order by id desc  
  
--随机取出10条数据
select top 10 * from table_name order by newid()

--將字串值重複指定的次數。  
SELECT REPLICATE ( 'K' ,5 ) --KKKKK

  
--统计有多少个汉字  
select datalength('kk中国123')-len('kk中国123')  
  
select nullif('kk','kk') --相等为null，否则取第一个 
select isnull(null,'kk') --第一个值不为空取第一个,否则为第二个  
select coalesce(null,null,'kk','中国') --返回第一个非空值  

--小数取整
select CEILING(12.7)	--[13];取大于12.7的最小整数
select CEILING(12.2)	--[13];取大于12.2的最小整数
select FLOOR(12.7)	--[12];取小于12.7的最大整数
select FLOOR(12.2)	--[12];取小于12.2的最大整数
select round(12.77,0)	--[13.00];四舍五入,0位小数
select round(12.24,1)	--[12.20];四舍五入,1位小数

--按位置替换
select STUFF ( 'ABCDEFG' , 2 , 0 ,'-' )	--[A-BCDEFG];第二个位置,取字符长度为0,替换为-
select STUFF ( 'ABCDEFG' , 2 , 1 ,'b' )	--[AbCDEFG];第二个位置,取字符长度为1,替换为b
select STUFF ( 'ABCDEFG' , 2 , 2 ,'*' )	--[A*DEFG];第二个位置,取字符长度为2,替换为*

--按相同字符替换
select REPLACE('ABCDEFG','B','b')	--[AbCDEFG];将所有B对应替换为b
select REPLACE('ABCDEFG-Bc','BC','*')	--[A*DEFG-*];将所有BC对应替换为一个*,不区分大小写

--判断某字符存在
select CHARINDEX('456','123456789')
select CHARINDEX('1','235694526')	--[0];判断1是否存在
select CHARINDEX('1','12314510215985')	--[1];1出现的位置
select CHARINDEX('1','12314510215985',8)	--[10];从第八个字符查找,1在字符串中出现的位置

--起始位置,支持匹配表达式
select patindex('456', '123456789')	--[0];
select patindex('456%', '123456789')	--[0];
select patindex('%456%', '123456789')	--[4];
select patindex('12%', '123456789')	--[1];
select patindex('__3%', '123456789')	--[1];
select patindex('%[js]%','hsdjgjsrgsdgfjt')--返回j或s中第一个字符出现的位置
select patindex('%[^js]%','ssjjgjsrgsdgfjt')--返回不是j和s外第一个字符出现的位置

print '开始执行输出'  
go  
waitfor delay '00:00:05'  --5秒后执行  
print '延时执行输出'  
go  
waitfor time '12:00:00'  --12点执行  
print '定时执行输出'  
go  
  
  
--远程连接  
select * from openrowset('sqloledb','servername';'username';'password',dbname.dbo.tablename)   
select * from opendatasource('sqloledb','data source=ip(or servername);user id=username;password=password').dbname.dbo.tablename   
  
  
  
--当前日期加一个月  
select convert(varchar(10),dateadd(m,1,getdate()),120)  
select dateadd(m,1,getdate())  

  
--取得当前年月的最后一天  
select dateadd(d,-1,convert(datetime,convert(varchar(7),dateadd(m,1,getdate()),120) + '-01'))   

--取得当前年月第一日  
select convert(datetime,convert(varchar(7),getdate(),120)+ '-01',120)  
  
--取得今年第一个月  
select convert(varchar(4),getdate(),120)+'-01'  
  
--取得当前年月的前一个月  
select convert(varchar(7),dateadd(m,-1,getdate()),120)  
  
--当前季度的第一天
select dateadd(quarter, datediff(quarter,0,getdate()), 0)

--本年的最后一天
select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate())+1, 0))

--判断是否闰年
select case day(dateadd(mm, 2, dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate()), 0)))) when 28 then '平年' else '闰' end  
select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01')) when 28 then '平年' else' 闰年' end  


/*查看对象或表是否存在*/  
  
--查看与表相关的所有其他对象  
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%tb%'  
  
--查看当前数据库所有表    
select name from sysobjects where xtype='u' and status>=0    
select * from information_schema.tables where table_type='base table'  
  
--查看指定表的所有数据列    
select name from syscolumns where id=object_id('tablename')    
select column_name from information_schema.columns where table_name='tablename'  
    
  
--查看当前数据库所有视图  
select * from information_schema.views --(有定义 )  
select * from dbo.sysobjects where  objectproperty(id, n'isview') = 1  
select * from dbo.sysobjects where  type='v'  
  
-- 判断数据库是否存在  
if  exists(select 1 from master.dbo.sysdatabases where name=n'ghhg')  
select * from master..sysdatabases --数据库  
  
-- 判断临时表是否存在  
if object_id(n'tempdb..[#temp_table]') is not null   
if exists (select * from tempdb.dbo.sysobjects where id = object_id(n'[tempdb].[dbo].[#temp_table]'))  
  
-- 判断作业是否存在  
if  exists (select job_id from msdb.dbo.sysjobs_view where name ='jobname')  

[sql] view plain copy print ?

1. /*  
2. DBCC FREEPROCCACHE --清除执行计划缓存   
3. DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS --清除缓冲区   
4.   
5. set statistics profile on  
6. set statistics io on  
7. set statistics time on  
8.   
9. set statistics profile off  
10. set statistics io off  
11. set statistics time off  
12. */  

/*
DBCC FREEPROCCACHE --清除执行计划缓存
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS --清除缓冲区

set statistics profile on
set statistics io on
set statistics time on

set statistics profile off
set statistics io off
set statistics time off
*/

[sql] view plain copy print ?

1. --已知表名查数据库名   
2. declare @Table table(DB sysname,TabName sysname)  
3. insert @Table  
4. exec sp_msforeachdb 'select ''?'' as DB,Name as 表名 from [?]..sysobjects where type=''U'' and Name =''fjda'''  
5.   
6. select * from @Table  

--已知表名查数据库名
declare @Table table(DB sysname,TabName sysname)
insert @Table
exec sp_msforeachdb 'select ''?'' as DB,Name as 表名 from [?]..sysobjects where type=''U'' and Name =''fjda'''

select * from @Table

原作者的博客名：kk185800961