第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 命名。