List of MySQL Clauses

MySQL list of clauses; Through this tutorial, i am going to show you MySQL clauses like DISTINCT, FROM, GROUP BY, ORDER BY, HAVING, WHERE with the help of examples.

Let’s see following MySQL clauses with the help of examples; as follows:

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 MySQL WHERE Clause; as follows;

WHERE conditions;

Example 1 – MySQL WHERE Clause

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].

Example 2 – MySQL 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 of MySQL DISTINCT Clause

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

Example 1 – Mysql DISTINCT Clause

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 of MySQL FROM Clause

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

Example 1 – FROM Clause MySQL

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 of ORDER BY MySQL Clause

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

Example 1 – ORDER BY Clause MySQL

 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 of GROUP BY Clause MySQL

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

Example 1 – MySQL GROUP BY Clause

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 of MySQL HAVING Clause

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

Example 1 – HAVING Clause MySQL

 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.

List of MySQL Clauses