Transact-SQL语言编程修改后.ppt

第3讲Transact-SQL语言编程,编程基础知识,问题如何编制类似C语言的基本程序？要能编程须具备哪些结构？引用数据库对象的规则Transact-SQL的语法元素每一条Transact-SQL语句都包含一系列元素，这些元素可分为：。1、标识符：是用来标志服务器、数据库和数据库对象的名称。SQLServer中标识符有两类：常规标识符和分隔标识符。SQLServer中标识符的命名规则,（1）常规标识符第一个字符必须是下列字符之一：字母az和AZ,以及其它字符。其他字符可以是字符、数字、$、#或下划线。以开始的标识符表示局部变量或参数。以#开始的标识符表示临时表或过程。以#开始的标识符表示全局临时对象。以开始的标识符表示全局变量，也称为配置函数。,（2）分隔标识符对不符合所有标识符规则的标识符必须进行分隔，即将其包含在双引号或者方括号内。例如：TableX、KeyCol都是分隔标识符。ORDER也必须使用分隔标识符，因为ORDER是关键字。,练习1,问题：下面的标识符是否符合SQLServer的命名规则：$tabletable#_var_1orderdetailsorder,常用函数（自学）,思考：如何将一个数字转换为一个字符串？如何截取字符串？如何根据一个人的出生年月，使用日期函数计算年龄？如何对一个数进行取整？进行四舍五入？,常用函数,Sqlserver提供的函数分为以下几类：聚合函数、配置函数、游标函数、日期函数、数学函数、元数据函数、行集函数、安全函数、字符函数、系统函数、文本与图像函数。1、聚合函数也称统计函数，它对一组值进行计算并返回一个数值。常与select语句的子句一起使用。Sum()计算一组数据的和min()给出一组数据的最小值。Max()Count()计算总行数。Count(*)返回行数，包括含有空值的行，不能与distinct一起使用。,常用函数,注意：all为默认选项，指计算所有的值；dinstinct则去掉重复值。例1:计算course表的总行数。usexkgoselectcount(*)ascourse表的总行数fromcoursego,例2：计算course表各门课程限选人数的总人数。USEXkGOSELECTSUM(LimitNum)AS限选人数总人数FROMCourseGO,字符串函数,字符串函数用于对字符串进行连接、截取等操作下列是常用的字符串函数：CHARINDEX(字符表达式1，字符表达式2，开始位置）返回字符表达式1在字符表达式2的开始位置，可从所给出的“开始位置”进行查找，如果没指定开始位置，或者指定为负数或0，则默认从字符表达式2的开始位置查找。例3：给出“数据库”在“大型数据库技术”中的位置。,字符串函数,ASCII(字符表达式）返回字符表达式最左边字符的ASCII码。例4：计算ASCII(Alklk),将返回“Alklk”最左边字符“A”的ASCII码。SELECTASCII(Alklk)GO,字符串函数,LEN（字符表达式）返回字符表达式的字符个数，不计算尾部的空格。例5：计算字符串“SQLServer数据库管理系统”的长度。SELECTLEN(SQLServer数据库管理系统)GO例6：查找字符串“wo”在“MYwonderful”中的开始位置。在查询分析器中运行如下命令：,字符串函数,SELECTCHARINDEX(wo,MYwonderful)SELECTCHARINDEX(wo,MYwonderful,5)SELECTCHARINDEX(wo,MYwonderful,0)GODIFFERENCES(字符表达式1，字符表达式2）返回两个字符表达式发音的相似程度（04）。4发音最相似。,字符串函数,SELECTDIFFERENCE(HelloWorld,HelloWild)GOREPLICATE(字符表达式，整型表达式）将字符表达式重复多次，整数表达式给出重复的次数。STUFF（字符表达式1，start,length,字符表达式2）字符表达式1中从“start”开始的“length”个字符换成字符表达式2,字符串函数,例6：REPLICATE函数和STUFF函数的练习。SELECTREPLICATE(Hello,2),SPACE(10),REPLICATE(World,2)GOSELECTSTUFF(Herld,3,1,lloWo)GO,日期函数,日期函数：用来显示日期和时间的信息。GETDATE()返回服务器当前的系统日期和时间DATEPART(日期元素，日期）返回指定日期的一部分，用整数返回DATENAME(日期元素，日期）返回指定日期的一部分，用整数返回。DATEDIFF(日期元素，日期1，日期2）返回两个日期间的差值并转换为指定日期元素的形式,日期函数,日期元素参见课本P88表3-5例3.11给出服务器当前的系统日期与时间。在查询分析器中运行如下命令：SELECTGETDATE()GO例3.12给出系统当前的月份和月份名字。SELECTDATEPART(MONTH,GETDATE()SELECTDATENAME(MONTH,GETDATE()GO例3.13Mary的生日为1979/12/23，使用日期函数计算现在的年龄。,系统函数,SELECT年龄=DATEDIFF(yy,1979/12/23,GETDATE()GO系统函数：用来获得SQLSERVER的有关信息。HOST_ID()：返回主机标识HOST_NAME()返回主机名称。CONVERT(data_type(lenth),expression,style)功能：将表达式显示转换为另一种数据类型。例如3.14给出主机名称。SELECTHOST_NAME()GO,数学函数,将浮点数10.3456转换为货币型数据。SELECTCONVERT(money,10.3456)GO数学函数：用来对数值型数据进行数学运算。CEILING(数值表达式）：返回大于或等于数值表达式值的最小整数。FLOOR(数值表达式）：返回小于或等于数值表达式值的最大整数，CEILING的反函数。POWER(数值表达式，幂）：返回数字表达式值的指定次幂的值。SQRT(浮点表达式）：返回一个浮点表达式的平方根。,数学函数,例3.16将180转换为弧度。SELECTRADIANS(180.)GO例3.17CEILING及FLOOR函数的练习。SELECTHOST_NAME()GO例3.18计算34的值及16的平方根。SELECTPOWER(3,4)SELECTSQRT(16)GO,元数据函数,元数据函数：返回有关数据库和数据库对象的信息，是一种查询系统表的快捷方法。COL_NAME(table_id,column_id):返回数据库列的名称。DB_ID(database_name):返回数据库标识。例3.19显示Student表第一列的名字。USEXkSETNOCOUNTOFFSELECTCOL_NAME(OBJECT_ID(Student),1)GO,配置函数,例3.20显示xk数据库的标识。SELECTDB_ID(Xk)GO配置函数LANGUAGE返回当前使用语言的名称。MAX_CONNECTIONS返回允许用户同时连接的最大数例3.27给出SQLServer当前所使用的语言。SELECTLanguageGO,例3.28给出SQLServer同时最大的连接数。SELECTMax_ConnectionsGO,常量和变量,常量：1、字符串常量:包含在单引号内，由字母数字字符以及特殊字符组成。2、数值常量：分为二进制常量、bit常量、datatime常量、integer常量、decimal常量、float常量、real常量、money常量、指定负数和正数。数值常量不需要使用引号。二进制常量：具有前缀0X,并且是十六进制数字字符串。例如：0X12EF、0XEF。Bit常量：使用0或1表示，如果使用一个大于1的数字，它将被转换为1.,Integer常量：整数常量，不能包含小数点，如：1987Decimal常量：可以包含小数点的数值常量。Float常量和real常量：使用科学计数法表示。Money常量：货币常量，以$作为前缀，可以包含小数点。3、日期常量使用特定格式的字符日期值表示，并被单引号括起来。4、uniqueidentifier常量注：引用数值常量不用单引号，引用日期、字符串常量时需要加单引号,变量,变量：对应内存中的一个存储空间，在程序运行过程中其值是变化的量。有局部变量和全局变量。局部变量（以开头，最长为128个字节。使用DECLARE语句声明，定义局部变量的名字、数据类型和长度。）局部变量的赋值：局部变量的初值为NULL（空），可以使用SELECT语句或SET语句对局部变量进行赋值。SET语句一次只能给一个局部变量赋值。SELECT语句可以同时给一个或多个变量赋值。例如：编写计算两个整数之和的程序。DECLAREitinyint,jtinyint,sumtinyint-定义三个局部变量SETi=50-给变量i赋值50SETj=60-给变量j赋值60SELECTsum=i+j-将i和j的和赋值给变量sumPRINTsumGO,例:打印course表中有多少类型的课程,要求声明局部变量,进行赋值,然后打印变量内容.分析:计算course表中有多少种类型的课程,就是对KIND列的值进行统计,并且要消除重复值,完成统计需要使用函数COUNT(),括号内为要统计的列名,并且使用DINSTINCT消除重复值,完成统计的SELECT语句为:SELECTCOUNT(DISTINCTkind)fromcourse,统计值为数值类型,为了进行字符运算,需要将它转换为字符型,使用转换函数CONVERT()进行转换,即CONVERT(varchar(3),(SELECTCOUNT(DISTINCTKind)FROMCourse),USEXk-切换到Xk数据库，然后GO使用结束符提交给服务器执行DECLAREuser_msgvarchar(50)-定义长度为50的字符型变量SELECTuser_msg=在Course表中有+CONVERT(varchar(3),(SELECTCOUNT(DISTINCTKind)FROMCourse)+种类型的课程。-给字符型变量进行赋值PRINTuser_msg-显示字符变量的值GO,运算符,全局变量:以开头,实际上它是SQLServer的配置函数。表达式可以是列名、字符、运算符或函数的任意组合。运算符用来指定要在一个或多个表达式中执行的操作。SQLSERVER2005使用算术运算符、一元运算符、赋值运算符、位运算符、比较运算符、逻辑运算符和字符串连接运算符。1、算术运算符对两个表达式进行数学运算。主要有+、-、*、/、%四个运算符。,运算符,2、一元运算符：只对一个表达式执行操作，这个表达式可以是数值数据类型。一元运算符如表：一元运算符运算符描述+（正）返回数值表达式的正值(负)返回数值表达式的负值。(按位NOT)将给定的整型数值转换为二进制形式，然后按位进行逻辑非运算3、比较运算符用来对两个表达式进行比较，比较的结果为逻辑值，值有3种：TRUE（真）、FALSE（假），UNKNOWN（未知），比较运算符列表如下：,运算符描述=等于大于=大于等于不等于!=不等于!不大于,运算符,4、逻辑运算符对某个条件进行测试，返回的逻辑值为TRUE或FALSE，逻辑运算符列表ALL、AND、ANY、BETWEEN、EXISTS、IN、LIKE、NOT、OR、SOME5、字符串连接运算符字符串连接运算符（+）用来将字符串进行连接。如：SELECTSQLServer2005+数据库技术，显示结果为：SQLServer2005数据库技术6、赋值运算符赋值运算符，即等号（=），它通常与SET语句或SELECT语句一起使用来为变量赋值。例如：DECLAREMyCounterINTSETMyCounter=1,当有多个运算符参与运算时，各运算符的优先级顺序如下：、-(负号)和+（正号）*（乘）、/、%+、-、+（连接）=,=,!=,!,!、=、!=、!）逻辑运算符（NOT、OR、AND、IN、LIKE、BETWEEN、EXISTS）字符串连接运算符（）赋值运算符（=）,批处理和流控语句,一、批处理批处理是包含一个或多个Transact-SQL语句的组，它将一次性地发送到SQLSERVER中执行。用GO来通知SQLServer一批Transact-SQL语句的结束。大多数CREATE命令要在单个批命令中执行。但CREATEDATABASE、CREATETABLE和CREATEINDEX例外。例1：批处理示例分析。USEXkGOCREATEVIEWV-TestASSELECT*FROMStudentGOSELECT*FROMCourseGO,因为CREATEVIEW必须是批处理中的第一条语句，所以需要GO命令将CREATEVIEW语句与其上下的语句（USE和SELECT)隔离。例2：DECLAREMyVarINTGOSELECTMyVar=33GO运行结果出错，因为变量MyVar在第一个批处理中定义，但在第二个批处理中引用（SELECTMyVar=33），所以运行出错。,批处理,批处理的特点一次发给数据库服务器的所有的SQL语句，由GO作为结束标志。编译成一个可执行单元执行计划执行时，每次执行计划中的一条语句特别注意：如果在一个批处理中，某条语句存在语法错误，SQLServer将不执行批处理中的任何语句。,流控语句,流控语句可以和T-SQL语句一起使用以控制程序流，可以在批内部使用流控制语句，也可以在存储过程、脚本和特定的检索内部使用。【问题】如何用SQL语句编程实现：求1+2+3+4+10000=?DECLAREiint,sumint-定义整型变量i用来计数；sum为求和单元SELECTi=1,sum=0-给整型变量i和sum赋值WHILEi=10000-当i小于等于10000时，执行循环体,BEGIN-定义语句块SELECTsum=sum+i-求和SELECTi=i+1-计数单元加1END-语句块定义结束-显示求和结果GO方法二：DECLAREiint,sumint-定义整型变量i用来计数；sum为求和单元SELECTi=1,sum=0-给整型变量i和sum赋值BQ:IFi10000SELECT1+2+3+10000=,sumELSEGOTOBQEND-,从以上的程序我们可以看出流控语句可以实现程序的3种基本结构：顺序结构、选择结构和循环结构。下面介绍T-SQL提供的流控语句。（1）IF语句用来实现选择结构，其语法为：IF逻辑表达式SQL语句块ELSESQL语句块功能：如果逻辑表达式的条件成立，则执