Microsoft SQL Server 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

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

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

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

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

Northwind数据库查询

注意 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将Employee中的FirstNameLastName组合成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仅建议使用表变量。如果要处理大量数据,请使用临时表或临时表。