[理学]附加第8章 T-SQL 编程ppt课件

1、第8章T-SQLT-SQL编程编程目的目的 掌握如何定义变量并赋值掌握如何定义变量并赋值 掌握如何输出显示数据掌握如何输出显示数据 掌握掌握IF、WHILE、CASE逻辑控制语句逻辑控制语句 了解了解SQL中批处置的概念中批处置的概念运用变量运用变量 变量分为：变量分为：部分变量：部分变量：部分变量必需以标志部分变量必需以标志作为前缀作为前缀 ，如，如age部分变量的运用也是先声明，再赋值部分变量的运用也是先声明，再赋值 全局变量：全局变量：全局变量必需以标志全局变量必需以标志 作为前缀，如作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修正全全局变量由系统定义和维护，我

2、们只能读取，不能修正全局变量的值局变量的值 部分变量部分变量 例如：例如：DECLARE name varchar(8)DECLARE seat int声明部分变量声明部分变量 DECLARE 变量名变量名 数据类型数据类型赋值赋值 SET 变量名变量名 =值值 SELECT 变量名变量名 = 值值例如：例如：SET name=张三张三SELECT name=stuName FROM stuInfo WHERE stuNo=s25302 必需确保挑选出的记录只需必需确保挑选出的记录只需1条条 或 全局变量全局变量 全局变量都运用两个全局变量都运用两个标志作为前缀标志作为前缀 变量变量含义含义E

3、RROR最后一个最后一个T-SQL错误的错误号错误的错误号IDENTITY最后一次插入的标识值最后一次插入的标识值LANGUAGE当前使用的语言的名称当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目可以创建的同时连接的最大数目ROWCOUNT受上一个受上一个SQL语句影响的行数语句影响的行数SERVERNAME本地服务器的名称本地服务器的名称TRANSCOUNT当前连接打开的事务数当前连接打开的事务数VERSIONSQL Server的版本信息的版本信息SQL Server中的函数中的函数相当于相当于Java言语中的内置包言语中的内置包 字符串函数字符串函数 日期

4、函数日期函数 数学函数数学函数 系统函数系统函数字符串函数字符串函数SELECT STUFF(ABCDEFG, 2, 3, 我的音乐我的世界我的音乐我的世界)前往：前往：A我的音乐我的世界我的音乐我的世界EFG在一个字符串中，删除指定长度的在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字字符，并在该位置插入一个新的字符串符串STUFFSELECT CHARINDEX(ACCP,My Accp Course,1 )前往：前往：4用来寻觅一个指定的字符串在另一用来寻觅一个指定的字符串在另一个字符串中的起始位置个字符串中的起始位置CHARINDEX举例举例描画描画函数名函数名SELECT

