MySQL SQL Basics: SELECT, Filtering, Sorting, CRUD and Aggregate Functions

MySQL is one of the most widely used relational database systems in web development, backend applications, dashboards, content management systems, and data-driven platforms. To work with MySQL correctly, a developer must understand SQL statements, filtering rules, sorting, data modification, NULL handling, aggregate functions, pattern matching, ranges, and aliases.

This article explains the main MySQL SQL topics shown in the learning path: SQL, SELECT, SELECT DISTINCT, WHERE, ORDER BY, AND, OR, NOT, INSERT INTO, NULL values, UPDATE, DELETE, LIMIT, aggregate functions, MIN(), MAX(), COUNT(), SUM(), AVG(), LIKE, wildcards, IN, BETWEEN, and aliases.

The goal is not only to memorize syntax. The goal is to understand how these commands are used in real projects, how they affect data, and what mistakes should be avoided when reading, filtering, inserting, updating, or deleting records.

MySQL SQL

SQL stands for Structured Query Language. It is the language used to communicate with relational databases. In MySQL, SQL is used to create tables, read data, insert new records, update existing records, delete records, filter results, group data, and calculate summaries.

MySQL follows SQL principles but also has its own features and syntax details. This means that most common SQL statements are similar across relational databases, while some behaviors may be specific to MySQL.

SQL commands are commonly written in uppercase for readability, but MySQL keywords are generally not case-sensitive. For example, SELECT, select, and Select can work the same way. However, writing SQL keywords in uppercase makes queries easier to read.

SELECT name, email
FROM users
WHERE status = 'active';

This query asks MySQL to return the name and email of users whose status is active. It contains three important parts: what to select, which table to read from, and which condition to apply.

In real applications, SQL is used behind many features: login systems, product listings, search pages, admin panels, reports, invoices, orders, analytics, and user dashboards.

MySQL SELECT

The SELECT statement is used to read data from a table. It is one of the most important commands in MySQL because most application pages need to retrieve data before displaying it to the user.

You can select all columns using *, or you can select only specific columns. In professional development, selecting only the columns you need is usually better because it reduces unnecessary data transfer and makes the query clearer.

SELECT *
FROM users;

This query returns all columns from the users table.

SELECT id, name, email
FROM users;

This query returns only the id, name, and email columns. This is cleaner when the application does not need every column.

A SELECT query can also include filtering, sorting, limits, aliases, calculations, and aggregate functions. Because of that, SELECT is usually the foundation for more advanced MySQL queries.

MySQL SELECT DISTINCT

The SELECT DISTINCT statement is used to return unique values only. It removes duplicate rows from the result based on the selected columns.

For example, if many users live in the same city, a normal SELECT may return the city name many times. DISTINCT returns each city only once.

SELECT DISTINCT city
FROM users;

This query returns a list of unique cities from the users table.

When DISTINCT is used with more than one column, MySQL checks the uniqueness of the full selected combination, not each column separately.

SELECT DISTINCT city, country
FROM users;

This query returns unique city and country combinations. If the same city name exists in different countries, each different combination can appear separately.

DISTINCT is useful for filters, dropdown lists, reports, and cases where repeated values should not appear in the output.

MySQL WHERE

The WHERE clause is used to filter records. Without WHERE, a SELECT query returns all rows from the table. With WHERE, MySQL returns only rows that match the condition.

SELECT id, name, email
FROM users
WHERE status = 'active';

This query returns only active users.

WHERE can be used with comparison operators such as =, <>, >, <, >=, and <=.

SELECT id, name, price
FROM products
WHERE price > 100;

This query returns products whose price is greater than 100.

WHERE is also used in UPDATE and DELETE statements. This makes it powerful but dangerous if used incorrectly. A missing WHERE clause in UPDATE or DELETE can affect every row in the table.

MySQL ORDER BY

The ORDER BY clause is used to sort query results. Without ORDER BY, MySQL does not guarantee that rows will be returned in a specific order, even if the result may appear ordered sometimes.

You can sort results in ascending order using ASC or descending order using DESC. If no direction is provided, MySQL uses ascending order by default.

SELECT id, name, created_at
FROM users
ORDER BY created_at DESC;

