Database Reference
In-Depth Information
| 2014-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.
Recipe 5.8
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 |
+---------------------+---------+---------+---------+---------+-------+
| 2014-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2014-05-12 18:59:18 | barb | saturn | tricia | venus | 271 |
+---------------------+---------+---------+---------+---------+-------+
Output columns can be calculated by evaluating expressions. This query combines the
srcuser
and
srchost
columns using
CONCAT()
to produce composite values in email
address format:
mysql>
SELECT t, CONCAT(srcuser,'@',srchost), size FROM mail;
+---------------------+-----------------------------+---------+
| t | CONCAT(srcuser,'@',srchost) | size |
+---------------------+-----------------------------+---------+
| 2014-05-11 10:15:08 | barb@saturn | 58274 |
| 2014-05-12 12:48:13 | tricia@mars | 194925 |
| 2014-05-12 15:02:49 | phil@mars | 1048 |
| 2014-05-12 18:59:18 | barb@saturn | 271 |
…
You'll notice that the email address column label is the expression that calculates it. To
provide a better label, use a column alias (see
Recipe 3.2
).
3.2. Naming Query Result Columns
Problem
The column names in a query result are unsuitable, ugly, or difficult to work with.
Solution
Use aliases to choose your own column names.
Discussion
When you retrieve a result set, MySQL gives every output column a name. (That's how
the
mysql
program gets the names you see displayed in the initial row of column headers
in result set output.) By default, MySQL assigns the column names specified in the
