MySQL in a Nutshell [15]
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