sql 某些概念的重新整理和巩固

事务
事务是将一系列操作作为一个单元执行，要么成功，要么失败，回滚到最初状态。在事务处理术语中，事务要么提交，要么中止。若要提交事务，所有参与者都必须保证对数据的任何更改是永久的。不论系统崩溃或是发生其他无法预料的事件，更改都必须是持久的。只要有一个参与者无法做出此保证，整个事务就会失败。事务范围内的所有数据更改将回滚到特定设置点。  

           
1，显式事务

BEGIN TRANSACTION

为连接标记显式事务的起始点。

COMMIT TRANSACTION 或 COMMIT WORK

如果没有遇到错误，可使用该语句成功地结束事务。该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。

ROLLBACK TRANSACTION 或 ROLLBACK WORK

用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。
2，隐性事务

当连接以隐性事务模式进行操作时，Microsoft® SQL Server™ 将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始，只需提交或回滚每个事务。隐性事务模式生成连续的事务链。

在为连接将隐性事务模式设置为打开之后，当 SQL Server 首次执行下列任何语句时，都会自动启动一个事务：

ALTER TABLE	INSERT
CREATE	OPEN
DELETE	REVOKE
DROP	SELECT
FETCH	TRUNCATE TABLE
GRANT	UPDATE

在发出 COMMIT 或 ROLLBACK 语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次当连接执行这些语句中的任何语句时，SQL Server 都将自动启动一个新事务。SQL Server 将不断地生成一个隐性事务链，直到隐性事务模式关闭为止。

隐性事务模式可以通过使用 Transact-SQL SET 语句，或通过数据库 API 函数和方法进行设置。
Transact-SQL 隐性事务

DB-Library 应用程序和 Transact-SQL 脚本使用 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 语句启动隐性事务模式。使用 SET IMPLICIT_TRANSACTIONS OFF 语句关闭隐性事务模式。使用 COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK 语句结束每个事务。

SET QUOTED_IDENTIFIER OFFGOSET NOCOUNT OFFGOUSE pubsGOCREATE TABLE ImplicitTran (Cola int PRIMARY KEY,         Colb char(3) NOT NULL)GOSET IMPLICIT_TRANSACTIONS ONGO/* First implicit transaction started by an INSERT statement */INSERT INTO ImplicitTran VALUES (1, 'aaa')GOINSERT INTO ImplicitTran VALUES (2, 'bbb')GO/* Commit first transaction */COMMIT TRANSACTIONGO/* Second implicit transaction started by a SELECT statement */SELECT COUNT(*) FROM ImplicitTranGOINSERT INTO ImplicitTran VALUES (3, 'ccc')GOSELECT * FROM ImplicitTranGO/* Commit second transaction */COMMIT TRANSACTIONGOSET IMPLICIT_TRANSACTIONS OFFGO

请参见

 

COMMIT TRANSACTION

ROLLBACK WORK

COMMIT WORK

SET IMPLICIT_TRANSACTIONS

ROLLBACK TRANSACTION

BEGIN TRANSACTION

SET   IMPLICIT_TRANSACTIONS  
  为连接设置隐性事务模式。  
   
  语法  
  SET   IMPLICIT_TRANSACTIONS   {   ON   |   OFF   }  
   
  注释  
  当设置为   ON   时，SET   IMPLICIT_TRANSACTIONS   将连接设置为隐性事务模式。当设置为   OFF   时，则使连接返回到自动提交事务模式。  
   
  当连接是隐性事务模式且当前不在事务中时，执行下列语句将启动事务：  
   
  ALTER   TABLE   FETCH   REVOKE    
  CREATE   GRANT   SELECT    
  DELETE   INSERT   TRUNCATE   TABLE    
  DROP   OPEN   UPDATE    
   
   
  如果连接已经在打开的事务中，则上述语句不启动新事务。  
   
  对于因为该设置为   ON   而自动打开的事务，用户必须在该事务结束时将其显式提交或回滚。否则当用户断开连接时，事务及其所包含的所有数据更改将回滚。在事务提交后，执行上述任一语句即可启动新事务。  
   
  隐性事务模式将保持有效，直到连接执行   SET   IMPLICIT_TRANSACTIONS   OFF   语句使连接返回到自动提交模式。在自动提交模式下，如果各个语句成功完成则提交。  
   
  在进行连接时，SQL   Server   ODBC   驱动程序和用于   SQL   Server   的   Microsoft   OLE   DB   提供程序自动将   IMPLICIT_TRANSACTIONS   设置为   OFF。对来自   DB-Library   应用程序的连接，SET   IMPLICIT_TRANSACTIONS   默认为   OFF。  
   
  当   SET   ANSI_DEFAULTS   为   ON   时，将启用   SET   IMPLICIT_TRANSACTIONS。  
   
  SET   IMPLICIT_TRANSACTIONS   的设置是在执行或运行时设置，而不是在分析时设置。  

