Building the Database

fter completing your database design (see Chapter 3 if you haven’t done this yet), you’re ready to turn it into a working database. In this chapter,
you find out how to build a database based on your design — and how to move data in and out of it.

The database design names the database and defines the tables that make up the database. In order to build the database, you must communicate with MySQL, providing the database name and the table structure. Later on, you must communicate with MySQL to add data to (or request information from) the database. The language that you use to communicate with MySQL is SQL. In this chapter, I explain how to create SQL queries and use them to build
new databases and interact with existing databases.

Communicating with MySQL

The MySQL server is the manager of your database:

It creates new databases.

It knows where the databases are stored.

It stores and retrieves information, guided by the requests (queries) that it receives.

To make a request that MySQL can understand, you build an SQL query and send it to the MySQL server. (For a more complete description of the MySQL server, see Chapter 1.) The next two sections detail how to do this.

Building SQL queries

SQL (Structured Quer y Language) is the computer language that you use to communicate with MySQL. SQL is almost English; it is made up largely of English words, put together into strings of words that sound similar to
English sentences. In general (fortunately), you don’t need to understand any arcane technical language to write SQL queries that work.

The first word of each query is its name, which is an action word (a verb)
that tells MySQL what you want to do. The queries that I discuss in this chap­ ter are CREATE, DROP, ALTER, SHOW, INSERT, LOAD, SELECT, UPDATE, and DELETE. This basic vocabulary is sufficient to create — and interact with — databases on Web sites.

The query name is followed by words and phrases — some required and some optional — that tell MySQL how to perform the action. For instance, you always need to tell MySQL what to create, and you always need to tell it which table to insert data into or to select data from.

The following is a typical SQL query. As you can see, it uses English words:

SELECT lastName FROM Member

This query retrieves all the last names stored in the table named Member. Of course, more complicated queries (such as the following) are less English-like:

SELECT lastName,firstName FROM Member WHERE state=”CA” AND
city=”Fresno” ORDER BY lastName

This query retrieves all the last names and first names of members who live in Fresno and then puts them in alphabetical order by last name. This query is less English-like but still pretty clear.

Here are some general points to keep in mind when constructing an SQL
query, as illustrated in the preceding sample query:

Capitalization: In this book, I put the SQL language words in all caps; items of variable information (such as column names) are usually given labels that are all or mostly lowercase letters. I did this to make it easier for you to read — not because MySQL needs this format. The case of the SQL words doesn’t matter; select is the same as SELECT, and from is the same as FROM, as far as MySQL is concerned. On the other hand, the case of the table names, column names, and other variable information does matter if your operating system is Unix and Linux. When using Unix or Linux, MySQL needs to match the column names exactly, so the case for the column names has to be correct — lastname is not the same as lastName. Windows, however, isn’t as picky as Unix and Linux; from its point of view, lastname and lastName are the same.

Chapter 4: Building the Database 67

Spacing: SQL words need to be separated by one or more spaces. It doesn’t matter how many spaces you use; you could just as well use 20 spaces or just 1 space. SQL also doesn’t pay any attention to the end of the line. You can start a new line at any point in the SQL statement or write the entire statement on one line.

Quotes: Notice that CA and Fresno are enclosed in double quotes (“) in the preceding query. CA and Fresno are series of characters called text strings or character strings. (I explain strings in detail later in this chap­ ter.) You are asking MySQL to compare the text strings in the SQL query with the text strings already stored in the database. Text strings are enclosed in quotes. When you compare numbers (such as integers) stored in numeric columns, you don’t enclose the numbers in quotes. (In Chapter 3, I explain the types of data that can be stored in a MySQL database.)

Sending SQL queries

This book is about PHP and MySQL as a pair. Consequently, I don’t describe the multitude of ways in which you can send SQL queries to MySQL — many of which have nothing to do with PHP. Rather, I provide a simple PHP pro­ gram that you can use to execute SQL queries. (For the lowdown on PHP and how to write PHP programs, check out Part III of this book.)

The program mysql_send.php has one simple function: to execute queries and display the results. Enter the program into the directory where you’re developing your Web application (or download it from my Web site at janet.valade.com), change the information in lines 9–19, and then point your browser at the program. Listing 4-1 shows the program.
Listing 4-1: PHP Program for Sending SQL Queries to MySQL

<!-- Program: mysql_send.php
Desc: PHP program that sends an SQL query to the
MySQL server and displays the results.
-->
<html>
<head><title>SQL Query Sender</title></head>
<body>
<?php
$host=”hostname”;
$user=” mysqlaccountname “;
$password=”mysqlpassword”;

