理学附加TSQL编程PPT课件

1、使用变量 变量分为：变量分为：局部变量：局部变量：局部变量必须以标记局部变量必须以标记作为前缀作为前缀 ，如，如age局部变量的使用也是先声明，再赋值局部变量的使用也是先声明，再赋值 全局变量全局变量：全局变量必须以标记全局变量必须以标记 作为前缀，如作为前缀，如version全局变量由系统定义和维护，我们只能读取，不能修改全局变全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值量的值 第1页/共43页局部变量 例如：例如：DECLARE name varchar(8)DECLARE seat int声明局部变量声明局部变量 DECLARE 变量名变量名 数据类型数据类型赋值赋值 S

2、ET 变量名变量名 =值值 SELECT 变量名变量名 = 值值例如：例如：SET name=张三张三SELECT name=stuName FROM stuInfo WHERE stuNo=s25302 必须确保筛选出的记录只有必须确保筛选出的记录只有1条条 或 第2页/共43页全局变量 全局变量都使用两个全局变量都使用两个标志作为前缀标志作为前缀 变量变量含义含义ERROR最后一个最后一个T-SQL错误的错误号错误的错误号IDENTITY最后一次插入的标识值最后一次插入的标识值LANGUAGE当前使用的语言的名称当前使用的语言的名称MAX_CONNECTIONS可以创建的同时连接的最大数目

3、可以创建的同时连接的最大数目ROWCOUNT受上一个受上一个SQL语句影响的行数语句影响的行数SERVERNAME本地服务器的名称本地服务器的名称TRANSCOUNT当前连接打开的事务数当前连接打开的事务数VERSIONSQL Server的版本信息的版本信息第3页/共43页SQL Server中的函数相当于相当于Java语言中的内置包语言中的内置包字符串函数字符串函数日期函数日期函数数学函数数学函数系统函数系统函数第4页/共43页字符串函数SELECT STUFF(ABCDEFG, 2, 3, 我的音乐我的世界我的音乐我的世界)返回：返回：A我的音乐我的世界我的音乐我的世界EFG在一个字符串

4、中，删除指定长度的在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字字符，并在该位置插入一个新的字符串符串STUFFSELECT CHARINDEX(ACCP,My Accp Course,1 )返回：4用来寻找一个指定的字符串在另一用来寻找一个指定的字符串在另一个字符串中的起始位置个字符串中的起始位置CHARINDEX举例举例描述描述函数名函数名SELECT LEN(SQL Server课程课程)返回：返回：12返回传递给它的字符串长度返回传递给它的字符串长度LENSELECT LOWER(SQL ServerSELECT LOWER(SQL Server课程课程)返回：返回：sq

5、l serversql server课程课程把传递给它的字符串转换为小写把传递给它的字符串转换为小写LOWERSELECT UPPER(sql server课程课程)返回：返回：SQL SERVER课程课程把传递给它的字符串转换为大写把传递给它的字符串转换为大写UPPERSELECT LTRIM ( 周智宇周智宇 )返回：周智宇返回：周智宇 （后面的空格保留）（后面的空格保留）清除字符左边的空格清除字符左边的空格LTRIMSELECT RTRIM ( 周智宇周智宇 )返回：返回： 周智宇（前面的空格保留）周智宇（前面的空格保留）清除字符右边的空格清除字符右边的空格RTRIMSELECT RIG

6、HT(买卖提买卖提.吐尔松吐尔松,3)返回：吐尔松返回：吐尔松从字符串右边返回指定数目的字符从字符串右边返回指定数目的字符RIGHTSELECT REPLACE(莫乐可切莫乐可切.杨可杨可,可可,兰兰)返回：莫乐兰切返回：莫乐兰切.杨兰杨兰替换一个字符串中的字符替换一个字符串中的字符REPLACE第5页/共43页日期函数SELECT DATEPART(day, 01/15/2000)返回：返回：15日期中指定日期部分的整数形日期中指定日期部分的整数形式式DATEPARTSELECT GETDATE()返回：今天的日期返回：今天的日期取得当前的系统日期取得当前的系统日期GETDATE举例举例描述

7、描述函数名函数名SELECT DATEADD(mm,4,01/01/99)返回：以当前的日期格式返回返回：以当前的日期格式返回05/01/99将指定的数值添加到指定的日将指定的数值添加到指定的日期部分后的日期期部分后的日期DATEADDSELECT DATEDIFF(mm,01/01/99,05/01/99)返回：返回：4两个日期之间的指定日期部分两个日期之间的指定日期部分的区别的区别DATEDIFFSELECT DATENAME(dw,01/01/2000)返回：返回：Saturday日期中指定日期部分的字符串日期中指定日期部分的字符串形式形式DATENAME第6页/共43页数学函数SELE

8、CT SQRT(9)返回：返回：3取浮点表达式的平方根取浮点表达式的平方根SqrtSELECT ABS(-43)返回：返回：43取数值表达式的绝对值取数值表达式的绝对值ABS举例举例描述描述函数名函数名SELECT CEILING(43.5)返回：返回：44返回大于或等于所给数字表达返回大于或等于所给数字表达式的最小整数式的最小整数CEILINGSELECT POWER(5,2)返回：返回：25取数值表达式的幂值取数值表达式的幂值POWERSELECT ROUND(43.543,1)返回：返回：43.5将数值表达式四舍五入为指定将数值表达式四舍五入为指定精度精度ROUNDSELECT SIGN

9、(-43)返回：返回：-1对于正数返回对于正数返回+1，对于负数返，对于负数返回回-1，对于，对于0 则返回则返回0SignSELECT FLOOR(43.5)返回：返回：43取小于或等于指定表达式的最取小于或等于指定表达式的最大整数大整数FLOOR第7页/共43页系统函数SELECT USER_NAME(1)返回：从任意数据库中返回返回：从任意数据库中返回“dbo”从给定的用户从给定的用户I D返回用户名返回用户名USER_NAMESELECT CONVERT (VARCHAR (5)，12345)返回返回：字符串：字符串12345用来转变数据类型用来转变数据类型CONVERT 举例举例描述

10、描述函数名函数名SELECT CURRENT_USER返回返回：你登录的用户名你登录的用户名返回当前用户的名字返回当前用户的名字CURRENT_USER SELECT DATALENGTH (中国中国A盟盟)返回：返回：7返回用于指定表达式的字节返回用于指定表达式的字节数数DATALENGTHSELECT HOST_NAME()返回：你所登录的计算机的名字返回：你所登录的计算机的名字返回当前用户所登录的计算返回当前用户所登录的计算机名字机名字HOST_NAME SELECT SYSTEM_USER返回：你当前所登录的用户名返回：你当前所登录的用户名返回当前所登录的用户名称返回当前所登录的用户名

11、称SYSTEM_USER 第8页/共43页案例分析1要求 某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题： 卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；请编写SQL语句实现以上要求； 数据库表名：Card；密码字段名：PassWord；第9页/共43页案例分析1分析 这是更新语句，需要使用UPDATE语句； 因为牵涉到字符串的替换，需要使用到SQL Server中的函数Replace；第10页/共43页案例分析2要求 在数据库表中有以下字符数据，如：13-1、13-2

12、、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排需，输出要排成这样：13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2 数据库表名：SellRecord；字段名：ListNumber；第11页/共43页案例分析2分析 这是查询语句，需要使用SELECT语句 需要使用到ORDER BY进行排序，并且在ORDER BY的排序列中，也需要重新计算出排序的数字来 前半部分的

13、数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字：Convert(int, Left(ListNumber, CharIndex(-, ListNumber)-1) 后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字：Convert(int, Stuff(ListNumber,1, Charindex(-, ListNumber), )第12页/共43页用户自定义函数 标量函数：返回单个数据值。 表值函数：返回值是一个记录集合表。在此函数中，return语句包含

14、一条单独的select语句。 多语句表值函数：返回值是由选择的结果构成的记录集。在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。 用户自定义函数可以在对象资源管理器中创建，也可以使用CREATE FUNCTION 语句创建。 在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。第13页/共43页1. 使用CREATE FUNCTION语句创建用户自定义函数使用CREATE FUNCTION创建用户自定义函数，其语法格式如下： CREATE FUNCTION owner_n

15、ame. function_name ( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS scalar_return_data_type AS BEGIN function_body RETURN scalar_expression END第14页/共43页lfunction_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。l parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用于该函数本身；相同的参数名

16、称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定default关键字才能获得默认值。lscalar_parameter_data_type：参数的数据类型。lscalar_return_data_type：是用户定义函数的返回值。可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。lfunction_body：位于begin和end之间的一系列 Transact-SQL

17、语句，其只用于标量函数和多语句表值函数。lscalar_expression：用户自定义函数中返回值的表达式。第15页/共43页2.表值函数表值函数遵循的原则：l RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN 子句中的 SELECT 语句的结果集决定。l function_body 不由BEGIN和END分隔。l RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。 第16页/共43页例：在stuDB库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下：CRE

18、ATE FUNCTION XUESHENG(inputdep nvarchar(4) RETURNS table AS RETURN ( SELECT 学号, 姓名 FROM 学生 WHERE 所属院系=inputdep)GO建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：SELECT * FROM DBO.XUESHENG(100)GO 第17页/共43页3.多语句表值函数 多语句函数的主体中允许使用以下语句：l赋值语句lDECLARE 语句，该语句定义函数局部的数据变量和游标。lSELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。l游标操作，该

19、操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。lINSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。lEXECUTE语句调用扩展存储过程。第18页/共43页3.多语句表值函数案例：在stuDB库中创建一个多语句表值函数chengji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。代码如下：第19页/共43页USE stuDBGOCREATE FUNCTION chengji( inputkm as char(20) )/*为chengji 函数

20、定义的表结构，名称变量为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定义返回表的结构第20页/共43页 在查询编辑器中输入以下查询命令： SELECT * FROM dbo.chengji(9001

21、)第21页/共43页查看、修改和删除自定义函数 1.查看用户自定义函数的属性在SQL Server中，根据不同需要，可以使用sp_helptext、sp_help等系统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的具体作用和语法如下：使用sp_helptext查看用户定义函数的文本信息，其语法格式为： sp_helptext 用户自定义函数名使用sp_help查看用户自定义函数的一般信息，其语法格式为： sp_help 用户自定义函数名例：使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下： SP_HELPTEXT XUEFEN G

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

23、ction_body RETURN scalar_expressionEND其中的参数与建立用户自定义函数中的参数意义相同。第23页/共43页查看、修改和删除自定义函数3.使用T-SQL命令删除用户自定义函数使用DROP命令可以一次删除多个用户自定义函数，其语法格式为：DROP FUNCTION 所有者名称.函数名称,n案例：删除在student库上建立的xuefen函数。代码如下： DROP FUNCTION dbo.xuefenGO第24页/共43页逻辑控制语句 IFELSE语句SQL中的中的IF-ELSE语句语句IF （条件）（条件） BEGIN 语句语句1 语句语句2 ENDELSE

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

25、；第二步，用第二步，用IFELSE判断；判断；第26页/共43页IF-ELSE示例第27页/共43页设置输出结果的格式 为了文本消息和输出结果显示在同一窗口，需要设置输出结果的格式第28页/共43页逻辑控制语句WHILE循环语句SQL中的中的WHILE语句语句WHILE （条件）（条件） BEGIN 语句语句1 语句语句2 BREAK ENDBREAK表示退出循环表示退出循环如果有多条语句，才需要如果有多条语句，才需要BEGIN-END语句块语句块 第29页/共43页WHILE示例问题：问题：本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简本次考试成绩较差，假定要提分，确保每人笔

26、试都通过。提分规则很简单，先每人都加单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加分，看是否都通过，如果没有全部通过，每人再加2分，分，再看是否都通过，如此反复提分，直到所有人都通过为止再看是否都通过，如此反复提分，直到所有人都通过为止 。学员成绩表学员成绩表分析：分析：第一步，统计没通过的人数第一步，统计没通过的人数 ；第二步，如果有人没通过，加分；第二步，如果有人没通过，加分；第三步，循环判断。第三步，循环判断。 第30页/共43页DECLARE n intWHILE(1=1) -条件永远成立条件永远成立 BEGIN SELECT n=COUNT(*) FROM 成绩表成绩表

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

28、。五级打分制来显示笔试成绩。 A级级: 90分以上分以上 B级：级： 8089分分 C级级: 7079分分 D级：级： 6069分分 E级：级： 60分以下分以下学员成绩表学员成绩表第33页/共43页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 TH

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

30、等级= 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.学号第36页/共43页批处理语句批处理是包含一个或多个批处理是包含一个或多个 SQL 语句的组，从应用程序一语句的组，从应用程序一次性地发送到次性地发送到SQL Server执行执行SQL Server 将批处理语句编译成一个可执行单元，此单将批处理语句编译成一个可执行单元，此单元称为执行计划。执行计划中的语句每次执行一条元称为执行计划。执行计划中的语句每次执行一条客户端应用程序客户端应用程序SQL Server服务器服务器批处理语句：批处理语句：语句语句1语句语句2GO第37页/共43页批处理语句示例SELECT * FROM stuInfoSELECT * F