常用SQL语句

0.GUID去除横线和变换为小写

1	SELECT LOWER(REPLACE(LTRIM(NEWID()),'-',''))

1.IDENTITY 函数说明

1 2	IDENTITY ( data_type [ , seed , increment ] ) AS column_name IDENTITY(numric,1,1)

2.将数据库的日志文件压缩

1
2
3

ALTER DATABASE [DBName] SET RECOVERY SIMPLE  
ALTER DATABASE [DBName] SET RECOVERY FULL  
DBCC SHRINKDATABASE([DBName], 0)

3.查询数据


select A.[合同号],A.[工程名],  Replace(B.设备型号,char(10),'@') as 设备型号 , Replace(B.[设备名称],',','@') as 设备名称  FROM  [dbo].[工程清单$] A 
LEFT JOIN [dbo].[CI_商务产品数据表] B ON A.[合同号]=B.[订货记录合同号]
where ([设备型号] like'%曙光%' OR [设备型号] LIKE '%ThinkVision%' OR [设备型号] LIKE '%戴尔%' OR [设备型号] LIKE '%DELL%') AND [设备名称] not like '%打印机%'

4.判断字符串最后一位是否为字母

SELECT [订货记录号] AS ContractNum,[工程名称] as ProjectName,
[更改内容] AS ChangeContent,[商务下发时间] AS CICompleteTime 
FROM [dbo].[CI_订货记录] WHERE  UPPER(RIGHT([订货记录号],1))>='A' AND  UPPER(RIGHT([订货记录号],1))<='Z'
AND [订货记录号] LIKE 'SZ%' ORDER BY [订货记录号] DESC

5.跨服务器查询数据

1	select * from openrowset('SQLOLEDB','sql服务器名';'用户名';'密码',数据库名.dbo.表名)

6.SQL Server暂停数据连接

1 2	ALTER DATABASE [TbBSystem] SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE [TbBSystem] SET ONLINE

7.逗号分隔函数
XQuery方法

CREATE function [dbo].[func_splitid]
(@str varchar(max),@split varchar(10))
RETURNS @t Table (colname VARCHAR(200))
AS
BEGIN
    DECLARE @x XML
    SET @x = CONVERT(XML,'<items><item id="' + REPLACE(@str, @split, '"/><item id="') + '"/></items>')
    INSERT INTO @t SELECT x.item.value('@id[1]', 'VARCHAR(50)') FROM @x.nodes('//items/item') AS x(item)
    RETURN 
END

通过charindex和substring。

create function func_splitstring
(@str nvarchar(max),@split varchar(10))
returns @t Table (c1 varchar(100))
as
begin
    declare @i int
    declare @s int
    set @i=1
    set @s=1
    while(@i>0)
    begin    
        set @i=charindex(@split,@str,@s)
        if(@i>0)
        begin
            insert @t(c1) values(substring(@str,@s,@i-@s))
        end   
        else begin
            insert @t(c1) values(substring(@str,@s,len(@str)-@s+1))
        end
        set @s = @i + 1   
    end
    return
end

8.CTE表达式简单使用

declare @num int
SET @num=3
WHILE @num>0
begin 
    declare @userids varchar(200)
    declare @nowtime datetime
    DECLARE @USERID VARCHAR(20) 
    set @nowtime=GETDATE()
    set  @userids= (select TOP 1 UserIds from [dbo].[Sys_RoleInfo] WHERE RoleId=@num );
    WITH USERIDS AS
    (
        select * from SRApp.dbo.func_splitid(@userids,',') 
    )
 
    SELECT colname as EmpAccount,@num as RoleId,@nowtime as UpdateTime,'fupeng' as Oper INTO ##TEMP_TABLE  from USERIDS
    insert into [dbo].[Sys_RL_RoleInfo](EmpAccount,RoleId,UpdateTime,Oper) 
    SELECT  EmpAccount,@num as RoleId,@nowtime as UpdateTime,'fupeng' as Oper from ##TEMP_TABLE
    DROP TABLE ##TEMP_TABLE
    set @num=@num-1
end