1.5 SQL

MySQL fully supports ANSI SQL 92, entry level. A SQL reference for MySQL is thus largely a general SQL reference. Nevertheless, MySQL contains some proprietary enhancements that can help you at the mysql command line. This section thus provides a reference for the SQL query language as it is supported in MySQL.

SQL is a kind of controlled English language consisting of verb phrases. Each of these verb phrases begins with an SQL command followed by other SQL keywords, literals, identifiers, or punctuation.

1.5.1 Case Sensitivity

Case-sensitivity in MySQL depends on a variety of factors, including the token in question and the underlying operating system. Table 1-3 shows the case-sensitivity of different SQL tokens in MySQL.

1.5.2 Literals

Literals come in the following varieties:

String

String literals may be enclosed either by single or double quotes. If you wish to be ANSI compatible, you should always use single quotes. Within a string literal, you may represent special characters through escape sequences. An escape sequence is a backslash followed by another character to indicate to MySQL that the second character has a meaning other than its normal meaning. Table 1-4 shows the MySQL escape sequences. Quotes can also be escaped by doubling them up: 'This is a ''quote'''. However, you do not need to double up on single quotes when the string is enclosed by double quotes: "This is a 'quote'".

Binary

Like string literals, binary literals are enclosed in single or double quotes. You must use escape sequences in binary data to escape NUL (ASCII 0), " (ASCII 34), ' (ASCII 39), and \ (ASCII 92).

Decimal

Numbers appear as a sequence of digits. Negative numbers are preceded by a - sign and a . indicates a decimal point. You may also use scientific notation, as in: -45198.2164e+10.

Hexadecimal

The way in which a hexadecimal is interpreted is dependent on the context. In a numeric context, the hexadecimal literal is treated is a numeric value. In a non-numeric context, it is treated as a binary value. For example, 0x1 + 1 is 2, but 0x4d7953514c by itself is MySQL.

Null

The special keyword NULL signifies a null literal in SQL. In the context of import files, the special escape sequence \N signifies a null value.

1.5.3 Identifiers

You can reference any given object on a MySQL serverassuming you have the proper rightsusing one of the following conventions:

Absolute naming

Absolute naming specifies the full path of the object you are referencing. For example, the column BALANCE in the table ACCOUNT in the database BANK would be referenced absolutely as:

BANK.ACCOUNT.BALANCE

Relative naming

Relative naming allows you to specify only part of the object's name, with the rest of the name being assumed based on your current context. For example, if you are currently connected to the BANK database, you can reference the BANK.ACCOUNT.BALANCE column as ACCOUNT.BALANCE. In an SQL query where you have specified that you are selecting from the ACCOUNT table, you may reference the column using only BALANCE. You must provide an extra layer of context whenever relative naming might result in ambiguity. An example of such ambiguity would be a SELECT statement pulling from two tables that both have BALANCE columns.

Aliasing

Aliasing enables you to reference an object using an alternate name that helps avoid both ambiguity and the need to fully qualify a long name.

In general, MySQL allows you to use any character in an identifier. (Older versions of MySQL limited identifiers to valid alphanumeric characters from the default character set, as well as $ and _.) This rule is limited, however, for databases and tables, because these values must be treated as files on the local filesystem. You can therefore use only characters valid for the underlying filesystem's naming conventions in a database or table name. Specifically, you may not use / or . in a database or table name. You can never use NUL (ASCII 0) or ASCII 255 in an identifier.

When an identifier is also an SQL keyword, you must enclose the identifier in backticks:

CREATE TABLE 'select' ( 'table' INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

Since Version 3.23.6, MySQL supports the quoting of identifiers using both backticks and double quotes. For ANSI compatibility, however, you should use double quotes for quoting identifiers. You must, however, be running MySQL in ANSI mode.

1.5.4 Comments

You can introduce comments in your SQL to specify text that should not be interpreted by MySQL. This is particularly useful in batch scripts for creating tables and loading data. MySQL specifically supports three kinds of commenting: C, shell-script, and ANSI SQL commenting.

C commenting treats anything between /* and */ as comments. Using this form of commenting, your comments can span multiple lines. For example:

/*
 * Creates a table for storing customer account information.
*/
DROP TABLE IF EXISTS ACCOUNT;
   
CREATE TABLE ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL
                       PRIMARY KEY AUTO_INCREMENT,
                       BALANCE DECIMAL(9,2) NOT NULL );

