A MySQL Tutorial

When you're formulating a query that requires logical operators, take care not to confuse the meaning of the logical AND operator with the way we use "and" in everyday speech. Suppose you want to find "presidents born in Virginia and presidents born in Massachusetts." That question is phrased using "and," which seems to imply that you'd write the query as follows:

mysql>

SELECT last_name, first_name, state FROM president

->

WHERE state='VA' AND state='MA';

Empty set (0.36 sec)

It's clear from the empty result that the query doesn't work. Why not? Because what it really means is "Select presidents who were born both in Virginia and in Massachusetts," which makes no sense. In English, you might express the query using "and," but in SQL, you connect the two conditions with OR :

mysql>

SELECT last_name, first_name, state FROM president

->

WHERE state='VA' OR state='MA';

+------------+-------------+-------+
 last_name   first_name   state 
+------------+-------------+-------+
 Washington  George       VA    
 Adams       John         MA    
 Jefferson   Thomas       VA    
 Madison     James        VA    
 Monroe      James        VA    
 Adams       John Quincy  MA    
 Harrison    William H.   VA    
 Tyler       John         VA    
 Taylor      Zachary      VA    
 Wilson      Woodrow      VA    
 Kennedy     John F       MA    
 Bush        George H.W.  MA    
+------------+-------------+-------+

This disjunction between natural language and SQL is something to be aware of, not just when formulating your own queries, but also when writing queries for other people. It's best to listen carefully as they describe what they want to retrieve, but you don't necessarily want to transcribe their descriptions into SQL using the same logical operators. For the example just described, the proper English equivalent for the query is "Select presidents who were born either in Virginia or in Massachusetts."

The NULL Value

The NULL value is special. It means "no value," so you can't assess it against known values the way you can assess two known values against each other. If you attempt to use NULL with the usual arithmetic comparison operators, the result is undefined:

mysql>

SELECT NULL < 0, NULL = 0, NULL != 0, NULL > 0;

+----------+----------+-----------+----------+
 NULL < 0  NULL = 0  NULL != 0  NULL > 0 
+----------+----------+-----------+----------+
     NULL      NULL       NULL      NULL 
+----------+----------+-----------+----------+

In fact, you can't even compare NULL against itself because the result of comparing two unknown values cannot be determined:

mysql>

SELECT NULL = NULL, NULL != NULL;

+-------------+--------------+
 NULL = NULL  NULL != NULL 
+-------------+--------------+
        NULL          NULL 
+-------------+--------------+

To perform searches for NULL values, you must use a special syntax. Instead of using = or != to test for equality or inequality, use IS NULL or IS NOT NULL . For example, you can find presidents who are still living using the following query because we have represented their death dates as NULL in the president table:

mysql>

SELECT last_name, first_name FROM president WHERE death IS NULL;

+-----------+-------------+
 last_name  first_name  
+-----------+-------------+
 Ford       Gerald R    
 Carter     James E.    
 Reagan     Ronald W.   
 Bush       George H.W. 
 Clinton    William J.  
 Bush       George W.   
+-----------+-------------+

To find names that have a suffix part, use IS NOT NULL :

mysql>

SELECT last_name, first_name, suffix

->

FROM president WHERE suffix IS NOT NULL;

+-----------+------------+--------+
 last_name  first_name  suffix 
+-----------+------------+--------+
 Carter     James E.    Jr.    
+-----------+------------+--------+

MySQL 3.23 and up has a special MySQL-specific <=> comparison operator that is true even for NULL -to- NULL comparisons. The preceding two queries can be rewritten to use this operator as follows:

mysql>

SELECT last_name, first_name FROM president WHERE death <=> NULL;

+-----------+-------------+
 last_name  first_name  
+-----------+-------------+
 Ford       Gerald R    
 Carter     James E.    
 Reagan     Ronald W.   
 Bush       George H.W. 
 Clinton    William J.  
 Bush       George W.   
+-----------+-------------+
mysql>

SELECT last_name, first_name, suffix

->

FROM president WHERE NOT (suffix <=> NULL);

+-----------+------------+--------+
 last_name  first_name  suffix 
+-----------+------------+--------+
 Carter     James E.    Jr.    
+-----------+------------+--------+

Sorting Query Results

Every MySQL user notices sooner or later that if you create a table, load some records into it, and then issue a SELECT * FROM tbl_name statement, the records tend to be retrieved in the same order in which they were inserted. That makes a certain intuitive sense and, as a result, it's natural to make the assumption that retrieval of records in insertion order is a principle that you can rely on. But you can't, because the assumption is incorrect. For example, if you delete and insert rows after loading the table initially, those actions likely will change the order in which the server returns the table's rows. (Deleting records puts "holes" of unused space in the table, which MySQL tries to fill later when you insert new records.)

The principle that you can rely on is this: There is no guarantee about the order in which the server will return rows, unless you specify one yourself. To do so, add an ORDER BY clause to the statement that defines the sort order you want. The following query returns president names, sorted lexically ( alphabetically ) by last name:

mysql>

SELECT last_name, first_name FROM president

->

ORDER BY last_name;

+------------+---------------+
 last_name   first_name    
+------------+---------------+
 Adams       John          
 Adams       John Quincy   
 Arthur      Chester A.    
 Buchanan    James         
...

You can specify whether to sort a column in ascending or descending order by using the ASC or DESC keywords after column names in the ORDER BY clause. For example, to sort president names in reverse (descending) name order, use DESC as follows:

mysql>

SELECT last_name, first_name FROM president

->

ORDER BY last_name DESC;

+------------+---------------+
 last_name   first_name    
+------------+---------------+
 Wilson      Woodrow       
 Washington  George        
 Van Buren   Martin        
 Tyler       John          
...

Ascending order is the default if you specify neither ASC nor DESC for a column name in an ORDER BY clause.

Query output can be sorted on multiple columns, and each column can be sorted in ascending or descending order independently of any other. The following query retrieves rows from the president table, sorts them by reverse state of birth, and by last name within each state:

mysql>

SELECT last_name, first_name, state FROM president

->

ORDER BY state DESC, last_name ASC;

+------------+---------------+-------+
 last_name   first_name     state 
+------------+---------------+-------+
 Arthur      Chester A.     VT    
 Coolidge    Calvin         VT    
 Harrison    William H.     VA    
 Jefferson   Thomas         VA    
 Madison     James          VA    
 Monroe      James          VA    
 Taylor      Zachary        VA    
 Tyler       John           VA    
 Washington  George         VA    
 Wilson      Woodrow        VA    
 Eisenhower  Dwight D.      TX    
 Johnson     Lyndon B.      TX    
...

