Types of clauses in MySQL

MySQL clauses tutorial point; Through this tutorial, we will demonstrate about MySQL clauses like DISTINCT, FROM, GROUP BY, ORDER BY, HAVING, WHERE. And we will learn about MySQL clauses with simple and easy syntax & examples.

You should also learn MySQL date and time functions, which are used to MySQL clauses.

Types of clauses in MySQL

Let’s see following MySQL clauses with an examples:

• WHERE Clause MySQL
• DISTINCT Clause MySQL
• FROM Clause MySQL
• ORDER BY Clause MySQL
• GROUP BY Clause MySQL
• HAVING Clause MySQL

WHERE Clause MySQL

In MySQL WHERE Clause commonly used with SELECT statement, INSERT INTO statement, UPDATE statment and DELETE statement. Because it filter the record from database table.

Syntax

WHERE conditions;  

WHERE Clause MySQL Example

SELECT * FROM users
WHERE email = '[email protected]';

The above statement selects all the records from the database table users, where the value of the email column is equal to [email protected].

Where Clause with AND

 SELECT *  
 FROM users  
 WHERE first_name = 'tut'  
 AND id < 1000;  

The above statement selects all the records from the database table users, where the value of the name column is equal to tuts and it’s id less than 1000.

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 tables  
 [WHERE conditions];  

DISTINCT Clause MySQL Example

SELECT DISTINCT name
FROM users;  

This above query using the DISTINCT keyword to removed the duplicates data. Therefore, it will contain a unique value.

MySQL FROM Clause

The MySQL FROM Clause can be used to select some records from the database table. Using the JOIN Clause, it can also be used to obtain records from several database tables.

Syntax

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

FROM Clause MySQL Example

SELECT *  
FROM users  
WHERE id = 50;  

The above query selects all the records from the database table users, where the id value of the id column is equal to 50.

ORDER BY MySQL Clause

The MYSQL ORDER BY Clause can be used to sort records in ascending or descending order and to sort records by one or more database table fields.

Syntax

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

ORDER BY Clause MySQL Example

 SELECT *  
 FROM users  
 WHERE country = 'USA'  
 ORDER BY first_name ASC;  

The above query selects all records from the users database table, then orders them by the first_name field in ascending order.

 SELECT *  
 FROM users  
 WHERE country = 'USA'  
 ORDER BY first_name DESC;  

The above query selects all records from the users database table, then orders them by the first_name field in descending order.

GROUP BY Clause MySQL

The GROUP BY clause groups the returned record set by one or more columns. You specify which columns the result set is grouped by.

Syntax

SELECT column1, column2, … column_n,   
aggregate_function (column)  
FROM tables  
[WHERE conditions]  
GROUP BY column1, column2, … column_n;  

GROUP BY Clause MySQL Example

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

The above query selects all records from the users database table, then groups them by the address field.

HAVING Clause MySQL

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

Syntax

 SELECT column1, column2, … column_n,   
 aggregate_function (column)  
 FROM tables  
 [WHERE conditions]  
 GROUP BY column1, column2, … column_n  
 HAVING condition;   

HAVING Clause MySQL Example

 SELECT op_name, SUM(working_hours) AS "totalWorking"  
 FROM operators  
 GROUP BY op_name  
 HAVING SUM(working_hours) > 7;     

The above query selects all records from the users database table, then return the emp_name and sum of their working hours.

Conclusion

In this MySQL tutorial point – we have demonstrated about the MySQL clause with its syntax and example.

Recommended MySQL Tutorials