mysql（数据库）

内置库表

MySQL版本大于5.0时，有个默认数据库information_schema，里面存放着所有数据库的信息(比如表名、 列名、对应权限等)，通过这个数据库，我们就可以跨库查询，爆表爆列

查询数据库名，逗号隔开

select GROUP_CONCAT(SCHEMA_NAME) from information_schema.SCHEMATA where SCHEMA_NAME like '%库名关键字%'

爆表

selectTABLE_NAMEfrominformation_schema.TABLESwhere TABLE_SCHEMA=0×6D656D626572 limit 5,1/TABLE_SCHEMA=后面是库名的16进制

 爆字段

select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME=0×61646D5F75736572 limit 5,1/

 查询所有库学生表的字段并集

select column_name from information_schema.columns WHERE table_name = 'pe_student' GROUP BY column_name

 查询字段

select table_schema,column_name from information_schema.columns WHERE table_name = 'pe_student' and column_name like '%address%' GROUP BY column_name;
select a.* from (
select table_schema,column_name from information_schema.columns WHERE table_name = 'pe_student' GROUP BY column_name
) a
where 
column_name like '%address%'![img](http://cdn.processon.com/631004720e3e7429a06e9709?e=1661997699&token=trhI0BY8QfVrIGn9nENop6JAc6l5nZuxhjQ62UfM:FmcwDp4jwlyYB7bptrC4GvU2doU=)

SELECT
  TABLE_SCHEMA AS '数据库',
  TABLE_NAME AS '表名',
  column_name AS '字段名称',
  COLUMN_TYPE AS '字段类型',
  IS_NULLABLE AS '是否为空',
  COLUMN_DEFAULT AS '默认值',
  column_comment AS '字段备注'
FROM
  information_schema. COLUMNS
WHERE
  table_name = 'yyd_elife_city'
OR table_name = 'yyd_elife_column_list'
OR table_name = 'yyd_elife_coupon_list'
OR table_name = 'yyd_elife_goods_label_list'
OR table_name = 'yyd_elife_member_extend'
OR table_name = 'yyd_biz_content_log'
OR table_name = 'yyd_e_refund'
OR table_name = 'yyd_e_statistic_goods'
OR table_name = 'yyd_e_statistic_ip'
OR table_name = 'yyd_e_statistic_main'

-- 不存在某表的数据库
SELECT * FROM information_schema. COLUMNS WHERE NOT EXISTS （

-- 存在某表的数据库
 SELECT *  FROM information_schema. COLUMNS WHERE TABLE_NAME = 'module_enroll_user_info' GROUP BY
  TABLE_SCHEMA
  
 )
GROUP BY
 TABLE_SCHEMA;

-- 存在某个字段 
SELECT * FROM information_schema. COLUMNS
WHERE TABLE_NAME = 'module_enroll_user_info'
AND COLUMN_NAME = 'site_code'
and TABLE_SCHEMA = 'risen'


SELECT * FROM information_schema. COLUMNS
WHERE  TABLE_NAME = 'module_enroll_user_info'
AND COLUMN_NAME = 'fk_enroll_core_id'
and IS_NULLABLE = 'NO';

SELECT  * FROM  information_schema. COLUMNS
WHERE  TABLE_NAME = 'module_enroll_user_info'
AND COLUMN_NAME = 'fk_enroll_step_id'
and IS_NULLABLE = 'NO';

差集求不存在某表的数据库名
select * from (
select SCHEMA_NAME from information_schema.SCHEMATA where SCHEMA_NAME like '%risen%'
) a  left join (SELECT TABLE_SCHEMA FROM information_schema. COLUMNS
 WHERE TABLE_NAME = 'pe_student'
AND COLUMN_NAME = 'registration_date'
and TABLE_SCHEMA like '%risen%'
) b on a.SCHEMA_NAME = b.TABLE_SCHEMA
 where b.TABLE_SCHEMA is null

查询所有库指定表，没有某字段的全部数据名

select GROUP_CONCAT(SCHEMA_NAME) from information_schema.SCHEMATA where SCHEMA_NAME like '%risen%'