/* Section that executes query */
if(@$_GET[‘form’] == “yes”)
{

mysql_connect($host,$user,$password);
mysql_select_db($_POST[‘database’]);
$query = stripSlashes($_POST[‘query’]);
$result = mysql_query($query);
echo “Database Selected: <b>{$_POST[‘database’]}</b><br> Query: <b>$query</b><h3>Results</h3><hr>”;
if($result == 0)
echo “<b>Error “.mysql_errno().”: “.mysql_error(). “</b>”;
elseif (@mysql_num_rows($result) == 0)
echo(“<b>Query completed. No results returned.
</b><br>”);
else
{
echo “<table border=’1’>
<thead>
<tr>”;
for($i = 0;$i < mysql_num_fields($result);$i++)
{
echo “<th>”.mysql_field_name($result,$i). “</th>”;
}
echo “ </tr>
</thead>
<tbody>”;
for ($i = 0; $i < mysql_num_rows($result); $i++)
{
echo “<tr>”;
$row = mysql_fetch_row($result);
for($j = 0;$j<mysql_num_fields($result);$j++)
{
echo(“<td>” . $row[$j] . “</td>”);
}
echo “</tr>”;
}
echo “</tbody>
</table>”;
} //end else echo “
<hr><br>
<form action=\”{$_SERVER[‘PHP_SELF’]}\” method=\”POST\”>
<input type=’hidden’ name=’query’ value=’$query’>
<input type=’hidden’ name=’database’
value={$_POST[‘database’]}>
<input type=’submit’ name=\”queryButton\”
value=\”New Query\”>
<input type=’submit’ name=\”queryButton\”
value=\”Edit Query\”>
</form>”; unset($form); exit();
} // endif form=yes

/* Section that requests user input of query */
@$query=stripSlashes($_POST[‘query’]);
if (@$_POST[‘queryButton’] != “Edit Query”)
{
$query = “ “;
}
?>

<form action=”<?php echo $_SERVER[‘PHP_SELF’] ?>?form=yes”
method=”POST”>
<table>
<tr>
<td align=right><b>Type in database name</b></td>
<td><input type=”text” name=”database”
value=<?php echo @$_POST[‘database’] ?> ></td>
</tr>
<tr>
<td align=”right” valign=”top”>
<b>Type in SQL query</b></td>
<td><textarea name=”query” cols=”60”
rows=”10”><?php echo $query ?></textarea>
</td>
</tr>
<tr>
<td colspan=”2” align=”center”><input type=”submit”
value=”Submit Query”></td>
</tr>
</table>
</form>
</body></html>

You need to change lines 9, 10, and 11 of the program before you can use it. These lines are

$host=”hostname”;
$user=”mysqlaccountname”;
$password=”mysqlpassword”;

Change hostname to the name of the computer where MySQL is installed: for example, databasehost.mycompany.com. If the MySQL database is installed on the same computer as your Web site, you can use localhost as the hostname.

Change mysqlaccountname and mysqlpassword to the account name and password that you were given by the MySQL administrator to use to access your MySQL database. If you installed MySQL yourself, an account named root with no password is automatically installed. Sometimes an account with a blank account name and password is installed. You can use either the root or the blank account, but it’s much better if you install an account specifi­ cally for use with your Web database application. (I discuss MySQL accounts and passwords in detail in Chapter 5.)

An account named root with no password is not secure. You should give it a password right away. An account with a blank account name and password is even less secure. Anyone can access your database without needing to know an account name or password. You should delete this account if it exists
(see Chapter 5).

If your MySQL account doesn’t require a password, type nothing between the double quotes, as follows:

$password=””;

After you enter the correct hostname, account name, and password in mysqlsend.php, these are the general steps that you follow to execute an SQL query:

1. Point your browser at mysql_send.php.

You see the Web page shown in Figure 4-1.

2. Type the SQL query in the large text box.

3. Enter a database name in the first text box if the SQL query
requires one.

I explain the details of writing specific SQL queries in the following sec­
tions of this chapter.

4. Click the Submit Query button.

The query is executed, and a page is displayed, showing the results of the query. If your query had an error, the error message is displayed.

You can test the mysql_send.php program by entering this test query in
Step 2 of the preceding steps:

SHOW DATABASES

This query does not require you to enter a database name, so you can skip Step 3. When you click the Submit Query button in Step 4, a listing of the existing databases is displayed. In most cases, you see a database called Test, which is installed automatically when MySQL is installed. Also, you’ll probably see a database called mysql, which MySQL uses to store informa­ tion that it needs, such as account names, passwords, and permissions. Even if there are no existing databases, your SQL query will execute correctly. If a problem occurs, an error message is displayed. MySQL error messages are usually pretty helpful in finding the problem.

A quicker way to send SQL queries to the MySQL server
When MySQL is installed, a simple, text-based program called mysql (or sometimes the terminal monitor or the monitor) is also installed. Programs that communicate with servers are client soft­ ware; because this program communicates with the MySQL server, it’s a client. When you enter SQL queries in this client, the response is returned to the client and displayed onscreen. The mon­ itor program can send queries across a network; it doesn’t have to be running on the machine where the database is stored.

To send SQL queries to MySQL by using the mysql client, follow these steps:

1. Locate the mysql client.

By default, the mysql client program is installed in the subdirectory bin, under the directory where MySQL was installed. In Unix/Linux, the default is /usr/local/mysql/bin or
/usr/local/bin. In Windows, the default is c:\mysql\bin. However, the client might have been installed in a different directory. Or, if you’re not the MySQL administrator, you might not have access to the mysql client. If you don’t know where MySQL is installed or can’t run the client, ask the MySQL administrator to put the client somewhere where you can run it or to give you a copy that you can put on your own computer.

2. Start the client.

In Unix/Linux, type the path/filename (for example, /usr/local/mysql/bin/mysql). In Windows, open a command prompt window and then type the path/filename (for example, c:\mysql\bin\mysql.exe). Press Enter after typing the path/filename unless you’re using the parameters shown in Step 3.

(continued)

(continued)

3. If you’re starting the mysql client to access a database across the network, use the follow­
ing parameters after the mysql command:

-h host: host is the name of the machine where MySQL is located.

-u user: user is your MySQL account name.

-p: This parameter prompts you for the password for your MySQL account.

For instance, if you’re in the directory where the mysql client is located, the command might look like this:
mysql -h mysqlhost.mycompany.com -u root -p

Press Enter after typing the command.

4. Enter your password when prompted for it.

The mysql client starts, and you see something similar to this:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 459 to server version: 4.0.13
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql>

5. Select the database that you want to use.

At the mysql prompt, type the following:

use databasename

Use the name of the database that you want to query.

6. At the mysql prompt, type your SQL query, followed by a semicolon (;), and then press the
Enter key.

The mysql client continues to prompt for input and does not execute the query until you enter a semicolon. The response to the query is displayed onscreen.

7. To leave the mysql client, type quit at the prompt and then press the Enter key.

Building a Database

A database has two parts: a structure to hold the data and the data itself. In the following few sections, I explain how to create the database structure. First you create an empty database with no structure at all, and then you add tables to it.

The SQL queries that you use to work with the database structure are CREATE, ALTER, DROP, and SHOW. To use these queries, you must have a MySQL account that has permission to create, alter, and drop databases and tables. See Chapter 5 for more on MySQL accounts.

Creating a new database

To create a new, empty database, use the following SQL query:

CREATE DATABASE databasename

where databasename is the name that you give the database. For instance, these two SQL queries create the sample databases used in this book:

CREATE DATABASE PetCatalog
CREATE DATABASE MemberDirectory

Some Web hosting companies don’t allow you to create a new database. You are given one database to use with MySQL, and you can only create tables in this one database. You can try requesting another database, but you need a good reason. MySQL and PHP don’t care that all your tables are in one data­ base instead of organized into databases with meaningful names. It’s just easier for humans to keep track of projects when they’re organized.

To see for yourself that a database was in fact created, use this SQL query:

SHOW DATABASES

After you create an empty database, you can add tables to it. (Adding tables to a database is described later in this chapter.)

Deleting a database

You can delete any database with this SQL query:

DROP DATABASE databasename

Use DROP carefully because it is irreversible. After a database is dropped, it is gone forever. And any data that was in it is gone as well.

Adding tables to a database

You can add tables to any database, whether it’s a new, empty database that you just created or an existing database that already has tables and data in it. You use the CREATE query to add tables to a database.

In the sample database designs that I introduce in Chapter 3, the PetCatalog database is designed with three tables: Pet, PetType, and PetColor. The MemberDirectory database is designed with two tables: Member and Login. Because a table is created in a database, you must indicate the database
name where you want the table created. That is, when using the form shown

in Figure 4-1, you must type a database name into the top field. If you don’t, you see the error message No Database Selected.

The query to add a table begins with

CREATE TABLE tablename

Next comes a list of column names with definitions. The information for each column is separated from the information for the next column by a comma. The entire list is enclosed in parentheses. Each column name is followed by its data type (I explain data types in detail in Chapter 3) and any other defini­ tions required. Here are some definitions that you can use:

NOT NULL: This column must have a value; it cannot be empty.

DEFAULT value: This value is stored in the column when the row is cre­
ated if no other value is given for this column.

AUTO_INCREMENT: You use this definition to create a sequence number.
As each row is added, the value of this column increases by one integer from the last row entered. You can override the auto number by assign­ ing a specific value to the column.

UNSIGNED: You use this definition to indicate that the values for this
numeric field will never be negative numbers.

The last item in a CREATE TABLE query indicates which column or combina­ tion of columns is the unique identifier for the row — the primar y key. Each row of a table must have a field or a combination of fields that is different for each row. No two rows can have the same primary key. If you attempt to add a row with the same primary key as a row that’s already in the table, you get an error message, and the row is not added. The database design identifies the primary key (as I describe in Chapter 3). You specify the primary key by using the following format:

CREATE TABLE Member (
loginName VARCHAR(20) NOT NULL PRIMARY KEY,
createDate DATE NOT NULL); PRIMARY KEY(columnname)

The columnname is enclosed in parentheses. If you’re using a combination of columns as the primary key, include all the column names, separated by commas. For instance, you would designate the primary key for the Login table in the MemberDirectory database by using this query:

PRIMARY KEY (loginName,loginTime)

Listing 4-2 shows the CREATE TABLE query used to create the Member table of the MemberDirectory database. You could enter this query on a single line if you wanted to. MySQL doesn’t care how many lines you use. However, the format shown in Listing 4-2 makes it easier to read. This human-friendly format also helps you spot typos.

You have an error in your SQL syntax near ‘order var(20))’ at line 1

Notice this message shows the column definition that it didn’t like and the
line where it found the offending definition. However, the message doesn’t tell you much about what the problem is. The error in your SQL syntax that it refers to is using the MySQL reserved word order as a column name.

After a table has been created, you can query to see it, review its structure, or remove it.

To see the tables that have been added to a database, use this SQL
query:

SHOW TABLES

You can also see the structure of a table with this query:

SHOW COLUMNS FROM tablename

You can remove any table with this query:

DROP TABLE tablename

Use DROP carefully because it is irreversible. After a table is dropped, it is gone forever. And any data that was in it is gone as well.

Changing the database structure

Your database isn’t written in stone. By using the ALTER query, you can change the name of the table; add, drop, or rename a column; or change the data type or other attributes of the column.

The basic format for this query is ALTER TABLE tablename, followed by the specific changes that you’re requesting. Table 4-1 shows the changes that you can make.

Table 4-1 Changes You Can Make with the ALTER Query

Change Description

ADD columnname Adds a column; definition includes the data
definition type and optional definitions.

ALTER columnname Changes the default value for a column.
SET DEFAULT value

ALTER columnname Removes the default value for a column.
DROP DEFAULT

CHANGE columnname Changes the definition of a column and newcolumnname renames the column; definition includes definition the data type and optional definitions.

DROP columnname Deletes a column, including all the data in the column. The data cannot be recovered.

MODIFY columnname Changes the definition of a column;
definition definition includes the data type and optional definitions.

RENAME newtablename Renames a table.

Changing a database is not a rare occurrence. You might want to change your database for many reasons. For example, suppose that you defined the column lastName with VARCHAR(20) in the Member table of the
MemberDirectory database. At the time, 20 characters seemed sufficient for a last name. But now you just received a memo announcing the new CEO, John Schwartzheimer-Losertman. Oops. MySQL will truncate his name to the first 20 letters, a less-than-desirable new name for the boss. So you need to make the column wider — pronto. Send this query to change the column in a second:

ALTER TABLE Member MODIFY lastName VARCHAR(50)

Moving Data In and Out of the Database

An empty database is like an empty cookie jar — it’s not much fun. And, searching an empty database is no more interesting or fruitful than searching an empty cookie jar. A database is only useful with respect to the information that it holds.

A database needs to be able to receive information for storage and to deliver information on request. For instance, the MemberDirectory database needs to be able to receive the member information, and it also needs to be able to deliver its stored information when you request it. For instance, if you want to know the address of a particular member, the database needs to deliver that information when you request it.

Your MySQL database responds to four types of requests:

Adding information: Adding a row to a table.

Updating information: Changing information in an existing row. This includes adding data to a blank field in an existing row.

Retrieving information: Looking at the data. This request does not
remove data from the database.

Removing information: Deleting data from the database.

Sometimes your question requires information from more than one table. For instance, the question, “How much does a green dragon cost?” requires infor­ mation from the Pet table and from the Color table. You can ask this ques­ tion easily in a single SELECT query by combining the tables.

In the following sections, I discuss how to receive and deliver information as well as how to combine tables.

Adding information

Every database needs data. For example, you might want to add data to your database so that your users can look at it — an example of this is the Pet Catalog that I introduce in Chapter 3. Or you might want to create an empty database for users to put data into, making the data available for your eyes only — an example of this is the Member Directory. In either scenario, data will be added to the database.

If your data is still on paper, you can enter it directly into a MySQL database, one row at a time, by using an SQL query. However, if you have a lot of data, this process could be tedious and involve a lot of typing. Suppose that you

have information on 1,000 products that needs to be added to your database. Assuming that you’re greased lightening on a keyboard and can enter a row per minute, that’s 16 hours of rapid typing — well, rapid editing, anyway. Doable, but not fun. On the other hand, suppose that you need to enter 5,000 members of an organization into a database and that it takes five minutes to enter each member. Now you’re looking at over 400 hours of typing — who has time for that?

If you have a large amount of data to enter, consider some alternatives. Sometimes scanning in the data is an option. Or perhaps you need to beg, borrow, or hire some help. In many cases, it could be faster to enter the data into a big text file than to enter each row in a separate SQL query.

The SQL query LOAD can read data from a big text file (or even a small text file). So, if your data is already in a computer file, you can work with that file; you don’t need to type all the data again. Even if the data is in a format other than a text file (for example, in an Excel, Access, or Oracle file), you can usu­ ally convert the file to a big text file, which can then be read into your MySQL database. If the data isn’t yet in a computer file and there’s a lot of it, it might be faster to enter that data into the computer in a big text file and transfer it into MySQL as a second step.

Most text files can be read into MySQL, but some formats are easier than others. If you’re planning to enter the data into a big text file, read the sec­ tion, “Adding a bunch of data,” to find the best format for your text file. Of course, if the data is already on the computer, you have to work with the file as it is.

Adding one row at a time
You use the INSERT query to add a row to a database. This query tells MySQL
which table to add the row to and what the values are for the fields in the row. The general form of the query is

