select * from users where zip <> â€˜11111â€™ or state = â€˜11111â€™;
But in fact, row 9 will not be returned by the query. Null values will test neither
true nor false to any of these operators. Instead, to deal with null values, you will
need to make use of the is null or is not null predicates.
To get the previous query to work as we had intended youâ€™d need to augment
your original query, as follows:
select * from users
where zip <> â€˜11111â€™ or
zip = â€˜11111â€™ or
zip is null
Or if you want to find all the rows where zip contains any value (except null) you
can use the following:
select * from users where zip is not null;
70 Part I: Working with MySQL
At times, your query will contain duplicate data. For instance, if your goal is to see
all the cities in California, your first instinct might be to run a query like select
city, state from users where state=â€™CAâ€™. But look at the result returned in
Figure 3-3: Results of query using select city, state from users where state=â€™CAâ€™
Notice that the first three rows are identical. You could use PHP to sort through
the identical results and return only unique city names in California, but that would
be a fair amount of scripting. You can get what you want directly from the data-
base by using select distinct. When you use distinct, the MySQL engine will
remove rows with identical results. So here the better query is select distinct
city, state from users where state=â€™CAâ€™, which returns the data in Figure 3-4,
which is exactly what you want.
You can also choose values within a range by using the between predicate. The
between predicate works for numeric values as well as dates. In the following
query, lastchanged is a timestamp column. If you want to find the people who
signed up on June 26, 2002, you could use this query:
select * from users where lastchanged between 20020626000000 and
Chapter 3: Inserting, Editing, and Selecting Data 71
Figure 3-4: Results of query using select distinct city, state from users where state=â€™CAâ€™
This is a shorthand way of saying:
select * from users where lastchanged >= 20020626999999 and
lastchanged <= 20020626335959;
Remember that the default timestamp column type stores dates in the form
YYYYMMDDHHMMSS, so to get all entries for a single day you need to start your range
at midnight (00:00:00) and end it at 11:59:59 p.m. (23:59:59).
You can also use between on text strings. If you wish to list all the last names
that start with the letters A through G, the following query would work. Note that
it will not include names that start with A.
select * from users where lname between â€˜aâ€™ and â€˜gâ€™;
USING in/not in
The in predicate is helpful if a single column that can be returned has several pos-
sible values. If you want to query the users table to get all the states in New
England, you could write the query like this:
select * from users
where state = â€˜RIâ€™ or
state = â€˜NHâ€™ or
state = â€˜VTâ€™ or
72 Part I: Working with MySQL
state = â€˜MAâ€™ or
state = â€˜MEâ€™
Using in, you can specify a set of possible values and simplify this statement.
The following query achieves the same result:
select * from users
where state in (â€˜RIâ€™, â€˜NHâ€™, â€˜VTâ€™, â€˜MAâ€™, â€˜MEâ€™);
If you want the same effect in reverse you can use the not in predicate. To get
a listing of all people in the table not living in New England, simply throw in the
select * from users where
state not in (â€˜RIâ€™, â€˜NHâ€™, â€˜VTâ€™, â€˜MAâ€™, â€˜MEâ€™);
Of course there will be occasions when you are searching for a string, but arenâ€™t
exactly sure what the string looks like. In cases like these you will need to use wild-
card characters. In order to use wildcards, you need the like predicate.
Two wildcard characters are available: the underscore (_) and the percent sign
(%). The underscore stands for a single character; the percent sign represents any
number of characters, including none.
So, for example, if you were looking for someone with the first name of Daniel
or Danny or Dan, you would use the percent sign:
select * from users where fname like â€˜Dan%â€™;
Note that because the percent sign will match on zero characters, the preceding
query matches the name Dan.
However, if for some odd reason you need to find all of the people in your data-
base with four-letter first names beginning with the letter J, youâ€™d construct your
query like this (note that three underscores follow the J):
select * from users where fname like â€˜J___â€™;
The three underscores will match any characters and return names like Jean,
John, and Jack. Jay and Johnny will not be returned.
In MySQL the like comparison is not case-sensitive.This makes it quite dif-
ferent from most SQL implementations.
Chapter 3: Inserting, Editing, and Selecting Data 73
There is one thing you should always keep in mind when working with relational
databases: The storage of rows in any table is completely arbitrary. In general,
youâ€™ll have no idea of the order in which your database has decided to put the rows
youâ€™ve inserted. When it matters, you can specify the order of rows returned in your
query by tacking order by on the end of it.
The order by command can sort by any column type: alphabetical, chronolog-
ical, or numeric. In addition, you can sort in either ascending or descending order
by placing asc or desc, respectively, after order by. If neither is included, asc is
used by default.
To alphabetize a list of the entries in the table, you probably want to make sure
that the entries were sorted by both the fname and lname columns:
select * from users order by lname, fname;
You can sort by as many columns as you wish, and you can mix the asc and
desc as necessary. The following query isnâ€™t particularly useful, but it is possible:
select * from users order by lname asc, fname desc;
The limit predicate will restrict the number of rows returned from your query. It
enables you to specify both the starting row and the number of rows you want
returned. To get the first five rows from the table, run the following query:
select * from users limit 0,5;
To find the first five rows alphabetically, you can use limit with order by:
select * from users order by lname, fname limit 0,5;
Youâ€™ll probably notice that the numbering is like arrays â€” the first row is row 0.
To get the second five rows of the table, youâ€™d run the following:
select * from users limit 5,5;
The limit predicate is particularly useful in situations where you want to
restrict the display on any one page. Youâ€™ll see the use of limit throughout this
book. Even Chapter 8, which describes the first application in this book, uses limit.
Itâ€™s worth noting that LIMIT n is the same as LIMIT 0, n and that negative values
are illegal after LIMIT.
74 Part I: Working with MySQL
group by and aggregate functions
Remember back to when we were talking about using select with distinct and
how that removes rows you donâ€™t need? That may have seemed pretty cool, but itâ€™s
nothing compared to what you can get out of the group by predicate and its asso-
ciated aggregate functions.
Consider this task: You wish to know the number of entries from each state in
the database (for example, six from California, seven from New York, two from
Vermont). If you did a select distinct state from users order by state
query, you would get a listing of each state in the database, but there would be no
way to get the numbers. As MySQL goes through the table to process the query it
simply skips over rows that would return identical values.
However, with group by, MySQL creates a temporary table where it keeps all the
information on the rows and columns fitting your criteria. This allows the engine to
perform some very important tasks on the temporary table. Probably the easiest
way to show what group by can do is by showing one of the aggregate functions.
Weâ€™ll start with count().
MySQL may not actually create a temporary table for each group by; how-
ever, the actual inner workings of a group by are pretty complex, and this
is a good way to think about what MySQL is doing.
Once again, the goal of your query is to find out the number of people from each
state in your users table. To do that you will use group by with count().
Remember that when the group by clause is used you can imagine MySQL cre-
ating a temporary table where it assembles like rows. The count() function then
(you guessed it) counts the number of rows in each of the groups. Check out the fol-
lowing query and the result returned in Figure 3-5:
select state, count(*) from users group by state;
Here the asterisk (*) indicates that all rows within the group should be counted. The
count(*) function is also handy for getting the total number of rows in a table.
select count(*) from users;
Within a group by, you can also indicate a specific field that is to be counted.
count will look for the number of non-null values. Take, for example, the table in
Chapter 3: Inserting, Editing, and Selecting Data 75
Figure 3-5: Results of a query using select state, count(*) from users group by state
Figure 3-6: The users_ages table
76 Part I: Working with MySQL
If youâ€™re the type thatâ€™s really into statistics, you can use this table to figure out
what percentage from each city feels comfortable indicating its age. First, you need
a count of all the entries from each specific city and state; following that you need a
count of all the non-null values in the age field.
select city, state, count(*), count(age) from user_ages
group by state, city;
From the result in Figure 3-7, you can see that Chicagoans are far more forth-
coming than those from the coasts.
Figure 3-7: Results of query using count() function
There will be times, particularly when youâ€™re working with functions, when the
column name returned by the query isnâ€™t what youâ€™d like it to be. For example, in
Figure 3-7 you may wish for a table header a bit more descriptive than count(*).
You can follow any function or column name with the word as and then specify
a name you prefer. as simply designates an alias â€” an identifier that represents
something else. If you need a column name that is more than one word, surround
the text string with single quotes.
While on the topic of aliases, weâ€™ll also mention that a variety of functions and
operators are available in MySQL (see Appendix J). They range from simple math
Chapter 3: Inserting, Editing, and Selecting Data 77
functions to more complex operations. The following is some math weâ€™ve thrown in
to clarify the purpose of the query. Notice the use of as for the alias and the way it
affects the display of the query (shown in Figure 3-8).
select city, state, count(*) as â€˜Total Rowsâ€™,
count(age) as â€˜The Willingâ€™,
(count(age)/count(*)*100) as â€˜Percent Respondingâ€™
group by state, city;