5.3 Managing Databases and Tables

In this section, we use the MySQL command interpreter to create databases and tables using the winestore database as a case study. We also show you the statements that remove databases and tables.

A discussion of advanced features is in Chapter 15. We show you how to manage indexes and alter tables after they've been created, and delete and update data using queries and multiple tables. We also show you how the details of how to store multiple statements in a file and execute them; this is how we created our winestore script that you used in the installation steps in Appendix A through Appendix C.

5.3.1 Creating Databases

The CREATE DATABASE statement creates a new, empty database without any tables or data. The following statement creates a database called winestore:

mysql> CREATE DATABASE winestore;

A database name can be 64 characters in length at most and can contain any character except the forward slash, backward slash, or period characters.

Database and table names are used as the disk file names that store the data. Therefore, if your operating system has case-sensitive filenames, MySQL is case-sensitive to database and table names; in general, Unix platforms are case sensitive and Microsoft Windows platforms aren't. Attribute names are not case sensitive on all platforms. Aliases (which are discussed in Chapter 15) are partially case sensitive: table aliases follow the same rule as table names (and so are case sensitive on some platforms), while attribute aliases are case insensitive.

For the rest of this chapter, we omit the mysql> prompt from the command examples. To work with a database, the command interpreter requires the user to be using a database before SQL statements can be issued. Database servers have different methods for using a database and these aren't part of the SQL standard. In the MySQL interpreter, you issue the command:

use winestore;

5.3.2 Creating Tables

After issuing the use winestore command, you then usually enter statements to create the tables in the database. Let's look one table from the winestore database, the customer table. The statement that creates this table is shown in Example 5-1.

Example 5-1. Creating the customer table with SQL

CREATE TABLE customer (

  cust_id int(5) NOT NULL,

  surname varchar(50),

  firstname varchar(50),

  initial char(1),

  title_id int(3),

  address varchar(50),

  city varchar(50),

  state varchar(20),

  zipcode varchar(10),

  country_id int(4),

  phone varchar(15),

  birth_date char(10),

  PRIMARY KEY (cust_id)

) type=MyISAM;

The CREATE TABLE statement has three parts:

• Following the CREATE TABLE statement is a table name, which in this case is customer.

• Following an opening bracket is a list of attribute names, types and lengths, and modifiers. These are comma separated.

• After this is a list of other information about the structure and use of the table. In this example, a PRIMARY KEY is defined and the table type is set to MyISAM.

• Like all SQL statements, this one ends with a semi-colon.

We explain most of these in detail later in this section. Tables types are discussed in Chapter 15.

The CREATE TABLE statement for the customer table is derived from the entity-relationship model in Figure 5-3, and the process of converting this model to CREATE TABLE statements is described in Appendix E. The complete list of tables in the winestore database and a brief description of each and its relationships is shown in Table 5-1.

Table 5-1. The tables in the winestore database

Table	Description
countries	Lookup table containing country names. Related to customer.
customer	Customer details, including address, contact details, and date of birth. Related to countries, orders, titles, and users.
grape_variety	Lookup table containing grape variety names. Related to wine_variety.
inventory	Stock records that show much wine is available and its price. Related to wine.
items	The wines in an order and their quantity and price. Related to wine and orders.
orders	Orders placed by customer, which contain items. Related to customer and items.
region	Wine growing districts that contain wineries. Related to winery.
titles	Lookup table containing titles (such as Mr. or Miss). Related to customer.
users	Email addresses (which are also used as user names) and encrypted passwords for each customer. Related to customer.
wine	Details about the wines. Related to items, inventory, wine_type, wine_variety, and winery.
wine_type	Lookup table containing wine categories (such as red or white). Related to wine.
wine_variety	The link between a wine and its grape varieties. Related to wine and grape_variety.
winery	Winery details. Related to wine and region.

If you followed our installation instructions in Appendix A through Appendix C, you've already downloaded the installation script that contains the statements to create all of the winestore database tables and this has been loaded into your MySQL installation (along with example data). To view the CREATE TABLE statements for the other tables in database, you can use the SHOW CREATE TABLE command in the command interpreter. For example, to see the statement used to create the wine table, type:

SHOW CREATE TABLE wine;

This statement is discussed in more detail in Chapter 15. You can also view the CREATE TABLE statements by opening the installation file winestore.data in a text editor; this is a good way to view all of the statements at once.

5.3.2.1 Tables and attributes

A table name can be 64 characters in length at most and may contain any character except a forward slash or a period. As you've seen, the name is usually the name of an entity created in the ER model. Attribute names may be up to 64 characters in length and can contain any character.

There are many possible data types for attributes, and details of selected commonly-used types are shown in Table 5-2. A complete list is provided in Section 6.2 of the MySQL manual. The MySQL manual is found at http://www.mysql.com/documentation. You can also download a copy from the same location and open it as a local file using your web browser; we recommend this approach, as it allows you fast access to the manual.

Table 5-2. Common SQL data types for attributes

