Chapter 15. SQL Syntax for MySQL
In this chapter, we cover the full range of SQL supported by MySQL. MySQL supports the ANSI SQL2 standard. If you are interested in compatibility with other SQL databases, you should avoid using any proprietary MySQL extensions to the SQL standard.
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.
Table 15-1. MySQL escape sequences
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:
In general, MySQL allows you to use any character in an identifier. 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.
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.
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.
/* * 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.
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
Copyright © 2003 O'Reilly & Associates. All rights reserved.