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> 

可以看到结果记录被按行的方式打印输出。

转载于:https://www.cnblogs.com/shamao/p/11371574.html