Recipe 3.2. Specifying Which Rows to Select

Problem

You want to see only those rows that match certain criteria.

Solution

To specify which rows to return, add a WHERE clause to identify the rows that you want to see, such as customers that live in a particular city or tasks that have a status of "finished."

Discussion

Unless you qualify or restrict a SELECT query in some way, it retrieves every row in your table, which in many cases is a lot more information than you really want to see. To be more precise about which rows to select, provide a WHERE clause that specifies one or more conditions that rows must match.

Conditions can perform tests for equality, inequality, or relative ordering. For some types of data, such as strings, you can use pattern matches. The following statements select columns from rows from the mail table containing srchost values that are exactly equal to the string 'venus' or that begin with the letter 's' :

mysql>

SELECT t, srcuser, srchost  FROM mail WHERE srchost = 'venus';

+---------------------+---------+---------+
 t                    srcuser  srchost 
+---------------------+---------+---------+
 2006-05-14 09:31:37  gene     venus   
 2006-05-14 14:42:21  barb     venus   
 2006-05-15 08:50:57  phil     venus   
 2006-05-16 09:00:28  gene     venus   
 2006-05-16 23:04:19  phil     venus   
+---------------------+---------+---------+
mysql>

SELECT t, srcuser, srchost FROM mail WHERE srchost LIKE 's%';

+---------------------+---------+---------+
 t                    srcuser  srchost 
+---------------------+---------+---------+
 2006-05-11 10:15:08  barb     saturn  
 2006-05-13 13:59:18  barb     saturn  
 2006-05-14 17:03:01  tricia   saturn  
 2006-05-15 17:35:31  gene     saturn  
 2006-05-19 22:21:51  gene     saturn  
+---------------------+---------+---------+

The LIKE operator in the previous query performs a pattern match, where % acts as a wildcard that matches any string. Section 5.10 discusses pattern matching further.

A WHERE clause can test multiple conditions and different conditions can test different columns. The following statement finds messages sent by barb to tricia :

mysql>

SELECT * FROM mail WHERE srcuser = 'barb' AND dstuser = 'tricia';

+---------------------+---------+---------+---------+---------+-------+
 t                    srcuser  srchost  dstuser  dsthost  size  
+---------------------+---------+---------+---------+---------+-------+
 2006-05-11 10:15:08  barb     saturn   tricia   mars     58274 
 2006-05-13 13:59:18  barb     saturn   tricia   venus      271 
+---------------------+---------+---------+---------+---------+-------+

Recipe 3.3. Giving Better Names to Query Result Columns

Problem

You don't like the names of the columns in a query result.

Solution

Use column aliases to supply names of your own choosing.

Discussion

When you retrieve a result set, MySQL gives every output column a name . (That's how the mysql program gets the names that you see displayed as the initial row of column headers in result set output.) By default, MySQL assigns the column names specified in the CREATE TABLE or ALTER TABLE statement to output columns, but if these defaults are not suitable, you can use column aliases to specify your own names.

This section explains aliases and shows how to use them to assign column names in statements. If you're writing a program that needs to retrieve information about column names (that is, column metadata), see Section 9.2.

If an output column in a result set comes directly from a table, MySQL uses the table column name for the output column name. For example, the following statement selects three table columns, the names of which become the corresponding output column names:

mysql>

SELECT t, srcuser, size FROM mail;

+---------------------+---------+---------+
 t                    srcuser  size    
+---------------------+---------+---------+
 2006-05-11 10:15:08  barb       58274 
 2006-05-12 12:48:13  tricia    194925 
 2006-05-12 15:02:49  phil        1048 
 2006-05-13 13:59:18  barb         271 
...

If you generate a column by evaluating an expression, the expression itself is the column name. This can produce rather long and unwieldy names in result sets, as illustrated by the following statement that uses an expression to reformat the t column of the mail table:

mysql>

SELECT

->

CONCAT(MONTHNAME(t),' ',DAYOFMONTH(t),', ',YEAR(t)),

->

srcuser, size FROM mail;

+-----------------------------------------------------+---------+---------+
 CONCAT(MONTHNAME(t),' ',DAYOFMONTH(t),', ',YEAR(t))  srcuser  size    
+-----------------------------------------------------+---------+---------+
 May 11, 2006                                         barb       58274 
 May 12, 2006                                         tricia    194925 
 May 12, 2006                                         phil        1048 
 May 13, 2006                                         barb         271 
...

The query in the preceding example is specifically contrived to illustrate how awful -looking column names can be. The reason it's contrived is that you probably wouldn't really write the query that way; the same result can be produced more easily using the DATE_FORMAT⁠(⁠ ⁠ ⁠) function. But even if you use DATE_FORMAT⁠(⁠ ⁠ ⁠) , the column header is still ugly:

mysql>

SELECT

->

DATE_FORMAT(t,'%M %e, %Y'),

->

srcuser, size FROM mail;

+----------------------------+---------+---------+
 DATE_FORMAT(t,'%M %e, %Y')  srcuser  size    
+----------------------------+---------+---------+
 May 11, 2006                barb       58274 
 May 12, 2006                tricia    194925 
 May 12, 2006                phil        1048 
 May 13, 2006                barb         271 
...

To give an output column a name of your own choosing, use an AS name clause to specify a column alias (the keyword AS is optional). The following statement retrieves the same result as the previous one, but renames the first column to date_sent :

mysql>

SELECT

->

DATE_FORMAT(t,'%M %e, %Y') AS date_sent,

->

srcuser, size FROM mail;

+--------------+---------+---------+
 date_sent     srcuser  size    
+--------------+---------+---------+
 May 11, 2006  barb       58274 
 May 12, 2006  tricia    194925 
 May 12, 2006  phil        1048 
 May 13, 2006  barb         271 
...

The alias makes the column name more concise , easier to read, and more meaningful. If you want to use a descriptive phrase, an alias can consist of several words. Aliases can be fairly arbitrary, although they are subject to a few restrictions; for example, they must be quoted if they are SQL keywords, contain spaces or other special characters , or are entirely numeric. The following statement retrieves the same data values as the preceding one but uses phrases to name the output columns:

mysql>

SELECT

->

DATE_FORMAT(t,'%M %e, %Y') AS 'Date of message',

->

srcuser AS 'Message sender', size AS 'Number of bytes' FROM mail;

+-----------------+----------------+-----------------+
 Date of message  Message sender  Number of bytes 
+-----------------+----------------+-----------------+
 May 11, 2006     barb                      58274 
 May 12, 2006     tricia                   194925 
 May 12, 2006     phil                       1048 
 May 13, 2006     barb                        271 
...

You can apply an alias to any output column, not just those that come from tables:

mysql>

SELECT '1+1+1' AS 'The expression', 1+1+1 AS 'The result';

+----------------+------------+
 The expression  The result 
+----------------+------------+
 1+1+1                    3 
+----------------+------------+

Here, the value of the first column is '1+1+1' (quoted so that it is treated as a string), and the value of the second column is 1+1+1 (without quotes so that MySQL treats it as an expression and evaluates it). The aliases are descriptive phrases that help to clarify the relationship between the two column values.

If you use a single-word alias, and MySQL complains about it, the word probably is reserved. Quoting the alias should make it legal:

mysql>

SELECT 1 AS INTEGER;

You have an error in your SQL syntax near 'INTEGER' at line 1
mysql>

SELECT 1 AS 'INTEGER';

+---------+
 INTEGER 
+---------+
       1 
+---------+