Chapter 4. Creating Databases, Tables, and Indexes

In this chapter you'll learn how to create all the basic MySQL structures: databases, tables, and indexes. We'll cover the following:

• Creating a database

• Selecting a database

• Creating tables

• Column and data types in MySQL

• Creating indexes

• Deleting tables, indexes, and databases

• Altering existing table structures

We will use a simple sample database for the examples in this chapter ”this is the employee database we talked about in the preceding chapter.This is the database schema:

employee( employeeID , name , job, )

department( departmentID , name)

employeeSkills( , skill )

client( clientID , name, address, contactPerson, contactNumber)

assignment( , , workdate, hours)

You can follow along in this chapter by entering the commands to create this database into MySQL. You can, of course, create databases, tables, and indexes easily from any of the MySQL IDEs, but in these examples, we will use the MySQL monitor to create the database. We will do it this way here to give you a greater understanding of database, table, and index structures.

You should begin by logging in to the MySQL monitor as you did in Chapter 1, "Installing MySQL," and Chapter 2, "Quick Tour."

In this chapter we will use the data definition language aspects of SQL to create databases, tables, and indexes. What does this mean? SQL stands for Structured Query Language, which is the language we use for creating and querying relational databases. It consists of two semantically separate parts : a Data Definition Language (or DDL) for creating database structures and a Data Manipulation Language (or DML) for querying the database.

Let's go ahead and look at how to create databases, tables, and indexes with MySQL.