If you sort a column that may contain NULL values, where will they appear in the sort order? It depends. As of MySQL 4.0.2, they'll always sort at the beginning, even if specify DESC . Prior to that, they sort at the beginning for ascending sorts and at the end for descending sorts.

If you want to ensure that NULL values will appear at a given end of the sort order, add an extra sort column that distinguishes NULL from non- NULL values. For example, to sort presidents by death date but put living presidents (those with NULL death dates) at the beginning of the sort order, use the following query:

mysql>

SELECT last_name, first_name, death FROM president

->

ORDER BY IF(death IS NULL,0,1), death;

+------------+---------------+------------+
 last_name   first_name     death      
+------------+---------------+------------+
 Ford        Gerald R       NULL       
 Carter      James E.       NULL       
 Reagan      Ronald W.      NULL       
 Bush        George H.W.    NULL       
 Clinton     William J.     NULL       
 Bush        George W.      NULL       
 Washington  George         1799-12-14 
 Adams       John           1826-07-04 
...
 Truman      Harry S.       1972-12-26 
 Johnson     Lyndon B.      1973-01-22 
 Nixon       Richard M      1994-04-22 
+------------+---------------+------------+

To put living presidents at the end instead, use the following query:

mysql>

SELECT last_name, first_name, death FROM president

->

ORDER BY IF(death IS NULL,1,0), death;

+------------+---------------+------------+
 last_name   first_name     death      
+------------+---------------+------------+
 Washington  George         1799-12-14 
 Adams       John           1826-07-04 
...
 Truman      Harry S.       1972-12-26 
 Johnson     Lyndon B.      1973-01-22 
 Nixon       Richard M      1994-04-22 
 Ford        Gerald R       NULL       
 Carter      James E.       NULL       
 Reagan      Ronald W.      NULL       
 Bush        George H.W.    NULL       
 Clinton     William J.     NULL       
 Bush        George W.      NULL       
+------------+---------------+------------+

The IF() function evaluates an expression and returns the value of its second or third argument, depending on whether the expression is true or false. For the first query, IF() evaluates to for NULL values and 1 for non- NULL values, which places all NULL values ahead of all non- NULL values. For the second query, it does the opposite , placing all NULL values after all non- NULL values. This strategy can be used as of MySQL 3.23.2, which is the first version that allows expressions in ORDER BY clauses.

Limiting Query Results

When a query returns many rows, but you want to see only a few of them, the LIMIT clause is useful, especially in conjunction with ORDER BY . MySQL allows you to limit the output of a query to the first n rows of the result that would otherwise be returned. The following query selects the five presidents who were born first:

mysql>

SELECT last_name, first_name, birth FROM president

->

ORDER BY birth LIMIT 5;

+------------+------------+------------+
 last_name   first_name  birth      
+------------+------------+------------+
 Washington  George      1732-02-22 
 Adams       John        1735-10-30 
 Jefferson   Thomas      1743-04-13 
 Madison     James       1751-03-16 
 Monroe      James       1758-04-28 
+------------+------------+------------+

If you sort in reverse order, using ORDER BY birth DESC , you'd get the five most recently born presidents instead:

mysql>

SELECT last_name, first_name, birth FROM president

->

ORDER BY birth DESC LIMIT 5;

+-----------+-------------+------------+
 last_name  first_name   birth      
+-----------+-------------+------------+
 Clinton    William J.   1946-08-19 
 Bush       George W.    1946-07-06 
 Carter     James E.     1924-10-01 
 Bush       George H.W.  1924-06-12 
 Kennedy    John F       1917-05-29 
+-----------+-------------+------------+

LIMIT also allows you to pull a section of records out of the middle of a result set. To do this, you must specify two values. The first value is the number of records to skip at the beginning of the result set, and the second is the number of records to return. The following query is similar to the previous one but returns 5 records after skipping the first 10:

mysql>

SELECT last_name, first_name, birth FROM president

->

ORDER BY birth LIMIT 10, 5;

+-----------+------------+------------+
 last_name  first_name  birth      
+-----------+------------+------------+
 Tyler      John        1790-03-29 
 Buchanan   James       1791-04-23 
 Polk       James K.    1795-11-02 
 Fillmore   Millard     1800-01-07 
 Pierce     Franklin    1804-11-23 
+-----------+------------+------------+

To pull a randomly selected record from the president table, use ORDER BY RAND() in conjunction with LIMIT :

mysql>

SELECT last_name, first_name FROM president

->

ORDER BY RAND() LIMIT 1;

+-----------+------------+
 last_name  first_name 
+-----------+------------+
 McKinley   William    
+-----------+------------+

Ordering by the result of a formula like this works for MySQL 3.23.2 and later. Prior to that, you must use a workaround that involves generating an additional column containing random numbers. See the "Overriding Optimization" section in Chapter 4 for details.

Calculating and Naming Output Column Values

Most of the preceding queries have produced output by retrieving values from tables. MySQL also allows you to calculate an output column value as the result of an expression. Expressions can be simple or complex. The following query evaluates a simple expression (a constant) and a more complex expression involving several arithmetic operations and a couple of function calls:

mysql>

SELECT 17, FORMAT(SQRT(3*3+4*4),0);

+----+-------------------------+
 17  FORMAT(SQRT(3*3+4*4),0) 
+----+-------------------------+
 17  5                       
+----+-------------------------+

Expressions can also refer to table columns:

mysql>

SELECT CONCAT(first_name,' ',last_name),CONCAT(city,', ',state)

->

FROM president;

+----------------------------------+-------------------------+
 CONCAT(first_name,' ',last_name)  CONCAT(city,', ',state) 
+----------------------------------+-------------------------+
 George Washington                 Wakefield, VA           
 John Adams                        Braintree, MA           
 Thomas Jefferson                  Albemarle County, VA    
 James Madison                     Port Conway, VA         
...

This query formats president names as a single string by concatenating first and last names separated by a space and formats birthplaces as the birth cities and states separated by a comma.

When you use an expression to calculate a column value, the expression becomes the column's name and is used for its heading. That can lead to a very wide column if the expression is long (as the preceding query illustrates). To deal with this, you can assign the column a different name using the AS name construct. Such names are called column aliases. The output from the previous query can be made more meaningful as follows:

mysql>

SELECT CONCAT(first_name,' ',last_name) AS Name,

->

CONCAT(city,', ',state) AS Birthplace

->

FROM president;

+-----------------------+-------------------------+
 Name                   Birthplace              
+-----------------------+-------------------------+
 George Washington      Wakefield, VA           
 John Adams             Braintree, MA           
 Thomas Jefferson       Albemarle County, VA    
 James Madison          Port Conway, VA         
...

If the column alias contains spaces, you'll need to put it in quotes:

mysql>

SELECT CONCAT(first_name,' ',last_name) AS 'President Name',

