SQL SERVER基本知识小结

本是古典何须时尚 2023-01-14 01:54 353阅读 0赞

### SQL SERVER基本知识小结 ###

**SQL SERVER的系统数据库**

在安装好SQL SERVER后，系统会自动安装5个用于维护系统正常运行的系统数据库：

（1）master：记录了SQL SERVER实例的所有系统级消息，包括实例范围的元数据（如登录帐号）、端点、链接服务器和系统配置设置。

（2）msdb：供SQL SERVER 代理服务调度报警和作业以及记录操作员的使用，保存关于调度报警、作业、操作员等信息。（备份还原时）

（3）model：SQL SERVER 实例上创建的所有数据库的模板。

（4）tempdb：临时数据库，用于保存临时对象或中间结果集，为数据库的排列等操作提供一个临时工作空间。（每次启动都会重新创建）

（5）Resource：一个只读数据库，包含了SQL SERVER 的所有系统对象。（隐藏的数据库）

**SQL SERVER数据库的组成文件**

1 数据文件

（1）主要数据文件：扩展名为 .mdf ，每个数据库有且只能有一个。

（2）次要数据文件：扩展名为 .ndf ， 可以没有或有多个。

2 日志文件

扩展名为 .ldf ，用于存放恢复数据库的所有日志信息。

【SQL Server 不强制使用 .mdf、.ndf 和 .ldf 文件扩展名，但使用它们有助于标识文件的各种类型和用途。】

3 数据的存储分配

（1）数据文件和日志文件的默认存放位置为：\\Programe Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data文件夹。

（2）数据的存储分配单位是数据页。一页表是一块8KB的连续磁盘空间。

（3）页是存储数据的最小空间分配单位，页的大小决定了数据库表中一行数据的最大大小。

SQL Server Management Studio（SSMS）对象资源管理器

对象资源管理器（SSMS）是SQL SERVER集成的吗一种工具，可以查看和管理所有服务器类型的对象。用户可以通过该组件操作数据库，包括新建、修改、删除数据库、表、视图等数据库对象，新建查询、设置关系图、设置系统安全、数据库复制、数据备份、恢复等操作，是SSMS中最常用、也是最重要的一个组件。

在SSMS窗口中单击"新建查询"按钮，在对象资源管理器右侧会出现“查询分析器”窗口。

**查询分析器**（Query Analyzer）

SQ L查询分析器是一个图形化的查询工具，对语法中的保留字提供彩色显示，用于以文本的方式编辑Transact-SQL语句，然后发送给服务器，并接受执行的结果。使用这个工具，用户可以交互地设计和测试Transact-SQL语句、批处理和脚本。

**标识符**

T-SQL标识符是表示数据库对象的符号。对象标识符是在定义对象时创建的。标识符随后用于引用该对象。

标识符有两类：

．常规标识符，符合标识符的格式规则。

