下面列出了日常管理 MySQL 数据库过程中常用的命令
USE 命令
USE 命令用于选择要操作的 MySQL 数据库
使用该命令后所有 MySQL 命令都只针对该数据库
USE 命令语法格式如下
USE**数据库名**
示例
MariaDB [(none)]> USE test;
Database changed
MariaDB [test]>
可以看到，使用了 USE 命令后, mysql 提示符中的 [(none)] 会变成 [test]
SHOW DATABASE 命令
SHOW DATABASES 命令用于列出 MySQL 数据库管理系统的数据库
MariaDB [test]> SHOW DATABASES;
+--------------------+
|Database |
+--------------------+
|information_schema |
|mysql |
|performance_schema |
|test |
+--------------------+
4rows in set (0.00 sec)
SHOW TABLES 命令
SHOW TABLES 命令用于列出指定数据库中的所有表 使用该命令前需要使用 USE 命令来选择要操作的数据库
MariaDB [test]> USE mysql;
Reading table information for completion of table and column names
Youcan turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
|Tables_in_mysql |
+---------------------------+
|columns_priv |
|db |
|event |
|func |
|general_log |
|help_category |
|help_keyword |
|help_relation |
|help_topic |
|host |
|ndb_binlog_index |
|plugin |
|proc |
|procs_priv |
|proxies_priv |
|servers |
|slow_log |
|tables_priv |
|time_zone |
|time_zone_leap_second |
|time_zone_name |
|time_zone_transition |
|time_zone_transition_type |
|user |
+---------------------------+
24rows in set (0.00 sec)
SHOW COLUMNS FROM 命令
SHOW COLUMNS FROM table_name 命令用于显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等等信息
MariaDB [test]> SHOW COLUMNS FROM comps;
+---------+--------------+------+-----+---------+----------------+
|Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
|id | int(11) | NO | PRI | NULL | auto_increment |
|name | varchar(64) | YES | | NULL | |
|website | varchar(128) | YES | | NULL | |
|city | varchar(32) | YES | | NULL | |
|ceo | varchar(32) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
5rows in set (0.01 sec)
SHOW INDEX FROM 命令
SHOW INDEX FROM table_name 命令用于显示数据表的详细索引信息，包括 PRIMARY KEY(主键)
MariaDB [test]> SHOW INDEX FROM comps\G
*************************** 1. row ***************************
Table: comps
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1row in set (0.00 sec)
SHOW TABLE STATUS LIKE LIKE 命令
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE ‘pattern’] 命令用于输出 MySQL 数据库管理系统的性能及统计信息
1. 显示数据库 test 中所有表的信息
MariaDB [(none)]> SHOW TABLE STATUS FROM test\G
*************************** 1. row ***************************
Name: comps
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: 8
Create_time: 2017-10-08 19:13:22
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
1row in set (0.00 sec)
显示表名以 comps 开头的表的信息
MariaDB [test]> SHOW TABLE STATUS FROM test LIKE '%comps%'\G
*************************** 1. row ***************************
Name: comps
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: 8
Create_time: 2017-10-08 19:13:22
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
1row in set (0.00 sec)