MySql 常用命令

1.describe

describe [tableName]: Display the table definition, only includes basic columns definitions without any constraints.e.g.,:

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| player_id        | varchar(100) | NO   | PRI | NULL    |       |
| login_date_time  | datetime     | NO   | PRI | NULL    |       |
| logout_date_time | datetime     | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+

 

2. show

2.1 show databases [LIKE 'pattern']: show all databases in mysql.

Example:

show databases like 'tes%':

+---------------+
| Database (t%) |
+---------------+
| test          |
+---------------+

 

2.2 SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]: FULL-- display column "table type".

Example:

show full tables from cardgame;

+----------------------+------------+
| Tables_in_cardgame   | Table_type |
+----------------------+------------+
| active_player        | BASE TABLE |
| card_group_ref       | BASE TABLE |
| card_play_session    | BASE TABLE |
| card_ref             | BASE TABLE |
| card_suit_ref        | BASE TABLE |
| card_table           | BASE TABLE |
| player_table_session | BASE TABLE |
| table_session        | BASE TABLE |
| user                 | BASE TABLE |
+----------------------+------------+

 

2.3 SHOW CREATE TABLE|VIEW|DATABASE|FUNCTION|PROCEDURE|TRIGGER|EVEN T: display the create scripts.

Example:

show create table user;

CREATE TABLE `user` (
  `user_id` varchar(100) NOT NULL,
  `nickname` varchar(100) NOT NULL,
  `passwd` varchar(100) NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `user_id` (`user_id`),
  KEY `user_id_2` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

 

2.4 SHOW COLUMNS FROM tbl_name : synonym to DESCRIBE tbl_name.

 

3 explain:

EXPLAIN [EXTENDED] SELECT select_options






The EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way to obtain information about how 

MySQL executes a SELECT statement.When you precede a SELECT  statement with the keyword EXPLAIN, MySQL displays 

information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, 

including information about how tables are joined and in which order.

EXTENDED-- provide additional information. 



Example:

explain extended select * 

                          from player_table_session tps 

                          inner join table_session ts on tps.table_session_id = ts.table_session_id;



+----+-------------+-------+--------+------------------+------+---------+------+------+----------+-------+

| id | select_type | table | type   | possible_keys    | key  | key_len | ref  | rows | filtered | Extra |

+----+-------------+-------+--------+------------------+------+---------+------+------+----------+-------+

|  1 | SIMPLE      | tps   | system | FK_table_session | NULL | NULL    | NULL |    1 |   100.00 |       |

|  1 | SIMPLE      | ts    | system | PRIMARY          | NULL | NULL    | NULL |    1 |   100.00 |       |

+----+-------------+-------+--------+------------------+------+---------+------+------+----------+-------+