SQL技巧

1 替换查询的条件。

declare @strWhere  nvarchar(1000)
declare @connFilter nvarchar(1000)

set @strWhere='A.SaleUser=1'
set  @connFilter=replace(@strWhere,'SaleUser','InUserID')

print @connFilter
print @strWhere

2 联合查询使用排序

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

3 备份数据库

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

4 游标

declare @GoodsName varchar(50)
declare youbiao cursor scroll for select GoodsName from Base_Goods
open youbiao
fetch first from youbiao into @GoodsName while @@fetch_Status=0
begin 
  print @GoodsName
fetch next from youbiao into @GoodsName 
end
close youbiao
deallocate 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))+@numerStr
print @numerStr

10 创建视图

create view v_Base_Brand
as
select * from Base_Brand where isNull(dr,0)=0

11 查询某张表的最大值

alter proc proc_GetMaxNumber
(
@tableName nvarchar(60)
)
as
begin

 exec ('
    select max (number) from '+@tableName+' ')

end

12 使用sp_executesql返回数据

--定义要执行的sql语句和要返回的变量
declare @sql nvarchar(4000),@GoodsName nvarchar(30)
set @GoodsName=0
set @sql='select @GoodsName=isNull(GoodsName,0) from Base_Goods where InGoodsID=16'

--执行
exec sp_executesql @sql,N'@GoodsName nvarchar(30) output',@GoodsName output

if(@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 = 1
while(@m > 0)
begin
  set @user=substring(@users,@n,@m-@n)
  set @n = @m + 1
  set @m = charindex(',',@users,@n)
  -- 得到 user
  print @user
end

17 case语句

--简单Case函数
CASE sex
WHEN '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=@sysNO
End

19 修改表字段的数据类型

if   exists (select * from syscolumns  where id=object_id('表名') and  name='列名') 
begin
    update 表名 set 列名=null
ALTER 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_id
where 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