SQLServer_第6章 T-SQL语言和函数

第6章 T-SQL语言

T-sql语言由以下几部分组成：

（1）数据定义语言DDL；  create alter  drop

（2）数据操纵语言DML；  select  update insert delect

（3）数据控制语言DCL；   grant  revoke  deny

（4）T-SQL增加的语言元素

6.1 变量、常量与数据类型

6.1.1 常量

1．字符串常量  ASCII码字符串常量和Unicode字符串常量

ASCII字符串常量是用单引号括起来，由ASCII字符构成的字符串；

Unicode字符串前面使用大写的N来定义，如N ‘hello,T-sql’

在字符串里面，可以使用两个单引号表示嵌入的单引号。

2．整型常量

二进制整型常量                  由数字0/1组成的串，不能用‘‘修饰

十六进制整型常量           由0x开头

十进制整型常量              

3．实型常量

（1）定点表示                   4.235

（2）浮点表示                   101.2E1

4．日期时间常量                用单引号将表示日期时间的字符串括起来构成。

通常有如下的格式：

（1）字母日期型             ‘April20,2000’

（2）数字日期型         ‘ 4/15/2002 ’

（3）未分隔的字符串格式  ‘20000415’

如：

‘14:30: 24’    ’04:30:24:pm’

5．Money常量                   -$1234.32   $879

6．Uniqueidentifier常量       使用字符或十六进制字符串格式指定

6.1.2 数据类型

1．系统数据类型

2．自定义数据类型

       创建用户自定义数据类型时首先要考虑：

（1）       名称

（2）       基类型

（3）       为空性

可以使用企业管理器自定义数据类型，也可以使用系统存储过程定义数据类型。如：

       Sp_addtype  [@typename] type,

                            [@phystype] system_data_type

                            [,[@nulltype=] null_type]

                            [,[@owner=] owner_name]

如：exec sp_addtype 'student_num','char(6)','not null'

可以使用企业管理器删除，也可以使用命令 sp_droptype [@typename=]type

6.1.3 变量

1．标识符

（1）常规标识符：以字母、下划线、@ 、#开头。

（2）分隔标识符：包含在“”或【】内的常规标识符或不符合常规标识符规则的标识符。

2．变量的分类

（1）全局变量  全局变量由系统提供，通过在名称前加两个“@”符号区别于局部变量；用＃＃标识一个全局数据库对象名。

（2）局部变量  通常用@标识局部变量名，用＃标识为一个临时数据库对象名。

3．局部变量的使用

（1） 定义

Delcare {@local_variable data_type} [,….n]

（2）赋值

用set语句赋值：Set @ local_variable=expression

用select语句赋值：

例子：

declare @sex bit

set @sex=0

select * from xs

where stu_sex=@sex

例子：

declare @stu_name char(8)

set @stu_name=(select  top 1 stu_name from xs)

注意：使用select语句赋值需要说明几点：

（1）如果expression返回多个值，则取最后一个返回值；

（2）如果select语句没有返回值，则保留原值；

（3）如果expression是不返回值的标量子查询，则变量值为Null；

（4）一个select语句可以初始化多个变量

例子：

declare @var1 nvarchar(30)

select @var1='lifeng'

select @var1=stu_name from xs where stu_id='001104'   /*如果没有该记录*/

select @var1 as 'name'  /*显示其值为'lifeng'*/ 

例子：

declare @var1 nvarchar(30)

select @var1='lifeng'

select @var1=(select stu_name from xs where stu_id='001104')

select @var1 as 'name'

4．局部游标变量的定义与赋值

（1）定义

Declare {@cursor_variable_name cursor}[,…n]

（2）赋值

l           将一个已经存在的并且赋值的游标变量赋给另一个游标变量；

l           将一个已声明的游标名赋给指定的局部游标变量；

l           声明一个游标，同时将其赋给指定的局部游标变量。

Set {@cursor_variable_name={cursor_variable| cursor_name | cursor 子句}}

例子：/*使用游标变量*/

use xscj

declare @cursorVar cursor

set @cursorVar =cursor scroll dynamic

       for

       select * from xs

       where stu_name like '王%'

open @cursorVar

fetch next from @cursorVar

while @@fetch_status=0

       begin

       fetch next from @cursorVar

       end

close @cursorVar

deallocate @cursorVar

6.2 运算符和表达式

6.2.1 算术运算符  ＋ - * / ％

6.2.2 位运算符           & | ^

6.2.3 比较运算符

6.2.4 逻辑运算符 

and or not all any between exists in like some

6.2.5 字符串连接符 ＋

6.2.6 一元运算符 + - ～

6.2.7 赋值运算符  ＝

6.3 流程控制语句

6.3.1 if else

6.3.2 goto

6.3.4 while（begin end）、break、continue

6.3.5 return

备注：1）通常情况下，系统存储过程返回0表示执行成功，非0值表示失败；

2）当用于存储过程时，return不能返回空值

例子：/*检查学生的平均成绩，如果>=75，返回1，否则返回2*/

create procedure checkavg @param varchar(10)

as

       if(select avg(stu_score) from xs_kc where stu_id=@param)>=75

              return 1

       else

              return 2

