JavaScript Editor Source code editor     What Is Ajax 

Main Page

13.3. MySQL Utility Statements

13.3.1. DESCRIBE Syntax

{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”.

13.3.2. HELP 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.8, “Server-Side Help”).

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 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              |
    +----------------------+-------------------------+----------------+

If you intend to use the HELP() statement while other tables are locked with LOCK TABLES, you must also lock the required mysql.help_xxx tables.

13.3.3. USE Syntax

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 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.

JavaScript Editor Source code editor     What Is Ajax