->

CONCAT(city,', ',state) AS 'Place of Birth'

->

FROM president;

+-----------------------+-------------------------+
 President Name         Place of Birth          
+-----------------------+-------------------------+
 George Washington      Wakefield, VA           
 John Adams             Braintree, MA           
 Thomas Jefferson       Albemarle County, VA    
 James Madison          Port Conway, VA         
...

Working with Dates

The principal thing to keep in mind when it comes to dates in MySQL is that it always represents them with the year first. July 27, 2002 is represented as '2002-07-27' . It is not represented as '07-27-2002' or as '27-07-2002' , as you might be more used to writing.

MySQL provides several ways to perform operations on dates. Some of the things you can do are as follows:

• Sort by date. (We've seen this several times already.)

• Look for particular dates or a range of dates.

• Extract parts of a date value, such as the year, month, or day.

• Calculate the difference between dates.

• Compute a date by adding or subtracting an interval from another date.

Some examples of these operations follow.

To look for particular dates, either by exact value or compared to another value, compare a DATE column to the value you're interested in:

mysql>

SELECT * FROM event WHERE date = '2002-10-01';

+------------+------+----------+
 date        type  event_id 
+------------+------+----------+
 2002-10-01  T            6 
+------------+------+----------+
mysql>

SELECT last_name, first_name, death

->

FROM president

->

WHERE death >= '1970-01-01' AND death < '1980-01-01';

+-----------+------------+------------+
 last_name  first_name  death      
+-----------+------------+------------+
 Truman     Harry S.    1972-12-26 
 Johnson    Lyndon B.   1973-01-22 
+-----------+------------+------------+

To test or retrieve parts of dates, you can use functions such as YEAR() , MONTH() , or DAYOFMONTH() . For example, I can find presidents who were born in the same month that I was (March) by looking for dates with a month value of 3:

mysql>

SELECT last_name, first_name, birth

->

FROM president WHERE MONTH(birth) = 3;

+-----------+------------+------------+
 last_name  first_name  birth      
+-----------+------------+------------+
 Madison    James       1751-03-16 
 Jackson    Andrew      1767-03-15 
 Tyler      John        1790-03-29 
 Cleveland  Grover      1837-03-18 
+-----------+------------+------------+

The query can also be written in terms of the month name:

mysql>

SELECT last_name, first_name, birth

->

FROM president WHERE MONTHNAME(birth) = 'March';

+-----------+------------+------------+
 last_name  first_name  birth      
+-----------+------------+------------+
 Madison    James       1751-03-16 
 Jackson    Andrew      1767-03-15 
 Tyler      John        1790-03-29 
 Cleveland  Grover      1837-03-18 
+-----------+------------+------------+

To be more specific—down to the day—I can combine tests for MONTH() and DAYOFMONTH() to find presidents born on my birthday:

mysql>

SELECT last_name, first_name, birth

->

FROM president WHERE MONTH(birth) = 3 AND DAYOFMONTH(birth) = 29;

+-----------+------------+------------+
 last_name  first_name  birth      
+-----------+------------+------------+
 Tyler      John        1790-03-29 
+-----------+------------+------------+

This is the kind of query you'd use to generate one of those "these famous people have birthdays today" lists such as you see in the Entertainment section of your newspaper. However, for the current date, you don't have to plug in a specific day the way the previous query did. To check for presidents born today, no matter what day of the year today is, compare their birthdays to the month and day parts of CURDATE() , which always returns the current date:

SELECT last_name, first_name, birth 
FROM president WHERE MONTH(birth) = MONTH(CURDATE())
AND DAYOFMONTH(birth) = DAYOFMONTH(CURDATE());

You can subtract one date from another, which allows you to find the interval between dates. For example, to determine which presidents lived the longest, subtract the birth date from the death date. To do this, convert birth and death to days using the TO_DAYS() function and take the difference:

mysql>

SELECT last_name, first_name, birth, death,

->

TO_DAYS(death) - TO_DAYS(birth) AS age

->

FROM president WHERE death IS NOT NULL

->

ORDER BY age DESC LIMIT 5;

+-----------+------------+------------+------------+-------+
 last_name  first_name  birth       death       age   
+-----------+------------+------------+------------+-------+
 Adams      John        1735-10-30  1826-07-04  33119 
 Hoover     Herbert C.  1874-08-10  1964-10-20  32943 
 Truman     Harry S.    1884-05-08  1972-12-26  32373 
 Madison    James       1751-03-16  1836-06-28  31150 
 Jefferson  Thomas      1743-04-13  1826-07-04  30397 
+-----------+------------+------------+------------+-------+

To convert age in days to approximate age in years, divide by 365 (the FLOOR() function used here chops off any fractional part from the age to produce an integer):

mysql>

SELECT last_name, first_name, birth, death,

->

FLOOR((TO_DAYS(death) - TO_DAYS(birth))/365) AS age

->

FROM president WHERE death IS NOT NULL

->

ORDER BY age DESC LIMIT 5;

+-----------+------------+------------+------------+------+
 last_name  first_name  birth       death       age  
+-----------+------------+------------+------------+------+
 Adams      John        1735-10-30  1826-07-04    90 
 Hoover     Herbert C.  1874-08-10  1964-10-20    90 
 Truman     Harry S.    1884-05-08  1972-12-26    88 
 Madison    James       1751-03-16  1836-06-28    85 
 Jefferson  Thomas      1743-04-13  1826-07-04    83 
+-----------+------------+------------+------------+------+

In this particular case, the age values happen to correspond to true age at death. But the calculation used in the query may not always do so, because years are not always exactly 365 days long. To calculate ages as we normally think of them, take the difference between the year parts of the dates and then subtract one if the calendar day of the death date occurs earlier than that of the birth date:

mysql>

SELECT last_name, first_name, birth, death,

->

(YEAR(death) - YEAR(birth)) - IF(RIGHT(death,5) < RIGHT(birth,5),1,0)

->

AS age

->

FROM president WHERE death IS NOT NULL

->

ORDER BY age DESC LIMIT 5;

+-----------+------------+------------+------------+------+
 last_name  first_name  birth       death       age  
+-----------+------------+------------+------------+------+
 Adams      John        1735-10-30  1826-07-04    90 
 Hoover     Herbert C.  1874-08-10  1964-10-20    90 
 Truman     Harry S.    1884-05-08  1972-12-26    88 
 Madison    James       1751-03-16  1836-06-28    85 
 Jefferson  Thomas      1743-04-13  1826-07-04    83 
+-----------+------------+------------+------------+------+

The IF() expression used here performs the calendar day test based on a simple substring comparison of the last five characters of the dates. This works for two reasons. First, MySQL treats dates as strings if you pass them to a string function—in this case, RIGHT() , which returns the rightmost n characters of a string. Second, MySQL produces dates with a fixed number of digits in each of their subparts. The comparison would not work if leading zeroes were not present for month and day values less than 10.

Taking a difference between dates is also useful for determining how far dates are from some reference date. That's how you can tell which Historical League members need to renew their memberships soon. Compute the difference between their expiration dates and the current date, and if it's less than some threshold value, a renewal will soon be needed. The following query finds memberships that are due for renewal within 60 days:

SELECT last_name, first_name, expiration FROM member 
WHERE (TO_DAYS(expiration) - TO_DAYS(CURDATE())) < 60;

To calculate one date from another, you can use DATE_ADD() or DATE_SUB() . These functions take a date and an interval and produce a new date, for example:

mysql>

SELECT DATE_ADD('1970-1-1', INTERVAL 10 YEAR);

+----------------------------------------+
 DATE_ADD('1970-1-1', INTERVAL 10 YEAR) 
+----------------------------------------+
 1980-01-01                             
+----------------------------------------+
mysql>

SELECT DATE_SUB('1970-1-1', INTERVAL 10 YEAR);

+----------------------------------------+
 DATE_SUB('1970-1-1', INTERVAL 10 YEAR) 
+----------------------------------------+
 1960-01-01                             
+----------------------------------------+

A query shown earlier in this section selected presidents who died during the 1970s, using literal dates for the endpoints of the selection range. That query can be rewritten to use a literal starting date and an ending date calculated from the starting date and an interval:

mysql>

SELECT last_name, first_name, death

->

FROM president

->

WHERE death >= '1970-1-1'

->

AND death < DATE_ADD('1970-1-1', INTERVAL 10 YEAR);

+-----------+------------+------------+
 last_name  first_name  death      
+-----------+------------+------------+
 Truman     Harry S.    1972-12-26 
 Johnson    Lyndon B.   1973-01-22 
+-----------+------------+------------+

The membership-renewal query can be written in terms of DATE_ADD() :

SELECT last_name, first_name, expiration FROM member 
WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 60 DAY);

