sqlserver常用知识点备忘录（持续更新）

2024-07-21 02:50:28

字体：大中小

来源：转载

供稿：网友

背景

　　一个项目的开发，离不开数据库的相关操作，表/视图设计，存储过程，触发器等等数据库对象的操作是非常频繁的。有时候，我们会查找系统中类似的代码，然后复制/粘贴进行再进行相应的修改。本文的目的在于归纳、总结sqlserver数据库的常用操作，并不断更新。期以备忘！

P1 sql的执行顺序

　　sql语句是操作数据库的工具，了解sql的执行顺序会极大地帮助我们提高我们编写的sql的执行效率。见以下代码：

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>(1)FROM [left_table](3)<join_type> JOIN <right_table>(2)ON <join_condition>(4)WHERE <where_condition>(5)GROUP BY <group_by_list>(6)WITH <CUBE | RollUP>(7)HAVING <having_condition>(10)ORDER BY <order_by_list>

FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian PRoduct)(交叉联接），生成虚拟表VT1
ON：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
OUTER(JOIN)：如果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
WHERE：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.
GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
HAVING：对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.
SELECT：处理SELECT列表，产生VT8.
DISTINCT：将重复的行从VT8中移除，产生VT9.
ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

　总的来说，select的列是最后一步被执行的，而From的Table是首先被执行的。

P2 创建带Try。。。Catch的存储过程模板

　　Copy下面的代码，然后新建查询，就可以写sql语句，执行完后，一个你自己的存储过程就建立好了！

USE [DB]--设定对应的数据库GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- AUTHOR:-- DESCRIBE:-- =============================================CREATE PROCEDURE [dbo].[UP_InsertJHBData]   --存储过程名    (      @CustomerName VARCHAR(50)             --参数    )AS     BEGIN            SET NOCOUNT ON                     --提高性能的，必须要有        DECLARE @Now DATETIME        SET @Now = GETDATE()               --所有操作保证统一时间                BEGIN TRY         --在这里写SQL        END TRY                BEGIN CATCH                DECLARE @ErrorMessage NVARCHAR(4000) ;            DECLARE @ErrorSeverity INT ;            DECLARE @ErrorState INT ;                               SELECT  @ErrorMessage = ERROR_MESSAGE() ,                    @ErrorSeverity = ERROR_SEVERITY() ,                    @ErrorState = ERROR_STATE() ;            PRINT @ErrorMessage             RAISERROR(@ErrorMessage,  -- Message text.                        @ErrorSeverity, -- Severity.                        @ErrorState     -- State.                        ) ;            RETURN -1 ;        END CATCH    END

P3 创建带事务的存储过程模板

　　只是将带Try。。。Catch的存储过程的模板中加入了事务的控制，使用类似

USE [DB]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- AUTHOR:-- DESCRIBE:-- =============================================CREATE PROCEDURE [dbo].[UP_InsertJHBData]--存储过程名--参数    (      @CustomerName VARCHAR(50)    )--参数AS     BEGIN           SET NOCOUNT ON ;--提高性能的，必须要有        DECLARE @Now DATETIME ;        SET @Now = GETDATE() ;--所有操作保证统一时间               BEGIN TRY            BEGIN TRANSACTION myTrans ;--开始事务           --在这里写SQL           COMMIT TRANSACTION myTrans ;--事务提交语句        END TRY                BEGIN CATCH            ROLLBACK TRANSACTION myTrans-- 始终回滚事务            --抛出异常            DECLARE @ErrorMessage NVARCHAR(4000) ;            DECLARE @ErrorSeverity INT ;            DECLARE @ErrorState INT ;            SELECT  @ErrorMessage = ERROR_MESSAGE() ,                    @ErrorSeverity = ERROR_SEVERITY() ,                    @ErrorState = ERROR_STATE() ;            RAISERROR(@ErrorMessage,  -- Message text.                 @ErrorSeverity, -- Severity.                 @ErrorState     -- State.                 ) ;        END CATCH    END

P4 批量插入

　　或者生成测试数据，或者填充临时表，我们都会碰到批量插入表的需求，此时，针对被插入的表是否存在分以下两种情况：

被插入的表存在，使用以下sql，达到将表#Table2Name中的ID和Name两列的数据插入表#Table1Name中
1. ```
INSERT INTO #Table1Name SELECT ID,NAME FROM #Table2Name
```
被插入的表不存在，使用以下sql，达到创建表#Table1Name，并将表#Table2Name中的ID和Name两列的数据插入表#Table1Name中
1. ```
SELECT ID,NAME INTO #Table1Name FROM #Table2Name
```

P5 批量更新

　　链接两个表，通过第一张表的数据去批量地更新第二张表，使用以下的sql

　　　　　　　　 UPDATE  t2               SET     t2.FirstSaleOrderDate = t1.FirstSaleOrderDate ,                       t2.LastSaleOrderDate = t1.LastSaleOrderDate               FROM    #T_ValidSODate t1               INNER JOIN #T_PendingReport t2 ON t1.GiftCardNO = t2.GiftCardNO

P6 循环模板

　　在存储过程中，经常会生成一些临时表，然后循环临时表的数据进行处理，以下模板可以帮助伙伴们快速处理此类需求

　　 --生成带行号的临时表数据，并插入临时表#T_Table中    SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum,           NAME     INTO #T_Table    FROM TableName        --获取记录总数    DECLARE @RecordCount INT = 0        SELECT @RecordCount = COUNT(1) FROM #T_Table                    DECLARE @CurrRowNum INT = 1        --当前行号    DECLARE @CurrName VARCHAR(50)    --当前字段        --循环记录                WHILE @CurrRowNum <= @RecordCount         BEGIN            --获取当前记录            SELECT @CurrName = Name FROM #T_Table WHERE RowNum = @CurrRowNum                        --自定义sql                        SET @CurrRowNum = @CurrRowNum + 1 --到下一条记录        END

P7 字符串转表函数

　　下面的函数的功能是将【a;b;c;】这样的字符串按照【;】进行分割并返回一张表

USE [Util]GO/****** Object:  UserDefinedFunction [dbo].[Func_StringListToTable]    Script Date: 04/08/2014 10:59:53 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        -- Create date: -- Description:    将字符串转换为表 --                调用示例如下                --DECLARE  @StringList NVARCHAR(max)                --SET @StringList='a;b;c;'                --DECLARE @Split VARCHAR(10)                --SET @Split=';'                --SELECT * FROM Util.dbo.[Func_StringListToTable](@StringList,@Split)-- =============================================CREATE FUNCTION [dbo].[Func_StringListToTable]    (      -- Add the parameters for the function here      @StringList NVARCHAR(MAX) ,      @split VARCHAR(10)    )RETURNS @StringTable TABLE    (      ID INT ,      String VARCHAR(MAX)    )AS     BEGIN    -- Fill the table variable with the rows for your result set        DECLARE @i INT        SET @i = 1        WHILE ( CHARINDEX(@split, @StringList) <> 0 )             BEGIN                   INSERT  @StringTable                        ( ID ,                          String                        )                VALUES  ( @i ,                          SUBSTRING(@StringList, 1,                                    CHARINDEX(@split, @StringList) - 1)                        )                   SET @StringList = STUFF(@StringList, 1,                                        CHARINDEX(@split, @StringList)                                        + LEN(@split) - 1, '')                    SET @i = @i + 1            END            IF @StringList <> ''             BEGIN                INSERT  @StringTable                        ( ID, String )                VALUES  ( @i, @StringList )             END         RETURN       END

P8 分组数据集并返回每个组的前n条记录

　　Row_NUMBER()函数用于生成行号；利用PARTITION BY可以将结果集按照指定需求进行分组；最终使用一个简单的子查询就能够获取每组的前3条数据

SELECT  *FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY ProductNO ORDER BY ProductNO ) AS RowNum ,                    *          FROM      IM.dbo.ItemInfo        ) tWHERE   t.RowNum IN ( 1, 2, 3 )

P9 【用户自定义表类型】的使用

　　您是否碰到过这样的需求：调用存储过程的时候传一张表进去？？？

在sqlserver数据库中有一种称为【用户自定义表类型】的数据结构，类似表，存储过程的参数可以定义为【用户自定义表类型】，代码调用时可以直接传入一个List<T>，而存储过程