B.3. SQL Data Definition Language

The Data Definition Language (DDL) part of SQL permits database objects to be created or destroyed. You can also define indexes (keys), specify links between tables, and impose constraints between database tables. Often, decisions to create and remove databases are handled by a database administrator, and having permission to create and drop tables depends on what access rights are granted.

The most important data definition statements in SQL are:

• CREATE TABLE—Creates a new database table.

• ALTER TABLE—Alters (changes) a database table.

• DROP TABLE—Deletes a database table.

• CREATE INDEX—Creates an index (search key).

• DROP INDEX—Deletes an index.

B.3.1. Creating the Database

Creating the database is very simple. All you have to do is issue one command, and the only parameter is the database name.

Format CREATE DATABASE database_name

In the earlier examples, we used the northwind database. Because we will be working on a complete Web application for an art gallery in Appendix A, now we will create the database for that application.

Example B.24.

1 mysql> CREATE DATABASE gallerydb; Query OK, 1 row affected (0.03 sec) 2 mysql> show databases; +------------+ | Database | +------------+ | gallerydb | | mysql | | northwind | | phpmyadmin | | test | +------------+ 5 rows in set (0.00 sec) Explanation That's it. The database is now created. Note that just because we created the database, we are still not in that database. The USE command in the next example will make the new database the current default database.

Example B.25.

1 mysql> USE gallerydb; Database changed Explanation We are now in the gallerydb database, and all the SQL commands will be executed on that database.

B.3.2. SQL Data Types

After creating a database, you will add the tables that make up the database. Before creating a table, you have to decide what kind of data will be stored in it; for example, will you have rows of names, dates, part numbers, Social Security numbers, prices, and so on? The data type specifies what type of data the column can hold. The basic types are string, numeric, and date and time types. For a fully documented list, see http://dev.mysql.com/doc/refman/5.0/en/data-types.html.

Table B.3 contains the most common data types in SQL.

Table B.3. Most Common SQL Data Types

Data Type	Description
Numbers
INTEGER	Holds a 4-byte whole number.
INT UNSIGNED	Holds a 4-byte non-negative whole number.
SMALLINT	Holds a 2-byte whole number.
TINYINT	Holds a 1-byte whole number.
FLOAT(m,d)	A 4-byte fractional number. FLOAT(7,4) for value 999.00009 results in 999.0001. The maximum number of digits is specified in m. The maximum number of digits to the right of the decimal is specified in d.
DOUBLE(m,d)	An 8-byte fractional double-precision number.
DECIMAL(m,d)	A real or fractional 8-byte number. The maximum number of digits is specified in m. The maximum number of digits to the right of the decimal is specified in d.
NUMERIC(m,d)	The DECIMAL and NUMERIC data types are used to store exact numeric data values with exact precision; e.g., monetary data. Hold numbers with fractions.
Strings
CHAR(SIZE)	Holds a fixed-length string (can contain letters, numbers, and special characters) from 0 to 255 characters long. The fixed size is specified in the parentheses.
VARCHAR(SIZE)	A variable-length string (can contain letters, numbers, and special characters) from 0 to 65,535 in MySQL 5.0.3 and later versions. The maximum size is specified in the parentheses.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A variable-length text string with a maximum length of 65,535 characters, used for storing large text files, documents, text areas, etc.
BLOB	Binary large object. A binary string with a maximum length of 65,535 characters, used for storing binary files, images, sounds, etc.
Date and Time
DATE	(yyyy-mm-dd) year, month, day; e.g., 2006-10-30 (Note: MySQL also allows you to store 0000-00-00 as a "dummy date.")
DATETIME	(yyyy-mm-dd hh:mm:ss) date and time; e.g., 2006-10-30 22:59:59
TIMESTAMP	(yyyy-mm-dd hh:mm:ss) date and time; e.g., 1970-01-01 (date and time of last transaction on a row)
TIME	(hh:mm:ss) time; e.g., 10:30:58
YEAR	(yyyy | yy) year in four or two digits; e.g., 1978 or 78

B.3.3. Creating a Table

Creating a table is a little more complicated than creating the database. The CREATE TABLE statement is used to create a new table in the database. First, you must name the new table and then specify all the fields that will be included in the table as well as the data types and any other attributes. A data type can be an integer, a floating point (real) number such as 5.4, a string of characters, a date, a time, and so on. Not all databases will specify data types in the same way. To see what data types and attributes are available for MySQL, see Table B.3 or the MySQL documentation.

