第5章T-SQL语言

5.1 T-SQL语言简介

T-SQL语言由以下几部分组成：

1.数据定义语言（Data Definition Language，DDL）。DDL用于执行数据库任务，对数据库及数据库中的各种对象进行创建、删除、修改。

语句	功能	说明
CREATE	创建数据库或数据库对象	不同数据库对象，其CREATE语句的语法形式不同
ALTER	对数据库或数据库对象进行修改	不同数据库对象，其ALTER语句的语法形式不同
DROP	删除数据库或数据库对象	不同数据库对象，其DROP语句的语法形式不同

2.数据操纵语言（Data Manipulation Language，DML）。DML用于操纵数据库中的各种对象，检索和修改数据。

语句	功能	说明
SELECT	从表或视图中检索数据	是使用最频繁的SQL语句之一
INSERT	将数据插入表或视图中
UPDATE	修改表或视图中的数据	既可修改表或视图的一行，也可修改一组或全部数据
DELETE	从表或视图中删除数据	可根据条件删除指定的数据

3.数据控制语言（Data Management Language，DCL）。DCL用于安全管理，确定哪些用户可以查看或修改数据库中的数据。

语句	功能	说明
GRANT	授予权限	可把语句许可或对象许可的权限授予其他用户或角色
REVOKE	收回权限	与GRANT功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限
DENY	收回权限，禁止并从其他角色继承许可权限	功能与REMOVE相似，不同之处为除收回权限外，还禁止从其他角色继承许可权限

5.2 常量、变量、数据类型

根据常量值的不同，常量分为字符串常量、整型常量、实型常量、日期时间常量、货币常量、唯一标识常量。

变量用于临时存放数据，变量中的数据随着程序的执行而变化。变量有名称及其数据类型两个属性。变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。当变量名的首字符为“@”时，标识该标识符为局部变量名；当首字符为“@@”时，为全局变量名；当首字母为“#”时，此标识符为临时数据库对象名，若开头含一个“#”，表示局部临时数据库对象名，若开头含有两个“#”，表示全局临时数据库对象名。

1.局部变量

局部变量的定义，在批处理或过程中用DECLARE语句声明局部变量，所有局部变量在声明后均初始化为NULL。语法格式如下：

DECLARE {@局部变量 数据类型 [= 值]} [,…]

说明a.@局部变量：局部变量名应为常规标识符

b.数据类型：用于定义局部变量的类型，可为系统类型或自定义类型。

c．=值：为变量赋值，值可以是常量或表达式，但它必须与变量声明类型匹配。

示例：创建局部变量@var1、@var2并赋值，输出变量的值

DECLARE @var1 char(10 ) ,@var2 char(30)

SET @var1 = ‘中国’

SET @var2 =@var1+’是一个伟大的国家’

SELECT @var1,@var2

GO

2.局部游标变量

其语法格式如下：

DECLARE { @游标变量名 CURSOR}[,…]

局部游标变量名的使用：定义游标变量-给游标变量赋值-打开游标-利用游标读取行（记录）-使用结束后关闭游标-删除游标的引用。

示例：使用游标变量

USE pxscj

GO

DECLARE @CursorVar CURSOR

SET @CursorVar = CURSOR SCROLL DYNAMIC

FOR

SELECT 学号,姓名

FROM xsb

WHERE 姓名 LIKE ‘王%’

OPEN @CursorVar

FETCH NEXT FROM @CursorVar

FETCH NEXT FROM @CursorVar

CLOSE @CursorVar

DEALLOCATE @CursorVar

3.表数据类型变量

语法格式如下：

DECLARE { @表变量名 [AS] TABLE ( { <列定义> |<表约束>} [,…] ) }

示例：声明一个表数据类型变量并向变量中插入数据

DECLARE @var_table

AS TABLE

(

num char(6) NOT NULL PRIMARY KEY,

name char(8) NOT NULL,

sex bit NULL

)

INSERT INTO @var_table

SELECT 学号,姓名,性别 FROM xsb

SELECT TOP(4) * FROM @var_table WHERE 备注= NOT NULL

 

 

 

数据类型

用户自定义数据类型的定义

CREATE TYPE [架构名.] 类型名

FROM 基类型 [ (精度[,位数]) ]

[NULL|NOT NULL]

示例：CREATE TYPE STUDENT_num

