下面列出了日常管理 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 |
+--------------------+
4 rows in set (0.00 sec)
SHOW TABLES 命令
SHOW TABLES 命令用于列出指定数据库中的所有表 使用该命令前需要使用 USE 命令来选择要操作的数据库
MariaDB [test]> USE mysql;
Reading table information for completion of table and column names
You can 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 |
+---------------------------+
24 rows 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 | |
+---------+--------------+------+-----+---------+----------------+
5 rows 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:
1 row 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:
1 row 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:
1 row in set (0.00 sec)