Designing your tables correctly is important and a subject that merits further research if you have not worked with databases before. See http://databases.about.com/od/specificproducts/a/normalization.htm for an excellent beginner's tutorial on database design. For now, here are some rules to keep in mind when designing the table.

Format CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type <-- no comma on the last entry ) Example B.26. Code View: 1 mysql> CREATE DATABASE pets; Query OK, 1 row affected (0.24 sec) 2 mysql> USE pets; 3 mysql> CREATE TABLE dog -> ( name varchar(20), -> owner varchar(20), -> breed varchar(20), -> sex char(1), -> birth date, -> death date -> ); Query OK, 0 rows affected (0.16 sec) 4 mysql> describe dog; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | breed | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) Explanation 1. A database called pets is created. 2. The pets database is selected and entered. 3. A table called dogs is created with fields and their data types. The name, owner, and breed will consist of a varying number of up to 20 characters. The sex is one character, either f or m for female or male. The birth and death columns are assigned date type. 4. The DESCRIBE command is like the SHOW command. It displays the layout of the new table.

Now we can insert some data into the new table.

Example B.27.

mysql> INSERT INTO dog(name,owner,breed, sex, birth, death) -> VALUES('Fido','Mr. Jones', 'Mutt', 'M', '2004-11-12', '2006-04-02'); Query OK, 1 row affected (0.09 sec)

B.3.4. Creating a Key

In real life, people can be identified by Social Security numbers, driver's license numbers, and employee numbers; books can be identified by ISBN numbers; and a Web store order can be identified by a purchase order number. These identification numbers must be unique so that no two people have the same Social Security number, no two books have the same ISBN number, and so on. Keys are used to uniquely identify a record in a table. There are two types of keys: primary keys and foreign keys.

Primary Keys

Each table typically has a primary key. Primary keys are used to uniquely identify a record in the database. They must be unique, never change, occur only once per table, and are normally numeric types.

You can choose to manually generate this unique number for each record or let the database do it for you. If you let the database generate the primary key, it will generate a unique number, given a starting value (e.g., 1) and then for each new record, increment that number by 1. Even if a record is deleted, that number is never recycled. The database increments its internal counter, guaranteeing that each record will be given a unique "key."

To set a field as a primay key, use the attribute PRIMARY KEY (field_name), and to tell the database to automatically create the unique number, use the AUTO_INCREMENT attribute following the field definition. The primary key cannot be null.

The following two examples describe a table called categories where the primary key is called CategoryID. It will automatically be incremented each time a new category is added to the table.

Example B.28.

mysql> USE northwind; Database changed mysql> DESCRIBE categories; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | CategoryID | int(11) | | PRI | NULL | auto_increment | | CategoryName | varchar(15) | | MUL | | | | Description | longtext | YES | | NULL | | | Picture | longblob | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.09 sec) Explanation The CategoryID is the primary key, an integer of up to 11 digits, which will be incremented by 1, initially set to NULL (no value). The first time a record is inserted into the database, the value will be 1.

Example B.29.

mysql> SELECT CategoryID, CategoryName FROM categories; +------------+----------------+ | CategoryID | CategoryName | +------------+----------------+ | 1 | Beverages | | 2 | Condiments | | 3 | Confections | | 4 | Dairy Products | | 5 | Grains/Cereals | | 6 | Meat/Poultry | | 7 | Produce | | 8 | Seafood | +------------+----------------+ 8 rows in set (0.16 sec) Explanation The primary key is called CategoryID. It is used to uniquely identify the different categories in this table from the northwind database. When a new category is added to the table, the CategoryID will be automatically incremented by 1.

Foreign Keys

If a primary key is referenced in another table, it is called a foreign key. Foreign keys are used to create relation between tables. In the following example, two tables are described that both reference the CategoryID key, although it is primary in one and foreign in the other.

Example B.30.

