How does mysql query all table and field information

Editor to share with you how mysql query all the table and field information, I hope you will learn a lot after reading this article, let's discuss it together!

Mysql query the information of all tables and fields: 1, get the information of all tables according to the library name [information_ schema.`TABLES`]; 2, get all the field information according to the library name [ORDINAL_POSITION AS 'column order'].

The method for mysql to query all table and field information:

1. Get the information of all tables according to 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_COMMENTFROM information_ schema.`TABLES`Were TABLE_SCHEMA = 'erp'

View:

3. Get all the field information according to the library name

SELECT TABLE_SCHEMA AS 'library name', TABLE_NAME AS 'table name', COLUMN_NAME AS 'column name', ORDINAL_POSITION AS 'column order', COLUMN_DEFAULT AS 'default value', IS_NULLABLE AS 'whether empty', DATA_TYPE AS 'data type', CHARACTER_MAXIMUM_LENGTH AS 'maximum character length', NUMERIC_PRECISION AS 'numeric precision (maximum digits)' NUMERIC_SCALE AS 'decimal precision', COLUMN_TYPE AS column type, COLUMN_KEY 'KEY', EXTRA AS' extra description, COLUMN_COMMENT AS 'comment' FROM information_ schema.`COLUMNS`WERE TABLE_SCHEMA = 'erp'ORDER BY TABLE_NAME, ORDINAL_POSITION

View:

4. Get the basic information of all the library and table fields according to the library name

SELECT C.TABLE_SCHEMA AS 'library name', T.TABLE_NAME AS 'table name', T.TABLE_COMMENT AS 'table comment', C.COLUMN_NAME AS 'column name', C.COLUMN_COMMENT AS 'column comment', C.ORDINAL_POSITION AS 'column sort order', C.COLUMN_DEFAULT AS 'default', C.IS_NULLABLE AS 'whether empty', C.DATA_TYPE AS 'data type' C.CHARACTER_MAXIMUM_LENGTH AS 'maximum character length', C.NUMERIC_PRECISION AS 'numeric precision (maximum digits)', C.NUMERIC_SCALE AS 'decimal precision', C.COLUMN_TYPE AS column type, C.COLUMN_KEY 'KEY' C.EXTRA AS 'additional instructions' FROM information_ schema.`TABLES` TLEFT JOIN information_ schema.`COLUMNS`C ON T.TABLE_NAME = C.TABLE_NAMEAND T.TABLE_SCHEMA = C.TABLE_SCHEMAWHERE T.TABLE_SCHEMA = 'erp' ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION

View:

After reading this article, I believe you have a certain understanding of how mysql queries all tables and fields. Want to know more about it. Welcome to follow the industry information channel. Thank you for your reading!