Earlier in this chapter, a query was presented for determining which of a dentist's patients haven't been in for their checkups in a while:

SELECT last_name, first_name, last_visit FROM patient 
WHERE last_visit < DATE_SUB(CURDATE(),INTERVAL 6 MONTH);

That query may not have meant much to you then. Is it more meaningful now?

Pattern Matching

MySQL supports pattern matching operations, which allows you to select records without supplying an exact comparison value. To perform a pattern match, you use special operators ( LIKE and NOT LIKE ), and you specify a string containing wild card characters. The character ' _ ' matches any single character, and ' % ' matches any sequence of characters (including an empty sequence). Pattern matches using LIKE or NOT LIKE are not case sensitive.

The following pattern matches last names that begin with a ' W ' or ' w ' character:

mysql>

SELECT last_name, first_name FROM president

->

WHERE last_name LIKE 'W%';

+------------+------------+
 last_name   first_name 
+------------+------------+
 Washington  George     
 Wilson      Woodrow    
+------------+------------+

On the other hand, the following pattern match is erroneous:

mysql>

SELECT last_name, first_name FROM president

->

WHERE last_name = 'W%';

Empty set (0.00 sec)

The query demonstrates a common error, which is to use a pattern with an arithmetic comparison operator. The only way for such a comparison to succeed is for the column to contain exactly the string 'W%' or 'w%' .

This pattern matches last names that contain ' W ' or ' w ' anywhere in the name, not just at the beginning:

mysql>

SELECT last_name, first_name FROM president

->

WHERE last_name LIKE '%W%';

+------------+------------+
 last_name   first_name 
+------------+------------+
 Washington  George     
 Wilson      Woodrow    
 Eisenhower  Dwight D.  
+------------+------------+

This pattern matches last names that contain exactly four characters:

mysql>

SELECT last_name, first_name FROM president

->

WHERE last_name LIKE '____';

+-----------+-------------+
 last_name  first_name  
+-----------+-------------+
 Polk       James K.    
 Taft       William H.  
 Ford       Gerald R    
 Bush       George H.W. 
 Bush       George W.   
+-----------+-------------+

MySQL also provides another form of pattern matching based on regular expressions, which are described in the section that discusses the REGEXP operator in Appendix C.

Setting and Using SQL Variables

MySQL versions 3.23.6 and up allow you to set variables using query results, which provides a convenient way to save values for use in later queries. Suppose you want to find out which presidents were born before Andrew Jackson. To determine that, you can retrieve his birth date into a variable and then select other presidents with a birth date earlier than the value of the variable: ^[1]

^[1] This problem could be solved in a single query using a join, but we're not to the point of writing joins yet. Besides, sometimes it's just easier to use a variable.

mysql>

SELECT @birth := birth FROM president

->

WHERE last_name = 'Jackson' AND first_name = 'Andrew';

+-----------------+
 @birth := birth 
+-----------------+
 1767-03-15      
+-----------------+
mysql>

SELECT last_name, first_name, birth FROM president

->

WHERE birth < @birth ORDER BY birth;

+------------+------------+------------+
 last_name   first_name  birth      
+------------+------------+------------+
 Washington  George      1732-02-22 
 Adams       John        1735-10-30 
 Jefferson   Thomas      1743-04-13 
 Madison     James       1751-03-16 
 Monroe      James       1758-04-28 
+------------+------------+------------+

