MySQL 常用SQL整理

导入sql文件

$ source   d:/myprogram/database/db.sql;

查询

查询右表为空的数据

select 
    rr.recharge_id, rr.cost_record_id, rr.agent_id, rr.user_id, rr.user_type, rr.amount, rr.cooperation_mode,  
    cr.amount as costAmount, cr.create_time as costCreateTime  
from t_recharge_revenue rr 
left join t_cost_record cr on cr.id=rr.cost_record_id
where rr.recharge_id>0 and cr.id is null;

统计单字段值重复的数据条数

select `id`,`name`,count(id) as count from `item` 
where `MerchantId`=10120685 
group by Barcode having count>1;

多个子表合并成一个总表

-- UNION会做去重, 性能低
-- UNION ALL 不会做去重, 一般建议使用UNION ALL, 代码中去重

CREATE TABLE user_all
select phone_number,real_name from user_active
UNION ALL
select phone_number,real_name from user_leader
UNION ALL
select phone_number,real_name from user_rich;

查询树形结构

只支持二级分类

SELECT 
    t1.level '行业等级', t1.name '行业名称',
    t2.level '行业等级', t2.name '行业名称'
FROM z_product_industry t1
LEFT JOIN z_product_industry t2
ON t1.id = t2.parent_id
where t1.parent_id is null;

删除

删除右表中不存在的数据

delete t_recharge_revenue from t_recharge_revenue, (
	select rr.id
	from t_recharge_revenue rr 
	left join t_cost_record cr on cr.id=rr.cost_record_id
	where rr.recharge_id>0 and cr.id is null
) tmp
where t_recharge_revenue.id=tmp.id;

删除重复数据,只保留1条

DELETE FROM es_cart WHERE cart_id NOT IN (
    SELECT dt.cart_id FROM ( 
        SELECT MIN(cart_id) AS cart_id
        FROM es_cart
        GROUP BY member_id,goods_id
    ) dt
)

写入时跳过错误

INERT INTO t_xxx (`id`, `name`)
VALUES (1, 'Demon-HY');

更新

写入重复时更新

INERT INTO t_xxx (`id`, `name`)
VALUES (1, 'Demon-HY')
ON DUPLICATE KEY UPDATE
name='Demon-HY';

查看 & 设置锁超时时间

SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
SET GLOBAL innodb_lock_wait_timeout=1500;

only_full_group_by

Mysql 8.0 以上版本, 去掉 this is incompatible with sql_mode=only_full_group_by 限制

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';