<<

. 9
( 132 .)



>>

and to protect data in the event of a crash.
With the example given above, if in Step 2 the application were to discover that
no items are left, a ROLLBACK command will be given and no items will be removed
from the inventory. In the case of a crash, the in-progress transactions will be auto-
matically rolled back.
A transaction-capable database must support the four properties that go by the
acronym ACID, which are defined as follows:

— Atomicity ” The operations that make up each transaction are treated col-
lectively as a single, or atomic, unit. Either all changes are committed or
none are.
— Consistency ” The available data will never be in an inconsistent state;
either other threads will see the data in the state it was in prior to the
transaction, or other threads will see the data in the state it winds up in
after the transaction is completed.
— Isolation ” Each transaction is isolated from all others. The effects of
Transaction A are not visible to Transaction B until Transaction A is com-
pleted. If a transaction is in progress, the interim state of the data will not
be visible to other transactions.
— Durability ” When a transaction is complete, the changes are permanent.
Even if a database crashes, the information from a committed transaction
will be available and complete.

In older versions of MySQL transactions were not supported. This was a major
problem for many developers, who could not fathom the idea of designing proper
applications without this feature. Now MySQL features several table types (includ-
ing InnoDB and BerkeleyDB) that support transactions. You read more about these
tables in Chapter 2.

Stored procedures
The big fancy database systems allow for procedural code (real computer code, sim-
ilar to PHP or Perl) to be placed within the database. Using stored procedures pro-
vides a couple of key advantages. First, it can reduce the amount of code needed in
22 Part I: Working with MySQL

middleware applications. If MySQL accepted stored procedures (which it unfortu-
nately does not ” yet), a single PHP command could be sent to the database to
query data, do some string manipulation, and then return a value ready to be dis-
played in your page.
The other major advantage comes when you are working in an environment in
which more than one front-end is accessing the same database. Consider a situation
in which there happens to be one front-end written for the Web and another, acces-
sible on Windows machines, written in Visual C++. It would be a pain to write all
the queries and transactions in two different places. You™d be much better off writ-
ing stored procedures and accessing those from your various applications. Stored
procedures are planned for MySQL version 5.0.



Summary
At this point you should have a pretty good idea of how relational databases work.
The theory covered here is really important, as quality data design is one of the cor-
nerstones of quality applications. If you fail in the normalization process, you
could create difficulties that will haunt you for months or years.
In the applications in Parts III and IV of this book, you see how we approach and
normalize several sets of data.
Now that you know how tables in a relational database work, move on to
Chapter 2, where you see how to make these tables in MySQL.
Chapter 2

The Structured Query
Language for Creating
and Altering Tables
IN THIS CHAPTER

— Creating tables and databases in MySQL

— Choosing the proper column type and column attributes for tables

— Choosing the proper tables for your applications

— Altering existing tables

— Using phpMyAdmin

— Using MySQLCC

— Using MacSQL



IN CHAPTER 1 you learned that tables are the basis of all the good things that come
from working with relational databases. You can do a fair amount with these tables,
as you™ll see throughout this book. So it should come as no surprise that creating
and maintaining them requires some knowledge.
If you™re coming to MySQL from a background in Microsoft™s SQL Server or
a desktop package like Access, you may be used to creating tables with a slick
WYSIWYG (what you see is what you get) interface. There™s no doubt that working
with a graphical interface can be a lot more pleasant than figuring out the syntax
of a language ” any language. In fact, you can use any of several GUI tools to cre-
ate and manipulate tables, and we™ll discuss some of these later in the chapter.
However, even if you plan on installing and using a GUI tool, you should take some
time to learn how to create and maintain tables using the Data Definition Language
(DDL), which is part of SQL. Specifically, it will be a great help to you to understand
the create and alter commands. Before too long you will have to use these com-
mands within your scripts. There also may be an occasion when you don™t have
access to the graphical interface, and you™ll need this knowledge to fall back on.


23
24 Part I: Working with MySQL


Essential Definitions
Before we get to creating tables and databases in MySQL, you™ll need to understand
a couple of items. The concepts we™re about to present are very important ” make
sure you understand how to deal with these before you move forward in your data-
base design.

Null values
One of the first decisions you will have to make for every column in your table is
whether or not to allow null values. If you remember back to your basic math, you
may recall the null set ” a group that contains nothing. In relational databases, null
has the same meaning: A null field contains nothing.
The concept of nothing is different from the concept of zero. A field that is null
is distinctly different from a field containing a text string with no characters (a
zero-length string) or a numerical value of 0. The difference is that empty strings
and zeros are values.
This SQL statement . . .

select * from mytable where myfield = 0;

returns rows in which the myfield column contains the numerical value 0. In
contrast, the statement . . .

select * from mytable where myfield = ˜™;

