第08章 Transcat-SQL程序设计
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
3continue语句回到循环的第一行命令重新开始循环例计算从1加到100declaremyresultintmyvarintsetmyvar0setmyresult0whilemyvar100beginsetmyresultmyresultmyvarsetmyvarmyvar1endprintmyresult例826whileselectavg成绩fromsc60beginupdatescset成绩成绩2selectmax成绩fromscselectmax成绩fromsc80breakelsecontinueendprint?平均成绩大于分或者最高成绩大于分?83用户自定义函数由用户自己定义的函数
4）ROUND函数
格式：ROUND (数字表达式，m) 功能：返回返回数字表达式并四舍五入为指定的长度或精 度。
【例】
SELECT ROUND(321.45678,0) AS 'm为0 的结果' SELECT ROUND(321.45678,1) AS 'm为1 的结果'
SELECT ROUND(321.45678,-1) AS 'm为-1 的结果'
第8章 Transact-SQL 程序设计
本章导读 本章主要介绍了变量、运算符、函数、流程控制语句和注解等语言元素。 学习目的与要求 （1）理解变量、运算符和流程控制语句。 （2）掌握函数的使用方法，能够使用流程控制语句编程。
8.1 素
Transact-SQL常用语言元
8.1.1 变量
变量有两种形式：
【例8.2】 DECLARE @varsex Char(2),@varsno Char(8) SET @varsex='女' SELECT 学号,姓名 FROM S WHERE 性别=@varsex 注意下述命令与上述命令的区别： DECLARE @varsex Char(2),@varsno Char(8) SET @varsex='女‘ SELECT @varsno=学号 FROM S WHERE 性别=@varsex SELECT 学号=@varsno 注意：向变量赋值的SELECT 语句不能与数据检索操 作结合使用。 如：SELECT @varsno=学号，姓名 FROM S WHERE 性别=@varsex 是错误的
格式：CAST (表达式 AS 数据类型 ) 功能： 将指定的表达式转换成对应的数据类型。
2）CONVERT函数
格式：CONVERT (数据类型[(长度)], 表达式[,样式]) 功能：样式是指日期格式样式。 【例8.16】 SELECT 学号+ '的成绩为：' + CAST(成绩AS Varchar(6)) AS '80分以上成绩' FROM SC WHERE CAST(成绩AS Char(6)) LIKE '8_‘
【例】 DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @State NCHAR(2) 若要为变量赋值，可以使用 SET 语句，也可以通过 SELECT 语句的选择列表中当前所引用值为变量赋值 语法为： SET @局部变量＝表达式 SELECT @局部变量＝表达式 【例】DECLARE @MyCounter SET @MyCounter=100 Print @MyCounter INT
【例】 SELECT @@VERSION
3．@@language 返回当前使用的语言名。 语法: @@LANGUAGE 返回类型: nvarchar
【例】
SELECT @@LANGUAGE AS 'Language Name'
8.1.2 DECLARE 语句
局部变量
1．格式
DECLARE @变量的名称 数据类型[ ,...n] 2．功能 1）DECLARE语句声明局部变量，变量初始化为 NULL。 2）局部变量名必须以@开头。
格式：MONTH (日期)
功能：返回表示指定日期中的月份的整数。返回类型为int。
DECLARE @Today DATETIME
SET @Today=GETDATE()
SELECT MONTH(@Today),DAY(@Today),YEAR(@Today)
8.1.4 函数
4．数据转换函数
1）CAST函数
5）RAND函数 格式：RAND ([seed]) 功能：返回 0 到1 之间的随机float 值。参数seed为整型 表达式。
SELECT RAND() Random_Number SELECT EXP(1),SQRT(16),ABS(-5.3) SELECT ABS(-1.0),ABS(0.0),ABS(1.0) 6）三角函数SIN,COS, TAN等 【例】求sin30度的值 DECLARE @x float SET @x =sin(30*3.14/180) SELECT @x SELECT TAN(45*3.14/180)
8.1.4 函数
SQL Server 提供了许多内置函数，通过使用这些函数，可以 方便快捷的执行某些运算和操作。 1．字符函数 １）SUBSTRING 函数 格式：SUBSTRING (<字符表达式>,<m>[,<n>])
功能：从字符表达式中的第m个字符开始截取n个字符，形成 一个新字符串，m,n都是数值表达式。
【例】Select subs=substring('GOOD night',6,5) 【例8.5】检索所有学生的姓 SELECT DISTINCT SUBSTRING(姓名,1,1) FROM S 【例8.6】 DECLARE @SS VARCHAR(20)
SET @SS='我们是管理信息系学生'
SELECT X1=SUBSTRING(@SS,4,5),X2=SUBSTRING(@SS,9,2)
【例子】 update dbo.C set 课程号='C02‘ Where 课程名='数据库' if @@error<>0
print '有错误啊！‘
2．@@Version 返回 Microsoft® SQL Server™ 当前安装的日期、版 本和处理器类型。 语法：@@VERSION 返回类型：nvarchar
8.1.3 注释
1．格式
格式一： / * 注释文本* / 格式二： -- 注释文本 2．功能 多行的注释必须用 /* 和 */ 指明。用于多行注释的样 式规则是，第一行用 /* 开始，接下来的注释行用 ** 开始 ，并且用 */ 结束注释。 -- 注释可插入到单独行中或嵌套（只限 --）在命令行 的末端，用 -- 插入的注释由换行字符分界。 注释没有最大长度限制。服务器将不运行注释文本。
8.1.4 函数
12）LEN函数 格式： LEN (字符表达式)
功能： 返回给定字符串表达式的字符个数，不包含尾随空 格。
select len('Select ')
13）ASCII函数 格式： ASCII(字符表达式) 功能： 返回给定字符串表达式的最左端字符的ASCII码值。 返回整型值。
14）CHAR函数
2）LTRIM函数 格式：LTRIM (<字符表达式>) 功能：删除字符串起始空格函数，返回varchar类 型数据 3）RTRIM函数 格式：RTRIM (<字符表达式>) 功能：删除字符串尾随空格函数，返回varchar类 型数据。 【例】
DECLARE @SS VARCHAR(20) SET @SS=' 中华人民共和国' SELECT '我爱'+LTRIM(@SS)+RTRIM('
2）EXP函数 格式：EXP (数字表达式) 功能： 返回给定数字表达式的指数值。参数数字表达式是 float 类型的表达式。返回类型为float。 3）SQRT函数 格式：SQRT(数字表达式)
功能： 返回给定数字表达式的平方根。参数数字表达式是 float 类型的表达式。返回类型为float。
8.1.4 函数
8.1.4 函数
3．日期和时间函数
ቤተ መጻሕፍቲ ባይዱ
1）DATEADD函数
格式： DATEADD (日期参数, 数字, 日期) 功能： 在向指定日期加上一段时间的基础上，返回新的 datetime 值。日期参数规定了新值的类型。参数有：Year、 Month、Day、Week、Hour 【例8.11】
SELECT 姓名,出生日期,DATEADD(day,21,出生日 期) AS newtime FROM S
SELECT LEFT(姓名,1) AS 姓,RIGHT(电话,4) AS 电话 后四位 FROM S ORDER BY 学号
6）UPPER函数 格式：UPPER(<字符表达式>) 功能：将小写字符数据转换为大写的字符表达式， 返回varchar类型数据。 7）LOWER函数 格式：LOWER (<字符表达式>) 功能：将大写字符数据转换为小写的字符表达式， 返回varchar类型数据。 8）REVERSE函数 格式：REVERSE (<字符表达式>) 功能：返回字符表达式的反转。返回varchar类型数 据。 【例】print REVERSE(‘他打我')
格式： CHARINDEX (表达式1 , 表达式2 [ , m ] )
功能： 在表达式2的第m个字符开始查找表达式1起始字符 位臵。m是整数表达式，如果m是负数或缺省，则将从表达式2 的起始位臵开始搜索。返回int类型数据。
【例】SELECT CHARINDEX('blue','redgreenblue') as 起始位臵,LEN('blue') AS 长度
她是我们的祖国 ')
8.1.4 函数
4）RIGHT函数
格式：RIGHT (<字符表达式>,<数据表达式>) 功能：返回字符串中从右边开始指定个数的字符，返回 varchar类型数据。
5）LEFT函数
格式：LEFT (<字符表达式>,<数据表达式>) 功能：返回字符串中从左边开始指定个数的字符，返回 varchar类型数据。 【8.7】
用户自定义的局部变量 局部变量必须先定义，后使用，被引用时要在其 名称前加上标志“＠”。 系统提供的全局变量
全局变量被引用时要在其名称前加上标志“＠＠ ”。全局变量由SQL事先定义好了的，用户不能参与 定义，因此对用户来说是只读的。
几个使用全局变量的例子。 1．@@ERROR 返回最后执行的 Transact-SQL 语句的错误代码。 语法:@@ERROR 返回类型:integer 注释:当完成 Transact-SQL 语句的执行时，如果语 句执行成功，则 @@ERROR 设置为 0。若出现一个错 误，则返回一条错误信息。@@ERROR 返回此错误信 息代码，直到另一条 Transact-SQL 语句被执行。可以 在 sysmessages 系统表（在master库中）中查看与 @@ERROR 错误代码相关的文本信息。
格式： CHAR(整数表达式) 功能： 用于将ASCII码转换为字符，整数表达式的取值范围 为0到255之间的整数，返回字符型数据值。
【例】select char(ascii('Select'))
8.1.4 函数
2．数学函数
1）ABS函数 格式：ABS(数字表达式)
功能： 返回给定数字表达式的绝对值。
2）GETDATE函数 格式： GETDATE () 【】
PRINT '现在的日期是：'+CAST(GETDATE()AS char(50))
8.1.4 函数
3）DAY函数
格式： DAY (日期)
功能：返回代表指定日期的“日”部分的整数。返回类型为 int。 4）YEAR函数 格式：YEAR(日期) 功能：返回表示指定日期中的年份的整数。返回类型为int。 5）MONTH函数
8.1.4 函数
【例8.8】
SELECT TOP 3 LOWER(课程号) AS Lower,UPPER(课程号) AS Upper,REVERSE(课程号) AS Reverse FROM SC 9）SPACE函数
格式：SPACE(<整数表达式>) 功能：返回由重复的空格组成的字符串。整数表达式的值 表示空格个数。返回char类型数据。 【8.9】 SELECT RTRIM(姓名)+','+SPACE(8)+LTRIM(系) AS 学生 所在系
SELECT 学号+ '的成绩为：'+
convert(Varchar(6),成绩) AS '80分以上成绩'FROM SC WHERE convert(Varchar(6),成绩) LIKE '8_'
FROM S ORDER BY 姓名
8.1.4 函数
10）STUFF函数
格式：STUFF(字符表达式1, m ,n , 字符表达式2 )
功能：删除指定长度的字符并在指定的起始点插入另一组 字符。m,n是整数，m指定删除和插入的开始位臵,n指定要删除 的字符数,最多删除到最后一个字符。如果m或n 是负数，则返 回空字符串。如果m比字符表达式1 长，则返回空字符串。返 回char类型数据。 【例】SELECT STUFF('redgreenblue',4,5,'black') 11）CHARINDEX函数
4）ROUND函数
格式：ROUND (数字表达式，m) 功能：返回返回数字表达式并四舍五入为指定的长度或精 度。
【例】
SELECT ROUND(321.45678,0) AS 'm为0 的结果' SELECT ROUND(321.45678,1) AS 'm为1 的结果'
SELECT ROUND(321.45678,-1) AS 'm为-1 的结果'
第8章 Transact-SQL 程序设计
本章导读 本章主要介绍了变量、运算符、函数、流程控制语句和注解等语言元素。 学习目的与要求 （1）理解变量、运算符和流程控制语句。 （2）掌握函数的使用方法，能够使用流程控制语句编程。
8.1 素
Transact-SQL常用语言元
8.1.1 变量
变量有两种形式：
【例8.2】 DECLARE @varsex Char(2),@varsno Char(8) SET @varsex='女' SELECT 学号,姓名 FROM S WHERE 性别=@varsex 注意下述命令与上述命令的区别： DECLARE @varsex Char(2),@varsno Char(8) SET @varsex='女‘ SELECT @varsno=学号 FROM S WHERE 性别=@varsex SELECT 学号=@varsno 注意：向变量赋值的SELECT 语句不能与数据检索操 作结合使用。 如：SELECT @varsno=学号，姓名 FROM S WHERE 性别=@varsex 是错误的
格式：CAST (表达式 AS 数据类型 ) 功能： 将指定的表达式转换成对应的数据类型。
2）CONVERT函数
格式：CONVERT (数据类型[(长度)], 表达式[,样式]) 功能：样式是指日期格式样式。 【例8.16】 SELECT 学号+ '的成绩为：' + CAST(成绩AS Varchar(6)) AS '80分以上成绩' FROM SC WHERE CAST(成绩AS Char(6)) LIKE '8_‘
【例】 DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @State NCHAR(2) 若要为变量赋值，可以使用 SET 语句，也可以通过 SELECT 语句的选择列表中当前所引用值为变量赋值 语法为： SET @局部变量＝表达式 SELECT @局部变量＝表达式 【例】DECLARE @MyCounter SET @MyCounter=100 Print @MyCounter INT
【例】 SELECT @@VERSION
3．@@language 返回当前使用的语言名。 语法: @@LANGUAGE 返回类型: nvarchar
【例】
SELECT @@LANGUAGE AS 'Language Name'
8.1.2 DECLARE 语句
局部变量
1．格式
DECLARE @变量的名称 数据类型[ ,...n] 2．功能 1）DECLARE语句声明局部变量，变量初始化为 NULL。 2）局部变量名必须以@开头。
格式：MONTH (日期)
功能：返回表示指定日期中的月份的整数。返回类型为int。
DECLARE @Today DATETIME
SET @Today=GETDATE()
SELECT MONTH(@Today),DAY(@Today),YEAR(@Today)
8.1.4 函数
4．数据转换函数
1）CAST函数
5）RAND函数 格式：RAND ([seed]) 功能：返回 0 到1 之间的随机float 值。参数seed为整型 表达式。
SELECT RAND() Random_Number SELECT EXP(1),SQRT(16),ABS(-5.3) SELECT ABS(-1.0),ABS(0.0),ABS(1.0) 6）三角函数SIN,COS, TAN等 【例】求sin30度的值 DECLARE @x float SET @x =sin(30*3.14/180) SELECT @x SELECT TAN(45*3.14/180)
8.1.4 函数
SQL Server 提供了许多内置函数，通过使用这些函数，可以 方便快捷的执行某些运算和操作。 1．字符函数 １）SUBSTRING 函数 格式：SUBSTRING (<字符表达式>,<m>[,<n>])
功能：从字符表达式中的第m个字符开始截取n个字符，形成 一个新字符串，m,n都是数值表达式。
【例】Select subs=substring('GOOD night',6,5) 【例8.5】检索所有学生的姓 SELECT DISTINCT SUBSTRING(姓名,1,1) FROM S 【例8.6】 DECLARE @SS VARCHAR(20)
SET @SS='我们是管理信息系学生'
SELECT X1=SUBSTRING(@SS,4,5),X2=SUBSTRING(@SS,9,2)
【例子】 update dbo.C set 课程号='C02‘ Where 课程名='数据库' if @@error<>0
print '有错误啊！‘
2．@@Version 返回 Microsoft® SQL Server™ 当前安装的日期、版 本和处理器类型。 语法：@@VERSION 返回类型：nvarchar
8.1.3 注释
1．格式
格式一： / * 注释文本* / 格式二： -- 注释文本 2．功能 多行的注释必须用 /* 和 */ 指明。用于多行注释的样 式规则是，第一行用 /* 开始，接下来的注释行用 ** 开始 ，并且用 */ 结束注释。 -- 注释可插入到单独行中或嵌套（只限 --）在命令行 的末端，用 -- 插入的注释由换行字符分界。 注释没有最大长度限制。服务器将不运行注释文本。
8.1.4 函数
12）LEN函数 格式： LEN (字符表达式)
功能： 返回给定字符串表达式的字符个数，不包含尾随空 格。
select len('Select ')
13）ASCII函数 格式： ASCII(字符表达式) 功能： 返回给定字符串表达式的最左端字符的ASCII码值。 返回整型值。
14）CHAR函数
2）LTRIM函数 格式：LTRIM (<字符表达式>) 功能：删除字符串起始空格函数，返回varchar类 型数据 3）RTRIM函数 格式：RTRIM (<字符表达式>) 功能：删除字符串尾随空格函数，返回varchar类 型数据。 【例】
DECLARE @SS VARCHAR(20) SET @SS=' 中华人民共和国' SELECT '我爱'+LTRIM(@SS)+RTRIM('
2）EXP函数 格式：EXP (数字表达式) 功能： 返回给定数字表达式的指数值。参数数字表达式是 float 类型的表达式。返回类型为float。 3）SQRT函数 格式：SQRT(数字表达式)
功能： 返回给定数字表达式的平方根。参数数字表达式是 float 类型的表达式。返回类型为float。
8.1.4 函数
8.1.4 函数
3．日期和时间函数
ቤተ መጻሕፍቲ ባይዱ
1）DATEADD函数
格式： DATEADD (日期参数, 数字, 日期) 功能： 在向指定日期加上一段时间的基础上，返回新的 datetime 值。日期参数规定了新值的类型。参数有：Year、 Month、Day、Week、Hour 【例8.11】
SELECT 姓名,出生日期,DATEADD(day,21,出生日 期) AS newtime FROM S
SELECT LEFT(姓名,1) AS 姓,RIGHT(电话,4) AS 电话 后四位 FROM S ORDER BY 学号
6）UPPER函数 格式：UPPER(<字符表达式>) 功能：将小写字符数据转换为大写的字符表达式， 返回varchar类型数据。 7）LOWER函数 格式：LOWER (<字符表达式>) 功能：将大写字符数据转换为小写的字符表达式， 返回varchar类型数据。 8）REVERSE函数 格式：REVERSE (<字符表达式>) 功能：返回字符表达式的反转。返回varchar类型数 据。 【例】print REVERSE(‘他打我')
格式： CHARINDEX (表达式1 , 表达式2 [ , m ] )
功能： 在表达式2的第m个字符开始查找表达式1起始字符 位臵。m是整数表达式，如果m是负数或缺省，则将从表达式2 的起始位臵开始搜索。返回int类型数据。
【例】SELECT CHARINDEX('blue','redgreenblue') as 起始位臵,LEN('blue') AS 长度
她是我们的祖国 ')
8.1.4 函数
4）RIGHT函数
格式：RIGHT (<字符表达式>,<数据表达式>) 功能：返回字符串中从右边开始指定个数的字符，返回 varchar类型数据。
5）LEFT函数
格式：LEFT (<字符表达式>,<数据表达式>) 功能：返回字符串中从左边开始指定个数的字符，返回 varchar类型数据。 【8.7】
用户自定义的局部变量 局部变量必须先定义，后使用，被引用时要在其 名称前加上标志“＠”。 系统提供的全局变量
全局变量被引用时要在其名称前加上标志“＠＠ ”。全局变量由SQL事先定义好了的，用户不能参与 定义，因此对用户来说是只读的。
几个使用全局变量的例子。 1．@@ERROR 返回最后执行的 Transact-SQL 语句的错误代码。 语法:@@ERROR 返回类型:integer 注释:当完成 Transact-SQL 语句的执行时，如果语 句执行成功，则 @@ERROR 设置为 0。若出现一个错 误，则返回一条错误信息。@@ERROR 返回此错误信 息代码，直到另一条 Transact-SQL 语句被执行。可以 在 sysmessages 系统表（在master库中）中查看与 @@ERROR 错误代码相关的文本信息。
格式： CHAR(整数表达式) 功能： 用于将ASCII码转换为字符，整数表达式的取值范围 为0到255之间的整数，返回字符型数据值。
【例】select char(ascii('Select'))
8.1.4 函数
2．数学函数
1）ABS函数 格式：ABS(数字表达式)
功能： 返回给定数字表达式的绝对值。
2）GETDATE函数 格式： GETDATE () 【】
PRINT '现在的日期是：'+CAST(GETDATE()AS char(50))
8.1.4 函数
3）DAY函数
格式： DAY (日期)
功能：返回代表指定日期的“日”部分的整数。返回类型为 int。 4）YEAR函数 格式：YEAR(日期) 功能：返回表示指定日期中的年份的整数。返回类型为int。 5）MONTH函数
8.1.4 函数
【例8.8】
SELECT TOP 3 LOWER(课程号) AS Lower,UPPER(课程号) AS Upper,REVERSE(课程号) AS Reverse FROM SC 9）SPACE函数
格式：SPACE(<整数表达式>) 功能：返回由重复的空格组成的字符串。整数表达式的值 表示空格个数。返回char类型数据。 【8.9】 SELECT RTRIM(姓名)+','+SPACE(8)+LTRIM(系) AS 学生 所在系
SELECT 学号+ '的成绩为：'+
convert(Varchar(6),成绩) AS '80分以上成绩'FROM SC WHERE convert(Varchar(6),成绩) LIKE '8_'
FROM S ORDER BY 姓名
8.1.4 函数
10）STUFF函数
格式：STUFF(字符表达式1, m ,n , 字符表达式2 )
功能：删除指定长度的字符并在指定的起始点插入另一组 字符。m,n是整数，m指定删除和插入的开始位臵,n指定要删除 的字符数,最多删除到最后一个字符。如果m或n 是负数，则返 回空字符串。如果m比字符表达式1 长，则返回空字符串。返 回char类型数据。 【例】SELECT STUFF('redgreenblue',4,5,'black') 11）CHARINDEX函数