Searching Data
Once our database is loaded with large amounts of data, it can be
cumbersome to locate data simply by scrolling through the results of
SELECT
statements. Also, sometimes we don’t have the
exact or complete text for a column we’re examining. 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 name of the author or the title of the book, but he does
remember that the words traveler and
winter are in the title. We could enter this SQL
statement to search the database based on this minimal information:
SELECT book_id, title, CONCAT(author_first, ' ', author_last) AS author FROM books JOIN authors USING(author_id) WHERE title LIKE '%traveler%' AND title LIKE '%winter%'; +---------+-----------------------------------+---------------+ | book_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 contains the string
traveler with zero or more
characters before it (the preceding percent sign), and zero or more
characters after it (the terminating percent sign). Put another way, the
word traveler must be contained somewhere in the column’s ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.