select UNIX_TIMESTAMP(NOW());   -- 将时间转换为时间戳 1518428395

select FROM_UNIXTIME(1518428166);  -- 将时间戳转换为时间 2018-02-12 17:36:06

select FROM_UNIXTIME(1518428166,'%Y-%m-%d')  -- 2018-02-12

SELECT STR_TO_DATE(NOW(),'%Y-%m-%d'); -- 2018-02-12 java代码调用会出错

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %k:%i:%s');  -- 2018-02-12 17:44:33

select date_format(DATE_SUB(NOW(),INTERVAL +1 DAY), '%Y%m%d') -- 20180211  +为减去多少天  -为加多少天
SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY); -- 2018-02-13

set @min=1;

set @max =3;

select FLOOR(RAND() * @max-@min+1)+@min;  -- 返回1-3之间的随机数

select FLOOR(RAND() * 56); -- 产生0-56的随机整数数

SELECT FORMAT(rand()*55,2); -- 0-55随机数，保留2位小数

SELECT FORMAT(22.6655,2); --  四舍五入  保留2位小数

SELECT truncate(1.228,2); --  只舍不入  保留2位小数

select 'a1','a2' FROM DUAL UNION  -- 会合并成1条数据 结果1

select 'a1','a2' from DUAL;


select 'a1','a2' FROM DUAL UNION ALL  -- 不会合并 还是2条数据 结果2

select 'a1','a2' from DUAL;

select 53+IFNULL(NULL,0);-- IFNULL 返回53

-- case WHEN

select uid, (CASE ISNULL(leader_uid) WHEN TRUE THEN 0 ELSE leader_uid END ) leader_uid ,channel_id from ( select uid,b.leader_uid,channel_id from t_mer a LEFT JOIN t_mer_group b on a.uid = b.member_uid) a

-- IFNULL(expr1,expr2)

select uid, IFNULL(leader_uid,0) ,channel_id from ( select uid,b.leader_uid,channel_id from t_mer a LEFT JOIN t_mer_group b on a.uid = b.member_uid) a

update t_user_game_binding t set t.member_uid = (select uid from t_mer where channel_id = t.chl_id limit 1);

-- 比上面那个高效

UPDATE t_user_game_binding LEFT JOIN t_mer ON t_user_game_binding.chl_id = t_mer.channel_id SET member_uid = t_mer.uid

-- insert包含join

INSERT INTO t_user_game_binding_1 (user_id,outter_game_id,chl_id,member_uid)

SELECT a.user_id,a.outter_game_id,a.chl_id,b.uid FROM t_user_game_binding a LEFT JOIN t_mer b on a.chl_id=b.channel_id;

INSERT ignore t_user_game_binding_1…… -- 忽略错误

-- 根据条件insert insert into ... select ..from dual where EXISTS （）

INSERT  INTO  `test` (`t1`) SELECT 'x' from dual  where  not EXISTS  (select * from test);
-- 避免插入重复的数据详情点击  https://www.cnblogs.com/duanxiaojun/p/6855680.html?utm_source=itdadao&utm_medium=referral

-- 随机排序

SELECT * FROM t_game ORDER BY RAND() LIMIT 1;       

select CONCAT('a','','') -- 返回 a1112

select * from t_user_register_chl t where t.user_register_chl =1 and user_register_account REGEXP '^[a-z].*$' -- 正则表达式匹配

-- 正数 负数转换

select -1*55,ABS(-11);

-- 向前补位  0001

select LPAD('',3,0);

-- 向后补位  1000

select RPAD('',3,0);

alter table tableName modify column redeem_code VARCHAR(500) NOT NULL DEFAULT '' COMMENT '兑换码'; -- 修改字段长度

alter table t_user_checked_record ADD id BIGINT auto_increment primary key; -- 增加自增列

EXPLAIN select * from user_base_info t where uid='';

-- 设置cache大小

SET GLOBAL max_binlog_cache_size =1009715200;

SET GLOBAL binlog_cache_size =1009715200;

-- select into 一定要初始化变量

SET @id = NULL;

SET @n = NULL;

select game_id,outter_game_id INTO @id,@n from t_game where game_id%5=0 limit 1;

select @id,@n;