Chapter 3. MySQL SQL Syntax and Use

Fluency with SQL is necessary for effective communication with the MySQL server, because that is the language that it understands. For example, when you use a program such as the mysql client, it functions primarily as a means for you to send SQL statements to the server to be executed. You must also know SQL if you write programs that use the MySQL interface provided by your programming language because the interface functions as the means that allows you to communicate with the server by sending SQL statements to it.

Chapter 1, "Getting Started with MySQL and SQL," presented a tutorial introduction to many of MySQL's capabilities. This chapter builds on that material to go into more detail on several areas of SQL implemented by MySQL. It discusses how to refer to elements of databases, including the rules for naming and the case sensitivity constraints that apply. It also describes many of the more important SQL statements that are used for the following types of operations:

• Creating and destroying databases, tables, and indexes

• Obtaining information about your databases and tables

• Retrieving data using joins, subselects, and unions

• Using multiple-table deletes and updates

• Performing transactions that allow multiple statements to be treated as a unit

• Setting up foreign key relationships

• Using the FULLTEXT search engine

MySQL's SQL statements can be grouped into several broad categories; Table 3.1 lists some representative statements for each. In some cases, a utility program is available that provides a command-line interface to a statement. For example, mysqlshow allows SHOW operations to be performed from the command line. This chapter points out such equivalences where appropriate.

Some of the statements in the table are not covered here because they are more appropriately discussed in other chapters. For example, the administrative statements GRANT and REVOKE for setting up user privileges are dealt with in Chapter 11, "General MySQL Administration." Chapter 12, "Security," provides further details on what privileges are available and what they allow. The syntax for all SQL statements implemented by MySQL is listed in Appendix D, "SQL Syntax Reference." In addition, you should consult the MySQL Reference Manual for additional information, especially for changes made in recent versions of MySQL.

SELECTING, CREATING, DROPPING, AND ALTERING DATABASES

USE

CREATE DATABASE

DROP DATABASE

ALTER DATABASE

CREATING, ALTERING, AND DROPPING TABLES AND INDEXES

CREATE TABLE

DROP TABLE

CREATE INDEX

DROP INDEX

ALTER TABLE

GETTING INFORMATION ABOUT DATABASES AND TABLES

DESCRIBE

SHOW

RETRIEVING INFORMATION FROM TABLES

SELECT

UNION

PERFORMING TRANSACTIONS

BEGIN

COMMIT

ROLLBACK

SET AUTOCOMMIT

MODIFYING INFORMATION IN TABLES

DELETE

INSERT

LOAD DATA

REPLACE

UPDATE

ADMINISTRATIVE STATEMENTS

FLUSH

GRANT

REVOKE

The final section of the chapter describes what MySQL does not include that is, what features it lacks. These are capabilities found in some other databases but not in MySQL. Such features include triggers, stored procedures, and views. Do these omissions mean that MySQL isn't a "real" database system? Some people think so, but in response I'll simply observe that the lack of these capabilities in MySQL hasn't stopped large numbers of people from using it. That's probably because for many or most applications, those features don't matter.

I should also point out that the set of features missing from MySQL continues to shrink over time. For the first edition of this book, the list of missing features included transactions, subselects, foreign keys, and referential integrity. A significant amount of progress has been made in improving MySQL since then, and those capabilities all have been added now. Triggers, stored procedures, and views are scheduled for implementation in the future.