《Transact-SQL程序设计.ppt》由会员分享，可在线阅读，更多相关《Transact-SQL程序设计.ppt（44页珍藏版）》请在三一办公上搜索。
1、第7章 Transact-SQL 程序设计,7.1 Transact-SQL语言基础7.2 SELECT查询语句7.3 流程控制语句,7.1 Transact-SQL语言基础,SQL是英文Structured Query Language的缩写，意为结构化查询语言。SQL语言是关系数据库管理系统(Database Management System，DBMS)中的标准语言，SQL语句可以用来执行创建和删除数据库、数据表，创建索引，更新数据库中的数据，从数据库中提取数据等各种操作。MS SQL Server 2000中使用的SQL被称为Transact-SQL，简称T-SQL。两个GO之间的T-
2、SQL语句作为一个批处理。在一个批处理中可以包含一条或多条T-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。SQL Server服务器将批处理编译成一个可执行单元，称为执行计划。,7.1.1 数据类型,在SQL Server 中每个变量、参数、表达式等都有一个相关的数据类型，此数据类型定义对象所能包含的数据种类。SQL Server 2000中定义了许多预定义的数据类型，同时允许用户根据需要自定义新的数据类型。系统中预定义的数据类型如表4.1所示。,7.1.2 变量,1.局部变量利用局部变量还可以保存程序执行过程中的中间结果，保存由存储过程
3、返回的数据值等。（1）局部变量的定义 必须先用DECLARE命令定义后才可以使用。DECLAER local_variable data_type n（2）局部变量的赋值方法 SET local_variable=expression 或者SELECT local_variable=expression,.n,7.1.2 变量,2.全部变量全局变量在整个SQL Server系统内使用。存储的通常是一些SQL Server的配置设定值和统计数据。在使用全局变量时应该注意以下几点：全局变量是在服务器级定义的。用户只能使用预先定义的全局变量。引用全局变量时，必须以标记符“”开头。全局变量对用户来说是
4、只读的。局部变量的名称不能与全局变量的名称相同。,7.1.3 运算符、表达式和优先级,1.算术运算符 算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）2.赋值运算符（=）3.字符串串联运算符（+）4.比较运算符比较运算符包括：等于（=）、大于（）、大于或等于（=）、小于（或!=）、不小于（!）,7.1.3 运算符、表达式和优先级,5.逻辑运算符 逻辑运算符包括与（AND）、或（OR）和非（NOT）等运算符。比较和逻辑运算均返回布尔值，值为TRUE或FALSE6.位运算符位运算符包括按位与（&）、按位或（|）、按位异或（）和求反（）。位运算符用来对整型数据或者二进制数据（image
5、数据类型除外）之间执行位操作。,7.1.3 运算符、表达式和优先级,5.逻辑运算符 逻辑运算符包括与（AND）、或（OR）和非（NOT）等运算符。比较和逻辑运算均返回布尔值，值为TRUE或FALSE6.位运算符位运算符包括按位与（&）、按位或（|）、按位异或（）和求反（）。位运算符用来对整型数据或者二进制数据（image数据类型除外）之间执行位操作。,7.1.3 运算符、表达式和优先级,7.运算符优先级 在同一表达式中可能包含多种运算符，而运算符是有优先级的。运算符的优先级决定了表达式中的各个运算符参加运算的顺序。在T-SQL中，运算符的优先级从高到低如下所示：括号：（）求反：正负：+、-乘、
6、除、求模运算符：*、/、%加、字符串连接和减运算符：+、+、-比较运算符：=、=、!=、!、!位运算符：、&、|逻辑运算符：NOT逻辑运算符：AND逻辑运算符：OR赋值运算符：=,7.1.4 系统内置函数,1.数学函数 ABS、ACOS、ASIN、ATAN、CEILING、SIN、COS、TAN、EXP、FLOOR、LOG、LOG10、PI、POWER、RAND、ROUND、SIGN、SQRT2.字符串函数ASCII、CHAR、LEFT、RIGHT、LEN、LOWER、UPPER、LTRIM、RTRIM、REPLACE、REVERSE、SPACE、STR、STUFF、SUBSTRING3.转换
7、函数 CAST（expression AS data_type）CONVERT（data_type（length）,expression,style）,7.1.4 系统内置函数,4.日期与时间函数 YEAR、MONTH、DAY、GETDATE、DATEADD、DATEDIFF、DATENAME、DATEPART5.聚合函数 AVG、COUNT、COUNT(*)、MIN、MAX、SUM,7.2 SELECT查询语句,SELECT语句中子句很多，但常用的关键字一般有四个：SELECT、FROM、WHERE和ORDER BY。SELECT关键字用于从数据库中检索数据；FROM关键字用于指定要检索的表
8、名；WHERE关键字用于指定检索条件；ORDER BY关键字用于指定结果数据集的排序方式。SELECT语句的基本语法格式如下：SELECT select_listINTO new_table_nameFROM table_listWHERE search_conditionsGROUP BY group_by_listHAVING search_conditionsORDER BY order_list ASC|DESC,7.2.1 SELECT子句,SELECT子句用于指定由查询返回的结果列。SELECT子句的语法格式如下：SELECT ALL|DISTINCT TOP n PERCENT*
9、|column_name|expression AS column_alias|column_alias=expression,n【例7-3】从学生信息表中查询所有学生的学号、姓名、性别和已修学分。SELECT TOP 5 StudentID As 学号,StudentName As 姓名,Sex As 性别,已修学分=CreditHourFROM Student,7.2.2 FORM子句,用 FROM子句可以：l列出SELCET子句或WHERE子句中所引用的列所在的表和视图。可用AS子句为表和视图的名称指定别名。l连接类型。这些类型由ON子句中指定的连接条件限定。FROM子句是用逗号分隔的表
10、名、视图名和JOIN子句的列表。FROM子句的语法格式如下：FROM,.n,7.2.2 FROM子句,1指定表或视图 通过FORM子句指定查询数据的来源是表或视图。这是FORM子句中最为简单的应用2指定连接 按照数据库的设计理论，在设计数据库时，为了减少数据冗余、防止因设计的数据库不规范而出现的插入、删除和修改异常，往往将相关数据存储在不同的表中。如此在查询数据库时就要将几个相关表进行连接后才能得到需要的查询结果。,7.2.2 FROM子句,【例7-5】一个内连接的例子。查询所有学生已考的课程成绩，并以姓名、课程名、成绩的格式显示。学生已考的课程成绩存放在学生成绩表Grade中，而姓名存放在学
11、生信息表Student中；课程名存放在课程信息表Course中。因此，要取得所有学生每门课程的成绩，必须通过连接才能实现：按StudentID列连接Student表，按CourseID列连接Course表。SELECT S.StudentName As 姓名,C.CourseName As 课程名,Grade As 成绩FROM Grade G INNER JOIN Student S ON G.StudentID=S.StudentIDINNER JOIN Course C ON G.CourseID=C.CourseID,7.2.2 FROM子句,【例7-6】一个外连接的例子。Specia
12、lity_Course是左表，Speciality为右表，若两表进行左外连接，则返回左表Speciality_Course中的全部行，如右表中有不满足连接条件的行，则右表对应的数据就为空值，结果如图7-3所示；若两表进行右外连接，则返回右表Speciality中的全部行，如左表中有不满足连接条件的行，则左表对应的数据就为空值。左外连接：SELECT S.SpecialityID,S.SpecialityName,SC.CourseID,SC.TermFROM Speciality_Course SC LEFT OUTER JOIN Speciality S ON S.SpecialityID=
13、SC.SpecialityID,7.2.3 WHERE子句,在SELECT语句的实际应用中，极大多数情况下都不是针对表中所有行的查询，而是希望从表中筛选出符合条件的行。要实现筛选查询就需要使用WHERE子句。WHERE子句的语法格式如下：WHERE 其中search_condition用于指定限制返回行的查询条件。其中SQL Server支持的查询条件运算符参见表7.1中的比较和逻辑运算符。,7.2.3 WHERE子句,1基于比较的查询基于比较的WHERE子句，其语法格式为：WHERE expression comparision_operator expression其中expression
14、为比较的表达式，comparision_operator为比较运算符。SQL支持的比较运算符见表7.1所示。【例7-7】从Student表中查询出在1983/3/30之前出生的学生。USE EducationalGOSELECT*FROM StudentWHERE Birthday 1983/3/30;,7.2.3 WHERE子句,2基于BETWEEN关键字的查询 有时希望查找的是具有上下限范围的记录，这时就需要使用基于范围的查询。基于范围的WHERE子句，其语法格式为：WHERE expression NOT BETWEEN expression1 AND expression2其中expr
15、ession1是下限，expression2是上限。该语句表示返回expression值在或不在（有NOT时）这两个值之间的所有记录。【例7-8】从Student表中查询已修学分在86到100之间的所有学生。USE EducationalGOSELECT*FROM StudentWHERE CreditHour BETWEEN 86 AND 100;,7.2.3 WHERE子句,3基于IN关键字的查询 有时希望查找的是指定值的若干个记录，这时就需要使用基于集合的查询。基于集合的WHERE子句，其语法格式为：WHERE NOT expression NOT IN(value,n)其中IN后面括号
16、内的就是要查询的若干指定值，中间用逗号“,”分隔。【例7-9】从Student表中查询班号为030501和030505的所有学生。USE EducationalGOSELECT*FROM StudentWHERE ClassID IN(030501,030505);,7.2.3 WHERE子句,4基于LIKE关键字的查询 通过使用LIKE运算符可以设定只返回与用户规定格式匹配的记录，查询时，系统将逐行对表中的记录进行字符串匹配，如满足匹配条件，则包含该行，否则就不包含该行。基于字符串匹配的WHERE子句，其语法格式为：WHERE string_expression NOT LIKE strin
17、g_valueSQL中可以使用的通配符见表7.8所示。【例7-11】从Student表中分别查询出所有姓“李”的所有学生资料；名字的第二个字是“林”或“琳”的所有学生资料；名字的最后一个字是“林”或“琳”的所有学生资料；名字中包含“林”或“琳”的所有学生资料。USE EducationalGOSELECT*FROM Student WHERE StudentName LIKE 李%;SELECT*FROM Student WHERE StudentName LIKE _林,琳%;SELECT*FROM Student WHERE StudentName LIKE%林,琳;SELECT*FROM
18、 Student WHERE StudentName LIKE%林,琳%;,7.2.3 WHERE子句,5基于空值的查询 有时希望知道表中的某一字段中到底有几个记录是NULL，即没有输入过任何值。例如需要知道哪些学生还没有输入过联系电话，此时在该记录的TelePhone字段中存放的是NULL。通常字段未赋予初值时，其值为NULL，不要把NULL值等同于0，NULL表示一种不能确定的数据，不能将具有NULL值的列参加算术运算。基于空值查询的WHERE子句，其语法格式为：WHERE column_name IS NOT NULL【例7-12】从Student表中查询出哪些学生的联系电话为NULL。
19、USE EducationalGOSELECT*FROM Student WHERE TelePhone IS NULL;,7.2.3 WHERE子句,6组合查询 可以使用逻辑运算符来连接多个条件，构成组合查询，从而完成更加复杂的查询。可以使用AND或OR连接两个关系或逻辑表达式，使用NOT对关系或逻辑表达式的值求反。组合查询的WHERE子句，其语法格式如下：WHERE NOT expression AND|OR expression;其中expression可以是关系或逻辑表达式。【例7-13】从Student表中查询出班号为“030501”的男同学资料。USE EducationalGOS
20、ELECT*FROM StudentWHERE ClassID=030501 and Sex=男,7.2.4 ORDER BY子句,一般存放在数据表中的记录是无法确定次序的。但对返回的数据集进行排序是常有的事。可以通过ORDER BY子句对SELECT语句检索出来的数据按要求进行排序。ORDER BY子句的语法格式如下：ORDER BY order_expression ASC|DESC,n其中order_expression指定排序所使用的表达式，ASC表示按升序排列，否则使用DESC表示按降序排列。【例7-15】取出班号为“030501”所有同学的课程成绩，并按学号排序。USE Educa
21、tionalGOSELECT S.StudentID,S.StudentName,CourseName,GradeFROM Grade GINNER JOIN Student S ON S.StudentID=G.StudentIDINNER JOIN Course C ON C.CourseID=G.CourseIDWHERE S.ClassID=030501ORDER BY S.StudentID,7.2.5 INTO子句,INTO子句可以根据查询所得到的数据集自动创建一个新数据表，并将查询所返回的数据集插入到新表中。INTO子句的语法格式如下：INTO new_table_name【例7
22、-16】将班号为“030501”的学生存入Stu030501表中。USE EducationalGOSELECT*INTO Stu030501FROM StudentWHERE ClassID=030501;,7.2.6 统计,数据汇总是数据库应用程序的重要组成部分。SQL语句能将数据表中的数据按照一定规律和条件进行汇总，从而得到所需的统计结果。【例7-17】统计“030501”班学生已修学分的最高、最低和平均值。USE EducationalGOSELECT MAX(CreditHour)最高已修学分,AVG(CreditHour)平均已修学分,MIN(CreditHour)最低已修学分FR
23、OM StudentWHERE ClassID=030501,7.2.7 GROUP BY子句和HAVING子句,在SELECT语句中使用GROUP BY子句可以对查询的数据按要求进行分组，在分组基础上再对每个组使用常用聚合函数进行分类汇总。GROUP BY子句把检索出来的记录按条件进行分类分组，HAVING子句对GROUP BY子句检索出来的记录进行约束和限制。SQL的常用聚合函数只能用于SELECT子句及HAVING子句中。1GROUP BY子句GROUP BY子句的语法格式如下：GROUP BY ALL group_by_expression,.n,7.2.7 GROUP BY子句和HA
24、VING子句,【例7-20】统计“030501”班第五学期每门课程的最高分、最低分和平均分。USE EducationalGOSELECT C.ClassName 班名,Co.CourseName 课程,MAX(Grade)最高分,MIN(Grade)最低分,AVG(Grade)平均分FROM Grade G INNER JOIN Student S ON S.StudentID=G.StudentID INNER JOIN Class C ON C.ClassID=S.ClassID INNER JOIN Speciality_Course SC ON C.SpecialityID=SC.S
25、pecialityID and SC.CourseID=G.CourseID INNER JOIN Course Co ON Co.CourseID=G.CourseIDWHERE C.ClassID=030501 and SC.Term=5GROUP BY C.ClassName,Co.CourseName,7.2.7 GROUP BY子句和HAVING子句,2HAVING子句在GROUP BY子句中可以使用HAVING子句选取特定的组。如果一个组满足HAVING子句中关系表达式的要求，则该组就被包含在结果记录集中，否则将被排除在外，因此HAVING子句实际上是对分组后的数据再进行一次“过滤
26、”。HAVING子句的语法格式如下：HAVING 其中search_condition：指定组或聚合应满足的搜索条件。当HAVING与GROUP BY ALL一起使用时，HAVING子句替代ALL。,7.2.7 GROUP BY子句和HAVING子句,【例7-22】统计第五学期每门课平均分超出85分的班级、课程名、课程的最高分、最低分和平均分。USE EducationalGOSELECT C.ClassID,C.ClassName 班名,Co.CourseName 课程,MAX(Grade)最高分,MIN(Grade)最低分,AVG(Grade)平均分FROM Grade G INNER J
27、OIN Student S ON S.StudentID=G.StudentID INNER JOIN Class C ON C.ClassID=S.ClassID INNER JOIN Speciality_Course SC ON C.SpecialityID=SC.SpecialityID and SC.CourseID=G.CourseID INNER JOIN Course Co ON Co.CourseID=G.CourseIDGROUP BY C.ClassID,C.ClassName,Co.CourseNameHAVING AVG(Grade)85ORDER BY C.Clas
28、sID,7.2.8 子查询,子查询可以嵌套在SELECT、DELETE、UPDATE语句的表达式中，并总是用圆括号括起来。一般使用在以下几个方面：l由关键字IN引出的子查询；l以单值的方式使用在各种表达式中；l使用NOT EXISTS关键字的子查询；,7.2.8 子查询,1由关键字IN引出的子查询在该种方式下，通过子查询得到一组相关的数据。其语法格式为：NOT IN(子查询)【例7-24】查询“030501”班第五学期的任课教师情况。USE EducationalGOSELECT*FROM TeacherWHERE TeacherID IN(/*子查询:第五学期在030501班上课的教师ID*
29、/SELECT CS.TeacherID/*取教师ID*/FROM Speciality_Course SC INNER JOIN CurriculumSchedule CS ON CS.CourseID=SC.CourseID INNER JOIN Class C ON SC.SpecialityID=C.SpecialityID AND CS.ClassID=C.ClassID WHERE CS.ClassID=030501/*设置班级ID*/AND SC.Term=5/*第5学期*/),7.2.8 子查询,2以单值的方式使用在各种表达式中 在该种方式下，通过子查询返回一个单一的数据，该数
30、据可以参加相关表达式的运算。【例7-26】查询“张小丽”同学的所有同班同学。USE EducationalSELECT*FROM StudentWHERE ClassID=(/*子查询:取张小丽的班级ID*/SELECT ClassID/*取班级ID*/FROM Student WHERE StudentName=张小丽),7.2.8 子查询,3使用NOT EXISTS关键字的子查询 使用NOT EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。子查询实际上不产生任何数据；它只返回TRUE或FALSE值。其语法格式为：WHERE NOT EXISTS(子查询)使用 EXISTS
31、和 NOT EXISTS 引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。差集包含只属于两个集合中的第一个集合的元素。,7.2.8 子查询,【例7-27】查询“030501”班学生的学号、课程号及其成绩。USE EducationalGOSELECT*FROM Grade GWHERE EXISTS(/*子查询:030501班的并在Grade表中有成绩记录的学生*/SELECT*FROM Student S WHERE ClassID=030501 AND StudentID=G.StudentID),7.3 流程控制语句,T-SQL语言与其它
32、高级语言一样，也提供了几个可以控制程序执行流程的语句。使用这些流程控制语句可以让程序员象C、Delphi和VB等高级语言一样，更好地组织和控制程序的流程。7.3.1 注释使用/*/可以将其中的内容作为注释。/*表示注释的开始，*/表示注释的结束，它们必须成对出现，服务器不对位于/*和*/注释字符之间的文本进行处理。/*/的语法格式如下：/*text_of_comment*/在SQL Server中还有一种用于一行的注释，就是使用两个减号开头的注释，如下所示：-注释文本字符串,7.3 流程控制语句,7.3.2 BEGINEND 使用BEGINEND可以将多条T-SQL语句封装起来，构成一个独立的
33、语句块。BEGIN关键字表示语句块的开始，END关键字表示语句块的结束，它们必须成对出现。BEGINEND的语法格式如下：BEGIN sql_statement|statement_block END,7.3 流程控制语句,7.3.3 IFELSE IFELSE语句可以控制程序按条件执行。当IF关键字后的条件满足（布尔表达式返回TRUE 时），则在执行IF关键字及其条件之后的T-SQL 语句。否则，就执行ELSE关键字后的T-SQL语句（若ELSE部分存在）。IFELSE语句的语法格式如下：IF Boolean_expression sql_statement|statement_block
34、ELSE sql_statement|statement_block,7.3 流程控制语句,【例7-27】查询课程号为“105237”的课程的平均分是否超过了85分，若超过，则输出考出了高分的信息。USE EducationalGODECLARE Course_ID varchar(10)SELECT Course_ID=105237/*查询的课程号*/IF(SELECT AVG(Grade)FROM Grade WHERE CourseID=Course_ID)85/*平均分大于85分*/BEGIN SELECT Course_ID=CourseName/*从课程信息表中取课程名*/FROM
35、 Course WHERE CourseID=Course_ID PRINT Course_ID+课程 PRINT 考出了高分ENDELSE PRINT 考的一般,7.3 流程控制语句,7.3.4 GOTO 使用GOTO语句可以使程序的流程无条件地转移到指定的标签处继续执行。GOTO语句的语法格式如下：GOTO label其中label就是需指向的标签。标签必须符合标识符规则，且必须存在。【例7-28】求1+2+3+100的累加和。DECLARE i INT,sum INTSET i=1SET sum=0LabelLoop:SET sum=sum+i SET i=i+1IF(i=100)GOT
36、O LabelLoopPRINT Sum=+CAST(sum AS VARCHAR(20),7.3 流程控制语句,7.3.5 WHLIE、BREAK和CONTINUE 使用WHLIE语句可以实现语句块的循环执行。WHLIE语句的语法格式如下：WHILE Boolean_expression sql_statement|statement_block BREAK sql_statement|statement_block CONTINUE,7.3 流程控制语句,【例7-29】求1+2+3+n的累加和第一次超过1000时的n值。DECLARE i INT,sum INTSET i=1SET sum=0WHILE(i 1000)BREAKENDPRINT n=+CAST(i AS VARCHAR(5)+,Sum=+CAST(sum AS VARCHAR(10),7.3 流程控制语句,7.3.6 RETURN RETURN语句的作用是无条件地退出正在操作的批处理、查询、存储过程或触发器。从而不执行位于RETURN之后的语句。RETURN语句退出时，还可以根据需要返回一个状态信息。RETURN语句的语法格式如下：RETURN integer_expression,
