Data Manipulation Statements

This section of my documentation of MySQL explains SQL statements in MySQL related to data manipulation: adding, changing, and deleting data, as well as retrieving selected data. Statements that create and alter databases and tables are covered in the Database & Table Schema Statements section. In essence, this section covers SQL statements used when manipulating the data itself, not when developing or altering a database. The two modes involve fairly distinct mindsets and are sometimes conducted by different persons.

Below is a listing of related SQL statements followed by related clauses, with brief explanations of each one's purpose. Click on an SQL statement to see a more detailed explanation including examples of the statement's use.

DELETE

Use this MySQL statement to delete rows of data from a given table. Be careful with this one: there's basically no UNDO statement.

DO

This MySQL statement suppresses the display of an expression's results. I can't think of much point to this one. Please let me know you can.

EXPLAIN

Use this MySQL statement to display information about the columns of a given table or the handling of a SELECT statement. It used to be similar to DESCRIBE and SHOW COLUMNS statements. Now it shows which index the statement will use and, when multiple tables are queried, the order in which the tables are used. It can be helpful in determining the cause of a slow query.

HANDLER

A handle provides direct access to a table, as opposed to working from a results set. Handles can be faster than a SELECT statement when reading large numbers of rows from a table. MyISAM and InnoDB tables currently support handlers.

INSERT

This MySQL statement is used to add rows of data to a table. It has a few primary syntax with many options.

This MySQL statement can be used to import organized data from a text file into a table in MySQL. The file can be either on the server or on the client.

REPLACE

This MySQL statement to insert new rows of data and to replace existing rows where the PRIMARY KEY or UNIQUE index key is the same as the new record being inserted.

SELECT

To retrieve and display data from tables within a database, use this MySQL statement. It also has more than one syntax and many options—it also allows for sub-queries.

SET

This MySQL statement sets a system or user variable for global or session use.

SHOW ERRORS

Use this MySQL statement to display error messages for the previous SQL statement. It has a COUNT(*) clause that you can use to see the number of error messages generated rather than displaying them.

SHOW WARNINGS

Use this MySQL statement to display warning messages, error messages, and notes for previous SQL statements for the current session. It also has a COUNT(*) clause that you can use to see the number of error messages generated rather than displaying them.

TRUNCATE

Use this MySQL statement to delete the contents of a table rapidly. It's similar to the DELETE statement in that it will delete all of the data contained in a given table.

UPDATE

If you want to change data in a table or just in some rows—even one row only—use this MySQL statement. Click on the heading here for more details.