教程：编写Transact-SQL语句

第 1 课：创建数据库对象 本课将介绍如何创建数据库，在数据库中创建表，然后访问表中的数据并对其进行更改。由于本课是对使用 Transact-SQL 的简介，因此它未使用或说明为这些语句提供的许多选项。 可以使用下列方法编写 Transact-SQL 语句并将其提交到数据

本课将介绍如何创建数据库，在数据库中创建表，然后访问表中的数据并对其进行更改。由于本课是对使用 Transact-SQL 的简介，因此它未使用或说明为这些语句提供的许多选项。

可以使用下列方法编写 Transact-SQL 语句并将其提交到数据库引擎：

代码以相同方式和相同权限在数据库引擎上执行，而不管您如何提交代码语句。

若要在 Management Studio 中运行 Transact-SQL 语句，请打开 Management Studio 并连接到 SQL Server 数据库引擎 的实例。如果您不熟悉 Management Studio，请查看 教程中的前两项任务。

与许多 Transact-SQL 语句一样，CREATE DATABASE 语句具有一个必需参数：数据库的名称。CREATE DATABASE 还具有许多可选参数，如希望放置数据库文件的磁盘位置。在您执行不带可选参数的 CREATE DATABASE 时，SQL Server 使用其中许多参数的默认值。本教程使用的可选语法参数非常少。

创建数据库

1. 在查询编辑器窗口中，键入以下代码，但不要执行它：

   CREATE DATABASE TestData
   GO

2. 使用指针选择词语 CREATE DATABASE，再按 F1。应该会打开 SQL Server 联机丛书中的 CREATE DATABASE 主题。您可以使用此方法查找 CREATE DATABASE 以及在本教程中使用的其他语句的完整语法。

3. 在查询编辑器中，按 F5 以执行语句并创建名为 TestData 的数据库。

在您创建数据库时，SQL Server 制作 model 数据库的副本，并将该副本重命名为数据库名称。除非您将初始大小很大的数据库指定为可选参数，否则此操作应该只需要几秒钟。

注意
在单个批处理中提交多条语句时，可以用关键字 GO 分隔各语句。当批处理只包含一条语句时，GO 是可选的。

创建表（教程）

若要创建表，您必须提供该表的名称以及该表中每个列的名称和数据类型。指出每个列中是否允许空值，也是一种很好的做法。

大多数表有一个主键，主键由表的一列或多列组成。主键始终是唯一的。数据库引擎 将强制实施以下限制：表中的任何主键值都不能重复。

有关数据类型的列表以及每种数据类型的说明的链接，请参阅数据类型 (Transact-SQL)。

注意
数据库引擎可安装为区分大小写或不区分大小写。如果数据库引擎区分大小写进行安装，则对象名必须始终具有相同的大小写。例如，名为 OrderData 的表与名为 ORDERDATA 的表是不同的表。如果数据库引擎按不区分大小写进行安装，则这两个表名被视为同一个表，而且该名称只能使用一次。

创建数据库以包含新表

USE master;
GO

--Delete the TestData database if it exists.
IF EXISTS(SELECT * from sys.databases WHERE name='TestData')
BEGIN
 DROP DATABASE TestData;
END

--Create a new database called TestData.
CREATE DATABASE TestData;
--Press the F5 key to execute the code and create the database.

将查询编辑器连接切换到 TestData 数据库

USE TestData
GO

创建表

CREATE TABLE dbo.Products
 (ProductID int PRIMARY KEY NOT NULL,
 ProductName varchar(25) NOT NULL,
 Price money NULL,
 ProductDescription text NULL)
GO

您已经创建 Products 表，可以通过使用 INSERT 语句向该表中插入数据了。插入数据后，将通过使用 UPDATE 语句更改行的内容。您将使用 UPDATE 语句的 WHERE 子句，以限制对单个行的更新。这四条语句将输入以下数据。

基本语法如下：INSERT、表名、列的列表、VALUES，然后是要插入的值的列表。如果某行的前面有两个连字符，则指示该行为注释，编译器将忽略其文本。在这种情况下，注释说明允许的语法变体。

向表中插入数据

1. 执行以下语句，将一行插入到在上一个任务中创建的 Products 表中。基本语法如下：

   -- Standard syntax
   INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
    VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
   GO
   

2. 以下语句显示如何通过在字段列表（在圆括号中）中和值列表中均切换 ProductID 和 ProductName 的位置，更改提供参数的顺序。

   -- Changing the order of the columns
   INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
    VALUES ('Screwdriver', 50, 3.17, 'Flat head')
   GO
   

3. 以下语句演示，只要值是按正确顺序列出的，列的名称就是可选的。此语法很常见，但是建议不要使用它，因为其他人了解您的代码可能会更困难。为 Price 列指定了 NULL，因为还不知道此产品的价格。

   -- Skipping the column list, but keeping the values in order
   INSERT dbo.Products
    VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
   GO
   

4. 只要在默认架构中访问和更改表，架构名称就是可选的。由于 ProductDescription 列允许 Null 值，而且没有提供值，因此可以从语句中完全删除 ProductDescription 列的名称和值。

   -- Dropping the optional dbo and dropping the ProductDescription column
   INSERT Products (ProductID, ProductName, Price)
    VALUES (3000, '3mm Bracket', .52)
   GO

更新 products 表

