mysql查询所有表和字段信息的方法：
1、根据库名获取所有表的信息
select
*
from
information_schema.`tables`
where
table_schema = 'erp';
2、根据库名获取所有表名称和表说明
select
table_name,
table_comment
from
information_schema.`tables`
where
table_schema = 'erp';
view:
3、根据库名获取所有的字段信息
select
table_schema as '库名',
table_name as '表名',
column_name as '列名',
ordinal_position as '列的排列顺序',
column_default as '默认值',
is_nullable as '是否为空',
data_type as '数据类型',
character_maximum_length as '字符最大长度',
numeric_precision as '数值精度(最大位数)',
numeric_scale as '小数精度',
column_type as 列类型,
column_key 'key',
extra as '额外说明',
column_comment as '注释'
from
information_schema.`columns`
where
table_schema = 'erp'
order by
table_name,
ordinal_position;
view:
4、根据库名获取所有的库和表字段的基本信息
select
c.table_schema as '库名',
t.table_name as '表名',
t.table_comment as '表注释',
c.column_name as '列名',
c.column_comment as '列注释',
c.ordinal_position as '列的排列顺序',
c.column_default as '默认值',
c.is_nullable as '是否为空',
c.data_type as '数据类型',
c.character_maximum_length as '字符最大长度',
c.numeric_precision as '数值精度(最大位数)',
c.numeric_scale as '小数精度',
c.column_type as 列类型,
c.column_key 'key',
c.extra as '额外说明'
from
information_schema.`tables` t
left join information_schema.`columns` c on t.table_name = c.table_name
and t.table_schema = c.table_schema
where
t.table_schema = 'erp'
order by
c.table_name,
c.ordinal_position;
view:
