sqlserver常用知識點備忘錄（持續更新）

一個項目的開發，離不開資料庫的相關操作，表/視圖設計，存儲過程，觸發器等等資料庫對象的操作是非常頻繁的。有時候，我們會查找系統中類似的代碼，然後複制/粘貼進行再進行相應的修改。本文的目的在于歸納、總結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]--設定對應的資料庫
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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
        ) t
WHERE   t.RowNum IN ( 1, 2, 3 )

P9 【使用者自定義表類型】的使用

　　您是否碰到過這樣的需求：調用存儲過程的時候傳一張表進去？？？

在sqlserver資料庫中有一種稱為【使用者自定義表類型】的資料結構

，類似表，存儲過程的參數可以定義為【使用者自定義表類型】，代碼調用時可以直接傳入一個List<T>，而存儲過程調用時可以直接傳入一個表變量。

以下代碼實作了在IM資料庫中建立一個名稱為GCRP_PendingGiftCard_TYPE的使用者自定義表結構：

USE [IM]
GO

/****** Object:  UserDefinedTableType [dbo].[GCRP_PendingGiftCard_TYPE]    Script Date: 04/08/2014 14:56:16 ******/
CREATE TYPE [dbo].[GCRP_PendingGiftCard_TYPE] AS TABLE(
    [RowNum] [int] NULL,
    [GiftCardNO] [varchar](500) NULL,
    [UsedDate] [datetime] NULL,
    [CustomerName] [varchar](50) NULL,
    [ReduceAmount] [decimal](18, 2) NULL,
    [Amount] [decimal](18, 2) NULL
)
GO

下面的代碼示範了【使用者自定義表類型】的使用方式和場景（使用起來和臨時表、表變量類似）

--1 聲明一個自定義表類型@T_PendingGiftCard
   DECLARE @T_PendingGiftCard GCRP_PendingGiftCard_TYPE
   --2 執行一個存儲過程,并把傳回的結果集插入到上面聲明的自定義表類型@T_PendingGiftCard中
   INSERT INTO @T_PendingGiftCard 
            EXEC IM.dbo.UP_GCRP_GetEntireGfitCardWithReduceAmount    
   --3 聲明一個自定義表類型@T_PendingSO
   DECLARE @T_PendingSO GCRP_PendingSO_TYPE
   --4 執行一個存儲過程,傳入表類型@T_PendingGiftCard,并把傳回的結果集插入自定義表類型@T_PendingSO中
   INSERT INTO @T_PendingSO
            EXEC IM.dbo.UP_GCRP_GetEntireSOWithGiftCardUsed 
                @T_PendingGiftCard

下面貼出這兩個存儲過程的源碼，供大家參考

sqlserver常用知識點備忘錄（持續更新）