Variables are named using @ name syntax and assigned a value in a SELECT statement using an expression of the form @name := value . The first query therefore looks up the birth date for Andrew Jackson and assigns it to the @birth variable. (The result of the SELECT still is displayed; assigning a query result to a variable doesn't cause the query output to be suppressed.) The second query refers to the variable and uses its value to find other president records with a lesser birth value.

Variables also can be assigned using a SET statement, although in this case either = or := are allowable as the assignment operator:

mysql>

SET @one_week_ago = DATE_SUB(CURDATE(),INTERVAL 7 DAY);

mysql>

SELECT CURDATE(), @one_week_ago;

+------------+---------------+
 CURDATE()   @one_week_ago 
+------------+---------------+
 2002-09-03  2002-08-27    
+------------+---------------+

Generating Summaries

One of the most useful things MySQL can do for you is to boil down lots of raw data and summarize it. MySQL becomes a powerful ally when you learn to use it to generate summaries because that is an especially tedious, time-consuming , error-prone activity when done manually.

One simple form of summarizing is to determine which unique values are present in a set of values. Use the DISTINCT keyword to remove duplicate rows from a result. For example, the different states in which presidents have been born can be found as follows:

mysql>

SELECT DISTINCT state FROM president ORDER BY state;

+-------+
 state 
+-------+
 AR    
 CA    
 CT    
 GA    
 IA    
 IL    
 KY    
 MA    
 MO    
 NC    
 NE    
 NH    
 NJ    
 NY    
 OH    
 PA    
 SC    
 TX    
 VA    
 VT    
+-------+

Another form of summarizing involves counting, using the COUNT() function. If you use COUNT(*) , it tells you the number of rows selected by your query. If a query has no WHERE clause, COUNT(*) tells you the number of rows in your table. The following query shows how many members are listed in the Historical League membership table:

mysql>

SELECT COUNT(*) FROM member;

+----------+
 COUNT(*) 
+----------+
      102 
+----------+

If a query does have a WHERE clause, COUNT(*) tells you how many rows the clause matches. The following query shows how many quizzes you have given to your class so far:

mysql>

SELECT COUNT(*) FROM event WHERE type = 'Q';

+----------+
 COUNT(*) 
+----------+
        4 
+----------+

COUNT(*) counts every row selected. By contrast, COUNT( col_name ) counts only non- NULL values. The following query demonstrates these differences:

mysql>

SELECT COUNT(*),COUNT(email),COUNT(expiration) FROM member;

+----------+--------------+-------------------+
 COUNT(*)  COUNT(email)  COUNT(expiration) 
+----------+--------------+-------------------+
      102            80                 96 
+----------+--------------+-------------------+

This shows that while the member table has 102 records, only 80 of them have a value in the email column. It also shows that six members have a lifetime membership. (A NULL value in the expiration column indicates a lifetime membership, and because 96 out of 102 records are not NULL , that leaves six.)

As of MySQL 3.23.2, you can combine COUNT() with DISTINCT to count the number of distinct values in a result. For example, to count the number of different states in which presidents have been born, do the following:

mysql>

SELECT COUNT(DISTINCT state) FROM president;

+-----------------------+
 COUNT(DISTINCT state) 
+-----------------------+
                    20 
+-----------------------+

You can produce an overall count of values in a column or break down the counts by categories. For example, you may know the overall number of students in your class as a result of running the following query:

mysql>

SELECT COUNT(*) FROM student;

+----------+
 COUNT(*) 
+----------+
       31 
+----------+

But how many students are boys and how many are girls? One way to find out is by asking for a count for each sex separately:

mysql>

SELECT COUNT(*) FROM student WHERE sex='f';

+----------+
 COUNT(*) 
+----------+
       15 
+----------+
mysql>

SELECT COUNT(*) FROM student WHERE sex='m';

+----------+
 COUNT(*) 
+----------+
       16 
+----------+

However, although that approach works, it's tedious and not really very well suited for columns that might have several different values. Consider how you'd determine the number of presidents born in each state this way. You'd have to find out which states are represented so as not to miss any ( SELECT DISTINCT state FROM president ) and then run a SELECT COUNT(*) query for each state. That is clearly something you don't want to do.

Fortunately, MySQL can count, using a single query, how many times each distinct value occurs in a column. For our student list, we can count boys and girls as follows:

mysql>

SELECT sex, COUNT(*) FROM student GROUP BY sex;

+-----+----------+
 sex  COUNT(*) 
+-----+----------+
 F          15 
 M          16 
+-----+----------+

The same form of query tells us how many presidents were born in each state:

mysql>

SELECT state, COUNT(*) FROM president GROUP BY state;

+-------+----------+
 state  COUNT(*) 
+-------+----------+
 AR            1 
 CA            1 
 CT            1 
 GA            1 
 IA            1 
 IL            1 
 KY            1 
 MA            4 
 MO            1 
 NC            2 
 NE            1 
 NH            1 
 NJ            1 
 NY            4 
 OH            7 
 PA            1 
 SC            1 
 TX            2 
 VA            8 
 VT            2 
+-------+----------+

When you count values this way, the GROUP BY clause is necessary; it tells MySQL how to cluster values before counting them. You'll just get an error if you omit it.

The use of COUNT(*) with GROUP BY to count values has a number of advantages over counting occurrences of each distinct column value individually:

• You don't have to know in advance what values are present in the column you're summarizing.

• You need only a single query, not several.

• You get all the results with a single query, so you can sort the output.

The first two advantages are important for expressing queries more easily. The third advantage is important because it affords you flexibility in displaying your results. By default, MySQL uses the columns named in the GROUP BY to sort the results, but you can specify an ORDER BY clause to sort in a different order. For example, if you want number of presidents grouped by state of birth but sorted with the most well-represented states first, you can use an ORDER BY clause as follows:

mysql>

SELECT state, COUNT(*) AS count FROM president

->

GROUP BY state ORDER BY count DESC;

+-------+-------+
 state  count 
+-------+-------+
 VA         8 
 OH         7 
 MA         4 
 NY         4 
 NC         2 
 VT         2 
 TX         2 
 SC         1 
 NH         1 
 PA         1 
 KY         1 
 NJ         1 
 IA         1 
 MO         1 
 CA         1 
 NE         1 
 GA         1 
 IL         1 
 AR         1 
 CT         1 
+-------+-------+

When the column you want to sort by is determined by a summary function, you can give the column an alias and refer to the alias in the ORDER BY clause. The preceding query demonstrates this, where the COUNT(*) column is aliased as count. Another way to refer to such a column is by its position in the output. The previous query could have been written as follows instead:

SELECT state, COUNT(*) FROM president 
GROUP BY state ORDER BY 2 DESC;

I don't find that referring to columns by position leads to very understandable queries. Also, if you add, remove, or reorder output columns, you must remember to check the ORDER BY clause and fix the column number if it has changed. Aliases don't have this problem.

If you want to group results using GROUP BY with a calculated column, you can refer to it using an alias or column position, just as with ORDER BY . The following query determines how many presidents were born in each month of the year:

mysql>

SELECT MONTH(birth) AS Month, MONTHNAME(birth) AS Name,

->

COUNT(*) AS count

->

FROM president GROUP BY Name ORDER BY Month;

+-------+-----------+-------+
 Month  Name       count 
+-------+-----------+-------+
     1  January        4 
     2  February       4 
     3  March          4 
     4  April          4 
     5  May            2 
     6  June           1 
     7  July           4 
     8  August         4 
     9  September      1 
    10  October        6 
    11  November       5 
    12  December       3 
+-------+-----------+-------+

Using column positions , the query would be written as follows:

SELECT MONTH (birth), MONTHNAME(birth), COUNT(*) 
FROM president GROUP BY 2 ORDER BY 1;

COUNT() can be combined with ORDER BY and LIMIT to find, for example, the four most well-represented states in the president table:

mysql>

SELECT state, COUNT(*) AS count FROM president

->

GROUP BY state ORDER BY count DESC LIMIT 4;

+-------+-------+
 state  count 
+-------+-------+
 VA         8 
 OH         7 
 MA         4 
 NY         4 
+-------+-------+

If you don't want to limit query output with a LIMIT clause but rather by looking for particular values of COUNT() , use a HAVING clause. HAVING is similar to WHERE in that it specifies conditions that must be satisfied by output rows. It differs from WHERE in that it can refer to the results of summary functions like COUNT() . The following query will tell you which states are represented by two or more presidents:

mysql>

SELECT state, COUNT(*) AS count FROM president

->

GROUP BY state HAVING count > 1 ORDER BY count DESC;

+-------+-------+
 state  count 
+-------+-------+
 VA         8 
 OH         7 
 MA         4 
 NY         4 
 NC         2 
 VT         2 
 TX         2 
+-------+-------+

More generally, this is the type of query to run when you want to find duplicated values in a column (or to find non- duplicated values, use HAVING count = 1 .)

There are summary functions other than COUNT() . The MIN() , MAX() , SUM() , and AVG() functions are useful for determining the minimum, maximum, total, and average values in a column. You can even use them all at the same time. The following query shows various numeric characteristics for each quiz and test you've given. It also shows how many scores go into computing each of the values. (Some students may have been absent and are not counted.)

mysql>

SELECT

->

event_id,

->

MIN(score) AS minimum,

->

MAX(score) AS maximum,

->

MAX(score)-MIN(score)+1 AS range,

->

SUM(score) AS total,

->

AVG(score) AS average,

->

COUNT(score) AS count

->

FROM score

->

GROUP BY event_id;

+----------+---------+---------+-------+-------+---------+-------+
 event_id  minimum  maximum  range  total  average  count 
+----------+---------+---------+-------+-------+---------+-------+
        1        9       20     12    439  15.1379     29 
        2        8       19     12    425  14.1667     30 
        3       60       97     38   2425  78.2258     31 
        4        7       20     14    379  14.0370     27 
        5        8       20     13    383  14.1852     27 
        6       62      100     39   2325  80.1724     29 
+----------+---------+---------+-------+-------+---------+-------+

This information might be more meaningful if you knew whether the event_id values represented quizzes or tests, of course. However, to produce that information, we need to consult the event table as well; we'll revisit this query in the next section, "Retrieving Information from Multiple Tables."

Summary functions are fun to play with because they're so powerful, but it's easy to get carried away with them. Consider the following query:

mysql>

SELECT

->

state AS State,

->

AVG((TO_DAYS(death)-TO_DAYS(birth))/365) AS Age

->

FROM president WHERE death IS NOT NULL

->

GROUP BY state ORDER BY Age;

+-------+-----------+
 State  Age       
+-------+-----------+
 KY     56.208219 
 VT     58.852055 
 NC     60.141096 
 OH     62.866145 
 NH     64.917808 
 NY     69.342466 
 NJ     71.315068 
 TX     71.476712 
 MA     72.642009 
 VA     72.822945 
 PA     77.158904 
 SC     78.284932 
 CA     81.336986 
 MO     88.693151 
 IA     90.254795 
+-------+-----------+

The query selects presidents who have died, groups them by state of birth, figures out their age at time of death, computes the average age (per state), and then sorts the results by average age. In other words, the query determines, for non-living presidents, the average age of death by state of birth.

And what does that demonstrate ? It shows only that you can write the query. It certainly doesn't show that the query is worth writing. Not all things you can do with a database are equally meaningful; nevertheless, people sometimes go query-happy when they find out what they can do with their database. This may account for the rise of increasingly esoteric (and pointless) statistics on televised sporting events over the last few years. The sports statisticians can use their databases to figure out everything you'd ever want to know about a team and also everything you'd never want to know. Do you really care which third-string quarterback holds the record for most interceptions on third down when his team is leading by more than 14 points with the ball inside the 15-yard line in the last two minutes of the second quarter?

Retrieving Information from Multiple Tables

The queries we've written so far have pulled data from a single table. But MySQL is capable of working much harder for you. I've mentioned before that the power of a relational DBMS lies in its ability to relate one thing to another because that allows you to combine information from multiple tables to answer questions that can't be answered from individual tables alone. This section describes how to write queries that do that.

When you select information from multiple tables, you're performing an operation called a join. That's because you're producing a result by joining the information from one table to the information in another. This is done by matching up common values in the tables.

Let's work through an example. Earlier, in "Tables for the Grade-Keeping Project" section, a query to retrieve quiz or test scores for a given date was presented without explanation. Now it's time for the explanation. The query actually involves a three-way join, so we'll build up to it in two steps. In the first step, we construct a query to select scores for a given date as follows:

mysql>

SELECT student_id, date, score, type

->

FROM event, score

->

WHERE date = '2002-09-23'

->

AND event.event_id = score.event_id;

+------------+------------+-------+------+
 student_id  date        score  type 
+------------+------------+-------+------+
          1  2002-09-23     15  Q    
          2  2002-09-23     12  Q    
          3  2002-09-23     11  Q    
          5  2002-09-23     13  Q    
          6  2002-09-23     18  Q    
...

The query works by finding the event record with the given date and then using the event ID in that record to locate scores that have the same event ID. For each matching event record and score record combination, the student ID, score, date, and event type are displayed.

The query differs from others we have written in two important respects:

• The FROM clause names more than one table because we're retrieving data from more than one table:

  FROM event, score
  

• The WHERE clause specifies that the event and score tables are joined by matching up the event_id values in each table:

  WHERE ... event.event_id = score.event_id
  

Notice how we refer to the event_id columns using tbl_name.col_name syntax so that MySQL knows to which tables we're referring. ( event_id occurs in both tables, so it's ambiguous if used without a table name to qualify it.) The other columns in the query ( date , score , type ) can be used without a table qualifier because they appear in only one of the tables and thus are unambiguous.

