From http://www.juicystudio.com/tutorial/mysql/select.asp

Using the SQL SELECT Statement with MySQL

MySQL Select Statement

Author: Gez Lemon

Contents

• The SQL SELECT Statement
• Limiting Records
• Joining Tables
• Selecting Distinct Records
• Aggregate Functions
• Aggregate Functions in MySQL
• The HAVING Clause
• The ORDER BY Clause
• The LIMIT Modifier

The SQL SELECT Statement

The select query is used to retrieve records from a database. The keywords used in a select query are summarised in the
following table.

The simplest SELECT query is to retrieve all records from a single table. The following example lists all fields from the search table.

mysql> SELECT * FROM search;

To select specific fields from a table, you provide a comma-separated list of field names. The following example selects
the "Page" and "Directory" from the search table:

mysql> SELECT Page, Directory FROM search;

[Back to the contents]

Limiting Records

The WHERE clause may be used to limit records. The following lists the comparison operators available with MySQL to limit the records returned using the WHERE clause.

When working with strings, the % character may be used as a wildcard. The following example retrieves all fields from the
search table where the Keyword field contains the text, "cookies".

mysql> SELECT * FROM search WHERE Keywords LIKE '%cookies%';

The underscore character may be used as a placeholder. The following example selects all records from the search table,
where the Page name beings with 'P', followed by four characters (four underscores are used).

mysql> SELECT * FROM search WHERE Page LIKE 'P____';

The BETWEEN clause may be used with numbers, dates and text. The following example retrieves all fields from the "Products" table, where the cost is between 1000 and 4000.

mysql> SELECT * FROM Products WHERE cost BETWEEN 1000 AND 4000;

The OR clause may be used to specify a range of values to check against. The following example lists all records
where the Category is either "ASP" or "PHP" in the search table.

mysql> SELECT * FROM search WHERE Category = 'ASP' OR Category = 'PHP';

If you have many values that you want to check against, you can use the IN clause as it makes the code a lot
more readable. The following is the above statement using the IN clause.

mysql> SELECT * FROM search WHERE Category IN ('ASP', 'PHP');

Similarly, you can use the NOT modifier with the IN clause to check for values that are not within the list. The following example returns all records where the Category is not equal to "ASP" or "PHP".

mysql> SELECT * FROM search WHERE Category NOT IN ('ASP', 'PHP');

[Back to the contents]

Joining Tables

Sometimes the data you require may come from two or more tables. Supposing our search table contained a foreign key called AuthorID that related to a primary key of the same name in an author table, we could retrieve records by linking the two fields. The following example lists the Surname and Forename from an author table, and the Directory and Page from the search table written by that author.

SELECT 
    author.Surname, author.Forename, search.Directory, search.Page 
FROM 
    search, author 
WHERE 
    author.AuthorID = search.AuthorID;

You can add further clauses to the WHERE clause, using the AND and OR operators. The following example extends the previous example to return only records where the Author has the Surname, Lemon.

SELECT 
    author.Surname, author.Forename, search.Directory, search.Page 
FROM 
    search, author 
WHERE 
    author.AuthorID = search.AuthorID AND
    author.Surname = 'Lemon';

[Back to the contents]

Selecting Distinct Records

Our "search" table contains a list of pages in directories. If we were to list all directories from the table, we would
end up with duplicate records, as there may be more than one page in a directory. The DISTINCT modifier may be used to ensure that one one record is returned for each Directory name.

mysql> SELECT DISTINCT Directory FROM search;

[Back to the contents]

Aggregate Functions

The GROUP BY modifier may be used to perform aggregate functions, such as COUNT records. The following example lists the distinct Directories, along with a count of how many records there are for that Directory in the search table.

mysql> SELECT Directory, COUNT(*) FROM search GROUP BY Directory;

The AS modifier may be used to provide meaningful column names for the result. In the above example, the column
headings from running the query are, Directoy and COUNT(*). The following example uses the column name "Entries" instead of COUNT(*).

mysql> SELECT Directory, COUNT(*) AS Entries FROM search GROUP BY Directory;

If you want the column name to contain spaces, you must put the name in single quotes. The next example uses a column name
of Number of Entries.

mysql> SELECT Directory, COUNT(*) AS 'Number of Entries' FROM search GROUP BY Directory;

[Back to the contents]

List of Aggregate Functions Available in MySQL

The following table contains a list of the aggregate function available in MySQL.

SELECT AVG(cost) FROM Invoice GROUP BY ClientID;

COUNT()

SELECT COUNT(cost) FROM Invoice GROUP BY ClientID;

SELECT MAX(cost) FROM Invoice GROUP BY ClientID;

SELECT MIN(cost) FROM Invoice GROUP BY ClientID;

SELECT SUM(cost) FROM Invoice GROUP BY ClientID;

[Back to the contents]

The HAVING Clause

The WHERE clause is used to restrict records in a query. If you wish to restrict records from an aggregate function, you use the HAVING clause. The difference is that the HAVING clause restricts the records after they have been grouped. The following lists all customers who have spent over 20,000 on average.

mysql> SELECT AVG(cost) FROM Invoice GROUP BY ClientID HAVING AVG(cost) > 20000;

[Back to the contents]

The ORDER BY Clause

The ORDER BY clause may be used to order the records returned. The following example lists all Pages in the search table in alphabetical order.

mysql> SELECT Page FROM search ORDER BY Page;

The ORDER BY clause may use the ASC or DESC modifiers to determine if the records should be in ascending or descending order. If neither are provided, the records are shown in ascending order. The following example lists all Pages in the search table in descending order.

mysql> SELECT Page FROM search ORDER BY Page DESC;

[Back to the contents]

The LIMIT Modifier

The LIMIT modifier may be used to limit the records returned by the SELECT statement. You specify the start row (start from zero), and the number of records returned. The following example lists the first 10 records from the search table.

mysql> SELECT * FROM search LIMIT 0, 10;

The following example would retrieve the next ten records from the search table.

mysql> SELECT * FROM search LIMIT 10, 10;

[Back to the contents]