In MySQL, a SELECT query is used to extract data from one or more database tables. Among the most basic and often used SQL commands is this one. The SELECT statement will be thoroughly examined below, including its syntax, clauses, sophisticated features, and real-world applications. In this article, We will learn about MySQL Select Statement with the help of examples.
Basic Syntax of SELECT Statement
The basic syntax of a SELECT statement is:
SELECT column1, column2, ... FROM table_name;
column1, column2, … These are the column names you want to retrieve.
table_name The name of the table from which you want to fetch data.
When we want to retrieve all columns from a table, we should use the asterisk (*).
Example
SELECT * FROM table_name;
1. Selecting Specific Columns
When we want to fetch only specific column from a table. Then We add those columns in query through comma separated.
Example
SELECT first_name, last_name FROM users;
Above query will fetch only first_name & last_name from the users table.
2. Using WHERE Clause for Filtering
Sometimes, we need to filtered records from any table based on some conditions. Then we use Where clause to add condition in any query.
Example
SELECT * FROM users WHERE department = 'IT';
Above query will display those users records who belongs to IT department.
In Where clause, we use multiple type of operators for filtering.
| Operators | Name |
| = | Equal To |
| != or <> | Not Equal To |
| > | Greater Than |
| < | Less Than |
| >= | Greater Than Or Equal To |
| <= | Less Than Or Equal To |
Examples
SELECT * FROM users WHERE marks > 120;
SELECT * FROM users WHERE city = 'Delhi';
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE department != 'IT';
3. Using Logical Operators
Sometimes, we need multiple conditions in a single query, we use Logical Operators. There are three Logical Operators AND, OR, NOT.
AND Operator
SELECT * FROM users WHERE department = 'IT' AND marks > 75;
Above query will display those records who belongs to IT department and their marks is Greater Than 75.
OR Operator
SELECT * FROM users WHERE department = 'IT' OR department = 'Art';
Above query will display those records who belongs to IT department or Art department.
NOT Operator
SELECT * FROM users WHERE NOT department = 'IT';
Above query will display those records who does not belongs to IT department, means it will display all department users except IT.
Also read about Important Functions in MySQL
4. Ordering Results Using ORDER BY
Sometimes, we need to display records in Ascending or Descending order based on one or more columns. In this case we use ORDER BY clause.
Ascending Order (Default)
SELECT * FROM users ORDER BY first_name;
Above query will display all records by first_name in Ascending order, means it display in A-Z ordering.
Descending Order
SELECT * FROM users ORDER BY first_name DESC;
Above query will display all records by first_name in Descending order, means it display in Z-A ordering.
Sorting by Multiple Columns
SELECT * FROM users ORDER BY department ASC, marks DESC;
Above query will display all records by department in Ascending order & then by marks in Descending order within each department.
5. Limiting Results Using LIMIT
Sometimes, we need to display only 10 records from table. In this case, We use LIMIT clause for restriction on rows.
Example
SELECT * FROM users LIMIT 10;
Above query will display only first 10 users.
Using OFFSET
The OFFSET keyword is used to skip rows before returning results.
SELECT * FROM users LIMIT 10 OFFSET 5;
Above query will display 10 users but skips the first 5 rows.
6. Using DISTINCT to Remove Duplicates
Sometimes, We need to remove duplicate data from records, we use DISTINCT keyword to fetch unique values from a column.
Example
SELECT DISTINCT department FROM users;
Above query will display unique department names from the users table.
7. Using Aliases for Columns and Tables
Sometimes we need to provide temporary names to columns or tables, we use AS.
Column Alias
SELECT first_name AS Name, salary AS Income FROM employees;
Above query will rename first_name to Name and salary to Income in the output.
Table Alias
SELECT e.first_name, e.salary FROM employees AS e;
Above query will rename the employees table to e.
8. Using Aggregate Functions
A number of aggregate functions are available in MySQL for data calculations.
COUNT(): Counting Rows
SELECT COUNT(*) FROM employees;
Above query will display counts the total number of employees.
SUM(): Adding Values
SELECT SUM(salary) FROM employees;
Above query will calculates the total sum of all salaries..
AVG(): Calculating Average
SELECT AVG(salary) FROM employees;
Above query will finds the average salary.
MIN(): Finding Minimum value
SELECT MIN(salary) FROM employees;
Above query will finds the minimum salary.
MAX(): Finding Maximum value
SELECT MAX(salary) FROM employees;
Above query will finds the maximum salary.
9. Grouping Data Using GROUP BY
Sometimes, we need to display records by grouping of one or more column, we use GROUP BY clause.
Example
SELECT department, COUNT(*) FROM employees GROUP BY department;
Above query will counts employees in each department.
SELECT department, AVG(salary) FROM employees GROUP BY department;
Above query will finds the average salary for each department.
10. Filtering Groups Using HAVING
Sometimes, we need to filter grouped data, we use HAVING clause.
Example
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
Above query will fetches only departments where the average salary is greater than 50,000.
Conclusion
MySQL's SELECT statement is an effective tool for data retrieval and manipulation. Complex queries can be effectively executed by mixing several clauses, conditions, and functions. To interact with databases in MySQL, you must become proficient in SELECT.