6.3.6 waitfor

指定触发器、存储过程或事务执行的时刻或需要等待的时间。

语法格式：waitfor {delay ‘time’ | time ‘time’}.

说明：执行waitfor语句后，在到达指定时间之前将无法使用与SqlServer的连接。若要查看活动的进程和正在等待的进程，可以使用exec sp_who。

例子：/*设定在8：00执行存储过程Manager*/

Begin

       Waitfor time ‘8: 00’

       Execute sp_addrole ‘manager’

End

6.4 系统内置函数

6.4.1 行集函数

1） containstable

2） freetexttable

3） openquery

4） openrowset

6.4.2 聚合函数

1） select statement

2)  compute  / compute by

3)  having

6.4.3 标量函数

1） 配置函数

2）数学函数 abs/acos/rand

3）字符串处理函数 

ascii ：指定字符串最左边字符的Ascii码值，返回值为整型；

char：将ASCII码值转为字符；

left ：返回从字符串左边开始指定个数的字符；

ltrim ：删除前导空格；

replace（‘string1’，‘string2’，‘string3’）：在string1中由string3替换string2的内容；

substring（expression，start，length）：返回指定位置的数据

str（float_expression,length,decimal）：将数字数据转换为字符数据。

4）系统函数

l           Case

Case

       When   then

       Else

End

l           Cast (expression as data_type)

l           Convert （data_type[(length)],expression[,style]）

例子：

/*检索总学分30～39的学生姓名，并将总学分转换为char（20)*/

select stu_name,stu_total_credit from xs

where cast (stu_total_credit as char(20)) like '3_' and stu_total_credit>=30

或

select stu_name,stu_total_credit from xs

where convert(char(20),stu_total_credit) like '3_' and stu_total_credit>=30

l           Coalesce  ：返回列表中第一个非空表示式的值

5） 日期时间函数 getdate（）

6）游标函数

l           @@cursor_rows

l           @@cursor_status

l           @@fetch_status

例子：/*用fetch_status控制在一个while中的循环*/

declare @name char(20),@num char(10)

declare Student_cursor cursor

       for

       select stu_name,stu_id from xs

open Student_cursor

fetch next from student_cursor into @name,@num

while @@fetch_status=0

       begin

              select @name,@num

              fetch next from student_cursor into @name,@num

              end

close student_cursor

deallocate student_cursor

7）元数据函数

db_id

db_name

6.5 用户定义的函数

用户自己定义的函数可以分为如下三个类别：

（1）       返回值为可更新表的函数，这样的函数称为内嵌表值函数；

（2）       返回不可更新表的函数，这样的函数称为多语句表值函数；

（3）       返回标量值的函数

注意：用户自定义函数不支持输出函数。

6.5.1 标量函数的定义

Create function owname_name, function_name

(@parameter_name as data_type,….)

Returns data_type

[with encryption | schemabinding.]

[as]

Begin

       …

End

例子：/*计算全体学生某们功课的平均成绩*/

create function average

(@cnum char(20))

returns int

as

begin      

       declare @aver int

       select @aver=

       (select avg(score) from xs_kc where kc_id=@cnum group by kc_id)

       return @aver

end

6.5.2标量函数的调用

（1）在select语句中调用

例子：/*在select语句中调用*/

declare @course1 char(20)

declare @aver1 int

select @course1='101'

select @aver1=dbo.average(@course1)

select @aver1 as '101课程的平均成绩'

（2）利用EXEC语句执行

例子：/*exec语句中调用*/

declare @course1 char(20)

declare @aver1 int

select @course1='101'

exec @aver1=dbo.average  @course1

select @aver1 as '101课程的平均成绩'

例子：/*新建course表*/

use xscj

create table course

(cno int

,cname nchar(20)

,credit int

,aver as (dbo.average(cno))

)

6.5.3 内嵌表值函数

内嵌表值函数可用于实现参数化视图。

/*内嵌表值函数的定义*/

create function fn_view1

(@para nvarchar(30))

returns table

as

return

       (

       select stu_id,stu_name from xscj.dbo.xs where stu_major=@para

       )

/*内嵌表值函数的调用*/

select * from fn_view1(N'计算机')

6.5.4 多语句表值函数

内嵌表值函数和多语句表值函数都返回表，二者不同之处在于：内嵌表值函数没有函数主体，返回的表是单个select语句的结果集；而多语句表值函数在begin…end块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入至表中，最后返回表。

例子：/*创建返回table的函数，通过学号作为实参调用该函数，可显示该学生各门功课的成绩和学分*/

Create function score_table

(@student_id char(6))

returns @score table

(xs_id char(6)

,xs_name char(8)

,kc_name char(16)

,cj tinyint

,xf tinyint

)

as

begin

       insert @score

       select s.stu_id,s.stu_name,p.kc_name,p.kc_credit,o.score

       from xs as s

       inner join xs_kc as o on (s.stu_id=o.stu_id)

       inner join kc as p on (o.kc_id=p.kc_id)

       where s.stu_id=@student_id

       return

end

/*多语句表值函数的调用*/

select * from score_table('001103')

6.5.5 函数定义的修改和删除

使用命令alter function function_name  和drop  function  function_name 命名。