常用sql命令

MySQL常用命令

一个数据库实例有多个库，遍历表名称

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE';
通常应对仅丢过来表明为说清楚数据库名的情况，使用前还是要结合数据进行验证，避免多个库中有同名表导致预期不一致情况

查询数据库表结构

show create table TABLE_SCHEMA.TABLE_NAME;
查询建表语句
describe TABLE_SCHEMA.TABLE_NAME
查询表有哪些列以及关键要素
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.tables group by table_schema;
查询库大小
SELECT table_schema AS "Database", table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.tables ORDER BY (data_length + index_length) DESC;
查询表大小（包含库名、表明）