SQL server常用总结：

 

 

1.SQL 子句的执行顺序？

         1.FORM 2.WHERE 3.GROUP BY 4.HAVING 5.SELECT 6.ORDER BY

2.SQL 子句的书写顺序？

         1.SELECT 2.FROM 3.WHERE 4.GROUP BY 5.HAVING 6.ORDER BY

3.CASE WHEN 语法的then 结果是不同数据类型会导致错误吗？为什么？

 

DECLARE @c INT=1

SELECT CASE @c WHEN 1

THEN 'code'

ELSE 2 END;

 

有可能会导致错误，因为case when 的所有结果会转成相同的数据类型，而不同的数据类型转换可能会报错。

 

4.相关子查询的子查询的查询字段是*或者常量会影响整个查询定的效率吗？

不会，子查询的select * ,select 1都不影响，这里执行计划的结果是一样的。

 

 

 

5.Pivot和Unpivot的使用?

 

Pivot 是透视，行转列，Unpivot是逆透视，列转行。

 

 

DECLARE @t TABLE(Student NVARCHAR(50),[Subject] NVARCHAR(50),Score float)

 

INSERT INTO @t SELECT '小明','数学',27

INSERT INTO @t SELECT '小王','数学',35

INSERT INTO @t SELECT '小方','数学',76

INSERT INTO @t SELECT '小明','语文',44

INSERT INTO @t SELECT '小王','语文',43

INSERT INTO @t SELECT '小方','语文',90

 

SELECT * FROM @t

 

--行转列

SELECT * FROM @t PIVOT(MAX(score) FOR subject IN (数学,语文)) b

--列转行

SELECT * FROM (

         SELECT * FROM @t PIVOT(MAX(score) FOR subject IN (数学,语文)) b )

         a UNPIVOT (score FOR subject IN (数学,语文)) b

 

 

 

--行专列 case when 语法：

SELECT

         Student,

MAX(CASE Subject WHEN '数学' THEN score ELSE '' END) 数学 ,

MAX(CASE Subject WHEN '语文' THEN score ELSE '' END) 语文

FROM @t

GROUP BY Student

 

行转列有3个步骤1.分组2.使用聚合函数3.使用case when 语法进行行转列。

 

6.row_number() 的使用方法?

 

--row_number和over()函数配合使用，再over中可以进行分组排序的功能。

SELECT ROW_NUMBER() OVER(PARTITION BY Password ORDER BY CreateDateTime),UserCode

FROM dbo.SysUser

 

over()函数的最大功能就是不使用group by 也可以具有分组的功能。

 

7.xml 和table 数据的相互转换?

 

 

DECLARE @t TABLE(Student NVARCHAR(50),[Subject] NVARCHAR(50),Score float)

 

INSERT INTO @t SELECT '小明','数学',27

INSERT INTO @t SELECT '小王','数学',35

 

SELECT * FROM @t

 

DECLARE @xml XML=(SELECT * FROM @t FOR XML RAW('row'),ROOT('document'))

 

SELECT

         row.value('@Student','nvarchar(50)') Student,

         row.value('@Subject','nvarchar(50)') [Subject],

         row.value('@Score','float') Score

 FROM @xml.nodes('document/row') ut(row)

 

 

8.创建存储过程，标量函数，表值函数，触发器的语法?

 

CREATE PROC Prd_test

@c1 NVARCHAR(50),

@c2 FLOAT

AS

BEGIN

    SET NOCOUNT ON;

         --其他语句

END

 

 

CREATE FUNCTION ftn_test

(@c1 NVARCHAR(50),

@c2 FLOAT)

RETURNS int

BEGIN

        

         --其他语句

         RETURN 1;

END

 

 

CREATE FUNCTION ftn_test

(@c1 NVARCHAR(50),

@c2 FLOAT)

RETURNS @t TABLE(rowNum INT)

BEGIN

        

         --其他语句

         INSERT INTO @t SELECT 1;

         RETURN ;

END

 

 

CREATE TRIGGER tri_Test ON  dbo.SysUser

FOR UPDATE

AS

BEGIN

    --deleted inserted

         SELECT 1;

END

 

9.临时表进行循环?

 

DECLARE @t TABLE(rowNum INT IDENTITY(1,1),Code NVARCHAR(50))

 

