MySQL Cookbook, 3rd Edition

Chapter 3. Selecting Data from Tables

Introduction

This chapter focuses on using the SELECT statement to retrieve information from your database. You will find the chapter helpful if your SQL background is limited or to find out about the MySQL-specific extensions to SELECT syntax.

There are many ways to write SELECT statements; we’ll look at only a few. Consult the MySQL Reference Manual or a general MySQL text for more information about SELECT syntax and the functions and operators available to extract and manipulate data.

Many examples in this chapter use a table named mail that contains rows that track mail message traffic between users on a set of hosts:

CREATE TABLE mail
(
  t       DATETIME,    # when message was sent
  srcuser VARCHAR(8),  # sender (source user and host)
  srchost VARCHAR(20),
  dstuser VARCHAR(8),  # recipient (destination user and host)
  dsthost VARCHAR(20),
  size    BIGINT,      # message size in bytes
  INDEX (t)
);

The mail table contents look like this:

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 | | 2014-05-14 09:31:37 | gene | venus | barb | mars | 2291 | | ...