SQL Statements

This section describes the syntax and meaning of each of MySQL's SQL statements. A statement will fail if you do not have the necessary privileges to perform it. For example, USE db_name fails if you have no permission to access the database db_name.

`ALTER DATABASE`

ALTER DATABASE db_name action_list

This statement changes global database characteristics. The action_list specifies one or more actions separated by commas. However, there is currently only one possible action:

[DEFAULT] CHARACTER SET charset

charset may be a character set name or DEFAULT to have the database use the current server character set by default.

ALTER DATABASE requires the ALTER privilege for the database.

This statement was introduced in MySQL 4.1.

`ALTER TABLE`

ALTER [IGNORE] TABLE tbl_name action_list

ALTER TABLE allows you to rename tables or modify their structure. To use it, specify the table name tbl_name then give the specifications for one or more actions to be performed on the table. The IGNORE keyword comes into play if the action could produce duplicate key values in a unique index in the new table. Without IGNORE, the effect of the ALTER TABLE statement is canceled. With IGNORE, the rows that duplicate values for unique key values are deleted.

Except for table renaming operations, ALTER TABLE works by creating from the original table a new one that incorporates the changes to be made. If an error occurs, the new table is discarded and the original remains unchanged. If the operation completes successfully, the original table is discarded and replaced by the new one. During the operation, other clients may read from the original table. Any clients that try to update the table are blocked until the ALTER TABLE statement completes, at which point the updates are applied to the new table.

action_list specifies one or more actions separated by commas. Each action is performed in turn. An action may be any of the following:

ADD [COLUMN] col_declaration [FIRST | AFTER col_name]
Adds a column to the table. col_declaration is the column declaration; it has the same format as that used for the CREATE TABLE statement. The column becomes the first column in the table if the FIRST keyword is given or is placed after the named column if AFTER col_name is given. If the column placement is not specified, the column becomes the last column of the table.
```
ALTER TABLE t ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; 
ALTER TABLE t ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
ALTER TABLE t ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    AFTER suffix;
```
ADD [COLUMN] (create_definition,...)
Adds columns or indexes to the table. Each create_definition is a column or index definition, in the same format as for CREATE TABLE. This syntax was introduced in MYSQL 3.23.11.
ADD [CONSTRAINT name] FOREIGN KEY [index_name] (index_columns)reference_definition
Adds a foreign key definition to a table. This is supported only for InnoDB tables. The foreign key is based on the columns named in index_columns, which is a list of one or more columns in the table separated by commas. Any CONSTRAINT or index_name, if given, are ignored. reference_definition defines how the foreign key relates to the parent table. The syntax is as described in the entry for CREATE TABLE.ADD FOREIGN KEY was introduced in MySQL 3.23.50. (There is a corresponding DROP FOREIGN KEY clause, but currently it is just parsed and ignored.)
```
ALTER TABLE child 
    ADD FOREIGN KEY (par_id) REFERENCES parent (par_id) ON DELETE CASCADE;
```
ADD FULLTEXT [KEY | INDEX] [index_name] (index_columns)
Adds a FULLTEXT index to a MyISAM table. The index is based on the columns named in index_columns, which is a list of one or more columns in the table separated by commas. ADD FULLTEXT was introduced in MySQL 3.23.23.
```
ALTER TABLE poetry ADD FULLTEXT (author,title,stanza); 
```
ADD INDEX [index_name] (index_columns)
Adds an index to the table. The index is based on the columns named in index_columns, which is a list of one or more columns in the table separated by commas. For CHAR and VARCHAR columns, you can index a prefix of the column, using col_name(n) syntax to index the first n bytes of column values. For BLOB and TEXT columns, you must specify a prefix value; you cannot index the entire column. If the index name index_name is not specified, a name is chosen automatically based on the name of the first indexed column.
ADD PRIMARY KEY (index_columns)
Adds a primary key on the given columns. The key is given the name PRIMARY. index_columns is specified as for the ADD INDEX clause. An error occurs if a primary key already exists or if any of the columns are defined to allow NULL values.
```
ALTER TABLE president ADD PRIMARY KEY (last_name, first_name); 
```
ADD UNIQUE [index_name] (index_columns)
Adds a unique-valued index to tbl_name. index_name and index_columns are specified as for the ADD INDEX clause.
```
ALTER TABLE absence ADD UNIQUE id_date (student_id, date); 
```
ALTER [COLUMN] col_name {SET DEFAULT value | DROP DEFAULT}
Modifies the given column's default value, either to the specified value or by dropping the current default value. In the latter case, a new default value is assigned, as described in the entry for the CREATE TABLE statement.
```
ALTER TABLE event ALTER type SET DEFAULT 'Q'; 
ALTER TABLE event ALTER type DROP DEFAULT;
```
CHANGE [COLUMN] col_name col_declaration [FIRST | AFTER col_name]
Changes a column's name and definition. col_name is the column's current name, and col_declaration is the declaration to which the column should be changed. col_declaration is in the same format as that used for the CREATE TABLE statement. Note that the declaration must include the new column name, so if you want to leave the name unchanged, it's necessary to specify the same name twice. FIRST or AFTER be used as of MySQL 4.0.1 and have the same effect as for ADD COLUMN.
```
ALTER TABLE student CHANGE name name VARCHAR(40); 
ALTER TABLE student CHANGE name student_name CHAR(30) NOT NULL;
```
DISABLE KEYS
For a MyISAM table, this disables the updating of non-unique indexes that normally occurs when the table is changed. ENABLE KEYS may be used to re-enable index updating. DISABLE KEYS was introduced in MySQL 4.0.
```
ALTER TABLE score DISABLE KEYS; 
```
DROP [COLUMN] col_name [RESTRICT | CASCADE]
Removes the given column from the table. If the column is part of any indexes, it is removed from those indexes. If all columns from an index are removed, the index is removed as well.
```
ALTER TABLE president DROP suffix; 
```
The RESTRICT and CASCADE keywords have no effect. They are parsed for compatibility with code ported from other databases, but ignored.
DROP INDEX index_name
Removes the given index from the table.
```
ALTER TABLE member DROP INDEX name; 
```
DROP PRIMARY KEY
Removes the primary key from the table. If a table has no unique index that was created as a PRIMARY KEY but has one or more UNIQUE indexes, the first one of those is dropped.
```
ALTER TABLE president DROP PRIMARY KEY; 
```
ENABLE KEYS
For a MyISAM table, re-enables updating for non-unique indexes that have been disabled with DISABLE KEYS. ENABLE KEYS was introduced in MySQL 4.0.
```
ALTER TABLE score ENABLE KEYS; 
```
MODIFY [COLUMN] col_declaration [FIRST | AFTER col_name]
Changes the declaration of a column. The column declaration col_declaration is given, using the same format for column descriptions as is shown in the entry for the CREATE TABLE statement. The declaration begins with a column name, which is how the column that is to be modified is identified. MODIFY was introduced in MySQL 3.22.16. FIRST or AFTER may be used as of MySQL 4.0.1 and have the same effect as for ADD COLUMN.
```
ALTER TABLE student MODIFY name VARCHAR(40) DEFAULT '' NOT NULL; 
```
ORDER BY col_list
Sorts the rows in the table according to the columns named in col_list, which should be a list of one or more columns in the table separated by columns. The default sort order is ascending. A column name can be followed by ASC or DESC to specify ascending or descending order explicitly. Sorting a table this way may improve performance of subsequent queries that retrieve records in the same order. This is mostly useful for a table that will not be modified afterward, because rows will not remain in order if the table is modified after performing the ORDER BY operation. This option was introduced in MySQL 3.23.28.
```
ALTER TABLE score ORDER BY event_id, student_id; 
```
RENAME [TO | AS] new_tbl_name
Renames the table tbl_name to new_tbl_name.
```
ALTER TABLE president RENAME TO prez; 
```
Prior to MySQL 3.23.17, there is no keyword between RENAME and the new table name. From 3.23.17 on, TO is optional there, and from 3.23.23 on, TO or AS are optional there.
table_options
Specifies table options of the sort that may be given in the table_options part of a CREATE TABLE statement.
```
ALTER TABLE score TYPE = MYISAM CHECKSUM = 1; 
ALTER TABLE sayings CHARACTER SET utf8;
```
Any version-specific constraints on the availability of a given table option are as described in the entry for the CREATE TABLE statement.

`ANALYZE TABLE`

ANALYZE {TABLE | TABLES} tbl_name [, tbl_name] ...

This statement causes MySQL to analyze each of the named tables, storing the distribution of key values present in each table's indexes. It works for MyISAM and BDB tables and requires SELECT and INSERT privileges on each table. After analysis, the Cardinality column of the output from SHOW INDEX indicates the number of distinct values in the indexes. Information from the analysis can be used by the optimizer during subsequent queries to perform certain types of joins more quickly.

Analyzing a table requires a read lock, which prevents that table from being updated during the operation. If you run ANALYZE TABLE on a table that has already been analyzed and that has not been changed since, no analysis is performed.

ANALYZE TABLE produces output in the format described under the entry for CHECK TABLE.

ANALYZE TABLE was introduced in MySQL 3.23.14.

`BACKUP TABLE`

BACKUP {TABLE | TABLES} tbl_name [, tbl_name] ... TO 'dir_name'

Copies the named table or tables to the directory named by 'dir_name', which should be the full pathname to a directory on the server host where the backup files should be written. BACKUP TABLE works only for MyISAM tables and requires the SELECT and FILE privileges. It copies the table definition and data files (the .frm and .MYD files), which are the minimum required to restore the table. Index files are not copied because they can be re-created as necessary (using RESTORE TABLE) from the definition and data files.

