Appendix E. SQL Syntax Reference

This appendix describes the syntax for SQL statements provided by MySQL. It has three parts:

• SQL statements other than those associated with stored routines.

• SQL statements for stored routines. These are procedures and functions that are stored on the server side.

• The syntax for writing comments in SQL code. Comments are used to write descriptive text that is ignored by the server and to hide MySQL-specific keywords that will be executed by MySQL but ignored by other database servers.

The syntax descriptions use the following conventions:

• Square brackets ([]) in syntax descriptions indicate optional information.

• Vertical bars (|) separate alternative items in a list. If a list is enclosed in square brackets, one alternative may be chosen. If a list is enclosed in curly brackets ({}), one alternative must be chosen.

• Ellipsis notation (…) indicates that the term preceding the ellipsis may be repeated.

• n indicates an integer.

• 'string' indicates a string value. A quoted value such as 'file_name' or 'pattern' indicates a more-specific kind of value such as a filename or a pattern.

There are some general synonyms that always hold, so they are listed here rather than every place in which they can be used:

• To specify a character set, you can use any of the following formats:

  CHARACTER SET charset
  CHARSET = charset
  CHARSET charset
  

  
  

  These synonymous forms can be used in table and column definitions, and in the CREATE DATABASE and ALTER DATABASE statements.

• As of MySQL 5.0.2, SCHEMA and SCHEMAS are treated as synonyms for DATABASE and DATABASES, respectively, and can freely be substituted in statements anywhere you might use the latter two keywords. For example, you can create a database with either CREATE DATABASE or CREATE SCHEMA. Over time, the use of DATABASE and DATABASES will be phased out. However, I continue to use them here as the preferred terms because this change is relatively recent and will take time to permeate the installed base.

Unless otherwise indicated, the statements listed here have been present in MySQL at least as early as MySQL 4.1.0. Statements that were introduced or that changed in meaning since then are noted.