入门SQL Server必备：T-SQL语句的编写入门

一、背景

本文主要面向对编写 SQL 语句不熟悉的用户。 通过创建表和插入数据的基本语句，帮助快速入门。 本文使用基于SQL 标准的 Microsoft 实现的 Transact-SQL。

本文旨在简介 Transact-SQL 语言，选用了简单的语句，因此不能代表标准生产数据库中存在的语句的复杂程度。

将介绍如何创建数据库、在数据库中创建表、将数据插入到表中、更新数据、读取数据、删除数据，然后删除表。

将创建视图和存储过程，并为数据库和数据配置用户。

二、创建和查询数据库对象

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

Transact-SQL 语句可以通过使用 sqlcmd 实用工具进行编写并提交到数据库引擎 ，代码以相同方式和相同权限在 数据库引擎 上执行。

2.1、创建数据库

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

创建数据库语句示例：

CREATE DATABASE testDB;
GO
1
2

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

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

2.2、创建表

若要创建表，必须提供该表的名称以及该表中每个列的名称和数据类型。 指出每个列中是否允许空值，也是一种很好的做法。
若要创建表时，必须具有 CREATE TABLE 权限，以及对包含该表的架构的 ALTER SCHEMA 权限。 db_ddladmin 固定数据库角色具有这些权限。

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

常用语法：

CREATE TABLE
    { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( { <column_definition> } [ ,... n ] )
[ ; ]
1
2
3
4

（1）将查询编辑器连接切换到 TestData 数据库：

USE TestData
GO
1
2

（2）创建表。创建一个名为 Products 的表。 该表中各列的名称为 ProductID、 ProductName、 Price和 ProductDescription。 ProductID 列是表的主键。 intvarchar(25)、 money和 varchar(max) 都是数据类型。 当插入或更改行时，只有 Price 和 ProductionDescription 列可以不包含数据。 此语句包含称为架构的可选元素 (dbo.)。 架构是拥有表的数据库对象。 如果是管理员，则 dbo 是默认架构。 dbo 代表数据库所有者。

CREATE TABLE dbo.Products
    (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription varchar(max) NULL)
GO
1
2
3
4
5
6

2.3、插入数据

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

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

将数据插入到表：

-- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription) VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO
-- Changing the order of the columns
INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription) VALUES ('Screwdriver', 50, 3.17, 'Flat head')
GO
-- Skipping the column list, but keeping the values in order
INSERT dbo.Products  VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
GO
-- Dropping the optional dbo and dropping the ProductDescription column
INSERT Products (ProductID, ProductName, Price)  VALUES (3000, '3 mm Bracket', 0.52)
GO
1
2
3
4
5
6
7
8
9
10
11
12

如果插入操作失败，则可能是因为 Product 表中已存在具有该产品 ID 的行，请删除表中的所有行并重复上一步。 TRUNCATE TABLE 删除表中的所有行。运行以下命令来删除表中的所有行：

TRUNCATE TABLE TestData.dbo.Products;
GO
1
2

截断表后，在此步骤中重复 INSERT 命令。

2.4、更新表中的数据

更新 products 表。键入并执行以下 UPDATE 语句，将第二种产品的 ProductName 从 Screwdriver更改为 Flat Head Screwdriver。

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

2.5、从表中读取数据

使用 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
1
2
3

（2）使用星号 (*) 选择表中的所有列。 星号用于临时查询。 在永久代码中，请提供列列表，使语句返回预测列，即使稍后会将新列添加到表中，也是如此。

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

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

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

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

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

（5）可以在返回列中的值时使用它们。除非通过使用 AS 关键字提供一个名称，否则以此方式更改的列将没有名称。

-- Returns ProductName and the Price including a 7% tax
-- Provides the name CustomerPays for the calculated column
SELECT ProductName, Price * 1.07 AS CustomerPays FROM dbo.Products
GO
1
2
3
4

三、创建视图和存储过程

视图是存储的 SELECT 语句，而存储过程是以批处理方式执行的一条或多条 Transact-SQL 语句。

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

3.1、创建视图

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

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

3.2、测试视图

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

SELECT * FROM vw_Names;
GO
1
2

3.3、建存储过程

以下语句创建一个名为 pr_Names的存储过程，接受名为 @VarPrice 、数据类型为 money的输入参数。 该存储过程打印与输入参数串联的语句 varchar(10) 。 然后，该存储过程对视图执行 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 < @VarPrice;
   END
GO
1
2
3
4
5
6
7
8
9
10

3.4、测试存储过程

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

EXECUTE pr_Names 10.00;
GO
1
2

四、配置数据库对象的权限

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

4.1、创建登录名

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

CREATE LOGIN [computer_name\Mary]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = [TestData];
GO
1
2
3
4

这将授权通过计算机的身份验证的用户名 Mary访问此 SQL Server实例。 如果计算机上存在多个 SQL Server 实例，则必须在 Mary 必须访问的每个实例上创建登录名。

4.2、授予对数据库的访问权限

切换到数据库，再使用 CREATE USER 语句进行名称映射。

在数据库中创建用户，授予 Mary 访问 TestData 数据库的权限：

USE [TestData];
GO

CREATE USER [Mary] FOR LOGIN [computer_name\Mary];
GO
1
2
3
4
5

4.3、授予权限GRANT

执行以下语句将 Mary 存储过程的 EXECUTE 权限授予 pr_Names 。

GRANT EXECUTE ON pr_Names TO Mary;
GO
1
2

在这种情况下，Mary 只能通过使用存储过程访问 Products 表。 如果您希望 Mary 能够对视图执行 SELECT 语句，则您还必须执行 GRANT SELECT ON vw_Names TO Mary。 若要删除对数据库对象的访问权限，请使用 REVOKE 语句。

关于grant：必须具有 EXECUTE 权限才能执行存储过程。 必须具有 SELECT、INSERT、UPDATE 和 DELETE 权限才能访问和更改数据。 GRANT 语句还用于其他权限，如创建表的权限。

五、删除数据库对象

5.1、撤销存储过程权限

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

REVOKE EXECUTE ON pr_Names FROM Mary;
GO
1
2

5.2、删除权限

（1）使用 DROP 语句删除 Mary 对 TestData 数据库的访问权限：

DROP USER Mary;
GO
1
2

（2）使用 DROP 语句删除 Mary 对此 SQL Server实例的访问权限。

DROP LOGIN [<computer_name>\Mary];
GO
1
2

（3）使用 DROP 语句删除存储过程 pr_Names：

DROP PROC pr_Names;
GO
1
2

（4）使用 DROP 语句删除视图 vw_Names：

DROP VIEW vw_Names;
GO
1
2

5.3、删除表

（1）使用 DELETE 语句删除 Products 表中的所有行：

DELETE FROM Products;
GO
1
2

（2）使用 DROP 语句删除 Products 表：

DROP TABLE Products;
GO
1
2

5.4、删除数据库

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

USE MASTER;
GO
DROP DATABASE TestData;
GO
1
2
3
4

总结

设计和创建有效的数据库结构以及配置对数据的安全访问，需要比本教程中显示的数据库更复杂的数据库。