Tables are read-locked individually as they are backed up. If you are backing up a set of tables, it's possible that tables named later in the table list will be modified while earlier tables are being backed up, or vice versa. If you want to ensure that all the tables are backed up as a group with the contents they have when BACKUP TABLE begins executing, use LOCK TABLE to lock them first and then unlock them after backing up with UNLOCK TABLE. Of course, this will cause the tables to be unavailable for a longer time to other clients that want to update the tables.

The files created by BACKUP TABLE will be owned by the account used to run the server. Any existing backup files for a table are overwritten.

BACKUP TABLE was introduced in MySQL 3.23.25.

Back up table t by creating files t.frm and t.MYD in the directory /var/mysql/bkup:

BACKUP TABLE t TO '/var/mysql/bkup';

`BEGIN`

BEGIN [WORK]

Begins a transaction by disabling auto-commit mode until the next COMMIT or ROLLBACK statement. Statements executed while auto-commit mode is disabled will be committed or rolled back as a unit.

After the transaction has been committed or rolled back, auto-commit mode is restored to the state it was in prior to BEGIN. To manipulate auto-commit mode explicitly, use SET AUTOCOMMIT. (See the description for the SET statement.)

Issuing a BEGIN while a transaction is in progress causes the transaction to be committed implicitly.

BEGIN was introduced in MySQL 3.23.17. BEGIN WORK was introduced as a synonym in MySQL 3.23.19.

`CHANGE MASTER`

CHANGE MASTER TO master_defs

For use on replication slave servers to change the parameters that indicate which master host to use, how to connect to it, or which logs to use. master_defs is a comma-separated list of one or more parameter definitions in param =value format. The allowable definitions are as follows:

MASTER_CONNECT_RETRY = n
The number of seconds to wait between attempts to connect to the master
MASTER_HOST = 'host_name'
The host on which the master server is running
MASTER_LOG_FILE = 'file_name'
The name of the master's binary update log file to use for replication
MASTER_LOG_POS = n
The position within the master log file from which to begin or resume replication
MASTER_PASSWORD = 'pass_val'
The password to use for connecting to the master server
MASTER_PORT = n
The port number to use for connecting to the master server
MASTER_USER = 'user_name'
The username to use for connecting to the master server
RELAY_LOG_FILE = 'file_name'
The slave relay log file name
RELAY_LOG_POS = n
The current position within the slave relay log

With the exception of the hostname or port number, only those parameters that you specify explicitly are changed. Changes to the host or port normally indicate that you're switching to a different master server, so in those cases, the binary update log filename and position are reset to the empty string and zero.

CHANGE MASTER was introduced in MySQL 3.23.23. The RELAY_LOG_FILE and RELAY_LOG_POS options were introduced in MySQL 4.0.2 (replication relay logs were not instituted until then).

`CHECK TABLE`

CHECK {TABLE | TABLES} tbl_name [, tbl_name] ... [options]

This statement checks tables for errors. It works with MyISAM tables and also with InnoDB tables as of MySQL 3.23.39. It requires the SELECT privilege on each table.

options, if given, is a list naming one or more of the following options (not separated by commas):

CHANGED
Check only those tables that have been changed since they were last checked or that have not been closed properly.
EXTENDED
Perform an extended check that attempts to ensure that the table is fully consistent. For example, it verifies that each key in each index points to a data row. This option can be slow.
FAST
Check only those tables that have not been closed properly.
MEDIUM
Check the index, scan the data rows for problems, and perform a checksum verification. This is the default if no options are given.
QUICK
Don't scan the data rows, just the index.

CHECK TABLE returns information about the result of the operation?for example:

mysql> CHECK TABLE t; 
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.t | check | status   | OK       |
+--------+-------+----------+----------+

ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE also return information in this format. Table indicates the table on which the operation was performed. Op indicates the type of operation and has a value of check, analyze, optimize, or repair. The Msg_type and Msg_text columns provide information about the result of the operation.

CHECK TABLE was introduced in MySQL 3.23.13, but it does not work under Windows prior to 3.23.25. The QUICK, FAST, and MEDIUM options were added in MySQL 3.23.16, 3.23.23, and 3.23.31, respectively. From 3.23.15 to 3.23.25, only a single option is allowed and TYPE = must precede it; after that, TYPE = is deprecated, and multiple options are allowed.

`COMMIT`

COMMIT

Commits changes made by statements that are part of the current transaction to record those changes permanently in the database. COMMIT works only for transaction-safe table types. (For non-transactional table types, statements are committed as they are executed.)

COMMIT has no effect if auto-commit mode has not been disabled with BEGIN or by setting AUTOCOMMIT to 0.

Some statements implicitly end any current transaction, as if a COMMIT had been performed:

ALTER TABLE 
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
LOAD MASTER DATA
LOCK TABLES
RENAME TABLE
SET AUTOCOMMIT = 1
TRUNCATE TABLE
UNLOCK TABLES (if tables currently are locked)

