Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

are used, the values in the VALUES clause must match up to the column list by position, type, and

number.

If using the SELECT clause, the results of the SELECT clause must match by type, position, and number

of columns in the table; If a column list is used, the results must match up to it.

The optional LOW_PRIORITY will cause the insertion to be delayed until the table is not in use.

The DELAYED option causes the insertion to be delayed until the table is free. It will also bundle multiple

statements together and write them all at once, improving performance.

Example:

INSERT INTO Customers

(Customer_ID, First_Name, Last_Name)

VALUES (NULL, 'Scott', 'Johnson')

KILL

thread_ID
This statement kills the process identified by the thread. This can also be accomplished using the
mysqladmin command. The thread's ID can be seen after a SHOW PROCESSLIST command.

Example:

KILL 18

LOAD DATA

[LOW PRIORITY] [LOCAL] INFILE 'file name' [IGNORE or REPLACE] INTO table name,
[FIELDS TERMINATED BY symbol] [ENCLOSED BY symbol], [ESCAPED BY symbol] [LINES
TERMINATED BY symbol],[IGNORE n LINES] [(column_list)]
This statement causes a text file named 'file name' to be loaded into a table.
If the optional keyword LOCAL is used, MySQL will look for the file on the client machine. If this word is
absent, the file will be loaded from the server.

A full path must be given for the filename.

The table must have the proper number of columns with matching data types. If not, errors similar to

those in an INSERT statement will occur.

Example:

The following example will load a comma-delimited text file, named accessdata.txt, into the

Customers table. The fields are enclosed with quotation marks.

LOAD DATA INFILE '/home/mark/accessdata.txt' INTO Customers

FIELDS TERMINATED BY "," ENCLOSED BY """

LOCK TABLES

table_name {READ or WRITE}[, table_name {READ or WRITE}] ...

This statement locks a table for the named process. When locking tables, it is important to lock all the

tables you are going to use.

The LOCK remains in effect until either it is unlocked or the thread that issued the lock dies. See

UNLOCK.

Example:

LOCK TABLES Customers READ, Orders WRITE

SELECT C.* FROM Customer AS C, Orders AS O WHERE O.Customer_ID = C.Customer_ID

UNLOCK TABLES

In this example, the Customers and the Orders table had to be locked because the SELECT

statement used both tables.

OPTIMIZE TABLE

table_name

This statement frees up the dead space that is held by deleted records.

This statement should be used when large numbers of records have been deleted or variable length

fields have been altered.

The table is read-only until this operation is completed.