Chapter 3. MySQL SQL Syntax and Use

Fluency with SQL is necessary for effective communication with the MySQL server, because that is the language that it understands. For example, when you use a program such as the mysql client, it functions primarily as a means 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 sending SQL statements to it.

Chapter 1, "Getting Started with MySQL and SQL," presented a tutorial introduction to many of MySQL's capabilities. This chapter builds on that material to go into more detail on several areas of SQL implemented by MySQL. It discusses how to refer to elements of databases, including the rules for naming and the case sensitivity constraints that apply. It also describes many of the more important SQL statements that are used for the following types of operations:

Creating and destroying databases, tables, and indexes
Obtaining information about your databases and tables
Retrieving data using joins, subselects, 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

MySQL's SQL statements can be grouped into several broad categories; Table 3.1 lists some representative statements for each. In some cases, a utility program is available that provides a command-line interface to a statement. For example, mysqlshow allows SHOW operations to be performed from the command line. This chapter points out such equivalences where appropriate.

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, "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 D, "SQL Syntax Reference." In addition, you should consult the MySQL Reference Manual for additional information, especially for changes made in recent versions of MySQL.

Table 3.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 `BEGIN` `COMMIT` `ROLLBACK` `SET AUTOCOMMIT`
MODIFYING INFORMATION IN TABLES `DELETE` `INSERT` `LOAD DATA` `REPLACE` `UPDATE`
ADMINISTRATIVE STATEMENTS `FLUSH` `GRANT` `REVOKE`

The final section of the chapter describes what MySQL does not include?that is, what features it lacks. These are capabilities found in some other databases but not in MySQL. Such features include triggers, stored procedures, and views. Do these omissions mean that MySQL isn't a "real" database system? Some people think so, but in response I'll simply observe that the lack of these capabilities in MySQL hasn't stopped large numbers of people from using it. That's probably because for many or most applications, those features don't matter.

I should also point out that the set of features missing from MySQL continues to shrink over time. For the first edition of this book, the list of missing features included transactions, subselects, foreign keys, and referential integrity. A significant amount of progress has been made in improving MySQL since then, and those capabilities all have been added now. Triggers, stored procedures, and views are scheduled for implementation in the future.