《SQL必知必会》--函数

第八课 使用函数处理数据

在本节课开始之前，我们首先大致回顾一下目前为止，我们所学过的知识及其内在逻辑。

首先我们知道，本文的初始点，并不是如何从无到有建立一个新的数据库。而是，默认我们目前已经有了一个数据库。

在有了数据库之后，我们首先需要知道，数据库里有什么？即数据检索（SELECT语句，LIMIT语句，ORDER语句）和数据过滤（WHERE子句、组合过滤、通配符过滤）。

在了解数据库里有什么之后，从第七课数据拼接与执行算术计算开始，就已经进入了数据处理的世界。

本课就进一步介绍SQL语言中处理数据的一项便利但又不便利的工具–函数。

函数

如果，你之前学过其他计算机语言，那么你对计算机中的函数概念想必也有一些了解。

在特定的DBMS中，函数为数据处理带来的便利性是毋庸置疑 的。

但是，目前存在的最大的问题就在于，虽然SQL语句被几乎所有的DBMS都等同地支持，但是，却仅有相当少的一部分SQL函数也享有相同的待遇。

各大厂商在基本的SQL语句和函数的基础上各自设计出不同的函数以便利DBMS客户的使用。最终的结果就是，明明具有相同功能的函数，在不同的DBMS中，却有不同的命名。因此大部分SQL函数代码，不可移植。

在下面，我们将介绍集中可移植的SQL函数。

使用函数

大多数SQL实现支持以下类型的函数：

  • 用于处理文本字符串（如删除或填充，转换为大写或小写）的文本函数。
  • 用于在数值数据上进行的算术操作（如返回绝对值，进行代数运算）的数值函数。
  • 用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
  • 返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

文本处理函数

在上一课中，我们使用到了去掉列值右边空格的函数RTRIM()。

1
2
3
4
SELECT prod_name,
       prod_id,
       RTRIM(prod_name+prod_id)
FROM products;

以上函数在MySQL中是不正确的，因为该系统中字段拼接方式为特定的函数CONCAT()，而该函数自带空格消除术。当然RTRIM()还是可以用的。

1
2
3
4
SELECT prod_name,
          prod_id,
          concat(prod_name, prod_id)
   FROM products;

除此之外，再举一个文本处理函数的例子UPPER()。

输入

1
2
3
select vend_name, upper(vend_name) as vend_name_upcase
from Vendors
order by vend_name;

输出

1
2
3
4
5
6
7
8
vend_name       vend_name_upcase
-------------   --------------
Bear Emporium	BEAR EMPORIUM
Bears R Us	    BEARS R US
Doll House Inc.	DOLL HOUSE INC.
Fun and Games	FUN AND GAMES
Furball Inc.	FURBALL INC.
Jouets et ours	JOUETS ET OURS

在下表中罗列出常用的通用的文本处理函数。

函数 说明
LEFT() 返回字符串左边的字符
LENGTH() 返回字符串长度
LOWER() 将字符串转换成小写
LTRIM() 去掉字符串左边的字符
RIGHT() 返回字符串右边的字符
RTRIM() 去掉字符串右边的字符
SOUNDEX() 返回字符串的SOUNDEX值
UPPER() 将字符串转换成大写

LENGTH()输入：

1
2
3
select vend_name, length(vend_name) as vend_name_upcase
from Vendors
order by vend_name;

输出：

1
2
3
4
5
6
7
8
9
vend_name       vend_name_upcase
-------------   -----------------
Bear Emporium	13
Bears R Us	    10
Doll House Inc.	15
Fun and Games	13
Furball Inc.	12
Jouets et ours	14

LOWER()输入：

1
2
3
select vend_name, lower(vend_name) as vend_name_upcase
from Vendors
order by vend_name;

输出：

1
2
3
4
5
6
7
8
9
vend_name       vend_name_upcase
-------------   -----------------
Bear Emporium	bear emporium
Bears R Us  	bears r us
Doll House Inc.	doll house inc.
Fun and Games	fun and games
Furball Inc.	furball inc.
Jouets et ours	jouets et ours

LTRIM()输入：

1
2
3
select vend_name, ltrim(vend_name) as vend_name_upcase
from Vendors
order by vend_name;

输出：

1
2
3
4
5
6
7
8
9
vend_name       vend_name_upcase
-------------   -----------------
Bear Emporium	Bear Emporium
Bears R Us	    Bears R Us
Doll House Inc.	Doll House Inc.
Fun and Games	Fun and Games
Furball Inc.	Furball Inc.
Jouets et ours	Jouets et ours

LTRIM()输入：

1
2
3
select vend_name, SOUNDEX(vend_name) as vend_name_upcase
from Vendors
order by vend_name;

输出：

1
2
3
4
5
6
7
8
vend_name       vend_name_upcase
-------------   -----------------
Bear Emporium	B65165
Bears R Us	    B6262
Doll House Inc.	D4252
Fun and Games	F53252
Furball Inc.	F61452
Jouets et ours	J32362

SOUNDEX()是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。

下面这个例子呢，很好玩，也充分说明了该函数的作用。

  • 假如，我们有一个客户名为Michael Green；
  • 但是，我们在储存数据时误将其存为Michelle Green。
  • 所以，我们应如何将其检索出来呢？
1
2
3
select CUST_NAME, cust_contact
 from customers
 where soundex(cust_contact) = soundex('Michael Green');

输出：

1
2
3
CUST_NAME   cust_contact
---------   ------------
Kids Place	Michelle Green

日期和时间处理函数

各个数据库管理系统基本都不相同，因此，不在这里学了。以后专门学习某一固定的DBMS时候再说吧。

数值处理函数

数值处理函数仅处理数值数据。

这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期-时间处理函数那么频繁使用。

具有讽刺意味的是，在主要的DBMS的函数中，数值函数是最一致、最统一的函数。

在下表中列出常用数值处理函数。

函数 说明
ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率
SIN() 返回一个监督的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切

小结

在本课中，主要的函数具有的可移植性不强，应该在日后选择某个具体的DBMS时，深入学习。