Selecting Data - Learning MySQL and MariaDB - page 150

then by the bird's common name. If you look at the results, you can see that's what it did:
it sorted the orders first. If you look at the rows for the Charadriiformes , you can see that
the families for that order are in alphabetical order. The two birds in the Caprimulgidae
family are in alphabetical order.
NOTE
You cannot use alias names for columns in the ORDER BY clause, but you can use alias table names. In
fact, they're required if you've usedthe aliases in the FROM clause.
The previous example used the LIKE operator, which has limited pattern matching abilit-
ies. As an alternative, you can use REGEXP , which has many pattern matching characters
and classes. Let's look at a simpler example, of theprevious SELECT statement, but using
REGEXP . In the previous example we searched for small birds, birds with a common
name starting with the word Least . The largest bird in a family is typically called Great .
To add these birds, enter the following SQL statement on yourserver:
SELECT common_name AS 'Birds Great and Small'
FROM birds
WHERE common_name REGEXP 'Great|Least'
ORDER BY family_id LIMIT 10;
+-----------------------------+
| Birds Great and Small |
+-----------------------------+
| Great Northern Loon |
| Greater Scaup |
| Greater White-fronted Goose |
| Greater Sand-Plover |
| Great Crested Tern |
| Least Tern |
| Great Black-backed Gull |
| Least Nighthawk |
| Least Pauraque |
| Great Slaty Woodpecker |
+-----------------------------+
The expression we're giving with REGEXP , within the quote marks, contains two string
values: Great and Least . By default, MySQL assumes the text given for REGEXP is meant
to be for the start of the string. To be emphatic, you can insert acarat (i.e., ^ ) at the start
of these string values, but it's unnecessary. Thevertical bar (i.e., | ) between the two ex-
pressions signifies that either value is acceptable — it means or .