4.2. Statements and Clauses in Alphabetical Order


The following is a list of MySQL statements and clauses in alphabetical order. Each statement is given with its syntax and an explanation. Optional clauses and flags are shown in square brackets. Particular components, such as a database or table name, are shown in italic. The vertical bar is used to separate alternative choices and is not part of the statement syntax.

Some statements have alternative structures to their syntax. Those alternatives are usually shown in complete form. The curly braces indicate that one of the choices is required. Examples are provided to show how a statement and the various clauses may be used for almost all statements. The examples involve a fictitious database for a computer consulting firm that maintains work requests for computer maintenance. Some examples involve a fictitious database of a vendor.

To save space, almost all of the examples are shown without their results. To be able to focus on the particulars of the statements and clauses, the statements are fairly straightforward and do not make much use of the many built-in functions available with MySQL. Explanations of any functions used, though, can be found in other chapters.

ALTER DATABASE

ALTER DATABASE database      [DEFAULT] CHARACTER SET character_set |     [DEFAULT] CHARACTER SET character_set |     [DEFAULT] COLLATE collation

Use this statement to alter settings for a database. Version 4.1.1 introduced this function and added a file named db.opt containing the database settings to the database directory. Currently, two options are available: CHARACTER SET and COLLATE. Here are the contents of a typical db.opt file:

default-character-set=latin1 default-collation=latin1_swedish_ci

The CHARACTER SET option sets the first line, which specifies the default database character set that will be used. The COLLATE option sets the second line, which specifies the default database collation. Here's an example of how you can use this statement:

ALTER DATABASE human_resources    CHARACTER SET latin2_bin    COLLATE latin2_bin;

Notice that both options may be given in one SQL statement. The DEFAULT keyword is unnecessary, but is offered for compatibility with other database systems. Beginning with Version 4.1.8 of MySQL, if the name of the database is omitted from this SQL statement, the current database will be assumed.

ALTER TABLE

ALTER [IGNORE] TABLE table changes[,  . . . ]

Use this statement to change an existing table's structure and other properties. Alterations to a table can include adding a new column (see the ADD clauses that follow), changing an existing column (see the ALTER, CHANGE, and MODIFY clauses), deleting a column or index (see the DROP clauses), and miscellaneous other tablewide settings. The IGNORE flag applies to all clauses and instructs MySQL to ignore any error messages regarding duplicate rows that may occur as a result of a column change. It will keep the first unique row found and drop any duplicate rows. Otherwise, the statement will be terminated and changes rolled back.

The following are the syntax and explanations of each clause, with examples, grouped by type of clause. Multiple alterations may be combined in a single ALTER TABLE statement. They must be separated by commas and each clause must include the minimally required elements.

ADD clauses for columns and indexes

ALTER [IGNORE] TABLE table ADD [COLUMN] column definition [FIRST|AFTER column] ADD INDEX [index] [USING type] (column,...) ADD [FULLTEXT|SPATIAL] [index] (column,...)

These clauses add columns and indexes to a table. The first syntax adds a new column to a table. The same column definition that would be used in a CREATE TABLE statement is used in this syntax and in several others found with the ALTER TABLE statement. Basically, the name of the column is given, followed by the column datatype and the default value or other relevant components of a column desired. The COLUMN keyword is shown but is not necessary.

By default, a column that is added is appended to the end of the table definition. To insert a new column at the beginning of a table, use the FIRST keyword at the end of the ADD COLUMN clause. To insert it after a particular existing column, use the AFTER keyword followed by the name of the column after which the new column is to be inserted:

ALTER TABLE workreq    ADD COLUMN req_type CHAR(4) AFTER req_date,    ADD COLUMN priority CHAR(4) AFTER req_date;

In this example, two columns are to be added after the existing req_date column. When req_type is added, it will be placed after req_date. When the column called priority is added, it will be added after req_date and before req_type.

To add an index to a table, you can use the ADD INDEX clause. The name of the index may be given with this clause. If it's not given, though, the first column name on which the index is based will be used for the index name, as well. The type of index may be stated, but usually it's not necessary, because there's usually only one choice for most table types. The names of one or more columns for indexing must be given within parentheses, separated by commas. Here is an example of how you can add an index using the ALTER TABLE statement:

ALTER TABLE clients    ADD INDEX client_index    USING BTREE (client_name(10), city(5));

This index is to be added to the table clients and is to be called client_index. The index is based on two columns: the first 10 characters of the client_name column and the first five characters of the city column. Limiting the number of characters used in the index makes for a smaller index, which will be faster and probably just as accurate as using the complete column widths. This table is a MyISAM table, so the only index type currently allowed is a BTREE index. However, starting in Version 5 of MySQL, MyISAM tables allow RTREE types of indexes in addition to BTREE.

The third syntax structure shown previously is used for adding either FULLTEXT or SPATIAL indexes to an existing MyISAM table. A FULLTEXT index can index only CHAR, TEXT, or VARCHAR types of columns. A SPATIAL index can only index spatial columns. Note that the INDEX keyword is omitted from this syntax structure.

ADD clauses for foreign keys

ALTER [IGNORE] TABLE table ADD [CONSTRAINT [symbol]] PRIMARY KEY [type] (column,...) ADD [CONSTRAINT [symbol]] UNIQUE [index] [type] (column,...) ADD [CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)    REFERENCES table (column, ...)    [ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]    [ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]

These three ADD clauses are for use in InnoDB tables and are for adding foreign keys and references. A foreign key is an index to a key or index in another table. See the explanation of the CREATE TABLE statement in this chapter for more information and an example of an SQL statement involving the creation of foreign keys in a table. The various flags shown are also explained under the CREATE TABLE statement.

CHANGE clauses

ALTER [IGNORE] TABLE table ALTER [COLUMN] column {SET DEFAULT value|DROP DEFAULT} CHANGE [COLUMN] column column definition [FIRST|AFTER column] MODIFY [COLUMN] column definition [FIRST|AFTER column]

These three clauses are used to alter an existing column. The first syntax structure is used either to set the default value of a column or to reset it back to its default value. The other two syntax structures are roughly synonymous. They follow the standards of different SQL systems for the sake of compatibility (e.g., MODIFY is used with Oracle). In all three syntax methods, the COLUMN keyword is not necessary. Here's an example of the first clause:

ALTER TABLE clients    ALTER COLUMN city SET DEFAULT 'New Orleans';

This statement sets the default value of the city column in the clients table to a value of New Orleans, because that's where most of the clients are located.

The other two clauses are used primarily to change the column definitions. They can also be used to relocate the column in the table schema with the FIRST or the AFTER keywords, just as the ADD clause does. In the CHANGE clause, the current column name must be specified first, followed by either the same column name or a new column name if the name is to be changed. The column definition for the column is to be given, as well, even if it's not to be changed. The MODIFY clause cannot be used to change a column's name, so the column name is listed only once. The following SQL statement uses both of these clauses to change two columns:

ALTER TABLE clients    CHANGE COLUMN city client_city VARCHAR(100),    MODIFY COLUMN client_state CHAR(4) AFTER client_city;

The first clause changes the name of the city column, and potentially its column type and size. The second clause changes the column type and size and relocates the client_state column to a position after the client_city column. Incidentally, when a column is changed, MySQL will attempt to preserve the data. If a column size is reduced, the data won't be completely deleted, but may be truncated.

DROP clauses

ALTER [IGNORE] TABLE table DROP [COLUMN] column DROP PRIMARY KEY DROP INDEX index DROP FOREIGN KEY foreign_key_symbol

The DROP clauses of the ALTER TABLE statement remove columns or indexes. These clauses delete the data contained within the columns dropped. Here is an example of the first syntax for deleting a column:

ALTER TABLE clients    DROP COLUMN miscellaneous,    DROP COLUMN comments;

This statement deletes two columns. To delete the primary key index of a table, you'd the second syntax structure shown at the beginning of this subsection. If the primary key is based on a column with an AUTO_INCREMENT type, you may need to change the column definition in the same statement like so:

ALTER TABLE clients    CHANGE client_id client_id INT,    DROP PRIMARY KEY;

The CHANGE clause here changes the client_id column from its original type of INT AUTO_INCREMENT to INT. After the AUTO_INCREMENT is removed, the PRIMARY KEY may be dropped.

To drop an index, the third syntax structure is used. To delete a foreign key, the fourth syntax is used. Here is an example of deleting an index:

ALTER TABLE client    DROP INDEX client_name;

In this example, the name of the index is not the name of any of the columns. It's an index that was created by combining two columns and was given a unique name. To get a list of indexes for a table, use the SHOW INDEX statement.

Miscellaneous clauses

ALTER [IGNORE] TABLE table ENABLE|DISABLE KEYS RENAME [TO] table ORDER BY column CONVERT TO CHARACTER SET charset [COLLATE collation] [DEFAULT] CHARACTER SET charset [COLLATE collation] DISCARD|IMPORT TABLESPACE

You can use these miscellaneous clauses with the ALTER TABLE statement. You can use the first clause to enable or disable the updating of nonunique indexes. When running a large number of row inserts, it can be useful to disable indexing afterward. You can use the next clause syntax to change the name of an existing table. Here is an example of this clause:

ALTER TABLE client RENAME TO clients;

This statement renames the client table to clients. The TO keyword, incidentally, is not required. It's a matter of style preference and compatibility.

You can use the next clause syntax structure, the ORDER BY clause, to permanently reorder the rows in a given table. Note that after an ALTER TABLE statement any new rows inserted will be added to the end of the table and the table will not be reordered automatically. The ALTER TABLE statement will need to be run again with this clause. Therefore, the ORDER BY clause with SELECT statements is recommended. Here's an example of this clause:

ALTER TABLE programmers    ORDER BY programmer_name;

You can use the next two syntaxes to change the character set and collation for tables. When a table is first created with the CREATE TABLE statement, unless specified otherwise, a default character set and collation are used. To see the character set and collation for a particular table, use the SHOW TABLE STATUS statement. The following example shows how to convert a table's character set:

ALTER TABLE clients    CONVERT TO CHARACTER SET latin2 COLLATE latin2_bin,    DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

The first clause in this example converts the data in the clients table from its default of latin1 to latin2. The second clause sets the new default for the table to latin2, as well. The CONVERT clause may cause problems with the data. Be sure to make a backup copy before experimenting with this clause.

InnoDB tables use table-spaces instead of individual files for each table. A table-space can involve multiple files and can allow a table to exceed the filesystem file limit as a result. You can use the last clause shown for the ALTER TABLE statement to delete or to import a table-space.

ALTER TABLE workreq    IMPORT TABLESPACE;

This statement will import the .idb file if it's in the database's directory. Replacing the IMPORT keyword with DISCARD will delete the .idb file.

Table options

ALTER TABLE table options

This clause sets the same table options as those set by the CREATE TABLE statement. You can find a list of them under the explanation for that statement. As an example of their use with this statement, to change the starting point for an AUTO_INCREMENT column, you can enter the following statement:

ALTER TABLE clients    AUTO_INCREMENT=1000;

This statement will set the value of the key column to 1,000 for the next row inserted. You can add more options to this statement in a comma-separated list.

ALTER VIEW

ALTER VIEW view [(column,  . . . )] AS SELECT...

Use this statement to change a view. It's used primarily to change the SELECT statement that determines the view. The new SELECT statement for the view is simply given after the AS keyword. You can change the associated column names for the view by providing the new column names in a comma-separated list within the parentheses following the view's name. Neither the old SELECT statement nor the old column names are to be given. To change a view's name, use the DROP VIEW statement, and then create a new view with the CREATE VIEW statement. View statements are available as of Version 5.0.1 of MySQL.

ANALYZE TABLE

ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table[, ...]

Use this statement to analyze and store the key distribution of a table. It works on BDB, InnoDB, and MyISAM tables. Unless the NO_WRITE_TO_BINLOG option is given, the statement is written to the binary log file. The LOCAL option is synonymous.

ANALYZE TABLE workreq;     +----------------------+---------+----------+----------+ | Table                | Op      | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | workrequests.workreq | analyze | status   | OK       | +----------------------+---------+----------+----------+

The message type results can be status, error, info, or warning. If the table hasn't changed since it was last analyzed, the message text will read "Table is already up to date" and the table won't be analyzed. This statement is equivalent to using myisamchk -a at the command line for MyISAM tables.

BACKUP TABLE

BACKUP TABLE table[, ...] TO '/path'

Use this statement to make a backup copy of a table. You can specify additional tables in a comma-separated list. The absolute path to the directory to which MySQL is to copy files is given in quotes after the TO keyword. Only MyISAM tables work with this statement. This statement has been deprecated. It's recommended that mysqlhotcopy (see Chapter 11) be used until this statement is replaced.

The statement copies the .frm file and the .MYD file, which contain the table structure and the table data, respectively. The .MYI file containing the index is not copied, but will be rebuilt with the RESTORE TABLE statement when restoring the table.

BACKUP TABLE clients TO '/tmp/backup'; +----------------------+--------+----------+----------+ | Table                | Op     | Msg_type | Msg_text | +----------------------+--------+----------+----------+ | workrequests.clients | backup | status   | OK       | +----------------------+--------+----------+----------+

If the backup succeeds, the results will look like the preceding results and two files will be created for each table backed up: a .frm file and a .MYD file. If MySQL does not have the filesystem privileges necessary to write to the backup directory, or if a file with the same name is already in the directory, the backup will fail. The results set will include one row with an error message type and another with a status type and the message text stating "Operation failed."

CACHE INDEX

CACHE INDEX table[[INDEX|KEY] (index,  . . . ), ...] IN cache

This statement tells MySQL to cache the given indexes to a specific index cache, which can be created with a SET GLOBAL statement. This statement is used only on MyISAM tables. Multiple tables may be listed in a comma-separated list. To specify only certain indexes of a table, they may be given in a comma-separated list in parentheses after the table name. The INDEX or KEY keyword may be given for clarity and compatibility. Note that the naming of specific indexes for a table is ignored in the current versions of MySQL. This option is for a future release. For now, all indexes are assigned to the named cache, which is the same as specifying no indexes.

To create an additional cache, issue a SET GLOBAL statement with the key_buffer_size variable like this:

SET GLOBAL my_cache.key_buffer_size = 100*1024; CACHE INDEX workreq, clients IN my_cache\G *************************** 1. row ***************************    Table: workrequests.workreq       Op: assign_to_keycache Msg_type: status Msg_text: OK *************************** 2. row ***************************    Table: workrequests.clients       Op: assign_to_keycache Msg_type: status Msg_text: OK

In this example, a cache called my_cache is created in the first line with a buffer size of 100 megabytes. The second line assigns the indexes for the two tables named to my_cache. As long as this cache exists, all queries by all users will use this cache. If the key cache is eliminated for any reason, the indexes will be assigned back to the default key cache for the server.

CHANGE MASTER TO

CHANGE MASTER TO option[,  . . . ]
      MASTER_HOST = 'host'       MASTER_USER = 'user'       MASTER_PASSWORD = 'password'       MASTER_PORT = port       MASTER_CONNECT_RETRY = count       MASTER_LOG_FILE = 'filename'       MASTER_LOG_POS = position       RELAY_LOG_FILE = 'filename'       RELAY_LOG_POS = position       MASTER_SSL = {0|1}       MASTER_SSL_CA = 'filename'       MASTER_SSL_CAPATH = 'path'       MASTER_SSL_CERT = 'filename'       MASTER_SSL_KEY = 'filename'       MASTER_SSL_CIPHER = 'list'

Use this statement to change several properties regarding connections to the master MySQL server. Some of the variables relate to connecting to the server and some relate to master log files and the current position in the logfiles. This statement is run from the slave. If the slave is running, it may be necessary to use the STOP SLAVE statement before using this statement, and the START SLAVE statement afterward.

The following SQL statement sets several properties for this slave:

CHANGE MASTER TO    MASTER_HOST='mysql.company.com',    MASTER_PORT=3306,    MASTER_USER='slave_server',    MASTER_PASSWORD='password',    MASTER_CONNECT_RETRY=5;

