MYSQL数据库特殊查询-INFORMATION_SCHEMA

>1. 查看所有数据库

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

>2. 检查特定数据库中的所有表

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

>3. 查看表的字段详情

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

>4. 检查索引信息

SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

>5. 查看表空间使用情况

SELECT table_schema "Database",
       table_name "Table",
       round(((data_length + index_length) / 1024 / 1024), 2) "Size (MB)"
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY (data_length + index_length) DESC;

>6.查询数据库your_database_name 包含表your_table_name的所有表明

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name' 
AND COLUMN_NAME IN ('your_table_name1', 'your_table_name2','your_table_name3')
GROUP BY TABLE_NAME;