ABSTRACT: This paper mainly studies the common commands of operating and querying database.
View the basic information of the database
Query all databases
Syntax:
1 show databases;
Example:
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>
Specify the database to use
Syntax:
1 show database name;
Example:
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>
Query all tables in the specified database
Syntax:
1 show tables;
Example:
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>
Query the fields of the specified table
Syntax:
1 show columns from table name;
Example:
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>
Query the index of the specified table
Syntax:
1 show index from table name;
Example:
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>