mysql日常有哪些用_MySQL常用

1、查看当前所有数据库，show databases;

2、查看当前使用的数据库select database();

3、进入数据库 use backlog;查看所有表 show tables;

4、查看某表里的字段，desc UNDOINFO;

5、查看建表语句，show create table UNDOINFO\G;

6、查看当前的mysql数据库中，有哪些客户端保持了连接，每个客户端分别保持了多少连接

SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state,host_name;

7、查看数据库所有用户信息，select host，user from mysql.user;

8、查看数据库的数据大小，进入数据库 information_schema；

use information_schema；

9、查看库backlog的大小；

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

10、查看backlog库中UNDOINFO表的大小；

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='backlog' and table_name='UNDOINFO';

11、查看指定库的索引大小；

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'backlog';

12、查看指定库指定表的索引大小；

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'backlog' and table_name='UNDOINFO';