INSERT SELECT UPDATE 删除数据操作语言的基础知识

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

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

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

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

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

https://i.stack.imgur.com/6xeX4.jpg

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

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

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

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

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

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

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

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

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

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

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

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

加盟

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

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

将显示 Territories 的所有列以及 RegionRegionDescription 列。结果由 TerritoryDescription 订购。

表别名

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

<TableName> [as] <alias>

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

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

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

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

联合

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

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

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

表变量

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

例:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

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

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

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

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

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

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

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

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