MySQL in a Nutshell

Searching Data

Once our database is loaded with large amounts of data, it can be cumbersome to locate data by simply scrolling through the results of SELECT statements. Also, sometimes we don’t have the exact or complete text for a column in which we’re looking. For these situations, we can use the LIKE operator. Suppose that our books table now has thousands of entries. Suppose further that a customer says he’s looking for a specific book. He can’t remember the author or the title, but he does remember that the words traveler and winter are in the title. We could enter this statement to search the database based on this minimal information:

SELECT books.rec_id, title,
       CONCAT(author_first, ' ', author_last) AS author
FROM books, authors
WHERE title LIKE '%traveler%'
   AND title LIKE '%winter%'
   AND author_id = authors.rec_id;
   
+--------+-----------------------------------+---------------+
| rec_id | title                             | author        |
+--------+-----------------------------------+---------------+
|   1400 | If on a winter's night a traveler | Italo Calvino |
+--------+-----------------------------------+---------------+

With the LIKE operator, we use the percent-sign wildcard twice to indicate that we’re searching for all rows in which the title column’s data starts with zero or more characters before the pattern of traveler is found, and then zero or more characters may follow. Put another way, the word traveler must be contained somewhere in the column’s data to have a pattern match. Also, winter must be ...