COMMIT was introduced in MySQL 3.23.14.

`CREATE DATABASE`

CREATE DATABASE [IF NOT EXISTS] db_name 
    [[DEFAULT] CHARACTER SET charset]

Creates a database with the given name. The statement fails if you don't have the proper privilege to create it. Attempts to create a database with a name that already exists normally result in an error; if the IF NOT EXISTS clause is specified, the database is not created but no error occurs. This clause was introduced in MySQL 3.23.12.

As of MySQL 4.1, the DEFAULT CHARACTER SET clause can be used to specify a default character set attribute for the database. charset can be a character set name or DEFAULT to have tables in the database use the current server character set by default. Database attributes are stored in the db.opt file in the database directory.

`CREATE FUNCTION`

CREATE [AGGREGATE] FUNCTION function_name 
    RETURNS {STRING | REAL | INTEGER}
    SONAME 'shared_library_name'

Specifies a user-defined function (UDF) to be loaded into the func table in the mysql database. function_name is the name by which you want to refer to the function in SQL statements. The keyword following RETURNS indicates the return type of the function. The 'shared_library_name' string names the pathname of the file that contains the executable code for the function.

The AGGREGATE keyword, if given, indicates that the function is an aggregate (group) function like SUM() or MAX(). AGGREGATE was introduced in MySQL 3.23.5.

CREATE FUNCTION requires that the server be built as a dynamically linked binary (not as a static binary) because the UDF mechanism requires dynamic linking. For instructions on writing user-defined functions, refer to the MySQL Reference Manual.

`CREATE INDEX`

CREATE [UNIQUE | FULLTEXT] INDEX index_name 
    ON tbl_name (index_columns)

Adds an index named index_name to the table tbl_name. This statement is handled as an ALTER TABLE ADD INDEX, ALTER TABLE ADD UNIQUE, or ALTER TABLE ADD FULLTEXT statement, according to the absence or presence of the UNIQUE or FULLTEXT keywords. See the entry for ALTER TABLE for details. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead.

If you want to create several indexes on a table, it's preferable to use ALTER TABLE directly; you can add them all with a single statement, which is faster than adding them individually.

CREATE INDEX is functional only as of MySQL 3.22. The option of creating FULLTEXT indexes was introduced in MySQL 3.23.23.

`CREATE TABLE`

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 
    (create_definition,...)
    [table_options]
    [[IGNORE | REPLACE] [AS] select_statement]

create_definition:
    {   col_declaration [reference_definition]
      | [CONSTRAINT symbol] PRIMARY KEY (index_columns)
      | [CONSTRAINT symbol] UNIQUE [INDEX | KEY] [index_name] (index_columns)
      | {INDEX | KEY} [index_name] (index_columns)
      | FULLTEXT [INDEX | KEY] [index_name] (index_columns)
      | [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_columns)
            [reference_definition]
      | [CONSTRAINT symbol] CHECK (expr)
    }

col_declaration:
    col_name col_type
        [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE [KEY]]
        [COMMENT 'string']

reference_definition:
    REFERENCES tbl_name (index_columns)
        [ON DELETE reference_action]
        [ON UPDATE reference_action]
        [MATCH FULL | MATCH PARTIAL]

