Exploring the MySQL SELECT Command

The SELECT command is one of the most versatile and commonly used commands in MySQL, crucial for querying and retrieving data from your database. From fetching specific columns to applying distinct filters, understanding how to leverage SELECT efficiently can significantly enhance your database management capabilities.

AUTHOR
Cemil Tokatli 11 April 2025

MySQL's SELECT statement is at the core of most database operations, allowing you to retrieve and manipulate data with ease. In this article, we'll delve into the diverse functionalities of the SELECT command, focusing on essential concepts like SELECT, SELECT DISTINCT, SELECT COUNT, and selecting specific columns.

Setting Up Sample Tables

Before diving into SELECT commands, it's helpful to prepare the database structure you will use. Below are SQL statements to create and populate the tables used throughout this guide. These tables provide a basis for understanding how different SELECT operations work.

CREATE TABLE `customer` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `customer` (`id`, `first_name`, `last_name`, `email`, `city`, `country`) VALUES 
('1', 'John', 'Doe', 'john.doe@stashsoftware.com', 'Dublin', 'Republic of Ireland'),
('2', 'Jane', 'Doe', 'jane.doe@stashsoftware.com', 'Denver, Colorado', 'USA'),
('3', 'Katie', 'Doe', 'katie.doe@stashsoftware.com', 'Anaheim, California', 'USA'),
('4', 'Joe', 'Bloggs', 'joe.bloggs@stashsoftware.com', 'London', 'UK'),
('5', 'Joe', 'Schmoe', 'joe.schmoe@stashsoftware.com', 'San Francisco, California', 'USA'),
('6', 'Mary', 'Smith', 'mary.smith@stashsoftware.com', 'Austin, Texas', 'USA'),
('7', 'Max', 'Mustermann', 'max.mustermann@stashsoftware.com', 'Berlin', 'Germany'),
('8', 'Taro', 'Yamada', 'taro.yamada@stashsoftware.com', 'Osaka', 'Japan'),
('9', 'Jean', 'Dupont', 'jean.dupont@stashsoftware.com', 'Paris', 'France');

