MySQL管理——操作和查看資料庫的命令 摘要：本文主要學習了操作和查詢資料庫的常用命令。 查看資料庫的基本信息 查詢所有的資料庫 語法： 示例： 指定要使用的資料庫 語法： 示例： 查詢指定資料庫所有的表 語法： 示例： 查詢指定表的欄位 語法： 示例： 查詢指定表的索引 語法： 示例： ...
MySQL管理——操作和查看資料庫的命令
摘要：本文主要學習了操作和查詢資料庫的常用命令。
查看資料庫的基本信息
查詢所有的資料庫
語法：
1 show databases;
示例：
1 mysql> show databases; 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | 6 | mysql | 7 | performance_schema | 8 +--------------------+ 9 3 rows in set (0.00 sec) 10 11 mysql>
指定要使用的資料庫
語法：
1 show 資料庫名;
示例：
1 mysql> use mysql; 2 Reading table information for completion of table and column names 3 You can turn off this feature to get a quicker startup with -A 4 5 Database changed 6 mysql>
查詢指定資料庫所有的表
語法：
1 show tables;
示例：
1 mysql> show tables; 2 +---------------------------+ 3 | Tables_in_mysql | 4 +---------------------------+ 5 | columns_priv | 6 | db | 7 | event | 8 | func | 9 | general_log | 10 | help_category | 11 | help_keyword | 12 | help_relation | 13 | help_topic | 14 | host | 15 | ndb_binlog_index | 16 | plugin | 17 | proc | 18 | procs_priv | 19 | proxies_priv | 20 | servers | 21 | slow_log | 22 | tables_priv | 23 | time_zone | 24 | time_zone_leap_second | 25 | time_zone_name | 26 | time_zone_transition | 27 | time_zone_transition_type | 28 | user | 29 +---------------------------+ 30 24 rows in set (0.00 sec) 31 32 mysql>
查詢指定表的欄位
語法：
1 show columns from 表名;
示例：
1 mysql> show columns from user; 2 +------------------------+-----------------------------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +------------------------+-----------------------------------+------+-----+---------+-------+ 5 | Host | char(60) | NO | PRI | | | 6 | User | char(16) | NO | PRI | | | 7 | Password | char(41) | NO | | | | 8 | Select_priv | enum('N','Y') | NO | | N | | 9 | Insert_priv | enum('N','Y') | NO | | N | | 10 | Update_priv | enum('N','Y') | NO | | N | | 11 | Delete_priv | enum('N','Y') | NO | | N | | 12 | Create_priv | enum('N','Y') | NO | | N | | 13 | Drop_priv | enum('N','Y') | NO | | N | | 14 | Reload_priv | enum('N','Y') | NO | | N | | 15 | Shutdown_priv | enum('N','Y') | NO | | N | | 16 | Process_priv | enum('N','Y') | NO | | N | | 17 | File_priv | enum('N','Y') | NO | | N | | 18 | Grant_priv | enum('N','Y') | NO | | N | | 19 | References_priv | enum('N','Y') | NO | | N | | 20 | Index_priv | enum('N','Y') | NO | | N | | 21 | Alter_priv | enum('N','Y') | NO | | N | | 22 | Show_db_priv | enum('N','Y') | NO | | N | | 23 | Super_priv | enum('N','Y') | NO | | N | | 24 | Create_tmp_table_priv | enum('N','Y') | NO | | N | | 25 | Lock_tables_priv | enum('N','Y') | NO | | N | | 26 | Execute_priv | enum('N','Y') | NO | | N | | 27 | Repl_slave_priv | enum('N','Y') | NO | | N | | 28 | Repl_client_priv | enum('N','Y') | NO | | N | | 29 | Create_view_priv | enum('N','Y') | NO | | N | | 30 | Show_view_priv | enum('N','Y') | NO | | N | | 31 | Create_routine_priv | enum('N','Y') | NO | | N | | 32 | Alter_routine_priv | enum('N','Y') | NO | | N | | 33 | Create_user_priv | enum('N','Y') | NO | | N | | 34 | Event_priv | enum('N','Y') | NO | | N | | 35 | Trigger_priv | enum('N','Y') | NO | | N | | 36 | Create_tablespace_priv | enum('N','Y') | NO | | N | | 37 | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | 38 | ssl_cipher | blob | NO | | NULL | | 39 | x509_issuer | blob | NO | | NULL | | 40 | x509_subject | blob | NO | | NULL | | 41 | max_questions | int(11) unsigned | NO | | 0 | | 42 | max_updates | int(11) unsigned | NO | | 0 | | 43 | max_connections | int(11) unsigned | NO | | 0 | | 44 | max_user_connections | int(11) | NO | | 0 | | 45 | plugin | char(64) | NO | | | | 46 | authentication_string | text | NO | | NULL | | 47 +------------------------+-----------------------------------+------+-----+---------+-------+ 48 42 rows in set (0.00 sec) 49 50 mysql>
查詢指定表的索引
語法：
1 show index from 表名;
示例：
1 mysql> show index from user; 2 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 4 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 | user | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | | 6 | user | 0 | PRIMARY | 2 | User | A | 3 | NULL | NULL | | BTREE | | | 7 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 8 2 rows in set (0.00 sec) 9 10 mysql>
查看表的狀態和使用信息
語法：
1 show table status like from 資料庫 like '模糊查詢表名';
示例：
1 mysql> mysql> show table status from mysql like 'user'; 2 +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+ 3 | 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 | 4 +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+ 5 | user | MyISAM | 10 | Dynamic | 6 | 76 | 460 | 281474976710655 | 2048 | 0 | NULL | 2019-08-14 19:30:01 | 2019-08-14 19:30:03 | NULL | utf8_bin | NULL | | Users and global privileges | 6 +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+ 7 1 row in set (0.00 sec) 8 9 mysql>
按列輸出：
1 mysql> show table status from mysql like 'user'\G; 2 *************************** 1. row *************************** 3 Name: user 4 Engine: MyISAM 5 Version: 10 6 Row_format: Dynamic 7 Rows: 6 8 Avg_row_length: 76 9 Data_length: 460 10 Max_data_length: 281474976710655 11 Index_length: 2048 12 Data_free: 0 13 Auto_increment: NULL 14 Create_time: 2019-08-14 19:30:01 15 Update_time: 2019-08-14 19:30:03 16 Check_time: NULL 17 Collation: utf8_bin 18 Checksum: NULL 19 Create_options: 20 Comment: Users and global privileges 21 1 row in set (0.00 sec) 22 23 ERROR: 24 No query specified 25 26 mysql>
可以看到結果記錄被按行的方式列印輸出。
