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 regards to reserved words for SQL statements and clauses. Database and table names are case-sensitive on operating systems that are case-sensitive, such as Unix 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 this SQL statement 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, we can switch the default database for the session to the new database like so:

USE bookstore;

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

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

This SQL statement creates the table books with three columns. The first column is a simple identification number for each record. It has an integer type. Incidentally, fields are referred to as columns and records as rows in MySQL. The data-type for the second and third columns consists of character fields of variable widths up to 50 characters each. Notice that the list of columns is contained within parentheses.

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 | +--------+-------------+------+-----+---------+-------+ | rec_id | int(11)     | YES  |     | NULL    |       | | title  | varchar(50) | YES  |     | NULL    |       | | author | varchar(50) | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+

Looking at the resulting table, we realize that we need to add a few more columns for data elements: publisher, publication year, ISBN number, genre (i.e., novel, poetry, drama), description of book, etc. We also realize that we want MySQL to automatically assign a number to the rec_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 rec_id rec_id INT AUTO_INCREMENT PRIMARY KEY, CHANGE COLUMN author author_id INT, ADD COLUMN description BLOB, 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 rec_id column. Even though we are keeping the column name and the datatype 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.

In the third line, we're also 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 which we will join in queries. Because that column will have a datatype of integer, so must this one.

The fourth line adds a column for each book's description. This has a datatype of BLOB, which stands for "binary large object." A BLOB is a variable-length datatype that can hold very large amounts of data, up to 64 kilobytes. There are other datatypes that will hold even more data. See Appendix A for a list of datatypes and their limits.

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 in the command.

Before moving on to adding data to our books table, let's quickly set up the authors table. The authors table will be what is known as a reference table. We need to set it up first, 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 (rec_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, we'll join the books table to the authors table through the author_id in the books table and the rec_id in this table. My style is to name the key column (rec_id) of each table the same so that when I'm entering queries, I can easily remember their names without having to enter a DESCRIBE statement for each table.

In this table, we've separated the first and last name of the author into two columns so that we can easily sort on the last name, and search on it. 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.



MySQL in a Nutshell
MYSQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596514336
EAN: 2147483647
Year: 2006
Pages: 134
Authors: Russell Dyer

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net