INSERT INTO tablename (columnname, columnname,....,columnname) VALUES (value, value,....,value)

The following rules apply to the INSERT query:

Values must be listed in the same order in which the column names are listed. The first value in the value list is inserted into the column that’s named first in the column list; the second value in the value list
is inserted into the column that’s named second in the column list; and so on.

A partial column list is allowed. You don’t need to list all the columns.
Columns that are not listed are given their default value or left blank if no default value is defined.

A column list is not required. If you’re entering values for all the columns, you don’t need to list the columns at all. If no columns are listed, MySQL will look for values for all the columns, in the order in which they appear in the table.

The column list and value list must be the same length. If the list of columns is longer or shorter than the list of values, you get an error message like this: Column count doesn’t match value count.

The following INSERT query adds a row to the Member table:

INSERT INTO Member (loginName,createDate,password,lastName, street,city,state,zip,email,phone,fax)
VALUES (“bigguy”,”2001-Dec-2”,”secret”,”Smith”, “1234 Happy St”,”Las Vegas”,”NV”,”88888”, “gsmith@GSmithCompany.com”,”(555) 555-5555”,””)

Notice that firstName is not listed in the column name list. No value is entered into the firstName field. If firstName were defined as NOT NULL, MySQL would not allow this, but because firstName is not defined as NOT NULL, this is okay. Also, if the definition for firstName included a default, the default value would be entered, but because it doesn’t, the field is left empty. Notice that the value stored for fax is an empty string; MySQL has no prob­ lem with empty strings.

To look at the data that you entered and ensure that you entered it correctly, use an SQL query that retrieves data from the database. I describe these SQL queries in detail in “Retrieving information,” later in this chapter. In brief, the following query retrieves all the data in the Member table:

SELECT * FROM Member

Adding a bunch of data
If you have a large amount of data to enter and it’s already in a computer file, you can transfer the data from the existing computer file to your MySQL data­ base. The SQL query that reads data from a text file is LOAD. The LOAD query requires you to specify a database.

Because data in a database is organized in rows and columns, the text file being read must indicate where the data for each column begins and ends
and where the end of a row is. To indicate columns, a specific character sepa­
rates the data for each column. By default, MySQL looks for a tab character to separate the fields. However, if a tab doesn’t work for your data file, you can choose a different character to separate the fields and tell MySQL in the query that a different character than the tab separates the fields. Also by default, the end of a line is expected to be the end of a row — although you can choose a character to indicate the end of a line if you need to. A data file for the Pet table might look like this:

Unicorn<TAB>horse<TAB>Spiral horn<Tab>5000.00<Tab>/pix/unicorn.jpg Pegasus<TAB>horse<TAB>Winged<Tab>8000.00<Tab>/pix/pegasus.jpg Lion<TAB>cat<TAB>Large; Mane on neck<Tab>2000.00<Tab>/pix/lion.jpg

A data file with tabs between the fields is a tab-delimited file. Another common format is a comma-delimited file, where commas separate the fields. If your data is in another file format, you need to convert it into a delimited file.

To convert data in another file format into a delimited file, check the manual for that software or talk to your local expert who understands the data’s cur­ rent format. Many programs, such as Excel, Access, or Oracle, allow you to output the data into a delimited file. For a text file, you might be able to con­ vert it to delimited format by using the search-and-replace function of an editor or word processor. For a truly troublesome file, you might need to seek the help of an expert or a programmer.

The basic form of the LOAD query is

LOAD DATA INFILE “datafilename” INTO TABLE tablename

This basic form can be followed by optional phrases if you want to change a default delimiter. The options are

FIELDS TERMINATED BY ‘character’ FIELDS ENCLOSED BY ‘character’ LINES TERMINATED BY ‘character’

Suppose that you have the data file for the Pet table, shown earlier in this section, except that the fields are separated by a comma rather than a tab. The name of the data file is pets.dat, and it’s located in the same directory as the database. The SQL query to read the data into the table is

LOAD DATA INFILE “pets.dat” INTO TABLE Pet
FIELDS TERMINATED BY ‘,’

In order to use the LOAD DATA INFILE query, the MySQL account must have the FILE privilege on the server host. I discuss MySQL account privileges in Chapter 5.

To look at the data that you loaded — to be sure that it’s correct — use an SQL query that retrieves data from the database. I describe these types of SQL queries in detail in the next section. In brief, use the following query to look at all the data in the table so that you can check it:

SELECT * FROM Pet

Retrieving information

The only purpose in storing information is to have it available when you need it. A database lives to answer questions. What pets are for sale? Who are the members? How many members live in Arkansas? Do you have an alligator for sale? How much does a dragon cost? What is Goliath Smith’s phone number? And on and on. You use the SELECT query to ask the database questions.

The simplest, basic SELECT query is

SELECT * FROM tablename

This query retrieves all the information from the table. The asterisk (*) is a wildcard meaning all the columns.

The SELECT query can be much more selective. SQL words and phrases in the SELECT query can pinpoint exactly the information needed to answer your question. You can specify what information you want, how you want it organized, and what the source of the information is:

You can request only the information (the columns) that you need to answer your question. For instance, you can request only the first and last names to create a list of members.

