常用SQL语句

roobe

##导出所有数据库的数据

1. mysqldump -u root -p123456 --all-databases > all.sql

复制代码

##导入所有数据库的数据

1. mysql -u root -p123456 < "/root/all.sql"

复制代码

##导出整个数据库

1. mysqldump -u dbuser -p dbname > dbname.sql

复制代码

导入

1. mysql -u root -p123456 test < "/root/all.sql"

复制代码

##导出整个数据库

1. mysqldump -u dbuser -p dbname users> dbname_users.sql

复制代码

##查看所有表信息

1. SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mall'

复制代码

##查看各个表数据量

1. SELECT table_name,table_rows FROM information_schema.tables
   
2. WHERE TABLE_SCHEMA = 'mall' ORDER BY table_rows DESC;
   

复制代码

统计一下所有库数据量
每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

1. SELECT
   
2. SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
   
3. FROM information_schema.TABLES

复制代码

统计每个库大小：

1. SELECT
   
2. table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
   
3. FROM information_schema.TABLES group by table_schema;

复制代码