MySQL common show statements

1. Show Character Set [like 'pattern']

Show all available character sets

 
Mysql> show character set like 'gbk'->; + --------- + signature + ----------------- + -------- + | charset | description | default collation | maxlen | + --------- + signature + -------- + | GBK Simplified Chinese | gbk_chinese_ci | 2 | + --------- + ------------------------ + ------------------- + -------- + 1 row in SET (0.00 Sec) mysql> show character set like 'utf % '; + --------- + --------------- + character + -------- + | charset | description | default collation | maxlen | + --------- + --------------- + character + -------- + | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | + --------- + --------------- + ------------------- + -------- + 1 row in SET (0.00 Sec)
 

The maxlen column is used to describe how many bytes are required to store such a character. In the preceding example, it takes 2 bytes to store a GBK character and 3 characters to store a utf8 character.

 

2. Show [full] Columns {from | in} tbl_name [{from | in} db_name] [like 'pattern']

Displays the columns of the given table. It is similar to desc tbl_name.

Query the structure of the auth_group table in the Django Database

Mysql> show columns from auth_group from Django; + ------- + ------------- + ------ + ----- + --------- + ---------------- + | FIELD | type | null | key | default | extra | + ------- + ------------- + ------ + ----- + --------- + ---------------- + | id | int (11) | pri | null | auto_increment | Name | varchar (80) | uni | + ------- + ------------- + ------ + ----- + --------- + ---------------- + 2 rows in SET (0.00 Sec) mysql> show columns from Django. auth_group; + ------- + ------------- + ------ + ----- + --------- + ---------------- + | FIELD | type | null | key | default | extra | + ------- + ------------- + ------ + ----- + --------- + ---------------- + | id | int (11) | pri | null | auto_increment | Name | varchar (80) | uni | + ------- + ------------- + ------ + ----- + --------- + ---------------- + 2 rows in SET (0.00 Sec) mysql> DESC Django. auth_group; + ------- + ------------- + ------ + ----- + --------- + ---------------- + | FIELD | type | null | key | default | extra | + ------- + ------------- + ------ + ----- + --------- + ---------------- + | id | int (11) | pri | null | auto_increment | Name | varchar (80) | uni | + ------- + ------------- + ------ + ----- + --------- + ---------------- + 2 rows in SET (0.00 Sec)

 

3. Show create table tbl_name

Displays the SQL statement used to create a table.

Mysql> show create table auth_group; + ------------ + partition + | table | create table | + ------------ + partition + | auth_group | create table 'auth _ group' ('id' int (11) not null auto_increment, 'name' varchar (80) not null default '', primary key ('id'), unique key 'name' ('name ')) engine = InnoDB default charset = utf8 | + ------------ + bytes ------------------------------------------------------------------- + 1 row in SET (0.00 Sec)

4. Show create database db_name

Displays the SQL statement when db_name is created.

5. Show databases [like 'pattern']

Display all databases on the current MySQL

 
Mysql> show databases; + ---------- + | database | + ---------- + | Django | MySQL | test | TRAC | + ---------- + 4 rows in SET (0.00 Sec)
 

 

6. Show tables [like 'pattern']

Show all tables in the current database

Mysql> show tables; + groups + | tables_in_django | + groups + | auth_group | percent | auth_message | auth_permission | auth_user | auth_user_groups | percent | django_session | django_site | polls_choice | polls_poll | + ---------------------------- + 12 rows in SET (0.00 Sec)
 

 

7. Show grants [for user]

Displays the authorization statement for the user.

Mysql> show grants; + tables --------- + | grants for root @ localhost | + tables --------- + | grant all privileges on *. * To 'root' @ 'localhost' identified by password' * a4b6157342538724e35608 'with grant option | + rows --------- + 1 row in SET (0.00 Sec)
 

 

8. Show {index | indexes | keys} {from | in} tbl_name [{from | in} db_name]

Show Index

Mysql> show index from auth_group; + ------------ + ---------- + upper + ----------- + upper + ---------- + -------- + ------------ + ----------- + | table | non_unique | key_name | partition | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | + ------------ + ---------- + accept + ----------- + accept + ---------- + -------- + ------------ + ---------- + --------- + | auth_group | 0 | primary | 1 | ID | A | 0 | null | btree | auth_group | 0 | Name | 1 | Name | A | 0 | null | btree | | + ------------ + ---------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + ----------- + 2 rows in SET (0.00 Sec)
 

 

9. Show Open Table

Display tables in the buffer of the current table

 
Mysql> show open tables; + ---------- + ------------ + -------- + ------------- + | database | table | in_use | name_locked | + ---------- + ------------ + -------- + logging + | Django | auth_group | 0 | + ---------- + ------------ + -------- + ------------- + 1 row in SET (0.00 Sec)
 

 

10. Show privileges

Displays the permissions supported by MySQL.

11. Show [full] processlist
Display threads in use)

Mysql> show processlist; + ---- + ------ + ---------------- + -------- + --------- + ------ + ------- + ------------------ + | ID | user | host | dB | command | time | state | info | + ---- + ------ + ---------------- + -------- + --------- + ------ + ------- + ------------------ + | 7 | root | localhost: 1158 | Django | query | 0 | null | show processlist | + ---- + ------ + ---------------- + -------- + --------- + ------ + ------- + ------------------ + 1 row in SET (0.00 Sec)
 

12. Show [Global | session] variables [like 'pattern']

Show Variables

Mysql> show variables like "% char % "; + character + | variable_name | value | + character + | character | Latin1 | character_set_connection | Latin1 | character_set_database | utf8 | character | Latin1 | character_set_server | utf8 | | character_set_system | utf8 | character_sets_dir | D: \ Program Files \ mysql server 4.1 \ share \ charsets/| + ---------------------------- + ----------------------------------------------------- + 7 rows in SET (0.00 Sec)
 

13. Show warnings [limit [offset,] row_count]
Show count (*) Warnings

Displays alerts for errors produced by the last statement in the current session.