TransactSQL语言.ppt

《TransactSQL语言.ppt》由会员分享，可在线阅读，更多相关《TransactSQL语言.ppt（64页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第3章 Transact-SQL 语言3.1 SQL Server 编程工具3.2 SQL语言简介3.3 数据类型3.4 局部变量与全局变量3.5 运算符与优先级3.6 函数3.7 简单的SELECT 语句3.8 批处理与流程控制语句3.1 SQL Server 编程工具v查询分析器vOsql实用程序3.1 SQL Server 编程工具v启动查询分析器在启动查询分析器之前，用户必须先登录到服务器。两种方法：v开始程序Microsoft SQL Server“查询分析器”v企业管理器工具菜单“查询分析器”身份验证方法vWindows Nt方式vSQL Server 3.1 SQL Server

2、 编程工具v配置和使用查询分析器“工具”“选项” P39图33v使用3.2 SQL 语言简介vSQL语言是一种在关系数据库中定义、查询、操纵和控制数据的标准语言。vSQL92标准vTransactSQL3.3 数据类型SQL-Server 定义了定义了25种数据类型种数据类型二进制数据类型二进制数据类型数字数据类型数字数据类型字符数据类型字符数据类型unicodeunicode数据类型数据类型日期和时间数据类型日期和时间数据类型货币数据类型货币数据类型特殊数据类型：特殊数据类型：bit、timetamp、uniqueidentifer1.1.用户自定义数据类型用户自定义数据类型二进制数据类型v

3、binary固定长度的二进制数据，其最大长度为 8,000 个字节。vvarbinary可变长度的二进制数据，其最大长度为 8,000 个字节。vimage可变长度的二进制数据，其最大长度为 231 - 1 (2,147,483,647) 个字节。2. 数字数据数字数据1）精确整数vbigint从 -263 (-9223372036854775808) 到 263-1 (9223372036854775807) 的整型数据（所有数字）。vint从 -231 (-2,147,483,648) 到 231 - 1 (2,147,483,647) 的整型数据（所有数字）。vsmallint从 -21

4、5 (-32,768) 到 215 - 1 (32,767) 的整数数据。vtinyint从 0 到 255 的整数数据。2）精确小数vdecimal 从 -1038 +1 到 1038 1 的固定精度和小数位的数字数据。vnumeric 功能上等同于 decimal。3）近似小数vFloat 从 -1.79E + 308 到 1.79E + 308 的浮点精度数字。vreal从 -3.40E + 38 到 3.40E + 38 的浮点精度数字。3.字符串字符串vchar固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符。vvarchar可变长度的非 Unicode 数据，

5、最长为 8,000 个字符。vtext可变长度的非 Unicode 数据，最大长度为 231 - 1 (2,147,483,647) 个字符。4. Unicode 字符串Unicode 含义：含义： 统一的字符编码标准，采用双字节对字符进行编码。统一的字符编码标准，采用双字节对字符进行编码。vnchar固定长度的 Unicode 数据，最大长度为 4,000 个字符。 vnvarchar可变长度 Unicode 数据，其最大长度为 4,000 字符。sysname 是系统提供用户定义的数据类型，在功能上等同于 nvarchar(128)，用于引用数据库对象名。vntext可变长度 Unicod

6、e 数据，其最大长度为 230 - 15.日期和时间数据类型vdatetimev从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）。vsmalldatetimev从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟。设置时间格式的命令vSET DATEFORMAT format|format_var有效参数包括 mdy、dmy、ymd、ydm、myd 和 dym。美国英语默认值是 mdy。例： SET DATEFORMAT mdy GO DECLARE datevar dateti

7、me SET datevar = 12/31/98 SELECT datevar/*GO是结束符*/6.货币类型vmoneyv货币数据值介于 -263 (-922,337,203,685,477.5808) 与 263 - 1 (+922,337,203,685,477.5807) 之间，精确到货币单位的千分之十。vsmallmoneyv货币数据值介于 -214,748.3648 与 +214,748.3647 之间，精确到货币单位的千分之十。7.特殊数据类型vbit 1 或 0 的整数数据。vTimetampvuniqueidentifier参见联机丛书8.用户自定义数据类型v创键：sp_a

8、ddtypev查看: sp_helpv重命名:sp_renamev删除: sp_droptypev举例3.4 局部变量与全局变量v局部变量一般在一个批处理中被声明、定义、赋值和引用，批处理结束后局部变量消失。利用局部变量还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。1.局部变量1)定义: 必须先用DECLARE命令定义后才可以使用。 DECLAER local_variable data_type , local_variable data_type 2)局部变量的赋值方法: SET local_variable = expression ,local_variable

9、= expression 或者 SELECT local_variable = expression , local_variable = expression2.全局变量全局变量在整个SQL Server系统内使用,存储的通常是一些SQL Server的配置设定值和统计数据。在使用全局变量时应该注意以下几点：v全局变量不是由用户的程序定义的，全局变量是在服务器全局变量不是由用户的程序定义的，全局变量是在服务器级定义的。级定义的。v用户只能使用预先定义的全局变量。用户只能使用预先定义的全局变量。v引用全局变量时，必须以标记符引用全局变量时，必须以标记符“”开头。开头。v全局变量对用户来说是只读