I generally prefer to qualify every column in a join to make it clearer (more explicit) which table each column is part of, and that's how I'll write joins from now on. In fully qualified form, the query looks like the following:

SELECT score.student_id, event.date, score.score, event.type 
FROM event, score
WHERE event.date = '2002-09-23'
AND event.event_id = score.event_id;

The first-stage query uses the event table to map a date to an event ID and then uses the ID to find the matching scores in the score table. Output from the query contains student_id values, but names would be more meaningful. By using the student table, we can map student IDs onto names, which is the second step. Name display is accomplished using the fact that the score and student tables both have student_id columns, allowing the records in them to be linked. The resulting query is as follows:

mysql>

SELECT student.name, event.date, score.score, event.type

->

FROM event, score, student

->

WHERE event.date = '2002-09-23'

->

AND event.event_id = score.event_id

->

AND score.student_id = student.student_id;

+-----------+------------+-------+------+
 name       date        score  type 
+-----------+------------+-------+------+
 Megan      2002-09-23     15  Q    
 Joseph     2002-09-23     12  Q    
 Kyle       2002-09-23     11  Q    
 Abby       2002-09-23     13  Q    
 Nathan     2002-09-23     18  Q    
...

This query differs from the previous one as follows:

• The student table is added to the FROM clause because it is used in addition to the event and score tables.

