《Transact-SQL语言编程修改后.ppt》由会员分享，可在线阅读，更多相关《Transact-SQL语言编程修改后.ppt（47页珍藏版）》请在三一办公上搜索。
1、第3讲 Transact-SQL语言编程,编程基础知识,问题如何编制类似C语言的基本程序？要能编程须具备哪些结构？引用数据库对象的规则Transact-SQL的语法元素每一条Transact-SQL语句都包含一系列元素，这些元素可分为：。1、标识符：是用来标志服务器、数据库和数据库对象的名称。SQL Server中标识符有两类：常规标识符和分隔标识符。SQL Server中标识符的命名规则,（1）常规标识符第一个字符必须是下列字符之一：字母az和AZ,以及其它字符。其他字符可以是字符、数字、$、#或下划线。以开始的标识符表示局部变量或参数。以#开始的标识符表示临时表或过程。以#开始的标识符表示
2、全局临时对象。以开始的标识符表示全局变量，也称为配置函数。,（2）分隔标识符对不符合所有标识符规则的标识符必须进行分隔，即将其包含在双引号或者方括号内。例如：TableX、KeyCol都是分隔标识符。ORDER也必须使用分隔标识符，因为ORDER是关键字。,练习1,问题：下面的标识符是否符合SQL Server的命名规则：$tabletable#_var_1order detailsorder,常用函数（自学）,思考：如何将一个数字转换为一个字符串？如何截取字符串？如何根据一个人的出生年月，使用日期函数计算年龄？如何对一个数进行取整？进行四舍五入？,常用函数,Sql server提供的函数分为
3、以下几类：聚合函数、配置函数、游标函数、日期函数、数学函数、元数据函数、行集函数、安全函数、字符函数、系统函数、文本与图像函数。1、聚合函数也称统计函数，它对一组值进行计算并返回一个数值。常与select 语句的子句一起使用。Sum()计算一组数据的和min()给出一组数据的最小值。Max()Count()计算总行数。Count(*)返回行数，包括含有空值的行，不能与distinct一起使用。,常用函数,注意：all为默认选项，指计算所有的值；dinstinct则去掉重复值。例1:计算course表的总行数。use xkgoselect count(*)as course表的总行数 from
4、coursego,例2：计算course表各门课程限选人数的总人数。USE XkGOSELECT SUM(LimitNum)AS 限选人数总人数FROM CourseGO,字符串函数,字符串函数用于对字符串进行连接、截取等操作下列是常用的字符串函数：CHARINDEX(字符表达式1，字符表达式2，开始位置）返回字符表达式 1在字符表达式2 的开始位置，可从所给出的“开始位置”进行查找，如果没指定开始位置，或者指定为负数或0，则默认从字符表达式2的开始位置查找。例3：给出“数据库”在“大型数据库技术”中的位置。,字符串函数,ASCII(字符表达式）返回字符表达式最左边字符的ASCII码。例4：计
5、算ASCII(Alklk),将返回“Alklk”最左边字符“A”的ASCII码。SELECT ASCII(Alklk)GO,字符串函数,LEN（字符表达式）返回字符表达式的字符个数，不计算尾部的空格。例5：计算字符串“SQL Server 数据库管理系统”的长度。SELECT LEN(SQL Server数据库管理系统)GO例6：查找字符串“wo”在“MY wonderful”中的开始位置。在查询分析器中运行如下命令：,字符串函数,SELECT CHARINDEX(wo,MY wonderful)SELECT CHARINDEX(wo,MY wonderful,5)SELECT CHARIND
6、EX(wo,MY wonderful,0)GODIFFERENCES(字符表达式1，字符表达式2）返回两个字符表达式发音的相似程度（04）。4发音最相似。,字符串函数,SELECT DIFFERENCE(Hello World,Hello Wild)GOREPLICATE(字符表达式，整型表达式）将字符表达式重复多次，整数表达式给出重复的次数。STUFF（字符表达式1，start,length,字符表达式2）字符表达式1中从“start”开始的“length”个字符换成字符表达式2,字符串函数,例6：REPLICATE函数和STUFF函数的练习。SELECT REPLICATE(Hello,2
7、),SPACE(10),REPLICATE(World,2)GOSELECT STUFF(He rld,3,1,llo Wo)GO,日期函数,日期函数：用来显示日期和时间的信息。GETDATE()返回服务器当前的系统日期和时间 DATEPART(日期元素，日期）返回指定日期的一部分，用整数返回DATENAME(日期元素，日期）返回指定日期的一部分，用整数返回。DATEDIFF(日期元素，日期1，日期2）返回两个日期间的差值并转换为指定日期元素的形式,日期函数,日期元素参见课本P88表3-5例3.11 给出服务器当前的系统日期与时间。在查询分析器中运行如下命令：SELECT GETDATE()G
8、O例3.12 给出系统当前的月份和月份名字。SELECT DATEPART(MONTH,GETDATE()SELECT DATENAME(MONTH,GETDATE()GO例3.13 Mary 的生日为1979/12/23，使用日期函数计算现在的年龄。,系统函数,SELECT 年龄=DATEDIFF(yy,1979/12/23,GETDATE()GO系统函数：用来获得SQL SERVER的有关信息。HOST_ID()：返回主机标识HOST_NAME()返回主机名称。CONVERT(data_type(lenth),expression,style)功能：将表达式显示转换为另一种数据类型。例如3
9、.14 给出主机名称。SELECT HOST_NAME()GO,数学函数,将浮点数10.3456转换为货币型数据。SELECT CONVERT(money,10.3456)GO数学函数：用来对数值型数据进行数学运算。CEILING(数值表达式）：返回大于或等于数值表达式值的最小整数。FLOOR(数值表达式）：返回小于或等于数值表达式值的最大整数，CEILING的反函数。POWER(数值表达式，幂）：返回数字表达式值的指定次幂的值。SQRT(浮点表达式）：返回一个浮点表达式的平方根。,数学函数,例3.16将180转换为弧度。SELECT RADIANS(180.)GO例3.17 CEILING及
10、FLOOR函数的练习。SELECT HOST_NAME()GO例3.18 计算34的值及16的平方根。SELECT POWER(3,4)SELECT SQRT(16)GO,元数据函数,元数据函数：返回有关数据库和数据库对象的信息，是一种查询系统表的快捷方法。COL_NAME(table_id,column_id):返回数据库列的名称。DB_ID(database_name):返回数据库标识。例3.19 显示Student表第一列的名字。USE XkSET NOCOUNT OFFSELECT COL_NAME(OBJECT_ID(Student),1)GO,配置函数,例3.20显示xk数据库的标
11、识。SELECT DB_ID(Xk)GO配置函数LANGUAGE 返回当前使用语言的名称。MAX_CONNECTIONS返回允许用户同时连接的最大数例3.27给出SQL Server 当前所使用的语言。SELECT LanguageGO,例3.28给出SQL Server同时最大的连接数。SELECT Max_ConnectionsGO,常量和变量,常量：1、字符串常量:包含在单引号内，由字母数字字符以及特殊字符组成。2、数值常量：分为二进制常量、bit常量、datatime常量、integer常量、decimal常量、float常量、real常量、money常量、指定负数和正数。数值常量不需
12、要使用引号。二进制常量：具有前缀0X,并且是十六进制数字字符串。例如：0X12EF、0XEF。Bit常量：使用0或1表示，如果使用一个大于1的数字，它将被转换为1.,Integer常量：整数常量，不能包含小数点，如：1987Decimal常量：可以包含小数点的数值常量。Float常量和real常量：使用科学计数法表示。Money常量：货币常量，以$作为前缀，可以包含小数点。3、日期常量使用特定格式的字符日期值表示，并被单引号括起来。4、uniqueidentifier常量注：引用数值常量不用单引号，引用日期、字符串常量时需要加单引号,变量,变量：对应内存中的一个存储空间，在程序运行过程中其值是
13、变化的量。有局部变量和全局变量。局部变量（以开头，最长为128个字节。使用DECLARE语句声明，定义局部变量的名字、数据类型和长度。）局部变量的赋值：局部变量的初值为NULL（空），可以使用SELECT语句或SET语句对局部变量进行赋值。SET语句一次只能给一个局部变量赋值。SELECT语句可以同时给一个或多个变量赋值。例如：编写计算两个整数之和的程序。DECLARE i tinyint,j tinyint,sum tinyint-定义三个局部变量SET i=50-给变量i赋值50SET j=60-给变量j赋值60SELECT sum=i+j-将i和j的和赋值给变量sumPRINT sumG
14、O,例:打印 course表中有多少类型的课程,要求声明局部变量,进行赋值,然后打印变量内容.分析:计算course表中有多少种类型的课程,就是对KIND列的值进行统计,并且要消除重复值,完成统计需要使用函数COUNT(),括号内为要统计的列名,并且使用DINSTINCT消除重复值,完成统计的SELECT语句为:SELECT COUNT(DISTINCT kind)from course,统计值为数值类型,为了进行字符运算,需要将它转换为字符型,使用转换函数CONVERT()进行转换,即CONVERT(varchar(3),(SELECT COUNT(DISTINCT Kind)FROM Co
15、urse),USE Xk-切换到Xk数据库，然后GO使用结束符提交给服务器执行 DECLARE user_msg varchar(50)-定义长度为50的字符型变量SELECT user_msg=在Course表中有+CONVERT(varchar(3),(SELECT COUNT(DISTINCT Kind)FROM Course)+种类型的课程。-给字符型变量进行赋值PRINT user_msg-显示字符变量的值GO,运算符,全局变量:以开头,实际上它是SQL Server 的配置函数。表达式可以是列名、字符、运算符或函数的任意组合。运算符用来指定要在一个或多个表达式中执行的操作。SQL
16、SERVER 2005使用算术运算符、一元运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和字符串连接运算符。1、算术运算符对两个表达式进行数学运算。主要有+、-、*、/、%四个运算符。,运算符,2、一元运算符：只对一个表达式执行操作，这个表达式可以是数值数据类型。一元运算符如表：一元运算符运算符 描述+（正）返回数值表达式的正值(负)返回数值表达式的负值。(按位 NOT)将给定的整型数值转换为二进制形式，然后按位进行逻辑非运算3、比较运算符用来对两个表达式进行比较，比较的结果为逻辑值，值有3种：TRUE（真）、FALSE（假），UNKNOWN（未知），比较运算符列表如下：,运算符 描述=
17、等于 大于=大于等于 不等于!=不等于!不大于,运算符,4、逻辑运算符对某个条件进行测试，返回的逻辑值为TRUE或FALSE，逻辑运算符列表ALL、AND、ANY、BETWEEN、EXISTS、IN、LIKE、NOT、OR、SOME5、字符串连接运算符字符串连接运算符（+）用来将字符串进行连接。如：SELECT SQL Server 2005+数据库技术，显示结果为：SQL Server 2005数据库技术6、赋值运算符赋值运算符，即等号（=），它通常与SET语句或SELECT语句一起使用来为变量赋值。例如：DECLARE MyCounter INT SET MyCounter=1,当有多个运
18、算符参与运算时，各运算符的优先级顺序如下：、-(负号)和+（正号）*（乘）、/、%+、-、+（连接）=,=,!=,!,!比较运算符(位异或)、&（位与）、|（位或）NOT AND ALL、ANY、BETWEEN、IN、LIKE、OR、SOME=（赋值）,运算符,算术运算符（+、*、/、%）比较运算符（=、=、!=、!）逻辑运算符（NOT、OR、AND、IN、LIKE、BETWEEN、EXISTS）字符串连接运算符（）赋值运算符（=）,批处理和流控语句,一、批处理批处理是包含一个或多个Transact-SQL语句的组，它将一次性地发送到SQL SERVER中执行。用GO来通知SQL Server
19、一批Transact-SQL语句的结束。大多数CREATE命令要在单个批命令中执行。但CREATE DATABASE、CREATE TABLE和CREATE INDEX例外。例1：批处理示例分析。USE XkGO CREATE VIEW V-TestASSELECT*FROM StudentGO SELECT*FROM CourseGO,因为CREATE VIEW必须是批处理中的第一条语句，所以需要GO命令将CREATE VIEW语句与其上下的语句（USE和SELECT)隔离。例2：DECLARE MyVar INTGOSELECT MyVar=33GO 运行结果出错，因为变量MyVar在第一
20、个批处理中定义，但在第二个批处理中引用（SELECT MyVar=33），所以运行出错。,批处理,批处理的特点一次发给数据库服务器的所有的SQL语句，由GO作为结束标志。编译成一个可执行单元执行计划执行时，每次执行计划中的一条语句特别注意：如果在一个批处理中，某条语句存在语法错误，SQL Server将不执行批处理中的任何语句。,流控语句,流控语句可以和T-SQL语句一起使用以控制程序流，可以在批内部使用流控制语句，也可以在存储过程、脚本和特定的检索内部使用。【问题】如何用SQL语句编程实现：求1+2+3+4+10000=?DECLARE i int,sum int-定义整型变量i用来计数；s
21、um为求和单元SELECT i=1,sum=0-给整型变量i和sum赋值WHILE i=10000-当i小于等于10000时，执行循环体,BEGIN-定义语句块 SELECT sum=sum+i-求和 SELECT i=i+1-计数单元加1 END-语句块定义结束-显示求和结果GO方法二：DECLARE i int,sum int-定义整型变量i用来计数；sum为求和单元SELECT i=1,sum=0-给整型变量i和sum赋值BQ:IF i=10000-如果i小于等于10000，则求和,BEGIN-定义语句块 SELECT sum=sum+i-求和 SELECT i=i+1-计数单元加1IF
22、 i10000 SELECT 1+2+3+10000=,sum ELSE GOTO BQEND-,从以上的程序我们可以看出流控语句可以实现程序的3种基本结构：顺序结构、选择结构和循环结构。下面介绍T-SQL提供的流控语句。（1）IF语句用来实现选择结构，其语法为：IF 逻辑表达式SQL 语句块ELSESQL 语句块功能：如果逻辑表达式的条件成立，则执行IF语句后的语句块；否则，执行ELSE语句后的语句块。,（2）BEGINENDBEGIN和END用来定义语句块，必须成对出现。它将多个SQL语句括起来，相当于一个单一语句。（3）WHILE、BREAK和CONTINUEWHILE语句用来实现循环结
23、构，其语法为：WHIEL 逻辑表达式语句块功能：当逻辑表达式为真时，执行循环体，直到表达式为假。,BREAKE语句退出WHILE循环，CONTINUE语句跳过语句块中的所有其他语句，开始下一次循环。（4）GOTO语句GOTO语句是无条件转移语句，语法为：GOTO 标号（5）CASE表达式用于计算多个条件并为每个条件返回单个值，以简化SQL表达式。,流控语句,CASE语句（开关语句）用来计算多个条件并为每个条件返回单个值，以简化SQL表达式。Case 表达式 when 表达式 then 表达式 else 表达式end【例】对课程进行分类统计，要求显示课程类别、课程名称、报名人数并计算各类平均报名人数，按照类别和报名人数升序排序。用CASE语句实现。,USE XkGOSELECT 课程分类=CASE Kind WHEN 工程技术 THEN 工科类课程 WHEN 人文 THEN 人文类课程 WHEN 信息技术 THEN 信息类课程 ELSE 其他类课程 END,课程名称=couname,报名人数=willnum FROM Course,ORDER BY Kind,WillNumCOMPUTE AVG(WillNum)BY KindGO,本章总结,重点掌握：SQL Server中标识符的命名规则学会使用局部变量和全局变量学会使用一些常用的函数流控语句学会编写简单的SQL程序,