returns an entirely different set of rows: those with nothing at all in their
myfield columns.
Value comparisons do not work with null. Since null is the absence of value, any
comparison with any value (including another null) is meaningless. In Chapter 3
you can see that using null values requires that the application developer be very
careful when writing table joins. To give you a quick preview, consider what would
happen if we wanted to join Table 2-1 and Table 2-2:


In your SQL select statements (covered in Chapter 3), you can deter-
mine if a field contains a null value in a couple of ways. First, you can use
MySQL™s isnull() function. For example, to find rows in a table where the
middle_name column contains null values, you could run the following
query:
select * from names where isnull(middle_name);
Or, to exclude null values from the query result:
select * from names where !isnull(middle_name);
Chapter 2: The Structured Query Language for Creating and Altering Tables 25

The exclamation point means “not.”
You can also use the is null and is not null statements. For example:
select * from users were addr2 is null;
select * from users where addr2 is not null;




TABLE 2-1 CONTACTS

first_name last_name fantasy_spouse_id

Jay Greenspan 1
Brad Bulger NULL




TABLE 2-2 desired_spouse

fantasy_spouse_id First_name last_name

1 Nicole Kidman




If you wanted to find the authors of a great book on MySQL and PHP and their
dream spouses, you would have to join these tables on the fantasy_spouse_id
field. (Don™t worry if you don™t understand the exact syntax, it will be covered in
the next chapter.)

SELECT * FROM contacts, desired_spouse
WHERE contacts.fantasy_spouse_id =
desired_spouse.fantasy_spouse_id;

This statement works fine for Jay, but there™s going to be a problem for Brad
because he™s a happy bachelor and his fantasy_spouse_id field is null. He will not
show up in the result set even though the goal of the query is to get all the people
in the contacts table and the associated fantasy spouses, if the retrieved contacts
have them.
Again, this is just a preview, an example of why null is so important. In Chapter
3 you can see how the outer join solves problems like this.
26 Part I: Working with MySQL


Indexes
Arguably the single greatest advantage of a relational database is the speed with
which it can query and sort tremendous amounts of information. To achieve this
great speed, MySQL and all other database servers make use of optimized data-
retrieval mechanisms called indexes.
An index allows a database server to create a representation of a column that it
can search with amazing speed. Indexes are especially helpful in finding a single
row or group of rows from a large table. They can also speed up joins and aggre-
gate functions, like min() and max(), which we cover in Chapter 3.
Given these advantages, why not just create an index for every column for every
table? There are some very good reasons. First, indexes can actually slow some things
down. It takes time for your database server to maintain indexes. You wouldn™t
want to create overhead for your server that is not going to be a benefit to you
down the road. Also, on some occasions the indexes themselves are slower. If you
need to iterate through every row in a table, you™re actually better off not using an
index. Also, unnecessary indexes will use a lot of disk space and memory.
A table™s primary key is often the subject of searches (for obvious reasons). Thus
the column or columns that you declare as your primary key in a table definition
will automatically be indexed.
We™ll talk more about creating indexes later in this chapter.



The create database Statement
Before you can get to creating your tables, you™ll need to create a database to hold
them. This should take all of a second. The basic create database statement is
fairly simple and can be run from any interface that has access to MySQL.
The general syntax is as follows:

create database database_name



In case you™re wondering, after running this command, MySQL creates a
folder in which it stores all the files needed for your database. On our Linux
machines the database folders are stored in /usr/local/mysql/lib.



When naming databases, or for that matter columns or indexes, avoid using
names that will cause confusion down the road. On operating systems in which file
names are case-sensitive, such as most Unix systems, database names will also be
case-sensitive. Come up with conventions that you plan on sticking to, such as
using all-lowercase names for tables and columns. Spaces are not allowed.
Though MySQL can work around potentially bad choices, you should avoid
using words that MySQL uses in the course of its business. For instance, naming a
Chapter 2: The Structured Query Language for Creating and Altering Tables 27

table “Select” is a really bad idea. Chapter 6 of the MySQL reference manual lists
over 150 reserved words. If you stay away from words used by SQL or MySQL func-
tions, you should be okay.
From the MySQL command-line client, you can simply type in the following
command:

mysql> create database my_database;



The MySQL command-line client is in the bin/ directory of your MySQL
installation and has the file name mysql (in Unix) or mysql.exe (in DOS/
Windows).



From PHP, you can use the mysql_query() function. The following piece of
code would create two databases. (Keep in mind that you need to log into MySQL
as a user with the proper rights for the code to work.)

$conn = mysql_connect(“localhost”,”username”, “password”)
or die (“Could not connect to localhost”);



mysql_query(“ CREATE DATABASE IF NOT EXISTS my_database “) or
die (“Could not create database”);



<<

. 9
( 132 .)



>>