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: String literals may be enclosed either by single or double quotes. If you wish to be ANSI compatible, you should always use single quotes. Within a string literal, you may represent special characters through escape sequences. An escape sequence is a backslash followed by another character to indicate to MySQL that the second character has a meaning other than its normal meaning. Table 15-1 shows the MySQL escape sequences. Quotes can also be escaped by doubling them up: 'This is a ''quote'''. However, you do not need to double up on single quotes when the string is enclosed by double quotes: "This is a 'quote'".
Binary literals: Like string literals, binary literals are enclosed in single or double quotes. You must use escape sequences in binary data to escape NUL (ASCII 0), " (ASCII 34), ' (ASCII 39), and \ (ASCII 92).
Number literals: Numbers appear as a sequence of digits. Negative numbers are preceded by a - sign and a . indicates a decimal point. You may also use scientific notation, as in: -45198.2164e+10.
Hexadecimal literals: MySQL also supports the use of hexadecimal literals in SQL. The way in which that hexadecimal is interpreted is dependent on the context. In a numeric context, the hexadecimal literal is treated is a numeric value. In a non-numeric context, it is treated as a binary value. For example, 0x1 + 1 is 2, but 0x4d7953514c by itself is MySQL.
Null: The special keyword NULL signifies a null literal in SQL. In the context of import files, the special escape sequence \N signifies a null value.

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

Absolute naming is specifying the full tree of the object you are referencing. For example, the column BALANCE in the table ACCOUNT in the database BANK would be referenced absolutely as:

BANK.ACCOUNT.BALANCE

Relative naming

Relative naming allows you to specify only part of the object's name, with the rest of the name being assumed based on your current context. For example, if you are currently connected to the BANK database, you can reference the BANK.ACCOUNT.BALANCE column simply as ACCOUNT.BALANCE. In an SQL query where you have specified that you are selecting from the ACCOUNT table, you can reference the column using only BALANCE. You must provide an extra layer of context whenever relative naming might result in ambiguity. An example of such ambiguity would be a SELECT statement pulling from two tables that both have BALANCE columns.

Aliasing

Aliasing enables you to reference an object using an alternate name that helps avoid both ambiguity and the need to fully qualify a long name.

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

Chapter 15. SQL Syntax for MySQL

Contents:

15.1. Basic Syntax

15.1.1. Literals

Table 15-1. MySQL escape sequences

15.1.2. Identifiers

15.1.3. Comments