1. show databases;

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

1
2
3
4
5
6
7
8
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
| mysql |
| performance_schema |
+--------------------+

2. show tables;

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

1
2
3
4
5
6
+----------------------------+
| Tables_in_sms |
+----------------------------+
| test_group |
| test_user |
+----------------------------+

3. show columns from 数据表;

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

1
2
3
4
5
6
7
8
9
+-------------+-------------+------+-----+---------+----------------+
| 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 数据表;

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

1
2
3
4
5
6
+------------+------------+-------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
*************************** 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语句。

1
2
3
4
5
+----------+-------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+-------------------------------------------------------------------------------------+

7. show create table 数据表名;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
+-----------+------------------------------------------------------------------+
| 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 |
+-----------+------------------------------------------------------------------+