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;