reference_action:
    {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

The CREATE TABLE statement creates a new table named tbl_name in the current database. If the name is specified as db_name.tbl_name, the table is created in the named database.

If the TEMPORARY keyword is given, the table exists only until the current client connection ends (either normally or abnormally) or until a DROP TABLE statement is issued. A temporary table is visible only to the client that created it.

Normally, attempts to create a table with a name that already exists result in an error. No error occurs under two conditions. First, if the IF NOT EXISTS clause is specified, the table is not created but no error occurs. Second, if TEMPORARY is specified and the original table is not a temporary table, the new temporary table is created, but the original table named tbl_name becomes hidden to the client while the temporary table exists. The original table remains visible to other clients. The original table becomes visible again either at the next client session, if an explicit DROP TABLE is issued for the temporary table, or if the temporary table is renamed to some other name.

The create_definition list names the columns and indexes that you want to create. The list is optional if you create the table by means of a trailing SELECT statement. The table_options clause allows you to specify various properties for the table. If a trailing select_statement is specified (in the form of an arbitrary SELECT statement), the table is created using the result set returned by the SELECT statement. These clauses are described more fully in the following sections.

The IF NOT EXISTS clause, the table_options clause, and the ability to create a table from the result of a SELECT statement were introduced in MySQL 3.23. TEMPORARY tables were introduced in MySQL 3.23.2.

Column and index definitions. A create_definition can be a column or index definition, a FOREIGN KEY clause, or a CHECK clause. CHECK is parsed for compatibility with other database systems, but otherwise ignored. FOREIGN KEY is treated similarly, except for InnoDB tables.

A column declaration col_declaration begins with a column name col_name and a type col_type and may be followed by several optional keywords. The column type may be any of the types listed in Appendix B, Column Type Reference. See that appendix for type-specific attributes that apply to the columns you want to declare. Other optional keywords that may follow the column type are as follows:

NULL or NOT NULL
Specifies that the column may or may not contain NULL values. If neither is specified, NULL is the default.
DEFAULT default_value
Specifies the default value for the column. This cannot be used for BLOB or TEXT types. A default value must be a constant, specified as a number, a string, or NULL.

If no default is specified, a default value is assigned. For columns that may take NULL values, the default is NULL.. For columns that may not be NULL, the default is assigned as follows:
- For numeric columns, the default is 0, except for AUTO_INCREMENT columns. For AUTO_INCREMENT, the default is the next number in the column sequence.
- For date and time types other than TIMESTAMP, the default is the "zero" value for the type (for example, '0000-00-00' for DATE). For TIMESTAMP, the default is the current date and time for the first TIMESTAMP column in a table, and the "zero" value for any following TIMESTAMP columns.
- For string types other than ENUM, the default is the empty string. For ENUM, the default is the first enumeration element.
AUTO_INCREMENT
This keyword applies only to integer column types. An AUTO_INCREMENT column is special in that when you insert NULL into it, the value actually inserted is the next value in the column sequence. (Typically, this is one greater than the current maximum value in the column.) AUTO_INCREMENT values start at 1 by default. For MyISAM tables (and for HEAP tables as of MySQL 4.1), the first value may be specified explicitly with the AUTO_INCREMENT = n table option. The column must also be specified as a UNIQUE index or PRIMARY KEY and should be NOT NULL. There may be at most one AUTO_INCREMENT column per table.
PRIMARY KEY
Specifies that the column is a PRIMARY KEY. A PRIMARY KEY must be NOT NULL.
UNIQUE [KEY]
Specifies that the column is a UNIQUE index. This attribute may be specified as of MySQL 3.23.
COMMENT 'string'
Specifies a descriptive comment to be associated with the column. Prior to MySQL 4.1, this attribute is parsed but ignored. As of 4.1, it is remembered and displayed by SHOW CREATE TABLE and SHOW FULL COLUMNS.

The PRIMARY KEY, UNIQUE, INDEX, KEY, and FULLTEXT clauses specify indexes. PRIMARY KEY and UNIQUE specify indexes that must contain unique values. INDEX and KEY are synonymous; they specify indexes that may contain duplicate values. The index is based on the columns named in index_columns, each of which must be a column in tbl_name. If there are multiple columns, they should be separated by commas. For CHAR and VARCHAR columns, you can index a prefix of the column, using col_name(n) syntax to index the first n bytes of column values. (The exception is that InnoDB tables do not allow index prefixes.) For BLOB and TEXT columns, you must specify a prefix value; you cannot index the entire column. Prefixes for columns named in a FULLTEXT index are ignored if given. If the index name index_name is not specified, a name is chosen automatically based on the name of the first indexed column.

FULLTEXT indexes are allowed only for MyISAM tables and only for TEXT columns and non-BINARY CHAR and VARCHAR columns.

Indexed columns must be declared NOT NULL for ISAM tables, and for HEAP tables prior to MySQL 4.0.2. PRIMARY KEY columns must always be declared NOT NULL.

Table options. The table_options clause is available as of MySQL 3.23 (some of the options appeared later, as indicated in the descriptions). Table options can include one or more of the options in the following list. If multiple options are present, they should not be separated by commas. Each specifier applies to all table types unless otherwise noted.

AUTO_INCREMENT = n
The first AUTO_INCREMENT value to be generated for the table. This option is effective only for MyISAM tables, and for HEAP tables as of MYSQL 4.1.
AVG_ROW_LENGTH = n
The approximate average row length of your table. For MyISAM tables, MySQL uses the product of the AVG_ROW_LENGTH and MAX_ROWS values to determine the maximum data file size. The MyISAM handler can use internal row pointers with a table from 1 to 8 bytes wide. The default pointer width is wide enough to allow tables up to 4GB. If you require a larger table (and your operating system supports larger files), the MAX_ROWS and AVG_ROW_LENGTH table options allow the MyISAM handler to adjust the internal pointer width. A large product of these values causes the handler to use wider pointers. (Conversely, a small product allows the handler to use smaller pointers. This won't save you much space if the table is small anyway, but if you have many small tables, the cumulative savings may be significant.)
[DEFAULT] CHARACTER SET charset
Specifies the table's default character set. charset can be a character set name or DEFAULT to use the database character set if it is defined or the server character set if not. This option determines which character set is used for character columns that are declared without an explicit character set. In the following example, c1 will be assigned the sjis character set and c2 the ujis character set:
```
CREATE TABLE t 
(
    c1 CHAR(50) CHARACTER SET sjis,
    c2 CHAR(50)
) CHARACTER SET ujis;
```
This option also applies to subsequent table modifications made with ALTER TABLE for character column changes that do not name a character set explicitly.

CHARACTER SET was introduced in MySQL 4.1. It can be given in any of several variant synonymous forms. The following are all equivalent:
```
CHARACTER SET charset 
CHARSET = charset
CHARSET charset
```
These synonymous forms can also be used in other places where character sets can be specified, such as in column definitions or in the CREATE DATABASE and ALTER DATABASE statements.
CHECKSUM = {0 | 1}
If this is set to 1, MySQL maintains a checksum for each table row. There is a slight penalty for updates to the table, but the presence of checksums improves the table checking process. (MyISAM tables only.)
COMMENT = 'string'
A comment for the table. The maximum length is 60 characters. This comment is shown by SHOW CREATE TABLE and SHOW TABLE STATUS.
DATA DIRECTORY = 'dir_name'
This option is used for MyISAM tables only, and indicates the directory where the data (.MYD) file should be written. 'dir_name' must be a full pathname. This option was introduced in MySQL 4.0, and works only if the server is started without the --skip-symlink option. On some operating systems, such as Mac OS X, FreeBSD, and BSDI, symlinks are not thread-safe and are disabled by default.
DELAY_KEY_WRITE = {0 | 1}
If this is set to 1, the index cache is flushed only occasionally for the table, rather than after each insert operation. (MyISAM tables only.)
INDEX DIRECTORY = 'dir_name'
This option is used for MyISAM tables only and indicates the directory where the index (.MYI) file should be written. 'dir_name' must be a full pathname. This option was introduced in MySQL 4.0, and is subject to the same constraints as the DATA DIRECTORY option.
INSERT_METHOD = {NO | FIRST | LAST}
This is used for MERGE tables to specify how to insert rows. A value of NO disallows inserts entirely. Values of FIRST or LAST indicate that rows should be inserted into the first or last of the MyISAM tables that make up the MERGE table. This option was introduced in MySQL 4.0.
MAX_ROWS = n
The maximum number of rows you plan to store in the table. The description of the AVG_ROW_LENGTH option indicates how this value is used. (MyISAM tables only.)
MIN_ROWS = n
The minimum number of rows you plan to store in the table. This option can be used for HEAP tables to give the HEAP handler a hint about how to optimize memory usage.
PACK_KEYS = {0 | 1 | DEFAULT}
This option controls index compression for MyISAM and ISAM tables, which allows runs of similar index values to be compressed. The usual effect is an update penalty and an improvement in retrieval performance. A value of 0 specifies no index compression. A value of 1 specifies compression for string (CHAR and VARCHAR) values and (for MyISAM tables) numeric index values. As of MySQL 4.0, a value of DEFAULT can be used, which specifies compression only for long string columns.
PASSWORD = 'string'
Specifies a password for encrypting the table's description file. This option normally has no effect; it enabled only for certain support contract customers.
RAID_TYPE = {1 | STRIPED | RAID0} RAID_CHUNKS = n RAID_CHUNKSIZE =n
These options are used together and are available as of MySQL 3.23.12 for use with MyISAM to achieve larger effective table sizes. The options are ineffective unless MySQL was configured with the --with-raid option at build time.

The default RAID_TYPE value is STRIPED; the other two types actually are just aliases for STRIPED. RAID_CHUNKS and RAID_CHUNKSIZE control the allocation of space to be used for the table's data. The server creates several directories under the database directory (the number is determined by the RAID_CHUNKS value) and creates a data file named tbl_name.MYD in each. As rows are added to the table, the server writes to the file in the first directory until it fills up and then proceeds to the next directory. The size of the file in each directory is controlled by the value of RAID_CHUNKSIZE, which is measured in MB (1024 bytes). Directories are named using hexadecimal digits in the sequence 00, 01, and so forth. For example, if RAID_CHUNKS is 256 and RAID_CHUNKSIZE is 1000, the server creates 256 directories named 00 through ff, and writes up to 1000MB of data to the file in each directory.
ROW_FORMAT = {DEFAULT | FIXED | DYNAMIC | COMPRESSED}
This option applies only to MyISAM tables and specifies the row storage type. The option can be used as of MySQL 3.23.6.
TYPE = {ISAM | MYISAM | MERGE | HEAP | BDB | INNODB}
Specifies the table storage format. The characteristics of these storage formats are described in the "Table Types" section in Chapter 3, "MySQL SQL Syntax and Use." The default format for MySQL as of version 3.23 is MyISAM unless the server has been configured otherwise (either at build time or if the server was started with a --default-table-type option). MRG_MYISAM, BERKELEYDB, and INNOBASE are synonyms for MERGE, BDB, and INNODB, respectively. If you specify a table type that is legal but for which no table handler is available, MySQL uses the default storage format. If you give an invalid value for the option, an error results. This option was introduced in MySQL 3.23. Prior to that, CREATE TABLE always creates tables in ISAM format.
UNION = (tbl_list)
This option is used for MERGE tables. It specifies the list of MyISAM tables that make up the MERGE table.

Trailing SELECT statement. If a select_statement clause is specified (as a trailing SELECT query), the table is created using the contents of the result set returned by the query. Rows that duplicate values on a unique index are either ignored or they replace existing rows according to whether IGNORE or REPLACE is specified. If neither is specified, the statement aborts with an error.

Foreign key support. The InnoDB table handler provides foreign key support. A foreign key in a child table is indicated by FOREIGN KEY, an optional index name, a list of the columns that make up the foreign key, and a REFERENCES definition. The index name, if given, is ignored. The REFERENCES definition names the parent table and columns to which the foreign key refers and indicates what to do when a parent table record is deleted. The actions that InnoDB implements are CASCADE (delete the corresponding child table records) and SET NULL (set the foreign key columns in the corresponding child table records to NULL). The RESTRICT, NO ACTION, and SET DEFAULT actions are parsed but ignored.

ON UPDATE and MATCH clauses in REFERENCE definitions are parsed but ignored. (If you specify a foreign key definition for a table type other than InnoDB, the entire definition is ignored.)

The following statements demonstrate some ways in which CREATE TABLE can be used.

Create a table with three columns. The id column is a PRIMARY KEY, and the last_name and first_name columns are indexed together:

CREATE TABLE customer 
(
    id          SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    last_name   CHAR(30) NOT NULL,
    first_name  CHAR(20) NOT NULL,
    PRIMARY KEY (id),
    INDEX (last_name, first_name)
);

Create a temporary table and make it a HEAP (in-memory) table for greater speed:

CREATE TEMPORARY TABLE tmp_table 
    (id MEDIUMINT NOT NULL UNIQUE, name CHAR(40))
    TYPE = HEAP;

Create a table as a copy of another table:

CREATE TABLE prez_copy SELECT * FROM president;

Create a table using only part of another table:

CREATE TABLE prez_alive SELECT last_name, first_name, birth 
    FROM president WHERE death IS NULL;

If creation declarations are specified for a table created and populated by means of a trailing SELECT statement, the declarations are applied after the table contents have been inserted into the table. For example, you can declare that a selected column should be made into a PRIMARY KEY:

CREATE TABLE new_tbl (PRIMARY KEY (a)) SELECT a, b, c FROM old_tbl;

As of MySQL 4.1, you can specify declarations for the columns in the new table to override the definitions that would be used by default based on the characteristics of the result set:

CREATE TABLE new_tbl (a INT NOT NULL AUTO_INCREMENT, b DATE, PRIMARY KEY (a)) 
    SELECT a, b, c FROM old_tbl;

`DELETE`

DELETE [LOW_PRIORITY] [QUICK] FROM tbl_name 
    [WHERE where_expr] [ORDER BY ...] [LIMIT n]

DELETE [LOW_PRIORITY] [QUICK] tbl_name [, tbl_name] ...
    FROM tbl_name [, tbl_name] ...
    [WHERE where_expr]

DELETE [LOW_PRIORITY] [QUICK] FROM tbl_name [, tbl_name] ...
    USING tbl_name [, tbl_name] ...
    [WHERE where_expr]

The first form of the DELETE statement deletes rows from the table tbl_name. The rows deleted are those that match the conditions specified in the WHERE clause:

DELETE FROM score WHERE event_id = 14; 
DELETE FROM member WHERE expiration < CURDATE();

If the WHERE clause is omitted, all records in the table are deleted.

Specifying LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table. LOW_PRIORITY was introduced in MySQL 3.22.5.

For MyISAM tables, specifying QUICK can make the statement quicker; the MyISAM handler will not perform its usual index tree leaf merging. QUICK was introduced in MySQL 3.23.25.

If the LIMIT clause is given, the value n specifies the maximum number of rows that will be deleted. LIMIT was introduced in MySQL 3.22.7.

With ORDER BY, rows are deleted in the resulting sort order. Combined with LIMIT, this provides more precise control over which rows are deleted. ORDER BY was introduced in MySQL 4.0.0 and has same syntax as for SELECT.

Normally, DELETE returns the number of records deleted. DELETE with no WHERE clause will empty the table, and you may find that, prior to MySQL 4, the server optimizes this special case by dropping and recreating the table from scratch rather than deleting records on a row-by-row basis. This is extremely fast, but a row count of zero may be returned. To obtain a true count, specify a WHERE clause that matches all records?for example:

DELETE FROM tbl_name WHERE 1;

There is a significant performance penalty for row-by-row deletion, however.

If you don't need a row count, another way to empty a table is to use TRUNCATE TABLE.

The second and third forms of DELETE allow rows to be deleted from multiple tables at once. They also allow you to identify the rows to delete based on joins between tables. These forms are available as of MySQL 4.0.0 and 4.0.2, respectively. Names in the list of tables from which rows are to be deleted can be given as tbl_name or tbl_name.*; the latter form is supported for ODBC compatibility.

To delete rows in t1 having id values that match those in t2, use the first multiple-table syntax like this:

DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;

or the second syntax like this:

DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;

`DESCRIBE`

{DESCRIBE | DESC} tbl_name [col_name | 'pattern'] 
{DESCRIBE | DESC} select_statement

DESCRIBE with a table name produces the same kind of output as SHOW COLUMNS. See the SHOW entry for more information. With this syntax, a trailing column name restricts output to information for the given column. A trailing string is interpreted as a pattern, as for the LIKE operator, and restricts output to those columns having names that match the pattern.

Display output for the last_name column of the president table:

DESCRIBE president last_name;

Display output for both the last_name and first_name columns of the president table:

DESCRIBE president '%name';

DESCRIBE with a SELECT statement is a synonym for EXPLAIN. See the EXPLAIN entry for more information. (DESCRIBE and EXPLAIN actually are completely synonymous in MySQL, but DESCRIBE is more often used to obtain table descriptions and EXPLAIN to obtain SELECT statement execution information.)

`DO`

DO expr [, expr] ...

Evaluates the expressions without returning any results. This makes DO more convenient than SELECT for expression evaluation, because you need not deal with a result set. For example, DO can be used for setting variables or for invoking functions that you are interested in primarily for their side effects rather than for their return values.

DO @sidea := 3, @sideb := 4, @sidec := SQRT(@sidea*@sidea+@sideb*@sideb); 
DO RELEASE_LOCK('mylock');

DO was introduced in MySQL 3.23.47.

`DROP DATABASE`

DROP DATABASE [IF EXISTS] db_name

Drops (removes) the given database. After you drop a database, it's gone, so be careful. The statement fails if the database does not exist (unless you specify IF EXISTS) or if you don't have the proper privilege. The IF EXISTS clause can be specified to suppress the error message that normally results if the database does not exist. IF EXISTS was introduced in MySQL 3.22.2.

A database is represented by a directory under the data directory. If you have put non-table files in that directory, those files are not deleted by the DROP DATABASE statement. In that case, the database directory itself is not removed, either, and its name will continue to be listed by SHOW DATABASES.

`DROP FUNCTION`

DROP FUNCTION function_name

Removes a user-defined function that was previously loaded with CREATE FUNCTION.

`DROP INDEX`

DROP INDEX index_name ON tbl_name

Drops the index index_name from the table tbl_name. This statement is handled as an ALTER TABLE DROP INDEX statement. See the entry for ALTER TABLE for details. DROP INDEX cannot be used to drop a PRIMARY KEY; use ALTER TABLE instead.

DROP INDEX is functional only as of MySQL 3.22.

`DROP TABLE`

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

Drops the named table or tables from the database they belong to. If the IF EXISTS clause is given, dropping a non-existent table is not an error. IF EXISTS was introduced in MySQL 3.22.2.

The RESTRICT and CASCADE keywords have no effect. They are parsed for compatibility with code ported from other databases, but ignored. These keywords can be used as of MySQL 3.23.29.

`EXPLAIN`

EXPLAIN tbl_name [col_name | 'pattern'] 

EXPLAIN select_statement

The first form of this statement is equivalent to DESCRIBE tbl_name. See the description of the DESCRIBE statement for more information.

The second form of the EXPLAIN statement provides information about how MySQL would execute the SELECT statement following the EXPLAIN keyword.

EXPLAIN SELECT score.* FROM score, event 
    WHERE score.event_id = event.event_id AND event.event_id = 14;

Output from EXPLAIN consists of one or more rows containing the following columns:

table
The table to which the output row refers.
type
The type of join that MySQL will perform. The possible types are, from best to worst: system, const, eq_ref, ref, range, index, and ALL., The better types are more restrictive, meaning that MySQL has to look at fewer rows from the table when performing the retrieval.
possible_keys
The indexes that MySQL considers candidates for finding rows in the table named in the table column. A value of NULL means that no indexes were found.
key
The index that MySQL actually will use for finding rows in the table. A value of NULL indicates that no index will be used.
key_len
How much of the index will be used. This can be less than the full index row length if MySQL will use a leftmost prefix of the index.
ref
The values to which MySQL will compare index values. The word const or '???' means the comparison is against a constant; a column name indicates a column-to-column comparison.
rows
An estimate of the number of rows from the table that MySQL must examine to perform the query. The product of the values in this column is an estimate of the total number of row combinations that must be examined from all tables.
Extra
Using index indicates that MySQL can retrieve information for the table using only information in the index without examining the data file (this used to appear as Only index). Using where indicates the use of the information in the WHERE clause of the SELECT statement (this used to appear as where used).