1 / 52

第 8 章 T-SQL 编程基础

第 8 章 T-SQL 编程基础. 本章学习目标. 掌握用户自定义数据类型的定义方法 了解并掌握 T-SQL 语言中创建默认和规则的方法 掌握变量的类型和局部变量的定义与使用 掌握常用的系统函数、系统存储过程，会自定义函数 掌握各种流程控制语句的用法. 一、 T-SQL 基础知识. T-SQL 是 Microsoft 公司设计开发的一种结构化查询 语言（ Structure Query Language ， SQL ），它在关系 数据库管理系统（ Rational Database Management

ledell
Download Presentation

第 8 章 T-SQL 编程基础

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 第8章 T-SQL编程基础

  2. 本章学习目标 • 掌握用户自定义数据类型的定义方法 • 了解并掌握T-SQL语言中创建默认和规则的方法 • 掌握变量的类型和局部变量的定义与使用 • 掌握常用的系统函数、系统存储过程，会自定义函数 • 掌握各种流程控制语句的用法

  3. 一、T-SQL基础知识 T-SQL是Microsoft公司设计开发的一种结构化查询 语言（Structure Query Language，SQL），它在关系 数据库管理系统（Rational Database Management System，RDBMS）中实现数据的检索、操纵和添加功能。 严格意义上说T-SQL并不是一种编程语言，而是结构化 查询语言。

  4. 1、用户自定义数据类型 • 添加用户自定义数据类型 其基本语法格式如下： sp_addtype [@typename=]type, [@phystype=]system_data_type [,[@nulltype=]'null_type'] 参数说明： @typename：用户自定义的数据类型的名称。 [@phystype=]system_data_type：自定义类型所依 据的系统提供的数据类型。 [[@nulltype=]‘null_type’]：自定义数据类型处 理空值的方式

  5. 使用T-SQL语句创建用户自定义数据类型 Use CJGL Go Exec sp_addtype usernametype,'varchar(20)','not null' Exec sp_addtype userpasswordtype,'varchar(20)','not null' Exec sp_addtype userclasstype,'char(1)','not null'

  6. 创建名为users的数据表，包含三个字段，username，userpassword，usertype，各字段的类型分别为刚创建的三个类型。创建名为users的数据表，包含三个字段，username，userpassword，usertype，各字段的类型分别为刚创建的三个类型。 • Use CJGL • Go • create table users • (username usernametype, • userpassword userpasswordtype, • usertype userclasstype)

  7. 使用“对象资源管理器”创建用户自定义数据类型使用“对象资源管理器”创建用户自定义数据类型 用此方法创建自定义数据类型的主要截图如下。

  8. 删除用户自定义数据类型 删除用户自定义数据类型时要注意当该自定义类型 在使用时不能删除，必须要先停止使用，才能执行删除 的操作。 删除用户自定义数据类型的基本语法为： sp_droptype [ @typename = ] 'type‘ 如： use CJGL go exec sp_droptype usernametype

  9. 2、规则和默认 • 规则（RULE） 规则是一种约束，用于执行一些与CHECK约束相同 的功能，但要注意规则和CHECK约束的区别： （1）使用CHECK约束是限制列取值的首选方案， CHECK约束比规则更简明。 （2）一个列只能应用一个规则，但可以应用多个 CHECK约束。 （3）CHECK约束被制定为“CREATE TABLE”语句的一部 分，而规则是作为一个单独的对象来创建，并由系统存 储过程绑定到列上。

  10. 创建规则的基本语法为： CREATE RULE rule_name AS condition_expression 参数说明： rule_name：规则的名称。 condition_expression：定义规则的条件，条件 里应该使用一个变量加以判断和传递。

  11. 绑定规则使用的是系统存储过程： sp_bindrule ‘rule_name‘, ‘[db.]table_name.column_name‘ 取消绑定规则的系统存储过程为： sp_unbindrule ‘[db.]table_name.column_name’ 删除规则的基本语法为： DROP RULE rule_name

  12. 【例8.4】为例8.1创建的Users表的UserPassword列上加上一个约束规则：要求密码的长度不能少于8位。【例8.4】为例8.1创建的Users表的UserPassword列上加上一个约束规则：要求密码的长度不能少于8位。 （1）在“新建查询”窗口中输入以下代码并执行，用以创 建规则： Use CJGL Go CREATE RULE rule_Password As Len(@password)>=8

  13. （2）将此规则绑定到Users表的UserPassword列上。输（2）将此规则绑定到Users表的UserPassword列上。输 入以下代码并执行： • Use CJGL • Go • Exec sp_bindrule'rule_Password','Users.UserPassword'

  14. （3）测试规则 向Users表插入两条记录，其中一条记录中 UserPassword值的长度小于8位，一条记录的 UserPassword值大于8位。 • Use CJGL • Go • insert into users • values('ssssss','123456','a') • go • insert into users • values('admin','12345678','a') • go

  15. 删除上例中创建的rule_password • （1）取消绑定规则rule_password。 Use CJGL Go Exec sp_unbindrule 'Users.UserPassword' • （2）删除规则。 Use CJGL Go drop rule rule_password

  16. 默认（DEFAULT） 默认就是用户在创建表或向表中添加记录时，对 某些列预输入的默认值，比如在CJGL数据库中的 Score表中的某一门课程的成绩，在没有成绩时，系 统的默认值是null。

  17. 创建默认的基本语法为： CREATE DEFAULT [db.]default_name AS constant_expression 参数说明： db：数据库的名称。 default_name：创建的默认的名称。 constant_expression：默认的表达式。这里只包 含常量值的表达式，不能使用用户自定义 函数。

  18. 绑定默认的系统存储过程为： sp_bindefault'default_name','[db.]table_name.column_name' 取消绑定默认的系统存储过程为： sp_undbindefault ' [db.]table_name.column_name' 删除默认的基本语法为： DROP DEFAULT default_name

  19. 3、变量 SQL Server 2005中的变量有两种类型： • 用户自定义的变量（局部变量） • 系统提供的变量（全局变量）

  20. 全局变量 全局变量是SQL Server 2005系统内部使用的变 量，其作用范围是全局的，不局限于某一程序，用户 不能定义全局变量。全局变量以@@前缀开头，使用 时，不必进行声明。

  21. SQL Server 2005中的全局变量被当成了系统 函数进行维护，其存放的位置和系统函数在一起。

  22. 下面通过一个例子看如何使用全局变量 【例8.6】要对Student表中的StuNo为2009001的学 生的StuNo改为20090011111，用全局变量@@ERROR检查 某列的取值是否超过范围。 题目分析： 当字段长度超过规定长度，系统的错误代码为8152； 当取值违反了约束，系统的错误代码547；……。全局 变量@@ERROR的返回值是一个integer类型。

  23. 在“新建查询”窗口中输入以下代码： • use CJGL • go • update Student • set StuNo='20090011111' • where StuNo='2009001' • if @@ERROR =8152 • print '新值的长度超过了定义类型的取值范围'

  24. 局部变量 局部变量是一个有特定数据类型的对象，它的作 用范围仅限于程序内部。局部变量前应加上@符号， 必须要先定义才能引用，定义局本部变量使用的关键 字是DECLARE。 定义局部变量的基本语法如下： DECLARE @variable_name datatype[,@variable_name datatype…]

  25. 在T-SQL语句中，对局部变量赋值时，不能和其他在T-SQL语句中，对局部变量赋值时，不能和其他 程序设计语言一样直接对其赋值，必须使用SET或 SELECT命令来设置变量的值，其基本语法如下： SET @variable_name=value 或 SELECT @variable_name=value

  26. 【例】对两个变量赋值，并且求它们相加的和。【例】对两个变量赋值，并且求它们相加的和。 在“新建查询”窗口中输入以下代码，并执行： declare @a int,@b int, @sum int set @a=2 --这里也可以使用select赋值 set @b=5 set @sum=@a+@b print @sum

  27. 二、函数 Server 2005中的函数分为两种类型：一种是系 统提供的内部函数（系统函数）；另一种是用户自 定义函数。

  28. 1、常用系统函数 利用系统函数，可以帮助用户获取系统的相关信 息、执行相关计算、实现数据转换，以及统计功能等， SQL Server 2005提供的系统函数包括聚合函数、配置 函数、日期函数、数学函数、字符串函数等，如下图所 示。

  29. SQL Server 2005中的系统函数

  30. 聚合函数

  31. 日期和时间函数 分析输出结果： print Datediff(dd,Getdate(),'01/01/2012')

  32. 数学函数

  33. 字符串函数

  34. 【例8.11】向Student表中插入一条记录，检查当StuNo【例8.11】向Student表中插入一条记录，检查当StuNo 的长度大于8时，提示不允许插入的信息，否则允许插 入，并提示插入成功的信息。 题目分析： 本例中使用了两个系统函数，一个是len()，一个是 convert()。len()函数计算字符长度；而convert()函 数则是将数据类型进行转换，因为这里的len()函数返 回的是一个整型数据，而单引号里的是字符串类型，不 能进行连接（+）运算，所以需要将整型数据转换成字 符型数据，再进行连接运算。如下：

  35. use CJGL go declare @ls nvarchar(20) set @ls='0123' if len(@ls)<=8 begin insert into Student values(@ls, '李欢','男','网游1班','成都','班长','1988-3-5') print '此记录插入成功' end else print 'StunNo的长度为'+convert(nvarchar,len(@ls))+'位,超过了8位'

  36. 2、自定义函数 在SQL Server 2005中，用户自定义函数有两种， 分别是标量函数和表值函数。

  37. 标量函数 创建标量函数的基本语法为： CREATE FUNCTION [owner_name.] function_name ([{@parameter_name[AS]parameter_data_type[=default]}][,…n]) RERURNS return_data_type [WITH<function_option>[,…n]] [AS] BEGIN function_body RETURN scalar_expression END

  38. 参数说明： function_name：用户自定义的函数名称 @parameter_name：用户自定义函数的参数，可以声 明一个或多个参数 parameter_data_type：参数的类型 [=default]：参数的默认值 return_data_type：用户自定义标量函数的返回值， 不能是ext、ntext、image、cuisor和 timestamp据类型 function_body：指定一系列定义函数值的T-SQL语 句，这些语句一起使用的计算结果为标量值 scalar_expression：指定标量函数返回的标量值

  39. 【例8.12】求某一科目的平均成绩。 题目分析： 求平均成绩，系统提供了平均值函数Avg()，但这里的科目名称未定，Where子句里应该是一个待定条件。这里就应该建立一个用户自定义标量函数方便用户求解。

  40. （1）创建自定义标量函数 • use CJGL • go • create function avggrade(@kechengming nvarchar(20)) • returns float • as • begin • declare @pingjunfen float • set @pingjunfen=(select avg(grade) • from score • where couno=(select couno • from course • where couname=@kechengming)) • return @pingjunfen • end

  41. （2）调用此函数 use cjgl go select dbo.avgGrade('高等数学') as '高等数学‘ (3) 删除用户自定义函数 use cjgl go Drop function avggrade

  42. 3、常用系统存储过程 系统存储过程是一种特殊类型的系统函数，系统存 储过程主要存储在master数据库中，并以sp_为前缀， 在使用时用EXEC调用。 如： EXEC sp_helptext avgGrade

  43. 三、批处理和流程控制语句 1、语句注释 注释语句为分： • 单行注释语句 “--” • 多行注释语句/*...*/

  44. 2、批处理 批处理是包含一个或多个T-SQL语句的组，从应用 程序一次性地发送到SQL Server中执行。SQL Server将 批处理语句编译成一个可执行的单元，此单元称为执行 计划，执行计划中的语句每次执行一条。

  45. 一些SQL语句不可以放在一个批处理语句中处理，一些SQL语句不可以放在一个批处理语句中处理， 它们需遵守以下规则：大多数CREATE命令要在单个命令 中执行，但除CREATE DATABASE、CREATE TABLE、 CREATE INDEX例外。 T-SQL语句中的批处理其实很简单，就是用GO命令 来通知SQL Server和T-SQL语句的结束。

  46. 【例8.14】在同一个查询窗口中查找Student表中所有【例8.14】在同一个查询窗口中查找Student表中所有 姓王的学生信息和在Score表中查找所有成绩大于60分 的信息，并将查询结果存放在名为VV的视图中。 使用的命令如下： • use cjgl • go • create view vv • as • select * from student where stuname like '王%' • go • select * from score where grade>='60' • go

  47. 本例说明： 原本create语句应该是批处理中的第一条语句，但 这里为了指明在CJGL数据库下创建视图，在前面先使用 了use CJGL，所以应该用go语句将其分开。如果将use CJGL和语句create view vv之间的go语句去掉，会有什 么结果呢？

  48. 3、流程控制语句 • IF…ELSE语句 IF…ELSE语句的基本格式为： IF<条件表达式> <命令或程序块> [ELSE[条件表达式] <命令行或程序块>]

  49. BEGIN…END语句 BEGIN…END语句的基本格式为： BEGIN <命令行或程序块> END

  50. CASE语句 CASE语句的基本格式为： CASE <表达式或字段名> WHEN<逻辑表达式1> THEN<结果表达式1> … WHEN< 逻辑表达式n> THEN<结果表达式n> [ELSE <结果表达式>] END 例8.15

More Related