深入DML

DML(Data Management Language, 数据管理定义)语句处理数据,可以删除数据,插入数据,增加数据。列出数据。
INSERT:
有四种基本的格式,先是一种最简单的格式:
INSERT INTO #famousjaycees VALUES('Julius')
DEFAULT和NULL
为了在有默认值约束的列中插入一个被赋有默认值对象的默认值,其中默认对象允许为NULL。那么使用关键字DEFAULT来代替实际值。使用关键字NULL,可以为一个允许为NULL的列准确地指定为NULL值,如果为一个不许为NULL的列指定为NULL(或者为一个没有默认值的NOT NULL的列指定为DEFAULT)那么INSERT将失败。
命令的第二种格式允许同时为所有列指定哪个默认值。
INSERT INTO targetable DEFAULT VALUES
命令的第三种格式可以检索出用SELECT语句查表出的值。
INSERT #famousjaycees2
SELECT * FROM #famousjaycess
INSERT命令第四种格式允许结果集由存储过程或嵌在表中的SELECT语句来返回。
INSERT #Sp_Who EXEC SP_WHO
INSERT和错误:
INSERT命令的一个有趣的特性就是批命令错误的密封性。由于约束或无效的复制值而引起的INSERT失败不会引起批命令失败。
如果希望有一个INSERT失败时整个批命令失败,那么就在每个INSERT之后检查自动变量@@ERROR并做出相应的反应。例如:
INSERT #famousjaycees VALUES ('Julis')
IF (@@ERROR <> 0)
GOTO LIST
用INSERT命令来删除重复行
还有一个相关的事项,也是INSERT命令的另一个有趣方面,它可以通过IGNORE_DUP_KEY设定惟一索引的办法来删除重复行。如果在表中插入一系列的以IGNORE_DUP_KEY为索引的行,那么破坏索引的惟一约束的行将被拒绝,但是其他行会失败,为了删除表中的中复行,可以创建一个结构上与原表相同的工作表,然后再第二个表上创建IGNORE_DUP_KEY索引,其中包括第一个表中所有的候选关键字,然后将其插入到第一个表中。
成批插入:
除了标准的INSERT之外,T-SQL还提供了BULK INSERT命令来进行大量数据的载入。
例如:
BULK INSERT famousjaycees FROM 'D:\GG_TS\famousjaycees.bcp'
成批插入和触发器:
当使用BULK INSERT命令插入进行时,INSERT触发器不会被触发。
成批插入和约束:
说明约束可通过使用BULK INSERT的CHECK_CONSTRAINTS选项来执行。默认情况下,除了UNIQUE约束之外,目标表的其他约束都将被忽略,所以如果想在大量数据操作的同时坚持这约束,那么就要包括这个选项。注意,这样会大幅度地降低操作速度。
成批插入和同一性列
BULK INSERT的另一个突出的特点是,在默认情况下,载入数据时,将重新产生同一性列的值。很显然,如果将数据载入到一个有外部关键字引用的表中,那么后果是不堪想象的。为了防止这种情况,需要包括BULK INSERT 的关键词KEEPIDEBTITY。
UPDATE:
UPDATE有两种基本格式,一种是用静态来修改表,另一种是用其他表中的数据来修改表。
第一种格式:
UPDATE #famousjaycees
SET jc = 'Johnny'
WHERE jc = 'Joh'
第二种格式:
UPDATE f
SET jc = s.jc
FROM #famousjaycees f
JOIN #semifamousjaycees s ON (f.becamefamous = s.becamefamous)
SQL Server的触发器每个语句被触发一次,而不是每行被触发一次,并且触发器只有在数据修改之前或之后能够进行存取。而不是在数据修改过程中的中间时期进行存取,触发器的代码不会和触发他们的INSERT,UPDATE,DELETE命令一起编译进执行计划,而是独立地编译并放入缓存区,所以无论触发它的命令是什么,都可以有效地重复使用。DML语句的执行计划分支给它激发的所有触发器,这种操作时在执行计划结束之前进行的,如果是结束之后就无法完成了。
这种情况并不适用于约束,每个表的约束都是直接加入DML的执行计划中。
用UPDATE检测约束:
如果使用BULK INSERT或其他大批量的载入工具来对有INSERT触发器的表进行追加数据,那么你会发现触发器不能被触发,在载入数据结束之后,马上再对表进行一个假的UPDATE操作。这个假的修改操作只是简单地将列值置为其本身的值。这样就会触发触发器并对约束进行检查。如果其中有包含错误数据的行,那么
UPDATE失败。例如:
BULK INSERT famousjaycees FROM 'D:\GG_TS\famousjaycees.bcp'
SELECT *  FROM famousjaycees
UPDATE famousjaycees
SET jc= jc
限制受UPDATE命令的TOP n的选项,可以限制受UPDATE影响的行数的数目。SELECT语句作为导出表嵌入在UPDATE的FROM子句中,并与目标表进行连接,例如:
SELECT TOP 10 au_lanme, au_fname, contract FROM authors ORDER BY au_id
UPDATE a
SET a.contract = 0
FROM authors a join (SELECT TOP 5 au_id FROM authors ORDER BY au_id) u ON (a.au_id = u.au_id)
用UPDATE交换列值:
因为UPDATE语句引用的列值通常在操作之前,就已经得到了本身的值了,所以交换值时并不需要中间变量,这样就可以简单地得将一列赋给其他列了,如下:
UPDATE #sample SET samp1 = samp2, samp2 = samp1
UPDATE和游标:
可用UPDATE命令来修改那些由可修改游标返回的行,这种可能是通过UPDATE的WHERE CURRENT OF语句来实现的.例如:
DECLARE jcs Cursor DYNAMIC FOR SELECT * FROM #famousjaycees FOR UPDATE
OPEN jcs
FETCH RELATIVE 3 FROM jcs
UPDATE  #famousjaycees
SET jc = 'johnny'
WHERE CURRENT OF jcs
CLOSE jcs
DEALLOCATe jcs
DELETE:
DELETE命令似乎是与INSERT相对的,DELETE除了可以通过WHERE 子句中使用约束和变量来限制被删除的行之外,还可以引用其他表。下面例子中的DELETE语句就是基于与其他表的连接的。例子删除了Northwind Customers表中的在Order表中没有订单的客户:
DELETE c
FROM Customer c LEFT OUTER JOIN order o ON (c.CustomerID = o.CustomerID)
WHER o.OrderID IS NULL
与UPDATE命令一样,受DELETE影响行数可以通过SELECT TOP n来限制。
例如:
DELETE s
FROM sales s JOIN (SELECT TOP 5 ord_num FROM sales ORDER BY ord_num) a ON (s.ord_num = a.ord_num)
DELETE和游标:
使用DELETE命令可以删除由可修改游标返回的行。与UPDATE相似,这个功能是通过WHERE CURRENT OF子句来实现。
DECLARE jcs Cursor DYNAMIC FOR SELECT * FROM #famousjaycees
FOR UPDATE
OPEN jcs
FETCH RELATIVE 3 FROM jcs
DELETE #famousjaycees WHERE CURRENT OF jcs
CLOSE jcs
DEALLOCATE jcs
检测DML错误:
通常可以通过检查自动变量@@ERROR来检测DML运行时的错误。然而,如果DML语句对任何行都没有影响,那么@@ERROR将没有值,因为技术上并不存在错误条件,所以就必须检查@@ROWCOUNT

以上内容是《Transact-SQL权威指南》一书的读书笔记,感谢作者KEN HENDERSON 和 译者 健莲科技 中国电力出版社 为我带来这么经典的T-SQL书籍。
posted @ 2006-12-26 21:42  斯伯内德  阅读(...)  评论(...编辑  收藏