MySQL® Administrator's Bible

Chapter 4. How MySQL Extends and Deviates from SQL

IN THIS CHAPTER

• Learning MySQL language Structure

• Understanding MySQL deviations

• Using MySQL extensions

MySQL was originally designed with three basic ideas in mind: to be fast, reliable, and easy to use. Like every database system, MySQL does not completely follow the SQL standard and has its own extensions to SQL. Some very basic SQL standards, however, did not exist for a very long time—the data dictionary was only added in MySQL 5.0, released in October 2005.

MySQL became popular because of its relative ease of use. For example, the SQL standard way of finding out which tables are in the sakila database is:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='sakila';

The easiest way to find out this information in MySQL is:

SHOW TABLES FROM sakila;

Most of the differences from standard SQL and SQL extensions in MySQL come from the desire to make the database system easy to use. In the past several years, there has been more of a commitment to support the current SQL standard—as of the time of the writing of this book, that standard is the ANSI/ISO SQL:2003 standard. When we refer to "the SQL standard" or "standard SQL," we are referring to ANSI/ISO SQL:2003.

For information on the different SQL modes supported, see Chapter 5.

Using the Sample Database

In Chapter 3, you imported the sakila sample database. Throughout this chapter and the rest of the book, we will be using the sakila sample database to demonstrate database ...