常用SQL语句实例(含过滤及删除重复数据、导入导出数据等)

本文整理一些常用的SQL语句实例(如过滤重复数据、删除重复数据、导入导出数据等),收藏备用,不断更新中……。欢迎访问作者网站获取最新版:http://hi.wonsoft.cn

-------------------------------第一部份 高级技巧-------------------------------------------

一、过滤重复数据

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子句中省去此列)。原文:http://www.chinaitpower.com/2006Aug/2006-08-23/212751.html

三、导入导出语句大全:

  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

第二部份原文:http://www.crazycoder.cn/DataBase/Article20931.html

 

感谢提供帮助成参考的朋友们。(wonsoft: http://hi.wonsoft.cn

 

 

--删除测试
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 2
insert into @s select 3
insert into @s select 2.5

select exp(sum(log(id))) from @s
12.like的用法
查出记录中的字符串包含1,23,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'
GO

Select * From TEST Where  ',' + share + ',' Like '%,[1-4],%'

13,按条件分类排序
select brand from bra
order by case when brand ='飞利浦' then '' else brand end
//将brand为飞利浦的排在最上面
14.给返回的记录加上一个行数
(1)当没有自增的id列时:
select bh = identity(int,1,1) ,proname,price into temp from product
select '' + 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 
) t
order by bh 
---SQL2005启用 openrowset/opendataset
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
-- 启用xp_cmdshell
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO

--启动远程服务器的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 
2select  * from openrowset('msdasql','driver={sql server};server=10.124.20.10;uid=ncc2008;pwd=ncc2008',hjzx4.dbo.t_p_order) AS a
3select  * 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$] 
office2007
SELECT  * 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)分割

导入:in
EXEC master..xp_cmdshell 'bcp pruduct in d:/wsp.txt -c -t. -r/n'

导出:
--导出全表数据out
EXEC master..xp_cmdshell 'bcp pruduct out d:/wsp.txt -c -Usa -Psa'
--导出查询结果queryout
EXEC master..xp_cmdshell 'bcp "select * from pruduct where part_id like ''80%''" queryout d:/wsp.txt -c -t,  -Usa -Psa'
--导入文本文档:
BULK INSERT os
FROM '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 syscolumns

select a,b,c,substring(d+'/',id,charindex('/',d+'/',id+1)-id) X
from @a a,@t b
where 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 OUTPUT
EXEC @err=sp_OAMethod @fso, 'FileExists',@fleExists OUTPUT,@file
EXEC @err = sp_OADestroy @fso

IF @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  字段 
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页