MySQL Tutorial

The 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.

Keyword Description
SELECT Retrieves fields from one or more tables.
FROM Tables containing the fields.
WHERE Criteria to restrict the records returned.
GROUP BY Determines how the records should be grouped.
HAVING Used with GROUP BY to specify the criteria for the grouped records.
ORDER BY Criteria for ordering the records.
LIMIT Limit the number of records returned.

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;

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 with the WHERE clause.

Operator Description
= Equal to
<> or != Not equal to
< Less than
<= Less than or equal to
> greater than
>= greater than or equal to
LIKE Used to compare strings
BETWEEN Checks for values between a range
IN Checks for values in a list
NOT IN Ensures the value is not in the list

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 Products, 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');

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 operator. The following example extends the previous example to return only records where the Author has the Surname, Lemon.

listLemons.sql

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

The file may then be used with MySQL as follows:

mysql> \. listLemons.sql

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;

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;

List of Aggregate Functions Available in MySQL

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

Function Example Description
AVG() SELECT AVG(cost) FROM Invoice GROUP BY ClientID;

Returns the average value in a group of records. The example returns the average order for each customer.

COUNT() SELECT COUNT(cost) FROM Invoice GROUP BY ClientID;

Returns the number of records in a group of records. The example returns the number of orders for each customer.

MAX() SELECT MAX(cost) FROM Invoice GROUP BY ClientID;

Returns the largest value in a group of records. The example returns the largest order by each customer.

MIN() SELECT MIN(cost) FROM Invoice GROUP BY ClientID;

Returns the lowest value in a group of records. The example returns the smallest order by each customer.

SUM() SELECT SUM(cost) FROM Invoice GROUP BY ClientID;

Returns the sum of a field. The example returns the total amount spent by each customer.

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;

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;

Limiting the Records Returned

The LIMIT clause 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;

You might also like...

Comments

About the author

Gez Lemon United Kingdom

I'm available for contract work. Please visit Juicify for details.

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“An expert is a man who has made all the mistakes that can be made in a very narrow field” - Niels Bohr