Data type	Comments
int(length)	Integer with a maximum length; used for IDs, age, counters, etc.
decimal(width[,decimal_digits])	A number with a width including an optional number of decimal_digits after the decimal point; used for currency, measurements, etc.
datetime	Stores a date and time in the format YYYY-MM-DD HH:MM:SS.
time	Stores a time in the format HH:MM:SS.
date	Stores a date in the format YYYY-MM-DD.
timestamp	Stores the date and time in the format YYYYMMDDHHMMSS. The first-occurring timestamp attribute in a row has a special property: it is set to the current date and time when the row that contains it is created and it updates each time the row that contains it is modified. You can also update it to the current date and time by setting the attribute to NULL. Any other timestamp attributes in a row do not have this special property, but they can be updated to the current date and time by assigning NULL.
varchar(length)	An unpadded, variable-length text string with a specified maximum length.
char(length)	A padded, fixed-length text string of size length.
blob	An attribute that stores up to 64 KB of data.

For situations where the data stored is always much smaller or larger than the usual maximum possible value, most attribute types can be defined as tiny, small, medium, and big. For example, int can be specified as tinyint, smallint, mediumint, and bigint that are for signed integers in the ranges -128 to 127, -32768 to 32767, -8388608 to 8388607, and -9223372036854775808 to 9223372036854775807 respectively. The normal-size int has the range -2147483648 to 2147483647. We recommend choosing the smallest type that is suitable for a task: this saves space, and makes data retrieval and updates faster.

You'll find more detail of attribute types in Section 6.4 of the MySQL manual.

5.3.2.2 Modifiers

Modifiers may be applied to attributes. The most common modifier is NOT NULL, which means that a row can't exist without this attribute having a value. For example:

cust_id int(5) NOT NULL,

Another common modifier is DEFAULT , which sets the data to the value that follows when no data is supplied. For example, suppose you want to set the state attribute to the value Unknown when it isn't provided. You can do this using:

state varchar(20) DEFAULT "Unknown",

DEFAULT and NOT NULL can be used in combination: if a value isn't supplied for an attribute, NULL can be avoided by using the DEFAULT value; we return to this later in Section 5.4.

All numeric attributes have optional zerofill and unsigned modifiers. The former left-pads a value with zeros up to the size of the attribute type. The latter allows only positive values to be stored and roughly doubles the maximum positive value that can be stored.

Finally, the useful auto_increment modifier is described in Section 5.4.

5.3.2.3 Keys

A primary key is one or more attributes that uniquely identify a row in a table. As we discussed previously, primary keys are essential to maintaining relationships between tables in the database, and every table should have one. In the customer table in Example 5-1, the primary key is the cust_id attribute: each customer has a unique cust_id, and these are assigned sequentially as customers are added to the table.

You don't always have to create an extra attribute that serves the purpose of being the primary key. For example, in our users table we could choose the user_name attribute as the primary key, because each customer must have a unique email address. In our customer table, we could also have defined the primary key to be the combination of the surname plus the firstname plus the initial plus the zipcode (in the hope that's enough information to uniquely identify a customer!). As this example illustrates, if you don't already have an attribute that unique, it's easier to add an extra attribute that's purpose is to be the primary key. Determining primary keys from an ER model is discussed in detail in Appendix E.

The final component of the CREATE TABLE statement includes a specification of the keys. In Example 5-1, we specify that the unique identifier is the cust_id attribute by adding the statement PRIMARY KEY (cust_id). The PRIMARY KEY constraint has two restrictions: the attribute must be defined as NOT NULL, and any value inserted must be unique.

You can add other non-primary keys to a table. As we show you in Chapter 15, extra keys can make querying and updating of data in the database much faster. Each additional key definition creates an additional index that permits fast access to the data using the attributes defined in the key. As an example, suppose you want to access the customer data by a surname and firstname combination. In this case, you can add a KEY definition to the end of the CREATE TABLE statement:

  PRIMARY KEY (cust_id),

  KEY names (surname,firstname)

) type=MyISAM;

Each new KEY is given a unique label that you choose, in this case we've chosen the label names.

In many cases, without yet knowing what kinds of queries will be made on the database, it is difficult to determine what keys you should specify. MySQL permits at least 16 indexes to be created on any table (this depends on the table type), but unnecessary indexes should be avoided. Each index takes additional storage space, and it must be updated by the database server as the data stored in the table is inserted, deleted, and modified. In addition, indexes on multiple attributes can only be used to speed up certain queries. We discuss how to use indexes and index tuning in Chapter 15.

5.3.3 Deleting Databases and Tables

The DROP statement is used to remove tables and databases. Removing a table or database also deletes the data contained in it. For example, to remove the customer table and its data, use:

DROP TABLE customer;

To remove the complete winestore database (including all tables, indexes, and data), use:

DROP DATABASE winestore;

Take care with DROP—the command interpreter won't ask you if you're sure. However, we show you how to prevent accidental deletion (and prevent other database users from deleting databases, tables, and data) in Chapter 15.

Both DROP TABLE and DROP DATABASE support an optional IF EXISTS keyword which can be used to prevent an error being reported if the database or table doesn't exist. For example, to drop the winestore database and avoid an error if it's already been dropped (or was never created), use:

DROP DATABASE IF EXISTS winestore;

We've used this feature at the beginning of the winestore.data file that contains the SQL statements for loading the winestore database. The first three lines remove the database if it exists, create a new database, and use the new database:

DROP DATABASE IF EXISTS winestore;

CREATE DATABASE winestore;

USE winestore;

You can therefore reload the file by following our instructions in Appendix A through Appendix C, and it'll create and load a new winestore database every time.