MySQL Cookbook, 4th Edition

Chapter 5. Selecting Data from Tables

5.0 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 if you 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 User 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. The following shows how that table was created:

CREATE TABLE mail
(
  id      INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  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 t, srcuser, srchost, dstuser, dsthost, size 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 | ...