1、基础查询

查询所有表

select a.* from information_schema.tables a
where table_schema='flowable650' and table_type='BASE TABLE';

table_schema 数据库名称
table_type 表类型 表、视图

查询字段名称

select column_name,column_comment,data_type 
from information_schema.columns 

查询所有的外键约束信息

SELECT C.TABLE_SCHEMA            拥有者,
           C.REFERENCED_TABLE_NAME  父表名称 ,
           C.REFERENCED_COLUMN_NAME 父表字段 ,
           C.TABLE_NAME             子表名称,
           C.COLUMN_NAME            子表字段,
           C.CONSTRAINT_NAME        约束名,
           T.TABLE_COMMENT          表注释,
           R.UPDATE_RULE            约束更新规则,
           R.DELETE_RULE            约束删除规则
      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
      JOIN INFORMATION_SCHEMA. TABLES T
        ON T.TABLE_NAME = C.TABLE_NAME
      JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
        ON R.TABLE_NAME = C.TABLE_NAME
       AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
       AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
 where C.REFERENCED_TABLE_NAME ='ACT_RE_PROCDEF'

2、行号

利用自定义变量rownum来显示行号

SELECT CONCAT('RU',@rownum:=@rownum+1) as id,su.id as uid
from (SELECT @rownum:=0) r,sys_user su

结果

20210105130405.png

3、递归查询树型表

利用union实现

WITH RECURSIVE cte AS
(
SELECT a.id, a.pid,a.role_name FROM sys_role a WHERE a.id='R003'
UNION ALL
SELECT k.id, k.pid,k.role_name FROM sys_role k INNER JOIN cte c ON c.id = k.pid
)SELECT id,role_name,pid FROM cte