常用函数
数值函数
数值函数是MySQL中一种很重要的函数，主要用于处理数值方面的运算。
求绝对值函数
函数ABS(x)的返回值是数值x的绝对值。正数的绝对值是其本身，负数的绝对值是其相反数。
例：
select abs(5),abs(-5),abs(-5.5);
求余函数
函数MOD(x,y)的返回值是数值x除以数值y后的余数。
- 除数和被除数任何一个为NULL，返回结果都将为NULL；
- 除数为0将是非法运算，返回结果为NULL。
例：
select mod(6,4),mod(6,-4),mod(null,6),mod(6,0),mod(0,2.5);
用于获取整数的函数
- ceil(x): 函数CEIL(x)的返回值是大于数值x的最小整数值
例：
select ceil(2.45),ceil(-2.45);
- floor(x): 函数FLOOR(x)的返回值是小于数值x的最大整数值
例：
select floor(2.45),floor(-2.45);
获取随机数的函数
函数RAND()的返回值是0～1内的小数，并且每次的运行结果都不同
例：
select rand(),rand(),rand();
四舍五入函数
函数ROUND()的作用是对数值执行四舍五入操作
- 当函数格式为ROUND(x)时，返回值为整数；
- 当函数格式为ROUND(x,y)时，对数值x进行四舍五入并保留小数点后y位；
例：
select round(100.144),round(100.568),round(100.144,2),round(100.568,2);
截取小数函数
函数TRUNCATE(x,y)的作用是对数值x进行截取，保留小数点后y位。
直接截取值，并不进行四舍五入
例：
select truncate(1.47,1),truncate(1.584,2),round(1.47,1);
字符串函数
字符串函数是MySQL中使用最频繁的函数，主要用于处理数据库中字符串类型的数据。
返回字符串长度和字符串中字符个数的函数
- 函数LENGTH(str)用于返回字符串的长度，一个汉字占用2个字节，一个英文字符和数字占用1个字节。
例：
select length('abcdef'),length('字符长度');
- 函数CHAR_LENGTH(str)用于返回字符串中的字符个数。
例：
select char_length('abcdef'),char_length('字符长度');
合并字符串的函数
- 函数CONCAT(str1,str2…strn)可以将多个字符串拼接成为一个字符串，但如果参数中有一个NULL值，则返回结果都将为NULL。
例：
select concat('abcd','efg'),concat('abcd',null,'efg');
- 函数CONCAT_WS(x,str1,str2…strn)是函数CONCAT(str1,str2…strn)的特殊形式
作用是以第一个参数为分隔符，连接后面的多个字符串。
例：
select concat_ws('_','ab','cd','ef'),concat_ws('_','gh',null,'ij');
函数CONCAT_WS(x,str1,str2…strn)会忽略分隔符后的NULL值，但如果分隔符为NULL，则返回结果为NULL。
替换字符串的函数
- 函数INSERT(str,x,y,instr)的作用是将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。
例：
select insert('beijinglvyoushichang',13,8,'gonglue');
- 函数REPLACE(str,a,b)也可以替换字符串，作用是使用字符串b替换字符串str中的子串a。
例：
select replace('abcabc','abc','you');
字母大小写转换函数
函数LOWER(str)用于将字符串str中的字母全部转换为小写字母
函数UPPER(str)用于将字符串str中的字母全部转换为大写字母。
例：
select lower('aBcD'),upper('aBcD');
获取指定长度字符串的函数
- 函数LEFT(str,x)用于获取字符串str中最左边的x个字符
函数RIGHT(str,x)用于获取字符串str中最右边的x个字符。
例：
select left('beijinglvyougonglue',7),right('beijinglvyougonglue',7);
- 函数SUBSTRING(str,x,y)用于获取字符串str中从x位置开始，后面y个字符长度的子串。该函数常用于在给定字符串中提取子串。
例：
select substring('beijinglvyougonglue',8,5);
- substr(str, pos, len)截取字符串。从pos位置开始对str截取len长度。
填充字符串的函数
- 函数LPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最左边进行填充，直到字符串str1总长度达到n个字符长度。如果str1的字符长度大于或等于n，则不填充。
例：
select lpad('abcefg',5,'km'),lpad('abcefg',10,'km');
- 函数RPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最右边进行填充，直到字符串str1总长度达到n个字符长度。
例：
select rpad('abcdef',5,'km'),rpad('abcdef',10,'km');
删除字符串中空格的函数
- 函数LTRIM(str)用于删除字符串左侧的空格字符，
函数RTRIM(str)用于删除字符串右侧的空格字符。
例：
select concat('ab',' cd ','ef') as str1,
concat('ab',ltrim(' cd '),'ef') as str2,
concat('ab',' cd ','ef') as str3,
concat('ab',rtrim(' cd '),'ef');
- 函数TRIM(str)用于删除字符串开头和结尾的空格，另外，它还可以删除字符串两侧的指定字符。
例：
SELECT CONCAT('ab',' cd ','ef') AS str1,
CONCAT('ab',TRIM(' cd '),'ef') AS str2,
TRIM('a' from 'aabacaa') AS str3;
重复生成字符串的函数
函数REPEAT(str,n)返回字符串str重复n次的结果。
例：
SELECT REPEAT('abc ',3);
获取字符串中子串开始位置的函数
函数LOCATE(str1,str)返回子串str1在字符串str中的开始位置，返回值的最小值为1，
如果字符串str中不包含字符串str1，则返回0。
例：
SELECT LOCATE('abc','ababcabd'),LOCATE('efg','ababcabd');
- locate(substr, str, start_pos) ：从起始位置start_pos开始。返回index索引值。
- instr(str, substr)：与locate()函数的参数位置相反。返回index索引值。
- position(substr in str)：返回index索引值。
- 扩展知识：find_in_set(substr, str)：str需要以逗号分隔。返回index索引值。
反转字符串的函数
函数REVERSE(str)返回将字符串str中字符倒序排列后的结果。
例：
SELECT REVERSE('abcdefg');
截取字符串函数
- substring_index(str, delim, count)：用于截取字符串
参数：
- delim是所使用的分隔符。
- count为正数时，对str截取从左往右直到第count个分隔符，count为负数时，对str截取从右往左直到第count个分隔符。
日期与时间函数
获取当前的日期
current————当前
格式: current_date()
select current_date();
简写: curdate()
select curdate();
获取当前时间的函数
格式：current_time()
select current_time();
简写：curtime()
select curtime();
获取当前日期和时间的函数
格式：now()
select now() as 当前日期;
-- 另外还有：sysdate();同上-select sysdate();
获取unix时间戳函数
时间戳就是从1970开始到现在所过了多少秒(unix系统是1969发明的，所有计算机为了纪念它把1970-1-1设为开始时间)
nuix————nuix系统
timestemp————时间戳
格式：unix_timestamp(date)
select unix_timestamp(now()); -- 到当前时间
select format(unix_timestamp(now()),0); -- 格式化后的...
获取年份、月份、星期、日、时、分和秒的函数
- 获取日期的年份 year(date)
select now(),year(now());
select year('2018-5-9');
- 获取日期的月份 month(date)
select month('2020-7-23');
- 获取月份的名字 格式：monthname(date)
select monthname('2021-9-10');
外加
- 获取星期 索引
格式1：weekday(date) -- 星期一0 星期二1 星期三2 ...星期日6
select weekday('2021-4-15');
格式2：dayofweek(date) -- 星期天1 星期一2 ... 星期六7
select dayofweek('2021-5-11');
格式3：weekname(date) -- 获取星期的名字
select now(),dayname(now());
- 获取当前的周数 --一般来说，一年53周
格式：week(date)
select week(now());
外加格式：weekofyear(date)--在年中是第几周
select weekofyear(now());
- 获取小时、分钟、秒
格式分别：hour(date)、minute(date)、second(date)
select hour('07-01-16 5:17:19') as hour,
minute('07-01-16 5:17:19') as minute,
second('07-01-16 5:17:19') as second;
格式化日期和时间的函数
格式： date_format(date,format);
select date_format('2020-5-11 12:50:30','%Y %m %D %r %S');
计算日期和时间的函数
- 时间日期的加减
格式1：adddate(date,interval 数字 单位)--日期和时间加运算
select adddate('2009-1-1',interval 2 year) as date1,
adddate('2009-1-1 06:20:20',interval 2 hour) as date2,
adddate('2009-1-1 06:20:20',interval '10:10' minute_second);
格式：date_add(date,interval 数字 单位)--同上一样
select date_add('2020-1-30',interval 2 day);
用interval 关键字直接实现加减
select '2020-1-30' + interval '2' day;
select '2020-1-30' - interval '2 2' day_hour;
注意：只能year_month/day_hour,day_minute,day_second/hour_minute,minute_second这几个组合
格式2：surdate(date,interval 数字 单位)--日期和时间减运算
select subdate('2009-01-01',interval 2 year) as date1,
subdate('2009-01-01 08:20:20',interval 7 hour) as date2;
格式：date_sub(date,interval 数字 单位) --同上
select date_sub('2020-5-11',interval 2 day);
- 两个日期相差天数
格式：datediff(日期1,日期2) --日期1-日期2 得到相差天数
select datediff('2022-02-17',now());
- last_day(time)函数返回指定日期对应月份的最后一天。
时间和秒的转换的函数
格式：time_to_sec(date) ———— 时间换算为秒
select time_to_sec('5:28:33');
格式：sec_to_time(date) ———— 秒换算为”hh:mm:ss“的时间格式
select sec_to_time('19713');
条件判断函数
条件判断函数又称为流程控制函数，也是MySQL中使用较多的一种函数。
IF()函数
IF(expr,v1,v2)函数的意义是，如果表达式expr的结果为真，函数的返回值为v1，如果表达式expr的结果为假，则返回值为v2。
例：
select name,price,if(price>50,'high','low') from good;
IFNULL()函数
IFNULL(v1,v2)函数的意义是，如果v1不为NULL，则函数的返回值为v1，否则返回值为v2。
NULL值是不能参与数值运算的，实际应用中常用该函数来替换NULL值
例：
select ifnull(num,0) from good;
CASE函数
格式1: case when 表达式1 then 返回值1 [when 表达式2 then 返回值2] [else 返回值n] end
例：select case when price>100 then 'high' else 'low' end from good;
格式2: case 表达式 when 表达式1 then 值1 [when 表达式2 then 值2] [else 值n] end
例：select case price when 800 then 'high' when 120 then 'mid' else 'low' end from good;
JSON函数
从MySQL 5.7.8起，开始支持JSON数据类型。JSON函数就是用于处理JSON类型的数据，
创建JSON值的函数
在MySQL中创建JSON值的函数有两个，一个用于创建数组形式的JSON值，另一个用于创建对象形式的JSON值。
- 创建JSON数组
格式：json_array(值1,值2,...,值n)
例:SELECT JSON_ARRAY(1, "abc", null, true,CURTIME());
- 创建JSON对象
格式：json_object(对象1,值1,对象2,值2,...,对象n,值n)
例：SELECT JSON_OBJECT('id', 8, 'name', 'Tom');
修改JSON值的函数
- JSON_ARRAY_APPEND(json文档,示键,附加值)
作用：将值附加到JSON文档中指示数组的结尾并返回结果
例：
SET @j = '["a", ["b", "c"], "d"]';
SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
- JSON_SET(json文档,示键1,值1 [,示键2,值2]…);
作用：JSON文档中插入或更新数据并返回结果
例：
SET @j = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
从JSON文档中删除数据的函数
格式：
JSON_REMOVE(json文档,示键1,删除值1 [,示键2,删除值2]…...)
例：
SET @j = '["a", ["b", "c"], "d"]', @h = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_REMOVE(@j, '$[1]'), JSON_REMOVE(@h, '$.a');
返回JSON文档中数据和路径的函数
- JSON_EXTRACT()
格式：JSON_EXTRACT (json_doc,key1[,key2]…...)
- JSON_SEARCH()
格式：JSON_SEARCH (json_doc,one_or_all,str)
其他函数
返回数据库信息的函数
- MD5(): 函数MD5(str)可以对字符串str进行加密，算出一个128位二进制形式的信息，但是系统会显示为32位十六进制的信息；若参数为NULL，则返回NULL值。该函数常用于对一些普通的不需要解密的数据进行加密。
例：SELECT MD5('1'),MD5('abc');
注意：
无论输入信息长度为多少，经过处理后，结果均为128位二进制形式的信息，但系统会显示为32位十六进制的信息；
MD5()函数是单向加密，根据输出结果不能反推出输入的信息。
- PASSWORD(str): 函数PASSWORD(str)返回字符串str的加密版本，一个41位长的字符串。该函数只用来对系统用户的密码进行设置，但不能对应用的数据进行加密。
例：SELECT PASSWORD('123456');
对不同进制数据进行转换的函数
函数CONV(val,from_base,to_base)用于不同进制数据之间的相互转换，其中，参数val为需要转换的数据，该函数的作用是将其由from_base进制转换为to_base进制。
例：SELECT CONV(15,10,2),CONV('a',16,10),CONV(1111,2,8);
IP地址与数字相互转换的函数
为方便地进行IP或者网段的比较，可以将字符串形式的IP地址转换为数字表示的网络字节序。
这就用到了INET_ATON(IP)函数和INET_NTOA(val)函数。
- INET_ATON(IP)
例：SELECT INET_ATON('192.168.220.110');
- INET_NTOA(val): 返回网络字节序val代表的IP地址。
例：SELECT INET_NTOA(3232291950);
114
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
