{DESCRIBE | DESC} tbl_name [col_name | wild]
DESCRIBE provides information about the
columns in a table. It is a shortcut for SHOW COLUMNS
FROM. As of MySQL 5.0.1, these statements also display
information for views. (See Section 13.5.4.3, “SHOW COLUMNS Syntax”.)
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 within quotes unless it
contains spaces or other special characters.
mysql> DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Field indicates the column name.
The Null field indicates whether
NULL values can be stored in the column.
The Key field indicates whether the column is
indexed. A value of PRI indicates that the
column is part of the table's primary key.
UNI indicates that the column is part of a
UNIQUE index. The MUL
value indicates that multiple occurrences of a given value are
allowed within the column.
One reason for MUL to be displayed on a
UNIQUE index is that several columns form a
composite UNIQUE index; although the
combination of the columns is unique, each column can still hold
multiple occurrences of a given value. Note that in a composite
index, only the leftmost column of the index has an entry in the
Key field.
Before MySQL 5.0.11, if the column allows
NULL values, the Key value
can be MUL even when a
UNIQUE index is used. The rationale was that
multiple rows in a UNIQUE index can hold a
NULL value if the column is not declared
NOT NULL. As of MySQL 5.0.11, the display is
UNI rather than MUL
regardless of whether the column allows NULL;
you can see from the Null field whether or
not the column can contain NULL.
The Default field indicates the default value
that is assigned to the column.
The Extra field contains any additional
information that is available about a given column. In the
example shown, the Extra field indicates that
the Id column was created with the
AUTO_INCREMENT keyword.
If the data types are different from what you expect them to be
based on a CREATE TABLE statement, note that
MySQL sometimes changes data types. See
Section 13.1.5.1, “Silent Column Specification Changes”.
The DESCRIBE statement is provided for
compatibility with Oracle.
The SHOW CREATE TABLE and SHOW TABLE
STATUS statements also provide information about
tables. See Section 13.5.4, “SHOW Syntax”.
HELP 'search_string'
The HELP statement returns online information
from the MySQL Reference manual. Its proper operation requires
that the help tables in the mysql database be
initialized with help topic information (see
Section 5.2.7, “MySQL Server-Side Help Support”).
The HELP statement searches the help tables
for the given search string and displays the result of the
search. The search string is not case sensitive.
The HELP statement understands several types of search strings:
At the most general level, use contents
to retrieve a list of the top-level help categories:
HELP 'contents'
For a list of topics in a given help category, such as
Data Types, use the category name:
HELP 'data types'
For help on a specific help topic, such as as the
ASCII() function or the CREATE
TABLE statement, use the associated keyword or
keywords:
HELP 'ascii' HELP 'create table'
In other words, the search string matches a category, many
topics, or a single topic. You cannot necessarily tell in
advance whether a given search string will return a list of
items or the help information for a single help topic. However,
you can tell what kind of response HELP
returned by examining the number of rows and columns in the
result set.
The following descriptions indicate the forms that the result
set can take. Output for the example statements is shown using
the familar “tabular” or “vertical”
format that you see when using the mysql
client, but note that mysql itself reformats
HELP result sets in a different way.
Empty result set
No match could be found for the search string.
Result set containing a single row with three columns
This means that the search string yielded a hit for the help topic. The result has three columns:
name: The topic name.
description: Descriptive help text
for the topic.
example: Usage example or exmples.
This column might be blank.
Example: HELP 'replace'
Yields:
name: REPLACE
description: Syntax:
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str
replaced by the string to_str. REPLACE() performs a case-sensitive
match when searching for from_str.
example: mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
Result set containing multiple rows with two columns
This means that the search string matched many help topics. The result set indicates the help topic names:
name: The help topic name.
is_it_category: Y
if the name represents a help category,
N if it does not. If it does not, the
name value when specified as the
argument to the HELP statement should
yield a single-row result set containing a description
for the named item.
Example: HELP 'status'
Yields:
+-----------------------+----------------+ | name | is_it_category | +-----------------------+----------------+ | SHOW | N | | SHOW ENGINE | N | | SHOW INNODB STATUS | N | | SHOW MASTER STATUS | N | | SHOW PROCEDURE STATUS | N | | SHOW SLAVE STATUS | N | | SHOW STATUS | N | | SHOW TABLE STATUS | N | +-----------------------+----------------+
Result set containing multiple rows with three columns
This means the search string matches a category. The result set contains category entries:
source_category_name: The help
category name.
name: The category or topic name
is_it_category: Y
if the name represents a help category,
N if it does not. If it does not, the
name value when specified as the
argument to the HELP statement should
yield a single-row result set containing a description
for the named item.
Example: HELP 'functions'
Yields:
+----------------------+-------------------------+----------------+ | source_category_name | name | is_it_category | +----------------------+-------------------------+----------------+ | Functions | CREATE FUNCTION | N | | Functions | DROP FUNCTION | N | | Functions | Bit Functions | Y | | Functions | Comparison operators | Y | | Functions | Control flow functions | Y | | Functions | Date and Time Functions | Y | | Functions | Encryption Functions | Y | | Functions | Information Functions | Y | | Functions | Logical operators | Y | | Functions | Miscellaneous Functions | Y | | Functions | Numeric Functions | Y | | Functions | String Functions | Y | +----------------------+-------------------------+----------------+
USE db_name
The USE
statement tells MySQL to use the
db_namedb_name database as the default
(current) database for subsequent statements. The database
remains the default until the end of the session or another
USE statement is issued:
USE db1; SELECT COUNT(*) FROM mytable; # selects from db1.mytable USE db2; SELECT COUNT(*) FROM mytable; # selects from db2.mytable
Making a particular database the default 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:
USE db1; SELECT author_name,editor_name FROM author,db2.editor WHERE author.editor_id = db2.editor.editor_id;
The USE statement is provided for
compatibility with Sybase.