sql 2019 第08章PPT课件内容

第第8 8章章 Transact-SQL Transact-SQL 程序设计程序设计u本章导读本章导读本章主要介绍了变量、运算符、函数、流程控制语句和注解等语言元素。本章主要介绍了变量、运算符、函数、流程控制语句和注解等语言元素。u学习目的与要求学习目的与要求（1 1）理解变量、运算符和流程控制语句。）理解变量、运算符和流程控制语句。（2 2）掌握函数的使用方法，能够使用流程控制语句编程。）掌握函数的使用方法，能够使用流程控制语句编程。8.1Transact-SQL常用语言元素常用语言元素8.1.1 变量变量变量有两种形式：变量有两种形式：l 用户自定义的局部变量用户自定义的局部变量局部变量必须先定义，后使用，被引用时要在其名称前加上标志“”。l 系统提供的全局变量系统提供的全局变量全局变量被引用时要在其名称前加上标志“”。8.1.2 DECLARE 语句语句1 1格式格式格式一：格式一：DECLARE DECLARE 变量的名称变量的名称 数据类型数据类型,.n,.n 格式二：格式二：SELECT SELECT 变量的名称变量的名称=表达式表达式,.n,.n2 2功能功能1 1）DECLAREDECLARE语句声明局部变量，变量初始化为语句声明局部变量，变量初始化为 NULLNULL。2 2）格式二，在声明局部变量的同时，给变量赋值。）格式二，在声明局部变量的同时，给变量赋值。3 3）局部变量名必须以）局部变量名必须以 开头。开头。4 4）变量不能设置为变量不能设置为 text、ntext 或或 image 数据类型。数据类型。5 5）局部变量只能在表达式中出现。局部变量只能在表达式中出现。8.1.2 DECLARE 语句语句【例例8.1】定义局部变量定义局部变量varname,vardepartment，并，并为为varname赋值赋值“李丽李丽”，为，为vardepartment赋值赋值“是是管理信息系的学生管理信息系的学生”。DECLARE varname char(8),vardepartment char(30)SET varname=李丽李丽SET vardepartment=varname+是管理信息系的学生是管理信息系的学生SELECT varname as 姓名姓名,vardepartment as 介绍介绍8.1.2 DECLARE 语句语句【例例8.2】定义局部变量定义局部变量varsex,varsno，并利用这，并利用这些变量去查找女同学的姓名与学号。些变量去查找女同学的姓名与学号。use studentcourseDECLARE varsex char(2),varsno char(8)SET varsex=女女SELECT 学号学号,姓名姓名 FROM S WHERE 性别性别=varsex8.1.2 DECLARE 语句语句下面命令组的功能是将最后一个女同学的学号赋值给下面命令组的功能是将最后一个女同学的学号赋值给变量变量varsno。DECLARE varsex char(2),varsno char(8)SET varsex=女女SELECT varsno=学号学号 FROM S WHERE 性别性别=varsexSELECT varsno as 学号学号向变量赋值的向变量赋值的 SELECT 语句不能与数据检索操作结语句不能与数据检索操作结合使用。下述命令是错误的。合使用。下述命令是错误的。SELECT varsno=学号学号,姓名姓名 FROM S WHERE 性别性别=varsex8.1.2 DECLARE 语句语句【例例8.3】使用名为使用名为 find 的局部变量检索所有陈姓的局部变量检索所有陈姓学生的信息。学生的信息。USE STUDENTCOURSEDECLARE find varchar(30)SET find=陈陈%SELECT 姓名姓名,学号学号,系系 FROM S WHERE 姓名姓名 LIKE find8.1.2 DECLARE 语句语句【例例8.4】从从S中检索中检索1995年年1月月5日后出生的学生姓名日后出生的学生姓名与学号信息。与学号信息。DECLARE varsex char(2),vardate datetimeSET varsex=女女SET vardate=80/01/05SELECT 姓名姓名,学号学号,出生日期出生日期 FROM S WHERE 性别性别=varsex and 出生日期出生日期=vardate8.1.3 注释注释1 1格式格式格式一：格式一：/*注释文本注释文本*/格式二：格式二：-注释文本注释文本2 2功能功能多行的注释必须用多行的注释必须用 /*和和 */指明。用于多行注释的样指明。用于多行注释的样式规则是，第一行用式规则是，第一行用 /*开始，接下来的注释行用开始，接下来的注释行用 *开始开始，并且用，并且用 */结束注释。结束注释。-注释可插入到单独行中或嵌套（只限注释可插入到单独行中或嵌套（只限 -）在命令行）在命令行的末端，用的末端，用 -插入的注释由换行字符分界。插入的注释由换行字符分界。注释没有最大长度限制。服务器将不运行注释文本。注释没有最大长度限制。服务器将不运行注释文本。8.1.4 函数函数1 1字符函数字符函数）SUBSTRING SUBSTRING 函数函数格式：格式：SUBSTRING(,)功能：功能：从字符表达式中的第m个字符开始截取n个字符，形成一个新字符串，m,n都是数值表达式。【例【例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)8.1.4 函数函数2 2）LTRIMLTRIM函数函数格式：格式：LTRIM(LTRIM()功能：功能：删除字符串起始空格函数，返回删除字符串起始空格函数，返回varcharvarchar类型数据类型数据3 3）RTRIMRTRIM函数函数格式：格式：RTRIM(RTRIM()功能：功能：删除字符串尾随空格函数，返回删除字符串尾随空格函数，返回varcharvarchar类型数据。类型数据。DECLARE ss VARCHAR(20)set ss=中华人民共和国中华人民共和国SELECT 我爱我爱+LTRIM(ss)+RTRIM(她是我们的祖国她是我们的祖国 )8.1.4 函数函数1 1字符函数字符函数4 4）RIGHTRIGHT函数函数格式：格式：RIGHT(RIGHT(,)功能：功能：返回字符串中从右边开始指定个数的字符，返回返回字符串中从右边开始指定个数的字符，返回varcharvarchar类型数据。类型数据。5 5）LEFTLEFT函数函数格式：格式：LEFT(LEFT(,)功能：功能：返回字符串中从左边开始指定个数的字符，返回返回字符串中从左边开始指定个数的字符，返回varcharvarchar类型数据。类型数据。【例例8.78.7】返回每个电话后返回每个电话后4 4 位。返回每个学生名字中最左边位。返回每个学生名字中最左边的的1 1个字符。个字符。SELECT LEFT(SELECT LEFT(姓名姓名,1)as,1)as 姓姓,RIGHT(,RIGHT(电话电话,4)as,4)as 电话后四电话后四位位 FROM SFROM S ORDER BY ORDER BY 学号学号8.1.4 函数函数1 1字符函数字符函数6 6）UPPERUPPER函数函数格式：格式：UPPER(UPPER()功能：功能：将小写字符数据转换为大写的字符表达式，返回将小写字符数据转换为大写的字符表达式，返回varcharvarchar类型数据。类型数据。7 7）LOWERLOWER函数函数格式：格式：LOWER(LOWER()功能：功能：将大写字符数据转换为小写的字符表达式，返回将大写字符数据转换为小写的字符表达式，返回varcharvarchar类型数据。类型数据。8 8）REVERSEREVERSE函数函数格式：格式：REVERSE(REVERSE()功能：功能：返回字符表达式的反转。返回返回字符表达式的反转。返回varcharvarchar类型数据。类型数据。【例例8.8】以大写、小写两种方式显示课程号，显以大写、小写两种方式显示课程号，显示反转的课程号。示反转的课程号。SELECT top 3 LOWER(课程号课程号)as Lower,UPPER(课程课程号号)as Upper,REVERSE(课程号课程号)as Reverse FROM SC8.1.4 函数函数1 1字符函数字符函数9 9）SPACESPACE函数函数格式：格式：SPACE()功能：功能：返回由重复的空格组成的字符串。整数表达式的值表示空格个数。返回char类型数据。【例【例8.9】显示学生的姓名和所在系，之间用逗号和显示学生的姓名和所在系，之间用逗号和2个空格分隔。个空格分隔。SELECT RTRIM(姓名姓名)+,+SPACE(2)+LTRIM(系系)as 学学生所在系生所在系 FROM S ORDER BY 姓名姓名8.1.4 函数函数1 1字符函数字符函数1010）STUFFSTUFF函数函数格式：格式：STUFF(STUFF(字符表达式字符表达式1,m,n,1,m,n,字符表达式字符表达式2)2)功能：功能：删除指定长度的字符并在指定的起始点插入另一组删除指定长度的字符并在指定的起始点插入另一组字符。字符。m,nm,n是整数，是整数，m m指定删除和插入的开始位置指定删除和插入的开始位置,n,n指定要删指定要删除的字符数除的字符数,最多删除到最后一个字符。如果最多删除到最后一个字符。如果m m或或n n 是负数，是负数，则返回空字符串。如果则返回空字符串。如果m m比字符表达式比字符表达式1 1 长，则返回空字符串长，则返回空字符串。返回。返回charchar类型数据。类型数据。1111）CHARINDEXCHARINDEX函数函数格式：格式：CHARINDEX(CHARINDEX(表达式表达式1,1,表达式表达式2 ,m )2 ,m )功能：功能：在表达式在表达式2 2的第的第m m个字符开始查找表达式个字符开始查找表达式1 1起始字符起始字符位置。位置。m m是整数表达式，如果是整数表达式，如果m m是负数或缺省，则将从表达式是负数或缺省，则将从表达式2 2 的起始位置开始搜索。返回的起始位置开始搜索。返回intint类型数据。类型数据。8.1.4 函数函数1 1字符函数字符函数1212）LENLEN函数函数格式：格式：LEN(LEN(字符表达式字符表达式)功能：功能：返回给定字符串表达式的字符个数，不包含尾随空返回给定字符串表达式的字符个数，不包含尾随空格。格。1313）ASCIIASCII函数函数格式：格式：ASCII(ASCII(字符表达式字符表达式)功能：功能：返回给定字符串表达式的最左端字符的返回给定字符串表达式的最左端字符的ASCIIASCII码值码值。返回整型值。返回整型值。1414）CHARCHAR函数函数格式：格式：CHAR(CHAR(整数表达式整数表达式)功能：功能：用于将用于将ASCIIASCII码转换为字符，整数表达式的取值范码转换为字符，整数表达式的取值范围为围为0 0到到255255之间的整数，返回字符型数据值。之间的整数，返回字符型数据值。8.1.4 函数函数1 1字符函数字符函数【例例8.10】将字符串将字符串redgreenblue中的中的green替换成替换成black。判断。判断blue在字符串在字符串redgreenblue中的起始位置中的起始位置。判断。判断blue字符的长度。字符的长度。SELECT STUFF(redgreenblue,4,5,black)GOSELECT CHARINDEX(blue,redgreenblue)as 起始位置起始位置,LEN(blue)as 长度长度8.1.4 函数函数2 2数学函数数学函数1 1）ABSABS函数函数格式：格式：ABS(ABS(数字表达式数字表达式)功能：功能：返回给定数字表达式的绝对值。返回给定数字表达式的绝对值。2 2）EXPEXP函数函数格式：格式：EXP(EXP(数字表达式数字表达式)功能：功能：返回给定数字表达式的指数值。参数数字表达式是返回给定数字表达式的指数值。参数数字表达式是 float float 类型的表达式。返回类型为类型的表达式。返回类型为floatfloat。3 3）SQRTSQRT函数函数格式：格式：SQRT(SQRT(数字表达式数字表达式)功能：功能：返回给定数字表达式的平方根。参数数字表达式是返回给定数字表达式的平方根。参数数字表达式是 float float 类型的表达式。返回类型为类型的表达式。返回类型为floatfloat。8.1.4 函数函数2 2数学函数数学函数4 4）ROUNDROUND函数函数格式：格式：ROUND(ROUND(数字表达式，数字表达式，m)m)功能：功能：返回返回数字表达式并四舍五入为指定的长度或精返回返回数字表达式并四舍五入为指定的长度或精度。度。8.1.4 函数函数2 2数学函数数学函数5 5）RANDRAND函数函数格式：格式：RAND(seed)RAND(seed)功能：功能：返回返回 0 0 到到1 1 之间的随机之间的随机float float 值。参数值。参数seedseed为整型为整型表达式。表达式。SELECT rand()Random_NumberSELECT exp(1),sqrt(4),abs(-5.3)SELECT round(123.123456,0),round(123.123456,2),round(123.123456,-2)SELECT abs(-1.0),abs(0.0),abs(1.0)8.1.4 函数函数3 3日期和时间函数日期和时间函数1 1）DATEADDDATEADD函数函数格式：格式：DATEADD(DATEADD(日期参数日期参数,数字数字,日期日期)功能：功能：在向指定日期加上一段时间的基础上，返回新的在向指定日期加上一段时间的基础上，返回新的 datetime datetime 值。日期参数规定了新值的类型。参数有：值。日期参数规定了新值的类型。参数有：YearYear、MonthMonth、DayDay、WeekWeek、HourHour【例例8.118.11】查询每个学生出生查询每个学生出生2121天和天和2121年后的日期。年后的日期。SELECT SELECT 姓名姓名,出生日期出生日期,DATEADD(day,21,DATEADD(day,21,出生日期出生日期)AS)AS newtimenewtimeFROM SFROM SSELECT SELECT 姓名姓名,出生日期出生日期,DATEADD(year,21,DATEADD(year,21,出生日期出生日期)AS)AS newtimenewtimeFROM SFROM S8.1.4 函数函数3 3日期和时间函数日期和时间函数2 2）GETDATEGETDATE函数函数格式：格式：GETDATE()GETDATE()【例【例8.12】返回返回当前系统的日期和时间。当前系统的日期和时间。SELECT GETDATE()8.1.4 函数函数3 3日期和时间函数日期和时间函数3 3）DAYDAY函数函数格式：格式：DAY(DAY(日期日期)功能：功能：返回代表指定日期的返回代表指定日期的“日日”部分的整数。返回类型部分的整数。返回类型为为intint。【例例8.138.13】返回日期返回日期 03/12/200803/12/2008中的日。中的日。SELECT DAY(03/12/2008)AS Day NumberSELECT DAY(03/12/2008)AS Day Number 8.1.4 函数函数3 3日期和时间函数日期和时间函数4 4）YEARYEAR函数函数格式：格式：YEAR(YEAR(日期日期)功能：功能：返回表示指定日期中的年份的整数。返回类型为返回表示指定日期中的年份的整数。返回类型为intint。【例例8.148.14】从日期从日期 03/12/2008 03/12/2008 中返回年份数。中返回年份数。SELECT Year Number=YEAR(03/12/2008)SELECT Year Number=YEAR(03/12/2008)8.1.4 函数函数3 3日期和时间函数日期和时间函数5 5）MONTHMONTH函数函数格式：格式：MONTH(MONTH(日期日期)功能：功能：返回表示指定日期中的月份的整数。返回类型为返回表示指定日期中的月份的整数。返回类型为intint。【例例8.158.15】从日期从日期 03/12/2008 03/12/2008 中返回月份。中返回月份。SELECT MONTH(03/12/2008)as Month NumberSELECT MONTH(03/12/2008)as Month Number 8.1.4 函数函数4 4数据转换函数数据转换函数1 1）CASTCAST函数函数格式：格式：CAST(CAST(表达式表达式 AS AS 数据类型数据类型 )功能：功能：将指定的表达式转换成对应的数据类型。将指定的表达式转换成对应的数据类型。2 2）CONVERTCONVERT函数函数格式：格式：CONVERT(CONVERT(数据类型数据类型(长度长度),),表达式表达式,样式样式)功能：样式是指日期格式样式。功能：样式是指日期格式样式。8.1.4 函数函数4 4数据转换函数数据转换函数【例例8.168.16】将将SCSC表中的成绩列转换为表中的成绩列转换为char(10)char(10)，并显示成，并显示成绩在绩在8080分以上的学生的学号。分以上的学生的学号。SELECT SELECT 学号学号+的成绩为：的成绩为：+CAST(+CAST(成绩成绩 AS varchar(6)AS varchar(6)as 80as 80分以上成绩分以上成绩 FROM SC FROM SC WHERE CAST(WHERE CAST(成绩成绩 AS char(6)LIKE 8_AS char(6)LIKE 8_或或SELECT SELECT 学号学号+的成绩为：的成绩为：+CONVERT(varchar(6),+CONVERT(varchar(6),成绩成绩)as 80as 80分以上成绩分以上成绩 FROM SC FROM SC WHERE CONVERT(varchar(6),WHERE CONVERT(varchar(6),成绩成绩)LIKE 8_)LIKE 8_8.1.4 函数函数5 5系统函数系统函数函数函数,DB_NAME(),DB_NAME()的功能是返回数据库的名称。的功能是返回数据库的名称。函数函数HOST_ NAME()HOST_ NAME()的功能是返回服务器端计算机的名称。的功能是返回服务器端计算机的名称。函数函数HOST_ID()HOST_ID()的功能是返回服务器端计算机的的功能是返回服务器端计算机的IDID号。号。函数函数USER_NAME()USER_NAME()的功能是返回用户的数据库用户名。的功能是返回用户的数据库用户名。【例例8.178.17】返回服务器端计算机的名称，服务器端计算机返回服务器端计算机的名称，服务器端计算机的的IDID号，数据库的用户名，数据库的名称。号，数据库的用户名，数据库的名称。SELECT HOST_NAME()as SELECT HOST_NAME()as 服务器端计算机的名称服务器端计算机的名称,HOST_ID()as HOST_ID()as 服务器端计算机的服务器端计算机的IDID号号,USER_NAME()as,USER_NAME()as 数据库数据库的用户名的用户名,DB_NAME()as,DB_NAME()as 数据库的名称数据库的名称8.1.5 PRINT1 1格式格式PRINT PRINT 文本字符串文本字符串|字符数据类型变量字符数据类型变量|返回字符串结返回字符串结果的函数果的函数|字符串表达式字符串表达式2 2功能功能将用户定义的消息返回客户端。必须是将用户定义的消息返回客户端。必须是 char char 或或 varcharvarchar，或者能够隐式转换为这些数据类型。或者能够隐式转换为这些数据类型。若要打印用户定义的错误信息（该消息中包含可由若要打印用户定义的错误信息（该消息中包含可由 ERROR ERROR 返回的错误号），请使用返回的错误号），请使用 RAISERROR RAISERROR 而不要使用而不要使用 PRINTPRINT。【例例8.188.18】使用使用CONVERTCONVERT函数，将函数，将GETDATE GETDATE 函数的结果转换函数的结果转换为为 varchar varchar 数据类型，以字符的形式打印机器当前的时间。数据类型，以字符的形式打印机器当前的时间。PRINT PRINT 今天的日期是：今天的日期是：+RTRIM(CONVERT(varchar(30),+RTRIM(CONVERT(varchar(30),GETDATE()+.GETDATE()+.8.2 T-SQL 控制流语句控制流语句8.2.1 BEGIN.END语句语句1 1格式格式BEGINBEGIN Transact-SQL Transact-SQL 语句语句|语句块语句块 ENDEND2 2功能功能BEGIN.END BEGIN.END 语句将多个语句将多个SQL SQL 语句组合成一组语句块，并语句组合成一组语句块，并将些语句块视为一个单元。将些语句块视为一个单元。BEGIN.END BEGIN.END 语句块允许嵌套。语句块允许嵌套。8.2 T-SQL 控制流语句控制流语句8.2.2 IF.ELSE语句语句1 1格式格式IF IF 逻辑表达式逻辑表达式 SQL SQL 语句语句1|1|语句块语句块1 1 ELSE ELSESQL SQL 语句语句2|2|语句块语句块2 2 2 2功能功能IF.ELSEIF.ELSE语句是双分支条件判断语句，根据某个条件的成语句是双分支条件判断语句，根据某个条件的成立与否，来决定执行哪组语句。立与否，来决定执行哪组语句。8.2 T-SQL 控制流语句控制流语句8.2.2 IF.ELSE语句语句8.2 T-SQL 控制流语句控制流语句【例例8.19】至少有一门课程成绩大于至少有一门课程成绩大于80分的分的学生人数。学生人数。DECLARE num intSELECT num=(SELECT count(distinct 学号学号)From sc WHERE 成绩成绩80)IF num0 SELECT num as 成绩成绩80的人数的人数8.2 T-SQL 控制流语句控制流语句【例例8.20】如果数据库平均成绩高于如果数据库平均成绩高于75分，则显示信分，则显示信息息“平均成绩高于平均成绩高于75分分”。DECLARE text1 char(20)SET text1=平均成绩高于平均成绩高于75分分IF(SELECT avg(成绩成绩)FROM SC,C WHERE SC.课程号课程号=C.课程号课程号 and C.课程名课程名=数据库数据库)=75 BEGIN SET text1=平均成绩平均成绩=75分分 ENDSELECT text1 AS 平均成绩平均成绩8.2 T-SQL 控制流语句控制流语句【例例8.21】如果课程如果课程C03的平均成绩低于的平均成绩低于60，显示，显示“不不及格及格”；如果高于；如果高于9