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

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.

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.

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: 

% 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'.

_ 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.

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.

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.

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.

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.

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.

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.

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.