INSERT SELECT UPDATE 删除数据操作语言的基础知识
D ata M anipulation L anguage(简称 DML)包括 INSERT
,UPDATE
和 DELETE
等操作:
-- 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
的所有列以及 Region
的 RegionDescription
列。结果由 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 将 FirstName
和 LastName
从 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
在这种情况下,将返回所有 Northern
和 Southern
地区。更多详细信息可以在这里找到。如果你有兴趣阅读有关该主题的更多信息,请在此处讨论临时表。
注意: 如果表变量中的数据行数小于 100,Microsoft 仅建议使用表变量。如果要处理大量数据,请使用临时表或临时表。