sqlserver常用知識(shí)點(diǎn)備忘錄（持續(xù)更新）

2024-07-21 02:50:28

字體：大中小

供稿：網(wǎng)友

背景

　　一個(gè)項(xiàng)目的開發(fā)，離不開數(shù)據(jù)庫的相關(guān)操作，表/視圖設(shè)計(jì)，存儲(chǔ)過程，觸發(fā)器等等數(shù)據(jù)庫對象的操作是非常頻繁的。有時(shí)候，我們會(huì)查找系統(tǒng)中類似的代碼，然后復(fù)制/粘貼進(jìn)行再進(jìn)行相應(yīng)的修改。本文的目的在于歸納、總結(jié)sqlserver數(shù)據(jù)庫的常用操作，并不斷更新。期以備忘！

P1 sql的執(zhí)行順序

　　sql語句是操作數(shù)據(jù)庫的工具，了解sql的執(zhí)行順序會(huì)極大地幫助我們提高我們編寫的sql的執(zhí)行效率。見以下代碼：

(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子句中的前兩個(gè)表執(zhí)行笛卡爾積（Cartesian PRoduct)(交叉聯(lián)接），生成虛擬表VT1
ON：對VT1應(yīng)用ON篩選器。只有那些使<join_condition>為真的行才被插入VT2。
OUTER(JOIN)：如果指定了OUTER JOIN（相對于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部聯(lián)接把左表標(biāo)記為保留表，右外部聯(lián)接把右表標(biāo)記為保留表，完全外部聯(lián)接把兩個(gè)表都標(biāo)記為保留表）中未找到匹配的行將作為外部行添加到 VT2,生成VT3.如果FROM子句包含兩個(gè)以上的表，則對上一個(gè)聯(lián)接生成的結(jié)果表和下一個(gè)表重復(fù)執(zhí)行步驟1到步驟3，直到處理完所有的表為止。
WHERE：對VT3應(yīng)用WHERE篩選器。只有使<where_condition>為true的行才被插入VT4.
GROUP BY：按GROUP BY子句中的列列表對VT4中的行分組，生成VT5.
CUBE|ROLLUP：把超組(Suppergroups)插入VT5,生成VT6.
HAVING：對VT6應(yīng)用HAVING篩選器。只有使<having_condition>為true的組才會(huì)被插入VT7.
SELECT：處理SELECT列表，產(chǎn)生VT8.
DISTINCT：將重復(fù)的行從VT8中移除，產(chǎn)生VT9.
ORDER BY：將VT9中的行按ORDER BY 子句中的列列表排序，生成游標(biāo)（VC10).
TOP：從VC10的開始處選擇指定數(shù)量或比例的行，生成表VT11,并返回調(diào)用者。

　總的來說，select的列是最后一步被執(zhí)行的，而From的Table是首先被執(zhí)行的。

P2 創(chuàng)建帶Try。。。Catch的存儲(chǔ)過程模板

　　Copy下面的代碼，然后新建查詢，就可以寫sql語句，執(zhí)行完后，一個(gè)你自己的存儲(chǔ)過程就建立好了！

USE [DB]--設(shè)定對應(yīng)的數(shù)據(jù)庫GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- AUTHOR:-- DESCRIBE:-- =============================================CREATE PROCEDURE [dbo].[UP_InsertJHBData]   --存儲(chǔ)過程名    (      @CustomerName VARCHAR(50)             --參數(shù)    )AS     BEGIN            SET NOCOUNT ON                     --提高性能的，必須要有        DECLARE @Now DATETIME        SET @Now = GETDATE()               --所有操作保證統(tǒng)一時(shí)間                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 創(chuàng)建帶事務(wù)的存儲(chǔ)過程模板

　　只是將帶Try。。。Catch的存儲(chǔ)過程的模板中加入了事務(wù)的控制，使用類似

