本文共 7413 字,大约阅读时间需要 24 分钟。
本章介绍什么是函数,MySQL支持何种函数,以及如何使用这些函数。
与其他大多数计算机语言一样,SQL也支持利用函数来处理数据。函数一般是在数据上执行的,它给数据的转换和处理提供了方便。例如在前一章中用来去掉串尾空格的RTrim()就是一个函数的例子。
特点
函数的类型:
处理文本串
(如删除或填充值,转换值为大写或小写)的文本函数
。算术操作
(如返回绝对值,进行代数运算)的数值函数
。日期和时间函数
。系统函数
。函数 | 说明 |
---|---|
Left() | 返回串左边的字符 |
Right() | 返回串右边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换为小写 |
upper | 将串转换为大写 |
LTrim() | 去掉串左边的空格 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX值 |
SubString() | 返回子串的字符 |
upper
MariaDB [test]> select user,upper(user) as UP_user from linux;+--------+---------+| user | UP_user |+--------+---------+| user2 | USER2 || user3 | USER3 || user4 | USER4 || user5 | USER5 || a | A || 1 | 1 || (cay) | (CAY) || (cays) | (CAYS) || user1 | USER1 |+--------+---------+9 rows in set (0.00 sec)
length
MariaDB [test]> select user,length(user) as a from linux; +--------+---+| user | a |+--------+---+| user2 | 5 || user3 | 5 || user4 | 5 || user5 | 5 || a | 1 || 1 | 1 || (cay) | 5 || (cays) | 6 || user1 | 5 |+--------+---+9 rows in set (0.00 sec)
left
MariaDB [test]> select user,left(user,2) as a from linux; +--------+----+| user | a |+--------+----+| user2 | us || user3 | us || user4 | us || user5 | us || a | a || 1 | 1 || (cay) | (c || (cays) | (c || user1 | us |+--------+----+9 rows in set (0.00 sec)
locate
用法:
LOCATE(substr,str), 返回字符串str第一次出现的子串SUBSTR的位置。 LOCATE(substr,str,pos),返回第一次出现在字符串str的子串substr的位置,从位置pos开始MariaDB [test]> select locate('word','password'); +---------------------------+| locate('word','password') |+---------------------------+| 5 |+---------------------------+1 row in set (0.00 sec)MariaDB [test]> select locate('word','passwordword',4); +---------------------------------+| locate('word','passwordword',4) | /从第四个字符开始(s)+---------------------------------+| 5 |+---------------------------------+1 row in set (0.00 sec)MariaDB [test]> select locate('word','passwordword',5); +---------------------------------+| locate('word','passwordword',5) | /从第五个字符(w)开始,+---------------------------------+| 5 |+---------------------------------+1 row in set (0.00 sec)MariaDB [test]> select locate('word','passwordword',6); +---------------------------------+| locate('word','passwordword',6) | /从第五个字符(o)开始,所以只有第九位可以+---------------------------------+| 9 |+---------------------------------+1 row in set (0.00 sec)
Soundex()
MariaDB [test]> select Soundex(user) from linux;+---------------+| Soundex(user) |+---------------+| U260 | /返回串的SOUNDEX值| U260 || U260 || U260 || A000 || || C000 || C000 || U260 |+---------------+9 rows in set (0.03 sec)
SubString()
MariaDB [test]> select user,SubString(user,5) as a from linux;+--------+----+| user | a |+--------+----+| user2 | 2 | /返回子串的位置| user3 | 3 || user4 | 4 || user5 | 5 || a | || 1 | || (cay) | ) || (cays) | s) || user1 | 1 |+--------+----+9 rows in set (0.00 sec)
函 数 | 说 明 |
---|---|
AddDate() | 增加一个日期(天、周等) |
AddTime() | 增加一个时间(时、分等) |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期之差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayOfWeek() | 对于一个日期,返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
Time() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
时间的存储格式为: 2005-09-01 11:30:05
MariaDB [(none)]> select adddate('2020-6-1',31);+------------------------+| adddate('2020-6-1',31) |+------------------------+| 2020-07-02 |+------------------------+1 row in set (0.05 sec)MariaDB [(none)]> select addtime('9:00:00',60);+-----------------------+| addtime('9:00:00',60) |+-----------------------+| NULL |+-----------------------+1 row in set, 1 warning (0.00 sec)MariaDB [(none)]> select addtime('9:00:00',1);+----------------------+| addtime('9:00:00',1) |+----------------------+| 09:00:01 |+----------------------+1 row in set (0.00 sec)MariaDB [(none)]> select addtime('9:00:00',30);+-----------------------+| addtime('9:00:00',30) |+-----------------------+| 09:00:30 |+-----------------------+1 row in set (0.00 sec)MariaDB [(none)]> select date('2010-9-15 12:00:00');+----------------------------+| date('2010-9-15 12:00:00') |+----------------------------+| 2010-09-15 |+----------------------------+1 row in set (0.05 sec)MariaDB [(none)]> select time('2010-9-15 12:00:00');+----------------------------+| time('2010-9-15 12:00:00') |+----------------------------+| 12:00:00 |+----------------------------+1 row in set (0.00 sec)MariaDB [(none)]> select hour('2010-9-15 12:00:00');+----------------------------+| hour('2010-9-15 12:00:00') |+----------------------------+| 12 |+----------------------------+1 row in set (0.00 sec)MariaDB [(none)]> select dayofweek('2010-9-15 12:00:00');+---------------------------------+| dayofweek('2010-9-15 12:00:00') |+---------------------------------+| 4 |+---------------------------------+1 row in set (0.04 sec)MariaDB [(none)]> select Datediff('2012-9-15','2012-6-20');+-----------------------------------+| Datediff('2012-9-15','2012-6-20') |+-----------------------------------+| 87 |+-----------------------------------+1 row in set (0.00 sec)MariaDB [(none)]> select curdate();+------------+| curdate() |+------------+| 2020-08-05 |+------------+1 row in set (0.01 sec)MariaDB [(none)]> select curtime();+-----------+| curtime() |+-----------+| 22:19:22 |+-----------+1 row in set (0.00 sec)MariaDB [(none)]> select date_add(curdate(),interval 1 day);+------------------------------------+| date_add(curdate(),interval 1 day) |+------------------------------------+| 2020-08-06 |+------------------------------------+1 row in set (0.00 sec)MariaDB [(none)]> select date_add(curtime(),interval 1 hour);+-------------------------------------+| date_add(curtime(),interval 1 hour) |+-------------------------------------+| 23:45:23 |+-------------------------------------+1 row in set (0.00 sec)
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算,因此没有串或日期—时间处理函数的使用那么频繁。
MariaDB [test]> select * from linux;+--------+----------+------+------+-----+-------+------------+| user | passwd | sex | age | num | price | math_price |+--------+----------+------+------+-----+-------+------------+| user1 | 111 | boy | -18 | 2 | 35 | 88 |+--------+----------+------+------+-----+-------+------------+9 rows in set (0.00 sec)MariaDB [test]> select abs(age) from linux where user='user1';+----------+| abs(age) |+----------+| 18 |+----------+1 row in set (0.00 sec)MariaDB [(none)]> select pi();+----------+| pi() |+----------+| 3.141593 |+----------+1 row in set (0.01 sec)MariaDB [test]> select rand();+---------------------+| rand() |+---------------------+| 0.23124154958058293 |+---------------------+1 row in set (0.00 sec)MariaDB [test]> select rand();+--------------------+| rand() |+--------------------+| 0.9650037875073065 |+--------------------+1 row in set (0.00 sec)
转载地址:http://wyxli.baihongyu.com/