5、 LEN(SQL Server课程课程)前往：前往：12前往传送给它的字符串长度前往传送给它的字符串长度LENSELECT LOWER(SQL ServerSELECT LOWER(SQL Server课程课程)前往：前往：sql serversql server课程课程把传送给它的字符串转换为小写把传送给它的字符串转换为小写LOWERSELECT UPPER(sql server课程课程)前往：前往：SQL SERVER课程课程把传送给它的字符串转换为大写把传送给它的字符串转换为大写UPPERSELECT LTRIM ( 周智宇周智宇 )前往：周智宇前往：周智宇 后面的空格保管后面的空格保管

6、去除字符左边的空格去除字符左边的空格LTRIMSELECT RTRIM ( 周智宇周智宇 )前往：前往： 周智宇前面的空格保管周智宇前面的空格保管去除字符右边的空格去除字符右边的空格RTRIMSELECT RIGHT(买卖提买卖提.吐尔松吐尔松,3)前往：吐尔松前往：吐尔松从字符串右边前往指定数目的字符从字符串右边前往指定数目的字符RIGHTSELECT REPLACE(莫乐可切莫乐可切.杨可杨可,可可,兰兰)前往：莫乐兰切前往：莫乐兰切.杨兰杨兰交换一个字符串中的字符交换一个字符串中的字符REPLACE日期函数日期函数SELECT DATEPART(day, 01/15/2000)前往：前往

7、：15日期中指定日期部分的整数方日期中指定日期部分的整数方式式DATEPARTSELECT GETDATE()前往：今天的日期前往：今天的日期获得当前的系统日期获得当前的系统日期GETDATE举例举例描画描画函数名函数名SELECT DATEADD(mm,4,01/01/99)前往：以当前的日期格式前往前往：以当前的日期格式前往05/01/99将指定的数值添加到指定的日将指定的数值添加到指定的日期部分后的日期期部分后的日期DATEADDSELECT DATEDIFF(mm,01/01/99,05/01/99)前往：前往：4两个日期之间的指定日期部分两个日期之间的指定日期部分的区别的区别DATE

8、DIFFSELECT DATENAME(dw,01/01/2000)前往：前往：Saturday日期中指定日期部分的字符串日期中指定日期部分的字符串方式方式DATENAME数学函数数学函数SELECT SQRT(9)前往：前往：3取浮点表达式的平方根取浮点表达式的平方根SqrtSELECT ABS(-43)前往：前往：43取数值表达式的绝对值取数值表达式的绝对值ABS举例举例描画描画函数名函数名SELECT CEILING(43.5)前往：前往：44前往大于或等于所给数字表达前往大于或等于所给数字表达式的最小整数式的最小整数CEILINGSELECT POWER(5,2)前往：前往：25取数值

9、表达式的幂值取数值表达式的幂值POWERSELECT ROUND(43.543,1)前往：前往：43.5将数值表达式四舍五入为指定将数值表达式四舍五入为指定精度精度ROUNDSELECT SIGN(-43)前往：前往：-1对于正数前往对于正数前往+1，对于负数前，对于负数前往往-1，对于，对于0 那么前往那么前往0SignSELECT FLOOR(43.5)前往：前往：43取小于或等于指定表达式的最取小于或等于指定表达式的最大整数大整数FLOOR系统函数系统函数SELECT USER_NAME(1)前往：从恣意数据库中前往前往：从恣意数据库中前往“dbo从给定的用户从给定的用户I DI D前往

10、用户名前往用户名USER_NAMESELECT CONVERT (VARCHAR (5)，12345)前往：字符串前往：字符串12345用来转变数据类型用来转变数据类型CONVERT 举例举例描画描画函数名函数名SELECT CURRENT_USER前往：他登录的用户名前往：他登录的用户名前往当前用户的名字前往当前用户的名字CURRENT_USER SELECT DATALENGTH (中国中国A盟盟)前往：前往：7前往用于指定表达式的字节前往用于指定表达式的字节数数DATALENGTHSELECT HOST_NAME()前往：他所登录的计算机的名字前往：他所登录的计算机的名字前往当前用户所登

11、录的计算前往当前用户所登录的计算机名字机名字HOST_NAME SELECT SYSTEM_USER前往：他当前所登录的用户名前往：他当前所登录的用户名前往当前所登录的用户称号前往当前所登录的用户称号SYSTEM_USER 案例分析案例分析1要求要求 某公司印了一批充值卡，卡的密码是随机生成的，如今出某公司印了一批充值卡，卡的密码是随机生成的，如今出现这个问题：现这个问题： 卡里面的卡里面的“O和和0哦和零哦和零“i和和1哎和一，用哎和一，用户反映说看不清楚，公司决议，把存储在数据库中的密码户反映说看不清楚，公司决议，把存储在数据库中的密码中一切的中一切的“哦都改成哦都改成“零，把一切的零，把

12、一切的“i都改成都改成“1；请编写请编写SQL语句实现以上要求；语句实现以上要求； 数据库表名：数据库表名：Card；密码字段名：；密码字段名：PassWord；案例分析案例分析1分析分析 这是更新语句，需求运用这是更新语句，需求运用UPDATE语句；语句； 由于牵涉到字符串的交换，需求运用到由于牵涉到字符串的交换，需求运用到SQL Server中的中的函数函数Replace；案例分析案例分析2要求要求在数据库表中有以下字符数据，如：在数据库表中有以下字符数据，如：13-113-1、13-213-2、13-313-3、13-1013-10、13-10013-100、13-10813-108、1

13、3-1813-18、13-1113-11、13-1513-15、14-114-1、14-214-2如今希望经过如今希望经过SQLSQL语句进展排序，并且首先要按照前半部分的数语句进展排序，并且首先要按照前半部分的数字进展排序，然后再按照后半部分的数字进展排需，输出要排成这样：字进展排序，然后再按照后半部分的数字进展排需，输出要排成这样：13-113-1、13-213-2、13-313-3、13-1013-10、13-1113-11、13-1513-15、13-1813-18、13-10013-100、13-10813-108、14-114-1、14-214-2数据库表名：数据库表名：SellR

14、ecordSellRecord；字段名：；字段名：ListNumberListNumber；案例分析案例分析2分析分析 这是查询语句，需求运用这是查询语句，需求运用SELECT语句语句 需求运用到需求运用到ORDER BY进展排序，并且在进展排序，并且在ORDER BY的排的排序列中，也需求重新计算出排序的数字来序列中，也需求重新计算出排序的数字来 前半部分的数字，可以从先找到前半部分的数字，可以从先找到“-符号的位置，然后，取符号的位置，然后，取其左半部分，最后再运用其左半部分，最后再运用Convert函数将其转换为数字：函数将其转换为数字：Convert(int, Left(ListNum

15、ber, CharIndex(-, ListNumber)-1) 后半部分的数字，可以先找到后半部分的数字，可以先找到“-符号的位置，然后把从第符号的位置，然后把从第一个位置到该位置的全部字符交换为空格，最后再运用一个位置到该位置的全部字符交换为空格，最后再运用Convert函数将其转换为数字：函数将其转换为数字：Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), )用户自定义函数 标量函数：前往单个数据值。表值函数：前往值是一个记录集合表。在此函数中，return语句包含一条单独的select语句。 多语句表值函数：前往值是由

