O'Reilly logo

Web Database Applications with PHP, and MySQL by David Lane, Hugh E. Williams

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required