Web Database Applications with PHP, and MySQL

Managing Databases, Tables, and Indexes

The Data Definition Language (DDL) is the set of SQL statements used to manage a database. In this section, we use the MySQL command interpreter to create databases and tables using the online winestore as a case study. We also discuss the statements that delete, alter, and drop databases and tables, as well as statements for managing indexes.

Creating Databases

The CREATE DATABASE statement can create a new, empty database without any tables or data. The following statement creates a database called winestore:

mysql> CREATE DATABASE winestore;

To work with a database, the command interpreter requires the user to be using a database before SQL statements can be issued. Different command interpreters have different methods for using a database and these aren’t part of the SQL standard. In the MySQL interpreter, you can issue the command:

mysql> use winestore

For the rest of this chapter, we omit the mysql> prompt from the command examples.

Creating Tables

After issuing the use winestore command, you then usually issue commands to create the tables in the database, as shown in Example 3-1. (You already created the tables in the winestore database in Section 3.2 of this chapter). Let’s look at one of these tables, the customer table. The statement that created this table is shown in Example 3-2.

Example 3-2. Creating the customer table with SQL

CREATE TABLE customer ( cust_id int(5) DEFAULT '0' NOT NULL auto_increment, surname varchar(50) NOT ...