Table of Contents for

MySQL in a Nutshell, 2nd Edition

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.