SqlServer 技术点总结(持续更新)

　　本文是用于记录自己平时遇到的一些SQL问题或知识点，以便以后自己查阅，会持续的更新，增加内容。发在博客园也可以和各位博友共同学习交流，如文中记录的有错误之处希望指出，谢谢。

一、用SQL语句调用作业

USE msdb
EXEC sp_start_job @job_name = '作业名称'

 

二、SQL语句中GO语句

1.GO的解释

官方文档：GO只是SQL Server管理器（SSMS）中用来提交T-SQL语句的一个标志。

解释：GO向SqlServer实用工具发出一批Transact-SQL语句结束的信号。

每一个被GO分隔的语句都是一个单独的事务，一批GO隔开的语句执行失败，不会影响其他GO隔开的语句执行。也就是说其中的一个GO失败的，不会影响其他的GO成功。

但是多个GO之间是有顺序的，前面一个执行完毕，才会执行后面的。

update xxxx
set num = 1 
GO

update student
set studentNo = 222
where studentName = '张三'
GO

如上的示例：假如数据库中没有'xxxx'表，有'student'表，就是说第一个语句是错误的，第二个语句是正确的。它的执行顺序是'update xxxx'执行完毕再去执行'update student'，因为GO是有顺序的；但是由于他们都是单独的事务，所以第一个失败了，但是第二个会成功。

2.C#中执行的SQL语句带'GO'，会报'GO'附近有语法错误

USE [ApiDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Test](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

如上的建表语句，在SQL Server Management Studio管理工具中是可以执行的，然后本来想放在C#程序中直接执行，但是抛出：'GO'附近有语法错误，然后去查阅资料。

SQL Server Management Studio管理工具属于客户端，它可以识别GO用来分批提交，但是'GO'不是SQL语句。它是可为osql，isql使用工具及SQL Server查询分析器识别的命令，SQL服务器不能识别所以不能用在程序中。

如果确实要在程序中使用这些语句，只能在程序中启用一个事务，多条语句分别执行(去掉GO)，所有语句执行完毕再提交事务。

 

三、SQL中的BEGIN和END语句

BEGIN和END语句用于将多个Transact-SQL语句组合为一个逻辑块。

BEGIN单独一行，标志着语句块的开始，后面跟语句块，也就是多条T-SQL语句，然后END单独一行，标志着语句块结束。

注：BEGIN和END必须一起使用

BEGIN  
    { sql_statement | statement_block }   
END  

上面为语法。其实BEGIN和END相当于C#中的'{'和'}'。

 

四、查询数据库中有没有某表，没有就创建

if not exists(select * from sysobjects s where s.xtype = 'U' and s.name = '表名')
begin 
    CREATE TABLE 表名
end 

其中sysobjects存储的是数据库中表相关的信息，xtype = 'U'代表所有类型为表的。

 五、SqlServer 新建自定义函数：使用指定字符分隔字符串（例：按照"，"将字符串分隔开）

CREATE FUNCTION [dbo].[Fun_SplitStr]
(
    @originalStr      VARCHAR(8000), --要分割的字符串
    @split varchar(100)              --分隔符号
)
RETURNS @temp TABLE(Result VARCHAR(100))
AS
BEGIN
    DECLARE @result AS VARCHAR(100);   --定义变量用于接收单个结果 
    
    SET @originalStr = @originalStr + @split ;  
      
    WHILE (@originalStr <> '')
    BEGIN
        SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr, 1) -1) ;  
        
        INSERT @temp VALUES(@result) ;  
        
        --STUFF()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。
        SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr, 1), '');
    END 
    RETURN
END

调用函数：

select * from dbo.Fun_SplitStr('978,7,5007,7234,7',',')

表值函数与表关联查询示例：

SELECT
o.*,
sp.result productname
FROM dbo.[order] o
cross apply dbo.Fun_SplitStr(o.detailname,',') as sp
WHERE o.DataValid = 20--数据校验成功

 

 六、Sqlserver按照a字段分组后合并b字段的值

create table #temp(
membercode nvarchar(100) null,
orderdate datetime null,
gifts nvarchar(100) null
)

insert into #temp values('ye', '2019-8-1', '筷子')
insert into #temp values('ye', '2019-7-1', '戒指')
insert into #temp values('ye', '2018-2-1', '手镯')

select * from #temp

select
t.membercode,
year(t.orderdate) years,
cast(year(t.orderdate) as nvarchar(4)) + '：' + stuff((select '，' + gifts from #temp te 
                            where te.membercode = t.membercode 
                            and year(te.orderdate) = year(t.orderdate) 
                            for XML PATH('')),1,1,'') + '；' year_gifts
from #temp t
where t.gifts is not null and t.gifts != ''
group by t.membercode,year(t.orderdate)
order by year(t.orderdate)

 七、sqlserver生成随机数

注：在单个批处理中多次调用时，rand（）返回相同的数字。

批量生成随机数时使用：

SELECT ABS(CHECKSUM(NewId())) % 99

代表在1到99之间生成一个随机数。