MySQL SELECT Statement - Coding

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.

FAQs on MySQL SELECT Statement

What is the MySQL SELECT statement used for?

The MySQL SELECT statement is used to retrieve data from one or more tables in a database. It allows you to specify which columns of data you want to fetch, apply conditions to filter rows, sort the results, and limit the number of rows returned.

Can I use the MySQL SELECT statement to fetch data from multiple tables?

Yes, you can fetch data from multiple tables using the MySQL SELECT statement with the JOIN clause. This allows you to combine related data from different tables based on a common column.

How do I retrieve all columns from a table using SELECT?

To retrieve all columns from a table, you can use the wildcard * in your SELECT statement. For example:
SELECT * FROM table_name;

Reffered: https://www.geeksforgeeks.org

Databases

Related
How to Select Row With Max Value in MySQL?
How to Rename a Column in MySQL?
How to Efficiently Convert Rows to Columns in SQL?
How to Remove Duplicate Records Except a Single Record in MySQL?
How to Select Row With Max Value in SQL?

Type:	Geek
Category:	Coding
Sub Category:	Tutorial
Uploaded by:	Admin
Views:	11