UPDATE dbo.Products
 SET ProductName = 'Flat Head Screwdriver'
 WHERE ProductID = 50
GO

使用 SELECT 语句可以读取表中的数据。SELECT 语句是最重要的 Transact-SQL 语句之一，其语法有许多变体。在本教程中，您将使用五个简单版本。

读取表中的数据

1. 键入并执行以下语句以读取 Products 表中的数据。

   -- The basic syntax for reading data from a single table
   SELECT ProductID, ProductName, Price, ProductDescription
    FROM dbo.Products
   GO
   

2. 您可以使用星号选择表中的所有列。这通常用于即席查询中。您应该在永久代码中提供列的列表，以便语句将返回预测列，即使稍后将新列添加到表中也是如此。

   -- Returns all columns in the table
   -- Does not use the optional schema, dbo
   SELECT * FROM Products
   GO
   

3. 可以省略不希望返回的列。列将按列出它们的顺序返回。

   -- Returns only two of the columns from the table
   SELECT ProductName, Price
    FROM dbo.Products
   GO
   

4. 使用 WHERE 子句可以限制返回给用户的行。

   -- Returns only two of the records in the table
   SELECT ProductID, ProductName, Price, ProductDescription
    FROM dbo.Products
    WHERE ProductID < 60
   GO
   

5. 您可以在返回列中的值时使用它们。以下示例对 Price 列执行数学运算。除非通过使用 AS 关键字提供一个名称，否则以此方式更改的列将没有名称。

   第 2 课：配置数据库对象的权限

   授予用户访问数据库的权限涉及三个步骤。首先，创建登录名。使用登录名，用户可以连接到SQL Server 数据库引擎。然后将登录名配置为指定数据库中的用户。最后，授予该用户访问数据库对象的权限。本课介绍了这三个步骤，并介绍了如何将视图和存储过程创建为对象。

   本课程包含以下主题：

6. 创建登录名

7. 授予访问数据库的权限

8. 创建视图和存储过程

9. 授予访问数据库对象的权限

10. 摘要:配置数据库对象的权限

若要访问数据库引擎，用户需要有登录名。登录名可以按 Windows 帐户或 Windows 组成员表示用户身份，登录名也可以是仅存在于 SQL Server 中的 SQL Server 登录名。应该尽可能使用 Windows 身份验证。

既然 Mary 可以访问 TestData 数据库，您可能希望创建一些数据库对象（如视图和存储过程），再将它们的访问权限授予 Mary。视图是存储的 SELECT 语句，而存储过程是以批处理方式执行的一条或多条 Transact-SQL 语句。

视图像表那样进行查询，但不接受参数。存储过程比视图更复杂。存储过程可以同时具有输入参数和输出参数，并可以包括控制代码流的语句，如 IF 和 WHILE 语句。将存储过程用于数据库中的所有重复操作，是一个良好的编程做法。

在此示例中，您将使用 CREATE VIEW 创建一个视图，该视图仅选择 Products 表中的两列。然后，您将使用 CREATE PROCEDURE 创建一个存储过程，该存储过程接受价格参数，并仅返回价格小于指定参数值的那些产品。

创建视图

CREATE VIEW vw_Names
 AS
 SELECT ProductName, Price FROM Products;
GO

测试视图

SELECT * FROM vw_Names;
GO

创建存储过程

CREATE PROCEDURE pr_Names @VarPrice money
 AS
 BEGIN
 -- The print statement returns text to the user
 PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
 -- A second statement starts here
 SELECT ProductName, Price FROM vw_Names
 WHERE Price < @varPrice;
 END
GO

测试存储过程

EXECUTE pr_Names 10.00;
GO

USE [TestData];
GO

CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO

若要删除在本教程中创建的所有对象，您只需删除数据库即可。但是，在本主题中，您将完成下列步骤执行与教程中每项操作相反的操作。

删除权限和对象

1. 在删除对象之前，请确保使用正确的数据库：

   USE TestData;
   GO

2. 使用 REVOKE 语句删除 Mary 对存储过程的执行权限：

   REVOKE EXECUTE ON pr_Names FROM Mary;
   GO
   

3. 使用 DROP 语句删除 Mary 对 TestData 数据库的访问权限：

   DROP USER Mary;
   GO
   

4. 使用 DROP 语句删除 Mary 对此 SQL Server 2005 实例的访问权限。

   DROP LOGIN [\Mary];
   GO
   

5. 使用 DROP 语句删除存储过程 pr_Names：

   DROP PROC pr_Names;
   GO
   

6. 使用 DROP 语句删除视图 vw_Names：

   DROP View vw_Names;
   GO
   

7. 使用 DELETE 语句删除 Products 表中的所有行：

   DELETE FROM Products;
   GO
   

8. 使用 DROP 语句删除 Products 表：

   DROP Table Products;
   GO
   

9. 正使用 TestData 数据库时，无法删除该数据库；因此，请首先将上下文切换到其他数据库，再使用 DROP 语句删除 TestData 数据库：

   USE MASTER;
   GO
   DROP DATABASE TestData;
   GO
   

“编写 Transact-SQL 语句”教程到此结束。请记住，本教程只是简要概述，它并未介绍所用语句的所有选项。设计和创建有效的数据库结构以及配置对数据的安全访问，需要比本教程中显示的数据库更复杂的数据库。