SQL第6章T-SQL编程.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,1,第,6,章,T-SQL,编程,6.1 Transact-SQL,简介,6.2,T-SQL,中的批处理,6.3,T-SQL,中的注释,6.4,T-SQL,数据类型及转换,6.5,T-SQL,运算符,6.6,T-SQL,中的常量和变量,6.7,T-SQL,的流程控制,6.8,T-SQL,中常用函数,2,6.1 Transact-SQL,简介,结构化查询语言（,SQL,）是由美国国家标准协会（,American National Standards Institute,，,ANSI,）和国际标准化组织（,International Standards Organization,，,ISO,）定义的标准，而,T-SQL,是,Microsoft,公司对此标准的一个实现。,T-SQL,是在,SQL,上发展而来的，,T-SQL,在,SQL,的基础上添加了流程控制，是,SQL,语言的扩展。因此，,SQL,是几乎所有的关系型数据库都支持的语言，而,T-SQL,是,Microsoft SQL server,支持的语言。,3,6.1 Transact-SQL,简介,在,Microsoft SQL server 2005,系统中，,T-SQL,可以创建、维护、保护数据库对象，并且可以操作对象中的数据，所以,Transact-SQL,语言是一种完整的语言。根据,T-SQL,语言的执行功能特点，可以将,T-SQL,语言分为三种类型：,数据定义语言：,Create,、,Alter,和,Drop,等语句,数据操纵语言：,Insert,、,Update,、,Delete,和,Select,语句,数据控制语言：,Grant,、,Revoke,、,Deny,等语句,附加的语言元素：,Transact-SQL,语言的附加语言元素，包括变量、运算符、函数、注释和流程控制语句等。,4,6.1 Transact-SQL,简介,数据定义语言,（,Data Definition Language,，,DDL,）,数据定义语言,DDL,用于在,SQL,中,创建或修改数据库及数据库对象,，如创建表、视图、存储过程、函数等数据库对象。在,DDL,中主要包括,CREATE,语句、,ALTER,语句和,DROP,语句，分别用于创建、修改、删除数据库及数据库对象。,5,6.1 Transact-SQL,简介,数据操纵语言,（,Data Manipulation Language,，,DML,）,数据操纵语言,DML,是指,用来操纵数据库中数据的语句,，主要包括,SELECT,语句、,INSERT,语句、,UPDATE,语句、,DELETE,语句、,CURSOR,语句等。,数据控制语言,（,Data Control Language,，,DCL,）,数据控制语言,DCL,是,用来确保数据库安全的语句,，主要,用于控制数据库组件的存取许可、存取权限等权限管理问题,。其主要包括,GRANT,语句、,REVOKE,语句、,DENY,。,6,6.2 T-SQL,中的批处理,在,SQL Server2005,中，可以一次执行多个,T-SQL,语句，这些多个,T-SQL,语句称为“批”。,SQL Server2005,会将一批,T-SQL,语句当成一个执行单元，将其编译后一次执行，而不是将一个个,T-SQL,语句编译后再一个个执行。,在,SQL Server2005,中同样允许一次使用多个批，不同的批之间用“,GO”,来分隔。查询编辑器会自动根据,GO,指令来将,T-SQL,语句分为多个批来编译执行。,如：,use northwind,select*from categories,select*from customers,go,7,6.2 T-SQL,中的批处理,注意：,1,、,go,并不是,T-SQL,语句，只有查询编辑器才能识别并处理，编写其他应用程序就不能使用,go,指令。,2,、由于批与批之间是独立的，所以，当其中一个批出现错误时，不会影响其他批的运行。,8,6.3 T-SQL,中的注释,在,T-SQL,程序里加入注释语句，可以增加程序的可读性。,SQL Server,不会对注释的内容进行编辑和执行，在,T-SQL,中支持两种注释方式。,1,、,-,注释,-,注释的有效范围只能到该行结束的地方，也就是说，从,开始，到本行结束为止，都可以是注释的内容，如果有多行注释内容的话，每一行的最前面都必须加上,-,。,2,、,/*,*/,注释,当要进行比较长的注释时，可以使用,/*,*/,注释，,/*,*/,可以对多行语句进行注释，其有效范围是从,“,/*,”,开始，到,“,*,/,”,结束，中间可以跨越多行。,9,6.4 T-SQL,数据类型及转换,在,SQL Server,中，无论是数据表的字段、常量、变量、表达式还是参数，都具有一个相对应的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型。,1,、使用,CAST,转换数据类型,当要对不同类型的数据进行运算时，就必须将其转换成相同的数据类型才能进行运算。在,SQL Server,里提供了两个函数可以进行数据类型的转换，其中一个是,CAST,。,10,6.4 T-SQL,数据类型及转换,语法格式如下：,CAST,（,expression AS data_type(length),）,其中,expression,为任何有效的表达式，,data_type,为要转换的数据类型，,length,为数据类型的长度，一般只有在,nchar,、,nvarchar,、,char,、,varchar,、,binary,和,varbinary,这几种数据类型才需要使用，是可选参数。,11,6.4 T-SQL,数据类型及转换,例,1,：,查看,northwind,数据库,products,表中产品及单价，并在一列中显示出来,.,程序代码如下：,SELECT productname+N,的单价为：,+CAST(unitprice as varchar(10)+N,元,AS,产品介绍,FROM products,12,6.4 T-SQL,数据类型及转换,同步任务,1,：查看,northwind,数据库,orders,表中订单号和订单时间，并在一列中显示出来,SELECT CAST(orderid as varchar(10)+N,的时间为：,+CAST(orderdate as varchar(20)AS,订,单时间,FROM orders,13,6.4 T-SQL,数据类型及转换,2,、使用,CONVERT,转换数据类型,CONVERT,函数与,CAST,函数类似，作用也是转换数据类型，其语法格式如下：,CONVERT,（,data_type(length),，,expression,style,）,data_type,为要转换的数据类型,length,为数据类型的长度,expression,为任何有效的表达式,style,是样式,一般用于将,datetime,或,smalldatetime,数据转换为字符数据（,nchar,、,nvarchar,、,char,、,varchar,数据类型）的日期格式的样式；或者用于将,float,、,real,、,money,或,smallmoney,数据转换为字符数据的字符串格式。如果,style,为,NULL,，则返回的结果也为,NULL,。,14,6.4 T-SQL,数据类型及转换,例,2,：,查看,northwind,数据库,orders,表中订单号、,订购日期,、,到货日期,、,发货日期,。,select orderid as,订单号,convert(varchar(20),orderdate,1)as,订购日期,convert(varchar(20),requireddate102)as,到货日期,convert(varchar(20),shippeddate,103)as,发货日期,from orders,15,6.4 T-SQL,数据类型及转换,3,隐式数据类型转换,在进行不同类型的数据运算时，不一定都必须要使用,CAST,或,CONVERT,来进行数据类型转换，在,SQL Server,里，系统会自动将一些数据类型进行转换，这种转换称为“隐式转换”。而用,CAST,或,CONVERT,转换数据类型称为“显式转换”。,16,6.4 T-SQL,数据类型及转换,例,3,：,查看,northwind,数据库,products,表中产品库存量所值的资金,SELECT productname,unitprice*unitsinstock AS jiyazijin,FROM products,同步任务,3,：,查看,northwind,数据库,order details,表中每个订单的总金额,SELECT orderid,sum(unitprice*quantity)AS jine,FROM order details,GROUP BY orderid,17,6.5 T-SQL,运算符,算术运算符,逻辑运算符,字符串连接运算符,一元运算符,比较运算符,2025/12/6 周六,18,1.,算术运算符,2.,赋值运算符,算术运算符包括加（,+,）、减（,）、乘（*）、除（,/,）和取模（,%,，返回两个数相除后的余数）。,Transact-SQL,中只有一个赋值运算符（,=,）,6.5 T-SQL,运算符,运算符,名称,语法,+,加,Expression1+Expression2,-,减,Expression1-Expression2,*,乘,Expression1*Expression2,/,除,Expression1/Expression2,%,取余,Expression1%Expression2,3.,位运算符,运算符,含义,&,（与运算）,两个位为,1,时，结果为,1,，否则为,0,|,（或运算）,两个位只要有一个为,1,，结果为,1,，否则为,0,（异或运算）,两个位不同时，结果为,1,，否则为,0,（非运算）,对,1,运算结果为,0,，对,0,运算结果为,1,【,例,】,输出对,1,与,0,按位求与运算、或运算和异或运算的结果。,DECLARE a bit,b bit,c bit,SET a=1&0,SET b=1|0,SET c=10,PRINT a PRINT b PRINT c,6.5 T-SQL,运算符,2025/12/6 周六,20,4.,比较运算符,比较运算符表,运算符,说明,运算符,说明,=,等于,大于,或,!=,不等于,不大于,=,大于或等于,!,、,=,、,、,!),5,位或和位异或运算符（,|,、,）,6,NOT,7,AND,8,OR,、,ALL,、,ANY,、,SOME,9,赋值运算符（,=,）,6.5 T-SQL,运算符,24,6.6 T-SQL,中的常量和变量,1,、常量,常量,，也称为文字值或标量值，是一个代表,特定值,的符号,，是一个,不变,的值。常量的,格式取决于,它所表示,的,值的数据类型,。,字符串常量,:a,，,Im back,，,Unicode,常量,:N,a,，,N,Bit,常量,:1,，,0,Datetime,常量,:,August 3,2006,，,2006-8-3,，,06/08/06,Integer,常量,，,decimal,常量，,float,和,real,常量，,money,常量,25,6.6 T-SQL,中的常量和变量,2,、变量,T-SQL,中的变量可以分为,全局变量,和,局部变量,两,种，全局变量是以,开头命名的变量，,局部变量,是以,开头命名的变量。,全局变量是,SQL SERVER,系统提供并赋值的变量。,用户不能建立及修改全局变量,。全局变量是一组特殊的,函数，它们的名称是以,开始,且不需要任何参数。,SQL SERVER,提供,33,个全局变量。,26,6.6 T-SQL,中的常量和变量,error:,上一条,SQL,语句报告的错误号,rowcount:,上一条,SQL,语句处理的行数,identity:,最后插入的标识值,fetch_status:,上一条游标,Fetch,语句的状态,nestlevel:,当前存储过程或触发器的嵌套级别,servername:,本地服务器的名称,spid:,当前用户进程的会话,id,cpu_busy:SQL Server,自上次启动后的工作时间,例,4,：,查看版本信息,print version,例,5,：,查看,select,后的记录集里的记录数,select*from employees,print,一共查询了,+CAST(rowcount as varchar(5)+,条记录,27,6.6 T-SQL,中的常量和变量,3,、局部变量,指在批处理或脚本中用来保存数据值的对象,局部变量名总是以,符号开始,必须符合标识符命名规则。在,使用,一个,局部变量前,，,必须,使用,DECLARE,语句来,声明,这个局部变量，指定其变量名和数据类型。,局部变量声明的语法格式为：,DECLARE,局部变量名 数据类型,n,28,6.6 T-SQL,中的常量和变量,局部变量赋值：,SET,局部变量名,=,表达式,也可以使用,select,语句,select,局部变量名,=,表达式,n,注意：,声明的变量名，其第一个字符必须是,必须指定变量的数据类型及长度,默认情况下，系统将声明后的变量设置为,NULL,29,6.6 T-SQL,中的常量和变量,局部变量的作用域：,局部变量的,作用域从声明,它们的地方,开始,到声明它们的批处理或存储过程的结尾。,【,任务,】,使用,birthday,存储出生日期，实用,age,存储年龄，实用,name,存储姓名。同时为所声明的,birthday,变量赋值“,1999-4-14”,（使用,set,语句）；然后,将,Employees,表,中,的,职员,的,最大年龄赋值给变量,age,（使用,Select,语句）。,Declare birthday datetime,Declare age int,name char(8),Set birthday=1994-4-14,Use northwind,Select age=max(year(getdate()-year(birthdate)from employees,Print N-,变量的输出结果,-,Print Nbirthday,的值,Print birthday,Print N,最大年龄,Print age,Print name,6.6,T-SQL,中的常量和变量,31,6.7 T-SQL,的流程控制,1,）,BEGINEND,语句块,用于将多个,T-SQL,语句组合在一个语句块中,其语法格式为,:,BEGIN,语句,1,语句,n,END,32,6.7 T-SQL,的流程控制,2)IFELSE,语句,语法格式为,:,IF,条件表达式,语句,ELSE,语句,33,6.7 T-SQL的流程控制,例,6,：向,products,表插入一条记录，如果插入成功则输出插入记录成功，否则输出出现错误,INSERT products(productid,productname,discontinued),VALUES(112,computer,1),IF error0,PRINT N,出现错误,ELSE,PRINT N,插入记录,成功,GO,34,6.7 T-SQL,的流程控制,例,7,：,查看,northwind,数据库,products,中产品名为,Chang,的单价是否低于,20,元，如果低于,20,元的话，查看其订购量。,分析：,1,、最后及过程中需要查询的内容是什么？,2查询用到哪些表？,3,、查询过程中的条件是什么？,4,、查询过程中需要定义哪些变量？,35,6.7 T-SQL,的流程控制,DECLARE price money,DECLARE productid int,DECLARE,sum_total,int,SELECT price=unitprice,productid=productid,FROM products,WHERE productname=Chang,IF price20,BEGIN,PRINT NChang,的单价低于,20,元,SELECT,sum_total,=sum(order details.quantity),FROM order details,WHERE order details.productid=productid,PRINT N,其订购数量为：,+CAST(,sum_total,as varchar(5),END,36,6.7 T-SQL,的流程控制,例,8,：,查看,northwind,数据库,products,表中产品名为,Chang,的单价是否低于,8,元，如果低于,8,元的话，查看其订购量，否则查看其库存量,37,6.7 T-SQL,的流程控制,DECLARE price money,DECLARE productid int,DECLARE count int,SELECT priceunitprice,productid=productid,FROM products,WHERE productname=Chang,IF price8,BEGIN,PRINT NChang,的单价低于,8,元,SELECT,count=sum(order details.quantity),FROM,order details,WHERE order details.productid=productid,PRINT N,其订购数量为：,+CAST(count as varchar(5),END,38,6.7 T-SQL,的流程控制,ELSE,BEGIN,PRINT NChang,的单价高于,8,元,SELECT count=sum(unitsinstock),FROM products,WHERE productid=productid,PRINT N,其库存量为：,+CAST(count as varchar(5),END,39,6.7 T-SQL,的流程控制,3,）,CASE,表达式,CASE,表达式可在程序中处理多个条件，完成不同的分支操作，,CASE,表达式,的结果可,应用到,SELECT,或,UPDATE,等语句中,，其语法格式为：,CASE,WHEN,条件,1 THEN,表达式结果,1,WHEN,条件,2 THEN,表达式结果,2,WHEN,条件,n THEN,表达式结果,n,END,40,6.7 T-SQL,的流程控制,例,9,：,对学生成绩表查询出所有学生的成绩情况：,要求凡成绩为空者输出,【,缺考,】,，小于,60,分的输出,【,不及格,】,，,60-69,分间的输出,【,及格,】,，,70-79,分间的输出,【,中,】,，,80-89,分间的输出,【,良,】,，,90,分以上的输出,【,优,】,。,41,6.7 T-SQL,的流程控制,SELECT stu_id,学号,course_id,课程号,score,成绩,总评成绩,=,CASE,WHEN score IS NULL THEN,缺考,WHEN score=60 AND score=70 AND score=80 AND score=90 THEN,优,END,FROM stu_score,42,6.7 T-SQL,的流程控制,4)WHILE,语句,语法格式为,:,WHILE,条件表达式,BEGIN,语句序列,1,BREAK,语句序列,2,CONTINUE,语句序列,3,END,43,6.7 T-SQL,的流程控制,例,10,：,输出,northwind,数据库,products,表中产品编号为,10,以内的产品名。,DECLARE id int,DECLARE productname varchar(40),SET id=1,WHILE id10,BEGIN,SELECT productname=productname FROM products,WHERE productid=id,PRINT productname,SET id=id+1,END,44,6.7 T-SQL,的流程控制,例,11,：,编程计算,1100,之间所有能被,3,整除的数的个数和总和。,DECLARE sum INT,i SMALLINT,nums SMALLINT,BEGIN,SET sum=0,SET i=1,SET nums=0,45,6.7 T-SQL,的流程控制,WHILE(i=100),BEGIN,IF(i%3=0),BEGIN,SET sum=sum+i,SET numsnums+1,END,SET i=i+1,END,PRINT ,总和是,+STR(sum),PRINT ,个数是,+STR(nums),END,46,6.7 T-SQL,的流程控制,5)Return,语句,Return,语句会终止目前,T-SQL,语句的执行，从查询或过程中无条件地退出来，并且可以返回一个整数值给调用该代码的程序。与,break,不同，,Return,可以在任何时候从过程、批处理或语句块中退出，而不是跳出某个循环或跳到位置。,语法格式为,:,RETURN integer_expression,47,6.8 T-SQL,中常用函数,1,、日期和时间函数,year(date),返回表示指定日期的年份的整数,month day,getdate(),返回系统当前日期和时间,dateadd,(,datepart,number,date),在指定的日期上再加一个时间间隔，并返回新的日期,datediff,(,datepart,startdate,enddate),返回跨两个指定日期的日期边界数和时间边界数,datename,(,datepart,date),返回表示指定日期的指定日期部分的字符串,datepart,(,datepart,date),返回表示指定日期的指定日期部分的整数,48,6.8 T-SQL,中常用函数,例,11,：,查看今天的年月日，并以格式化的形式显示。select,今天是,+datename(YY,getdate()+,年,+datename(MM,getdate()+,月,+datename(dd,getdate()+,日,49,6.8 T-SQL,中常用函数,例,12,：,用日期函数计算学生信息表中学生的年龄。,USE student,GO,SELECT,姓名,datediff(YY,出生,年月,getdate()as,年龄,FROM,stud_info,GO,50,6.8 T-SQL,中常用函数,2,、数学函数,abs,返回绝对值,acos,返回反余弦,3,、字符串函数,len(),返回指定字符串表达式的字符数，其中不包含尾随空格,Ltrim(),返回删除了前导空格之后的字符表达式,left,(,字符串,n),返回字符串中从左边开始指定个数的字符,lower(),将大写字符数据转换为小写字符数据后返回字符表达式,STR,(numeric_expression,length,decimal ),返回一个数字的等效字符串。,51,6.8 T-SQL,中常用函数,例,13,：,使用字符串函数查找姓王的同学，并格式化显示其出生年月。,SELECT,姓名,str(year(,出生,年月,)+,年,+ltrim(str(month(,出生,年月,)+,月,as,出生年月,FROM,stud_info,WHERE left(,姓名,1)=,王,GO,6.8 T-SQL,中常用函数,【,任务,】,有一字符串“,Hunan Railway Professional College”,，要对其进行如下操作：去掉其左边和右边空格；将该字符串全部转换为大写；了解整个字符串的长度；提取左边,6,个字符；提取“,Hunan”,字串。,6.8 T-SQL,中常用函数,Declare temp varchar(50),Set temp=Hunan Railway Professional College,Print N,去掉空格后,+Rtrim(Ltrim(temp),Print N,转换为大写,+UPPER(TEMP),Print N,字符串长度,+Cast(Len(temp)as char(4),Print N,取左个字符,+Left(temp,6),Print N,截取子串后,+Substring(temp,2,8),Print Nway,第一次出现位置,+convert(char(4),patindex(%way%,temp),54,小结,掌握,1,、,掌握变量、常量的定义，掌握常用的运算符；,2,、,掌握赋值语句、条件语句、循环语句的语法格式及应用；,了解,1,、,了解,T-SQL,语言类型；,2,、,了解,SQL,注释符号的用法。,