Database Reference
In-Depth Information
Chapter 4. Creating Databases and Tables
In order to be able to add andmanipulate data, you first have to create a database. There's
not much to this. You're creating just a container in which you will add tables.Creating a
table is more involved and offers many choices. There are several types of tables from
which to choose, some with unique features. When creating tables, you must also decide on
the structure of each table: the number of columns, the type of data each column may hold,
how the tables will be indexed, and several other factors. However, while you're still learn-
ing, you can accept the default setting for most of the options when creating tables.
There are a few basic things to decide when creating a structure for your data:
▪ The number of tables to include in your database, as well as the table names
▪ For each table, the number of columns it should contain, as well as the column
names
▪ For each column, what kind of data is to be stored
For the last part, in the beginning, we'll use just four types of columns: columns that con-
tain only numbers; columns that contain alphanumeric characters, but not too many (i.e., a
maximum of 255 characters); columns that contain plenty of text and maybe binary files;
and columns for recording date and time information. This is a good starting point for cre-
ating a database and tables. As we get further along, we can expand that list of column data
types to improve the performance of your databases.
This chapter contains examples of how to create a database and tables. The text is written
on the assumption that you will enter the SQL statements shown on your server, using the
mysql
client. The exercises at the end of this chapter will require that you make some
changes and additions to the database and its tables on your computer. So, when instructed,
be sure to try all of the examples on your computer.
The database and the tables that we create in this chapter will be used in several chapters in
this topic, especially in
PartIII
. In those later chapters, you will be asked to add, retrieve,
and change data from the tables you create in this chapter. Exercises in subsequent chapters
assume that you have created the tables you are asked to create in this chapter. Thus, in or-
der to get the most value possible from this topic, it's important that you complete the exer-
cises included for each chapter. It will help reinforce what you read, and you will learn
more.
