VMware ESXi and vSphere Cluster Management

Query Data from a MySQL Database with SELECT

Learn how to use MySQL SELECT to retrieve specific columns, all columns, and complete rows from a database table.

A database query is a request to retrieve or work with data stored in a database. In this lesson, you will use the MySQL SELECT statement to read data from a table.

A table organizes data into columns and rows. A column is a named field, such as name or year. A row is one complete record in the table.

What a SELECT query does

When you query a table, you can choose:

Columns: which fields should appear in the result.
Rows: which records should appear.
Both: a particular set of fields from a particular set of records.

SELECT is the SQL statement used to retrieve data from one or more tables. The list after SELECT controls the columns, while a later WHERE clause can control which rows match. This lesson starts with column selection; row filtering is covered in later SELECT usage.

Basic SELECT statement structure

The general form for selecting named columns is:

SELECT column_name, column_name
FROM table_name;

SELECT begins the query and identifies the fields to return.
The comma-separated column names determine the columns in the result set.
FROM identifies the table that supplies the data.
The semicolon ends the SQL statement. SQL clients conventionally use it to mark the end of a query.

A result set is the rows and columns returned by a query.

Example table

Assume a table named testtb has this structure and sample data:

Columns: name, surname, and year
Row 1: Amy, Goodridge, 1991
Row 2: Mark, Smith, 1955

The table might contain additional rows, but these examples use the two shown records to illustrate the result shape.

Selecting specific columns

To return only the personal-name fields, list the required columns after SELECT:

SELECT name, surname FROM testtb;

The column names are separated by commas. This query returns a two-column result set with name and surname for each row in testtb.

name  | surname
Amy   | Goodridge
Mark  | Smith

Because the query has no row-filtering condition, it produces one output row for every row in the table. The year column is not returned because it was not included in the SELECT list.

Selecting all columns with the asterisk

The asterisk, or *, is a wildcard. In a SELECT list, it means “all columns available in the referenced table.”

SELECT * FROM testtb;

This query returns every column in testtb, including name, surname, and year.

name  | surname   | year
Amy   | Goodridge | 1991
Mark  | Smith     | 1955

Use SELECT * when you genuinely need every field, such as when inspecting a table during learning or troubleshooting. Prefer explicit column names when only certain data is needed or when the result must have a stable shape. If someone later adds a column to the table, SELECT * will include it automatically, while an explicit SELECT list will not.

Specific columns versus all columns

Query form: SELECT name, surname FROM testtb;
Columns returned: name and surname
Appropriate use: When the application or person needs only those fields.
Query form: SELECT * FROM testtb;
Columns returned: Every column in testtb
Appropriate use: When all fields are required or when examining the table.

Running both queries against the same table without a filter returns the same number of rows. The difference is the number of displayed columns.

Reading MySQL query results

When MySQL displays a result set, the header labels are the selected column names. Each line beneath the headers represents one row, or record. Therefore, the SELECT list directly affects the width and labels of the output.

For example, SELECT name, surname FROM testtb; displays two result columns. SELECT * FROM testtb; displays every available column.

In the MySQL command-line client, a successful query commonly ends with a row-count message such as:

2 rows in set (0.00 sec)

The number tells you how many rows the query returned. It does not mean how many columns were returned. A query can return two rows with two columns, or the same two rows with many columns.

Choosing columns and filtering rows

The SELECT list controls columns. A WHERE clause controls which rows satisfy a condition. These concerns can be combined:

SELECT name, surname
FROM testtb
WHERE year > 1980;

This later-style query asks for only the name and surname columns, and only for rows whose year is greater than 1980. In general, a SELECT query can limit columns, rows, or both.

Exam-relevant note: The expressions after SELECT determine the columns in the result. The table named after FROM is the data source. Without a row-filtering clause such as WHERE, every row in the source table is eligible to appear.

Troubleshooting SELECT queries

Unknown column error

Likely cause: A requested column does not exist or its name is misspelled.

Resolution: Check the table definition and use the exact column name, including its spelling.

Table does not exist error

Likely cause: The table name is incorrect, or the active database is not the intended database.

Resolution: Verify the table name and select the correct database before running the query.

Unexpected columns appear

Likely cause: The query used SELECT * even though only a subset of fields was intended.

Resolution: Replace the asterisk with a comma-separated list of required column names.

More rows appear than expected

Likely cause: The query has no row-filtering condition.

Resolution: Learn to add a WHERE clause when you need to restrict which records are returned.

Quick reference

SELECT column_name FROM table_name; retrieves one named column.
SELECT column_name, another_column FROM table_name; retrieves selected columns.
SELECT * FROM table_name; retrieves every column.
A comma separates column names in the SELECT list.
The result normally contains one output row for each matching source row.
The semicolon marks the end of the SQL statement.

For the complete lesson path, see Query A Database.