MySQL - Select Query

MySQL - The SELECT Query

After creating tables and populating them with data, the next logical step is to retrieve and inspect that data. The SELECT statement is the primary tool for this in SQL. It’s the most frequently used command, allowing you to fetch data from your database tables and present it as a structured result set.

The Core of Data Retrieval: The SELECT Statement

The MySQL SELECT statement is used to query the database and retrieve data that matches criteria that you specify. The result is always returned in a temporary table called a result-set.

Note: You can execute this command directly via a MySQL client (like the command-line mysql> prompt or a GUI tool) or programmatically from an application written in languages like PHP, Node.js, Java, or Python.

Syntax

Here is the generalized syntax of a SELECT statement, showing its most common clauses:

SELECT
    column1, column2, ... -- Specify columns you want
FROM
    table_name -- Specify the table to query from
[WHERE condition] -- Optional: Filter rows based on a condition
[ORDER BY column_to_sort ASC|DESC] -- Optional: Sort the results
[LIMIT number_of_rows] -- Optional: Restrict the number of rows returned
[OFFSET starting_row]; -- Optional: Skip a number of rows before returning results

• To select all columns from a table, you can use the asterisk wildcard: SELECT * FROM table_name;. While convenient, it’s a best practice to explicitly list the columns you need, especially in production code.
• The WHERE clause is used to filter records and extract only those that fulfill a specific condition.
• The ORDER BY clause sorts the result-set in ascending (ASC, default) or descending (DESC) order.
• The LIMIT and OFFSET clauses are often used together for pagination (e.g., showing 10 results per page).

Practical Examples: Querying a Database

Let’s work with a practical example. First, we’ll create a modern customers table.

Example Setup

This query creates a customers table with an auto-incrementing primary key, which is a common and recommended practice.

-- Best practice: Use AUTO_INCREMENT for a unique ID to be generated for each new row.
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

Now, let’s insert some sample data into the table:

INSERT INTO customers (name, age, address, salary)
VALUES
    ('Ramesh', 32, 'Ahmedabad', 2000.00),
    ('Khilan', 25, 'Delhi', 1500.00),
    ('Kaushik', 23, 'Kota', 2000.00),
    ('Chaitali', 25, 'Mumbai', 6500.00),
    ('Hardik', 27, 'Bhopal', 8500.00),
    ('Komal', 22, 'Hyderabad', 4500.00),
    ('Muffy', 24, 'Indore', 10000.00);

To Retrieve All Columns

To fetch all data from the customers table, use the SELECT * statement:

SELECT * FROM customers;

This will return all rows and all columns from the table.

id	name	age	address	salary
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

To Retrieve Specific Columns

To be more efficient, specify only the columns you need:

SELECT id, name, salary FROM customers;

The result set will only contain the requested columns:

id	name	salary
1	Ramesh	2000.00
2	Khilan	1500.00
3	Kaushik	2000.00
4	Chaitali	6500.00
5	Hardik	8500.00
6	Komal	4500.00
7	Muffy	10000.00

Using Aliases for Clarity

You can rename a column in your result-set using the AS keyword. This is called aliasing. It doesn’t change the column name in the table; it only changes the label in the output. This is useful for making results more readable or avoiding column name conflicts in JOINs.

SELECT name AS customer_name, salary AS monthly_income FROM customers;

The output columns are now labeled with the aliases:

customer_name	monthly_income
Ramesh	2000.00
Khilan	1500.00
Kaushik	2000.00
Chaitali	6500.00
Hardik	8500.00
Komal	4500.00
Muffy	10000.00

Performing Calculations in Queries

The SELECT statement can also perform calculations. You can compute values based on table data or even run standalone calculations.

Example: Calculating a Bonus

Here, we calculate a potential 10% annual bonus for each customer based on their salary.

SELECT name, salary, (salary * 12 * 0.10) AS potential_annual_bonus FROM customers;

The result includes a new, calculated column:

name	salary	potential_annual_bonus
Ramesh	2000.00	2400.00
Khilan	1500.00	1800.00
Kaushik	2000.00	2400.00
Chaitali	6500.00	7800.00
Hardik	8500.00	10200.00
Komal	4500.00	5400.00
Muffy	10000.00	12000.00

