MySql Clauses

Clauses of select statement in Mysql

Where Clause

In SQL we use SELECT statement to select data/information from a table from the database. Here, the WHERE clause allows us to filter certain or specific records that exactly match a given condition. Therefore, It returns only those records which fulfill the specific conditions of the WHERE clause.WHERE clause is used in SELECT, UPDATE, DELETE statement, etc.

Syntax:

SELECT Column1,Column2,….ColumnN From Table_name WHERE [condition];

The SQL statement to get records from "EMP" table;

SQL Query to Select the Table:

select * from emp;

1. Select Query using Where:

select ename,sal,job,deptno from emp where deptno=30;

Output:

2. Update Query using Where:

update emp set sal=1500 where ename='WARD';

Output:

Now to verify whether records are updated or not.

select * from emp;

Ouput after update:

Mysql Distinct Clause

In MySQL DISTINCT Clause can be used within an MySQL statement to remove duplicate rows from the databse table and retrive only the unique data.

Syntax:

SELECT DISTINCT column1,column2 ….columnN FROM table_name [WHERE conditions];

SQL Query using Distinct Clause:

select distinct deptno from emp;

Output:

Group By Clause

The MYSQL Group By clause is used with select statement to arrange similar kinds of records into the groups.
Group by clause is placed after the where clause in the SQL statement.

Syntax:

SELECT Column1,Column2,….ColumnN From Table_name WHERE [condition] GROUP BY Column_Name;

SQL Query using Where and Group By:

select * from emp where sal>1000 group by deptno;

Output:

Having Clause

The MYSQL HAVING clause filters the data after the GROUP BY clause. It filters the grouped rows based on a specific condition and is typically used with aggregate functions like SUM, COUNT, AVG, MIN, and MAX because the WHERE clause couldn't be applied with aggregate functions.

Syntax:

SELECT Column FROM Table WHERE condition GROUP BY Column HAVING condition;

1. SQL Query using Having Clause:

select * from emp where sal>2000 group by deptno having sum(sal)>2000;

Output:

2. SQL Query using Having Clause:

select deptno,sum(sal) from emp where sal>2000 group by deptno having sum(sal)>2000;

Output:

Order By Clause

The MYSQL ORDER BY Clause can be used to sort records in ascending or descending order.The data will be sorted in ascending order whenever the ASC keyword is used with ORDER by clause, and the DESC keyword will sort the records in descending order.
By default, sorting in the SQL will be done using the ORDER BY clause in ASCENDING order if we didn't mention the sorting order.

Syntax:

SELECT column1,column2 FROM tables [WHERE conditions] ORDER BY column_name[ ASC | DESC ];

SQL Query using Order By:

select * from emp order by sal;

Default order is ASC (Ascending).

Output:

select * from emp order by sal desc;

Output: