If you’re working with a MySQL database, chances are you’ll need to write queries to extract, update, or delete data at some point. While the SQL language can be quite complex, there are a few essential queries that every MySQL user should know.
In this post, we’ll go over 20 MySQL queries that cover a range of common use cases, from selecting data to aggregating it and updating the database. For each query, we’ll provide an example and explain how it works.
Let’s get started!
1. Selecting Data
The most basic type of query is one that selects data from a table. Here are a few examples:
- Retrieve the first name and email of all customers from the “customers” table.
SELECT first_name, email FROM customers;
2. Retrieve the product name and price of all products from the “products” table.
SELECT product_name, price FROM products;
3.Retrieve the order number, total amount, and status of all orders from the “orders” table.
SELECT order_number, total_amount, status FROM orders;
- Retrieve the total number of customers from the “customers” table.
SELECT COUNT(*) FROM customers;
- Retrieve the total number of orders from the “orders” table.
SELECT COUNT(*) FROM orders;
- Retrieve the average age of all customers from the “customers” table.
SELECT AVG(age) FROM customers;
2. Aggregating Data
Sometimes you need to group data by a certain attribute and calculate some statistics on it. Here are a few examples:
- Retrieve the most expensive product from the “products” table.
SELECT * FROM products ORDER BY price DESC LIMIT 1;
- Retrieve the order with the highest total amount from the “orders” table.
SELECT * FROM orders ORDER BY total_amount DESC LIMIT 1;
- Retrieve the customer who made the highest total amount of orders from the “orders” table.
SELECT customers.*, SUM(orders.total_amount) AS total_spent
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.id
ORDER BY total_spent DESC
LIMIT 1;
- Retrieve the top 5 customers who spent the most money from the “orders” table.
SELECT customers.*, SUM(orders.total_amount) AS total_spent
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.id
ORDER BY total_spent DESC
LIMIT 5;
- Retrieve the total number of orders for each status from the “orders” table.
SELECT status, COUNT(*) AS count FROM orders GROUP BY status;
3. Filtering Data
To narrow down your query results, you can filter the data using logical operators and pattern matching. Here are a few examples:
- Retrieve the products with a price less than 1000 dollars from the “products” table.
SELECT * FROM products WHERE price < 1000;
- Retrieve the orders that were shipped from the “orders” table.
SELECT * FROM orders WHERE status = 'shipped';
- Update the price of the product with an id of 2 to 1599 dollars in the “products” table.
UPDATE products SET price = 1599 WHERE id = 2;
- Delete the order with an id of 3 from the “orders” table.
DELETE FROM orders WHERE id=3;
4. Joining Tables
When you have data spread across multiple tables, you can use JOIN statements to combine it into a single query result. Here are a few examples:
- Retrieve the first name, last name, and order number of all customers who placed an order from the “customers” and “orders” tables.
SELECT customers.first_name, customers.last_name, orders.order_number
FROM customers
JOIN orders ON customers.id = orders.customer_id;
- Retrieve the product name, price, and category of all products with the category name ‘Electronics’ from the “products” and “categories” tables.
SELECT products.product_name, products.price, categories.category_name
FROM products
JOIN categories ON products.id = categories.id
WHERE categories.category_name = 'Electronics';
- Retrieve the total number of orders and the total amount of money spent by customers from each country from the “orders” and “customers” tables.
SELECT customers.country, COUNT(orders.id) AS total_orders, SUM(orders.total_amount) AS total_spent
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.country;
5. Modifying Tables
Finally, you can use SQL to modify the database itself, by adding, modifying, or deleting tables and columns. Here are a few examples:
- Add a new column called “birthdate” to the “customers” table.
ALTER TABLE customers ADD birthdate DATE;
- Delete the “reviews” table from the database.
DROP TABLE reviews;
These are just a few examples of the many SQL queries you can write to manage your MySQL database. Whether you’re selecting data, aggregating it, filtering it, joining it, or modifying the database itself, SQL provides powerful tools for working with data. With a little practice, you’ll be able to use these queries to extract valuable insights from your data and build more powerful applications.
6. Conclusion
In this article, we covered some common MySQL queries that can be used to manage and analyze data in a MySQL database. We started with basic SELECT statements to retrieve data from a single table, and then moved on to more complex queries that involved filtering, sorting, and aggregating data.
We also covered how to join data from multiple tables, and how to modify the database structure itself. These queries provide a powerful set of tools for working with data, and can be used to extract valuable insights from large datasets.
However, keep in mind that these examples only scratch the surface of what is possible with MySQL. There are many more advanced features and techniques that you can use to manipulate and analyze data, including subqueries, views, and stored procedures.
Related MYSQL Queries and Python programs :
Create garment table and insert data into it.