You can request information in a particular order. For instance, you can request that the information be sorted in alphabetical order.

You can request information from selected objects (the rows) in your table. (See Chapter 3 for an explanation of database objects.) For instance, you can request the first and last names for only those mem­ bers whose addresses are in Florida.

Retrieving specific information
To retrieve specific information, list the columns containing the information you want. For example:

SELECT columnname,columnname,columnname,... FROM tablename

This query retrieves the values from all the rows for the indicated column(s). For instance, the following query retrieves all the last names and first names stored in the Member table:

SELECT lastName,firstName FROM Member

You can perform mathematical operations on columns when you select them. For example, you can use the following SELECT query to add two columns together:

SELECT col1+col2 FROM tablename

Or you could use the following query:

SELECT price,price*1.08 FROM Pet

The result is the price and the price with the sales tax of 8 percent added on. You can change the name of a column when selecting it, as follows:

SELECT price,price*1.08 AS priceWithTax FROM Pet

The AS clause tells MySQL to give the name priceWithTax to the second column retrieved. Thus, the query retrieves two columns of data: price and priceWithTax.

In some cases, you don’t want to see the values in a column, but you want to know something about the column. For instance, you might want to know the lowest value in the column or the highest value in the column. Table 4-2 lists some of the information that is available about a column.

Table 4-2 Information That Can Be Selected

SQL Format Description of Information

AVG(columnname) Returns the average of all the values in columnname

COUNT(columnname) Returns the number of rows in which columnname is not blank
MAX(columnname) Returns the largest value in columnname MIN(columnname) Returns the smallest value in columnname SUM(columnname) Returns the sum of all the values in columnname

For example, the query to find out the highest price in the Pet table is

SELECT MAX(price) FROM Pet

SQL words like MAX() and SUM() are functions. SQL provides many functions in addition to those in Table 4-2. Some functions, like those in Table 4-2, provide information about a column. Other functions change each value selected. For example, SQRT() returns the square root of each value in the column, and DAYNAME() returns the name of the day of the week for each value in a date column, rather than the actual date stored in the column. Over 100 functions are available for use in a SELECT query. For descriptions of all the functions, see the MySQL documentation at www.mysql.com/ documentation.

Retrieving data in a specific order
You might want to retrieve data in a particular order. For instance, in the Member table, you might want members organized in alphabetical order by last name. Or, in the Pet table, you might want the pets grouped by type
of pet.

In a SELECT query, ORDER BY and GROUP BY affect the order in which the data is delivered to you:

ORDER BY: To sort information, use the phrase

ORDER BY columnname

The data is sorted by columnname in ascending order. For instance, if columnname is lastName, the data is delivered to you in alphabetical order by the last name.

You can sort in descending order by adding the word DESC before the column name. For example:

SELECT * FROM Member ORDER BY DESC lastName

GROUP BY: To group information, use the following phrase:

GROUP BY columnname

The rows that have the same value of columnname are grouped together. For example, use this query to group the rows that have the same value as petType:

SELECT * FROM Pet GROUP BY petType

You can use GROUP BY and ORDER BY in the same query.

Retrieving data from a specific source
Very frequently, you don’t want all the information from a table. You only want information from selected database objects: that is, rows. Three SQL words are frequently used to specify the source of the information:

WHERE: Allows you to request information from database objects with certain characteristics. For instance, you can request the names of mem­ bers who live in California, or you can list only the pets that are cats.

LIMIT: Allows you to limit the number of rows from which information is retrieved. For instance, you can request all the information from the first three rows in the table.

DISTINCT: Allows you to request information from only one row of identical rows. For instance, in the Login table, you can request the loginName but specify no duplicate names, thus limiting the response

to one record for each member. This would answer the question, “Has the member ever logged in?” rather than the question “How many times has the member logged in?”

The WHERE clause of the SELECT query enables you to make very complicated selections. For instance, suppose your boss asks for a list of all the members whose last names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number. I’m sure there are many uses for such a list. You can get this list for your boss with a SELECT query by using a WHERE clause.

The basic format of the WHERE clause is

WHERE expression AND|OR expression AND|OR expression ...

expression specifies a value to compare with the values stored in the data­ base. Only the rows containing a match for the expression are selected. You can use as many expressions as needed, each one separated by AND or OR. When you use AND, both of the expressions connected by the AND (that is, both the expression before the AND and the expression after the AND) must be true in order for the row to be selected. When you use OR, only one of the expressions connected by the OR must be true for the row to be selected.

Some common expressions are shown in Table 4-3.

Table 4-3 Expressions for the WHERE Clause

Expression Example Result

column = value zip=”12345” Selects only the rows where 12345 is stored in the column named zip

column > value zip > “50000” Selects only the rows where the ZIP code is
50001 or higher

column >= value zip >= “50000” Selects only the rows where the ZIP code is
50000 or higher

column < value zip < “50000” Selects only the rows where the ZIP code is
49999 or lower

column <= value zip <= “50000” Selects only the rows where the ZIP code is
50000 or lower

Expression Example Result

