SQL 入门笔记(八)使用函数处理数据
SQL 中的函数
同许多编程语言一样,SQL 中也有函数
但有一个很重要的问题,每一个 DBMS 都有特定的函数,事实上,只有少数几个函数被所有主要 DBMS 同等地支持
下表很明显地体现了这一区别
函数 | 语法 |
---|---|
提前字符串的组成部分 | DB2、Oracle、PostgreSQL 和 SQLite 使用 SUBSTR() ;MariaDB、MySQL 和 SQL Server 使用 SUBSTRING() |
数据类型转换 | Oracle 使用多个函数,每种类型的转换有一个函数(听上去和 C/C++ 差不多?);DB2 和 PostgreSQL 使用 CAST() ;MariaDB、MySQL和 SQL Server 使用 CONVERT() |
取当前日期 | DB2 和 PostgreSQL 使用 CURRENT_DATE ;MariaDB 和 MySQL 使用 CURDATE() ;Oracle 使用 SYSDATE() ;SQL Server 使用 GETDATE() ;SQLite 使用 DATE() |
可以看到,SQL 函数是 不可移植的 ,这意味着为特定 DBMS 编写的代码在其他 DBMS 中可能不可用
为了代码的可移植性,许多 SQL 程序员不赞成使用这些函数,当然缺点也是显而易见的
提示:是否应该使用函数?
现在,你面临是否应该使用函数的选择。决定权在你,使用或是不使用也没有对错之分。如果你决定使用函数,应该保证做好代码注释,以便以后你或其他人能确切地知道这些 SQL 代码的含义
使用函数
文本处理函数
在上一篇中,已经使用过 CONCAT()
函数,下面是另一个例子,使用的是 UPPER()
函数
1 | SELECT vend_name, UPPER(vend_name) AS vend_name_up |
左右对比可以看到,UPPER()
将文本转换为大写
提示:大写、小写、大小写混合
此时你应该已经知道 SQL 函数不区分大小写,因此 upper()
,UPPER()
,Upper()
都可以。随你的喜好,不过注意风格一致,不用变来变去,否则你写的代码就不好读了
下表是一些常用的文本处理函数
函数 | 说明 |
---|---|
SUBSTR() 或是 SUBSTRING() |
提取子字符串 |
LENGTH() (DATALENGTH() 或 LEN() ) |
返回字符串长度 |
LOWER() 和 UPPER() |
转换为大/小写 |
LTRIM() 、RTRIM() 和 TRIM() |
去除字符串左/右/两边的空格 |
SOUNDEX() |
返回字符串的 SOUNDEX 值 |
其中 SOUNDEX
需要做进一步解释,SOUNDEX是一个将任何文本字符串转换为描述其语音表示的字母数学模式的算法,它考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母比较 (是不是听上去很高级?)
虽然 SOUNDEX
不是 SQL 的概念,但是多数 DBMS 都提供对 SOUNDEX
的支持
下面给出一个例子:Customsers
表中有一个客户 Kids Place
联系名为 Michelle Green
,当然这是一个拼写错误,正确的应为 Michele Green
,该怎么办呢?显然,直接搜索肯定不会返回数据
1 | SELECT cust_name , cust_contact |
但是如果使用 SOUNDEX()
函数进行搜索,它匹配所有发音类似于 Michele Green
的联系名
1 | SELECT cust_name , cust_contact |
可以看见,因为发音相似,两者的 SOUNDEX
值匹配,所以这次正确过滤出了所需的数据
日期与时间处理函数
日期和时间采用相应的数据类型存储在表中,每种 DBMS 都有自己的特殊形式。日期和时间值以特殊的格式存储,以便能快速和有效地排序或过滤,并节省物理存储空间
也就因为如此,日期和时间的可移植性最差(悲)
下表总结了一些最重要的 MySQL 内置日期函数
函数 | 描述 |
---|---|
NOW() |
返回当前日期和时间。 |
CURDATE() |
返回当前日期。 |
CURTIME() |
返回当前时间 |
DATE() |
提取日期或日期时间表达式的日期部分。 |
DAY() |
返回月份中的一天(0-31)。 |
DAYNAME() |
返回工作日的名称。 |
MONTH() |
返回经过日期(1-12)的月份。 |
MONTHNAME() |
返回月份的名称。 |
YEAR() |
返回年份。 |
DATE_FORMAT() |
以其他格式显示日期和时间值。 |
EXTRACT() |
提取日期的一部分。 |
DATE_ADD() |
将指定的时间值(或间隔)添加到日期值。 |
DATE_SUB() |
从日期值中减去指定的时间值(或间隔)。 |
DATEDIFF() |
返回两个日期之间的天数 |
数值处理函数
数值处理函数仅处理数值数据,这些函数一般用于代数、三角或几何运算,因此不像字符串或日期时间函数使用地那么频繁
但实际上,在主要的 DBMS 中,数值函数是最一致的,下面是一些基本函数
函数 | 描述 |
---|---|
ABS() |
求绝对值 |
COS()/SIN() |
正/余弦 |
EXP() |
返回一个数的指数值 |
PI() |
返回pi的值 |
SQRT() |
开根号 |
TAN() |
正切 |