常用SQL语句实例大全（含过滤及删除重复数据、导入导出数据等）

一、过滤重复数据

1、完全重复的记录

1. /* 功能：指定字段完全重复 */
2. select distinct 字段1,字段2,字段3 from 数据表

2、部分关键字段重复的记录

1. /*数据结构：角色档案（角色编码，角色，角色分类编码）
2. 功　　能：取出指定字段（角色分类编码）为关键字的无重复数据，重复的取第一条
3. 说　　明：重复记录取最后一条，只需要把min改成max即可
4. */
5. select * from 角色档案 t where 角色编码 in (select min(角色编码)  from 角色档案 t1 group by t1.角色分类编码)

 

二、删除重复记录

数据库的使用过程中由于程序方面的问题有时候会碰到重复数据，重复数据导致了数据库部分设置不能正确设置，本例举出删除它的办法。

方法1：

1. declare @max integer,@id integer
2. declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
3. open cur_rows
4. fetch cur_rows into @id,@max
5. while @@fetch_status=0
6. begin
7. select @max = @max -1
8. set rowcount @max
9. delete from 表名 where 主字段 = @id
10. fetch cur_rows into @id,@max
11. end
12. close cur_rows
13. set rowcount 0

方法2：

有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。

1、对于第一种重复，比较容易解决，使用

1. select distinct * from tableName

就可以得到无重复记录的结果集。

如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除

1. select distinct * into #Tmp from tableName
2. drop table tableName
3. select * into tableName from #Tmp
4. drop table #Tmp

发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。

2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下

假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集

1. select identity(int,1,1) as autoID, * into #Tmp from tableName
2. select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
3. select * from #Tmp where autoID in(select autoID from #tmp2)

最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）

 

三、导入导出语句大全：

