mysql 常用查询语句

1.show table status from dbname\G; //查询数据库的碎片
use test;
mysql> show table status from test\G;
1. row
Name: test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 45
Avg_row_length: 364
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2015-01-14 11:25:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Data_free：不等0表示有碎片

查询所有库（除mysql库）的所有碎片信息
select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;

2.show create table from tablename //查看建表语句
3.show create function from functionname //查看创建函数语句
4 show indexes from tablename //查看表的索引
5 show variable like 'varname' //查看配置变量的值
6 show [full ] processlist //查看SQL进程列表
7 show [engine innodb] status \G; //查看[innodb]各种状态

8. show databases /show tables /desc tablename

9. show plugins //显示所有插件
   10。show table status from [dbname] where name='tablename'; //查看某库某个表的状态信息
   show slave/master status; //查看主从库的状态
   SHOW MASTER LOGS //查看binlog 日志
   SHOW BINARY LOGS //查看binlog日志
   SHOW SLAVE HOSTS //查看从服务器信息
   show binlog events\G //查看binlog事件。

   CREATE DATABASE cptest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;