Critical Skill 4.2 Refine Your Data Selection

You can focus on particular areas of your database, viewing just the parts you need to see. You can also narrow your focus even smaller, to a specific record or field.

Focusing on Smaller Areas of Your Database

Several commands narrow the focus from your entire MySQL server to a specific database within it, and then to a specific table within that database. You ve used these commands in the projects in preceding modules to work with the sample database and tables. These commands and their meanings are listed in Table 4-1.

Table 4-1: Commands That Focus on Areas of the Database

Command	Meaning
SHOW DATABASES;	Shows all databases in the MySQL server
USE < database_name >; Narrows the focus to the specified database	Narrows the focus to the specified database
SHOW TABLES; Shows all tables within the specified database	Shows all tables within the specified database
DESC < table_name >; Lists the description of the specified table	Lists the description of the specified table

Using WHERE to Narrow Your Focus

Once you have selected a particular column or columns of data to output, the WHERE clause of the SELECT command allows you to narrow your selection even more. You can choose to output only a specific record or field. For example, you can use it to specify a range or a single choice by focusing on a key (or index) value. This allows you to present the user with exactly the required data from your database.

Note  

The WHERE clause can also be used in the same manner with the UPDATE and DELETE commands, to narrow their results.

In the previous modules, you have learned about using some of the variations of the WHERE clause to narrow data to a precise item or range of items. Here, you will learn how to use the WHERE clause with conditions, which use operators or reserved words to return output based on comparisons.

The WHERE clause syntax is listed in the MySQL manual as [WHERE < where_definition > ] , which is concise but not exactly detailed. Table 4-2 lists some, but not all, variations on the WHERE clause syntax with examples.

Table 4-2: Some Variations of the WHERE Syntax

Syntax	Examples
WHERE < column_name ><operator><where_value>;	WHERE car_color="green";
WHERE < column_name > IS < NULL/NOT NULL > ;	WHERE graduation_date IS NULL;
WHERE < column_name ><operator><where_value> AND < column_name ><operator><where_value>;	WHERE release_date > =1964 AND vocal="John Lennon";
WHERE < column_name ><operator><where_value> AND < column_name >BETWEEN < numeric_where_value > AND < numeric_where_value >;	WHERE car_maker="Chevrolet" AND car_year BETWEEN 1955 AND 1959;
WHERE < column_name ><operator><where_value> OR < column_name ><operator><where_value>;	WHERE series_code="BtVS" OR series_code="AtS";
WHERE < column_name > LIKE " < comparison_value >%";	WHERE music_styles LIKE "R%";

If each of the WHERE clauses listed in Table 4-2 were preceded by a SELECT * statement, the results would be a list of every record in the specified table that fits the criteria. Most of them, if read aloud , are self-explanatory, except for the last one, which selects records from a table where the data in the music_styles field begin with the letter R .

The next sections describe the operators and reserved words that you can use in WHERE clauses to refine your selections.

Using Operators with WHERE

The WHERE clause allows you to use the six arithmetic and two logical operators listed in Table 4-3 as conditional comparisons. Any numeric field, such as the customer number, can be used in a WHERE clause with the numeric operators. You have already used < = and > = in the projects in Module 3.

Table 4-3: Comparison Operators for the WHERE Clause

Operator	Definition
=	Equal to
!=	Not equal to
< =	Less than or equal to
<	Less than
> =	Greater than or equal to
>	Greater than
AND	Both values true
OR	One or both values true

Using the AND operator specifies that all the values involved in the comparison must be TRUE; using the OR operator specifies that at least one of the values in the comparison must be TRUE .

You can also use the reserved word BETWEEN in conjunction with the reserved word AND to establish a numeric range within which you want MySQL to select data.

Comparing Text with WHERE

The reserved word LIKE allows you to make comparisons with a portion of each field against a string value that you specify between double quotation marks. For instance, you could list all the fields in a column whose data begins with the same letter or letters .

The string comparisons in WHERE clauses are not case-sensitive by default. If you want to use case-sensitivity , you can add the BINARY option to the WHERE clause criteria:

WHERE BINARY < column_name >< operator >< where_value >;

This will allow MySQL to differentiate between strings where they vary because of case. For instance, you can use it to find only don and not Don , or in a LIKE wildcard search for "R%" , so you will find Rock and Reggae but not rock .

Progress Check

1.	What are the six arithmetic and two logical comparison operators?
2.	What reserved word allows you to compare strings for partially matching characters ?
3.	What code allows you to search a table called albums in the columns groups and release_dates for all records by Queen released between 1973 and 1976, giving you the data from the titles and sales fields.
4.	How do you make a string comparison case-sensitive?

Answers

1.	The six arithmetic operators used for comparison are equal to (=), not equal to (!=), less than or equal to (<=), less than (<), greater than or equal to (>=), and greater than (>). The two logical operators used for comparison are AND and OR.
2.	The reserved word LIKE allows you to compare strings for partially matching characters.
3.	The following code performs the search: SELECT titles, sales FROM albums WHERE groups="Queen" AND release_dates BETWEEN 1973 AND 1976;
4.	You add the reserved word BINARY to the WHERE clause to perform case-sensitive comparisons.