Transact-SQL程序设计.ppt

第7章Transact-SQL程序设计,7.1Transact-SQL语言基础7.2SELECT查询语句7.3流程控制语句,7.1Transact-SQL语言基础,SQL是英文StructuredQueryLanguage的缩写，意为结构化查询语言。SQL语言是关系数据库管理系统(DatabaseManagementSystem，DBMS)中的标准语言，SQL语句可以用来执行创建和删除数据库、数据表，创建索引，更新数据库中的数据，从数据库中提取数据等各种操作。MSSQLServer2000中使用的SQL被称为Transact-SQL，简称T-SQL。两个GO之间的T-SQL语句作为一个批处理。在一个批处理中可以包含一条或多条T-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQLServer服务器进行执行。SQLServer服务器将批处理编译成一个可执行单元，称为执行计划。,7.1.1数据类型,在SQLServer中每个变量、参数、表达式等都有一个相关的数据类型，此数据类型定义对象所能包含的数据种类。SQLServer2000中定义了许多预定义的数据类型，同时允许用户根据需要自定义新的数据类型。系统中预定义的数据类型如表4.1所示。,7.1.2变量,1.局部变量利用局部变量还可以保存程序执行过程中的中间结果，保存由存储过程返回的数据值等。（1）局部变量的定义必须先用DECLARE命令定义后才可以使用。DECLAERlocal_variabledata_typen（2）局部变量的赋值方法SETlocal_variable=expression或者SELECTlocal_variable=expression,.n,7.1.2变量,2.全部变量全局变量在整个SQLServer系统内使用。存储的通常是一些SQLServer的配置设定值和统计数据。在使用全局变量时应该注意以下几点：全局变量是在服务器级定义的。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“”开头。全局变量对用户来说是只读的。局部变量的名称不能与全局变量的名称相同。,7.1.3运算符、表达式和优先级,1.算术运算符算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）2.赋值运算符（=）3.字符串串联运算符（+）4.比较运算符比较运算符包括：等于（=）、大于（）、大于或等于（=）、小于（或!=）、不小于（!）,7.1.3运算符、表达式和优先级,5.逻辑运算符逻辑运算符包括与（AND）、或（OR）和非（NOT）等运算符。比较和逻辑运算均返回布尔值，值为TRUE或FALSE6.位运算符位运算符包括按位与（,7.2.3WHERE子句,2基于BETWEEN关键字的查询有时希望查找的是具有上下限范围的记录，这时就需要使用基于范围的查询。基于范围的WHERE子句，其语法格式为：WHEREexpressionNOTBETWEENexpression1ANDexpression2其中expression1是下限，expression2是上限。该语句表示返回expression值在或不在（有NOT时）这两个值之间的所有记录。【例7-8】从Student表中查询已修学分在86到100之间的所有学生。USEEducationalGOSELECT*FROMStudentWHERECreditHourBETWEEN86AND100;,7.2.3WHERE子句,3基于IN关键字的查询有时希望查找的是指定值的若干个记录，这时就需要使用基于集合的查询。基于集合的WHERE子句，其语法格式为：WHERENOTexpressionNOTIN(value,n)其中IN后面括号内的就是要查询的若干指定值，中间用逗号“,”分隔。【例7-9】从Student表中查询班号为030501和030505的所有学生。USEEducationalGOSELECT*FROMStudentWHEREClassIDIN(030501,030505);,7.2.3WHERE子句,4基于LIKE关键字的查询通过使用LIKE运算符可以设定只返回与用户规定格式匹配的记录，查询时，系统将逐行对表中的记录进行字符串匹配，如满足匹配条件，则包含该行，否则就不包含该行。基于字符串匹配的WHERE子句，其语法格式为：WHEREstring_expressionNOTLIKEstring_valueSQL中可以使用的通配符见表7.8所示。【例7-11】从Student表中分别查询出所有姓“李”的所有学生资料；名字的第二个字是“林”或“琳”的所有学生资料；名字的最后一个字是“林”或“琳”的所有学生资料；名字中包含“林”或“琳”的所有学生资料。USEEducationalGOSELECT*FROMStudentWHEREStudentNameLIKE李%;SELECT*FROMStudentWHEREStudentNameLIKE_林,琳%;SELECT*FROMStudentWHEREStudentNameLIKE%林,琳;SELECT*FROMStudentWHEREStudentNameLIKE%林,琳%;,7.2.3WHERE子句,5基于空值的查询有时希望知道表中的某一字段中到底有几个记录是NULL，即没有输入过任何值。例如需要知道哪些学生还没有输入过联系电话，此时在该记录的TelePhone字段中存放的是NULL。通常字段未赋予初值时，其值为NULL，不要把NULL值等同于0，NULL表示一种不能确定的数据，不能将具有NULL值的列参加算术运算。基于空值查询的WHERE子句，其语法格式为：WHEREcolumn_nameISNOTNULL【例7-12】从Student表中查询出哪些学生的联系电话为NULL。USEEducationalGOSELECT*FROMStudentWHERETelePhoneISNULL;,7.2.3WHERE子句,6组合查询可以使用逻辑运算符来连接多个条件，构成组合查询，从而完成更加复杂的查询。可以使用AND或OR连接两个关系或逻辑表达式，使用NOT对关系或逻辑表达式的值求反。组合查询的WHERE子句，其语法格式如下：WHERENOTexpressionAND|ORexpression;其中expression可以是关系或逻辑表达式。【例7-13】从Student表中查询出班号为“030501”的男同学资料。USEEducationalGOSELECT*FROMStudentWHEREClassID=030501andSex=男,7.2.4ORDERBY子句,一般存放在数据表中的记录是无法确定次序的。但对返回的数据集进行排序是常有的事。可以通过ORDERBY子句对SELECT语句检索出来的数据按要求进行排序。ORDERBY子句的语法格式如下：ORDERBYorder_expressionASC|DESC,n其中order_expression指定排序所使用的表达式，ASC表示按升序排列，否则使用DESC表示按降序排列。【例7-15】取出班号为“030501”所有同学的课程成绩，并按学号排序。USEEducationalGOSELECTS.StudentID,S.StudentName,CourseName,GradeFROMGradeGINNERJOINStudentSONS.StudentID=G.StudentIDINNERJOINCourseCONC.CourseID=G.CourseIDWHERES.ClassID=030501ORDERBYS.StudentID,7.2.5INTO子句,INTO子句可以根据查询所得到的数据集自动创建一个新数据表，并将查询所返回的数据集插入到新表中。INTO子句的语法格式如下：INTOnew_table_name【例7-16】将班号为“030501”的学生存入Stu030501表中。USEEducationalGOSELECT*INTOStu030501FROMStudentWHEREClassID=030501;,7.2.6统计,数据汇总是数据库应用程序的重要组成部分。SQL语句能将数据表中的数据按照一定规律和条件进行汇总，从而得到所需的统计结果。【例7-17】统计“030501”班学生已修学分的最高、最低和平均值。USEEducationalGOSELECTMAX(CreditHour)最高已修学分,AVG(CreditHour)平均已修学分,MIN(CreditHour)最低已修学分FROMStudentWHEREClassID=030501,7.2.7GROUPBY子句和HAVING子句,在SELECT语句中使用GROUPBY子句可以对查询的数据按要求进行分组，在分组基础上再对每个组使用常用聚合函数进行分类汇总。GROUPBY子句把检索出来的记录按条件进行分类分组，HAVING子句对GROUPBY子句检索出来的记录进行约束和限制。SQL的常用聚合函数只能用于SELECT子句及HAVING子句中。1GROUPBY子句GROUPBY子句的语法格式如下：GROUPBYALLgroup_by_expression,.n,7.2.7GROUPBY子句和HAVING子句,【例7-20】统计“030501”班第五学期每门课程的最高分、最低分和平均分。USEEducationalGOSELECTC.ClassName班名,Co.CourseName课程,MAX(Grade)最高分,MIN(Grade)最低分,AVG(Grade)平均分FROMGradeGINNERJOINStudentSONS.StudentID=G.StudentIDINNERJOINClassCONC.ClassID=S.ClassIDINNERJOINSpeciality_CourseSCONC.SpecialityID=SC.SpecialityIDandSC.CourseID=G.CourseIDINNERJOINCourseCoONCo.CourseID=G.CourseIDWHEREC.ClassID=030501andSC.Term=5GROUPBYC.ClassName,Co.CourseName,7.2.7GROUPBY子句和HAVING子句,2HAVING子句在GROUPBY子句中可以使用HAVING子句选取特定的组。如果一个组满足HAVING子句中关系表达式的要求，则该组就被包含在结果记录集中，否则将被排除在外，因此HAVING子句实际上是对分组后的数据再进行一次“过滤”。HAVING子句的语法格式如下：HAVING其中search_condition：指定组或聚合应满足的搜索条件。当HAVING与GROUPBYALL一起使用时，HAVING子句替代ALL。,7.2.7GROUPBY子句和HAVING子句,【例7-22】统计第五学期每门课平均分超出85分的班级、课程名、课程的最高分、最低分和平均分。USEEducationalGOSELECTC.ClassID,C.ClassName班名,Co.CourseName课程,MAX(Grade)最高分,MIN(Grade)最低分,AVG(Grade)平均分FROMGradeGINNERJOINStudentSONS.StudentID=G.StudentIDINNERJOINClassCONC.ClassID=S.ClassIDINNERJOINSpeciality_CourseSCONC.SpecialityID=SC.SpecialityIDandSC.CourseID=G.CourseIDINNERJOINCourseCoONCo.CourseID=G.CourseIDGROUPBYC.ClassID,C.ClassName,Co.CourseNameHAVINGAVG(Grade)85ORDERBYC.ClassID,7.2.8子查询,子查询可以嵌套在SELECT、DELETE、UPDATE语句的表达式中，并总是用圆括号括起来。一般使用在以下几个方面：l由关键字IN引出的子查询；l以单值的方式使用在各种表达式中；l使用NOTEXISTS关键字的子查询；,7.2.8子查询,1由关键字IN引出的子查询在该种方式下，通过子查询得到一组相关的数据。其语法格式为：NOTIN(子查询)【例7-24】查询“030501”班第五学期的任课教师情况。USEEducationalGOSELECT*FROMTeacherWHERETeacherIDIN(/*子查询:第五学期在030501班上课的教师ID*/SELECTCS.TeacherID/*取教师ID*/FROMSpeciality_CourseSCINNERJOINCurriculumScheduleCSONCS.CourseID=SC.CourseIDINNERJOINClassCONSC.SpecialityID=C.SpecialityIDANDCS.ClassID=C.ClassIDWHERECS.ClassID=030501/*设置班级ID*/ANDSC.Term=5/*第5学期*/),7.2.8子查询,2以单值的方式使用在各种表达式中在该种方式下，通过子查询返回一个单一的数据，该数据可以参加相关表达式的运算。【例7-26】查询“张小丽”同学的所有同班同学。USEEducationalSELECT*FROMStudentWHEREClassID=(/*子查询:取张小丽的班级ID*/SELECTClassID/*取班级ID*/FROMStudentWHEREStudentName=张小丽),7.2.8子查询,3使用NOTEXISTS关键字的子查询使用NOTEXISTS关键字引入一个子查询时，就相当于进行一次存在测试。子查询实际上不产生任何数据；它只返回TRUE或FALSE值。其语法格式为：WHERENOTEXISTS(子查询)使用EXISTS和NOTEXISTS引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。差集包含只属于两个集合中的第一个集合的元素。,7.2.8子查询,【例7-27】查询“030501”班学生的学号、课程号及其成绩。USEEducationalGOSELECT*FROMGradeGWHEREEXISTS(/*子查询:030501班的并在Grade表中有成绩记录的学生*/SELECT*FROMStudentSWHEREClassID=030501ANDStudentID=G.StudentID),7.3流程控制语句,T-SQL语言与其它高级语言一样，也提供了几个可以控制程序执行流程的语句。使用这些流程控制语句可以让程序员象C、Delphi和VB等高级语言一样，更好地组织和控制程序的流程。7.3.1注释使用/*/可以将其中的内容作为注释。/*表示注释的开始，*/表示注释的结束，它们必须成对出现，服务器不对位于/*和*/注释字符之间的文本进行处理。/*/的语法格式如下：/*text_of_comment*/在SQLServer中还有一种用于一行的注释，就是使用两个减号开头的注释，如下所示：-注释文本字符串,7.3流程控制语句,7.3.2BEGINEND使用BEGINEND可以将多条T-SQL语句封装起来，构成一个独立的语句块。BEGIN关键字表示语句块的开始，END关键字表示语句块的结束，它们必须成对出现。BEGINEND的语法格式如下：BEGINsql_statement|statement_blockEND,7.3流程控制语句,7.3.3IFELSEIFELSE语句可以控制程序按条件执行。当IF关键字后的条件满足（布尔表达式返回TRUE时），则在执行IF关键字及其条件之后的T-SQL语句。否则，就执行ELSE关键字后的T-SQL语句（若ELSE部分存在）。IFELSE语句的语法格式如下：IFBoolean_expressionsql_statement|statement_blockELSEsql_statement|statement_block,7.3流程控制语句,【例7-27】查询课程号为“105237”的课程的平均分是否超过了85分，若超过，则输出考出了高分的信息。USEEducationalGODECLARECourse_IDvarchar(10)SELECTCourse_ID=105237/*查询的课程号*/IF(SELECTAVG(Grade)FROMGradeWHERECourseID=Course_ID)85/*平均分大于85分*/BEGINSELECTCourse_ID=CourseName/*从课程信息表中取课程名*/FROMCourseWHERECourseID=Course_IDPRINTCourse_ID+课程PRINT考出了高分ENDELSEPRINT考的一般,7.3流程控制语句,7.3.4GOTO使用GOTO语句可以使程序的流程无条件地转移到指定的标签处继续执行。GOTO语句的语法格式如下：GOTOlabel其中label就是需指向的标签。标签必须符合标识符规则，且必须存在。【例7-28】求1+2+3+100的累加和。DECLAREiINT,sumINTSETi=1SETsum=0LabelLoop:SETsum=sum+iSETi=i+1IF(i=100)GOTOLabelLoopPRINTSum=+CAST(sumASVARCHAR(20),7.3流程控制语句,7.3.5WHLIE、BREAK和CONTINUE使用WHLIE语句可以实现语句块的循环执行。WHLIE语句的语法格式如下：WHILEBoolean_expressionsql_statement|statement_blockBREAKsql_statement|statement_blockCONTINUE,7.3流程控制语句,【例7-29】求1+2+3+n的累加和第一次超过1000时的n值。DECLAREiINT,sumINTSETi=1SETsum=0WHILE(i1000)BREAKENDPRINTn=+CAST(iASVARCHAR(5)+,Sum=+CAST(sumASVARCHAR(10),7.3流程控制语句,7.3.6RETURNRETURN语句的作用是无条件地退出正在操作的批处理、查询、存储过程或触发器。从而不执行位于RETURN之后的语句。RETURN语句退出时，还可以根据需要返回一个状态信息。RETURN语句的语法格式如下：RETURNinteger_expression,