SQL第8章T-SQL编程.ppt

《SQL第8章T-SQL编程.ppt》由会员分享，可在线阅读，更多相关《SQL第8章T-SQL编程.ppt（71页珍藏版）》请在三一办公上搜索。

1、1,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T-SQL数据类型及转换 8.5 T-SQL运算符8.6 T-SQL中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数8.9 T-SQL中用户自定义函数,2,8.1 Transact-SQL简介,结构化查询语言（SQL）是由美国国家标准协会（American National Standards Institute，ANSI）和国际标准化组织（International Standards Organization，ISO）定义的标准，而T-SQ

2、L是Microsoft公司对此标准的一个实现。T-SQL是在SQL上发展而来的，T-SQL在SQL的基础上添加了流程控制，是SQL语言的扩展。因此，SQL是几乎所有的关系型数据库都支持的语言，而T-SQL是Microsoft SQL server支持的语言。,3,8.1 Transact-SQL简介,在Microsoft SQL server 2005系统中，T-SQL可以创建、维护、保护数据库对象，并且可以操作对象中的数据，所以Transact-SQL语言是一种完整的语言。根据T-SQL语言的执行功能特点，可以将T-SQL语言分为三种类型：数据定义语言：Create、Alter和Drop等语

3、句 数据操纵语言：Insert、Update、Delete和Select语句 数据控制语言：Grant、Revoke、Deny等语句 附加的语言元素：Transact-SQL语言的附加语言元素，包括变量、运算符、函数、注释和流程控制语句等。,4,8.1 Transact-SQL简介,数据定义语言（Data Definition Language，DDL）数据定义语言DDL用于在SQL中创建或修改数据库及数据库对象，如创建表、视图、存储过程、函数等数据库对象。在DDL中主要包括CREATE语句、ALTER语句和DROP语句，分别用于创建、修改、删除数据库及数据库对象。,5,8.1 Transac

4、t-SQL简介,数据操纵语言（Data Manipulation Language，DML）数据操纵语言DML是指用来操纵数据库中数据的语句，主要包括SELECT语句、INSERT语句、UPDATE语句、DELETE语句、CURSOR语句等。数据控制语言（Data Control Language，DCL）数据控制语言DCL是用来确保数据库安全的语句，主要用于控制数据库组件的存取许可、存取权限等权限管理问题。其主要包括GRANT语句、REVOKE语句、DENY。,6,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T

5、-SQL数据类型及转换 8.5 T-SQL运算符8.6 T-SQL中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数,7,8.2 T-SQL中的批处理,在SQL Server2005中，可以一次执行多个T-SQL语句，这些多个T-SQL语句称为“批”。SQL Server2005会将一批T-SQL语句当成一个执行单元，将其编译后一次执行，而不是将一个个T-SQL语句编译后再一个个执行。在SQL Server2005中同样允许一次使用多个批，不同的批之间用“GO”来分隔。查询编辑器会自动根据GO指令来将T-SQL语句分为多个批来编译执行。如：use northwindsele

6、ct*from categoriesselect*from customersgo,8,8.2 T-SQL中的批处理,注意：1、go并不是T-SQL语句，只有查询编辑器才能识别并处理，编写其他应用程序就不能使用go指令。2、由于批与批之间是独立的，所以，当其中一个批出现错误时，不会影响其他批的运行。,9,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T-SQL数据类型及转换 8.5 T-SQL运算符8.6 T-SQL中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数,10,8.3 T-SQL中的

7、注释,在T-SQL程序里加入注释语句，可以增加程序的可读性。SQL Server不会对注释的内容进行编辑和执行，在T-SQL中支持两种注释方式。1、-注释-注释的有效范围只能到该行结束的地方，也就是说，从开始，到本行结束为止，都可以是注释的内容，如果有多行注释内容的话，每一行的最前面都必须加上-。2、/*/注释 当要进行比较长的注释时，可以使用/*/注释，/*/可以对多行语句进行注释，其有效范围是从“/*”开始，到“*/”结束，中间可以跨越多行。,11,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T-SQL数据类

8、型及转换 8.5 T-SQL运算符8.6 T-SQL中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数,12,8.4 T-SQL数据类型及转换,在SQL Server中，无论是数据表的字段、常量、变量、表达式还是参数，都具有一个相对应的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型。1、使用CAST转换数据类型当要对不同类型的数据进行运算时，就必须将其转换成相同的数据类型才能进行运算。在SQL Server里提供了两个函数可以进行数据类型的转换，其中一个是CAST。,13,8.4 T-SQL数据类型及转换,语法格式如下：CAST（expression AS d