• The student_id column was unambiguous before, so it was possible to refer to it in either unqualified ( student_id ) or qualified ( score.student_id ) form. Now it is ambiguous because it is present in both the score and student tables, so it must be qualified as score.student_id or student.student_id to make it clear which table to use.

• The WHERE clause has an additional term specifying that score table records are matched against student table records based on student ID:

  WHERE ... score.student_id = student.student_id
  

• The query displays the student name rather than the student ID. (You could display both if you wanted to, of course.)

With this query, you can plug in any date and get back the scores for that date, complete with student names and the score type. You don't have to know anything about student IDs or event IDs. MySQL takes care of figuring out the relevant ID values and using them to match up table rows automatically.

Another task the grade-keeping project involves is summarizing student absences. Absences are recorded by student ID and date in the absence table. To get student names (not just IDs), we need to join the absence table to the student table, based on the student_id value. The following query lists student ID number and name along with a count of absences:

mysql>

SELECT student.student_id, student.name,

->

COUNT(absence.date) as absences

->

FROM student, absence

->

WHERE student.student_id = absence.student_id

->

GROUP BY student.student_id;

+------------+-------+----------+
 student_id  name   absences 
+------------+-------+----------+
          3  Kyle          1 
          5  Abby          1 
         10  Peter         2 
         17  Will          1 
         20  Avery         1 
+------------+-------+----------+

Note: Although I'm supplying a qualifier in the GROUP BY clause, it isn't strictly necessary for this query. GROUP BY refers to output columns, and there is only one such column named student_id , so MySQL knows which one you mean.

The output produced by the query is fine if we want to know only which students had absences. But if we turn in this list to the school office, they may say, "What about the other students? We want a value for every student." That's a slightly different question. It means we want to know the number of absences, even for students that had none. Because the question is different, the query is different as well.

To answer the question, we can use LEFT JOIN rather than a regular join. LEFT JOIN tells MySQL to produce a row of output for each row selected from the table named first in the join (that is, the table named to the left of the LEFT JOIN keywords). By naming the student table first, we'll get output for every student, even those who are not represented in the absence table. To write this query, use LEFT JOIN between the tables named in the FROM clause (rather than separating them by a comma) and add an ON clause that says how to match up records in the two tables. The query is as follows:

mysql>

SELECT student.student_id, student.name,

->

COUNT(absence.date) as absences

->

FROM student LEFT JOIN absence

->

ON student.student_id = absence.student_id

->

GROUP BY student.student_id;

+------------+-----------+----------+
 student_id  name       absences 
+------------+-----------+----------+
          1  Megan             0 
          2  Joseph            0 
          3  Kyle              1 
          4  Katie             0 
          5  Abby              1 
          6  Nathan            0 
          7  Liesl             0 
...

Earlier, in the "Generating Summaries" section, we ran a query that produced a numeric characterization of the data in the score table. Output from that query listed event ID but did not include event dates or types, because we didn't know then how to join the score table to the event table to map the IDs onto dates and types. Now we do. The following query is similar to one run earlier, but shows the dates and types rather than simply the numeric event IDs:

mysql>

SELECT

->

event.date,event.type,

->

MIN(score.score) AS minimum,

->

MAX(score.score) AS maximum,

->

MAX(score.score)-MIN(score.score)+1 AS range,

->

SUM(score.score) AS total,

->

AVG(score.score) AS average,

->

COUNT(score.score) AS count

->

FROM score, event

->

WHERE score.event_id = event.event_id

->

GROUP BY event.date;

+------------+------+---------+---------+-------+-------+---------+-------+
 date        type  minimum  maximum  range  total  average  count 
+------------+------+---------+---------+-------+-------+---------+-------+
 2002-09-03  Q           9       20     12    439  15.1379     29 
 2002-09-06  Q           8       19     12    425  14.1667     30 
 2002-09-09  T          60       97     38   2425  78.2258     31 
 2002-09-16  Q           7       20     14    379  14.0370     27 
 2002-09-23  Q           8       20     13    383  14.1852     27 
 2002-10-01  T          62      100     39   2325  80.1724     29 
+------------+------+---------+---------+-------+-------+---------+-------+

You can use functions, such as COUNT() and AVG() , to produce a summary over multiple columns, even if the columns come from different tables. The following query determines the number of scores and the average score for each combination of event date and student sex:

mysql>

SELECT event.date, student.sex,

->

COUNT(score.score) AS count, AVG(score.score) AS average

->

FROM event, score, student

->

WHERE event.event_id = score.event_id

->

AND score.student_id = student.student_id

->

GROUP BY event.date, student.sex;

+------------+-----+-------+---------+
 date        sex  count  average 
+------------+-----+-------+---------+
 2002-09-03  F       14  14.6429 
 2002-09-03  M       15  15.6000 
 2002-09-06  F       14  14.7143 
 2002-09-06  M       16  13.6875 
 2002-09-09  F       15  77.4000 
 2002-09-09  M       16  79.0000 
 2002-09-16  F       13  15.3077 
 2002-09-16  M       14  12.8571 
 2002-09-23  F       12  14.0833 
 2002-09-23  M       15  14.2667 
 2002-10-01  F       14  77.7857 
 2002-10-01  M       15  82.4000 
+------------+-----+-------+---------+

We can use a similar query to perform one of the grade-keeping project tasks —computing the total score per student at the end of the semester. The query looks like this:

SELECT student.student_id, student.name, 
SUM(score.score) AS total, COUNT(score.score) AS n
FROM event, score, student
WHERE event.event_id = score.event_id
AND score.student_id = student.student_id
GROUP BY score.student_id
ORDER BY total;

There is no requirement that a join be performed between different tables. It might seem odd at first, but you can join a table to itself. For example, you can determine whether any presidents were born in the same city by checking each president's birthplace against every other president's birthplace:

mysql>

SELECT p1.last_name, p1.first_name, p1.city, p1.state

->

FROM president AS p1, president AS p2

->

WHERE p1.city = p2.city AND p1.state = p2.state

->

AND (p1.last_name != p2.last_name OR p1.first_name != p2.first_name)

->

ORDER BY state, city, last_name;

+-----------+-------------+-----------+-------+
 last_name  first_name   city       state 
+-----------+-------------+-----------+-------+
 Adams      John Quincy  Braintree  MA    
 Adams      John         Braintree  MA    