16、选择的结果构成的记录集。在在SQL Server中，用户不仅可以运用规范的内置函数，也中，用户不仅可以运用规范的内置函数，也可以运用本人定义的函数来实现一些特殊的功能。可以运用本人定义的函数来实现一些特殊的功能。 用户自定义函数可以在对象资源管理器中创建，也可以用户自定义函数可以在对象资源管理器中创建，也可以运用运用CREATE FUNCTION 语句创建。语句创建。 在创建时需求留意：函数名在数据库中必需独一，其可在创建时需求留意：函数名在数据库中必需独一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多以有参数，也可以没有参数，其参数只能是输入参数，最多可以有可以有1024参数。参

17、数。1. 运用CREATE FUNCTION语句创建用户自定义函数运用CREATE FUNCTION创建用户自定义函数，其语法格式如下： CREATE FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type AS BEGIN function_body RETURN scalar_expression ENDl function_name：指用户自定义函数的称号。其称号必需符合标识符的命名规那

18、么，并且对其一切者来说，该称号在数据库中必需独一。l parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用于该函数本身；一样的参数称号可以用在其它函数中。参数只能替代常量；而不能用于替代表名、列名或其它数据库对象的称号。函数执行时每个已声明参数的值必需由用户指定，除非该参数的默许值曾经定义。假设函数的参数有默许值，在调用该函数时必需指定default关键字才干获得默许值。l scalar_parameter_data_type：参数的数据类型。l scalar_return_data_type：是用户定义函数的前往值。可以是 SQL Server 支持的任

19、何标量数据类型text、ntext、image 和 timestamp 除外。l function_body：位于begin和end之间的一系列 Transact-SQL 语句，其只用于标量函数和多语句表值函数。l scalar_expression：用户自定义函数中前往值的表达式。2.表值函数表值函数表值函数遵照的原那么：表值函数遵照的原那么：RETURNSRETURNS子句仅包含关键字子句仅包含关键字tabletable。不用定义前往变量的格式，。不用定义前往变量的格式，由于它由由于它由RETURN RETURN 子句中的子句中的 SELECT SELECT 语句的结果集决议。语句的结果集

