Microsoft SQL Server入门

Download sql-server eBook

备注

这是一组突出显示SQL Server基本用法的示例。

版本

发布日期
SQL Server 2016 2016年6月1日
SQL Server 2014 2014年3月18日
SQL Server 2012 2011-10-11
SQL Server 2008 R2 2010-04-01
SQL Server 2008 2008-08-06
SQL Server 2005 2005-11-01
SQL Server 2000 2000-11-01

代码中的评论

Transact-SQL支持两种形式的注释编写。数据库引擎会忽略注释,供人们阅读。

注释前面带有-- 并且在遇到新行之前被忽略:

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;
 

斜杠明星评论/* 开头,以*/ 结尾。这些分隔符之间的所有文本都被视为注释块。

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;
 

如果SQL语句丢失了新的行字符,则斜杠明星注释的优点是可以保留注释。在故障排除期间捕获SQL时会发生这种情况。

斜线星形注释可以嵌套,并且斜线星形注释中的起始/* 需要以*/ 结束才能生效。以下代码将导致错误

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;
 

即使在报价内部,斜线明星也被视为评论的开始。因此,它需要以另一个收盘明星斜线结束。正确的方法是

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;
 

创建新表并从旧表中插入记录

SELECT * INTO NewTable FROM OldTable
 

创建一个包含旧表结构的新表,并将所有行插入到新表中。

一些限制

  1. 您不能将表变量或表值参数指定为新表。
  2. 即使源表已分区,也无法使用SELECT ... INTO创建分区表。 SELECT ... INTO不使用源表的分区方案;相反,新表是在默认文件组中创建的。要将行插入分区表,必须首先创建分区表,然后使用INSERT INTO ... SELECT FROM语句。
  3. 源表中定义的索引,约束和触发器不会传输到新表,也不能在SELECT ... INTO语句中指定。如果需要这些对象,则可以在执行SELECT ... INTO语句后创建它们。
  4. 指定ORDER BY子句不保证按指定顺序插入行。当稀疏列包含在选择列表中时,稀疏列属性不会传输到新表中的列。如果新表中需要此属性,请在执行SELECT ... INTO语句后更改列定义以包含此属性。
  5. 当计算列包含在选择列表中时,新表中的相应列不是计算列。新列中的值是SELECT ... INTO执行时计算的值。

[ sic ]

删除所有行

DELETE
FROM Helloworlds
 

这将删除表中的所有数据。运行此代码后,该表将不包含任何行。与DROP TABLE 不同,这会保留表本身及其结构,您可以继续在该表中插入新行。

删除表中所有行的另一种方法是截断它,如下所示:

DELETE
FROM Helloworlds
 

与DELETE操作的区别有几个:

  1. 截断操作不存储在事务日志文件中
  2. 如果存在IDENTITY 字段,则将重置此值
  3. TRUNCATE可以应用于整个表,而不是部分应用(而使用DELETE 命令可以关联WHERE 子句)

TRUNCATE的限制

  1. 如果存在FOREIGN KEY 引用,则无法对表进行TRUNCATE
  2. 如果表格参与了INDEXED VIEW
  3. 如果使用TRANSACTIONAL REPLICATIONMERGE REPLICATION 发布该表
  4. 它不会触发表中定义的任何TRIGGER

[原文如此]

获得表行计数

如果table_name 替换为您要查询的表,则可以使用以下示例查找数据库中特定表的总行数:

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;
 

通过使用以下脚本基于表的HEAP(index_id = 0)或集群聚簇索引(index_id = 1)连接回表的分区,还可以获取所有表的行计数:

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;
 

这是可能的,因为每个表本质上都是一个分区表,除非向其添加额外的分区。该脚本还具有不干扰对表行的读/写操作的好处。

INSERT / SELECT / UPDATE / DELETE:数据操作语言的基础知识

D ata M anipulation L anguage(简称DML)包括INSERTUPDATEDELETE

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

在这个脚本中,我们创建了一个表来演示一些基本查询。

以下示例显示了如何查询表:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

将选择Customer 表的前10条记录,这些记录由Northwind 数据库中的CompanyName 列排序(这是Microsoft的示例数据库之一,可以从此处下载):

Northwind数据库查询

