常用Mysql小结

-- 字符串转时间 select str_to_date ( '2018-04-14 00:00:00', '%Y-%m-%d %H:%i:%s' ) date from dual -- 2018-04-14 00:00:00 --时间转字符串 select date_format ( now(), '%Y-%m-%d %H:%i:%s' ) date from dual -- 2019-10-31 16:00:51 --concat连接字符串 select concat('a','b','c') from dual -- abc select concat('a','b',null) from dual -- null select concat_ws(',','a','b',null,'c') from dual -- abc select name, group_concat(id order by id desc separator '_')as ids from ( select '小明' name, 2 id from dual union all select '小芳' name, 3 id from dual union all select '小明' name, 3 id from dual union all select '小明' name, 3 id from dual union all select '小芳' name, 1 id from dual union all select '小芳' name, 2 id from dual )AS B group by name -- name ids -- 小明 3_3_2 -- 小芳 3_2_1 --替换 select replace('mysql测试','测试','test') as column1 from dual -- mysqltest --instr SELECT INSTR("abcd",'s') from dual ; --0 SELECT INSTR("abcd",'b') from dual ; --2 --join select * from ( select '小明' name, 1 as id from dual union all select '小芳' name, 2 as id from dual ) As A left join ( select * from ( select 1 name, 1 as id from dual union all select '小芳' name, 2 as id from dual ) As C )As B on A.id =B.name --name id name(B) id(B) --小明 1 1 1 --小芳 2 null null --left join 保留左表的全部数据、右边未匹配的保留null、 --right join 保留右表的全部数据,左边未匹配的保留null --join 交集 只保留匹配的数据 --count(*) 计算查询结果集数目 select count(*) from ( select '小明' name, 1 as id from dual union all select '小芳' name, 2 as id from dual )As A --if select if(id>1,'>','<=') as ids, id from ( select 1 as id from dual union select 2 as id from dual )As A ----ids id ----<= 1 ---- > 2 --casewhen 类似 if elseif else select case when id<1 then '<' when id>1 then '>' else '=' end as compare, id from ( select 1 as id from dual union select 2 as id from dual )As A -- compare id = 1 > 2 --cast SELECT cast( 1.3666 AS DECIMAL ( 10, 2 ) ) FROM DUAL ---1.37 --IFNULL select IFNULL(null,2) from dual --2 --distinct 去重 select distinct id from (select 1 as id from dual union all select 1 as id from dual) A --id --1 --like -- _占一个位置 %占多个位置 select id from (select 'AB' as id from dual union all select 'ABC' as id from dual) A where id like 'A_' -- id -- AB select id from (select 'A' as id from dual union all select 'ABC' as id from dual) A where id like 'A%' --id -- A -- ABC --between 范围查找 包含边界 SELECT id FROM ( SELECT 1 AS id FROM DUAL UNION SELECT 2 AS id FROM DUAL UNION SELECT 4 AS id FROM DUAL UNION SELECT 3 AS id FROM DUAL ) AS A WHERE id BETWEEN 1 AND 3 -- id -- 1 -- 2 -- 3 --in 存在性查找 SELECT id FROM ( SELECT 1 AS id FROM DUAL UNION SELECT 2 AS id FROM DUAL UNION SELECT 4 AS id FROM DUAL UNION SELECT 3 AS id FROM DUAL ) AS A WHERE id in(1,3) -- id -- 1 -- 3 --dateadd 时间处理 --second、minute、hour、day、week、month、year select date_add(now(),interval -30 minute) from dual --提前30分钟 --datediff 时间差 SELECT DATEDIFF('2008-12-30','2008-12-31') AS DiffDate --- 1 select TIMESTAMPDIFF(DAY, '2018-03-20 09:00:00', '2018-03-22 08:00:00'); -- 1 向下取整 select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00'); -- 49 --limit 分页检索 limit a,b; --a起始数(从0开始),b每页的展示数目 select * from table limit 5,10 -- 检索记录行6-15 select * from table limit 5 -- 返回前5行记录 --REGEXP 正则匹配 成功返回1 否则0 SELECT 'He2n' REGEXP '[0-9]'; -- 1 SELECT 'Hecn' REGEXP '[0-9]'; --0