CREATE TABLE `invoices` (
  `invoice_id` int NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(100) DEFAULT NULL,
  `invoice_date` datetime DEFAULT NULL,
  `total_amount` decimal(10,2) DEFAULT NULL,
  `due_date` datetime DEFAULT NULL,
  `paid` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`invoice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `invoices` (`invoice_id`, `customer_name`, `invoice_date`, `total_amount`, `due_date`, `paid`) VALUES 
('1', 'Alice Johnson', '2024-11-14 10:30:00', '1423.00', '2024-12-14 00:00:00', '0'),
('2', 'Bob Smith', '2024-10-05 14:15:00', '239.99', '2024-11-05 00:00:00', '1'),
('3', 'Charlie Rose', '2024-09-21 09:00:00', '1023.50', '2024-10-21 00:00:00', '0'),
('4', 'Dana White', '2024-11-01 16:45:00', '5000.75', '2024-12-01 00:00:00', '1');

Basic SELECT Statement

The basic SELECT command retrieves data from one or more columns in a table. It's the foundation of querying in MySQL.

SELECT * FROM customer;

This command retrieves all columns and all rows from the customer table, providing a complete view of your data:

id first_name last_name email city country
1 John Doe john.doe@stashsoftware.com Dublin Republic of Ireland
2 Jane Doe jane.doe@stashsoftware.com Denver, Colorado USA
3 Katie Doe katie.doe@stashsoftware.com Anaheim, California USA
4 Joe Bloggs joe.bloggs@stashsoftware.com London UK
5 Joe Schmoe joe.schmoe@stashsoftware.com San Francisco, California USA
6 Mary Smith mary.smith@stashsoftware.com Austin, Texas USA
7 Max Mustermann max.mustermann@stashsoftware.com Berlin Germany
8 Taro Yamada taro.yamada@stashsoftware.com Osaka Japan
9 Jean Dupont jean.dupont@stashsoftware.com Paris France

Selecting Specific Columns with SELECT

To focus on specific data, you can list the columns you need to retrieve. This not only streamlines your query but also optimizes performance.

SELECT first_name, last_name, city FROM customer;

This command selects only the first_name, last_name, and city columns, providing a more targeted view of your data:

first_name last_name city
John Doe Dublin
Jane Doe Denver, Colorado
Katie Doe Anaheim, California
Joe Bloggs London
Joe Schmoe San Francisco, California
Mary Smith Austin, Texas
Max Mustermann Berlin
Taro Yamada Osaka
Jean Dupont Paris

String Formatting in SELECT

MySQL allows you to format strings directly in your SELECT queries. This is particularly useful when you want to merge columns or create custom string outputs.

Using CONCAT for Merging Data

For example, to combine the city and country fields into one formatted string, you can use the CONCAT function:

SELECT first_name, last_name, CONCAT(city, ', ', country) AS location FROM customer;

This query generates a list of customers with a single field called location, which merges the city and country fields:

first_name last_name location
John Doe Dublin, Republic of Ireland
Jane Doe Denver, Colorado, USA
Katie Doe Anaheim, California, USA
Joe Bloggs London, UK
Joe Schmoe San Francisco, California, USA
Mary Smith Austin, Texas, USA
Max Mustermann Berlin, Germany
Taro Yamada Osaka, Japan
Jean Dupont Paris, France

Using UPPER and LOWER for Case Modification

To standardize or modify the case of text data, MySQL provides functions like UPPER and LOWER. For example, if you want all email addresses to be lowercase, you can use:

SELECT first_name, last_name, LOWER(email) AS email FROM customer;

This query ensures that email addresses are all in lowercase, useful for consistency:

first_name last_name email
John Doe john.doe@stashsoftware.com
Jane Doe jane.doe@stashsoftware.com
Katie Doe katie.doe@stashsoftware.com
Joe Bloggs joe.bloggs@stashsoftware.com
Joe Schmoe joe.schmoe@stashsoftware.com
Mary Smith mary.smith@stashsoftware.com
Max Mustermann max.mustermann@stashsoftware.com
Taro Yamada taro.yamada@stashsoftware.com
Jean Dupont jean.dupont@stashsoftware.com

You can also apply UPPER to make all last names uppercase:

SELECT first_name, UPPER(last_name) AS last_name FROM customer;

This results in all last names being capitalized:

first_name last_name
John DOE
Jane DOE
Katie DOE
Joe BLOGGS
Joe SCHMOE
Mary SMITH
Max MUSTERMANN
Taro YAMADA
Jean DUPONT

Using TRIM to Clean Up Data

The TRIM function is useful for removing unwanted spaces from the beginning and end of a string, which can help clean up data inputs.

SELECT first_name, TRIM(city) AS city FROM customer;

If there were extra spaces in the city column, they would now be removed:

first_name city
John Dublin
Jane Denver, Colorado
Katie Anaheim, California
Joe London
Joe San Francisco, California
Mary Austin, Texas
Max Berlin
Taro Osaka
Jean Paris

Using SUBSTRING to Extract Parts of a String

The SUBSTRING function allows you to extract a portion of a string, which is particularly handy for parsing or reformatting data.

SELECT first_name, SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username FROM customer;

This extracts the username from the email address by cutting the string before the @ symbol:

first_name username
John john.doe
Jane jane.doe
Katie katie.doe
Joe joe.bloggs
Joe joe.schmoe
Mary mary.smith
Max max.mustermann
Taro taro.yamada
Jean jean.dupont

Using LENGTH to Determine String Length

The LENGTH function returns the length of a string in bytes. It's useful for checking data integrity or applying conditional logic based on string length.

SELECT first_name, email, LENGTH(email) AS email_length FROM customer;

This query returns the length of each email address:

first_name email email_length
John john.doe@stashsoftware.com 26
Jane jane.doe@stashsoftware.com 26
Katie katie.doe@stashsoftware.com 27
Joe joe.bloggs@stashsoftware.com 28
Joe joe.schmoe@stashsoftware.com 28
Mary mary.smith@stashsoftware.com 28
Max max.mustermann@stashsoftware.com 32
Taro taro.yamada@stashsoftware.com 29
Jean jean.dupont@stashsoftware.com 29

Using CHAR_LENGTH to Measure Character Length

CHAR_LENGTH calculates the number of characters in a string, which is different from LENGTH, which measures the number of bytes. This distinction is important when dealing with multibyte characters.

SELECT first_name, email, CHAR_LENGTH(email) AS email_char_length FROM customer;

This query returns the character count for each email address:

first_name email email_char_length
John john.doe@stashsoftware.com 26
Jane jane.doe@stashsoftware.com 26
Katie katie.doe@stashsoftware.com 27
Joe joe.bloggs@stashsoftware.com 28
Joe joe.schmoe@stashsoftware.com 28
Mary mary.smith@stashsoftware.com 28
Max max.mustermann@stashsoftware.com 32
Taro taro.yamada@stashsoftware.com 29
Jean jean.dupont@stashsoftware.com 29

To illustrate the difference between LENGTH and CHAR_LENGTH, consider a string containing multibyte characters:

SELECT LENGTH('José'), CHAR_LENGTH('José');

For the string 'José', this query compares the byte length and character length:

LENGTH('José') CHAR_LENGTH('José')
5 4

In this example, the LENGTH function returns 5 because the character 'é' takes more bytes in a multibyte character set, while CHAR_LENGTH returns 4, consistent with the number of characters.

Using TO_BASE64 and FROM_BASE64 for Encoding and Decoding

These functions are used for encoding data to Base64 format and decoding from Base64 format, respectively. It's commonly used for handling binary data in web applications.

SELECT first_name, email, TO_BASE64(email) AS email_base64 FROM customer;

This query encodes the email addresses using Base64.

To decode a Base64 encoded string, use FROM_BASE64:

SELECT email_base64, FROM_BASE64(email_base64) AS decoded_email FROM (
    SELECT TO_BASE64(email) AS email_base64 FROM customer
) AS temp;

Using REVERSE to Reverse Strings

The REVERSE function is a simple way to reverse the order of characters in a string, which can be useful for certain types of data transformations or playful outputs.

SELECT first_name, REVERSE(email) AS email_reversed FROM customer;

This query reverses each email address:

first_name email_reversed
John moc.erawtfoshsats@eod.nhoj
Jane moc.erawtfoshsats@eod.enaj
Katie moc.erawtfoshsats@eod.eitak
Joe moc.erawtfoshsats@sggolb.eoj
Joe moc.erawtfoshsats@eohmocs.eoj
Mary moc.erawtfoshsats@htims.yram
Max moc.erawtfoshsats@nnamretsum.xam
Taro moc.erawtfoshsats@adama.yorat
Jean moc.erawtfoshsats@tnopud.naej

Using REPLACE to Substitute Strings

The REPLACE function allows you to replace occurrences of a specified string with another string, which is helpful for formatting or correcting data entries.

SELECT first_name, REPLACE(email, 'stashsoftware', 'example') AS new_email FROM customer;

This query replaces 'stashsoftware' with 'example' in email addresses:

first_name new_email
John john.doe@example.com
Jane jane.doe@example.com
Katie katie.doe@example.com
Joe joe.bloggs@example.com
Joe joe.schmoe@example.com
Mary mary.smith@example.com
Max max.mustermann@example.com
Taro taro.yamada@example.com
Jean jean.dupont@example.com

Numeric Formatting with FORMAT

The FORMAT function is used to format numeric values as strings with a specified number of decimal places and optional locale. This is particularly useful for displaying prices or currency amounts in a more readable format.

To display the total amount in a currency format from an invoices table:

SELECT invoice_id, customer_name, FORMAT(total_amount, 2) AS formatted_total FROM invoices;

This query formats the total_amount to two decimal places, presenting it as a currency:

invoice_id customer_name formatted_total
1 Alice Johnson 1,423.00
2 Bob Smith 239.99
3 Charlie Rose 1,023.50
4 Dana White 5,000.75

Leveraging the FORMAT function helps present numeric data in a more user-friendly manner, especially when dealing with financial data.

Aggregating Numeric Data with MySQL Functions

MySQL provides powerful aggregate functions that allow you to perform calculations across a range of values.

Using MIN to Find the Minimum Value

The MIN function returns the smallest value from a selected column. This is useful when you need to retrieve the lowest value, such as the smallest invoice total.

SELECT MIN(total_amount) AS smallest_invoice FROM invoices;

This query finds the minimum total amount in the invoices table:

smallest_invoice
239.99

Using MAX to Find the Maximum Value

The MAX function returns the largest value from a selected column. You can use this to identify the highest invoice total.

SELECT MAX(total_amount) AS largest_invoice FROM invoices;

This query finds the maximum total amount in the invoices table:

largest_invoice
5000.75

Using AVG to Calculate the Average

The AVG function computes the average of a selected column, which is ideal for analyzing the average expense per invoice.

SELECT AVG(total_amount) AS average_invoice FROM invoices;

This query calculates the average total amount for invoices:

average_invoice
1921.810000

Using SUM to Calculate the Total

The SUM function adds up all the values in a column, useful for summing all invoice totals.

SELECT SUM(total_amount) AS total_sales FROM invoices;

This query calculates the total sales amount from all invoices:

total_sales
7687.24

These aggregate functions empower you to perform meaningful data summarization in MySQL, contributing to insightful data analysis and informed decision-making.

Formatting Dates with DATE_FORMAT

The DATE_FORMAT function in MySQL is used to format date values according to a specific format string. This proves especially useful for presenting dates in a human-readable format or aligning date displays to specific regional formats.

To format the invoice date into a string like "14 Nov 2024", you can use:

SELECT invoice_id, customer_name, DATE_FORMAT(invoice_date, '%d %b %Y') AS formatted_date FROM invoices;

This query reformats the invoice_date to show the day, abbreviated month name, and year:

invoice_id customer_name formatted_date
1 Alice Johnson 14 Nov 2024
2 Bob Smith 05 Oct 2024
3 Charlie Rose 21 Sep 2024
4 Dana White 01 Nov 2024

Utilizing the DATE_FORMAT function enhances the readability of date outputs and ensures consistent presentation of date information across various applications.

Creating and Using Custom Functions with SELECT query

MySQL allows you to define custom functions to encapsulate and reuse common logic across your database operations. This feature can greatly enhance the reusability and maintainability of your SQL code.

Here is an example of how to create a custom function and use it with the SELECT command:

Creating a Custom Function format_currency

This function formats a numeric amount into a string with currency formatting:

DELIMITER $$

CREATE FUNCTION format_currency(amount DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE formatted_amount VARCHAR(20);
    SET formatted_amount = CONCAT(FORMAT(amount, 2), '$');
    RETURN formatted_amount;
END$$

DELIMITER ;

Once this function is defined, you can use it within a SELECT query:

SELECT format_currency(total_amount) AS formatted_amount
FROM invoices;

This query applies the format_currency function to each total_amount in the invoices table, formatting it as a currency string:

formatted_amount
1,423.00$
239.99$
1,023.50$
5,000.75$

Creating a Function to Abbreviate Customer Names

In this example, we'll create a function to return the abbreviated form of a customer's full name. We'll use the customer table for this purpose.

DELIMITER $$

CREATE FUNCTION abbreviate_name(full_name VARCHAR(100))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE abbrev VARCHAR(10);
    DECLARE first_space INT;
    SET first_space = LOCATE(' ', full_name);
    IF first_space > 0 THEN
        SET abbrev = CONCAT(SUBSTRING(full_name, 1, 1), '. ', SUBSTRING(full_name, first_space + 1, 1), '.');
    ELSE
        SET abbrev = full_name;
    END IF;
    RETURN abbrev;
END$$

DELIMITER ;

Use this function in a query to get abbreviated customer names:

SELECT customer_name, abbreviate_name(customer_name) AS abbrev_name FROM invoices;

This provides an abbreviated form of the customer names:

customer_name abbrev_name
Alice Johnson A. J.
Bob Smith B. S.
Charlie Rose C. R.
Dana White D. W.

By defining and using custom functions in MySQL, you can enhance the functionality and versatility of your SQL queries, making complex operations simpler and more efficient.

Using SELECT DISTINCT

In cases where you need to eliminate duplicate entries, SELECT DISTINCT comes in handy. It ensures that the returned dataset contains only unique records.

SELECT DISTINCT country FROM customer;

This command returns a list of unique countries from the customer table:

country
Republic of Ireland
USA
UK
Germany
Japan
France

Aggregating Data with SELECT COUNT

To determine the number of entries in a table, you can use the SELECT COUNT function. This is particularly useful for generating reports or summaries.

SELECT COUNT(*) FROM customer;

The above command returns the total number of records in the customer table:

COUNT(*)
9

You can refine the COUNT with DISTINCT to count unique occurrences.

SELECT COUNT(DISTINCT country) FROM customer;

This command counts the number of unique countries in the table:

COUNT(DISTINCT country)
6

Conclusion

The MySQL SELECT command is an essential tool for data retrieval and manipulation, offering powerful options like DISTINCT, COUNT, and column-specific queries to handle data effectively. By mastering these features, you can manage your database efficiently, ensuring both performance and ease of access. Whether you're generating reports or managing datasets, understanding and leveraging functions like CONCAT, UPPER, LOWER, TRIM, SUBSTRING, LENGTH, CHAR_LENGTH, TO_BASE64, FROM_BASE64, REVERSE, REPLACE, FORMAT, MIN, MAX, AVG, SUM, DATE_FORMAT, and custom-defined functions within SELECT is a critical skill for effective database management.

AUTHOR
Cemil Tokatli
PUBLISHED 11 April 2025
TOPICS