The log clauses for this statement are used to name the master logfiles and to provide the slave with the current position of the master logfiles. This may be necessary when first setting up a new slave or when a slave has been disabled for awhile. Use the SHOW MASTER STATUS statement to determine the current position of the master logfiles. Use the SHOW SLAVE STATUS statement to confirm a slave's position for the related files. Here is an example using these clauses:

CHANGE MASTER TO    MASTER_LOG_FILE= 'log-bin.000153',    MASTER_LOG_POS = 79,    RELAY_LOG_FILE = 'log-relay.000153',    RELAY_LOG_POS = 112;

The remaining clauses set various secure socket layer (SSL) variables. These values are saved to the master.info file. To see the current values for these options, use the SHOW SLAVE STATUS statement. Relay log options are available as of Version 4.1.1 of MySQL. The MASTER_SSL variable is set to 0 if the master does not allow SSL connections, and 1 if it does. The MASTER_SSL_CA variable holds the name of the file that contains a list of trusted CAs. MASTER_SSL_CAPATH contains the absolute path to that file. The MASTER_SSL_CERT variable specifies the name of the SSL certificate file for secure connections, and MASTER_SSL_KEY specifies the SSL key file used to negotiate secure connections. Finally, MASTER_SSL_CIPHER provides a list of acceptable cipher methods for encryption.

CHECK TABLE

CHECK TABLE table[,  . . . ] [CHANGED|QUICK|FAST|MEDIUM|EXTENDED|]

Use this statement to check MyISAM and InnoDB tables for errors. If errors are discovered, you should run the REPAIR TABLE statement to repair the table. Multiple tables may be given in a comma-separated list. There are several methods of checking. The CHANGED method checks only tables that have been changed since the last check. The QUICK option will check tables for errors, but won't scan individual rows for linking problems. The FAST option instructs MySQL to check only tables that have not been closed properly. The MEDIUM option determines the key checksum for the rows and compares the results against the checksum for the keys. It also checks rows to ensure that links were deleted properly. The EXTENDED method thoroughly checks each row for errors. Here is an example of how you can use this statement.

CHECK TABLE workreq MEDIUM; +----------------------+-------+----------+----------+ | Table                | Op    | Msg_type | Msg_text | +----------------------+-------+----------+----------+ | workrequests.workreq | check | status   | OK       | +----------------------+-------+----------+----------+

CHECKSUM TABLE

CHECKSUM TABLE table[,  . . . ] [QUICK|EXTENDED]

Use this statement to determine a table's checksum value. Multiple tables may be given in a comma-separated list. If the QUICK option is employed, the live table checksum will be returned, if available. If not, NULL will be returned. You would tend to use QUICK when the table is probably fine. To enable live checksum for a table, use the ALTER TABLE statement with a table option of CHECKSUM=1. The EXTENDED option instructs the server to check each row. You should use this option only as a last resort. If no option is specified, the QUICK option is the default, if available. Otherwise, the EXTENDED option is the default. Here is an example of this statement's use and its results:

CHECKSUM TABLE workreq; +----------------------+-----------+ | Table                | Checksum  | +----------------------+-----------+ | workrequests.workreq | 195953487 | +----------------------+-----------+

COMMIT

COMMIT

Use this statement to commit transactions in an InnoDB or a BDB table. If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful. To do this, set the value of AUTOCOMMIT to 0 with the SET statement. AUTOCOMMIT will also be disabled with the use of the START TRANSACTION statement and reinstated with the COMMIT statement. Here is an example of this statement:

START TRANSACTION; LOCK TABLES orders WRITE; INSERT DATA INFILE '/tmp/customer_orders.sql'   INTO TABLE orders; SELECT ...; COMMIT; UNLOCK TABLES;

In this example, after inserting a batch of orders into the orders table, an administrator enters a series of SELECT statements to check the integrity of the data. They are omitted here to save space. If there is a problem, the ROLLBACK statement could be issued rather than the COMMIT statement shown here. ROLLBACK would remove the data imported by the INSERT DATA INFILE statement. The ROLLBACK statement works only with InnoDB and BDB tables. If everything seems alright, the COMMIT statement would be issued to commit the transactions.

CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] database [options]

This statement creates a new database with the name given. You can use the IF NOT EXISTS flag to suppress an error message when the statement fails if a database with the same name already exists. As of Version 4.1.1, a db.opt file is added to the filesystem subdirectory created for the database in the MySQL server's data directory. This file contains a couple of settings for the database. You can specify these settings as options to this SQL statement in a comma-separated list.

Currently, two options are available: CHARACTER SET and COLLATE. Here is an example of how you can create a database with both of these options:

CREATE DATABASE db1    CHARACTER SET latin1    COLLATE latin1_bin;

Unlike most lists of options, there is no comma between the first option and the second option. Below is the contents of the db.opt file created for this statement:

default-character-set=latin1 default-collation=latin1_bin

For a list of character sets, use the SHOW CHARACTER SET statement. For a list of collation possibilities, use the SHOW COLLATION statement.

CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index     [USING type|TYPE type]     [USING type|TYPE type]     ON table (column [(length)] [ASC|DESC],  . . . )

Use this statement to add an index to a table after it has been created. This is an alias of the ALTER TABLE statement that adds an index. You can add indexes only to MyISAM, InnoDB, and BDB types of tables. You can also create these tables with indexes, as shown under the CREATE TABLE statement.

To prevent duplicates, add the UNIQUE flag between the CREATE keyword and INDEX. Only columns with CHAR, TEXT, and VARCHAR datatypes of MyISAM tables can be indexed with FULLTEXT indexes. SPATIAL indexes can index spatial columns only in MyISAM tables. This is available starting with Version 4.1 of MySQL.

After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed, or a totally new name.

You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default and only choice currently. The RTREE type will be available as of Version 5 of MySQL. The TYPE keyword is an alias for USING.

For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.

Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending order, these are for a future release of MySQL. All indexes are currently sorted in ascending order. Additional columns for indexing may be given within the parentheses.

CREATE UNIQUE INDEX client_name    ON clients (client_lastname, client_firstname(4), rec_date);

In this example, an index is created called client_name. It is based on the last names of clients, the first four letters of their first names, and the dates that the records were created. This index is based on it being unlikely that a record would be created on the same day for two people with the same last name and a first name starting with the same four letters.

To see the indexes that have been created for a table, use the SHOW INDEX statement. To remove an index, use the DROP INDEX statement.

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table  {[(definition)][options]|[[AS] SELECT...]|[LIKE table]}

Use this statement to create a new table within a database. This statement has many clauses and options. However, when creating a basic table, you can omit many of them. You can use the TEMPORARY keyword to create a temporary table that is used only for the current connection thread and not accessible by other users. You can use the IF NOT EXISTS flag to suppress error messages caused by attempting to create a table by the same name as an existing one. After the table name is given, either the table definition is given (i.e., a list of columns and their datatypes) along with table options or properties, or a table can be created based on another table. You create a table based on the schema of another table with a SELECT statement or with a LIKE clause. These two possibilities are covered at the end of this statement's explanation. Here is a simple example of how you can use this statement:

CREATE TABLE clients    (client_id INT AUTO_INCREMENT PRIMARY KEY,     client_name VARCHAR(75), telephone CHAR(15));

This creates a table with three columns. The first column is called client_id and may contain integers. It will be incremented automatically as records are created. It will also be the primary key field for records, which means that no duplicates are allowed and that the rows will be indexed off of this column. The second column, client_name, is a variable-width, character-type column with a maximum width of 75 characters. The third column is called telephone and is a fixed-width, character-type column with a minimum and maximum width of 15 characters. To see the results of this statement, you can use a DESCRIBE statement. Many column datatypes are available. They are described in Appendix A.

Column flags

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table  (column type[(width)] [ASC|DESC] [NOT NULL|NULL] [DEFAULT value]    [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string'] [,...]) [options]

This is the syntax for the CREATE TABLE statement again, but detailing the column flags portion of the column definition. For some column types, you may need to specify the size of the column within parentheses after the column name and column type.

If a column is indexed, the keyword ASC or DESC may be given next to indicate whether indexes should be stored in ascending or descending order, respectively. By default, they are stored in ascending order. For the current and past versions of MySQL, these flags are ignored. Adding the NOT NULL flag indicates the column may not be NULL. The NULL flag may be given to state that a NULL value is allowed. To set a default value for a column, you can use the DEFAULT keyword. The AUTO_INCREMENT option has MySQL assign a unique identification number automatically to a column. If a column is to be the basis of an index, either PRIMARY KEY or just KEY can be given.

As a reference for an administrator or a developer, a comment regarding a column may be given. It won't be displayed in the results of a SELECT statement, but it will be revealed with a SHOW FULL COLUMNS statement. To add a comment, use the COMMENT keyword followed by a string within quotes. Here is an example using some of the flags and clauses mentioned here:

CREATE TABLE clients    (client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,    client_name VARCHAR(75),    client_city VARCHAR(50) DEFAULT 'New Orleans',    telephone CHAR(15) COMMENT 'Format: ###-###-####');

In this example, the client_id column is a primary key. The NOT NULL option is included for completeness, even though it's not necessary, because a primary key must be unique and non-NULL. For the client_city column, the DEFAULT clause is used to provide the default value of the column. It will be used when no value is given, although you can override it by specifying an explicit blank value for the column. This statement also includes a comment regarding the typical format for entering telephone numbers in the telephone column. Again, this will be displayed only with the SHOW FULL COLUMNS statement.

Index and key definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table  (column, ..., index type[(width)] [ASC|DESC] |[CONSTRAINT [symbol]] PRIMARY KEY [type] (column,...) |INDEX|[PRIMARY] KEY [index] [type] (column,...) |[CONSTRAINT [symbol]] UNIQUE [INDEX] [index] [type] (column,...) |[FULLTEXT|SPATIAL] [INDEX] [index] (column,...) |[CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)     [reference_definition] |CHECK (expression)] [,...]) [options]

You can use one or more columns for an index, and a table can contain multiple indexes. Indexes can greatly increase the speed of data retrieval from a table. You can define an index involving multiple columns with this statement, or later with the ALTER TABLE statement or the CREATE INDEX statement. With the CREATE TABLE statement, though, indexes can be given after the definition of the columns they index.

A key (also called a primary key) is a particular kind of index obeying certain constraints: it must be unique, for instance. It often is combined in MySQL with the AUTO_INCREMENT keyword, and it is often used for identifiers that appear as columns in tables. The general format is to specify the type of index, such as KEY, INDEX, or UNIQUE. This is followed by the index name. Optionally, the index type may be specified with the USING keyword. For most tables there is only one type of index, so this is unnecessary. Before Version 5 of MySQL, BTREE is the only type for MyISAM tables. Beginning with Version 5, the RTREE index type also will be available, so you may want to specify the index type. After the index type, one or more columns for which the index is based are listed within parentheses, separated by commas. Before explaining the various possibilities, let's look at an example:

CREATE TABLE clients    (client_id INT AUTO_INCREMENT KEY,    name_last VARCHAR(50), name_first VARCHAR(50),    telephone CHAR(15),    INDEX names USING BTREE (name_last(5), name_first(5) DESC));

In this example, the client_id column is a PRIMARY KEY, although it has been abbreviated to just KEY. This abbreviation is available as of Version 4.1 of MySQL. There can be only one PRIMARY KEY, but several other indexes. The table contains a second index using the first five characters of the two name columns. To do a combination, the index definition is generally given at the end of the table's column definitions with the INDEX keyword. The index is named names in the example. After the index name, the type of index to be used is specified with the USING clause. Currently, this is unnecessary because BTREE is the default type for a MyISAM table. Next, the two columns to index are named in parentheses. The name columns are variable-width columns and 50 characters in length, so to speed up indexing only the first five characters of each column are to be used. The name_first column is supposed to be used in descending order per the DESC flag. However, this will be ignored for the current version of MySQL.

The syntax structures for the index clauses listed here vary depending on the type of table index to be created: PRIMARY KEY, INDEX, UNIQUE, FULLTEXT (or BLOB column types), or SPATIAL.

For creating constraints on tables based on columns in another table, you would use the FOREIGN KEY index syntax structures. Foreign keys are used only between InnoDB tables. The CHECK clause is not used in MySQL but is available for porting to applications for other database systems. Here is an example of how you can use foreign keys to create a table:

CREATE TABLE employees    (rec_id INT NOT NULL PRIMARY KEY,     name_last VARCHAR(25), name_first VARCHAR(25)) TYPE = INNODB; CREATE TABLE programmers    (rec_id INT, emp_id INT,     INDEX (emp_id),     FOREIGN KEY (emp_id) REFERENCES employees(rec_id)     ON DELETE CASCADE) TYPE=INNODB;

The first SQL statement creates a table for basic information on employees. The second table creates a simple table of programmers. In the employees table, the key column rec_id will be used to identify employees and will be the foreign key for the programmers table. The programmers table sets up an index based on emp_id, which will be tied to the rec_id column in the employees table. The FOREIGN KEY clause establishes this connection using the REFERENCES keyword to indicate the employees table and the key column in that table to use in that table. Additionally, the ON DELETE CASCADE clause instructs MySQL to delete the row in the programmers table for a programmer that is deleted from the employees table. The next subsection gives the syntax for references to foreign keys and the meaning of each component. At the end of both of these SQL statements, the table type is set to InnoDB with the TYPE clause. The ENGINE keyword could have been used instead.

References

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table  (column, ..., index type[(width)] [ASC|DESC] [CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...) REFERENCES table [(column,...)]    [MATCH FULL|MATCH PARTIAL]    [ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT]]    [ON UPDATE [RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT]] [,...]) [options]

This subsection describes the FOREIGN KEY clause, which creates a relationship between an index and another table. The MATCH FULL clause requires that the reference match on the full width of each column indexed. MATCH PARTIAL allows for partial columns to be used. Partial columns can accelerate indexing only when the first few characters of a column are necessary for determining the uniqueness of a row. The ON DELETE clause instructs MySQL to react to deletions of matching rows from the foreign table according to the option that follows. The ON UPDATE clause has MySQL respond to updates made to the referenced table according to the options that follow it. You can use both clauses in the same CREATE TABLE statement.

The RESTRICT keyword option instructs MySQL not to allow the deletion or updating (depending on the clause in which it's used) of the rows in the foreign table if rows in the current table are linked to them. The CASCADE keyword says that when deleting or updating the rows that are referenced in the parent table, delete or update the related rows in the child table accordingly (as in the last example). SET NULL has MySQL change the data contained in the related columns to a NULL value. For this to work, the column in the child table must allow NULL values. The NO ACTION setting has MySQL not react to deletions or updates with regard to the referencing table. The SET DEFAULT option would seem to suggest that the referencing column's value would be set to the column's default, but at this time, it does nothing.

Table options

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table  (column, ..., index type[(width)] [ASC|DESC]   {ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM}   AUTO_INCREMENT = value   AVG_ROW_LENGTH = value   CHECKSUM = {0|1}   COMMENT = 'string'   MAX_ROWS = value   MIN_ROWS = value   PACK_KEYS = {0|1|DEFAULT}   PASSWORD = 'string'   DELAY_KEY_WRITE = {0|1}   ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED}   RAID_TYPE = {1|STRIPED|RAID0}       RAID_CHUNKS = value       RAID_CHUNKSIZE = value   UNION = (table[,...])   INSERT_METHOD = {NO|FIRST|LAST }   DATA DIRECTORY = '/path'   INDEX DIRECTORY = '/path'   [DEFAULT] CHARACTER SET characterset [COLLATE collation] [,...]) [options]

When creating a table, in addition to defining the columns and indexes, you can also set various table properties. You set the type of table also known as the storage engine with a TYPE or ENGINE clause, which are synonymous. By default, if the TYPE clause isn't used, a table will be a MyISAM table type. You can set the default to a different table type with the --default-table-type option when starting the mysqld daemon. See Chapter 10 for more information on this option. The different table types are listed in the syntax for the preceding clause. To see the values for an existing table, use the SHOW TABLE STATUS statement. See the explanation of that statement for the meaning of each variable. To set or change any of these values after a table has been created, use the ALTER TABLE statement.

