Chapter 4. Data Definition Language

This chapter covers the following exam topics:

• General database and table properties

• Storage engines and table types

• Limits on number and size of database components

• Identifier syntax

• The CREATE DATABASE and DROP DATABASE statements

• The CREATE TABLE, ALTER TABLE, and DROP TABLE statements

• The CREATE INDEX and DROP INDEX statements; specifying indexes at table-creation time

• Creating and using primary keys

• Column types

• Using AUTO_INCREMENT

• String and number formats

• Using SHOW and DESCRIBE to review table structures

Questions on the material in this chapter make up approximately 20% of the exam.

Several of MySQL's SQL statements comprise the Data Definition Language (DDL) that is used to define the structural characteristics of your databases. The following statements create or remove databases and tables or modify the structure of tables:

• CREATE DATABASE creates a new database.

• DROP DATABASE removes a database and any tables it contains.

• CREATE TABLE creates a new table.

• DROP TABLE removes a table and any data it contains.

• ALTER TABLE modifies the structure of an existing table.

• CREATE INDEX adds an index to a table.

• DROP INDEX removes an index from a table.

Several of the table-related DDL statements require you to provide column definitions. MySQL allows several different types of data to be stored, and it's important to understand what column datatypes are available so that you can define your tables appropriately for the information they'll contain.

This chapter provides a general overview of how MySQL manages databases and tables and a discussion of the syntax of legal names that can be used to refer to them. It also describes how to use each of the DDL statements and discusses the available column datatypes, their properties, how to use them, and the syntax for writing column definitions.

Related to the DDL statements, MySQL supports several statements that are helpful for checking what databases or tables exist and for getting information about the internal column and index structure of tables. These statements include SHOW and DESCRIBE; they are discussed at the end of this chapter.