3.3.4.2 Selecting Particular Rows

You can select only particular rows from your table. For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this:

mysql> SELECT * FROM pet WHERE name = "Bowser";
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

The output confirms that the year is correctly recorded now as 1989, not 1998.

String comparisons are normally case-insensitive, so you can specify the name as "bowser", "BOWSER", etc. The query result will be the same.

You can specify conditions on any column, not just name. For example, if you want to know which animals were born after 1998, test the birth column:

mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

You can combine conditions, for example, to locate female dogs:

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

The preceding query uses the AND logical operator. There is also an OR operator:

mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

AND and OR may be intermixed. If you do that, it's a good idea to use parentheses to indicate how conditions should be grouped:

mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
    -> OR (species = "dog" AND sex = "f");
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

User Comments

I don't think that this was mentioned specifically, and many
of you know it already, but :

In the Where clause, AND takes precedence over OR, which
means that
WHERE name='Smith' or salary>90000 and department=2
is equivalent to
WHERE name='Smith' OR (salary > ... AND depat...)

which will return anybody who has the name Smith and
anybody in department 2 with a salary greater than 90000.

Actually, Nate la Montagne, this is incorrect.

The "order of operations" for logical operators in
mathematics and in PHP and most all other languages is:

NOT
OR
AND
XOR

As a math teacher, I tell my students to remember it with
this pneumonic: NOAh.

So, just remember "noah" and you will always remember:
Not, Or, And.

X not Y or Z and Q is the same as:

(((X not Y) or Z) and Q)

Fortran, C, Java, Awk, and Perl all have
precedence, from
higher to lower, of Not, And, Or. I hope soon I
will learn the
precedence order for MySQL. XOR is not so
commonly used,
so its precedence rule isn't well standardized.

Nate is right, AND takes precedence over OR, e.g.
try: select * from pet where species='dog' and
sex='f' or owner='Gwen';

This is a fine kettle of fish! The bottom line is: "When in doubt, use parentheses!" Actually, even if you're sure, use parentheses anyway; the next person might not think the same way you do.

Add your own comment.