MySQL Select Statement
In this lesson you will be learn how to use SELECT statement in MySQL and you can also learn how to use SELECT statement with WHERE clause. The SELECT statement is used to retrieve the records from the table. There are some keywords uses in SELECT statement that are described in the following table
| Keywords | Description |
| SELECT | SELECT statement is used to retrieve fields from one or more tables. |
| FROM | Tables containing to the fields. |
| WHERE | The WHERE clause is used to describe the criteria to restrict the records retrieved. |
| GROUP BY | The GROUP BY clause is used to determine how the records should be grouped. |
| HAVING | HAVING clause used with GROUP BY to define the criteria for grouped records |
| ORDER BY | The ORDER BY clause is used to described the criteria for ordering the record. |
| LIMIT | The LIMIT clause is used to restrict the limit of number of records retrieved. |
The simple SELECT statement is used to retrieve the all records from table. By the following example you can retrieve the full list of Emp table.
| mysql> SELECT * FROM Emp; |
If you want to retrieve only some fields from a table, then you have to provide a comma separated list of column names. By the following example you select Name, City and Age fields from the Emp table.
| mysql> SELECT Name, City, Age from EMP; |
The WHERE clause is used to limit the number of records. The comparison operators are used with WHERE clause to limit the number of records. Comparison operator?s list are given below:
| Operator | Description |
| = | Equal to |
| <> or != | Not equal to |
| < | Less then |
| < = | Less then or equal to |
| > | Greater then |
| > = | Greater then or equal to |
| Like | Used for comparing string |
| Between | Used for checking value between a range. |
| IN | Used to check values in a list |
| NOT IN | Used to check the value is not in the list. |
% Character - If you are working with Strings, then % character can be used as a wildcard. By the following example you can retrieve the all fields from Emp table where the Designation field contain the text, 'Manager'.
| mysql> SELECT * FROM Emp WHERE Designation LIKE '%Manager%'; |
_ character - The underscore character can be used as a placeholder. By the following example you can selects the all records from the table Emp, where the Name starts with ?R? followed by four characters. For this we have to use four underscores.
| mysql> SELECT * FROM Emp WHERE Name LIKE 'R____'; |
BETWEEN Clause - The BETWEEN clause can be used with numbers, dates and text. The following example is used to retrieve all fields Emp table where the Salary is between 10000 AND 20000.
| mysql> SELECT * FROM Emp WHERE Salary BETWEEN 10000 AND 20000; |
OR Clause - The OR clause is used to check the values against the range of values that have been specified. The following example retrieves the list of all records where the Designation is either Manager or Assistant in the Emp table.
| mysql> SELECT * FROM Emp WHERE Designation ='Manager' OR 'Assistant'; |
IN Clause - The IN clause is used to check the values against to many values that have been specified in IN clause. The following example retrieves the list of all records where the Designation is either Manager or Assistant in the Emp table.
| mysql> SELECT * FROM Emp WHERE Designation IN ('Manager', 'Assistant'); |
NOT IN Clause - You can use the NOT modifier with IN clause for checking the values,. Which are not within the list. The following example retrieves the list of all records where the Designation is not equal to Manager or Assistant in the Emp table.
| mysql> SELECT * FROM Emp WHERE Designation NOT IN ('Manager', 'Assistant'); |
The following list shows you a Aggregate Function that available in MySQL.
- AVG( );
The AVG( ) function returns the average value in a group of records. Example of the AVG( ) function:-SELECT AVG(Profit) FROM Income GROUP BY EmpId; - COUNT( );
The COUNT( ) function returns the number of records in a group of records. Example of the COUNT( ) function:-SELECT COUNT(Profit) FROM Income GROUP BY EmpId; - MAX( );
The MAX( ) function return the maximum value in a group of records. Example of the MAX( ) function:-SELECT MAX(Profit) FROM Income GROUP BY EmpId; - MIN( );
The MIN( ) function returns minimum value in a group of records. Example of the MIN( ) function:-SELECT MIN(Profit) FROM Income GROUP BY EmpId; - SUM( );
The SUM( ) function return the sum of the field. Example of the SUM() function :SELECT SUM(Profit) FROM Income GROUP BY EmpId ;
The HAVING Clause
As you know the WHERE clause is used to restrict the records in a query. But if you want to restrict the records by using the Aggregate function then you have to use the HAVING clause. The HAVING clause restricts the records after they have been grouped.
The following example shows the list of all Employees who did profit over 10000 on average.
| mysql> SELECT AVG(Profit) FROM Income GROUP BY EmpId HAVING AVG(Profit) > 10000; |
The ORDER BY Clause
The ORDER BY clause can be used to set the order of the retrieved records. The following example shows the list of all employees in the Emp table in alphabetical order.
In this clause we can use the ASC or DESC modifiers to set the order of records in ascending or descending order. If any modifier is not provided then the records are listed in ascending order.
| mysql> SELECT Name FROM Emp ORDER BY Name; |
The LIMIT Clause
The LIMIT clause can be used to limit the number of records that have been returned by the SELECT statement. You can specify the start row, and number of records retrieved.
| mysql> SELECT * FROM Emp LIMIT 0,10; |