SQL Server 第7次课

《SQL Server 第7次课》由会员分享，可在线阅读，更多相关《SQL Server 第7次课（79页珍藏版）》请在装配图网上搜索。

1、,,,,,,,单击此处编辑母版标题样式,,单击此处编辑母版文本样式,,第二级,,第三级,,第四级,,第五级,,,*,回顾,维护数据库空间的方法有哪些？,,分离和附加数据库是什么意思,?,,为什么要有数据类型的概念？,,存储,0,到,1000,之间的数值，用,bigInt,和,smallint,有什么不同吗？,,Decimal,（,P,，,s,）中的,p,和,s,表示什么意思？,Decimal(2,2)?,,Cast,（,expression as,data_type,）功能是什么？‘,2009-1-1’,是什么数据类型？,,,练习,1,、下述正确的语句是,____,。,,A,、,money,小

2、数点后保留,4,位，而,smallmoney,小数点后保留,2,位。,,B,、,money,的整数部分和,smallmoney,的整数部分范围一致。,,C,、,money,的小数位数和,smallmoney,的小数位数一致。,,D,、,money,类型可以有货币符号，而,smallmoney,类型不可以。,,练习,2,． “图书”表中价格列是一个定点小数，小数点前,3,位，小数点后保留,1,位，正确的类型是,________,。,,A. numeric(3,1) B. numeric(4,1),,C. numeric(5,1) D. floa

3、t,,练习,3,．,Money,数据类型和,smallmoney,数据类型的区别是,_________,。,,A. Money,类型保留到小数位数,4,位，而,smallmoney,保留到小数位数,2,位,,B. Money,类型保留到小数位数,2,位，而,smallmoney,保留到小数位数,4,位,,C.,存放的整数位数相同，小数位数不同,,D.,存放的整数位数不同，小数位数相同,,练习,,4,．用于存放定期执行任务的数据库是,_________,。,,A,、,master,,B,、,model,,C,、,msdb,,,D.,tempdb,,练习,,5,、下述哪个不是,SQL,语言的特征,

4、________,。,,A.,一体化语言,B.,过程化语言,,C.,非过程语言,D.,简洁,,练习,7,．创建完数据库后,___________,。,,A,．可以修改数据文件的逻辑文件名,,B,．可以修改数据文件的物理文件名,,C,．可以修改数据文件的初始大小,,D,．可以修改数据文件的增长方式,,练习,8,．,SmallDatetime,类型精确到哪个时间单位（）。,,A,、小时,,B,、分钟,,C,、秒,,D,、其他,,练习,）用图形化工具建立满足如下要求的数据库：,,（,1,）数据库名字为：,abc,；,,（,2,）此数据库包含两个数据文件，其中主数据文件的逻辑文件名为：,Data1,,

5、物理文件名为,Data1_data.mdf,，初始大小为,3MB,，存放在新建目录下，增长方式为自动增长，每次增加,1MB,，最多增长到,10MB,。辅助数据文件的逻辑文件名为：,Data2,，物理文件名为,Data2_data.ndf,，初始大小为,2MB,，同主数据文件存放在相同的位置，不自动增长。,,（,3,）日志文件的逻辑文件名为：,log1,,物理文件名为,log1_log.ldf,，初始大小为,2MB,，同主数据文件存放在相同的位置，增长方式为自动增长，每次增加,15%,。,,4.2.1.,4,字符串型,字符串是由汉字、英文字母、数字和各种符号组成。用,单引号,括起来。,,普通编码

6、字符串类型,,统一字符编码字符串类型,,二进制字符串类型,,普通编码字符串类型,Char,（,n,）,:,定长存储，,n<=8000,,Varchar,（,n,）：,不定长存储（按实际长度存储），长度最大不超过,n , n<=8000,,注：,n,为字符个数,,Text,：,存储大于,8000,字节的文本,,统一字符编码字符串类型,nchar,（,n,）,:,定长存储，,n<=4000,,nvarchar,（,n,）：,不定长存储，长度最大不超过,n , n<=4000,,ntext,：,存储大于,8000,字节的文本,,特点：每个字符占两个字节,,二进制字符串类型,Binary(n),：,固

