【MySQL】第六部分 单行函数
文章目录
6. 单行函数
6.1 常用的函数
| 函数 | 用法 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN(X) | 返回X的符号。正数返回1，负数返回-1，0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(x)，CEILING(x) | 返回该值向上取整 |
| FLOOR(x) | 返回该值向下取整 |
| LEAST(e1,e2,e3…) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
| MOD(x,y) | 返回X除以Y后的余数 |
| RAND() | 返回0~1的随机值 |
| RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 |
| ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 |
| ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
| SQRT(x) | 返回x的平方根。当X的值为负数时，返回NULL |
SELECT ABS(123),ABS(-123),SIGN(5),SIGN(-5),SIGN(0),PI(),CEILING(4.8),FLOOR(4.8),
LEAST(12,45,22),GREATEST(45,55,89),MOD(18,5),RAND(),RAND()*10,RAND(1)
FROM DUAL;
SELECT ROUND(4.5),ROUND(4.2),ROUND(4.556,2),ROUND(RAND()*10),TRUNCATE(4.563,1),SQRT(4)
FROM DUAL;
6.2 角度与弧度转换函数
| 函数 | 用法 |
|---|---|
| RADIANS(x) | 将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) | 将弧度转化为角度，其中，参数x为弧度值 |
SELECT RADIANS(57.3),DEGREES(1),RADIANS(DEGREES(2*PI())),DEGREES(2*PI())
FROM DUAL;
6.3 三角函数
| 函数 | 用法 |
|---|---|
| SIN(x) | 返回x的正弦值，其中，参数x为弧度值 |
| ASIN(x) | 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| COS(x) | 返回x的余弦值，其中，参数x为弧度值 |
| ACOS(x) | 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| TAN(x) | 返回x的正切值，其中，参数x为弧度值 |
| ATAN(x) | 返回x的反正切值，即返回正切值为x的值 |
| ATAN2(m,n) | 返回两个参数的反正切值 |
| COT(x) | 返回x的余切值，其中，X为弧度值 |
SELECT SIN(RADIANS(30)),COS(RADIANS(30))
FROM DUAL;
6.4 指数与对数
| 函数 | 用法 |
|---|---|
| POW(x,y)，POWER(X,Y) | 返回x的y次方 |
| EXP(X) | 返回e的X次方，其中e是一个常数，2.718281828459045 |
| LN(X)，LOG(X) | 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG10(X) | 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG2(X) | 返回以2为底的X的对数，当X <= 0 时，返回NULL |
SELECT POW(2,3),POWER(2,3),EXP(2),LN(10),LOG(10),LOG10(10),LOG2(2)
FROM DUAL;
6.5 进制转换
| 函数 | 用法 |
|---|---|
| BIN(x) | 返回x的二进制编码 |
| HEX(x) | 返回x的十六进制编码 |
| OCT(x) | 返回x的八进制编码 |
| CONV(x,f1,f2) | 将x当成f1进制,返回f1进制数变成f2进制数 |
SELECT BIN(10),OCT(10),HEX(10),CONV(10,2,8)
FROM DUAL;
6.6 字符串函数
注意：MySQL中，字符串的位置是从1开始的 !!!
| 函数 | 用法 |
|---|---|
| ASCII(S) | 返回字符串S中的第一个字符的ASCII码值 |
| CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关,utf8中文占3个字节 |
| CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
| CONCAT_WS(x, s1,s2,…,sn) | 以x进行连接s1,s2…sn |
| INSERT(str, idx, len, replacestr) | 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr |
| REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
| UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(str,n) | 返回字符串str最左边的n个字符 |
| RIGHT(str,n) | 返回字符串str最右边的n个字符 |
| LPAD(str, len, pad) | 用字符串pad对str最左边进行填充，直到str的长度为len个字符,效果是右对齐 |
| RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充，直到str的长度为len个字符,效果是左对齐 |
| LTRIM(s) | 去掉字符串s左侧的空格 |
| RTRIM(s) | 去掉字符串s右侧的空格 |
| TRIM(s) | 去掉字符串s开始与结尾的空格 |
| TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
| REPEAT(str, n) | 返回str重复n次的结果 |
| SPACE(n) | 返回n个空格 |
| STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小,s1大于s2返回1,反之-1,如果相等返回0 |
| SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同 |
| LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0 |
| ELT(m,s1,s2,…,sn) | 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn |
| FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
| FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
| REVERSE(s) | 返回s反转后的字符串 |
| NULLIF(value1,value2) | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 |
SELECT ASCII('hello'),CHAR_LENGTH('世界'),LENGTH('世界'),CONCAT('hello ','world'),CONCAT_WS('-','2022','12','12'),
INSERT('hello',2,2,'adb'),REPLACE('hello world','l','e'),UPPER('ssss'),LOWER('ADSAD'),LEFT('hello',2),RIGHT('hello',2)
FROM DUAL;
SELECT LTRIM(' hello'),RTRIM('sdas '),TRIM(' dsad dsad '),TRIM('ll' FROM 'helloll'),
REPEAT('world ',3),LENGTH(SPACE(5)),STRCMP('asa','asc'),SUBSTR('hello',2,4),LOCATE('l','hello')
FROM DUAL;
6.7 日期时间函数
6.7.1 获取时间和日期
| 函数 | 用法 |
|---|---|
| CURDATE() 或 CURRENT_DATE() | 返回当前日期，只包含年、月、日 |
| CURTIME() 或 CURRENT_TIME() | 返回当前时间，只包含时、分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期和时间 |
| UTC_DATE() | 返回UTC（世界标准时间）日期 |
| UTC_TIME() | 返回UTC（世界标准时间）时间 |
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;
6.7.2 日期与时间戳的转换
| 函数 | 用法 |
|---|---|
| UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。 |
| UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
| FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
SELECT UNIX_TIMESTAMP(NOW()) , UNIX_TIMESTAMP('2022-01-22 15:33:14'),FROM_UNIXTIME(1642836794)
FROM DUAL;
6.7.3 获取月份、星期、星期数、天数等
| 函数 | 用法 |
|---|---|
| YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
| HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
| MONTHNAME(date) | 返回月份：January，… |
| DAYNAME(date) | 返回星期几：MONDAY，TUESDAY…SUNDAY |
| WEEKDAY(date) | 返回周几，注意，周1是0，周2是1，。。。周日是6 |
| QUARTER(date) | 返回日期对应的季度，范围为1～4 |
| WEEK(date) ， WEEKOFYEAR(date) | 返回一年中的第几周 |
| DAYOFYEAR(date) | 返回日期是一年中的第几天 |
| DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
| DAYOFWEEK(date) | 返回周几，注意：周日是1，周一是2 … 周六是7 |
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME(NOW()),DAYNAME(CURDATE()),WEEKDAY(NOW()),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
6.7.4 日期的操作函数
EXTRACT(type FROM date)函数中type的取值与含义：
| type取值 | 含义 |
|---|---|
| MICROSECOND | 返回毫秒数 |
| SECOND | 返回秒数 |
| MINUTE | 返回分钟数 |
| HOUR | 返回小时数 |
| DAY | 返回天数 |
| WEEK | 返回日期在一年中的第几个星期 |
| MONTH | 返回日期在一年中的第几个月 |
| QUARTER | 返回日期在一年中的第几个季度 |
| YEAR | 返回日期的年份 |
| SECOND_MICROSECOND | 返回秒和毫秒值 |
| MINUTE_MICROSECOND | 返回分钟和毫秒值 |
| MINUTE_SECOND | 返回分钟和秒值 |
| HOUR_MICROSECOND | 返回小时和毫秒值 |
| HOUR_SECOND | 返回小时和秒值 |
| HOUR_MINUTE | 返回小时和分钟值 |
| DAY_MICROSECOND | 返回天和毫秒值 |
| DAY_SECOND | 返回天和秒值 |
| DAY_MINUTE | 返回天和分钟值 |
| DAY_HOUR | 返回天和小时 |
| YEAR_MONTH | 返回年和月 |
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()),
EXTRACT(DAY_HOUR FROM NOW())
FROM DUAL;
6.7.5 时间和秒钟转换的函数
| 函数 | 用法 |
|---|---|
| TIME_TO_SEC(time) | 将当前时间转化为秒并返回结果值。转化的公式为：小时*3600+分钟*60+秒 |
| SEC_TO_TIME(seconds) | 将秒转换为时间 |
SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(57350)
FROM DUAL;
6.7.6 计算日期和时间的函数
| 函数 | 用法 |
|---|---|
| DATE_ADD(date INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) | 返回当前时间根据type进行相加expr |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) | 返回当前时间根据type进行相减expr |
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1, DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col2, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col3, #需要单引号
DATE_SUB(NOW(),INTERVAL 10 DAY) AS col4
FROM DUAL;
| 函数 | 用法 |
|---|---|
| ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数 |
| SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数 |
| DATEDIFF(date1,date2) | 返回date1 – date2的日期间隔天数 |
| TIMEDIFF(time1, time2) | 返回time1 – time2的时间间隔 |
| FROM_DAYS(N) | 返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) | 返回time加上n后的时间 |
SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:1'),DATEDIFF(NOW(),'2022-10-01'),
TIMEDIFF(NOW(),'2022-10-02 22:10:10'),FROM_DAYS(355),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;
举例：查询 7 天内的新增用户数有多少？
SELECT COUNT(*) as num
FROM info_user
WHERE DATEDIFF(NOW(),regist_time) <=7
6.7.7 日期的格式化与解析
| 函数 | 用法 |
|---|---|
| DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03。。。） |
| %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) |
| %e | 数字形式表示月中的天数（1,2,3,4,5…） | ||
| %H | 两位数字表示小数，24小时制（01,02…） | %h和%I | 两位数字表示小时，12小时制（01,02…） |
| %k | 数字形式的小时，24小时制(1,2,3) | %l | 数字形式表示小时，12小时制（1,2,3,4…） |
| %i | 两位数字表示分钟（00,01,02） | %S和%s | 两位数字表示秒(00,01,02…) |
| %W | 一周中的星期名称（Sunday…） | %a | 一周中的星期缩写（Sun.，Mon.,Tues.，…） |
| %w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
| %j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 |
| %u | 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 | ||
| %T | 24小时制 | %r | 12小时制 |
| %p | AM或PM | %% | 表示% |
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%H:%i:%s')
FROM DUAL;
| 函数 | 用法 |
|---|---|
| GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
此方法主要是配合上述三个函数的使用
SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA')),TIME_FORMAT(CURTIME(),GET_FORMAT(TIME, 'USA'))
FROM DUAL;
总结
以上就是今天要讲的内容，希望对大家有所帮助！！！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之家整理，本文链接：https://www.bmabk.com/index.php/post/82807.html
