Basic MySQL Queries: A Comprehensive Guide

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:

INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 25);

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.