7、定长度，,n < 8000,，,占用,n + 4,个字节的存储空间,,Varbinary(n,),：,可变长度，,n < 8000,，,占用实际长度,+ 4,个字节的存储空间,,注：,n,为二进制数据的字节数,,image,：,大容量、可变长二进制字符数据，可用于存储文件,,4.2.2,用户定义数据类型,除了系统提供的数据类型外，用户还可以根据需要定义数据类型。,,用户定义数据类型需要基于,SqL,server,的系统数据类型。,,创建用户定义数据类型,,删除用户定义数据类型,,,,4.3 T-SQL,的语法元素,语句批,,,T-SQL,的使用约定,,保留字,,标识符,,变量,,语句批,,脚本

8、,,4.3.1 T-SQL,的使用约定,1,、语法格式约定,,2,、对象引用的规范,,,对象引用的规范,[,,[,服务器名称,.[,数据库名称,].[,架构名称,].,,|,数据库名称,.[,架构名称,].,,|,架构名称,.,,],,],,对象名,,,架构是包含表、视图、存储过程等数据库对象的容器。,,4.3.3,注释,注释,,有对,SQL,语句进行说明的注释，,注释是不能执行的文字字符串，或暂时禁用的部分语句。,,为程序加注释不仅能使程序易懂，更有助于日后的管理和维护。,,单行注释符：,--,,多行注释符：,/* */,,,4.3.1.3,注释的规范,注释是程序代码

