MySQL provides a variety of methods to obtain database metadata (information about the database and various objects in it):
*Various SHOW statements, such as SHOW DATABASES or SHOW TABLES;
*Information? Table in schema database;
*Command line programs, such as mysqlshow or mysqldump.

Use SHOW statement to get metadata

-- List accessible databases
SHOW DATABASES ;
-- Show database's CREATE DATABASE Sentence
SHOW CREATE DATABASE db_name;
-- List the default database or tables in a given database
SHOW TABLES;
SHOW TABLES FROM db_name;
-- SHOW TABLES Statement cannot be displayed temporary surface
-- Show table's CREATE TABLE Sentence
SHOW CREATE TABLE tbl_name;
-- Display column or index information in the table
-- SHOW COLUMNS And DESCRIBE tbl_name & EXPLAIN tbl_name Same meaning
SHOW COLUMNS FROM tbl_name;
SHOW INDEX FROM tbl_name;
-- Display the table description information in the default database or a given database
SHOW TABLE STATUS;
SHOW TABLE STATUS FROM db_name;
-- hold tbl_name The names listed in the table are"s" All columns at the beginning are displayed
SHOW COLUMNS FROM tbl_name LIKE 's%';

-- Identify whether a given table exists in the application
SHOW TABLES LIKE 'student';
SHOW TABLES FROM db_name LIKE 'tbl_name';
SELECT count(*) FROM tbl_name; -- Best fit MyISAM Table, not suitable InnoDB Table, because it will scan the whole table
SELECT * FROM tbl_name WHERE FALSE ; -- Universal, fast for different storage engines

Obtaining metadata with INFORMATION SCHEMA

-- query INFORMATION_SCHEMA Which tables are included in the library
SHOW TABLES IN information_schema;
-- SCHEMATA,TABLES,VIEWS,ROUTINES,TRIGGERS,EVENTS,PARAMETERS,PARTITIONS,COLUMNS
--  These are information about databases, tables, views, stored procedures, triggers, events, table partitions, and columns
-- FILES
--  Information about those files used to store tablespace data
-- TABLE_CONSTRAINTS,KEY_COLUMN_USAGE: 
--  Information about tables and columns with constraints
-- STATISTICS
--  Information about table index properties
-- REFERENTIAL_CONSTRAINTS
--  Information about foreign keys
-- CHARACTER_SETS,COLLATIONS,COLLATION_CHARACTER_SET_APPLICABILITY
--  Information about supported character sets, collations for each character set, and the mapping between each collation and its character set
-- ENGINES,PLUGINS
--  Information about storage engine and server plug-ins
-- USER_PRIVILEGES,SCHEMA_PRIVILEGES,TABLE_PRIVILEGES,COLUMN_PRIVILEGES
--  Information about permission assignments for global, database, table, and column, respectively, comes from mysql In the database user,db,tables_priv and column_priv surface
-- GLOBAL_VARIABLES,SESSION_VARIABLES,GLOBAL_STATUS,SESSION_STATUS
--  System and state variable values for global and session
-- PROCESSLIST
--  Information about execution threads in the server

-- query information_schema What columns does the inner table contain?
DESCRIBE information_schema.schemata;
-- Test whether a specific table exists:1 Existing,0 non-existent
SELECT count(*) FROM information_schema.tables
WHERE table_schema = 'sampdb' AND table_name='member';
-- Check which storage engine a table uses
SELECT engine FROM information_schema.tables
WHERE table_schema = 'sampdb' AND table_name='student';

Get metadata from the command line
windows command line or Linux command line
When using mysqlshow and mysqldump, remember to specify the necessary link parameter options, such as – host, – user, or – password

-- List databases managed by the server
mysqlshow
-- List tables in the database
mysqlshow db_name
-- Display column information in the table
mysqlshow db_name tbl_name
-- Display index information in the table
mysqlshow --keys db_name tbl_name
-- Display descriptive information of all tables in the database
mysqlshow --status db_name
-- Client program mysqldump To show you CREATE TABLE Table structure defined by statement
mysqldump --no-data db_name [tbl_name] ...