SQL 相关技术点收集贴

摘要: 不定期、偶尔、添加更新 在网络上看到或者自己开发过程中碰到的 SQL 相关问题

本文地址:http://www.cnblogs.com/vnii/archive/2012/04/13/2445526.html 

1.利用EXEC和sp_executesql对传递参数的sql返回执行结果 

Declare @code varchar(20),@type varchar(50),@title varchar(200),@tableName varchar(200)
Declare @flag int
Declare @nSql nvarchar(4000)
Set @flag=0
        
--Select @flag=id From tableName Where code=@code and type=@type and title=@title
Set @nSql=N'Select @flag=ID From '+@tableName+' Where Code='''+@code+''' and Type='''+@type+''' and Title='''+@title+''''
        
--结果返回给@flag，如果存在返回的是第一条符号的ID，否则返回0
EXEC sp_executesql @nSql,N'@flag int output',@flag output
print @flag

--------------------------------------------------------------


如果有一个存储过程，需要返回结果，可如下
DECLARE @M1 INT
EXEC dbo.sp_myProcName ,@M1 OUTPUT
Print @M1

2.函数内不能执行SQL，即不能在函数内使用 EXEC 

3.函数内不能使用newId()、rand()等函数

4.一个字符串分割函数

-- =============================================
-- Author:        忧郁的匹格 http://www.cnblogs.com/vnii
-- Create date: 2012-4-23
-- Description:    将字符串按照特定字符进行分割成table返回
--                @content 需要被分割的字符串，@split分隔符，@allowEmpty 是否允许空字符串(1表示允许，其它数字表示排除空字符串)
-- =============================================
CREATE FUNCTION [dbo].[fun_splitToTable]
(
@content VARCHAR(8000),@split VARCHAR(10),@allowEmpty int=0
)
Returns @T TABLE(ID INT,VALUE VARCHAR(8000))
AS
BEGIN
    --序号
    DECLARE @ID INT
    SET @ID=1
    --切割的字符串
    DECLARE @VALUE VARCHAR(8000)
    SET @VALUE=''
    
    --第一个分隔符位置
    DECLARE @splitIndex INT
    set @splitIndex=1
    
    --分隔符长度，后续用于计算
    DECLARE @LEN_split INT
    Set @LEN_split=DATALENGTH(@split)

    --DECLARE @T TABLE(ID INT,VALUE VARCHAR(4000))
    
    --第一个分隔符的位置
    Set @splitIndex=CHARINDEX(@split,@content)

    --是否有分隔符
    WHILE @splitIndex>0
    Begin
    
        --切割的字符串
        SET @VALUE=SUBSTRING(@content,1,@splitIndex-1)
        
        --允许空字符串 或者 切割完成的字符串不为空
        IF @allowEmpty=1 or @VALUE<>''
        Begin
            --添加分割后的内容到表变量
            INSERT INTO @T VALUES(@ID, @VALUE)
            --序号+1
            Set @ID=@ID+1
        End
        
        --删除已经添加的数据，重新给被分割的字符串赋值
        Set @content=SUBSTRING(@content,@splitIndex+@LEN_split,DATALENGTH(@content)-@splitIndex-@LEN_split+1)
        --第一个分隔符的位置
        Set @splitIndex=CHARINDEX(@split,@content)
    End

    --切割的字符串
    SET @VALUE=SUBSTRING(@content,1,DATALENGTH(@content))
    
    --允许空字符串 或者 切割完成的字符串不为空
    IF @allowEmpty=1 or @VALUE<>''
    Begin
        --部分分割完成的字符串中没有分隔符时，添加剩余的所有字符串内容到表变量
        INSERT INTO @T VALUES(@ID, @VALUE)
    End
    
    RETURN
END

5.获取一个时间范围内的随机时间

-- ====返回规定天数内的一个随机时间=========================================
-- Author:        忧郁的匹格 http://www.cnblogs.com/vnii
-- Create date: 2012.4.23
-- Description:    返回规定时间范围内的一个随机时间
--                @startTime～@endTime：规定时间范围内
--                @date 返回的结果随机时间
-- =============================================
CREATE PROC PROC_GetRandomTime(@startTime datetime,@endTime datetime,@date datetime output )
AS
Begin
    Declare @time datetime,@min int,@max int
    Set @time=getdate()
    Set @min=DATEDIFF(n,@time,@startTime)
    Set @max=DATEDIFF(n,@time,@endTime)
    
    Declare @tmp int
    IF @max<@min
    Begin
        Set @tmp=@max
        Set @max=@min
        Set @min=@tmp
    End

    set @date=dateadd(d,0,getdate())
    set @date=dateadd(n    ,Floor(@min+(@max-@min)*rand()),@date)
    set @date=dateadd(s    ,-Floor(60*rand()),@date)
    set @date=dateadd(ms,-Floor(1000*rand()),@date)
    --Select @date
End
Go

--declare @result datetime
--exec PROC_GetRandomTime '2012-4-24','2012-4-23',@result OUTPUT
--print @result
Go

6.获取字符串长度的Len方法和DATALENGTH方法区别：Len计算字符数(对于字符串中尾字符的空格不计入长度内)；DATALENGTH计算字符串含有的字节数(对于Varchar类型的字符串计算返回实际字符的字节总数-包括空格，一个汉字两个字节，Char为类型长度)  --另外DATALENGTH支持Text类型的数据，而Len不支持

    Declare @v1 varchar(20),@v2 varchar(20),@v3 varchar(20),@v4 varchar(20),@v5 varchar(20),@v6 varchar(20)
    Declare @v7 char(20)
    SET @v1='abcdef好'    --一个汉字两个字节
    SET @v2='abcdef g'
    SET @v3='abcdefg '
    SET @v4='  '
    SET @v5='  abcde'  --开头两个个空格，结尾一个空格，LEN计算了开头的空格
    SET @v6='  abcde ' --开头两个个空格，结尾一个空格，LEN计算了开头的空格，但是结尾的空格排除了
    SET @v7='  abcde ' --@v7是char类型，Len和上面的规则相同，DATALENGTH返回的结果则为类型长度
    SELECT @v1 AS Value, LEN(@v1) AS [LEN], DATALENGTH(@v1) AS [DATALENGTH] --结果：7 7
        Union ALL
    SELECT @v2 AS Value, LEN(@v2) AS [LEN], DATALENGTH(@v2) AS [DATALENGTH] --结果：8 8
        Union ALL
    SELECT @v3 AS Value, LEN(@v3) AS [LEN], DATALENGTH(@v3) AS [DATALENGTH] --结果：7 8
        Union ALL
    SELECT @v4 AS Value, LEN(@v4) AS [LEN], DATALENGTH(@v4) AS [DATALENGTH] --结果：0 2
        Union ALL
    SELECT @v5 AS Value, LEN(@v5) AS [LEN], DATALENGTH(@v5) AS [DATALENGTH] --结果：7 7
        Union ALL
    SELECT @v6 AS Value, LEN(@v6) AS [LEN], DATALENGTH(@v6) AS [DATALENGTH] --结果：7 8
        Union ALL
    SELECT @v7 AS Value, LEN(@v7) AS [LEN], DATALENGTH(@v7) AS [DATALENGTH] --结果：7 20

7.利用SQL 中的CTE 进行 递归获取数据

--创建测试表
Create Table tbCTE
(
    Id int identity(1,1) not null,
    Name varchar(50) null,
    Pid int not null
)
Go

--填充数据
insert into tbCTE(name,pid)
Select 'top name',0 union all Select 'top name',0
union all 
Select 'name1',1 union all Select 'name2',1 union all Select 'name3',1 
union all 
Select 'name4',2 union all Select 'name5',2 union all Select 'name6',3 
union all 
Select 'name7',6 union all Select 'name8',2 union all Select 'name9',2 
union all 
Select 'name10',8 union all Select 'name11',8 union all Select 'name12',12
Go
;

--利用CTE 进行递归
With tbCTE_Tmp (ID,NAMD,PID,DEEP)
AS
(
    --递归出 符合当前本句条件 下的所有类型，Deep 表示深度
    Select Id,Name,Pid,1 AS Deep From tbCTE Where ID=1
    
    Union ALL
    
    --内联tbCTE_Tmp，不能使用外联，Deep+1 表示深度
    Select T1.Id,T1.Name,T1.Pid,T2.Deep+1 AS Deep 
    From tbCTE T1 Inner Join tbCTE_Tmp T2 ON t1.Pid=T2.Id
)
Select * From tbCTE_Tmp
Order By PID
;

结果为：

8.一句SQL“取得各个部门下排名前三位的销售量”的两种写法比较（当然也可以用循环。。。）

--测试表
Declare @TempTable Table
(
    RowId int Identity,--序列 *忧郁的匹格 http://www.cnblogs.com/vnii *
    DepartId int,--部门id
    SealNum int, --销售量
    CreateTime Datetime default getdate() --时间
)

--测试数据
Declare @i int
Set @i=1
while @i<200
Begin
    insert into @TempTable(DepartId,SealNum)
    Select 1+RAND()*10,1+RAND()*100
    Set @i=@i+1
End

--查询方法 1 : 效率好，但是当本例中 同一类型下出现相同的销售量数据SealNum时候，该类型的数据量会多于指定量
--            （比如，类型为1销售量为98的数据有两条时，类型1就会返回4条数据）
Select * From @TempTable T
Where 
(
    Select COUNT(1) From @TempTable 
    Where DepartId=T.DepartId And SealNum>T.SealNum
    --每条数据对应类型在中相对于同类型的数据，>本数据销售量的记录数 少于3条
)<3
Order By DepartId,SealNum Desc



--查询方法 2 : 相对于方法1，效率低，但是不会出现方法一种多于指定量的情况，反过来就会出现相同销售量的数据"丢失"
Select * From @TempTable T
Where RowId in
(
    Select Top 3 RowId From @TempTable 
    Where DepartId=T.DepartId
    Order By SealNum DESC,RowId DESC
)
Order By DepartId,SealNum Desc

9.查看数据库相关文件

sp_helpdb 数据库名

结果为：

 

10.数据库还原；参见： http://www.yesky.com/imagesnew/software/tsql/ts_ra-rz_25rm.htm

语法：
RESTORE DATABASE { database_name | @database_name_var } 
[ FROM < backup_device > [ ,...n ] ] 
[ WITH
    [ RESTRICTED_USER ]
    [ [ , ] FILE = { file_number | @file_number } ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
            [ ,...n ]
    [ [ , ] KEEP_REPLICATION ]
    [ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]
    [ [ , ] { NOREWIND | REWIND } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] REPLACE ]
    [ [ , ] RESTART ]
    [ [ , ] STATS [ = percentage ] ] 
]

例：
RESTORE DATABASE Test_BDT FROM DISK = N'D:\abc.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  MOVE N'MyDbLogicalName' TO N'F:\sqldata\Test_BDT.mdf',  MOVE N'Test_BDT_log' TO N'F:\Test_BDT_0.ldf',  REPLACE,  STATS = 10

11.获取数据库备份文件的备份信息；参见：http://www.yesky.com/imagesnew/software/tsql/ts_ra-rz_2i9l.htm

语法：
RESTORE FILELISTONLY 
FROM < backup_device > 
[ WITH
    [ FILE = file_number ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] { NOUNLOAD | UNLOAD } ] 
]

< backup_device > ::=
    { 
        { 'logical_backup_device_name' | @logical_backup_device_name_var }
        | { DISK | TAPE } =
            { 'physical_backup_device_name' | @physical_backup_device_name_var } 
    }

例：
RESTORE FileListOnly 
FROM disk='e:\Test_BDT.bak' 

结果为：

 

12.获取数据库备份文件的首部信息；参见：http://www.yesky.com/imagesnew/software/tsql/ts_ra-rz_5urd.htm

语法：
RESTORE HEADERONLY 
FROM < backup_device > 
[ WITH { NOUNLOAD | UNLOAD }
    [ [ , ] FILE = file_number ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] 
]

< backup_device > ::=
    { 
        { 'logical_backup_device_name' | @logical_backup_device_name_var }
        | { DISK | TAPE } =
            { 'physical_backup_device_name' | @physical_backup_device_name_var } 
    }

例：
RESTORE HEADERONLY 
FROM disk='e:\Test_BDT.bak' 

13.获取数据库备份文件的信息组成的结果集 ；参见：http://www.yesky.com/imagesnew/software/tsql/ts_ra-rz_6hkp.htm

语法：
RESTORE LABELONLY 
FROM < backup_device > 
[ WITH { NOUNLOAD | UNLOAD } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]

< backup_device > ::=
    { 
        { 'logical_backup_device_name' | @logical_backup_device_name_var }
        | { DISK | TAPE } =
            { 'physical_backup_device_name' | @physical_backup_device_name_var } 
    }

例：
RESTORE LABELONLY
FROM disk='e:\Test_BDT.bak' 

14.验证数据库备份文件；参见： http://www.yesky.com/imagesnew/software/tsql/ts_ra-rz_2i9l.htm 

语法：
RESTORE VERIFYONLY 
FROM < backup_device > [ ,...n ] 
[ WITH
    [ FILE = file_number ]
    [ [ , ] { NOUNLOAD | UNLOAD } ]
    [ [ , ] LOADHISTORY ]
    [ [ , ] PASSWORD = { password | @password_variable } ]
    [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
    [ [ , ] { NOREWIND | REWIND } ]
]

< backup_device > ::=
    { 
        { 'logical_backup_device_name' | @logical_backup_device_name_var }
        | { DISK | TAPE } =
            { 'physical_backup_device_name' | @physical_backup_device_name_var } 
    }


例：
RESTORE VERIFYONLY
FROM disk='e:\Test_BDT.bak' 

15.数据M分配成为N份，且每一份数据都是大小随机

    Declare @MaxNum int =500 --需要分配的数据数
    Declare @times int =10 --分配的份数

    Declare @randNumTable Table
    (
        Id int identity,
        rate float default(0),
        Num int default(0)
    )
    Declare @i int=0
    While @i<@times
    Begin
        Insert into @randNumTable(rate) Select RAND()
        Set @i=@i+1
    End
    Declare @rateTotal float=1
    Select @rateTotal=SUM(rate) from @randNumTable
    Update @randNumTable Set Num=@MaxNum*Rate/@rateTotal
    Declare @D_value int=0
    Select @D_value=@MaxNum-SUM(num) From @randNumTable
    Select @D_value
    Update @randNumTable Set Num=Num+@D_value Where Id in(Select top 1 Id From @randNumTable order by Num)
    
    Select * From @randNumTable

16.全角、半角 转换

Create  FUNCTION   [dbo].[fun_ConvertFullHalfWidth]
( 
    @str   NVARCHAR(4000),   --要转换的字符串 
    @flag   bit              --转换标志,0转换成半角,1转换成全角 
)
RETURNS   nvarchar(4000) 
AS 
BEGIN 
    DECLARE   @pat   nvarchar(8),@step   int,@i   int,@spc   int 
    IF   @flag=0 
        SELECT   @pat=N'%[！-～]%',@step=-65248, 
        @str=REPLACE(@str,N'　 ',N'   ') 
    ELSE 
        SELECT   @pat=N'%[!-~]%',@step=65248, 
        @str=REPLACE(@str,N'   ',N'　 ') 
        SET   @i=PATINDEX(@pat   COLLATE   LATIN1_GENERAL_BIN,@str) 
        WHILE   @i> 0 
            SELECT   @str=REPLACE(@str, 
            SUBSTRING(@str,@i,1), 
            NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)) 
            ,@i=PATINDEX(@pat   COLLATE   LATIN1_GENERAL_BIN,@str) 
        RETURN(@str) 
END 

17.SQL 控制字符列表对应值

--Tab        char(9)
--换行        char(10)
--回车        char(13)
--双引号    char(34)
--单引号    char(39)

Select Replace('abc def',' ',CHAR(39))

18.一些特殊文字的过滤---Private Use Area：E000-F8FF 点此查看