Selecting Data - Learning MySQL and MariaDB - page 140

Selecting by a Criteria
Suppose that wewant to select only birds of a certain family, say the Charadriidae (i.e.,
Plovers). Looking in the bird_families table, we find that its family_id is 103.
Using a WHERE clause with the SELECT statement, we can retrieve a list of birds from the
birds table for this particular family of birds like so:
SELECT common_name, scientific_name
FROM birds WHERE family_id = 103
LIMIT 3;
+----------------------+-------------------------+
| common_name | scientific_name |
+----------------------+-------------------------+
| Mountain Plover | Charadrius montanus |
| Snowy Plover | Charadrius alexandrinus |
| Black-bellied Plover | Pluvialis squatarola |
+----------------------+-------------------------+
This SELECT statement requests two columns, in a different order from the way the data is
listed in the table — in the table itself, scientific_name precedes common_name . I
also addedthe LIMIT clause to keep the results down to the first three rows in the table.
We'll talk more about the LIMIT clause in a little while.
NOTE
Because we separated families into a separate table, you had to look at the bird_families table to get
the right ID before selecting birds from the birds table. That seems round-about. There is a streamlined
way to ask for a family name such as Charadriidae instead of a number. They're called joins. We'll cover
them later.
This is all fairly straightforward and in line with what we've seen in several other examples
in previous chapters. Let's move on and take a look at how to change the order of the res-
ults.