MySQL_0n-常见函数-211116

常用命令临时记一下

查看字符集：show variables like ‘%char%’;

1. 数值函数

1.1基本函数

含义	MySQL函数	HIVE函数	MaxCompute函数
返回x的绝对值	ABS(x)
返回x的符号。正数返回1，负数返回0，0返回0	SIGN(x)
返回圆周率	PI()
返回大于或等于某个值的最小整数。向上取整	CEIL(x),CEILING(x) 举例ceil(1.23)=2
返回小于或等于某个值的最大整数。向下取整	FLOOR(x)
返回列表中的最小值	LEAST(e1,e2,e3…)
返回列表中的最大值	GREATEST(e1,e2,e3…)
返回x除以y后的余数	MOD(x,y)
返回0-1的随机值	RAND()
返回0-1的随机值，其中x的值用作种子值，相同的x值会产生相同的随机数	RAND(x)
返回一个对x值进行四舍五入后，接近于x的整数	ROUND(x)
返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后面y位	ROUND(x,y)
返回数字截断为y位小数的结果	TRUNCATE(x,y)
返回x的平方根。当x的值为负数时，返回null	SQRT(x)

1.2 角度弧度函数

含义	MySQL函数	Hive函数	MaxCompute函数
将角度转化为弧度，其中，参数x为角度值	RADIANS(x)
将弧度转化为角度，其中，参数x为弧度值	DEGREES(x)

1.3三角函数

含义	MySQL函数	HIVE函数	MaxCompute函数
返回x的正弦值，其中，参数x为弧度值	SIN(x) 举例sin(RADIANS(30))=0.49999…
返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL	ASIN(x)
返回x的余弦值，其中，参数x为弧度值	COS(x)
返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL	ACOS(x)
返回x的正切值，其中，参数x为弧度值	TAN(x)
返回x的反正切值，即返回正切值为x的值	ATAN(x)
返回两个参数的反正切值	ATAN2(m,n)
返回x的余切值，其中，X为弧度值	COT(x)

1.4 数学函数

含义	MySQL函数	Hive函数	MaxCompute函数
返回x的y次方	POW(x,y)，POWER(X,Y)
返回e的X次方，其中e是一个常数，2.718281828459045	EXP(X)
返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL	LN(X)，LOG(X)
返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL	LOG10(X)
返回以2为底的X的对数，当X <= 0 时，返回NULL	LOG2(X)

1.5 进制转换函数

含义	MySQL函数	Hive函数	MaxCompute函数
返回x的二进制编码	BIN(x)
返回x的十六进制编码	HEX(x)
返回x的八进制编码	OCT(x)
返回f1进制数变成f2进制数	CONV(x,f1,f2)

2. 字符串函数

含义	MySQL函数	Hive函数	MaxCompute函数
返回字符串S终端第一个字符的ASCII码的值	ACSII(S)
返回字符串s的字符数。作用与character_length(s)相同	char_length(s)
返回字符穿s的字节数，和字符集有关	length(s)
连接s1,s2…sn为一个字符串	CONCAT(s1,s2…sn)
用连接参数separator连接s1,s2…sn为一个字符串	CONCAT_WS(separator,s1,s2…sn)
将字符串str从idx位置开始，len个字符长的子串转换为字符串replacestr	insert(str,idx,len,repalcestr)
用字符串b替换字符串str中所有的a	repalce(str,a,b)
将字符串中所有的字母转大写	upper(str) 或 ucase(str)
将字符串中所有的字母转小写	lower(str) 或 lcase(str)
用字符串pad对str最左边开始填充，直到str的长度为len个字符	lpad(str,len,pad)
用字符串pad对str最右边开始填充，直到str的长度为len个字符	rpad(str,len,pad)
去掉字符串str左侧的空格	ltrim(str)
去掉字符串str右侧的空格	rtrim(str)
去掉字符串str开头和结尾的空格	trim(str)
去掉字符串str开头和结尾的str2	trim(str2 FROM str1)
返回str重复n次的结果	repeat(str,n)
返回n个空格	space(n)
比较字符串s1，s2的ascii码值的大小	strcmp(s1,s2)
返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0	LOCATE(substr,str)
返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn	ELT(m,s1,s2,…,sn)
返回str反转后的字串	reserve(str)
比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1	nullif(vallue1,value2)

3. 日期和时间函数

3.1 获取日期和时间

