一 TSQL概述

1.1  TSQL介绍

Transact Structured Query Language 它是ANSI和ISO SQL 标准的Microsoft SQL Server方言或扩展，中文理解为：SQL SERVER专用标准结构化查询语言增强版。

SQL可分为：

数据定义语言(DDL):Data Definition Language用于建立、修改、删除数据库对象。

CREATE:创建表或其他对象的结构，ALTER:修改表或其他对象的结构，DROP:删除表或其他对象的结构，TRUNCATE:删除表数据，保留表结构 

数据操作语言(DML):Data Manipulation Language

用于该表数据表中的数据，和事务相关，执行完后需要经过事务控制语句提交后才真正的将改变应用到数据库中。

INSERT:将数据插入到数据表中，UPDATE:更新数据表中已存在的数据，DELETE:删除数据表中的数据。

数据查询语言(DQL):Data Query Language

 用来查询所需要的数据，SELECT语句 。 

事务控制语言(TCL):Transaction Control Language

用于维护数据一致性的语句。COMMIT:提交，确认已经进行的数据改变，ROLLBACK:回滚，取消已经进行的数据改变，SAVEPOINT:保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变。   

数据控制语言(DCL):Data Control Language

用于执行权限的授予和收回操作：

GRANT:授予，用于给用户或角色授予权限，REVOKE:用于收回用户或角色已有的权限，CREATE USER:创建用户。

 

指针控制语言（CCL）：

它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

1.2 TSQL编程基础

1.2.1 TSQL三逻辑

     在TSQL中逻辑表达式的可能值包括TRUE、FALSE和UNKNOWN。它们被称之为三值逻辑。

        UNKNOW可以理解为不确定，既不是TRUE又不是FALSE的表达式，主要由与NULL相关的逻辑判断引起，值为NULL就意味着该未赋值或该值未确定。

与NULL值做算术运算时，其结果是NULL，如果1+NULL结果为NULL

SQL Server不同场景下对UNKNOWN处理不同，对NULL的处理也不同。

1. 在WHERE+ON+HAVING三种筛选器中，所有运算结果非TURE(FALSE 与UNKNOW)的记录都不会返回；

2. 在CHECK约束中，所有运算结果为非FALSE（TRUE与UNKNOW）的都属于满足CHECK约束的；

3. 在UNIQUE约束中，如果列定义未限制为NOT NULL，那么允许该列存在一条NULL值，如果另外插入或更新一条记录为NULL时，会违法UNIQUE约束，NULL与NULL是相等的；

4. 在GROUP BY中，NULL值被认为相同而分为一组，NULL与NULL是相等的；

5. 在ORDER BY中，NULL值被认为相同而排列在一起，所有NULL值比已知值小，NULL与NULL是相等的；

 

 

1.2.2 TSQL常规标识符

由字母、数字、下划线、@、#、$符号组成，其中字母可以是a-z或A-Z，也可以是来自其他语言的字母字符。首字符不能为数字和$。标识符不允许是T-SQL保留字。标识符内不允许有空格和特殊字符，长度小于128。

   在sqlserver中，某些位于标识符开头位置具有特殊意思，@开头代表局部变量或者参数，#开头代表本地临时表和存储过程，##代表全局临时表。

#开头代表本地临时表和存储过程

CREATE TABLE #A02_DBSERVER (

      ServerID int NOT NULL,

      ServerName nvarchar(100) NULL,

      ServerShort nvarchar(100) NULL,

);

1.2.3界定标识符

对于不符合标识符规则的标识符，则要使用界定符方括号([])或双引号(“”)将标识符括起来。如标识符[My Table]、“select”内分别使用了空格和保留字select。

 

 

1.2.4 注释符

   在TSQL中有两种注释符，行注释符--，块注释符/*…*/。

   --创建添加学生的存储过程

 

/*

      Copyright (C) 2005 141421.com,All Rights Reserved                                        

      Function:  [SP_Get_P01_TOD_INDEX]                                                                 

      Description:欠补的存储过程名称        

      Author:冯凯                                                                                                                                                

      Finish DateTime:2011年1月6日                                                     

*/

 

1.3 常量与变量

1.3.1 常量

   数字常量：12，-37，浮点常量使用符号E，15E3，-3.14E1。

   日期和时间常量：’1985-01-19’,’03/03/1985’， '14:30:24'。

   字符串常量：’SHIYINGJIE’,  ’shi jjj’,还有’’shi jjj’’ ，两个单引号表示成一个单引号。

   

   符号常量：例如CURRENT_DATE,CURRENT_TIME, CURRENT_TIMESTAMP等。

1.3.2 变量

