Section 3.2. Creating a Database and Tables

3.2. Creating a Database and Tables

Assuming that you have all of the privileges necessary to create and
modify databases on your server, let's look at how to create a
database and then tables within a database. For the examples in this
chapter, we will build a database for a fictitious bookstore:

CREATE DATABASE bookstore;

In this brief SQL statement, we have created a database called
bookstore. You may have noticed that the commands or
reserved words are printed here in uppercase letters. This isn't
necessary; MySQL is case-insensitive with regard to reserved words for SQL
statements and clauses. Database and table names are case-sensitive on
operating systems that are case-sensitive, such as Linux systems, but not
on systems that are case-insensitive, such as Windows. As a general
convention, though, reserved words in SQL documentation are presented in
uppercase letters and database names, table names, and column names in
lowercase letters. You may have also noticed that the SQL statement shown
ends with a semicolon. An SQL statement may be entered over more than one
line, and it's not until the semicolon is entered that the client sends
the statement to the server to read and process it. To cancel an SQL
statement once it's started, enter \c instead of a
semicolon.

With our database created, albeit an empty one, we can switch the
default database for the session to the new database like this:

USE bookstore

This saves us from having to specify the database name in every SQL
statement. MySQL by default will assume the current database, the one we
last told it to use. No semicolon is given with the USE
statement because it's a client-based SQL statement.

Next, we will create our first table, in which we will later add
data. We'll start by creating a table that we'll use to enter basic
information about books, because that's at the core of a bookstore's
business:

CREATE TABLE books (
book_id INT, 
title VARCHAR(50),
author VARCHAR(50));

This SQL statement creates the table books with three
columns. Note that the entire list of columns is contained within
parentheses.

The first column is a simple identification number for each record,
which represents one book. You can specify the data type either as INTEGER or as
INT like the example. The second and third columns
consist of character fields of variable width, up to 50 characters
each.

To see the results of the table we just created, enter a DESCRIBE statement, which displays a
table as output:

DESCRIBE books;
   
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| book_id | int(11)     | YES  |     | NULL    |       |
| title   | varchar(50) | YES  |     | NULL    |       |
| author  | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Considering our bookstore a bit more, we realize that we need to add
a few more columns for data elements: publisher, publication year, ISBN
number, genre (e.g., novel, poetry, drama), description of book, etc. We
also realize that we want MySQL to automatically assign a number to the
book_id column so that we don't have to bother creating
one for each row or worry about duplicates. Additionally, we've decided to
change the author column from the actual author's name
to an identification number that we'll join to a separate table containing
a list of authors. This will reduce typing, and will make sorting and
searching easier, as the data will be uniform. To make these alterations
to the table that we've already created, enter the following SQL
statement:

ALTER TABLE books
CHANGE COLUMN book_id book_id INT AUTO_INCREMENT PRIMARY KEY,
CHANGE COLUMN author author_id INT,
ADD COLUMN description TEXT,
ADD COLUMN genre ENUM('novel','poetry','drama'), 
ADD COLUMN publisher_id INT,
ADD COLUMN pub_year VARCHAR(4),
ADD COLUMN isbn VARCHAR(20);

After the opening line of this SQL statement, notice that each
clause in which we change or add a column is separated from the following
one by a comma. On the second line here, we're changing the
book_id column. Even though we are keeping the column
name and the data type the same, we have to restate them. We're adding
the AUTO_INCREMENT flag, which carries
out the task mentioned in the previous paragraph, assigning a unique and
arbitrary value to each book in the table. We're also making the
column the PRIMARY KEY for indexing,
which allows faster data retrieval.

The first CHANGE clause may look confusing
because it lists the column name (book_id) twice. This
makes sense when you understand the syntax of a CHANGE
clause: the first book_id names the existing column you
want to change, and the rest of the clause specifies the entire new
column. To understand this better, examine the second
CHANGE clause: it replaces the existing
author column with a new author_id
column. There will no longer be a column named
author.

In the third line, we're changing the author
column so that its label and data type align with the
authors table that we'll create later. The
authors table will have an indexed column to represent
the author, just as the books table has an indexed
column to represent the books. To figure out which author the
author_id column in the books table
is pointing to, we'll join the books table to the
authors table in queries. Because the corresponding
column in the authors table will have a data type of
INT, so must this one.

The fourth line adds a column for each book's description. This has
a data type of TEXT, which is a
variable-length data type that can hold very large amounts of data, up to
64 kilobytes. There are other factors, though, that can limit a
TEXT column further. See Appendix A for a list of data types, their limits, and
other limiting factors.

For genre, we're enumerating a list of possible
values to ensure uniformity. A blank value and a NULL value are also
possible, although they're not specified.

Before moving on to adding data to our books
table, let's quickly set up the authors table. This
table will be what is known as a reference table. We
need to enter data into the authors table, because when
we enter data into the books table, we will need to
know the identification number for the authors of the books:

CREATE TABLE authors
(author_id INT AUTO_INCREMENT PRIMARY KEY,
author_last VARCHAR(50),
author_first VARCHAR(50),
country VARCHAR(50));

This table doesn't require too many columns, although we might add
other columns to it for an actual bookstore. As mentioned before, as
needed, we'll join the books table to the
authors table through the author_id
in both tables.

In the authors table, we've separated the first
and last name of each author into two columns so that we can easily sort
and search on the last name. We've also added a column for the author's
country of origin so that we can search for works by authors of a
particular country when asked by customers. For production use, it might
be better to use a country code and then have yet another reference table
listing the full names of countries. But we're trying to keep this
tutorial simple and include detail only when it has educational
value.