注意 Use Northwind; 更改所有后续查询的默认数据库。您仍然可以使用[Database]形式的完全限定语法来引用数据库。[Schema]。[Table]:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

如果您要查询来自不同数据库的数据,这将非常有用。请注意,在“之间”指定的dbo 称为模式,需要在使用完全限定语法时指定。您可以将其视为数据库中的文件夹。 dbo 是默认架构。可以省略默认模式。需要指定所有其他用户定义的模式。

如果数据库表包含名为保留字的列,例如Date ,则需要将列名括在括号中,如下所示:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

如果列名在其名称中包含空格(不建议这样做),则同样适用。另一种语法是使用双引号而不是方括号,例如:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

相当但不常用。注意双引号和单引号之间的区别:单引号用于字符串,即

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

是一种有效的语法。请注意,T-SQL具有NChar和NVarchar数据类型的N 前缀,例如

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

返回所有公司名称以AL 开头的公司( % 是外卡,使用它,就像在DOS命令行中使用星号一样,例如DIR AL* )。对于LIKE ,有几个可用的通配符,请查看此处以了解更多详细信息。

加盟

如果要查询一个表中不存在但在多个表中不存在的字段,则联接很有用。例如:您想查询Northwind 数据库中Region 表中的所有列。但是您注意到还需要RegionDescription ,它存储在另一个表Region 。但是,有一个公共密钥RgionID 可用于在单个查询中组合此信息,如下所示( Top 5 只返回前5行,省略它以获取所有行):

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

将显示Territories 所有列以及RegionRegionDescription 列。结果按TerritoryDescription 排序。

表别名

当您的查询需要引用两个或更多表时,您可能会发现使用表别名很有用。表别名是对可用于代替完整表名的表的简写引用,并且可以减少键入和编辑。使用别名的语法是:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

as 是一个可选关键字。例如,以前的查询可以重写为:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

对于查询中的所有表,别名必须是唯一的,即使您使用同一个表两次。例如,如果您的Employee表包含SupervisorId字段,则可以使用此查询返回员工及其主管的姓名:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

工会

正如我们之前看到的,Join会添加来自不同表源的列。但是,如果要组合来自不同来源的行,该怎么办?在这种情况下,您可以使用UNION。假设您正计划参加一个聚会,并且不仅希望邀请员工,还希望邀请客户。然后你可以运行这个查询来做到这一点:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

它将在一个表中返回员工和客户的姓名,地址和城市。请注意,重复的行(如果应该有的话)会自动消除(如果您不想这样做,请改用UNION ALL )。列号,列名,顺序和数据类型必须匹配所有属于联合的select语句 - 这就是第一个SELECT将Employee中的FirstNameLastName 组合成ContactName

表变量

如果您需要处理临时数据(特别是在存储过程中),使用表变量可能很有用:“真实”表和表变量之间的区别在于它只存在于内存中以进行临时处理。

例:

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

在内存中创建一个表。在这种情况下, @ 前缀是必需的,因为它是一个变量。您可以执行上面提到的所有DML操作来插入,删除和选择行,例如

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

但通常情况下,你会根据真实的表格来填充它

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

这将读取真实表dbo.Region 的过滤值并将其插入到内存表@Region - 它可以用于进一步处理。例如,您可以在类似的连接中使用它

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld
 

在这种情况下,将返回所有NorthernSouthern 地区。更多详细信息可以在这里找到。如果您有兴趣阅读有关该主题的更多信息,请在此处讨论临时表。

注意:如果表变量中的数据行数小于100,Microsoft仅建议使用表变量。如果要处理大量数据,请使用临时表或临时表。

打印

向输出控制台显示消息。使用SQL Server Management Studio,它将显示在消息选项卡中,而不是结果选项卡中:

PRINT 'Hello World!';
 

检索基本服务器信息

SELECT @@VERSION
 

返回在实例上运行的MS SQL Server的版本。

SELECT @@VERSION
 

返回MS SQL Server实例的名称。

SELECT @@VERSION
 

返回MS SQL Server正在运行的Windows服务的名称。

SELECT @@VERSION
 

返回运行SQL Server的计算机的物理名称。用于标识故障转移群集中的节点。

SELECT @@VERSION
 