Create a table based on an existing table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table  LIKE table |[IGNORE|REPLACE] [AS] SELECT...

These two structural syntax choices for the CREATE TABLE statement allow a new table to be created based on an existing table. With the LIKE clause, a table is created based on the structure of the existing table given. For example, suppose a database has a table called employees that contains information on full-time and part-time employees. Suppose further that it has been decided that information on part-time employees should be stored in a separate table. You could execute the following statement to create a new table for part-time employees with the same structure as the existing employees table:

CREATE TABLE part_time_employees    LIKE employees;

This statement results in a new table with the same structure, but without any data. If the table that was copied has a primary key or any indexing, they won't be copied. You can use the CREATE INDEX statement to create an index.

To create a new table based on the structure of an existing table and to copy the data from the old table to the new one, you can enter something like the following statement:

CREATE TABLE part_time_employees   AS SELECT *      FROM employees      WHERE part_time='Y';

In this example, the table structure is copied and the data is copied for rows where the part_time column has a value of `Y', as in yes. (A WHERE clause is optional.) Then you could issue a DELETE statement to delete the rows for part-time employees from the employees table.

You can use the IGNORE keyword before the SELECT statement to instruct MySQL to ignore any error messages regarding duplicate rows, to not insert them, and to proceed with the remaining rows of the SELECT statement. Use the REPLACE keyword instead if duplicate rows are to be replaced in the new table.

CREATE VIEW

CREATE [OR REPLACE] [ALGORITHM = {MERGE|TEMPTABLE}]  VIEW view [(column,  . . . )] AS SELECT...  VIEW view [(column,  . . . )] AS SELECT...  [WITH [CASCADED|LOCAL] CHECK OPTION]

Use this statement to create a view, which is a preset result set in a database. It's available as Version 5.0.1 of MySQL. A view is created based on a given SELECT statement. The view is displayed with a SELECT statement naming the view instead of a table. The OR REPLACE clause may be given to overwrite an existing view with the same name. The name of the view is given immediately after the VIEW keyword and cannot be the same as a table in the database, because they share the same tablespace. To label the column headings for the view's results set, column names may be given in a comma-separated list in parentheses after the view name. The WITH CHECK [CASCADED|LOCAL] OPTION clause is available as of Version 5.0.2 of MySQL. Here is an example of how you can use this statement:

CREATE VIEW employee_directory(ID, Name, Ext.) AS   SELECT emp_id, CONCAT(emp_first, ' ', emp_last), tel_extension   FROM employees;

This SQL statement will create a view that will contain each employee's identification number, the employee's first and last name concatenated together with a space in between, and the employee's office telephone extension. To retrieve this data, enter the following SQL statement:

SELECT * FROM employee_directory LIMIT 1; +------+------------+------+ | ID   | Name       | Ext. | +------+------------+------+ | 1000 | Marie Dyer | 1207 | +------+------------+------+

Notice that the column names are the ones named by the CREATE VIEW statement. This view will be available for all users that have SELECT privileges for the database in which it was created. By default, a view is created in the default database at the time that the CREATE VIEW statement is entered. To create a view in a different database from the default, simply add the database name and a dot as a separator in front of the view name in the CREATE VIEW statement. To delete a view from a database, use the DROP VIEW statement.

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table            [WHERE condition]         [ORDER BY column [ASC|DESC][,  . . . ]] [LIMIT row_count]         DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table[, table]        FROM table[,  . . . ] [WHERE condition]         DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table[, table]        USING table[,  . . . ] [WHERE condition]

Use this statement to delete rows of data from a given table. Three basic syntax structures are allowed. The first one shown here is for one table only. The other two are for multiple tables. For all three, the LOW_PRIORITY flag instructs the server to wait until there are no queries on the table named before deleting rows. The QUICK flag is used with MyISAM tables to make deletions faster by not merging index leaves. The IGNORE flag instructs MySQL to continue even if it encounters errors. You can retrieve error messages afterward with the SHOW WARNINGS statement. You can use the WHERE clause to specify which rows are to be deleted based on a given condition. You can use the DELETE statement in conjunction with the JOIN clause, which is explained later in this chapter.

Here is a simple example of this statement:

DELETE LOW_PRIORITY FROM workreq    WHERE client_id = '1076'       AND status <> 'DONE';

In this example, the client 1076 has closed its account, and management has decided just to delete all of their incomplete work requests. If a WHERE clause is not given, all the rows for the table would be deleted permanently. Row deletions are performed one row at a time with the DELETE statement, so using this statement to delete all rows is slower than when using the trUNCATE TABLE statement. However, the trUNCATE TABLE statement doesn't return the number of rows deleted.

To delete only a certain number of rows in a table, use the ORDER BY clause along with the LIMIT clause. For example, suppose an account executive informed the database administrator that the last four work requests she entered for a particular client (1023) need to be deleted. The database administrator can enter the following to delete those rows:

DELETE FROM workreq   WHERE client_id = '1023'   ORDER BY request_date   LIMIT 4;

In this example, the rows are first ordered by the date of the work request. Additional columns may be given in a comma-separated list for the ordering. The LIMIT clause is used to limit the number of deletions to the first four rows of the results of the WHERE clause and the ORDER BY clause.

The second syntax structure shown earlier for this statement allows other tables to be referenced. In the first example shown here, the rows from the work request table were being deleted based on the client account number. However, if that number wasn't known, but the database administrator knows the client's name begins with Cole, the following could be entered to delete the records instead:

DELETE workreq FROM workreq, clients    WHERE workreq.client_id = clients.client_id    AND client_name LIKE 'Cole%';

In this example, the table in which rows will be deleted is given after the DELETE keyword. It's also given in the list of tables in the FROM clause, which specifies the table from which information will be obtained to determine the rows to delete. The two tables are joined in the WHERE clause on the client identification number column in each. Using the LIKE keyword, the selection of rows is limited to clients with a name beginning with the name Cole. Incidentally, if more than one client has a name beginning with Cole, the rows for both will be deleted from the work request table. You can delete rows in more than one table with a single statement by listing the tables in a comma-separated list after the DELETE keyword. For example, suppose that it has been decided to delete not only the work requests for the client, but also the row for the client in the clients table:

DELETE workreq, clients FROM workreq, clients    WHERE workreq.clientid = clients.clientid    AND client_name LIKE 'Cole%';

Notice that the only syntactical difference between this statement and the one for the previous example is that in this statement both tables for which rows are to be deleted are listed after the DELETE keyword and before the FROM clause. Deletions are permanent, so care should be taken as to which tables are listed for deletion.

The third syntax structure operates in the same way as the second one, but offers a couple of keywords that may be preferred for clarity. If the second-to-last statement example were entered with this third syntax structure, it would look like this:

DELETE FROM workreq USING workreq, clients    WHERE workreq.clientid = clients.clientid    AND client_name LIKE 'Cole%';

Notice that the table for which rows will be deleted is listed in the FROM clause. The tables that the statement will search to obtain information for determining which rows to delete are listed in the USING clause. The results of statements using this syntax structure and the previous one are the same. It's just a matter of style preference and compatibility with other database systems.

DESCRIBE

{DESCRIBE|DESC} table [column]

This statement displays information about the columns of a given table. The DESCRIBE keyword can be abbreviated to DESC. For information on a specific column, give the column name or a naming pattern within quotes to include multiple columns. For instance, to display a list of columns in the workreq table that begin with the characters client_, enter the following:

DESCRIBE workreq 'client_%'; +-------------+------------+------+-----+---------+-------+ | Field       | Type       | Null | Key | Default | Extra | +-------------+------------+------+-----+---------+-------+ | client_id   | varchar(4) | YES  |     | NULL    |       | | client_type | char(1)    | YES  |     | NULL    |       | +-------------+------------+------+-----+---------+-------+

DO

DO expression[,  . . . ] | (statement)

This statement suppresses the results of an expression. Multiple expressions may be given in a comma-separated list. As of Version 4.1 of MySQL, subqueries may be given:

DO  (SET @company = 'Van de Lay Industries' );

This statement creates the @company variable with the value given, but without displaying any results.

DROP DATABASE

DROP DATABASE [IF EXISTS] database

Use this statement to delete a given database along with all its tables and data. The addition of the IF EXISTS flag suppresses an error message if the database does not already exist:

DROP DATABASE IF EXISTS test; Query OK, 6 rows affected (0.42 sec)

Notice that the number of tables deleted is returned in the rows affected count. If the database doesn't exist or if there are other files in the database's filesystem directory, an error message will be displayed. The tables will be deleted if other files exist, but the foreign file and the directory won't be removed. They will have to be deleted manually at the command line using a filesystem command such as rm in Unix or del in Windows.

DROP INDEX

DROP INDEX index ON table

This statement deletes a given index from a table. To determine the name of a particular index, use the SHOW INDEX statement. The key name for the index found in the results of that statement is used with this statement.

DROP INDEX client_name ON contacts;

This statement drops the index with the key name of client_name from the contacts table.

DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS] table[, ...]       [RESTRICT|CASCADE]

Use this statement to delete a table from a database, including its data. You can delete additional tables in the same statement by naming them in a comma-separated list. The addition of the IF EXISTS flag prevents error messages from being displayed if the table doesn't already exist. If the TEMPORARY flag is given, only temporary tables matching the table names given will be deleted. DROP privileges won't be checked with this flag, because temporary tables are visible and usable only by the user of the current session who created the temporary tables. The RESTRICT and CASCADE flags are for future versions and are related to compatibility with other systems.

DROP TABLE IF EXISTS repairs, clientss_old; Query OK, 0 rows affected (0.00 sec) SHOW WARNINGS; +-------+------+------------------------------+ | Level | Code | Message                      | +-------+------+------------------------------+ | Note  | 1051 | Unknown table 'clientss_old' | +-------+------+------------------------------+

In this example, the user tried to instruct MySQL to delete both the repairs and the clients_old tables, but misspelled clients_old. Because the IF EXISTS flag was included, the statement doesn't give an error message. Starting with Version 4.1 of MySQL, a note is created that you can retrieve by issuing a SHOW WARNINGS statement, as shown in this example. Notice that the number of tables deleted is not returned.

DROP USER

DROP USER 'user'@'host'

Use this statement to delete privileges for a user by deleting the row for that user and host, given the user table of the mysql database. This statement won't delete a user that has any privileges set to `Y', though. To be assured that the user has no privileges, issue a REVOKE statement. You will have to issue a separate statement for each host for which the user has privileges.

REVOKE ALL ON *.* FROM 'rstringer'@'localhost'; DROP USER 'rstringer'@'localhost';

The ALL option is used to assure revocation of all privileges. The *.* covers all tables in all databases. Prior to Version 4.1.1 of MySQL, you would have to issue the following instead of a DROP USER statement:

DELETE FROM mysql.user    WHERE User='rstringer' AND Host='localhost'; FLUSH PRIVILEGES;

Notice that the FLUSH PRIVILEGES statement is necessary so that the preceding DELETE statement takes effect immediately. It's not necessary after the DROP USER statement, though.

DROP VIEW

DROP VIEW [IF EXISTS] view[,  . . . ] [RESTRICT|CASCADE]

Use this statement to delete a view. The IF EXISTS flag prevents error messages if the view doesn't exist before attempting to delete it. To retrieve the error message when this flag is added, use the SHOW WARNINGS statement. The RESTRICT or CASCADE options are for a future release of MySQL. This statement is available as of Version 5.0.1 of MySQL.

EXPLAIN

EXPLAIN {table|SELECT...}

You can use this statement to display information about the columns of a given table, or to display information about the results set of a given SELECT statement without displaying the rows of data. For this latter use, it will show which index the statement will use and, when multiple tables are queried, the order in which the tables are used. This can be helpful in determining the cause of a slow query.

FLUSH

FLUSH [LOCAL|NO_WRITE_TO_BINLOG] option[,  . . . ]

Options:

DES_KEY_FILE, HOSTS, LOGS, PRIVILEGES, QUERY_CACHE, STATUS, TABLE, TABLES, TABLES WITHOUT READ LOCK, USER_RESOURCES

Use this statement to clear temporary caches in MySQL. It requires RELOAD privileges. To prevent this statement from writing to the binary log file, the NO_WRITE_TO_BINLOG flag or its LOCAL alias may be given. The cache may be given as an option to the statement to be flushed. Multiple options may be given in a comma-separated list:


DES_KEY_FILE

Reloads the DES encryption file.


HOSTS

Clears the hosts cache, which is used to minimize host/IP address lookups. The hosts cache may need to be flushed if a host has been blocked from accessing the server.


LOGS

Used to close all of the logfiles and reopen them.


PRIVILEGES

Reloads the grant table for users. This is necessary if the user table in the mysql database is modified manually, without a GRANT statement.


QUERY CACHE

Instructs the server to defragment the query cache.


STATUS

Resets the various status variables.


TABLE

Followed by one or more table names, forces the given tables to be closed. This will terminate any active queries on the given tables.


TABLES

Causes all tables to be closed, all queries to be terminated, and the query cache will be flushed, as well. This is the same as TABLE with no table name.


TABLES WITH READ LOCK

Closes all tables and locks them with a read lock. This will allow users to view the data, but not to update it or to insert records. The lock will remain in place until the UNLOCK TABLES statement is executed.


USER_RESOURCES

Resets all user resources. You can use this when users have been locked out due to exceeding usage limits.

GRANT

GRANT privileges [(columns)][,  . . . ] ON database.table TO 'user'*'host' [IDENTIFIED BY [PASSWORD] 'password'][,  . . . ] [REQUIRE NONE|[{SSL|X509}] [CIPHER 'cipher' [AND]] [SUBJECT 'subject']] [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |                       MAX_UPDATES_PER_HOUR count |                       MAX_CONNECTIONS_PER_HOUR count]]

Use this statement to create new MySQL users and to grant user privileges. Privileges can be global (apply to all databases on the server), database-specific, table-specific, or column-specific. User information is stored in the grant tables in the mysql database on the server. Global privileges are stored in the user table. Database-specific privileges are stored in the db table. Table privileges are in the tables_priv table and column privileges are in the columns_priv table. You can edit these tables directly with SQL statements, such as INSERT, UPDATE, and DROP, followed by a FLUSH PRIVILEGES statement to update the server's cache. However, it's recommended that you use the GRANT statement to create users and to grant privileges, and the REVOKE statement to revoke privileges.

In the syntax, the privileges to grant to a user are listed immediately after the GRANT keyword in a comma-separated list. Several privileges may be granted to a user. To give a user all simple user privileges, you can use the ALL keyword. Here is an example of how you can grant privileges to a user:

GRANT ALL PRIVILEGES ON *.*    TO 'tina'@'localhost'    IDENTIFIED BY 'muller'    WITH GRANT OPTION;

In this example, the user tina is created and granted all basic privileges because of the ALL keyword. This does not include GRANT privilege, the ability to use the GRANT statement. To do that, the WITH GRANT OPTION clause is given, as shown here, to explicitly give that privilege to the user. It's not a good idea to give users this privilege unless they are MySQL server administrators. Table 4-1 lists and describes each privilege.

Table 4-1. Privileges in GRANT and REVOKE

Privilege

Description

ALL [PRIVILEGES]

Grants all of the basic privileges. Does not include GRANT OPTION.

ALTER

Allows use of the ALTER TABLE statement.

CREATE

Grants CREATE TABLE statement privileges.

CREATE TEMPORARY TABLES

Allows the CREATE TEMPORARY TABLES statement to be used.

CREATE VIEW

Permits the CREATE VIEW statement. This is for Version 5.0.1 of MySQL.

DELETE

Allows the DELETE statement to be used.

DROP

Permits the user to execute DROP TABLE and TRUNCATE statements.

EXECUTE

