MySQL常用SQL语句

作者：

在

记录一些常用的MySQL语句，方便查找翻阅。

1. 查看MySQL数据库磁盘占用大小

select 
TABLE_SCHEMA as '数据库', 
concat(truncate(sum(data_length)/1024/1024,2),'MB') as '数据容量(MB)',
concat(truncate(sum(index_length)/1024/1024,2),'MB') as '索引容量(MB)'
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc

2. 查看MySQL数据库中表的磁盘占用

select  
table_schema as '数据库',  
table_name as '表名',  
table_rows as '记录数',  
truncate(data_length/1024/1024, 2) as '数据容量(MB)',  
truncate(index_length/1024/1024, 2) as '索引容量(MB)'  
from information_schema.tables  
where table_schema='bwc_plsedu_com'
order by data_length desc, index_length desc;

3. MySQL创建、删除用户，授权、撤销授权

create user user@host identified by 'password';
# 授权
grant all privileges on db.table to user@host;
# 取消授权
revoke all privileges on test.* from 'user'@'host'；
#删除用户
drop user 'test'@'127.0.0.1';
# 刷新权限使授权生效
flush privileges;

4. 性能检查

-- 查看当前连接
SHOW PROCESSLIST;

评论

发表回复取消回复

更多文章