3 自动提交模式是 Microsoft® SQL Server™ 的默认事务管理模式
sql系统表

关于SQL Server数据库的一切信息都保存在它的系统表格里。在大多数情况下，对你最有用的两个列是sysobjects.name和sysobjects.xtype。前面一个用来列出待考察对象的名字，而后一个用来定义对象的类型，其代码如下：

• C：检查约束。
• D：默认的约束
• F：外键约束
• L：日志
• P：存储过程
• PK：主键约束
• RF：复制过滤存储过程
• S：系统表格
• TR：触发器
• U：用于表格。
• UQ：独特的约束。
• V：视图
• X：被扩展的存储过程

在碰到触发器的情形下，用来识别触发器类型的其他三个列是：deltrig、instrig和uptrig。

你可以用下面的命令列出感兴趣的所有对象：

SELECT * FROM sysobjects WHERE xtype = <type of interest>

在特殊情况下，也就是在父表格拥有触发器的情况下，你可能想要用下面这样的代码查找数据库：

SELECT
      Sys2.[name] TableName,
      Sys1.[name] TriggerName,
      CASE
            WHEN Sys1.deltrig > 0 THEN'Delete'
            WHEN Sys1.instrig > 0 THEN'Insert'
            WHEN Sys1.updtrig > 0 THEN'Update'
      END'TriggerType'
FROM
      sysobjects Sys1 JOIN sysobjects Sys2 ON Sys1.parent_obj = Sys2.[id]
WHERE Sys1.xtype='TR'
ORDERBY TableName

在SQL Server 2005里，首选的技术是使用系统视图。这种方式会把你的查询同微软选择对系统表格进行的任何改变隔绝开来。

下面是一个简单的例子，它使用了INFORMATION_SCHEMA_TABLES视图：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROMINFORMATION_SCHEMA.TABLES
ORDERBY TABLE_SCHEMA, TABLE_NAME

对于AdventureWorks数据库或者任何你自己的数据库，使用这个查询可以产生一个表格快速列表。

为了说明这些架构查询的能力，看下面的语句，它们列出所选数据库里所有的函数和存储过程。

SELECT*FROMINFORMATION_SCHEMA.ROUTINES
ORDERBY ROUTINE_TYPE, ROUTINE_NAME

市面上有20种这样的信息架构视图。如果你需要将数据库归档，而且无法承担商业解决方案，例如Red Gate或者Apex提供的方案的话，那么通过这些视图和一点点试验，你就可以让SQL Server给自己生成文档了。

列名	数据类型	描述
name	sysname	对象名。
Id	int	对象标识号。
xtype	char(2)	对象类型。可以是下列对象类型中的一种： C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束（类型是 K） RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束（类型是 K） V = 视图 X = 扩展存储过程
uid	smallint	所有者对象的用户 ID。
info	smallint	保留。仅限内部使用。
status	int	保留。仅限内部使用。
base_schema_ ver	int	保留。仅限内部使用。
replinfo	int	保留。供复制使用。
parent_obj	int	父对象的对象标识号（例如，对于触发器或约束，该标识号为表 ID）。
crdate	datetime	对象的创建日期。
ftcatid	smallint	为全文索引注册的所有用户表的全文目录标识符，对于没有注册的所有用户表则为 0。
schema_ver	int	版本号，该版本号在每次表的架构更改时都增加。
stats_schema_ ver	int	保留。仅限内部使用。
type	char(2)	对象类型。可以是下列值之一： C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 FN = 标量函数 IF = 内嵌表函数 K = PRIMARY KEY 或 UNIQUE 约束 L = 日志 P = 存储过程 R = 规则 RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 V = 视图 X = 扩展存储过程
userstat	smallint	保留。
sysstat	smallint	内部状态信息。
indexdel	smallint	保留。
refdate	datetime	留作以后使用。
version	int	留作以后使用。
deltrig	int	保留。
instrig	int	保留。
updtrig	int	保留。
seltrig	int	保留。
category	int	用于发布、约束和标识。
cache	smallint	保留。

left join、right join、inner join、cross join

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
cross join（交叉连接）没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）
数据准备：
create table test1(id int,name varchar(10))
create table test2 (id int,number varchar(10))
insert into test1 values(1,'test')
insert into test1 values(2,'test2')
insert into test1 values(3,'test3')
insert into test2 values(1,'testb1')
insert into test2 values(3,'testb3')
insert into test2 values(4,'testb4')

