SQL第3章Transact-SQL语言.ppt

第3章 Transact-SQL程序设计,3.1 SQL 语言简介 3.2 数据类型 3.3 局部变量与全局变量 3.4 运算符及优先级 3.5 函数 3.6 简单的SELECT 语句 3.7 批处理与流程控制语句,返回目录,2,3.1 SQL 语言简介,SQL(Structure Query Language)语言是一种在关系型数据库中定义、查询、操纵和控制数据的标准语句。大多数据库厂商都在SQL-92标准的基础上对SQL又进行了一定的扩充，产生了事务化的SQL语言，即Transact SQL语言，简称T-SQL语言。,3,语言简介,SQL 语言是一种面向集合的数据库语言，其主要特点是：类似于英语，直观、简单易学；SQL语言只是提出要“干什么”，“怎么办”则由DBMS来完成。SQL语句分为四类：数据查询语句、数据操纵语句、数据定义语句和数据控制语句；SQL语句既可以独立，也可以嵌入到另外一种语句中使用的语句，即具有自含型和缩主型两种特征。自含型特征可以用于所有用户，缩主型适合于应用程序员开发数据库应用程序。,4,语言简介,数据操作语句select、insert、delete、update数据定义语句create table、drop table、alter table create view、drop view create index、drop index create procedure、alter procedure、drop procedurecreate trigger、alter trigger、drop trigger 数据访问控制grant、revoke 事务控制语句begin transaction/commit、rollback、set transaction 编程语句declare、open、fetch、close、execute,5,数据库管理员和应用系统开发人员用SQL语言可以做以下事情：创建主键、外键、约束、规则、触发器、事务，用以实现数据完整性；查询、更新、删除数据库中的信息；对各种数据库对象设置不同的权限、实现数据库的安全性；进行分布式数据处理，实现数据库间的数据复制、传递和执行分布式查询；可以创建批处理、存储过程、视图，方便应用程序访问数据库中的数据；实现数据仓库，从联机事务处理系统中提取数据，对数据汇总进行决策支持分析；其他功能。,本章首页,6,3.2 数据类型,数据类型是指数据所代表的信息的类型。每一种语言都定义了自己的数据类型。SQL Server 2000中定义了25种数据类型，同时允许用户自己定义数据类型。系统定义的数据类型有：binary(n)、varbinary(n)、char(n)、varchar(n)、nchar(n)、nvarchar(n)、datetime、smalldatetime、decimalp(,s)、numericp(,s)、float(n)、real、int、smallint、tinyint、money、smallymoney、bit、cursor、sysname、timestamp、uniqueidentifier、text、image 和ntext;,7,3.2.1 二进制数据类型二进制数据类型包括三种：binary、varbinary和image。binary(n)：固定长度的n个字节二进制数据。n必须是1到8000。存储空间大小为n+4字节。varbinary(n)：n个字节可变长二进制数据。n必须从1到8000。存储空间大小为实际数据长度+4个字节，而不是n个字节。image：可变长度的二进制，其最大长度为231-1个字节。3.2.2 数字数据类型数字数据类型包括精确数字、近似数字两种。精确数字又分为精确整数和精确小数。1.精确整数精确整数数据类型有四种，它们是bigint、int、smallint、tinyint。bigint：从-263到263-1的整型数据。存储大小为8个字节。int：从-231到231-1的整型数据。存储大小为4个字节。,8,smallint：从-215到215-1的整数数据。存储大小为2个字节。tinyint：从0到255的整数数据。存储大小为1个字节。bigint 的存储范围大大于int 的存储范围，int 的存储范围大于Smallint 的存储范围，Smallint的存储范围大于tinyint的存储范围。2.精确小数精确小数数据类型有两种：它们是decimal和numeric。decimal：带定点精度和小数位数的numeric数据类型，从-1038+1到1038-1。numeric：功能上等同decimal。3.近似数字近似小数数据类型有两种：float 和 real。float：从-1.79E+308到1.79E+308的浮点精度数字。real：从-3.04E+38到3.04E+38的浮点精度数字。,9,3.2.3 字符数据类型字符数据是用一对单引号括起来由字母、数字、和符号组合而成的数据,在内存中占用一个字节。例Nanjing、3.23、&*op都是合法的字符数据。字符数据类型有char、varchar和text三种。char：固定长度的非unicode字符数据，最大长度为8000个字符；varchar：可变长度的非unicode 字符数据，最大长度为8000个字符；text：可变长度的非unicode 字符数据，最大长度为231-1个字符；如果没有在数据定义或变量声明语句中指定n，则默认长度为1。,10,3.2.4 unicode 数据类型对于用单个字节编码每个字符的数据类型，存在的问题就是此数据类型只能表示256个不同的字符。象汉字、日文或韩国文字这样具有数千个字符的字母表就不可能处理了。unicode标准每个字符用两个字节来编码，所以在系统中使用unicode数据类型，就可以解决大字母表的字符问题了。unicode数据类型包括nchar、nvarchar和ntext。nchar：固定长度的unicode 数据，最大长度为4000个字符。nvarchar：可变长度的unicode数据，其最大长度为4000字符。ntext：可娈长度的unicoce数据，其最大长度为230-1个字符。当列的长度固定不变时，使用nchar数据类型；当列的长度变化时，使用nvarchar数据类型；如果需要存储的字符数多于4000个时，应使用ntext数据类型存储。,11,3.2.5 日期和时间数据类型日期和时间数据类型包括datetime 和smalldatetime两种数据类型。datetime：存储从1753年1月1日到9999年12月31日的日期和时间数据，每一个值要求8个字节，精确到百分之三秒（或3.33毫秒）。smalldatime：存储从1900年1月1日到2079年12月31日的日期和时间数据，每一个值要求4个字节，精确到分钟。设置日期格式的命令如下：set dateformat format|format_var其中,format|format 是日期的格式。有效参数包括MDY、DMY、YMD、YDM、MYD、DYM。默认格式为MDY。例3-1 设置日期格式set dateformat mdygodeclare datevar datetimeset datevar=06/03/2006select datevar,12,3.2.6货币数据类型货币数据类型表示货币数量，货币数据类型包括money和smallmoney。money：货币数值介于-263与263-1之间，精确到货币单位的千分之十，money数据类型要求个存储字节。smallmoney：货币数值介于-214748.3648与+214748.3647之间，精确到货币单位的千分之十，smallmoney数据类型要求4个存储字节。3.2.7特殊数据类型在SQL中特殊的数据类型有三种：bit、timestamp和uniqueidentifier。bit：由1和0组成，使用bit数据类型可以表示真、假或on、off，不能对bit的列使用索引。timestamp：以二进制格式表示SQL Server活动的先后顺序。在数据库范围内，timestamp是唯一的，每次更新行时进行更新。uniqueidentifer：全局唯一标识符（GUID），以十六进制数字表示一个全局惟一的标识。,13,3.2.8 用户自定义数据类型在SQL Server中,用户可以根据需要自定义数据类型。用户可以用SQL语言或企业管理器来自定义数据类型。自定义数据类型定义后，与系统数据类型一样使用。创建自定义数据类型的好处：利用自定义数据类型可使不同表不重复出现的各列数据具有相同的特性。1.创建用户自定义数据类型()使用SQL 语句创建使用系统存储过程sp_addtype创建，语法如下：sp_addtype type_name,phystype(length)|(precision,scale),null|not null|identity,14,其中：type_name：是用户定义的数据类型的名字。Phystype：是用户自定义数据类型所基于的系统数据类型，可以包括长度、精度、标度。null|not null|identity：用户自定义数据类型的性质，分别为允许空值、不允许为空值、具有标识列性质。如果不指定列的性质，默认为not null。,15,例3-2 创建两个自定义数据类型use jwglgoexec sp_addtype a0,varchar(15),nullexec sp_addtype postalcode,char(6),not null(2)使用企业管理器创建自定义数据类型进入企业管理器进入JWGL数据库右击“用户自定义数据类型”点击“新建用户自定义数据类型”后出现“用户自定义数据类型”窗口输入要定义的数据名称，选择数据类型，输入数据长度，确定是否允许为空点击“确定”。,16,2.查看用户自定义数据类型可以用系统存储过程sp_help或企业管理器来查看用户定义的数据类型的特征。语法格式：exec sp_help 自定义数据类型名称3.重命名用户自定义数据类型使用过程sp_rename 可以很方便地重新命名一个用户自定义数据类型。语法格式：exec sp_rename 旧名称,新名称4.删除用户定义数据类型使用过程sp_droptype 来删除一个已经定义且未被使用的用户自定义数据类型。语法格式：exec sp_droptype 自定义数据类型名必须注意不能删除正在被表或其他数据库使用的用户自定义数据类型。,本章首页,17,3.3 局部变量与全局变量,从变量的作用范围来区分，SQL Server提供两种形式的变量：局部变量和全局变量。1.局部变量局部变量一般在批处理中被声明、定义、赋值和引用，批处理结束后，局部变量就消失了。利用局部变量还可以保存程序执行过程中的中间数据值，保存由存储过程返回的数据值等。(1)局部变量的定义 必须先用DECLARE命令定义后才可以使用。DECLAER local_variable data_type,n(2)局部变量的赋值方法 SET local_variable=expression 或者 SELECT local_variable=expression,.n,18,expression：与局部定义的数据类型相匹配的表达式。赋值前变量默认值为 NULL。(3)局部变量的显示 select variable_name例3-3 定义一个整型局部变量和一个可变长字符型局部变量并赋值。DECLARE local_variable_int int,local_variable_char nvarchar(9)SELECT local_variable_int=40SET local_variable_char=Welcom to NanjingSELECT local_variable_intSELECT local_variable_charGO,19,注意：1)如果被赋值的长度超过DECLAER语句中声明的长度，将此值赋值给局部变量时，超过的部分将被截去。如上例，局部变量 local_variable_char定义的长度为9，而给其赋的值是“Welcom to Nanjing”，最后局部变量 local_variable_char的值为“Welcom to”。2)也可以用SELECT语句从表中查询出的结果给局部变量赋值，但要保证查询出的值的惟一性，否则不能保证给局部变量赋值的准确性。3)在定义局部变量时容易发生数据类型不匹配的错误。即：使用DECLARE语句定义的局部变量与赋给局部变量的值的数据类型不匹配。如果发生这种情况，SQL Server总是试图隐式地将被赋的值的数据类型转化为局部变量的数据类型。,20,2.全局变量全局变量在整个SQL Server系统内使用。存储的通常是一些SQL Server的配置设定值和统计数据。在使用全局变量时应该注意以下几点：全局变量是在服务器级定义的。SQL Server提供的全局变量分为两类：1)与SQL Server 连接有关的全局变量。如：rowcount表示受最近一个语句影响的行数。2)关于系统内部信息有关的全局变量。如：version表示SQL Server的版本号。全局变量反映的是服务器当前的状态，SQL Server 2000提供了30多个全局变量，下面介绍几个常用的全局变量：connections：返回自上次启动SQL Server 以来连接或试图连接的次数。rowcount：返回上一条T-SQL语句影响到的数据行数。,21,error：返回上一条T-SQL语句执行后的错误号。procid：返回当前存储过程的ID标识。remserver：返回登录记录中远程服务器的名字。spid：返回当前服务器进程的ID标识。version：返回当前SQLServer服务器的版本。3.局部变量与全局变量的显示使用SELECT语句可以显示局部变量和全局变量。语句形式为：显示局部变量：SELECT variable_name 显示全局变量：SELECT variable_name,本章首页,22,3.4 运算符,3.4.1 算术运算符 算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%）（select 10%4,4%10）3.4.2 赋值运算符Transact-SQL中只有一个赋值运算符，即等号（=）3.4.3 位运算符位运算符包括按位与（&）、按位或（|）、按位异或（）和求反（）。位运算符用来对整型数据或者二进制数据（image数据类型除外）之间执行位操作。要求在位运算符左右两侧的操作数不能同时是二进制数据。例 select 2,2,23,2|4,2&4(由同学自己去思考),23,3.4 运算符,3.4.4 比较运算符 比较运算符包括：等于（=）、大于（）、大于或等于（=）、小于（或!=）、不小于（!）。3.4.5 逻辑运算符逻辑运算符包括与(AND)、或(OR)和非(NOT)等运算符。关系运算和逻辑运算返回布尔值，值为TRUE或FALSE。这种值不能参于运算及输出.例 select 53(是错误的,由同学去完成)3.4.6 字符串连接运算符（+）字符串运算符加号（+）将两个以上的字符串(不作任何处理、直接)相联。例如 北京2008+奥运会 连接后结果为 北京2008 奥运会,24,3.4.7 运算符的优先级（）（括号）（按位取反）正、负、乘、除、取模：*、/、%（取模）加、连接符、减比较符：=,=,NOT（非）AND（与）OR（或）=（赋值）,本章首页,3.4 运算符,25,3.5 函数,输出函数值要用select 命令3.5.1 数学函数 P523.5.2 字符串函数 P53 转换函数 P55 CAST（expression AS data_type）CONVERT（data_type（length）,expression,style）3.5.4 日期和时间函数 P563.5.5 系统函数 P583.5.6 集合函数 P58 count()、avg()、max()、min(),本章首页,26,3.6 简单的SELECT 语句,select语句是数据SQL中最重要的语句，其功能强大，使用灵活。SELECT语句有丰富的子句，其最简单的用法是:select from where 列名表可以用 星号(*)、表达式、列表、变量等，其中*表示表中所有的列项。例1 select au_lname,au_fname,phone,city,state from authors例2 select au_lname,au_fname,phone,city,state from authors where state=CA,本章首页,27,3.7 批处理和 流程控制语句,通常，服务器端的程序使用SQL语句来编写，一般而言，一个服务器端的程序是由以下一些成分组成：批；注释；程序中使用的变量；改变批中语句的执行顺序的控制语句；错误和消息的处理。3.7.1 批和脚本1.批（batch）两个GO之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQL Server服务器进行执行。SQL Server服务器将批处理编译成一个可执行单元，称为执行计划。这样处理可以节省系统开销。,28,使用批时有如下限制：1)并不是所有语句都可以和其他语句在一个组合成批。下列语句不能组合在同一个批中：create procedure create rule create defaul create trigger create view 2）规则和缺省不能在同一个批中既绑定到列又被使用。3）CHECK约束不能在同一个批中既定义又使用。4）在同一个批中不能删除对象又重新它。5）用SET语句改变的选项在批结束时生效。6）在同一个批中不能改变一个表再立即引用其新列。另外当一外含有多个批的SQL脚本提交执行时发生错误，SQL服务器显示出的错误行提示是错误语句所在批中的行号，而不是该语句在整个SQL脚本中的行号。2.脚本脚本是一系列顺序提交的批。,29,3.7.2 流程控制语句流程控制语言是Transact-SQL对ANSI-92 SQL标准的扩充。它可以控制SQL语句执行的顺序，在存储过程、触发器和批中很有用。具体包括：IF.ELSE 条件执行命令；CASE语句 多条件分支选择语句；GOTO语句 无条件转移语句；BEGIN.END 将一组SQL语句作为一个语句块；WHILE 循环执行相同的语句；RETURN 无条件返回；PRINT 在屏幕上显示信息；RAISERROR 将错误信息显示在屏幕上，同时也可以记录在NT日志中；WAITFOR 等待语句。,3.7.2 流程控制语句,30,1.RETURNRETURN的作用是无条件退出所在的批、存储过程和触发器。退出时，可以返回状态信息。在RETURN语句后面的任何语句不被执行。RETURN语句的语法形式：RETURN integer_expression其中，integer_expression 是一个表示过程返回的状态值。系统保留0为成功。小于0为有错误。,31,2.RAISERROR和PRINT PRINT语句PRINT语句的作用是在屏幕上显示用户信息。PRINT语句的语法形式如下：PRINT string|local_variable|local_variable 其中，string代表一个不超过255字节的字符串。local_variable 代表一个局部变量，该局部变量必须是CHAR或VARCHAR类型。local_variable代表能被转化为char或varchar 类型的全局变量。【例3-14】将 GETDATE 函数的结果转换为 varchar 数据类型，并将其与 PRINT 要返回的文本“本信息打印的时间是”。PRINT 本信息打印的时间是+RTRIM(CONVERT(varchar(30),GETDATE()+.,32,RAISERROR语句RAISERROR语句的作用是将错误信息显示在屏幕上，同时也可以记录在NT日志中。RAISERROR 语句的语法形式：RAISERROR error_number msg_id|msg_str,SEVERITY,STATE,argument1,.n其中：error_number：是指错误号。msg_id|msg_str：是指错误信息。SEVERITY：是指错误的严重级别。STATE：是指发生错误时的状态信息。,33,3.CASE 表达式在Microsoft SQL Server 2005中CASE语句有三种形式：简单型CASE语句、搜索型CASE语句、CASE关系函数。本书只介绍简单型CASE语句和搜索型CASE语句。（1）简单型CASE语句简单型CASE语句的语法形式：CASE expressionWHEN expression THEN result,.nELSE result END其中：expression：可以是常量、列名、函数、算术运算符等。简单型CASE语句是根据表达式expression 的值与WHEN后面的表达式逐一比较，如果两者相等，返回THEN后面的表达式result的值，否则返回ELSE后面表达式result的值。,34,【例3-15】将PUBS库中表titles里的 pub_id号为“0736”和“1839”记录显示出来，其余的用“OTHER”显示。use pubsgoselect title,pub_id=case pub_id when 0736 then 0736 when 1389 then 1389 else OTHER endfrom titles,35,(2)搜索型CASE语句搜索型CASE语句的语法形式：CASE WHEN Boolean_expression THEN result,.nELSE result END其中：Boolean_expression：CASE语句要判断的逻辑表达式。搜索型CASE语句是判断逻辑表达式Boolean_expression为真，则返回THEN后面表达式result的值，然后判断下一个逻辑表达式，如果所有逻辑表达式都为假，则返回ELSE后面表达式result的值。,36,【例3-16】将PUBS库中表titles里的price大于10的记录对应的price_level显示“HIGH”，price小于10的记录对应的price_level显示“LOW”，其余的显示“EQ10”。use pubsgoselect title,price level=case when price 10 then HIGH when price 10 then LOW else EQ10 endfrom titles,37,4.BEGIN.END当需要将一个以上的SQL语句作为一组对待时，可以使用BEGIN和END将它们括起来形成一个SQL语句块，以达到一起执行的目的。BEGIN.END语句的语法形式：BEGIN Sql_statementEND Sql_statement：是要执行的任何合法的SQL语句或语句组，需要用户加以注意的是它必须包含在一个单独的批中。,38,5.IF.ELSEIF.ELSE语句的语法形式：IF Boolean_expression Sql_statementELSE IF Boolean_expression Sql_statement 其中：Boolean_ expression：是布尔表达式，其值是TRUE或FALSE。Sql_statement：是要执行的SQL语句。这既可以是单个SQL语句，也可以是一组SQL语句。如果在IF或ELSE语句后面有多条SQL语句，必须把它们放在BEGIN.END块中。,39,【例3-17】定义一个整形变量，如赋值为1，显示“I am a student”，否则显示“I am a teacher”。declare a intselect a=2 if a=1 begin print I am print a student endelse print I am a teachergo,40,6.WHILE 结构WHILE语句的作用是为重复执行某一语句或语句块设置条件。当指定条件为TRUE时，执行这些语句，直到为FALSE为止。WHILE语句的语法形式：WHILE boolean-expression Sql_statementBREAK Sql_statement CONTINUE Sql_statement,41,其中：boolean-expression：是布尔表达式，其值是TRUE或FALSE。Sql_statement：是要执行的SQL语句。既可以是单个SQL语句，也可以是一组SQL语句。如果在WHILE语句后面有多条SQL语句，必须把它们放在BEGIN.END块中。BREAK：是退出所在的循环。CONTINUE：是使循环跳过CONTINUE之后的语句重新开始。,42,【例3-18】输出字符串“School”中每一个字符的ASCII值和字符。declare position int,string char(6)set position=1set string=Schoolwhile position=datalength(string)begin select ascii(substring(string,position,1)as asccode,char(ascii(substring(string,position,1)as ascchar set position=position+1 end,43,7.GOTO语句使用GOTO语句可以使SQL语句的执行流程无条件地转移到指定的标号位置。GOTO语句和标号可以用在语句块、批处理和存储过程中，标号的命名要符合标识符命名规则。GOTO语句经常用在WHILE和IF语句中以跳出循环或分支处理。GO语句的语法形式：Label:GOTO label,44,【例3-19】使用IF语句求1到10之间的累加和并输出结果declare sum int,count intselect sum=0,count=1label:select sum=sum+countselect count=count+1if count=10goto labelselect sum,count,45,8.WAITFOR语句使用WAITFOR语句可以在某一个时刻或某一个时间间隔后执行SQL语句或语句组。WAITFOR语句语法形式：WAITFOR DELAY time|TIME time其中：DELAY 指定时间间隔，TIME指定在某一个时刻。TIME参数的数据类型为datetime,格式为hh:mm:ss。,46,【例3-20】设置在9：00进行一次查询操作。use pubsgobeginwaitfor time 9:00select*from titlesend,47,【例3-21】设置在5分钟后进行一次查询操作。use pubsgobeginwaitfor delay 00:05select*from titlesend,48,9.注释语句注释语句是起说明代码含义，增加脚本的可读性，是不执行的，有两种用法：（1）/*/该方法可以注释多行。（2）-（两个减号）用于一行的注释。,本章首页,