Allows the execution of stored procedures. This is available as of Version 5 of MySQL.

FILE

Allows the use of SELECT . . . INTO OUTFILE and LOAD DATA INFILE statements to export and import to and from a filesystem.

GRANT OPTION

Permits the use of the GRANT statement to grant privileges to users.

INDEX

Grants the use of CREATE INDEX and DROP INDEX statements.

INSERT

Permits the use of INSERT statements.

LOCK TABLES

Allows the use of LOCK TABLES statements for tables for which the user has SELECT privileges.

PROCESS

Allows the use of SHOW FULL PROCESSLIST statements.

REFERENCES

This is not used. It's for future releases.

RELOAD

Allows the FLUSH statement to be used.

REPLICATION CLIENT

Allows the user to query master and slave servers for status information.

REPLICATION SLAVE

Required for replication slave servers. Allows binary log events to be read from the master server.

SELECT

Allows the use of the SELECT statement.

SHOW DATABASES

Permits the use of the SHOW DATABASES statement for all databases, not just the ones for which the user has privileges.

SHOW VIEW

Allows the use of the SHOW CREATE VIEW statement. This is for MySQL Version 5.0.1 and above.

SHUTDOWN

Allows the use of the shutdown option with the mysqladmin utility.

SUPER

Grants use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, and the debug option with the command-line utility mysqladmin.

UPDATE

Allows the UPDATE statement to be used.

USAGE

Used to create a user without privileges.


A user's privileges can be refined to specific SQL statements and specific databases. A GRANT statement can also restrict a user only to certain tables and columns. Here is an example of a statement that leaves the user fairly limited:

GRANT SELECT ON workrequests.*    TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186'; GRANT SELECT,INSERT,UPDATE ON workrequests.workreq    TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';

Assuming the user jerry does not already exist, the first statement here creates the user and gives him SELECT privileges only for the workrequests database for all of its tables. This will allow him to read from the various tables but not edit the data. The second SQL statement grants jerry the right to add and to change data in the workreq table of the workrequests database. This will allow him to enter work requests and to make changes to them. The first statement makes an entry in the db table in the mysql database. The second one makes an entry in the tables_priv table. An entry is also made to the user table showing the user jerry but with no global privileges. This is the equivalent of granting just the USAGE privilege.

A user can also be required to communicate through an SSL connection. Various clauses specify the cipher method and other aspects of an encrypted connection.

You can use the WITH clause to grant the GRANT OPTION privilege to a user, as mentioned earlier. You also can use this clause to specify the maximum number of queries that a user may execute per hour (MAX_QUERIES_PER_HOUR), the maximum number of UPDATE statements that may be issued per hour (MAX_UPDATES_PER_HOUR), or the maximum number of connections to the server per hour (MAX_CONNECTIONS_PER_HOUR). Here is an example of how a user might be limited in such a way:

GRANT SELECT ON catalogs.*    TO 'webuser'@'%'    WITH MAX_QUERIES_PER_HOUR 1000    MAX_CONNECTIONS_PER_HOUR 100;

This account is designed for large numbers of users running queries through a web server. The webuser user is created, and is allowed to read tables from the catalogs database. The user may not run more than 1,000 queries in an hour and may establish only 100 connections in an hour.

HANDLER

 HANDLER table OPEN [AS handle]         HANDLER handle READ index operator (value,...)     [WHERE condition] [LIMIT  . . . ]     HANDLER handle READ index {FIRST|NEXT|PREV|LAST}     [WHERE condition] [LIMIT  . . . ]     HANDLER handle READ {FIRST|NEXT}     [WHERE condition] [LIMIT  . . . ]            HANDLER handle CLOSE

Use this statement as a faster alternative to the SELECT statement. The HANDLER statement establishes a handle for reading a MyISAM or an InnoDB table, much like a file handle in a programming language such as Perl. You must issue the HANDLER table OPEN method first to establish a table handle and assign it the name in the AS clause. The AS clause and handle name are optional, though. If an alias is not set up, the table name is used for subsequent HANDLER statements. A handle provides direct access to the table, as opposed to working from a results set. The handle is usable only by the current connection thread that established it. The table is still accessible by others, though, and is not locked by this statement. Because of this and because the method provides direct table access, the data read can change and even be incomplete on an active database while running subsequent read statements.

You can use the three HANDLER handle READ... formats shown here to read data from a table. The HANDLER handle CLOSE method is used to close a table handle. Here are a couple of basic examples of the HANDLER statement:

HANDLER clients OPEN AS clients_handle; HANDLER clients_handle READ FIRST;

The first line creates the table handle called clients_handle, based on the clients table. The next SQL statement retrieves the first row of data from the table. The result of this statement is the same as a SELECT for all columns of the table.

If the second SQL statement in this example was run again, but with the FIRST keyword replaced with NEXT, the next row of data would be displayed. Every time the statement is run with the NEXT flag, the pointer is advanced and the next row in the table is displayed until the end of the table is reached. To retrieve more than one row, you can use the LIMIT clause like this:

HANDLER client_handle READ NEXT LIMIT 3;

This statement will display the next three rows from the table. The WHERE clause may be used with a HANDLER...READ statement in the same way as with the SELECT statement. Here is an example of this:

HANDLER clients_handle READ FIRST    WHERE state = 'MA' LIMIT 5;

This statement will display the first five rows in which the client is located in the state of Massachusetts. Note that no ORDER BY clause is available for HANDLER...READ statements. Therefore, the first five rows are based on the order in which they are stored in the table.

To extract data based on an index, use the two HANDLER handle READ...index syntax structures. Here is example like the previous one, but with the addition of an index:

HANDLER clients_handle READ cid PREV    WHERE state = 'MA' LIMIT 2;

In this example, two rows matching the condition of the WHERE clause, from the previous batch of rows displayed (due to the PREV flag), will be retrieved. The cid index was created when the table was created and is based on a couple of columns for uniqueness and quick retrieval of data. To retrieve the next set of rows using this syntax structure, the PREV flag would be replaced with the NEXT flag. The LAST flag searches and retrieves rows starting from the last row of the table. Here is another example using an index and not a WHERE clause:

HANDLER clients_handle READ name = ('NeumeyerGera');

The name index is a combination of the name_last and the name_first column, but only the first four characters of the first name are used by the index. Given the sample database used for this book, this statement displays the row for the client Gerard Neumeyer. The values for each column may be given with commas (e.g., `Neumeyer', `Gera') in between, or spliced together as shown. This feature of being able to provide a condition for a multicolumn index would be a difficult contortion with a SELECT statement.

INSERT

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]     [INTO] table     SET column={expression|DEFAULT}, ...     [ON DUPLICATE KEY UPDATE column=expression, ... ] INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]     [INTO] table [(column, ...)]     VALUES ({expression|DEFAULT},...),(...),...     [ON DUPLICATE KEY UPDATE column=expression, ... ] INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]     [INTO] table [(column, ...)]     SELECT...

Use this statement to add rows of data to a table. Three statement structures are available. The first syntax format, the INSERT...table SET... method, can insert only one row of data per statement. The second syntax structure shown, the INSERT...table (columns) VALUES(values) method, can handle one or more rows in one statement. The columns and their order are specified once, but values for multiple rows may be given. Each row of values is to be contained in its own set of parentheses, separated by commas. The third syntax structure for this statement, the INSERT...table...SELECT... method, allows columns from rows in other tables to be inserted. Explanations of each type of statement, their various clauses and flags, and examples of their use follow.

Single-row insertion with SET clause

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]     [INTO] table     SET column={expression|DEFAULT}, ...     [ON DUPLICATE KEY UPDATE column=expression, ... ]

This method of the INSERT statement allows only one row of data to be inserted into a table at a time. Each column name and the value to which it's to be set is given in the SET clause, with the use of the equals-sign operator. The value given can be a static value or an expression. The DEFAULT keyword can be given instead to instruct the server to use the default value for the column. You can set the default either with the CREATE TABLE statement when the table is created or with the ALTER TABLE statement for existing tables.

You can use the LOW_PRIORITY flag to instruct the server to wait until all other queries related to the table in which data is to be added are finished before running the INSERT statement. When the table is free, the table is locked for the INSERT statement and will prevent concurrent inserts.

The DELAYED flag indicates the same priority status, but releases the client so that other queries may be run and so that the connection may be terminated. It does not confirm the success of the DELAYED query; it confirms only that the query is to be processed. If the server crashes, the data additions may not be executed when the server restarts and the user won't be informed of the failure. To confirm a DELAYED insert, the user must check the table later for the inserted content with a SELECT statement. The DELAYED option works only with MyISAM, InnoDB, and ISAM tables. It's also not applicable when the ON DUPLICATE KEY UPDATE clause is used. See the explanation for the next syntax structure of this method for more on this clause.

Use the HIGH_PRIORITY flag to override the default setting of --low-priority-updates and to disable concurrent inserts.

Here is an example of the INSERT statement using this particular syntax structure:

INSERT INTO clients   SET client_name =  'Geoffrey & Company',       city = 'Boston', state = 'MA';

This example lists three columns along with the values set in a row entry in the clients table. Other columns in that table will be handled in a default manner for instance, an AUTO_INCREMENT column, client_id, that will receive the next number in sequence.

The ON DUPLICATE KEY UPDATE clause allows an INSERT statement to handle the case where an entry exists already for the data to be inserted. The clause's operation is illustrated in the following example:

CREATE UNIQUE INDEX client_name   ON clients(client_name); INSERT INTO clients   SET client_name = 'Marie & Associates',     telephone = '504-486-1234'   ON DUPLICATE KEY UPDATE     telephone=VALUES(telephone);

This example starts by creating an index on the client_name column in the clients table. The index type is UNIQUE, which means that duplicate values for the client_name column are not allowed. The INSERT statement tries to insert the specified client name and telephone number. But it indicates that if there is already a row in the table for the client, a new row is not to be added. Instead, the existing row is to be updated per the UPDATE clause, setting the original entry's telephone column to the value given in the SET clause of the same INSERT statement. The assumption is that the new data being inserted either is for a new client or is an update to the existing client's telephone number. Instead of using a column value after the equals sign, a literal value or an expression may be given.

Multiple-row insertions

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]     [INTO] table [(column,...)]     VALUES ({expression|DEFAULT},...),(...)     [ON DUPLICATE KEY UPDATE column=expression,...]

This method of the INSERT statement allows for multiple rows to be inserted in one SQL statement. The columns in which data is to be inserted may be given in parentheses in a comma-separated list. If no columns are specified, the statement must include a value for each column in each row, in the order that they appear in the table. The VALUES clause lists the values of each row to be inserted into the table. The values for each row are enclosed in parentheses; each row is separated by a comma. Here is an example of this syntax structure:

INSERT INTO clients (client_name, telephone)   VALUES('Marie & Associates', '504-486-1234'),         ('Geoffrey & Company', '617-522-1234'),         ('Kenneth & Partners', '617-523-1234');

In this example, three rows are inserted into the clients table with one SQL statement. Although the table has several columns, only two columns are inserted for each row here. The other columns are set to their default or to NULL. The order of the values for each row corresponds with the order that the columns are listed.

Normally, if a multiple INSERT statement is entered and one of the rows to be inserted is a duplicate, an error is triggered and an error message is displayed. The statement is terminated and no rows are inserted. The IGNORE flag, however, instructs the server to ignore any errors encountered, suppress the error messages, and insert only the non-duplicate rows. The INTO keyword is optional and only for compatibility. The results of this statement display like so:

Query OK, 120 row affected (4.20 sec) Records: 125  Duplicates: 5  Warnings: 0

These results indicate that 125 records were to be inserted, but only 120 rows were affected or successfully inserted. There were five duplicates in the SQL statement, but there were no warnings because of the IGNORE flag.

Inserting rows based on a SELECT

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]     [INTO] table [(column,...)]     SELECT...

This method of the INSERT statement allows for multiple rows to be inserted in one SQL statement, based on data retrieved from another table by way of a SELECT statement. If a list of columns is given, those columns in the new table are filled with values returned by the SELECT statement from the other table, on a one-by-one basis in the order listed. If no columns are listed, the SELECT must return values for all columns in the order in which they appear in the new table.

For the following example, suppose that the employees table contains a column called softball to indicate whether an employee is a member of the company's softball team. Suppose further that it is decided that a new table should be created to store information about members of the softball team and that the team's captain will have privileges to this new table (softball_team), but no other tables. The employee names and telephone numbers need to be copied into the new table, because the team's captain will not be allowed to do a query on the employees table to extract that information. Here are the SQL statements to set up the new table with its initial data:

CREATE TABLE softball_team    (rec_id INT AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(50),  position VARCHAR(20),     emp_id INT, telephone CHAR(8)); INSERT INTO softball_team    (emp_id, name, telephone)    SELECT rec_id, CONCAT(name_first, ' ', name_last),           RIGHT(telephone_home, 8)       FROM employees       WHERE softball = 'Y';

