鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > 数据库 > mssql > >

sqlserver常用知识点备忘录

来源:互联网 作者:佚名 时间:2015-03-13 00:45
背景 一个项目的开发,离不开数据库的相关操作,表/视图设计,存储过程,触发器等等数据库对象的操作是非常频繁的。有时候,我们会查找系统中类似的代码,然后复制/粘贴进行再进行相应的修改。本文的目的在于归纳、总结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

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 【用户自定义表类型】的使用

网友评论
<