我们之前讲过,SQL是一种结构化(Structured)的语言(Language),所以它也必然具有以下要素:
变量(复习)
可以把变量看成一个存储数据的容器。使用变量必须首先声明(创建),然后再赋值(讲数据装入这个容器中) 。
DECLARE
也可以一次性声明多个变量:
DECLARE
还可以在声明变量的同时给他赋值
DECLARE
注意,SQL中的变量必须以@开头,以一个@开头的变量被称为局部变量。所谓“局部”,是指他的作用域(也就是能够产生作用、有限的区域)是局部的,而不是全局的。具体来说,局部变量的作用域位于一个批处理语句中。
SQL Server中还有一种变量,是使用@@开头的,被称之为全局变量。全局变量可以不受批处理的约束,可以在任何地方直接使用。但用户不能定义全局变量,全局变量只能由SQL SERVER定义。
但我就是要定义一个全局变量呢?比如:
DECLARE
SQL Server会将其作为一个名为@name(不是name)的局部变量使用。
演示:局部变量和全局变量的区别
常用的全局变量有:
PRINT
数据类型和运算符(复习)
分支和循环
分支使用的只有两个关键字:IF和ELSE(复习)
猜一猜这段代码的结果:
DECLARE
如果改成@age<18呢?=18呢?
为什么呢?因为@age声明过后没有被赋值,它的值为NULL;而NULL值和任何值进行任何比较,结果都为假。
通常我们在使用IF...ELSE...时,会配合使用BEGIN...END,以界定IF或ELSE的范围。
SQL只有一个循环关键字:WHILE
WHILE后面接条件,只要条件为真,就会一直循环执行WHILE引导(通常由BEGIN...END界定)的循环体语句。循环无法终止,就是“死循环”,这是一定要注意避免出现的。所以在WHILE的循环体中,通常都会设置语句,使得一定条件下WHILE条件为假,以结束循环。比如:
DECLARE
运行上述SQL语句,会输入1,2,3,4,5。
使用这些分支循环,就可以构建大量的、复杂的SQL语句,这些SQL语句需要被有效的管理,函数应运而生。(复习:人人都是程序猿)
SQL Server中函数的最大特点: 必须有一个返回值 。根据返回值类型,我们可以将其分为:
标量函数:返回一个简单类型的单一值。
其函数声明的SQL代码如下:
CREATE
注意:
- 函数不属于任何一张表,而属于整个数据库,所以函数名在整个数据库中不能重复
- 函数的命名规则见:,
- 函数的参数和变量声明一样,必须用@开头,且指明类型
- 标明函数返回类型时使用的关键字是RETURNS,带了一个S;函数体内标明返回值时,使用的是RETURN,没有S
- BEGIN和END是不能省略的
- 函数体中:
- 不能对数据库中的数据进行修改,比如使用INSERT/UPDATE/DELETE等
- RETURN语句只能在函数体的最后
声明函数之后,运行,就可以在SQL Server Object Explorer中看到这个函数了:
可以看到,YzAdd被添加到:数据库17bang -> Programmability -> Functions -> Scalare-valued Functions下面,而且被自动的添加了 dbo 的schema。
然后,我们就可以调用它了:
PRINT
注意:Scalar函数必须指定schema_name(这里是dbo)
函数还可以被 修改,这需要使用 ALTER 关键字。我们可以使用ALTER,把上述YzAdd函数的一个参数设定一个默认值:
ALTER
运行上述SQL语句,函数就会被修改,在SQL Server Object Explorer中查看:
@b已经变成了Default(区别于@a的No default)。
设置了默认值的参数,可以用两种方式调用:
PRINT
此外,函数还可以被加密,以免被其他用户看到函数的具体实现。未加密的函数,在函数上右键点击 View Code,就能查看到函数的“源代码”
但是,如果创建(或修改)函数时添加了WITH ENCRYPTION:
ALTER
再去View Code,就会发现:无法查看到函数体(函数的定义,如名称、参数、返回值等还是可以看到的)
CREATE
SQL函数的另一个特殊性,就表现在它的返回值可以是“表”,这种函数被称之为:表值函数。注意这里所称的表,实际上是一种具有表结构的数据,它和使用CREATE Table生成的表不一样:数据库中并没有这样一张表,这张“表”是通过函数运算而“临时”获得的。
表值函数又可以被细分为:
单行表值函数(Inline Function)
其SQL语句如下:
CREATE
除了RETURNS后直接接TABLEG关键字,这种函数的特点是:函数体只能有一句SELECT语句,且不能由BEGIN...END包裹。其他(如DEFAULT参数和WITH ENCRYTION)都和标量函数一样。
调用YzInlineTable函数,会返回一个“表”,所以,我们也要像表一样使用它:
--不是:PRINT YzInlineTable(2),而是:
注意:调用表值函数时可以不需要使用schema,¯_(ツ)_/¯
如果函数中需要封装更复杂的逻辑,我们就只有使用多行表值函数(Multi-Statement Function)了。它同样是返回一个表,但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体,返回值的数据是由函数体中的语句插入的。
SQL代码及语法代码如下所示:
CREATE
其实,随着软件工程的发展,让数据库承担“增删改查”的单一职能,越来越成为主流。利用T-SQL生成函数封装业务逻辑的情形越来越少。目前我们经常使用的是:
系统函数
即由SQL Serer系统内置的、已经定义好了函数。包括:
聚合函数:分组(GROUP BY)查询统计时使用 (复习)
数值函数: 对数值进行运算,以下用value表示需传入的、被运算数值参数
- 四舍五入:ROUND(value, n) ,把小数value舍入为n位小数位数。比如:ROUND(9.865, 2),结果值为9.870
- 取整:
- FLOOR(value),向下取整 ,比如:FLOOR(3.8),结果为3;
- CEILING(value),向上取整,比如:CEILING(3.8),结果为4
- 随机:
- RAND(value),获得一个以value为“种子(seed)”的小于1的随机小数。(注意:value固定,生成的随机值也就是固定的)
- RAND(),获得一个以系统当前时间为种子的随机小数;
文本函数: 对字符串进行运算,以下用value表示需传入的、被运算字符串参数
- 大小写:
- LOWER(value) :将value转变成小写,比如:LOWER('AbC'),结果为'abc'
- UPPER(value):将value转变成大写,比如:UPPER('AbC'),结果为'ABC'
- 修剪,删除空格
- LTRIM(value):删除value中左边开头的空格,比如:LTRIM(' AB C '),结果为'AB C '
- RTRIM(value) :删除value中右边开头的空格,比如:RTRIM(' AB C '),结果为' AB C'
- 取长度:LEN(value),取value的长度,但注意value末尾的空格不会被计入长度,即:
' AB C '和' AB C'的长度是一样的,都是9(左边有5个空格,AB和C中间也有一个空格) - 截取:
- LEFT(value, n) ,从左向右截取value的n位字符,比如:LEFT(N'源栈欢迎您',2),结果为'源栈'
- RIGHT(value, n) ,从右向左截取value的n位字符,比如:RIGHT(N'源栈欢迎您',3),结果为'欢迎您'
- SUBSTRING(value, start, n) ,从start开始,从左向右截取value的n个字符,比如:SUBSTRING(N'源栈欢迎您',3,2),结果为'欢迎'
- 查找:CHARINDEX(target, value, n) ,在value中从第n位字符开始查找target,
- 找到返回target在string中的起始位置,比如:CHARINDEX(N'欢迎', N'源栈欢迎您', 1) ,结果为3
- 找不到返回0,比如:CHARINDEX(N'欢迎', N'源栈欢迎您', 4)
- 替换:REPLACE(value, target, substitute) 在string找到target,用substitute替换它。比如: REPLACE(N'源栈欢迎您', N'您', N'英俊的你'),结果为'源栈欢迎英俊的你'
- 重复:REPLICATE(value, n) ,将value重复n遍,比如:REPLICATE(N'源栈欢迎您', 2),结果为'源栈欢迎您源栈欢迎您'
- 空格填充:SPACE(n) ,生成n个空格,比如:SPACE(10),结果为10个空格
日期函数: 对日期进行运算,以下用value表示需传入的、会被运算的日期参数
- 当前时间:GETDATE(),获取当前时间
- 年月日:获取日期的年/月/日
- YEAR(value):value上的年份,比如:YEAR('2019/10/21'),结果为:2019
- MONTH(value):value上的月份,比如:MONTH('2019/10/21'),结果为:10
- DAY(value) :value上的日期,比如:DAY('2019/10/21'),结果为:21
- 增加:DATEADD(datetype,n,value):在value日期的基础上增加n个datetype单位的时间。datetype可以为:YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND。比如:DATEADD(YEAR, 1, '2018/10/21')表示在2018年10月21日的基础上再增加1年,结果为'2019/10/21';n值也可以为负数,表示减少,比如DATEADD(YEAR, -1, '2018/10/21'),结果为'2017/10/21'。
- 计算时间差:DATEDIFF(datetype, start, end),比较start和end两个日期之间相差多少个datetype单位的时间。比如:DATEDIFF(DAY, '2018/10/21', '2018/12/2')表示计算'2018/12/2'比'2018/10/21'多了多少天,结果为42
- 截取:DATEPART(datetype,value),截取value中的datetype,比如DATEPART(HOUR, '2018/12/2 23:11')就是截取'2018/12/2 23:11'中的小时数,结果为23
类型转换函数:
T-SQL是一种强类型语言,不兼容类型的数据之间不能进行运算;兼容的类型进行运算,也需要进行类型转换。转换分为两种,一种是隐式(implicit)转换,即SQL Server自动的为你进行的转换,比如:
DECLARE
隐式转换不需要我们使用任何类型转换函数,很方便,但规则都是T-SQL预定义的(参考:类型优先级)。比如:
PRINT
如果我们想将27.83转换成整数再运行呢?这就需要使用函数:CAST(value AS datatype) 或 CONVERT(datatype, value)进行显式(explicit)转换
PRINT
作业:
- 利用循环,打印如下所示的等腰三角形:
- 定义一个函数GetBigger(INT a, INT b),可以取a和b之间的更大的一个值
- 创建一个单行表值函数GetLatestPublish(INT n),返回最近发布的n篇求助
- 创建一个多行表值函数GetByReward(INT n, BIT asc),如果asc为1,返回悬赏最少的n位同学;否则,返回悬赏最多的n位同学。
- 在表TProblem中:
- 找出所有周末发布的求助(添加CreateTime列,如果还没有的话)
- 找出每个作者所有求助悬赏的平均值,精确到小数点后两位
- 有一些标题以test、[test]后者Test-开头的求助,找到他们并把这些前缀都换成大写
每日单词:
感谢童鞋们的阅读!^_^
我就是:黑律师/包工头/创业狗/老码农……现在还是教书匠的大飞哥。
再次重申这个系列的目标是:
1)通俗易懂。2)实战为主。3)面向就业。
系列内容的完善需要你的反馈!
欢迎点赞和评论,以及加入我们的QQ交流群:326801052。