Learning Mysql

ORDER BY Clauses Of course, we haven’t stored the surname and the given names separately, and MySQL isn’t smart enough to know that Kylie Minogue is a person’s name that should ordinarily be sorted by surname (in phonebook order).
Another very common task you’ll want to perform with strings is to find matches that begin with a prefix, contain a string, or end in a suffix. For example, you might want to find all album names beginning with the word Retro You can do this with the
LIKE
operator in a
WHERE
clause:
mysql> SELECT album_name FROM album WHERE album_name LIKE "Retro%";
+------------------------------------------+
| album_name |
+------------------------------------------+
| Retro - John McCready FAN |
| Retro - Miranda Sawyer POP |
| Retro - New Order / Bobby Gillespie LIVE |
+------------------------------------------+
3 rows inset sec)
Let’s discuss in detail how this works.
The
LIKE
clause is used only with strings and means that a match must meet the pattern in the string that follows. In our example, we’ve used LIKE "Retro, which means the string Retro followed by zero or more characters. Most strings used with
LIKE
contain the percentage character (
%
) as a wildcard character that matches all possible strings.
You can also use it to define a string that ends in a suffix—such as "%ing"
—or a string that contains a particular substring, such as
%Corruption%
For example,
"John%"
would match all strings starting with "John, such as
John
Smith and John Paul Getty. The pattern "%Paul"
matches all strings that have "Paul"
at the end. Finally, the pattern "%Paul%"
matches all strings that have "Paul"
in them,
including at the start or at the end.
If you want to match exactly one wildcard character in a
LIKE
clause, you use the underscore character (
_
). For example, if you want all tracks that begin with a three- letter word that starts with 'R, you use:
mysql> SELECT * FROM track WHERE track_name LIKER %";
+----------+----------------+-----------+----------+----------+
| track_id | track_name | artist_id | album_id | time |
+----------+----------------+-----------+----------+----------+
| 4 | Red Right Hand | 2 | 1 | 00:06:11 |
| 14 | Run Wild | 1 | 1 | 00:03:57 |
| 1 | Rip This Joint | 4 | 1 | 00:02:23 |
+----------+----------------+-----------+----------+----------+
3 rows inset sec)
The specification "R means a three-letter word beginning with 'R'
—for example "Red,
"Run"
and "Rip"
—followed by a space character, and then any string.

Download 4.24 Mb.

Share with your friends: