In this guide, we will introduce you to the basics of MySQL queries and provide some practical examples.
Understanding Queries
A query is a request to access or manipulate data stored in a database. In MySQL, you primarily use SQL (Structured Query Language) to write these queries.
Basic Query Structure
The basic structure of a query involves the SELECT statement, which retrieves data from the database.
Example: Selecting Data
To retrieve all data from a Users table:
SELECT * FROM Users;In this example, * means "all columns."
Example: Selecting Specific Columns
To retrieve only the Name and Email columns:
SELECT Name, Email FROM Users;Filtering Data with WHERE
The WHERE clause filters records that meet certain criteria.
Example: Filtering by a Condition
To find users named 'John Doe':
SELECT * FROM Users WHERE Name = 'John Doe';Example: Using Logical Operators
To find users named 'John Doe' or 'Jane Doe':
SELECT * FROM Users WHERE Name = 'John Doe' OR Name = 'Jane Doe';To find users aged 30 and above:
SELECT * FROM Users WHERE Age >= 30;Sorting Data with ORDER BY
The ORDER BY clause is used to sort the result set.
Example: Sorting in Ascending Order
To sort users by their names in alphabetical order:
SELECT * FROM Users ORDER BY Name;Example: Sorting in Descending Order
To sort users by age in descending order:
SELECT * FROM Users ORDER BY Age DESC;Aggregating Data
Aggregation functions perform a calculation on a set of values and return a single value.
Example: Counting Records
To count the number of users:
SELECT COUNT(*) FROM Users;Example: Finding Maximum and Minimum
To find the oldest user's age:
SELECT MAX(Age) FROM Users;To find the youngest user's age:
SELECT MIN(Age) FROM Users;Joining Tables
Joins are used to combine rows from two or more tables.
Example: Inner Join
To list users along with their orders from an Orders table:
SELECT Users.Name, Orders.OrderID
FROM Users
INNER JOIN Orders ON Users.UserID = Orders.UserID;This query joins Users and Orders based on the UserID.
Using Subqueries
Subqueries are queries nested inside another query.
Example: Subquery
To find users who have placed an order:
SELECT Name FROM Users
WHERE UserID IN (SELECT UserID FROM Orders);