SQL学习六、函数的使用

文章最开始发布在简书，我自己又复制过来了，这篇文章简书的链接

SQL支持使用函数，我们可以在SQL语句中使用函数来处理数据，方便数据的转换和处理。
比如我们前面接触的去除空格的函数—TRIM()

需要注意的是，每一个BDMS都有自己特定的函数，只有少数的函数会被主要的DBMS同时支持，即使是这样，他们的函数名称和使用的语法也可能不同

比如，下面几个常用的函数在不同DBMS中的使用

• 提取字符串的组成部分

DBMS	函数
Access	MID()
DB2、Oracle、PostgreSQL、SQLite	SUBSTR()
MySQL、SQL Server	SUBSTRING()

• 数据类型的转换

DBMS	函数
Access、Oracle	每个类型都有一个转换的函数
DB2、PostgreSQL	CAST()
MariaDB、MySQL、SQL Server	CONVERT()

• 获取当前日期

DBMS	函数
Access	NOW()
DB2、PostgreSQL	CURRENT_DATE()
MariaDB、MySQL	CURDATE()
Oracle	SYSDATE()
SQL Server	GETDATE()
SQLite	DATE()

---

是否使用函数

通过上面的描述，我们基本可以确认各个DBMS中的函数基本不可以复用，也就是说当我们在一个DBMS中使用包含函数的SQL语句时，当我们更换数据库或者移植SQL到其他项目使用时很可能不能使用。 那么我们到底应不应该使用函数呢？

虽然使用这些函数会影响SQL的可移植性，但是在某些时候将这些函数的功能放到应用程序代码中去实现会异常艰难，也不利于应用程序的性能。所以，最终是否使用的确定权在实际编码人员手中，只是如果要使用函数的话，应该做好代码注释，方便自己和其他人可以确切的知道SQL的含义。

---

使用函数

函数的处理可以大致分为以下几类

• 处理文本字符串的文本函数，比如文本的删除、修改
• 处理数值计算的数值函数，比如返回绝对值
• 处理日期和时间值并从这些值中提取特定成分的日期和时间函数，比如返回两个日期 之差，检查日期有效性
• 返回 DBMS正使用的特殊信息的系统函数，比如返回用户的登录信息

1、文本函数

前面我们使用过TRIM()相关的几个函数， 现在我们来看下常用的文本函数

函数	说明
LEFT()	返回字符串左边的字符
RIGHT()	返回字符串右边的字符
LTRIM()	去掉字符串左边的空格
RTRIM()	去掉字符串右边的空格
LENGTH() 也使用DATALENGTH()或LEN()	返回字符串的长度
LOWER() Access使用LCASE()	将字符串转换为小写
UPPER() Access使用UCASE()	将字符串转换为大写
SOUNDEX()	返回字符串的SOUNDEX值

说明：
1、SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑了 类似的发音字符和音节，使得能对字符串进行发音比较而不是字母比较
2、Microsoft Access和 PostgreSQL不支持 SOUNDEX()
3、如果在创建 SQLite时使用了 SQLITE_SOUNDEX 编译时选项，那 么 SOUNDEX()在 SQLite 中就可用。因为 SQLITE_SOUNDEX 不是默认 的编译时选项，所以多数 SQLite实现不支持 SOUNDEX()

---

2、日期和时间处理函数

日期和时间值以特殊的格式存储，以便能快速和有效地排序或 过滤，并且节省物理存储空间。

• 返回日期的某一部分

函数	MDBS	示例	说明
DATEPART()	SQL Server、Sybase、Access	DATEPART(‘yyyy’, order_date)	返回日期里的年份
DATE_PART()	PostgreSQL	DATE_PART(‘year’, order_date)	返回日期里的年份
to_char()、to_number()	Oracle	to_number(to_char(order_date, ‘YYYY’))	to_char()函数用来提取日期的成分 to_number()用来将提取出的成分转换为数值
YEAR()	MySQL 、 MariaDB	YEAR(order_date)	从日期中提取年份
strftime()	SQLite	strftime(’%Y’, order_date)	从日期中提取年份

• 字符串转换为日期

函数	MDBS	示例	说明
to_date()	Oracle	to_date(‘01-01-2012’)	将字符串转换为日期

当然，还有很多其他的日期和时间的处理函数，这个就需要到正式使用的时候去查询相关的文档了。

---

3、数值处理函数

数值处理函数仅处理数值数据，比如 int、short、long、float、double等数据类型。 这些函数一般主要用于代数、三角或几 何运算，一些常见的 数值处理函数

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

相同功能的函数在不同的DBMS中可能实现方式不尽相同，如果不是很确定，最好在使用前查询相关文档

---

数据行相关的函数

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

---

对输入值进行修改的函数

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

---

常用函数及说明

引用地址:https://www.cnblogs.com/sydeveloper/archive/2012/09/09/2677963.html

abs(X) 返回参数X的绝对值

coalesce(X,Y,……) 返回第一个非空参数的副本。若所有的参数均为NULL，返回NULL。至少2个参数。
ifnull(X,Y) 返回第一个非空参数的副本。若两个参数均为NULL，返回NULL。

last_insert_rowid() 返回当前数据库连接最后插入行的RowID。

length(X) 返回X的长度，以字符计。

lower(X) 返回X字符串的所有字符小写化版本。

max(X,Y) 返回最大值。

nullif(X,Y) 当两参数不同时返回X，否则返回NULL。

quote(X) 返回参数的适于插入其他SQL语句中的值。字符串会被添加单引号。

random(*) 返回介于-231和231之间的随机整数。

round(X)或round(X,Y) 将X四舍五入，保留小数点后Y位。若忽略Y参数，则默认其为0。

sqlite_version(*) 返回所运行的SQLite库的版本号字符串。

substr(X,Y,Z) 返回输入字符串X中以第Y个字符开始，Z个字符长的子串。X最左端的字符序号为1。若Y为负，则从右至左数起。

typeof(X) 返回表达式X的类型。

upper(X) 返回X字符串的所有字符大写化版本。

在单参数聚集函数中，参数可以加前缀distinct。这时重复参数会被过滤掉，然后才传入到函数中。例如，函数count(distinct X)返回字段X的不重复非空值的个数，而不是字段X的全部非空值。

avg(X) 返回一组中非空的X的平均值。非数字值作0处理。结果总是一个浮点数。

count(X) 返回一组中X是非空值的次数。

count(*) 返回该组中的行数。

change_count() 返回受上一语句影响的行数。

max(X) 返回一组中的最大值。

min(X) 返回一组中的最小值。

sum(X)和total(X) 返回一组中所有非空值的数字和。若没有非空行，sum()返回null而total()返回0.0。total()的返回值为浮点数，sum()可以为整数。

date(日期时间字符串，修正符，修正符，……) 返回一个以YYYY-MM-DD为格式的日期。

time(日期时间字符串，修正符，修正符，……) 返回一个以HH:MM:SS 为格式的日期时间。

datetime(日期时间字符串，修正符，修正符，……) 返回一个以YYYY-MM-DD HH:MM:SS 为格式的日期时间。
julianday(日期时间字符串，修正符，修正符，……) 返回一个天数，从格林威治时间公元前4714年11月24号开始算起。

strftime(日期时间格式，日期时间字符串，修正符，修正符，……) 返回一个经过格式化的日期时间，它可以用以下的符号对日期和时间进行格式化：

%d一月中的第几天，01-31

%f小数形式的秒，SS.SSSS

%H小时，00-24

%j一年中的第几天，01-366

%JJulian Day Numbers

%m月份，01-12

%M分钟，00-59

%s从1970-01-01日开始计算的秒数

%S秒，00-59

%w星期，0-6

%W一年中的第几周，00-53

%Y年份，0000-9999

%%%，百分号

其他四个函数都可以用strftime()函数来表示：

date(……)strftime(’%Y-%m-%d’,……)

time(……)strftime(’%H:%M:%S’,……)

datetime(……)strftime(’%Y-%m-%d %H:%M:%S’,……)

julianday(……)strftime(’%J’,……)

日期时间字符串，可以用以下几种格式：

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

YYYY-MM-DDTHH:MM(T是一个分割日期和时间的字符)

YYYY-MM-DDTHH:MM:SS

YYYY-MM-DDTHH:MM:SS.SSS

HH:MM

HH:MM:SS

HH:MM:SS.SSS

now(返回一个当前的日期和时间，使用格林威治时间UTC)

DDDD.DDDD

日期和时间可以使用下面的修正符来更改日期或时间：

(1)增加指定数值的日期和时间

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

(2)返回当前日期的开始

start of month

start of year

start of week

start of day

(3)返回下一个星期是N的日期和时间

weekday N

(4)返回从1970-01-01开始算起的秒数

unixepoch

(5)返回本地时间

localtime

例子：

计算机当前时间：date(‘now’)

计算机当前月份的最后一天：date(‘now’,‘start of month’,’+1 month’,’-1 day’)

计算UNIX时间戳1092941466表示的日期和时间：datetime(‘1092941466’,‘unixepoch’)

计算UNIX时间戳1092941466表示的本地日期和时间：datetime(‘1092941466’,‘unixepoch’,‘localtime’)

计算机当前UNIX时间戳：strftime(’%s’,‘now’)

两个日期之间相差多少天：julianday(‘now’)-julianday(‘1981-12-23’)

两个日期时间之间相差多少秒：julianday(‘now’)*86400-juianday(‘2004-01-01 02:34:45’)*86400

计算今年十月份第一人星期二的日期：date(‘now’,‘start of year’,’+9 months’,‘weekday 2’)