INSERT INTO @t SELECT '01'

INSERT INTO @t SELECT '02'

INSERT INTO @t SELECT '03'

INSERT INTO @t SELECT '04'

 

DECLARE @i INT =1

DECLARE @c INT = (SELECT COUNT(*) FROM @t)

DECLARE @Code NVARCHAR(50)

WHILE @i<=@c

BEGIN

    --

         SELECT @Code=Code FROM @t WHERE rowNum=@i;

 

         PRINT @Code;

         SET @i = @i+1;

END

 

10.存储过程中如何加快查询和插入速度？

 

临时表定义主键可以加快查询速度

CREATE TABLE #t(rowNum INT IDENTITY(1,1) primary key,Code NVARCHAR(50))

 

批量插入中把循环视为一次事务，可以加快插入速度。

 

BEGIN TRAN;

while @i<=@c

begin

         insert into aaa select 1;

         set @i =@i+1;

end

COMMIT TRAN;

 

因为默认情况下，一条SQL语句是一个事务。

11.delete 和 truncate 的区别？

truncate删除表的全部数据 没有日志
delete 按照条件删除，有日志

12.SELECT 'aa'+2 这个SQL 语句会报错吗？

会报错，由于数字类型的等级较高，'aa'会被转成数字，由于aa是字符串，转换必然失败。

13.数字取整使用什么函数？

向上取整使用celing()  ceiling是天花板的意思
向下取整使用floor() floor 是地板的意思

14.sql 调用web服务/webapi?

启用参数：

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE  WITH OVERRIDE;;
    GO
    sp_configure 'Ole Automation Procedures', 1;
    GO
    RECONFIGURE  WITH OVERRIDE;;
    GO
    EXEC sp_configure 'Ole Automation Procedures';
    GO

get请求：

DECLARE @url NVARCHAR(max)
set @url='http://localhost:5000/usertest/login?name=admin'
Declare @Object as Int
Declare @ResponseText AS  varchar(1000)   ;    
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@url,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
Exec sp_OAMethod @Object, 'send', NULL, null --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出
Exec sp_OADestroy @Object

SELECT @ResponseText

POST请求：

DECLARE @url NVARCHAR(max)
set @url='http://localhost:5000/usertest/getName'
Declare @Object as Int
Declare @ResponseText AS  varchar(1000)   ;    
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',@url,'false'
Exec sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type','application/x-www-form-urlencoded'
Exec sp_OAMethod @Object, 'send', NULL, null --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出
Exec sp_OADestroy @Object

SELECT @ResponseText

 

15.SQL语句拼接中的引号问题

--含有引号的常量

SELECT * FROM dbo.SysUser WHERE UserCode='admin'
DECLARE @t NVARCHAR(max)='SELECT * FROM dbo.SysUser WHERE UserCode=''admin'''
EXEC (@t)

--变量拼接
DECLARE @t NVARCHAR(50)='admin'

DECLARE @sql NVARCHAR(max)='SELECT * FROM dbo.SysUser WHERE UserCode='''+@t+'''' 

PRINT @sql;
EXEC(@sql)

16.SQL 递归

递归CTE：比如BOM/组织结构

DECLARE @t TABLE(
    TestId BIGINT ,
    ParentId BIGINT,
    Name NVARCHAR(50)
)

INSERT INTO @t SELECT 1,null,'老张'
INSERT INTO @t SELECT 2,1,'小明1'
INSERT INTO @t SELECT 3,1,'小明2'
INSERT INTO @t SELECT 4,1,'小明3'
INSERT INTO @t SELECT 5,4,'小王'

SELECT * FROM @t;

WITH cte_test
AS
(
    SELECT testid,name FROM @t WHERE TestId=4
    UNION all
    SELECT a.TestId,a.Name FROM @t a INNER JOIN cte_test b ON a.ParentId=b.testid 
)

SELECT * FROM cte_test

--
1.递归要点
    自己调用自己 

    循环条件：UNION ALL ，正是UNION ALL 驱动循环下去，
    截止条件：INNER JOIN 结果为空的时候，此时停止递归。
    SQL SERVER 默认递归次数 100，超过会停止执行。不过100次也够用了，能超过100次也很困难。