FROM char(6) NOT NULL

 

 

 

5.4 流程控制语句

控制语句	说明	控制语句	说明
BEGIN…END	语句块	CONTINUE	用于重新开始下一个循环
IF…ELSE	条件语句	BREAK	用于退出最内层的循环
CASE	分支语句	RETURN	无条件返回
GOTO	无条件转移语句	WAITFOR	为语句的执行设置延迟
WHILE	循环语句

 

1.语句块：BEGIN…END

在T-SQL中可以定义BEGIN…END语句块。当要执行多条T-SQL时，就需要使用BEGIN…END将这些语句定义成一个语句块，作为一组语句来执行。语法格式如下：

BEGIN

{SQL语句|语句块}

END

示例：

BEGIN

SELECT * FROM xsb

SELECT * FROM kcb

END

 

2.条件语句：IF…ELSE

IF 条件表达式

{SQL语句|语句块}      /*条件表达式为真时执行*/

[ ELSE

{SQL语句|语句块}]     /*条件表达式为假时执行*/

示例：如果“计算机基础”课程的平均成绩高于75分，则显示“平均成绩高于75分”。

IF

(

SELECT AVG(成绩)

FROM xsb,cjb,kcb

WHERE xsb.学号=cjb.学号

AND cjb.课程号=kcb.课程号

AND kcb.课程名=’计算机基础而’

)<75

SELECT ‘平均成绩低于75’

ELSE

SELECT ‘平均成绩高于75’

 

3.分支语句：CASE

CASE 输入表达式

WHEN 表达式 THEN 结果表达式

[…]

[ELSE 结果表达式]

END

示例：根据性别值输出男或女

SELECT 学号,姓名,专业,SEX=

CASE 性别

WHEN 1 THEN ‘男’

WHEN 0 THEN ‘女’

ELSE ‘无’

END

FROM xsb

WHERE 总学分>48

 

4.循环语句：WHILE/BREAK/CONTINUE

WHILE 条件表达式

{SQL语句|语句块}

示例：将学号为191301的学生的总学分使用循环修改到60，每次只加2，并判断循环了多少次。

DECLARE @num INT

SET @num=0

WHILE (SELECT 总学分 FROM xsb WHERE 学号=’191301’)<60

BEGIN

UPDATE xsb SET 总学分=总学分+2 WHERE 学号=’191301’

SET @num=@num+1

END

SELECT @num AS 循环次数

 

5.等待语句：WAITFOR DELAY/TIME

WAITFOR

{

DELAY ‘等待时间’

|TIME ‘执行时间’

}

说明：DELAY’等待时间’，指定运行批处理、存储过程和事务必须等待的时间，最长可达24小时。等待时间可以用datetime数据格式指定，用单引号括起来，但在值中不允许有日期部分，也可以用局部变量指定参数。TIME’执行时间’，指定运行批处理、存储过程和事务的时间，执行时间表示WAITFOR语句完成的时间，值的指定同上。

示例：设定在早上8点执行查询语句

BEGIN

WAITFOR TIME ‘8:00’

SELECT *FROM xsb

END

6.错误处理语句：TRY…CATCH

语法格式：

BEGIN TRY

{SQL语句|语句块}

END TRY

BEGIN CATCH

[{SQL语句|语句块}]

END CATCH

注意：要进行错误处理，T-SQL语句组可以包含在TRY块中。如果TRY块内部发生错误，则会将控制传递给CATCH块中包含的另一个语句组。TRY…CATCH构造可对严重程度高于10但不关闭数据库连接的所有执行错误进行缓存。

5.5 系统内置函数

行集函数、聚合函数、标量函数

1. 确定性函数：每次使用特定的输入值集调用该函数时，总是返回相同的结果。
2. 非确定性函数：每次使用特定的输入值集调用函数时，可能返回不同的结果。

 

 

1. 行集函数

行集函数是返回值为对象的函数，该对象可在T-SQL语句中作为表引用。所有行集函数都是非确定性的，即每次用一组特定参数调用它们时，所返回的结果不总是相同的。

SQL-Server提供如下行集函数：

CONTAINSTABLE:对于基于字符类型的列，按照一定的搜索条件进行精确或模糊匹配，然后返回一个表，该表可能为空。

FREETEXTTABLE:为基于字符类型的列返回一个表，其中的值符合指定文本的含义，但不符合确切的表达方式。

