SQL语句之SHOW语句使⽤
SHOW语句有许多形式，提供关于服务器的数据库、表、列或状态信息的信息。
SHOW语法格式：
SHOW 关键字 LIKE 'pattern'
如果对于⼀个给定的说明语句的语法包括像'模式'，'模式'是⼀个字符串，可以包含“%”和“_“通配符。该模式是有⽤的限制语句输出匹配的值。本节介绍以下：
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.0
6 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 als (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
sql触发器的使用
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)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。