一、T-SQL介绍

百度介绍：

T-SQL 即 Transact-SQL，是 SQL 在 Microsoft SQL Server 上的增强版，它是用来让应用程序与 SQL Server 沟通的主要语言。T-SQL 提供标准 SQL 的 DDL 和 DML 功能，加上延伸的函数、系统预存程序以及程式设计结构(例如 IF 和 WHILE)让程式设计更有弹性。

在T-SQL中，命令和语句的书写是不区分大小写的

二、T-SQL编程基础

主要从以下3个方面进行讲解：

1. 标识符
2. 数据类型
3. 表单式

1.标识符

T-SQL规则标识符

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

界定标识符

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

2.数据类型

T-SQL中有两种数据类型：(1).系统数据类型 (2).自定义数据类型

2.1 系统数据类型

精确数字数据类型

数据类型	解释
int	存储整型数值
bigint	bigint比int能存储更大的数值
smallint	数据类型的范围数值比int更小,定义这种数据类型的时候一定要小心，要确定存储的数据不会超过smallint所能存储的数值范围
tinyint	数据类型的范围数值比smallint更小，存储从 0 到 255 的整型数据
decimal / numeric	decimal[(p,s)]和numeric[(p,s)]这两种数据类型用于存储相同精度和范围的数据(小数点的左、右两边存储的数值位数相同)

近似数字数据类型

数据类型	解释
float	存储小数点不固定的数值，存储的数值范围。为-1.79E+308~1.79E+308。
real	与float非常相似，存储数值范围为-3.40E+38~3.40E+38。

货币数据类型

数据类型
money
smallmoney

字符数据类型

数据类型	解释
char	长度固定，最多可以定义8000个字符。如果定义一个列为char(n)，则将存储n个字符。当输入少于定义的字符数时，剩余的长度将被在右边的空格填满。
nchar	与char类型相似，但最多可以定义4000个字符，使用Unicode编码。
varchar	与char一样，用于存储字母数字数据，最多可定义8000个字符。二者不同之处在于varchar的每一行可以有不同的字符数，最大字符数未定义的最大长度。例如列定义为varchar(50)，则该列数据最多可以有50个字符长。然而，如果列中只存储了3个字符长的字符串，则只会使用3个字符的存储空间。如果定义列时没有指定大小，即varchar()，则其长度默认为1
nvarchar	定义方式与varchar相似,nvarchar使用Unicode格式存储字符。

日期和时间数据类型

数据类型	解释
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
datetime2	与datetime类似，datetime2用于存储日期和时间。不同之处是，datatime2的数据类型秒的小数部分的精度更高。此外，该数据类型能存储从0001年1月1日到9999年12月31日的日期。其格式为YYYY-MM-DD hh:mm:ss[.nnnnnnn]。
smalldatetime	与datetime十分相似，除了smalldatetime可存储的数值范围是从1900年1月1日到2079年6月6日。该数值范围的结束日期不是月末。

二进制数据类型

数据类型	解释
binary	存储固定大小的二进制个十数据，最多可存储8000字节。
varbinary	与binary十分相似，但是varbinary每一行的物理列大小随存储的值而不同。varbinary(max)能存储长度超过8000个字符的数据，最多可存储2GB，可用于存储类似图像这样的数据。

专用数据类型

数据类型	解释
bit	该数据类型存储的值为0或1.通常用于判定真假值。
uniqueidentifier	用于存储16位全局唯一标识符(UUID)
XML

2.2 程序中的数据类型

数据类型	解释
cursor	数据能够以驻留内存的状态进行存储。游标，与表类似，有数据行和列，但它们的相似之处仅限于此。不同之处如：游标没有索引。通过使用游标来建立数据集，以便一次处理一行数据。
table	table数据类型与游标和表有几分相似之处。该数据类型用于存储行和列的数据，但不能在数据上建索引。此时，系统可以“一次处理一个数据集”的数据，就想处理一个标准的表那样。
sql_varint	可以根据存储的数据改变数据类型，即用来存储一些不同类型的数据类型。不过强烈不推荐使用这种数据类型。

3.表达式

表达式常指由常量、变量、函数等通过运算符按一定的规则连接起来的有意义的式子。

3.1 变量

T-SQL变量分为全局变量和局部变量

1. 局部变量
局部变量由用户定义，一般出现在批处理、存储过程和触发器中，其作用范围仅在程序内部。

局部变量必须先声明，后使用。T-SQL还为局部变量提供了赋值语句。

declare变量声明语句，其语法为：

declare @变量名 [as] 数据类型
declare @xx [as] int

• 局部变量名必须以@开始开头
• as 可以省略
• 赋初值为null

局部变量的赋值有三种方式：

1).在变量定仪的时候对其赋值：

declare @变量名 [as] 数据类型 = 值

2).select语句赋值：

select @变量名 = 字段名 from xx

• 用select命令可以一次给多个变量赋值
• 表达式可以为普通的value，也可以是查询结构
• 当表达式为表的列名时，形式与普通查询中使用的用法类似。可以使用子查询从表中一次返回多个值。如果查询的结果为多行，则只会把最后一行的相应列值赋给变量，这与PL/SQL的处理方式不同，在PL/SQL中，不允许把多行查询结果赋值给变量