This query returns users from newest to oldest based on the created_at column.

You can also sort by more than one column.

SELECT id, name, city, created_at
FROM users
ORDER BY city ASC, created_at DESC;

This query sorts users by city first, then sorts users inside each city from newest to oldest.

ORDER BY is important for tables, reports, pagination, search results, product pages, and dashboards where the user expects a clear order.

MySQL AND

The AND operator is used when all conditions must be true. It narrows the result because every condition connected with AND must match.

SELECT id, name, email
FROM users
WHERE status = 'active'
AND country = 'Turkey';

This query returns users who are active and whose country is Turkey. If a user is active but lives in another country, that user will not be included.

AND is commonly used in admin filters, search forms, permission checks, and reports where multiple requirements must be satisfied at the same time.

SELECT id, name, price
FROM products
WHERE category_id = 5
AND price >= 100
AND stock > 0;

This query returns products from category 5 with a price of at least 100 and available stock.

MySQL OR

The OR operator is used when at least one condition must be true. It expands the result because a row can match any one of the conditions.

SELECT id, name, role
FROM users
WHERE role = 'admin'
OR role = 'editor';

This query returns users whose role is admin or editor.

When AND and OR are used together, parentheses are important. They make the logic clear and prevent unexpected results.

SELECT id, name, status, role
FROM users
WHERE status = 'active'
AND (role = 'admin' OR role = 'editor');

This query returns active users who are either admins or editors. Without parentheses, the query could be interpreted differently and may return records that were not intended.

MySQL NOT

The NOT operator reverses a condition. It is used to exclude records that match a specific rule.

SELECT id, name, status
FROM users
WHERE NOT status = 'inactive';

This query returns users whose status is not inactive.

A more common way to write the same idea is to use the not-equal operator.

SELECT id, name, status
FROM users
WHERE status <> 'inactive';

NOT can also be used with IN, BETWEEN, and LIKE.

SELECT id, name
FROM users
WHERE country NOT IN ('Turkey', 'Germany');

This query excludes users whose country is Turkey or Germany.

NOT is useful for exclusion filters, blocked statuses, unavailable products, archived records, and negative search conditions.

MySQL INSERT INTO

The INSERT INTO statement is used to add new records to a table. In most applications, INSERT is used when users register, create posts, submit forms, add products, create orders, or save new data.

The safest and clearest form is to specify the column names and then provide the values.

INSERT INTO users (name, email, status)
VALUES ('Adnan', 'adnan@example.com', 'active');

This query inserts a new user with a name, email, and status.

You can also insert multiple records in one statement.

INSERT INTO categories (name, slug)
VALUES
('Programming', 'programming'),
('Databases', 'databases'),
('Backend Development', 'backend-development');

When inserting data, the values must match the expected column types. For example, numbers should be inserted into numeric columns, valid dates into date columns, and strings into text-based columns.

In real applications, developers should never build INSERT queries by directly concatenating unsafe user input. Prepared statements or framework query builders should be used to protect the application from SQL injection.

MySQL NULL Values

NULL means that a value is missing, unknown, or not provided. It is not the same as an empty string, zero, or false.

For example, a user may have no birth date stored yet. In that case, the birth_date column may contain NULL.

To check for NULL values, use IS NULL. Do not use = NULL, because NULL represents an unknown value and normal equality comparison does not work as expected.

SELECT id, name, birth_date
FROM users
WHERE birth_date IS NULL;

This query returns users whose birth date is missing.

To return records where the value exists, use IS NOT NULL.

SELECT id, name, birth_date
FROM users
WHERE birth_date IS NOT NULL;

NULL values also affect aggregate functions. For example, COUNT(column) does not count NULL values, while COUNT(*) counts rows regardless of NULL values.

When you need to replace NULL with a readable value in the result, you can use COALESCE().

SELECT name, COALESCE(phone, 'No phone number') AS phone_display
FROM users;

This query displays a fallback text when the phone column is NULL.

MySQL UPDATE

The UPDATE statement is used to modify existing records in a table. It is commonly used when users edit their profile, change an order status, update a product price, publish an article, or modify settings.

UPDATE users
SET status = 'active'
WHERE id = 10;

This query updates the user with id 10 and changes the status to active.

You can update more than one column at the same time.

UPDATE products
SET price = 149.99,
    stock = 25
WHERE id = 7;

This query updates the price and stock of the product with id 7.

The WHERE clause is extremely important in UPDATE queries. Without WHERE, MySQL updates every row in the table.

UPDATE users
SET status = 'inactive';

This query changes the status of all users to inactive. In most real projects, that would be a serious mistake unless it is intentionally planned.

Before running a dangerous UPDATE, it is often safer to run a SELECT query with the same WHERE condition first to confirm which records will be affected.

MySQL DELETE

The DELETE statement is used to remove records from a table. It is commonly used for deleting posts, comments, temporary records, notifications, cart items, and other data that should no longer exist.

DELETE FROM users
WHERE id = 10;

This query deletes the user with id 10.

DELETE should be used carefully because removed data may be difficult or impossible to recover if there is no backup or soft delete system.

Just like UPDATE, DELETE becomes dangerous when it is used without WHERE.

DELETE FROM users;

This query deletes all rows from the users table. It does not delete the table structure, but it removes the records.

In professional applications, developers often use soft deletes for important business data. Instead of removing the record permanently, the application marks it as deleted using a deleted_at column or a status field.

MySQL LIMIT

The LIMIT clause is used to restrict the number of rows returned by a query. It is useful for pagination, latest records, top lists, previews, dashboards, and performance control.

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10;

This query returns the latest 10 posts.

LIMIT can also be used with an offset. The offset tells MySQL how many rows to skip before returning results.

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

This query skips the first 20 rows and returns the next 10 rows. It can be used for page 3 when each page contains 10 records.

MySQL also supports the shorter syntax:

SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20, 10;

In this syntax, the first number is the offset and the second number is the number of rows to return.

For stable pagination, LIMIT should usually be combined with ORDER BY. Without a clear order, the database may return inconsistent pagination results.

MySQL Aggregate Functions

Aggregate functions perform calculations on a group of rows and return a single result. They are used in reports, dashboards, statistics, analytics, financial summaries, product counts, order totals, and user activity reports.

The most common MySQL aggregate functions are MIN(), MAX(), COUNT(), SUM(), and AVG().

SELECT COUNT(*) AS total_orders,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS average_order_value
FROM orders
WHERE status = 'paid';

This query calculates the number of paid orders, total revenue, and average order value.

Aggregate functions are often used with GROUP BY, although GROUP BY is not the main focus of this article. GROUP BY allows calculations per category, per user, per status, or per date.

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

This query counts orders by status.

MySQL MIN()

The MIN() function returns the smallest value in a column. It can be used with numbers, dates, and text values.

SELECT MIN(price) AS lowest_price
FROM products;

This query returns the lowest product price.

MIN() is useful for finding the cheapest product, earliest date, smallest score, lowest salary, first order date, or minimum quantity.

SELECT MIN(created_at) AS first_order_date
FROM orders
WHERE user_id = 15;

This query returns the first order date for user 15.

When MIN() is used on a column that contains NULL values, NULL values are ignored unless all values are NULL.

MySQL MAX()

The MAX() function returns the largest value in a column. It is the opposite of MIN().

SELECT MAX(price) AS highest_price
FROM products;

This query returns the highest product price.

MAX() is useful for finding the most expensive product, latest date, highest score, largest quantity, newest order date, or maximum value in a report.

SELECT MAX(created_at) AS latest_login
FROM login_logs
WHERE user_id = 15;

This query returns the latest login time for user 15.

Like other aggregate functions, MAX() ignores NULL values in the selected column.

MySQL COUNT()

The COUNT() function returns the number of rows or non-NULL values. It is one of the most frequently used aggregate functions in MySQL.

SELECT COUNT(*) AS total_users
FROM users;

This query counts all rows in the users table.

COUNT(*) counts rows, even if some columns contain NULL. COUNT(column_name) counts only rows where that specific column is not NULL.

SELECT COUNT(phone) AS users_with_phone
FROM users;

This query counts users who have a non-NULL phone value.

COUNT() is useful for dashboards, pagination totals, reports, unread notifications, product counts, order counts, and analytics.