1. SELECT * FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, 
2. ’Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions 
3. /*动态文件名 
4. declare @fn varchar(20),@s varchar(1000) 
5. set @fn = ’c:/test.xls’ 
6. set @s =’’’Microsoft.Jet.OLEDB.4.0’’, 
7. ’’Data Source="’+@fn+’";User ID=Admin;Password=;Extended properties=Excel 5.0’’’ 
8. set @s = ’SELECT * FROM OpenDataSource (’+@s+’)...sheet1$’ 
9. exec(@s) 
10. */ 
11. SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+’　’ 转换后的别名 
12. FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, 
13. ’Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions 
14. /********************** EXCEL导到远程SQL 
15. insert OPENDATASOURCE( 
16. ’SQLOLEDB’, 
17. ’Data Source=远程ip;User ID=sa;Password=密码’ 
18. ).库名.dbo.表名 (列名1,列名2) 
19. SELECT 列名1,列名2 
20. FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, 
21. ’Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions 
22. /** 导入文本文件 
23. EXEC master..xp_cmdshell ’bcp dbname..tablename in c:/DT.txt -c -Sservername -Usa -Ppassword’ 
24. /** 导出文本文件 
25. EXEC master..xp_cmdshell ’bcp dbname..tablename out c:/DT.txt -c -Sservername -Usa -Ppassword’
26. 或 
27. EXEC master..xp_cmdshell ’bcp "Select * from dbname..tablename" queryout c:/DT.txt -c -Sservername -Usa -Ppassword’ 
28. 导出到TXT文本，用逗号分开 
29. exec master..xp_cmdshell ’bcp "库名..表名" out"d:/tt.txt" -c -t ,-U sa -P password’ 
30. BULK INSERT 库名..表名 
31. FROM ’c:/test.txt’ 
32. WITH ( 
33. FIELDTERMINATOR = ’;’, 
34. ROWTERMINATOR = ’/n’ 
35. ) 
36. --/* dBase IV文件 
37. select * from  
38. OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’ 
39. ,’dBase IV;HDR=NO;IMEX=2;DATABASE=C:/’,’select * from [客户资料4.dbf]’) 
40. --*/ 
41. --/* dBase III文件 
42. select * from  
43. OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’ 
44. ,’dBase III;HDR=NO;IMEX=2;DATABASE=C:/’,’select * from [客户资料3.dbf]’) 
45. --*/ 
46. --/* FoxPro 数据库 
47. select * from openrowset(’MSDASQL’, 
48. ’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/’, 
49. ’select * from [aa.DBF]’) 
50. --*/ 
51. /**************导入DBF文件****************/ 
52. select * from openrowset(’MSDASQL’, 
53. ’Driver=Microsoft Visual FoxPro Driver; 
54. SourceDB=e:/VFP98/data; 
55. SourceType=DBF’, 
56. ’select * from customer where country != "USA" order by country’) 
57. go 
58. /***************** 导出到DBF ***************/ 
59. 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句 
60. insert into openrowset(’MSDASQL’, 
61. ’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/’, 
62. ’select * from [aa.DBF]’) 
63. select * from 表 
64. 说明: 
65. SourceDB=c:/ 指定foxpro表所在的文件夹 
66. aa.DBF 指定foxpro表的文件名. 
67. /*************导出到Access********************/ 
68. insert into openrowset(’Microsoft.Jet.OLEDB.4.0’,  
69. ’x:/A.mdb’;’admin’;’’,A表) select * from 数据库名..B表 
70. /*************导入Access********************/ 
71. insert into B表 selet * from openrowset(’Microsoft.Jet.OLEDB.4.0’,  
72. ’x:/A.mdb’;’admin’;’’,A表) 
73. /*文件名为参数*/
74. declare @fname varchar(20) 
75. set @fname = ’d:/test.mdb’ 
76. exec(’SELECT a.* FROM opendatasource(’’Microsoft.Jet.OLEDB.4.0’’, 
77. ’’’+@fname+’’’;’’admin’’;’’’’, topics) as a ’) 
78. SELECT *  
79. FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, 
80. ’Data Source="f:/northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;’) --产品 
81. /********************** 导入 xml　文件********************/
82. DECLARE @idoc int 
83. DECLARE @doc varchar(1000) 
84. --sample XML document 
85. SET @doc =’ 
86. <root> 
87. <Customer cid= "C1" name="Janine" city="Issaquah"> 
88. <Order oid="O1" date="1/20/1996" amount="3.5" /> 
89. <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied 
90. </Order> 
91. </Customer> 
92. <Customer cid="C2" name="Ursula" city="Oelde" > 
93. <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue  
94. white red"> 
95. <Urgency>Important</Urgency> 
96. Happy Customer. 
97. </Order> 
98. <Order oid="O4" date="1/20/1996" amount="10000"/> 
99. </Customer> 
100. </root> 
101. ’ 
102. -- Create an internal representation of the XML document. 
103. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
104. -- Execute a SELECT statement using OPENXML rowset provider. 
105. SELECT * 
106. FROM OPENXML (@idoc, ’/root/Customer/Order’, 1) 
107. WITH (oid char(5),  
108. amount float,  
109. comment ntext ’text()’) 
110. EXEC sp_xml_removedocument @idoc 
111. /**********************Excel导到Txt****************************************/ 
112. '想用  
113. select * into opendatasource(...) from opendatasource(...) 
114. /*实现将一个Excel文件内容导入到一个文本文件 
115. 假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位) 
116. 且银行帐号导出到文本文件后分两部分，前8位和后8位分开。  */
117. /*邹健： 
118. 如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2  然后就可以用下面的语句进行插入 
119. 注意文件名和目录根据你的实际情况进行修改.  */
120. insert into 
121. opendatasource(’MICROSOFT.JET.OLEDB.4.0’ 
122. ,’Text;HDR=Yes;DATABASE=C:/’ 
123. )...[aa#txt] 
124. --,aa#txt) 
125. --*/ 
126. select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)  
127. from  
128. opendatasource(’MICROSOFT.JET.OLEDB.4.0’ 
129. ,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’ 
130. --,Sheet1$) 
131. )...[Sheet1$] 
132. 如果你想直接插入并生成文本文件,就要用bcp 
133. declare @sql varchar(8000),@tbname varchar(50) 
134. --首先将excel表内容导入到一个全局临时表 
135. select @tbname=’[##temp’+cast(newid() as varchar(40))+’]’ 
136. ,@sql=’select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)  
137. into ’+@tbname+’ from  
138. opendatasource(’’MICROSOFT.JET.OLEDB.4.0’’ 
139. ,’’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’’ 
140. )...[Sheet1$]’ 
141. exec(@sql) 
142. --然后用bcp从全局临时表导出到文本文件 
143. set @sql=’bcp "’+@tbname+’" out"c:/aa.txt" /S"(local)" /P"" /c’ 
144. exec master..xp_cmdshell @sql 
145. --删除临时表 
146. exec(’drop table ’+@tbname) 
147. /********************导整个数据库*********************************************/ 
148. /*用bcp实现的存储过程  */
149. /* 
150. 实现数据导入/导出的存储过程 
151. 根据不同的参数,可以实现导入/导出整个数据库/单个表 
152. 调用示例: 
153. --导出调用示例 
154. ----导出单个表 
155. exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:/zj.txt’,1 
156. ----导出整个数据库 
157. exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:/docman’,1 
158. --导入调用示例 
159. ----导入单个表 
160. exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:/zj.txt’,0 
161. ----导入整个数据库 
162. exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:/docman’,0 
163. */ 
164. if exists(select 1 from sysobjects where name=’File2Table’ and objectproperty(id,’IsProcedure’)=1) 
165. drop procedure File2Table 
166. go 
167. create procedure File2Table 
168. @servername varchar(200) --服务器名 
169. ,@username varchar(200) --用户名,如果用NT验证方式,则为空’’ 
170. ,@password varchar(200) --密码 
171. ,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表 
172. ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这参数是文件存放路径,文件名自动用表名.txt 
173. ,@isout bit --1为导出,0为导入 
174. as 
175. declare @sql varchar(8000) 
176. if @tbname like ’%.%.%’ --如果指定了表名,则直接导出单个表 
177. begin 
178. set @sql=’bcp ’+@tbname 
179. +case when @isout=1 then ’ out ’ else ’ in ’ end 
180. +’ "’+@filename+’" /w’ 
181. +’ /S ’+@servername 
182. +case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end 
183. +’ /P ’+isnull(@password,’’) 
184. exec master..xp_cmdshell @sql
185. end 
186. else 
187. begin --导出整个数据库,定义游标,取出所有的用户表 
188. declare @m_tbname varchar(250) 
189. if right(@filename,1)<>’/’ set @filename=@filename+’/’ 
190. set @m_tbname=’declare #tb cursor for select name from ’+@tbname+’..sysobjects where xtype=’’U’’’ 
191. exec(@m_tbname) 
192. open #tb 
193. fetch next from #tb into @m_tbname 
194. while @@fetch_status=0 
195. begin 
196. set @sql=’bcp ’+@tbname+’..’+@m_tbname 
197. +case when @isout=1 then ’ out ’ else ’ in ’ end 
198. +’ "’+@filename+@m_tbname+’.txt " /w’ 
199. +’ /S ’+@servername 
200. +case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end 
201. +’ /P ’+isnull(@password,’’) 
202. exec master..xp_cmdshell @sql 
203. fetch next from #tb into @m_tbname 
204. end 
205. close #tb 
206. deallocate #tb  
207. end 
208. go 
209. /************* Oracle **************/ 
210. EXEC sp_addlinkedserver ’OracleSvr’,  
211. ’Oracle 7.3’,  
212. ’MSDAORA’,  
213. ’ORCLDB’ 
214. GO 
215. delete from openquery(mailser,’select * from yulin’) 
216. select * from openquery(mailser,’select * from yulin’) 
217. update openquery(mailser,’select * from yulin where id=15’)set disorder=555,catago=888 
218. insert into openquery(mailser,’select disorder,catago from yulin’)values(333,777) 
219. /*-----------------------------------------
220. 补充： 
221. 对于用bcp导出,是没有字段名的. 
222. 用openrowset导出,需要事先建好表. 
223. 用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导
224. -----------------------------------------*/

