MySQL Reference
ConventionsThe following conventions are used in this book:
1.2 InstallationYou can install MySQL by compiling the source code with the options that best suit your needs, or by downloading and installing a prebuilt binary. In general, you’ll want to use the package management system (such as the BSD ports system) appropriate to your operating system. You can also find both binary and source code at the MySQL web site, http://www.mysql.com. Before installing using either approach, you need to prepare your operating system for MySQL. Specifically, you should create a mysql user and group under which MySQL will run. 1.2.1 CompilationCompiling MySQL requires the following steps:
1.2.2 ConfigurationMySQL has three different kinds of configuration, both for the server process at server startup and for the client processes when a user executes them. In order of preference, these configuration options include:
In other words, if you have the password option specified on the command line, in your configuration file, and in an environment variable, the command-line option wins. Table 1-1 shows a list of configuration options. Each option applies to one or more MySQL tools, depending on the context.
A MySQL configuration file has the following format: # Example MySQL configuration file # # These options go to all clients [client] password = my_password port = 3306 socket = /var/lib/mysql/mysql.sock # These options are specifically targeted at the mysqld server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable = max_allowed_packet=1M MySQL supports multiple configuration files. As a general rule, it checks files in the following order of preference:
In all cases except the command-line and user configuration options, the name of the configuration file on Unix is my.cnf and on Windows is my.ini. A Unix user can override system configuration information by building their own configuration file in ~/.my.cnf. The system configuration file on a Unix system is /etc/my.cnf. Windows, on the other hand, has two system configuration locations, in order of preference:
You can alternately specify a file on the command line using the –defaults-file=filename option. This option causes all options specified in other files to be ignored, even if they are not overridden in the file you specify. 1.2.3 StartupIn general, you will want MySQL to begin running when the operating system comes up. How you do this depends on your operating system. 1.2.3.1 Mac OS XMac OS X automatically executes all scripts under the /Library/StartupItems directory when the system boots up. If that directory does not yet exist, you will need to create it. For MySQL, you should create the directory /Library/StartupItems/MySQL and place the startup shell script MySQL and the configuration file StartupParameters.plist in that directory. Once those files are set up, you need to edit the host configuration file /etc/hostconfig and add the line: MYSQLSERVER=-YES- 1.2.3.1.1 MySQLThe shell script to start, stop, and restart MySQL looks like this: #!/bin/sh
. /etc/rc.common
StartService( )
{
if [ "${MYSQLSERVER:=-NO-}" = "-YES-" ]; then
ConsoleMessage "Starting MySQL"
cd /usr/local/mysql
bin/mysqld_safe --user=mysql &
fi
}
StopService( )
{
ConsoleMessage "Stopping MySQL"
/usr/local/mysql/bin/mysqladmin shutdown
}
RestartService( )
{
if [ "${MYSQLSERVER:=-NO-}" = "-YES-" ]; then
ConsoleMessage "Restarting MySQL"
StopService
StartService
else
StopService
fi
}
RunService "$1"
1.2.3.1.2 StartupParameters.plistThe configuration file looks like this: <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist SYSTEM "file://localhost/System/Library/
DTDs/PropertyList.dtd">
<plist version="0.9">
<dict>
<key>Description</key>
<string>MySQL Database Server</string>
<key>Provides</key>
<array>
<string>MySQL</string>
</array>
<key>Requires</key>
<array>
<string>Network</string>
</array>
<key>OrderPreference</key>
<string>Late</string>
</dict
</plist>
Once installed, you should run the mysql_install_db tool to set up your databases. 1.2.3.2 Other UnixSetting up other variants of Unix is as simple as copying the script mysql.server from the source’s support-files directory to your version of Unix’s startup directory and making sure it is executable by root. Under FreeBSD, for example, place this script in /usr/local/etc/rc.d. Once installed, you should run the mysql_install_db tool to set up your databases. 1.2.3.3 Windows 2000/XPTo startup an application at system startup on the Windows platform, you need to install it as a Windows service. You can do this by hand using the command: C:> c:mysqlbinmysqld-nt --install A more convenient way to do accomplish this task is through the winmysqladmin.exe utility that comes with the Windows installation of MySQL. 1.2.4 Set the Root PasswordAfter starting the server, and before doing anything else, set a password for the root user: mysqladmin -u root password a_good_password 1.3 Command-Line ToolsYou can interact with MySQL entirely from the command line. In general, each MySQL command accepts as an argument any appropriate option from the configuration options listed earlier. You prefix any such option with two dashes: mysql –password=mypass In addition, each of these options has a short form: mysql -p mypass To see which options apply to individual commands and their short forms, refer to the manpage for the command in question: [23:00:03] george@firenze$ man -M/usr/local/mysql/man mysql MYSQL(1) MYSQL(1) NAME mysql - text-based client for mysqld, a SQL-based relational database daemon SYNOPSIS mysql [-B|–batch] [-#|–debug= logfile] [-T|–debug-info] [-e|–exec= command] [-f|–force] [-?|–help] [-h|–host=hostname] [-n|–unbuffered] [-p[pwd]] [–password=[pwd]] [-P|–port= pnum] [-q|–quick] [-r|–raw] [-s|–silent] [-S|–socket= snum] [-u|–user= uname] [-v|–verbose] [-V|–version] [-w|–wait] DESCRIPTION The mysql program provides a curses-based interface to the SQL-based database server daemon, mysqld(1). Full fuller documentation, refer to the HTML documents installed with the package. OPTIONS -B|–batch Print results with a tab as separator, each row on MySQL provides the following command-line tools:
1.4.1 NumericsMySQL supports all ANSI SQL2 numeric data types. MySQL numeric types break down into two groups: integer and floating point. Within each group, the types differ by the amount of storage required for them. Numeric types allow you to specify a display size, which affects the way MySQL displays results. The display size bears no relation to the internal storage provided by each data type. In addition, the floating types allow you to optionally specify the number of digits that follow the decimal point. In such cases, the digits value should be an integer from 0 to 30 that is at most two less than the display size. If you do make the digits value greater than two less than the display size, the display size will automatically change to two more than the digits value. For instance, MySQL automatically changes FLOAT(6,5) to FLOAT(7,5). When you insert a value into a column that requires more storage than the data type allows, it will be clipped to the minimum (negative values) or maximum (positive values) value for that data type. MySQL will issue a warning when such clipping occurs during ALTER TABLE, LOAD DATA INFILE, UPDATE, and multirow INSERT statements. The AUTO_INCREMENT attribute may be supplied for at most one column of an integer type in a table. The UNSIGNED attribute may be used with any numeric type. An unsigned column may contain only positive integers or floating-point values. The ZEROFILL attribute indicates that the column should be left padded with zeros when displayed by MySQL. The number of zeros padded is determined by the column’s display width.
Storage8 bytes DescriptionLargest integer type, supporting range of whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (0 to 18,446,744,073,709,551,615 unsigned). MySQL performs all arithmetic using signed BIGINT or DOUBLE values, but BIGINT has performing arithmetic on unsigned values. You should therefore avoid performing any arithmetic operations on unsigned BIGINT values greater than 9,223,372,036,854,775,807. If you do, you may end up with imprecise results.
Synonym for DECIMAL.
Storageprecision + 2 bytes DescriptionStores floating-point numbers where precision is critical, such as for monetary values. DECIMAL types require you to specify the precision and scale. The precision is the number of significant digits in the value. The scale is the number of those digits that come after the decimal point. For example, a BALANCE column declared as DECIMAL(9, 2) would store numbers with nine significant digits, two of which are to the right of the decimal point. The range for this declaration would be -9,999,999.99 to 9,999,999.99. If you specify a number with more decimal points, it is rounded to fit the proper scale. Values beyond the range of the DECIMAL are clipped to fit within the range. MySQL actually stores DECIMAL values as strings, not as floating-point numbers. It uses one character for each digit, one character for the decimal points when the scale is greater than 0, and one character for the sign of negative numbers. When the scale is 0, the value contains no fractional part. Prior to MySQL 3.23, the precision actually had to include space for the decimal and sign. This requirement is no longer in place, in accordance with the ANSI specification. ANSI SQL supports the omission of precision and/or scale where the omission of scale creates a default scale of zero and the omission of precision defaults to an implementation-specific value. In the case of MySQL, the default precision is 10.
Storage8 bytes DescriptionA double-precision floating-point number. This type stores large floating-point values. DOUBLE columns store negative values from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and positive numbers from 2.2250738585072014E-308 to 1.7976931348623157E+308.
Synonym for DOUBLE.
Storage4 bytes DescriptionA single-precision floating-point number. This type is used to store small floating-point numbers. FLOAT columns can store negative values between -3.402823466E+38 and -1.175494351E-38, 0, and positive values between 1.175494351E-38 and 3.402823466E+38.
Storage4 bytes DescriptionA basic whole number with a range of -2,147,483,648 to 2,147,483,647 (0 to 4,294,967,295 unsigned).
Synonym for INT.
Storage3 bytes DescriptionA basic whole number with a range of -8,388,608 to 8,388,607 (0 to 16,777,215 unsigned).
Synonym for DECIMAL.
Synonym for DOUBLE.
Storage2 bytes DescriptionA basic whole number with a range of -32,768 to 32,767 (0 to 65,535 unsigned).
Storage1 byte DescriptionA basic whole number with a range of -128 to 127 (0 to 255 unsigned). 1.4.2 StringsString data types store various kinds of text data. There are several types to accommodate data of different sizes. For each size, there is a type that sorts and compares entries in a case-insensitive fashion in accordance with the sorting rules for the default character set. A corresponding binary type performs simple byte-by-byte sorts and comparisons. In other words, binary values are case sensitive. For CHAR and VARCHAR, the binary types are declared using the BINARY attribute. The TEXT types, however, have corresponding BLOB types as their binary counterparts.
Binary form of TEXT.
SizeSpecified by the size value in a range of to 255 (1 to 255 prior to MySQL 3.23) Storagesize bytes DescriptionA fixed-length text field. String values with fewer characters than the column’s size will be right padded with spaces. The right padding is removed on retrieval of the value from the database. CHAR(0) fields are useful for backward compatibility with legacy systems that no longer store values in the column.
Synonym for CHAR.
Synonym for VARCHAR.
Binary form of LONGTEXT.
Size0 to 4,294,967,295 StorageLength of value + 4 bytes DescriptionStorage for large text values. While the theoretical limit on the size of the text that can be stored in a LONGTEXT column exceeds 4 GB, the practical limit is much less due to limitations of the MySQL communication protocol and the amount of memory available to both the client and server ends of the communication.
Binary form of MEDIUMTEXT.
Size0 to 16,777,215 StorageLength of value + 3 bytes DescriptionStorage for medium-sized text values.
Synonym of CHAR.
Synonym of CHAR.
Synonym of CHAR.
Synonym of VARCHAR.
Size0 to 65,535 StorageLength of value + 2 bytes DescriptionStorage for most text values.
Binary form of TINYTEXT.
Size0 to 255 StorageLength of value + 1 byte DescriptionStorage for short text values.
SizeSpecified by the size value in a range of to 255 (1 to 255 prior to MySQL 3.23) StorageLength of value + 1 byte DescriptionStorage for variable-length text. Trailing spaces are removed from VARCHAR values. 1.4.3 DatesMySQL date types are extremely flexible tools for storing date information. They are also extremely forgiving in the belief that it is up to the application, not the database, to validate date values. MySQL only checks that months range from 0 to 12 and dates range from to 31. February 31, 2001, is therefore a legal MySQL date. More useful, however, is the fact that February 0, 2001, is a legal date. In other words, you can use 0 to signify dates in which you do not know a particular piece of the date. Though MySQL is somewhat forgiving on the input format, you should attempt to format all date values in your applications in MySQL’s native format to avoid any confusion. MySQL always expects the year to be the left-most element of a date format. If you assign an illegal value in an SQL operation, MySQL inserts a zero for that value. MySQL automatically converts date and time values to integer values when used in an integer context.
FormatYYYY-MM-DD (2001-01-01) Storage3 bytes DescriptionStores a date in the range of January 1, 1000 (’1000-01-01‘) to December 31, 9999 (’9999-12-31‘) in the Gregorian calendar.
FormatYYYY-MM-DD hh:mm:ss (2001-01-01 01:00:00) Storage8 bytes DescriptionStores a specific time in the range of 12:00:00 AM, January 1, 1000 (’1000-01-01 00:00:00‘) to 11:59:59 P.M., December 31, 9999 (’9999-12-31 23:59:59‘) in the Gregorian calendar.
Formathh:mm:ss (06:00:00) Storage3 bytes DescriptionStores a time value in the range of midnight (’00:00:00‘) to one second before midnight (’23:59:59‘).
FormatYYYYMMDDhhmmss (20010101060000) Storage4 bytes DescriptionA simple representation of a point in time down to the second in the range of midnight on January 1, 1970, to one minute before midnight on December 31, 2037. Its primary utility is keeping track of table modifications. When you insert a NULL value into a TIMESTAMP column, the current date and time are inserted instead. When you modify any value in a row with a TIMESTAMP column, the first TIMESTAMP column will be automatically updated with the current date and time.
FormatYYYY (2001) Storage1 byte DescriptionStores a year of the Gregorian calendar. The size parameter enables you to store dates using 2 digit years or 4 digit years. The range for a YEAR(4) is 1900 to 2155; the range for a YEAR(2) is 1970-2069.The default size is YEAR(4). 1.4.4 Complex TypesMySQL’s complex data types ENUM and SET are just special string types. We list them separately because they are conceptually more complex and represent a lead into the SQL3 data types that MySQL may support in the future.
Storage1-255 members: 1 byte 256-65,535 members: 2 bytes DescriptionStores one value of a predefined list of possible strings. When you create an ENUM column, you provide a list of all possible values. Inserts and updates are allowed to set the column to values only from that list. Any attempt to insert a value that is not part of the enumeration will cause an empty string to be stored instead. You may reference the list of possible values by index where the index of the first possible value is 0. For example: SELECT COLID FROM TBL WHERE COLENUM = 0; Assuming COLID is a primary key column and COLENUM is the column of type ENUM, this SQL will retrieve the primary keys of all rows in which the COLENUM value equals the first value of that list. Similarly, sorting on ENUM columns happens according to index, not string value. The maximum number of elements allowed for an ENUM column is 65,535.
Storage1-8 members: 1 byte 9-16 members: 2 bytes 17-24 members: 3 bytes 25-32 members: 4 bytes 33-64 members: 8 bytes DescriptionA list of values taken from a predefined set of values. A field can contain any number—including none—of the strings specified in the SET statement. A SET is basically an ENUM that allows each field to contain more than one of the specified values. A SET, however, is not stored according to index, but as a complex bit map. Given a SET with the members Orange, Apple, Pear, and Banana, each element is represented by an “on” bit in a byte, as shown Table 1-2.
In this example, the values Orange and Pear are stored in the database as 5 (0101). You can store a maximum of 64 values in a SET column. Though you can assign the same value multiple times in an SQL statement updating a SET column, only a single value will actually be stored.
1.5.3 IdentifiersYou can reference any given object on a MySQL server—assuming you have the proper rights—using one of the following conventions:
In general, MySQL allows you to use any character in an identifier. (Older versions of MySQL limited identifiers to valid alphanumeric characters from the default character set, as well as $ and _.) This rule is limited, however, for databases and tables, because these values must be treated as files on the local filesystem. You can therefore use only characters valid for the underlying filesystem’s naming conventions in a database or table name. Specifically, you may not use / or . in a database or table name. You can never use NUL (ASCII 0) or ASCII 255 in an identifier. When an identifier is also an SQL keyword, you must enclose the identifier in backticks: CREATE TABLE 'select' ( 'table' INT NOT NULL PRIMARY KEY AUTO_INCREMENT); Since Version 3.23.6, MySQL supports the quoting of identifiers using both backticks and double quotes. For ANSI compatibility, however, you should use double quotes for quoting identifiers. You must, however, be running MySQL in ANSI mode. 1.5.4 CommentsYou can introduce comments in your SQL to specify text that should not be interpreted by MySQL. This is particularly useful in batch scripts for creating tables and loading data. MySQL specifically supports three kinds of commenting: C, shell-script, and ANSI SQL commenting. C commenting treats anything between /* and */ as comments. Using this form of commenting, your comments can span multiple lines. For example: /*
* Creates a table for storing customer account information.
*/
DROP TABLE IF EXISTS ACCOUNT;
CREATE TABLE ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL
PRIMARY KEY AUTO_INCREMENT,
BALANCE DECIMAL(9,2) NOT NULL );
Within C comments, MySQL still treats single and double quotes as a start to a string literal. In addition, a semicolon in the comment will cause MySQL to think you are done with the current statement. Shell-script commenting treats anything from a # character to the end of a line as a comment: CREATE TABLE ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL
PRIMARY KEY AUTO_INCREMENT,
BALANCE DECIMAL(9,2)
NOT NULL ); # Not null ok?
MySQL does not really support ANSI SQL commenting, but it comes close. ANSI SQL commenting is distinguished by adding – to the end of a line. MySQL supports two dashes and a space (’– `) followed by the comment. The space is the non-ANSI part: DROP TABLE IF EXISTS ACCOUNT; -- Drop the table if it already exists 1.5.5 CommandsThis section presents the full syntax of all commands accepted by MySQL.
The ALTER statement covers a wide range of actions that modify the structure of a table. This statement is used to add, change, or remove columns from an existing table as well as to remove indexes. To perform modifications on the table, MySQL creates a copy of the table and changes it, meanwhile queuing all table altering queries. When the change is done, the old table is removed and the new table put in its place. At this point the queued queries are performed. As a safety precaution, if any of the queued queries create duplicate keys that should be unique, the ALTER statement is rolled back and cancelled. If the IGNORE keyword is present in the statement, duplicate unique keys are ignored and the ALTER statement proceeds as normal. Be warned that using IGNORE on an active table with unique keys invites table corruption. Possible actions in action_list include:
Multiple ALTER statements may be combined into one using commas, as in the following example: ALTER TABLE mytable DROP myoldcolumn, ADD mynewcolumn INT To perform any of the ALTER TABLE actions, you must have SELECT, INSERT, DELETE, UPDATE, CREATE, and DROP privileges for the table in question. Examples# Add the field 'address2' to the table 'people' and make # it of type 'VARCHAR' with a maximum length of 100. ALTER TABLE people ADD COLUMN address2 VARCHAR(100) # Add two new indexes to the 'hr' table, one regular index # for the 'salary' field and one unique index for the 'id' # field. Also, continue operation if duplicate values are # found while creating the 'id_idx' index # (very dangerous!). ALTER TABLE hr ADD INDEX salary_idx ( salary ) ALTER IGNORE TABLE hr ADD UNIQUE id_idx ( id ) # Change the default value of the 'price' field in the # 'sprockets' table to $19.95. ALTER TABLE sprockets ALTER price SET DEFAULT '$19.95' # Remove the default value of the 'middle_name' field in # the 'names' table. ALTER TABLE names ALTER middle_name DROP DEFAULT # Change the type of the field 'profits' from its previous # value (which was perhaps INTEGER) to BIGINT. The first # instance of 'profits' is the column to change, and the # second is part of the create clause. ALTER TABLE finances CHANGE COLUMN profits profits BIGINT # Remove the 'secret_stuff' field from the table # 'not_private_anymore' ALTER TABLE not_private_anymore DROP secret_stuff # Delete the named index 'id_index' as well as the primary # key from the table 'cars'. ALTER TABLE cars DROP INDEX id_index, DROP PRIMARY KEY # Rename the table 'rates_current' to 'rates_1997' ALTER TABLE rates_current RENAME AS rates_1997
Acquires a read lock on the table and performs an analysis on it for MyISAM and BDB tables. The analysis examines the key distribution in the table. It returns a result set with the following columns:
Creates a new database with the specified name. You must have the proper privileges to create the database. Running this command is the same as running the mysqladmincreate utility. ExampleCREATE DATABASE Bank;
The CREATE FUNCTION statement allows MySQL statements to access precompiled executable functions known as user-defined functions (UDFs). These functions can perform practically any operation, since they are designed and implemented by the user. The return value of the function can be STRING, for character data; REAL, for floating point numbers; or INTEGER, for integer numbers. MySQL will translate the return value of the C function to the indicated type. The library file that contains the function must be a standard shared library that MySQL can dynamically link into the server. ExampleCREATE FUNCTION multiply RETURNS REAL SONAME mymath.so
The CREATE INDEX statement is provided for compatibility with other implementations of SQL. In older versions of SQL, this statement does nothing. As of 3.22, this statement is equivalent to the ALTER TABLE ADD INDEX statement. To perform the CREATE INDEX statement, you must have INDEX privileges for the table in question. The UNIQUE keyword constrains the table to having only one row in which the index columns have a given value. If the index is multicolumn, individual column values may be repeated; the whole index must be unique. The FULLTEXT keyword enables keyword searching on the indexed column or columns. ExampleCREATE UNIQUE INDEX TransIDX ON Translation ( language, locale, code );
The CREATE TABLE statement defines the structure of a table within the database. This statement is how all MySQL tables are created. If the TEMPORARY keyword is used, the table exists only as long as the current client connection exists, or until you explicitly drop the table. The IF NOT EXISTS clause tells MySQL to create the table only if the table does not already exist. If the table does exist, nothing happens. If the table exists and IF NOT EXISTS and TEMPORARY are not specified, an error will occur. If TEMPORARY is specified and the table exists but IF NOT EXISTS is not specified, the existing table will simply be invisible to this client for the duration of the new temporary table’s life. The CREATE clause can either define the structure of a specific column or define a meta-structure for the column. A CREATE clause that defines a column consists of the name of the new table followed by any number of field definitions. The syntax of a field definition is: column type [NOT NULL | NULL] [DEFAULT value] [AUTO_INCREMENT] [PRIMARY KEY] [reference] The modifiers in this syntax are:
You may specify meta-structure such as indexes and constraints via the following clauses:
In addition, MySQL supports the following special “types,” and the MySQL team is working on adding functionality to support them:
As of MySQL 3.23, you can specify table options at the end of a CREATE TABLE statement. These options are:
Finally, you can create a table and populate it straight from the results of a SQL query: CREATE TABLE tblname SELECT query You must have CREATE privileges on a database to use the CREATE TABLE statement. Examples# Create the new empty database 'employees' CREATE DATABASE employees; # Create a simple table CREATE TABLE emp_data ( id INT, name CHAR(50) ); # Create a complex table CREATE TABLE IF NOT EXISTS emp_review ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, emp_id INT NOT NULL REFERENCES emp_data ( id ), review TEXT NOT NULL, INDEX ( emp_id ), FULLTEXT ( review ) ) AUTO_INCREMENT = 1, TYPE=InnoDB; # Make the function make_coffee (which returns a string # value and is stored in the myfuncs.so shared library) # available to MySQL. CREATE FUNCTION make_coffee RETURNS string SONAME "myfuncs.so"; # Create a table using the resultss from another query CREATE TABLE Stadium SELECT stadiumName, stadiumLocation FROM City;
Deletes rows from a table. When used without a WHERE clause, this will erase the entire table and recreate it as an empty table. With a WHERE clause, it will delete the rows that match the condition of the clause. This statement returns the number of rows deleted. In versions prior to MySQL 4, omitting the WHERE clause will erase this entire table. This is done by using an efficient method that is much faster than deleting each row individually. When using this method, MySQL returns 0 to the user because it has no way of knowing how many rows it deleted. In the current design, this method simply deletes all the files associated with the table except for the file that contains the actual table definition. Therefore, this is a handy method of zeroing out tables with unrecoverably corrupt data files. You will lose the data, but the table structure will still be in place. If you really wish to get a full count of all deleted tables, use a WHERE clause with an expression that always evaluates to true: DELETE FROM TBL WHERE 1 = 1; The LOW_PRIORITY modifier causes MySQL to wait until no clients are reading from the table before executing the delete. For MyISAM tables, QUICK causes the table handler to suspend the merging of indexes during the DELETE, to enhance the speed of the DELETE. The LIMIT clause establishes the maximum number of rows that will be deleted in a single shot. When deleting from MyISAM tables, MySQL simply deletes references in a linked list to the space formerly occupied by the deleted rows. The space itself is not returned to the operating system. Future inserts will eventually occupy the deleted space. If, however, you need the space immediately, run the OPTIMIZE TABLE statement or use the myisamchk utility. The second two syntaxes are new multi-table DELETE statements that enable the deletion of rows from multiple tables. The first is new as of MySQL 4.0.0, and the second was introduced in MySQL 4.0.2. In the first multi-table DELETE syntax, the FROM clause does not name the tables from which the DELETEs occur. Instead, the objects of the DELETE command are the tables from which the deletes should occur. The FROM clause in this syntax works like a FROM clause in a SELECT in that it names all of the tables that appear either as objects of the DELETE or in the WHERE clause. I recommend the second multi-table DELETE syntax because it avoids confusion with the single table DELETE. In other words, it deletes rows from the tables specified in the FROM clause. The USING clause describes all the referenced tables in the FROM and WHERE clauses. The following two DELETEs do the exact same thing. Specifically, they delete all records from the emp_data and emp_review tables for employees in a specific department. DELETE emp_data, emp_review FROM emp_data, emp_review, dept WHERE dept.id = emp_data.dept_id AND emp_data.id = emp_review.emp_id AND dept.id = 32; DELETE FROM emp_data, emp_review USING emp_data, emp_review, dept WHERE dept.id = emp_data.dept_id AND emp_data.id = emp_review.emp_id AND dept.id = 32; You must have DELETE privileges on a database to use the DELETE statement. Examples# Erase all of the data (but not the table itself) for the table 'olddata'. DELETE FROM olddata # Erase all records in the 'sales' table where the 'syear' field is '1995'. DELETE FROM sales WHERE syear=1995
Gives information about a table or column. While this statement works as advertised, its functionality is available (along with much more) in the SHOW statement. This statement is included solely for compatibility with Oracle SQL. The optional column name can contain SQL wildcards, in which case information will be displayed for all matching columns. Example# Describe the layout of the table 'messy' DESCRIBE messy # Show the information about any columns starting # with 'my_' in the 'big' table. # Remember: '_' is a wildcard, too, so it must be # escaped to be used literally. DESC big my_%
Synonym for DESCRIBE.
Permanently remove a database from MySQL. Once you execute this statement, none of the tables or data that made up the database are available. All support files for the database are deleted from the filesystem. The number of files deleted will be returned to the user. This statement is equivalent to running the mysqladmindrop utility. As with running mysqladmin, you must be the administrative user for MySQL (usually root or mysql) to perform this statement. You may use the IF EXISTS clause to prevent any error message that would result from an attempt to drop a nonexistent table.
Will remove a user-defined function from the running MySQL server process. This does not actually delete the library file containing the function. You may add the function again at any time using the CREATE FUNCTION statement. In the current implementation, DROP FUNCTION simply removes the function from the function table within the MySQL database. This table keeps track of all active functions.
Provides compatibility with other SQL implementations. In older versions of MySQL, this statement does nothing. As of 3.22, this statement is equivalent to ALTER TABLE … DROP INDEX. To perform the DROP INDEX statement, you must have SELECT, INSERT, DELETE, UPDATE, CREATE, and DROP privileges for the table in question.
Will erase an entire table permanently. In the current implementation, MySQL simply deletes the files associated with the table. As of 3.22, you may specify IF EXISTS to make MySQL not return an error if you attempt to remove a table that does not exist. The RESTRICT and CASCADE keywords do nothing; they exist solely for ANSI compatibility. You must have DELETE privileges on the table to use this statement.
Used with a table name, this command is an alias for SHOW COLUMNS FROM table_name. Used with an SQL statement, this command displays verbose information about the order and structure of a SELECT statement. This can be used to see where keys are not being used efficiently. This information is returned as a result set with the following columns:
ExampleEXPLAIN SELECT customer.name, product.name FROM customer, product, purchases WHERE purchases.customer=customer.id AND purchases. product=product.id
Flushes or resets various internal processes depending on the options given. You must have RELOAD privileges to execute this statement. The option can be any of the following:
In versions prior to MySQL 3.22.11, the GRANT statement was recognized but did nothing. In current versions, GRANT is functional. This statement enables access rights to a user (or users). Access can be granted per database, table or individual column. The table can be given as a table within the current database; use * to affect all tables within the current database, *.* to affect all tables within all databases or database.* to affect all tables within the given database. The following privileges are currently supported:
The user variable is of the form user@hostname. Either the user or the hostname can contain SQL wildcards. When wildcards are used, either the whole name must be quoted, or just the parts with the wildcards (e.g., joe@”%.com ” and “joe@%.com” are both valid)[1]. A user without a hostname is considered to be the same as user@”%“.
If you have a global GRANT privilege, you may specify an optional INDENTIFIED BY modifier. If the user in the statement does not exist, it will be created with the given password. Otherwise, the existing user will have her password changed. The GRANT privilege is given to a user with the WITH GRANT OPTION modifier. If this is used, the user may grant any privilege she has to another user. You may alternately chose to limit the number of queries made by a particular user ID through the MAX_QUERIES_PER_HOUR option. Support for secure SSL encryptions, as well as X.509 authentication, has recently been added to MySQL. The REQUIRE clause enables you to require a user to authenticate in one of these manners and identify the credentials to be used. Just specifying REQUIRE SSL tells MySQL that the user can connect to MySQL using only an SSL connection. Similarly, REQUIRE X509 requires the user to authenticate using an X.509 certificate. You can place the following restrictions on the connection:
Examples# Give full access to joe@carthage for the Account table GRANT ALL ON bankdb.Account TO joe@carthage; # Give full access to jane@carthage for the # Account table and create a user ID for her GRANT ALL ON bankdb.Account TO jane@carthage IDENTIFIED BY 'mypass'; # Give joe the ability # to SELECT from any table on the webdb database GRANT SELECT ON webdb.* TO joe; # Give joe on the local machine access to everything in webdb but # require some special security GRANT ALL on webdb.* TO joe@localhost IDENTIFIED BY 'mypass' REQUIRE SUBJECT `C=US, ST=MN, L=Minneapolis, O=My Cert, CN=Joe Friday/Email=joe@localhost' AND ISSUER='C=US, ST=MN, L=Minneapolis, O=Imaginet, CN=Joe Friday/Email=joe@localhost' AND CIPHER='RSA-DES-3DES-SHA';
Inserts data into a table. The first form of this statement simply inserts the given values into the given columns. Columns in the table that are not given values are set to their default values or NULL. The second form takes the results of a SELECT query and inserts them into the table. The third form is simply an alternate version of the first form that more explicitly shows which columns correspond with which values. If the DELAYED modifier is present in the first form, all incoming SELECT statements will be given priority over the insert, which will wait until the other activity has finished before inserting the data. In a similar way, using the LOW_PRIORITY modifier with any form of INSERT causes the insertion to be postponed until all other operations from other clients have been finished. Starting with MySQL 3.22.5, it is possible to insert more than one row into a table at a time. This is done by adding additional value lists to the statement separated by commas. You must have INSERT privileges to use this statement. Examples# Insert a record into the 'people' table.
INSERT INTO people ( name, rank, serial_number )
VALUES ( 'Bob Smith', 'Captain', 12345 );
# Copy all records from 'data' that are older than a
certain date into
# 'old_data'. This would usually be followed by deleting
the old data from
# 'data'.
INSERT INTO old_data ( id, date, field )
SELECT ( id, date, field)
FROM data
WHERE date < 87459300;
# Insert 3 new records into the 'people' table.
INSERT INTO people (name, rank, serial_number )
VALUES ( 'Tim O'Reilly', 'General', 1),
('Andy Oram', 'Major', 4342),
('Randy Yarger', 'Private', 9943);
Terminates the specified thread. The thread ID numbers can be found using SHOW PROCESSLIST. Killing threads owned by users other than yourself requires PROCESS privilege. In MySQL 4.x, this privilege is now the SUPER privilege. Example# Terminate thread 3 KILL 3
Reads a text file and inserts its data into a database table. This method of inserting data is much quicker than using multiple INSERT statements. Although the statement may be sent from all clients like any other SQL statement, the file referred to in the statement is assumed to be located on the server unless the LOCAL keyword is used. If the filename does not have a fully qualified path, MySQL looks under the directory of the current database for the file. With no delimiters specified, LOAD DATA INFILE will assume that the file is tab delimited with character fields, special characters escaped with backslashes (\), and lines terminated with newline characters. In addition to the default behavior, you may specify your own delimiters using the following keywords. Delimiters apply to all tables in the statement.
By default, if a value read from the file is the same as an existing value in the table for a field that is part of a unique key, an error is given. If the REPLACE keyword is added to the statement, the entire row from the table will be replaced with values from the file. Conversely, the IGNORE keyword causes MySQL to ignore the new value and keep the old one. The word NULL encountered in the data file is considered to indicate a null value unless the FIELDS ENCLOSED BY character encloses it, or if no FIELDS ENCLOSED BY clause is specified. Using the same character for more than one delimiter can confuse MySQL. For example, FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘,’ would produce unpredictable behavior. If a list of columns is provided, the data is inserted into those particular fields in the table. If no columns are provided, the number of fields in the data must match the number of fields in the table, and they must be in the same order as the fields are defined in the table. You must have SELECT and INSERT privileges on the table to use this statement. Example# Load in the data contained in 'mydata.txt' into the table 'mydata'. Assume # that the file is tab delimited with no quotes surrounding the fields. LOAD DATA INFILE 'mydata.txt' INTO TABLE mydata # Load in the data contained in 'newdata.txt' Look for two comma delimited # fields and insert their values into the fields 'field1' and 'field2' in # the 'newtable' table. LOAD DATA INFILE 'newdata.txt' INTO TABLE newtable FIELDS TERMINATED BY ',' ( field1, field2 )
Locks a table for the use of a specific thread. This command is generally used to emulate transactions. If a thread creates a READ lock, all other threads may read from the table, but only the controlling thread can write to the table. If a thread creates a WRITE lock, no other thread may read from or write to the table. Example# Lock tables 'table1' and 'table3' to prevent updates, and block all access # to 'table2'. Also create the alias 't3' for 'table3' in the current thread. LOCK TABLES table1 READ, table2 WRITE, table3 AS t3 READ
Recreates a table, eliminating any wasted space and sorting any unsorted index pages. Also updates any statistics that are not currently up to date. This task is performed by creating the optimized table as a separate, temporary table and using it to replace the current table. This command currently works only for MyISAM and BDB tables. If you want the syntax to work no matter what table type you use, you should run mysqld with –skip-new or –safe-mode on. Under these circumstances, OPTIMIZE TABLE is an alias for ALTER TABLE. ExampleOPTIMIZE TABLE mytable
Inserts data into a table, replacing any old data that conflicts. This statement is identical to INSERT except that if a value conflicts with an existing unique key, the new value replaces the old one. The first form of this statement simply inserts the given values into the given columns. Columns in the table that are not given values are set to their default values or to NULL. The second form takes the results of a SELECT query and inserts them into the table. The final form inserts specific values using a syntax similar to an UPDATE statement. Examples# Insert a record into the 'people' table. REPLACE INTO people ( name, rank, serial_number ) VALUES ( 'Bob Smith', 'Captain', 12345 ) # Copy all records from `data' that are older than a certain date into # 'old_data'. This would usually be followed by deleting the old data from # 'data'. REPLACE INTO old_data ( id, date, field ) SELECT ( id, date, field) FROM data WHERE date < 87459300
Removes a privilege from a user. The values of privilege, table, and user are the same as for the GRANT statement. You must have the GRANT privilege to be able to execute this statement.
Retrieves data from a database. The SELECT statement is the primary method of reading data from database tables. If the DISTINCT keyword is present, only one row of data will be output for every group of rows that is identical. The ALL keyword is the opposite of DISTINCT and displays all returned data. The default behavior is ALL. DISTINCT and DISTINCTROWS are synonyms. MySQL provides several extensions to the basic ANSI SQL syntax that help modify how your query runs:
The selected columns’ values can be any one of the following:
By default, MySQL sends all output to the client that sent the query. It is possible however, to have the output redirected to a file. In this way you can dump the contents of a table (or selected parts of it) to a formatted file that can either be human readable, or formatted for easy parsing by another database system. The INTO OUTFILE ‘filename‘ modifier is the means in which output redirection is accomplished. With this, the results of the SELECT query are put into filename. The format of the file is determined by the delimiters arguments, which are the same as the LOAD DATA INFILE statement with the following additions:
The default behavior with no delimiters is to export tab delimited data using backslash (\) as the escape character and to write one record per line. You may optionally specify a DUMPFILE instead of an OUTFILE. This syntax will cause a single row to be placed into the file with no field or line separators. It is used for outputting binary fields. The list of tables to join may be specified in the following ways:
MySQL also supports right joins using the same syntax as left joins—except for the OJ syntax. For portability, however, it is recommended that you formulate your joins as left joins. If no constraints are provided, SELECT returns all the data in the selected tables. You may also optionally tell MySQL whether to use or ignore specific indexes on a join using USE INDEX and IGNORE INDEX. The search constraints can contain any of the following substatements:
|