OPENDATASOURCE：提供与数据源的连接。

OPENQUERY:在指定的数据源上执行查询。可以在查询的FROM子句中像引用基本表一样引用OPENQUERY函数，虽然查询可能返回多个记录，但OPENQUERY只返回一个记录。

OPENROWSET：包含访问OLE DB数据源中远程数据所需的全部链接信息。可在查询的FROM子句中像引用基本表一样引用OPENROWSET函数，虽然查询可能返回多个记录，但OPENROWSET只返回一个记录。

OPENXML函数：通过XML文档提供行集视图。

1. 聚合函数

聚合函数是对一组值操作，返回单一的汇总值。聚合函数在以下情况下，允许作为表达式使用：SELECT语句的选择列表（子查询或外部查询）；COMPUTE或COMPUTE BY子句；HAVING子句。

1. 标量函数

标量函数的特点：输入参数的类型为基本类型，返回值也为基本类型。

 

常用的系统标量函数：

 

数学函数：

ABS(数字表达式)  返回给定数字表达式的绝对值

示例：SELECT ABS(-5.0),ABS(0.0),ABS(8.0)

RAND函数：RAND([种子])  返回0-1之间的一个随机值。参数“种子”是指定种子值的整型表达式，返回值类型为float，如果未指定，则随机分配种子值。对于指定的种子值，返回的结果始终相同。

示例：如下程序通过RAND函数返回随机值

DECLARE @count int

SET @count =5

SELECT RAND(@count)

字符串处理函数：

ASCII(字符串表达式) 返回字符表达式最左端字符的ASCII值，返回值为整型。

示例：查找字符串‘sql’最左端字符的ASCII值

SELECT ASCII(‘sql’)

CHAR(整型表达式) 将ASCII码转换为字符。“整型表达式”为介于0-255之间的整数，返回值为字符型。

LEFT(字符表达式,整型表达式)  返回从字符串左边开始指定个数的字符，返回值为varchar型。

示例：返回课程名最左边的4个字符

SELECT LEFT(课程名,4)

FROM kcb

ORDER BY 课程号

LTRIM(字符表达式) 删除“字符表达式”字符串中的前导空格，并返回字符串。

示例：使用LTRIM函数删除字符变量中的起始空格

DECLARE @string varchar(40)

SET @string = ‘     中国，是一个古老而伟大的国家’

SELECT LTRIM(@string)

SELECT @string

REPLACE(‘字符串表达式1’,’ 字符串表达式2’,’ 字符串表达式3’)  用“字符串表达式3”替换“字符串表达式1”中包含的“字符串表达式2”，并返回替换后的表达式（返回值为字符型）

SUBSTRING(表达式,起始,长度)  返回表达式中指定的部分数据。参数“表达式”可以是字符串、二进制串、text、image字段或表达式；“起始”、“长度”均为整型，前者指定字符串的开始位置，后者指定字符串的长度（要返回字节数）。

SUBSTRING函数返回值不同于给定表达式的情况

给定的表达式	返回值类型	给定的表达式	返回值类型	给定的表达式	返回值类型
text	varchar	iamge	varbinary	ntext	nvarchar

示例：显示第1列为xsb表中的姓，在第2列中为学生名

SELECT TOP(4) SUBSTRING(姓名,1,1), SUBSTRING(姓名,2,LEN(姓名)-1)

FROM xsb

ORDER BY 姓名

示例：显示字符串“China”中每个字符的ASCII值和字符。

DECLARE @position int, @string char(8)

SET @position = 1

SET @string = ‘China’

WHILE @position <= DATALENGTH(@string)

BEGIN

SELECT ASCII(SUBSTRING(@string,@position,1)) AS ASCII码,

CHAR(ASCII(SUBSTRING(@string,@position,1))) AS 字符

SET @position = @position +1

END

系统函数:

CAST和CONVERT函数:功能都是实现数据类型转换,但CONVERT的功能更强，常用的类型转换有以下几种情况：日期-字符型，字符型-日期型，数值型-字符型。

CAST（表达式 AS 数据类型[长度]）

