数据库库设计之数据库行为特征设计-SQL程序设计（函数和表达式）

• 表达式

(一)各种类型的数据运算及数据类型的隐式转换

(1)数值和字符串混合运算

select 100+'200'    输出：300    字符串被转换成数值

select 100+'A200'    输出：错误    不能转换

(2)日期和数值混合运算

日期和数值混合运算将得到的日期向前或向后的推移n天后的日期

select getdate()+365    输出为明年与今日同月同日的日期

(3)日期相减和字符串转化为日期

日期相减结果仍为日期

首先计算两个日期之间的天数n，同一天日期相减后的天数n=1，然后在固定日期1900-01-01加上n-1天后的日期就是日期相减的结果

select getdate()-'2020-5-7'    字符串自动转化为日期

结果：1900-01-02 23:36:31.030

(4)日期转化为数值

只能使用显示转换，1900-01-01被转化为0，其他日期被转化为1900-01-01到该日期的天数

select CAST(getdate() as int)

结果：43958

(5)日期转换成字符串

在函数的参数中，参数类型为字符串，而实际代入的为日期型表达式

select LEFT(GETDATE(),2)

结果：05  即当前月份

(二)表达式和空值

当表达式中存在空值，则表达式值为空

当条件表达式中出现空值，则条件表达式值为false

例：

if（X=NULL）、if（3<>NULL）、if（NULL=NULL）条件判断均为false

所以，在判断表达式是否为空时，必须使用IS及IS NOT

例：

if（X IS NULL）、if（X IS NOT NULL）

(三)CASE表达式

１.格式一：

case　表达式０

when 表达式i then 结果表达式i

[···n]

[else 表达式n+1]

end

例：

SELECT StdId,StdName,
case StdSex
when 1 then '男'
when 0 then '女'
else '未知'
end
from Student

2。格式二：

case 

when 逻辑表达式i then 结果表达式i

[···n]

[else 表达式n+1]

end

case表达式依次判断每个when后的逻辑表达式i是否为真，若为真返回then后的表达式i的值若均为假返回else后的表达式n+1的值，若无else子句返回NULL值

例：

select a.StdId,a.StdName,b.EleName,      注意不要缺省逗号
Grade=case                                              Grade=case，不是case=Grade
when c.Grade<=100 and c.Grade>=90 then '优'
when c.Grade<90 and c.Grade>=80 then '良'
when c.Grade<80 and c.Grade>=70 then '中'
when c.Grade<70 and c.Grade>=60 then '差'
else '不及格'
end
from Student a,Elective b,Student_Elevtive c
where a.StdId=c.StdId and b.EleId=c.EleId

“Grade=”的作用是使该查询结果中成绩的列名显示为Grade，若没有“Grade=”则显示无列名

• 函数

1.常用函数

• 日期型函数

getdate():返回当前系统的日期和时间（返回datetime类型），无参数

year(date):参数类型为datetime,返回参数date的年份（int型），相关函数有month(date)、day(date)

datediff(detepart,startdate,enddate):返回开始日期startdate到结束日期enddate之间的depart（年、季、月、周等）指定的时间单位的个数（int型）

dateadd（depart,number,date）:获取date指定日期加或减number个depart指定的日期或时间周期后得到的日期

datepart(datepart,date):可获取date对应的年份、季度、月份、星期等，当depart为weekday时，返回值为1-7，分别对应周日到周六

• 字符类型函数

left(character_experssion,integer_expression):返回字符串character_experssion左边由integer_experssion指定字符数的字符串（varchar类型），相关函数right(character_experssion,integer_experssion)

replace(string_ex1,string_ex2,string_ex3):用字符串string_ex3替换string_ex1中所有出现string_ex2的字串

charindex(ex1,ex2[,start_location]):从ex2表达式的start_location位置处开始搜索ex1表达式的子字符串，找到则返回字符串的位置，否则返回0

substring(experssion,start,length):返回字符串参数experssion从start位置开始处长度为length的字串

len(string_experssion):返回给定字符串表达式的字符（不是字节），不包括尾随空格

replicate(character_ex,integer_ex):把integer_ex个charater_ex参数表示的字符串串接起来返回

ltrim/ritim(charater_ex):去除字符串参数charater_ex表示的字符串的左/右边的全部空格后返回

• 类型转换和其他函数

convert(data_type[(length)],experssion[,style]):把表达式experssion转换成第一个参数指定的数据类型

str(float_expr[,length[,decimal]]):把数字表达式float_expr转换成字符串，总长度为length包括小数点，小数长度为demical

power(numeric_ex,y):返回numeric_ex的n次方

isnull(check_ex,replacement_value):若check_ex为空，则返回replacement_value的值

• 自定义函数——标量函数

语句：

create function 函数名

             ([{@参数名 参数类型[=默认值]} [,···n]])

returns 返回类型

[with <函数选项>]

[as]

begin

        函数体(其中必须包含return 语句)

end

其中，函数选项包括encryption和schemabinding可选一或全选，之间用",分割"

encryption：加密该函数体中的内容，使用任何工具都无法看到函数体程序

schemabinding：将函数绑定到它所引用的数据库对象，即不能更改或移除该函数所引用的数据库对象

函数一但创建就永久存在，除非使用 drop function删除

例：

要求：每个单词第一个字母大写，若两个单词间出现多于一个空格，则去除多余空格

create function FormatStr(@SourceStr varchar(100))
returns varchar(100)
as
begin
declare @c char(1),@LC char(1),@I int,@TargeStr varchar(100)
set @I=1
set @LC=' '        --空格
set @TargeStr=''    --null
while @I<=LEN(@SourceStr)
    begin
        set @c=SUBSTRING(@SourceStr,@I,1)        --substring(experssion,start,length):返回字符串参数experssion从start位置开始处长度为length的字串
        if(@c<>' ')
            if @LC=' '
                if(ASCII(@c)>=97 and ASCII(@c)<=122)
                set @c=UPPER(@c)
            if(@c<>' 'or @LC<>' ')
            set @TargeStr=@TargeStr+@c
            set @I=@I+1
            set @LC=@c
        end
        return @TargeStr
        end

执行：select  dbo.FormatStr('my     name is why')

输出：My Name Is Why