sqlserver2008数据库应用与开发教学课件作者李新德第五章

第五章 TSQL 程序设计,项目七 在数据库系统中编程,返回,项目七 在数据库系统中编程,一、TSQL 语言 尽管大多数关系数据库管理系统都将SQL 语言（Structured Query Language，结构化查询语句）作为标准的数据库语言，但几个重要的数据库供应商都对SQL 进行了不同程度的扩展，TSQL 和PSQL 便是市场上典型的对SQL 的扩充版本。TSQL 语言适用于微软的SQL Server数据库和赛贝斯的Sybase Adaptive Server 数据库，而PSQL 语言则适用于甲骨文公司的Oracle 数据库。 TSQL 是TransactSQL 的简写。利用TSQL 语言进行程序设计是SQL Server 的主要应用形式之一。,下一页,返回,项目七 在数据库系统中编程,不论是客户机/服务器架构的数据库应用程序，还是Web 应用程序，都会涉及对数据库中的数据进行处理，客户端应用程序只要发送TSQL 语句，就可通过特定的接口与服务器端的SQL Server 数据库管理系统实现通信，查询或修改相应数据并返回到客户端，完成数据的显示和管理。 可以把TSQL 语句嵌入某种高级程序设计语言中来执行（如VB、VC、PB、Delphi），但TSQL 本身不提供用户界面，数据的输入和显示需要通过高级程序设计语言中的控件来完成。 二、TSQL 语言的组成,上一页,下一页,返回,项目七 在数据库系统中编程,TSQL 语言由以下几部分组成。 1. 数据定义语句（DDL） 数据定义语句用于对数据库以及数据库中的对象进行创建、删除、修改等操作，如CREATE、ALTER、DROP 等。数据库对象主要包括表、缺省约束、规则、视图、存储过程和触发器等。 数据定义语句的语法请参见前面章节。 2. 数据操纵语句（DML） 数据操纵语句用于操纵数据库中的各种对象，如select、insert、update、delete 等。其语法的使用方法，请参见前面章节。,上一页,下一页,返回,项目七 在数据库系统中编程,3. 数据控制语句（DCL） 数据控制语句用于安全管理，确认哪些用户可以查看或修改数据库中的数据，如：GRANT授予用户权限，REVOKE 收回权限，DENY 收回权限，并禁止其他角色继承权限。 4. TSQL 增加的语言元素 这部分是微软为了方便用户编程而增加的语言元素，包括变量、运算符、函数、能够控制的语句和注释等，本章将介绍这部分内容。 三、TSQL 语言程序设计 TSQL 语言与其他计算机语言一样包含以下部分内容，TSQL 程序设计也将分任务介绍：,上一页,下一页,返回,项目七 在数据库系统中编程,（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.4307 3.6 236876.013,上一页,下一页,返回,项目七 在数据库系统中编程,（2） 浮点表示： 234.6E5 0.4E2 46E5 4）日期时间常量 其包含日期常量、时间常量和日期时间常量，它们都需要用单引号将表示时间日期的字符串括起来，以下都是SQL Server 可以识别的不同格式的常量： （1） 字母日期格式，如：'April23，2010'；,上一页,下一页,返回,项目七 在数据库系统中编程,（2） 数字日期格式，如：'5/21/1995'； （3） 未分隔的字符串格式，如：'20110927'、'December19，2000'； （4） 时间常量，如：'17：36：12'、'09：45：PM'； （5） 日期时间常量，如：'April23，2010 09：45：12'。 2. 变量 变量用于存放数据，分为全局变量和局部变量。 1）变量的声明 全局变量一般由系统提供且预先声明，典型标志是标识符前有“”符号，如ERROR、CONNECTIONS 等。,上一页,下一页,返回,项目七 在数据库系统中编程,局部变量的首字母是“”符号，通常在局部声明DECLARE 中使用，局部变量在声明后的初始值为NULL,声明局部变量的语法格式为： DECLARE 变量名 数据类型,变量名 数据类型, 2）变量的赋值 可以用set 和select 来给变量赋值，语法格式为： set 变量名=值或表达式 select 变量名=值或表达式,值或表达式 用set 只能一次赋值一个变量，而使用select 赋值，可以一次赋值多个变量。,上一页,下一页,返回,项目七 在数据库系统中编程,3. 系统数据类型 TSQL 语言的数据类型有： （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） 自定义数据类型的定义： EXEC SP_ADDTYPE '新数据类型名'，'系统数据类型'，'是否为空' （2） 自定义数据类型的删除：,上一页,下一页,返回,项目七 在数据库系统中编程,EXEC sp_droptype '新数据类型名' 5. 运算符 SQL Server 2008 提供了很多运算符，有算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符等，主要的运算符及其对应的优先级见表51。 【任务实施】 1. 常量、变量的使用（定义和赋值） 创建两个字符型局部变量ver1、ver1，给变量赋值并输出。 在SQL Server 2008 中新建查询，输入以下代码：,上一页,下一页,返回,项目七 在数据库系统中编程,DECLARE ver1 char(8),ver2 char(30) set ver1='江苏省' set ver2=ver1+'信息化技能大赛' select ver1,ver2 执行的结果如图51 所示。 打开数据库STUDY，创建一个局部变量sex，并在select 语句中使用该局部变量查找王姓男同学的学号、姓名和出生时间。 use STUDY GO,上一页,下一页,返回,项目七 在数据库系统中编程,DECLARE sex bit set sex=1 select studentid,name,birthday from tb_student where name like '王%' and sex=sex 执行的结果如图52 所示。 给局部变量赋空值。代码如下： DECLARE var char(20) select var='李阳' select var=,上一页,下一页,返回,项目七 在数据库系统中编程,(select name from tb_student where studentid='008800') select var as 'NAME' 执行的结果如图53 所示。 2. 自定义数据类型的语法实现 定义一个新的数据类型char_student，要求其是长度为10 的字符串类型，非空，代码如下： use STUDY /*打开数据库*/ GO,上一页,下一页,返回,项目七 在数据库系统中编程,EXEC sp_addtype 'char_student','char(10)','not null' /*执行存储过程sp_addtype 来定义新数据类型*/' 删除自定义数据类型char_student，代码如下： EXEC sp_droptype 'char_student' 值得注意的是，删除自定义数据类型的前提是：没有哪个表正在使用该自定义数据类型，即必须先解除数据库表中对自定义数据库类型的使用，才能删除自定义数据库类型。 3. 自定义数据类型的界面实现 采用界面方式实现自定义数据类型的定义、使用和删除等非常方便，其步骤如下：,上一页,下一页,返回,项目七 在数据库系统中编程,第一步：在SQL Server 2008 的“对象资源管理器”中展开“数据库”STUDY展开“可编程性”展开“类型”用鼠标右键单击“用户定义数据类型”单击“新建用户定义数据类型”，如图54 所示。 第二步：在弹出的窗口中输入新数据类型的名称“char_student”，选择“数据类型”为“char”，“长度”为“10”，不选“允许空值”，单击“确定”按钮，如图55 所示。,上一页,下一页,返回,项目七 在数据库系统中编程,第三步：选择表tb_student，用鼠标右键单击“设计”，在学号字段“studentid”的数据类型处，通过拉杆可以看到新数据类型“char_student”已经存在，选中它并保存表，即完成了新数据类型的使用，如图56 所示。 第四步：当解除了数据库表中对自定义数据类型的使用后，在展开的“用户定义数据类型”中用鼠标右键单击自定义类型“char_student” ，选择“删除”，即可删除自定义数据类型，如图57 所示。 4. 运算符的使用 定义两个整型变量，按位运算。代码如下：,上一页,下一页,返回,项目七 在数据库系统中编程,DECLARE var1 int,var2 int set var1=150 set var2=175 select var1&var2,var1|var2 执行的结果如图58 所示。 判断学号为001101 的学生是否存在，并输出该学生的情况。代码如下： DECLARE student char(8) set student='001101',上一页,下一页,返回,项目七 在数据库系统中编程,if(student' ') select * from tb_student where studentid=student 执行的结果如图59 所示。 查询总学分在4050 之外的所有学生的学号和姓名。代码如下： select studentid,name from tb_student where sumcredit not between 40 and 50 查询所有选课学生的姓名。代码如下：,上一页,下一页,返回,项目七 在数据库系统中编程,select distinct name from tb_student where exists(select * from tb_score) 即只要在tb_score 表中选修了任何课程，就查