9、ata_type(length)）其中expression为任何有效的表达式，data_type为要转换的数据类型，length为数据类型的长度，一般只有在nchar、nvarchar、char、varchar、binary和varbinary这几种数据类型才需要使用，是可选参数。,14,8.4 T-SQL数据类型及转换,例1：查看northwind数据库products表中产品及单价，并在一列中显示出来.程序代码如下：SELECT productname+N的单价为：+CAST(unitprice as varchar(10)+N元 AS 产品介绍FROM products,15,8.4 T

10、-SQL数据类型及转换,同步任务1：查看northwind数据库orders表中订单号和订单时间，并在一列中显示出来SELECT CAST(orderid as varchar(10)+N的时间为：+CAST(orderdate as varchar(20)AS 订单时间FROM orders,16,8.4 T-SQL数据类型及转换,2、使用CONVERT转换数据类型CONVERT函数与CAST函数类似，作用也是转换数据类型，其语法格式如下：CONVERT（data_type(length)，expression,style）data_type为要转换的数据类型length为数据类型的长度ex

11、pression为任何有效的表达式style是样式,一般用于将datetime或smalldatetime数据转换为字符数据（nchar、nvarchar、char、varchar数据类型）的日期格式的样式；或者用于将float、real、money或smallmoney数据转换为字符数据的字符串格式。如果style为NULL，则返回的结果也为NULL。,17,8.4 T-SQL数据类型及转换,例2：查看northwind数据库orders表中订单号、订购日期、到货日期、发货日期。select orderid as 订单号,convert(varchar(20),orderdate,1)as

12、订购日期,convert(varchar(20),requireddate,102)as 到货日期,convert(varchar(20),shippeddate,103)as 发货日期from orders,18,8.4 T-SQL数据类型及转换,3 隐式数据类型转换在进行不同类型的数据运算时，不一定都必须要使用CAST或CONVERT来进行数据类型转换，在SQL Server里，系统会自动将一些数据类型进行转换，这种转换称为“隐式转换”。而用CAST或CONVERT转换数据类型称为“显式转换”。,19,8.4 T-SQL数据类型及转换,例3：查看northwind数据库products表中

13、产品库存量所值的资金SELECT productname,unitprice*unitsinstock AS jiyazijinFROM products同步任务3：查看northwind数据库order details表中每个订单的总金额SELECT orderid,sum(unitprice*quantity)AS jineFROM order detailsGROUP BY orderid,20,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T-SQL数据类型及转换 8.5 T-SQL运算符8.6 T-SQL

14、中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数,21,8.5 T-SQL运算符,算术运算符逻辑运算符字符串连接运算符一元运算符比较运算符,2023/11/8,22,1.算术运算符,2.赋值运算符,算术运算符包括加（+）、减（）、乘（*）、除（/）和取模（%，返回两个数相除后的余数）。,Transact-SQL 中只有一个赋值运算符（=）,8.5 T-SQL运算符,3.位运算符,【例】输出对1与0按位求与运算、或运算和异或运算的结果。DECLARE a bit,b bit,c bitSET a=1&0SET b=1|0SET c=10PRINT a PRINT b PRI

15、NT c,8.5 T-SQL运算符,2023/11/8,24,4.比较运算符,比较运算符表,8.5 T-SQL运算符,2023/11/8,25,5.逻辑运算符,逻辑运算符表,8.5 T-SQL运算符,2023/11/8,26,6.字符串联运算符,字符串联运算符只有一个，即加号“+”。利用字符串运算符可以将多个字符串连接起来，构成一个新的字符串。,例如，执行语句SELECT abc+def，其结果为abcdef。,【例题9.7】查询student表中学生的姓名和学号，要求两者要作为一列结果输出，学号和姓名间用字符“-”隔开。USE SCOREGOSELECT student_id+-+stude

16、nt_nameFROM student,8.5 T-SQL运算符,2023/11/8,27,运算符的优先级别表,8.5 T-SQL运算符,28,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T-SQL数据类型及转换 8.5 T-SQL运算符8.6 T-SQL中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数,29,8.6 T-SQL中的常量和变量,1、常量 常量，也称为文字值或标量值，是一个代表特定值的符号，是一个不变的值。常量的格式取决于它所表示的值的数据类型。字符串常量:a，Im back，

17、Unicode常量:N a，N Bit常量:1，0 Datetime常量:August 3,2006，2006-8-3，06/08/06 Integer常量，decimal常量，float和real常量，money常量,30,8.6 T-SQL中的常量和变量,2、变量 T-SQL中的变量可以分为全局变量和局部变量两种，全局变量是以开头命名的变量，局部变量是以开头命名的变量。全局变量是SQL SERVER 系统提供并赋值的变量。用户不能建立及修改全局变量。全局变量是一组特殊的函数，它们的名称是以开始,且不需要任何参数。SQL SERVER提供33个全局变量。,31,8.6 T-SQL中的常量和变

18、量,error:上一条SQL语句报告的错误号rowcount:上一条SQL语句处理的行数identity:最后插入的标识值fetch_status:上一条游标 Fetch 语句的状态nestlevel:当前存储过程或触发器的嵌套级别servername:本地服务器的名称spid:当前用户进程的会话idcpu_busy:SQL Server 自上次启动后的工作时间例4：查看版本信息print version例5：查看select后的记录集里的记录数select*from employeesprint 一共查询了+CAST(rowcount as varchar(5)+条记录,32,8.6 T-S

19、QL中的常量和变量,3、局部变量 指在批处理或脚本中用来保存数据值的对象,局部变量名总是以符号开始,必须符合标识符命名规则。在使用一个局部变量前，必须使用DECLARE语句来声明这个局部变量，指定其变量名和数据类型。局部变量声明的语法格式为：DECLARE 局部变量名 数据类型,n,33,8.6 T-SQL中的常量和变量,局部变量赋值：SET 局部变量名=表达式也可以使用select语句 select 局部变量名=表达式,n,注意：声明的变量名，其第一个字符必须是必须指定变量的数据类型及长度默认情况下，系统将声明后的变量设置为NULL,34,8.6 T-SQL中的常量和变量,局部变量的作用域：

20、局部变量的作用域从声明它们的地方开始到声明它们的批处理或存储过程的结尾。,【任务】使用birthday存储出生日期，实用age存储年龄，实用name存储姓名。同时为所声明的birthday变量赋值“1999-4-14”（使用set语句）；然后将Student表中的学生的最大年龄赋值给变量age（使用Select语句）。,Declare birthday datetimeDeclare age int,name char(8)Set birthday=1994-4-14Use databasenameSelect age=max(age)from studentPrint-变量的输出结果-Pri

21、nt birthday的值Print birthdayPrint 最大年龄Print agePrint name,8.6 T-SQL中的常量和变量,36,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T-SQL数据类型及转换 8.5 T-SQL运算符8.6 T-SQL中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数,37,8.7 T-SQL的流程控制,1）BEGINEND语句块 用于将多个T-SQL语句组合在一个语句块中,其语法格式为:BEGIN 语句1 语句n END,38,8.7 T-SQ

22、L的流程控制,2)IFELSE语句 语法格式为:IF 条件表达式 语句 ELSE 语句,39,8.7 T-SQL的流程控制,例6：向products表插入一条记录，如果插入成功则输出插入记录成功，否则输出出现错误INSERT products(productid,productname,discontinued)VALUES(112,computer,1)IF error0 PRINT N出现错误ELSE PRINT N插入记录成功GO,40,8.7 T-SQL的流程控制,例7：查看northwind数据库products中产品名为 Chang的单价是否低于20元，如果低于20元的话，查看其订

23、购量。,分析：1、最后及过程中需要查询的内容是什么？2、查询用到哪些表？3、查询过程中的条件是什么？4、查询过程中需要定义哪些变量？,41,8.7 T-SQL的流程控制,DECLARE price moneyDECLARE productid intDECLARE sum_total intSELECT price=unitprice,productid=productidFROM productsWHERE productname=ChangIF price20 BEGIN PRINT NChang的单价低于20元 SELECT sum_total=sum(order details.qua

24、ntity)FROM order details WHERE order details.productid=productid PRINT N其订购数量为：+CAST(sum_total as varchar(5)END,42,8.7 T-SQL的流程控制,例8：查看northwind数据库products表中产品名为Chang的单价是否低于8元，如果低于8元的话，查看其订购量，否则查看其库存量,43,8.7 T-SQL的流程控制,DECLARE price moneyDECLARE productid intDECLARE count intSELECT price=unitprice,p

25、roductid=productidFROM productsWHERE productname=ChangIF 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,44,8.7 T-SQL的流程控制,ELSE BEGIN PRINT NChang的单价高于8元 SELECT count=sum(un

26、itsinstock)FROM products WHERE productid=productid PRINT N其库存量为：+CAST(count as varchar(5)END,45,8.7 T-SQL的流程控制,3）CASE表达式CASE表达式可在程序中处理多个条件，完成不同的分支操作，CASE表达式的结果可应用到SELECT或UPDAT等语句中，其语法格式为：CASE WHEN 条件1 THEN 表达式结果1 WHEN 条件2 THEN 表达式结果2 WHEN 条件n THEN 表达式结果n END,46,8.7 T-SQL的流程控制,例9：对学生成绩表查询出所有学生的成绩情况：要

27、求凡成绩为空者输出【缺考】，小于60分的输出【不及格】，60-69分间的输出【及格】，70-79分间的输出【中】，80-89分间的输出【良】，90分以上的输出【优】。,47,8.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 优 ENDFROM stu_score,48,8.7 T-SQL的流程控制,4)WHILE语句 语法格式为:WHILE 条件表

28、达式 BEGIN 语句序列1 BREAK 语句序列2 CONTINUE 语句序列3 END,49,8.7 T-SQL的流程控制,例10：输出 northwind数据库products表中产品编号为10以内的产品名。DECLARE id intDECLARE productname varchar(40)SET id=1WHILE id10BEGIN SELECT productname=productname FROM products WHERE productid=id PRINT productname SET id=id+1END,50,8.7 T-SQL的流程控制,例11：编程计算1

29、100之间所有能被3整除的数的个数和总和。DECLARE sum INT,i SMALLINT,nums SMALLINTBEGIN SET sum=0 SET i=1 SET nums=0,51,8.7 T-SQL的流程控制,WHILE(i=100)BEGIN IF(i%3=0)BEGIN SET sum=sum+i SET nums=nums+1 END SET i=i+1 END PRINT 总和是+STR(sum)PRINT 个数是+STR(nums)END,52,8.7 T-SQL的流程控制,5)Return语句 Return语句会终止目前T-SQL语句的执行，从查询或过程中无条件地

30、退出来，并且可以返回一个整数值给调用该代码的程序。与break不同，Return可以在任何时候从过程、批处理或语句块中退出，而不是跳出某个循环或跳到位置。语法格式为:RETURN integer_expression,53,第8章 T-SQL编程,8.1 Transact-SQL简介 8.2 T-SQL中的批处理 8.3 T-SQL中的注释8.4 T-SQL数据类型及转换 8.5 T-SQL运算符8.6 T-SQL中的常量和变量8.7 T-SQL的流程控制8.8 T-SQL中常用函数,54,8.8 T-SQL中常用函数,1、日期和时间函数 year(date)返回表示指定日期的年份的整数 mo

31、nth day getdate()返回系统当前日期和时间 dateadd(datepart,number,date)在指定的日期上再加一个时间间隔，并返回新的日期 datediff(datepart,startdate,enddate)返回跨两个指定日期的日期边界数和时间边界数 datename(datepart,date)返回表示指定日期的指定日期部分的字符串 datepart(datepart,date)返回表示指定日期的指定日期部分的整数,表 datepart参数可取的关键字,8.8 T-SQL中常用函数,56,8.8 T-SQL中常用函数,例11：查看今天的年月日，并以格式化的形式显示

32、。select 今天是+datename(YY,getdate()+年+datename(MM,getdate()+月+datename(dd,getdate()+日,【例】返回“1998-10-7”所在的月份和季度。SELECT datepart(month,1998-10-6)as 月份,datepart(quarter,1998-10-6)as 季度返回结果如下：月份 季度 10 4,57,8.8 T-SQL中常用函数,例12：用日期函数计算学生信息表中学生的年龄。USE studentGOSELECT 姓名,datediff(YY,出生年月,getdate()as 年龄 FROM st

33、ud_infoGO,58,8.8 T-SQL中常用函数,2、数学函数abs 返回绝对值acos 返回反余弦3、字符串函数len()返回指定字符串表达式的字符数，其中不包含尾随空格Ltrim()返回删除了前导空格之后的字符表达式left(字符串,n)返回字符串中从左边开始指定个数的字符lower()将大写字符数据转换为小写字符数据后返回字符表达式STR(numeric_expression,length,decimal)返回一个数字的等效字符串。,59,8.8 T-SQL中常用函数,例13：使用字符串函数查找姓王的同学，并格式化显示其出生年月。SELECT 姓名,str(year(出生年月)+年

34、+ltrim(str(month(出生 年月)+月 as 出生年月FROM stud_infoWHERE left(姓名,1)=王GO,8.8 T-SQL中常用函数,【任务】有一字符串“Hunan Railway Professional College”，要对其进行如下操作：去掉其左边和右边空格；将该字符串全部转换为大写；了解整个字符串的长度；提取左边6个字符；提取“Hunan”字串。,8.8 T-SQL中常用函数,Declare temp varchar(50)Set temp=Hunan Railway Professional CollegePrint 去掉空格后+Rtrim（Ltri

35、m（temp）Print 转换为大写+UPPER（TEMP）Print 字符串长度+Cast（Len（temp）as char(4)）Print 取左个字符+Left（temp,6）Print 截取子串后+Substring(temp，2，5)Print way第一次出现位置+convert（char(4),patindex(%way%,temp)）,2023/11/8,62,SQL Server 2005中根据函数返回值形式的不同将用户自定义函数分为三种类型:标量型函数、内嵌表值型函数和多语句表值函数。,标量函数,标量函数返回一个具有确定数据类型的标量值，其返回值数据类型为除TEXT、NTE

36、XT和TABLE等类型外的其它数据类型。函数体语句定义在BEGIN-END语句内。,8.9 T-SQL中用户自定义函数,2023/11/8,63,语法格式如下：CREATE FUNCTION owner_name.函数名称（形式参数名称 AS 数据类型=default,）RETURNS 返回数据类型asBEGIN 函数体 RETURN 表达式END,(1)标量函数的定义,8.9 T-SQL中用户自定义函数,2023/11/8,64,【例】编写一个标量函数：以学号和课程编号做为参数，输入学号和课程编号后得到该学生该门课程的成绩。CREATE FUNCTION F_grade(s_id char(

37、10),c_id char(5)RETURNS INTASBEGIN DECLARE G INT SET G=(SELECT grade FROM student,score WHERE student.student_id=score.student_id and student.student_id=s_id and course_id=c_id)RETURN GEND,8.9 T-SQL中用户自定义函数,65,（2）标量函数的调用可以在使用标量表达式的位置调用标量函数，也可以使用EXECUTE语句执行标量函数。执行标量函数有两种方法，1)owner_name函数名(实参,实参n)2)EX

38、EC owner_name.函数名 实参,实参n 或：EXEC owner_name.函数名 形参实参,形参n=实参n【例】分别用两种方法调用F_grade函数，完成查看0801101号学生1001号课程的成绩。方法一：USE SCOREPRINT dbo.F_grade(0801101,1001)方法二：USE SCOREDECLARE 成绩 intEXEC 成绩=dbo.F_grade 0801101,1001PRINT 成绩,8.9 T-SQL中用户自定义函数,2023/11/8,66,2.内嵌表值型函数 内嵌表值型函数返回值的类型为table，即它返回的是一个表，返回的表中的数据由位于

39、RETURN 子句后的SELECT语句决定。内嵌表值型函数的调用内嵌表值型函数只能通过select语句调用。调用语法格式为：SELECT*FROM 函数名(实参,实参n),8.9 T-SQL中用户自定义函数,【例】建立一个函数，以学号和课程编号做为参数，输入学号和课程编号后得到该学生学号、姓名、课程编号和成绩。USE SCOREGOCREATE FUNCTION F_s_grade(s_id char(10),c_id char(5)RETURNS TABLERETURN SELECT student.student_id,student_name,course_id,gradeFROM st

40、udent,scoreWHERE student.student_id=score.student_id and student.student_id=s_id and course_id=c_i调用函数F_s_grade，查看0801101号学生1001号课程的成绩。SELECT*FROM F_s_grade(0801101,1001),8.9 T-SQL中用户自定义函数,2023/11/8,68,(3)多语句表值函数 多语句表值函数可生成记录，并将记录插入返回的表类型的变量中。多语句表值函数的定义CREATE FUNCTION owner_name.函数名称（形式参数名称 AS 数据类型=

41、default,）RETURNS return_variable TABLE BEGIN 函数体 RETURNEND,8.9 T-SQL中用户自定义函数,建立一个函数，查询学生的学号、姓名、课程编号和成绩，并将这些信息插入到一个表类型变量中。USE SCOREGOCREATE FUNCTION F_student_grade()RETURNS result TABLE(s_id char(10),name char(8),c_id char(5),result int)BEGININSERT resultSELECT student.student_id,student_name,course

42、_id,gradeFROM student,scoreWHERE student.student_id=score.student_idRETURNEND,8.9 T-SQL中用户自定义函数,2023/11/8,70,多语句表值函数的调用多语句表值函数的调用与内嵌表值型函数的调用方法相同。调用语法格式为：SELECT*FROM 函数名(实参,实参n)调用F_student_grade函数SELECT*FROM F_student_grade(),8.9 T-SQL中用户自定义函数,2023/11/8,71,删除用户自定义函数使用DROP FUNCTION语句可以删除当前数据库中指定的一个或多个函数。其语法如下：DROP FUNCTION owner_name.函数名称,.n 删除数据库score中的用户自定义函数F_student_grade。DROP FUNCTION F_student_grade,8.9 T-SQL中用户自定义函数,