MYSQL 测试常用语句使用技巧

 终于有时间可以整理一下工作中常用的sql语句，基本的sql语句及增删改查就不说了。对于测试而言，经常用到的还是造数据，取随机数据和查询。比如造数据时，为了确保数据真实性，可能时间是随机的，用户是随机的。对null值得处理和update。下面列出我工作中使用较多的一些sql使用技巧，希望能帮助到大家。

时间处理

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位小数

UNION和 UNION ALL区别

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

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

-- 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

 其他使用技巧

http://www.jb51.net/article/42229.htm 随机取数据

-- 随机排序
SELECT * FROM t_game ORDER BY RAND() LIMIT 1;       

select CONCAT('a','111','2') -- 返回 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('1',3,0);
-- 向后补位  1000
select RPAD('1',3,0);

修改DDL

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; -- 增加自增列

mysql 性能分析 EXPLAIN 

https://www.cnblogs.com/EasonJim/p/8108040.html

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

-- 设置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;

关于教程：http://www.w3school.com.cn/sql/index.asp