mysql语句技巧

 

1，查看所有数据库大小

use information_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;

2，查看指定数据库大小

use information_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='dbname';

3，查看指定数据库中指定表的大小

use information_schema;

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='dbname' and table_name='tbname';

4，查看日志文件位置

show variables like 'general_log_file';              日志文件路径

show variables like 'log_error';                         错误日志文件路径

show variables like 'slow_query_log_file';        慢查询日志文件路径

 

5，查询重复数据

select user ,count(user) as count from  dz_wx_users GROUP BY user  HAVING count(user) >1

 

select * from dz_wx_users where user in (select user from dz_wx_users GROUP BY user HAVING count(user) >1)