背景 一个项目的开发，离不开数据库的相关操作，表/视图设计，存储过程，触发器等等数据库对象的操作是非常频繁的。有时候，我们会查找系统中类似的代码，然后复制/粘贴进行再进行相应的修改。本文的目的在于归纳、总结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筛选器。只有那些使 为真的行才被插入VT2。 OUTER(JOIN)： 如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。 WHERE： 对VT3应用WHERE筛选器。只有使 为true的行才被插入VT4. GROUP BY： 按GROUP BY子句中的列列表对VT4中的行分组，生成VT5. CUBE|ROLLUP： 把超组(Suppergroups)插入VT5,生成VT6. HAVING： 对VT6应用HAVING筛选器。只有使 为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中
INSERT INTO #Table1Name SELECT ID,NAME FROM #Table2Name被插入的表不存在，使用以下sql，达到创建表#Table1Name，并将表#Table2Name中的ID和Name两列的数据插入表#Table1Name中
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 ，而存储过程调用时可以直接传入一个表变量。
以下代码实现了在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
下面贴出这两个存储过程的源码，供大家参考
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
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
View Code
查看更多关于sqlserver常用知识点备忘录（持续更新）的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did97200