含义	MySQL函数	Hive函数	MaxCompute函数
返回当前日期，只包含年、月、日	CURDATE() ，CURRENT_DATE()
返回当前时间，只包含时、分、秒	CURTIME() ， CURRENT_TIME()
返回当前系统日期和时间	NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()
返回UTC（世界标准时间）日期	UTC_DATE()
返回UTC（世界标准时间）时间	UTC_TIME()返回具体的日期值

3.2 日期和时间戳转换

含义	MySQL函数	Hive函数	MaxCompute函数
以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884	UNIX_TIMESTAMP()
将时间date以UNIX时间戳的形式返回。	UNIX_TIMESTAMP(date) 将
将UNIX时间戳的时间转换为普通格式的时间	FROM_UNIXTIME(timestamp)

3.3 获取月份、星期、星期数、天数等函数

含义	MySQL函数	Hive函数	MaxCompute函数
返回具体的日期值	YEAR(date) / MONTH(date) / DAY(date) ps: MONTH(NOW()) – 返回1
返回具体的时间值	HOUR(time) / MINUTE(time) / SECOND(time)
返回月份：January，…	MONTHNAME(date)
返回星期几：MONDAY，TUESDAY…SUNDAY	DAYNAME(date)
返回周几，注意，周1是0，周2是1，。。。周日是6	WEEKDAY(date)
返回一年中的第几周	WEEK(date) ， WEEKOFYEAR(date)
返回日期是一年中的第几天	DAYOFYEAR(date)
返回日期位于所在月份的第几天	DAYOFMONTH(date)
返回周几，注意：周日是1，周一是2，。。。周六是	DAYOFWEEK(date)

--3.3 返回具体的时间值
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()) FROM DUAL; -- 返回2022	1	16

3.4 EXTRACT(type FROM date)函数

含义	MySQL函数	Hive函数	MaxCompute函数
返回指定日期中特定的部分，type指定返回的值	EXTRACT(type FROM date)

EXTRACT(type FROM date)函数中type的取值与含义：

3.5 时间和秒钟的转换

含义	MySQL函数	Hive函数	MaxCompute函数
将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟 *60+秒	TIME_TO_SEC(time)
将 seconds 描述转化为包含小时、分钟和秒的时间	SEC_TO_TIME(seconds)

3.6 计算日期和时间的函数

第一组：

含义	MySQL函数	Hive函数	MaxCompute函数
返回与给定日期时间相差INTERVAL时间段的日期时间	DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间 ps: DATE_ADD(NOW(),INTERVAL -1 DAY) – 返回 2022-01-15 14:28:31
返回与date相差INTERVAL时间间隔的日期	DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type)

上述函数中type的取值：

第二组：

含义	MySQL函数	Hive函数	MaxCompute函数
返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数	ADDTIME(time1,time2)
返回time1减去time2后的时间。当time2为一个数字时，代表的 是 秒 ，可以为负数	SUBTIME(time1,time2)
返回date1 - date2的日期间隔天数	DATEDIFF(date1,date2)
返回time1 - time2的时间间隔	TIMEDIFF(time1, time2)
返回从0000年1月1日起，N天以后的日期	FROM_DAYS(N)
返回日期date距离0000年1月1日的天数	TO_DAYS(date)
返回date所在月份的最后一天的日期	LAST_DAY(date)
针对给定年份与所在年份中的天数返回一个日期	MAKEDATE(year,n)
将给定的小时、分钟和秒组合成时间并返回	MAKETIME(hour,minute,second)
返回time加上n后的时间	PERIOD_ADD(time,n)

3.7 日期的格式化和解析

含义	MySQL函数	Hive函数	MaxCompute函数
按照字符串fmt格式化日期date的值	DATE_FORMAT(date,fmt) ps：datetime也可以
按照字符串fmt格式化时间time的值	TIME_FORMAT(time,fmt)
返回日期字符串的显示格式	GET_FORMAT(date_type,format_type) ps: date_format(now(),get_format(datetime,‘ISO’)) – 返回2022-01-01 13:01:01
按照字符串fmt对str进行解析，解析为一个日期	STR_TO_DATE(str,fmt) ps: STR_TO_DATE(‘2022-January-15 11:53:28’,’%Y-%M-%d %H:%i:%s’) – 返回2022-01-15 11:53:28

格式符 说明
**%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
%% 表示%

GET_FORMAT函数中date_type和format_type参数取值如下:

含义	MySQL函数	Hive函数	MaxCompute函数

---

未完。