3.10. 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 found in the column. Incidentally, the LIKE keyword is an operator. For more information on operators, see Appendix B.

If another customer asks us to search the database for a book with either the word Ford or Chevrolet in the title, we could use the OR operator within an expression like so:

SELECT books.rec_id, title,        CONCAT(author_first, ' ', author_last) AS author FROM books, authors WHERE title LIKE '%Ford%' AND author_id = authors.rec_id OR title LIKE '%Chevrolet%' AND author_id = authors.rec_id;

You can find more examples and possibilities for searching data in Chapter 4.



MySQL in a Nutshell
MYSQL in a Nutshell (In a Nutshell (OReilly))
ISBN: 0596514336
EAN: 2147483647
Year: 2006
Pages: 134
Authors: Russell Dyer

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net