select * from test1 join test2 on test1.id=test2.id
------------------
1 test 1 testb1
3 test3 3 testb3
------------------
select * from test1 left join test2 on test1.id=test2.id
------------------
1 test 1 testb1
2 test2 NULL NULL
3 test3 3 testb3
------------------
select * from test1 right join test2 on test1.id=test2.id
------------------
1 test 1 testb1
3 test3 3 testb3
NULL NULL 4 testb4
------------------
select * from test1 cross join test2
或者
select * from test1 , test2
------------------

1 test 1 testb1
2 test2 1 testb1
3 test3 1 testb1
1 test 3 testb3
2 test2 3 testb3
3 test3 3 testb3
1 test 4 testb4
2 test2 4 testb4
3 test3 4 testb4
------------------
在数据库中，union和union all 关键字都是将两个结果集合合并为一个，但这两者从使用和效率上来说都有所不同。

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
select id from test1 union select id from test2
1
2
3
4
select id from test1 union all select id from test2
1
2
3
1
3
4
这个 SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

而UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL

使用存储过程并返回值与及返回值的获得方法

CREATE   TABLE   [ dbo ] . [ Order ] (
[ o_id ]   [ bigint ]   IDENTITY ( 1 , 1 )  NOT   FOR   REPLICATION   NOT   NULL ,
[ o_buyerid ]   [ int ]   NOT   NULL
)
1.OUPUT参数返回值
例： 向Order表插入一条记录,返回其标识

CREATE PROCEDURE [dbo].[nb_order_insert] (
@o_buyerid int  ,
@o_id bigint  OUTPUT
)
AS
BEGIN
SET NOCOUNT ON ;
BEGIN
INSERT INTO [Order] (o_buyerid )
VALUES (@o_buyerid  )
SET @o_id = @@IDENTITY
END
END

存储过程中获得方法：

DECLARE   @o_buyerid   int
DECLARE   @o_id   bigint
EXEC   [ nb_order_insert ]   @o_buyerid  ,o_id  bigint

2.RETURN过程返回值

CREATE PROCEDURE [dbo].[nb_order_insert] (
@o_buyerid int  ,
@o_id bigint  OUTPUT
)
AS
BEGIN
SET NOCOUNT ON ;
IF(EXISTS(SELECT * FROM [Shop] WHERE [s_id] = @o_shopid ))
BEGIN
INSERT INTO [Order] (o_buyerid )
VALUES (@o_buyerid  )
SET @o_id = @@IDENTITY
RETURN 1  — 插入成功返回1
END
ELSE
RETURN 0  — 插入失败返回0
END

存储过程中的获取方法

DECLARE   @o_buyerid   int
DECLARE   @o_id   bigint
DECLARE   @result   bit
EXEC   @result   =   [ nb_order_insert ]   @o_buyerid  ,o_id  bigint  

3.SELECT 数据集返回值

CREATE PROCEDURE

 [dbo].[nb_order_select] (
@o_id int
)
AS
BEGIN
SET NOCOUNT ON ;
SELECT o_id,o_buyerid FROM [Order]
WHERE o_id = @o_id
GO

存储过程中的获取方法
（1）、使用临时表的方法

CREATE   TABLE   [ dbo ] . [ Temp ] (
[ o_id ]   [ bigint ]   IDENTITY ( 1 , 1 )  NOT   FOR   REPLICATION   NOT   NULL ,
[ o_buyerid ]   [ int ]   NOT   NULL
)
INSERT   [ Temp ]   EXEC   [ nb_order_select ]   @o_id
– 这时  Temp  就是EXEC执行SELECT 后的结果集
SELECT   *   FROM   [ Temp ]
DROP   [ Temp ]  — 删除临时表

查询条件模式匹配

搜索条件中的模式匹配

LIKE 关键字搜索与指定模式匹配的字符串、日期或时间值。有关更多信息，请参见数据类型。LIKE 关键字使用常规表达式包含值所要匹配的模式。模式包含要搜索的字符串，字符串中可包含四种通配符的任意组合。

通配符	含义
%	包含零个或更多字符的任意字符串。
_	任何单个字符。
[ ]	指定范围（例如 [a-f]）或集合（例如 [abcdef]）内的任何单个字符。
[^]	不在指定范围（例如 [^a - f]）或集合（例如 [^abcdef]）内的任何单个字符。

请将通配符和字符串用单引号引起来，例如：

• LIKE 'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
  
  
• LIKE '%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
  
  
• LIKE '%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
  
  
• LIKE '_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。
  
  
• LIKE '[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
  
  
• LIKE '[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。
  
  
• LIKE 'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如 MacFeather）。

转载于:https://www.cnblogs.com/JessieDong/archive/2009/07/23/1527729.html