MySQL命令之show用法详解

整理中…

参考示例 （一）查看表字段详情

mysql> show full columns from test;
+-------+---------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type          | Collation      | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+---------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| sex   | enum('M','F') | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+---------------+----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)

（二）显示某个表的创建语句

mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `sex` enum('M','F') DEFAULT NULL COMMENT 'xingbie'
) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='test' |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

（三）显示所有数据库的名称

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| zentao             |
+--------------------+
8 rows in set (0.00 sec)

（四）查看已选数据库中的所有表

你必须先选择数据库，才能使用以下命令查看所有的表

mysql> show tables;
+-----------------+
| Tables_in_test  |
+-----------------+
| dept_htlwk      |
| dept_htlwk_bak1 |
| emp_htlwk       |
| emp_yyy         |
| student         |
| test            |
| tf_company      |
+-----------------+
7 rows in set (0.00 sec)

（五）查看某个数据库的创建语句

mysql> show create database mysql;
+----------+----------------------------------------------------------------------------------------------+
| Database | Create Database                                                                              |
+----------+----------------------------------------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注：可以通过这样的命令查看数据库的字符编码和字符集。

（六）查看数据库系统的进程

我们删除比较大的数据表时，可能删一半的时候，我们手动关闭了mysql，那么此时这个数据库是处于被锁的状态，可以通过 show processlist 来查看，然后杀掉该进程即可。

mysql> show processlist;
+------+-------+---------------------+------------+---------+------+----------+------------------+
| Id   | User  | Host                | db         | Command | Time | State    | Info             |
+------+-------+---------------------+------------+---------+------+----------+------------------+
| 4307 | root  | localhost           | qydpw      | Query   |    0 | starting | show processlist |
| 4337 | qydpw | 47.114.59.224:57374 | qydpw      | Sleep   | 1717 |          | NULL             |

mysql> kill 4337; -- 终止线程 4337

（七）查看在表缓存中当前被打开的非 TEMPORARY 表

mysql> show open tables from test;
+----------+----------+--------+-------------+
| Database | Table    | In_use | Name_locked |
+----------+----------+--------+-------------+
| test     | account  |      0 |           0 |
| test     | user     |      0 |           0 |
| test     | employee |      0 |           0 |
| test     | student  |      0 |           0 |
+----------+----------+--------+-------------+
4 rows in set (0.00 sec)

列含义说明：

列名称含义Database表所在的数据库Table表名称In_use值为 0 表示表是打开的，但是当前没有被使用；值为 1 表示表正在被使用中。例如，给表加上排他锁或者共享锁，值就会变成 1。Name_locked表名称是否被锁定。0 表示未锁定，1 表示已锁定。名称锁定用于删除表或重命名表等操作。

查看正在被使用的非 TEMPORARY 表：

mysql> show open tables where in_use > 0;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| test     | account |      1 |           0 |
+----------+---------+--------+-------------+
1 row in set (0.00 sec)

（八）显示给定数据表的描述性信息

SHOW TABLE STATUS;
SHOW TABLE STATUS FROM db_name; -- 该FROM子句，将显示的表限制为db_name数据库中存在的表
SHOW TABLE STATUS IN db_name; -- 和FROM子句一样
SHOW TABLE STATUS LIKE 'pattern'; -- 该LIKE子句，指示要匹配的表名。
SHOW TABLE STATUS WHERE where_expr;