{DESCRIBE | DESC} tbl_name [col_name | wild]
DESCRIBE provides information about a table's columns. It is a shortcut for SHOW COLUMNS FROM. See SHOW COLUMNS.
col_name can be a column name, or a string containing the SQL ‘%’ and ‘_’ wildcard characters to obtain output only for the columns with names matching the string. There is no need to enclose the string in quotes unless it contains spaces or other special characters.
mysql> DESCRIBE city; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | Id | int(11) | | PRI | NULL | auto_increment | | Name | char(35) | | | | | | Country | char(3) | | UNI | | | | District | char(20) | YES | MUL | | | | Population | int(11) | | | 0 | | +------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
The Null column indicates whether NULL values can be stored, with YES displayed when NULL values are allowed.
The Key column indicates whether the field is indexed. A value of PRI indicates that the field is part of the table's primary key. UNI indicates that the field is part of a a UNIQUE index. The MUL value indicates that multiple occurences of a given value allowed within the field.
A field can be designated as MUL even if a UNIQUE index is used if NULL values are allowed, as multiple rows in a UNIQUE index can hold a NULL value if the column is not declared NOT NULL. Another cause for MUL on a UNIQUE index is when two columns form a composite UNIQUE index; while the combination of the columns will be unique, each column can still hold multiple occurences of a given value. Note that in a composite index only the leftmost field of the index will have an entry in the Key column.
The Default column indicates the default value that is assigned to the field.
The Extra column contains any additional information that is available about a given field. In our example the Extra column indicates that our Id column was created with the AUTO_INCREMENT keyword.
If the column types are different from what you expect them to be based on a CREATE TABLE statement, note that MySQL sometimes changes column types. See the section called “Silent Column Specification Changes”.
The DESCRIBE statement is provided for Oracle compatibility.
The SHOW CREATE TABLE and SHOW TABLE STATUS statements also provide information about tables. See SHOW.
USE db_name
The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or until another USE statement is issued:
mysql> USE db1; mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable mysql> USE db2; mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable
Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The following example accesses the author table from the db1 database and the editor table from the db2 database:
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
-> WHERE author.editor_id = db2.editor.editor_id;