本文整理一些常用的SQL语句实例（如过滤重复数据、删除重复数据、导入导出数据等），收藏备用，不断更新中……。欢迎访问作者网站获取最新版：http://hi.wonsoft.cn
-------------------------------第一部份 高级技巧-------------------------------------------
一、过滤重复数据
1、完全重复的记录
/* 功能：指定字段完全重复 */ select distinct 字段1,字段2,字段3 from 数据表2、部分关键字段重复的记录
/*数据结构：角色档案（角色编码，角色，角色分类编码） 功 能：取出指定字段（角色分类编码）为关键字的无重复数据，重复的取第一条 说 明：重复记录取最后一条，只需要把min改成max即可 */ select * from 角色档案 t where 角色编码 in (select min(角色编码) from 角色档案 t1 group by t1.角色分类编码)二、删除重复记录
数据库的使用过程中由于程序方面的问题有时候会碰到重复数据，重复数据导致了数据库部分设置不能正确设置，本例举出删除它的办法。
方法1：
declare @max integer,@id integer declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1 open cur_rows fetch cur_rows into @id,@max while @@fetch_status=0 begin select @max = @max -1 set rowcount @max delete from 表名 where 主字段 = @id fetch cur_rows into @id,@max end close cur_rows set rowcount 0方法2：
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用
select distinct * from tableName就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName drop table tableName select * into tableName from #Tmp drop table #Tmp发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID 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
三、导入导出语句大全：
SELECT * FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions /*动态文件名 declare @fn varchar(20),@s varchar(1000) set @fn = ’c:/test.xls’ set @s =’’’Microsoft.Jet.OLEDB.4.0’’, ’’Data Source="’+@fn+’";User ID=Admin;Password=;Extended properties=Excel 5.0’’’ set @s = ’SELECT * FROM OpenDataSource (’+@s+’)...sheet1$’ exec(@s) */ SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+’ ’ 转换后的别名 FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions /********************** EXCEL导到远程SQL insert OPENDATASOURCE( ’SQLOLEDB’, ’Data Source=远程ip;User ID=sa;Password=密码’ ).库名.dbo.表名 (列名1,列名2) SELECT 列名1,列名2 FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’Data Source="c:/test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0’)...xactions /** 导入文本文件 EXEC master..xp_cmdshell ’bcp dbname..tablename in c:/DT.txt -c -Sservername -Usa -Ppassword’ /** 导出文本文件 EXEC master..xp_cmdshell ’bcp dbname..tablename out c:/DT.txt -c -Sservername -Usa -Ppassword’ 或 EXEC master..xp_cmdshell ’bcp "Select * from dbname..tablename" queryout c:/DT.txt -c -Sservername -Usa -Ppassword’ 导出到TXT文本，用逗号分开 exec master..xp_cmdshell ’bcp "库名..表名" out "d:/tt.txt" -c -t ,-U sa -P password’ BULK INSERT 库名..表名 FROM ’c:/test.txt’ WITH ( FIELDTERMINATOR = ’;’, ROWTERMINATOR = ’/n’ ) --/* dBase IV文件 select * from OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’ ,’dBase IV;HDR=NO;IMEX=2;DATABASE=C:/’,’select * from [客户资料4.dbf]’) --*/ --/* dBase III文件 select * from OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’ ,’dBase III;HDR=NO;IMEX=2;DATABASE=C:/’,’select * from [客户资料3.dbf]’) --*/ --/* FoxPro 数据库 select * from openrowset(’MSDASQL’, ’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/’, ’select * from [aa.DBF]’) --*/ /**************导入DBF文件****************/ select * from openrowset(’MSDASQL’, ’Driver=Microsoft Visual FoxPro Driver; SourceDB=e:/VFP98/data; SourceType=DBF’, ’select * from customer where country != "USA" order by country’) go /***************** 导出到DBF ***************/ 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句 insert into openrowset(’MSDASQL’, ’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/’, ’select * from [aa.DBF]’) select * from 表 说明: SourceDB=c:/ 指定foxpro表所在的文件夹 aa.DBF 指定foxpro表的文件名. /*************导出到Access********************/ insert into openrowset(’Microsoft.Jet.OLEDB.4.0’, ’x:/A.mdb’;’admin’;’’,A表) select * from 数据库名..B表 /*************导入Access********************/ insert into B表 selet * from openrowset(’Microsoft.Jet.OLEDB.4.0’, ’x:/A.mdb’;’admin’;’’,A表) /*文件名为参数*/ declare @fname varchar(20) set @fname = ’d:/test.mdb’ exec(’SELECT a.* FROM opendatasource(’’Microsoft.Jet.OLEDB.4.0’’, ’’’+@fname+’’’;’’admin’’;’’’’, topics) as a ’) SELECT * FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’Data Source="f:/northwind.mdb";Jet OLEDB:Database Password=123;User ID=Admin;Password=;’) --产品 /********************** 导入 xml 文件********************/ DECLARE @idoc int DECLARE @doc varchar(1000) --sample XML document SET @doc =’ <root> <Customer cid= "C1" name="Janine" city="Issaquah"> <Order oid="O1" date="1/20/1996" amount="3.5" /> <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied </Order> </Customer> <Customer cid="C2" name="Ursula" city="Oelde" > <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue white red"> <Urgency>Important</Urgency> Happy Customer. </Order> <Order oid="O4" date="1/20/1996" amount="10000"/> </Customer> </root> ’ -- Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement using OPENXML rowset provider. SELECT * FROM OPENXML (@idoc, ’/root/Customer/Order’, 1) WITH (oid char(5), amount float, comment ntext ’text()’) EXEC sp_xml_removedocument @idoc /**********************Excel导到Txt****************************************/ '想用 select * into opendatasource(...) from opendatasource(...) /*实现将一个Excel文件内容导入到一个文本文件 假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位) 且银行帐号导出到文本文件后分两部分，前8位和后8位分开。 */ /*邹健： 如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2 然后就可以用下面的语句进行插入 注意文件名和目录根据你的实际情况进行修改. */ insert into opendatasource(’MICROSOFT.JET.OLEDB.4.0’ ,’Text;HDR=Yes;DATABASE=C:/’ )...[aa#txt] --,aa#txt) --*/ select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8) from opendatasource(’MICROSOFT.JET.OLEDB.4.0’ ,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’ --,Sheet1$) )...[Sheet1$] 如果你想直接插入并生成文本文件,就要用bcp declare @sql varchar(8000),@tbname varchar(50) --首先将excel表内容导入到一个全局临时表 select @tbname=’[##temp’+cast(newid() as varchar(40))+’]’ ,@sql=’select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8) into ’+@tbname+’ from opendatasource(’’MICROSOFT.JET.OLEDB.4.0’’ ,’’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:/a.xls’’ )...[Sheet1$]’ exec(@sql) --然后用bcp从全局临时表导出到文本文件 set @sql=’bcp "’+@tbname+’" out "c:/aa.txt" /S"(local)" /P"" /c’ exec master..xp_cmdshell @sql --删除临时表 exec(’drop table ’+@tbname) /********************导整个数据库*********************************************/ /*用bcp实现的存储过程 */ /* 实现数据导入/导出的存储过程 根据不同的参数,可以实现导入/导出整个数据库/单个表 调用示例: --导出调用示例 ----导出单个表 exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:/zj.txt’,1 ----导出整个数据库 exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:/docman’,1 --导入调用示例 ----导入单个表 exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:/zj.txt’,0 ----导入整个数据库 exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:/docman’,0 */ if exists(select 1 from sysobjects where name=’File2Table’ and objectproperty(id,’IsProcedure’)=1) drop procedure File2Table go create procedure File2Table @servername varchar(200) --服务器名 ,@username varchar(200) --用户名,如果用NT验证方式,则为空’’ ,@password varchar(200) --密码 ,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表 ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这参数是文件存放路径,文件名自动用表名.txt ,@isout bit --1为导出,0为导入 as declare @sql varchar(8000) if @tbname like ’%.%.%’ --如果指定了表名,则直接导出单个表 begin set @sql=’bcp ’+@tbname +case when @isout=1 then ’ out ’ else ’ in ’ end +’ "’+@filename+’" /w’ +’ /S ’+@servername +case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end +’ /P ’+isnull(@password,’’) exec master..xp_cmdshell @sql end else begin --导出整个数据库,定义游标,取出所有的用户表 declare @m_tbname varchar(250) if right(@filename,1)<>’/’ set @filename=@filename+’/’ set @m_tbname=’declare #tb cursor for select name from ’+@tbname+’..sysobjects where xtype=’’U’’’ exec(@m_tbname) open #tb fetch next from #tb into @m_tbname while @@fetch_status=0 begin set @sql=’bcp ’+@tbname+’..’+@m_tbname +case when @isout=1 then ’ out ’ else ’ in ’ end +’ "’+@filename+@m_tbname+’.txt " /w’ +’ /S ’+@servername +case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end +’ /P ’+isnull(@password,’’) exec master..xp_cmdshell @sql fetch next from #tb into @m_tbname end close #tb deallocate #tb end go /************* Oracle **************/ EXEC sp_addlinkedserver ’OracleSvr’, ’Oracle 7.3’, ’MSDAORA’, ’ORCLDB’ GO delete from openquery(mailser,’select * from yulin’) select * from openquery(mailser,’select * from yulin’) update openquery(mailser,’select * from yulin where id=15’)set disorder=555,catago=888 insert into openquery(mailser,’select disorder,catago from yulin’)values(333,777) /*----------------------------------------- 补充： 对于用bcp导出,是没有字段名的. 用openrowset导出,需要事先建好表. 用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导 -----------------------------------------*/
---------------------------------第二部份、入门与进阶-------------------------------------------
一、基础
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，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' 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 2、 select * from openrowset('msdasql','driver={sql server};server=10.124.20.10;uid=ncc2008;pwd=ncc2008',hjzx4.dbo.t_p_order) AS a 3、 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$] 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 字段
