SQL数据修改

最新推荐文章于 2024-09-15 00:04:44 发布

绿龙术士

最新推荐文章于 2024-09-15 00:04:44 发布

阅读量7.9k

点赞数 1

分类专栏：数据库文章标签： SQL语句数据库

本文链接：https://blog.csdn.net/xc917563264/article/details/99826870

版权

数据库专栏收录该内容

41 篇文章 1 订阅

订阅专栏

INSERT语句

INSERT VALUES语句可以将基于指定值的行插入表。

SQL Server2008增强了VALUES语句的功能，允许在一条语句中指定由逗号隔开的多行记录。例如：

INSERT INTO T_Order

VALUES (1,1,'冰箱','2017-01-01'),

(2,1,'洗衣机','2017-01-01'),

(3,1,'电视','2017-01-01')

这个操作是原子操作，这意味这如果有任何一行报错，那么所有行都会插入失败。

SQL Server2008还增强了VALUES子句的功能，现在可以用它来构建虚拟表（virtual table）。这种功能成为行值构造函数（row value contructor），或表值构造函数（table value contructor）。例如：

SELECT * FROM

(VALUES(1,'USA'),(2,'CHI'),(3,'FRA'))

AS O(ID,COUNTRY)

它会生成如下表：

可以使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 高效地将大量行从一个表（例如临时表）传输到按最小方式记录日志的其他表中。按最小方式记录日志可以提高语句的性能，减少在事务期间此操作填充可用事务日志空间的可能性。

INSERT EXEC语句可以把存储过程或动态SQL批处理返回的结果及插入表中

SELECT * INTO <target_table> From <source_table> 语句的作用是创建一个目标表，并用返回的结果填充它。

SELECT INTO它可以复制来源的表的基本结构（包括列名，数据结构，是否允许为NULL及IDENTITY属性）和数据。不过它不会复制索引，触发器，约束，如果需要这些东西，你可以手动添加。

SELECT INTO 的另一个优点是它会按照最小日志记录模式来执行操作，与完整日志模式相比，这种模式执行效率很高。

BULK INSERT语句用于将文件中的数据导入一个已经存在的表。

IDENTITY属性，被它标识的数字类型的列，能在插入数据时自动递增。

如果你想获取这个新生成的标识值（例如：你想获取新增的对象），可以查询@@IDENTITY或者SCOPE_IDENTITY()，推荐使用SCOPE_IDENTITY()因为它会返回当前作用域内会话生成的最后一个标识符，而@@IDENTITY则不会考虑。

删除数据

T-SQL提供两种从表中删除数据行的语句：DELETE和TRUNCATE

DELETE 语句是标准的SQL语句，它用于根据谓词删除表中的数据。

DELETE采用的是完整模式的日志处理，当删除大量数据时，可能会花费大量时间。

TRUNCATE不是标准的SQL语句，它用于删除表中所有数据，与DELETE不同的是，它采取的是最小模式记录日志，在性能上它们有很大差异。例如：删除几百万条数据时，使用TRUNCATE只需要几秒钟，而DELETE则需要几分钟甚至几小时。

当表中有标识列时，TRUNCATE会清除标识种子，而DELETE不会清除。

由于TRUNCATE语句执行速度非常快，因为有时具有一定的危险性。当清空表时搞错了要操作的表，类似的事情时有发生。

为避免发生这样的事故，可以简单地创建一个虚拟表（dummy table），以便于可以及时回滚。

T-SQL支持一种基于联接的语法，由于联接本身具有过滤的作用，因为它有一个基于谓词的过滤器（ON子句）。通过连接可以访问另一个表中相关的属性（列），并在WHERE子句中引用这些属性。

例如：

删除所有中国客户的订单

DELETE FROM O

FROM T_Order AS O

JOIN T_Customer AS C

ON O.CustID = C.CustID

WHERE C.Country = 'China'