变量的名称必须是@开头，它的作用域仅限于一个批处理语句中，从定义变量开始到批处理结束，中间不能用GO。

Transact-SQL 系统函数的名称以两个 at 符号 (@@) 开头。 尽管在旧版 SQL Server 中，@@函数称为全局变量，但它们不是变量，不具有等同于变量的行为。 @@函数是系统函数，语法遵循函数规则。

 

变量初始化

• 指定一个名称。 名称的首字符必须为一个 @。

•定系统提供的或用户定义的数据类型和长度。 对于数值变量还指定精度和小数位数。 对于 XML 类型的变量，可以指定一个可选的架构集合。

•将值设置为 NULL。

使用 DECLARE定义变量，一次性定义多个变量，每个变量之间使用逗号隔开。变量赋值，可以使用SET或SELECT。

事例

1  DECLARE @LastName nvarchar(30), @FirstName nvarchar(20), @twoName nvarchar(30)

Set @LastName=’syj’

 

2  DECLARE @LastName nvarchar(30)='syj', @FirstName nvarchar(20)='syj1', @twoName nvarchar(30)='syj2'

 

3    DECLARE @LastName nvarchar(30), @FirstName nvarchar(20), @twoName nvarchar(30)

SELECT @LastName =max(USER_NAME)   FROM A70_USERS_ZH ;

SELECT @LastName;

 

 

4    DECLARE @LastName nvarchar(30), @FirstName nvarchar(20), @twoName nvarchar(30)

SELECT @LastName =’syj’, @FirstName=’jjj’, @twoName=’hct’

 

1.3.3 全局变量

  全局变量是系统预定义的，返回一些系统信息，全局变量以两个at(@)开头。下面是我统计了一些较为常用的变量。 

全局变量名称	描述
@@CONNECTIONS	返回 SQL Server 自上次启动以来尝试的连接数。
@@CPU_BUSY	返回 SQL Server 自上次启动后的工作时间。
@@CURSOR_ROWS	返回连接上打开的上一个游标中的当前限定行的数目， 确定当其被调用时检索了游标符合条件的行数。
@@DATEFIRST	针对会话返回 SET DATEFIRST 的当前值，SET DATEFIRST 表示指定的 每周的第一天。
@@DBTS	返回当前数据库的当前 timestamp 数据类型的值， 这一时间戳值在数据库中必须是唯一的。
@@ERROR	返回执行的上一个 Transact-SQL 语句的错误号， 如果前一个 Transact-SQL 语句执行没有错误，则返回 0。
@@FETCH_STATUS	返回针对连接当前打开的任何游标发出的上一条游标 FETCH 语句的状态。
@@IDENTITY	返回上次插入的标识值。
@@IDLE	返回 SQL Server 自上次启动后的空闲时间。结果以 CPU 时间 增量或“时钟周期”表示，并且是所有 CPU 的累积。
@@IO_BUSY	返回自从 SQL Server 最近一次启动以来，Microsoft SQL Server 已经用于执行输入和输出操作的时间。其结果是 CPU 时间增量 （时钟周期），并且是所有 CPU 的累积值
@@LANGID	返回当前使用的语言的本地语言标识符 (ID)。
@@LANGUAGE	返回当前所用语言的名称。
@@LOCK_TIMEOUT:	返回当前会话的当前锁定超时设置（毫秒）。
@@MAX_CONNECTIONS	返回 SQL Server 实例允许同时进行的最大用户连接数。 返回的数值不一定是当前配置的数值。
@@MAX_PRECISION	按照服务器中的当前设置，返回 decimal 和 numeric 数据类型 所用的精度级别。
@@NESTLEVEL	返回对本地服务器上执行的当前存储过程的嵌套级别（初始值为 0）。
@@OPTIONS	返回有关当前 SET 选项的信息。
@@PACK_RECEIVED	返回 SQL Server 自上次启动后从网络读取的输入数据包数。
@@PACK_SENT	返回 SQL Server 自上次启动后写入网络的输出数据包个数。
@@PACKET_ERRORS	返回自上次启动 SQL Server 后，在 SQL Server 连接上发生的网络 数据包错误数。
@@PROCID	返回 Transact-SQL 当前模块的对象标识符 (ID)。Transact-SQL 模块可以是存储过程、用户定义函数或触发器。
@@REMSERVER	返回远程 SQL Server 数据库服务器在登录记录中显示的名称。
@@ROWCOUNT	返回受上一语句影响的行数。
@@SERVERNAME	返回运行 SQL Server 的本地服务器的名称。
@@SERVICENAME	返回 SQL Server 正在其下运行的注册表项的名称。若当前实例为 默认实例，则 @@SERVICENAME 返回 MSSQLSERVER。
@@SPID	返回当前用户进程的会话 ID。
@@TEXTSIZE	返回 SET 语句中的 TEXTSIZE 选项的当前值。
@@TIMETICKS	返回每个时钟周期的微秒数。
@@TOTAL_ERRORS	返回 SQL Server 自上次启动之后所遇到的磁盘写入错误数。
@@TOTAL_READ	返回 SQL Server 自上次启动后读取磁盘（不是读取高速缓存）的次数。
@@TOTAL_WRITE	返回 SQL Server 自上次启动以来所执行的磁盘写入次数。
@@TRANCOUNT	返回当前连接的活动事务数。
@@VERSION	返回当前的 SQL Server 安装的版本、处理器体系结构、生成日期 和操作系统。

 

 

SELECT @@CONNECTIONS

1219

 

1.4 TSQL 流程控制

1.4.1 SQL语句的批处理符号GO

   GO必须独占一行，SQL数据库能以批处理方式处理单个或多个TSQL语句，当执行不包含GO的多条SQL语句时，SQL数据库将这些语句编程一个执行计划。包含GO的多条SQL语句会将他们成批发送，SQL数据库将他们编译成多个执行计划。

 

1.4.1.1 注意 1 局部变量必须在一个批里面GO，否则会报错。

GO

DECLARE @LastName nvarchar(30)='syj', @FirstName nvarchar(20)='syj1', @twoName nvarchar(30)='syj2'

GO

print @LastName

 

消息137，级别15，状态2，第1 行

必须声明标量变量"@LastName"。

1.4.1.2注意2 多个批处理，当一个批处理出问题的时候，下面的批处理还会运行。批为单元语法解析工作

GO

DECLARE @LastName nvarchar(30)='syj', @FirstName nvarchar(20)='jjj', @twoName nvarchar(30)='syj2'

GO

print @LastName

GO

DECLARE @LastName nvarchar(30)='syj', @FirstName nvarchar(20)='hct', @twoName nvarchar(30)='syj2'

print @FirstName

消息137，级别15，状态2，第1 行

必须声明标量变量"@LastName"。

hct

 

 

注意3 多个批处理，在其中的一个批处理中，错误了一个点，还会去执行，只是错误的没有提交。

GO

create table tname (id int)

insert into tname values(1)

insert into tname values(1,2)

insert into tname values(3)

GO

select * from tname

 

结果

ID

1

1.4.1.3 不能被组合在同一批中的语句

 CREATE DEFAUTL , CREATE FUNCTION, CREATE PROCDEURE, CREATE RULE, CREATE SCHEMA,TIGER ,VIEW,必须单独在一个批里面

1.4.1.4 GO N选项

GO N 选项执行多少次。

set nocount on;

GO 1000

select * from tname

开始执行循环

批处理执行已完成1000 次。

    

 

 

1.4.2 TSQL逻辑块

   BEGIN…..END语句用于将多个TSQL语句组合成为一个逻辑块，当流程控制语句必须执行2条以上的TSQL语句的时候，推荐使用BEGIN….END。主要用于IF语句或者while语句。

语法

BEGIN  

     { sql_statement | statement_block }  

END  

 

begin

CREATE table A00Hname (hname  varchar(20))

CREATE table A00Jname (name varchar(20))

End

 

1.4.3 TSQL 条件控制语句IF.. ELSE

指定 Transact-SQL 语句的执行条件。 如果满足条件，则在 IF 关键字及其条件之后执行 Transact-SQL 语句：布尔表达式返回 TRUE。 可选的 ELSE 关键字引入另一个 Transact-SQL 语句，当不满足 IF 条件时就执行该语句：布尔表达式返回 FALSE。可选 ELSE 的关键字是要执行的备选Transact-SQL 语句。

语法

IF Boolean_expression  

     { sql_statement | statement_block }   

ELSE  

     { sql_statement | statement_block } ]  

1.4.3.1 简单的判断单IF

declare @score int=75

IF @score >60 PRINT '及格' 

 

及格

1.4.3.2 简单的判断单IF.. ELSE

declare @score int=59.5

IF @score >60 PRINT '及格'

ELSE PRINT '不及格'

不及格

 

1.4.3.3嵌套的 IF...ELSE 语句

 

declare @score int=52

IF @score >90 PRINT '完美'

ELSE

   begin

        IF @score >85 PRINT '优秀'

        ELSE

            begin

                 IF @score >75 PRINT '良好'

                  ELSE

                  begin

                       IF @score >60 PRINT '及格'

                        ELSE PRINT '差'

                  end

             end

  

  

   end

 

差

 

 

DECLARE @Number int; 

SET @Number = 50; 

