1、表关联更新:select a.`user`,a.`host`,b.db from mysql.`user` as a
left join mysql.db as b on (a.`user` = b.`user` and a.`host` = b.`host`)
where a.`user` != 'root';
更新前的查询:select * from user_bank where id = 1650;
select * from user_bank where bank_type is null;
select count(*) from user_bank where bank_type is null;
select * from bank;
select * from bank where bank_code = '03010000';
2、函数的使用
2.1、时间相关的函数select UNIX_TIMESTAMP('2015-12-11 11:24:00'); -- 日期时间转时间戳
select FROM_UNIXTIME("1449804240"); -- 时间戳转日期时间
select FROM_UNIXTIME("1449804240",'%Y-%m-%d %H:%i:%S') --含格式化的转换
select NOW(); -- 当前系统时间
select DATE_ADD(NOW(),INTERVAL 1 YEAR) -- 时间加1个年
select DATE_ADD(NOW(),INTERVAL -1 YEAR) -- 时间减1个年
select DATE_ADD(NOW(),INTERVAL 1 MONTH); -- 时间加1个月
-- 常用日期单位:MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,SECOND_MICROSECOND,MINUTE_MICROSECOND,MINUTE_SECOND,HOUR_MICROSEC,ND,HOUR_SECOND,HOUR_MINUTE,DAY_MICROSECOND,DAY_SECOND,DAY_MINUTE,DAY_HOUR,YEAR_MONTH
2.2、if函数的使用select if(host='127.0.0.1','本机','非本机') from mysql.user;
2.3、case的使用select db,
(
case
when host = "localhost" then '本机'
when host = "127.0.0.1" then '本机'
else '其他'
end
) as host,user
from mysql.db;
2.4、分组函数使用select user,count(user) from mysql.user group by user;
2.5、产生唯一值select uuid(); -- 长uuid
select UUID_SHORT(); -- 短uuid
2.6、存储过程
2.6.1、游标的使用BEGIN
DECLARE v_user varchar(50);
-- 遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标
DECLARE cur CURSOR FOR select `user` from mysql.`user`;
-- 将结束标记绑定到游标
DECLARE CONTINUE HANDLER FOR NOT found set done = TRUE;
-- 打开游标
OPEN cur;
-- 开始循环
read_loop:LOOP
-- 变量初始化
SET v_user='';
-- 提取游标数据
FETCH cur INTO v_user;
-- 声明游标结束条件
IF done THEN
LEAVE read_loop;
END IF;
-- 这里写想做的循环
SELECT v_user;
END LOOP;
-- 关闭游标
CLOSE cur;
END