终于有时间可以整理一下工作中常用的sql语句，基本的sql语句及增删改查就不说了。对于测试而言，经常用到的还是造数据，取随机数据和查询。比如造数据时，为了确保数据真实性，可能时间是随机的，用户是随机的。对null值得处理和update。下面列出我工作中使用较多的一些sql使用技巧，希望能帮助到大家。
时间处理
select UNIX_TIMESTAMP(NOW()); -- 将时间转换为时间戳 1518428395select FROM_UNIXTIME(1518428166); -- 将时间戳转换为时间 2018-02-12 17:36:06select FROM_UNIXTIME(1518428166,'%Y-%m-%d') -- 2018-02-12SELECT 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:33select 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条数据 结果1select 'a1','a2' from DUAL; select 'a1','a2' FROM DUAL UNION ALL -- 不会合并 还是2条数据 结果2select 'a1','a2' from DUAL;
其他使用技巧
select 53+IFNULL(NULL,0);-- IFNULL 返回53-- case WHENselect 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包含joinINSERT 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','111','2') -- 返回 a1112select * from t_user_register_chl t where t.user_register_chl =1 and user_register_account REGEXP '^[a-z].*$' -- 正则表达式匹配 -- 正数 负数转换select -1*55,ABS(-11);-- 向前补位 0001select LPAD('1',3,0);-- 向后补位 1000select 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
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