Querying Data from MySQL

In order to retrieve data from MySQL database server you use SELECT statement. Here is the simple form of it:

SELECT column_name1,column_name2...
FROM tables
[WHERE conditions]
[GROUP BY group
[HAVING group_conditions]]
[ORDER BY sort_columns]
[LIMIT limits];

The SELECT statement has many option clauses which you can use these or not. If you use, the order has to be appear as shown.

To select all columns in a table you can use (*) notation instead of listing all column name. For example, if you need to query all the columns in offices table just execute this query:

SELECT * FROM employees

Because of the long result so we don't listed here, you can see it on your PC. SELECT statement also allows you to to view some partial data of a table by listing columns' name after the SELECT keyword. For example if you need to view only first name, last name and job title of all employee you can do the following query :

SELECT lastname,firstname,jobtitle
FROM employees

The MySQL database server will return the record set like follows:

+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
| Jennings  | Leslie    | Sales Rep            |
| Thompson  | Leslie    | Sales Rep            |
| Firrelli  | Julie     | Sales Rep            |
| Patterson | Steve     | Sales Rep            |
| Tseng     | Foon Yue  | Sales Rep            |
| Vanauf    | George    | Sales Rep            |
| Bondur    | Loui      | Sales Rep            |
| Hernandez | Gerard    | Sales Rep            |
| Castillo  | Pamela    | Sales Rep            |
| Bott      | Larry     | Sales Rep            |
| Jones     | Barry     | Sales Rep            |
| Fixter    | Andy      | Sales Rep            |
| Marsh     | Peter     | Sales Rep            |
| King      | Tom       | Sales Rep            |
| Nishi     | Mami      | Sales Rep            |
| Kato      | Yoshimi   | Sales Rep            |
| Gerard    | Martin    | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set (0.00 sec)

WHERE Clause

WHERE clause enables you to select a particular rows which match its conditions or search criteria. In our example we can find the president of company by doing this query:

SELECT firstname,lastname,email
FROM employees
WHERE jobtitle="president"

And of course MySQL returns the result

+-----------+----------+------------------------------+
| firstname | lastname | email                        |
+-----------+----------+------------------------------+
| Diane     | Murphy   | dmurphy@classicmodelcars.com |
+-----------+----------+------------------------------+
1 row in set (0.00 sec)

DISTINCT

With DISTINCT keyword, you can eliminate the duplicated result from SELECT statement. For example, to find how many job title of all employee in employees table, we use DISTINCT keyword in SELECT statement like below:

SELECT DISTINCT jobTitle FROM employees;

And here are all job titles the employee has

+----------------------+
| jobTitle             |
+----------------------+
| President            |
| VP Sales             |
| VP Marketing         |
| Sales Manager (APAC) |
| Sale Manager (EMEA)  |
| Sales Manager (NA)   |
| Sales Rep            |
+----------------------+
7 rows in set (0.00 sec)

GROUP BY

If you need to find number of employee who hold each job, you can use GROUP BY clause. GROUP BY clause allows use to retrieve rows in group. Here is the query example:

SELECT count(*), jobTitle
FROM employees
GROUP BY jobTitle;

And here is the result

+----------+----------------------+
| count(*) | jobTitle             |
+----------+----------------------+
|        1 | President            |
|        1 | Sale Manager (EMEA)  |
|        1 | Sales Manager (APAC) |
|        1 | Sales Manager (NA)   |
|       17 | Sales Rep            |
|        1 | VP Marketing         |
|        1 | VP Sales             |
+----------+----------------------+
7 rows in set (0.02 sec)

HAVING Clause

HAVING clause usually use with GROUP BY clause to selecting a particular of group. For example:

SELECT count(*), jobTitle
FROM employees
GROUP BY jobTitle
HAVING count(*) = 1

This query select the job in the company for which we have one employee in each job title. HAVING clause select all group which have count(*) equal 1. Here is the output:

+----------+----------------------+
| count(*) | jobTitle             |
+----------+----------------------+
|        1 | President            |
|        1 | Sale Manager (EMEA)  |
|        1 | Sales Manager (APAC) |
|        1 | Sales Manager (NA)   |
|        1 | VP Marketing         |
|        1 | VP Sales             |
+----------+----------------------+
6 rows in set (0.00 sec)

Sorting with ORDER BY

The ORDER BY clause allows you to sort the result set on one or more column in ascending or descending order. To sort the result set in ascending order you use ASC and in descending order you use DESC keywords. By default, the ORDER BY will sort the result set in ascending order. For example, to sort the name of employee on the first name and job title you can execute the following query:

SELECT firstname,lastname, jobtitle
FROM employees
ORDER BY firstname ASC,jobtitle DESC;

+-----------+-----------+----------------------+
| firstname | lastname  | jobtitle             |
+-----------+-----------+----------------------+
| Andy      | Fixter    | Sales Rep            |
| Anthony   | Bow       | Sales Manager (NA)   |
| Barry     | Jones     | Sales Rep            |
| Diane     | Murphy    | President            |
| Foon Yue  | Tseng     | Sales Rep            |
| George    | Vanauf    | Sales Rep            |
| Gerard    | Hernandez | Sales Rep            |
| Gerard    | Bondur    | Sale Manager (EMEA)  |
| Jeff      | Firrelli  | VP Marketing         |
| Julie     | Firrelli  | Sales Rep            |
| Larry     | Bott      | Sales Rep            |
| Leslie    | Jennings  | Sales Rep            |
| Leslie    | Thompson  | Sales Rep            |
| Loui      | Bondur    | Sales Rep            |
| Mami      | Nishi     | Sales Rep            |
| Martin    | Gerard    | Sales Rep            |
| Mary      | Patterson | VP Sales             |
| Pamela    | Castillo  | Sales Rep            |
| Peter     | Marsh     | Sales Rep            |
| Steve     | Patterson | Sales Rep            |
| Tom       | King      | Sales Rep            |
| William   | Patterson | Sales Manager (APAC) |
| Yoshimi   | Kato      | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set (0.00 sec)