常用sql语句

因为常用，先记录下。

--总是记不清参数顺序的replace，substring，charindex

replace("待搜索的","要查找的","用来替换的")

substring("待截取字符串",start,length)--start从1开始

substring("待截取字符串",start)--返回从start位置开始的后边所有字符
charindex("目的字符或字符串","待搜寻字符串")--返回字符或字符串在另一字符串中的起始位置

--checksum加newid生成20位主键值
select  left((convert(varchar(100),getdate(),112)+cast(abs(checksum(newid())) as nvarchar(max))+'0000000000000000'),20)

        --删除约束 DF为约束名称前缀
	declare @constraitName nvarchar(100)
	select @constraitName=b.name from syscolumns a,sysobjects b where a.id=object_id(@tableName) 
	and b.id=a.cdefault and a.name='FlowEmps' and b.name like 'DF%'
	--select @constraitName

	if exists (select * from  sys.check_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
	or exists(select * from  sys.default_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
	or exists(select * from  sys.edge_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
    or exists(select * from  sys.key_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
	begin
	set @sql='alter table '+@tableName+' drop constraint '+@constraitName
	--select @sql
	exec(@sql)
	end

　　

--统计字符ch在字符串中出现的次数
select LEN(columnname)-LEN(REPLACE(columnname,N'ch',N'')) from tableName

--decimal(a,b),a指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
--b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0.
--可以通过decimal来保留较多小数位数的浮点数转换至字符串时的位数
cast(Convert(decimal(18,6),a.Float_X) as varchar)

--判断字段值不是由纯数字构成

select * from 表名 where PATINDEX('%[^0-9]%',列名)>0

--分组后 row_number

select ROW_NUMBER() over (partition by 列名1,列名2 order by 列名3) as row_num from 表名

--查找字段值带%的记录

select * from 表名 where 列名 like '%[%]%'

--attatch 同名数据库文件

use [master]
create database [123] on
(FileName=N'D:Data.MDF'),
(FileName=N'D:Log.LDF')
for attach
go