在故障转移群集中,返回可运行SQL Server的每个节点。如果不是集群,它什么也不返回。

从表中选择所有行和列

句法:

SELECT *
FROM table_name
 

使用星号运算符* 可用作选择表中所有列的快捷方式。还将选择所有行,因为此SELECT 语句没有WHERE 子句,以指定任何过滤条件。

如果您向表中添加别名,这也会以相同的方式工作,例如在这种情况下为e

SELECT *
FROM table_name
 

或者,如果要从特定表中选择全部,可以使用别名+“。*”:

SELECT *
FROM table_name
 

也可以使用完全限定名称访问数据库对象:

SELECT *
FROM table_name
 

不一定建议这样做,因为更改服务器和/或数据库名称会导致使用完全限定名称的查询由于无效的对象名称而不再执行。

请注意,如果查询分别在单个服务器,数据库和模式上执行,则在许多情况下可以省略table_name 之前的字段。但是,数据库通常具有多个模式,在这些情况下,在可能的情况下不应省略模式名称。

警告:在生产代码或存储过程中使用SELECT * 可能会导致以后出现问题(因为新列被添加到表中,或者如果列重新排列在表中),特别是如果您的代码对列的顺序做出简单的假设,或返回的列数。因此,总是在SELECT语句中为生产代码显式指定列名更安全。

SELECT *
FROM table_name
 

选择与条件匹配的行

通常,语法是:

SELECT <column names>
FROM <table name>
WHERE <condition>
 

例如:

SELECT <column names>
FROM <table name>
WHERE <condition>
 

条件可能很复杂:

SELECT <column names>
FROM <table name>
WHERE <condition>
 

TRUNCATE TABLE

TRUNCATE TABLE Helloworlds 
 

此代码将删除表Helloworlds中的所有数据。截断表几乎类似于Delete from Table 代码。区别在于你不能在带有Truncate的where子句中使用。 Truncate表被认为比删除更好,因为它使用较少的事务日志空间。

请注意,如果存在标识列,则会将其重置为初始种子值(例如,自动递增的ID将从1重新启动)。如果标识列用作另一个表中的外键,则可能导致不一致。

更新所有行

一种简单的更新形式是递增表格给定字段中的所有值。为此,我们需要定义字段和增量值

以下是将Score 字段增加1(在所有行中)的示例:

UPDATE Scores
SET score = score + 1  
 

这可能很危险,因为如果您不小心对表中的所有行的UPDATE进行特定行的更新,则可能会损坏您的数据。

更新特定行

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5
 

上面的代码用“HELLO WORLD !!!”更新字段“HelloWorld”的值对于HelloWorlds表中“Id = 5”的记录。

注意:在update语句中,建议使用“where”子句以避免更新整个表,除非并且直到您的要求不同。

使用事务安全地更改数据

无论何时更改数据,都可以在数据操作语言(DML)命令中将更改包装在事务中。 DML包括UPDATETRUNCATEINSERTDELETE 。您可以确保更改正确数据的方法之一是使用事务。

DML更改将锁定受影响的行。当您开始交易时,您必须结束交易,否则DML中所有正在更改的对象将由开始交易的人保持锁定状态。您可以使用ROLLBACKCOMMIT 结束事务。 ROLLBACK 将事务中的所有内容返回到其原始状态。 COMMIT 将数据置于最终状态,在没有其他DML语句的情况下,您无法撤消更改。

例:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction
 

笔记:

  • 这是一个简化的示例 ,不包括错误处理。但任何数据库操作都可能失败,因此抛出异常。 下面是一个示例,说明这种必需的错误处理方式。您不应该在没有 错误处理程序的情况下使用事务,否则您可能会使事务处于未知状态。
  • 根据隔离级别 ,事务会对要查询或更改的数据进行锁定。您需要确保事务长时间不运行,因为它们将锁定数据库中的记录,并可能导致与其他并行运行事务的死锁 。保持封装在事务中的操作尽可能短,并最大限度地减少对锁定的数据量的影响。

Stats

2257 Contributors: 63
Sunday, August 6, 2017
许可下: CC-BY-SA

不隶属于 Stack Overflow
Rip Tutorial: info@zzzprojects.com

下载电子书