Author: Nirmalya Mondal
Introduction
MySQL is one of the most popular relational database management systems (RDBMS) used for web applications and other data-driven applications. Whether you’re a beginner or someone looking to brush up on your MySQL skills, understanding the basic queries is essential. This blog will walk you through some fundamental MySQL queries that you can use for database operations, table manipulations, and data management.
1. Database Operations
Create Database
To start with, you need a database where you will store your tables and data. Creating a database is straightforward:
CREATE DATABASE my_database;
Select Database
Once you have created the database, use the following query to select it:
USE my_database;
Drop Database
If you need to delete a database, use the following command:
DROP DATABASE my_database;
2. Table Operations
Create Table
Tables are where your data is stored. You can create a table with specific columns as follows:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
);
Show Tables
To see all the tables in your selected database:
SHOW TABLES;
Describe Table Structure
If you want to know the structure of a table, you can describe it:
DESCRIBE users;
Alter Table
If you need to modify a table by adding or changing columns:
Add a Column
ALTER TABLE users ADD phone VARCHAR(15);
Modify a Column
ALTER TABLE users MODIFY age TINYINT;
Drop Table
To delete a table:
DROP TABLE users;
3. Data Operations
Insert Data
To add data to a table:
Select Data
Retrieve data from a table:
SELECT name, email FROM users WHERE age > 20;
Select All Data
To retrieve all data from a table:
SELECT * FROM users;
Update Data
To update data in a table:
UPDATE users SET age = 26 WHERE name = 'John Doe';
Delete Data
To remove data from a table:
DELETE FROM users WHERE name = 'John Doe';
4. Conditional Queries
WHERE Clause
Use the WHERE clause to filter records based on specific conditions:
SELECT * FROM users WHERE age > 20;
AND/OR Conditions
Combine multiple conditions using AND or OR:
SELECT * FROM users WHERE age > 20 AND name = 'John Doe';
IN Clause
Select data based on a list of values:
SELECT * FROM users WHERE age IN (20, 25, 30);
BETWEEN Clause
Filter data within a range:
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
LIKE Clause
Search for patterns using the LIKE clause:
SELECT * FROM users WHERE name LIKE 'J%';
IS NULL / IS NOT NULL
Filter records with NULL or NOT NULL values:
SELECT * FROM users WHERE email IS NULL;
5. Aggregate Functions
COUNT
Count the number of rows:
SELECT COUNT(*) FROM users;
SUM
Calculate the sum of a column:
SELECT SUM(age) FROM users;
AVG
Find the average value of a column:
SELECT AVG(age) FROM users;
MAX and MIN
Find the maximum or minimum value of a column:
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
6. Grouping and Sorting
GROUP BY
Group data based on one or more columns:
SELECT age, COUNT(*) FROM users GROUP BY age;
HAVING
Filter grouped data:
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
ORDER BY
Sort data in ascending or descending order:
SELECT * FROM users ORDER BY age DESC;
7. Join Operations
Inner Join
Fetch data from multiple tables where the condition is met in both:
SELECT users.name, orders.order_date FROM users
INNER JOIN orders ON users.id = orders.user_id;
Left Join
Fetch data from the left table and matching rows from the right table:
SELECT users.name, orders.order_date FROM users
LEFT JOIN orders ON users.id = orders.user_id;
Right Join
Fetch data from the right table and matching rows from the left table:
SELECT users.name, orders.order_date FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
8. Subqueries
Subquery in WHERE
Use a subquery to filter results:
SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE order_id = 1);
Subquery in SELECT
Use a subquery to calculate values:
SELECT name, (SELECT COUNT(*) FROM orders WHERE users.id = orders.user_id) AS order_count
FROM users;
9. Views
Create View
Create a virtual table based on a query:
CREATE VIEW user_orders AS
SELECT users.name, orders.order_date FROM users
INNER JOIN orders ON users.id = orders.user_id;
Drop View
Delete a view:
DROP VIEW user_orders;
10. Indexing
Create Index
Improve query performance by creating an index:
CREATE INDEX idx_name ON users (name);
Drop Index
Remove an index:
DROP INDEX idx_name ON users;
Conclusion
Understanding these basic MySQL queries is essential for anyone working with relational databases. Whether you are managing data, optimizing queries, or ensuring data integrity, these commands form the foundation of your MySQL skills. By mastering them, you'll be well-equipped to handle most database-related tasks with ease.