Frequently, you don’t want all the information from a table. You want information from selected database objects, that is, 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 members who live in California, or you can list only pets that are cats.
- LIMIT: Allows you to limit the number of rows from which information is retrieved. For instance, you can request all the information from the first three rows in the table.
- DISTINCT: Allows you to request information from only one row of identical rows. For instance, in the 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 member ever logged in?” rather than the question “How many times has the member logged in?”
The WHERE clause of the SELECT query enables you to make complicated selections. For instance, suppose your boss asks for a list of all the members whose last names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number. I’m sure there are many uses for such a list. You can get this list for your boss with a SELECT query by using 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. 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 people in the Member Directory whose names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number:
SELECT lastName,firstName FROM Member
WHERE lastName LIKE “B%”
AND city = “Santa Barbara”
AND (phone LIKE “%8%” OR fax LIKE “%8%”) Notice the parentheses in the last line. You would not get the results that your boss 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 members whose names begin with B and who live in Santa Barbara and whose phone numbers have an 8 in them and all members whose fax numbers have an 8 in them, whether they live in Santa Barbara or not and whether their name begins with a B or not. When the last OR is processed, members 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 does not stand alone, but the expression after the OR does stand alone, resulting in the selection of all members with an 8 in their fax number.
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 members who live in Texas, use this query:
SELECT * FROM Member WHERE state=”TX” LIMIT 3
Some SELECT queries will find identical records, but in this example you want to see only one — not all — of the identical records. To prevent the query from returning all identical records, add the word DISTINCT immediately after SELECT.
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. 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 people in the Member Directory whose names begin with B, who live in Santa Barbara, and who have an 8 in either their phone or fax number:
SELECT lastName,firstName FROM Member
WHERE lastName LIKE “B%”
AND city = “Santa Barbara”
AND (phone LIKE “%8%” OR fax LIKE “%8%”) Notice the parentheses in the last line. You would not get the results that your boss 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 members whose names begin with B and who live in Santa Barbara and whose phone numbers have an 8 in them and all members whose fax numbers have an 8 in them, whether they live in Santa Barbara or not and whether their name begins with a B or not. When the last OR is processed, members 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 does not stand alone, but the expression after the OR does stand alone, resulting in the selection of all members with an 8 in their fax number.
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 members who live in Texas, use this query:
SELECT * FROM Member WHERE state=”TX” LIMIT 3
Some SELECT queries will find identical records, but in this example you want to see only one — not all — of the identical records. To prevent the query from returning all identical records, add the word DISTINCT immediately after SELECT.
No comments:
Post a Comment