+-----------+-------------+-----------+-------+

There are two tricky things about this query:

• It's necessary to refer to two instances of the same table, so we create table aliases ( p1 and p2 ) and use them to disambiguate references to the table's columns.

• Every president's record matches itself, but we don't want to see that in the output. The second line of the WHERE clause disallows matches of a record to itself by making sure the records being compared are for different presidents.

A similar query finds presidents who were born on the same day. However, birth dates cannot be compared directly because that would miss presidents who were born in different years. Instead, use MONTH() and DAYOFMONTH() to compare month and day of the birth date:

mysql>

SELECT p1.last_name, p1.first_name, p1.birth

->

FROM president AS p1, president AS p2

->

WHERE MONTH(p1.birth) = MONTH(p2.birth)

->

AND DAYOFMONTH(p1.birth) = DAYOFMONTH(p2.birth)

->

AND (p1.last_name != p2.last_name OR p1.first_name != p2.first_name)

->

ORDER BY p1.last_name;

+-----------+------------+------------+
 last_name  first_name  birth      
+-----------+------------+------------+
 Harding    Warren G.   1865-11-02 
 Polk       James K.    1795-11-02 
+-----------+------------+------------+

Using DAYOFYEAR() rather than the combination of MONTH() and DAYOFMONTH() would result in a simpler query, but it would produce incorrect results when comparing dates from leap years to dates from non-leap years.

The joins performed thus far have combined information from tables that have some meaningful logical relationship, but meaningfulness is in the eye of the beholder—that is, you. MySQL doesn't know (or care) whether or not the joined tables have anything to do with each other. For example, you can join the event table to the president table to find out whether you gave any quizzes or tests on a president's birthday:

mysql>

SELECT president.last_name, president.first_name,

->

president.birth, event.type

->

FROM president, event

->

WHERE MONTH(president.birth) = MONTH(event.date)

->

AND DAYOFMONTH(president.birth) = DAYOFMONTH(event.date);

+-----------+------------+------------+------+
 last_name  first_name  birth       type 
+-----------+------------+------------+------+
 Carter     James E.    1924-10-01  T    
+-----------+------------+------------+------+

It turns out you did. But so what? This illustrates that MySQL will happily crank out results, whether they make any sense or not. Just because you're using a computer, it doesn't automatically mean that results from a query are useful or worthwhile. Fortunately or unfortunately, we still must think about what we're doing.

Deleting or Updating Existing Records

Sometimes you want to get rid of records or change their contents. The DELETE and UPDATE statements let you do this. This section discusses how to use them.

The DELETE statement has the following form:

DELETE FROM

tbl_name

WHERE

which records to delete;

The WHERE clause that specifies which records should be deleted is optional. But if you leave it out, all records in the table are deleted, which means the simplest DELETE statement is also the most dangerous:

DELETE FROM

tbl_name;

That query wipes out the table's contents entirely, so be careful with it! To delete specific records, use the WHERE clause to select the records in which you're interested. This is similar to using a WHERE clause in a SELECT statement to avoid selecting the entire table. For example, to specifically delete from the president table only those presidents born in Ohio, use the following query:

mysql>

DELETE FROM president WHERE state='OH';

Query OK, 7 rows affected (0.00 sec)

If you're not really sure which records a DELETE statement will remove, it's often a good idea to test the WHERE clause first by using it with a SELECT statement to find out which records it matches. This can help you ensure that you'll actually delete the records you intend (and only those records). Suppose you want to delete the record for Teddy Roosevelt. Would the following query do the job?

DELETE FROM president WHERE last_name='Roosevelt';

Yes, in the sense that it would delete the record you have in mind. No, in the sense that it also would delete the record for Franklin Roosevelt. It's safer to check the WHERE clause with a SELECT statement first, like this:

mysql>

SELECT last_name, first_name FROM president

->

WHERE last_name='Roosevelt';

+-----------+-------------+
 last_name  first_name  
+-----------+-------------+
 Roosevelt  Theodore    
 Roosevelt  Franklin D. 
+-----------+-------------+

From that you can see the need to be more specific:

mysql>

SELECT last_name, first_name FROM president

->

WHERE last_name='Roosevelt' AND first_name='Theodore';

+-----------+------------+
 last_name  first_name 
+-----------+------------+
 Roosevelt  Theodore   
+-----------+------------+

Now you know the proper WHERE clause to select the desired record, so the DELETE query can be constructed correctly:

mysql>

DELETE FROM president

->

WHERE last_name='Roosevelt' AND first_name='Theodore';

This seems like a lot of work to delete a record, doesn't it? Better safe than sorry! (This is the type of situation in which you'll want to minimize typing through the use of copy and paste or input line-editing techniques. See the "Tips for Interacting with mysql" section later in this chapter for more information.)

To modify existing records, use UPDATE , which has the following form:

UPDATE

tbl_name

SET

which columns to change

WHERE

which records to update;

The WHERE clause is just as for DELETE . It's optional, so if you don't specify one, every record in the table will be updated. For example, the following query changes the name of each of your students to George:

mysql>

UPDATE student SET name='George';

Obviously, you must be careful with queries like that, so normally you'll add a WHERE clause to be more specific about which records to update. Suppose you recently added a new member to the Historical League but filled in only a few columns of his entry:

mysql>

INSERT INTO member (last_name,first_name)

->

VALUES('York','Jerome');

Then you realize you forgot to set his membership expiration date. You can fix that with an UPDATE statement that includes an appropriate WHERE clause to identify which record to change:

mysql>

UPDATE member

->

SET expiration='2001-7-20'

->

WHERE last_name='York' AND first_name='Jerome';

You can update multiple columns with a single statement. The following UPDATE modifies Jerome's email and postal addresses:

mysql>

UPDATE member

->

SET email='jeromey@aol.com',street='123 Elm St',city='Anytown',

->

state='NY',zip='01003'

->

WHERE last_name='York' AND first_name='Jerome';

You can also "unset" a column by setting its value to NULL (assuming the column allows NULL values). If at some point in the future Jerome later decides to pay the big membership renewal fee that allows him to become a lifetime member, you can mark his record that way by setting his expiration date to NULL ("never expires"):

mysql>

UPDATE member

->

SET expiration=NULL

->

WHERE last_name='York' AND first_name='Jerome';

With UPDATE , just as for DELETE , it's not a bad idea to test a WHERE clause using a SELECT statement to make sure you're choosing the right records to update. If your selection criteria are too narrow or too broad, you'll update too few or too many records.

If you've tried the queries in this section, you'll have deleted and modified records in the sampdb tables. Before proceeding to the next section, you should undo those changes. Do that by reloading the tables using the instructions at the end of the "Adding New Records" section earlier in this chapter.