MySQL SELECT

The SELECT statement is used to retrieve data from a table.

SELECT * FROM students;

This query returns all columns and all rows from the students table.

The basic syntax is:

SELECT column_name
FROM table_name;

Example:

SELECT name
FROM students;

The asterisk * means all columns.

SELECT *
FROM students;

This displays every column available in the students table.

You can retrieve only one column.

SELECT name
FROM students;

Only the student names will be displayed.

Separate multiple column names using commas.

SELECT name, age
FROM students;

This returns only the name and age columns.

A common query is to retrieve the ID and name of students.

SELECT id, name
FROM students;

SELECT can be combined with WHERE to retrieve specific records.

SELECT *
FROM students
WHERE age = 20;

This returns students whose age is 20.

Text values are normally written inside quotes.

SELECT *
FROM students
WHERE name = 'Rahul';

This returns records where the name is Rahul.

You can use comparison operators with SELECT.

SELECT *
FROM students
WHERE age > 18;

This returns students whose age is greater than 18.

The AND operator can be used to apply multiple conditions.

SELECT *
FROM students
WHERE age > 18 AND course = 'Python';

Both conditions must be satisfied.

The OR operator allows either condition to be true.

SELECT *
FROM students
WHERE course = 'Python'
OR course = 'Java';

You can sort SELECT results using ORDER BY.

SELECT *
FROM students
ORDER BY name;

By default, the results are sorted in ascending order.

Use DESC to sort data in descending order.

SELECT *
FROM students
ORDER BY age DESC;

This displays students from higher age to lower age.

LIMIT restricts the number of rows returned.

SELECT *
FROM students
LIMIT 5;

This returns up to five rows.

DISTINCT removes duplicate values from the result.

SELECT DISTINCT course
FROM students;

This displays each course only once in the result.

DISTINCT can also be used with multiple columns.

SELECT DISTINCT course, age
FROM students;

MySQL considers the combination of the selected columns when removing duplicates.

An alias can give a column a temporary display name.

SELECT name AS student_name
FROM students;

The result will display the column using the alias student_name.

A table can also have a temporary alias.

SELECT s.name
FROM students AS s;

Here, s is an alias for the students table.

SELECT can perform calculations.

SELECT 10 + 20;

Another example:

SELECT fee, fee + 1000 AS increased_fee
FROM students;

The COUNT() function can count records.

SELECT COUNT(*) AS total_students
FROM students;

This returns the total number of rows in the students table.

The SUM() function can calculate the total of numeric values.

SELECT SUM(fee) AS total_fee
FROM students;

This calculates the total value of the fee column.

The AVG() function calculates an average.

SELECT AVG(fee) AS average_fee
FROM students;

MIN() returns the smallest value and MAX() returns the largest value.

SELECT MIN(fee) AS minimum_fee,
       MAX(fee) AS maximum_fee
FROM students;

You can specify the database name before the table name.

SELECT *
FROM training_db.students;

This is useful when working with multiple databases.

You can combine WHERE, AND, OR, and comparison operators.

SELECT name, course, fee
FROM students
WHERE fee > 10000
AND course = 'Python';

This retrieves Python students whose fee is greater than 10,000.

A simple SELECT workflow is:

1. Select the database.
2. Identify the table.
3. Choose the required columns.
4. Add conditions if required.
5. Sort the results if required.
6. Limit the number of records if required.

USE training_db;

SELECT name, course, fee
FROM students
WHERE fee > 10000
ORDER BY fee DESC
LIMIT 5;

MySQL Workbench provides a SQL editor where you can write and execute SELECT queries.

SELECT *
FROM students;

The query result is displayed in the result grid.

PHP applications commonly use SELECT queries to retrieve data from MySQL.

$sql = "SELECT id, name, course
        FROM students";

$stmt = $pdo->query($sql);

$students = $stmt->fetchAll();

The retrieved records can then be displayed on a webpage.

Suppose we want to display students enrolled in Python with a fee greater than 10,000.

SELECT id, name, mobile, course, fee
FROM students
WHERE course = 'Python'
AND fee > 10000
ORDER BY name ASC;

This is a practical example of combining several SELECT features.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    mobile VARCHAR(15),
    course VARCHAR(100),
    fee DECIMAL(10,2),
    age INT
);

INSERT INTO students
(name, mobile, course, fee, age)
VALUES
('Rahul', '9876543210', 'Python', 15000.00, 22),
('Priya', '9876543211', 'Java', 18000.00, 21),
('Amit', '9876543212', 'Python', 12000.00, 24),
('Neha', '9876543213', 'PHP', 10000.00, 20);

SELECT name, course, fee
FROM students
WHERE fee > 10000
ORDER BY fee DESC
LIMIT 3;

This query selects student names, courses, and fees, filters the records, sorts them by fee, and returns up to three records.