MySQL管理命令–MySQL 系列教程之（五）

文章目录

本篇文章 Linux大学来为大家介绍一下MySQL的一些管理命令，这些命令在以后的文章中还会为大家讲到，在此只为大家做一个简单的介绍。

一、显示所有数据库

使用 SHOW DATABASES; 命令可以显示出服务器中所有的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linuxdaxue         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

二、选择数据库

使用 USE 数据库名来选择需要操作的数据库：

mysql> use linuxdaxue
Database changed

三、显示指定数据库的所有表

使用SHOW TABLES；命令可以显示数据库的所有表：

mysql> show tables;
+----------------------+
| Tables_in_linuxdaxue |
+----------------------+
| linuxdaxue_tbl       |
+----------------------+
1 row in set (0.00 sec)

四、显示数据表的属性

使用SHOW COLUMNS FROM 数据表:来显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

mysql>  SHOW COLUMNS FROM linuxdaxue_tbl;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| linuxdaxue_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| linuxdaxue_title  | varchar(100) | NO   |     | NULL    |                |
| linuxdaxue_author | varchar(40)  | NO   |     | NULL    |                |
| submission_date   | date         | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql>

五、显示数据表的详细索引信息

使用SHOW INDEX FROM 数据表: 来显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

mysql> SHOW INDEX FROM linuxdaxue_tbl;
+----------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| linuxdaxue_tbl |          0 | PRIMARY  |            1 | linuxdaxue_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

六、输出Mysql数据库管理系统的性能及统计信息

使用SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G: 命令来输出Mysql数据库管理系统的性能及统计信息。

mysql> SHOW TABLE STATUS  FROM linuxdaxue;      
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| linuxdaxue_tbl | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2016-09-09 08:54:34 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

使结果按列打印：

mysql> SHOW TABLE STATUS  FROM linuxdaxue\G;
*************************** 1. row ***************************
           Name: linuxdaxue_tbl
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2016-09-09 08:54:34
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified