How to Use MySQL Commands (Complete Beginner to Advanced Guide 2026)

Introduction

MySQL is one of the most powerful and widely used relational database management systems (RDBMS) in the world. It is used by startups, enterprise companies, e-commerce platforms, content management systems, and web applications.

If you are learning:

Web Development
PHP
WordPress
Data Analytics
Backend Development
Full Stack Development

Then understanding MySQL commands is essential.

In this complete guide, you will learn:

Basic MySQL commands
Intermediate queries
Advanced SQL concepts
Real-world examples
Best practices for performance
Commands used in interviews

This article is beginner-friendly but also covers advanced concepts for professionals.

What is MySQL?

MySQL is an open-source relational database management system that stores data in tables.

It uses SQL (Structured Query Language) to:

Store data
Retrieve data
Update data
Delete data
Manage databases

How to Start MySQL

If installed locally, open terminal and type:

mysql -u root -p

After entering your password, you will enter the MySQL shell.

Basic MySQL Commands (Must Know)

SHOW DATABASES

SHOW DATABASES;

CREATE DATABASE

Creates a new database:

CREATE DATABASE school_db;

USE DATABASE

Select a database:

USE school_db;

DROP DATABASE

Delete a database:

DROP DATABASE school_db;

⚠ Be careful. This permanently deletes data.

Table Commands in MySQL

CREATE TABLE

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT,
    course VARCHAR(50)
);

Explanation:

INT → Integer
VARCHAR → Text
PRIMARY KEY → Unique identifier
AUTO_INCREMENT → Automatically increases

SHOW TABLES

SHOW TABLES;

DESCRIBE TABLE

DESCRIBE students;

Shows structure of table.

DROP TABLE

DROP TABLE students;

Data Manipulation Commands (DML)

These are the most important commands.

INSERT INTO

Add data:

INSERT INTO students (name, email, age, course)
VALUES ('Rahul', 'rahul@gmail.com', 21, 'Web Development');

Insert multiple rows:

INSERT INTO students (name, email, age, course)
VALUES 
('Aman', 'aman@gmail.com', 22, 'Python'),
('Sneha', 'sneha@gmail.com', 23, 'Data Science');

SELECT (Most Important)

Retrieve data:

SELECT * FROM students;

Select specific columns:

SELECT name, course FROM students;

WHERE Clause

Filter data:

SELECT * FROM students WHERE age > 21;

UPDATE

Modify data:

UPDATE students
SET course = 'Full Stack Development'
WHERE id = 1;

DELETE

DELETE FROM students WHERE id = 3;

Sorting and Filtering Commands

ORDER BY

SELECT * FROM students ORDER BY age DESC;

ASC → Ascending
DESC → Descending

LIMIT

SELECT * FROM students LIMIT 5;

Used in pagination.

LIKE

Search pattern:

SELECT * FROM students WHERE name LIKE 'R%';

% → wildcard

Aggregate Functions

These are used in reporting and dashboards.

COUNT()

SELECT COUNT(*) FROM students;

SUM()

SELECT SUM(age) FROM students;

AVG()

SELECT AVG(age) FROM students;

MAX() and MIN()

SELECT MAX(age) FROM students;
SELECT MIN(age) FROM students;

GROUP BY and HAVING

GROUP BY

SELECT course, COUNT(*) 
FROM students
GROUP BY course;

HAVING

SELECT course, COUNT(*) 
FROM students
GROUP BY course
HAVING COUNT(*) > 1;

JOIN Commands (Very Important for Interviews)

Assume we have two tables:

students
payments

INNER JOIN

SELECT students.name, payments.amount
FROM students
INNER JOIN payments
ON students.id = payments.student_id;

LEFT JOIN

RIGHT JOIN

SELECT students.name, payments.amount
FROM students
RIGHT JOIN payments
ON students.id = payments.student_id;

Constraints in MySQL

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
DEFAULT

Example:

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) DEFAULT 0
);

Indexes for Performance

Create index:

CREATE INDEX idx_name ON students(name);

Indexes improve search speed.

Advanced MySQL Commands

ALTER TABLE

Add column:

ALTER TABLE students ADD phone VARCHAR(15);

Drop column:

ALTER TABLE students DROP phone;

TRUNCATE

Deletes all rows:

TRUNCATE TABLE students;

Faster than DELETE.

VIEW

Create virtual table:

CREATE VIEW student_view AS
SELECT name, course FROM students;

Stored Procedure

DELIMITER //

CREATE PROCEDURE GetStudents()
BEGIN
    SELECT * FROM students;
END //

DELIMITER ;

MySQL Commands Used in Real Projects

In real web projects:

Login system → SELECT + WHERE
Registration → INSERT
Dashboard → GROUP BY
Admin panel → UPDATE + DELETE
Reports → JOIN + COUNT

If you are building:

E-commerce website
School management system
CRM system
Blog platform

You will use these commands daily.

MySQL Interview Questions (Important)

Difference between DELETE and TRUNCATE
What is PRIMARY KEY?
What is JOIN?
What is Index?
What is Normalization?
Difference between WHERE and HAVING

Best Practices for MySQL

Always use WHERE with UPDATE and DELETE
Use indexes for large tables
Avoid SELECT * in production
Backup database regularly
Use prepared statements (security)

Common MySQL Errors and Solutions

Error: Access denied
Solution: Check username/password

Error: Unknown column
Solution: Check column name spelling

Error: Cannot delete or update parent row
Solution: Check foreign key constraint

Final Thoughts

MySQL commands are the backbone of:

Web development
Backend programming
Data analytics
Business applications

If you master these commands, you can:

Build dynamic websites
Create admin dashboards
Manage large datasets
Crack developer interviews

Start practicing daily in phpMyAdmin or MySQL Workbench.

FAQ

What are basic MySQL commands?

SHOW, CREATE, INSERT, SELECT, UPDATE, DELETE.

Is MySQL easy to learn?

Yes. Beginners can learn basics in 1–2 weeks with practice.

Is MySQL still in demand in 2026?

Yes. It is widely used in web development and enterprise systems.