MySQL SELECT Query

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Introduction

In this chapter, we will learn how to use the SELECT statement in MySQL to retrieve data from a table. The SELECT statement is one of the most commonly used commands in SQL, allowing you to query data from one or more tables. We will cover the syntax, examples, and important considerations for using the SELECT statement.

Basic SELECT Query

To retrieve data from a table, we use the SELECT statement.

Syntax

SELECT column1, column2, ..., columnN FROM table_name;

• column1, column2, ..., columnN: The names of the columns you want to retrieve data from.
• table_name: The name of the table from which you want to retrieve data.

Example

SELECT first_name, last_name, email FROM employees;

This example retrieves the first_name, last_name, and email columns from the employees table.

SELECT * Query

To retrieve all columns from a table, you can use the asterisk (*) wildcard.

Syntax

SELECT * FROM table_name;

Example

SELECT * FROM employees;

This example retrieves all columns from the employees table.

SELECT with WHERE Clause

The WHERE clause is used to filter records based on specific conditions.

Syntax

SELECT column1, column2, ..., columnN FROM table_name WHERE condition;

• condition: The condition to filter the records.

Example

SELECT first_name, last_name, email FROM employees WHERE last_name = 'Sharma';

This example retrieves the first_name, last_name, and email columns from the employees table where the last_name is 'Sharma'.

SELECT with ORDER BY Clause

The ORDER BY clause is used to sort the result set in ascending or descending order.

Syntax

SELECT column1, column2, ..., columnN FROM table_name ORDER BY column_name [ASC|DESC];

• ASC: Sort the result set in ascending order (default).
• DESC: Sort the result set in descending order.

Example

SELECT first_name, last_name, email FROM employees ORDER BY hire_date DESC;

This example retrieves the first_name, last_name, and email columns from the employees table and sorts the result set by hire_date in descending order.

SELECT with LIMIT Clause

The LIMIT clause is used to specify the number of records to return.

Syntax

SELECT column1, column2, ..., columnN FROM table_name LIMIT number;

• number: The number of records to return.

Example

SELECT first_name, last_name, email FROM employees LIMIT 2;

This example retrieves the first two records from the employees table.

Full Example

Let's go through a full example where we create a table, insert data into it, and perform various SELECT queries.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create a Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. Insert Data into the Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04');

5. Basic SELECT Query:

SELECT first_name, last_name, email FROM employees;

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com
Priya	Singh	priya.singh@example.com
Amit	Kumar	amit.kumar@example.com
Neha	Verma	neha.verma@example.com

6. SELECT * Query:

SELECT * FROM employees;

Output

id	first_name	last_name	email	hire_date
1	Rahul	Sharma	rahul.sharma@example.com	2023-07-01
2	Priya	Singh	priya.singh@example.com	2023-07-02
3	Amit	Kumar	amit.kumar@example.com	2023-07-03
4	Neha	Verma	neha.verma@example.com	2023-07-04

7. SELECT with WHERE Clause:

SELECT first_name, last_name, email FROM employees WHERE last_name = 'Sharma';

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com

8. SELECT with ORDER BY Clause:

SELECT first_name, last_name, email FROM employees ORDER BY hire_date DESC;

Output

first_name	last_name	email
Neha	Verma	neha.verma@example.com
Amit	Kumar	amit.kumar@example.com
Priya	Singh	priya.singh@example.com
Rahul	Sharma	rahul.sharma@example.com

9. SELECT with LIMIT Clause:

SELECT first_name, last_name, email FROM employees LIMIT 2;

Output

first_name	last_name	email
Rahul	Sharma	rahul.sharma@example.com
Priya	Singh	priya.singh@example.com

Important Considerations

• Data Types: Ensure that the data types in your query match the data types of the columns.
• Indexing: Use indexing on columns that are frequently used in the WHERE clause to improve query performance.
• Security: Use parameterized queries to prevent SQL injection attacks.

Conclusion

The SELECT statement is used for retrieving data from a MySQL database. This chapter covered how to use the SELECT statement to query data from a table, including examples of basic queries, filtering, sorting, and limiting results. In the next chapter, we will learn how to update existing data in tables.