20、决议。function_body function_body 不由不由BEGINBEGIN和和ENDEND分隔。分隔。RETURNRETURN子句在括号中包含单个子句在括号中包含单个SELECTSELECT语句。语句。SELECTSELECT语句的结果语句的结果集构成函数所前往的表。集构成函数所前往的表。 例：在例：在stuDB库中创建一个内嵌表值函数库中创建一个内嵌表值函数XUESHENG，该，该函数可以根据输入的系部代码前往该系学生的根本信息。其函数可以根据输入的系部代码前往该系学生的根本信息。其代码如下：代码如下：CREATE FUNCTION XUESHENG(inputdep nva

21、rchar(4) RETURNS table AS RETURN ( SELECT 学号学号, 姓名姓名 FROM 学生学生 WHERE 所属院系所属院系=inputdep)GO建立好该内嵌表值函数后，就可以象运用表或视图一样来运用它：SELECT * FROM DBO.XUESHENG(100)GO 3.多语句表值函数多语句表值函数 多语句函数的主体中允许运用以下语句：赋值语句DECLARE 语句，该语句定义函数部分的数据变量和游标。SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的部分变量。游标操作，该操作援用在函数中声明、翻开、封锁和释放的部分游标。只允许运用

22、以INTO子句向部分变量赋值的FETCH语句；不允许运用将数据前往到客户端的FETCH语句。INSERT、UPDATE和DELETE语句，这些语句修正函数的部分table变量。EXECUTE语句调用扩展存储过程。3.多语句表值函数多语句表值函数案例：在案例：在stuDBstuDB库中创建一个多语句表值函数库中创建一个多语句表值函数chengjichengji，该函，该函数可以根据输入的课程称号前往选修该课程的学生姓名和数可以根据输入的课程称号前往选修该课程的学生姓名和成果。代码如下：成果。代码如下：USE stuDBGOCREATE FUNCTION chengji( inputkm as c

23、har(20) )/*为chengji 函数定义的表构造，称号变量为cj */RETURNS cji TABLE (科目编号 varchar(10), 姓名 varchar(10), 成果 int )ASBEGIN INSERT cj /*该变量是上面定义的表称号变量*/ select b.科目编号,a.学生姓名,(b.笔试成果+b.上机成果)/2 from 学生信息表 as a inner join 成果表 as b on a.学号=b.学号 where b.科目编号=inputkm RETURNENDGO定义前往表的构造 在查询编辑器中输入以下查询命令： SELECT * FROM dbo

24、.chengji(9001)查看、修正和删除自定义函数查看、修正和删除自定义函数 1.查看用户自定义函数的属性在SQL Server中，根据不同需求，可以运用sp_helptext、sp_help等系统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的详细作用和语法如下：运用sp_helptext查看用户定义函数的文本信息，其语法格式为： sp_helptext 用户自定义函数名运用sp_help查看用户自定义函数的普通讯息，其语法格式为： sp_help 用户自定义函数名例：运用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下： SP_

25、HELPTEXT XUEFEN GO查看、修正和删除自定义函数查看、修正和删除自定义函数2.修正用户自定义函数的属性运用SQL命令修正用户自定义函数，运用ALTER FUNCTION 命令可以修正用户自定义函数。修正由CREATE FUNCTION 语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。其语法格式如下：ALTER FUNCTION owner_name. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_d

26、ata_type AS BEGIN function_body RETURN scalar_expressionEND其中的参数与建立用户自定义函数中的参数意义一样。查看、修正和删除自定义函数查看、修正和删除自定义函数3.运用T-SQL命令删除用户自定义函数运用DROP命令可以一次删除多个用户自定义函数，其语法格式为：DROP FUNCTION 一切者称号.函数称号,n案例：删除在student库上建立的xuefen函数。代码如下： DROP FUNCTION dbo.xuefenGO逻辑控制语句逻辑控制语句IFELSE语句语句SQL中的中的IF-ELSE语句语句IF 条件条件 BEGIN 语

27、句语句1 语句语句2 ENDELSE BEGIN 语句语句1； 语句语句2； ENDlELSE是可选部分是可选部分l假设有多条语句，才需求假设有多条语句，才需求BEGIN-END语句块语句块 IF-ELSE例如例如问题：问题：统计并显示本班笔试平均分，假设平均分在统计并显示本班笔试平均分，假设平均分在70以上，显示以上，显示“成果优秀成果优秀“，并显示前三名学员的考试信息；假设在，并显示前三名学员的考试信息；假设在70以以下，显示下，显示“本班成果较差本班成果较差“，并显示后三名学员的考试信息，并显示后三名学员的考试信息 。学员成果表学员成果表分析：分析：第一步，统计平均成果存入暂时变量；第一

28、步，统计平均成果存入暂时变量；第二步，用第二步，用IFELSE判别；判别；IF-ELSE例如例如设置输出结果的格式设置输出结果的格式为了文本音讯和输出结果显示在同一窗口，需求设置输出结果的格式逻辑控制语句逻辑控制语句WHILE循环语句循环语句SQL中的中的WHILE语句语句WHILE 条件条件 BEGIN 语句语句1 语句语句2 BREAK ENDBREAK表示退出循环表示退出循环假设有多条语句，才需求假设有多条语句，才需求BEGIN-END语句块语句块 WHILE例如例如问题：问题：本次考试成果较差，假定要提分，确保每人笔试都经过。本次考试成果较差，假定要提分，确保每人笔试都经过。提分规那么

29、很简单，先每人都加提分规那么很简单，先每人都加2分，看能否都经过，假设分，看能否都经过，假设没有全部经过，每人再加没有全部经过，每人再加2分，再看能否都经过，如此反复分，再看能否都经过，如此反复提分，直到一切人都经过为止提分，直到一切人都经过为止 。学员成果表学员成果表分析：分析：第一步，统计没经过的人数第一步，统计没经过的人数 ；第二步，假设有人没经过，加分；第二步，假设有人没经过，加分；第三步，循环判别。第三步，循环判别。 DECLARE n intWHILE(1=1) -条件永远成立条件永远成立 BEGIN SELECT n=COUNT(*) FROM 成果表成果表 WHERE writ

30、tenExam0) UPDATE 成果表成果表 -每人加每人加2分分 SET writtenExam=writtenExam+2 ELSE BREAK -退出循环退出循环 ENDprint 加分后的成果如下：加分后的成果如下：SELECT * FROM 成果表成果表WHILE例如例如参考语句参考语句逻辑控制语句逻辑控制语句CASEEND多分支语句多分支语句CASE WHEN 条件条件1 THEN 结果结果1 WHEN 条件条件2 THEN 结果结果2 ELSE 其他结果其他结果ENDCASE-END例如例如问题：问题：采用美国的采用美国的ABCDE五级打分制来显示笔试成果。五级打分制来显示笔试

