Team LiB
Previous Section Next Section

Chapter 2. MySQL SQL Syntax and Use

Structured Query Language (SQL) is the language that the MySQL server understands, so fluency with SQL is necessary for effective communication with the server. When you use a program such as the mysql client, it functions primarily as a way 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 issuing SQL statements.

Chapter 1, "Getting Started with MySQL and SQL," presents a tutorial introduction to many of MySQL's capabilities. Now we'll build on that material to go into more detail on several areas of SQL implemented by MySQL:

  • Naming rules for referring to elements of databases

  • Changing the server SQL mode to affect its behavior

  • Support for multiple character sets

  • Creating and destroying databases, tables, and indexes

  • Obtaining information about your databases and tables

  • Retrieving data using joins, subqueries, 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

  • New features in MySQL 5.0: Views, stored procedures and functions, and triggers

Several aspects of how the MySQL server executes SQL statements can be modified by setting its SQL mode. Instructions for doing this are given in "The Server SQL Mode."

MySQL's SQL statements may be grouped into several broad categories; Table 2.1 lists representative statements for each. 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, "MySQL and 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 E, "SQL Syntax Reference." That appendix also covers the syntax for using comments in your SQL statements. In addition, you should consult the MySQL Reference Manual for additional information, especially for changes made in the most recent versions of MySQL.

Table 2.1. Types of SQL Statements Supported by 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

 

SET AUTOCOMMIT

 

START TRANSACTION

 

COMMIT

 

ROLLBACK

MODIFYING INFORMATION IN TABLES

 

DELETE

 

INSERT

 

LOAD DATA

 

REPLACE

 

UPDATE

ADMINISTRATIVE STATEMENTS

 

FLUSH

 

GRANT

 

REVOKE


    Team LiB
    Previous Section Next Section