Code View: mysql> DESCRIBE categories; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ 1 | CategoryID | int(11) | | PRI | NULL | auto_increment | | CategoryName | varchar(15) | | MUL | | | | Description | longtext | YES | | NULL | | | Picture | longblob | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> DESCRIBE products; +----------------+--------------+-----+-----+--------+---------------+ | Field | Type | Null| Key | Default| Extra | +----------------+--------------+-----+-----+--------+---------------- | ProductID | int(11) | | PRI | NULL | auto_increment| | ProductName | varchar(40) | | MUL | | | | SupplierID | int(11) | YES | MUL | NULL | | 2 | CategoryID | int(11) | YES | MUL | NULL | | | QuantityPerUnit| varchar(20) | YES | | NULL | | | UnitPrice | decimal(19,4)| YES | | NULL | | | UnitsInStock | smallint(6) | YES | | NULL | | | UnitsOnOrder | smallint(6) | YES | | NULL | | | ReorderLevel | smallint(6) | YES | | NULL | | | Discontinued | tinyint(4) | | | 0 | | +----------------+--------------+-----+-----+--------+---------------+ 10 rows in set (0.00 sec) Explanation The categories table has a primary key field called CategoryID. The products table has its own primary key (ProductID) in addition to a foreign key called CategoryID. If a primary key is referenced in another table, it is called a foreign key.

B.3.5. Relations

A major advantage of the relational database system is the ability to create relations between tables. Simply put, a relation is a connection between a field of one table and a field of another. This relation allows you to look up related records in the database.

The operation of matching rows from one table to another using one or more column values is called a join. There are several types of join statements, such as full joins, cross joins, left joins, and so on, but let's start with a simple joining of two tables, called an inner join.

Tables can be related to each other with keys. As we discussed earlier, a primary key is a column with a unique value for each row. A matching key in a second table is called a foreign key. With these keys, you can bind data together across tables without repeating all of the data in every table where a certain condition is met.

Consider the previous Example B.30, in which two tables from the northwind database are described. One table is called categories and the other called products. CategoryID is a primary key field in the categories table, and it is a foreign key in the products table. The CategoryID key is used to create a relationship between the two tables.

Two Tables with a Common Key

As discussed previously, both the categories table and the products table have a CategoryID key with the same values, making it possible to create a relation between the two tables.

Let's create a relation in which all the product names are listed if they are in the Seafood category. Because every product in the products table falls into one of the eight categories in the categories table, the two tables can be bound by their common CategoryID.

Example B.31.

Code View: mysql> SELECT CategoryID, CategoryName FROM categories; +------------+----------------+ | categoryID | categoryName | +------------+----------------+ | 1 | Beverages | | 2 | Condiments | | 3 | Confections | | 4 | Dairy Products | | 5 | Grains/Cereals | | 6 | Meat/Poultry | | 7 | Produce | | 8 | Seafood | +------------+----------------+ 8 rows in set (0.00 sec) mysql> SELECT CategoryID, ProductName FROM products; (Partial Output) +------------+----------------------------------+ | CategoryID | ProductName | +------------+----------------------------------+ | 1 | Chai | | 1 | Chang | | 2 | Aniseed Syrup | | 2 | Chef Anton's Cajun Seasoning | | 2 | Chef Anton's Gumbo Mix | | 2 | Grandma's Boysenberry Spread | | 7 | Uncle Bob's Organic Dried Pears | | 2 | Northwoods Cranberry Sauce | | 6 | Mishi Kobe Niku | | 8 | Ikura | | 4 | Queso Cabrales | | 4 | Queso Manchego La Pastora | | 8 | Konbu | | 7 | Tofu | | 2 | Genen Shouyu | Explanation This example displays columns from both the categories table and the products table. In the categories table, the CategoryID is the primary field and uniquely identifies all other fields in the table. In the products table, the CategoryID is a foreign key and is repeated many times for all the products.

Using a Fully Qualified Name and a Dot to Join the Tables

When querying more than one table, a dot is used to fully qualify the columns by their table name to avoid potential ambiguity if two tables have a field with the same name, as shown in Example B.32.

Example B.32.

