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

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

一、基础

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 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官方博客 返回首页