What if you forget the name of a database or table, or what the structure of a given table is (for example, what its columns are called)? MySQL addresses this problem through several statements that provide information about the databases and tables it supports.
You have previously seen SHOW
DATABASES
, which lists the databases managed by the
server. To find out which database is currently selected, use the
DATABASE()
function:
Press CTRL+C to copymysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+
If you have not yet selected any database, the result is
NULL
.
To find out what tables the default database contains (for example, when you are not sure about the name of a table), use this statement:
Press CTRL+C to copymysql> SHOW TABLES; +---------------------+ | Tables_in_menagerie | +---------------------+ | event | | pet | +---------------------+
The name of the column in the output produced by this statement is
always
Tables_in_
,
where db_name
db_name
is the name of the
database. See Section 15.7.7.39, “SHOW TABLES Statement”, for more information.
If you want to find out about the structure of a table, the
DESCRIBE
statement is useful; it
displays information about each of a table's columns:
Press CTRL+C to copymysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
Field
indicates the column name,
Type
is the data type for the column,
NULL
indicates whether the column can contain
NULL
values, Key
indicates
whether the column is indexed, and Default
specifies the column's default value. Extra
displays special information about columns: If a column was
created with the AUTO_INCREMENT
option, the
value is auto_increment
rather than empty.
DESC
is a short form of
DESCRIBE
. See
Section 15.8.1, “DESCRIBE Statement”, for more information.
You can obtain the CREATE TABLE
statement necessary to create an existing table using the
SHOW CREATE TABLE
statement. See
Section 15.7.7.11, “SHOW CREATE TABLE Statement”.
If you have indexes on a table, SHOW INDEX FROM
produces information
about them. See Section 15.7.7.23, “SHOW INDEX Statement”, for more about this
statement.
tbl_name