The select query is used to retrieve records from a database. The keywords used in a select query are summarised in the following table.
|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.|
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;
WHERE clause may be used to limit records. The following lists the comparison
operators available with MySQL to limit the records returned with the
|<> or !=||Not equal to|
|<=||Less than or equal to|
|>=||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____';
BETWEEN clause may be used with numbers, dates and text. The following
example retrieves all fields from Products, where the cost is between 1000
mysql> SELECT * FROM Products WHERE cost BETWEEN 1000 AND 4000;
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
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');
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
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';
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
may be used to ensure that one one record is returned for each Directory
mysql> SELECT DISTINCT Directory FROM search;
GROUP BY modifier may be used to perform aggregate functions, such
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
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
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.
Returns the average value in a group of records. The example returns the average order for each customer.
Returns the number of records in a group of records. The example returns the number of orders for each customer.
Returns the largest value in a group of records. The example returns the largest order by each customer.
Returns the lowest value in a group of records. The example returns the smallest order by each customer.
Returns the sum of a field. The example returns the total amount spent by each customer.
The Having Clause
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
mysql> SELECT AVG(cost) FROM Invoice GROUP BY ClientID HAVING AVG(cost) > 20000;
The Order By Clause
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;
ORDER BY clause may use the
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
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
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;