Syntax

ALTER [IGNORE] TABLE table action_list

Description

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.

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

Syntax

ANALYZE TABLE table1, table2, ..., tablen

Description

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:

Table

The name of the table.

Op

The value analyze.

Msg_type

One of status, error, or warning.

Msg_text

The message resulting from the analysis.

Syntax

CREATE DATABASE [IF NOT EXISTS] dbname

Description

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 mysqladmin create utility.

Example

CREATE DATABASE Bank;

Syntax

CREATE [AGGREGATE] FUNCTION name 
RETURNS return_type SONAME library

Description

Syntax

CREATE [UNIQUE|FULLTEXT] INDEX name
ON table (column, ...)

Description

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.

Example

CREATE UNIQUE INDEX TransIDX ON Translation ( language, locale, code );

Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
(create_clause, ...) [table_options]
[[IGNORE|REPLACE] select]

Description

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]

When indexing character fields (CHAR, VARCHAR, and their synonyms only), it is possible to index only a prefix of the entire field. For example, the following will create an index of the numeric field id along with the first 20 characters of the character field address:

INDEX adds ( id, address(20) );

When performing any searches of the field address, only the first 20 characters will be used for comparison, unless more than one match is found that contains the same first 20 characters, in which case a regular search of the data is performed. Therefore, it can be a big performance bonus to index only the number of characters in a text field that you know will make the value unique.

Fields contained in an index must be defined with the NOT NULL modifier. When adding an index as a separate declaration, MySQL will generate an error if NOT NULL is missing. However, when defining the primary key by adding the PRIMARY KEY modifier to the field definition, the NOT NULL modifier is added automatically (without a warning) if it is not explicitly defined.

In addition to the above, MySQL supports the following special "types," and the team is working on adding functionality to support them:

Table 15-2. MySQL table types

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";

Syntax

DELETE [LOW_PRIORITY | QUICK]
FROM table [WHERE clause] [ORDER BY column, ...]
[LIMIT n]

DELETE [LOW_PRIORITY | QUICK]
table1[.*], table2[.*], ..., tablen[.*]
FROM tablex, tabley, ..., tablez [WHERE clause]

DELETE [LOW_PRIORITY | QUICK]
FROM table1[.*], table2[.*], ..., tablen[.*]
USING references
[WHERE clause]

Description

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.

As mentioned above, 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. 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 DELETE s occur. Instead, the objects of the DELETE command are the tables to delete from. 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.

We 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 DELETE s 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

Syntax

DESCRIBE table [column]
DESC table [column]

Description

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\_%

Syntax

DROP DATABASE [IF EXISTS] name

Description

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 the 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 mysqladmin drop 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.

Syntax

DROP FUNCTION name

Description

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.

Syntax

DROP INDEX idx_name ON tbl_name

Description

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.

Syntax

DROP TABLE [IF EXISTS] name [, name2, ...]
[RESTRICT | CASCADE]

Description

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.

WARNING: DROP is by far the most dangerous SQL statement. If you have drop privileges, you may permanently erase a table or even an entire database. This is done without warning or confirmation. The only way to undo a DROP is to restore the table or database from backups. The lessons to be learned here are: (1) always keep backups; (2) don't use DROP unless you are really sure; and (3) always keep backups.

Syntax

EXPLAIN [table_name | sql_statement]

Description

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:

Syntax

FLUSH option[, option...]

Description

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:

DES_KEY_FILE

Reloads the DES keys from the file originally specified with the --des-key-file option.

HOSTS

Empties the cache table that stores hostname information for clients. This should be used if a client changes IP addresses, or if there are errors related to connecting to the host.

LOGS

Closes all the standard log files and reopens them. This can be used if a log file has changed its inode number. If no specific extension has been given to the update log, a new update log will be opened with the extension incremented by one.

PRIVILEGES

Reloads all the internal MySQL permissions grant tables. This must be run for any changes to the tables to take effect.

QUERY CACHE

For better memory use, this command defragments the query cache but it does not delete queries from the cache.

STATUS

Resets the status variables that keep track of the current state of the server.

TABLE table

TABLES table, table2, ..., tablen

Flushes only the specified tables.

TABLES [WITH READ LOCK]

Closes all currently open tables and flushes any cached data to disk. With a read lock, it acquires a read lock that will not be released until UNLOCK TABLES is issued.

Syntax

GRANT privilege
[ (column, ...) ] [, privilege [( column, ...) ] ...]
ON {table} TO user [IDENTIFIED BY 'password']
[, user [IDENTIFIED BY 'password'] ...]
[REQUIRE [{SSL | X509}] [CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR=limit]]

Description

Previous 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:

ALL PRIVILEGES/ALL

Assigns all privileges except FILE, PROCESS, RELOAD, and SHUTDOWN

ALTER

To alter the structure of tables

CREATE

To create new tables

DELETE

To delete rows from tables

DROP

To delete entire tables

FILE

To create and remove entire databases as well as manage log files

INDEX

To create and delete indexes from tables

INSERT

To insert data into tables

PROCESS

To kill process threads

REFERENCES

Not implemented (yet)

RELOAD

To refresh various internal tables (see the FLUSH statement)

SELECT

To read data from tables

SHUTDOWN

To shut down the database server

UPDATE

To alter rows within tables

USAGE

No privileges at all

The user variable is of the form user@hostname. Either the user or the hostname can contain SQL wildcards. If 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). 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:

ISSUER issuer

Demands that the certificate have the issuer specified.

SUBJECT subject