column BETWEEN
value1 AND value2
zip BETWEEN “20000” AND “30000”
Selects only the rows where the ZIP code is greater than 19999 but less than 30001

column IN (value1,
zip IN (“90001”, Selects only the rows
value2,...) ”30044”) where the ZIP code is
90001 or 30044

column NOT IN (value1,value2,...)
zip NOT IN (“90001”,
”30044”) Selects only the rows where the ZIP code is any ZIP code except
90001 or 30044
column LIKE value — value can contain the wildcards % (which matches any string) and _ (which matches any character)
zip LIKE “9%” Selects all rows where the ZIP code begins with 9

column NOT LIKE
value — value can contain the wildcards % (which matches any string) and _ (which matches any character)
zip NOT LIKE “9%” Selects all rows where the ZIP code does not begin with 9

You can combine any of the expressions in Table 4-3 with ANDs and ORs. In some cases, you need to use parentheses to clarify the selection criteria. For instance, you can use the following query to answer your boss’s urgent need to find all the people in the Member Directory whose names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number:

SELECT lastName,firstName FROM Member
WHERE lastName LIKE “B%”
AND city = “Santa Barbara”
AND (phone LIKE “%8%” OR fax LIKE “%8%”)

Notice the parentheses in the last line. You would not get the results that your boss asked for without the parentheses. Without the parentheses, each connector would be processed in order from the first to the last, resulting in a list that includes all members whose names begin with B and who live in
Santa Barbara and whose phone numbers have an 8 in them and all members whose fax numbers have an 8 in them, whether they live in Santa Barbara or not and whether their name begins with a B or not. When the last OR is processed, members are selected whose characteristics match the expres­ sion before the OR or the expression after the OR. The expression before the

OR is connected to previous expressions by the previous ANDs and so does not stand alone, but the expression after the OR does stand alone, resulting in the selection of all members with an 8 in their fax number.

LIMIT specifies how many rows can be returned. The form for LIMIT is

LIMIT startnumber,numberofrows

The first row that you want to retrieve is startnumber, and the number of rows that you want to retrieve is numberofrows. If startnumber is not speci­ fied, 1 is assumed. To select only the first three members who live in Texas, use this query:

SELECT * FROM Member WHERE state=”TX” LIMIT 3

Some SELECT queries will find identical records, but in this example, you only want to see one — not all — of the identical records. To prevent the query from returning all the identical records, add the word DISTINCT immediately after SELECT.

Combining information from tables

In the earlier sections of this chapter, I assume that all the information you want is in a single table. However, you might want to combine information from different tables. You can do this easily in a single query.

Two words can be used in a SELECT query to combine information from two or more tables:

UNION: Rows are retrieved from one or more tables and stored together, one after the other, in a single result. For example, if your query selected
6 rows from one table and 5 rows from another table, the result would contain 11 rows.

JOIN: The tables are combined side by side, and the information is retrieved from both tables.

UNION
UNION is used to combine the results from two or more select queries. The results from each query are added to the result set following the results of the previous query. The format of the UNION query is as follows:

SELECT query UNION ALL SELECT query ...

You can combine as many SELECT queries as you need to. A SELECT query
can include any valid SELECT format, including WHERE clauses, LIMIT clauses, and so on. The rules for the queries are

All the select queries must select the same number of columns.

The columns selected in the queries must contain the same type of data.

The results set will contain all the rows from the first query followed by all the rows from the second query and so on. The column names used in the results set are the column names from the first SELECT query.

The series of SELECT queries can select different columns from the same table, but situations in which you want a new table with one column in a table followed by another column from the same table are unusual. It’s much more likely that you want to combine columns from different tables. For example, you might have a table of members who have resigned from the club and a separate table of current members. You can get a list of all mem­ bers, both current and resigned, with the following query:

SELECT lastName,firstName FROM Member UNION ALL SELECT lastName,firstName FROM OldMember

The result of this query is the last name and first name of all current mem­ bers, followed by the last name and first name of all the members who have resigned.

Depending on how you organized your data, you might have duplicate names. For instance, perhaps a member resigned, and his name is in the OldMember table — but he joined again, so his name is added to the Member table. If you don’t want duplicates, don’t include the word ALL. If ALL is not included, duplicate lines are not added to the result.

You can use ORDER BY with each SELECT query, as I discuss in the previous section, or you can use ORDER BY with a UNION query to sort all the rows in the result set. If you want ORDER BY to apply to the entire the result set, rather than just to the query that it follows, use parentheses as follows:

(SELECT lastName FROM Member UNION ALL
SELECT lastName FROM OldMember) ORDER BY lastName

The UNION statement was introduced in MySQL 4.0. It is not available in
MySQL 3.

Join
Combining tables side by side is a join. Tables are combined by matching data in a column — the column that they have in common. The combined results table produced by a join contains all the columns from both tables. For instance, if one table has two columns (memberID and height), and the second table has two columns (memberID and weight), a join results in a table with four columns: memberID (from the first table), height, memberID (from the second table), and weight.

