To filter the results with SELECT, INSERT, UPDATE and DELETE clause, we need to use WHEREClause.

Syntax:

    WHERE conditions;  

Beside we can use WHERE clause with ‘AND’ or ‘OR’ condition. We can also use WHERE Clause with combination of AND & OR conditions.

To remove duplicate records from the table we have to use MySQL DISTINCT clause. DISTINCT clause is only used with SELECT statement.

Syntax:

SELECT DISTINCT expressions  
FROM tables  
[WHERE conditions];

Example:

    SELECT DISTINCT address  
    FROM officers;  

We can also use DISTINCTClause with multiple expressions.

MySQL FROM Clause

The MySQL FROM Clause is used to select some records from a table. It can also be used to retrieve records from multiple tables using JOIN condition.

Syntax:

    FROM table1  
    [ { INNER JOIN | LEFT [OUTER] JOIN| RIGHT [OUTER] JOIN } table2  
    ON table1.column1 = table2.column1 ]  

Example: Retrieve data from one table

    SELECT *  
    FROM officers  
    WHERE officer_id <= 3;  

Retrieve data from two tables with inner join

SELECT officers.officer_id, students.student_name  
FROM students  
INNER JOIN officers  
ON students.student_id = officers.officer_id; 

Retrieve data from two tables using outer join

    SELECT officers.officer_id, students.student_name  
    FROM officers  
    LEFT OUTER JOIN students  
    ON officers.officer_id = students.student_id;  

If we want tosort the records in ascending or descending order we need to use ORDER BY clause.

Syntax:

    SELECT expressions  
    FROM tables  
    [WHERE conditions]  
    ORDER BY expression [ ASC | DESC ];  

To sort the data in ascending order we have to use ASC. If we want to sort the data in descending order, then we need to use DESC. In the syntax both ASC& DESC are optional.

Example:


Without using ASC/DESC attribute

SELECT *  
FROM officers  
WHERE address = 'Lucknow'  
ORDER BY officer_name;

With ASC attribute

    SELECT *  
    FROM officers  
    WHERE address = 'Lucknow'  
    ORDER BY officer_name ASC;  

With DESC attribute

SELECT *  
FROM officers  
WHERE address = 'Lucknow'  
ORDER BY officer_name DESC; 

Using both ASC and DESC attributes

SELECT officer_name, address  
FROM officers  
WHERE officer_id < 5  
ORDER BY officer_name DESC, address ASC; 

To collect data from multiple records and group the result by one or more column, then we have to use MYSQL GROUP BY Clause. It is generally used with SELECT statement. We can also use some aggregate functions. Like AVG, SUM, COUNT, MAX, MIN etc.

Syntax:

SELECT expression1, expression2, ... expression_n,   
aggregate_function (expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n; 

The expression is not encapsulated within an aggregate function and must be included in the GROUP BY clause.

Example:


With COUNTfunction:

    SELECT address, COUNT(*)  
    FROM   officers   
    GROUP BY address;   

With SUM function

    SELECT emp_name, SUM(working_hours) AS "Total working hours"  
    FROM employees  
    GROUP BY emp_name;  

With MIN function

    SELECT emp_name, MIN(working_hours) AS "Minimum working hour"  
    FROM employees  
    GROUP BY emp_name;  

With MAX function

SELECT emp_name, MAX (working_hours) AS "Minimum working hour"  
FROM employees  
GROUP BY emp_name;

With AVG function

    SELECT emp_name, AVG(working_hours) AS "Average working hour"  
    FROM employees  
    GROUP BY emp_name;  

MySQL HAVING Clause

HAVING Clause always returns rows where condition is TRUE. It is used with GROUP BY clause.

Syntax:

    SELECT expression1, expression2, ... expression_n,   
    aggregate_function (expression)  
    FROM tables  
    [WHERE conditions]  
    GROUP BY expression1, expression2, ... expression_n  
    HAVING condition;  

With SUM function

    SELECT emp_name, SUM(working_hours) AS "Total working hours"  
    FROM employees  
    GROUP BY emp_name  
    HAVING SUM(working_hours) > 5;