背景
一个项目的开发，离不开数据库的相关 *** 作，表/视图设计，存储过程，触发器等等数据库对象的 *** 作是非常频繁的。有时候，我们会查找系统中类似的代码，然后复制/粘贴进行再进行相应的修改。本文的目的在于归纳、总结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(),@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 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,@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>，而存储过程调用时可以直接传入一个表变量。以下代码实现了在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 ONGOSET QUOTED_IDENTIFIER ONGO -- =============================================-- 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,Customername FROM #T_UsedGiftCard ) T GROUP BY T.GiftCardNO,T.Customername ) S INNER JOIN #T_TotalgiftCard M ON S.GiftCardNO = M.GiftCardNO WHERE S.ReduceAmount > 0 ENDVIEw Code
USE [IM]GO/****** Object: StoredProcedure [dbo].[UP_GCRP_GetEntireSOWithGiftCardUsed] Script Date: 04/08/2014 15:02:50 ******/SET ANSI_NulLS ONGOSET QUOTED_IDENTIFIER ONGO -- =============================================-- 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,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,4 ) UNION ALL SELECT A.soNO,0 AS EwalletdiscountAmount,B.Usedamount,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,4 ) ) S END总结
以上是内存溢出为你收集整理的sqlserver常用知识点备忘录（更新）全部内容，希望文章能够帮你解决sqlserver常用知识点备忘录（更新）所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）