|
|
SQL Statement SyntaxThis 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 permissions for accessing the database db_name. ALTER DATABASEALTER DATABASE [db_name] db_attr [, db_attr] ... This statement changes database attributes. The db_attr database attribute values that may be specified are the same as those listed in the entry for CREATE DATABASE. ALTER DATABASE requires the ALTER privilege for the database. The database name, db_name, is mandatory before MySQL 4.1.8. As of MySQL 4.1.8, the database name can be omitted, in which case the statement applies to the default database. If there is no default database, an error occurs. 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, and then specify 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 altered 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 alteration actions separated by commas. Each action is performed in turn. An action may be any of the following:
ALTER VIEW
ALTER
[ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name
[(col_name [, col_name] ...)] AS
select_stmt
[WITH [CASCADED | LOCAL] CHECK OPTION]
Alters an existing view to have the given definition. The meaning of the ALGORITHM clause and the clauses following the view name are described in the entry for CREATE VIEW. ANALYZE TABLE
ANALYZE
[LOCAL | NO_WRITE_TO_BINLOG]
{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, InnoDB, 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. If binary logging is enabled, MySQL writes the ANALYZE TABLE statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given. BACKUP TABLE
BACKUP {TABLE | TABLES} tbl_name [, tbl_name] ... TO 'dir_name'
BACKUP TABLE creates table backups that can be restored using RESTORE TABLE. It 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 FILE privilege as well as the SELECT privilege for the tables to be copied. It copies the table format and data files (the .frm and .MYD files), which are the minimum required to restore the table. The files must not already exist. Index files are not copied, because RESTORE TABLE can re-create them as necessary from the format 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. This will of course cause each table to be unavailable for a longer time to other clients that want to update the tables. Ownership for the files created by BACKUP TABLE is assigned to the account that is used to run the server. The following statement backs up table t by creating files t.frm and t.MYD in the directory /var/mysql/bkup: BACKUP TABLE t TO '/var/mysql/bkup'; The mysqlhotcopy program can be used as an alternative to the BACKUP TABLE statement. BEGINBEGIN [WORK] This statement is a synonym for START TRANSACTION; see the entry for that statement. CACHE INDEXCACHE INDEX tbl_name [[INDEX | KEY] (index_name [, index_name] ...)] [, tbl_name [[INDEX | KEY] (index_name [, index_name] ...)]] IN cache_name Sets up an association between one or more MyISAM tables and the named key cache, which must already exist. You must have the INDEX privilege for each table named in the statement. The default key cache is named default. The table indexes can be loaded into the cache later with LOAD INDEX. Currently, the statement associates all indexes in each table with the cache, even though the syntax allows for designating only certain indexes. Individual-index cache association remains for future implementation. The following statement caches indexes for the member statement in the key cache named member_cache: CACHE INDEX member IN member_cache; CACHE INDEX produces output in the format described under the entry for CHECK TABLE. MyISAM key cache management is discussed further in Chapter 11, "General MySQL Administration." This statement was introduced in MySQL 4.1.1. CHANGE MASTER
CHANGE MASTER TO master_defs
Changes replication parameters for a slave server, to indicate which master host to use, how to connect to it, or which logs to use. The parameters are saved in the slave's master.info and relay-log.info files, which are used for subsequent slave restarts. master_defs is a comma-separated list of one or more parameter definitions in param = value format. The allowable definitions are as follows:
Parameters that are not specified in the statement maintain their current values, with the following exception: Changes to MASTER_HOST or MASTER_PORT normally indicate that you're switching to a different master server, so in those cases, the MASTER_LOG_FILE and MASTER_LOG_POS values are set to the beginning of the master's first binary log file. You should not mix the MASTER_LOG_FILE and MASTER_LOG_POS options with the RELAY_LOG_FILE and RELAY_LOG_POS options in the same statement. The CHANGE MASTER statement deletes any existing relay log files and begins a new one unless the RELAY_LOG_FILE or RELAY_LOG_POS options are specified. CHECK TABLE
CHECK {TABLE | TABLES} tbl_name [, tbl_name] ... [options]
This statement checks tables for errors. It works with MyISAM and InnoDB tables, and requires the SELECT privilege on each table. As of MySQL 5.0.2, CHECK TABLE also can be used to check views for problems. options, if given, is a list naming one or more of the following options (not separated by commas). These options apply to MyISAM tables and are ignored for InnoDB tables and views.
If you don't specify one of QUICK, MEDIUM, or EXTENDED when checking a MyISAM table, CHECK TABLE defaults to MEDIUM if the table has variable-length rows. If it has fixed-length rows, the default is QUICK if you specify CHANGED or FAST, and MEDIUM otherwise. 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, CACHE INDEX, LOAD INDEX INTO CACHE, 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 carried out by the statement. The Msg_type and Msg_text columns provide information about the result of the operation. CHECKSUM TABLE
CHECKSUM {TABLE | TABLES} tbl_name [, tbl_name] ...
[QUICK | EXTENDED]
Reports a table checksum. By default, the statement reports the live checksum if the storage engine supports it. (A live checksum is one that is updated each time the table is modified.) For MyISAM tables, you can turn on live checksumming for a table by using the CHECKSUM = 1 option with CREATE TABLE or ALTER TABLE. With the QUICK option, the statement reports the live checksum if there is one and NULL otherwise. With the EXTENDED option, a checksum is calculated by reading the entire table and then reported. This operation becomes slower as the table size increases. CHECKSUM TABLE was introduced in MySQL 4.1.1. COMMITCOMMIT Commits changes made by statements in the current transaction, to record those changes permanently in the database. COMMIT works only for transaction-safe storage engines. (For non-transactional storage engines, statements are committed as they are executed.) COMMIT has no effect if autocommit mode has not been disabled with START trANSACTION or by setting the autocommit variable 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 START TRANSACTION TRUNCATE TABLE UNLOCK TABLES (if tables currently are locked) CREATE DATABASECREATE DATABASE [IF NOT EXISTS] db_name [db_attr] ... db_attr: [[DEFAULT] CHARACTER SET charset] | [[DEFAULT] COLLATE collation] Creates a database with the given name. The statement fails if you don't have the CREATE privilege for the database. 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. The optional CHARACTER SET and COLLATE attributes may be given after the database name to specify a default character set and collation for the database. These attributes are used for tables for which no character set or collation is given explicitly. charset can be a character set name, or DEFAULT to use the current server character set. collation can be a collation name, or DEFAULT to use the current server collation. If neither attribute is given, the server character set and collation are used. If CHARACTER SET is given without COLLATE, the default collation for the character set is used. If COLLATE is given without CHARACTER SET, the character set is determined from the collation. If both CHARACTER SET and COLLATE are used, the collation must be compatible with the character set. Database attributes are stored in the db.opt file in the database directory. CREATE FUNCTIONCREATE [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. CREATE FUNCTION also can be used to create a stored function. See "Stored Routine Syntax." 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 is 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(). On many systems, the UDF mechanism requires dynamic linking. In such cases, CREATE FUNCTION requires that the server be built as a dynamically linked binary (not as a static binary). For instructions on writing user-defined functions, refer to the MySQL Reference Manual. CREATE INDEXCREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING = index_type] ON tbl_name (index_columns) Adds an index named index_name to the table tbl_name. This statement is handled internally as an ALTER TABLE statement. See the entry for ALTER TABLE for details. The UNIQUE, FULLTEXT, or SPATIAL keywords can be given to indicate a specific kind of index. If none are given, a non-unique index is created. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. The USING clause can be given to specify the indexing algorithm. The types are described in the entry for CREATE TABLE. TYPE is a synonym for USING. If you want to create several indexes on a table, it's preferable to use ALTER TABLE; you can add all the indexes with a single statement, which is faster than adding them individually. CREATE TABLECREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { (create_definition,...) [table_options] [trailing_select] | [table_options] trailing_select | LIKE tbl_name2 | (LIKE tbl_name2) } table_options: (see following discussion) trailing_select: [IGNORE | REPLACE] [AS] select_stmt create_definition: { col_definition [reference_definition] | [CONSTRAINT [constraint_name]] PRIMARY KEY [index_name] [USING = index_type] (index_columns) | [CONSTRAINT [constraint_name]] UNIQUE [INDEX | KEY] [index_name] [USING = index_type] (index_columns) | {INDEX | KEY} [index_name] [USING = index_type] (index_columns) | FULLTEXT [INDEX | KEY] [index_name] (index_columns) | SPATIAL [INDEX | KEY] [index_name] (index_columns) | [CONSTRAINT [constraint_name]] FOREIGN KEY [index_name] (index_columns) [reference_definition] | CONSTRAINT [constraint_name] [CHECK (expr)] | [CONSTRAINT [constraint_name]] CHECK (expr) } col_definition: 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 | MATCH SIMPLE] reference_action: {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT} The CREATE TABLE statement creates a new table named tbl_name in the default database. If the name is specified as db_name.tbl_name, the table is created in the named database. The statement fails if you don't have the CREATE privilege for the table. 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. During its existence, it hides from that client any non-TEMPORARY table that has the same name. 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 and no error occurs. Second, if TEMPORARY is specified and the original table is not a temporary table, the new temporary table is created, and 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 to the current client 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_stmt is specified (in the form of an arbitrary SELECT statement), the table is created using the result set that it returns. These clauses are described more fully in the following sections. Column and index definitions. A create_definition may be a column or index definition, a FOREIGN KEY clause, or a CHECK clause. CHECK is parsed but ignored. FOREIGN KEY is treated similarly, except for InnoDB tables. A column definition col_definition 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 data types listed in Appendix B, "Data Type Reference." See that appendix for type-specific attributes that apply to the columns you want to define. Other optional keywords that may follow the data type are as follows:
The PRIMARY KEY, UNIQUE, INDEX, KEY, FULLTEXT, and SPATIAL 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. If the index name index_name is not specified, MySQL chooses a name automatically based on the name of the first indexed column. The index is created for the columns named in index_columns, each of which must be a column in tbl_name. If multiple columns are named in the index definition, they should be separated by commas. For CHAR, VARCHAR, BINARY, VARBINARY, TEXT, and BLOB columns, you can index a prefix of the column, using col_name(n) syntax to specify a prefix length of n, where n is from 1 to 255. (1 to 1000 for MyISAM and InnoDB as of MySQL 4.1.2.) The prefix length is in bytes for binary string columns and characters for non-binary string columns. For BLOB and TEXT columns, you must specify a prefix value. Prefixes for columns named in a FULLTEXT index are ignored if given. FULLTEXT and SPATIAL indexes are allowed only for MyISAM tables. FULLTEXT indexes are allowed only for non-binary string columns (CHAR, VARCHAR, TEXT). PRIMARY KEY columns must always be defined NOT NULL; MySQL adds NOT NULL to the definition of such columns if you omit it. For index definitions that allow a USING clause, the index_type value specifies an indexing algorithm. This can be BTREE for MyISAM and InnoDB tables, and either HASH or BTREE for MEMORY tables. TYPE is a synonym for USING. Table options. The table_options clause specifies additional characteristics of the table. It may include one or more of the options in the following list. Each specifier applies to all storage engines unless otherwise noted. Before MySQL 4.1, the = in each option setting is mandatory and settings should be separated by whitespace. As of MySQL 4.1, the = is optional and settings can be separated by whitespace or commas.
Trailing LIKE clause. If a trailing LIKE tbl_name2 clause is given, the table is created as an empty copy of tbl_name2. The copy will include the same column definitions, index definitions, and table options, with these exceptions: The DATA DIRECTORY and INDEX DIRECTORY table options are not copied, nor are foreign key definitions. Trailing SELECT statement. If a select_stmt clause is specified (as a trailing SELECT statement), the table is created using the contents of the result set returned by the statement. Rows that duplicate values in a unique index are either ignored or 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 storage engine 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 default actions are to prevent deletes or updates to the parent or child tables that would compromise referential integrity. The RESTRICT and NO ACTION actions are the same as specifying no action. The ON DEFAULT and ON UPDATE clauses may be given to specify explicit actions. The actions that InnoDB implements are CASCADE (delete or update the corresponding child table records) and SET NULL (set the foreign key columns in the corresponding child table records to NULL). The SET DEFAULT action is not implemented and InnoDB issues an error. MATCH clauses in REFERENCE definitions are parsed but ignored. If you specify a foreign key definition for a storage engine other than InnoDB, the entire definition is ignored. Further discussion of foreign keys is given in "Foreign Keys and Referential Integrity," in Chapter 2. Examples: 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 MEMORY table for greater speed:
CREATE TEMPORARY TABLE tmp_table
(id MEDIUMINT NOT NULL UNIQUE, name CHAR(40))
ENGINE = MEMORY;
Create a table as an empty copy of another table: CREATE TABLE prez_copy LIKE president; 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 column definitions are specified for a table created and populated by means of a trailing SELECT statement, the definitions are applied after the table contents have been inserted into the table. For example, you can define 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; You can specify definitions 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 UNSIGNED NOT NULL AUTO_INCREMENT, b DATE, PRIMARY KEY (a))
SELECT a, b, c FROM old_tbl;
CREATE TRIGGERCREATE TRIGGER trigger_name action event ON tbl_name FOR EACH ROW trigger_stmt Associates a trigger with a table, causing the given action to be activated when a given event occurs for the table. When this happens, the triggered statement is executed. The trigger name can be given in tbl_name format to name a table in the current database, or db_name.tbl_name format to name a table in a specific database. The trigger action is either BEFORE or AFTER, indicating that the triggered statement should be executed before or after each row processed by the statement that caused the trigger to be activated. The trigger event should be INSERT, UPDATE, or DELETE to indicate what kind of statement causes trigger activation. The triggered statement, trigger_stmt, can be a simple SQL statement such as SET. You also can use the BEGIN … END compound statement construct, in which case the triggered statement can be written using the statements that are available within the body of a stored function. (See "Stored Routine Syntax.") The syntax OLD.col_name can be used to refer to columns in the old row to be deleted or updated in a DELETE or UPDATE trigger. Similarly, NEW.col_name can be used to refer to columns in the new row to be inserted or updated in an INSERT or UPDATE trigger. OLD and NEW are not case sensitive. In a BEFORE trigger, you can change the values in the new row by using a SET statement: SET NEW.col_name = value To access a column with NEW, you must have the SELECT privilege. To change the value, you must have the UPDATE privilege. Current limitations on triggers in MySQL: You cannot create a trigger on a TEMPOARY table or a view. In the body of a trigger, you cannot use statements that refer to tables, you cannot use the CALL statement, and you cannot begin or end transactions. CREATE TRIGGER was introduced in MySQL 5.0.2. It requires the SUPER privilege. CREATE USERCREATE USER account [IDENTIFIED BY [PASSWORD] 'password'] [, account [IDENTIFIED BY [PASSWORD] 'password'] ] ... Creates one or more MySQL accounts. For each account, a record is created in the mysql.user table with no privileges. It is an error if the account already exists. Specify each account name in the same 'user_name'@'host_name' format that is used for the GRANT statement. An optional password may be given for each account. This statement was introduced in MySQL 5.0.2. It requires the GRANT OPTION privilege for the mysql database. CREATE VIEW
CREATE [OR REPLACE]
[ALGORITHM = { MERGE | TEMPTABLE | UNDEFINED}]
VIEW view_name [(col_list)] AS select_stmt
[WITH [CASCADED | LOCAL] CHECK OPTION]
Creates a view. If a view with the same name already exists, an error occurs if the OR REPLACE clause is given (in which case, the new view replaces the old one). The ALGORITHM clause determines how the view is processed. For MERGE, when you issue a statement that references the view, the view definition is merged into the statement. The resulting statement is executed. For TEMPTABLE, temporary tables are used during the course of executing the view. For UNDEFINED, the server chooses which algorithm to use. The default is UNDEFINED. column_list, if present, provides names for the columns returned by the view. There must be one name for each column. If no column_list is given, the column names come from the SELECT statement in the view definition. select_stmt is a SELECT statement that defines the view. It can refer to tables or other views. The WITH CHECK OPTION clause applies to updatable views. It allows use of the view to insert or update only those rows in the underlying table for which the WHERE clause in the SELECT statement is true. The CASCADED and LOCAL keywords apply in the case that the view definition refers to other views. With CASCADED, checks cascade to underlying views. With LOCAL, checks are restricted to the current view. The default is CASCADED if neither is given. The CREATE VIEW statement was introduced in MySQL 5.0.1. The WITH CHECK OPTION clause was introduced in MySQL 5.0.2. DEALLOCATE PREPARE
{DEALLOCATE | DROP} PREPARE stmt_name
Deallocates a prepared statement named stmt_name that previously was prepared with PREPARE. After the statement has been deallocated, you should not attempt to execute it again. DEALLOCATE PREPARE was introduced in MySQL 4.1.3. DELETEDELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_expr] [ORDER BY ...] [LIMIT n] DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name [, tbl_name] ... FROM tbl_name [, tbl_name] ... [WHERE where_expr] DELETE [LOW_PRIORITY] [QUICK] [IGNORE] 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 second and third forms can delete rows from multiple tables, or delete rows based on conditions that involve multiple tables. 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. For MyISAM tables, specifying QUICK may make the statement quicker; the MyISAM storage engine will not perform its usual index tree leaf merging. If the IGNORE modifier is given, errors that occur while records are being deleted are ignored. These errors generate warnings instead. IGNORE was introduced in MySQL 4.1.1. If the LIMIT clause is given, the value n specifies the maximum number of rows that will be deleted. 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 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 re-creating 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. Names in the list of tables from which rows are to be deleted may 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; Multiple-table DELETE statements do not allow ORDER BY or LIMIT clauses. Also, the WHERE clause cannot include a subquery that selects rows from a table from which rows are deleted. DESCRIBE
{DESCRIBE | DESC} tbl_name [col_name | 'pattern']
{DESCRIBE | DESC} select_stmt
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.) DODO 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');
DROP DATABASE
DROP DATABASE [IF EXISTS] db_name
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 DROP privilege for it. The IF EXISTS clause may be specified to suppress the error that normally results if the database does not exist. In this case, a warning is generated instead. A database is represented by a directory under the data directory. The server deletes only files and directories that it can identify as having been created by itself (for example, .frm files or RAID directories). It does not delete other files and directories. If you have put non-table files in that directory, those files are not deleted by the DROP DATABASE statement. This results in failure to remove the database directory and DROP DATABASE fails. In that case, the database will continue to be listed by SHOW DATABASES. To correct this problem, you can manually remove the database directory and any files within it. A successful DROP DATABASE returns a row count that indicates the number of tables dropped. (This actually is the number of .frm files removed, which amounts to the same thing.) DROP FUNCTION
DROP FUNCTION function_name
Removes a user-defined function that was previously loaded with CREATE FUNCTION. DROP FUNCTION also can be used to remove a stored function. See "Stored Routine Syntax." DROP INDEXDROP INDEX index_name ON tbl_name Removes the index index_name from the table tbl_name. This statement is handled internally as an ALTER TABLE DROP INDEX statement. See the entry for ALTER TABLE for details. To use DROP INDEX to drop a PRIMARY KEY, the index name is PRIMARY, which must be named as a delimited identifier:
DROP INDEX `PRIMARY` ON tbl_name;
DROP TABLE
DROP [TEMPORARY] {TABLE | TABLES} [IF EXISTS] tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
Removes the named table or tables from the database they belong to. If the TEMPORARY keyword is given, drops only TEMPORARY tables. The IF EXISTS clause may be specified to suppress the error that normally results if the table does not exist. In this case, a warning is generated instead. The RESTRICT and CASCADE keywords are parsed but ignored and have no effect. DROP TRIGGER
DROP TRIGGER tbl_name.trigger_name
Removes a trigger that is associated with the named table. It is necessary to include the table name. DROP TRIGGER was introduced in MySQL 5.0.2. It requires the SUPER privilege. DROP USERDROP USER account [, account] ... Prior to MySQL 5.0.2, the DROP USER statement removes accounts from which all privileges have been revoked. Such an account exists in the grant tables only as a record in the user table. This statement removes that record. From MySQL 5.0.2 on, DROP USER revokes the account's privileges and removes the account record. Specify each account name in the same 'user_name'@'host_name ' format that is used for the GRANT statement. It is an error if the account does not exist. DROP USER was introduced in MySQL 4.1.1. It requires the GRANT OPTION privilege for the mysql database. DROP VIEWDROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE] Removes the named view or views from the database to which they belong. You must have the DROP privilege for the view. The IF EXISTS clause may be specified to suppress the error that normally results if the view does not exist. In this case, a warning is generated instead. The RESTRICT and CASCADE keywords are parsed but ignored and have no effect. DROP VIEW was introduced in MySQL 5.0.1. EXECUTEEXECUTE stmt_name [USING @var_name [, @var_name] ...] Executes a prepared statement named stmt_name that was previously prepared with PREPARE. The USING clause must be given if the prepared statement contains any placeholder markers. The clause should provide a comma-separated list of user variables that contains the values for successive placeholders. EXECUTE was introduced in MySQL 4.1.3. EXPLAINEXPLAIN tbl_name [col_name | 'pattern'] EXPLAIN select_stmt 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, grade_event WHERE score.event_id = grade_event.event_id AND grade_event.event_id = 14; Output from EXPLAIN consists of one or more rows containing the following columns:
If you use EXPLAIN with a statement that includes a subquery, the subquery has to be executed. This occurs because the optimizer must know what the subquery returns so that it can determine the execution plan for the outer query. FLUSHFLUSH [LOCAL | NO_WRITE_TO_BINLOG] option [, option] ... Flushes various internal caches used by the server. Each option value should be one of the following items:
If binary logging is enabled, MySQL writes the FLUSH statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given. The FLUSH statement requires the RELOAD privilege. GRANTGRANT priv_type [(column_list)] [, priv_type [(column_list)] ] ... ON {*.* | * | db_name.* | db_name.tbl_name | tbl_name} TO account [IDENTIFIED BY [PASSWORD] 'password'] [, account [IDENTIFIED BY [PASSWORD] 'password'] ] ... [REQUIRE security_options] [WITH grant_or_resource_options] The GRANT statement grants access privileges to one or more MySQL users. To use this statement, you must have the GRANT OPTION privilege and you must possess the privileges that you are trying to grant. The priv_type value specifies the privileges to be granted. It consists of privilege types chosen from the following list. ALL is used by itself. For the other privileges, you may specify one or more of them as a comma-separated list. ALL signifies the combination of all the other privileges, except for GRANT OPTION, which must be granted separately or by adding a WITH GRANT OPTION clause.
The LOCK TABLES privilege can be exercised only over tables for which you also have the SELECT privilege, but it allows you to place any kind of lock, not just read locks. You can always view or kill your own threads. The PROCESS and SUPER privileges allow you to view and kill, respectively, threads that belong to any account, not just your own. The CREATE VIEW and SHOW VIEW privileges were introduced in MySQL 5.0.1. ALTER ROUTINE and CREATE ROUTINE were introduced in MySQL 5.0.3; they apply only to stored routines, not to user-defined functions (UDFs). MySQL 5.0.3 also is the version in which the EXECUTE privilege became operational. The ON clause specifies how widely the privileges should be granted, as shown in the following table:
When you use ALL as a privilege specifier, it grants only those privileges that are available at the level for which you are granting privileges. For example, RELOAD is only available as a global privilege, so it would be granted by GRANT ALL if you specify ON *.*, but not if you specify ON db_name.*. In the latter case, only these privileges that apply to databases would be granted. ALL also can be used only when granting global, database, or table privileges. For column privileges, you must list each privilege to be granted. When a table is named in the ON clause, privileges may be made column-specific by naming one or more comma-separated columns in the column_list clause. (This applies only for the INSERT, REFERENCES, SELECT, and UPDATE privileges, which are the only ones that may be granted on a column-specific basis.) To grant table or column privileges, the table must already exist. USAGE means "no privileges." It should be used only at the global level. GRANT OPTION applies to all privileges granted at a given level. For example, you cannot grant an account SELECT and INSERT for a given database, but make just one of them grantable by that account. The TO clause specifies one or more accounts to which the privileges should be granted. Each account name consists of a specifier in 'user_name'@'host_name' format and may be followed by an optional IDENTIFIED BY clause to specify a password. The user_name and host_name parts need not be quoted if they are values that can be used as unquoted identifiers. However, if quoted, they must be quoted separately. (For example, bill@%.com should be quoted as 'bill'@'%.com', not as 'bill@%.com'.) The user_name may be a name or an empty string (''); the latter specifies an anonymous user. host_name may be given as localhost, a hostname, an IP address, or a pattern matching a domain name or network number. The pattern characters are '%' and '_', with the same meaning as for the LIKE operator. It's also possible for host_name to be an IP number/netmask pair in n.n.n.n/m.m.m.m notation, where n.n.n.n indicates the IP address and m.m.m.m indicates the netmask to use for the network number. A user_name specified alone with no hostname is equivalent to 'user_name'@'%'. Database, table, and column names, if quoted, must be quoted using identifier quoting characters. Usernames and hostnames can be quoted using identifier or string quoting characters. For example: GRANT INSERT (`mycol`) ON `test`.`t` TO 'myuser'@'localhost'; The IDENTIFIED BY clause, if given, assigns a password to the user. Normally, you omit the PASSWORD keyword and specify the literal value of the password in plain text. Do not use the PASSWORD() function, in contrast to the way passwords are specified for the SET PASSWORD statement. In the special case that you want to specify the password hash value in the format returned by PASSWORD(), precede the value with the keyword PASSWORD. (This might be the case if you are using the output of SHOW GRANTS to re-create the privileges for an account. SHOW GRANTS displays the hashed password value, not the literal password.) If the account already exists and IDENTIFIED BY is specified, the new password replaces the old one. The existing password remains unchanged otherwise. The REQUIRE clause, if given, allows you to specify that secure connections are to be used and what kinds of information the client is required to supply. The REQUIRE keyword may be followed by:
The WITH clause, if given, can specify that the account is able to grant other accounts the privileges that it holds itself. It can also be used to place limits on the account's resource consumption. The allowable options are shown in the following list. You may specify more than one option; their order does not matter.
For MAX_CONNECTIONS_PER_HOUR, MAX_QUERIES_PER_HOUR, and MAX_UPDATES_PER_HOUR, a value of 0 means "no limit." For MAX_USER_CONNECTIONS, a value of 0 means that the value of the max_user_connections system variable applies. The following statements demonstrate some ways in which the GRANT statement can be used. See Chapter 11 for other examples. See Chapter 12, "MySQL and Security," for information on setting up secure connections using SSL. Create an account for paul who can access all tables in the sampdb database from any host. The following two statements are equivalent because omitting the hostname part of an account identifier is equivalent to specifying % as the hostname: GRANT ALL ON sampdb.* TO 'paul' IDENTIFIED BY 'secret'; GRANT ALL ON sampdb.* TO 'paul'@'%' IDENTIFIED BY 'secret'; Create an account with read-only privileges for the tables in the menagerie database. The lookonly user can connect from any host in the xyz.com domain:
GRANT SELECT ON menagerie.* TO 'lookonly'@'%.xyz.com'
IDENTIFIED BY 'ragweed';
Create an account with full privileges, but only for the member table in the sampdb database. The member_mgr user can connect from a single host:
GRANT ALL ON sampdb.member TO 'member_mgr'@'boa.snake.net'
IDENTIFIED BY 'doughnut';
Create a superuser who can do anything, including granting privileges to other users, but who must connect from the local host:
GRANT ALL ON *.* TO 'superduper'@'localhost' IDENTIFIED BY 'homer'
WITH GRANT OPTION;
Create an anonymous user of the menagerie database who can connect from the local host with no password: GRANT ALL ON menagerie.* TO ''@'localhost'; Create an account for a remote user who must connect via SSL and present a valid X509 certificate: GRANT ALL ON privatedb.* TO 'paranoid'@'%.mydom.com' IDENTIFIED BY 'keepout' REQUIRE X509; Create an account for a limited-access user who can issue only 100 statements per hour, of which at most 10 may be updates: GRANT ALL ON test.* TO 'caleb'@'localhost' IDENTIFIED BY 'rosepetal' WITH MAX_QUERIES_PER_HOUR 100 MAX_UPDATES_PER_HOUR 10; HANDLERHANDLER tbl_name OPEN [AS alias_name] HANDLER tbl_name READ {FIRST | NEXT} [where_clause] [limit_clause] HANDLER tbl_name READ index_name {FIRST | NEXT | PREV | LAST | < | <= | = | => | >} (expr_list) [where_clause] [limit_clause] HANDLER tbl_name CLOSE HANDLER provides a low-level interface to the MyISAM and InnoDB storage engines that bypasses the optimizer and accesses table contents directly. To access a table through the HANDLER interface, first use HANDLER … OPEN to open it. The table remains available for use until you issue a HANDLER … CLOSE statement to close it explicitly or until or the connection terminates. While the table is open, use HANDLER … READ to access the table's contents. HANDLER provides no protection against concurrent updates. It does not lock the table, so it's possible for the table to be modified while HANDLER has it open, and there is no guarantee that the modifications will be reflected in the records that you read from the table. INSERTINSERT [DELAYED | LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(column_list)] VALUES (expr [, expr] ...) [, (...)] ... [ON DUPLICATE KEY UPDATE col_name=expr ...] INSERT [DELAYED | LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(column_list)] {SELECT ... | (SELECT ...)} [ON DUPLICATE KEY UPDATE col_name=expr ...] INSERT [DELAYED | LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name=expr [, col_name=expr] ... [ON DUPLICATE KEY UPDATE col_name=expr ...] Inserts rows into an existing table tbl_name and returns the number of rows inserted. The INTO keyword is optional. The DELAYED, LOW_PRIORITY, and HIGH_PRIORITY options affect execution scheduling:
If inserting a record would result in a duplicate key value in a unique index, INSERT terminates in error and no more rows are inserted. Adding IGNORE causes such records not to be inserted and no error occurs. In strict mode, IGNORE also causes data conversion errors that otherwise would terminate the statement to be treated as non-fatal warnings. Columns are set to the nearest legal value in this case. The first form of INSERT requires a VALUES() list that specifies all values to be inserted. If no column_list is given, the VALUES() list must specify one value for each column in the table. If a column_list is given consisting of one or more comma-separated column names, one value per column must be specified in the VALUES() list. Columns not named in the column list are set to their default values. Multiple value lists may be specified, allowing multiple rows to be inserted using a single INSERT statement. The column_list and VALUES() list may be empty, which can be used as follows to create a record for which all columns are set to their default values: INSERT INTO t () VALUES(); The second form of INSERT retrieves records according to the SELECT statement and inserts them into tbl_name. The SELECT statement must select as many columns as are in tbl_name, or as many columns as are named in column_list if a column list is specified. When a column list is specified, any columns not named in the list are set to their default values. You cannot select records using a subquery from the same table into which you are inserting them. The third form of INSERT inserts columns named in the SET clause to the values given by the corresponding expressions. Columns not named are set to their default values.
INSERT INTO absence (student_id, date) VALUES(14,'1999-11-03'),(34,NOW());
INSERT INTO absence SET student_id = 14, date = '1999-11-03';
INSERT INTO absence SET student_id = 34, date = NOW();
INSERT INTO score (student_id, score, event_id)
SELECT student_id, 100 AS score, 15 AS event_id FROM student;
The word DEFAULT may be used in a VALUES() list or SET clause to set a column to its default value explicitly without knowing what the default value is. More generally, to refer to a column's default value in expressions, you can use DEFAULT(col_name). The following statement sets the column i to 0 if its default value is NULL and to 1 otherwise: INSERT INTO t SET i = IF(DEFAULT(i) IS NULL,1,0); In strict mode, it is an error to omit a column or to specify its value by using DEFAULT if the column has no DEFAULT clause in its definition. The ON DUPLICATE KEY UPDATE clause applies for rows that would result in a duplicate-key violation for a unique-valued index. With this clause, the INSERT is converted to an UPDATE that modifies the column of the existing row using the column assignments following the UPDATE keyword. If an update did occur, the rows-affected count returned by INSERT is 2 rather than 1. KILL
KILL [CONNECTION | QUERY] thread_id
Kills the server thread with the given thread_id. You must have the SUPER privilege to kill the thread, unless it is one of your own. The KILL statement allows only a single ID. The mysqladmin kill command performs the same operation, but allows multiple thread ID values to be specified on the command line. The CONNECTION and QUERY options were introduced in MySQL 5.0.0. CONNECTION has the same effect as no option: The thread with the given ID is terminated. QUERY terminates any statement that the thread is executing, but not the thread itself. LOAD DATALOAD DATA [LOW_PRIORITY | CONCURRENT ] [LOCAL] INFILE 'file_name' [IGNORE | REPLACE] INTO TABLE tbl_name [field_options] [line_options] [IGNORE n LINES] [(column_list)] LOAD DATA reads records from the file file_name and loads them in bulk into the table tbl_name. This is faster than using a set of INSERT statements. LOAD DATA returns an information string that has the following format: Records: n Deleted: n Skipped: n Warnings: n If the warning count is non-zero, use the SHOW WARNINGS statement to see what the problems were. LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table. CONCURRENT is used only for MyISAM tables. If the table has no holes in the middle, new rows are loaded at the end of the table. In this case, other clients can retrieve from the table concurrently while rows are being loaded. Without the LOCAL keyword, the file is read directly by the server on the server host. In this case, you must have the FILE privilege and the file must either be located in the database directory of the default database or be world-readable. If LOCAL is specified, the client reads the file on the client host and sends its contents over the network to the server. In this case, the FILE privilege is not required. LOCAL can be disabled or enabled selectively. If it is disabled on the server side, you cannot use it from the client side. If it is enabled on the server side, but disabled by default on the client side, you'll need to enable it explicitly. For example, with the mysql program, you can use the --local-infile flag to enable the LOCAL capability. When LOCAL is not specified in the LOAD DATA statement, the server locates the file as follows:
If LOCAL is given, filename interpretation is as follows:
For Windows, backslashes in filenames may be written either as slashes ('/') or as doubled backslashes ('\\'). Rows that duplicate values in a unique index are either ignored or replace existing rows according to whether IGNORE or REPLACE is specified. If neither is specified, an error occurs, and any remaining records are ignored. If LOCAL is specified, transmission of the file cannot be interrupted, so the default behavior is like that of IGNORE if neither duplicate-handling option is given. The field_options and line_options clauses indicates the format of the data. (The options available in these clauses also apply to the corresponding clauses of the SELECT … INTO OUTFILE statement.) The syntax for the two clauses is as follows: field_options: [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] line_options: [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] The 'string' and 'char' values may include the escape sequences in the following table to indicate special characters. The sequences should be given in the lettercase shown.
You can also use hexadecimal constants to indicate arbitrary characters. For example, LINES TERMINATED BY 0x02 indicates that lines are terminated by Ctrl-B (ASCII 2) characters. If FIELDS is given, at least one of the TERMINATED BY, ENCLOSED BY, or ESCAPED BY clauses must be given. If multiple clauses are present, they may appear in any order. Similarly, if LINES is given, at least one of the STARTING BY or TERMINATED BY clauses must be given, but if both are present, they may appear in any order. FIELDS must precede LINES if both are given. The parts of the FIELDS clause are used as follows:
The parts of the LINES clause are used as follows:
If neither FIELDS nor LINES is given, the defaults are as if you had specified them like this:
FIELDS
TERMINATED BY '\t'
ENCLOSED BY ''
ESCAPED BY '\\'
LINES
STARTING BY ''
TERMINATED BY '\n'
In other words, fields within a line are tab-delimited without being quoted, backslash is treated as the escape character, and lines are terminated by newline characters. If the TERMINATED BY and ENCLOSED BY values for the FIELDS clause are both empty, a fixed-width row format is used with no delimiters between fields. Column values are read (or written, for output) using the display widths of the columns. For example, VARCHAR(15) and MEDIUMINT(5) columns are read as 15-character and 5-character fields for input. For output, the columns are written using 15 characters and 5 characters. NULL values are written as strings of spaces. NULL values in an input data file are indicated by the unquoted sequence \N. If the FIELDS ENCLOSED BY character is not empty, all non-NULL input values must be quoted with the enclosed-by character and the unquoted word NULL also will be interpreted as a NULL value. If the IGNORE n LINES clause is given, the first n lines of the input are discarded. For example, if your data file has a row of column headers that you don't want to read into the database table, you can use IGNORE 1 LINES: LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl IGNORE 1 LINES; If no column_list is specified, input lines are assumed to contain one value per column in the table. If a list consisting of one or more comma-separated column names is given, input lines should contain a value for each named column. Columns not named in the list are set to their default values. If an input line is short of the expected number of values, columns for which values are missing are set to their default values. If you have a tab-delimited text file that you created on Windows, you can use the default column separator, but the lines are probably terminated by carriage return/newline pairs. To load the file, specify a different line terminator ('\r' indicates a carriage return, and '\n' indicates a newline):
LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
LINES TERMINATED BY '\r\n';
Unfortunately, for files created on Windows, you may end up with a malformed record in the database if the program that created the data file uses the odd MS-DOS convention of putting the Ctrl-Z character at the end of the file to indicate end-of-file. Either write the file using a program that doesn't do this, or delete the record after loading the file. Files in comma-separated values (CSV) format have commas between fields, and fields may be quoted with double quotes. Assuming lines have newlines at the end, the LOAD DATA statement to load such a file looks like this:
LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Hexadecimal notation is useful for specifying arbitrary control characters. The following statement reads a file for which fields are separated by Ctrl-A (ASCII 1) characters, and lines are terminated by Ctrl-B (ASCII 2) characters:
LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl
FIELDS TERMINATED BY 0x01 LINES TERMINATED BY 0x02;
LOAD ... FROM MASTER
LOAD DATA FROM MASTER
LOAD TABLE tbl_name FROM MASTER
These statements are used on replication slave servers to request data from the master server. LOAD DATA FROM MASTER requests all tables from the master. It also updates the slave's replication coordinates so that the slave replicates only updates on the master that were made after completion of the LOAD DATA operation. The tables to transfer are subject to any restrictions specified by any --replicate-xxx options the slave server may have been started with. The statement itself also has a number of constraints that must be satisfied:
If the preceding conditions are acceptable, this statement provides a convenient way to initialize a slave server. LOAD TABLE … FROM MASTER transfers a copy of the single named table from the master to the slave. This is used primarily for replication debugging. LOAD INDEX INTO CACHELOAD INDEX INTO CACHE tbl_name [[INDEX | KEY] (index_name [, index_name] ...) [IGNORE LEAVES]] [, tbl_name [[INDEX | KEY] (index_name [, index_name] ...)] [IGNORE LEAVES]] Loads indexes from each named MyISAM table into the key cache to which the table is assigned. This is the default key cache unless the table has been assigned to another cache with the CACHE INDEX statement. By default, all index blocks are loaded. The IGNORE LEAVES clause causes only non-leaf blocks in the index tree to be loaded. As with the CACHE INDEX statement, the syntax for LOAD INDEX INTO CACHE allows individual indexes to be specified, but the current implementation is such that all indexes for a table are loaded. You must have the INDEX privilege for each table named in the statement. LOAD INDEX INTO CACHE produces output in the format described under the entry for CHECK TABLE. MyISAM key cache management is discussed further in Chapter 11. The LOAD INDEX INTO statement was introduced in MySQL 4.1.1. LOCK TABLE
LOCK {TABLE | TABLES}
tbl_name [AS alias_name] lock_type
[, tbl_name [AS alias_name] lock_type] ...
Obtains a lock on the named tables, waiting if necessary until all locks are acquired. Each lock_type value must be one of the following:
LOCK TABLE releases any existing locks that you currently hold. Thus, to lock multiple tables, you must lock them all using a single LOCK TABLE statement. Any locks that are held by a client when it terminates are released automatically. While you have acquired locks with LOCK TABLE, you cannot refer to any not-locked tables. LOCK TABLE allows an alias to be specified so that you can lock a table under an alias that you are going to use when referring to the table in a subsequent query. If you refer to a table multiple times in a query, you must obtain a lock for each instance of the table, locking aliases as necessary. You must request all the locks in the same statement. LOCK TABLES student READ, score WRITE, grade_event READ; LOCK TABLE member READ; LOCK TABLES t AS t1 READ, t AS t2 READ; Table locks acquired with LOCK TABLE are released implicitly if you start a transaction with START TRANSACTION. OPTIMIZE TABLE
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG]
{TABLE | TABLES} tbl_name [, tbl_name] ...
DELETE, REPLACE, and UPDATE statements may result in areas of unused space in a table, particularly for tables that have variable-length rows. To counter this, OPTIMIZE TABLE performs the following actions for MyISAM tables:
Issuing an OPTIMIZE TABLE statement is like executing myisamchk with the --check-only-changed, --quick, --sort-index, and --analyze options. However, with myisamchk, you must arrange to prevent the server from accessing the table at the same time. With OPTIMIZE TABLE, the server does the work, and takes care of making sure that other clients do not modify the table while it's being optimized. For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE to rebuild the index as of MySQL 4.1.3, and to ANALYZE TABLE before that. For BDB tables, OPTIMIZE TABLE is mapped to ANALYZE TABLE. OPTIMIZE TABLE requires SELECT and INSERT privileges on each table. If binary logging is enabled, MySQL writes the OPTIMIZE TABLE statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given. OPTIMIZE TABLE produces output in the format described under the entry for CHECK TABLE. PREPAREPREPARE stmt_name FROM {'string' | @var_name} Prepares a statement and assigns it the name stmt_name. The statement can be executed later with EXECUTE and deallocated with DEALLOCATE PREPARE. If there was already a previously prepared statement that has the given name, the previous statement is deallocated before the new statement is prepared. Statement names are not case sensitive. The statement to be prepared can be given either as a string literal or a user variable. Allowable statements to be used with PREPARE are CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most variations of SHOW. Other statements cannot be prepared, including PREPARE, EXECUTE, and DEALLOCATE PREPARE. The prepared statement can contain '?' characters that serve as placeholder markers. Later when you execute the statement, you supply data values to be bound to the placeholders. This allows you to parameterize the statement so that you can use the same prepared statement but execute it with different values from execution to execution. PREPARE was introduced in MySQL 4.1.3. PREPARE, EXECUTE, and DEALLOCATE provide an SQL-level interface to prepared statements. They are not the same as or as efficient as the binary API for prepared statements that is discussed in Chapter 6, "Writing MySQL Programs Using C," and Appendix G, "C API Reference." PURGE MASTER LOGS
PURGE {MASTER | BINARY} LOGS {TO 'log_name' | BEFORE 'date'}
Deletes all the binary logs on the server that were generated earlier than the named log file or before the given date, and resets the binary log index file to list only those logs that remain. Normally, you use this after running SHOW SLAVE STATUS on each of the master's slaves to determine which log files are still in use. This statement requires the SUPER privilege. The following statement removes binlog.000001 through binlog.000009 (or whichever of them exist), and causes binlog.000010 to become the first of the remaining log files: PURGE MASTER LOGS TO 'binlog.000010'; RENAME TABLE
RENAME {TABLE | TABLES} tbl_name TO new_tbl_name
[, tbl_name TO new_tbl_name] ...
Renames one or more tables. RENAME TABLE is similar to ALTER TABLE … RENAME, except that it can rename multiple tables at once, and locks them all during the rename operation. This is advantageous if you need to prevent any of the tables from being accessed during the operation. If you rename an InnoDB table on which other tables depend for foreign key relationships, InnoDB adjusts the dependencies to point to the renamed table. If you rename a MyISAM table that is part of a MERGE table, you must redefine the MERGE table accordingly. RENAME TABLE cannot be used for TEMPORARY tables. RENAME USERRENAME USER from_account TO to_account [, from_account TO to_account] ... Renames one or more MySQL accounts. Each from_account is renamed to the corresponding to_account. An error occurs if from_account does not exist or if to_account already exists. Specify each account name in the same 'user_name'@'host_name' format that is used for the GRANT statement. This statement was introduced in MySQL 5.0.2. It requires the GRANT OPTION privilege for the mysql database. REPAIR TABLE
REPAIR [LOCAL | NO_WRITE_TO_BINLOG]
{TABLE | TABLES} tbl_name [, tbl_name] ... [options]
This statement performs table repair operations. It works only for MyISAM tables, and requires SELECT and INSERT privileges on each table. options, if given, is a list naming one or more of the following options (not separated by commas):
REPAIR TABLE with no options performs a table repair option like that done by myisamchk --recover. If binary logging is enabled, MySQL writes the REPAIR TABLE statement to the binary log unless the LOCAL or NO_WRITE_TO_BINLOG option is given. REPAIR TABLE produces output in the format described under the entry for CHECK TABLE. REPLACEREPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(column_list)] VALUES (expr [, expr] ...) [, (...)] ... REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(column_list)] {SELECT ... | (SELECT ...)} REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name=expr [, col_name=expr] ... The basic action of REPLACE statement is like that of INSERT, with the exception that if a row to be inserted has a value for a unique index that duplicates the value in a row already present in the table, the old row is deleted before the new one is inserted. For this reason, there is no IGNORE clause option in the syntax of REPLACE. See the INSERT entry for more information. Also, REPLACE has no support for ON DUPLICATE KEY UPDATE. It's possible for a REPLACE to delete more than one row if the table contains multiple unique indexes. This can happen if a new row matches values in several of the unique indexes, in which case all the matching rows are deleted before the new row is inserted. REPLACE requires the INSERT and DELETE privileges. RESETRESET option [, option] ... The RESET statement is similar to FLUSH in that it affects log or cache information. (In fact, RESET began life as part of the FLUSH statement.) Each option value should be one of the following items:
RESET requires the RELOAD privilege. RESTORE TABLE
RESTORE {TABLE | TABLES} tbl_name [, tbl_name] ... FROM 'dir_name'
RESTORE TABLE is the complement to BACKUP TABLE. It restores the named table or tables using files located in the backup directory that were created with BACKUP TABLE. 'dir_name' should be the full pathname to the directory on the server host that contains the backup files. The tables to be restored must not already exist. RESTORE TABLE works only for MyISAM tables and requires the FILE privilege as well as the INSERT privilege for the tables to be restored. The restore operation for each table uses only the table format and data files (the .frm and .MYD files). Indexes are rebuilt using the information contained in those two files. REVOKEREVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {*.* | * | db_name.* | db_name.tbl_name | tbl_name} FROM account [, account ] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROM account [, account ] ... The REVOKE statement revokes privileges from the named account or accounts. In the first syntax, the priv_type, column_list, and account clauses are specified the same way as for the GRANT statement. The same kind of specifiers as for GRANT are allowed in the ON clause as well. To use this statement, you must have the GRANT OPTION privilege and you must possess the privileges that you are trying to revoke. The second syntax has a fixed privilege list and no ON clause. It revokes all privileges held by each of the named accounts. The second syntax was introduced in MySQL 4.1.2. It requires the GRANT OPTION privilege for the mysql database. REVOKE does not remove an account's record from the user grant table. This means that the account still can be used to connect to the MySQL server even when all its privileges have been revoked. To remove the account entirely, use the DROP USER statement (or manually delete the account record from the user table). Revoke privileges that allow the member_mgr user to modify the member table in the sampdb database:
REVOKE INSERT,DELETE,UPDATE ON sampdb.member
FROM 'member_mgr'@'boa.snake.net';
Revoke all privileges for a single table in the menagerie database from the anonymous user on the local host: REVOKE ALL ON menagerie.pet FROM ''@'localhost'; Note that ALL revokes all but the GRANT OPTION privilege. To revoke that privilege as well, you must do so explicitly: REVOKE GRANT OPTION ON menagerie.pet FROM ''@'localhost'; Revoke all privileges held at all levels by superduper@localhost: REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'superduper'@'localhost'; ROLLBACK
ROLLBACK [TO SAVEPOINT savepoint_name]
Rolls back changes made by statements that are part of the current transaction so that those changes are forgotten. This works only for transaction-safe storage engines. (For non-transactional storage engines, statements are committed as they are executed and thus cannot be rolled back.) If the TO SAVEPOINT clause is given, the statement rolls back the current transaction only to the named savepoint. This clause was introduced in MySQL 4.1.1 and works for InnoDB transactions. ROLLBACK does nothing if autocommit mode has not been disabled with START TRANSACTION or by setting the autocommit variable to 0. SAVEPOINT
SAVEPOINT savepoint_name
Creates a transaction savepoint with the given name. Any savepoint that exists with the given name is deleted. Statements executed later within the current transaction can be rolled back to the savepoint with the ROLLBACK TO SAVEPOINT statement. SAVEPOINT was introduced in MySQL 4.1.1. SELECTSELECT [select_options] select_expr [, select_expr] ... [ INTO OUTFILE 'file_name' [field_options] [line_options] | INTO DUMPFILE 'file_name' | INTO var_name [, var_name ] ... ] [FROM tbl_list [WHERE where_expr] [GROUP BY {col_name | position | expr} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_expr] [ORDER BY {col_name | position | expr} [ASC | DESC], ...] [LIMIT [skip_count,] show_count] [PROCEDURE procedure_name([param_list])] [FOR UPDATE | LOCK IN SHARE MODE] ] SELECT normally is used to retrieve rows from one or more tables. However, because everything in the statement is optional except the SELECT keyword and at least one select_expr, it's also possible to write statements that simply evaluate expressions: SELECT 'one plus one =', 1+1; For compatibility with database systems that require a FROM clause, MySQL recognizes the DUAL pseudo-table: SELECT 'one plus one =', 1+1 FROM DUAL; The select_options clause, if present, consists of one or more of the following options separated by whitespace:
The select_expr expressions list the output columns to be returned. Multiple columns should be separated by commas. Columns may be references to table columns or expressions (including scalar subqueries). Any column may be assigned a column alias using AS alias_name syntax. The alias then becomes the column name in the output and may also be referred to in GROUP BY, ORDER BY, and HAVING clauses. However, you cannot refer to column aliases in a WHERE clause. The special notation * means "all columns from the tables named in the FROM clause," and tbl_name.* means "all columns from the named table." The result of a SELECT statement may be written into a file file_name using an INTO OUTFILE 'file_name' clause. The syntax of the field_options and line_options clauses is the same as for the corresponding clauses of the LOAD DATA statement. See the LOAD DATA entry for more information. INTO DUMPFILE 'file_name' is similar to INTO OUTFILE but writes only a single row and writes the output entirely without interpretation. That is, it writes raw values without delimiters, quotes, or terminators. This can be useful if you want to write BLOB data such as an image or other binary data to a file. For both INTO OUTFILE and INTO DUMPFILE, the filename is interpreted using the same rules that apply when reading non-LOCAL files with LOAD DATA. You must have the FILE privilege, the output file must not already exist, and the file is created by the server on the server host with a world-accessible mode. Its ownership will be set to the account used to run the server. The results of a SELECT may be stored into a set of variables. Each variable can be either a user-defined variables of the form @var_name, or, within a stored routine, a local variable for the routine. The query must select a single row of values, and must name one variable per output column, separated by commas. The FROM clause names one or more tables from which rows should be selected. MySQL supports the following join types for use in SELECT statements: tbl_list: tbl_name tbl_list, tbl_name tbl_list {[CROSS] JOIN | INNER JOIN} tbl_name tbl_list {[CROSS] JOIN | INNER JOIN} tbl_name ON conditional_expr tbl_list {[CROSS] JOIN | INNER JOIN} tbl_name USING (column_list) tbl_list STRAIGHT_JOIN tbl_name tbl_list LEFT [OUTER] JOIN tbl_name ON conditional_expr tbl_list LEFT [OUTER] JOIN tbl_name USING (column_list) tbl_list NATURAL [LEFT [OUTER]] JOIN tbl_name { OJ tbl_list LEFT OUTER JOIN tbl_name ON conditional_expr } tbl_list RIGHT [OUTER] JOIN tbl_name ON conditional_expr tbl_list RIGHT [OUTER] JOIN tbl_name USING (column_list) tbl_list NATURAL [RIGHT [OUTER]] JOIN tbl_name (tbl_list) Each table name may be accompanied by an alias or index hints. That is, the full syntax for referring to a table actually looks like this: tbl_name [[AS] alias_name] [{USE | IGNORE | FORCE} INDEX (index_list)] Tables may be assigned aliases in the FROM clause using either tbl_name alias_name or tbl_name AS alias_name syntax. An alias provides an alternative name by which to refer to the table columns elsewhere in the query. A table reference in the FROM clause also can be a subquery within parentheses, as long as you provide a table alias: SELECT * FROM (SELECT 1) AS t; The USE INDEX, IGNORE INDEX, and FORCE INDEX clauses provide index hints to the optimizer. This may be helpful in cases where the optimizer doesn't make the correct choice about which index to use in a join. FORCE INDEX forces the optimizer to use the index. (The word KEY can be used as a synonym for INDEX in these clauses.) index_list should name one or more indexes separated by commas. Each index in index_list should be the name of an index from the table, or the keyword PRIMARY to indicate the table's PRIMARY KEY. The join types select rows from the named tables as indicated in the following descriptions. The rows actually returned to the client may be limited by WHERE, HAVING, or LIMIT clauses.
The WHERE clause specifies an expression that is applied to rows selected from the tables named in the FROM clause. Rows that do not satisfy the criteria given by the expression are rejected. The result set may be further limited by HAVING and LIMIT clauses. Column aliases may not be referred to in the WHERE clause. The GROUP BY and ORDER BY clauses have similar syntax. GROUP BY column_list is used to group rows of the result set based on the columns named in the list. This clause is used when you specify summary functions such as COUNT() or MAX() in a select_expr. ORDER BY column_list indicates that the result set should be sorted based on the named columns. In either clause, columns may be referred to by column names or aliases, or by position within the list of select_expr expressions. Column positions are unsigned integers beginning with 1. (However, use of column positions is deprecated.) You can also use expressions to group or sort by expression results. For example, ORDER BY RAND() sorts rows in random order. Expressions can refer to summary (aggregate) functions in GROUP BY clauses but not in ORDER BY clauses. (As a workaround, select the function in the output column list, provide an alias for it, and refer to the alias in the ORDER BY clause.) In a GROUP BY or ORDER BY clause, you can follow any column in the column list with ASC or DESC to indicate that the column should be sorted in ascending or descending order. The default for each column is ascending if neither keyword is present. Sort order indicators are allowed in GROUP BY clauses because, in MySQL, GROUP BY not only groups rows, it sorts the results. The output order resulting from GROUP BY is overridden by any ORDER BY clause that is present. To prevent the implicit ordering that results from GROUP BY (and thus not incur the sorting overhead), use ORDER BY NULL. WITH ROLLUP can be used at the end of a GROUP BY clause. It causes the output to include summary rows for higher level combinations of the grouped columns, plus an overall summary at the end. The HAVING clause specifies a secondary expression that is used to limit rows after they have satisfied the conditions named by the WHERE clause and after they have been grouped according to any GROUP BY clause. Rows that do not satisfy the HAVING condition are rejected. HAVING is useful for expressions involving summary functions that cannot be tested in the WHERE clause. However, if a condition is legal in either the WHERE clause or the HAVING clause, it is preferable to place it in the WHERE clause where it will be subject to analysis by the optimizer. The LIMIT clause can be used to select a section of rows from the result set. It takes either one or two arguments, which must be integer constants. LIMIT show_count returns the first show_count rows. LIMIT skip_count, show_count skips the first skip_count rows, and then returns the next show_count rows. An alternative LIMIT syntax is LIMIT show_count OFFSET skip_count. PROCEDURE names a procedure to which the data in the result set will be sent before a result set is returned to the client. The optional parameter list, param_list, is a comma-separated list of values to pass to the procedure. You can use PROCEDURE ANALYSE() to obtain information about the characteristics of the data in the columns named in the column selection list. The FOR UPDATE and LOCK IN SHARE MODE clauses place locks on the rows that are examined during query execution. The locks remain in force until the current transaction is committed or rolled back. These locking clauses can be useful in multiple-statement transactions. If you use FOR UPDATE with a table for which the storage engine uses page-level or row-level locks (BDB or InnoDB), the examined rows are write-locked for exclusive use. Using LOCK IN SHARE MODE sets read locks on the rows, allowing other clients to read but not modify them. Note that if the query optimizer finds no index to use for examining rows, it must scan (and thus lock) all rows in the table. The following statements demonstrate some ways in which the SELECT statement can be used. See Chapter 1, "Getting Started with MySQL and SQL," and Chapter 2 for many other examples. Select the entire contents of a table: SELECT * FROM president; Select entire contents, but sort by name: SELECT * FROM president ORDER BY last_name, first_name; Select records for presidents born on or after '1900-01-01': SELECT * FROM president WHERE birth >= '1900-01-01'; Do the same, but sort in birth order: SELECT * FROM president WHERE birth >= '1900-01-01' ORDER BY birth; Determine which states are represented by rows in the member table: SELECT DISTINCT state FROM member; Select rows from member table and write columns as comma-separated values into a file:
SELECT * INTO OUTFILE '/tmp/member.txt'
FIELDS TERMINATED BY ',' FROM member;
Select the top five scores for a particular grade event: SELECT * FROM score WHERE event_id = 9 ORDER BY score DESC LIMIT 5; Subquery support. A subquery is one SELECT nested within another. Several forms of subquery are available; examples can be found in Chapter 2. Subqueries can also be used in the WHERE clause of DELETE and UPDATE statements or with INSERT and REPLACE statements. However, currently you cannot use a subquery to select from a table that you are modifying. SETSET [OPTION] assignment [, assignment ] ... assignment: var_name = expr The SET statement is used to assign values to system variables, user-defined variables, or local variables for stored routines. Information about system and user-defined variables is provided in Appendix D, "System, Status, and User Variable Reference." Declaration syntax for stored routine local variables is described in "Stored Routine Syntax." SET also is used for a few miscellaneous settings that are described later in this entry. The OPTION keyword in the SET statement is allowed but unnecessary. Other statements that begin with SET (SET PASSWORD and SET TRANSACTION) are described in separate sections later in this appendix. When SET is used to make variable assignments, var_name in each assignment is the variable to be assigned a value and expr is the expression that indicates the value to assign to the variable. The assignment operator in a SET statement can be either = or :=. SET can be used to assign values to user-defined variables, which are named using @var_name syntax: SET @day = CURDATE(), @time = CURTIME(); SET also can assign values to system variables, many of which are dynamic and can be changed while the server is running. Dynamic system variables exist at two levels. Global system variables are server-wide and affect all clients. Session system variables (also called local system variables) are specific to a given client connection only. For variables that exist at both levels, a given client's session variables are initialized from the values of the corresponding global variables when the client connects. It is necessary to have the SUPER privilege to modify a global variable, but any client can modify its own session variables. The syntax for setting system variables has several forms. To set a global variable (for example, the global sql_mode value), use a statement having either of the following forms: SET GLOBAL sql_mode = 'ANSI_QUOTES'; SET @@GLOBAL.sql_mode = 'ANSI_QUOTES'; To set a session variable, substitute the word SESSION for GLOBAL: SET SESSION sql_mode = 'ANSI_QUOTES'; SET @@SESSION.sql_mode = 'ANSI_QUOTES'; You can also use LOCAL as a synonym for SESSION: SET LOCAL sql_mode = 'ANSI_QUOTES'; SET @@LOCAL.sql_mode = 'ANSI_QUOTES'; If none of GLOBAL, SESSION, or LOCAL are present, the SET statement modifies the session-level variable: SET sql_mode = 'ANSI_QUOTES'; SET @@sql_mode = 'ANSI_QUOTES'; To check the value of system variables, use the SHOW VARIABLES statement. You can also retrieve individual system variable values by using SELECT: SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@LOCAL.sql_mode; The use of system variables is discussed further in "Setting and Checking System Variable Values," in Chapter 11. The following list describes miscellaneous settings that can be controlled with SET.
SET PASSWORDSET PASSWORD [FOR account] = PASSWORD('pass_val') SET PASSWORD [FOR account] = OLD_PASSWORD('pass_val') SET PASSWORD [FOR account] = 'encrypted_pass_val' SET PASSWORD changes the password for a MySQL account. You can always change your own password, unless you have connected as an anonymous user. To change the password for another account, you must have the UPDATE privilege for the mysql database. With no FOR clause, the statement sets the password for the current account. With a FOR clause, it sets the password for the named account, which should be given in the same 'user_name'@'host_name' format that is used for the GRANT statement. The password value, 'pass_val' should be encrypted using PASSWORD() for standard encryption or OLD_PASSWORD() for the older (pre-MySQL 4.1) encryption. If you use neither function, 'encrypted_pass_val' should be given as an already-encrypted password string.
SET PASSWORD = PASSWORD('secret');
SET PASSWORD FOR 'paul' = PASSWORD('secret');
SET PASSWORD FOR 'paul'@'localhost' = PASSWORD('secret');
SET PASSWORD FOR 'bill'@'%.bigcorp.com' = PASSWORD('old-sneep');
SET TRANSACTION
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL level
This statement sets the isolation level for transaction processing:
The SUPER privilege is required to set the global isolation level. Any client can change its own session or next-transaction isolation level. The transaction level indicated by level should be one of the following values:
The SET TRANSACTION statement applies only to the InnoDB storage engine, which by default runs at the REPEATABLE READ level. The BDB storage engine always runs at the SERIALIZABLE level. Non-transactional storage engines do not have isolation levels. "Transaction Isolation," in Chapter 2, further discusses transaction isolation and isolation levels. SHOWSHOW BINLOG EVENTS SHOW CHARACTER SET SHOW COLLATION SHOW COLUMN TYPES SHOW COLUMNS FROM tbl_name SHOW CREATE DATABASE db_name SHOW CREATE FUNCTION func_name SHOW CREATE PROCEDURE proc_name SHOW CREATE TABLE tbl_name SHOW CREATE VIEW view_name SHOW DATABASES SHOW ENGINE engine_name {LOGS | STATUS} SHOW ENGINES SHOW ERRORS SHOW FUNCTION STATUS SHOW GRANTS SHOW INDEX FROM tbl_name SHOW INNODB STATUS SHOW LOGS SHOW {MASTER | BINARY} LOGS SHOW MASTER STATUS SHOW OPEN TABLES SHOW PRIVILEGES SHOW PROCEDURE STATUS SHOW PROCESSLIST SHOW SLAVE HOSTS SHOW SLAVE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW VARIABLES SHOW WARNINGS The various forms of the SHOW statement provide information about databases, tables, columns, and indexes, or information about server operation. Several of the forms take an optional FROM db_name clause, allowing you to specify the database for which information should be shown. If the clause is not present, the default database is used. In each of these statements where FROM is used to specify a table or database name, IN can be used as a synonym. Some forms allow an optional LIKE 'pattern' clause to limit output to values that match the pattern. 'pattern' is interpreted as an SQL pattern and may contain the '%' or '_' wildcard characters. The following sections describe most of the SHOW statements in the preceding list. Descriptions for the SHOW CREATE FUNCTION, SHOW CREATE PROCEDURE, SHOW FUNCTION STATUS, and SHOW PROCEDURE STATUS statements can be found later in the chapter in "Stored Routine Syntax." As of MySQL 5.0.2, INFORMATION_SCHEMA provides another way to obtain database metadata. In addition, several SHOW statements have been extended to allow a WHERE clause. See "Getting Information About Databases and Tables," in Chapter 2. SHOW BINLOG EVENTSSHOW BINLOG EVENTS [IN 'file_name'] [FROM position] [LIMIT [skip_count,] show_count] This statement is used on replication master servers to display events in a binary log file. Events correspond roughly to SQL statements. The output from this statement includes the following columns:
This statement requires the REPLICATION SLAVE privilege. SHOW CHARACTER SET
SHOW CHARACTER SET [LIKE 'pattern']
Displays a list of the character sets supported by the server. The LIKE clause may be included to display information only for character sets with names that match the given pattern. The output from SHOW CHARACTER SET includes the following columns:
SHOW COLLATION
SHOW COLLATION [LIKE 'pattern']
Displays a list of available collations for each character set. The LIKE clause may be included to display information only for collations with names that match the given pattern. The output from SHOW COLLATION includes the following columns:
SHOW COLUMN TYPESSHOW COLUMN TYPES Displays information about the data types that may be used when creating MySQL tables. The output from SHOW COLUMN TYPES includes the following columns:
SHOW COLUMNS
SHOW [FULL] COLUMNS {FROM | IN} tbl_name
[{FROM | IN} db_name] [LIKE 'pattern']
Displays the columns for the given table. SHOW FIELDS is a synonym for SHOW COLUMNS. With the FULL keyword, the statement displays the Collation, Privilege, and Comment output fields. The LIKE clause may be included to display information only for columns with names that match the given pattern. To specify the database that contains the table, use a FROM db_name clause or write the table name in db_name.tbl_name format: SHOW COLUMNS FROM president; SHOW COLUMNS FROM president FROM sampdb; SHOW COLUMNS FROM sampdb.president; The output from SHOW COLUMNS provides the following types of information about each column in the table:
SHOW CREATE DATABASE
SHOW CREATE DATABASE [IF NOT EXISTS] db_name
Displays the CREATE DATABASE statement that creates the named database. If the SHOW statement includes an IF NOT EXISTS clause, the output statement does as well. As of MySQL 4.1.2, the value of the sql_quote_show_create session variable controls quoting of identifiers in the output statement. SHOW CREATE TABLE
SHOW CREATE TABLE tbl_name
Displays the CREATE TABLE statement that creates the named table. The value of the sql_quote_show_create session variable controls quoting of identifiers in the output statement. SHOW CREATE VIEW
SHOW CREATE VIEW view_name
Displays the CREATE VIEW statement that creates the named view. The value of the sql_quote_show_create session variable controls quoting of identifiers in the output statement. SHOW CREATE VIEW was introduced in MySQL 5.0.1. SHOW DATABASES
SHOW DATABASES [LIKE 'pattern']
Displays the databases available on the server host. The LIKE clause may be included to display information only for databases with names that match the given pattern. If you don't have the SHOW DATABASES privilege, you'll see only the databases for which you have some kind of access privilege. If the server was started with the --skip-show-database option, you'll see all databases if you have the SHOW DATABASES privilege and none otherwise. SHOW ENGINE ... { LOGS | STATUS}
SHOW ENGINE engine_name {LOGS | STATUS}
Displays information about the given storage engine's logs or status. SHOW ENGINE BDB LOGS SHOW ENGINE INNODB STATUS; This statement is a generalized-syntax form that is intended to replace other storage engine-specific statements such as SHOW INNODB STATUS and SHOW BDB LOGS. This statement was introduced in MySQL 4.1.2. SHOW ENGINESSHOW [STORAGE] ENGINES Displays the storage engines that the server knows about. For each engine, the output indicates the support level and provides a brief description of the engine characteristics. The output from this statement includes the following columns:
This statement was introduced in MySQL 4.1.0 as SHOW TABLE TYPES. In 4.1.2, the SHOW ENGINES syntax was introduced, which now is the preferred form. SHOW ERRORSSHOW ERRORS [LIMIT [skip_count,] show_count] SHOW COUNT(*) ERRORS SHOW ERRORS is like SHOW WARNINGS but displays only messages that have error severity. SHOW COUNT(*) ERRORS is like SHOW COUNT(*) WARNINGS but displays the value of the error_count variable rather than the value of warning_count. See the entry for SHOW WARNINGS for more information. SHOW GRANTS
SHOW GRANTS [FOR account]
Displays grant information about the specified account, which should be given in the same 'user_name'@'host_name' format that is used for the GRANT statement. SHOW GRANTS FOR 'root'@'localhost'; SHOW GRANTS FOR ''@'cobra.snake.net'; As of MySQL 4.1.2, you can use any of the following statements to display the privileges for the account that you are connected to the server as: SHOW GRANTS FOR CURRENT_USER(); SHOW GRANTS FOR CURRENT_USER; SHOW GRANTS; SHOW INDEX
SHOW {INDEX | KEY} {FROM | IN} tbl_name [{FROM | IN} db_name]
Displays information about a table's indexes. To specify the database that contains the table, use a FROM db_name clause or write the table name in db_name.tbl_name format: SHOW INDEX FROM score; SHOW INDEX FROM score FROM sampdb; SHOW INDEX FROM sampdb.score; The output from SHOW INDEX includes the following columns:
SHOW INNODB STATUSSHOW INNODB STATUS Displays information about the internal operation of the InnoDB storage engine. It requires the SUPER privilege. SHOW LOGSSHOW [BDB] LOGS Displays information about the server's BDB log files. It requires the FILE privilege. The output from SHOW LOGS includes the following columns:
SHOW MASTER LOGS
SHOW {MASTER | BINARY} LOGS
This statement is used on replication master servers. It displays the names of the binary log files currently available on the master. It can be useful before issuing a PURGE MASTER LOGS statement after running SHOW SLAVE STATUS on each of the slaves to determine the binary logs to which they currently are positioned. SHOW MASTER STATUSSHOW MASTER STATUS This statement is used on replication master servers. It displays information about the status of the master's binary logs. The output from SHOW MASTER STATUS includes the following columns:
SHOW OPEN TABLESSHOW OPEN TABLES Displays the list of open non-TEMPORARY tables that are registered in the table cache. The output from SHOW OPEN TABLES includes the following columns:
SHOW PRIVILEGESSHOW PRIVILEGES Displays the privileges that can be granted and information about the purpose of each one. The output from SHOW PRIVILEGES includes the following columns:
SHOW PROCESSLISTSHOW [FULL] PROCESSLIST Displays information about the threads executing within the server. If you have the PROCESS privilege, the statement displays all threads. Otherwise, it displays only your own threads. The output includes the following columns:
SHOW SLAVE HOSTSSHOW SLAVE HOSTS This statement is used on replication master servers. It displays information about the slave servers that are currently registered with the master. A slave is not registered unless it is started with the --report-host option. Even for a registered slave, other conditions apply for display of certain columns. The Port column value is blank unless the slave is started with the --report-port option. The User and Password column values are blank unless the slave is started with the --report-user and --report-password options and the master is started with the --show-slave-auth-info option. The output from SHOW SLAVE HOSTS includes the following columns:
SHOW SLAVE STATUSSHOW SLAVE STATUS This statement is used on slave servers and displays information about the replication status of the server. The output from SHOW SLAVE STATUS includes the following columns:
SHOW STATUS
SHOW STATUS [LIKE 'pattern']
Displays the server's status variables and their values. These variables provide information about the server's operational state. The LIKE clause may be included to display information only for variables with names that match the given pattern. Appendix D describes each of the status variables. SHOW TABLE STATUS
SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern']
Displays descriptive information about the tables in a database. The LIKE clause may be included to display information only for tables with names that match the given pattern. As of MySQL 5.0.1, this statement also displays the views in a database, but all columns are NULL except that Name is the view name and Comment is view. The output from SHOW TABLE STATUS includes the following columns:
In MySQL 4.1.0, the output included a Charset column. This was changed to Collation in 4.1.1. SHOW TABLE TYPESSHOW TABLE TYPES SHOW TABLE TYPES was the original syntax for the SHOW ENGINES statement. It is still recognized but is deprecated and its use produces a warning. See the entry for SHOW ENGINES for a description of the output. SHOW TABLES
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern']
Displays the names of the non-TEMPORARY tables in a database. The LIKE clause may be included to display information only for tables with names that match the given pattern. Beginning with MySQL 5.0.1, this statement also displays view names. The FULL keyword may be given as of MySQL 5.0.2 to display for each row whether the name refers to a table or a view. The output from this statement includes the following columns:
SHOW VARIABLES
SHOW [GLOBAL | SESSION ] VARIABLES [LIKE 'pattern']
Displays a list of system variables and their values. These variables provide information about the server's configuration and capabilities. The LIKE clause may be included to display information only for variables with names that match the given pattern. Appendix D describes each of the system variables. The server can display the values of system variables at the global (server-wide) or session (client-specific) level. By default, SHOW displays the session-level value for any given variable, or the global value if no session value exists. To display global or session values explicitly, specify a level indicator: SHOW GLOBAL VARIABLES [LIKE 'pattern']; SHOW SESSION VARIABLES [LIKE 'pattern']; LOCAL is a synonym for SESSION. It is also possible to retrieve the values of individual dynamic variables with SELECT: SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@LOCAL.sql_mode; Using SELECT has the advantage that you can more easily manipulate the query result in certain contexts. See the entry for the SET statement for a discussion of the syntax used to assign values to system variables. SHOW WARNINGSSHOW WARNINGS [LIMIT [skip_count,] show_count] SHOW COUNT(*) WARNINGS SHOW WARNINGS displays error, warnings, and notes generated by the most recent statement that generates such messages. If that statement executed successfully, SHOW WARNINGS returns an empty set. SHOW COUNT(*) WARNINGS displays the value of the warning_count system variable that counts the number of messages. (A related variable, error_count, counts only errors.) It is possible for the value of warning_count to be larger than the number of messages displayed by SHOW WARNINGS. The max_error_count system variable limits the number of messages that can be stored for display by SHOW WARNINGS, but warning_count counts all messages regardless of whether they are stored. The LIMIT clause can be used to restrict the number of rows returned by SHOW WARNINGS. Its syntax is the same as the LIMIT clause for SELECT. START SLAVESTART SLAVE [slave_options] START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = position START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'file_name', RELAY_LOG_POS = position This statement, together with STOP SLAVE, controls the operation of replication threads on a slave server. START SLAVE initiates the slave I/O and SQL threads and STOP SLAVE terminates them. The optional slave_options clause may be specified to indicate which of the threads to start or stop. It should consist of one or more of the following options, separated by commas:
If no thread or SQL_THREAD is named, an UNTIL clause can be used as of MySQL 4.1.1. Depending on which pair of log file and position options are named in the clause, the slave runs until its SQL thread reaches the given position in the master binary logs or slave relay logs. If the SQL thread is already running, the server ignores the UNTIL clause and generates a warning. If the clause includes the SQL_THREAD option, the server starts only the SQL thread; otherwise, it starts both threads. START TRANSACTIONSTART TRANSACTION [WITH CONSISTENT SNAPSHOT] Begins a transaction by disabling autocommit mode until the next COMMIT or ROLLBACK statement. Statements executed while autocommit mode is disabled thus will be committed or rolled back as a unit. After the transaction has been committed or rolled back, autocommit mode is restored to the state it was in prior to START TRANSACTION. To manipulate autocommit mode explicitly, use SET autocommit. The autocommit variable is described in Appendix D. As of MySQL 4.1.8, the WITH CONSISTENT SNAPSHOT clause can be used to cause the transaction to begin with a consistent read. Currently, this applies only to InnoDB tables. START TRANSACTION implicitly releases any table locks that the client has acquired with LOCK TABLE but has not yet released. Executing START TRANSACTION while a transaction is in progress causes that transaction to be committed implicitly. STOP SLAVE
STOP SLAVE [slave_options]
This statement, together with START SLAVE, controls the operation of replication threads on a slave server. See the description of START SLAVE for details. TRUNCATE
TRUNCATE [TABLE] tbl_name
trUNCATE TABLE performs a fast truncation of table contents by dropping and re-creating the table. This is much faster than deleting each row individually. For InnoDB, this statement is implemented as DELETE FROM tbl_name before MySQL 5.0.3. As of 5.0.3, InnoDB implements fast truncation directly. This statement is not transaction-safe; an error will occur should you issue a trUNCATE TABLE statement in the middle of an active transaction or while you are holding any explicit table locks. UNIONselect_stmt UNION [DISTINCT | ALL] select_stmt [UNION [DISTINCT | ALL] select_stmt] ... [ORDER BY column_list] [LIMIT [skip_count,] show_count] UNION isn't really a separate statement, it's a way of combining SELECT statements such that their results are concatenated one after the other. Each SELECT statement must produce the same number of columns in its result set. The names of the columns in the final result are determined by the column names in the first SELECT. The data types of the columns are determined taking into account all values from the corresponding columns of the selected tables. The UNION keyword can be followed by DISTINCT to eliminate duplicate rows or by ALL to preserve duplicates and return all selected rows. The implicit default is to eliminate duplicates if neither DISTINCT nor ALL is given. Any DISTINCT union operation (either explicit or implicit) takes precedence over any ALL union operations to its left: mysql> SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 1; +---+ | 1 | +---+ | 1 | | 2 | | 1 | +---+ mysql> SELECT 1 UNION ALL SELECT 2 UNION SELECT 1; +---+ | 1 | +---+ | 1 | | 2 | +---+ To use ORDER BY or LIMIT clauses with any individual SELECT, enclose each SELECT within parentheses. To apply ORDER BY or LIMIT to the UNION as a whole, enclose each SELECT within parentheses and add ORDER BY or LIMIT following the final closing parenthesis. In this case, any columns named in an ORDER BY should refer to the names of the columns in the first SELECT. UNLOCK TABLE
UNLOCK {TABLE | TABLES}
This statement releases any table locks being held by the current client. If a client connection terminates while the client holds table locks, the server releases them when it closes the connection. If a client begins a transaction while holding table locks, the server implicitly releases those locks. UPDATEUPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name=expr [, col_name=expr ] ... [WHERE where_expr] [ORDER BY ... ] [LIMIT n] UPDATE [LOW_PRIORITY] [IGNORE] tbl_name , tbl_name ... SET col_name=expr [, col_name=expr ] ... [WHERE where_expr] [ORDER BY ... ] [LIMIT n] For the first syntax, UPDATE modifies the contents of existing rows in the table tbl_name. The second UPDATE syntax is like the first, but allows multiple tables to be named to perform a multiple-table update. The rows to be updated are those selected by the expression specified in the WHERE clause. For those rows that are selected, each column named in the SET clause is set to the value of the corresponding expression.
UPDATE member SET expiration = NULL, phone = '197-602-4832'
WHERE member_id = 14;
If no WHERE clause is given, all records in the table are updated. The WHERE clause can include subqueries, but they cannot select from a table that is being updated. UPDATE returns the number of rows that were updated. However, a row is not considered as having been updated unless some column value actually changed. Setting a column to the value it already contains is not considered to affect the row. If your application really needs to know how many rows matched the WHERE clause regardless of whether the UPDATE actually changed any values, you should specify the CLIENT_FOUND_ROWS flag when you establish a connection to the server. See the entry for the mysql_real_connect() function in Appendix G. LOW_PRIORITY causes the statement to be deferred until no clients are reading from the table. If updating a record would result in a duplicate key value in a unique index, UPDATE terminates in error and no more records are updated. Adding IGNORE causes such records not to be updated and no error occurs. In strict mode, IGNORE also causes data conversion errors that otherwise would terminate the statement to be treated as non-fatal warnings. Columns are updated to the nearest legal value in this case. ORDER BY causes rows to be updated according to the resulting sort order. This clause has the same syntax as for SELECT. If the LIMIT clause is given, the value n specifies the maximum number of rows to update. For a multiple-table UPDATE, the WHERE clause can specify conditions based on a join between tables, and the SET clause can update columns in multiple tables. For example, the following statement updates rows in t1 having id values that match those in t2, copying the quantity values from t2 to t1: UPDATE t, t2 SET t.quantity = t2.quantity WHERE t.id = t2.id; USE
USE db_name
Selects db_name to make it the current database (the default database for table, view, and stored routine references that include no explicit database name). After a successful USE statement, the server sets the session character_set_database and collation_database system variables to the database character set and collation. The USE statement fails if the database doesn't exist or if you have no privileges for accessing it. |
|
|