PHP & MySQL: Novice to Ninja, 5th Edition (2012)
Appendix B. MySQL Syntax Reference
This appendix describes the syntax of the most commonly used SQL statements in MySQL, as of version 5.5.22 (current as of this writing). The following conventions are used in this reference:
· Commands are listed in alphabetical order for easy reference.
· Optional portions of each command are surrounded by square brackets ([]).
· Lists of elements from which one element must be chosen are surrounded by braces ({}), with the elements separated by vertical bars (|).
· An ellipsis (…) means that the preceding element may be repeated.
The query syntax documented in this appendix has been simplified in several places by the omission of the alternative syntax, and of keywords that performed no function, but which were originally included for compatibility with other database systems. Query features having to do with some advanced features such as transactions have also been omitted. For a complete, up-to-date reference to supported MySQL syntax, see the MySQL Reference Manual.
SQL Statements Implemented in MySQL
ALTER TABLE
ALTER [IGNORE] TABLE tbl_name
action
[, action …]
In this code, action refers to an action as defined as follows. ALTER TABLE queries may be used to change the definition of a table without losing any of the information in that table (except in obvious cases, such as the deletion of a column). Here are the main actions that are possible:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
This action adds a new column to the table. The syntax for create_definition is as described for the section called “ CREATE TABLE ”. By default, the column will be added to the end of the table, but by specifying FIRST or AFTER column_name , you can place the column wherever you like. The optional word COLUMN performs no actual function—leave it off unless you particularly like to see it there.
ADD INDEX [ index_name ] (index_col_name, …)
This action creates a new index to speed up searches based on the column(s) specified. You may assign a name to your indexes by specifying the index_name; otherwise, a default name based on the first column in the index will be used. When creating an index based on CHAR and/orVARCHAR columns, you can specify a number of characters to index as part of index_col_name (for example, myColumn(5) will index the first five characters of myColumn). This number must be specified when indexing BLOB and TEXT columns.
ADD FULLTEXT [ index_name ] (index_col_name, …)
This action creates a full-text index on the column(s) specified. This special type of index allows you to perform complex searches for text in CHAR, VARCHAR, or TEXT columns using the MATCH MySQL function. For full details, see the MySQL Reference Manual.
ADD FOREIGN KEY [ index_name ] (index_col_name, …) reference_definition
On InnoDB tables, this creates a foreign key constraint, requiring the values in this index to correspond to matching entries in another table. reference_definition specifies the table and column(s) that are referenced by the constraint:
REFERENCES tbl_name (index_col_name, …)
[ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION }]
[ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION }]
The optional ON DELETE and ON UPDATE portions of reference_definition specify what should happen to entries in this table when the corresponding entry in the referenced table is deleted or updated. For full details, see the MySQL Reference Manual.
ADD PRIMARY KEY (index_col_name, …)
This action creates an index for the specified row(s) with the name PRIMARY, identifying it as the primary key for the table. All values (or combinations of values) must be unique, as described for the ADD UNIQUE action below. This action will cause an error if a primary key already exists for the table. index_col_name is defined as it is for the ADD INDEX action before.
ADD UNIQUE [ index_name ] (index_col_name, …)
This action creates an index on the specified columns, but with a twist: all values in the designated column—or all combinations of values if more than one column is included in the index—must be unique. The parameters index_name and index_col_name are defined as they are for theADD INDEX action.
ALTER [COLUMN] col_name {SET DEFAULT value | DROP DEFAULT}
This action assigns a new default value to a column (SET DEFAULT), or removes the existing default value (DROP DEFAULT). Again, the word COLUMN is completely optional and has no effect.
CHANGE [COLUMN] col_name create_definition
This action replaces an existing column (col_name) with a new column, as defined by create_definition (the syntax of which is as specified for the section called “ CREATE TABLE ”). The data in the existing column is converted, if necessary, and placed in the new column. Note thatcreate_definition includes a new column name, so this action may be used to rename a column. If you want to leave the name of the column unchanged, however, remember to include it twice (once for col_name and once for create_definition), or use the MODIFY action below.
DISABLE KEYS , ENABLE KEYS
When you insert a large number of records into a table, MySQL can spend a lot of time updating the index(es) of the table to reflect the new entries. Executing ALTER TABLE … DISABLE KEYS before you perform the inserts will instruct MySQL to postpone those index updates. Once the inserts are complete, execute ALTER TABLE … ENABLE KEYS to update the indexes for all the new entries at once. This will usually save time over performing the updates one at a time.
DROP [COLUMN] col_name
Fairly self-explanatory, this action completely removes a column from the table. The data in that column is irretrievable after this query completes, so be sure of the column name you specify. COLUMN, as usual, can be left off; it just makes the query sound better when read aloud.
DROP PRIMARY KEY , DROP INDEX index_name , DROP FOREIGN KEY index_name
These actions are quite self-explanatory: they remove from the table the primary key, index, or foreign key constraint, respectively.
MODIFY [COLUMN] create_definition
Nearly identical to the aforementioned CHANGE action, this action lets you specify a new declaration for a column in the table, but assumes the name will remain the same. Thus, you simply have to re-declare the column with the same name in the create_definition parameter (as defined for the section called “ CREATE TABLE ”). As before, COLUMN is completely optional and does nothing. Although convenient, this action is not standard SQL syntax, and was added for compatibility with an identical extension in Oracle database servers.
ORDER BY col_name
This action lets you sort a table’s entries by a particular column. However, as soon as new entries are added to the table or existing entries modified, ordering can no longer be guaranteed. The only practical use of this action would be to increase performance of a table that you sorted regularly in a certain way in your application’s SELECT queries. Under some circumstances, arranging the rows in (almost) the right order to begin with will make sorting quicker.
RENAME [TO] new_tbl_name
This action renames the table. The word TO is completely optional, and does nothing. Use it if you like it.
table_options
Using the same syntax as in the CREATE TABLE query, this action allows you to set and change advanced table options. These options are fully documented in the MySQL Reference Manual.
ANALYZE TABLE
ANALYZE TABLE tbl_name
[, tbl_name …]
This function updates the information used by the SELECT query in the optimization of queries that take advantage of table indexes. It pays in performance to run this query periodically on tables whose contents change a lot over time. The table(s) in question are locked as “read-only” while the analysis runs.
BEGIN
BEGIN
BEGIN performs the same action as START TRANSACTION.
COMMIT
COMMIT
Once a transaction has been started (that is, autocommit mode has been disabled) with START TRANSACTION, MySQL collects the changes made to the database so that they may be applied simultaneously. A COMMIT query applies all those changes at once, ending the transaction.
CREATE DATABASE
CREATE DATABASE [IF NOT EXISTS]
db_name
This action simply creates a new database with the given name (db_name). This query will fail if the database already exists (unless IF NOT EXISTS is specified), or if you lack the required privileges.
CREATE INDEX
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON tbl_name
(col_name
[(length)], …)
This query creates a new index on an existing table. It works identically to ALTER TABLE ADD {INDEX | UNIQUE | FULLTEXT}, described in the section called “ ALTER TABLE ”.
CREATE TABLE
CREATE [TEMPORARY] TABLE [IF NOT EXISTS]
[
db_name.]
tbl_name
{ [(create_definition, …)]
[
table_options
]
[
[IGNORE | REPLACE]
select_statement
]
| LIKE [
db_name.]
old_tbl_name }
Where create_definition is:
{ col_name
type
[NOT NULL]
[DEFAULT default_value
]
[AUTO_INCREMENT]
[PRIMARY KEY]
| PRIMARY KEY (index_col_name, …)
| INDEX [
index_name
] (index_col_name, …)
| UNIQUE [INDEX]
[
index_name
] (index_col_name, …)
| FULLTEXT [
index_name
] (index_col_name, …)
| FOREIGN KEY [
index_name
] (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)
[ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION }]
[ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION }]}
In this code, type is a MySQL column type (see Appendix D), and index_col_name is as described for ALTER TABLE ADD INDEX in the section called “ ALTER TABLE ”. CREATE TABLE is used to create a new table called tbl_name in the current database (or in a specific database if db_name is specified). If TEMPORARY is specified, the table disappears when the connection that created it is terminated. A temporary table created with the same name as an existing table will hide the existing table from the current client session until the temporary table is deleted or the session ends; however, other clients will continue to see the original table. Assuming TEMPORARY is not specified, this query will fail if a table with the given name already exists, unless IF NOT EXISTS is specified (in which case the query is ignored). A CREATE TABLE query will also fail if you lack the required privileges. Most of the time, the name of the table will be followed by a series of column declarations (create_definition above). Each column definition includes the name and data type for the column, and any of the following options:
NOT NULL
This specifies that the column may not be left empty (NULL). Note that NULL is a special “no value” value, which is quite different from, say, an empty string (''). A column of type VARCHAR, for instance, which is set NOT NULL may be set to '' but will not be NULL. Likewise, a NOT NULLcolumn of type INT may contain zero (0), which is a value, but it may not contain NULL, as this is not a value.
DEFAULT default_value
DEFAULT lets you specify a value to be given to a column when no value is assigned in an INSERT statement. When there’s no value given in an INSERT statement, NULL columns (that is, columns where the NOT NULL option isn’t set) are normally assigned a value of NULL. When DEFAULT is specified, NOT NULL columns will instead be assigned a “default default value”: an empty string (''), zero (0), '0000-00-00', or a current timestamp, depending on the data type of the column.
AUTO_INCREMENT
As described in Chapter 2, an AUTO_INCREMENT column will automatically insert a number that is one greater than the current highest number in that column when NULL is inserted. AUTO_INCREMENT columns must also be NOT NULL, and be either a PRIMARY KEY or UNIQUE.
PRIMARY KEY
This option specifies that the column in question should be the primary key for the table; that is, the values in the column must identify uniquely each of the rows in the table. This forces the values in this column to be unique, and speeds up searches for items based on this column by creating an index of the values it contains.
UNIQUE
Very similar to PRIMARY KEY, this option requires all values in the column to be unique, and indexes the values for high-speed searches.
In addition to column definitions, you can list additional indexes you wish to create on the table using the PRIMARY KEY, INDEX, UNIQUE, FULLTEXT, and FOREIGN KEY forms of create_definition. See the descriptions of the equivalent forms of ALTER TABLE in the section called “ ALTER TABLE ” for details. The table_options portion of the CREATE TABLE query is used to specify advanced properties of the table, such as DEFAULT CHARACTER SET utf8 and the ENGINE=InnoDB, and is described in detail in the MySQL Reference Manual. The select_statement portion of the CREATE TABLE query allows you to create a table from the results of a SELECT query (see the section called “ SELECT ”). When you create this table, it’s unnecessary to declare separately the columns that correspond to those results. This type of query is useful if you want to obtain the result of a SELECT query, store it in a temporary table, and then perform a number of SELECT queries upon it. Instead of defining a table from scratch, you can instead instruct MySQL to create the new table using the same structure as another table. Rather than a list of create_definitions and the table_options, simply end theCREATE TABLE query with LIKE, followed by the name of the existing table.
DELETE
DELETE [LOW_PRIORITY]
[QUICK]
[IGNORE]
{ FROM tbl_name
[WHERE where_clause
]
[ORDER BY order_by_expr
]
[LIMIT rows
]
| tbl_name
[, tbl_name …]
FROM table_references
[WHERE where_clause
]
| FROM tbl_name
[, tbl_name …]
USING table_references
[WHERE where_clause
] }
The first form of this query deletes all rows from the specified table, unless the optional (but desirable) WHERE or LIMIT clauses are specified. The WHERE clause works the same way as its twin in the SELECT query (see the section called “ SELECT ”). The LIMIT clause simply lets you specify the maximum number of rows to be deleted. The ORDER BY clause lets you specify the order in which the entries are deleted, which, in combination with the LIMIT clause, allows you to perform actions such as delete the ten oldest entries from the table. The second and third forms are equivalent, and enable you to delete rows from multiple tables in a single operation, in much the same way as you can retrieve entries from multiple tables using a join in a SELECT query (see the section called “ SELECT ”). The table_references work the same way as they do for SELECT queries (you can create simple joins or outer joins), while the WHERE clause lets you narrow down the rows that are considered for deletion. The first list of tables ( tbl_name [, tbl_name …] ), however, identifies from the table_references the tables where rows will actually be deleted. In this way, you can use a complex join involving a number of tables to isolate a set of results, then delete the rows from only one of those tables. The LOW_PRIORITY option causes the query to wait until there are no clients reading from the table before performing the operation. The QUICK option attempts to speed up lengthy delete operations by changing the way it updates the table’s index(es). The IGNORE option instructs MySQL to refrain from reporting any errors that occur while the delete is performed.
DESCRIBE/DESC
{DESCRIBE | DESC} tbl_name
[
col_name | wild
]
This command supplies information about the columns, a specific column (col_name), or any columns that match a pattern containing the wild cards % and _ (wild) that make up the specified table. The information returned includes the column name, its type, whether it accepts NULL as a value, whether the column has an index, the default value for the column, and any extra features it has (for example, AUTO_INCREMENT).
DROP DATABASE
DROP DATABASE [IF EXISTS]
db_name
This is a dangerous command. It will immediately delete a database, along with all its tables. This query will fail with an error if the database does not exist (unless IF EXISTS is specified, in which case it will fail silently), or if you lack the required privileges.
DROP INDEX
DROP INDEX index_name ON tbl_name
DROP INDEX has exactly the same effect as ALTER TABLE DROP INDEX, described in the section called “ ALTER TABLE ”.
DROP TABLE
DROP TABLE [IF EXISTS]
tbl_name
[, tbl_name, …]
This query completely deletes one or more tables. This is a dangerous query, since the data can never be retrieved once this action is executed. Be very careful with it! The query will fail with an error if the table doesn’t exist (unless IF EXISTS is specified, in which case it will fail silently) or if you lack the required privileges.
EXPLAIN
The explain query has two very different forms. The first,
EXPLAIN tbl_name
is equivalent to DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name . The second format,
EXPLAIN select_statement
where select_statement can be any valid SELECT query, will produce an explanation of how MySQL would determine the results of the SELECT statement. This query is useful for finding out where indexes will help speed up your SELECT queries, and for determining if MySQL is performing multi-table queries in optimal order. See the STRAIGHT_JOIN option of the SELECT query in the section called “ SELECT ” for information on how to override the MySQL optimizer and control this order manually. See the MySQL Reference Manual for complete information on how to interpret the results of an EXPLAIN query.
GRANT
GRANT priv_type
[(column_list)], …
ON {tbl_name | * | *.* | db_name.*}
TO username
[IDENTIFIED BY 'password'], …
[WITH GRANT OPTION]
GRANT adds new access privileges to a user account, and creates a new account if the specified username does not yet exist. It may also change the password if IDENTIFIED BY 'password' is used on an account that already has a password. See the MySQL Reference Manual for a complete description of this and other queries that may be used to manage user accounts.
INSERT
INSERT [LOW_PRIORITY | DELAYED]
[IGNORE]
[INTO]
tbl_name
{ [(col_name, …)] VALUES (expression, …), …
| SET col_name=expression, col_name=expression, …
| [(col_name, …)] SELECT … }
[ON DUPLICATE KEY UPDATE col_name=expression
[, …]
]
The INSERT query is used to add new entries to a table. It supports three general options:
LOW_PRIORITY
The query will wait until there are no clients reading from the table before it proceeds.
DELAYED
The query completes immediately from the client’s point of view, and the INSERT operation is performed in the background. This option is useful when you wish to insert a large number of rows without waiting for the operation to complete. Be aware that the client will not know the last inserted ID on an AUTO_INCREMENT column when a DELAYED insert is performed (for example, PHP’s PDO lastInsertId method will fail to work correctly).
IGNORE
Normally, when an insert operation causes a clash in a PRIMARY KEY or UNIQUE column, the insert fails and produces an error message. This option allows the insert to fail silently; the new row is not inserted, but no error message is displayed.
The word INTO is entirely optional, and has no effect on the operation of the query. As you can see above, INSERT queries may take three forms. The first form lets you insert one or more rows by specifying the values for the table columns in parentheses. If the optional list of column names is omitted, the list(s) of column values must include a value for every column in the table, in the order in which they appear in the table. The second form of INSERT can be used only to insert a single row, but, very intuitively, it allows you to assign values to the columns in that row by giving them in col_name=value format. In the third and final form of INSERT, the rows to be inserted result from a SELECT query. Again, if the list of column names is omitted, the result set of the SELECT must contain values for each and every column in the table, in the correct order. A SELECT query that makes up part of an insert statement may not contain an ORDER BY clause, and you’re unable to use the table into which you’re inserting in the FROM clause. Columns to which you assign no value (for example, if you leave them out of the column list) are assigned their default. By default, inserting a NULL value into a NOT NULL field will also cause that field to be set to its default value; however, if MySQL is configured with the DONT_USE_DEFAULT_FIELDS option enabled, this sort of INSERT operation will cause an error. For this reason, it’s best to avoid them. The optional ON DUPLICATE KEY UPDATE clause takes effect when the INSERT query attempts to add a new entry to the table that would introduce a duplicate value disallowed by a unique index or primary key. Instead of the query failing with an error, this clause specifies how the existing entry in the table should be updated. The form of this clause is very similar to an UPDATE statement: it specifies one or more columns and the new value that should be assigned to each. See the section called “ UPDATE ” for more information.
LOAD DATA INFILE
LOAD DATA [LOW_PRIORITY | CONCURRENT]
[LOCAL] INFILE
'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[
[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES [STARTING BY '']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name, …)]
The LOAD DATA INFILE query is used to import data from a text file either on the MySQL server, or on the LOCAL (client) system (for example, a text file created with a SELECT INTO OUTFILE query). The syntax of this command is in the code, but I’d refer you to the MySQL Reference Manualfor a complete explanation of this query and the issues that surround its use.
OPTIMIZE TABLE
OPTIMIZE TABLE tbl_name
[, tbl_name …]
Much like a hard-disk partition becomes fragmented if existing files are deleted or resized, MySQL tables become fragmented as you delete rows and modify variable-length columns (such as VARCHAR or BLOB) over time. This query performs the database equivalent of a defrag on the table, reorganizing the data it contains to eliminate wasted space. It’s important to note that a table is locked while an optimize operation occurs, so if your application relies on a large table being constantly available, that application will grind to a halt while the optimization takes place. In such cases, it’s better to copy the table, optimize the copy, and then replace the old table with the newly optimized version using a RENAME query. Changes made to the original table in the interim will be lost, so this technique is only appropriate for some applications.
RENAME TABLE
RENAME TABLE tbl_name TO new_table_name
[, tbl_name2 TO …, …]
This query quickly and conveniently renames one or more tables. This differs from ALTER TABLE tbl_name RENAME in that all the tables being renamed in the query are locked for the duration of the query, so that no other connected clients may access them. As the MySQL Reference Manual explains, this assurance of atomicity lets you replace a table with an empty equivalent; for example, if you wanted to safely start a new table once a certain number of entries was reached:
CREATE TABLE new_table (…);
RENAME TABLE old_table TO backup_table, new_table TO old_table;
You can also move a table from one database to another by specifying the table name as db_name.tbl_name , as long as both tables are stored on the same physical disk, which is usually the case. You must have ALTER and DROP privileges on the original table, as well as CREATE and INSERTprivileges on the new table, in order to perform this query. A RENAME TABLE query that fails to complete halfway through will automatically be reversed, so that the original state is restored.
REPLACE
REPLACE [LOW_PRIORITY | DELAYED]
[INTO]
tbl_name
{ [(col_name, …)] VALUES (expression, …), …
| [(col_name, …)] SELECT …
| SET col_name=expression, col_name=expression, … }
REPLACE is identical to INSERT, except that if an inserted row clashes with an existing row in a PRIMARY KEY or UNIQUE column, the old entry is replaced with the new.
REVOKE
REVOKE priv_type
[(column_list)], …
ON {tbl_name | * | *.* | db_name.*}
FROM user, …
This function removes access privileges from a user account. If all privileges are removed from an account, the user will still be able to log in but unable to access any information. See the section called “MySQL Access Control Tips” in Chapter 10 for a complete description of this query.
ROLLBACK
ROLLBACK
Once a transaction has been started (that is, autocommit mode has been disabled) with START TRANSACTION, MySQL collects the changes made to the database so that they may be applied all at once. A ROLLBACK query discards all those changes, canceling the transaction.
SELECT
SELECT [select_options]
select_expression, …
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options
]
[FROM table_references
[WHERE where_definition
]
[GROUP BY {col_name | col_pos } [ASC | DESC], …]
[HAVING where_definition
]
[ORDER BY {col_name | col_pos } [ASC | DESC], …]
[LIMIT [
offset,]
rows
]
]
SELECT is the most complex query in SQL, and is used to perform all data retrieval operations. This query supports the following select_options, which may be specified in any sensible combination simply by listing them, separated by spaces:
ALL , DISTINCT , DISTINCTROW
Any one of these options may be used to specify the treatment of duplicate rows in the result set. ALL (the default) specifies that all duplicate rows appear in the result set, while DISTINCT and DISTINCTROW (they have the same effect) specify that duplicate rows should be eliminated from the result set.
HIGH_PRIORITY
This option does exactly what it says: it assigns a high priority to the SELECT query. Normally, if a query is waiting to update a table, all read-only queries (such as SELECT) must yield to it. A SELECT HIGH_PRIORITY, however, will go first.
STRAIGHT_JOIN
Forces MySQL to join multiple tables specified in the table_references portion of the query in the order specified there. If you think MySQL’s query optimizer is doing it the slow way, this argument lets you override it. See the section called “Joins” for more information.
SQL_BUFFER_RESULT
This option forces MySQL to store the result set in a temporary table. This frees up the tables employed in the query for use by other processes, while the result set is transmitted to the client.
SQL_CACHE
This option instructs MySQL to store the result of this query in the query cache, an area of memory set aside by the server to store the results of frequently run queries so that there’s no need to recalculate them from scratch if the contents of the relevant tables are still the same. MySQL can be configured so that only queries with the SQL_CACHE option are cached. If the query cache is disabled, this option will have no effect.
SQL_NO_CACHE
This option instructs MySQL to avoid storing the result of this query in the query cache (see the previous option). MySQL can be configured so that every query is cached unless it has the SQL_NO_CACHE option. If the query cache is disabled, this option will have no effect.
SQL_CALC_FOUND_ROWS
For use in conjunction with a LIMIT clause, this option calculates and sets aside the total number of rows that would be returned from the query if no LIMIT clause were present. You can then retrieve this number using SELECT FOUND_ROWS() (see Appendix C).
select_expression defines a column of the result set to be returned by the query. Typically, this is a table column name, and may be specified as col_name , tbl_name.col_name , or db_name.tbl_name.col_name , depending on how specific you need to be for MySQL to identify the column that you’re referring to. select_expressions can refer to other expressions apart from the database column; simple mathematical formulas including column names as variables, and complex expressions calculated with MySQL functions may also be used. Here’s an example of the latter, which will give the date one month from now in the form “January 1, 2010”:
SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%M %D,
%Y')
select_expressions may also contain an alias or assigned name for the result column, if the expression is followed with [AS] alias (the AS is entirely optional). This expression must be used when referring to that column elsewhere in the query (for example, in WHERE and ORDER BY clauses), as follows:
SELECT jokedate AS jd FROM joke ORDER BY jd ASC
MySQL lets you use an INTO clause to output the results of a query into a file instead of returning them to the client. The most typical use of this clause is to export the contents of a table into a text file containing comma-separated values (CSV). Here’s an example:
SELECT * INTO OUTFILE '/home/user/myTable.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM myTable
The file to which the results are dumped must not exist beforehand, or this query will fail. This restriction prevents an SQL query from being used to overwrite critical operating system files. The created file will also be world-readable on systems that support file security, so consider this before you export sensitive data to a text file that anyone on the system can read. DUMPFILE may be used instead of OUTFILE to write only a single row to the file, without row or column delimiters. It can be used, for example, to dump a BLOB stored in the table to a file (SELECT blobCol INTO DUMPFILE …). For complete information on the INTO clause, see the MySQL Reference Manual. For information on reading data back from a text file, see the section called “ LOAD DATA INFILE ”. The FROM clause contains a list of tables from which the rows composing the result set should be formed, along with instructions on how they should be joined together. At its most basic, table_references is the name of a single database table, which may be assigned an alias with or without using AS as described for select_expression beforehand. If you specify more than one table name, you’re performing a join. These are discussed in the section called “Joins” shortly. The where_definition in the WHERE clause sets the condition for a row to be included in the table of results sent in response to the SELECT query. This may be a simple condition (for example, id = 5), or a complex expression that makes use of MySQL functions and combines multiple conditions using Boolean operators (AND, OR, NOT). The GROUP BY clause lets you specify one or more columns (by name, alias, or column position, where 1 is the first column in the result set) for which rows with equal values should be collapsed into single rows in the result set. This clause should normally be used in combination with the MySQL grouping functions such as COUNT, MAX, and AVG, described in Appendix C, to produce result columns that give summary information about the groups created. By default, the grouped results are sorted in ascending order of the grouped column(s); however, the ASC or DESC argument may be added following each column reference to explicitly sort that column’s results in ascending or descending order, respectively. Results are sorted by the first column listed, then tying sets of rows are sorted by the second, and so on. Note that the WHERE clause is processed before GROUP BY grouping occurs, so conditions in the WHERE clause may not refer to columns that depend on the grouping operation. To impose conditions on the post-grouping result set, you should use the HAVING clause instead. This clause’s syntax is identical to that of the WHERE clause, except the conditions specified here are processed just prior to returning the set of results, and are not optimized. For this reason, you should use the WHERE clause whenever possible. For more information on GROUP BY and the HAVING clause, see Chapter 11. The ORDER BY clause lets you sort results according to the values in one or more rows before they’re returned. As for the GROUP BY clause, each column may be identified by a column name, alias, or position (where 1 is the first column in the result set), and each column may have an ASC or DESC argument to specify that sorting occurs in ascending or descending order, respectively (ascending is the default). Rows are sorted initially by the first column listed, then tying sets of rows are sorted by the second, and so on. The LIMIT clause instructs the query to return only a portion of the results it would normally generate. In the simplest case, LIMIT n returns only the first n rows of the complete result set. You can also specify an offset by using the form LIMIT x, n . In this case, up to nrows will be returned, beginning from the x th row of the complete result set. The first row corresponds to x = 0, the second to x = 1, and so on.
Joins
As recently described, the FROM clause of a SELECT query lets you specify the tables that are combined to create the result set. When multiple tables are combined in this way, it’s called a join. MySQL supports several types of joins, as defined by the following supported syntaxes for thetable_references component of the FROM clause:
table_ref
table_references, table_ref
table_references
[CROSS] JOIN table_ref
table_references INNER JOIN table_ref
join_condition
table_references STRAIGHT_JOIN table_ref
table_references LEFT [OUTER] JOIN table_ref
join_condition
{ OJ table_ref LEFT OUTER JOIN table_ref ON cond_expr }
table_references NATURAL [LEFT [OUTER]
] JOIN table_ref
table_references RIGHT [OUTER] JOIN table_ref
join_condition
table_references NATURAL [RIGHT [OUTER]
] JOIN table_ref
where table_ref is defined as:
table_name [
[AS] alias]
[USE INDEX (key_list)]
[IGNORE INDEX (key_list)]
and join_condition is defined as one of the following:
ON cond_expr
USING (column_list)
Don’t be disheartened by the sheer variety of join types; I’ll be explaining how each of them works. The most basic type of join, an inner join, produces rows made up of all possible pairings of the rows from the first table with the second. You can perform an inner join in MySQL either by separating the table names with a comma (,) or with the words JOIN, CROSS JOIN, or INNER JOIN (these are all equivalent). It’s common—especially in older PHP code—to use the comma (,) form to create an inner join, and then use the WHERE clause of the SELECT query to specify a condition, in order to narrow down which of the combined rows are actually returned (for example, to match up a primary key in the first table with a column in the second); however, this is generally considered untidy and bad practice today. Instead, the INNER JOIN syntax followed by a join_conditionshould be used. The ON form of the join_condition puts the condition(s) required to join two tables right next to the names of those tables, keeping the WHERE clause for conditions unrelated to the join operations. As a final alternative, the USING (column_list) form of join_condition lets you specify columns that must match between the two tables. For example:
SELECT * FROM t1 INNER JOIN t2 USING (tid)
This is equivalent to:
SELECT * FROM t1 INNER JOIN t2 ON t1.tid = t2.tid
STRAIGHT_JOIN works in the same way as an inner join, except that the tables are processed in the order listed (left first, then right). Normally, MySQL selects the order that will produce the shortest processing time, but if you think you know better, you can use a STRAIGHT_JOIN. The second type of join is an outer join, which is accomplished in MySQL with LEFT/RIGHT [OUTER] JOIN (OUTER is completely optional, and has no effect). In a LEFT outer join, any row in the left-hand table that has no matching rows in the right-hand table (as defined by the join_condition), will be listed as a single row in the result set. NULL values will appear in all the columns that come from the right-hand table. The { OJ … } syntax is equivalent to a standard left outer join; it’s included for compatibility with other ODBC (Open Database Connectivity) databases. RIGHT outer joins work in the same way as LEFT outer joins, except in this case, it’s the table on the right whose entries are always included, even if they lack a matching entry in the left-hand table. Since RIGHT outer joins are nonstandard, it’s usually best to stick to LEFT outer joins for cross-database compatibility. For some practical examples of outer joins and their uses, see Chapter 11. Natural joins are “automatic” in that they automatically match up rows based on column names that are found to match between the two tables. Thus, if a table called joke has an authorid column referring to entries in an author table whose primary key is another authorid column, you can perform a join of these two tables on that column very simply (assuming there are no other columns with identical names in the two tables):
SELECT * FROM joke NATURAL JOIN author
Unions
A union combines the results from a number of SELECT queries to produce a single result set. Each of the queries must produce the same number of columns, and these columns must be of the same type. The column names produced by the first query are used for the union’s result set:
SELECT …
UNION [ALL | DISTINCT]
SELECT …
[UNION [ALL | DISTINCT]
SELECT …] …
By default, duplicate result rows in the union will be eliminated so that each row in the result set is unique. The DISTINCT option can be used to make this clear, but it has no actual effect. The ALL option, on the other hand, allows duplicate results through to the final result set.
SET
SET option = value, …
The SET query allows you to set a number of options both on your client and on the server. For example, you can SET autocommit = 0 to disable autocommit mode for the current session. In effect, this is like running START TRANSACTION, and then running it again automatically after everyCOMMIT or ROLLBACK. With autocommit off, you always have a transaction open, and queries like INSERT, UPDATE, and DELETE will not take effect until you COMMIT them. For a complete list of the options that may be SET, refer to the MySQL Reference Manual.
SHOW
The SHOW query may be used in a number of forms to obtain information about the MySQL server, the databases, and the tables it contains. Many of these forms have an optional LIKE wild component, where wild is a string that may contain wildcard characters (% for multiple characters, _ for just one) to filter the list of results. Each of the forms of the SHOW query are described here:
SHOW DATABASES [LIKE wild ]
This query lists the databases that are available on the MySQL server.
SHOW [OPEN] TABLES [FROM db_name ] [LIKE wild ]
This query lists the tables (or, optionally, the currently OPEN tables) in the default or specified database.
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name ] [LIKE wild ]
When FULL is not used, this query provides the same information as a DESCRIBE query (see the section called “ DESCRIBE/DESC ”). The FULL option adds a listing of the privileges you have on each column to this information. SHOW FIELDS is equivalent to SHOW COLUMNS.
SHOW INDEX FROM tbl_name [FROM db_name ]
This query provides detailed information about the indexes that are defined on the specified table. See the MySQL Reference Manual for a guide to the results produced by this query. SHOW KEYS is equivalent to SHOW INDEX.
SHOW TABLE STATUS [FROM db_name ] [LIKE wild ]
This query displays detailed information about the tables in the specified or default database.
SHOW STATUS [LIKE wild ]
This query displays detailed statistics for the server. See the MySQL Reference Manual for details on the meaning of each figure.
SHOW VARIABLES [LIKE wild ]
This query lists the MySQL configuration variables and their settings. See the MySQL Reference Manual for a complete description of these options.
SHOW [FULL] PROCESSLIST
This query displays all threads running on the MySQL server and the queries being executed by each. If you lack the process privilege, you’ll only see threads executing your own queries. The FULL option causes complete queries to be displayed, rather than only the first 100 characters of each (the default).
SHOW GRANTS FOR user
This query lists the GRANT queries that would be required to recreate the privileges of the specified user.
SHOW CREATE TABLE table_name
This query displays the CREATE TABLE query that would be required to reproduce the specified table.
START TRANSACTION
START TRANSACTION
Once a transaction has been started (that is, autocommit mode has been disabled) with START TRANSACTION, MySQL collects the changes made to the database so that they may be applied all at once (with COMMIT) or discarded (with ROLLBACK).
TRUNCATE
TRUNCATE [TABLE]
tbl_name
A TRUNCATE command deletes all the rows in a table, just like a DELETE command with no WHERE clause. TRUNCATE, however, takes a number of shortcuts to make the process go much faster, especially with large tables. In effect, TRUNCATE performs a DROP TABLE query, followed by a CREATE TABLEquery to re-create an empty table.
UPDATE
UPDATE [LOW_PRIORITY]
[IGNORE]
tbl_name
SET col_name = expr
[, …]
[WHERE where_definition
]
[ORDER BY …]
[LIMIT #
]
The UPDATE query updates existing table entries by assigning new values to the specified columns. Columns that aren’t listed are left alone, except columns with the TIMESTAMP type (see Appendix D). The WHERE clause lets you specify a condition (where_definition) that rows must satisfy if they’re to be updated, while the LIMIT clause lets you specify a maximum number of rows to be updated.
Warning: Avoid Omitting WHERE or LIMIT
If WHERE and LIMIT are unspecified, every row in the table will be updated!
The ORDER BY clause lets you specify the order in which entries are updated. This is most useful when combined with the LIMIT clause; together they let you create queries like “update the ten most recent rows.” An UPDATE operation will fail with an error if the new value assigned to a row clashes with an existing value in a PRIMARY KEY or UNIQUE column, unless the IGNORE option is specified; in this case the query will simply have no effect on that particular row. The LOW_PRIORITY option instructs MySQL to wait until there are no other clients reading the table before it performs the update. Like the DELETE query (see the section called “ DELETE ”), UPDATE has an alternate form that can affect multiple tables in a single operation:
UPDATE [LOW_PRIORITY]
[IGNORE]
tbl_name
[, tbl_name …]
SET col_name = expr
[, …]
[WHERE where_definition
]
USE
USE db_name
This simple query sets the default database for MySQL queries in the current session. Tables in other databases may still be accessed as db_name.tbl_name .