SQL Syntax for MySQL (Managing and Using MySQL, 2nd Edition)

15.1. Basic Syntax

SQL is a kind of controlled English language consisting of verb phrases. Each of these verb phrases begins with an SQL command followed by other SQL keywords, literals, identfiers, or punctuation. Keywords are never case sensitive. Identifiers for database names and table names are case sensitive when the underlying filesystem is case sensitive (this includes all Unix except Mac OS X) and case insensitive when the underlying filesystem is case insensitive (this includes Mac OS X and Windows). You should, however, avoid referring to the same database or table name in a single SQL statement using different cases—even if the underlying operating system is case insensitive. For example, the following SQL is troublesome:

SELECT TBL.COL FROM tbl;

Table aliases are case sensitive, but column aliases are case insensitive.

If all this case sensitivity nonsense is annoying, you can force MySQL to convert all table names to lowercase by starting mysqld with the argument -O lower_case_table_names=1.

15.1.1. Literals

Literals come in the following varieties:

String literals

Table 15-1. MySQL escape sequences

Escape Sequence	Value
`\0`	`NUL`
`\'`	Single quote
`\"`	Double quote
`\b`	Backspace
`\n`	Newline
`\r`	Carriage return
`\t`	Tab
`\z`	Ctrl-z (workaround for Windows use of Ctrl-z as EOF)
`\\`	Backslash
`\%`	Percent sign (only in contexts where a percent sign would be interpreted as a wildcard)
`\_`	Underscore (only in contexts where an underscore would be interpreted as a wildcard)

15.1.2. Identifiers

Identifiers are names you make up to reference database objects. In MySQL, database objects consist of databases, tables, and columns. These objects fit into a hierarchical namespace whose root element is the database in question. You can reference any given object on a MySQL server—assuming you have the proper rights—using one of the following conventions:

Absolute naming

In general, MySQL allows you to use any character in an identifier.[33] This rule is limited, however, for databases and tables, because these values must be treated as files on the local filesystem. You can therefore use only characters valid for the underlying filesystem's filenaming conventions in a database or table name. Specifically, you may not use / or . in a database or table name. You can never use NUL (ASCII 0) or ASCII 255 in an identifier.

[33]Older versions of MySQL limited identifiers to valid alphanumeric characters from the default character set as well as $ and _.

Given these rules, it is very easy to shoot yourself in the foot when naming things. As a general rule, it is a good idea to stick to alphanumeric characters from whatever character set you are using.

When an identifier is also an SQL keyword, you must enclose the identifier in backticks:

CREATE TABLE `select` ( `table` INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

Since Version 3.23.6, MySQL supports the quoting of identifiers using both backticks and double quotes. For ANSI compatibility, however, you should use double quotes for quoting identifiers. You must, however, be running MySQL in ANSI mode.

15.1.3. Comments

You can introduce comments in your SQL to specify text that should not be interpreted by MySQL. This is particularly useful in batch scripts for creating tables and loading data. MySQL specifically supports three kinds of commenting: C, shell-script, and ANSI SQL commenting.

C commenting treats anything between /* and */ as comments. Using this form of commenting, your comments can span multiple lines. For example:

/*
 * Creates a table for storing customer account information.
*/
DROP TABLE IF EXISTS ACCOUNT;

CREATE TABLE ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                      BALANCE DECIMAL(9,2) NOT NULL );

Within C comments, MySQL still treats single quotes and double quotes as a start to a string literal. In addition, a semicolon in the comment will cause MySQL to think you are done with the current statement.

Shell-script commenting treats anything from a # character to the end of a line as a comment:

CREATE TABLE ACCOUNT ( ACCOUNT_ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                      BALANCE DECIMAL(9,2) NOT NULL ); # Not null ok?

MySQL does not really support ANSI SQL commenting, but it comes close. ANSI SQL commenting is distinguished by adding -- to the end of a line. MySQL supports two dashes and a space ('-- ') followed by the comment. The space is the non-ANSI part:

DROP TABLE IF EXISTS ACCOUNT; -- Drop the table if it already exists

Table 15-1. MySQL escape sequences

Chapter 15. SQL Syntax for MySQL

Contents:

15.1. Basic Syntax

15.1.1. Literals

15.1.2. Identifiers

15.1.3. Comments