MySQL 内置函数
mysql 内置函数
mysql数据库中提供了很丰富的函数，可以在 sql 语句中直接使用，非常方便。mysql函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。以下列出了这些函数的说明。
1. mysql 字符串函数
| 函数 | 描述 | 范例 |
|---|---|---|
| ascii(s) | 返回字符串 s 的第一个字符的 ascii 码。 |
返回 customername 字段第一个字母的 ascii 码： select ascii(customername) as numcodeoffirstchar from customers; |
| char_length(s) | 返回字符串 s 的字符数 |
返回字符串 yapf 的字符数
select char_length("yapf") as lengthofstring;
|
| character_length(s) | 返回字符串 s 的字符数 |
返回字符串 yapf 的字符数
select character_length("yapf") as lengthofstring;
|
| concat(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 |
合并多个字符串
select concat("sql ", "yapf ", "gooogle ", "facebook") as concatenatedstring;
|
| concat_ws(x, s1,s2...sn) | 同 concat(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 |
合并多个字符串，并添加分隔符：
select concat_ws("-", "sql", "tutorial", "is", "fun!")as concatenatedstring;
|
| field(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 |
返回字符串 c 在列表值中的位置：
select field("c", "a", "b", "c", "d", "e");
|
| find_in_set(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 |
返回字符串 c 在指定字符串中的位置：
select find_in_set("c", "a,b,c,d,e");
|
| format(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 |
格式化数字 "#,###.##" 形式： select format(250500.5634, 2); -- 输出 250,500.56 |
| insert(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 |
从字符串第一个位置开始的 6 个字符替换为 yapf
select insert("google.com", 1, 6, "yapf"); -- yapf.com
|
| locate(s1,s) | 从字符串 s 中获取 s1 的开始位置 |
获取 b 在字符串 abc 中的位置：
select locate('st','myteststring'); -- 5
返回字符串 abc 中 b 的位置： select locate('b', 'abc') -- 2
|
| lcase(s) | 将字符串 s 的所有字母变成小写字母 |
字符串 yapf 转换为小写：
select lcase('yapf') -- yapf
|
| left(s,n) | 返回字符串 s 的前 n 个字符 |
返回字符串 yapf 中的前两个字符：
select left('yapf',2) -- co
|
| lower(s) | 将字符串 s 的所有字母变成小写字母 |
字符串 yapf 转换为小写：
select lower('yapf') -- yapf
|
| lpad(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len |
将字符串 xx 填充到 abc 字符串的开始处：
select lpad('abc',5,'xx') -- xxabc
|
| ltrim(s) | 去掉字符串 s 开始处的空格 |
去掉字符串 yapf 开始处的空格：
select ltrim(" yapf") as lefttrimmedstring;-- yapf
|
| mid(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 substring(s,n,len) |
从字符串 yapf 中的第 2 个位置截取 3个 字符：
select mid("yapf", 2, 3) as extractstring; -- deb
|
| position(s1 in s) | 从字符串 s 中获取 s1 的开始位置 |
返回字符串 abc 中 b 的位置：
select position('b' in 'abc') -- 2
|
| repeat(s,n) | 将字符串 s 重复 n 次 |
将字符串 yapf 重复三次：
select repeat('yapf',3) -- yapfyapfyapf
|
| replace(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 |
将字符串 abc 中的字符 a 替换为字符 x：
select replace('abc','a','x') --xbc
|
| reverse(s) | 将字符串s的顺序反过来 |
将字符串 abc 的顺序反过来：
select reverse('abc') -- cba
|
| right(s,n) | 返回字符串 s 的后 n 个字符 |
返回字符串 yapf 的后两个字符：
select right('yapf',2) -- ku
|
| rpad(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len |
将字符串 xx 填充到 abc 字符串的结尾处：
select rpad('abc',5,'xx') -- abcxx
|
| rtrim(s) | 去掉字符串 s 结尾处的空格 |
去掉字符串 yapf 的末尾空格：
select rtrim("yapf ") as righttrimmedstring; -- yapf
|
| space(n) | 返回 n 个空格 |
返回 10 个空格： select space(10); |
| strcmp(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 |
比较字符串：
select strcmp("yapf", "yapf"); -- 0
|
| substr(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
从字符串 yapf 中的第 2 个位置截取 3个 字符：
select substr("yapf", 2, 3) as extractstring; -- deb
|
| substring(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 |
从字符串 yapf 中的第 2 个位置截取 3个 字符：
select substring("yapf", 2, 3) as extractstring; -- deb
|
| substring_index(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 |
select substring_index('a*b','*',1) -- a
select substring_index('a*b','*',-1) -- b
select substring_index(substring_index('a*b*c*d*e','*',3),'*',-1) -- c
|
| trim(s) | 去掉字符串 s 开始和结尾处的空格 |
去掉字符串 yapf 的首尾空格：
select trim(' yapf ') as trimmedstring;
|
| ucase(s) | 将字符串转换为大写 |
将字符串 yapf 转换为大写：
select ucase("yapf"); -- yapf
|
| upper(s) | 将字符串转换为大写 |
将字符串 yapf 转换为大写：
select upper("yapf"); -- yapf
|
2. mysql 数字函数
| 函数名 | 描述 | 范例 |
|---|---|---|
| abs(x) | 返回 x 的绝对值 |
返回 -1 的绝对值： select abs(-1) -- 返回1 |
| acos(x) | 求 x 的反余弦值(参数是弧度) |
select acos(0.25); |
| asin(x) | 求反正弦值(参数是弧度) |
select asin(0.25); |
| atan(x) | 求反正切值(参数是弧度) |
select atan(2.5); |
| atan2(n, m) | 求反正切值(参数是弧度) |
select atan2(-0.8, 2); |
| avg(expression) | 返回一个表达式的平均值，expression 是一个字段 |
返回 products 表中price 字段的平均值： select avg(price) as averageprice from products; |
| ceil(x) | 返回大于或等于 x 的最小整数 |
select ceil(1.5) -- 返回2 |
| ceiling(x) | 返回大于或等于 x 的最小整数 |
select ceiling(1.5); -- 返回2 |
| cos(x) | 求余弦值(参数是弧度) |
select cos(2); |
| cot(x) | 求余切值(参数是弧度) |
select cot(6); |
| count(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 |
返回 products 表中 products 字段总共有多少条记录： select count(productid) as numberofproducts from products; |
| degrees(x) | 将弧度转换为角度 |
select degrees(3.1415926535898) -- 180 |
| n div m | 整除，n 为被除数，m 为除数 |
计算 10 除于 5： select 10 div 5; -- 2 |
| exp(x) | 返回 e 的 x 次方 |
计算 e 的三次方： select exp(3) -- 20.085536923188 |
| floor(x) | 返回小于或等于 x 的最大整数 |
小于或等于 1.5 的整数： select floor(1.5) -- 返回1 |
| greatest(expr1, expr2, expr3, ...) | 返回列表中的最大值 |
返回以下数字列表中的最大值： select greatest(3, 12, 34, 8, 25); -- 34 返回以下字符串列表中的最大值：
select greatest("google", "yapf", "apple"); -- google
|
| least(expr1, expr2, expr3, ...) | 返回列表中的最小值 |
返回以下数字列表中的最小值： select least(3, 12, 34, 8, 25); -- 3 返回以下字符串列表中的最小值：
select least("google", "yapf", "apple"); -- apple
|
| ln | 返回数字的自然对数，以 e 为底。 |
返回 2 的自然对数： select ln(2); -- 0.6931471805599453 |
| log(x) 或 log(base, x) | 返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。 |
select log(20.085536923188) -- 3 select log(2, 4); -- 2 |
| log10(x) | 返回以 10 为底的对数 |
select log10(100) -- 2 |
| log2(x) | 返回以 2 为底的对数 |
返回以 2 为底 6 的对数： select log2(6); -- 2.584962500721156 |
| max(expression) | 返回字段 expression 中的最大值 |
返回数据表 products 中字段 price 的最大值： select max(price) as largestprice from products; |
| min(expression) | 返回字段 expression 中的最小值 |
返回数据表 products 中字段 price 的最小值： select min(price) as minprice from products; |
| mod(x,y) | 返回 x 除以 y 以后的余数 |
5 除于 2 的余数： select mod(5,2) -- 1 |
| pi() | 返回圆周率(3.141593） |
select pi() --3.141593 |
| pow(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： select pow(2,3) -- 8 |
| power(x,y) | 返回 x 的 y 次方 |
2 的 3 次方： select power(2,3) -- 8 |
| radians(x) | 将角度转换为弧度 |
180 度转换为弧度： select radians(180) -- 3.1415926535898 |
| rand() | 返回 0 到 1 的随机数 |
select rand() --0.93099315644334 |
| round(x) | 返回离 x 最近的整数 |
select round(1.23456) --1 |
| sign(x) | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 |
select sign(-10) -- (-1) |
| sin(x) | 求正弦值(参数是弧度) |
select sin(radians(30)) -- 0.5 |
| sqrt(x) | 返回x的平方根 |
25 的平方根： select sqrt(25) -- 5 |
| sum(expression) | 返回指定字段的总和 |
计算 orderdetails 表中字段 quantity 的总和： select sum(quantity) as totalitemsordered from orderdetails; |
| tan(x) | 求正切值(参数是弧度) |
select tan(1.75); -- -5.52037992250933 |
| truncate(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 round 最大的区别是不会进行四舍五入） |
select truncate(1.23456,3) -- 1.234 |
3. mysql 日期函数
| 函数名 | 描述 | 范例 |
|---|---|---|
| adddate(d,n) | 计算起始日期 d 加上 n 天的日期 |
select adddate("2017-06-15", interval 10 day);
->2017-06-25
|
| addtime(t,n) | n 是一个时间表达式，时间 t 加上时间表达式 n |
加 5 秒：
select addtime('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:16 (秒)
添加 2 小时, 10 分钟, 5 秒:
select addtime("2020-06-15 09:34:21", "2:10:5");
-> 2020-06-15 11:44:26
|
| curdate() | 返回当前日期 |
select curdate(); -> 2018-09-19 |
| current_date() | 返回当前日期 |
select current_date(); -> 2018-09-19 |
| current_time | 返回当前时间 |
select current_time(); -> 19:59:02 |
| current_timestamp() | 返回当前日期和时间 |
select current_timestamp() -> 2018-09-19 20:57:43 |
| curtime() | 返回当前时间 |
select curtime(); -> 19:59:02 |
| date() | 从日期或日期时间表达式中提取日期值 |
select date("2017-06-15");
-> 2017-06-15
|
| datediff(d1,d2) | 计算日期 d1->d2 之间相隔的天数 |
select datediff('2001-01-01','2001-02-02')
-> -32
|
| date_add(d，interval expr type) | 计算起始日期 d 加上一个时间段后的日期 |
select adddate('2011-11-11 11:11:11',1)
-> 2011-11-12 11:11:11 (默认是天)
select adddate('2011-11-11 11:11:11', interval 5 minute)
-> 2011-11-11 11:16:11 (type的取值与上面那个列出来的函数类似)
|
| date_format(d,f) | 按表达式 f的要求显示日期 d |
select date_format('2011-11-11 11:11:11','%y-%m-%d %r')
-> 2011-11-11 11:11:11 am
|
| date_sub(date,interval expr type) | 函数从日期减去指定的时间间隔。 |
orders 表中 orderdate 字段减去 2 天： select orderid,date_sub(orderdate,interval 2 day) as orderpaydate from orders |
| day(d) | 返回日期值 d 的日期部分 |
select day("2017-06-15");
-> 15
|
| dayname(d) | 返回日期 d 是星期几，如 monday,tuesday |
select dayname('2011-11-11 11:11:11')
->friday
|
| dayofmonth(d) | 计算日期 d 是本月的第几天 |
select dayofmonth('2011-11-11 11:11:11')
->11
|
| dayofweek(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 |
select dayofweek('2011-11-11 11:11:11')
->6
|
| dayofyear(d) | 计算日期 d 是本年的第几天 |
select dayofyear('2011-11-11 11:11:11')
->315
|
| extract(type from d) | 从日期 d 中获取指定的值，type 指定返回的值。
type可取值为：
|
select extract(minute from '2011-11-11 11:11:11') -> 11 |
| from_days(n) | 计算从 0000 年 1 月 1 日开始 n 天后的日期 |
select from_days(1111) -> 0003-01-16 |
| hour(t) | 返回 t 中的小时值 |
select hour('1:2:3')
-> 1
|
| last_day(d) | 返回给给定日期的那一月份的最后一天 |
select last_day("2017-06-20");
-> 2017-06-30
|
| localtime() | 返回当前日期和时间 |
select localtime() -> 2018-09-19 20:57:43 |
| localtimestamp() | 返回当前日期和时间 |
select localtimestamp() -> 2018-09-19 20:57:43 |
| makedate(year, day-of-year) | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 |
select makedate(2017, 3); -> 2017-01-03 |
| maketime(hour, minute, second) | 组合时间，参数分别为小时、分钟、秒 |
select maketime(11, 35, 4); -> 11:35:04 |
| microsecond(date) | 返回日期参数所对应的微秒数 |
select microsecond("2017-06-20 09:34:00.000023");
-> 23
|
| minute(t) | 返回 t 中的分钟值 |
select minute('1:2:3')
-> 2
|
| monthname(d) | 返回日期当中的月份名称，如 november |
select monthname('2011-11-11 11:11:11')
-> november
|
| month(d) | 返回日期d中的月份值，1 到 12 |
select month('2011-11-11 11:11:11')
->11
|
| now() | 返回当前日期和时间 |
select now() -> 2018-09-19 20:57:43 |
| period_add(period, number) | 为 年-月 组合日期添加一个时段 |
select period_add(201703, 5); -> 201708 |
| period_diff(period1, period2) | 返回两个时段之间的月份差值 |
select period_diff(201710, 201703); -> 7 |
| quarter(d) | 返回日期d是第几季节，返回 1 到 4 |
select quarter('2011-11-11 11:11:11')
-> 4
|
| second(t) | 返回 t 中的秒钟值 |
select second('1:2:3')
-> 3
|
| sec_to_time(s) | 将以秒为单位的时间 s 转换为时分秒的格式 |
select sec_to_time(4320) -> 01:12:00 |
| str_to_date(string, format_mask) | 将字符串转变为日期 |
select str_to_date("august 10 2017", "%m %d %y");
-> 2017-08-10
|
| subdate(d,n) | 日期 d 减去 n 天后的日期 |
select subdate('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)
|
| subtime(t,n) | 时间 t 减去 n 秒的时间 |
select subtime('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)
|
| sysdate() | 返回当前日期和时间 |
select sysdate() -> 2018-09-19 20:57:43 |
| time(expression) | 提取传入表达式的时间部分 |
select time("19:30:10");
-> 19:30:10
|
| time_format(t,f) | 按表达式 f 的要求显示时间 t |
select time_format('11:11:11','%r')
11:11:11 am
|
| time_to_sec(t) | 将时间 t 转换为秒 |
select time_to_sec('1:12:00')
-> 4320
|
| timediff(time1, time2) | 计算时间差值 |
select timediff("13:10:11", "13:10:10");
-> 00:00:01
|
| timestamp(expression, interval) | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 |
select timestamp("2017-07-23", "13:10:11");
-> 2017-07-23 13:10:11
|
| to_days(d) | 计算日期 d 距离 0000 年 1 月 1 日的天数 |
select to_days('0001-01-01 01:01:01')
-> 366
|
| week(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
select week('2011-11-11 11:11:11')
-> 45
|
| weekday(d) | 日期 d 是星期几，0 表示星期一，1 表示星期二 |
select weekday("2017-06-15");
-> 3
|
| weekofyear(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 |
select weekofyear('2011-11-11 11:11:11')
-> 45
|
| year(d) | 返回年份 |
select year("2017-06-15");
-> 2017
|
| yearweek(date, mode) | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 |
select yearweek("2017-06-15");
-> 201724
|
4. mysql 高级函数
| 函数名 | 描述 | 范例 |
|---|---|---|
| bin(x) | 返回 x 的二进制编码 |
15 的 2 进制编码: select bin(15); -- 1111 |
| binary(s) | 将字符串 s 转换为二进制字符串 |
select binary "yapf"; -> yapf |
case expression
when condition1 then result1
when condition2 then result2
...
when conditionn then resultn
else result
end
|
case 表示函数开始，end 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 |
select case when 1 > 0 then '1 > 0' when 2 > 0 then '2 > 0' else '3 > 0' end ->1 > 0 |
| cast(x as type) | 转换数据类型 |
字符串日期转换为日期：
select cast("2017-08-29" as date);
-> 2017-08-29
|
| coalesce(expr1, expr2, ...., expr_n) | 返回参数中的第一个非空表达式（从左向右） |
select coalesce(null, null, null, 'yapf.com', null, 'google.com'); -> yapf.com |
| connection_id() | 返回唯一的连接 id |
select connection_id(); -> 4292835 |
| conv(x,f1,f2) | 返回 f1 进制数变成 f2 进制数 |
select conv(15, 10, 2); -> 1111 |
| convert(s using cs) | 函数将字符串 s 的字符集变成 cs |
select charset('abc')
->utf-8
select charset(convert('abc' using gbk))
->gbk
|
| current_user() | 返回当前用户 |
select current_user(); -> guest@% |
| database() | 返回当前数据库名 |
select database(); -> yapf |
| if(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 |
select if(1 > 0,'正确','错误') ->正确 |
| ifnull(v1,v2) | 如果 v1 的值不为 null，则返回 v1，否则返回 v2。 |
select ifnull(null,'hello word') ->hello word |
| isnull(expression) | 判断表达式是否为 null |
select isnull(null); ->1 |
| last_insert_id() | 返回最近生成的 auto_increment 值 |
select last_insert_id(); ->6 |
| nullif(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 null，否则返回 expr1 |
select nullif(25, 25); -> |
| session_user() | 返回当前用户 |
select session_user(); -> guest@% |
| system_user() | 返回当前用户 |
select system_user(); -> guest@% |
| user() | 返回当前用户 |
select user(); -> guest@% |
| version() | 返回数据库的版本号 |
select version() -> 5.6.34 |
学习MySQL