Chapter 2. MySQL SQL Syntax and Use

Structured Query Language (SQL) is the language that the MySQL server understands, so fluency with SQL is necessary for effective communication with the server. When you use a program such as the mysql client, it functions primarily as a way for you to send SQL statements to the server to be executed. You must also know SQL if you write programs that use the MySQL interface provided by your programming language, because the interface functions as the means that allows you to communicate with the server by issuing SQL statements.

Chapter 1, "Getting Started with MySQL and SQL," presents a tutorial introduction to many of MySQL's capabilities. Now we'll build on that material to go into more detail on several areas of SQL implemented by MySQL:

• Naming rules for referring to elements of databases

• Changing the server SQL mode to affect its behavior

• Support for multiple character sets

• Creating and destroying databases, tables, and indexes

• Obtaining information about your databases and tables

• Retrieving data using joins, subqueries, and unions

• Using multiple-table deletes and updates

• Performing transactions that allow multiple statements to be treated as a unit

• Setting up foreign key relationships

• Using the FULLTEXT search engine

• New features in MySQL 5.0: Views, stored procedures and functions, and triggers

Several aspects of how the MySQL server executes SQL statements can be modified by setting its SQL mode. Instructions for doing this are given in "The Server SQL Mode."

MySQL's SQL statements may be grouped into several broad categories; Table 2.1 lists representative statements for each. Some of the statements in the table are not covered here because they are more appropriately discussed in other chapters. For example, the administrative statements GRANT and REVOKE for setting up user privileges are dealt with in Chapter 11, "General MySQL Administration." Chapter 12, "MySQL and Security," provides further details on what privileges are available and what they allow. The syntax for all SQL statements implemented by MySQL is listed in Appendix E, "SQL Syntax Reference." That appendix also covers the syntax for using comments in your SQL statements. In addition, you should consult the MySQL Reference Manual for additional information, especially for changes made in the most recent versions of MySQL.

Table 2.1. Types of SQL Statements Supported by MySQL

SELECTING, CREATING, DROPPING, AND ALTERING DATABASES
	USE
	CREATE DATABASE
	DROP DATABASE
	ALTER DATABASE
CREATING, ALTERING, AND DROPPING TABLES AND INDEXES
	CREATE TABLE
	DROP TABLE
	CREATE INDEX
	DROP INDEX
	ALTER TABLE
GETTING INFORMATION ABOUT DATABASES AND TABLES
	DESCRIBE
	SHOW
RETRIEVING INFORMATION FROM TABLES
	SELECT
	UNION
PERFORMING TRANSACTIONS
	SET AUTOCOMMIT
	START TRANSACTION
	COMMIT
	ROLLBACK
MODIFYING INFORMATION IN TABLES
	DELETE
	INSERT
	LOAD DATA
	REPLACE
	UPDATE
ADMINISTRATIVE STATEMENTS
	FLUSH
	GRANT
	REVOKE