Shulou(Shulou.com)06/01 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.