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;
Comments