【MySQL笔记】函数
显示当前服务器版本
select version();
显示当前用户
select user();
显示当前用户打开的数据库
select database();
连接id
select connection_id();
最后插入记录id
select last_insert_id();
被影响的记录总数
select row_count();
返回字符串value自变量的字符集
CHARSET(value)
返回字符串value的比较规则
COLLATION(value)
字符串函数
字符连接
select concat('a','b');
使用指定分隔符连接
select concat_ws('-','a','b');
数字格式话
select format(20.25,1);
转小写
select lower('MYSQL');
转大写
select upper('mysql');
左截取
select left('mysql',2);
右截取
select right('mysql',2);
获取字符串长度
select length('mysql');
删除左空格
select ltrim(' mysql');
删除右空格
select rtrim('mysql ');
删除左右空格
select trim(' mysql ');
左填充
用字符串pad对str的左边进行填充，达到n个字符串长度
LPAD(str, n, pad)
案例：
select LPAD('12345', 10, 'a')
结果：aaaaa12345
右填充
用字符串pad对str的右边进行填充，达到n个字符串长度
RPAD(str, n, pad)
替换字符
select replace('-my-sql','-','+');
截取字符
select substring('mysql',1,2);
数学函数
进一取整
select ceil(3.99);
去尾取整
select floor(3.01);
整数除法
select 5 div 3;
取余数
select 5 mod 3;
幂运算
select power(2,8);
四舍五入
select round(3.56,1);
数字截取
select truncate(20.235,2);
平均值
select avg(age);
总数
select count(age);
最大值
select max(age);
最小值
select min(age);
和值
select sum(age);
随机数
0-1以内的随机数
RAND()
日期函数
获取当前日期、时间
- CURDATE() ，CURRENT_DATE() 返回当前日期，只包含年、月、日
- CURTIME() ， CURRENT_TIME() 返回当前时间，只包含时、分、秒
- NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() /LOCALTIMESTAMP() 返回当前系统日期和时间
- UTC_DATE() 返回UTC（世界标准时间）日期
- UTC_TIME() 返回UTC（世界标准时间）时间
SELECT NOW(),CURDATE(),CURTIME(),UTC_DATE(),UTC_TIME();
获取月份、星期、星期数、天数等函数
- 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
- EXTRACT(type FROM date) 返回指定日期中特定的部分，type指定返回的值
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW())
日期与时间戳转换
- UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
- UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。
- FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(now()),FROM_UNIXTIME(1692257689)
时间和秒钟转换
- TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为： 小时3600+分钟60+秒
- SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间
SELECT TIME_TO_SEC(NOW()),SEC_TO_TIME(78774);
时间和日期计算
- DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间
- DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)返回与date相差INTERVAL时间间隔的日期
type可选值：
select date_add('2015-2-12',interval -365 day);
- 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后的时间
查询 7 天内的新增用户数有多少？
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
日期的格式化与解析
- DATE_FORMAT(date,fmt) 按照字符串fmt格式化日期date值
- TIME_FORMAT(time,fmt) 按照字符串fmt格式化时间time值
- GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式
- STR_TO_DATE(str, fmt) 按照字符串fmt对str进行解析，解析为一个日期
fmt可选值:
流程函数
如果value为true，则返回t，否则返回f
IF(value, t, f)
如果value1不为空，返回value1，否则返回value2
IFNULL(value1, value2)
如果val1为true，返回res1，… 否则返回default默认值
CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END
如果expr的值等于val1，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END
加密与解密函数
- PASSWORD(str)返回字符串str的加密版本，41位长的字符串。加密结果 不可逆 ，常用于用户的密码加密
- MD5(str)返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
- SHA(str)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全 。
- ENCODE(value,password_seed) 返回使用password_seed作为加密密码加密value
- DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value
自定义函数
create function fun_name
returns
{string|int|real|decimal}
routine_body
例（无参数）：
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y-%m-%d');
例（参数）：
CREATE FUNCTION f2(num1 INT , num2 INT)
RETURNS DECIMAL(10,2)
RETURN (num1+num2)/2;
例（复合）：
CREATE FUNCTION addusers(username VARCHAR(20))
RETURNS INT
BEGIN
INSERT user (name) VALUES (username);
RETURN LAST_INSERT_ID();
END
$$
函数案例
按天分组
select date_format(PayTime,'%Y-%m-%d 00:00:00') PayTime,count(*) OrderCount from order_master
where paytime is not null
group by date_format(PayTime,'%Y-%m-%d 00:00:00')
按小时分组
select date_format(PayTime,'%Y-%m-%d %H:00:00') PayTime,count(*) OrderCount from order_master
where paytime is not null
group by date_format(PayTime,'%Y-%m-%d %H:00:00')