10、的。全局变量对用户来说是只读的。v局部变量的名称不能与全局变量的名称相同，否则会在应局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。用程序中出现不可预测的结果。 3.局部变量和全局变量的显示vSELECT local_variable_namevSELECT variable_name3.5 运算符在SQL Server 2000中，运算符主要有以下几 类：v一元运算：+（正）、-（负）、（按位取反）v赋值运算符：=v算术运算符：+、-、*、/、%（模运算）v字符串连接运算符：+v比较运算符：=、=、=、（）v逻辑运算符：and、or、not优先级3.6 函数字符

11、串函数日期和时间函数数学函数转换函数系统函数集合函数其它函数 1.字符串函数v字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。此外，某些字符串函数还能够处理text、ntext、image数据类型的数据。 字符串函数分类：v基本字符串函数：UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。v字符串查找函数：CHARINDEX、PATINDEX。v长度和分

12、析函数：DATALENGTH、SUBSTRING、RIGHT。v转换函数：ASCH、CHAR、STR、SOUNDEX、DIFFERENCE。例1SELECT upper(Microsoft) SELECT Microsoft+ltrim( SQL SERVER)SELECT Rtrim(SQL SERVER )+2000SELECT substring( MICROSOFT SQL SEVER 2000,11,10)SELECT reverse (MICROSOFT)2日期和时间函数日期和时间函数v日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间

