教程：编写 Transact-SQL 语句

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

第 1 课：创建数据库对象

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

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

• 通过使用 SQL Server Management Studio。本教程假定您使用的是 Management Studio，但是也可以使用 Management Studio Express（可以从 Microsoft Download Center （Microsoft 下载中心）免费下载）。

• 通过使用 sqlcmd 实用工具。

• 通过从您创建的应用程序进行连接。

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

若要在 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 数据库

  • 在查询编辑器窗口中，键入以下代码，并执行它以更改与 TestData 数据库的连接。

    复制代码

    USE TestData
    GO

  创建表

  • 在查询编辑器窗口中，键入以下代码，并执行它以创建一个名为 Products 的简单表。该表中各列的名称为 ProductID、ProductName、Price 和 ProductDescription。ProductID 列是表的主键。int、varchar(25)、money 和 text 都是数据类型。当插入或更改行时，只有 Price 和 ProductionDescription 列可以不包含数据。此语句包含称为架构的可选元素 (dbo.)。架构是拥有表的数据库对象。如果您是管理员，则 dbo 是默认架构。dbo 代表数据库所有者。

    复制代码

    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 子句，以限制对单个行的更新。这四条语句将输入以下数据。

  ProductID	ProductName	价格	ProductDescription
  1	Clamp	12.48	Workbench clamp
  50	Screwdriver	3.17	Flat head
  75	Tire Bar		Tool for changing tires.
  3000	3mm Bracket	.52

  基本语法如下：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 语句，将第二种产品的 ProductName 从 Screwdriver 更改为 Flat Head Screwdriver。

    复制代码

    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 
     
     
     

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

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

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

     本课程包含以下主题：

     • 创建登录名

     • 授予访问数据库的权限

     • 创建视图和存储过程

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

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

  创建登录名

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

  创建视图和存储过程

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

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

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

  创建视图

  • 执行以下语句创建一个非常简单的视图，该视图执行 Select 语句，并将产品的名称和价格返回给用户。

    复制代码

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

  测试视图

  • 视图的处理方式与表类似。使用 SELECT 语句访问视图。

    复制代码

    SELECT * FROM vw_Names;
    GO
    

  创建存储过程

  • 以下语句创建一个名为 pr_Names 的存储过程，接受名为 @VarPrice、数据类型为 money 的输入参数。该存储过程打印与输入参数（已从 money 数据类型更改为 varchar(10) 字符数据类型）串联的语句 Products less than。然后，该存储过程对视图执行 SELECT 语句，将输入参数作为 WHERE 子句的一部分进行传递。这将返回价格小于输入参数值的所有产品。

    复制代码

    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 
    
    
    

  测试存储过程

  • 若要测试存储过程，请键入并执行以下语句。存储过程应该返回在第 1 课中向 Products 表中输入的、其价格小于 10.00 的两个产品的名称。

    复制代码

    EXECUTE pr_Names 10.00;
    GO
    

  
  

• 授予访问数据库的权限

  现在 Mary 具有访问此 SQL Server 实例的权限，但是没有访问数据库的权限。在您授权她作为数据库用户之前，她甚至无权访问其默认数据库 TestData。

  若要授予 Mary 访问权限，请切换到 TestData 数据库，再使用 CREATE USER 语句将她的登录名映射到名为 Mary 的用户。

  在数据库中创建用户

  • 键入并执行下列语句（将 computer_name 替换为您计算机的名称），以授予 Mary 访问 TestData 数据库的权限。

    复制代码

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

    现在，对于 SQL Server 和 TestData 数据库，Mary 都具有访问权限。

  删除数据库对象

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

  删除权限和对象

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