SQL Syntax for MySQL

DRAFT, 8/24/01 Chapter 16 In this chapter, interested in co SQL2 standard. I sions to the SQL Basic Syn SQL is a kind o verb phrases be als, identfiers, o database names tem is case sens underlying file s however, avoid ment using diffe sitive. For examp SELECT TBL.C Table aliases are If all of this case convert all table lower_case_table Literals Literals come in tax f controlled English language gin with a SQL command follo r punctuation. Keywords are and table names are case sen itive (all UNIX except Mac OS ystem is case insensitive (Mac referring to the same database rent cases—even if the underly le, the following SQL is trouble OL FROM tbl; case sensitive, but column alia sensitivity nonsense is annoyi names to lower case by star _names=1. the following varieties:Copyright © 2001 O’Reing to you, you can force MySQL to ting mysqld with the argument -OX) and case insensitive when the OS X and Windows). You should, or table name in a single SQL state- ing operating system is case insen- some: ses are case insensitive.16 16.SQL Syntax for MySQL we cover the full range of SQL supported by MySQL. If you are mpatibility with other SQL databases, MySQL supports the ANSI n that case, you should avoid using any proprietary MySQL exten- standard. consisting of verb phrases. These wed by other SQL keywords, liter- never case sensitive. Identifiers for sitive when the underlying file sys-293 lly & Associates, Inc. DRAFT, 8/24/01 String Literals String literal wish to be string literal An escape s to MySQL t meaning. Ta escaped by do not need double quot Binary Literals Like string li must use es 34), ‘ (ASCI Number Literals Numbers ap - sign and a -45198.2164e Hexadecimal Lit MySQL also that hexadec text, the hex context, it is itself is ‘MyS Null The special import files, Table 16-1. . MySQ Escape Sequence \0 \’ \” \b \n \r \t \z \\s may be enclosed either by single quotes or double quotes. If you ANSI compatible, you should always use single quotes. Within a , you may represent special characters through escape sequences. equence is a backslash followed by another character to indicate hat the second character has a meaning other than its normal ble 16-1 shows the MySQL escape sequences. Quotes can also be doubling them up: ‘This is a ‘’quote’’’. However, you to double up on single quotes when the string is enclosed by es: "This is a ‘quote’". terals, binary literals are enclosed in single or double quotes. You cape sequences in binary data to escape NUL (ASCII 0), " (ASCII I 39), and \ (ASCII 92). pear as a sequence of digits. Negative numbers are preceded by a . indicates a decimal point. You may also use scientific notation: +10. erals supports the use of hexadecimal literals in SQL. The way in which imal is interpreted is dependent on the context. In a numeric con- adecimal literal is treated is a numeric value. Absent of a numeric treated as a binary value. This 0x1 + 1 is 2, but 0x4d7953514c by QL’. keyword NULL signifies a null literal in SQL. In the context of the special escape sequence \N signifies a null value. L Escape Sequences Value NUL Single quote Double quote Backspace Newline Carriage return Tab Ctrl-z (workaround for Windows use of ctrl-z as EOF) BackslashCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Identifiers Identifiers are na base objects con archical namesp ence any given o in one of the fol Absolute Naming Absolute na For example BANK would BANK.ACCO Relative Naming Relative nam rest of the n if you are c BANK.ACCO query where you can ref extra layer o example of tables that b Aliasing Aliasing ena avoid both a In general, MyS limited, howeve files on the loca underlying file s \% \_ * Older versions of M set as well as $ and Table 16-1. . MySQ Escape Sequencemes you make up to reference database objects. In MySQL, data- sist of databases, tables, and columns. These objects fit into a hier- ace whose root element is the database in question. You can refer- bject on a MySQL server—assuming you have the proper rights— lowing conventions: ming is specifying the full tree of the object you are referencing. , the column BALANCE in the table ACCOUNT in the database be referenced absolutely as: UNT.BALANCE ing allows you to specify only part of the object’s name with the ame being assumed based on your current context. For example, urrently connected to the BANK database, you can reference the UNT.BALANCE column simply as ACCOUNT.BALANCE. In a SQL you have specified you are selecting from the ACCOUNT table, erence the column using only BALANCE. You must provide an f context whenever relative naming might result in ambiguity. An such ambiguity would be a SELECT statement pulling from two oth have BALANCE columns. bles you to reference an object using an alternate name that helps mbiguity and the need to fully qualify a long name. QL allows you to use any character in an identifier.* This rule is r, for databases and tables since these values must be treated as l file system. You can therefore use only characters valid for the ystem’s file naming conventions in a database or table name. Spe- Percent sign (only in contexts where a percent sign would be inter- preted as a wild card) Underscore (only in contexts where an underscore would be inter- preted as a wild card) ySQL limited identifiers to valid alphanumeric characters from the default character _. L Escape Sequences ValueCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 cifically, you ma NUL (ASCII 0) o Given these rule As a general rul ever character se When an identif ticks: CREATE TABLE Since MySQL 3. ticks and double quotes for quot mode. Comments You can introdu preted by MySQ loading data. M script, and ANSI C commenting t of commenting, /* * Creates a */ DROP TABLE I CREATE TABLE Within C comme to a string litera think you are do Shell-script comm comment: CREATE TABLE MySQL does no SQL commentin space (‘-- ‘) f DROP TABLE Iy not use / or . in a database or table name. You can never use r ASCII 255 in an identifier. s, it is very easy to shoot yourself in the foot when naming things. e, it is a good idea to stick to alphanumeric characters from what- t you are using. ier is also a SQL keyword, you must enclose the identifier in back- `select` ( `table` INT NOT NULL PRIMARY KEY AUTO_INCREMENT); 23.6, MySQL supports the quoting of identifiers using both back- quotes. For ANSI compatibility, however, you should use double ing identifiers. You must, however, be running MySQL in ANSI ce comments in your SQL to specify text that should not be inter- L. This is particularly useful in batch scripts for creating tables and ySQL specifically supports three kinds of commenting: C, shell- SQL commenting. reats anything between /* and */ as comments. Using this form your comments can span multiple lines. For example: table for storing customer account information. F EXISTS ACCOUNT; ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, BALANCE DECIMAL(9,2) NOT NULL ); nts, MySQL still treats single quotes and double quotes as a start l. In addition, a semi-colon in the comment will cause MySQL to ne with the current statement. enting treats anything from a # character to the end of a line as a ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, BALANCE DECIMAL(9,2) NOT NULL ); # Not null ok? t really support ANSI SQL commenting, but it comes close. ANSI g is -- to the end of a line. MySQL supports two dashes and a ollowed by the comment. The space is the non-ANSI part: F EXISTS ACCOUNT; -- Drop the table if it already existsCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 SQL Com ALTER TABL Syntax ALTER [IG Description The ALTER state table. This statem table as well as creates a copy o queries. When th it its place. At t tion, if any of th ALTER statement the statement, d ceeds as if norm keys is inviting t ADD [COLUMN] Adds a new would defin ated as the can use the after. If neit the end of t separating c ADD INDEX [n Adds an ind automaticall ADD PRIMARY Adds a prim occurs if the ADD UNIQUE[n Adds a uniqmands E NORE] TABLE table action_list ment covers a wide range of actions that modify the structure of a ent is used to add, change, or remove columns from an existing to remove indexes. To perform modifications on the table, MySQL f the table and changes it, meanwhile queuing all table altering e change is done, the old table is removed and the new table put his point the queued queries are performed. As a safety precau- e queued queries create duplicate keys that should be unique, the is rolled back and cancelled. If the IGNORE keyword is present in uplicate unique keys are ignored and the ALTER statement pro- al. Be warned that using IGNORE on an active table with unique able corruption. Possible actions include: create_clause [FIRST | AFTER column] column to the table. The create_clause is simply the SQL that e the column in a normal table creation. The column will be cre- first column if the FIRST keyword is specified. Alternately, you AFTER keyword to specify which column it should be added her FIRST nor AFTER is specified, then the column is added at he table’s column list. You may add multiple columns at once by reate clauses by commas. ame] (column, ...) ex to the altered table. If the name is omitted, one will be chosen y by MySQL. KEY (column, ...) ary key consisting of the specified columns to the table. An error table already has a primary key. ame] (column, ...) ue index to the altered table similar to the ADD INDEX statement.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 ALTER [COLUMN] column SET DEFAULT value Assigns a new default value for the specified column. The COLUMN keyword is optional and ALTER [COLUM Drops the c will be assig ate the table CHANGE [COLU MODIFY [COLUM Alters the d from one ty The create c includes the if the new optional and conversion. when using With this in version and able. DROP [COLUMN Deletes a co its data from in this mann ces will be destroyed as DROP PRIMARY Drops the p the first uniq DROP INDEX key Removes an from a table only the ind ated using th has no effect. N] column DROP DEFAULT urrent default value for the specified column. A new default value ned to the column based on the CREATE statement used to cre- . The COLUMN keyword is optional and has no effect. MN] column create_clause N] create_clause efinition of a column. This statement is used to change a column pe to a different type while affecting the data as little as possible. lause is a full clause as specified in the CREATE statement. This name of the column. The MODIFY version is the same as CHANGE column has the same name as the old. The COLUMN keyword is has no effect. MySQL will try its best to perform a reasonable Under no circumstance will MySQL give up and return an error this statement; a conversion of some sort will always be done. mind you should (1) make a backup of the data before the con- (2) immediately check the new values to see if they are reason- ] column lumn from a table. This statement will remove a column and all of a table permanently. There is no way to recover data destroyed er other than from backups. All references to this column in indi- removed. Any indices where this was the sole column will be well. (The COLUMN keyword is optional and has no effect.) KEY rimary key from the table. If no primary key is found in the table, ue key is deleted. index from a table. This statement will completely erase an index . This statement will not delete or alter any of the table data itself, ex data. Therefore, an index removed in this manner can be recre- e ALTER TABLE ...ADD INDEX statement.Copyright © 2001 O’Reilly & Associates, Inc. RENAME [AS] ne RENAME [TO] ne Changes the or indices w formed alon ate a tempo Unix-level re ORDER BY colu Forces the t The table w option is use table_options Enables a re Multiple ALTER lowing example: ALTER TABLE MySQL also pro nothing. . The sy To perform any DELETE,UPDATE Examples # Add the fi # it of type ALTER TABLE # Add two ne # 'salary' f # operation # the 'id_id ALTER TABLE ALTER IGNORE # Change the # 'sprockets ALTER TABLE # Remove the ALTER TABLE # Change the # perhaps IN ALTER TABLE # Remove the ALTER TABLE # Delete the # table 'car ALTER TABLE # Rename the ALTER TABLE DRAFT, 8/24/01 w_table w_table name of the table. This operation does not affect any of the data ithin the table, only the table’s name. If this statement is per- e, without any other ALTER TABLE clauses, MySQL will not cre- rary table as with the other clauses, but simply perform a fast name of the table files. mn able to be re-ordered by sorting on the specified column name. ill no longer be in this order when new rows are inserted. This ful for optimizing tables for common sorting queries. definition of the tables options such as the table type. statements may be combined into one using commas as in the fol- mytable DROP myoldcolumn, ADD mynewcolumn INT vides support for actions to alter the FOREIGN KEY, but they do ntax is there simply for compatibility with other databases. of the ALTER TABLE actions, you must have SELECT, INSERT, ,CREATE, and DROP privileges for the table in question. eld 'address2' to the table 'people' and make 'VARCHAR' with a maximum length of 200. people ADD COLUMN address2 VARCHAR(100) w indexes to the 'hr' table, one regular index for the ield and one unique index for the 'id' field. Also, continue if duplicate values are found while creating x' index (very dangerous!). hr ADD INDEX salary_idx ( salary ) TABLE hr ADD UNIQUE id_idx ( id ) default value of the 'price' field in the ' table to $19.95. sprockets ALTER price SET DEFAULT '$19.95' default value of the 'middle_name' field in the 'names' table. names ALTER middle_name DROP DEFAULT type of the field 'profits' from its previous value (which was TEGER) to BIGINT. finanaces CHANGE COLUMN profits profits BIGINT 'secret_stuff' field from the table 'not_private_anymore' not_private_anymore DROP secret_stuff named index 'id_index' as well as the primary key from the s'. cars DROP INDEX id_index, DROP PRIMARY KEY table 'rates_current' to 'rates_1997'Copyright © 2001 O’Reilly & Associates, Inc. rates_current RENAME AS rates_1997 DRAFT, 8/24/01 CREATE DATABASE Syntax CREATE DA Description Creates a new d leges to create mysqladmin crea Example CREATE DATAB CREATE FUNC Syntax CREATE [A RETURNS re Description The CREATE F piled executable since they are d function can be INTEGER for inte tion to the indi standard shared Example CREATE FUNCT CREATE IND Syntax CREATE [U Description The CREATE IN tations of SQL. this statement isTABASE dbname atabase with the specified name. You must have the proper privi- the database. Running this command is the same as running the te utility. ASE Bank; TION GGREGATE] FUNCTION name turn_type SONAME library UNCTION statement allows MySQL statements to access precom- functions. These functions can perform practically any operation, esigned and implemented by the user. The return value of the STRING, for character data; REAL, for floating point numbers; or ger numbers. MySQL will translate the return value of the C func- cated type. The library file that contains the function must be a library that MySQL can dynamically link into the server. ION multiply RETURNS REAL SONAME mymath EX NIQUE] INDEX name ON table (column, ...) DEX statement is provided for compatibility with other implemen- In older versions of SQL this statement does nothing. As of 3.22, equivalent to the ALTER TABLE ADD INDEX statement. To per-Copyright © 2001 O’Reilly & Associates, Inc. form the CREATE in question. Example CREATE UNIQU CREATE TAB Syntax CREATE [T (create_c [[IGNORE| Description The CREATE TA This statement is used, the table e is dropped first The IF NOT EX not already exis IF NOT EXIST PORARY is speci existing table wi porary table’s lif This statement c field definitions. by its type, follo supports the dat DEFAULT value This attribut table withou not defined, which case M NOT NULL This attribut NULL value. NULL will ge NULL This attribut the default iDRAFT, 8/24/01 INDEX statement, you must have INDEX privileges for the table E INDEX TransIDX ON Translation ( language, locale, code ); LE EMPORARY] TABLE [IF NOT EXISTS] table lause, ...) [table_options] REPLACE] select] BLE statement defines the structure of a table within the database. how all MySQL tables are created. If the TEMPORARY keyword is xists only as long as the current client connection exists, unless it ISTS clause tells MySQL to create the table only if the table does t. If the table does exist, nothing happens. If the table exists and S and TEMPORARY are not specified, an error will occur. If TEM- fied and the table exists but IF NOT EXISTS is not specified, the ll simply be invisible to this client for the duration of the new tem- e. onsists of the name of the new table followed by any number of The syntax of a field definition is the name of the field followed wed by any modifiers (e.g., name char(30) not null). MySQL a types described in Chapter 17. The allowed modifiers are: e assigns a default value to a field. If a row is inserted into the t a value for this field, this value will be inserted. If a default is a null value is inserted unless the field is defined as NOT NULL in ySQL picks a value based on the type of the field. e guarantees that every entry in the column will have some non- Attempting to insert a NULL value into a field defined with NOT nerate an error. e specifies that the field is allowed to contain NULL values. This is f neither this nor the NOT NULL modifier are specified. Fields thatCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 are containe ignored, wit PRIMARY KEY This attribut table. Only key must als REFERENCES t PARTIAL] [ON This attribut syntax but d it easier to i ity may be in MySQL supports Database Design included with th FULLTEXT ( co Since MySQ text index, u CREATE TA ); KEY/INDEX [na Creates a re context, are name is pro trailing num column, left Consider the INDEX idx1 ( When this in — name, — name, — name PRIMARY KEY Creates the be definedd within an index cannot contain the NULL modifier. (It will be hout warning, if it does exist in such a field.) e automatically makes the field the primary key (see later) for the one primary key may exist for a table. Any field that is a primary o contain the NOT NULL modifier. able [(column, . . .)] [MATCH FULL | MATCH DELETE option] [ON UPDATE option] e currently has no effect. MySQL understands the full references oes not implement its behavior. The modifier is included to make mport SQL from different SQL sources. In addition, this functional- cluded in a future release of MySQL. the concept of an index of a table, as described in Chapter 8, . Indexes are created by means of special “types” that are e table definition: lumn, ... ) L 3.23.23, MySQL has supported full text indexing. To create a full se the FULLTEXT keyword: BLE Item ( itemid INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL, description TEXT NOT NULL, FULLTEXT ( name, description ) me] (column, ...) gular index of all of the named columns (KEY and INDEX, in this synonyms). Optionally the index may be given a name. If no vided, a name is assigned based on the first column given and a ber, if necessary, for uniqueness. If a key contains more than one most subsets of those columns are also included in the index. following index definition. name, rank, serial ); dex is created, the following groups of columns will be indexed: rank, serial rank primary key of the table. A primary key is a special key that can only once in a table. The primary key is a UNIQUE key with theCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 name “PRIM every other UNIQUE [name] Creates a sp fore in the already exis create a uniq UNIQUE (nick When indexing c possible to inde create an index character field ‘a INDEX adds ( When performin will be used for the same first 2 formed. Therefo characters in a te Fields contained adding an index NULL is missing. KEY modifier to (without a warni In addition to th • FOREIGN KE • CHECK These keywords exported from o this missing func As of MySQL 3.2 statement. These AUTO_INCREME Specifies the AVG_ROW_LENG An option f average rowARY.” Despite it’s privileged status, in function it is the same as unique key. (column, ...) ecial index where every value contained in the index (and there- fields indexed) must be unique. Attempting to insert a value that ts into a unique index will generate an error. The following would ue index of the “nicknames” field: names); haracter fields (CHAR, VARCHAR and their synonyms only), it is x only a prefix of the entire field. For example, this following will of the numeric field ‘id’ along with the first 20 characters of the ddress’: id, address(20) ); g any searches of the field ‘address’, only the first 20 characters comparison unless more than one match is found that contains 0 characters, in which case a regular search of the data is per- re, it can be a big performance bonus to index only the number of xt field that you know will make the value unique. in an index must be defined with the NOT NULL modifier. When as a separate declaration, MySQL will generate an error if NOT However, when defining the primary key by adding the PRIMARY the field definition, the NOT NULL modifier is automatically added ng) if it is not explicitly defined. e above, MySQL supports the following special “types”: Y (name (column, [column2, . . . ]) do not actually perform any action. They exist so that SQL ther databases can be more easily read into MySQL. Also, some of tionality may be added into a future version of MySQL. 3, you can specify table options at the end of a CREATE TABLE options are: NT = start first value to be used for an AUTO_INCREMENT column. TH = length or tables containing large amounts of variable-length data. The length is an optimization hint to help MySQL manage this data.Copyright © 2001 O’Reilly & Associates, Inc. CHECKSUM = 0 When set to to improve d COMMENT = co Provides a c ters. DELAY_KEY_WR For MyISAM the table is c MAX_ROWS = r The maximu MIN_ROWS = r The minimu PACK_KEYS = For MyISAM booster for created and PASSWORD = ‘ Only availab option uses has no effec ROW_FORMAT = For MyISAM TYPE = rowty Specifies the able, then t available ye tem, MySQL tains a list o plete discuss Table 16-2. . MySQ Type BDB Berkeley_db HEAP ISAM InnoDB MERGEDRAFT, 8/24/01 or 1 1, this option forces MySQL to maintain a checksum for the table ata consistency. This option creates a performance penalty. mment omment for the table. The comment may not exceed 60 charac- ITE = 0 or 1 tables only. When set, this option delays key table updates until losed. owcount m number of rows you intend to store in the table. owcount m number of rows you intend to store in the table. 0 or 1 and ISAM tables only. This option provides a performance heavy-read tables. Set to 1, this option causes smaller keys to be thus slows down writes while speeding up reads. password’ le to MySQL customers with special commercial licenses. This the specified password to encrypt the table’s .frm file. This option t on the standard version of MySQL. DYNAMIC or STATIC tables only. Defines how the rows should be stored in a table. pe table type of the database. If the selected table type is not avail- he closest table type available is used. For example, BDB is not t for Mac OS X. If you specified TYPE=BDB on a Mac OS X sys- will instead create the table as a MyISAM table. Table 16-2 con- f supported table types and their advanatages. For a more com- ion of MySQL tables types, see the MySQL table type reference. L Table Types Transactional Description yes Transaction-safe tables with page locking. yes Alias for BDB no Memory-based table. Not persistent. no Ancient format. Replaced by MyISAM. yes Transaction-safe tables with row locking. no A collection of MyISAM tables merged as a singleCopyright © 2001 O’Reilly & Associates, Inc. table. DRAFT, 8/24/01 You must have statement. Examples # Create the CREATE DATAB # Create a s CREATE TABLE # Make the f # in the myf CREATE FUNCT DELETE Syntax DELETE [L Description Deletes rows fro entire table and that match the c deleted to the us As mentioned ab is done using an vidually. When way of knowing simply deletes a tains the actual tables with unre structure will sti tables, use a WHE DELETE FROM The LOW_PRIOR from the table b The LIMIT clau in a single shot. You must have D MyISAM Table 16-2. . MySQ TypeCREATE privileges on a database to use the CREATE TABLE new empty database 'employees' ASE employees; imple table emp_data ( id INT, name CHAR(50) ); unction make_coffee (which returns a string value and is stored uncs.so shared library) available to MySQL. ION make_coffee RETURNS string SONAME "myfuncs.so"; OW_PRIORITY] FROM table [WHERE clause] [LIMIT n] m a table. When used without a WHERE clause, this will erase the recreate it as an empty table. With a clause, it will delete the rows ondition of the clause. This statement returns the number of rows er. ove, not including a WHERE clause will erase this entire table. This efficient method that is much faster than deleting each row indi- using this method, MySQL returns 0 to the user because it has no how many rows it deleted. In the current design, this method ll of the files associated with the table except for the file that con- table definition. Therefore, this is a handy method of zeroing out coverably corrupt data files. You will lose the data, but the table ll be in place. If you really wish to get a full count of all deleted RE clause with an expression that always evaluates to true: TBL WHERE 1 = 1; ITY modifier causes MySQL to wait until no clients are reading efore executing the delete. ses establishes the maximum number of rows that will be deleted ELETE privileges on a database to use the DELETE statement. no A newer table type to replace ISAM that is portable. L Table Types Transactional DescriptionCopyright © 2001 O’Reilly & Associates, Inc. Examples # Erase all DELETE FROM # Erase all DELETE FROM DESCRIBE Syntax DESCRIBE DESC tabl Description Gives informatio tised, its functio This statement i column name ca played for all ma Example # Describe t DESCRIBE mes # Show the i # with 'my_' # Remember: # escaped to DESC big my\ DESC Synonym for DE DROP DATAB Syntax DROP DATA Description Permanently rem ment, none of th support files for deleted will be r number returned running the mys the administrativDRAFT, 8/24/01 of the data (but not the table itself) for the table 'olddata'. olddata records in the 'sales' table where the 'syear' field is '1995'. sales WHERE syear=1995 table [column] e [column] n about a table or column. While this statement works as adver- nality is available (along with much more) in the SHOW statement. s included solely for compatibility with Oracle SQL. The optional n contain SQL wildcards, in which case information will be dis- tching columns. he layout of the table 'messy' sy nformation about any columns starting in the 'big' table. '_' is a wildcard, too, so it must be be used literally. _% SCRIBE. ASE BASE [IF EXISTS] name ove a database from the MySQL. Once you execute this state- e tables or data that made up the database are available. All of the the database are deleted from the file system. The number of files eturned to the user. Because three files represent most tables, the is usually the number of tables times three. This is equivalent to qladmin drop utility. As with running mysqladmin, you must be e user for MySQL (usually root or mysql) to perform this state-Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 ment.You may would result from DROP FUNCT Syntax DROP FUNC Description Will remove a u does not actuall function again a implementation table within the DROP INDEX Syntax DROP INDE Description Provides for com MySQL, this sta ALTER TABLE must have SELE table in question DROP TABLE Syntax DROP TABL Description Will erase an en ply deletes the EXISTS to make does not exist. ment.use the IF EXISTS clause to prevent any error message that an attempt to drop a non-existent table. ION TION name ser defined function from the running MySQL server process. This y delete the library file containing the function. You may add the t any time using the CREATE FUNCTION statement. In the current DROP FUNCTION simply removes the function from the function MySQL database. This table keeps track of all active functions. X idx_name ON tbl_name patibility with other SQL implementations. In older versions of tement does nothing. As of 3.22, this statement is equivalent to ... DROP INDEX. To perform the DROP INDEX statement, you CT, INSERT, DELETE, UPDATE, CREATE, and DROP privileges for the . E [IF EXISTS] name [, name2, ...] tire table permanently. In the current implementation, MySQL sim- files associated with the table. As of 3.22, you may specify IF MySQL not return an error if you attempt to remove a table that You must have DELETE privileges on the table to use this state-Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 DROP privi data way ups. (2) d back EXPLAIN Syntax EXPLAIN [ Description Used with a tab table_name. Used with a SQL order and structu not being used e lowing columns: table The name o type The type of are: system A specia row. const Used fo start of cessing. eq_ref Reads o vious ta and the is by far the most dangerous SQL statement. If you have drop leges, you may permanently erase a table or even an entire base. This is done without warning or confirmation. The only to undo a DROP is to restore the table or database from back- The lessons to be learned here are: (1) always keep backups; on’t use DROP unless you are really sure; and (3) always keep ups. table_name | sql_statement] le name, this command is an alias for SHOW COLUMNS FROM statement, this command displays verbose information about the re of a SELECT statement. This can be used to see where keys are fficiently. This information is returned as a result set with the fol- f the table referenced by the result set row explaining the query. join that will be performed. These types, in order of performance, l case of the const type, this join supports a table with a single r tables with at most a single matching row that will be read at the the query. MySQL treats this value as a constant to speed up pro- ne row from the table for each combination of rows from the pre- bles. It is used when all parts of the index are used by the join index is unique or a primary key.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 ref Reads a nation o leftmost This is a range Reads o They ke tains the type. index Reads al ALL A full ta tables. T need to possible_key Indicates wh empty, there enhance per key Indicates wh key_len Provides the ref Describes w join. rows Indicates the form the que Extra Additional in Example EXPLAIN SELE WHERE purchall rows with matching index values from the table for each combi- f rows from the previous tables. This join occurs when only the part of an index or if the index is not unique or a primary key. good join when the key in use matches only a few rows. nly the rows in a given range using an index to select the rows. y column indicates the key in use and the key_len column con- longest part of the key. The ref column will be NULL for this l rows based on an index tree scan. ble scan is done for each combination of rows from the previous his join is generally a very bad thing. If you see it, you probably build a different SQL query or better organize your indices. s ich indices MySQL could use to build the join. If this column is are no relevant indices and you probably should build some to formance. ich index MySQL decided to use. length of the key MySQL decided to use for the join. hich columns or constants were used with the key to build the number of rows MySQL estimates it will need to examine to per- ry. formation indicating how MySQL will perform the query. CT customer.name, product.name FROM customer, product, purchases ses.customer=customer.id AND purchases.product=product.idCopyright © 2001 O’Reilly & Associates, Inc. FLUSH Syntax FLUSH opt Description Flushes or resets must have reload following: HOSTS Empties the should be u connecting t LOGS Closes all of file has chan update log, by one. PRIVILEGES Reloads all for any chan STATUS Resets the st TABLES Closes all cu GRANT Syntax GRANT pri [ (column, .. ON {table [, user [ [WITH GRAN Previous to MySQ current versions user (or users). The table can be within the curr ‘database.*’ toDRAFT, 8/24/01 ion[, option...] various internal processes depending on the options given. You privileges to execute this statement. The option can be any of the cache table that stores hostname information for clients. This sed if a client changes IP addresses, or if there are errors related to o the host. the standard log files and reopens them. This can be used if a log ged inode number. If no specific extension has been given to the a new update log will be opened with the extension incremented of the internal MySQL permissions grant tables. This must be run ges to the tables to take effect. atus variables that keep track of the current state of the server. rrently opened tables and flushes any cached data to disk. vilege .) ] [, privilege [( column, ...) ] ...] } TO user [IDENTIFIED BY 'password'] IDENTIFIED BY 'password'] ...] T OPTION] L 3.22.11, the GRANT statement was recognized but did nothing. In , GRANT is functional. This statement will enable access rights to a Access can be granted per database, table or individual column. given as a table within the current database, ‘*’ to affect all tables ent database, ‘*.*’ to affect all tables within all databases orCopyright © 2001 O’Reilly & Associates, Inc. effect all tables within the given database. The following pr ALL PRIVILEDG Effects all pr ALTER Altering the CREATE Creating new DELETE Deleting row DROP Deleting ent FILE Creating and INDEX Creating and INSERT Inserting dat PROCESS Killing proce REFERENCES Not impleme RELOAD Refreshing v SELECT Reading data SHUTDOWN Shutting dow UPDATE Altering row USAGE No privilege The user variable contain SQL wi quoted, or just t are both valid). user@“%”.DRAFT, 8/24/01 ivileges are currently supported: ES/ALL ivileges structure of tables tables s from tables ire tables removing entire databases as well as managing log files deleting indices from tables a into tables ss threads nted (yet) arious internal tables (see the FLUSH statement) from tables n the database server s within tables s at all is of the form user@hostname. Either the user or the hostname can ldcards. If wildcards are used, either the whole name must be he part(s) with the wildcards (e.g., joe@"%.com " and “joe@%.com” A user without a hostname is considered to be the same asCopyright © 2001 O’Reilly & Associates, Inc. If you have a gl BY modifier. If t given password changed. Giving the GRAN fier. If this is use Examples # Give full GRANT ALL ON # Give full # Account ta GRANT ALL ON # Give joe o # to SELECT GRANT SELECT INSERT Syntax INSERT [D [INTO] ta VALUES ( INSERT [L [INTO] ta SELECT .. INSERT [L [INTO] ta SET colum Description Inserts data into values into the g set to their defa query and inser sion of the first which values. If SELECT stateme other activity ha LOW_PRIORITY postponed until DRAFT, 8/24/01 obal GRANT privilege, you may specify an optional INDENTIFIED he user in the statement does not exist, it will be created with the . Otherwise the existing user will have his or her password T privilege to a user is done with the WITH GRANT OPTION modi- d, the user may grant any privilege they have onto another user. access to joe@carthage for the Account table bankdb.Account TO joe@carthage; access to jane@carthage for the ble and create a user ID for her bankdb.Account TO jane@carthage IDENTIFIED BY ‘mypass’; n the local machine the ability from any table on the webdb database ON webdb.* TO joe; ELAYED | LOW_PRIORITY ] [IGNORE] ble [ (column, ...) ] values [, values... ]) OW_PRIORITY] [IGNORE] ble [ (column, ...) ] . OW_PRIORITY] [IGNORE] ble n=value, column=value,... a table. The first form of this statement simply inserts the given iven columns. Columns in the table that are not given values are ult value or NULL. The second form takes the results of a SELECT ts them into the table. The third form is simply an alternate ver- form that more explicitly shows which columns correspond with the DELAYED modifier is present in the first form, all incoming nts will be given priority over the insert, which will wait until the s finished before inserting the data. In a similar way, using the modifier with any form of INSERT will cause the insertion to be all other operations from the client have been finished.Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 When using a ORDER BY mod same table you a Starting with My at a time. This i by commas. You must have I Examples # Insert a r INSERT INTO VALUES ( 'Bo # Copy all r # 'old_data' # 'data'. INSERT INTO SELECT ( id, FROM data WHERE date < # Insert 3 n INSERT INTO VALUES ( 'Ti ('And ('Ran KILL Syntax KILL thre Description Terminates the SHOW PROCESSE require process p Example # Terminate KILL 3 LOAD Syntax LOAD DATA INTO TABLSELECT query with the INSERT statement, you cannot use the ifier with the SELECT statement. Also, you cannot insert into the re selecting from. SQL 3.22.5 it is possible to insert more than one row into a table s done by adding additional value lists to the statement separated NSERT privileges to use this statement. ecord into the 'people' table. people ( name, rank, serial_number ) b Smith', 'Captain', 12345 ); ecords from 'data' that are older than a certain date into . This would usually be followed by deleting the old data from old_data ( id, date, field ) date, field) 87459300; ew records into the 'people' table. people (name, rank, serial_number ) m O\'Reilly', 'General', 1), y Oram', 'Major', 4342), dy Yarger', 'Private', 9943); ad_id specified thread. The thread ID numbers can be found using the S statement. Killing threads owned by users other than yourself rivilege. thread 3 [LOCAL] INFILE file [REPLACE|IGNORE] E table [delimiters] [(columns)]Copyright © 2001 O’Reilly & Associates, Inc. Description Reads a text file table. This meth statements. Altho SQL statement, t server unless th qualified path, M file. With no delimite delimited with c and lines termin In addition to th following keywo FIELDS TERMIN Specifies the codes can b more than o a comma d delimited. T FIELDS ENCLOS Specifies the ENCLOSED "this", " "this", an used in the FIELDS ESCAPE Specifies the even though ESCAPED would be "Fourth". FIELDS ES ter number backslash ch be special. slash the bac LINES TERMINA Specifies the contain mor '.', a file cDRAFT, 8/24/01 that is in a readable format and inserts the data into a database od of inserting data is much quicker than using multiple INSERT ugh the statement may be sent from all clients just like any other he file referred to in the statement is assumed to be located on the e LOCAL keyword is used.. If the filename does not have a fully ySQL looks under the directory for the current database for the rs specified, LOAD DATA INFILE will assume that the file is tab haracter fields, special characters escaped with the backslash (\), ated with a newline character. e default behavior, you may specify your own delimiters using the rds: ATED BY 'c' character used to delimit the fields. Standard C language escape e used to designate special characters. This value may contain ne character. For example, FIELDS TERMINATED BY ',' denotes elimited file and FIELDS TERMINATED BY '\t' denotes tab he default value is tab delimited. ED BY 'c' character used to enclose character strings. For example, FIELD BY '"' would mean that a line containing "this, value", value" would be taken to have three fields: "this,value", d "value". The default behavior is to assume that no quoting is file. D BY 'c' character used to indicate that the next character is not special, it would usually be a special character. For example, with FIELDS BY '^' a line consisting of First,Second^,Third,Fourth parsed as three fields: "First", "Second,Third" and The exceptions to this rule are the null characters. Assuming the CAPED BY value is a backslash, \0 indicates an ASCII NUL (charac- 0) and \N indicates a MySQL NULL value. The default value is the aracter. Note that MySQL itself considers the backslash character to Therefore to indicate backslash in that statement you must back- kslash like this: FIELDS ESCAPED BY '\\'. TED BY 'c' character that indicates the start of a new record. This value can e than one character. For example, with LINES TERMINATED BYCopyright © 2001 O’Reilly & Associates, Inc. onsisting of a,b,c.d,e,f.g,h,k. would be parsed as three sepa- DRAFT, 8/24/01 rate records ter. This me record. The keyword FI LOAD DATA IN By default, if a v for a field that is is added to the the table. Conve value and keep The word NULL unless the FIELD Using the same example, FIELD unpredictable be If a list of colum the table. If no c the number of fi are defined in th You must have S Example # Load in th # that the f LOAD DATA IN # Load in th # fields and # the 'newta LOAD DATA IN INTO TABLE n FIELDS TERMI ( field1, fi LOCK Syntax LOCK TABL [AS alias, each containing three fields. The default is the newline charac- ans that by default, MySQL assumes that each line is a separate ELDS should only be used for the entire statement. For example: FILE data.txt FIELDS TERMINATED BY ',' ESCAPED BY '\\' alue read from the file is the same as an existing value in the table part of a unique key, an error is given. If the REPLACE keyword statement, the value from the file will replace the one already in rsely, the IGNORE keyword will cause MySQL to ignore the new the old one. encountered in the data file is considered to indicate a null value S ENCLOSED BY character encloses it. character for more than one delimiter can confuse MySQL. For S TERMINATED BY ',' ENCLOSED BY ',' would produce havior. ns is provided, the data is inserted into those particular fields in olumns are provided, the number of fields in the data must match elds in the table, and they must be in the same order as the fields e table. ELECT and INSERT privileges on the table to use this statement. e data contained in 'mydata.txt' into the table 'mydata'. Assume ile is tab delimited with no quotes surrounding the fields. FILE 'mydata.txt' INTO TABLE mydata e data contained in 'newdata.txt' Look for two comma delimited insert their values into the fields 'field1' and 'field2' in ble' table. FILE 'newdata.txt' ewtable NATED BY ',' eld2 ) ES name ] READ|WRITE [, name2 [AS alias] READ|WRITE, ...]Copyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Description Locks a table fo emulate transact from the table b creates a WRITE Usin proc acce or a MyS whil Example # Lock table # to 'table2 LOCK TABLES OPTIMIZE Syntax OPTIMIZE Description Recreates a table mized table as a current table. W normal (all write Example OPTIMIZE TABL REPLACE Syntax REPLACE I REPLACE Ir the use of a specific thread. This command is generally used to ions. If a thread creates a READ lock all other threads may read ut only the controlling thread can write to the table. If a thread lock, no other thread may read from or write to the table. g locked and unlocked tables at the same time can cause the ess thread to freeze. You must lock all of the tables you will be ssing during the time of the lock. Tables you access only before fter the lock do not need to be locked. The newest versions of QL generate an error if you attempt to access an unlocked table e you have other tables locked. s 'table1' and 'table3' to prevent updates, and block all access '. Also create the alias 't3' for 'table3' in the current thread. table1 READ, table2 WRITE, table3 AS t3 READ TABLE name eliminating any wasted space. This is done by creating the opti- separate, temporary table and then moving over to replace the hile the procedure is happening, all table operations continue as s are diverted to the temporary table). E mytable NTO table [(column, ...)] VALUES (value, ...) NTO table [(column, ...)] SELECT select_clauseCopyright © 2001 O’Reilly & Associates, Inc. DRAFT, 8/24/01 Description Inserts data to a cal to INSERT e value replaces th values into the g to their default v and inserts them Examples # Insert a r REPLACE INTO VALUES ( 'Bo # Copy all r # 'old_data' # 'data'. REPLACE INTO SELECT ( id, FROM data WHERE date < REVOKE Syntax REVOKE pr ..) ...] ON table Description Removes a privi same as for the to execute this s SELECT Syntax SELECT [S [INTO OUT FROM tabl Description Retrieve data fro reading data fromtable, replacing any old data that conflicts. This statement is identi- xcept that if a value conflicts with an existing unique key, the new e old one. The first form of this statement simply inserts the given iven columns. Columns in the table that are not given values are set alue or NULL. The second form takes the results of a SELECT query into the table. ecord into the 'people' table. people ( name, rank, serial_number ) b Smith', 'Captain', 12345 ) ecords from 'data' that are older than a certain date into . This would usually be followed by deleting the old data from old_data ( id, date, field ) date, field) 87459300 ivilege [(column, ...)] [, privilege [(column, . FROM user lege from a user. The values of privilege, table, and user are the GRANT statement. You must have the GRANT privilege to be able tatement. TRAIGHT_JOIN] [DISTINCT|ALL] value[, value2...] FILE 'filename' delimiters] e[, table2...] [clause] m a database. The SELECT statement is the primary method of database tables.Copyright © 2001 O’Reilly & Associates, Inc. If you specify m that you can com perform the join MySQL to join th If the DISTINCT group of rows t displays all retur The returned val Aliases Any comple for it. The v statement (e date FROM Column names These can b forms are n can be use mydata.peo Functions MySQL supp defined fun statement (e By default, MySQ however, to hav contents of a ta human readable The INTO OUTF tion is accompl filename. The form the same as the • The OPTION fier. This w enclosed da • Removing al '') will cau ing to the d bases can im The default beha slash (\) as the eDRAFT, 8/24/01 ore than one table, MySQL will automatically join the tables so pare values between the tables. In cases where MySQL does not in an efficient manner, you can specify STRAIGHT_JOIN to force e tables in the order you enter them in the query. keyword is present, only one row of data will be output for every hat is identical. The ALL keyword is the opposite of distinct and ned data. The default behavior is ALL. ues can be any one of the following: x column name or function can be simplified by creating an alias alue can be referred to by its alias anywhere else in the SELECT .g., SELECT DATE_FORMAT(date,"%W, %M %d %Y") as nice_ calendar). e specified as column, table.column or database.table.column. The longer ecessary only to disambiguate columns with the same name, but d at any time (e.g., SELECT name FROM people; SELECT ple.name FROM people). orts a wide range of built-in functions (see later). In addition, user ctions can be added at any time using the CREATE FUNCTION .g., SELECT COS(angle) FROM triangle). L sends all output to the client that sent the query. It is possible e the output redirected to a file. In this way you can dump the ble (or selected parts of it) to a formatted file that can either be , or formatted for easy parsing by another database system. ILE 'filename'modifier is the means in which output redirec- ished. With this the results of the SELECT query are put into at of the file is determined by the delimiters arguments, which are LOAD DATA INFILE statement with the following additions: ALLY keyword may be added to the FIELDS ENCLOSED BY modi- ill cause MySQL to thread e