In order to retrieve data from MySQL database server you use SELECT statement. Here is the simple form of it:
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 column_name1,column_name2...
FROM tables
[WHERE conditions]
[GROUP BY group
[HAVING group_conditions]]
[ORDER BY sort_columns]
[LIMIT limits];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 employeesSELECT lastname,firstname,jobtitle
FROM employees+-----------+-----------+----------------------+ | 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"+-----------+----------+------------------------------+ | 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;+----------------------+ | 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;+----------+----------------------+ | 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+----------+----------------------+ | 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)
So if you like this blog's articles? Then don't wait and Subscribe, Explore and Give Your Comments on My Blog. Thanks for visiting and hope you will be a frequent visitor.
