mysql如何查询所有表和字段信息

mysql查询所有表和字段信息的方法：1、根据库名获取所有表的信息【information_schema.`TABLES`】；2、根据库名获取所有的字段信息【ORDINAL_POSITION AS &＃39;列的排列顺序&＃39;】。

mysql查询所有表和字段信息的方法：

1、根据库名获取所有表的信息

SELECT
    *
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = &＃39;erp&＃39;;

2、根据库名获取所有表名称和表说明

SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = &＃39;erp&＃39;;

view:

3、根据库名获取所有的字段信息

SELECT
    TABLE_SCHEMA AS &＃39;库名&＃39;,
    TABLE_NAME AS &＃39;表名&＃39;,
    COLUMN_NAME AS &＃39;列名&＃39;,
    ORDINAL_POSITION AS &＃39;列的排列顺序&＃39;,
    COLUMN_DEFAULT AS &＃39;默认值&＃39;,
    IS_NULLABLE AS &＃39;是否为空&＃39;,
    DATA_TYPE AS &＃39;数据类型&＃39;,
    CHARACTER_MAXIMUM_LENGTH AS &＃39;字符最大长度&＃39;,
    NUMERIC_PRECISION AS &＃39;数值精度(最大位数)&＃39;,
    NUMERIC_SCALE AS &＃39;小数精度&＃39;,
    COLUMN_TYPE AS 列类型,
    COLUMN_KEY &＃39;KEY&＃39;,
    EXTRA AS &＃39;额外说明&＃39;,
    COLUMN_COMMENT AS &＃39;注释&＃39;
FROM
    information_schema.`COLUMNS`
WHERE
    TABLE_SCHEMA = &＃39;erp&＃39;
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

view:

4、根据库名获取所有的库和表字段的基本信息

SELECT
    C.TABLE_SCHEMA AS &＃39;库名&＃39;,
    T.TABLE_NAME AS &＃39;表名&＃39;,
    T.TABLE_COMMENT AS &＃39;表注释&＃39;,
    C.COLUMN_NAME AS &＃39;列名&＃39;,
    C.COLUMN_COMMENT AS &＃39;列注释&＃39;,
    C.ORDINAL_POSITION AS &＃39;列的排列顺序&＃39;,
    C.COLUMN_DEFAULT AS &＃39;默认值&＃39;,
    C.IS_NULLABLE AS &＃39;是否为空&＃39;,
    C.DATA_TYPE AS &＃39;数据类型&＃39;,
    C.CHARACTER_MAXIMUM_LENGTH AS &＃39;字符最大长度&＃39;,
    C.NUMERIC_PRECISION AS &＃39;数值精度(最大位数)&＃39;,
    C.NUMERIC_SCALE AS &＃39;小数精度&＃39;,
    C.COLUMN_TYPE AS 列类型,
    C.COLUMN_KEY &＃39;KEY&＃39;,
    C.EXTRA AS &＃39;额外说明&＃39;
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 = &＃39;erp&＃39; 
ORDER BY
    C.TABLE_NAME,
    C.ORDINAL_POSITION;

view:

更多相关免费学习推荐：mysql教程(视频)

以上就是mysql如何查询所有表和字段信息的详细内容，更多请关注 第一PHP社区 其它相关文章！