Database Reference
In-Depth Information
mysql>
SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2014-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2014-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2014-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2014-05-12 18:59:18 | barb | saturn | tricia | venus | 271 |
…
Using
*
is easy, but you cannot select only certain columns or control column display
order. Naming columns explicitly enables you to select only the ones of interest, in any
order. This query omits the recipient columns and displays the sender before the date
and size:
mysql>
SELECT srcuser, srchost, t, size FROM mail;
+---------+---------+---------------------+---------+
| srcuser | srchost | t | size |
+---------+---------+---------------------+---------+
| barb | saturn | 2014-05-11 10:15:08 | 58274 |
| tricia | mars | 2014-05-12 12:48:13 | 194925 |
| phil | mars | 2014-05-12 15:02:49 | 1048 |
| barb | saturn | 2014-05-12 18:59:18 | 271 |
…
Unless you qualify or restrict a
SELECT
query in some way, it retrieves every row in your
table. To be more precise, provide a
WHERE
clause that specifies one or more conditions
that rows must satisfy.
Conditions can test 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 in 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 |
+---------------------+---------+---------+
| 2014-05-14 09:31:37 | gene | venus |
| 2014-05-14 14:42:21 | barb | venus |
| 2014-05-15 08:50:57 | phil | venus |
| 2014-05-16 09:00:28 | gene | venus |
| 2014-05-16 23:04:19 | phil | venus |
+---------------------+---------+---------+
mysql>
SELECT t, srcuser, srchost FROM mail WHERE srchost LIKE 's%';
+---------------------+---------+---------+
| t | srcuser | srchost |
+---------------------+---------+---------+
| 2014-05-11 10:15:08 | barb | saturn |
| 2014-05-12 18:59:18 | barb | saturn |
| 2014-05-14 17:03:01 | tricia | saturn |
| 2014-05-15 17:35:31 | gene | saturn |
