经典SQL语句

--************ null字段处理 ************

1、PicUrl为null或为空的返回默认值：

select (case when len(isnull(PicUrl,''))=0
		then
		    '/images/default.jpg'
		else PicUrl
		end) 
from user where PicUrl is null

　

--************ 带分隔符字符串转化为表 ************　

1、replace函数

declare @IN_Str varchar(500),@IN_SplitChar varchar(10),@string  varchar(max);
set @IN_Str='a,b,b,v,b';
set @IN_SplitChar=',';
set @string='select * from (select '''+replace(replace(@IN_Str,'''',''''''),@IN_SplitChar,'''as str union all select ''')+''') a' ;
exec (@string) ;

2.自定义函数

if exists(select 1 from sysobjects where name = 'FunSplitString')
	drop function FunSplitString
go
create function FunSplitString(
	@SplitStr  nvarchar(max),       --需要分割的字符串
    @Separator nvarchar(max)=',',   --字符串分隔符
    @RemoveEmptyEntries bit=1       --0表示保留空字符串，1表示不保留空字符串
) 
returns @TABLE table 
(
    [Id] int identity(1,1),
    [Value] nvarchar(max)
) 
as
begin 
    declare @Index int, @Entry nvarchar(max)
    set @Index = charindex(@Separator,@SplitStr)

    while (@Index>0)
    begin
        set @Entry=ltrim(rtrim(substring(@SplitStr, 1, @Index-1)))
        
        if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
            begin
                insert into @TABLE([Value]) Values(@Entry)
            end

        set @SplitStr = substring(@SplitStr, @Index+datalength(@Separator)/2, len(@SplitStr))
        set @Index = charindex(@Separator, @SplitStr)
    end
    
    set @Entry=ltrim(rtrim(@SplitStr))
    if (@RemoveEmptyEntries=0) or (@RemoveEmptyEntries=1 and @Entry<>'')
        begin
            insert into @TABLE([Value]) Values(@Entry)
        end

    return
end

 

--************ MSSQL数据库如何批量清空数据表并恢复起始ID为0 ************

一、针对Microsoft SQL SERVER。

运行语句：TRUNCATE   TABLE   soft
说明：soft 为数据表名，按你要清空的数据库表名自行更改

运行语句：DBCC   CHECKIDENT   ('soft',   RESEED,  1)
说明：soft 为数据表名，1 为起始ID数，可自定义修改

 

二、针对mysql。

主需要第一句就可以了

 

--************ 数据库成员查询 ************　　

1、查找数据库含有ParentId字段的表　　

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U' AND SC.NAME LIKE 'parentid' -- LIKE in SQL Server is case insensitive
ORDER BY SO.[name], SC.colid;

2、查找数据库中用到ChildIds的存储过程

SELECT OBJECT_NAME(OBJECT_ID), LEN(definition) AS length, definition
FROM
    sys.sql_modules
WHERE
    objectproperty(OBJECT_ID, 'IsProcedure') = 1 AND definition LIKE '%childids%'
ORDER BY length DESC;

　　

--************ SQL collate ************

collate是一个子句，可应用于数据库定义或列定义以定义排序规则，或应用于字符串表达式以应用排序规则投影。

由两部份构成，前半部份是指本排序规则所支持的字符集。
如：
　　Chinese_PRC_CS_AI_WS
前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
排序规则的后半部份即后缀 含义： 
　　_BIN 二进制排序 
　　_CI(CS) 是否区分大小写，CI不区分，CS区分
　　_AI(AS) 是否区分重音，AI不区分，AS区分　　　
　　_KI(KS) 是否区分假名类型,KI不区分，KS区分　
     _WI(WS) 是否区分宽度 WI不区分，WS区分　

 

区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。
区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，
         比较还将重音不同的字母视为不等。
区分假名:如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。
区分宽度:如果想让比较将半角字符和全角字符视为不等，请选择该选项

联表：
select * from #t1 A inner join #t2 B on A.name=B.name collate Chinese_PRC_CI_AI_WS

排序：
select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS  

替换：
SELECT REPLACE('Das ist ein Test' COLLATE Latin1_General_BIN, 'Test', 'desk' )