．分隔标识符，包含在双引号 (") 或者方括号 (\[ \]) 内。分隔标识符可以不符合标识符的格式规则，在使用时必须包含在双引号或者方括号内。

常规标识符规则

1）第一个字符必须是下列字符之一：

．Unicode 标准 2.0 所定义的字母。Unicode 中定义的字母包括拉丁字母 a-z 和 A-Z，以及来自其它语言的字母字符。

．下划线 (\_)、at 符号 (@) 或者数字符号 (\#)。以at 符号 (@) 或者数字符号 (\#)开始的标识符具有特殊意义。以 at 符号 (@) 开始的标识符表示局部变量或参数，以 @@ 开头的 Transact-SQL 内置函数，全局变量是传统术语。以一个数字符号(\#)开始的标识符表示临时对象（临时表或临时过程），以双数字符号 (\#\#) 开始的标识符表示全局临时对象。

2）后续字符可以是：

．Unicode 标准 2.0 所定义的字母。

．来自基本拉丁字母或十进制数字。

．at 符号 (@)、美元符号 ($)、数字符号 (\#)或下划线(\_)。

3）标识符不能是 Transact-SQL 的保留字。SQL Server 保留其保留字的大写和小写形式。

4）不允许嵌入空格或其它特殊字符。

注意，符合标识符格式规则的标识符可以分隔，也可以不分隔。不符合规则的标识符必须用双引号或括号分隔，如：\[My Table\]、\[order\]。

**表达式**

表达式是标识符、值和运算符的组合，SQL Server可以对其求值以得到结果（单个数据值）。访问或更改数据时，可在多个不同的位置使用数据。例如，可以将表达式用作要搜索的数据的一部分（在查询中）或查找满足一组条件的数据的搜索条件。

简单的表达式可以是一个常量、变量、列名或标量函数。可以用运算符将两个或更多的简单表达式联接起来组成复杂的表达式。

表达式可以是：常量、函数、列名、变量、子查询、CASE、NULLIF 或 COALESCE ，还可以用运算符对这些实体进行组合以生成表达式。

**数据库对象名称的组成**

数据库对象的名称可以由四部分组成，格式如下：

server\_name .\[database\_name \].\[schema\_name \].object\_name

| database\_name .\[schema\_name \].object\_name

| schema\_name .object\_name

| object\_name

其中，

server\_name 指定链接的服务器名称或远程服务器名称。

database\_name 如果对象驻留在 SQL Server 的本地实例中，则指定 SQL Server 数据库的名称。 如果对象在链接服务器中，则 database\_name 将指定 OLE DB 目录 。

schema\_name 如果对象在 SQL Server 数据库中，则指定包含对象的架构的名称。 如果对象在链接服务器中，则 schema\_name 将指定 OLE DB 架构名称 。

object\_name 对象的名称，数据库对象如表、视图、列 。

**SQL Server的架构（SCHEMA）**

架构（schema）是指包含表、视图、过程等的容器。它位于数据库内部，而数据库位于服务器内部。【注、schema概念，在不同语境下的含义不同，可参见 [https://blog.csdn.net/cnds123/article/details/116188157][https_blog.csdn.net_cnds123_article_details_116188157]】

要理解SQL Server的架构（SCHEMA）可以参考

权限层次结构（数据库引擎）[https://docs.microsoft.com/zh-cn/sql/relational-databases/security/permissions-hierarchy-database-engine?view=sql-server-ver15][https_docs.microsoft.com_zh-cn_sql_relational-databases_security_permissions-hierarchy-database-engine_view_sql-server-ver15]

安全对象范围：架构 [https://docs.microsoft.com/zh-cn/sql/relational-databases/security/securables?view=sql-server-ver15\#securable-scope-schema][https_docs.microsoft.com_zh-cn_sql_relational-databases_security_securables_view_sql-server-ver15_securable-scope-schema]

在SQL Server 2000和早期版本中，数据库可以包含一个名为“架构”的实体，但此实体实际上是数据库用户（即架构等效于数据库用户）。

在SQL Server 2005 及之后版本，有了重要改进：用户与架构（schema）分开，让数据库内各对象不再绑在某个用户账号上，架构是一个命名空间（命名空间是一个集合，包含一组名称不重复的对象），可以将架构看成一个存放数据库中对象的一个容器。

用户和架构分离的好处或意义：

\*可以删除数据库用户而不删除相应架构中的对象。

\*架构可以由任何数据库主体拥有，这包括角色和应用程序角色。

\*多个数据库用户可以共享单个默认架构。

\*单个架构可以包含由多个数据库用户拥有的对象。

\*架构的所有权和架构范围内的安全对象可以转移——请参阅ALTER AUTHORIZATION (Transact-SQL)

中文版[https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver15][https_docs.microsoft.com_zh-cn_sql_t-sql_statements_alter-schema-transact-sql_view_sql-server-ver15]

英文版[https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql?view=sql-server-ver15][https_docs.microsoft.com_en-us_sql_t-sql_statements_alter-authorization-transact-sql_view_sql-server-ver15]

\*对象可以在架构之间移动——请参阅ALTER SCHEMA (Transact-SQL)。

英文版[https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver15][https_docs.microsoft.com_en-us_sql_t-sql_statements_alter-schema-transact-sql_view_sql-server-ver15]

示例

以下示例从单个 CREATE SCHEMA 语句开始。该语句创建 Krishna 拥有的架构 Sprockets 以及表 Sprockets.NineProngs，然后将 SELECT 权限授予 Anibal，并对 Hung-Fu 拒绝 SELECT 权限。

USE AdventureWorks;

CREATE SCHEMA Sprockets AUTHORIZATION Krishna

CREATE TABLE NineProngs (source int, cost int, partnumber int)

GRANT SELECT TO Anibal

DENY SELECT TO Hung-Fu;

下列语句删除架构。请注意，必须首先删除架构所包含的表。

DROP TABLE Sprockets.NineProngs;

DROP SCHEMA Sprockets;

如何使用sql语句查出所有的用户表的schema的限定名？

使用INFORMATION\_SCHEMA信息架构视图，操作步骤：

(1) 打开Microsoft SQL Server 2008 Management Studio。

(2) 提供必要的信息并单击Connect按钮，连接到数据库实例。

(3) 单击位于SQL Server Management Studio控制台左上角的New Query按钮。

(4) 输入下面的代码：  
USE library

SELECT \* FROM INFORMATION\_SCHEMA.TABLES

(5)结果如图：

![20210422091402317.png][]

说明：

第一个语句将数据库的上下文环境设置为LIBRARY数据库。Microsoft SQL Server的INFORMATION\_SCHEMA.TABLES视图收集了当前数据库中已创建的每一个自定义表的信息。

**视图**

SQL 视图是虚拟表或存储查询（数据库内存储的是 SELECT 语句）。它是一种逻辑意义上（虚拟）的表，它建立在其他的表或视图上，由一条SELECT 语句组成。

语法

CREATE VIEW 视图的名称 \[ ( 视图中的列名 \[ ,...`n` \] ) \]

\[ WITH \{ ENCRYPTION | SCHEMABINDING | VIEW\_METADATA \} \[ ,...`n` \] \]

select语句

\[ WITH CHECK OPTION \]

例、pubs 数据库中的 titleview 是一个视图，该视图选择三个基表中的数据来显示包含常用数据的虚拟表：

CREATE VIEW titleview

SELECT title, au\_ord, au\_lname, price, ytd\_sales, pub\_id

FROM authors AS a

JOIN titleauthor AS ta ON (a.au\_id = ta.au\_id)

JOIN titles AS t ON (t.title\_id = ta.title\_id)

之后，可以用引用表时所使用的方法在语句中引用 titleview：

SELECT \*

FROM titleview

**用户定义的函数**

SQL Server除内置（内建）函数外，还支持用户定义函数。

SQL 用户定义函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。

用户定义函数接受零个或更多的输入参数，并返回单值。一些用户定义函数返回单个的标量数据值，如 int、char 或 decimal 值。然后可以在允许整型表达式的任何地方（如表的计算列中）使用该函数。

SQL Server 2000 还支持返回 table 数据类型的用户定义函数：

☆ 该函数可声明内部 table 变量，将行插入该变量，然后将该变量作为返回值返回。

☆ 一类称为内嵌函数的用户定义函数，将 SELECT 语句的结果集作为变量类型 table 返回。

这些函数可用在能指定表表达式的地方。

用户定义函数是由一个或多个 Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。可以使用输入参数并返回类似于存储过程的结果。有三种用户定义函数：

．内嵌（联）表值函数——RETURNS子句中仅含TABLE关键字，在RETURN子句中仅包含一个SELECT语句，将这个SELECT语句结果作为表类型数据返回。若SELECT 语句可更新，则该函数返回的表格格式结果也可以更新，可以用在查询的FORM子句中。也叫用户定义内嵌（嵌入）函数。可以定义扩展属性。

内嵌表值函数语法

CREATE FUNCTION \[拥有者(所有者)的用户ID.\] 要创建的函数名称

( \[ \{ @函数的参数 \[AS\] 标量数据类型 \[ = 默认值\] \} \[ ,...n \] \] )

RETURNS TABLE

\[ WITH ENCRYPTION | SCHEMABINDING \]

\[ AS \]

RETURN \[ ( \] 返回值的单个SELECT语句 \[ ) \]

．多语句表值函数——返回由RETURNS 子句定义的表变量的值，函数体由BEGIN 和 END包围，由TRANSACT-SQL 语句组成，用于填充表变量，最后一句为RETURN不带任何参数和关键字；返回一个数据不可更新的表值，可以用在查询的FORM子句中。也叫用户定义表值函数。无法定义扩展属性。

多语句表值函数语法

CREATE FUNCTION \[拥有者(所有者)的用户ID.\] 要创建的函数名称

( \[ \{ @函数的参数 \[AS\] 标量数据类型 \[ = 默认值\] \} \[ ,...n \] \] )

RETURNS @返回表变量名 TABLE  \{ 表变量的列定义 | 表变量的列约束 \} \[ ,...n \]

\[ WITH ENCRYPTION | SCHEMABINDING \]

\[ AS \]

BEGIN

一系列填充表变量的Transact-SQL语句（一般是insert语句）

RETURN

END

．标量函数——标量函数返回在 RETURNS 子句中定义的返回值类型的单个数据值，函数体由BEGIN 和 END包围，由TRANSACT-SQL 语句组成，并需要包含RETURN子句（用于定义返回值），可以用在查询任何一个可以使用列名称的位置。也叫用户定义标量函数，返回标量值，如 int、decimal、varchar、sql\_variant 或“表”数据类型。无法定义扩展属性。

标量函数语法

CREATE FUNCTION \[拥有者(所有者)的用户ID.\] 要创建的函数名称

( \[ \{ @函数的参数 \[AS\] 标量数据类型 \[ = 默认值\] \} \[ ,...n \] \] )

RETURNS 函数的返回值数据类型

\[ WITH ENCRYPTION | SCHEMABINDING \]

\[ AS \]

BEGIN

一系列合起来求得标量值的Transact-SQL语句

RETURN 返回标量值的表达式

END

**参数查询**

某些情况下需要创建可以使用多次但每次使用不同值的查询。

若要创建每次使用不同值的查询，可以在查询中使用参数。参数是在运行查询时提供值的占位符。带参数的 SQL 语句可能如下所示，其中"?"(未命名参数)表示作者所在州的参数：

SELECT au\_lname, au\_fname

FROM state

WHERE state = ?

也可为参数指定一个名称（命名参数）。命名参数在查询中存在多个参数时很有用。例如，如果在 authors 表中使用命名参数搜索一个作者的姓和名，则 SQL 窗格中所得到的语句可能象下面这样：

SELECT au\_id

FROM authors

WHERE au\_fname = %first name% AND

au\_lname = %last name%

命名参数标记字符可以是"@"、":"或"%"。 参数名的前面要加上标记字符前缀，并可选择在后面加或不加标记字符后缀。

**存储过程**

是Transact-SQL 语句的预编译集合，这些语句在一个名称下存储并作为一个单元进行处理。可以使用参数、控制流语句。存储过程存储在数据库内，可由应用程序通过一个调用执行。

存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同SQL语句， 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，使用execute语句调用,即可自动完成命令。

存储过程的特性：

·因为存储过程是一个数据库对象，所以可以使用SQL Server的安全模型来对它应用权限许可／限制。

·存储过程可以接受参数，也能够为用户接口或者主调函数返回值：变量或者记录集等。

·存储过程可以用If…Else逻辑来实现代码流程的条件控制。

·存储过程可以调用其他的存储过程。

系统存储过程是一组 SQL Server 提供的存储过程，可以用于执行一些操作，如从系统目录检索信息或执行管理任务。这些存储过程的名称以 sp\_ 或 xp\_ 开头。

系统存储过程 (system stored procedures) ，是一组 SQL Server 提供的存储过程，可以用于执行一些操作，如从系统目录检索信息或执行管理任务。

这些存储过程的名称以 sp\_ 开头。

扩展存储过程 (extended stored procedure) ，名称以 xp\_ 开头的系统存储过程，是动态链接库 (DLL) 中的一种函数，不推荐使用。

系统存储过程保存在Master系统数据库中，为数据库管理者所有。一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其它用户所使用。

用户存储过程,创建语法

CREATE PROC\[EDURE\] 新存储过程的名称 \[ ; 同名的过程组编号 \]

\[ \{ @过程中的参数 参数的数据类型  \[ VARYING \] \[ = 默认值 \] \[ OUTPUT \] \} \] \[ ,...n \]

\[ WITH \{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION \} \]

\[ FOR REPLICATION \]

Transact-SQL 语句 \[ ...n \]

**触发器（**TRIGGER**）**

是一种特殊类型的存储过程，与存储过程的显著不同是，不能由应用程序调用，而在特定的表中使用数据修改操作修改数据时才起作用（激活），这些修改操作有：UPDATE、INSERT、或 DELETE。触发器常常用于强制业务规则和数据完整性。

触发器语法

CREATE TRIGGER 触发器的名称

ON 执行触发器的表或视图的名称

\[ WITH ENCRYPTION \]

\{ FOR | AFTER | INSTEAD OF \} \{ \[DELETE\] \[,\] \[INSERT\] \[,\] \[UPDATE\]  \}

\[ WITH APPEND \]

\[ NOT FOR REPLICATION \]

Transact-SQL 语句 \[ ...n \]

实际上FOR 子句指定触发器的两种类型（AFTER触发器和INSTEAD OF触发器）：

如果仅指定 FOR 关键字，和指定 AFTER 相当。

AFTER

触发器在触发它们的语句完成后执行。如果该语句因错误（如违反约束或语法错误）而失败，触发器将不会执行。不能为视图指定 AFTER 触发器，只能为表指定该触发器。可以为每个触发操作（INSERT、UPDATE 或 DELETE）指定多个 AFTER 触发器。如果表有多个 AFTER 触发器，可使用 sp\_settriggerorder 定义哪个 AFTER 触发器最先激发，哪个最后激发。除第一个和最后一个触发器外，所有其它的 AFTER 触发器的激发顺序不确定，并且无法控制。

INSTEAD OF

该触发器代替触发操作执行。可在表和视图上指定 INSTEAD OF 触发器。只能为每个触发操作（INSERT、UPDATE 和 DELETE）定义一个 INSTEAD OF 触发器。INSTEAD OF 触发器可用于对 INSERT 和 UPDATE 语句中提供的数据值执行增强的完整性检查。INSTEAD OF 触发器还允许指定某些操作，使一般不支持更新的视图可以被更新。

**常量**

常量，也称为**字面值**或**标量值**，是表示一个特定数据值的符号。常量的格式取决于它所表示的值的数据类型。

**变量** (variables)

SQL Server的T-SQL语言中的变量,具有独特的用法。全局变量由系统提供是只读的，Microsoft也称它们为标量函数，意指它们只返回一个值。全局变量名称以@@开头，属于系统定义维护的函数，你（用户）不能对它们声明（用户不能建立全局变量），全局变量是其传统术语，Transact-SQL中的全局变量与大多数编程语言中的概念含义不同，Transact-SQL全局变量是返回系统状态信息的内置函数，是只读的。

@@CONNECTIONS返回自上次启动 Microsoft® SQL Server 以来连接或试图连接的次数。

下面的示例显示显示当前为止试图登录的次数

SELECT @@CONNECTIONS AS 'Login Attempts'

下面是结果集：

局部变量名称以@开头，用DECLARE语句声明，作用范围是定义它的批处理、存储过程、触发器范围内，在声明时它被初始化为NULL。可以用SET|SELECT @local\_variable 语句给局部变量赋值。

局部变量 (local variable)

用户定义的变量，具有已赋的值。局部变量使用 DECLARE 语句定义，使用 SELECT 或 SET 语句赋以初始值，并在声明该变量的语句批或过程内使用。

在批处理或过程的正文中用 DECLARE 语句声明变量，并用 SET 或 SELECT 语句给其指派值。游标变量可通过该语句声明，并且可用在其它与游标相关的语句中。所有变量在声明后均初始化为 NULL。

下例创建 @myvar 局部变量，将一个字符串值放在变量中，并输出 @myvar 局部变量的值。

\--声明局部变量

DECLARE @myvar char(20)

\--由 SET 赋值的局部变量

SET @myvar = 'This is a test'

输出局部变量的值

SELECT @myvar

**临时对象**

SQL Server使用\#或\#\#字符串来作为临时对象（临时表或临时过程）或者全局临时对象名称的开头。不特别说明临时对象一般指局部（本地）临时对象。

临时对象保存在在 tempdb 系统数据库中。

临时表

有本地和全局两种类型的临时表，二者在名称、可见性和可用性上均不相同。本地临时表的名称以单个数字符号 (\#) 打头；它们仅对当前的用户连接（会话）是可见的；当用户从 Microsoft SQL Server 实例断开连接时被删除。全局临时表的名称以数学符号 (\#\#) 打头，创建后对任何用户都是可见的（对所有会话中都可见），当所有引用该表的用户从 SQL Server 断开连接时被删除。

例如，如果创建名为 employees 的表，则任何人只要在数据库中有使用该表的安全权限就可以使用该表，除非它已删除。如果您创建名为 \#employees 的本地临时表，只有您能对该表执行操作且在断开连接时该表删除。如果创建名为 \#\#employees 的全局临时表，数据表中的任何用户均可对该表执行操作。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果该表在您创建后有其他用户使用，则 SQL Server在所有用户断开连接后删除该表。

除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去：

．当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。

．所有其它本地临时表在当前会话结束时自动除去。

．局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。

如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建，则 SQL Server 必须能够区分由不同用户创建的表。为此，SQL Server 在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表，其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的表名 table\_name 不能超过 116 个字符。

临时存储过程

SQL Server 支持两种临时过程：局部临时过程和全局临时过程。局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时自动除去。全局临时过程在使用该过程的最后一个会话结束时除去。通常是在创建该过程的会话结束时。SQL Server 关闭后，这些过程也将不再存在。

临时过程用 \# 和 \#\# 命名，\# 表示本地临时存储过程，\#\# 表示全局临时存储过程。可以由任何用户创建。

创建过程后，局部过程的所有者是唯一可以使用该过程的用户。执行局部临时过程的权限不能授予其他用户。只有创建本地临时过程的连接才能执行该过程，当该连接关闭（用户从 SQL Server 中注销）时，将自动删除该过程。

如果创建了全局临时过程，则所有用户均可以访问该过程，权限不能显式废除。任何连接都可执行全局临时存储过程。只有创建该过程的用户所用的连接关闭，并且所有其它连接所用的该过程的当前执行版本运行完毕后，全局临时存储过程才不再存在。一旦用于创建该过程的连接关闭，将不再允许启动执行该全局临时存储过程。只允许那些已启动执行该存储过程的连接完成该过程的运行。

只有在 tempdb 数据库中具有显式 CREATE PROCEDURE 权限的用户，才可以在该数据库中显式创建临时过程（不使用编号符命名）。可以授予或废除这些过程中的权限。如果直接在 tempdb 数据库中创建没有 \# 或 \#\# 前缀的存储过程，则由于每次启动 SQL Server 时 tempdb 都要重新创建，因此当关闭 SQL Server 时将自动删除该存储过程。直接在 tempdb 中创建的过程即使在创建该过程的连接终止后也会存在。与任何其它对象一样，可向其他用户授予、拒绝和废除执行该临时存储过程的权限。

**table****数据类型**

由table类型可以声明一个空表结构（表变量）。

table 数据类型类似是一个临时表，可以用于存储一个结果集以便以后处理。这种数据类型只能用于定义 table 类型的局部变量和用户定义函数的返回值。在定义 table 变量的函数、存储过程或批处理结束时，自动清除 table 变量。

尽可能使用表变量而不使用临时表。table 变量有以下优点：

．table 变量的行为类似于局部变量，有明确定义的作用域。该作用域为声明该变量的函数、存储过程或批处理。

在其作用域内，table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的地方。但是，table 不能用在下列语句中：

INSERT INTO table\_variable EXEC 存储过程。

SELECT select\_list INTO table\_variable 语句。

在定义 table 变量的函数、存储过程或批处理结束时，自动清除 table 变量。

．在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。

．涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。

不支持在表变量之间进行赋值操作。另外，由于表变量作用域有限，并且不是持久数据库的一部分，因而不受事务回滚的影响。

**脚本**(Transact-SQL脚本)

是一个文件扩展名为.sql的文本文件，存储在文件中的是一系列 Transact-SQL 语句。此文件可以用作 SQL 查询分析器或 osql 和 isql 实用工具的输入，然后，实用文件中的 SQL 语句。

脚本包含一个或多个批处理。GO 命令作为批处理结束的信号。

模板（SQL脚本模板）是包含 SQL 脚本的样本文件，帮助您在数据库中创建对象。在 Templates\\SQL Query Analyzer 目录中提供各种模板。在 SQL 查询分析器中使用模板，单击工具栏上的"插入模板"按钮，可以选择脚本打开来显示代码。

**批处理**(batch)

批处理是包含一个或多个 Transact-SQL 语句的组，一次性地发送到 SQL Server执行。SQL Server 将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条。

编译错误（如语法错误）使执行计划无法编译，从而导致批处理中的任何语句均无法执行。

GO，标识一批 Transact-SQL 语句的结束。

当前批处理语句是自上一 GO 命令后输入的所有语句，若是第一条 GO 命令，则是从特殊会话或脚本的开始处到这条 GO 命令之间的所有语句。

注意，GO 不是 Transact-SQL 语句，而是可为 osql 和 isql 实用工具及SQL Server查询分析器识别的命令。

在SQL Server查询分析器中，你可以选定（高亮显示）部分代码执行，查询分析器将选定的代码部分作为一个批处理执行而不执行其余未选定的代码。

示例

下面的示例创建两个批处理。第一个批处理只包含一条 USE pubs 语句，用于设置数据库上下文。剩下的语句使用了一个局部变量，因此所有的局部变量声明必须在一个批处理中。这一点可通过在最后一条引用此变量的语句之后才使用 GO 命令来做到。

USE pubs

DECLARE @NmbrAuthors int

SELECT @NmbrAuthors = COUNT(\*)

FROM authors

PRINT 'The number of authors as of ' +

CAST(GETDATE() AS char(20)) + ' is ' +

CAST(@NmbrAuthors AS char (10))

**调试与错误处理**

SQL Server查询分析器中包含一个T-SQL调试程序。该调试程序支持传统功能，如设置断点、定义监视表达式和单步执行过程。

SQL Server 的所有错误都存储在系统表 master.dbo.sysmessages 中。用户定义的消息也可以存储在 sysmessages（在master 数据库中） 中。如果需要，可以使用 RAISERROR 语句将这些用户定义的错误返回到一个应用程序。SQL Server还根据遇到问题的严重级别，将把 sysmessages 系统表中的消息写入 SQL Server 错误日志和 Microsoft Windows NT应用程序日志（可用“事件查看器”打开查看），或者将消息发送到客户端。

如果最后的 Transact-SQL 语句执行成功，则 @@ERROR 系统函数返回 0；如果此语句产生错误，则 @@ERROR 返回错误号。每一个 Transact-SQL 语句完成时，@@ERROR 的值都会改变。

**注释**

批处理中可以使用注释，注释有两种格式：

\--（单行注释）可以放置在 Transact-SQL 语句行的末端或者 Transact-SQL 语句中,由换行字符分界(即由换行字符结束)。

/\*...\*/（块注释）可以放置在 Transact-SQL 语句行的末端或者 Transact-SQL 语句中,也可以占用多行,但不能跨越批处理——整个注释必须包含在一个批处理内。

T-SQL语言支持**流程控制语句，**也叫控制流语言，包括IF...ELSE语句、WHILE语句等。

要这些有明确的理解，需要深入学习有关SQL Server 方面的资料。

**游标（CURSOR）**

游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据（SELECT 语句返回所有满足WHERE 子句限定条件的行集）进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T\_SQL 选择语句相关联，因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

Transact-SQL 游标

基于 DECLARE CURSOR 语法，主要用在 Transact-SQL 脚本、存储过程和触发器中。Transac-SQL 游标在服务器上实现并由从客户端发送到服务器的 Transact-SQL 语句管理。它们还包含在批处理、存储过程或触发器中。

游标通过以下方式扩展结果处理：

允许定位在结果集的特定行。

从结果集的当前位置检索一行或多行。

支持对结果集中当前位置的行进行数据修改。

为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

当你要处理的结果集比较庞大，而你要对某一行或几行进行操作的时候，要考虑使用游标！特别是对结果集中第几行进行行操作的时候，一般可以考虑使用游标！但也不是唯一的方法，可以利用别的方法来替代！

**事务 (transaction)**

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。

事务作为整体或者完全提交，或者（若事务包含的操作有一个失败）回滚到以前。

SQL Server三种事务模式：

☆自动提交事务，每条单独的语句都是一个事务。自动提交模式是默认模式。

☆显式事务，每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT提交或 ROLLBACK回滚语句显式结束。

☆隐性事务，当设置SET IMPLICIT\_TRANSACTIONS ON之后，且当前不在事务中时，执行DML（数据操作语言）语句时，都会自动启动一个事务，直到发出 COMMIT 或 ROLLBACK结束（即在发出 COMMIT 或 ROLLBACK 语句之前，该事务将一直保持有效）。

数据操作语句如：ALTER TABLE、INSERT 、CREATE 、OPEN 、DELETE 、REVOKE 、DROP 、SELECT 、FETCH 、TRUNCATE TABLE 、GRANT 、UPDATE 。

注意，自动提交模式是默认模式（在BEGIN TRANSACTION 语句启动显式事务，或隐性事务模式设置为SET IMPLICIT\_TRANSACTIONS ON 之前）。

事务保存点

用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分，事务可以返回的位置。

保存点提供了一种机制，用于回滚部分事务。可以使用 SAVE TRANSACTION savepoint\_name 语句创建一个保存点，然后再执行 ROLLBACK TRANSACTION savepoint\_name 语句回滚到该保存点，从而无须回滚到事务的开始。

**事务日志(transaction log)**

事务日志是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。事务日志记录每个事务的开始。它记录了在每个事务期间，对数据的更改及撤消所做更改所需的足够信息。对于一些大的操作（如 CREATE INDEX），事务日志则记录该操作发生的事实。

用户提交一个更新操作，SQL在高速缓存里缓冲了需要的数据页和日志页，然后打上begin tran标签，对日志进行修改，再修改数据页，然后打上commit tran标签，最后把修改过的日志页刷新到磁盘上，在保证了这个步骤完成后，数据页才被写入磁盘，如果这个时候机器突然断电导致高速缓存中数据页的丢失，那么重启机器时SQL的恢复进程将根据已经刷新的日志记录来演算刚才的数据页，保证数据的完整性。提交了又还没来得及刷新数据页到磁盘的日志事务可以称为活动日志，一旦日志记录和其对应的数据页被刷新到磁盘的话，这条日志的作用也就完成了，并称为非活动的日志，他的唯一用处就是备份下来留着以后做日志恢复。

物理日志文件就像一个容器，里面容纳的是日志记录，数据库目录下面的.ldf文件就是物理日志文件，数据库的事务日志记录就在这里面。

[https_blog.csdn.net_cnds123_article_details_116188157]: https://blog.csdn.net/cnds123/article/details/116188157
[https_docs.microsoft.com_zh-cn_sql_relational-databases_security_permissions-hierarchy-database-engine_view_sql-server-ver15]: https://docs.microsoft.com/zh-cn/sql/relational-databases/security/permissions-hierarchy-database-engine?view=sql-server-ver15
[https_docs.microsoft.com_zh-cn_sql_relational-databases_security_securables_view_sql-server-ver15_securable-scope-schema]: https://docs.microsoft.com/zh-cn/sql/relational-databases/security/securables?view=sql-server-ver15#securable-scope-schema
[https_docs.microsoft.com_zh-cn_sql_t-sql_statements_alter-schema-transact-sql_view_sql-server-ver15]: https://docs.microsoft.com/zh-cn/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver15
[https_docs.microsoft.com_en-us_sql_t-sql_statements_alter-authorization-transact-sql_view_sql-server-ver15]: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql?view=sql-server-ver15
[https_docs.microsoft.com_en-us_sql_t-sql_statements_alter-schema-transact-sql_view_sql-server-ver15]: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver15
[20210422091402317.png]: /images/20221022/df76141d74e54b43b25dca1ebd19e8fc.png