IF @Number > 100 

   PRINT 'The number is large.'; 

ELSE  

   BEGIN 

      IF @Number < 10 

      PRINT 'The number is small.'; 

   ELSE 

      PRINT 'The number is medium.'; 

   END ; 

GO 

 

 

1.4.4  WHILE 循环控制

设置重复执行 SQL 语句或语句块的条件。 只要指定的条件为真，就重复执行语句。 可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。

WHILE Boolean_expression  

     { sql_statement | statement_block | BREAK | CONTINUE } 

 

While<条件表达式>

Begin

<命令与程序>

Break

Continue

<命令与程序>

END

 

返回 TRUE 或 FALSE 的表达式。 如果布尔表达式中含有 SELECT 语句，则必须用括号将 SELECT 语句括起来。{sql_statement | statement_block}
Transact-SQL 语句或用语句块定义的语句分组。 若要定义语句块，请使用控制流关键字 BEGIN 和 END。

BREAK 导致从最内层的 WHILE 循环中退出。 将执行出现在 END 关键字后面的任何语句，END 关键字为循环结束标记。

CONTINUE使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后面的任何语句。

1.4.4.1 简单while

declare @jjj int=1

while @jjj<10

    begin     

              set @jjj=@jjj+1

                    print @jjj        

         end

1.4.4.2 while  BREAK命令跳出

BREAK 导致从最内层的 WHILE 循环中退出。 将执行出现在 END 关键字后面的任何语句，END 关键字为循环结束标记。

 

declare @jjj int=1

while @jjj<10

    begin

              IF @jjj=6 break

                     print @jjj           

              set @jjj=@jjj+1

                          

         end

 

1.4.4.3 while continue命令跳出，重新开始执行

declare @jjj int=1;

while @jjj<10

    begin

              set @jjj=@jjj+1;

              IF @jjj=6 continue;

                     print @jjj;         

                                

         end

1.4.4.4 IF 与while braek continue嵌套使用

 

 

 

USE AdventureWorks2012; 

GO 

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 

BEGIN 

   UPDATE Production.Product 

      SET ListPrice = ListPrice * 2 

   SELECT MAX(ListPrice) FROM Production.Product 

   IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 

      BREAK 

   ELSE 

      CONTINUE 

END 

PRINT 'Too much for the market to bear'; 

 

如果产品的平均标价小于 $300，则 WHILE 循环将价格乘 2，然后选择最高价格。 如果最高价格小于或等于 $500，则 WHILE 循环重新开始，并再次将价格乘 2。 该循环不断地将价格乘 2，直到最高价格超过 $500，然后退出 WHILE 循环，并输出一条消息。

1.4.4.5 BREAK 退出

退出 WHILE 循环内部的 WHILE 语句或 IF ELSE 语句最里面的循环。 将执行出现在 END 关键字后面的任何语句，END 关键字为循环结束标记。 IF 测试通常会启动 BREAK，但并不总是如此。

WHILE ((SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300) 

BEGIN 

    UPDATE DimProduct 

        SET ListPrice = ListPrice * 2; 

     IF ((SELECT MAX(ListPrice) FROM dbo.DimProduct) > $500) 

         BREAK; 

END 

1.4.4.6 GOTO

将执行流更改到标签处。 跳过 GOTO 后面的 Transact-SQL 语句，并从标签位置继续处理。 GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。 GOTO 语句可嵌套使用。

 

语法 GOTO 标识符：

Define the label:  

label:  

Alter the execution: 

GOTO label  

 

declare @syj char(50)='加油第一',@x int=1

loving:

     print @syj

     select @x=@x+1

while @x<5 goto loving

 

1.4.4.7 RETURN和存储过程有关，没有明白。

从查询或过程中无条件退出。 RETURN 的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。 RETURN 之后的语句是不执行的。

语法：

RETURN [ integer_expression ]   

　存储过程返回值，return语句要指定返回值,如果没有指定返回值,SQL Server系统会根据程序执行的结果返回一个内定值,返回值含义如下所示:

返回值	含义
0	存储过程执行成功
-1	没有找到数据库对象
-2	数据类型错误
-3	进程死锁错误
-4	进程死锁错误
-5	语法错误
-6	其他用户错误
-7	资源错误
-8	非致命的内部错误
-9	达到系统配置参数极限
-10	内部一致性致命错误
-11	内部一致性致命错误
-12	表或索引崩溃
-13	数据库崩溃
-14	硬件错误

BEGIN

    PRINT(1);

    PRINT(2);

    RETURN;

    PRINT(3);    --在RETURN之后的代码不会被执行，因为会跳过当前批处理

END

GO

BEGIN

    PRINT(4);

END

　输出如下：

1

2

4