搬运自用： 来自：www.cnblogs.com/wchonge/p/8…
SQL语句之SHOW语句
SHOW DATABASES – 显示当前所有数据库的名称
mysql> SHOW DATABASES;
SHOW TABLES – 显示当前数据库中所有表的名称(需要设置默认数据库use DATABASE_NAME)
Mysql> SHOW TABLES;
SHOW TABLES FROM db_name – 显示数据库中的所有表
Mysql> SHOW TABLES FROM db_name;
SHOW ENGINES - 显示MySQL当前支持哪些存储引擎和默认存储引擎
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
SHOW CHARACTER SET – 显示MySQL当前支持哪些字符集
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
-------------------------------------------------------------------------
mysql> SHOW CHARACTER SET LIKE '%utf%';
+---------+------------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+------------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
+---------+------------------+--------------------+--------+
5 rows in set (0.00 sec)
SHOW COLLATION – 显示MySQL支持字符集的排序规则
mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
SHOW BINARY | MASTER – 显示二进制文件以及文件大小（需要开启二进制日志记录功能）
mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 143 |
SHOW BINLOG EVENTS – 显示二进制文件的执行过程
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.27-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Stop | 1 | 143 | |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 120;
+------------------+-----+------------+-----------+-------------+------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+------+
| mysql-bin.000001 | 120 | Stop | 1 | 143 | |
+------------------+-----+------------+-----------+-------------+------+
1 row in set (0.01 sec)
SHOW COLUMNS – 显示表的列信息（等同于DESC，需要先创建表）
mysql> SHOW COLUMNS FROM blog.info;
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | char(8) | NO | | NULL | |
| AGE | int(11) | NO | | NULL | |
| ADDRESS | varchar(20) | NO | | NULL | |
| SALARY | decimal(10,2) | NO | | NULL | |
+---------+---------------+------+-----+---------+----------------+
5 rows in set (0.06 sec)
SHOW CREATE DATABASES – 显示已经创建的库，创建时的语句
mysql> SHOW CREATE DATABASE blog;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
SHOW CREATE TABLE – 显示已经创建的表，创建时的语句
mysql> SHOW CREATE TABLE info\G
*************************** 1. row ***************************
Table: info
Create Table: CREATE TABLE `info` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` char(8) NOT NULL,
`AGE` int(11) NOT NULL,
`ADDRESS` varchar(20) NOT NULL,
`SALARY` decimal(10,2) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
SHOW CREATE FUNTCION – 显示已经创建的函数，创建时的语句
SHOW CREATE PROCEDURE – 显示已经创建的存储过程，创建时的语句
mysql> SHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
Procedure: simpleproc - 存储过程的名字
sql_mode:
Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW CREATE TRIGGER - 显示已经创建的触发器，创建时的语句
mysql> SHOW CREATE TRIGGER ins_sum\G
*************************** 1. row ***************************
Trigger: ins_sum
sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`me`@`localhost` TRIGGER ins_sum
BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
SHOW CREATE VIEW – 显示已经创建的视图，创建时的语句
mysql> SHOW CREATE VIEW v\G
*************************** 1. row ***************************
View: v
Create View: CREATE ALGORITHM=UNDEFINED
DEFINER=`bob`@`localhost`
SQL SECURITY DEFINER VIEW
`v` AS select 1 AS `a`,2 AS `b`
character_set_client: latin1
collation_connection: latin1_swedish_ci
SHOW CREATE EVENTS – 显示已经创建的事件，创建时的语句
mysql> SHOW CREATE EVENT test.e_daily\G
*************************** 1. row ***************************
Event: e_daily
sql_mode:
time_zone: SYSTEM
Create Event: CREATE EVENT `e_daily`
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'Saves total number of sessions then
clears the table each day'
DO BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW ENGINE – 显示存储引擎的详细信息
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2015-11-22 19:05:05 7f6b6c780700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds
..................
SHOW WARNINGS – 显示最后一个执行语句所产生的警告信息
)
mysql> SHOW WARNINGS\G
mysql> SELECT @@warning_count;
+-----------------+
| @@warning_count |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.01 sec)
SHOW ERRORS – 显示最后一个执行语句所产生的错误信息
mysql> SHOW ERRORS\G
SHOW EVENTS – 显示事件信息
mysql> SHOW EVENTS;
Empty set (0.02 sec)
SHOW GRANTS – 显示一个用户所拥有的权限
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR CURRENT_USER();
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
SHOW PROCESSLIST – 显示系统中正在运行的所有进程，普通用户只能查看自己的进行信息
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 6 | root | localhost | blog | Query | 0 | init | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
SHOW PRIVILEGES – 显示MySQL所支持的所有权限，及权限可操作的对象
mysql> SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
SHOW PLUGINS – 显示MySQL插件信息
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
...................
SHOW MASTER STATUS – 显示Master当前正在使用的二进制信息
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | 7040 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
SHOW TABLE STATUS – 显示表属性信息
mysql> SHOW TABLE STATUS LIKE 'info'\G
*************************** 1. row ***************************
Name: info - 表名
Engine: InnoDB - 表存储引擎
Version: 10 - 表文件版本号
Row_format: Compact - 行存储格式（固定、动态、压缩、冗余、紧凑）
Rows: 1 - 表中行数量
Avg_row_length: 16384 - 平均行长度
Data_length: 16384 - 数据文件长度
Max_data_length: 0 - 数据文件最大长度，这是可以存储在表中的数据的总字节数
Index_length: 0 - 索引文件长度
Data_free: 0 - 表文件已分配但未使用的字节数
Auto_increment: 2 - 下一个auto_increment的值
Create_time: 2015-11-22 17:53:10 - 表创建时间
Update_time: NULL - 表更新时间，没有为NULL
Check_time: NULL - 表检查时间，没有为NULL
Collation: utf8_general_ci - 表使用的排序规则
Checksum: NULL - 表校验
Create_options:
Comment:
1 row in set (0.00 sec)
SHOW INDEX – 显示表索引信息（需要先创建索引，详情看“索引文章”）
mysql> show index from testdb.info\G
*************************** 1. row ***************************
Table: info - 表名
Non_unique: 0
Key_name: PRIMARY - 索引名字
Seq_in_index: 1 - 索引中列顺序号从1开始
Column_name: ID - 索引列
Collation: A - 列怎样在索引中被排序
Cardinality: 6 - 索引中唯一值的数量
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE - 索引算法类型
Comment:
Index_comment:
1 row in set (0.01 sec)
SHOW PROCEDURE STATUS – 显示存储过程信息（需要先创建存储过程，详情请看“存储过程文章”）
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
SHOW FUNCTION STATUS – 显示存储函数信息（需要先创建存储函数，详情看“存储函数文章”）
mysql> SHOW fUNCTION STATUS;
Empty set (0.00 sec)
SHOW TRIGGERS – 显示触发器信息（需要先创建触发器，详情看“触发器部分”）
mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 1. row ***************************
Trigger: ins_sum - 触发器名字
Event: INSERT - 触发事件
Table: account - 在那个表定义触发器
Statement: SET @sum = @sum + NEW.amount - 触发体
Timing: BEFORE - 是在触发之前激活触发事件，还是在触发之后激活触发事件
Created: NULL -
sql_mode: NO_ENGINE_SUBSTITUTION - 触发器执行时的SQL模式
Definer: me@localhost - 谁创建了触发器
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
SHOW PROFILE and SHOW PROFILES – 显示执行语句的资源使用情况
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET profiling = 1;
mysql> CREATE TABLE T1 (id INT);
mysql> SHOW PROFILES;
+----------+------------+--------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------+
| 1 | 0.11698325 | CREATE TABLE T1 (id INT) |
| 2 | 0.12255200 | CREATE TABLE T2 (id INT) |
+----------+------------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000080 |
| checking permissions | 0.000015 |
| Opening tables | 0.000129 |
| creating table | 0.106344 |
| After create | 0.000033 |
| query end | 0.000066 |
| closing tables | 0.000017 |
| freeing items | 0.000241 |
| cleaning up | 0.015628 |
+----------------------+----------+
9 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000080 |
-----------------------------------
mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting | 0.000080 | 0.000000 | 0.000000 |
----------------------------------------------------------
TYPE: CPU | MEMORY | IPC | SOURCE | ALL | BLOCK IO | SWAPS
SHOW SLAVE HOSTS – 显示Master主机上已注册的复制主机列表（需要先做主从复制，详情看“主从复制部分”）
mysql> SHOW SLAVE HOSTS;
+-----------+-----------+-------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+-----------+-------+-----------+--------------------------------------+
| 192168010 | iconnect2 | 3306 | 192168011 | 14cb6624-7f93-11e0-b2c0-c80aa9429562 |
| 1921680101 | athena | 3306 | 192168011 | 07af4990-f41f-11df-a566-7ac56fdaf645 |
+------------+-----------+------+-----------+--------------------------------------+
SHOW SLAVE STATUS – 显示Slave主机状态信息（需要先做主从复制，详情看“主从复制部分”）
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 13000
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1307
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 1508
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...........
SHOW GLOBAL | SESSION STATUS – 显示MySQL状态变量信息
mysql> SHOW GLOBAL STATUS;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients | 3 |
| Aborted_connects | 1 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 25 |
................
SHOW GLOBAL | SESSION VARIABLES – 显示MySQL系统变量信息
mysql> SHOW SESSION VARIABLES LIKE '%INNODB%';
+------------------------------------------+------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------+
| ignore_builtin_innodb | OFF |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
..........................
