MySQL SELECT Command - Complete Guide with Examples

What is the MySQL SELECT Statement?

The SELECT statement in MySQL is used to retrieve data from one or more tables in a database. It allows you to fetch specific rows, columns, or aggregated results based on your query.

Syntax:

SELECT column1, column2, ... 
FROM table_name 
WHERE condition;
  • column1, column2, ...: Specify the columns to retrieve. Use * to select all columns.
  • table_name: The name of the table to retrieve data from.
  • WHERE: (Optional) Filters the data based on conditions.

Examples of MySQL SELECT Statement

1. Select All Columns

To retrieve all columns from a table, use the * wildcard:

SELECT * FROM employees;

Output Example:

+----+---------+--------+
| id | name    | salary |
+----+---------+--------+
| 1  | Alice   | 50000  |
| 2  | Bob     | 55000  |
| 3  | Charlie | 60000  |
+----+---------+--------+

2. Select Specific Columns

To retrieve only certain columns:

SELECT name, salary FROM employees;

Output Example:

+---------+--------+
| name    | salary |
+---------+--------+
| Alice   | 50000  |
| Bob     | 55000  |
| Charlie | 60000  |
+---------+--------+

3. Using WHERE Clause

Filter rows based on conditions:

SELECT * FROM employees WHERE salary > 50000;

Output Example:

+----+---------+--------+
| id | name    | salary |
+----+---------+--------+
| 2  | Bob     | 55000  |
| 3  | Charlie | 60000  |
+----+---------+--------+

4. Sorting Results with ORDER BY

Sort data in ascending or descending order:

SELECT * FROM employees ORDER BY salary DESC;

Output Example:

+----+---------+--------+
| id | name    | salary |
+----+---------+--------+
| 3  | Charlie | 60000  |
| 2  | Bob     | 55000  |
| 1  | Alice   | 50000  |
+----+---------+--------+

5. Aggregate Functions (COUNT, SUM, AVG, MAX, MIN)

Perform calculations on data:

  • Count Total Rows:

SELECT COUNT(*) AS total_employees FROM employees;

Output:

+----------------+
| total_employees|
+----------------+
| 3              |
+----------------+

Calculate Total Salary:

SELECT SUM(salary) AS total_salary FROM employees;

6. Select with LIMIT

Retrieve a specific number of rows:

SELECT * FROM employees LIMIT 2;

Output Example:

+----+---------+--------+
| id | name    | salary |
+----+---------+--------+
| 1  | Alice   | 50000  |
| 2  | Bob     | 55000  |
+----+---------+--------+

Using SELECT in MySQL Workbench

  • Open MySQL Workbench.
  • Connect to your database server.
  • Write the SELECT query in the SQL editor.
  • Click Execute (lightning icon) to run the query.
  • View the results in the result grid below the editor.

Best Practices for SELECT Queries

Specify Columns

Specify Columns
Instead of using SELECT *, specify only the needed columns to improve performance.

Index Columns
Use indexes on columns frequently queried in the WHERE or ORDER BY clause for faster data retrieval.

Use Aliases for Clarity
Use column or table aliases for better readability:

SELECT name AS employee_name, salary AS employee_salary FROM employees;

Filter Data
Always use the WHERE clause to limit the data retrieved.

Test Queries
Test queries with LIMIT to verify the output before fetching large datasets.

MySQL SELECT

Quiz: Test Your Knowledge