Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

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.

countries

customer

grape_variety

inventory

items

orders

region

titles

users

wine

wine_type

wine_variety

winery

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.

int(length)

decimal(width[,decimal_digits])

datetime

time

date

timestamp

varchar(length)

char(length)

blob

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 DROPthe 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.