mysql> SELECT CategoryName, ProductName FROM categories, products -> WHERE products.CategoryID = 8 AND categories.CategoryID = 8; +--------------+---------------------------------+ | CategoryName | ProductName | +--------------+---------------------------------+ | Seafood | Ikura | | Seafood | Konbu | | Seafood | Carnarvon Tigers | | Seafood | Nord-Ost Matjeshering | | Seafood | Inlagd Sill | | Seafood | Gravad lax | | Seafood | Boston Crab Meat | | Seafood | Jack's New England Clam Chowder | | Seafood | Rogede sild | | Seafood | Spegesild | | Seafood | Escargots de Bourgogne | | Seafood | Röd Kaviar | +--------------+---------------------------------+ 12 rows in set (0.00 sec) Explanation In the SELECT, two tables (separated by commas) will be joined by the CategoryID field. Because the field name is the same in both tables, the table name is prepended to the field name with a dot, as products.CategoryId and categories.CategoryId. In the WHERE clause, the two tables are connected if both tables have a CategoryID equal to 8.

Aliases

To make things a little easier by typing less with complicated queries, SQL provides an aliasing mechanism that allows you to use symbolic names for columns and tables. The alias is defined with the AS keyword and consists of a single character or an abbreviated string. When the alias is used in the WHERE clause to represent a table name, it is appended with a dot and the name of the field being selected from that table.

Format (Column Alias) SELECT column_name AS column_alias_name FROM table_name (Table Alias) SELECT column_name FROM table_name AS table_alias_name Example B.33. mysql> SELECT CategoryName as Foods FROM categories; +----------------+ | Foods | +----------------+ | Beverages | | Condiments | | Confections | | Dairy Products | | Grains/Cereals | | Meat/Poultry | | Produce | | Seafood | +----------------+ 8 rows in set (0.00 sec) Explanation The column name from table categories was named CategoryName. An alias called Foods is created by using the AS keyword after CategoryName. Now when the SELECT returns a result-set, the output will show Foods as the name of the column.

Example B.34.

mysql> SELECT ProductName FROM products AS p, categories AS c WHERE -> p.CategoryID = c.CategoryID AND c.CategoryName="SeaFood"; +---------------------------------+ | ProductName | +---------------------------------+ | Ikura | | Konbu | | Carnarvon Tigers | | Nord-Ost Matjeshering | | Inlagd Sill | | Gravad lax | | Boston Crab Meat | | Jack's New England Clam Chowder | | Rogede sild | | Spegesild | | Escargots de Bourgogne | | Röd Kaviar | +---------------------------------+ 12 rows in set (0.00 sec) Explanation This example might look a little tricky at first. The table named products is given an alias called p, and the table name categories is given the alias c. These aliases are short names, making it easier to type the query when more than one table is involved; for example, instead of typing products.CategoryID, we can type p.CategoryID, and categories.CategoryName can be referenced as c.CategoryName.

B.3.6. Altering a Table

When you alter a table, you redefine its structure by adding or dropping new columns, keys, indexes, and tables. You can also use the ALTER command to change column names, types, and the table name.

Format ALTER TABLE tablename ADD column datatype Example: alter table artist add column ArtDate date; alter table artist drop column "Address"; Example B.35. Code View: use pets; 1 mysql> ALTER TABLE dog ADD pet_id int(11); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 2 mysql> ALTER TABLE dog MODIFY column pet_id int(11) --> auto_increment primary key; Query OK, 1 row affected (0.11 sec) Records: 1 Duplicates: 0 Warnings: 0 3 mysql> describe dog; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | breed | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | | pet_id | int(11) | | PRI | NULL | auto_increment | +--------+-------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> select * from dog; +-------+-------------+-------+-----+-----------+-----------+--------+ | name | owner | breed | sex | birth | death | pet_id | +-------+-------------+-------+-----+------------+-----------+-------- | Fido | Mr. Jones | Mutt | M | 2004-11-12| 2006-04-02| 1 | | Lassie| Tommy Rettig| Collie| F | 2006-01-10| NULL | 2 | +-------+-------------+-------+-----+-----------+-----------+--------+ 2 rows in set (0.00 sec) Explanation The ALTER command will change the table by adding a new field, called pet_id, an integer of 11 digits. Once the pet_id field has been created, the ALTER command is used again to make this a primary key that will automatically be incremented each time a record is added. The DESCRIBE command shows the structure of the table after it was changed. A primary key has been added.

B.3.7. Dropping a Table

To drop a table is relatively simple. Just use the drop command and the name of the table:

mysql> drop table dog;
Query OK, 20 rows affected (0.11 sec)

B.3.8. Dropping a Database

To drop a database, use the drop database command:

mysql> drop database pets;
Query OK, 1 row affected (0.45 sec)