This tutorial assumes that you have access to the MySQL command prompt and uses the people table created in the Create Tutorial.
Using Select
The SELECT statement is used to query a database and output the results.
The required attributes are listed after the SELECT command separated by commas. If all attributes are required then the asterisk * character may be used as a wildcard.
Examples of Select Statements
mysql> SELECT firstname, lastname, age FROM people;
+---------+--------+---+
|firstname|lastname|age|
+---------+--------+---+
|Dave |Clark |40 |
|Mary |Smith |36 |
|Dave |Smith |51 |
+---------+--------+---+
3 rows in set (0.28 sec)
mysql> SELECT * FROM people;
+------------+--------+------------+------------+-------+---+
|id|firstname|lastname|address |county |country|age|
+------------+--------+------------+------------+-------+---+
|1 |Dave |Clark |Main Street |West Lothian|UK |40 |
|2 |Mary |Smith |North Street|Cumbria |UK |36 |
|3 |Dave |Smith |West Street |Strathclyde |UK |51 |
+------------+--------+------------+------------+-------+---+
3 rows in set (0.00 sec)
mysql>
Using the Where Clause
The WHERE clause is used with SELECT to limit results to table rows that match a condition (it is used in same way with UPDATE AND DELETE).
The conditional expressions used in MySQL are the same as in PHP and are covered in PHP Branches and Conditions.
Note: When testing for equality in MySQL a single = character is used as compared to the double == characters in PHP.
Example of Select with Where Clause
mysql> SELECT firstname, lastname, age FROM people WHERE age < 40;
+---------+--------+---+
|firstname|lastname|age|
+---------+--------+---+
|Mary |Smith |36 |
+---------+--------+---+
1 row in set (0.03 sec)
mysql>
More complex expressions may be used in WHERE statements using the Boolean operators AND and OR.
mysql> SELECT firstname, lastname FROM people WHERE age < 40 AND county = "Cumbria";
+---------+--------+
|firstname|lastname|
+---------+--------+
|Mary |Smith |
+---------+--------+
1 row in set (0.06 sec)
mysql> SELECT firstname, lastname FROM people WHERE age < 40 OR firstname = "Dave";
+---------+--------+
|firstname|lastname|
+---------+--------+
|Dave |Clark |
|Mary |Smith |
|Dave |Smith |
+---------+--------+
3 rows in set (0.01 sec)
mysql>
Using the Order By Clause
The ORDER BY clause sorts the results for output after the query has been carried out.
The sorting may be with one or more attributes separated by commas.
Examples of Select with Order By
mysql> SELECT firstname, lastname FROM people ORDER BY lastname;
+---------+--------+
|firstname|lastname|
+---------+--------+
|Dave |Clark |
|Mary |Smith |
|Dave |Smith |
+---------+--------+
3 rows in set (0.01 sec)
mysql> SELECT firstname, lastname FROM people ORDER BY lastname, firstname;
+---------+--------+
|firstname|lastname|
+---------+--------+
|Dave |Clark |
|Dave |Smith |
|Mary |Smith |
+---------+--------+
3 rows in set (0.01 sec)
mysql>
The ORDER BY clause can sort in ascending or descending order using ASC and DESC respectively. The default is ASC.
Example of Select with Order By Desc
mysql> SELECT firstname, lastname FROM people ORDER BY lastname, firstname DESC;
+---------+--------+
|firstname|lastname|
+---------+--------+
|Dave |Clark |
|Mary |Smith |
|Dave |Smith |
+---------+--------+
3 rows in set (0.03 sec)
mysql>
Using the Group By Clause
The GROUP BY clause is used to list common characteristics of a group of rows as opposed to the details of individual rows.
The example below lists last names and how many people in the table share that last name.
Example of Select with Group By
mysql> SELECT lastname, COUNT(*) FROM people GROUP BY lastname;
+--------+--------+
|lastname|COUNT(*)|
+--------+--------+
|Clark | 1 |
|Smith | 2 |
+--------+--------+
2 rows in set (0.03 sec)
mysql>
MySQL provides functions that may be used with the GROUP BY clause.
COUNT() returns the number of rows in a group of selected rows.
SUM() returns the sum of numeric attributes in a group of selected rows.
MAX() returns the maximum value of a numeric or string attribute in a group of selected rows.
MIN() returns the minimum value of a numeric or string attribute in a group of selected rows.
AVG() returns the average value of the numeric attributes in a group of selected rows.
The Having Clause
The HAVING clause allows a condition to be attached to the way rows are grouped with the GROUP BY clause.
The example below again lists last names, and how many people in the table share that last name, but leaves out last names belonging to only one person.
Example of Select with Group By and Having
mysql> SELECT lastname, COUNT(*) FROM people GROUP BY lastname HAVING COUNT(*) > 1;
+--------+--------+
|lastname|COUNT(*)|
+--------+--------+
|Smith | 2 |
+--------+--------+
1 row in set (0.04 sec)
mysql>
The Distinct Operator
The DISTINCT operator is used to return only one example of each row from a table.
The example below returns one example of last name from the people table.
Examples of Select with Distinct
mysql> SELECT DISTINCT lastname FROM people;
+--------+
|lastname|
+--------+
|Clark |
|Smith |
+--------+
2 rows in set (0.02 sec)
mysql> SELECT DISTINCT country FROM people;
+-------+
|country|
+-------+
|UK |
+-------+
1 row in set (0.02 sec)
mysql>
Limiting Results
MySQL provides the LIMIT operator to limit the number of results requested when operating on large tables.
Example of Select using Limit
mysql> SELECT * FROM people LIMIT 2;
+------------+--------+------------+------------+-------+---+
|id|firstname|lastname|address |county |country|age|
+------------+--------+------------+------------+-------+---+
|1 |Dave |Clark |Main Street |West Lothian|UK |40 |
|2 |Mary |Smith |North Street|Cumbria |UK |36 |
+------------+--------+------------+------------+-------+---+
2 rows in set (0.00 sec)
Free Web Design Tutorials
QueryingJoin QueriesFunctionsTable LockingPHP/MySQL Functions
Previous - Insert, Update and Delete
Next - Join Queries 