USE [DB]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- AUTHOR:-- DESCRIBE:-- =============================================CREATE PROCEDURE [dbo].[UP_InsertJHBData]--存儲(chǔ)過程名--參數(shù)    (      @CustomerName VARCHAR(50)    )--參數(shù)AS     BEGIN           SET NOCOUNT ON ;--提高性能的，必須要有        DECLARE @Now DATETIME ;        SET @Now = GETDATE() ;--所有操作保證統(tǒng)一時(shí)間               BEGIN TRY            BEGIN TRANSACTION myTrans ;--開始事務(wù)           --在這里寫SQL           COMMIT TRANSACTION myTrans ;--事務(wù)提交語句        END TRY                BEGIN CATCH            ROLLBACK TRANSACTION myTrans-- 始終回滾事務(wù)            --拋出異常            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 批量插入

　　或者生成測試數(shù)據(jù)，或者填充臨時(shí)表，我們都會(huì)碰到批量插入表的需求，此時(shí)，針對被插入的表是否存在分以下兩種情況：

被插入的表存在，使用以下sql，達(dá)到將表#Table2Name中的ID和Name兩列的數(shù)據(jù)插入表#Table1Name中
1. ```
INSERT INTO #Table1Name SELECT ID,NAME FROM #Table2Name
```
被插入的表不存在，使用以下sql，達(dá)到創(chuàng)建表#Table1Name，并將表#Table2Name中的ID和Name兩列的數(shù)據(jù)插入表#Table1Name中
1. ```
SELECT ID,NAME INTO #Table1Name FROM #Table2Name
```

P5 批量更新

　　鏈接兩個(gè)表，通過第一張表的數(shù)據(jù)去批量地更新第二張表，使用以下的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 循環(huán)模板

　　在存儲(chǔ)過程中，經(jīng)常會(huì)生成一些臨時(shí)表，然后循環(huán)臨時(shí)表的數(shù)據(jù)進(jìn)行處理，以下模板可以幫助伙伴們快速處理此類需求

　　 --生成帶行號(hào)的臨時(shí)表數(shù)據(jù)，并插入臨時(shí)表#T_Table中    SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum,           NAME     INTO #T_Table    FROM TableName        --獲取記錄總數(shù)    DECLARE @RecordCount INT = 0        SELECT @RecordCount = COUNT(1) FROM #T_Table                    DECLARE @CurrRowNum INT = 1        --當(dāng)前行號(hào)    DECLARE @CurrName VARCHAR(50)    --當(dāng)前字段        --循環(huán)記錄                WHILE @CurrRowNum <= @RecordCount         BEGIN            --獲取當(dāng)前記錄            SELECT @CurrName = Name FROM #T_Table WHERE RowNum = @CurrRowNum                        --自定義sql                        SET @CurrRowNum = @CurrRowNum + 1 --到下一條記錄        END

P7 字符串轉(zhuǎn)表函數(shù)

　　下面的函數(shù)的功能是將【a;b;c;】這樣的字符串按照【;】進(jìn)行分割并返回一張表

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:    將字符串轉(zhuǎn)換為表 --                調(diào)用示例如下                --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 分組數(shù)據(jù)集并返回每個(gè)組的前n條記錄

　　Row_NUMBER()函數(shù)用于生成行號(hào)；利用PARTITION BY可以將結(jié)果集按照指定需求進(jìn)行分組；最終使用一個(gè)簡單的子查詢就能夠獲取每組的前3條數(shù)據(jù)

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 【用戶自定義表類型】的使用

　　您是否碰到過這樣的需求：調(diào)用存儲(chǔ)過程的時(shí)候傳一張表進(jìn)去？？？

在sqlserver數(shù)據(jù)庫中有一種稱為【用戶自定義表類型】的數(shù)據(jù)結(jié)構(gòu)，類似表，存儲(chǔ)過程的參數(shù)可以定義為【用戶自定義表類型】，代碼調(diào)用時(shí)可以直接傳入一個(gè)List<T>，而存儲(chǔ)過程