MySQL SELECT Statement (original) (raw)
Last Updated : 21 Jun, 2024
The MySQL SELECT statement is essential for fetching data from tables. It retrieves information and stores it in a result table, often referred to as a result set. Widely used in MySQL, SELECT is a fundamental command for querying databases.
This article covers the basics of **SELECT syntax and explores its advanced features with practical examples, helping you understand how to effectively retrieve and work with data in MySQL databases.
SELECT Statement in MySQL
In MySQL, the SELECT statement is used to fetch data from tables. It allows you to specify which columns of data you want to retrieve, and optionally, you can apply filters to only get the data that meets specific conditions.
This statement is essential for interacting with databases, enabling you to retrieve and display information as needed for various purposes such as reporting, analysis, or application functionalities.
The **MySQL SELECT statement uses various clauses like WHERE, GROUP BY, ORDER BY, etc. We can also use aggregate functions like SUM, COUNT, etc with SELECT statements to summarize data.
**Syntax:
The Basic **Syntax of the MySQL SELECT Statement is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC | DESC]
LIMIT number;
Explanation:
- **SELECT: Specifies the columns or expressions that you want to retrieve from the database table. You can use
*to select all columns. - **FROM: Indicates the table from which you want to fetch the data.
- **WHERE: Optional clause that allows you to specify conditions to filter the rows retrieved from the table. Only rows that satisfy the condition will be included in the result set.
- **ORDER BY: Optional clause used to sort the result set in ascending (**ASC) or descending (**DESC) order based on one or more columns.
- **LIMIT: Optional clause that restricts the number of rows returned by the query. It is useful when you only want to retrieve a certain number of rows from the result set.
Here, column1, column2, ... are the columns you want to retrieve. If you want to retrieve data from all columns/fields, you can use the following
**Syntax:
SELECT * FROM table_name
Demo MySQL Database
For this tutorial on MySQL SELECT statement, we will use the following MySQL table.
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Robert | Johnson | 75000 |
To quickly create this table on your local MySQL Workbench, enter the following MySQL query:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2) );
INSERT INTO employees VALUES
(1, 'John', 'Doe', 50000),
(2, 'Jane', 'Smith', 60000),
(3, 'Robert', 'Johnson', 75000);
MySQL SELECT Statement Examples
Let's explore some examples to learn how to write **SELECT statement queries.
Example 1: Selecting Specific Columns
Retrieve only **first_name and **last_name columns
SELECT first_name, last_name
FROM employees;
This example retrieves the **first_name and last_name columns from the **employees table.
**Output:
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Jane | Smith |
| Robert | Johnson |
+------------+-----------+
**Explanation:
- The query retrieves the **first_name and **last_name columns from the **employees table.
- The result set displays the names of all employees in the table.
Example 2: Selecting All Columns
This query will retrieve the entire employee table.
SELECT * from employees;
Output:
+-------------+------------+-----------+--------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+--------+
| 1 | John | Doe | 50000 |
| 2 | Jane | Smith | 60000 |
| 3 | Robert | Johnson | 75000 |
+-------------+------------+-----------+--------+
Example 3: Performing Arithmetic Operations
SELECT 32*32
**Output:
+-------+
| 32*32 |
+-------+
| 1024 |
+-------+
We can also use SELECT statements to perform, basic mathematical operations.
MySQL SELECT DISTINCT Statement
MySQL **SELECT DISTINCT Statement is used to retrieve only distinct data from a field/column.
It is very used to remove duplicates from the results.
**Syntax:
SELECT DISTINCT _column1, column2, ...
FROM _table_name
Conclusion
The **MySQL SELECT statement is crucial for fetching specific data from tables, whether retrieving entire rows or selected columns. It supports filtering with **WHERE, sorting with **ORDER BY, and limiting results with **LIMIT. Additionally, SELECT can perform calculations and handle distinct values with SELECT DISTINCT, making it versatile for various database querying needs.