How to Retrieve Data from Specific Rows in MySQL Databases

Frequently, you don’t want to retrieve all the information from a MySQL table. You want information only from selected rows. Three SQL words are frequently used to specify the source of the information:

  • WHERE: Allows you to request information from database objects with certain characteristics. For instance, you can request the names of customers who live in California, or you can list only products that are a certain category of clothes.

  • LIMIT: Allows you to limit the number of rows from which information is retrieved. For instance, you can request the information from only the first three rows in the table.

  • DISTINCT: Allows you to request information from only one row of identical rows. For instance, in a Login table, you can request loginName but specify no duplicate names, thus limiting the response to one record for each member. This would answer the question, “Has the customer ever logged in?” rather than the question “How many times has the customer logged in?”

Use a WHERE clause

The WHERE clause of the SELECT query enables you to make complicated selections. For instance, suppose your boss wants to know all the customers whose last names begin with B, who live in Indianapolis, and who have an 8 in either their phone or fax number. You can get this list for your boss in a SELECT query with a WHERE clause.

The basic format of the WHERE clause is

WHERE expression AND|OR expression AND|OR expression ...

expression specifies a value to compare with the values stored in the database. Only the rows containing a match for the expression are selected. You can use as many expressions as needed, each one separated by AND or OR.

When you use AND, both of the expressions connected by the AND (that is, both the expression before the AND and the expression after the AND) must be true in order for the row to be selected. When you use OR, only one of the expressions connected by the OR must be true for the row to be selected.

Expressions for the WHERE Clause
Expression Example Result
column = value zip=“12345” Selects only the rows where 12345 is stored in the column named zip
column > value zip > “50000” Selects only the rows where the ZIP code is 50001 or higher
column >= value zip >= “50000” Selects only the rows where the ZIP code is 50000 or higher
column < value zip < “50000” Selects only the rows where the ZIP code is 49999 or lower
column <= value zip <= “50000” Selects only the rows where the ZIP code is 50000 or lower
column BETWEEN value1 AND value2 zip BETWEEN “20000” AND “30000” Selects only the rows where the ZIP code is greater than 19999 but less 30001
column IN (value1,value2,…) zip IN (“90001”,”30044”) Selects only the rows where the ZIP code is 90001 or 30044
column NOT IN (value1,value2,…) zip NOT IN (“90001”,”30044”) Selects only the rows where the ZIP code is any ZIP code except 90001 or 30044
column LIKE value
Note: value can contain the wildcards % (which matches any string) and _ (which matches any character).
zip LIKE “9%” Selects all rows where the ZIP code begins with 9
column NOT LIKE value
Note: value can contain the wildcards % (which matches any string) and _ (which matches any character).
zip NOT LIKE “9%” Selects all rows where the ZIP code doesn’t begin with 9

You can combine any of the expressions with ANDs and ORs. In some cases, you need to use parentheses to clarify the selection criteria. For instance, you can use the following query to answer your boss’s urgent need to find all customers whose names begin with B, who live in Indianapolis, and who have an 8 in either their phone or fax number:

SELECT lastName,firstName FROM Customer
       WHERE lastName LIKE "B%"
         AND city = "Indianapolis"
         AND (phone LIKE "%8%" OR fax LIKE "%8%")

Notice the parentheses in the last line. You wouldn’t get the results that you asked for without the parentheses.

Without the parentheses, each connector would be processed in order from the first to the last, resulting in a list that includes all customers whose names begin with B and who live in Indianapolis and whose phone numbers have an 8 in them and all customers whose fax numbers have an 8 in them, whether or not they live in Indianapolis and whether or not their name begins with a B.

When the last OR is processed, customers are selected whose characteristics match the expression before the OR or the expression after the OR. The expression before the OR is connected to previous expressions by the previous ANDs, and so it doesn’t stand alone, but the expression after the OR does stand alone, resulting in the selection of all customers with an 8 in their fax number.

Use the LIMIT keyword

LIMIT specifies how many rows can be returned. The form for LIMIT is

LIMIT startnumber,numberofrows

The first row that you want to retrieve is startnumber, and the number of rows to retrieve is numberofrows. If startnumber is not specified, 1 is assumed. To select only the first three customers who live in Texas, use this query:

SELECT * FROM Customer WHERE state="TX" LIMIT 3

Use the DISTINCT keyword

Rows in the table can have identical values in one or more columns. However, in some cases, when you SELECT a column, you don’t want to retrieve multiple rows with identical values. You want to retrieve the value only once. For example, suppose you have a table of products with one field called Category.

The data undoubtedly contains many products in each category. Now suppose you want to display a list of all the categories available in the database. You want this list to contain each category listed only once. The keyword DISTINCT is provided for this purpose.

To prevent a SELECT query from returning all identical records, add the keyword DISTINCT immediately after SELECT, as follows:

SELECT DISTINCT Category FROM Product
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com