【SqlServer】T-SQL的简介及基本用法
一.T-SQL概述
SQL Server用于操作数据库的编程语言为Transaction-SQL，简称T-SQL。T-SQL与PL/SQL不同，并没有固定的程序结构。
T-SQL包括以下4个部分：
DDL：定义和管理数据库及其对象，例如create、alter和drop等。
DML：实现对数据库表各对象的操作，例如insert、update等。
DCL：数据控制语言，实现对数据库进行安全管理和权限管理等控制，例如grant、revoke、deny等。
附加的语言元素。T-SQL的附加语言元素，包括变量、运算符、函数、注释和流程控制语句等。
在T-SQL中，命令和语句的书写是不区分大小写的。
二.T-SQL编程基础
1、标识符
①T-SQL规则标识符
- 由字母、数字、下划线、@、#、$符号组成，其中字母可以是a-z或A-Z，也可以是来自其他语言的字母字符。
- 首字符不能为数字和$。
- 标识符不允许是T-SQL保留字。
- 标识符内不允许有空格和特殊字符
- 长度小于128
②界定标识符
- 对于不符合标识符规则的标识符，则要使用界定符方括号([])或双引号(“”)将标识符括起来。如标识符[My Table]、“select”内分别使用了空格和保留字select。
2、数据类型
在SQL Server中提供了多种系统数据类型。除了系统数据类型外，还可以自定义数据类型。
①系统数据类型
| 类型 | 描述 |
| int | 存储整型数值，存储数值范围为-231~231-1。 |
| bigint | bigint比int能存储更大的数值，存储数值范围为-263~263-1。 |
| smallint | 数据类型的范围数值比int更小，在-215~215-1之间。定义这种数据类型的时候一定要小心，要确定存储的数据不会超过smallint所能存储的数值范围。 |
| tinyint | 数据类型的范围数值比smallint更小，存储从 0 到 255 的整型数据。 |
| decimal/numeric |
decimal[(p,s)]和numeric[(p,s)]这两种数据类型用于存储相同精度和范围的数据(小数点的左、右两边存储的数值位数相同)，所能存储的数值范围为-1038+1~1038-1。 |
(2)近似数字数据类型
| 类型 | 描述 |
| float | 存储小数点不固定的数值，存储的数值范围。为-1.79E+308~1.79E+308。 |
| real | 与float非常相似，存储数值范围为-3.40E+38~3.40E+38。 |
(3)字符数据类型
| 类型 | 描述 |
| char | 长度固定，最多可以定义8000个字符。如果定义一个列为char(n)，则将存储n个字节数。当输入少于定义的字节数时，剩余的长度将被在右边的空格填满。 |
| nchar | 与char类型相似，nchar(n)，但最多可以定义4000(n最大为4000)个字符，使用Unicode编码。 |
| varchar |
与char一样，用于存储字母数字数据，最多可定义8000个字节。二者不同之处在于varchar的每一行可以有不同的字节数，最大字节数未定义的最大长度。例如列定义为varchar(50)，则该列数据最多可以有50个字节长。然而，如果列中只存储了3个字节长的字符串，则只会使用3个字节的存储空间。如果定义列时没有指定大小，即varchar()，则其长度默认为1。 |
| nvarchar | 定义方式与varchar相似，nvarchar(n)，但最多可以定义4000(n最大为4000)个字符，nvarchar使用Unicode格式存储字符。（如果使用nvarchar(max)则可以存储超过4000个字符的数据） |
(4)日期和时间数据类型
| 类型 | 描述 |
| date | 仅用来存储日期，其范围从0001年1月1日到9999年12月31日。date数据类型的格式是YYYY-MM-DD。 |
| time | 只存储基于24小时制的时间，其格式为hh:mm:ss[.nnnnnnn]。与date数据类型类似，为了给要存储在列中的数据提供准确的数据类型，能存储精确度达100纳秒的数据。 |
| datetime | 用于存储从1753年1月1日到9999年12月31日之间的任何日期和时间。datetime不仅存储日期，而且会在日期的旁边存储时间。如果只像定义为datetime的列存入日期，则会在存储的日期中加入默认的时间12:00:00。datetime的精度为0,3,7毫秒。 |
| datetime2 | 与datetime类似，datetime2用于存储日期和时间。不同之处是，datatime2的数据类型秒的小数部分的精度更高。此外，该数据类型能存储从0001年1月1日到9999年12月31日的日期。其格式为YYYY-MM-DD hh:mm:ss[.nnnnnnn]。datetime2的精度为100纳秒。 |
| smalldatetime | 与datetime十分相似，除了smalldatetime可存储的数值范围是从1900年1月1日到2079年6月6日。该数值范围的结束日期不是月末。 |
(5)二进制数据类型
| 类型 | 描述 |
| binary |
存储固定大小的二进制个十数据，最多可存储8000字节。 |
| varbinary | 与binary十分相似，但是varbinary每一行的物理列大小随存储的值而不同。varbinary(max)能存储长度超过8000个字符的数据，最多可存储2GB，可用于存储类似图像这样的数据。 |
(6)专用数据类型
| 类型 | 描述 |
| bit | 该数据类型存储的值为0或1.通常用于判定真假值。 |
| uniqueidentifier | 用于存储16位全局唯一标识符(UUID)。 |
②程序中的数据类型
| 类型 | 描述 |
| cursor | 数据能够以驻留内存的状态进行存储。游标，与表类似，有数据行和列，但它们的相似之处仅限于此。不同之处如：游标没有索引。通过使用游标来建立数据集，以便一次处理一行数据。 |
| table | table数据类型与游标和表有几分相似之处。该数据类型用于存储行和列的数据，但不能在数据上建索引。此时，系统可以“一次处理一个数据集”的数据，就想处理一个标准的表那样。 |
| sql_variant | 可以根据存储的数据改变数据类型，即用来存储一些不同类型的数据类型。不过强烈不推荐使用这种数据类型。 |
3、表达式
表达式常指由常量、变量、函数等通过运算符按一定的规则连接起来的有意义的式子。
1.变量
T-SQL的变量分为局部变量和全局变量。
(1)局部变量
局部变量由用户定义，一般出现在批处理、存储过程和触发器中，其作用范围仅在程序内部。
局部变量必须先声明，后使用。T-SQL还为局部变量提供了赋值语句。
declare变量声明语句，其语法格式为：
declare @变量1 [as] datatype,@变量2 [as] datatype...
- 局部变量名称必须以@开始开头
- as可以省略
- 赋初值NULL
局部变量的赋值有三种方式：
①在变量定义的时候对其赋值：
declare @变量1 [as] datatype = value,@变量2 [as] datatype = value...
②select赋值语句，其语法格式为：
select @变量1 = 表达式1,@变量2 = 表达式2...
- 用select命令可以一次给多个变量赋值
- 表达式可以为普通的value，也可以为查询结果
- 当表达式为表的列名时，形式与普通查询中使用列别名的用法类似。可以使用子查询从表中一次返回多个值。如果查询的结果为多行，则只会把最后一行的相应列值赋给变量，这与PL/SQL的处理方式不同，在PL/SQL中，不允许把多行查询结果赋值给变量
③set赋值语句，其语法格式为：
set @变量 = 表达式
基本用法和select一样，区别在于一条set赋值语句只能给一个变量赋值，而一条select语句可以给多个变量赋值
【示例】
declare @sumsal as numeric(10,2),@dno as tinyint select @dno = deptno,@sumsal = sum(sal) from emp where deptno = 10 group by deptno print cast(@dno as varchar)+'：'+cast(@sumsal as varchar)
(2)全局变量
全局变量由SQL Server系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值。可以通过访问全局变量来了解系统目前的一些状态信息。
全局变量名以@@开头。下面给出一些常用的全局变量。
全局变量 说明
@@error 上一条SQL语句报告的错误号
@@nestlevel 当前存储过程或触发器的嵌套级别
@@rowcount 上一条SQL语句处理的行数
@@servername 本地服务器名称
@@identity 最后插入的标识值
@@spid 当前用户进程的会话id
@@fetch_status 上一条游标fetch语句的状态
@@cpu_busy SQL Server自上次启动后的时间状态
@@trancount 当前的用户连接的当前活动事务数
2.逻辑处理
(1). if ... else ... 表达式
格式：
if 表达式 begin 条件成立处理语句 end else begin 条件不成立处理语句 end
例如：
declare @i int set @i = 1 if @i > 0 begin select CONVERT(varchar(10),@i)+'大于0' AS 提示信息 end else begin select CONVERT(varchar(10),@i)+'小于0' AS 提示信息 end
3.函数
函数是用来完成某种特定功能，并返回处理结果的一组T-SQL语句，处理结果成为“返回值”，处理过程成为“函数体”。
函数又分为系统内置函数和用户自动以函数。SQL Server提供了大量系统内置函数，主要可以分为以下几类：数学函数、字符串函数、日期函数、convert函数、聚合函数。关于函数可见 【SqlServer】SqlServer的常规操作, 函数
4.运算符
算术运算符：+、-、*、/、%(求余)
字符串运算符：+(连接)
比较运算符：=、>、>=、<、<=、<>(不等于)、!>(不大于)、!<(不小于)
逻辑运算符：NOT、AND、OR、ALL(所有)、ANY(或SOME，任意一个)、BETWEEN...AND、EXISTS(存在)、IN(在范围内)、LIKE(匹配)
按位运算符：&(位与)、|(位或)、^(按位异或)
一元运算符：+(正)、-(负)、~(按位取反)
赋值运算符：=(等于)
4. 临时表和表变量
--创建一个本地临时表 CREATE #TempTable(ID INT, NAME CHAR(3)); INSERT INTO #TempTable(ID, NAME) VALUES (1, 'abc'); GO SELECT * FROM #TempTable; GO DROP TABLE #TempTable;
--创建一个全局临时表 CREATE ##TempTable(ID INT, NAME CHAR(3)); INSERT INTO ##TempTable(ID, NAME) VALUES (1, 'abc'); GO SELECT * FROM ##TempTable; GO DROP TABLE ##TempTable;
无论是本地临时表还是全局临时表，在创建当前临时表的会话结束后，都会被删除。这意味着，虽然是全局临时表，但当创建临时表的会话结束后，其它的会话无法访问它。
表变量 和 临时表的使用类似，下面是使用表变量的例子。
DECLARE @TempTable TABLE (ID INT, NAME CHAR(3)) INSERT INTO @TempTable(ID, NAME) VALUES(1, 'abc'); SELECT * FROM @TempTable;
表变量和临时表的语法略有不同，不过更显著的区别在于表变量的作用域范围是批处理，而不是整个会话。如果像前面的这例子一样，使用GO作为批处理之间的分隔符，那么在执行SELECT时候，会抛出“对象不存在”的错误，因为这个表变量的作用域不在这个批范围内。
下面的表格列举了本地临时表和表变量之间的区别：
| 临时表 | 表变量 | |
| 统计信息 | 支持 | 不支持 |
| 索引 | 支持 | 只有使用约束时支持 |
| 架构修改 | 支持 | 不支持 |
| 在包含sp_executesql的子句是否可用 | 是 | 否 |
| 可与INSERT INTO ... EXEC 一起使用 | 是 | 否 |
| 是否在内存结构中 | 否 | 否 |
如果未来处理的数据会逐渐增多，建议读者使用临时表，而不是表变量。在数据很少的时候，表变量可能比临时表更快。但几年后，有几十万的数据后，会导致性能非常差。所以在你做决定时候，应该考虑一些容量规划。
