MySQL SELECT Statement :
The select statement is used to select data from a database.
The SELECT statement in MySQL is used to fetch data from one or more tables. We can retrieve records of all fields or specified fields that match specified criteria using this statement.
SELECT column1, column2, …
- You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
- You can fetch one or more fields in a single SELECT command.
- You can specify any condition using the WHERE clause.
For the FROM and WHERE clauses, the MySQL SELECT query can include other optional clauses:
- GROUP BY organizes the retrieved data by grouping them by columns.
- HAVING relates to GROUP BY. When this clause is applied, it selects and returns only those rows having the specified conditions TRUE.
- ORDER BY sorts the records in the returned result-set.
- DISTINCT removes duplicates from the result-set.
- LIMIT controls the number of rows retrieved by the query.
- ALL returns all matching rows.
- HIGH_PRIORITY determines that MySQL must execute the SELECT statement before all the UPDATE operators waiting for the same resource
Example of MySQL SELECT Query :
1 .Use SELECT with ORDER BY
Select all fields from one table
Let’s look at how to use a MySQL SELECT query to select all fields from a table.
SELECT * FROM order_details WHERE quantity >= 20 ORDER BY quantity DESC;
In this MySQL SELECT statement example, we’ve used * to signify that we wish to select all fields from the order_details table where the quantity is greater than or equal to 20. The result set is sorted by quantity in descending order.
2. Use SELECT with HAVING CLAUSE
Write a query to retrieve the number of students in each city where the number of students is > 1, and are sorted in a descending order.
SELECT COUNT (studentID), city
FROM students GROUP BY city
HAVING COUNT (studentID) >1
ORDER BY COUNT (studentsID) DES ;
COUNNT (studentID) = 2 City = Mumbai