T-SQL简介及基本语法

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中提供了多种系统数据类型。除了系统数据类型外，还可以⾃定义数据类型。

①系统数据类型

(1)精确数字数据类型

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。

p表⽰指定⼩数点左边和右边可以存储的⼗进制数字的最⼤个数，s指定⼩数位数。[(p,s)]的范围为1≤p≤38,0≤s≤p。若省略s，则默

认为0；若未附带p及s，则numeric表⽰numeric(18)，只能表⽰整数

(2)近似数字数据类型

float

存储⼩数点不固定的数值，存储的数值范围。为-1.79E+308~1.79E+308。

real

与float⾮常相似，存储数值范围为-3.40E+38~3.40E+38。

(3)货币数据类型money

smallmoney

(4)字符数据类型

char

长度固定，最多可以定义8000个字符。如果定义⼀个列为char(n)，则将存储n个字符。当输⼊少于定义的字符数时，剩余的长度将被在右边的空格填满。nchar

与char类型相似，但最多可以定义4000个字符，使⽤Unicode编码。

varchar

与char⼀样，⽤于存储字母数字数据，最多可定义8000个字符。⼆者不同之处在于varchar的每⼀⾏可以有不同的字符数，最⼤字

符数未定义的最⼤长度。例如列定义为varchar(50)，则该列数据最多可以有50个字符长。然⽽，如果列中只存储了3个字符长的字符

串，则只会使⽤3个字符的存储空间。如果定义列时没有指定⼤⼩，即varchar()，则其长度默认为1。

特别，varchar(max)可以定义超过8000个字符的字符串数据类型，最多1073741824个字符。【注意不是varchar(n)】

nvarchar

定义⽅式与varchar相似，除了nvarchar使⽤Unicode格式存储字符。

(3)⽇期和时间数据类型

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⽇。该数值范围的结束⽇期不是⽉

末。

(4)⼆进制数据类型

binary

存储固定⼤⼩的⼆进制个⼗数据，最多可存储8000字节。

这种数据类型主要⽤于存储作为标记或标记组合的数据。例如存储关于客户的标记。需要了解客户是否处于活动状态(值为1)、最

近⼀个⽉有⽆消费记录(值为2)、最后⼀个⽉的消费额是否超过1000元(值为4)或者是否按时销账(值为8)。这将向数据库中加⼊4个数据

列。然⽽，若使⽤binary值，如果客户有⼀个值为1101的⼆进制值，那么该客户拥有的值为1+4+8，这表明客户是活动的。最后⼀个

⽉的消费额超过1000元并按时销账。varbinary

与binary⼗分相似，但是varbinary每⼀⾏的物理列⼤⼩随存储的值⽽不同。varbinary(max)能存储长度超过8000个字符的数据，最

多可存储2GB，可⽤于存储类似图像这样的数据。

(5)专⽤数据类型

bit

该数据类型存储的值为0或1.通常⽤于判定真假值。

uniqueidentifier

⽤于存储16位全局唯⼀标识符(UUID)。

XML

②程序中的数据类型

(1)cursor

数据能够以驻留内存的状态进⾏存储。游标，与表类似，有数据⾏和列，但它们的相似之处仅限于此。不同之处如：游标没有索引。通

过使⽤游标来建⽴数据集，以便⼀次处理⼀⾏数据。

(2)table

table数据类型与游标和表有⼏分相似之处。该数据类型⽤于存储⾏和列的数据，但不能在数据上建索引。此时，系统可以“⼀次处理⼀

个数据集”的数据，就想处理⼀个标准的表那样。

(3)sql_varint

可以根据存储的数据改变数据类型，即⽤来存储⼀些不同类型的数据类型。不过强烈不推荐使⽤这种数据类型。

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 tinyintselect @dno = deptno,@sumsal = sum(sal) from emp where deptno = 10group by deptnoprint cast(@dno as varchar)+'：'+cast(@sumsal as varchar)

(2)全局变量

全局变量由SQL Server系统定义，通常⽤来跟踪服务器范围和特定会话期间的信息，不能被⽤户显式地定义和赋值。可以通过访问全局

变量来了解系统⽬前的⼀些状态信息。

全局变量名以@@开头。下⾯给出⼀些常⽤的全局变量。

全局变量说明

@@error上⼀条SQL语句报告的错误号

@@nestlevel当前存储过程或触发器的嵌套级别

@@rowcount上⼀条SQL语句处理的⾏数

@@servername本地服务器名称

@@identity最后插⼊的标识值

@@spid当前⽤户进程的会话id

@@fetch_status上⼀条游标fetch语句的状态

@@cpu_busySQL Server⾃上次启动后的时间状态

2.函数

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

函数⼜分为系统内置函数和⽤户⾃动以函数。SQL Server提供了⼤量系统内置函数，主要可以分为以下⼏类：数学函数、字符串函数、

⽇期函数、convert函数、聚合函数。

(1)数学函数

T-SQL中提供的常⽤的数学函数如下：

abs()：返回绝对值

round(数值表达式,长度,[,类型])：舍⼊到指定长度或精度。类型为0，表⽰舍⼊，类型为⾮0，表⽰截断

power(m,n)：返回m的n次幂

trunc()：将数字截断到指定的位数

%：求余数，SQL Server没有mod(m,n)，⽽⽤m%n代替

(2)字符串函数

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()：求字符串长度

(3)⽇期和时间函数