1、查看当前使用的是哪个数据库

mysql> select database();

另外,在下面2个语句的输出里也能看出当前库是哪一个

mysql> show tables;

mysql> status;

2、查看MySQL版本和状态

mysql> select VERSION();

mysql> status;

3、查看MySQL实例的当前状态(参数形式)

mysql> show status;

4、查看MySQL实例的参数

mysql> show variables;

查看最大连接数

mysql> show variables like '%max_connections%';

5、查看MySQL实例当前的进程

mysql> show processlist;

6、查看实例中有哪些库、指定库中有哪些表

mysql> show databases;

mysql> use dbname;

mysql> show tables;

查看表名包含指定字符串的表

mysql> show tables like "%test%";

7、查询用户及连接权限配置

mysql> select user,host,password from mysql.user;

8、查看编码格式

查看默认的编码格式:

mysql> show variables like '%char%';

查看test数据库的编码格式:

mysql> show create database test;

查看test数据表的编码格式:

mysql> show create table test;

9、查看实例中所有数据库大小

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

10、查询实例中各个库的数据大小、索引大小

mysql> select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),' MB') as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;

11、查询实例中各个库的数据大小、索引大小、总大小

mysql> select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),' MB') as index_size,concat(round(sum(data_length+index_length)/1024/1024,2), ' MB') as total_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;

12、查看MySQL指定库中所有表的大小和记录数

mysql> SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), ' MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='dbname' order by length desc;

13、查看数据库中所有表的记录数

mysql> select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = 'dbname' order by table_rows desc;

14、查询建库、建表语句

mysql> show create database dbname;

指定库后才能查询建表语句

mysql> show create table tablename;

15、查询指定表的字段属性