《SQLServer数据库应用与开发》-第五章

项目七在数据库系统中编程一、T−SQL语言尽管大多数关系数据库管理系统都将SQL语言（StructuredQueryLanguage，结构化查询语句）作为标准的数据库语言，但几个重要的数据库供应商都对SQL进行了不同程度的扩展，T−SQL和P−SQL便是市场上典型的对SQL的扩充版本。T−SQL语言适用于微软的SQLServer数据库和赛贝斯的SybaseAdaptiveServer数据库，而P−SQL语言则适用于甲骨文公司的Oracle数据库。T−SQL是Transact−SQL的简写。利用T−SQL语言进行程序设计是SQLServer的主要应用形式之一。下一页返回项目七在数据库系统中编程不论是客户机/服务器架构的数据库应用程序，还是Web应用程序，都会涉及对数据库中的数据进行处理，客户端应用程序只要发送T−SQL语句，就可通过特定的接口与服务器端的SQLServer数据库管理系统实现通信，查询或修改相应数据并返回到客户端，完成数据的显示和管理。可以把T−SQL语句嵌入某种高级程序设计语言中来执行（如VB、VC、PB、Delphi），但T−SQL本身不提供用户界面，数据的输入和显示需要通过高级程序设计语言中的控件来完成。二、T−SQL语言的组成上一页下一页返回项目七在数据库系统中编程T−SQL语言由以下几部分组成。1.数据定义语句（DDL）数据定义语句用于对数据库以及数据库中的对象进行创建、删除、修改等操作，如CREATE、ALTER、DROP等。数据库对象主要包括表、缺省约束、规则、视图、存储过程和触发器等。数据定义语句的语法请参见前面章节。2.数据操纵语句（DML）数据操纵语句用于操纵数据库中的各种对象，如select、insert、update、delete等。其语法的使用方法，请参见前面章节。上一页下一页返回项目七在数据库系统中编程3.数据控制语句（DCL）数据控制语句用于安全管理，确认哪些用户可以查看或修改数据库中的数据，如：GRANT授予用户权限，REVOKE收回权限，DENY收回权限，并禁止其他角色继承权限。4.T−SQL增加的语言元素这部分是微软为了方便用户编程而增加的语言元素，包括变量、运算符、函数、能够控制的语句和注释等，本章将介绍这部分内容。三、T−SQL语言程序设计T−SQL语言与其他计算机语言一样包含以下部分内容，T−SQL程序设计也将分任务介绍：上一页下一页返回项目七在数据库系统中编程（1）变量和常量；（2）系统数据类型和自定义数据类型；（3）运算符；（4）流程控制语句；（5）系统函数。任务一常量、变量、自定义数据类型、运算符的使用【知识准备】上一页下一页返回项目七在数据库系统中编程1.常量常量是指在程序运行过程中其值不变的量。根据常量值的不同类型，常量分为字符串常量、整型常量、实型常量、日期时间常量等，举例如下：1）字符串常量字符串常量是用单引号括起来，由ASCII字符构成的符号串。2）整型常量整型常量分为二进制整型常量、十六进制整型常量和十进制整型常量。上一页下一页返回项目七在数据库系统中编程二进制整型常量为0或1。十六进制整型常量以前缀0x开头，后面紧跟由数字0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F组成的数字。十进制整型常量就是最常见的不带小数点的十进制数。3）实型常量实型常量有定点表示和浮点表示两种方式，如：（1）定点表示：2968.43073.6−236876.013上一页下一页返回项目七在数据库系统中编程（2）浮点表示：234.6E50.4E−2−46E54）日期时间常量其包含日期常量、时间常量和日期时间常量，它们都需要用单引号将表示时间日期的字符串括起来，以下都是SQLServer可以识别的不同格式的常量：（1）字母日期格式，如：'April23，2010'；上一页下一页返回项目七在数据库系统中编程（2）数字日期格式，如：'5/21/1995'；（3）未分隔的字符串格式，如：'20110927'、'December19，2000'；（4）时间常量，如：'17：36：12'、'09：45：PM'；（5）日期时间常量，如：'April23，201009：45：12'。2.变量变量用于存放数据，分为全局变量和局部变量。1）变量的声明全局变量一般由系统提供且预先声明，典型标志是标识符前有“@@”符号，如@@ERROR、@@CONNECTIONS等。上一页下一页返回项目七在数据库系统中编程局部变量的首字母是“@”符号，通常在局部声明DECLARE中使用，局部变量在声明后的初始值为NULL,声明局部变量的语法格式为：DECLARE@变量名数据类型[,@变量名数据类型,…]2）变量的赋值可以用set和select来给变量赋值，语法格式为：set@变量名=值或表达式select@变量名=值或表达式[,值或表达式]用set只能一次赋值一个变量，而使用select赋值，可以一次赋值多个变量。上一页下一页返回项目七在数据库系统中编程3.系统数据类型T−SQL语言的数据类型有：（1）数值类型，如：int、smallint、tinyint、bit、decimal、numeric、float、real等。（2）货币类型，如：money、smallmoney。（3）日期类型，如：datetime、smalldatetime。（4）字符类型，如：char、varchar、text等。（5）二进制类型，如：binary、varbinary、image等。（6）其他类型，如：timestamp等。上一页下一页返回项目七在数据库系统中编程详细用法请参见第二章相关内容。4.自定义数据类型系统数据类型通常已经够用，但当某一特定值的数据类型使用频度高时，为方便起见，可以将其定义为一种新的数据类型，这种新的数据类型就称为自定义数据类型。实现自定义数据类型，需要用到系统存储过程sp_addtype和sp_droptype。（1）自定义数据类型的定义：EXECSP_ADDTYPE'新数据类型名'，'系统数据类型'，'是否为空'（2）自定义数据类型的删除：上一页下一页返回项目七在数据库系统中编程EXECsp_droptype'新数据类型名'5.运算符SQLServer2008提供了很多运算符，有算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符等，主要的运算符及其对应的优先级见表5−1。【任务实施】1.常量、变量的使用（定义和赋值）创建两个字符型局部变量@ver1、@ver1，给变量赋值并输出。在SQLServer2008中新建查询，输入以下代码：上一页下一页返回项目七在数据库系统中编程DECLARE@ver1char(8),@ver2char(30)set@ver1='江苏省'set@ver2=@ver1+'信息化技能大赛'select@ver1,@ver2执行的结果如图5−1所示。打开数据库STUDY，创建一个局部变量sex，并在select语句中使用该局部变量查找王姓男同学的学号、姓名和出生时间。useSTUDYGO上一页下一页返回项目七在数据库系统中编程DECLARE@sexbitset@sex=1selectstudentid,name,birthdayfromtb_studentwherenamelike'王%'andsex=@sex执行的结果如图5−2所示。给局部变量赋空值。代码如下：DECLARE@varchar(20)select@var='李阳'select@var=上一页下一页返回项目七在数据库系统中编程(selectnamefromtb_studentwherestudentid='008800')select@varas'NAME'执行的结果如图5−3所示。2.自定义数据类型的语法实现定义一个新的数据类型char_student，要求其是长度为10的字符串类型，非空，代码如下：useSTUDY/*打开数据库*/GO上一页下一页返回项目七在数据库系统中编程EXECsp_addtype'char_student','char(10)','notnull'/*执行存储过程sp_addtype来定义新数据类型*/'删除自定义数据类型char_student，代码如下：EXECsp_droptype'char_student'值得注意的是，删除自定义数据类型的前提是：没有哪个表正在使用该自定义数据类型，即必须先解除数据库表中对自定义数据库类型的使用，才能删除自定义数据库类型。3.自定义数据类型的界面实现采用界面方式实现自定义数据类型的定义、使用和删除等非常方便，其步骤如下：上一页下一页返回项目七在数据库系统中编程第一步：在SQLServer2008的“对象资源管理器”中展开“数据库”STUDY→展开“可编程性”→展开“类型”→用鼠标右键单击“用户定义数据类型”→单击“新建用户定义数据类型”，如图5−4所示。第二步：在弹出的窗口中输入新数据类型的名称“char_student”，选择“数据类型”为“char”，“长度”为“10”，不选“允许空值”，单击“确定”按钮，如图5−5所示。上一页下一页返回项目七在数据库系统中编程第三步：选择表tb_student，用鼠标右键单击“设计”，在学号字段“studentid”的数据类型处，通过拉杆可以看到新数据类型“char_student”已经存在，选中它并保存表，即完成了新数据类型的使用，如图5−6所示。第四步：当解除了数据库表中对自定义数据类型的使用后，在展开的“用户定义数据类型”中用鼠标右键单击自定义类型“char_student”，选择“删除”，即可删除自定义数据类型，如图5−7所示。4.运算符的使用定义两个整型变量，按位运算。代码如下：上一页下一页返回项目七在数据库系统中编程DECLARE@var1int,@var2intset@var1=150set@var2=175select@var1&@var2,@var1|@var2执行的结果如图5−8所示。判断学号为001101的学生是否存在，并输出该学生的情况。代码如下：DECLARE@studentchar(8)set@student='001101'上一页下一页返回项目七在数据库系统中编程if(@student<>'')select*fromtb_studentwherestudentid=@student执行的结果如图5−9所示。查询总学分在40～50之外的所有学生的学号和姓名。代码如下：selectstudentid,namefromtb_studentwheresumcreditnotbetween40and50查询所有选课学生的姓名。代码如下：上一页下一页返回项目七在数据库系统中编程selectdistinctnamefromtb_studentwhereexists(select*fromtb_score)即只要在tb_score表中选修了任何课程，就查询显示出姓名，distinct用于消除重复姓名。字符串的连接。代码如下：select(studentid+','+name)as学号和姓名fromtb_studentwherestudentid='001102'执行的结果如图5−10所示。上一页下一页返回项目七在数据库系统中编程任务二流程控制语句的使用【知识准备】T−SQL语言中包含许多流程控制语句，见表5−2。（1）语句块的语法：beginT−SQL语句1T−SQL语句2…end上一页下一页返回项目七在数据库系统中编程多个T−SQL语句放在一起作为一个整体来运行。（2）条件语句的语法：if条件表达式T−SQL语句1elseT−SQL语句2条件表达式为真时，执行T−SQL语句1，否则，执行T−SQL语句2。本条件语句也可更为简单：if条件表达式上一页下一页返回项目七在数据库系统中编程T−SQL语句（3）分支语句的语法：casewhen条件语句1then值1when条件语句2then值2……else值nend上一页下一页返回项目七在数据库系统中编程条件语句1为真时，得到值1，条件语句2为真时，得到值2，……，所有条件语句都为假时，得到值n。（4）循环语句的语法：while条件语句T-SQL语句块当条件语句为真时，执行T−SQL语句块。【任务实施】1.begin…end语句块查询所有学生的情况，再查询所有的课程。代码如下：上一页下一页返回项目七在数据库系统中编程useSTUDYgobeginselect*fromtb_studentselect*fromtb_courseend2.条件语句查询总学分大于50分的学生人数。代码如下：DECLARE@numint上一页下一页返回项目七在数据库系统中编程select@num=(selectcount()fromtb_studentwheresumcredit>50)if@num>0select@numas'总学分大于50分的人数'如果“计算机基础”课程的平均成绩高于70分，则显示“平均成绩高于70分”，否则显示“平均成绩低于70分”。代码如下：if(selectavg(score)fromtb_student,tb_score,tb_coursewheretb_student.studentid=tb_score.studentid上一页下一页返回项目七在数据库系统中编程andtb_course.courseid=tb_score.courseidandtb_course.coursename='计算机基础')>70select'平均成绩高于70分'elseselect'平均成绩低于70分'本例中，查询语句返回的值用于条件判断。3.分支语句输出学生表tb_student中的学号、姓名、专业和性别，性别值按照“男”或“女”显示。代码如下：上一页下一页返回项目七在数据库系统中编程selectstudentid,name,specialty,sex=casewhensex=1then'男'whensex=0then'女'else'无'endfromtb_studentwheresumcredit>424.while循环语句上一页下一页返回项目七在数据库系统中编程将学号为001106的学生的总学分通过循环语句修改到大于等于62，每次加2分，判断循环了几次。代码如下：DECLARE@numintset@num=0while(selectsumcreditfromtb_studentwherestudentid='001106')<62beginupdatetb_studentsetsumcredit=sumcredit+2wherestudentid='001106'上一页下一页返回项目七在数据库系统中编程set@num=@num+1endselect@numas循环次数执行的结果是需要7次循环。任务三系统函数的使用【知识准备】SQLServer2008中包含大量的系统函数，展开“对象资源管理器”中的“可编程性”下的“函数”，可看到分门别类的系统函数，如图5−11所示。上一页下一页返回项目七在数据库系统中编程大量的系统函数为T−SQL程序设计提供了强大的支持，其中以聚合函数、日期时间函数、数学函数、其他函数、字符串函数等用得最多。常用函数见表5−3。【任务实施】（1）日期时间函数的使用——求系统时间。selectgetdate(),year(getdate()),month(getdate()),day(getdate())执行的结果如图5−12所示。（2）数学函数的使用——产生一个0～1的随机数。DECLARE@R