CONVERT(数据类型[（长度）]，表达式[,类型]）

示例：检索总学分在50-59分的学生姓名，并将总学分转换为char（20）。

使用CAST实现：

SELECT 姓名,总学分

FROM xsb

WHERE CAST(总学分 AS char(20)) LIKE ‘5_’

使用CONVERT实现:

SELECT 姓名,总学分

FROM xsb

WHERE CONVERT(char(20),总学分) LIKE ‘5_’

游标函数:

格式如下L(1)@@CURSOR_ROWS

返回最后打开的游标中当前存在的满足条件的行数.返回值为0时表示游标未打开;为-1时表示游标为动态游标;为-m时表示游标被异步填充,返回值(-m)是键集中当前的行数;为n时表示游标已完全填充,返回值(n)是游标中的总行数.

示例:声明一个游标,并用SELECT显示@@CURSOR_ROWS的值

SELECT @@CURSOR_ROWS

DECLARE student_cursor CURSOR

FOR SELECT 姓名FROM xsb

OPEN student_cursor

FETCH NEXT FROM student_cursor

SELECT @@CURSOR_ROWS

CLOSE student_cursor

DEALLOCATE student_cursor

(2)CURSOR_STATUS函数语法格式:

CURSOR_STATUS

(

{‘本地’,’游标名’}            /*指明数据源为本地游标*/

|{‘全局’,’游标名’}           /*指明数据源为全局游标*/

|{‘变量’,’游标变量’}         /*指明数据源为游标变量*/  

)

返回值	游标名或游标变量	返回值	游标名或游标变量
1	游标的结果集至少有一行	-2	游标不可用
0	由标的结果集为空*	-3	指定的游标不存在
-1	游标被关闭

 

示例:用@@FETCH_STATUS控制一个WHILE循环中的游标活动

DECLARE @name char(20), @num char(6)

DECLARE student_cur CURSOR

FOR

SELECT 姓名,学号 FROM pxscj.dbo.xsb

OPEN student_cur

FETCH NEXT FROM student_cur INTO @name,@num

SELECT @name, @num

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM student_cur

END

CLOSE student_cur

DEALLOCATE student_cur

 

 

 

5.6 用户定义函数:CREATE/ALTER/DROP FUNCTION

 

用户在编程时常常需要将多个T-SQL语句组成子程序,以便反复调用,可以通过自定义函数实现,根据用户定义函数返回值的类型可以分为两类:

1. 标量函数:用户定义函数返回值为标量值,这样的函数称为标量函数
2. 表值函数:返回值为整个表的用户定义函数为表值函数.根据函数主体的定义方式,表值函数又分为内嵌表值函数和多语句表值函数.若用户定义函数包含单个SELECT语句且该语句可更新,则该函数返回的表也可更新,这样的函数称为内嵌表值函数;若定义函数包含多个SELECT语句,则该函数返回的表不可更新,这样的函数称为多语句表值函数,创建用户定义函数可以使用CREATE FUNCTION命令,利用ALTER FUNCTION命令可以对用户定义函数进行修改,用DROP FUNCTION命令可以删除用户定义函数.

5.6.1标量函数:SELECT/EXEC

定义形式如下:

CREATE FUNCTION [架构名.] 函数名

(参数1 [AS] 类型1 [=默认值] [,…参数n [AS] 类型n [=默认值]])

RETURNS 返回值类型

[WITH 选项]

[AS]

BEGIN

函数体

RETURN 标量表达式

END

 

示例:创建用户定义函数,实现计算全体学生某门功课平均成绩的功能.

1. 创建用户定义函数:

CREATE FUNCTION average(@num char(20)) RETURNS int

AS

BEGIN

DECLARE @aver int

SELECT @aver=

(

SELECT avg(成绩)

FROM cjb

WHERE 课程号=@num

GROUP BY 课程号

)

RETURN @aver

END

1. 调用用户定义函数

DECLARE @course1 char(20)          /*定义局部变量*/

DECLARE @aver1 int

SELECT @course1 = ‘101’            /*给局部变量赋值*/

SELECT @aver1= dbo.average(@course1)      /*调用用户函数,并将返回值给局部变量*/

SELECT @aver1 AS ‘101课程的平均成绩’  /*显示局部变量的值*/

1. 在pxscj中建立一个course表,并将一个字段定义为计算列.

CREATE TABLE course

(

cno int,         /*课程号*/

cname nchar(20), /*课程名*/

credit int, /*学分*/

aver AS      /*将此列定义为计算列*/

(

dbo.average(cno)

)

)