MySQL 日常管理
下面列出了日常管理 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)