---------------------------------第二部份、入门与进阶-------------------------------------------

一、基础

1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表
drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
10、说明：几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、left outer join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right outer join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full outer join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

二、提升

1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"/data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

三、技巧

1、1=1，1=2的使用，在SQL语句组合时用的较多

“where 1=1” 是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' +@strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end

我们可以直接写成
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+@strWhere

2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE

3、压缩数据库
dbcc shrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:/dvbbs.bak'
6、修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO

7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

8、说明：更改某个表
exec sp_changeobjectowner 'tablename','dbo'

9、存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO

10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert

 

 

 

--删除测试drop table 表取5以下随机的数字select convert(varchar,ceiling(rand()*5))将数字转换成百分比：select rtrim(cast(2 * 100/10 as decimal(5,2))) + '%'cast(2 * 100/10 as decimal(5,2)) 这个是将2*100/10转换成5位且小数位为2位的浮点小数，11.乘积：declare @s table(id float)insert into @s select 2insert into @s select 3insert into @s select 2.5select exp(sum(log(id))) from @s12.like的用法查出记录中的字符串包含1，2，3，4的记录Create Table TEST(share    varchar(100))Insert TEST Select '1,2,24'Union All Select '2,23,56'Union All Select '6,10,11'Union All Select '3,4,15'Union All Select '6,29,31'GOSelect * From TEST Where  ',' + share + ',' Like '%,[1-4],%'13，按条件分类排序select brand from braorder by case when brand ='飞利浦' then '' else brand end//将brand为飞利浦的排在最上面14.给返回的记录加上一个行数(1)当没有自增的id列时：select bh = identity(int,1,1) ,proname,price into temp from productselect '第' + cast(bh as varchar) + '个' , proname,price from temp(2)当有自增的id列时select bh,name,price from(  SELECT bh=(SELECT COUNT(1) FROM product WHERE id > a.id)+ 1,*  FROM product a ) torder by bh ---SQL2005启用 openrowset/opendatasetexec sp_configure 'show advanced options',1reconfigureexec sp_configure 'Ad Hoc Distributed Queries',1reconfigure-- 启用xp_cmdshell-- 允许配置高级选项EXEC sp_configure 'show advanced options', 1GO-- 重新配置RECONFIGUREGO-- 启用xp_cmdshellEXEC sp_configure 'xp_cmdshell', 1GO--重新配置RECONFIGUREGO--启动远程服务器的MSDTC服务   exec   master..xp_cmdshell   'isql   /S"10.128.34.22"   /U"sa"   /P"123456"   /q"exec   master..xp_cmdshell   ''net   start  msdtc'',no_output"',no_output       --启动本机的MSDTC服务   exec   master..xp_cmdshell   'net   start   msdtc',no_output  ---远程链接：1、创建远程链接服务器，然后进行查询exec sp_addlinkedserver 'HJZX_SYN','','SQLOLEDB','10.128.34.22' exec sp_addlinkedsrvlogin 'HJZX_SYN','false',null,'sa','123456' go select * from HJZX_SYN.数据库名.dbo.表名---删除连接exec   sp_dropserver   'MyLink','droplogins' ---查询select * from sysservers 2、select  * from openrowset('msdasql','driver={sql server};server=10.124.20.10;uid=ncc2008;pwd=ncc2008',hjzx4.dbo.t_p_order) AS a3、select  * from opendatasource('sqloledb','Data Source=10.124.20.10;User ID=ncc2008;Password=ncc2008').hjzx4.dbo.t_p_order 如：alter       database       数据库名       COLLATE       Chinese_PRC_CI_AS     不区分大小写， 而     alter       database       数据库名       COLLATE       Chinese_PRC_CS_AS     使之区分大小写。导入excel数据：select * into # from OPENROWSET('microsoft.jet.oledb.4.0','Excel 5.0;hdr=yes;database=d:/1月安排.xls',准考证信息$)insert   into   表名         select   *   from   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0','Data   Source=f:/Test.xls;User   ID=Admin;Password=;Extended   properties=Excel   8.0')...[Sheet1$] office2007SELECT  * FROM  OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="f:/aa.xls";User ID=admin;Password=;Extended properties=Excel 5.0')...[sheet1$]----bcp:-t列分割符，默认是以制表符(/t)分割-r行分割符，默认是以换行符(/n)分割导入：inEXEC master..xp_cmdshell 'bcp pruduct in d:/wsp.txt -c -t. -r/n'导出：--导出全表数据outEXEC master..xp_cmdshell 'bcp pruduct out d:/wsp.txt -c -Usa -Psa'--导出查询结果queryoutEXEC master..xp_cmdshell 'bcp "select * from pruduct where part_id like ''80%''" queryout d:/wsp.txt -c -t,  -Usa -Psa'--导入文本文档：BULK INSERT osFROM 'c:/d.txt'WITH (    FIELDTERMINATOR = ',',  --列以逗号隔开    ROWTERMINATOR = '/n'    --行以换行符隔开)---osql,执行sql命令exec master..xp_cmdshell 'osql -U sa -P sa -i d:/tt.txt'--用SQL语句备份、还原数据库BACKUP DATABASE test      --这里的test指的是数据库名称   TO disk = 'c:/backup.bak'    --这里指名的数据库路径(backup.bak为备份文件名)   WITH FORMAT,   NAME = 'Full Backup of MyNwind'    --这个是备注，无所谓。。随便写。RESTORE DATABASE jz1    --所被恢复的数据库名称   FROM disk = 'c:/backup.bak     --本地硬盘路径(backup.bak为备份文件名)GO--SQL语句分离、附加：--分离sp_detach_db 'zetian'   --附加EXEC sp_attach_db @dbname = N'zetian',    @filename1 = N'C:/Inetpub/wwwroot/zetian/数据库/zetian.mdf',    @filename2 = N'C:/Inetpub/wwwroot/zetian/数据库/zetian_log.ldf'

--分解字咐declare @a table(A varchar(20),  B varchar(20),   C varchar(20),    D  varchar(20))insert @a select 'a1'  ,'b1',  'c1',   'd1/da'union all select 'a2'  ,'b2'  ,'c2'   ,'d22/da/da22' union all select 'a3'  ,'b3'  ,'c3'   ,'d3' declare @t table( id int identity(1,1),e int)insert @t select top 500 1 from syscolumnsselect a,b,c,substring(d+'/',id,charindex('/',d+'/',id+1)-id) Xfrom @a a,@t bwhere substring('/'+d,id,1)='/'--设置约束CREATE   TABLE   jobs ( min_lvl   int   NOT   NULL  CHECK   (min_lvl   > =   10)     ) 判断该文件是否存在：DECLARE @err INT,@fso INT,@fleExists BIT,@file VARCHAR(100)SET @file='d:/aaa.txt'EXEC @err=sp_OACreate 'Scripting.FileSystemObject',@fso OUTPUTEXEC @err=sp_OAMethod @fso, 'FileExists',@fleExists OUTPUT,@fileEXEC @err = sp_OADestroy @fsoIF @fleExists=0    PRINT '"' + @file + '" not exists'ELSE    exec('exec xp_cmdshell ''del '+@file+'''') --存在则删除设置级联：alter table 表名    constraint   FK_employee foreign  key   (外键字段)   references  主表(主键字段)ON  UPDATE  CASCADE   设置默认值：alter table 表名    constraint  FK_employee default 默认值 for  字段