31、成果。 A级级: 90分以上分以上 B级：级： 8089分分 C级级: 7079分分 D级：级： 6069分分 E级：级： 60分以下分以下学员成果表学员成果表print ABCDE五级显示成果如下：五级显示成果如下：SELECT 学号学号, 成果成果= CASE WHEN writtenExam60 THEN E WHEN writtenExam BETWEEN 60 AND 69 THEN D WHEN writtenExam BETWEEN 70 AND 79 THEN C WHEN writtenExam BETWEEN 80 AND 89 THEN B ElSE A END FROM

32、 成果表成果表CASEEND例如例如参考语句参考语句CASE-END练习练习练习：练习：请根据平均分和下面的评分规那么，编写请根据平均分和下面的评分规那么，编写T-SQL语句查询学语句查询学员的成员的成 绩，如上图所示。绩，如上图所示。 优优 ：90分以上分以上 良良 ：8089分分 中中 ：7079分分 差差 ：6069分分 不及格不及格 ：60分以下分以下学员成果分析学员成果分析CASE-END练习答案练习答案USE stuDBGOselect a.学生姓名学生姓名,a.学号学号,b.笔试成果笔试成果,b.上机成果上机成果,总成果总成果=(b.笔试成果笔试成果+b.上上机成果机成果)/2,

33、等级等级= case when (b.笔试成果笔试成果+b.上机成果上机成果)/260 then 不及格不及格 when (b.笔试成果笔试成果+b.上机成果上机成果)/2 between 60 and 69 then 及格及格 when (b.笔试成果笔试成果+b.上机成果上机成果)/2 between 70 and 79 then 中中 when (b.笔试成果笔试成果+b.上机成果上机成果)/2 between 80 and 89 then 良良 else 优优 endfrom 学生信息表学生信息表 as a,成果表成果表 as b where a.学号学号=b.学号学号批处置语句批处置语句 批处置是包含一个或多个批处置是包含一个或多个 SQL 语句的组，从运用程序一语句的组，从运用程序一次性地发送到次性地发送到SQL Server执行执行 SQL Server 将批处置语句编译成一个可执行单元，此单将批处置语句编译成一个可执行单元，此单元称为执行方案。执行方案中的语句每次执行一条元称为执行方案。执行方案中的语句每次执行一条客户端运用程序客户端运用程序SQL Server效力器效力器批处置语句：批处置语句：语句语句1语句语句2