Connecting and Querying from Application Code (Securely)

A crucial aspect of software development is interacting with the database from your application code. The most important principle is to never build queries by concatenating strings with user input. This creates a severe security vulnerability called SQL Injection. Always use prepared statements (or parameterized queries), where the SQL command and the data are sent to the database separately.

Modern Client Program Examples

Below are modern, secure examples for fetching data in popular programming languages. They assume you have the necessary database drivers/libraries installed (e.g., via composer, npm, maven, pip). Credentials should be stored securely, for example, in environment variables, not hardcoded.

PHP
Node.js
Java
Python



Using PDO for database interaction is a modern best practice in PHP due to its support for multiple database systems and robust prepared statements.

<?php
$host = getenv('DB_HOST') ?: '127.0.0.1';
$db   = getenv('DB_NAME') ?: 'TUTORIALS';
$user = getenv('DB_USER') ?: 'root';
$pass = getenv('DB_PASS') ?: 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully.\n";

    // Fetch all customers
    $stmt = $pdo->query('SELECT id, name, age FROM customers');

    echo "All Customers:\n";
    while ($row = $stmt->fetch()) {
        echo sprintf("ID: %d, Name: %s, Age: %d\n", $row['id'], $row['name'], $row['age']);
    }
} catch (\PDOException $e) {
    // In a real app, log this error, don't just echo it.
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>



Modern Node.js development heavily relies on `async/await` for handling asynchronous operations like database queries. The `mysql2/promise` library provides a promise-based API that works perfectly with this pattern.

const mysql = require('mysql2/promise');

async function main() {
  let connection;
  try {
    // Use environment variables for credentials in a real application
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || 'password',
      database: process.env.DB_NAME || 'TUTORIALS',
    });

    console.log('Connected successfully!');

    // The [rows, fields] destructuring is a feature of mysql2
    const [rows, fields] = await connection.execute('SELECT id, name, salary FROM customers');

    console.log('Query Results:');
    console.table(rows);

  } catch (err) {
    console.error('Error connecting or querying the database:', err);
  } finally {
    if (connection) {
      await connection.end();
      console.log('Connection closed.');
    }
  }
}

main();



Modern Java uses a `try-with-resources` block to automatically manage resources like the `Connection` and `Statement`, ensuring they are always closed. Using a `PreparedStatement` is the standard for executing queries, even those without parameters, for security and performance reasons.

import java.sql.*;

public class SelectQueryExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";

        String sql = "SELECT id, name, age, salary FROM customers";

        try (Connection con = DriverManager.getConnection(url, user, password);
             PreparedStatement pst = con.prepareStatement(sql);
             ResultSet rs = pst.executeQuery()) {

            System.out.println("Connected and query executed successfully!");
            System.out.println("Customer Records:");
            System.out.printf("%-5s %-20s %-5s %-10s\n", "ID", "Name", "Age", "Salary");
            System.out.println("---------------------------------------------");

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                double salary = rs.getDouble("salary");
                System.out.printf("%-5d %-20s %-5d %-10.2f\n", id, name, age, salary);
            }
        } catch (SQLException e) {
            // In a real application, use a logging framework
            e.printStackTrace();
        }
    }
}



The `mysql-connector-python` library is the official driver from Oracle. The best practice is to use a `try...except...finally` block to ensure the database connection is closed, even if errors occur. Parameterized queries are achieved by passing a tuple of arguments to the `execute` method.

import mysql.connector
from mysql.connector import Error

def main():
    connection = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='TUTORIALS',
            user='root',
            password='password'
        )

        if connection.is_connected():
            print('Connected successfully!')
            cursor = connection.cursor(dictionary=True) # dictionary=True returns rows as dicts

            cursor.execute("SELECT id, name, salary FROM customers")
            records = cursor.fetchall()

            print("\nTotal number of rows is: ", cursor.rowcount)
            print("\nPrinting each row")
            for row in records:
                print(f"Id: {row['id']}, Name: {row['name']}, Salary: {row['salary']}")

    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

if __name__ == '__main__':
    main()