There are two common types of joins: an inner join and an outer join. The dif­ ference between an inner join and an outer join is in the number of rows included in the results table. The results table produced by an inner join con­ tains only rows that existed in both tables. The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for the rows that did not exist in the second table. For instance, if table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain only one row: the row for Sally. However, an outer join would contain two rows — a row for Joe and a row for Sally — even though the row for Joe would have a blank field for weight.

The results table for the outer join contains all the rows for one table. If any of the rows for that table don’t exist in the second table, the columns for the second table are empty. Clearly, the contents of the results table are deter­ mined by which table contributes all its rows, requiring the second table to match it. Two kinds of outer joins control which table sets the rows and which match: a LEFT JOIN and a RIGHT JOIN.

You use different SELECT queries for an inner and the two types of outer joins. The following query is an inner join:

SELECT columnnamelist FROM table1,table2
WHERE table1.col2 = table2.col2

And these queries are outer joins:

SELECT columnnamelist FROM table1 LEFT JOIN table2
ON table1.col1=table2.col2

SELECT columnnamelist FROM table1 RIGHT JOIN table2
ON table1.col1=table2.col2

In all three queries, table1 and table2 are the tables to be joined. You can join more than two tables. In both queries, col1 and col2 are the names of the columns that are being matched to join the tables. The tables are matched based on the data in these columns. These two columns can have the same name or different names. The two columns must contain the same type of data.

As an example of inner and outer joins, consider a short form of the Pet Catalog. One table is Pet, with the two columns petName and petType hold­ ing the following data:

petName petType

Unicorn Horse Pegasus Horse Lion Cat

The second table is Color, with two columns petName and petColor holding the following data:

petName petColor

Unicorn white Unicorn silver Fish Gold

You need to ask a question that requires information from both tables. If you do an inner join with the following query:

SELECT * FROM Pet,Color WHERE Pet.petName = Color.petName

you get the following results table with four columns: petName (from Pet),
petType, petName (from Color), and petColor.

petName petType petName petColor
Unicorn
Unicorn Horse
Horse Unicorn
Unicorn white silver

Notice that only Unicorn appears in the results table — because only Unicorn was in both of the original tables, before the join. On the other hand, suppose you do a left outer join with the following query:

SELECT * FROM Pet LEFT JOIN Color
ON Pet.petName=Color.petName

You get the following results table, with the same four columns — petName (from Pet), petType, petName (from Color), and petColor — but with dif­ ferent rows:

petName petType petName petColor
Unicorn Unicorn Pegasus Lion Horse Horse Horse Cat Unicorn
Unicorn
<NULL>
<NULL> white silver
<NULL>
<NULL>

This table has four rows. It has the same first two rows as the inner join, but it has two additional rows — rows that are in the PetType table on the left but not in the Color table. Notice that the columns from the table Color are blank for the last two rows.

And, on the third hand, suppose that you do a right outer join with the fol­
lowing query:

SELECT * FROM Pet RIGHT JOIN Color
ON Pet.petName=Color.petName

You get the following results table, with the same four columns, but with still different rows:

petName petType petName petColor
Unicorn
Unicorn
<NULL> Horse
Horse
<NULL> Unicorn Unicorn Fish white silver Gold

Notice that these results contain all the rows for the Color table on the right but not for the Pet table. Notice the blanks in the columns for the Pet table, which doesn’t have a row for Fish.

The joins that I’ve talked about so far find matching entries in tables. Sometimes it’s useful to find out which rows in a table have no matching entries in another table. For example, suppose that you want to know who has never logged into your Members Only section. Because you have one table with the member’s login name and another table with the dates when the user logged in, you can ask this question by using the two tables. Doing a join and looking at all the matches to try to see who is missing might be
impossible with a large number of user names in the table. However, you can find out which login names do not have an entry in the login table with the following query:

SELECT loginName from Member LEFT JOIN Login ON Member.loginName=Login.loginName WHERE Login.loginName IS NULL

This query will give you a list of all the login names in Member that are not in the Login table.

Updating information

Changing information in an existing row is updating the information. For instance, you might need to change the address of a member because she has moved, or you might need to add a fax number that a member left blank when he originally entered his information.

The UPDATE query is very straightforward:

UPDATE tablename SET column=value,column=value,...
WHERE clause

In the SET clause, you list the columns to be updated and the new values to be inserted. List all the columns that you want to change in one query. Without a WHERE clause, the values of the column(s) would be changed in all the rows. But with the WHERE clause, you can specify which rows to update. For instance, to update an address in the Member table, use this query:

UPDATE Member SET street=”3333 Giant St”, phone=”555-555-5555”
WHERE loginName=”bigguy”

Removing information

Keep the information in your database up to date by deleting obsolete infor­
mation. You can remove a row from a table with the DELETE query:

DELETE FROM tablename WHERE clause

Be extremely careful when using DELETE. If you use a DELETE query without a WHERE clause, it will delete all the data in the table. I mean all the data. I repeat, all the data. The data cannot be recovered. This function of the DELETE query is right at the top of my don’t-try-this-at-home list.

You can delete a column from a table by using the ALTER query:

ALTER TABLE tablename DROP columnname

Or, of course, you could remove the whole thing and start over again with:

DROP TABLE tablename

or

DROP DATABASE databasename