9、中不执行的文本字符串（也称为备注）。,,用于对代码进行说明或暂时禁用正在进行诊断的,T-SQL,语句批。使用注释对代码进行说明，便于将来对程序代码进行维护。,,,注释,1,．行内注释,,行内注释的语法格式为：,,– –,注释文本,2,．块注释,,块注释的语法格式为：,,/*,注释文本*,/,,或：,,/*,,注释文本,,*,/,,举例,use test,,go,,declare @dept varchar(50),,--,存储系部信息,,select @dept=,,case sdept,,when 'cs' then ',计算机系,',,when 'info' then ',信息系,',,w

10、hen 'ma' then ',数学系,',,else ',出错,',,end,,from s,,where sno='001',,--,显示其值,,select @dept,as,系部,,,4.3.2,保留字,保留字是,SQL server,本身使用的词。数据库中的对象名不能使用这些词。,,如果必须使用，必须使用分隔标识符。,,,4.3.3,标识符,标识符是诸如表、视图、列和服务器等对象的名称。,,是在定义时创建的，随后可以被引用。,,分类：,,常规标识符,,分隔标识符,,,常规标识符,是指符合标识符格式规则的标识符。,,规则：,,（,1,）第一个字母必须是下列之一：英文字符,a-z,，,A

11、-Z,；其他语言的字符；下划线,_,；,at,符号,@,；数字符号,#,；,,（,2,）后续包括美元符号,$,以及十进制数字；,,（,3,）标识符不能是,T-,SqL,保留字；,,（,4,）不允许嵌入空格或其他字符。,,分隔标识符,分隔标识符包含在“”或者,[],中。,,符合标识符定义规则的标识符可以分隔，可以不分隔，但是不符合标识符格式规则的标识符必须进行分隔。,,Select * from [My Table] where [order]=10,,,4.3.4,变量,变量是被赋予一定的值的语言元素。,,T-SQL,语言中有两种形式的变量，,,用户自己定义的局部变量；用户用于保存特定类型的单

12、个数据值得对象。,,系统提供的全局变量。全局变量由系统提供且预先声明的变量，用户只能察看不能修改；,,,,,1,、局部变量,局部变量的作用范围仅限制在程序内部。局部变量被引用时要在其名称前加上标志“＠”，而且必须先用,DECLARE,命令定义后才可以使用。。,,声明局部变量,定义局部变量的语法形式如下：,,DECLAER {@,local_variable,,data_type,} […n],,,其中，参数,@,local_variable,用于指定,局部变量,的名称，,,参数,data_type,用于设置局部变量的,数据类型,及其大小,.,data_type,可以是任何由系统提供的或用户定义

13、的数据类型。但是，局部变量不能是,text,，,ntext,,或,image,数据类型,,局部变量,使用,DECLARE,命令声明并创建局部变量之后，会将其初始值设为,NULL,，如果想要设定局部变量的值，必须使用,SELECT,命令或者,SET,命令。其语法形式为：,,,SET { @,local_variable,= expression },,或者,,SELECT { @,local_variable,= expression } [ ,...n ],,其中，参数,@,local_variable,是给其赋值并声明的局部变量，参数,expression,是任何有效的,SQL Serve

14、r,表达式。,,,局部变量,例,1,创建一个,@,myvar,,变量，然后将一个字符串值放在变量中，最后输出,@,myvar,,变量的值。,,程序清单如下：,,,DECLARE @,myvar,char(20),,select @,myvar,= 'This is a test',,SELECT @,myvar,,GO,,,局部变量,例,2,通过查询给变量赋值。,,程序清单如下：,,,USE test,,GO,,DECLARE @rows,int,,SET @rows = (SELECT COUNT(*) FROM sc),,,全局变量,全局变量是,SQL Server,系统内部使用的变

15、量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。,,全局变量通常存储一些,SQL Server,的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是,T-SQL,命令执行后的状态值。,,全局变量,在使用全局变量时应该注意以下几点：,,（,1,）全局变量不是由用户的程序定义的，它们是在服务器级定义的。,,（,2,）用户只能使用预先定义的全局变量。,,（,3,）引用全局变量时，必须以标记符“,@@”,开头。,,（,4,）局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。,,,全局变量,例　显示到当前日期和时间为止试图登录,SQL

16、Server,的次数。,,程序清单如下：,,,SELECT GETDATE( ) AS ',当前的时期和时间,',,,@@CONNECTIONS AS ',试图登录的次数,',,,例题,定义两个局部变量，用他们来显示当前的日期,,本例中给出了两种显示方式：,PRINT,显示在“消息”框，而,SELECT,显示在“网格”框。,,declare @,todayDate,char(10),@dispStr varchar(20),,set @,todayDate,=,getdate,(),,set @,dispStr,=',今天的日期为,:',,print

17、 @,dispstr+@todaydate,,select @,dispstr+@todaydate,,,例题：通过,SELECT,语句来给多个变量赋值。,,declare @name varchar(50),@lianxiren varchar(10),@address varchar(50),,declare @,msgstr,varchar(80),,--,变量赋值,,select @name=,名称,,@,lianxiren,=,联系人,,@address=,地址,,from,供应商信息,,set @,msgstr,=',供应商名,:'+@name+',联系人

18、,:'+@,lianxiren,+',地址,:'+@address,,--,显示信息,,select @,msgstr,,Go,,例题,,例题,declare,@,uName,varchar(10),@uTel varchar(10),@uOrder varchar(10),,declare,@,goodN,varchar(30),@company varchar(30),,declare,@,MsgStr,varchar(80),,select,@,uName,=a.,姓名,,@,uTel,=a.,电话,,@,uOrder,=a.,订货日期,,,,@,goodN,=b.,货品名称,,@Com

19、pany=b.,供应商名称,,,from,客户订单视图,a inner join,货品视图,b on a.,货品编码,=b.,编码,and a.,编号,=2,,set,@,MsgStr,=',客户,'+@,uName,+',联系电话,'+@,uTel,+',于,'+@,uOrder,+',订购了,'+@company+',的,',,+@,goodN,+'.',,print,@,MsgStr,,go,,,局部变量的作用域，只能在声明它的批处理内部。一旦批处理消失，局部变量也将自动消失。,,,Declare,@,dispstr,varchar(20),,Set,@,dispstr,=',这是一

20、个局部变量引用出错的演示,',,Go,,--,批处理在这里结束，局部变量被清除。,,Print,@,dispstr,,Go,例局部变量引用出错的演示,,例：,利用全局变量查看,SQL Server,的版本、当前使用的语言、服务器及服务器名称。,,print ',所用,SQL sever,的版本信息,',,print @@version,,print '',,print ',服务器名称为：,'+@@,servername,,print ',所用的语言为：,'+@@language,,print ',所用的服务为,: '+@@,servicename,,go,例,,4.3.5,语句批,语句批

21、,,,一组,SQL,语句集合,,将批处理的语句变异为一个可执行单元，作为一个执行单元，将执行计划中的语句每次执行一条。,,结束标记：,GO,,,4.3.6,脚本,脚本,,脚本是批处理的存在方式，将一个或多个批处理组织到一起就是一个脚本,。,,存储在文件中的,SQL,语句集合,,这样可以省去重新 编写调试,SQL,语句的麻烦，提高工作效率。这些用于执行某项操作的,T-SQL,语句集合称为脚本。,T-SQL,脚本存储为文件，带有,sql,,扩展名。,,,1.,在查询分析器中保存脚本；,,2.,在企业管理器中创建数据库对象脚本。,,脚本可以在查询分析器中执行，也可以在,ISQL,或,OSQL,实用程

22、序中执行。,查询分析器是编辑、调试和使用脚本的最好环境。,,生成脚本有两种方法：,,生成脚本,,,使用脚本,,,4.4,流程控制语句,1,．语句块,,BEGIN,语句序列,END,,,2.,分枝语句,,IF,条件表达式 语句块,,[ ELSE,语句块,,],,3,．循环语句,,WHILE,条件表达式 语句块,,BEGIN…END,语句块,,BEGIN,和,END,用来定义语句块，必须成对出现。它将多个,SQL,语句括起来，相当于一个单一语句，其语法格式如下。,,,BEGIN,,,语句,1,或语句块,1,,,语句,2,或语句块,2,,…,,END,,,IF…ELSE,语句,IF…ELSE,语句是

23、条件判断语句，其中，,ELSE,子句是可选的，最简单的,IF,语句没有,ELSE,子句部分。,IF…ELSE,语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。,语法格式如下。,,,IF,逻辑表达式,,,{,语句,1,或语句块,1 },,[ELSE,,{,语句,2,或语句块,2} ],,例题,例　如果李四的平均成绩为,60,分以上，显示其成绩情况，否则显示文本：成绩状态不理想。,,declare @avgs int,,select @avgs=avg(grade),,from s,sc,,where s.sno=sc.sno and sname=',李四,',,if(@av

24、gs>=60),,select s.sno,sname,cno,grade,,from s,sc,,where s.sno=sc.sno and sname=',李四,',,else,,print ',成绩不理想,',,,例题,课本,P93,，例题,4-5,,课本,P93,，例题,4-6,,,多分枝语句,CASE,语句可以计算多个条件式，并将其中一个符合条件的结果表达式返回。,CASE,语句按照使用形式的不同，可以分为简单,CASE,语句和搜索,CASE,语句。,,简单,CASE,语句,,搜索,CASE,语句,,,搜索,CASE,表达式,与简单表达式不同的是，搜索表达式中，,CASE,关键字后

25、面不跟任何表达式，在各,WHEN,关键字后面跟的都是逻辑表达式，其语法格式如下。,,搜索,CASE,表达式,,CASE,,WHEN,布尔表达式,1 THEN,结果表达式,1,,WHEN,布尔表达式,2 THEN,结果表达式,2,,…,,[ ELSE,结果表达式,n+1 ],,END,,例 使用简单的,CASE,语句编写。查询客户信息，通过地址中的城市名确定其所属城市。,,select,姓名,,',城市,'=,,case,,when,地址,like '%,深圳,%' then ',深圳人,',,when,地址,like '%,北京,%' then ',北京人,',,when,地址,like

26、 '%,上海,%' then ',上海人,‘,,,else ‘,其它城市人,’,,end,,电话,,from,客户信息,,go,,例题,,特点与执行过程,特点：,,1,、,CASE,关键字的后面没有任何表达式；,,2,、各个,WHEN,关键字的后面跟的都是布尔表达式；,执行过程：,,1,、按从上到下的顺序为每个,WHEN,子句的布尔表达式求值；,,2,、返回第一个取值为,TRUE,的布尔表达式所对应的结果表达式的值。,,如果没有取值，则参照,Else,,例题,例　使用搜索,CASE,，根据成绩的范围将显示其对应系的文本,,use test,,go,,declare @score char(

27、1),,select @score=,,case,,when grade>=90 and grade<=100 then 'A',,when grade>=80 and grade<90 then 'B',,when grade>=70 and grade<80 then 'C',when grade>=60 and grade<70 then 'D',,else 'E',,end,,from sc,,where cno='100' and sno='001',,,select @score,,CASE,示例,1,declare @a,int,,@answer char(10),,set @a

28、=10,,set @answer=case @a,,when 1 then 'A',,when 2 then 'B',,when 3 then 'C',,when 4 then 'D',,else 'others',,end,,print 'is '+ @answer,,CASE,示例,2,declare @answer char(2),@a,int,,,set @a=10,,set @answer =,,case,,when @a>700 then 'A',,when @a>500 then 'B',,when @a>300 then 'C',,else 'D',,end,,print @a

29、nswer,,简单,CASE,表达式,CASE,测试表达式,,WHEN,简单表达式,1,,THEN,结果表达式,1,,WHEN,简单表达式,2,,THEN,结果表达式,2,,…,,[ ELSE,结果表达式,n +1 ],,END,,例：每个销售人员的销售人员订单视图,,，给出各销售人员的业绩等级，这里只以每两个订单进行分级。,,,select,工号,,,姓名,,',业绩等级,'=,,case,订单数,/2,,when 0 then ',初级,',,when 1 then ',中级,',,when 2 then ',高级,',,end,,订单数,,from,销售人员订单视图,,,简单,CASE,

30、表达式,,说明,测试表达式：可以是一个变量名、字段名、函数或子查询；,,简单表达式中不能包含比较运算符，他们给出被比较的表达式或值，其数据类型必须与测试表达式的数据类型相同，或者可以隐式转换为测试表达式的数据类型。,,,简单,CASE,表达式的执行过程,1,、计算测试表达式，然后按从上到下的顺序对每个,when,子句的简单表达式进行计算。,,2,、如果某个简单表达式的值与测试表达式的值匹配，则返回与第一个取值为,TRUE,的,WHEN,相对应的结果表达式的值。,,3,、如果所有的简单表达式的值都不予测试表达式的值匹配，则当指定,Else,子句时，返回,Else,子句中指定的表达式的值；如果没有

31、指定，则返回,NULL,。,,例题,例　根据,sdept,的数据显示其详细内容,,use test,,go,,declare @dept varchar(50),,select @dept=,,case sdept,,when 'cs' then ',计算机系,',,when 'info' then ',信息系,',,when 'ma' then ',数学系,',,else ',出错,',,end,,from s,,where sno='001',,--,显示其值,,select @dept,as,系部,,,While,语句,WHILE…CONTINUE…BREAK,语句用于设置重复执行,SQ

32、L,语句或语句块的条件。只要指定的条件为真，就重复执行语句。其中，,CONTINUE,语句可以使程序跳过,CONTINUE,语句后面的语句，回到,WHILE,循环的第一行命令。,BREAK,语句则使程序完全跳出循环，结束,WHILE,语句的执行。,,WHILE,语句的语法形式,WHILE Boolean_expression    {,sql_statement,|,statement_block,}    [ BREAK ]    {,sql_statement,|,statement_block,}    [ CONTINUE ],,计算,1,到,10,之间的奇数之和,,decla

33、re @i tinyint,@sum int,,set @sum=0,,set @i=0,,while @i>=0,,begin,,if(@i>=10),,begin,,select ',总和,'=@sum,,break,,end,,else,,begin,,set @i=@i+1,,if(@i % 2) = 0,,continue,,else,,set @sum=@sum+@i,,end,,end,,,示例,:,计算,1+2+3+…,＋,100,的和,DECLARE @i,tinyint,, @sum,smallint,,SET @sum = 0,,SET @i = 1,,While (@i

34、 <= 100 ),,Begin,,SET @sum = @sum + @i,,SET @i = @i + 1,,end,,print @sum,,1,．,RETURN,语句,,,,RETURN,语句实现无条件退出执行的批处理命令、存储过程或触发器。,其他语句,,,,GOTO,语句是无条件转移语句，语法格式为：,,,,GOTO,标号,2,．,GOTO,语句,,GOTO,语句,,GOTO,语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于,GOTO,语句和标识符之间的程序将不会被执行。,GOTO,语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以

35、“,: ”,结尾。如：‘,a1: ’,。在,GOTO,语句行，标识符后面不用跟“,: ”,。,GOTO,语句的语法形式为：,,GOTO label,,……,,label:,,,GOTO,语句,,例,7-57,利用,GOTO,语句求出从,1,加到,5,的总和。,,程序清单如下：,,declare @sum,int,, @count,int,,select @sum=0, @count=1,,label_1:,,select @sum=@,sum+@count,,select @count=@count+1,,if @count<=5,,goto,label_1,,s

36、elect @count @sum,,3,．,RAISERROR,,RAISERROR,语句通常用在错误处理中，它既可在屏幕上显示用户的信息，又可将错误号保存在,@@ERROR,全局变量中，以备错误处理时使用。其语法为：,,,RAISERROR ( {,消息标识,|,消息串,} { ,,错误等级,,,状态,},,[ ,,参数,[ ,...n ] ] ),,[ WITH,选项,[ ,...n ] ],RAISERROR,语句,,,,4.5,常用内置函数,,聚合函数,,日期和时间函数,,数学函数,,字符串函数,,类型转换函数,,系统函数,,,一 聚合函数,,1.,AVG,：,返回组中值的平

37、均值。空值将被忽略。,,2.,COUNT,：,返回组中项目的数量,,3.,COUNT_BIG,：,返回组中项目的数量。,COUNT_BIG,的使用与,COUNT,函数相似。它们之间的唯一差别是它们的返回值：,COUNT_BIG,总是返回,bigint,,数据类型值，而,COUNT,则总是返回,int,,数据类型值。,,4.,MAX,：,返回表达式的最大值。,,5.,MIN,：,返回表达式的最小值。,,一 聚合函数（续）,,6.,SUM,：,返回表达式中所有值的和。,SUM,只能用于数字列。空值将被忽略。,,7.,STDEV,：,返回给定表达式中所有值的统计标准偏差。,,8.,VAR,：,返回给

38、定表达式中所有值的统计方差。,,示例,,例,1.,返回,titles,表中所有版税（,royalty,）,费用的标准偏差。,,,USE pubs,,SELECT STDEV(royalty),,FROM titles,,示例,,例,2.,返回,titles,表中所有版税（,royalty,）,值的方差。,,,USE pubs,,SELECT VAR(royalty),,FROM titles,,二,.,日期和时间函数,,这些函数对日期和时间输入值执行操作，并返回一个字符串、数字值或日期和时间值。,,,1. GETDATE ( ),,返回类型：,datetime,,,示例,,A.,用,GET D

39、ATE,返回当前日期和时间,,,SELECT GETDATE(),,,,B.,在,CREATE TABLE,语句中使用,GETDATE ( ),作为列的默认值,,2,．,DATEADD,,在向指定日期加上一段时间的基础上，返回新的,datetime,,值。,,语法：,DATEADD (,datepart,, number, date ),,Datepart,的形式,,日期部分 缩写 日期部分 缩写,,Year,yy,,,yyyy,Week wk,,ww,,,quarter,qq,, q Hour,hh,,,Month mm, m minute mi, n,,dayofyear,,dy,, y second,ss,, s,,Day,dd,, d millisecond ms,,示例,,查询,pubs,数据库中当前发布日期加上,21,天后的日期。,,SELECT DATEADD(day, 21,,pubdate,) AS timeframe,,FROM titles,,