13、值。v在SQL Server 2000中，日期和时间函数的类型如P55表3-7所示.函函 数数参参 数数DATEADD( datepart , number, date )DATEDIFF( datepart , date1 , date2 )DATENAME( datepart , date )DATEPART( datepart , date )DAY( date )GETDATE（）MONTH( date )YEAR( date )例2:从从GETDATE函数返回的日期中提取月份数。函数返回的日期中提取月份数。SELECT DATEPART(month,GETDATE() AS Mont

14、h Number运行结果为： Month Number - 9 例例4:从日期从日期 03/12/2003 中返回月份数、天数和年份数。中返回月份数、天数和年份数。vSELECT MONTH(03/12/2003), DAY(03/12/2003),YEAR(03/12/2003)v运行结果为：运行结果为： - - - 3 12 20033.数学函数v数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行处理。 例5:

15、在同一表达式中使用在同一表达式中使用CEILING（）、（）、FLOOR（）、（）、ROUND（）函数。（）函数。select ceiling(13.4), floor(13.4), select ceiling(13.4), floor(13.4), round(13.4567,3)round(13.4567,3)运行结果为：- - -14 13 13.4570v使用联机丛书学习函数的使用。4.转换函数v数据类型转换有两种：隐性转换对于用户是不可见的。 SQL Server 自动将数据从一种数据类型转换成另一种数据类型。例如，如果一个 smallint 变量和一个 int 变量相比较，这个

16、smallint 变量在比较前即被隐性转换成 int 变量。显式转换使用 CAST 或 CONVERT 函数。 CONVERT和和CAST函数函数CAST ( expression AS data_type )vCONVERT函数允许用户把表达式从一种数据类型转换成另一种数据类型，还允许把日期转换成不同的样式。其语法形式为：CONVERT (data_type(length),expression ,style) 例：检索pubs表中的书名（这些图书的截止当前销售额的第一位数字为 3），并将这些图书的 ytd_sales 转换为 char(20)。v用 CAST 实现： USE pubs GO

17、 SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles WHERE CAST(ytd_sales AS char(20) LIKE 3% GO v用CONVERT 实现：USE pubs GO SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales FROM titles WHERE CONVERT(char(20), ytd_sales) LIKE 3% GO 例：检索pubs库中的书名(这些图书的截止当前销售额的第一位数字为 3），并将这些图书的 ytd_sales 转换为

18、 char(20)。5.系统函数v系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。 例： 返回 Northwind 数据库的 Employees 表中的首列的名称。USE NorthwindgoSELECT COL_NAME(OBJECT_ID(Employees), 1)运行结果为：EmployeeID6.集合函数集合函数v集合函数可以返回整个或者几个列或者一个列的汇总数据，它常用来计算SELE

19、CT语句查询的统计值。v集合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。例：求例：求PUBS库中库中TITLES表中所有书的平均价格、表中所有书的平均价格、最高价、最低价。最高价、最低价。USE PUBSGOSELECT avg(price) from titlesSELECT max(price) from titlesSELECT min(price) from titles8.用户自定义函数 v创建用户自定义函数：CREATE FUNCTION 函数名称函数名称 （形式参数名称（形式参数名称 AS 数据类型）数据类型）RETURNS 返回数据类型返回数据类型ASBE

20、GIN 函数内容函数内容 RETURN 表达式表达式END v调用用户自定义函数的基本语法为： 变量=用户名.函数名称（实际参数列表）注意：在调用返回数值的用户自定义函数时，一定要在函数名称的前面加上用户名 3.7 简单的SELECT 语句v从数据库中检索行，并允许从一个或多个表中选择一个或多个行或列。SELECT FROM WHERE SELECT语句的语法形式如下：SELECT select_list INTO new_table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING sea

21、rch_condition ORDER BY order_expression ASC | DESC COMPUTE clauseFOR BROWSE几种常用语句的用法vSELECT子句用于指定所选择的要查询的特定表中的列，它可以是星号（*）、表达式、列表、变量等。vFROM子句用于指定要查询的表或者视图，最多可以指定16个表或者视图，用逗号相互隔开。vWHERE子句用来限定查询的范围和条件。vINTO子句用于指定所要生成的新表的名称。vGROUP BY子句是分组查询子句。vHAVING子句用于指定分组子句的条件。几种常用语句的用法vGROUP BY子句、HAVING子句和集合函数一起可以实现

22、对每个组生成一行和一个汇总值。vORDER BY子句可以根据一个列或者多个列来排序查询结果，在该子句中，既可以使用列名，也可以使用相对列号。vASC表示升序排列，DESC表示降序排列。vCOMPUTE子句使用集合函数在查询的结果集中生成汇总行。vCOMPUTE BY子句用于增加各列汇总行。3.8 批处理与流程控制语句v批和脚本v流程控制语句1.批v批批是一条或多条是一条或多条Transact-SQL语句组，这些语句组，这些语句一起提交并作为一个组来执行。语句一起提交并作为一个组来执行。v批地结束符是批地结束符是GO。vSQL Server服务器将批处理编译成一个可执服务器将批处理编译成一个可执

23、行单元，称为执行计划。行单元，称为执行计划。使用批时的限制:v下述语句不能放在一个批中：CREATE PROCEDURECREATE RULECREATE DEFAULTCREATE TRIGGERCREATE VIEWv规则和缺省不能在同一个批中即被绑定到列有被使用。vCheck约束不能在同一个批中即被绑定到列有被使用。v在同一个批中不能删除对象又从建它。v用set语句改变的选项在批结束时生效。v在一个批中不能改变一个表由立即引用其新列。脚本v是一系列顺序提交的批。流程控制语句v流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQL Server 2000中，流程控制语句主要用来控制

24、SQL语句、语句块或者存储过程的执行流程。 1. Return 语句vRETURN语句用于无条件地终止一个查询、存储过程或者批处理，此时位于RETURN语句之后的程序将不会被执行。RETURN语句的语法形式为：v语句的语法形式为：RETURN integer_expression v其中，参数integer_expression为返回的整型值。存储过程可以给调用过程或应用程序返回整型值。2.print 语句和raiserrorvPrint 语句vraiserror2. IFELSE语句语句vIFELSE语句是条件判断语句，其中，ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。vIF

25、ELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。vSQL Server允许嵌套使用IFELSE语句，而且嵌套层数没有限制。1BEGINEND语句语句vBEGINEND语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGINEND语句，其语法形式为：vBEGIN sql_statement ENDIFELSE语句的语法形式语句的语法形式IF Boolean_expression sql_statement ELSE sql_statement

26、 3.CASE表达式vCASE语句根据表达式的不同，取可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE表达式语法形式：vCASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_expression END 联机丛书示例示例vA. 使用带有简单使用带有简单 CASE 函数的函数的 SELECT 语句语句在 SELECT 语句中，简单 CASE 函数仅检查是否相等，而不进行其它比较。下面的示例使用 CASE 函数更改图书分类显示，以使其更易于理解。v联机丛书vB.

27、使用带有简单使用带有简单 CASE 函数和函数和 CASE 搜索搜索函数的函数的 SELECT 语句语句v在 SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行替换。v下面的示例根据图书的价格范围将价格（money 列）显示为文本注释。USE pubs USE pubs GO GO SELECT Price Category = SELECT Price Category = CASE CASE WHEN price IS NULL THEN Not yet priced WHEN price 10 THEN WHEN price IS NULL THEN Not yet

28、 priced WHEN price = 10 and price = 10 and price 20 THEN Coffee Table Title ELSE Expensive book! ELSE Expensive book! END, CAST(title AS varchar(20) AS Shortened TitleEND, CAST(title AS varchar(20) AS Shortened Title FROM titles FROM titles ORDER BY price ORDER BY price GO GO 4.WHILE 结构v语法形式语法形式 WHILE Boolean_expression sql_statement BREAK sql_statement CONTINUE vCONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。vBREAK语句则使程序完全跳出循环，结束WHILE语句的执行。注释：v注释是程序代码中不执行的文本字符串（也称为注解）。v在SQL Server中，可以使用两种类型的注释字符：一种是ANSI标准的注释符“-”，它用于单行注释；另一种是与C语言相同的程序注释符号，即“/* */”。