Not only does the user have to have a valid certificate, but it must have a certificate for the specified subject.

CIPHER cipher

Enables MySQL to enforce a minimum encryption strength. The connection must use one of the ciphers specified here.

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';

Syntax

INSERT [DELAYED | LOW_PRIORITY ] [IGNORE] 
[INTO] table [ (column, ...) ] 
VALUES ( values [, values... ])

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

INSERT [DELAYED | LOW_PRIORITY] [IGNORE]
[INTO] table
SET column=value, column=value,...

Description

When using a SELECT query with the INSERT statement, you cannot use the ORDER BY modifier with the SELECT statement. Also, you cannot insert into the same table from which you are selecting.

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);

Syntax

KILL thread_id

Description

Example

# Terminate thread 3
KILL 3

Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE file [REPLACE|IGNORE]
INTO TABLE table [delimiters] [(columns)]

Description

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 for the current database of 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.

The word NULL encountered in the data file is considered to indicate a null value unless the FIELDS ENCLOSED BY character encloses it.

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 )

Syntax

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

Description

WARNING: Using locked and unlocked tables at the same time can cause the process thread to freeze. You must lock all the tables you will be accessing during the time of the lock. Tables you access only before or after the lock do not need to be locked. The newest versions of MySQL generate an error if you attempt to access an unlocked table while you have other tables locked.

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

Syntax

OPTIMIZE TABLE name

Description

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 then replacing the current table with it. 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 circumstance, OPTIMIZE TABLE is an alias for ALTER TABLE.

Example

OPTIMIZE TABLE mytable

Syntax

REPLACE [DELAYED | LOW_PRIORITY]
INTO table [(column, ...)]
VALUES (value, ...)

REPLACE [DELAYED | LOW_PRIORITY]
INTO table [(column, ...)]
SELECT select_clause

REPLACE [DELAYED | LOW_PRIORITY]
INTO table
SET column=value, column=value, ...

Description

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.

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

Syntax

REVOKE privilege [(column, ...)] [, privilege [(column, ...) ...]
ON table FROM user

Description

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.

Syntax

SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
[HIGH_PRIORITY]
[DISTINCT | | DISTINCTROW | ALL] 
column [[AS] alias][, ...]
[INTO {OUTFILE | DUMPFILE} 'filename' delimiters]
[FROM table [[AS] alias]
[USE INDEX (keys)] [IGNORE INDEX (keys)][, ...]
[constraints]]
[UNION [ALL] select substatement]

Description

Retrieves data from a database. The SELECT statement is the primary method of reading data from database tables.

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.

MySQL also supports right joins using the same syntax as left joins. 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:

Examples

# Find all names in the 'people' table where the 'state' field is 'MI'.
SELECT name FROM people WHERE state='MI'
# Display all of the data in the 'mytable' table.
SELECT * FROM mytable

Syntax

SET OPTION SQL_OPTION=value

Description

Defines an option for the current session. Values set by this statement are not in effect anywhere but the current connection, and they disappear at the end of the connection. The following options are currently supported:

Example

# Turn off logging for the current connection.
SET OPTION SQL_LOG_OFF=1

Syntax

SHOW [FULL] COLUMNS    FROM table [FROM database] [LIKE clause]
SHOW DATABASES [LIKE clause]
SHOW FIELDS FROM table [FROM database] [LIKE clause]
SHOW GRANTS FOR user
SHOW INDEX FROM table [FROM database]
SHOW KEYS FROM table [FROM database]
SHOW LOGS
SHOW MASTER STATUS
SHOW MASTER LOGS
SHOW [FULL] PROCESSLIST
SHOW SLAVE STATUS
SHOW STATUS [LIKE clause]
SHOW TABLE STATUS [FROM database [LIKE clause]]
SHOW [OPEN] TABLES [FROM database] [LIKE clause]
SHOW VARIABLES [LIKE clause]

Description

Displays a lot of different information about the MySQL system. This statement can be used to examine the status or structure of almost any part of MySQL.

Examples

# Show the available databases
SHOW DATABASES
# Display information on the indexes on table 'bigdata'
SHOW KEYS FROM bigdata
# Display information on the indexes on table 'bigdata' 
# in the database 'mydata'
SHOW INDEX FROM bigdata FROM mydata
# Show the tables available from the database 'mydata' that begin with the
# letter 'z'
SHOW TABLES FROM mydata LIKE 'z%'
# Display information about the columns on the table 'skates'
SHOW COLUMNS FROM stakes
# Display information about the columns on the table 'people' 
# that end with '_name'
SHOW FIELDS FROM people LIKE '%\_name'
# Show server status information.
SHOW STATUS
# Display server variables
SHOW VARIABLES

Syntax

TRUNCATE TABLE table

Description

Drops and recreates the specified table.

Example

# Truncate the emp_data table
TRUNCATE TABLE emp_data;

Syntax

UNLOCK TABLES

Description

Unlocks all tables that were locked using the LOCK statement during the current connection.

Example

# Unlock all tables
UNLOCK TABLES

Syntax

UPDATE [LOW_PRIORITY] [IGNORE] table 
SET column=value, ...
[WHERE clause]
[LIMIT n]

Description

Alters data within a table. You may use the name of a column as a value when setting a new value. For example, UPDATE health SET miles_ran=miles_ran+5 would add five to the current value of the miles_ran column.

Syntax

USE database

Description

Selects the default database. The database given in this statement is used as the default database for subsequent queries. Other databases may still be explicitly specified using the database.table.column notation.

Example

# Make db1 the default database.
USE db1