mysql常见函数总结

一、mysql函数是工作中极其常用的知识，但很多时候会经常忘记，故在在此做一个总结
1、对时间的操作的函数

//获取当前的时间 前一天
select date_sub(CURRENT_DATE,interval 1 day)
//获取当前的时间 后一天
select date_sub(CURRENT_DATE,interval -1 day)
//获取当前的时间 前一个月
select date_sub(CURRENT_DATE,interval 1 month)
//获取当前的时间 前一个年
select date_sub(CURRENT_DATE,interval 1 year)
select now(),CURRENT_DATE,CURRENT_TIME from dual

//通过对 now() 的一些处理 获取不同时间格式的时间
select date_format(now(),'%Y/%m/%d');
select time_format(now(),'%H-%i-%S');
//对时间戳转化为字符串
from_unixtime(time/1000,'%Y-%m-%d %H:%i:%s')
//将字符串转化为时间
SELECT STR_TO_DATE('2019-09-01', '%Y-%m-%d') VAULE

//对当前时间减少固定时间
//当前时间前5分钟
select now() -interval 5 MINUTE
//当前时间后5分钟
select now() +interval 5 MINUTE
//当前时间前5小时
select now() -interval 5 HOUR

//将时间戳转化为字符串
select from_unixtime(dt/1000,'%Y-%m-%d')
//获取当前时间前5天的时间的时间戳
select unix_timestamp(now() - interval 5 day)

2、对字符串操作的函数

//locate(s1,s2) 表示是s1在s2的位置（可以通过判断s1是否在存在s2）
select locate('df','sdfhf') 

//lower(s) 对s字符串转化为小写
select LOWER('GFJ')

//upper(s) 对s字符串转化为大写
select upper('Gsdf')

//left(s,2) 返回字符串的前两位
select left('Gsdf',2)

//lpad(s,len,s1) 在字符串 s 的左边 处填充字符串 s1， 使字符串长度达到 len
select lpad('34',4,'0')

//rpads,len,s1) 在字符串 s 的右边边 处填充字符串 s1， 使字符串长度达到 len
select rpad('34',4,'0')

//substr(s,start,len) 将s字符串 从第 start个字符开始，截图len长度为len的字符【同substring(s,start,len)】
select substr('abcdef',2,3)
select substring('abcdef',2,3)

//SUBSTRING_INDEX(s, 's1', n)  截取 s字符串出现s1第 n个字符的字符串（从头部到n个字符的字符串）
select SUBSTRING_INDEX('abcdef', 'c', 1)

//SUBSTRING_INDEX(s, 's1', n)  截取 s字符串出现s1第 n个字符的字符串（从尾部到n个字符的字符串）
select SUBSTRING_INDEX('abcdef', 'c', -1)

3、常用的其他函数

//convert('234.32',decimal()) 字符串 转化为保留两位小数的数字
select CONVERT('34.54534',decimal(5,2))
//cast将小数转化为保留两位小数的数字
select cast(34.5453,decimal(5,2))
//SQL_CALC_FOUND_ROWS  ，FOUND_ROWS（） 共同使用
（用于使用类似count）
select SQL_CALC_FOUND_ROWS * from sys_user
SELECT FOUND_ROWS();

//case when用法
 select case '1'
            when '1' then '男'
            when '2' then '女'
            else '其他' end

UPDATE graduates
SET income =
CASE
 WHEN income = 20000 THEN
 income * 0.5
 WHEN income = 15000 THEN
 income + 500
 ELSE income
END;

4、索引常用语句

//查看表所有的索引
show index from user_table;
// 添加索引
alter table user_table  add index id_name_index(uuid,name);
alter table  user_table  ADD UNIQUE (column)
alter table  user_table  ADD PRIMARY KEY (columnt)

//删除索引
drop index id_name_index on user_table;

5、sql进程操作（删除死锁）

//查看所有进程
show processlist;
//当前运行的所有事务  (trx_mysql_thread_id)
select * from information_schema.innodb_trx;  
//当前出现的锁  (lock_id)
select * from information_schema.innodb_locks;  
//死锁删除进程  id(show processlist 出现id列名)
kill  id；

6、对表操作的常见语句

//在name后面添加password1这一列
alter table user_table add column password1 varchar(255) after name;
//删除列
alter table user_table drop column password1;
//修改列的类型
alter table user_table modify password1 varchar(125);
//修改列名称
alter table user_table change column password    password2     varchar(255) ;

7、时区常用函数

将时间查询时间由数据库时间转化为东八区
select CONVERT_TZ('2020-01-06', @@session.time_zone,'+8:00')

#查看时区
show variables like "%time_zone%";


 ##修改mysql全局时区为北京时间，即我们所在的东8区
set global time_zone = '+8:00';
##修改当前会话时区 为utc时间
set time_zone = '+00:00';