Learning MySQL, 2nd Edition

Chapter 4. Working with Database Structures

This chapter shows you how to create your own databases, add and remove structures such as tables and indexes, and make choices about column types in your tables. It focuses on the syntax and features of SQL, and not the semantics of conceiving, specifying, and refining a database design; you’ll find an introductory description of database design techniques in Chapter 2. To work through this chapter, you need to understand how to work with an existing database and its tables, as discussed in Chapter 3.

This chapter lists the structures in the sample sakila database. If you followed the instructions for loading the database in “Entity Relationship Modeling Examples”, you’ll already have the database available and know how to restore it after you’ve modified its structures.

When you finish this chapter, you’ll have all the basics required to create, modify, and delete database structures. Together with the techniques you learned in Chapter 3, you’ll have the skills to carry out a wide range of basic operations. Chapters 5 and 7 cover skills that allow you to do more advanced operations with MySQL.

Creating and Using Databases

When you’ve finished designing a database, the first practical step to take with MySQL is to create it. You do this with the CREATE DATABASE statement. Suppose you want to create a database with the name lucy. Here’s the statement you’d type:

mysql> CREATE DATABASE lucy;

Query OK, 1 row affected (0.10 sec)

We assume ...