If you're learning backend development or working with databases, understanding MySQL queries is an essential skill. MySQL queries allow developers to create, retrieve, modify, and delete data stored in a relational database.
In this guide, we'll look at some of the most commonly used MySQL queries with simple examples.
What Are MySQL Queries?
A MySQL query is a SQL statement used to communicate with a MySQL database. Depending on the requirement, a query can be used to retrieve records, insert new data, update existing information, or delete records.
Some commonly used operations include:
SELECT – Retrieve data
INSERT – Add new records
UPDATE – Modify existing records
DELETE – Remove records
CREATE – Create database objects
ALTER – Modify database structures
DROP – Remove database objects
1. SELECT Query
The SELECT statement is one of the most frequently used MySQL queries. It is used to retrieve information from a table.
SELECT * FROM students;
You can also select specific columns:
SELECT name, email
FROM students;
This approach is useful when you only need particular fields rather than every column.
2. INSERT Query
The INSERT statement adds new records to a table.
INSERT INTO students (name, email, age)
VALUES ('Rahul', 'rahul@example.com', 21);
Multiple records can also be inserted using a single statement:
INSERT INTO students (name, email, age)
VALUES
('Aman', 'aman@example.com', 22),
('Priya', 'priya@example.com', 20);
3. UPDATE Query
The UPDATE statement is used to modify existing records.
UPDATE students
SET age = 23
WHERE name = 'Rahul';
The WHERE clause is important because it specifies which records should be updated.
4. DELETE Query
The DELETE statement removes records from a table.
DELETE FROM students
WHERE name = 'Rahul';
Always use a suitable WHERE condition when deleting specific records to avoid unintentionally removing other data.
5. WHERE Clause
The WHERE clause filters records according to a specified condition.
SELECT *
FROM students
WHERE age > 20;
You can combine conditions using operators such as AND and OR.
SELECT *
FROM students
WHERE age > 20 AND city = 'Delhi';
6. ORDER BY Clause
ORDER BY is used to sort query results.
For ascending order:
SELECT *
FROM students
ORDER BY age ASC;
For descending order:
SELECT *
FROM students
ORDER BY age DESC;
7. GROUP BY Clause
The GROUP BY clause groups rows that have the same values in specified columns. It is commonly used with aggregate functions.
SELECT city, COUNT(*)
FROM students
GROUP BY city;
This query counts the number of students in each city.
8. HAVING Clause
HAVING is useful when you need to filter grouped results.
SELECT city, COUNT() AS total_students
FROM students
GROUP BY city
HAVING COUNT() > 5;
Unlike WHERE, which filters rows before grouping, HAVING filters groups after aggregation.
9. DISTINCT Keyword
The DISTINCT keyword removes duplicate values from the result.
SELECT DISTINCT city
FROM students;
This returns each city only once.
10. JOIN Queries
Joins allow you to retrieve related information from multiple tables.
For example, suppose we have students and courses tables:
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.id;
An INNER JOIN returns records where the join condition matches in both tables.
MySQL also supports other join types, including LEFT JOIN, RIGHT JOIN, and CROSS JOIN.
Why Are MySQL Queries Important?
MySQL queries are fundamental to database-driven applications. They allow developers to interact with stored information and build functionality around that data.
For example, an application might use:
SELECT to display user information
INSERT to register a new user
UPDATE to modify a profile
DELETE to remove an account
JOIN to combine information from related tables
Understanding these operations makes it easier to work with applications built using languages such as Java, Python, PHP, and JavaScript.
Conclusion
MySQL queries provide the foundation for interacting with relational databases. From basic SELECT, INSERT, UPDATE, and DELETE operations to filtering, grouping, sorting, and joining tables, these commands are essential for database development.
If you're looking for a broader collection of MySQL query examples and related database concepts, the MySQL Queries tutorial on TPointTech is a useful resource to explore.
Top comments (0)