查询mysql所有表数据、字段信息

缺乏、安全感 2024-03-25 13:28 96阅读 0赞

##  ##

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

SELECT
        *
    FROM
        information_schema.`TABLES`
    WHERE
        TABLE_SCHEMA = 'erp';

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

SELECT
        TABLE_NAME,
        TABLE_COMMENT
    FROM
        information_schema.`TABLES`
    WHERE
        TABLE_SCHEMA = 'erp';

view:

![1be815308358b94096544cdcafbce977.png][]

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

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:

![331a66c4f1e2b627212da26d47eabd0a.png][]

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

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:

![f3a03f9083a1056ba6689f31bb0f39bf.png][]

[1be815308358b94096544cdcafbce977.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/7835d10a9d4c4db081c37ffd31173cd8.png
[331a66c4f1e2b627212da26d47eabd0a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5d32e6a5cdfd434d8cd1569f44420d31.png
[f3a03f9083a1056ba6689f31bb0f39bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/d92c1b17dd434b858e52f099b7438ad3.png