Полезная информация

next up previous contents index
Next: Removing Data with DELETE Up: Five Basic SQL Commands Previous: Viewing Data with SELECT

WHERE Clause

Let's take the next step in controlling the output of SELECT. In the previous section, we showed how to select only certain columns from the table. Now, we will show how to select only certain rows. The additional thing needed to do this is the WHERE clause. Without a WHERE clause, every row is returned.

The WHERE clause goes right after the FROM clause. In the WHERE clause, you specify the rows you want returned. For example, see figure [*].

  
Figure: My first WHERE
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...\vert Denver~~~~~~~~~\vert CO~~~\vert~23
\par (1~row)\end{list}\par
\end{figure}

The query requests to return the rows that have an age column equal to 23. Figure [*]
  
Figure: More complex WHERE clause
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...vert Allentown~~~~~~\vert PA~~~\vert~22
\par (2~rows)\end{list}\par
\end{figure}

shows a more complex example that returns two rows. You can combine the column restrictions and the row restrictions in a single query, allowing you to select any single cell, or a block of cells. See figures [*]
  
Figure: A single cell
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...{}-{}-{}-{}-{}-{}-{}-{}-{}-
\par Jackson
\par (1~row)\end{list}\par
\end{figure}

and [*].
  
Figure: A block of cells
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~~\vert CO
\par Allentown~~~~~~\vert PA
\par (2~rows)\end{list}\par
\end{figure}

Try using one of the other columns in the WHERE clause. Up to this point, we have made only comparisons on the age column. The age column is integer. The only tricky part about the other columns that they are char() columns, so you have to put the comparison value in single quotes. You also have to match the capitalization exactly. See figure [*]. If you compared the firstname column to 'SAM' or 'sam', it would have returned no rows.

Try a few more until you are comfortable.

  
Figure: Comparing string fields
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...\vert Allentown~~~~~~\vert PA~~~\vert~22
\par (1~row)\end{list}\par
\end{figure}


next up previous contents index
Next: Removing Data with DELETE Up: Five Basic SQL Commands Previous: Viewing Data with SELECT
Bruce Momjian
1999-11-21