《TSQL语言基础》PPT课件.ppt

第6章 T-SQL语言概述 主讲教师：卫琳,6.1 T-SQL语言概述,6.1.1 T-SQL语言的发展过程和特点 6.1.2 T-SQL语言的分类 数据定义语言(DDL) 数据操纵语言(DML) 数据控制语言(DCL),DDL,DML,DCL,6.1.3 T-SQL法约定,1.注释 “”：单行注释（从双连字符到行尾的内容） /*/：多行注释（对/*/ 之间所有内容） 2.系统保留字 不要使用保留关键字作为对象名称或标识符,/*创建名为example的数据库，并存放在“e:sql”目录中*/ CREATE DATABASE example ON (name=example,-设置数据库文件名称 filename=e:sqlexample.mdf, -设置文件存放位置 size=10,-指定文件大小 maxsize=50)-指定文件的最大容量 LOG ON-指定日志文件 (name=examplog,-指定日志文件名称 filename=e:sqlexample.ldf, -指定日志文件存放位置 size=5mb, maxsize=25mb, filegrowth=5mb) go,6.2 附加的语言元素,6.1.2 标识符 1. 标识符格式 （1）标识符的首字符必须是下列字符之一 。 统一码（Unicode）2.0标准中所定义的字母，包括拉丁字母a-z和A-Z，以及来自其他语言的字符。 下划线“_”、符号“”或者数字符号“#”。 （2）标识符的后续字符可以是以下3种。 统一码（Unicode）2.0标准中所定义的字母。 来自拉丁字母或其他国家/地区脚本的十进制数字。 “”符号、美元符号“$”、数字符号“#”或下划线“_”。 （3）标识符不允许是Transact-SQL的保留字。 （4）不允许嵌入空格或其他特殊字符。,6.1.2 标识符 2标识符分类 SQL Server将标识符分为以下两种类型： 常规标识符：符合标识符的格式规则。 分隔标识符：包含在双引号(“”)或者方括号( )内的标识符。 该标识符可以不符合标识符的格式规则，如MR GZGLXT、MR和GZGLXT之间含有空格，但因为使用了方括号，所以视为分隔标识符。 注意：常规标识符和分隔标识符包含的字符数必须在1128之间，对于本地临时表，标识符最多可以有116个字符。,6.2.2 常量 (1)字符型常量 ASCII字符串常量：用单引号括起来，由ASCII字符组成。 如果在字符常量中已经包含了一个单引号，那么可以使用两个单引号表示这个带单引号的字符。 Unicode字符串常量的格式与ASCII字符串常量相似，但它前面有一个前缀N，而且N前缀必须是大写的。 如：NSQL Server、N张三、N计算机科学与技术。,6.2.2 常量 (2) 数值型常量 数值型常量包含整型常量和实数型常量。 整型常量(Integer)用来表示整数。可细分为二进制整型常量、十六进制整型常量和十进制整型常量。二进制整型常量以数字0或1表示；十六进制整型常量由前缀0 x后跟十六进制数组成；十进制整型常量即不带小数点的十进制数； 实数型常量用来表示带小数部分的数，有定点数和浮点数两种表示方式，其中浮点数使用科学记数法来表示。如：0.3E-5。,(3) 日期时间型常量(datetime) 日期时间型常量使用特定格式的字符日期值来表示，并且用单引号括起来。如2009年4月1日可以用以下方式表示：April 1，2009、04/01/2009或20090401。 (4) 货币型常量(money) 货币型常量以前缀“$”作为标识。如$123.45。,6.2.2 变量,（1）变量名称 在SQL Server 2008系统中，变量的命名规则如下： 第一个字符必须是字母、数字、下画线或符号。需要注意的是，符号“”开头的变量表示局部变量、符号“”开头的变量表示全局变量。 变量名不能是T-SQL语言的系统保留字(如IF、ELSE、CONTINUE等)，包括大写和小写形式。 变量名中不允许出现空格或其他特殊字符。 根据以上规则，下列变量名都是合法的：a2、abc、student_3和average。 变量在使用中需要先声明再使用，声明变量用DECLARE语句，其语法格式如下： DECLARE 变量名称 变量的数据类型 ，n 说明： 为表示局部变量，变量名称的第一个字符必须是 所有变量在声明后均设置初值为NULL,6.2.2 变量,（2）变量赋值 有两种为变量赋值的方式：使用SET语句直接为变量赋值和使用SELECT语句选择表中的值来为变量赋值。 语法格式如下： 格式1：使用SET语句赋值 SET 变量名称=表达式 格式2：使用SELECT语句赋值 SELECT 变量名称=表达式 ，n 说明： 表达式可以是任何有效的SQL表达式； 一个SELECT语句可以给多个变量赋值，而一个SET语句一次只能给一个变量赋值。,例：用赋值语句分别定义两个整型变量x和y。使x的值为20，y的值为5，计算并显示x，y，3x +4y，xy，和x /y的值。 declare x int,y int set x=20 set y=5 select x,y,3*x+4*y,x*y,x/y 例：创建两个局部变量，并赋值，然后输出变量的值。 declare var1 char(4),var2 char(20) set var1=中国 set var2=var1+是一个伟大的国家 select var1,var2 go,示例：创建一个名为sex的局部变量，并在select语句中使用该局部变量查找表student中所有女同学的信息。 use xscj2005 declare xb char(2) set xb=女 select * from student where ssex=xb,例：使用查询给变量赋值。 use XSCJ2005 go declare xm varchar(8) set xm =(select sname from student where sno=2008056103) select xm Go use XSCJ2005 go declare xm varchar(8) select xm =sname from student select xm Go 说明：如果返回多个值，将返回的最后一个值赋给变量,例： use XSCJ2005 go declare xm varchar(8) select xm=刘丰 select xm=sname from student where sno=123456789 select xm as name go,说明：如果select语句没有返回行，变量将保留当前值,6.3 运算符和表达式,SQL Server 2008提供以下几类运算符：算术运算符、关系运算符、逻辑运算符、字符运算符和位运算符。,6.3.1 算术运算符 算术运算符对两个表达式执行数学运算。,算术运算符,例【6-3】：计算6/5、6.0/5.0、5/6、5.0/6.0与6%5的值。 select 6/5,6.0/5.0,0,5.0/6.0,6%5 select 14/5,6.0/5.0,0,5.0/6.0,6%5 select 6.0/5,6.0/5.0,0,5.0/6.0,6%5,通过执行语句我们注意到：6/5的结果为1，而6.0/5.0的结果为1.200000，两者运算结果并不相同。原因在于当两个具有相同数据类型的数进行算术运算时，运算结果依然是当前的数据类型。但是，当两个不同数据类型的数进行算术运算时，数据类型优先级规则指定将优先级较低的数据类型转换为优先级较高的数据类型。这样能够在最大程度上保护运算结果的正确性和合理性。同样，5/6与5.0/6.0的运算结果也不相同。,6.3.2 关系运算符,说明： 两个数值型数据比较时，按照值的大小直接比较； 两个日期时间型数据比较时，按照年、月、日的先后顺序比较； 两个字符型数据比较时，英文字母按照ASCII码值大小比较，汉字按照拼音先后顺序比较。,use XSCJ go declare student char(6) set student=001111 if(student0) select * from xs where 学号=student,6.3.3 逻辑运算符,例：查询成绩高于李明最高成绩的学生姓名、课程名和成绩 select sname,cname,grade from student join sc join course on o=o on student.sno=sc.sno and gradeall (select grade from student join sc join course on o=o on student.sno=sc.sno and sname=李明 ),1)any,all,例：查询成绩高于李明最低成绩的学生姓名、课程名和成绩 select sname,cname,grade from student join sc join course on o=o on student.sno=sc.sno and gradeany (select grade from student join sc join course on o=o on student.sno=sc.sno and sname=李明 ),2）.Between 例：查询91及92年出生的学生信息 select * from student where sbirth between 1991-1-1 and 1992-12-31 例：查询不是91、92年出生的学生信息 select * from student where sbirth not between 1991-1-1 and 1992-12-31,查询姓王或姓刘的学生信息 select * from student where sname like 王刘% 查询不姓王也不姓刘的学生信息 select * from student where sname like 王刘%,3）.Like,查询所有选课的学生信息 select * from student where exists (select * from sc where sno=student.sno) 查询没有选课的学生信息 select * from student where not exists (select * from sc where sno=student.sno),4. exists,6.3.4 字符运算符,字符运算符只有一个“+”，又称为字符串串联运算符。字符串之间通过“+”实现字符串的连接。,select 8899+7788 select 8899+7788 select 8899+7788,说明：当数据类型不相同时，转换成优先级别高的。,6.3.5 位运算符,位运算符在两个表达式之间实现按位操作，这两个表达式应该为整型数据类型或与整型兼容的数据类型（如字符型等，但不能是image类型),6.3.5 位运算符,示例： select 128&129,128|129,128129,6.3.6 运算符的优先顺序 当运算符的级别不同时，先对较高级别的运算符进行运算，然后再对较低级别的运算符进行运算。当一个表达式中运算符的级别相同时，一般按照从左到右的顺序进行运算。当表达式中有括号时，应先对括号内的表达式进行求值；若表达式中有嵌套的括号，则首先对嵌套最深的表达式求值。,例：判断两个数大小 declare x int,y int set x=8 set y=3 if xy print x大于y else print x小于或等于y,6.4 流程控制语句,6.4.2 IF ELSE语句 IF 条件表达式 语句体1 ELSE 语句体2,例：输入一个坐标值，判断其在哪一个象限 declare x int,y int set x=8 set y=-3 if x0 if y0 print xy位于第一象限 else print xy位于第四象限 else if y0 print xy位于第二象限 else print xy位于第三象限,IF.ELSE语句,例【6-6】：设定变量score，根据score的值判断成绩是否合格，并输出结论。 declare score int,a nchar(10) set score=55 if score=60 set a=N成绩合格 else set a=N成绩不合格 select a,1.简单case语句 计算 input_expression，然后按指定顺序对每个when子句的input_expressionwhen_expression进行计算。 返回第一个取值为true的input_expression= when_expression的result_expression. 如果没有取值为true的input_expression= when_expression，则当指定else子句时，sql server将返回else_result_expression；若没有指定else子句时，则返回null值。 2.Case 搜索语句 按指定顺序为每个when子句的Boolean_expression求值。 返回第一个取值为true的Boolean_expression的result_expression。 如果没有取值为true的Boolean_expression，则当指定else子句时，sql server将返回else_result_expression；若没有指定else子句时，则返回null值。,6.4.3 case 语句,-简单Case语句 CASE sex WHEN 1 THEN 男 WHEN 2 THEN 女 ELSE 其他 END -Case搜索语句 CASE WHEN sex = 1 THEN 男 WHEN sex = 2 THEN 女 ELSE 其他 END,简单Case语句只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 -比如说，下面这段SQL，你永远无法得到“第二类”这个结果 CASE WHEN col_1 IN ( a, b) THEN 第一类 WHEN col_1 IN (a) THEN 第二类 ELSE其他 END,select sno,cno,grade, case when grade=90 then 优秀 when grade=80 then 良好 when grade=70 then 中等 when grade=60 then 及格 when grade60 then 不及格 end as 成绩等级 from sc,1.简单Case 语句,6.4.3 case 语句 2.Case 搜索语句 例6-7:,create table temp (score int not null) insert into temp values(60) insert into temp values(30) insert into temp values(90) insert into temp values(106) insert into temp values(87),select score,等级= case when score=60 and score=80 and score=90 and score100 then 优秀 else 数据出界 end from temp,说明:若省略了else子句,则结果为NULL,6.4.3 case 语句 3.利用case语句创建视图 例:,create view v as select 学号,课程号,成绩,等级= case when 成绩=0 and 成绩=60 and 成绩=70 and 成绩=80 and 成绩=90 and 成绩=100 then 优秀 end from sc go,select * from v,6.4.4 goto语句 无条件转移语句，使用goto语句可以将执行流程转移到标签指定的位置。为了与前面的版本兼容，SQL SERVER 2008 支持goto语句，但由于该语句破坏了语句的结构，容易引发不易发现的问题，所以应该尽量减少或避免使用。,declare x int set x=1 loving: print x select x=x+1 while x=3 goto loving,说明：标号是GOTO目标，它不仅仅标识了跳转目标，标号不隔离其前后语句。执行标号前面语句的用户跳过标号并执行标号后的语句。除非标号前面的语句本身是控制流语句（return），这种情况才发生。,declare pjf float if (select COUNT(*)from sc where sno=2007056101)=0 goto label else begin select pjf=AVG(grade) from sc where sno=2007056101 print pjf end label: print 无学生或此学生没有选课！,示例：输出2007056101号学生平均成绩，若此学生没有选课，则显示相应提示信息，用goto完成。,说明：标号是GOTO目标，它不仅仅标识了跳转目标，标号不隔离其前后语句。执行标号前面语句的用户跳过标号并执行标号后的语句。除非标号前面的语句本身是控制流语句（return），这种情况才发生。,6.4.5 whilebreak和continue 语句 1.while 语句,declare s int,num int select s=0,num=1 while num=100 begin set s=s+num set num=num+1 end print 1+2+3+.+100=+convert(char,s) go,declare x int set x=1 while x=3 begin print x select x=x+1 end,6.4.5 whilebreak和continue 语句 2.break 语句 break 语句一般用在while循环或if .else语句中，用于退出本层循环。,例：求1100之间的累加和，当和超过1000时停止累加，显示累加和以及累加到的位置。,declare i int,s int set i=1 set s=0 while i=1000 break set i=i+1 end select s as s,i as i,6.4.5 whilebreak和continue 语句 3.continue 语句 continue 命令可以让程序跳过continue命令之后的语句，回到while循环的第一行命令。Break则让程序完全跳出循环，结束while命令的执行。,例：求110之间的偶数和，并用continue控制语句的输出。,declare x int,sum int set x=1 set sum=0 while x10 begin set x=x+1 if x%2=0 set sum=sum+x else continue print 只有x是偶数才输出这句话 end print sum,6.4.6 waitfor语句 waitfor delay time|time time| 延迟一段时间间隔执行 指定从何时起执行，用于指定触发语句块，存储过程以及事物执行时刻。,waitfor delay 00:00:03 print 葱葱好漂亮！ waitfor time 10:52:00 print 请获得格莱美最高荣誉奖的李小葱发言！,6.4.7 return 语句 return语句用于从过程、批处理或语句块中无条件地退出，位于return语句之后语句将不执行。,use xscj2005 go if exists (select * from course where cname=高等数学) begin print 高等数学课程记录已存在！ return end else insert into course values(1001,高等数学,1005,4),declare x int set x=3 if x0 print 遇到return之前 return print 遇到return之后,SQL SERVER2005新增功能 try.catch语句,Bgein try 语句语句块 End try Begin catch 语句语句块 End catch 类似C+的异常处理，当执行try语句块中的代码出现错误时，系统将把控制传递到catch语句块进行处理。,begin try create database test end try begin catch print test数据库已存在 drop database test print 原有的test数据库已删除 create database test print test数据库再次成功创建 end catch,6.5 函数,6.5.1 数学函数,例【6-8】：使用常用数学函数计算-1的绝对值，e的10次方，5的自然对数，半径为3的圆的面积和49的平方根。 SELECT N-1绝对值=ABS(-1), Ne的10次方=EXP(10), N5的自然对数=LOG(5), N半径为3的圆的面积=PI()*3*3, N49的平方根=SQRT(49),6.5.2 字符串函数,例【6-9】：使用常用字符串函数计算A的ASCII码，SQL Server的前3个字符，数据库原理字符串的长度，将China转换为大写字母，将英语四级改为英语六级。 T-SQL语句如下： SELECT NA的ASCII=ASCII(A), NSQL Server的前3个字符=LEFT(SQL Server,3), N数据库原理的长度=LEN(N数据库原理), N将China转换为大写字母=UPPER(China), N将英语四级改为英语六级=REPLACE(N英语四级,N四,N六),declare s varchar(50) set s=北京,欢迎您! select right(s,4)+left(s,2),将北京,欢迎您!变成欢迎您! 北京,select ASCII(loving) /*返回最左端字符的ascii码值,Len用于返回指定字符串的字符（而不是字节）个数。,select LEN(loving) select len(2011.10.01) select LEN (李小葱生日快乐！),declare s varchar(50) set s=2001年月欢庆北京申奥成功 -变成北京年月申奥成功 select substring(s,11,2)+LEFT(s,8)+RIGHT(s,4),select replicate(loving,3),select REPLACE(SQL SERVER,ER,loving),Replace函数将字符串中的子字符串替换为指定字符串,Replicate函数用于指定的次数重复字符串表达式,select ASCII(loving),ascii函数返回最左端字符的ascii代码值。 纯数字的字符串可以不用单引号，而其他字符表达式必需用,否则出错。,子串替换函数 格式：STUFF（字符表达式1，起始位置，长度，字符表达式2） 功能：用“字符表达式2”的值替换“字符表达式1”中由“起始位置”和“长度”指明的一个子串。 select STUFF(计算机等级考试,4,2,专业) select STUFF(计算机等级考试,6,0,二级),select charindex(l,I like football) 搜索的起始位置是1 select charindex(l,I like football,4) 搜索的起始位置是4 select charindex(l,I like football,15) 搜索的起始位置是15,Charindex函数返回字符串指定表达式的起始位置。,6.5.3 日期和时间函数,Convert CONVERT() 函数是把日期转换为新数据类型的通用函数。 CONVERT() 函数可以用不同的格式显示日期/时间数据。 例如：convert(char(4),year(date1),例【6-10】：使用日期和时间函数显示当前日期，在当前日期后10天的日期，当前日期与2011年1月1日相隔的天数。 T-SQL语句如下： SELECT N显示当前系统日期=GETDATE(), N在当前日期后10天的日期=DATEADD(day,10,GETDATE(), N当前日期与2011年1月1日相隔的天数=DATEDIFF(DAY,GETDATE(),2011-01-01),补充：数据类型转换函数 数据类型转换函数可以将某一种类型的数据转换成另一种类型的数据。 1)数值转换为字符函数 格式：STR(数值表达式 ，长度 ，小数位数) 功能：将数值表达式的值转换为字符串，转换时自动四舍五入。默认长度为10，若不指定小数位则保留到整数位，如果长度小于数值表达式值的整数位数，则返回一串*号。 理想长度=整数+小数点+小数 要求长度理想长度：加前导符 整数长度要求长度理想长度：保留整数部分，调整小数部分。 要求长度整数长度：*,【例】将数值型数据123.456转换为字符型数据，并显示其结果。 解：? STR(123.456) 主屏幕显示结果为： 凵凵凵凵凵凵凵123 ? STR(123.456,7,2), STR(123.456,5,2), STR(123.456,2) 主屏幕显示结果分别为： 凵123.46 123.5 *,select str(123.456),str(123.456,5,2),STR(123.456,2),SQL Server会自动完成数据类型的转换，这种转换称隐式转换。但有些类型就不能自动转换，如int整型与char型，这时就要显式转换函数。（case，convert） 示例 select cast(123 as int)+10 说明：as之后是系统提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。 select CONVERT(nvarchar(30),getdate(),102) +转换成字符型,说明：102是日期样式,6.5.4 聚合函数,avg：返回一组值的平均值。 binary_checksum：返回对表中的行或者表达式列表计算的二进制校验位。 checksum：返回在表中的行或者表达式列表计算的校验值，该函数用于生成哈希索引。 checksum_agg：返回一组值的校验值。 count:返回一组值中项目的数量。(返回值为int类型)。 count_big：返回一组值中项目的数量。(返回值为bigint类型)。 grouping：产生一个附加的列，当用cube或rollup运算符添加行时，附加的列输出为1，当添加的行不是由cube或rollup运算符产生时，附加的列输出为0。 max：返回表达式或者项目中的最大值。 min：返回表达式或者项目中的最小值。 sum：返回表达式中所有项的和，或者只返回distinct值。sum只能用于数字列。 stdev：返回表达式中所有值的统计标准偏差。 stdevp：返回表达式中所有值的统计统计标准偏差。 var：返回表达式中所有值的统计标准方差。 varp：返回表达式中所有值的统计统计标准方差。,【例6-11】avg函数的使用。以下语句统计所有学生成绩的平均值。 use 实例数据库 select avg(分数) as 平均成绩 from 选课表 go 【例6-12】max函数的使用。以下语句返回选课表中学生成绩的最高分数。 use 实例数据库 select max(分数) as 最高成绩 from 选课表 go 【例6-13】count函数的使用。以下语句返回学生表中的记录个数。 use 实例数据库 select count(学号) as 总人数 from 选课表 go,6.5.5 用户自定义函数,用户在编写程序的过程中，除了可以调用系统函数外，还可以根据自己的需要自定义函数。自定义函数包括表值函数和标量值函数两类，其中表值函数又包括内联表值函数和多语句表值函数。 内联表值函数：返回值为可更新表。如果用户自定义函数包含单个SELECT语句且该语句可以更新，则该函数返回的表也可以更新。 多语句表值函数：返回值为不可更新表。如果用户自定义函数包含多个SELECT语句，则该函数返回的表不可更新。 标量函数：返回值为标量值。 用户自定义函数的创建有两种方法: 直接在SQL Server Management Studio中创建。 利用代码进行创建。,create function maxf (para1 real,para2 real) returns real as begin declare max real if para1para2 set max=para1 else set max=para2 return(max) end,1.标量函数,use xscj2005 go create function a(no char(10) returns table as return (select student.学号,姓名,课程名,成绩 from student,sc,course where student.学号=sc.学号 and sc.课程号=course.课程号 and student.学号=no) go select * from a(2008056102),2.内嵌表值函数： 只能通过select语句调用，在调用时可以省略函数的所有者,use xscj2005 go create function b(no char(10) returns score table (sno char(10), sname varchar(8), cname varchar(30), grade tinyint) as begin insert score select student.学号,姓名,课程名,成绩 from student,sc,course where student.学号=sc.学号 and sc.课程号=course.课程号 and student.学号=no return end go select * from b(2008056103),3.多语句表值函数：,print dbo.maxf(12,38.6) select N最大数为:+ convert(varchar(10),dbo.maxf(2.8,15676.9) declare m real exec m=dbo.maxf 12.8,789.6 select m as 最大数为 declare m real exec m=dbo.maxf para1=789.6,para2=12.8 select m as 最大数为,4.自定义函数的调用 调用标量函数，必顺提供至少由两部分组成的名称（所有者.函数名），可用print, select, exec语句调用,内嵌表值函数和多语句表值函数都返回表，二者不同之处在于： 内嵌表值函数没有函数体，返回的表是单个select语句的结果集。 多语句表值函数在begin.end 定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入表中，最后返回表。,