1.explain

explain是mysql的查询分析计划

explain select * from user \G;

explain的partitions操作

explain partitions select * from user

explain的extended操作提供explain额外的filtered列

explain extended select * from test1 t1 inner join test2 t2 using(uid)

2.show warnings

关于show可参考mysql文档http://dev.mysql.com/doc/refman/5.7/en/show.html

show warnings用于查看生成的 警告

show warnings \G;

3.show create table

show create table用于呈现表的基本信息

show create table user \G;

4.show indexes

show indexes查看索引信息

show indexes from user \G;

5.show table status

show table status用于查看数据表的大小、结构信息

show table status like 'user' \G;

6.show status

show global status

show session status like 'kk';

7.show engine status命令

show engine status命令用查看mysql使用引擎的状态,为了方便重定向输出的文本文件，并\G格式化

mysql -e "show engine innodb status\G" -u[name] -p[password] [database] > /usr/local/status.txt

8.show procedure status查看存储过程状态

8.1查看数据库所有存储过程状态

mysql> SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
                  Db: test
                Name: sp1
                Type: PROCEDURE
             Definer: testuser@localhost
            Modified: 2004-08-03 15:29:37
             Created: 2004-08-03 15:29:37
       Security_type: DEFINER
             Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

8.2查看指定存储过程的状态

mysql> SHOW PROCEDURE STATUS LIKE 'sp1'\G
*************************** 1. row ***************************
                  Db: test
                Name: sp1
                Type: PROCEDURE
             Definer: testuser@localhost
            Modified: 2004-08-03 15:29:37
             Created: 2004-08-03 15:29:37
       Security_type: DEFINER
             Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

9.设置数据字符编码集

在使用数据库时有时可能需要改变数据的字符编码

9.1查看当前数据的编码

//toggle
mysql> use xxx;
//查看数据库编码
mysql>show variables like 'character_set_database';
//update CHARACTER
mysql>alter database xxx CHARACTER SET utf-8;

10.获取数据库表的schema

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'edu' AND TABLE_NAME = 't_boss';

11.获取数据表每一列的schema

show columns from pma_designer_coords;