SELECT COUNT(*) AS active_users
FROM users
WHERE status = 'active';

This query counts only active users.

MySQL SUM()

The SUM() function returns the total sum of numeric values in a column. It is used for financial reports, order totals, sales revenue, stock quantities, balances, and numeric summaries.

SELECT SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'paid';

This query returns the total revenue from paid orders.

SUM() should be used with numeric columns. If the column contains NULL values, those values are ignored.

SELECT user_id, SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

This query calculates how much each user spent on paid orders.

SUM() is especially important in e-commerce systems, accounting features, invoices, wallets, subscriptions, and business dashboards.

MySQL AVG()

The AVG() function returns the average value of a numeric column. It calculates the sum of values divided by the number of non-NULL values.

SELECT AVG(price) AS average_price
FROM products;

This query returns the average product price.

AVG() is useful for average order value, average rating, average salary, average score, average delivery time, and performance reports.

SELECT AVG(rating) AS average_rating
FROM product_reviews
WHERE product_id = 12;

This query returns the average rating for product 12.

Because AVG() ignores NULL values, missing ratings or missing values do not reduce the average as zeros would. This difference is important when analyzing data.

MySQL LIKE

The LIKE operator is used for pattern matching in text columns. It is commonly used in search features where the user enters part of a name, email, title, slug, category, or description.

SELECT id, name, email
FROM users
WHERE name LIKE 'A%';

This query returns users whose name starts with A.

SELECT id, title
FROM posts
WHERE title LIKE '%mysql%';

This query returns posts whose title contains the word mysql.

LIKE is useful, but it can become slow on large tables when patterns start with a wildcard such as %mysql or %mysql%. For advanced search systems, full-text indexes or external search engines may be better.

When user input is used in LIKE queries, prepared statements should still be used to avoid SQL injection and unsafe query building.

MySQL Wildcards

Wildcards are special characters used with LIKE to match patterns. The two most common MySQL wildcards are % and _.

• % matches zero or more characters.

• _ matches exactly one character.

SELECT id, name
FROM users
WHERE name LIKE 'Ad%';

This query matches names that start with Ad, such as Adnan or Adem.

SELECT id, code
FROM coupons
WHERE code LIKE 'A_1';

This query matches values such as AA1, AB1, or A91 because the underscore represents exactly one character.

Wildcard position changes the meaning of the search:

• 'mysql%' means starts with mysql.

• '%mysql' means ends with mysql.

• '%mysql%' means contains mysql anywhere.

Wildcards are simple and useful, but they should be used carefully on large datasets because broad pattern searches can affect performance.

MySQL IN

The IN operator checks whether a value matches any value in a list. It is cleaner than writing many OR conditions.

SELECT id, name, role
FROM users
WHERE role IN ('admin', 'editor', 'author');

This query returns users whose role is admin, editor, or author.

The same logic could be written with OR, but IN is easier to read when there are multiple values.

SELECT id, title, status
FROM posts
WHERE status IN ('draft', 'published');

IN can also be used with subqueries.

SELECT id, name
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE status = 'paid'
);

This query returns users who have at least one paid order.

IN is useful for filters, roles, statuses, categories, selected IDs, and permission-based queries.

MySQL BETWEEN

The BETWEEN operator checks whether a value is inside a range. It is commonly used with numbers, dates, and times.

SELECT id, name, price
FROM products
WHERE price BETWEEN 100 AND 500;

This query returns products with prices from 100 to 500.

BETWEEN is inclusive. This means that the starting value and ending value are included in the result.

SELECT id, title, created_at
FROM posts
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

This query returns posts created between the two dates. When working with datetime values, developers should be careful because a date such as '2026-01-31' may represent the beginning of that day depending on the column type and comparison context.

For datetime filtering, using a start date and an exclusive next-day or next-month boundary is often clearer.

SELECT id, title, created_at
FROM posts
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01';

This query returns all posts created during January 2026 when created_at includes time values.

MySQL Aliases

Aliases give temporary names to columns or tables in a query result. They make output easier to read and make complex queries shorter.

Column aliases are commonly created using AS.

SELECT name AS full_name,
       email AS contact_email
FROM users;

