MySql笔记

递归查询

# 递归，查询本级及所有下级
WITH RECURSIVE temp as (
    SELECT t.* FROM t_dept t WHERE id = 0
    UNION ALL
    SELECT t.* FROM t_dept t INNER JOIN temp ON t.pid = temp.id
)
SELECT * FROM temp;

# 递归，查询本级及所有直属上级
WITH RECURSIVE temp as (
    SELECT t.* FROM t_dept t WHERE id = 8
    UNION ALL
    SELECT t.* FROM t_dept t INNER JOIN temp ON t.id = temp.pid
)
SELECT * FROM temp;

解除外键关联

-- mysql解除外键关联
set FOREIGN_KEY_CHECKS=0;
set FOREIGN_KEY_CHECKS=1;

查询以“_”下划线开头的数据

-- mysql查询name以“_”下划线开头的数据
select * from student t0 where t0.name like 'S/_%' ESCAPE '/';

查询记录大于等于2条的记录

-- 查询记录大于等于2条的记录
select id,remark,count(1) from base_area GROUP BY remark HAVING count(remark) >=2;