3).set赋值：

declare @变量名 数据类型
set @变量名 = 值 / （表达式）

• 基本用法和select一样，区别在于一条set赋值语句只能给一个变量赋值，而一条select语句可以给多个变量赋值

案例:

// 1
declare @num1 int = 1
// 2
declare @num2 int
select @num2 = 2 from emp where id = 'xx'
// 3
declare @num3 int
set @num3 = 3

2. 全局变量
全局变量由SQL Server系统定义，通常用来跟踪服务器范围和特定会话期间的信息，不能被用户显式地定义和赋值。可以通过访问全局变量来了解系统目前的一些状态信息。

全局变量名以@@开头。下面给出一些常用的全局变量。

全局变量	说明
@@error	上一条SQL语句报告的错误号
@@nestlevel	当前存储过程或触发器的嵌套级别
@@rowcount	上一条SQL语句处理的行数
@@servername	本地服务器名称
@@identity	最后插入的标识值
@@spid	当前用户进程的会话id
@@fetch_status	上一条游标fetch语句的状态
@@cpu_busy	SQL Server自上次启动后的时间状态

3.2 函数

函数是用来完成某种特定功能，并返回处理结果的一组T-SQL语句，处理结果成为“返回值”，处理过程成为“函数体”。

函数又分为系统内置函数和用户自动以函数。SQL Server提供了大量系统内置函数，主要可以分为以下几类：数学函数、字符串函数、日期函数、convert函数、聚合函数。

数学函数

函数	说明
abs()	返回绝对值
round(数值表达式,长度,[,类型])	舍入到指定长度或精度。类型为0，表示舍入，类型为非0，表示截断
power(m,n)	返回m的n次幂
trunc()	将数字截断到指定的位数
%	求余数，SQL Server没有mod(m,n)，而用m%n代替

字符串函数

函数	说明
ltrim(str,substr)/rtrim(str,substr)	str表示要操作的字符串，substr表示要裁剪的子串，若裁剪空格，则可以省略
substring(str,position,length)	求子字符串
replace(str,search_str,rep_str)	替换一个字符串中的子串。search_str表示要搜索的子字符串，rep_str表示要替换的目标字符串
left(str,n)	返回字符串从左边开始的指定个数的字符
len()	求字符串长度

日期和时间函数

日期时间元素	缩写	含义
year	yy,yyyy	年
month	m,mm	月
day	d,dd	日
dayofyear	dy	年的天数
week	wk	星期数
weekday	dw	星期几
hour	hh	时
minute	mi	分
quarter	qq	刻
second	ss	秒
millisecond	ms	毫秒

T-SQL中提供下列日期函数：

• getdate()：返回当前的日期和时间
• year(日期)：返回指定日期的“年”部分的整数
• month(日期)：返回指定日期的“月”部分的整数
• day(日期)：返回指定日期的“日”部分的整数
• datepart(日期元素，日期)：返
• datename(日期元素，日期)：以字符串的形式返回日期元素指定时间的日期名称回日期元素指定的日期部分的整数
• datediff(日期元素，日期1，日期2)：返回两个日期间的差值并将其转换为指定日期元素的形式
• dateadd(日期元素，数值，日期) ：按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期

数据类型转换函数

转换的方式有隐式转换和显式转换两种。

1.隐式转换是SQL Server自动地将数据从一种数据类型转换为另一种数据类型，用户不可见。
2.显式转换使用convert函数，该函数可以将一种数据类型的表达式强制转换为另一种数据类型的表达式。两种数据类型必须能够进行转换，例如，char值可以转换为binary，但不能转换为image。该函数的主要作用是把数值型或日期型数据转换为字符串，而只包含数字的字符串转换为数值型数据一般隐式转换。

格式：convert(数据类型(长度),表达式[,n])

函数的第4个参数n是可选的，用于日期时间型数据类型和字符数据类型转换。参数取值如下表所示。

不带世纪数位	带世纪数位	格式
1	101	mm/dd/yyyy
2	102	yy.mm.dd
3	103	dd/mm/yyyy
4	104	dd.mm.yy
5	105	dd-mm-yy
8	108	hh:mi:ss
	20或120	yyyy-mm-dd hh:mi::ss(24h)

【示例】

select ename+''''+'s sal is'+convert(char(7),sal) as 'EMPLOYEE'S SAL' from emp;

select cid as '客户ID',cname as '客户名称'
   convert(char(10),cRegisterationDate,102) as '注册日期'
from customer;

最终显示的日期格式就会如2006.12.02

3.3 运算符

算术运算符：+、-、*、/、%(求余)
字符串运算符：+(连接)
比较运算符：=、>、>=、<、<=、<>(不等于)、!>(不大于)、!<(不小于)
逻辑运算符：NOT、AND、OR、ALL(所有)、ANY(或SOME，任意一个)、BETWEEN…AND、
EXISTS(存在)、IN(在范围内)、LIKE(匹配)
按位运算符：&(位与)、|(位或)、^(按位异或)
一元运算符：+(正)、-(负)、~(按位取反)
赋值运算符：=(等于)