USE [IM]
GO
/****** Object:  StoredProcedure [dbo].[UP_GCRP_GetEntireGfitCardWithReduceAmount]    Script Date: 04/08/2014 15:01:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
        
-- =============================================
-- Author:        DeanZhou    
-- Create date: 2014-04-04
-- Description:    擷取禮品卡報表資料-擷取目前所有有餘額的禮品卡資訊
-- =============================================
CREATE PROCEDURE [dbo].[UP_GCRP_GetEntireGfitCardWithReduceAmount]
AS 
    BEGIN
        SET NOCOUNT ON ;--提高性能的，必須要有

        --1.1 從表accounting.dbo.EWalletData中查詢出有充值記錄的禮品卡
        SELECT  RIGHT(RTRIM(ISNULL(SONO, '')), 10) AS GiftCardNO ,
                ISNULL(Amount, 0) AS Amount ,
                CreateDate AS UsedDate ,
                CustomerName
        INTO    #T_TotalGiftCard
        FROM    accounting.dbo.EWalletData
        WHERE   Source = 11 AND InOrOut = 1
    
        --1.2 從表IM.dbo.Temp_UsedGiftCardReportDetail中查詢出所有有使用記錄的禮品卡(消費金額設定為負數)
        SELECT  A.GiftCardNO ,
                -A.UsedAmount AS Amount ,
                A.GiftCardChargedDate AS UsedDate ,
                A.CustomerName
        INTO    #T_UsedGiftCard
        FROM    IM.dbo.Temp_UsedGiftCardReportDetail A
                INNER JOIN #T_TotalGiftCard B ON A.GiftCardNO = B.GiftCardNO
        DECLARE @E DECIMAL(18,2)
        
        --1.3 合并1.1和1.2的資料,擷取所有禮品卡的餘額 
        SELECT  ROW_NUMBER() OVER (ORDER BY S.UsedDate ASC) AS RowNum, --編号
                S.GiftCardNO ,                                --禮品卡編号
                S.UsedDate ,                                --禮品卡充值日期
                S.CustomerName ,                            --客戶名稱
                ISNULL(S.ReduceAmount, 0) AS ReduceAmount , --禮品卡剩餘金額
                ISNULL(M.Amount, 0) AS Amount                --禮品卡面額
        FROM    ( SELECT    T.GiftCardNO ,
                            T.UsedDate ,
                            T.CustomerName ,
                            SUM(T.Amount) AS ReduceAmount
                  FROM      ( SELECT    GiftCardNO ,
                                        Amount ,
                                        UsedDate ,
                                        CustomerName
                              FROM      #T_TotalGiftCard
                              UNION ALL
                              SELECT    GiftCardNO ,
                                        Amount ,
                                        UsedDate ,
                                        CustomerName
                              FROM      #T_UsedGiftCard
                            ) T
                  GROUP BY  T.GiftCardNO ,
                            T.UsedDate ,
                            T.CustomerName
                ) S
                INNER JOIN #T_TotalGiftCard M ON S.GiftCardNO = M.GiftCardNO
        WHERE   S.ReduceAmount > 0
        
    END

View Code

sqlserver常用知識點備忘錄（持續更新）

USE [IM]
GO
/****** Object:  StoredProcedure [dbo].[UP_GCRP_GetEntireSOWithGiftCardUsed]    Script Date: 04/08/2014 15:02:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
        
-- =============================================
-- Author:        DeanZhou    
-- Create date: 2014-04-04
-- Description:    擷取禮品卡報表資料-擷取使用禮品卡的訂單資訊
-- =============================================
ALTER PROCEDURE [dbo].[UP_GCRP_GetEntireSOWithGiftCardUsed]
    (
      @T_PendingGiftCard GCRP_PendingGiftCard_TYPE READONLY
    )
AS 
    BEGIN
        SET NOCOUNT ON ;--提高性能的，必須要有
        
       --訂單狀态：1 待稽核 2 待支付 3 已支付，待确認 4 已支付 5 出庫中 6 已發貨 7 已收貨 8 換貨中 9 退貨中
       --          10 已換貨 11 已退貨 12 退款成功 13 換貨被拒絕 14 退貨被拒絕 15 交易成功 16 訂單已關閉 17 客戶廢棄
       --          18 商城廢棄 19 系統自動廢棄 20 退款中 21 等待團購成功 22 組團失敗        
           
        SELECT  S.SONO ,                    -- 訂單編号 VARCHAR(30)                        
                S.EwalletDiscountAmount ,    -- 電子錢包支付金額 DECIMAL(18,2)
                S.RefundAmount ,            -- 發生退款的總金額 DECIMAL(18,2)
                ( S.EwalletDiscountAmount + S.RefundAmount ) AS RealUsedAmount , -- 實際使用電子錢包的金額 DECIMAL(18,2)
                0 AS RelatedRefundID ,
                S.CustomerName ,            -- 客戶名稱 VARCHAR(50)
                S.SaleOrderStatus ,            -- 訂單狀态    INT
                S.CreateDate                -- 下單日期 DATETIME
        INTO    #T_PendingSO
        FROM    ( SELECT    A.SONO ,
                            A.EwalletDiscountAmount ,
                            ( SELECT    -ISNULL(SUM(B.Amount), 0)
                              FROM      RMA.dbo.RefundRecord B
                              WHERE     B.SONO = A.SONO
                                        AND B.RefundType = 3
                                        AND B.Status IN ( 2, 3, 4 )
                            ) AS RefundAmount ,
                            CONVERT(DECIMAL(18, 2), 0) AS RealUsedAmount ,
                            A.CustomerName ,
                            A.SaleOrderStatus ,
                            A.CreateDate
                  FROM      SO.dbo.SOMaster A
                  WHERE     EwalletDiscountAmount > 0
                            AND SaleOrderStatus NOT IN ( 1, 2, 17, 19 )
                            AND EXISTS ( SELECT 1
                                         FROM   @T_PendingGiftCard C
                                         WHERE  A.CustomerName = C.CustomerName
                                                AND A.CreateDate > C.UsedDate )
                            AND NOT EXISTS ( SELECT 1
                                             FROM   IM.dbo.Temp_UsedGiftCardReportDetail B
                                             WHERE  A.SONO = B.SONO
                                                    AND B.UsedAmount > 0 )
                ) S

        DELETE IM.dbo.Temp_UsedGiftCardReportDetail WHERE UsedAmount <= 0 AND EXISTS (SELECT 1 FROM #T_PendingSO B WHERE Temp_UsedGiftCardReportDetail.SONO = B.SONO )
       
        SELECT  ROW_NUMBER() OVER ( ORDER BY S.CustomerName, S.SONO, S.CreateDate ASC ) AS RowNum ,
                S.*
        FROM    ( SELECT    *
                  FROM      #T_PendingSO
                  UNION ALL
                  SELECT    A.SONO ,
                            B.EwalletDiscountAmount ,
                            -A.Amount AS RefundAmount ,
                            B.RealUsedAmount ,
                            A.RelatedRefundID ,
                            B.CustomerName ,
                            A.Status ,
                            A.CreateDate
                  FROM      RMA.dbo.RefundRecord A
                            INNER JOIN #T_PendingSO B ON A.SONO = B.SONO
                  WHERE     A.RefundType = 3
                            AND A.Status IN ( 2, 3, 4 )
                  UNION ALL
                  SELECT    A.SONO ,
                            0 AS EwalletDiscountAmount ,
                            -A.Amount AS RefundAmount ,
                            B.UsedAmount ,
                            A.RelatedRefundID ,
                            B.CustomerName ,
                            A.Status ,
                            A.CreateDate
                  FROM      RMA.dbo.RefundRecord A
                            INNER JOIN IM.dbo.Temp_UsedGiftCardReportDetail B ON A.SONO = B.SONO AND B.UsedAmount > 0
                  WHERE     A.RefundType = 3
                            AND A.Status IN ( 2, 3, 4 )
                ) S
          
    END

P10 分區表

http://www.cnblogs.com/baidawei/p/4720605.html

sqlserver常用知識點備忘錄（持續更新）

繼續閱讀

SqlServer同步資料庫-分發-訂閱

幾種SqlServer資料庫分頁方式

SQLServer資料庫比對工具用什麼好。

C#連接配接sqlserver資料庫

我的職業生涯（四）

.NET開發人員的瓶頸和職業發展

資料庫拼接列字段拼接列字段

windows8.1上安裝sqlserver2005後找不到啟動程式

hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置hibernate.cfg.xml檔案連接配接mySql、Oracle、SqlServer配置

asp連接配接資料庫的代碼方法

MS SQL Server 資料庫（基礎篇）

SQL Server不存在或通路被拒絕"問題的解決"SQL Server不存在或通路被拒絕"問題的解決

asp 連接配接資料庫的方法

ms sqlserver常用sql語句

金蝶雲星空即時庫存查詢SQL語句SQLServer

解決"T-SQL無法附加到程序"錯誤的補充,T-SQLcan not attach to process