Within C comments, MySQL still treats single and double quotes as a start to a string literal. In addition, a semicolon in the comment will cause MySQL to think you are done with the current statement.

Shell-script commenting treats anything from a # character to the end of a line as a comment:

CREATE TABLE ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL 
                       PRIMARY KEY AUTO_INCREMENT,
                       BALANCE DECIMAL(9,2) 
                       NOT NULL ); # Not null ok?

MySQL does not really support ANSI SQL commenting, but it comes close. ANSI SQL commenting is distinguished by adding -- to the end of a line. MySQL supports two dashes and a space ('-- `) followed by the comment. The space is the non-ANSI part:

DROP TABLE IF EXISTS ACCOUNT; -- Drop the table if it already exists

1.5.5 Commands

This section presents the full syntax of all commands accepted by MySQL.

ALTER [IGNORE] TABLE table action_list

The ALTER statement covers a wide range of actions that modify the structure of a table. This statement is used to add, change, or remove columns from an existing table as well as to remove indexes. To perform modifications on the table, MySQL creates a copy of the table and changes it, meanwhile queuing all table altering queries. When the change is done, the old table is removed and the new table put in its place. At this point the queued queries are performed.

As a safety precaution, if any of the queued queries create duplicate keys that should be unique, the ALTER statement is rolled back and cancelled. If the IGNORE keyword is present in the statement, duplicate unique keys are ignored and the ALTER statement proceeds as normal. Be warned that using IGNORE on an active table with unique keys invites table corruption.

Possible actions in action_list include:

ADD [COLUMN] create_clause [FIRST | AFTER column]

ADD [COLUMN] ( create_clause, create_clause,...)

Adds a new column to the table. The create_clause is the SQL that would define the column in a normal table creation (see CREATE TABLE for the syntax and valid options). The column will be created as the first column if the FIRST keyword is specified. Alternately, you can use the AFTER keyword to specify which column it should be added after. If neither FIRST nor AFTER is specified, the column is added at the end of the table's column list. You may add multiple columns at once by enclosing multiple create clauses separated with commas, inside parentheses.

ADD [CONSTRAINT symbol] FOREIGN KEY name ( column, ...)[ reference]

Currently applies only to the InnoDB table type, which supports foreign keys. This syntax adds a foreign key reference to your table.

ADD FULLTEXT [ name] ( column, ...)

Adds a new full text index to the table using the specified columns.

ADD INDEX [ name] ( column, ...)

Adds an index to the altered table, indexing the specified columns. If the name is omitted, MySQL will choose one automatically.

ADD PRIMARY KEY ( column, ...)

Adds a primary key consisting of the specified columns to the table. An error occurs if the table already has a primary key.

ADD UNIQUE[ name] ( column, ...)

Adds a unique index to the altered table; similar to the ADD INDEX statement.

ALTER [COLUMN] column SET DEFAULT value

Assigns a new default value for the specified column. The COLUMN keyword is optional and has no effect.

ALTER [COLUMN] column DROP DEFAULT

Drops the current default value for the specified column. A new default value is assigned to the column based on the CREATE statement used to create the table. The COLUMN keyword is optional and has no effect.

DISABLE KEYS

Tells MySQL to stop updating indexes for MyISAM tables. This clause applies only to non-unique indexes. Because MySQL is more efficient at rebuilding its keys than it is at building them one at a time, you may want to disable keys while performing bulk inserts into a database. You should avoid this trick, however, if you have read operations going against the table while the inserts are running.

ENABLE KEYS

Recreates the indexes no longer being updated because of a prior call to DISABLE KEYS.

CHANGE [COLUMN] column create_clause

MODIFY [COLUMN] create_clause [FIRST | AFTER column]

Alters the definition of a column. This statement is used to change a column from one type to a different type while affecting the data as little as possible. The create clause is the same syntax as in the CREATE TABLE statement. This includes the name of the column. The MODIFY version is the same as CHANGE if the new column has the same name as the old. The COLUMN keyword is optional and has no effect. MySQL will try its best to perform a reasonable conversion. Under no circumstance will MySQL give up and return an error when using this statement; a conversion of some sort will always be performed. With this in mind, you should make a backup of the data before the conversion and immediately check the new values to see if they are reasonable.

DROP [COLUMN] column

Deletes a column from a table. This statement will remove a column and all its data from a table permanently. There is no way to recover data destroyed in this manner other than from backups. All references to this column in indexes will be removed. Any indexes where this was the sole column will be destroyed as well. (The COLUMN keyword is optional and has no effect.)

DROP PRIMARY KEY

Drops the primary key from the table. If no primary key is found in the table, the first unique key is deleted.

DROP INDEX key

Removes an index from a table. This statement will completely erase an index from a table. This statement will not delete or alter any of the table data itself, only the index data. Therefore, an index removed in this manner can be recreated using the ALTER TABLE ... ADD INDEX statement.

RENAME [AS] new_table

RENAME [TO] new_table

Changes the name of the table. This operation does not affect any of the data or indexes within the table, only the table's name. If this statement is performed alone, without any other ALTER TABLE clauses, MySQL will not create a temporary table as with the other clauses, but simply perform a fast Unix-level rename of the table files.

ORDER BY column [ASC | DESC]

Forces the table to be reordered by sorting on the specified column name. The table will no longer be in this order when new rows are inserted. This option is useful for optimizing tables for common sorting queries. You can specify multiple columns.

table_options

Enables a redefinition of the tables options such as the table type.

Multiple ALTER statements may be combined into one using commas, as in the following example:

ALTER TABLE mytable DROP myoldcolumn, ADD mynewcolumn INT

To perform any of the ALTER TABLE actions, you must have SELECT, INSERT, DELETE, UPDATE, CREATE, and DROP privileges for the table in question.

Examples

# Add the field 'address2' to the table 'people' and make 
# it of type 'VARCHAR' with a maximum length of 100.
ALTER TABLE people ADD COLUMN address2 VARCHAR(100)
# Add two new indexes to the 'hr' table, one regular index 
# for the 'salary' field and one unique index for the 'id' 
# field. Also, continue operation if duplicate values are 
# found while creating the 'id_idx' index 
# (very dangerous!).
ALTER TABLE hr ADD INDEX salary_idx ( salary )
ALTER IGNORE TABLE hr ADD UNIQUE id_idx ( id )
# Change the default value of the 'price' field in the 
# 'sprockets' table to $19.95.
ALTER TABLE sprockets ALTER price SET DEFAULT '$19.95'
# Remove the default value of the 'middle_name' field in
# the 'names' table.
ALTER TABLE names ALTER middle_name DROP DEFAULT
# Change the type of the field 'profits' from its previous 
# value (which was perhaps INTEGER) to BIGINT. The first
# instance of 'profits' is the column to change, and the
# second is part of the create clause.
ALTER TABLE finances CHANGE COLUMN profits profits BIGINT
# Remove the 'secret_stuff' field from the table
# 'not_private_anymore'
ALTER TABLE not_private_anymore DROP secret_stuff
# Delete the named index 'id_index' as well as the primary 
# key from the table 'cars'.
ALTER TABLE cars DROP INDEX id_index, DROP PRIMARY KEY
# Rename the table 'rates_current' to 'rates_1997'
ALTER TABLE rates_current RENAME AS rates_1997

ANALYZE TABLE table1, table2, ..., tablen

Acquires a read lock on the table and performs an analysis on it for MyISAM and BDB tables. The analysis examines the key distribution in the table. It returns a result set with the following columns:

Table

The name of the table.

Op

The value analyze.

Msg_type

One of status, error, or warning.

Msg_text

The message resulting from the analysis.

CREATE DATABASE [IF NOT EXISTS] dbname

Creates a new database with the specified name. You must have the proper privileges to create the database. Running this command is the same as running the mysqladmincreate utility.

Example

CREATE DATABASE Bank;

CREATE [AGGREGATE] FUNCTION name 
RETURNS return_type SONAME library

The CREATE FUNCTION statement allows MySQL statements to access precompiled executable functions known as user-defined functions (UDFs). These functions can perform practically any operation, since they are designed and implemented by the user. The return value of the function can be STRING, for character data; REAL, for floating point numbers; or INTEGER, for integer numbers. MySQL will translate the return value of the C function to the indicated type. The library file that contains the function must be a standard shared library that MySQL can dynamically link into the server.

Example

CREATE FUNCTION multiply RETURNS REAL SONAME mymath.so

CREATE [UNIQUE|FULLTEXT] INDEX name ON table (column, ...)

The CREATE INDEX statement is provided for compatibility with other implementations of SQL. In older versions of SQL, this statement does nothing. As of 3.22, this statement is equivalent to the ALTER TABLE ADD INDEX statement. To perform the CREATE INDEX statement, you must have INDEX privileges for the table in question.

The UNIQUE keyword constrains the table to having only one row in which the index columns have a given value. If the index is multicolumn, individual column values may be repeated; the whole index must be unique.

The FULLTEXT keyword enables keyword searching on the indexed column or columns.

Example

CREATE UNIQUE INDEX TransIDX ON Translation ( language, 
locale, code );

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
(create_clause, ...) [table_options]
[[IGNORE|REPLACE] select]

The CREATE TABLE statement defines the structure of a table within the database. This statement is how all MySQL tables are created. If the TEMPORARY keyword is used, the table exists only as long as the current client connection exists, or until you explicitly drop the table.

The IF NOT EXISTS clause tells MySQL to create the table only if the table does not already exist. If the table does exist, nothing happens. If the table exists and IF NOT EXISTS and TEMPORARY are not specified, an error will occur. If TEMPORARY is specified and the table exists but IF NOT EXISTS is not specified, the existing table will simply be invisible to this client for the duration of the new temporary table's life.

The CREATE clause can either define the structure of a specific column or define a meta-structure for the column. A CREATE clause that defines a column consists of the name of the new table followed by any number of field definitions. The syntax of a field definition is:

column type [NOT NULL | NULL] [DEFAULT value]
[AUTO_INCREMENT] [PRIMARY KEY] [reference]

The modifiers in this syntax are:

AUTO_INCREMENT

Indicates that the column should be automatically incremented using the current greatest value for that column. Only whole number columns may be auto-incremented.

DEFAULT value

This attribute assigns a default value to a field. If a row is inserted into the table without a value for this field, this value will be inserted. If a default is not defined, a null value is inserted, unless the field is defined as NOT NULL in which case MySQL picks a value based on the type of the field.

NOT NULL

This attribute guarantees that every entry in the column will have some non-null value. Attempting to insert a NULL value into a field defined with NOT NULL will generate an error.

NULL

This attribute specifies that the field is allowed to contain NULL values. This is the default if neither this nor the NOT NULL modifier are specified. Fields that are contained within an index cannot contain the NULL modifier. (The attribute will be ignored, without warning, if it does exist in such a field.)

PRIMARY KEY

This attribute automatically makes the field the primary key (see later) for the table. Only one primary key may exist for a table. Any field that is a primary key must also contain the NOT NULL modifier.

REFERENCES table [(column, . . .)] [MATCH FULL | MATCH PARTIAL ] [ON DELETE option] [ON UPDATE option]

Creates a foreign key reference. Currently applies only to the InnoDB table type.

You may specify meta-structure such as indexes and constraints via the following clauses:

FULLTEXT ( column, ... )

Since MySQL 3.23.23, MySQL has supported full text indexing. The use and results of this search are described in the online MySQL reference manual. To create a full text index, use the FULLTEXT keyword:

CREATE TABLE Item ( itemid INT NOT NULL PRIMARY KEY,
       name VARCHAR(25) NOT NULL,
       description TEXT NOT NULL,
       FULLTEXT ( name, description )
);

INDEX [name] (column, ...)

Creates a regular index of all of the named columns (KEY and INDEX, in this context, are synonyms). Optionally the index may be given a name. If no name is provided, a name is assigned based on the first column given and a trailing number, if necessary, for uniqueness. If a key contains more than one column, leftmost subsets of those columns are also included in the index. Consider the following index definition:

INDEX idx1 ( name, rank, serial );

When this index is created, the following groups of columns will be indexed:

• name, rank, serial

• name, rank

• name

KEY [name] (column, ...)

Synonym for INDEX.

PRIMARY KEY

Creates the primary key of the table. A primary key is a special key that can be defined only once in a table. The primary key is a UNIQUE key with the name PRIMARY. Despite its privileged status, it behaves almost the same as every other unique key, except it does not allow NULL values.

UNIQUE [name] (column, ...)

Creates a special index where every value contained in the index (and therefore in the fields indexed) must be unique. Attempting to insert a value that already exists into a unique index will generate an error. The following would create a unique index of the nicknames field:

UNIQUE (nicknames);

When indexing character fields (CHAR, VARCHAR, and their synonyms only), it is possible to index only a prefix of the entire field. For example, the following will create an index of the numeric field id along with the first 20 characters of the character field address:

INDEX adds ( id, address(20) );

When performing any searches of the field address, only the first 20 characters will be used for comparison, unless more than one match is found that contains the same first 20 characters, in which case a regular search of the data is performed. Therefore, it can be a big performance bonus to index only the number of characters in a text field that you know will make the value unique. This feature is, however, dependent on the underlying table type.

In addition, MySQL supports the following special "types," and the MySQL team is working on adding functionality to support them:

FOREIGN KEY (name (column, [column2, . . . ])

CHECK

As of MySQL 3.23, you can specify table options at the end of a CREATE TABLE statement. These options are:

AUTO_INCREMENT = start

Specifies the first value to be used for an AUTO_INCREMENT column. Works only with MyISAM tables.

AVG_ROW_LENGTH = length

An option for tables containing large amounts of variable-length data. The average row length is an optimization hint to help MySQL manage this data.

CHECKSUM = 0 or 1

When set to 1, this option forces MySQL to maintain a checksum for the table to improve data consistency. This option creates a performance penalty.

COMMENT = comment

Provides a comment for the table. The comment may not exceed 60 characters.

DELAY_KEY_WRITE = 0 or 1

For MyISAM tables only. When set, this option delays key table updates until the table is closed.

MAX_ROWS = rowcount

The maximum number of rows you intend to store in the table.

MIN_ROWS = rowcount

The minimum number of rows you intend to store in the table.

PACK_KEYS = 0 or 1

For MyISAM and ISAM tables only. This option provides a performance booster for read-heavy tables. Set to 1, this option causes smaller keys to be created and thus slows down writes while speeding up reads.

PASSWORD = 'password'

Available only to MySQL customers with special commercial licenses. This option uses the specified password to encrypt the table's .frm file. This option has no effect on the standard version of MySQL.

ROW_FORMAT = DYNAMIC or STATIC

For MyISAM tables only. Defines how the rows should be stored in a table.

TYPE = rowtype

Specifies the table type of the database. If the selected table type is not available, the closest table type available is used. For example, BDB is not available yet for Mac OS X. If you specified TYPE=BDB on a Mac OS X system, MySQL will instead create the table as a MyISAM table (the default table type). Supported table types are described later.

Finally, you can create a table and populate it straight from the results of a SQL query:

CREATE TABLE tblname SELECT query

You must have CREATE privileges on a database to use the CREATE TABLE statement.

Examples

# Create the new empty database 'employees'
CREATE DATABASE employees;
# Create a simple table
CREATE TABLE emp_data ( id INT, name CHAR(50) );
# Create a complex table
CREATE TABLE IF NOT EXISTS emp_review (
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 emp_id INT NOT NULL REFERENCES emp_data ( id ),
 review TEXT NOT NULL,
 INDEX ( emp_id ),
 FULLTEXT ( review )
) AUTO_INCREMENT = 1, TYPE=InnoDB;
# Make the function make_coffee (which returns a string
# value and is stored in the myfuncs.so shared library)
# available to MySQL.
CREATE FUNCTION make_coffee RETURNS string SONAME "myfuncs.so";
# Create a table using the resultss from another query
CREATE TABLE Stadium
SELECT stadiumName, stadiumLocation
FROM City;

DELETE [LOW_PRIORITY | QUICK]
FROM table [WHERE clause] [ORDER BY column, ...]
[LIMIT n]
DELETE [LOW_PRIORITY | QUICK]
table1[.*], table2[.*], ..., tablen[.*]
FROM tablex, tabley, ..., tablez [WHERE clause]
DELETE [LOW_PRIORITY | QUICK]
FROM table1[.*], table2[.*], ..., tablen[.*]
USING references[WHERE clause]

Deletes rows from a table. When used without a WHERE clause, this will erase the entire table and recreate it as an empty table. With a WHERE clause, it will delete the rows that match the condition of the clause. This statement returns the number of rows deleted.

In versions prior to MySQL 4, omitting the WHERE clause will erase this entire table. This is done by using an efficient method that is much faster than deleting each row individually. When using this method, MySQL returns 0 to the user because it has no way of knowing how many rows it deleted. In the current design, this method simply deletes all the files associated with the table except for the file that contains the actual table definition. Therefore, this is a handy method of zeroing out tables with unrecoverably corrupt data files. You will lose the data, but the table structure will still be in place. If you really wish to get a full count of all deleted tables, use a WHERE clause with an expression that always evaluates to true:

DELETE FROM TBL WHERE 1 = 1;

The LOW_PRIORITY modifier causes MySQL to wait until no clients are reading from the table before executing the delete. For MyISAM tables, QUICK causes the table handler to suspend the merging of indexes during the DELETE, to enhance the speed of the DELETE.

The LIMIT clause establishes the maximum number of rows that will be deleted in a single shot.

When deleting from MyISAM tables, MySQL simply deletes references in a linked list to the space formerly occupied by the deleted rows. The space itself is not returned to the operating system. Future inserts will eventually occupy the deleted space. If, however, you need the space immediately, run the OPTIMIZE TABLE statement or use the myisamchk utility.

The second two syntaxes are new multi-table DELETE statements that enable the deletion of rows from multiple tables. The first is new as of MySQL 4.0.0, and the second was introduced in MySQL 4.0.2.

In the first multi-table DELETE syntax, the FROM clause does not name the tables from which the DELETEs occur. Instead, the objects of the DELETE command are the tables from which the deletes should occur. The FROM clause in this syntax works like a FROM clause in a SELECT in that it names all of the tables that appear either as objects of the DELETE or in the WHERE clause.

I recommend the second multi-table DELETE syntax because it avoids confusion with the single table DELETE. In other words, it deletes rows from the tables specified in the FROM clause. The USING clause describes all the referenced tables in the FROM and WHERE clauses. The following two DELETEs do the exact same thing. Specifically, they delete all records from the emp_data and emp_review tables for employees in a specific department.

DELETE emp_data, emp_review
FROM emp_data, emp_review, dept
WHERE dept.id = emp_data.dept_id
AND emp_data.id = emp_review.emp_id
AND dept.id = 32;
DELETE FROM emp_data, emp_review
USING emp_data, emp_review, dept
WHERE dept.id = emp_data.dept_id
AND emp_data.id = emp_review.emp_id
AND dept.id = 32;

You must have DELETE privileges on a database to use the DELETE statement.

Examples

# Erase all of the data (but not the table itself) 
for the table 'olddata'.
DELETE FROM olddata
# Erase all records in the 'sales' table where the 'syear' 
field is '1995'.
DELETE FROM sales WHERE syear=1995

DESCRIBE table [column]
DESC table [column]

Gives information about a table or column. While this statement works as advertised, its functionality is available (along with much more) in the SHOW statement. This statement is included solely for compatibility with Oracle SQL. The optional column name can contain SQL wildcards, in which case information will be displayed for all matching columns.

Example

# Describe the layout of the table 'messy'
DESCRIBE messy
# Show the information about any columns starting 
# with 'my_' in the 'big' table.
# Remember: '_' is a wildcard, too, so it must be 
# escaped to be used literally.
DESC big my\_%

Synonym for DESCRIBE.

DROP DATABASE [IF EXISTS] name

Permanently remove a database from MySQL. Once you execute this statement, none of the tables or data that made up the database are available. All support files for the database are deleted from the filesystem. The number of files deleted will be returned to the user. This statement is equivalent to running the mysqladmindrop utility. As with running mysqladmin, you must be the administrative user for MySQL (usually root or mysql) to perform this statement. You may use the IF EXISTS clause to prevent any error message that would result from an attempt to drop a nonexistent table.

DROP FUNCTION name

Will remove a user-defined function from the running MySQL server process. This does not actually delete the library file containing the function. You may add the function again at any time using the CREATE FUNCTION statement. In the current implementation, DROP FUNCTION simply removes the function from the function table within the MySQL database. This table keeps track of all active functions.

DROP INDEX idx_name ON tbl_name

Provides compatibility with other SQL implementations. In older versions of MySQL, this statement does nothing. As of 3.22, this statement is equivalent to ALTER TABLE ... DROP INDEX. To perform the DROP INDEX statement, you must have SELECT, INSERT, DELETE, UPDATE, CREATE, and DROP privileges for the table in question.

DROP TABLE [IF EXISTS] name [, name2, ...]
[RESTRICT | CASCADE]

Will erase an entire table permanently. In the current implementation, MySQL simply deletes the files associated with the table. As of 3.22, you may specify IF EXISTS to make MySQL not return an error if you attempt to remove a table that does not exist. The RESTRICT and CASCADE keywords do nothing; they exist solely for ANSI compatibility. You must have DELETE privileges on the table to use this statement.

EXPLAIN [table_name | sql_statement]

Used with a table name, this command is an alias for SHOW COLUMNS FROM table_name.

Used with an SQL statement, this command displays verbose information about the order and structure of a SELECT statement. This can be used to see where keys are not being used efficiently. This information is returned as a result set with the following columns:

table

The name of the table referenced by the result set row explaining the query.

type

The type of join that will be performed.

possible_keys

Indicates which indexes MySQL could use to build the join. If this column is empty, there are no relevant indexes and you should probably build some to enhance performance.

key

Indicates which index MySQL decided to use.

key_len

Provides the length of the key MySQL decided to use for the join.

ref

Describes which columns or constants were used with the key to build the join.

rows

Indicates the number of rows MySQL estimates it will need to examine to perform the query.

Extra

Additional information indicating how MySQL will perform the query.

Example

EXPLAIN SELECT customer.name, product.name FROM customer, 
product, purchases 
WHERE purchases.customer=customer.id AND purchases.
product=product.id

FLUSH option[, option...]

Flushes or resets various internal processes depending on the options given. You must have RELOAD privileges to execute this statement. The option can be any of the following:

DES_KEY_FILE

Reloads the DES keys from the file originally specified with the --des-key-file option.

HOSTS

Empties the cache table that stores hostname information for clients. This should be used if a client changes IP addresses, or if there are errors related to connecting to the host.

LOGS

Closes all the standard log files and reopens them. This can be used if a log file has changed its inode number. If no specific extension has been given to the update log, a new update log will be opened with the extension incremented by one.

PRIVILEGES

Reloads all the internal MySQL permissions grant tables. This must be run for any changes to the tables to take effect unless those changes occurred through a GRANT/REVOKE statement.

QUERY CACHE

For better memory use, this command defragments the query cache but it does not delete queries from the cache.

STATUS

Resets the status variables that keep track of the current state of the server.

TABLE table

TABLES table, table2, . .., tablen

Flushes only the specified tables.

TABLES [WITH READ LOCK]

Closes all currently open tables and flushes any cached data to disk. With a read lock, it acquires a read lock that will not be released until UNLOCK TABLES is issued. Read locks are ineffective with InnoDB tables.

GRANT privilege
[ (column, ...) ] [, privilege [( column, ...) ] ...]
ON {table} TO user [IDENTIFIED BY 'password']
[, user [IDENTIFIED BY 'password'] ...]
[REQUIRE [{SSL | X509 |
CIPHER cipher [AND]
[ISSUER issuer [AND]]
[SUBJECT subject]]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR limit]]

In versions prior to MySQL 3.22.11, the GRANT statement was recognized but did nothing. In current versions, GRANT is functional. This statement enables access rights to a user (or users). Access can be granted per database, table or individual column. The table can be given as a table within the current database; use * to affect all tables within the current database, *.* to affect all tables within all databases or database.* to affect all tables within the given database.

The following privileges are currently supported:

ALL PRIVILEGES/ALL

Assigns all privileges except FILE, PROCESS, RELOAD, and SHUTDOWN.

ALTER

To alter the structure of tables.