How to query all table and field information in mysql

Mysql method of querying all table and field information: 1. Get all table information based on the database name [information_schema.`TABLES`]; 2. Get all field information based on the database name [ORDINAL_POSITION AS ' column Order'】.

Mysql method of querying all table and field information:

1. Get all table information based on the library name

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

2. Get all table names and table descriptions according to the library name

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

view:

3. According to the library Get all field information by name

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. Get basic information of all library and table fields based on the library name

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:

##More related free learning recommendations: mysql tutorial(video)

The above is the detailed content of How to query all table and field information in mysql. For more information, please follow other related articles on the PHP Chinese website!