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>