也可以改成EXISTS+子查询的形式

DELETE FROM T_Order

WHERE EXISTS(

SELECT * FROM T_Customer AS C

WHERE C.CustID = T_Order.CustID AND C.Country = 'China'

)

修改数据

UPDATE语句是标准的SQL语句，用于对表中数据行的一个子集进行更新。为了标识作为更新的子集行，须要在WHERE子句中指定一个谓词。在SET子句中要指定更改的列和这些列的新值（或表达式），各列之间用逗号分隔。

“同时操作”是SQL表达式中一个重要的特性，在编写UPDATE语句时应该谨记这一点。

例如：

UPDATE T

SET col1 = col1 + 10,col2 = col1 + 10

假如在更新钱col1等于100，col2等于200，你能计算出最终的结果值吗？

如果不考虑同时操作的概念，你可能认为col将等于110，col2等于120。好像赋值表达式是从左到右执行的。但是两个表达式是同时执行的它们都等于110.

用同时操作的思想，如果想交换两个变量的值该怎么操作呢？在大多数编程语言中，得需要一个临时变量。然而在SQL中这个问题非常简单：

UPDATE T

SET col1 = col2,col2 = col1

和DELETE一样，T-SQL也支持一种基于联接的UPDATE语法，这不是标准的SQL语法。和基于联接的DELETE语句一样，联接在此也起到过滤的作用。

UPDATE还支持一种特殊语法，可以对表中的数据更新的同时为变量赋值，这种语法可以使你不必单独使用UPDATE和SELECT语句，就能完成同样的任务。

例如：

declare @i AS int = 123;

update T_Customer

set @i = CustID = CustID + 1

select @i

合并数据

SQLServer2008引入了一个称为MERGE的语句，它在一条语句根据逻辑条件做出不同的修改操作（INSERT,UPDATE,DELETE），使用MERGE的好处是：用较少的代码就可以表达需求，提高查询性能，因为它可以更少地查询设计表。

在MERGE子句中指定目标表的名称，在USING子句中指定来源表的名称。然后在ON子句中用谓词来定义合并条件，合并条件用来定义来源表中哪些行在目标表中有匹配，哪些没有匹配。

我们可以在WHEN MATCHED THEN子句中定义找到匹配行时执行的操作，也可以在WHEN NOT MATCHED THEN子句中定义没有找到匹配行时要进行的操作。

例如：

--MERGE语句

MERGE T_Employee AS e1

USING T_Employee2 AS e2

ON e1.Empid = e2.Empid

WHEN Matched THEN    --以USING的表为准，修改MERGE的表

UPDATE SET

e1.FirstName = e2.FirstName,e1.LastName = e2.LastName,e1.Country = e2.Country

WHEN NOT Matched THEN   --以USING的表为准，新增MERGE的表

INSERT(FirstName,LastName,Country) VALUES

(e2.FirstName,e2.LastName,e2.Country)

WHEN NOT MATCHED BY SOURCE THEN  --以USING的表为准，删除MERGE的表

UPDATE SET

e1.IsDelete = 1;

T-SQL还支持第三种子句：WHEN NOT MATCHED BY SOURCE它用于当来源表中没有行对应目标表时，一般可以采取删除操作。

我们还可以在WHEN MATCHED后面添加AND来为操作子句增加谓词条件。

带有TOP的数据更新

SQLServer2005新增了数据修改语句（INSERT,UPDATE,DELETE）中使用TOP选项功能。SQLServer2008则把TOP选项支持拓展到新增的MERGE语句中。

它的作用是，当已经处理的行达到指定数量或百分比后，会立即停止处理修改语句。可是与SELECT语句不同，不能为修改语句中的TOP语句指定ORDER BY子句。然而我们可以使用表表达式来解决这个问题。

例如：删除最后50条数据

WITH C AS

(

SELECT TOP 50 *

FROM NUMS

ORDER BY n DESC

)