SQL技巧

1 替换查询的条件。

declare @strWhere  nvarchar(1000)declare @connFilter nvarchar(1000)set @strWhere='A.SaleUser=1'set  @connFilter=replace(@strWhere,'SaleUser','InUserID')print @connFilterprint @strWhere

2 联合查询使用排序

select * from (select * from A union  select * from B) as aorder a.Time

3 备份数据库

backup database 数据库名to disk='e://123.bak'

4 游标

declare @GoodsName varchar(50)declare youbiao cursor scroll for select GoodsName from Base_Goodsopen youbiaofetch first from youbiao into @GoodsName while @@fetch_Status=0begin   print @GoodsNamefetch next from youbiao into @GoodsName endclose youbiaodeallocate youbiao

5 创建临时表保存数据

--1、创建临时表保存、数据IF EXISTS (select * from syscolumns  where id=object_id('#临时表名'))    DROP TABLE #临时表名 Create  TABLE #临时表名(  列名)--2、插入临时表数据INSERT INTO #临时表名SELECT  列名FROM 数据表  

6 判断表是否存在字段

if exists(select * from syscolumns where id=object_id('table1') and name='name')

7 SQL的Stuff函数

以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串

SELECT STUFF('abcdef', 2, 3, 'ijklmn')

下面是结果集
Aijklmnef

8 SQL的charindex函数

charindex('要查询的字符串','被查询的字符穿',从哪里开始查询)

9 在字符串前加0

declare @numerStr nvarchar(30)set @numerStr='1'--从第位开始到第减去字符串长度的位数，替换为SET @numerStr=Replicate(0,5-len(@numerStr))+@numerStrprint @numerStr

10 创建视图

create view v_Base_Brandasselect * from Base_Brand where isNull(dr,0)=0

11 查询某张表的最大值

alter proc proc_GetMaxNumber(@tableName nvarchar(60))asbegin exec ('    select max (number) from '+@tableName+' ')end

12 使用sp_executesql返回数据

--定义要执行的sql语句和要返回的变量declare @sql nvarchar(4000),@GoodsName nvarchar(30)set @GoodsName=0set @sql='select @GoodsName=isNull(GoodsName,0) from Base_Goods where InGoodsID=16'--执行exec sp_executesql @sql,N'@GoodsName nvarchar(30) output',@GoodsName outputif(@GoodsName<>0)    print '@GoodsName='+@GoodsName

13 查询是否存在数据库

select 1 from master..sysdatabases where name=’数据库名’

14 还原数据库

RESTORE DATABASE XBDB3000Z FROM disk='路径'

15 查看数据库端口

exec sys.sp_readerrorlog 0, 1, 'listening'

16 存储过程传递拼接的字符串，分割成数组的格式

declare @users varchar(1000),@m int, @n int, @user varchar(20)set @users = 'john,ken,candy,'set @m = charindex(',',@tags)set @n = 1while(@m > 0)begin  set @user=substring(@users,@n,@m-@n)  set @n = @m + 1  set @m = charindex(',',@users,@n)  -- 得到 user  print @userend

17 case语句

--简单Case函数CASE sexWHEN '1' THEN '男'WHEN '2' THEN '女'ELSE '其他' END--Case搜索函数CASE WHEN sex = '1' THEN '男'WHEN sex = '2' THEN '女'ELSE '其他' END

18 循环临时表每行数据，进行处理

WHILE EXISTS(SELECT * FROM #TempSalesOrderDtl)Begin    --查询第一行数据，然后删除掉    SELECT TOP 1  @sysNO=sysNO ,@inGoodsID=inGoodsID,@quantity=quantity,@InComID=InComID FROM #TempSalesOrderDtl    Delete #TempSalesOrderDtl WHERE sysNO=@sysNOEnd

19 修改表字段的数据类型

if   exists (select * from syscolumns  where id=object_id('表名') and  name='列名') begin    update 表名 set 列名=nullALTER TABLE 表名 alter column 列名 类型end

20 修改表字段名称

if exists(select * from syscolumns where id=object_id('表名') and name='列名')    EXEC sp_rename '表名.列名','新列名','COLUMN'

21 查询表中的重复数据

select 列名from 表名group by 列名having count(*)>1

22 增加/修改字段说明

EXECUTE sp_addextendedproperty N'MS_Description', '说明',N'user',N'dbo',N'table',N'表名',N'column',N'列名'EXECUTE sp_updateextendedproperty --------------------------------------

23 查询字段说明

select *from sys.columns left join sys.extended_properties on class=1 and sys.columns.object_id=sys.extended_properties.major_id and sys.columns.column_id=sys.extended_properties.minor_idwhere sys.columns.object_id=object_id('表名') and sys.columns.name='列名'

24 表增加字段

if not exists(select * from syscolumns where id=object_id('表名') and name='列名')    alter table 表名add 列名类型not null UNIQUE