The first SQL statement creates the new table. The columns are very simple: one column for both the first and last name of the player, and one column for the player's home telephone number. There's also a column for a record identification number and another for the player's position, to be filled in later by the team's captain. In the second SQL statement, the INSERT statement uses an embedded SELECT statement to retrieve data from the employees table where the softball column for the row is set to `Y'. The CONCAT() function is used to put together the first and last name, separated by a space. This will go into the name column in the new table. The RIGHT( ) function is used to extract only the last eight characters of the telephone_home column, because all of the employees on the softball team are from the same telephone dialing area. See Chapter 5 for more information on these functions.

JOIN

SELECT...|UPDATE...|DELETE...        table [INNER|CROSS] JOIN table [ON condition|USING (column,...)] table STRAIGHT_JOIN table table LEFT [OUTER] JOIN table [ON condition|USING (column,...)] table NATURAL [LEFT [OUTER]] JOIN table    [OJ table LEFT OUTER JOIN table ON condition] table RIGHT [OUTER] JOIN table [ON condition|USING (column,...)] table NATURAL [RIGHT [OUTER]] JOIN table

The JOIN clause is common to several SQL statements (SELECT, UPDATE, DELETE) and is complex: therefore, it is listed here as its own entry in the chapter. You use JOIN to link tables together based on columns with common data for purposes of selecting, updating, or deleting data. The JOIN clause is entered at the place in the relevant statement that specifies the tables to be referenced. This precludes the need to join the tables based on key columns in the WHERE clause. The ON keyword is used to indicate the condition by which the tables will be joined.

As an alternative method, the USING keyword may be given along with a comma-separated list of columns within parentheses. The columns must be contained in each table that is joined. Here is an example of a JOIN:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name    FROM employees    JOIN branches ON branch_id = branches.rec_id    WHERE location = 'New Orleans';

This statement will display a list of employees from the employees table who are located in the New Orleans branch office. The problem being solved with the JOIN is that the employees table doesn't indicate New Orleans by name as the branch; that table just has a numeric identifier. The branches table is used to retrieve the branch name for the WHERE clause. The location column is a column in the branches table. Nothing is actually displayed from the branches table here.

If the record identification column for branches was named branch_id rather than rec_id, the USING keyword and associated method could be used for the same JOIN. Here is an example of this method for the same statement:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name    FROM employees    JOIN branches USING (branch_id)    WHERE location = 'New Orleans';

This will join the two tables on the branch_id column in each table. These tables have only one row in common, so it's not necessary to specify that row; instead, you can use the NATURAL keyword. Here is the same statement with this change:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name    FROM employees    NATURAL JOIN branches    WHERE location = 'New Orleans';

Notice that the USING keyword and the column for linking were omitted. The results of this SQL statement will be the same as for the previous one.

When joining two tables in a simple join, as shown in the previous example, if no rows in the second table match rows from the first table, no row will be displayed for the unmatched data. Sometimes, though, it can be useful to display a record regardless. The LEFT keyword may be given in front of the JOIN keyword to indicate that records from the first table listed on the left are to be displayed regardless of whether there is a matching row in the table on the right:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,        location AS Branch    FROM employees    LEFT JOIN branches USING (branch_id);

This SQL statement will list a row for each employee along with the employee's location. If a row for an employee has either a NULL value for the branch_id, or a branch number that is not in the branches table, the employee name will still be displayed but with the branch name reading as NULL. This can be useful for spotting errors or inconsistencies in the data between related tables.

In contrast to LEFT JOIN, the RIGHT JOIN clause includes all matching entries from the table on the right even if there are no matches from the table on the left. Here is an example using a RIGHT JOIN:

SELECT CONCAT(name_first, SPACE(1), name_last) AS 'Sales Rep',        SUM(total_order) AS Sales    FROM orders    RIGHT JOIN employees ON sales_rep = employees.rec_id    WHERE dept = 'sales'    GROUP BY sales_rep;

This example displays a list of employees who are members of the sales department, with the sum of their orders. If a sales representative does not have any orders, a row will still be presented because of the RIGHT JOIN. For both the LEFT and RIGHT JOIN methods, the OUTER keyword may be included, but it's not necessary and has no effect on the results. It's just a matter of preference and compatibility. NATURAL may also be combined with LEFT JOIN and RIGHT JOIN clauses.

The JOIN clause has a few other options. The STRAIGHT_JOIN flag explicitly instructs MySQL to read the tables as listed, from left to right. The keywords INNER and CROSS have no effect on the results, as of recent versions of MySQL. They cannot be used in conjunction with the keywords LEFT, RIGHT, or NATURAL. The syntax starting with the OJ keyword is for compatibility with ODBC.

Table flags and indexing options

You can use the AS keyword to introduce aliases for tables. Several examples of aliasing were provided earlier in the explanation of this clause.

When tables are joined and data is searched by MySQL, use indexes to increase the speed of SQL statements. To indicate to MySQL which index or key it should look to first for queries, you can provide the USE INDEX option along with the names of the keys to use in a comma-separated list, within parentheses. To instruct MySQL not to use certain keys, list them with the IGNORE INDEX option in the same manner. The FORCE INDEX option instructs MySQL to attempt to limit its search to the specified index; others, however, will be used if the requested columns make it necessary.

KILL

KILL [CONNECTION|QUERY] thread

Use this statement to terminate a client connection to MySQL. You can use the SHOW PROCESSLIST statement to obtain a connection thread identifier. As of Version 5, you can use CONNECTION or QUERY flags to distinguish between terminating a connection or terminating just the query associated with the connection for the given thread. Some processes cannot be terminated immediately. Instead, this statement flags the process for termination. The system may not check the flag until the process is completed. This will occur with statements such as REPAIR TABLE.

Here is an example of this statement:

KILL 22;

This simple example terminates the connection for the client associated with the thread identifier 22. If that client attempts to issue another SQL statement, it will receive an error 2006 message stating that the MySQL server has gone away. Then it typically will try to reconnect to the server, establish a new thread, and run the requested query.

LOAD DATA FROM MASTER

LOAD DATA FROM MASTER

This statement makes a copy of all the databases on the master server (except the mysql database) and then copies them to the slave servers. This statement will get a global read lock on all tables while it takes a snapshot of the databases. It will release the lock before copying them to the slaves. The MASTER_LOG_FILE and the MASTER_LOG_POS variables will be updated so that the slave knows where to begin logging. This statement currently works only with MyISAM tables, but will eventually work with InnoDB tables in future releases. The user for the connection must have RELOAD, SELECT, and SUPER privileges on the master server. The user must also have CREATE and DROP privileges on the slave server.

For large databases, increase the values of the net_read_timeout and net_write_timeout variables with the SET statement. To load a specific table from the master server, use the LOAD TABLE...FROM MASTER statement.

LOAD DATA INFILE

 LOAD DATA [LOW_PRIORITY|CONCURRENT] [LOCAL] INFILE 'filename'     [REPLACE|IGNORE] INTO TABLE table     [FIELDS [TERMINATED BY 'C']          [[OPTIONALLY] ENCLOSED BY 'C']         [ESCAPED BY 'C']]     [LINES [STARTING BY 'C'] [TERMINATED BY 'C']]     [IGNORE count LINES]     [(column, . . . )]

You can use this statement to import organized data properly from a text file into a table on the server. The data text file must be located either in the directory of the database into which the data is to be imported or in a directory on the server's filesystem with the file permission set to read for all filesystem users. If the text file is on the client's filesystem, the LOCAL flag must be given. This feature must be enabled on both the client and the server with --local-infile=1. See Chapter 10 for more information on these settings. Here is a basic example of how you can use this statement:

LOAD DATA INFILE '/tmp/catalog.txt'    INTO TABLE catalog    FIELDS TERMINATED BY '|'    LINES TERMINATED BY '\n';

In this example, the file to be loaded is in the /tmp directory and is called catalog.txt. The data contained in the file is to be inserted into the catalog table in the current database in use. Each field in the text file is terminated with a vertical bar character. The rows of data in the text file are on separate lines. They are separated by a newline character (\n). This is the default for a Unix text file. For DOS or Windows systems, lines are usually terminated with a \n\r, signifying a newline and a return character. If the rows start with a special character, you can identify that character with the LINES STARTED BY clause.

If a data text file contains rows of data that are duplicates of some of the rows in the table into which it's being imported, an error will occur and the import may end without importing the remaining data. Duplicate rows are those that have the same values for key columns. To instruct the server to ignore any errors encountered and to proceed with the import, you should use the IGNORE flag. Use the SHOW WARNINGS statement to retrieve the error messages that would have been displayed. To instruct the server to replace any duplicate rows with the ones being imported, use the REPLACE flag with the statement. This will completely replace the values of all columns, even when the new record contains no data for a column and the existing one does.

This statement also offers the ENCLOSED BY clause to specify a character that can start and terminate a field, such as a quotation mark. You can use the OPTIONALLY flag to indicate that the character is an optional one. The character which is used to escape special characters may be given with the ESCAPED BY clause. The backslash is the default value.

Some data text files contain one or more lines of column headings that should not be imported. To omit these initial lines from the import, use the IGNORE count LINES clause, where count is the number of lines to ignore.

For some data text files, the fields of data are not in the same order as the columns of the receiving table. Sometimes there are fewer fields in the text file than in the table. For both of these situations, to change the order and number of columns, add a list of columns and their order in the text file to the end of the statement within parentheses. Here is an example of such a scenario:

LOAD DATA LOW_PRIORITY INFILE '/tmp/catalog.txt' IGNORE   INTO TABLE catalog   FIELDS TERMINATED BY '|'   LINES TERMINATED BY '\n'   IGNORE 1 LINES   (cat_id, description, price);

The first line of the text file contains column headings describing the data, but that line will not be imported because of the IGNORE 1 LINES clause here. The catalog table has several more columns than the three that are being imported and they are in a different order. This import is not critical. Therefore, the LOW_PRIORITY flag near the beginning of the statement instructs the server to handle other queries on the catalog table before running this statement. If this was replaced with CONCURRENT, the import would be performed even if other clients are querying the same table.

LOAD INDEX INTO CACHE

LOAD INDEX INTO CACHE   table [[INDEX|KEY] (index[,  . . . )] [IGNORE LEAVES]   [,  . . . ]

Use this statement to preload a table's index into a given key cache for a MyISAM table. Although one or more indexes may be specified in a comma-separated list in parentheses, all indexes for the table will be loaded into the cache. This will change in future versions of MySQL. The keywords INDEX and KEY are interchangeable and are not necessary. The IGNORE LEAVES clause instructs MySQL not to preload leaf nodes of the index. Here is an example of how you can use this statement:

LOAD INDEX INTO CACHE workreq; +----------------------+--------------+----------+----------+ | Table                | Op           | Msg_type | Msg_text | +----------------------+--------------+----------+----------+ | workrequests.workreq | preload_keys | status   | OK       | +----------------------+--------------+----------+----------+

LOAD TABLE...FROM MASTER

LOAD TABLE table FROM MASTER

Use this statement to copy a MyISAM table from the master server to a slave server. The user for the connection must have RELOAD and SUPER privileges as well as SELECT privileges for the table on the master server. The user must also have CREATE and DROP privileges on the slave server.

LOCK TABLES

LOCK TABLES table [AS alias]     {READ [LOCAL]|[LOW_PRIORITY] WRITE]} [,  . . . ]

Use this statement to lock given tables for exclusive use of the current connection thread. A READ lock will allow the locked tables to be read, but will not allow writes to them even by the thread which locked them. A READ LOCAL lock will allow all threads to read the tables that are locked while the locking connection can execute INSERT statements. Until the lock is released, though, direct data manipulation by command-line utilities should be avoided. A WRITE lock will not allow other threads to read or write to tables locked, but will permit reads and writes by the locking thread. SQL statements for tables that are locked with the WRITE option have priority over statements involving tables with a READ lock. However, the LOW_PRIORITY flag may be given before the WRITE to instruct the server to wait until there are no queries on the tables being locked.

Only locked tables may be accessed by a locking thread. So, all tables to be used must be locked. To illustrate this, assume a new programmer has been hired. The programmer's information will need to be added to the programmers table. The wk_schedule table that contains the records for scheduling work will also need to be adjusted to assign work to the new programmer and away from others. Here is how you might lock the relevant tables:

LOCK TABLES workreq READ, programmers READ LOCAL,   wk_schedule AS work LOW_PRIORITY WRITE;

In this example, the workreq table is locked with a READ flag so that no new work requests may be added while the table for the programmers' work schedules is being updated, but the work requests may still be viewed by other users. The programmers table is locked for writing with the READ LOCAL flag, because one record needs to be inserted for the new programmer's personal information. The wk_schedule table is locked for exclusive use by the current thread.

For convenience, you can give a table an alias with the AS keyword. In the previous example, the wk_schedule table is referred to as work for subsequent SQL statements until the tables are unlocked. During this time, the thread can refer to the table only by this name in all other SQL statements.

You can release locks with the UNLOCK TABLES statements. Issuing a START TRANSACTION statement will also cause tables to unlock. Executing another TABLE LOCKS statement will also release table locks. Therefore, all tables to be locked should be named in one statement. Additional tables can be added to the end of the TABLE LOCKS statement in a comma-separated list.

You can lock all tables with a FLUSH TABLES WITH READ LOCK statement. You can use the GET_LOCK( ) and RELEASE_LOCK( ) functions as alternatives to LOCK TABLES and UNLOCK TABLES. See Chapter 9.

OPTIMIZE TABLE

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE table[,  . . . ]

Use this statement to optimize the data contained in a table. Optimization is useful when many rows have been deleted from a table. It's also useful to run this statement periodically with a table that contains several variable-character-width columns (i.e., VARCHAR, BLOB, and TEXT columns). This statement generally works only with MyISAM, BDB, and InnoDB tables. It may work on other tables, however, if the mysqld daemon is started with the --skip-new option or the --safe-mode option. See Chapter 10 for more information on setting server startup options.

This statement will repair some row problems and sort indexes. It will temporarily lock the tables involved while optimizing. Multiple tables can be listed for optimization in a comma-separated list. To prevent the activities of this statement from being recorded in the binary log file, use the NO_WRITE_TO_BINLOG flag or its alias, LOCAL. Here is an example of its use:

OPTIMIZE LOCAL TABLE workreq, clients; +----------------------+----------+----------+----------+ | Table                | Op       | Msg_type | Msg_text | +----------------------+----------+----------+----------+ | workrequests.workreq | optimize | status   | OK       | | workrequests.clients | optimize | status   | OK       | +----------------------+----------+----------+----------+

Here two tables were optimized successfully and the activity was not written to the binary log file.

PURGE MASTER LOGS

PURGE {MASTER|BINARY} LOGS {TO 'filename'|BEFORE 'date'}

Use this statement to delete the binary logs from a master server. The keywords MASTER and BINARY are synonymous and one is required for the statement. Log files are deleted sequentially from the starting logfile to the one named with the TO clause, or up until (but not including) the date named with the BEFORE clause. Here is an example of each method:

PURGE MASTER LOGS TO 'log-bin.00110'; PURGE MASTER LOGS BEFORE '2004-11-03 07:00:00';

Use the SHOW MASTER LOGS statement to obtain a list of logfiles before purging. When using slave servers, run the SHOW SLAVE LOGS statement on each slave to determine the oldest log that is still in use before deciding which logfiles to delete. It would also be prudent to make a backup of the logs before running this statement.

RENAME TABLE

RENAME TABLE table TO table[,...]

Use this statement to rename a given table to the name given after the TO keyword. Additional tables may be specified for renaming in a comma-separated list. Multiple renames are performed left to right, and if any errors are encountered, all of the table name changes are reversed from right to left. While tables are being renamed, no other client can interact with the tables involved. Tables that are currently locked or tables that are part of a transaction in progress cannot be renamed.

Tables can be renamed and moved to databases on the same filesystem. As an example, suppose that users add data to a particular table during the course of the day and that each day the contents of the table are to be preserved. Suppose further that you want to reset the table back to no data. Here's how to do that:

CREATE TABLE survey_new LIKE survey_bak; RENAME TABLE survey TO survey_bak,              survey_new TO survey;

In this example, a new table called survey_new is created based on the table structure of the old table called survey, but without the data. In the second SQL statement, the old table is renamed to survey_bak and the blank table, survey_new, is renamed to survey. If issued from a program, the new name could be generated based upon the date so that each day's data could be preserved.

REPAIR TABLE

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE     table[, ...] [QUICK] [EXTENDED] [USE_FRM]

Use this statement to repair corrupted MyISAM tables. Multiple tables may be given in a comma-separated list. To prevent this statement from recording its activities in the binary log file, the NO_WRITE_TO_BINLOG flag or its LOCAL alias may be given. The QUICK flag instructs MySQL to repair the table indexes only. The EXTENDED flag is for rebuilding the indexes one row at a time. This option takes longer, but can be more effective, especially with rows containing duplicate keys. Before running this statement, make a backup of the table. If a table continues to have problems, there may be other problems (e.g., filesystem problems) that you should consider. Here is an example of this statement:

REPAIR TABLE systems QUICK EXTENDED; +----------------------+--------+----------+----------+ | Table                | Op     | Msg_type | Msg_text | +----------------------+--------+----------+----------+ | workrequests.systems | repair | status   | OK       | +----------------------+--------+----------+----------+

In this example, the repair was successful. This is indicated by the OK in the Msg_text field. If it were unsuccessful, you could try the USE_FRM option with this statement. This option will create a new index file (.MYI) using the table schema file (.frm). It won't be able to determine the current value for AUTO_INCREMENT columns or for DELETE LINK, so it shouldn't be used unless the original .MYI file is lost. Incidentally, if the MySQL server dies while the REPAIR TABLE statement is running, you should run the statement again as soon as the server is back up, before running any other SQL statements.

REPLACE

 REPLACE [LOW_PRIORITY|DELAYED] [INTO] table [(column,...)]     VALUES ({expression|DEFAULT},...)[, (...)] REPLACE [LOW_PRIORITY|DELAYED] [INTO] table     SET column={expression|DEFAULT},  . . .  REPLACE [LOW_PRIORITY|DELAYED] [INTO] table [(column,...)]     SELECT...

Use this statement to insert new rows of data and to replace existing rows where the PRIMARY KEY or UNIQUE index key is the same as the new record being inserted. The LOW_PRIORITY flag instructs the server to wait until there are no queries on the table named, including reads, and then to lock the table for exclusive use by the thread so that data may be inserted and replaced. When the statement is finished, the lock is released automatically. For busy servers, a client may be waiting for quite a while. The DELAYED flag will free the client by storing the statement in a buffer for processing when the table is not busy. The client won't be given notice of the success of the statement, just that it's buffered. If the server crashes before the changes to the data are processed, the client will not be informed and the buffer contents will be lost. The INTO keyword is optional, and is a matter of style preference and compatibility. This statement requires INSERT and DELETE privileges, because it is potentially a combination of both.

Three basic statement structures may be used to insert and replace data. For the syntax of the first one shown, the values for each row are placed in parentheses after the VALUES keyword. If the number of values and their order do not match the columns of the table named, the columns will have to be listed in parentheses after the table name in the order in which the values are arranged. Here is an example of the REPLACE statement using this syntax structure:

REPLACE INTO workreq (wr_id, client_id, description) VALUES('5768','1000','Network Access Problem'),       ('5770','1000','Network Access Problem');

Notice that this statement is able to insert two rows without the column names being listed twice. In this example, the first row already existed before this statement was to be executed. Once it's run, the row represented by work request identifier 5768 is completely replaced with this data. Columns that are not included in the list of columns here are reset to their default values or to NULL, depending on the column.

The second syntax structure for the REPLACE statement shown does not allow for multiple rows. Instead of grouping the column names in one part of the statement and the values in another part, column names and values are given in a column=value pair. To enter the REPLACE statement from the preceding example in the format of the second syntax structure, you would have to enter the following two statements:

REPLACE INTO workreq    SET wr_id = '5768', client_id = '1000',        description = 'Network Access Problem'; REPLACE INTO workreq    SET wr_id = '5770', client_id = '1000',        description = 'Network Access Problem';

The third syntax structure for the REPLACE statement involves a subquery, which is available as of Version 4.1 of MySQL. With a subquery, data can be retrieved from another table and inserted into the table referenced in the main query for the statement. Here is an example of this:

REPLACE INTO workreq (wr_id, client_id, status) SELECT wr_id, client_id, 'HOLD'    FROM wk_schedule    WHERE programmer_id = '1000';

In this example, work requests assigned to a particular programmer are being changed to a temporarily on-hold status. The values for two of the columns are taken from the work schedule table and the fixed string of HOLD is inserted as the value of the third column. Currently, the table for which replacement data is being inserted cannot be used in the subquery.

RESET

RESET option[,  . . . ]

Use this statement to reset certain server settings and files. Currently, you can reset the MASTER, QUERY CACHE, and SLAVE options. See the RESET MASTER statement and the RESET SLAVE statement for detailed explanations of each option. The QUERY CACHE option will clear the cache containing SQL query results.

RESET MASTER

RESET MASTER

Use this statement to delete all the binary log files on the master server. Binary logfiles are located in the directory indicated by the value of the --bin-log option of mysqld (see Chapter 10). The logfiles are typically named log-bin.n, where n is a six-digit numbering index. Use the SHOW MASTER LOGS statement to get a list of log files to be sure.

This statement will delete all of the master logfiles and begin the numbering of the new file at 000001. To get the slave servers in line with the reset master, run the RESET SLAVE statement. You can run the MASTER and SLAVE options together in a comma-separated list like so:

RESET MASTER, SLAVE;

This is a recommended method for ensuring consistency.

RESET SLAVE

RESET SLAVE

Use this statement within or after the RESET MASTER statement that sets the binary logging index back to 1. This statement will delete the master.info file, the relay-log.info file, and all of the relay logfiles on the slave server. A new .info file will be created with the default, start-up values.

RESTORE TABLE

RESTORE TABLE table[,  . . . ] FROM '/path'

Use this statement to restore a table that was saved to the filesystem by the BACKUP TABLE statement. Multiple tables may be given in a comma-separated list. The absolute path to the directory containing the backup files is given within quotes. If the tables already exist in the database, an error message will be generated and the restore will fail. If it's successful, the table indexes will be built automatically. This is necessary because the BACKUP TABLE statement doesn't back up the index files.

RESTORE TABLE clients, programmers FROM '/tmp/backup'; +--------------------------+---------+----------+----------+ | Table                    | Op      | Msg_type | Msg_text | +--------------------------+---------+----------+----------+ | workrequests.clients     | restore | status   | OK       | +--------------------------+---------+----------+----------+ | workrequests.programmers | restore | status   | OK       | +--------------------------+---------+----------+----------+

In this example, the statement was successful in restoring the .frm and .MYD files located in the backup directory. The .MYI files were generated automatically after they were restored.

REVOKE

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user[,  . . . ] REVOKE privileges [(column,  . . . )]     ON table.database     FROM user[,  . . . ]

Use this statement to revoke all or certain privileges that were granted to a user with the GRANT statement. The first syntax structure is used to revoke all privileges from a user. Multiple users may be given in a comma-separated list. A list of users and their privileges are stored in the mysql database, in particular in the user table.

To revoke only some privileges, you can use the second syntax structure. The specific privileges are to be given in a comma-separated list after the keyword REVOKE. To revoke privileges for specific columns, those columns may be listed within parentheses in a comma-separated list. Privileges that are granted based on columns are stored in the columns_priv table of the mysql database. Privileges may be revoked on a specific table for a specific database. To revoke privileges on all tables of a database, the table name should be substituted with an asterisk as a wildcard. You can do the same for the database name for the statement to apply to all databases. Table-specific privileges are stored in the tables_priv table, and database privileges are stored in the db table.

ROLLBACK

ROLLBACK

Use this statement with an InnoDB or BDB table to reverse transactions that have not yet been committed. If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful. To do this, set the value of AUTOCOMMIT to 0 with the SET statement. You can also disable AUTOCOMMIT with the START TRANSACTION statement and reinstate it with the COMMIT statement.

Here is an example of this statement's use:

START TRANSACTION; LOCK TABLES orders WRITE; INSERT DATA INFILE '/tmp/customer_orders.sql'   INTO TABLE orders; SELECT ...; ROLLBACK; UNLOCK TABLES;

In this example, after the batch of orders was inserted into the orders table, the administrator manually enters a series of SELECT statements (not shown) to check the integrity of the data. If everything seems alright, the COMMIT statement would be issued instead of the ROLLBACK statement shown here, to commit the transactions. If there is a problem, though, the ROLLBACK statement could be issued as shown here. It would remove the data imported by the INSERT DATA INFILE statement.

The ROLLBACK statement works only with InnoDB and BDB tables. A rollback will not undo the creation or deletion of databases. It also cannot be performed on changes to table schema (i.e., ALTER TABLE, CREATE TABLE, or DROP TABLE statements).

Transactions cannot be reversed with the ROLLBACK statement if they have been committed. Commits are caused by the COMMIT statement, as well as with the following statements: 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, and UNLOCK TABLES.

ROLLBACK TO SAVEPOINT

ROLLBACK TO SAVEPOINT identifier

This statement instructs the server to reverse SQL statements for the current transaction back to a saved point in the transaction identified by the SAVEPOINT statement. See the SAVEPOINT statement for an example of its use. It works only on InnoDB and BDB tables.

SAVEPOINT

SAVEPOINT identifier

Use this statement in conjunction with the ROLLBACK TO SAVEPOINT statement to identify a point in a transaction to which SQL statements may potentially be undone later. You can use any unreserved word to identify a save point and create several save points during a transaction. If an additional SAVEPOINT statement is issued with the same name, the previous point will be replaced with the new point for the name given.

START TRANSACTION; LOCK TABLES orders WRITE; INSERT DATA INFILE '/tmp/customer_info.sql'   INTO TABLE orders; SAVEPOINT orders_import; INSERT DATA INFILE '/tmp/customer_orders.sql'   INTO TABLE orders;

At this point in this example, the administrator can check the results of the orders imported before committing the transactions. If the administrator decides that the orders imported have problems, but not the client information that was first imported, the following statement could be entered:

ROLLBACK TO SAVEPOINT orders_import;

If the administrator also decides that the customer information that was imported also has problems, the ROLLBACK statement can be issued to undo the entire transaction.

SELECT

SELECT [flags] {column,  . . . |expressions}[,  . . . ]    FROM table[,  . . . ]    [WHERE condition]   [GROUP BY {column|expression|position}[ASC|DESC],  . . .       [WITH ROLLUP]]   [HAVING condition]   [ORDER BY {column|expression|position}[ASC|DESC] ,  . . . ]   [LIMIT {[offset,] count|count OFFSET offset}]   [PROCEDURE procedure(arguments)]   options

Use this statement to retrieve and display data from tables within a database. It has many clauses and options. However, for simple data retrieval many of them can be omitted. The basic syntax for the statement is shown. After the SELECT keyword, some flags may be given. Next, a list of columns to retrieve and/or expressions may be given, separated by commas. For the tables, all that is required is one or more table names from which to retrieve data in a comma-separated list. The remaining clauses may be called on to refine the data to be retrieved, to order it, and so forth. These various flags, options, and clauses are detailed in subsections to this statement explanation. Here is a simple example of how you can use this statement:

SELECT name_first, name_last, telephone_home,        DATEDIFF(now( ), last_review)        AS 'Days Since Last Review'    FROM employees;

In this example, three columns and the results of an expression based on a fourth column are to be displayed. The first and last name of each employee, each employee's home telephone number, and the difference between the date of the employee's last employment review and the date now are listed. This last field has the addition of the AS keyword to set the column heading of the results set, and to name an alias for the field. An alias may be referenced in subsequent clauses of the same statement (e.g., the ORDER BY clause). To select all columns in the table, the wildcard * can be given instead of the column names.

SELECT statement flags

SELECT      [ALL|DISTINCT|DISTINCTROW]      [HIGH_PRIORITY] [STRAIGHT_JOIN]      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]      [SQL_CACHE|SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]   {column|expression}[, ...]   FROM table[, ...]      [WHERE condition] [other clauses] [options]

After the SELECT keyword and before the columns and expressions are listed in the SELECT statement, several flags may be given. They are shown in the preceding syntax, with the other components of the statement abbreviated.

When a WHERE clause is used with the statement, sometimes rows contain duplicate data. If you want all rows that meet the selection conditions to be displayed, you may include the ALL flag. This is the default, so it's not necessary to give this flag. If only the first occurrence of a row should be displayed and the duplicates should not be displayed, you should include the DISTINCT flag. The DISTINCTROW flag is a synonym for DISTINCT.

Any UPDATE statements that are issued have priority over SELECT statements; they will be run first. To give a particular SELECT statement higher priority than any UPDATE statements, you will need to use the HIGH_PRIORITY flag.

Multiple tables may be selected in this statement. The column on which they should be joined is given with the WHERE clause, the JOIN clause, or the UNION clause. The JOIN clause and the UNION (or SELECT...UNION) clause are described separately in this chapter. For optimization, MySQL might not join tables in the order that they are listed in the SQL statement. To insist on joining in the order given, you must use the STRAIGHT_JOIN flag.

When you know that the results of a SELECT statement using the DISTINCT flag or the GROUP BY clause will be small, you can use the SQL_SMALL_RESULT flag. This will cause MySQL to use temporary tables, with a key based on the GROUP BY clause elements, to sort the results and will possibly make for faster data retrieval. If you expect the results to be large, you can use the SQL_BIG_RESULT flag. This will cause MySQL to use temporary tables on the filesystem. Regardless of whether you use DISTINCT or GROUP BY, the SQL_BUFFER_RESULT flag may be given for any SELECT statement to have MySQL use a temporary table to buffer the results. You can use only one of the three flags mentioned in this paragraph in each statement.

If the MySQL server does not use the query cache by default, it can be overridden for a particular SELECT statement. To do this, include the SQL_CACHE flag. If the server does use the query cache by default, you can use the SQL_NO_CACHE to instruct MySQL not to use the cache for a particular SELECT statement. To determine if the server uses query cache by default, use the SHOW VARIABLES statement and look for the query_cache_type variable in the results.

The last flag available is SQL_CALC_FOUND_ROWS. With this flag, the number of rows that meet the conditions of the statement are counted. This is not affected by a LIMIT clause. The results of this count must be retrieved in a separate SELECT statement with the FOUND_ROWS( ) function. See Chapter 9 for information on this function.

Exporting SELECT results

SELECT [flags] columns|expressions      [INTO OUTFILE 'filename'         [FIELDS TERMINATED BY 'C']         [FIELDS ENCLOSED BY 'C']         [ESCAPED BY 'C' ]]         [LINES [STARTING BY 'C'] [TERMINATED BY 'C']]      |INTO DUMPFILE 'filename']      FROM table[, ...]      [WHERE condition]      [other clauses] [options]

The INTO clause is used to export data selected to an external text file. The column names and other information will not be exported. To set the character used to terminate fields, use the FIELDS TERMINATED BY option. Use the FIELDS ENCLOSED BY option to specify the character to be placed around each field. You can set the character which is to be used to escape special characters with the ESCAPED BY option. By default, the backslash is used. You can use the LINES STARTING BY option to set the character that will signify the beginning of a row. Use the LINES TERMINATED BY option to identify the character that indicates the end of a line. Here is an example of this clause and these options:

SELECT * FROM employees    INTO OUTFILE '/tmp/employees.txt'    FIELDS TERMINATED BY '|'    LINES TERMINATED BY '\n'    ESCAPED BY '\\';

The text file that's created by this SQL statement will contain a separate line for each row selected. Each field will end with a vertical bar. Any special characters (e.g., an apostrophe) will be preceded by a backslash. Because a backslash is an escape character within an SQL statement, two are needed in the ESCAPE BY clause, because the first escapes the second. To import such a data text file, use the INSERT...LOAD DATA INFILE statement.

The second syntax structure uses the clause INTO DUMPFILE. This clause is used to export only one row into an external text file. It does not allow for any field or line terminator like the INTO OUTFILE clause. Here is an example of its use:

SELECT photograph    INTO DUMPFILE '/tmp/bobs_picture.jpeg'    FROM employees    WHERE emp_id = '1827';

This statement will export the contents of the photograph column for an employee's record. It's a BLOB type column and contains an image file. The result of the exported file is a complete and usable image file.

Grouping SELECT results

SELECT [flags] column|expression[, ...]   FROM table[, ...]     [WHERE condition]     [GROUP BY {column|expression|position}[ASC|DESC], ...       [WITH ROLLUP]]     [other clauses] [options]

When running a SELECT statement, sometimes it's more meaningful to group together those rows containing the same value for a particular column. The GROUP BY clause specifies one or more columns by which MySQL is to group the data retrieved. This is used with aggregate functions so that the values of numeric columns for the rows grouped will be aggregated. For instance, suppose that a SELECT statement is to list the sales representatives for a business and their orders for the month. Without a GROUP BY clause, one line would be displayed for each sales representative for each order. Here's an example of how this might be resolved:

SELECT CONCAT(name_first, ' ', name_last) AS 'Sales Rep.',        SUM(total_order) AS 'Sales for Month'    FROM orders, employees    WHERE employees.rec_id = sales_rep      AND MONTH(order_date) = MONTH(CURDATE( ))    GROUP BY sales_rep;

This statement will concatenate the first and last name of each sales representative who placed an order for a customer during the current month. The GROUP BY clause will group together the rows found for each sales representative. The SUM( ) function will add the values of the total_order column for each row within each group. See Chapter 7 for more information on the SUM( ) function and other aggregate functions.

You can specify multiple columns in the GROUP BY clause. Instead of stating a column's name, you can state its position in the table. A value of 1 would represent the first column in the table. Expressions may be given, as well.

The GROUP BY clause does its own sorting and cannot be used with the ORDER BY clause. To set the sorting to ascending order explicitly for a column, enter the ASC keyword after the column in the clause which is to be set. This is not necessary, though, since it is the default setting. To set sorting to descending, add DESC after each column that is to be sorted in reverse.

When grouping rows by one column, it may be desirable not only to have a total of the values for certain columns, but also to display a total for all of the grouped rows at the end of the results set. To do this, use the WITH ROLLUP flag. Here is an example with this flag:

SELECT location AS Branch,        CONCAT(name_first, ' ', name_last) AS 'Sales Rep.',        SUM(total_order) AS 'Sales for Month'    FROM orders, employees, branches    WHERE sales_rep = employees.rec_id      AND MONTH(order_date) = MONTH(CURDATE( ))      AND branch_id = branches.rec_id    GROUP BY Branch, sales_rep WITH ROLLUP; +---------------+---------------+-----------------+ | Branch        | Sales Rep.    | Sales for Month | +---------------+---------------+-----------------+ | Boston        | Sean Wilson   |            2472 | | Boston        | Morgan Miller |            1600 | | Boston        | Morgan Miller |            4072 | | New Orleans   | Marie Dyer    |            1750 | | New Orleans   | Tom Smith     |            6407 | | New Orleans   | Sean Johnson  |            5722 | | New Orleans   | Sean Johnson  |           13879 | | San Francisco | Geoffrey Dyer |             500 | | San Francisco | Kenneth Dyer  |             500 | | San Francisco | Kenneth Dyer  |            1000 | | NULL          | Kenneth Dyer  |           18951 | +---------------+---------------+-----------------+

The total for each sales representative is grouped and summed. When there aren't any more sales representatives for a branch, a row in the display for the subtotal is generated. It displays the branch name and the name of the last representative. When there are no more branches, a row for the grand total of sales is generated. The branch shows NULL. For clarity, I've boldfaced the subtotals and the grand total in the results set.

Having SELECT results

SELECT [flags] column|expression [, ...]   FROM table [, ...]      [WHERE condition ]     [GROUP BY condition ]     [HAVING condition ]     [other clauses ] [options] 

The HAVING clause is similar to the WHERE clause, but it is used for conditions returned by aggregate functions (e.g., AVG(), MIN( ), and MAX( )). For older versions of MySQL, you must use aliases for aggregate functions in the main clause of the SELECT statement. Here is an example of how you can use this clause:

SELECT CONCAT(name_first, ' ', name_last) AS 'Sales Rep',        total_order    FROM orders, employees    WHERE sales_rep = employees.rec_id    GROUP BY sales_rep    HAVING MAX(total_order);

This SQL statement will retrieve a list of employee names from the employees table where the employee is located in the New Orleans branch office. From this list, it will refine the results by grouping the data for each representative together and determine the sum of each one's total_order column. Because of the MAX() function, it will display data only for the row with the maximum number.

Ordering SELECT results

SELECT [flags ] column |expression [, ...]    FROM table [, ...]     [WHERE condition ]     [ORDER BY {column |expression |position }[ASC|DESC], ...]     [other  clauses ] [options] 

The results of a SELECT statement will be displayed in the order in which the rows of data are found in the table, which may be the order in which they were entered into the table. To change the order of a results set, use the ORDER BY clause. As a basis for ordering the results, one or more columns may be named, separated by commas. The order in which columns are listed is the order in which sorts will be conducted. You can also use aliases for columns, column combinations, or expressions that were established earlier in the same SELECT statement. Instead of stating a column's name, you can state its position. A value of 1 would represent the first column in the table. Here is an example of a SELECT statement using the ORDER BY clause:

SELECT CONCAT(name_first, ' ', name_last) AS Name,        MONTH(birth_date) AS 'Birth Month', email_address    FROM employees    ORDER BY 'Birth Month' ASC, Name ASC;

Here a list of employees, the month in which they were born, and their email addresses are to be extracted. For the name, the CONCAT( ) function is used to put the first and last name together, separated by a space. The AS clause establishes an alias of Name. The MONTH( ) function is used to extract the month from the birth_date column and the AS clause sets up the alias Birth Date. In the ORDER BY clause, the alias for the birth date is used for the initial sort and the name for the secondary sort. The results will be that all of the employees who have a birth date in the same month will be listed together and in alphabetical order by name. Both aliases are followed by the ASC flag to indicate that the results should be sorted in ascending order. This is unnecessary, as ascending order is the default. However, to change an ordering method to descending, use the DESC flag.

You can also use expressions for ordering results. The expressions may, of course, be based on columns or aliases. Here is an example of a SELECT statement using an expression for ordering:

SELECT CONCAT(name_first, ' ', name_last) AS name,        pay_rate, hours    FROM employees    ORDER BY pay_rate * hours DESC;

In this example, the first and last names are selected and concatenated together under the name column heading in the results set. The pay_rate column lists the hourly dollar rate an employee is paid and the hours column contains the typical number of hours a week that an employee works. In the ORDER BY clause, the product of the hourly pay rate multiplied by the number of hours is determined for the ordering of the results set. The rows are to be listed in descending order per the DESC flag based on the expression.

Limiting SELECT results

SELECT [flags] column|expression[, ...]   FROM table[, ...]     [WHERE condition]      other clauses]     [LIMIT {[offset,] count|count OFFSET offset}]        [PROCEDURE procedure(arguments)]        [FOR UPDATE|LOCK IN SHARE MODE]]     [other clauses] [options]

The LIMIT clause is used to limit the number of rows displayed by the SELECT statement. The most straightforward method of limiting the number of rows is to specify the maximum row count to be displayed, like this:

SELECT * FROM employees    LIMIT 5;

To begin listing rows after a specific number of records, an offset may be given. The offset for the first row is 0. Two syntax structures accomplish this. One is to give the amount of the offset, followed by a comma and then the maximum count of rows to display. The other syntax structure is to specify the count followed by the OFFSET keyword, followed by the amount of the offset. Here is an example of the first structure, which is preferred:

SELECT * FROM employees    LIMIT 10, 5;

In this example, after the 10th record is reached, the next 5 records will be displayed in other words, results 11 through 15 are returned. The offset and count for the LIMIT clause are based on the rows in the results set, not necessarily on the rows in the tables. So, the amount of the offset is related to the order of the rows retrieved from the tables based on clauses, such as the WHERE clause and the ORDER BY clause.

Other SELECT clauses and options

SELECT [flags] column|expression[, ...]   FROM table[, ...]     [WHERE condition]     [other clauses]     [PROCEDURE procedure(arguments)]     [LOCK IN SHARE MODE|FOR UPDATE]

To send the results of a SELECT statement as standard input to an external script, use the PROCEDURE clause. The syntax for this clause is the PROCEDURE keyword, followed by the name of the path and filename of the external script. Any parameters or arguments to be passed to the script may be given after the script name within parentheses. Here is an example of this clause:

SELECT  * FROM employees    PROCEDURE /scripts/script.plx(250, 225);

In this statement, the results of the SELECT statement are sent to the script.plx script, located in the scripts directory. Two numeric parameters are sent to the script.

To lock the rows that are being selected from a table by a SELECT statement, the LOCK IN SHARE MODE flag may be given at the end of the statement. This will stop other clients from changing the data while the SELECT statement is running. The FOR UPDATE option will instruct MySQL to invoke a temporary write lock on the rows being selected. Both of these locks will be terminated when the statement is finished running.

SET

SET [GLOBAL|@@global.|SESSION|@@session.] variable = expression

Use this statement to set a system or user variable for global or session use. Global variables relate to all users. Session variables are available only to the connection thread that creates the variable. For system variables to be recognized as global, the GLOBAL flag is used. Alternatively, the variable can be preceded by @@global. to signify that it is global. For system variables that are limited to the current session, use the SESSION flag, or place @@session. or just @@ immediately in front of the variable name. The default for variables is to limit them to the session, to make them local. LOCAL and @@local. are aliases for SESSION and @@session., respectively. For a user variable, a single @ is placed in front of the variable name. Here is an example of creating a user variable:

SET @current_quarter = QUARTER(CURDATE( ));

This statement uses the CURDATE( ) function to determine the current date. It's wrapped in the QUARTER( ) function, which determines the quarter for the date given. The result is a number from one to four depending on the date. The number is stored in the user variable, @current_quarter. Here are a couple of examples involving system variables one using the flag method and the other using the variable prefix method:

SET GLOBAL concurrent_insert =  1; SET @@session.interactive_timeout=40000;

The first statement disables concurrent inserts without having to restart the server. The second statement changes the interactive timeout to a higher value than normal. This setting is for the current client connection only. For other clients, this variable will still contain the default value. To see a list of system variables and their values, use the SHOW VARIABLES statement. For a description of these variables, see Appendix C.

SET PASSWORD

SET PASSWORD [FOR 'user'@'host'] = PASSWORD('password')

Use this statement to change the password for a user. If the FOR clause is not given, the current user is assumed. The PASSWORD( ) function will encrypt the password given. This statement does not require the use of the FLUSH PRIVILEGES statement. It will automatically update the privileges cache for the new password.

SET PASSWORD FOR 'kenneth'@'localhost' = PASSWORD('password');

SET SQL_LOG_BIN

SET SQL_LOG_BIN = {0|1}

Use this statement to enable or disable binary logging of SQL statements for the current connection. It does not affect logging for the activities of other threads and is reset to the default value when the connection is closed. This statement requires SUPER privileges. A value of 0 disables binary logging; 1 enables it.

SET SQL_LOG_BIN = 0;

SET TRANSACTION

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}

Use this statement to set an isolation level for a transaction that's about to be started, globally or only for the current session. This statement applies only to InnoDB and BDB tables.

The GLOBAL flag indicates that the isolation level should be applied to all transactions on the server that have not yet started. The SESSION flag limits the isolation level setting to just the current session.

The READ UNCOMMITTED isolation level allows SELECT statements to read tables without locking them and to read changes to rows that are not yet committed. The READ COMMITTED option is used to ensure consistent reading of data and to avoid any outside inserts while reading a table. The REPEATABLE READ option is used to ensure consistent SELECT statements during a transaction. The SERIALIZABLE option is used to ensure consistent reads; all simple SELECT statements are converted to SELECT...LOCK IN SHARE MODE statements.

Here is an example of how you can use this statement:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION; ...

SHOW BINLOG EVENTS

SHOW BINLOG EVENTS [IN 'filename']     [FROM position] [LIMIT [offset,] count]

Use this statement to display the events in a binary logfile. Use the IN clause to specify a particular logfile. If the IN clause is omitted, the current file is used. To obtain a list of binary logfiles, use the SHOW MASTER LOGS statement. Here is an example of how you can use this statement with the results following the SQL statement:

SHOW BINLOG EVENTS IN 'log-bin.000161'\G *************************** 1. row ***************************     Log_name: log-bin.000161          Pos: 4   Event_type: Start    Server_id: 1 Orig_log_pos: 4         Info: Server ver: 4.1.7-standard-log, Binlog ver: 3 1 row in set (0.00 sec)

This logfile has only one row of data, because the SQL statement was run shortly after the server was started. For a larger logfile with many rows of events recorded, you can focus and limit the results with the FROM and LIMIT clauses. In the results, notice the Pos label with a value of 4. In a large logfile, that number might be higher, in the thousands. The results displayed could be focused only to rows starting from a particular position in the log with the FROM clause. You can limit the number of rows of events displayed with the LIMIT clause. In the LIMIT clause, you can set the starting point of the output based on the number of rows in the results set and limit them to a certain number of rows. Here is an example of both of these clauses:

SHOW BINLOG EVENTS IN 'log-bin.000160' FROM 3869 LIMIT 2,1\G *************************** 1. row ***************************     Log_name: log-bin.000160          Pos: 4002   Event_type: Intvar    Server_id: 1 Orig_log_pos: 4002         Info: INSERT_ID=5

In this example, the retrieval of log events is to begin from position 3869 because of the FROM clause. The results set contains several rows, although only one is shown here. The display is limited to one row, starting from the third one in the results set per the LIMIT clause.

SHOW CHARACTER SET

SHOW CHARACTER SET [LIKE 'pattern']

This statement will show all of the character sets installed on the server. To be more selective, use a pattern with the LIKE clause. For instance, to list all of the character sets beginning with the name latin, enter the following:

SHOW CHARACTER SET LIKE 'latin%'\G *************************** 1. row ***************************           Charset: latin1       Description: ISO 8859-1 West European Default collation: latin1_swedish_ci            Maxlen: 1 *************************** 2. row ***************************           Charset: latin2       Description: ISO 8859-2 Central European Default collation: latin2_general_ci            Maxlen: 1 *************************** 3. row ***************************           Charset: latin5       Description: ISO 8859-9 Turkish Default collation: latin5_turkish_ci            Maxlen: 1 *************************** 4. row ***************************           Charset: latin7       Description: ISO 8859-13 Baltic Default collation: latin7_general_ci            Maxlen: 1

To see the default character set, use the SHOW VARIABLES statement. To change the client's character set, use the SET CHARACTER SET statement.

SHOW COLLATION

SHOW COLLATION [LIKE 'pattern']

Use this statement to list all of the collation character sets. You can use the LIKE clause to list character sets based on a naming pattern. This statement is available as of Version 4.1 of MySQL. Here is an example:

SHOW COLLATION LIKE '%bin%';

In this example, character sets that contain the letters bin in their name will be listed. These are binary character sets.

SHOW COLUMNS

SHOW [FULL] COLUMNS FROM table [FROM database] [LIKE 'pattern']

Use this statement to display the columns for a given table. If the table is not in the current default database, the FROM database clause may be given to name another database. You can use the LIKE clause to list only columns that match a naming pattern given in quotes.

SHOW COLUMNS FROM clients FROM workrequests LIKE 'client%'; +-------------+-------------+------+-----+---------+-------+ | Field       | Type        | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | client_id   | varchar(4)  |      | PRI |         |       | | client_name | varchar(50) | YES  |     | NULL    |       | +-------------+-------------+------+-----+---------+-------+

In this example, only information for columns beginning with the name client are retrieved. The following example is for just the client_id column and uses the FULL flag along with the alternate display method (\G):

SHOW FULL COLUMNS FROM clients FROM workrequests LIKE 'client_id'\G *************************** 1. row ***************************      Field: client_id       Type: varchar(4)  Collation: latin1_swedish_ci       Null:        Key: PRI    Default:      Extra: Privileges: select,insert,update,references    Comment:

Notice that information on collation and the user's privileges with regard to the column is provided.

SHOW CREATE DATABASE

SHOW CREATE DATABASE database

Use this statement to display an SQL statement that can be used to create a database like the one given. This statement is mostly useful for determining the default character set. It's available as of Version 4.1 of MySQL.

SHOW CREATE DATABASE richard_stringer\G *************************** 1. row *************************** Database: richard_stringer Create Database: CREATE DATABASE `richard_stringer`                  /*!40100 DEFAULT CHARACTER SET latin2 */

SHOW CREATE TABLE

SHOW CREATE TABLE table

Use this statement to display an SQL statement that can be used to create a table like the one named. The results may be copied and used with another database. They also could be copied and the name of the table modified so that the statement may be used on the same database.

SHOW CREATE TABLE programmers\G *************************** 1. row *************************** Table: programmers Create Table: CREATE TABLE `programmers` (               `prog_id` varchar(4) NOT NULL default '',               `prog_name` varchar(50) NOT NULL default '',               PRIMARY KEY  (`prog_id`)               ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Notice that the results include the table type and other default options.

SHOW CREATE VIEW

SHOW CREATE VIEW view

Use this statement to display an SQL statement that can be used to create a view like the one named. The results may be copied and used with another database. They also could be copied and the name of the view modified so that the statement may be used on the same database. This statement is available as of Version 5.0.1 of MySQL.

SHOW CREATE VIEW employee_directory\G *************************** 1. row *************************** Table:  employees Create Table: CREATE VIEW `employee_directory`.`personnel`               (`ID`, `Name` `Ext.`,)               AS SELECT emp_id,               CONCAT(emp_first, ` `, emp_last),               tel_extension               FROM employees;

This view is the same one that is created in the example given for the CREATE VIEW statement earlier. Notice that the database name (personnel) has been added to the end of the view name (employee_directory).

SHOW DATABASES

SHOW DATABASES [LIKE 'pattern']

This statement displays a list of databases for the server. Using the LIKE clause, a naming pattern may be given. For example, suppose that a server has a separate database for each customer of the organization and that the pattern for the names of the databases is cust_number, where the number is the customer account number. You could enter the following SQL statement to obtain a list of databases based on this pattern:

SHOW DATABASES LIKE 'cust_%';

SHOW ENGINES

SHOW [STORAGE] ENGINES

This statement will list the table types or storage engines available for the version of MySQL running on the server. It will state which are disabled on the server and which are enabled, as well as which is the default type. It will also provide comments on each type. The STORAGE keyword is optional and has no effect on the results. This SQL statement replaces SHOW TABLE TYPES, which produced the same results, but is deprecated.

SHOW ERRORS

SHOW [COUNT(*)] ERRORS [LIMIT [offset,] count]

Use this statement to display error messages for previous SQL statements for the current session. To get a total number of error messages, use the COUNT(*) clause. This cannot be used with the LIMIT clause, though. The LIMIT clause is used to limit the number of errors displayed. An offset can be given along with the count to specify a starting point for displaying error messages.

This statement is available as of Version 4.1 of MySQL. It will not display warnings or notes just error messages. Use the SHOW WARNINGS statement to get all three.

Here are a couple of examples of how you can use this statement:

SHOW ERRORS; SHOW COUNT(*) ERRORS;

The first line displays all error messages in an ASCII-formatted table. The second line returns the number of error messages.

SHOW GRANTS

SHOW GRANTS FOR user

This SQL statement displays the GRANTS statement for a given user. This is useful in duplicating an existing user's privileges for another. If the username is given without reference to a particular host, the wildcard % is assumed. Otherwise, the username should be stated with the related host as shown here:

SHOW GRANTS FOR 'russell'@'localhost'\G *************************** 1. row *************************** Grants for russell@localhost: GRANT ALL PRIVILEGES ON *.* TO  'russell'@'localhost' IDENTIFIED BY PASSWORD '1ajk6f3845a1bbca'  WITH GRANT OPTION

To reduce clutter, this example is issued with \G instead of a semicolon to omit table formatting of the results. Notice that with the exception of the encrypted password in single quotes, the resulting statement is what would be entered to create the user russell for the host localhost, with the given privileges including the WITH GRANT OPTION flag.

SHOW INDEX

SHOW INDEX FROM table [FROM database]

This SQL statement will display information on the indexes for a given table. A table from a different database can be specified by either naming it with the table (e.g., database.table) or adding the FROM clause.

SHOW INDEX FROM contacts FROM sales_dept\G *************************** 1. row ***************************        Table: contacts   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: rec_id    Collation: A  Cardinality: 5     Sub_part: NULL       Packed: NULL         Null:   Index_type: BTREE      Comment:

In this example, instead of ending the statement with a semicolon, which would insert extra table formatting, a \G is used to alter the display to the format shown. This table has only one index, so only one is listed here. For each index, the table name is given. This is followed by a field indicating whether the index is nonunique. A unique index is indicated by 0. The name of the index or key (e.g., PRIMARY) is shown next. For indexes that use only one column, the key name and the column name are often the same. For indexes that use more than one column, a row will be listed for each column, each row having the same table name and the same key name. The sequence of the column in the table is given, where 1 is the first column. The name of the column or columns indexed is given next. Then the collation, how the column is sorted in the index, is specified. A value of A is given for ascending, D for descending. If the index is not sorted, the value would be NULL. The cardinality is based on the number of unique indexes contained in the column. This is used for determining whether an index is used with a JOIN. The higher the cardinality, the more likely it will be used. The Sub_part field indicates the number of characters of the column that are indexed for partially indexed columns (see the CREATE INDEX statement). This is NULL if all of the column is indexed. The Packed field indicates the method by which the key is packed. This index isn't packed, so the value for the field in this example is NULL. If the column may contain NULL, the NULL field will read NULL. Otherwise, it will be blank, as shown in the example. Index_type is the type of method used. The possibilities are BTREE, FULLTEXT, and HASH. Starting with Version 5.0.1 of MySQL, RTREE will be another possibility. The Comments field contains any comments associated with the index.

SHOW INNODB STATUS

SHOW INNODB STATUS

Use this statement to display details on the status of the InnoDB storage engine. This statement provides information on table and record locks for transactions, waiting locks, pending requests, buffer statistics and activity, and logs related to the engine.

SHOW LOGS

SHOW [BDB] LOGS

Use this statement to show information on logs for MySQL server. The type of log may be given as a qualifier to the statement. However, currently information only on logs for BDB tables is provided. The statement returns a results set containing the path and name of the logfile, the type of log (e.g., BDB), and the status of the log. The status possibilities are FREE if the file isn't being used at the moment and IN USE if it is.

SHOW MASTER LOGS

SHOW MASTER LOGS

This statement displays a list of binary logs created by the master MySQL server in the filesystem directory. To delete logs, use the PURGE MASTER LOGS statement. For information on enabling logs, see Chapter 10.

SHOW MASTER STATUS

SHOW MASTER STATUS

This statement displays information on the status of the binary logfile that is being used currently on the master MySQL server.

SHOW MASTER STATUS; +----------------+----------+--------------+------------------+ | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | +----------------+----------+--------------+------------------+ | log-bin.000141 |     1123 |              |                  | +----------------+----------+--------------+------------------+

SHOW PRIVILEGES

SHOW PRIVILEGES

This statement provides a list of privileges available, the context of each (e.g., server administration), and a description of each. It is not based on the user. Instead, it's a complete listing of the privileges that may be assigned to a user. This statement is available as of Version 4.1 of MySQL.

SHOW PROCESSLIST

SHOW [FULL] PROCESSLIST

This statement displays a list of connection threads running on the MySQL server. This statement requires SUPER privileges to be able to see all threads. Otherwise, only threads related to the current connection are shown. The FULL keyword shows the full text of the information field.

SHOW PROCESSLIST\G *************************** 1. row ***************************      Id: 1    User: root    Host: localhost      db: workrequests Command: Query    Time: 0   State: NULL    Info: SHOW PROCESSLIST

You can use this statement to determine a thread identification number to be used with the KILL statement.

SHOW SLAVE HOSTS

SHOW SLAVE HOSTS

This statement displays a list of slave servers for the master server. Slaves must be started with the --report-host=slave option to be shown.

SHOW SLAVE STATUS

SHOW SLAVE STATUS

This statement displays information on the slave thread. Here is an example of this statement and its results:

SHOW SLAVE STATUS\G *************************** 1. row ***************************        Slave_IO_State: Waiting for master to send event           Master_Host: localhost           Master_User: root           Master_Port: 3306         Connect_Retry: 5       Master_Log_File: log-bin.000154   Read_Master_Log_Pos: 159        Relay_Log_File: log-relay-bin.154         Relay_Log_Pos: 694 Relay_Master_Log_File: log-bin.154      Slave_IO_Running: Yes     Slave_SQL_Running: Yes       Replicate_Do_DB:   Replicate_Ignore_DB:            Last_Errno: 0            Last_Error:          Skip_Counter: 0   Exec_Master_Log_Pos: 159       Relay_Log_Space: 694       Until_Condition: None        Until_Log_File:         Until_Log_Pos: 0    Master_SSL_Allowed: Yes    Master_SSL_CA_File: ssl_ca.dat    Master_SSL_CA_Path: /data/mysql/ssl_ca       Master_SSL_Cert: ssl_cert.dat     Master_SSL_Cipher:        Master_SSL_Key: Seconds_Behind_Master: 3

You can set some of these values at startup with the MySQL server daemon (mysqld). See Chapter 10 for more information on setting server variables at startup. You can set some of these variables with the SET statement. You can adjust others for particular tables with the ALTER TABLE statement. You can reset some of the logfile variables with the RESET MASTER and RESET SLAVE statements.

SHOW STATUS

SHOW STATUS [LIKE 'pattern']

This statement displays status information and variables from the server. You can reduce the number of variables with the LIKE clause, based on a naming pattern for the variable name. Here is an example of how you can use this statement:

SHOW STATUS LIKE '%log%'; +------------------------+-------+ | Variable_name          | Value | +------------------------+-------+ | Binlog_cache_disk_use  | 0     | | Binlog_cache_use       | 3     | | Com_show_binlog_events | 0     | | Com_show_binlogs       | 0     | | Com_show_logs          | 0     | +------------------------+-------+

In this example, the results are limited only to variables that contain the word log in their names. You can change these variables at startup with certain options for the MySQL server daemon. See Chapter 10. You can change some of them while the daemon is running with the SET statement, without having to restart the server.

SHOW TABLE STATUS

SHOW TABLE STATUS [FROM database] [LIKE 'pattern']

This statement displays status information on a set of tables from a database. To obtain the status of tables from a database other than the current default one, use the FROM clause. The results will include information on all of the tables of the database unless the LIKE clause is used to limit the tables displayed by a naming pattern.

SHOW TABLE STATUS FROM workrequests LIKE 'workreq'\G *************************** 1. row ***************************            Name: workreq          Engine: MyISAM         Version: 7      Row_format: Dynamic            Rows: 543  Avg_row_length: 983     Data_length: 534216 Max_data_length: 4294967295    Index_length: 6144       Data_free: 120  Auto_increment: 5772     Create_time: 2002-04-23 14:41:58     Update_time: 2004-11-26 16:01:46      Check_time: 2004-11-28 17:21:20       Collation: latin1_swedish_ci        Checksum: NULL  Create_options:         Comment:

In this example, the number of tables is limited to one, because a specific table name is given in the LIKE clause without the % wildcard. Incidentally, the display here is not in the typical ASCII table format, because the statement ends with a \G instead of the usual semicolon. You can change some of these variables or table options using the ALTER TABLE statement.

SHOW TABLES

SHOW [OPEN] TABLES [FROM database] [LIKE 'pattern']

This statement displays a list of tables. For a listing of tables that is currently being used by queries, add the OPEN flag. The tables shown will not include temporary tables and will be from the current default database. To list tables from another database, add the FROM clause along with the name of the database. You can reduce the list of tables to ones with a name meeting a given naming pattern. As of Version 5.0.1 of MySQL, the results set will include tables and views for the database. It will also include a second column to specify whether a row is for a table or a view. This is indicated by the values of BASE TABLE and VIEW, respectively.

SHOW TABLES FROM workrequests LIKE 'work%';

This statement will list all of the tables and views (if the server is running Version 5.0.1 or higher) with a name that begins with the word "work," for the database workrequests. Incidentally, by default, only tables for which the user has privileges will be listed.

SHOW VARIABLES

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 'pattern']

This statement displays the system variables for the MySQL server. The SESSION flag will display values for current sessions or connections. This is the default and is synonymous with LOCAL. The GLOBAL flag will provide variables that will relate to new connections. You can limit the variables with the LIKE clause and a naming pattern for the variables. Here is an example of this statement:

SHOW VARIABLES LIKE 'version%'; +-------------------------+--------------------+ | Variable_name           | Value              | +-------------------------+--------------------+ | version                 | 4.1.7-standard-log | | version_comment         | Official MySQL RPM | | version_compile_machine | i686               | | version_compile_os      | pc-linux           | +-------------------------+--------------------+

In this example, the variables shown are limited to variable names beginning with the word "version."

SHOW WARNINGS

SHOW [COUNT(*)] WARNINGS [LIMIT [offset,] count]

Use this statement to display warning messages, error messages, and notes for previous SQL statements for the current session. To get a count of the number of such messages, use the COUNT(*) clause. You cannot use this with the LIMIT clause. The LIMIT clause is used to limit the number of messages displayed. An offset can be given along with the limit to specify a starting point for displaying messages. This statement is available as of Version 4.1 of MySQL. Here are a couple of examples of how you can use this statement:

SHOW WARNINGS; SHOW COUNT(*) WARNINGS;

The first line displays all messages in an ASCII-formatted table. The second line returns the number of messages. Here's another example, but with the LIMIT clause:

SHOW WARNINGS LIMIT 3,2;

START SLAVE

START SLAVE [IO_THREAD|SQL_THREAD] START SLAVE [SQL_THREAD]     UNTIL MASTER_LOG_FILE = 'filename', MASTER_LOG_POS = position START SLAVE [SQL_THREAD]     UNTIL RELAY_LOG_FILE = 'filename', RELAY_LOG_POS = position

Use this statement to start a slave server. In the first syntax structure shown, you can start the slave with the I/O thread or just with the SQL thread by using the respective keyword. You can start both by listing both keywords, separated by a comma. The default is to start both. The I/O thread will cause the slave to read SQL queries from the master server and to record them in the relay logfile. The SQL thread will read the relay logfile and then execute the SQL statements.

The second syntax structure is used to limit the reading of the threads to a specific point (given with MASTER_LOG_POS) in the master logfile (named with the MASTER_LOG_FILE parameter). With the UNTIL clause, processing of the logfiles given will stop when the position given is reached. The third syntax structure is used to specify the relay log file and to limit the reading of it. If the SQL_THREAD keyword is given in either of these latter two syntax structures, the reading will be limited to the SQL thread.

The starting of a slave thread isn't always dependable. Run the SHOW SLAVE STATUS statement to confirm that the thread began and remained running.

START TRANSACTION

START TRANSACTION

Use this statement to start a transaction, or a set of SQL statements for an InnoDB or a BDB table. Transaction statements are ignored when you use them with MyISAM tables. The purpose of a transaction is to be able to undo SQL statements if need be. You can reverse a transaction if the transaction has not yet been committed either with a COMMIT statement, implicitly by starting another transaction, or by terminating the connection. In earlier versions of MySQL, BEGIN or BEGIN WORK were used instead of START TRANSACTION. See the explanation of the COMMIT statement and the ROLLBACK statement for more information on transactions. The SAVEPOINT statement and the ROLLBACK TO SAVEPOINT statements may also be useful.

Here is an example of this statement's use:

START TRANSACTION; INSERT DATA INFILE '/tmp/customer_orders.sql'   INTO TABLE orders; COMMIT;

In this example, after the batch of orders was inserted into the orders table, if there was a problem the ROLLBACK statement could be issued instead of the COMMIT statement shown here. It would remove the data imported by the INSERT DATA INFILE statement. The ROLLBACK statement works only with InnoDB and BDB tables. If everything seems alright, the COMMIT statement would be issued to commit the entries and to end the transaction started with the START TRANSACTION statement.

STOP SLAVE

STOP SLAVE [IO_THREAD|SQL_THREAD]

Use this statement to stop slave server threads. To stop a specific type of slave thread, one or both of the thread types may be given. Both may be given in a comma-separated list. The default is to stop both. You can start slave threads with the START SLAVE statement.

TRUNCATE TABLE

TRUNCATE TABLE table

Use this statement to delete the contents of a table rapidly. This statement is similar to the DELETE statement in that it will delete all of the data contained in a given table. Its method is to delete the table and then to re-create the table, but without data. As a result, this statement is faster than using DELETE. However, the trUNCATE statement does not report the number of rows deleted. Another minor drawback to this statement is that the value for an AUTO_INCREMENT column will be lost along with the data.

UNION

SELECT  . . .  UNION [ALL|DISTINCT] [SELECT  . . . ]

The UNION keyword unites the results of multiple SELECT statements into one results set. The SELECT statements can retrieve data from the same table or from different tables. If different tables are used, the results set generated by each SQL statement should match in column count and in the order of column types. The column names do not need to be the same, but the data sent to the respective fields in the results set needs to match. Here is an example of a UNION used to merge the results of two SELECT statements:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,        telephone_work AS Telephone    FROM employees UNION SELECT location, telephone FROM branches ORDER BY Name;

This statement will present a list of employees and branch office locations in one column, with the telephone number for each in the second. The column headings used for the results set will be the ones used for the first SELECT statement. Because of the ORDER BY clause, the results will be sorted by the values for the alias Name. Otherwise, the names of employees would be listed before the names of offices.

The example shown merges the results of only two SELECT statements. You can merge several SELECT statements, entering the UNION keyword between each one.

UNLOCK TABLES

UNLOCK TABLES

Use this statement to unlock tables that were locked by the current connection thread with the LOCK TABLES statement. See the description of the ROLLBACK statement for an example of its use.

USE

USE database

This statement sets the default database MySQL is to use for the current session. This allows the name of the default database to be omitted from statements. For instance, db1.table1 can be written as just table1, and db1 is assumed.



MySQL in a Nutshell
MYSQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596514336
EAN: 2147483647
Year: 2006
Pages: 134
Authors: Russell Dyer

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net