MySql 非常用语法笔记

联表更新

update
    record_detail d
inner join record r on
    d.record_id = r.id 
    set
    d.column1 = d.column1 / 100,
    d.column2 = d.column2 / 100
where
    r.column1 = 1

复制表

//复制表结构及数据
CREATE TABLE 新表 SELECT * FROM 旧表

//复制表结构
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2

CREATE TABLE 新表 LIKE 旧表

//复制数据(表结构一样)
INSERT INTO 新表 SELECT * FROM 旧表

//复制数据(表结构不一样)
INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表

IF CASE

//case when
select
user_id,
year,
(CASE
    WHEN year = 2020 THEN '去年'
    WHEN year = 2019 THEN '前年'
    ELSE '其他'
END) as year_title
from
year_report

//if
select
user_id,
year,
IF(count_year = 2020, '去年', '前年') as year_title
from
year_report

ON DUPLICATE KEY UPDATE

//释义为 若判断该数据不存在，则执行insert，若存在则将update后面的列更新为对应的值

//判断数据存在的条件为唯一索引（会引起DUPLICATE KEY报错的条件）

ON DUPLICATE KEY UPDATE 
column1 = value1,
column2 = value2, 
column3 = value3, ...;

清空表数据

//truncate直译为截断，作用是清空表，且只能作用于表；
truncate table tbl_name 或 truncate tbl_name

清理数据碎片

//重建整个表，删掉未使用的空白空间，重新整理在聚簇索引上的数据和索引
ALTER TABLE table_name ENGINE= INNODB;

重命名表

alter table old_table_name rename new_table_name;

重命名列

alter table table_name change column old_column_name new_columen_name type;

优化分页查询

select * from table inner join (select id from table limit 1000, 100) tmp using (id)

DISTINCT 区分大小写

select distinct BINARY column from table;

UNION ALL 去重

SELECT
    a,
    b,
    c 
FROM
(SELECT *,
    ROW_NUMBER() OVER ( PARTITION BY a ORDER BY flag DESC ) AS rank 
    FROM
    (SELECT
        a,
        b,
        c,
        1 AS flag 
    FROM table_1 
    UNION ALL
    SELECT
        a,
        b,
        c,
        2 AS flag 
    FROM table_2
    ) tmp 
) rk 
WHERE
rank = 1

JSON解析

SELECT JSON_EXTRACT('{"key":"name","value":"123456"}', '$.key') AS `name`;

判断包含空格

select * from table_name where column_name regexp '[[:space:]]'

根据时间格式化并分组

SELECT DATE_FORMAT( create_time, '%Y%m%d' ), count( DISTINCT user_id ) FROM table_name GROUP BY DATE_FORMAT(create_time, '%Y%m%d')