Querying Data with SELECT

Learn how to retrieve data from your tables using the SELECT statement. We'll explore different clauses like WHERE, ORDER BY, LIMIT, and aggregate functions.

⬅ Previous Next ➡

Mastering MySQL: Querying Data with SELECT

Querying Data with SELECT

The SELECT statement is the fundamental tool for retrieving data from your MySQL tables. It allows you to specify which columns you want to retrieve, and optionally filter and sort the results. This section will cover the basics and introduce you to essential clauses like WHERE, ORDER BY, LIMIT, and common aggregate functions.

Basic SELECT Statement

The simplest form of the SELECT statement retrieves all columns from a table:

SELECT * FROM table_name;

This will return all rows and all columns from the table_name table. The * is a wildcard that represents all columns. Replace table_name with the actual name of your table.

To select specific columns, list them separated by commas:

SELECT column1, column2, column3 FROM table_name;

This will return only the column1, column2, and column3 columns from the table_name table.

The WHERE Clause: Filtering Data

The WHERE clause is used to filter the rows returned by the SELECT statement. You can specify conditions that must be met for a row to be included in the result set.

SELECT * FROM employees WHERE department = 'Sales';

This will return all rows from the employees table where the department column is equal to 'Sales'.

Common comparison operators used in the WHERE clause include:

= (equal to)
!= or <> (not equal to)
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
LIKE (pattern matching)
IN (check if a value is in a set)
BETWEEN (check if a value is within a range)

SELECT * FROM products WHERE price > 100 AND category = 'Electronics';

This example uses the AND operator to combine two conditions. It will return all rows from the products table where the price is greater than 100 AND the category is 'Electronics'. You can also use the OR operator.

The ORDER BY Clause: Sorting Data

The ORDER BY clause is used to sort the rows returned by the SELECT statement. You can sort by one or more columns, in ascending or descending order.

SELECT * FROM customers ORDER BY last_name ASC;

This will return all rows from the customers table, sorted alphabetically by the last_name column in ascending order (ASC).

SELECT * FROM orders ORDER BY order_date DESC, total_amount ASC;

This example sorts by two columns. First, it sorts by order_date in descending order (DESC), and then, within each date, it sorts by total_amount in ascending order.

The LIMIT Clause: Limiting Results

The LIMIT clause is used to restrict the number of rows returned by the SELECT statement. This is useful for pagination or for retrieving only the top N results.

SELECT * FROM products LIMIT 10;

This will return the first 10 rows from the products table.

SELECT * FROM products LIMIT 5, 10;

This example uses an offset. It will return 10 rows from the products table, starting from the 6th row (offset of 5). The first number is the offset, and the second number is the number of rows to return.

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include:

COUNT(): Returns the number of rows.
SUM(): Returns the sum of values.
AVG(): Returns the average of values.
MIN(): Returns the minimum value.
MAX(): Returns the maximum value.

SELECT COUNT(*) FROM orders;

This will return the total number of rows in the orders table.

SELECT AVG(price) FROM products WHERE category = 'Electronics';

This will return the average price of all products in the 'Electronics' category.

Grouping Data with GROUP BY

The GROUP BY clause is used in conjunction with aggregate functions to group rows based on the values in one or more columns. This allows you to perform aggregate calculations for each group.

SELECT category, COUNT(*) AS number_of_products FROM products GROUP BY category;

This query counts the number of products in each category. The GROUP BY category clause groups the rows by category, and then the COUNT(*) function calculates the number of rows in each group. The AS number_of_products renames the resulting count column to be more descriptive.

You can also use the HAVING clause to filter groups after the grouping and aggregation have been performed.

SELECT category, AVG(price) AS average_price FROM products GROUP BY category HAVING AVG(price) > 50;

This query calculates the average price for each product category and then only returns the categories where the average price is greater than 50. The WHERE clause cannot be used to filter on aggregated results; that's the purpose of HAVING.

⬅ Previous Next ➡