MySQL Clause

MySQL Clause


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.

MySQL Distinct Clause

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;


MySQL ORDER BY Clause

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;


MySQL GROUP BY Clause

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;
Post a Comment