【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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/eba85fcd32fb283e83c1010d74ff64e5.png#pic_center)
SELECT ROUND(4.5),ROUND(4.2),ROUND(4.556,2),ROUND(RAND()*10),TRUNCATE(4.563,1),SQRT(4)
FROM DUAL;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/cb07de112ac6fb53612f81e19a314b26.png#pic_center)
6.2 角度与弧度转换函数
函数 | 用法 |
---|
RADIANS(x) | 将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) | 将弧度转化为角度,其中,参数x为弧度值 |
SELECT RADIANS(57.3),DEGREES(1),RADIANS(DEGREES(2*PI())),DEGREES(2*PI())
FROM DUAL;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/9a2ece66fc6b17e21dff5d74976b7d19.png#pic_center)
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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/7a21dbae4985823432936f8e9ad227c2.png#pic_center)
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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/2dbedf244ee2dc61045a3b6063a38655.png#pic_center)
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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e28ba9e4799eb629f8041ade4878a5bc.png#pic_center)
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;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/5acf0a3a03fb63c3987f5eeda79984f6.png#pic_center)
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 |
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/1159bf721739ac26a8f2199ce0b5ad7c.png#pic_center)
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) | 返回日期字符串的显示格式 |
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/88d6afe5cb485da66a01da337d19ad62.png#pic_center)
此方法主要是配合上述三个函数的使用
SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA')),TIME_FORMAT(CURTIME(),GET_FORMAT(TIME, 'USA'))
FROM DUAL;
总结
以上就是今天要讲的内容,希望对大家有所帮助!!!