This query returns the name column as full_name and the email column as contact_email.

Aliases are very useful with aggregate functions.

SELECT COUNT(*) AS total_users
FROM users;

Instead of returning a column name like COUNT(*), the result uses the readable name total_users.

Table aliases are useful when a query contains long table names or when multiple tables are involved.

SELECT u.id, u.name, u.email
FROM users AS u
WHERE u.status = 'active';

In this query, u is an alias for the users table. This makes the query shorter and prepares the style used in joins and complex reports.

The AS keyword is optional in many alias cases, but using it can make beginner-friendly SQL easier to understand.

How These MySQL SQL Concepts Work Together

In real applications, these MySQL concepts are usually not used separately. A single query may select specific columns, filter records, use AND and OR, sort results, apply LIMIT, and use aliases.

SELECT id,
       title,
       status,
       created_at
FROM posts
WHERE status IN ('published', 'scheduled')
AND title LIKE '%mysql%'
ORDER BY created_at DESC
LIMIT 10;

This query returns the latest 10 posts whose status is published or scheduled and whose title contains mysql.

Reports often combine WHERE conditions with aggregate functions.

SELECT COUNT(*) AS paid_orders,
       SUM(total_amount) AS total_revenue,
       AVG(total_amount) AS average_order_value,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
WHERE status = 'paid'
AND created_at >= '2026-01-01'
AND created_at < '2026-02-01';

This query summarizes paid orders during January 2026. It counts the orders, calculates total revenue, average order value, smallest paid order, and largest paid order.

Data modification commands also depend heavily on conditions.

UPDATE posts
SET status = 'archived'
WHERE status = 'draft'
AND created_at < '2025-01-01';

This query archives old draft posts. Before running an UPDATE like this, a developer should first test the WHERE condition with SELECT.

SELECT id, title, status, created_at
FROM posts
WHERE status = 'draft'
AND created_at < '2025-01-01';

This safer step shows which records will be affected before the UPDATE is executed.

Common Mistakes to Avoid in MySQL SQL

Many SQL errors are not syntax errors. They are logic errors. The query may run successfully but return the wrong data or change more rows than expected.

Common mistakes include:

• Using UPDATE or DELETE without a WHERE clause.

• Using = NULL instead of IS NULL.

• Using SELECT * when only a few columns are needed.

• Forgetting parentheses when combining AND and OR.

• Using LIMIT without ORDER BY for pagination.

• Assuming BETWEEN excludes the start or end value, although it is inclusive.

• Using LIKE '%keyword%' on very large tables without considering performance.

• Not using aliases for calculated columns, which makes result names harder to use.

Writing reliable SQL requires careful thinking about the data, conditions, expected result, and possible side effects.

Best Practices for Writing MySQL Queries

Good MySQL queries are readable, safe, and specific. A query should make its intention clear to the next developer who reads it.

• Write SQL keywords in uppercase for readability.

• Select only the columns you need instead of using SELECT * everywhere.

• Always use WHERE carefully with UPDATE and DELETE.

• Use aliases for aggregate values and long table names.

• Use parentheses when mixing AND and OR.

• Use IS NULL and IS NOT NULL for NULL checks.

• Use ORDER BY with LIMIT when the result order matters.

• Test risky UPDATE and DELETE conditions with SELECT first.

• Use prepared statements or framework query builders when working with user input.

These practices are simple, but they prevent many real production problems.

Conclusion

MySQL SQL is a core skill for backend developers, full-stack developers, data analysts, and anyone who works with database-driven applications. The most important starting point is understanding how to read data with SELECT, filter it with WHERE, sort it with ORDER BY, and control logic using AND, OR, and NOT.

After that, developers must understand how to safely insert, update, and delete records. These statements directly change the database, so they should always be written with attention to conditions, data types, and expected results.

Aggregate functions such as MIN(), MAX(), COUNT(), SUM(), and AVG() make MySQL useful for reports and dashboards. Operators such as LIKE, IN, and BETWEEN make filtering more powerful, while aliases make results cleaner and easier to use.

By mastering these MySQL basics, you build the foundation needed for more advanced database topics such as joins, indexes, grouping, subqueries, transactions, database design, and query optimization.