MySQL management – commands for operating and viewing databases

Time:2019-11-8

MySQL management – commands for operating and viewing databases

Absrtact: This paper mainly studies the common commands of operating and querying database.

View basic information of database

Query all databases

Syntax:

 show databases;

Example:

 mysql> show databases;
 +
 | Database           |
 +
 | information_schema |
 | mysql              |
 | performance_schema |
 +
 3 rows in set (0.00 sec)
 
 mysql> 

Specify the database to use

Syntax:

Show database name;

Example:

 mysql> 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
 mysql> 

Query all tables in the specified database

Syntax:

 show tables;

Example:

 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)
 
 mysql> 

Query the fields of the specified table

Syntax:

Show columns from table name;

Example:

 mysql> show columns from user;
 +
 | Field                  | Type                              | Null | Key | Default | Extra |
 +
 | Host                   | char(60)                          | NO   | PRI |         |       |
 | User                   | char(16)                          | NO   | PRI |         |       |
 | Password               | char(41)                          | NO   |     |         |       |
 | Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
 | Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
 | Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
 | Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
 | Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
 | Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
 | Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
 | Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
 | Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
 | File_priv              | enum('N','Y')                     | NO   |     | N       |       |
 | Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
 | References_priv        | enum('N','Y')                     | NO   |     | N       |       |
 | Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
 | Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
 | Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
 | Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
 | Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
 | Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
 | Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
 | Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
 | Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
 | Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
 | Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
 | Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
 | Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
 | Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
 | Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
 | Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
 | Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
 | ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
 | ssl_cipher             | blob                              | NO   |     | NULL    |       |
 | x509_issuer            | blob                              | NO   |     | NULL    |       |
 | x509_subject           | blob                              | NO   |     | NULL    |       |
 | max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
 | max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
 | max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
 | max_user_connections   | int(11)                           | NO   |     | 0       |       |
 | plugin                 | char(64)                          | NO   |     |         |       |
 | authentication_string  | text                              | NO   |     | NULL    |       |
 +
 42 rows in set (0.00 sec)
 
 mysql> 

Query the index of the specified table

Syntax:

Show index from table name;

Example:

 mysql> show index from user;
 +
 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 +
 | user  |          0 | PRIMARY  |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
 | user  |          0 | PRIMARY  |            2 | User        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
 +
 2 rows in set (0.00 sec)
 
 mysql> 

View table status and usage information

Syntax:

Show table status like from database like 'fuzzy query table name';

Example:

 mysql> mysql> show table status from mysql like 'user';
 +
 | 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                     |
 +
 | 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 |
 +
 1 row in set (0.00 sec)
 
 mysql>

Output by column:

 mysql> show table status from mysql like 'user'\G;
 *************************** 1. row ***************************
            Name: user
          Engine: MyISAM
         Version: 10
      Row_format: Dynamic
            Rows: 6
  Avg_row_length: 76
     Data_length: 460
 Max_data_length: 281474976710655
    Index_length: 2048
       Data_free: 0
  Auto_increment: NULL
     Create_time: 2019-08-14 19:30:01
     Update_time: 2019-08-14 19:30:03
      Check_time: NULL
       Collation: utf8_bin
        Checksum: NULL
  Create_options: 
         Comment: Users and global privileges
 1 row in set (0.00 sec)
 
 ERROR: 
 No query specified
 
 mysql> 

You can see that the result record is printed out in lines.

Recommended Today

Perl beginner’s notes: Hello World

Work needs to start learning Perl, download a window version (5.16): download link http://www.activestate.com/activeperl/downloadsAfter installation, write the first Perl program Copy codeThe code is as follows: #!/usr/bin/perl  print “Hello,World!\n”; Running result: (very much like Python) Copy codeThe code is as follows: c:\Perl>perl HelloWorld.pl  Hello,World! Then I learned: cpan, although I don’t know what this guy […]