MySQL常用show命令

1. show databases;

• 列出数据库管理系统中的所有数据库列表

  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | test               |
  | mysql              |
  | performance_schema |
  +--------------------+
  

2. show tables;

• 显示指定数据库的所有表，使用该命令前需要使用 use 数据库名 命令来选择要操作的数据库。

  +----------------------------+
  | Tables_in_sms              |
  +----------------------------+
  | test_group                 |
  | test_user                  |
  +----------------------------+
  

3. show columns from 数据表;

• 显示数据表每一列的属性。也可以使用desc 数据表;

  +-------------+-------------+------+-----+---------+----------------+
  | Field       | Type        | Null | Key | Default | Extra          |
  +-------------+-------------+------+-----+---------+----------------+
  | id          | int(11)     | NO   | PRI | NULL    | auto_increment |
  | create_at   | datetime(6) | NO   |     | NULL    |                |
  | modify_at   | datetime(6) | NO   |     | NULL    |                |
  | phone       | varchar(11) | NO   | UNI | NULL    |                |
  | status      | int(11)     | NO   |     | NULL    |                |
  +-------------+-------------+------+-----+---------+----------------+
  

4. show index from 数据表;

• 显示数据表的详细索引信息，包括主键。

  +------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
  | Table      | Non_unique | Key_name                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
  +------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
  | test_phone |          0 | PRIMARY                       |            1 | id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     |
  | test_phone |          0 | test_phone_phone_041a5a8f_uniq |            1 | phone       | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     |
  +------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
  

5. show table status from 数据库名 like '数据表名'\G;

• 输出MySQL数据库管理系统的性能及统计信息。

  *************************** 1. row ***************************
             Name: test_phone
           Engine: InnoDB
          Version: 10
       Row_format: Dynamic
             Rows: 1
   Avg_row_length: 16384
      Data_length: 16384
  Max_data_length: 0
     Index_length: 0
        Data_free: 0
   Auto_increment: 2
      Create_time: 2020-07-29 16:07:26
      Update_time: 2020-07-29 16:16:59
       Check_time: NULL
        Collation: utf8mb4_bin
         Checksum: NULL
   Create_options:
          Comment:
  

6. show create database 数据库名;

• 显示创建数据库时的create database语句。

  +----------+-------------------------------------------------------------------------------------+
  | Database | Create Database                                                                     |
  +----------+-------------------------------------------------------------------------------------+
  | test      | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
  +----------+-------------------------------------------------------------------------------------+
  

7. show create table 数据表名;

• 显示创建数据表时的create table语句，也可以使用方法5查看

  +-----------+------------------------------------------------------------------+
  | Table     | Create Table                                                     |
  +-----------+------------------------------------------------------------------+
  | test_phone | CREATE TABLE `test_phone` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `create_at` datetime(6) NOT NULL,
    `modify_at` datetime(6) NOT NULL,
    `phone` varchar(11) COLLATE utf8mb4_bin NOT NULL,
    `status` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `test_phone_phone_041a5a8f_uniq` (`phone`)
  ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
  +-----------+------------------------------------------------------------------+
  

• 参考链接：MySQL常用show命令