DEV Community

Cover image for Basic MySQL Queries: A Comprehensive Guide
Nirmalya Mondal
Nirmalya Mondal

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

Select Database

Once you have created the database, use the following query to select it:

USE my_database;
Enter fullscreen mode Exit fullscreen mode

Drop Database

If you need to delete a database, use the following command:

DROP DATABASE my_database;
Enter fullscreen mode Exit fullscreen mode

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
);
Enter fullscreen mode Exit fullscreen mode

Show Tables

To see all the tables in your selected database:

SHOW TABLES;
Enter fullscreen mode Exit fullscreen mode

Describe Table Structure

If you want to know the structure of a table, you can describe it:

DESCRIBE users;
Enter fullscreen mode Exit fullscreen mode

Alter Table

If you need to modify a table by adding or changing columns:

  • Add a Column
  ALTER TABLE users ADD phone VARCHAR(15);
Enter fullscreen mode Exit fullscreen mode
  • Modify a Column
  ALTER TABLE users MODIFY age TINYINT;
Enter fullscreen mode Exit fullscreen mode

Drop Table

To delete a table:

DROP TABLE users;
Enter fullscreen mode Exit fullscreen mode

3. Data Operations

Insert Data

To add data to a table:

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

Select Data

Retrieve data from a table:

SELECT name, email FROM users WHERE age > 20;
Enter fullscreen mode Exit fullscreen mode

Select All Data

To retrieve all data from a table:

SELECT * FROM users;
Enter fullscreen mode Exit fullscreen mode

Update Data

To update data in a table:

UPDATE users SET age = 26 WHERE name = 'John Doe';
Enter fullscreen mode Exit fullscreen mode

Delete Data

To remove data from a table:

DELETE FROM users WHERE name = 'John Doe';
Enter fullscreen mode Exit fullscreen mode

4. Conditional Queries

WHERE Clause

Use the WHERE clause to filter records based on specific conditions:

SELECT * FROM users WHERE age > 20;
Enter fullscreen mode Exit fullscreen mode

AND/OR Conditions

Combine multiple conditions using AND or OR:

SELECT * FROM users WHERE age > 20 AND name = 'John Doe';
Enter fullscreen mode Exit fullscreen mode

IN Clause

Select data based on a list of values:

SELECT * FROM users WHERE age IN (20, 25, 30);
Enter fullscreen mode Exit fullscreen mode

BETWEEN Clause

Filter data within a range:

SELECT * FROM users WHERE age BETWEEN 20 AND 30;
Enter fullscreen mode Exit fullscreen mode

LIKE Clause

Search for patterns using the LIKE clause:

SELECT * FROM users WHERE name LIKE 'J%';
Enter fullscreen mode Exit fullscreen mode

IS NULL / IS NOT NULL

Filter records with NULL or NOT NULL values:

SELECT * FROM users WHERE email IS NULL;
Enter fullscreen mode Exit fullscreen mode

5. Aggregate Functions

COUNT

Count the number of rows:

SELECT COUNT(*) FROM users;
Enter fullscreen mode Exit fullscreen mode

SUM

Calculate the sum of a column:

SELECT SUM(age) FROM users;
Enter fullscreen mode Exit fullscreen mode

AVG

Find the average value of a column:

SELECT AVG(age) FROM users;
Enter fullscreen mode Exit fullscreen mode

MAX and MIN

Find the maximum or minimum value of a column:

SELECT MAX(age) FROM users;
Enter fullscreen mode Exit fullscreen mode
SELECT MIN(age) FROM users;
Enter fullscreen mode Exit fullscreen mode

6. Grouping and Sorting

GROUP BY

Group data based on one or more columns:

SELECT age, COUNT(*) FROM users GROUP BY age;
Enter fullscreen mode Exit fullscreen mode

HAVING

Filter grouped data:

SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

ORDER BY

Sort data in ascending or descending order:

SELECT * FROM users ORDER BY age DESC;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Drop View

Delete a view:

DROP VIEW user_orders;
Enter fullscreen mode Exit fullscreen mode

10. Indexing

Create Index

Improve query performance by creating an index:

CREATE INDEX idx_name ON users (name);
Enter fullscreen mode Exit fullscreen mode

Drop Index

Remove an index:

DROP INDEX idx_name ON users;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)