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 系统内置函数
行集函数、聚合函数、标量函数
- 确定性函数:每次使用特定的输入值集调用该函数时,总是返回相同的结果。
- 非确定性函数:每次使用特定的输入值集调用函数时,可能返回不同的结果。
- 行集函数
行集函数是返回值为对象的函数,该对象可在T-SQL语句中作为表引用。所有行集函数都是非确定性的,即每次用一组特定参数调用它们时,所返回的结果不总是相同的。
SQL-Server提供如下行集函数:
CONTAINSTABLE:对于基于字符类型的列,按照一定的搜索条件进行精确或模糊匹配,然后返回一个表,该表可能为空。
FREETEXTTABLE:为基于字符类型的列返回一个表,其中的值符合指定文本的含义,但不符合确切的表达方式。
OPENDATASOURCE:提供与数据源的连接。
OPENQUERY:在指定的数据源上执行查询。可以在查询的FROM子句中像引用基本表一样引用OPENQUERY函数,虽然查询可能返回多个记录,但OPENQUERY只返回一个记录。
OPENROWSET:包含访问OLE DB数据源中远程数据所需的全部链接信息。可在查询的FROM子句中像引用基本表一样引用OPENROWSET函数,虽然查询可能返回多个记录,但OPENROWSET只返回一个记录。
OPENXML函数:通过XML文档提供行集视图。
- 聚合函数
聚合函数是对一组值操作,返回单一的汇总值。聚合函数在以下情况下,允许作为表达式使用:SELECT语句的选择列表(子查询或外部查询);COMPUTE或COMPUTE BY子句;HAVING子句。
- 标量函数
标量函数的特点:输入参数的类型为基本类型,返回值也为基本类型。
常用的系统标量函数:
数学函数:
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语句组成子程序,以便反复调用,可以通过自定义函数实现,根据用户定义函数返回值的类型可以分为两类:
- 标量函数:用户定义函数返回值为标量值,这样的函数称为标量函数
- 表值函数:返回值为整个表的用户定义函数为表值函数.根据函数主体的定义方式,表值函数又分为内嵌表值函数和多语句表值函数.若用户定义函数包含单个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
示例:创建用户定义函数,实现计算全体学生某门功课平均成绩的功能.
- 创建用户定义函数:
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
- 调用用户定义函数
DECLARE @course1 char(20) /*定义局部变量*/
DECLARE @aver1 int
SELECT @course1 = ‘101’ /*给局部变量赋值*/
SELECT @aver1= dbo.average(@course1) /*调用用户函数,并将返回值给局部变量*/
SELECT @aver1 AS ‘101课程的平均成绩’ /*显示局部变量的值*/
- 在pxscj中建立一个course表,并将一个字段定义为计算列.
CREATE TABLE course
(
cno int, /*课程号*/
cname nchar(20), /*课程名*/
credit int, /*学分*/
aver AS /*将此列定义为计算列*/
(
dbo.average(cno)
)
)