MySQL Commands with Examples – Beginner’s Guide (2025)

Introduction

When working with MySQL, you interact with the database using commands. These commands tell MySQL what to do — for example:

Create a new database
Add tables
Insert data
Retrieve information
Update or delete records

Think of MySQL commands as instructions to manage and organize your data.

In this guide, we’ll explore the most important MySQL commands with examples.

Connect to MySQL

Before using any command, log into MySQL from the terminal:

mysql -u root -p

-u root → username (root is default admin user)
-p → prompts you for password

After this command screen ask for mysql server password. Give the password then # or $ prompt replace with

mysql>

This shows that you’re connected to the MySQL server.

Show All Databases

To see all databases in MySQL:

SHOW DATABASES;

Example Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| SchoolDB           |
+--------------------+

Create a New Database

CREATE DATABASE SchoolDB;

This makes a new database named SchoolDB.

Select a Database

Before creating tables or inserting data, select a database:

Show Tables in a Database

If SchoolDB has a table Students, you’ll see:

+----------------+
| Tables_in_SchoolDB |
+----------------+
| Students       |
+----------------+

Create a Table

CREATE TABLE Students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    grade VARCHAR(10)
);

Creates a Students table with ID, name, age, and grade.

Insert Data into a Table

INSERT INTO Students (name, age, grade)
VALUES ('Arjun Sharma', 15, '10th'),
       ('Priya Mehta', 14, '9th');

View Data in a Table

`SELECT * FROM Students;`

+------------+-------------+-----+-------+
| student_id | name        | age | grade |
+------------+-------------+-----+-------+
|          1 | Arjun Sharma|  15 | 10th  |
|          2 | Priya Mehta |  14 | 9th   |
+------------+-------------+-----+-------+

Update Data in a Table

UPDATE Students
SET grade = '11th'
WHERE student_id = 1;

Changes Arjun’s grade to 11th.

Delete Data from a Table

DELETE FROM Students
WHERE student_id = 2;

Removes Priya’s record.

Drop (Delete) a Table

`DROP TABLE Students;`

Removes the table permanently.

Drop (Delete) a Database

`DROP DATABASE SchoolDB;`

Deletes the entire database and its tables.

Quick Recap (Cheat Sheet)

-- Show all databases
SHOW DATABASES;

-- Create database
CREATE DATABASE SchoolDB;

-- Use a database
USE SchoolDB;

-- Show all tables
SHOW TABLES;

-- Create table
CREATE TABLE Students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  grade VARCHAR(10)
);

-- Insert data
INSERT INTO Students (name, age, grade) VALUES ('Rahul', 16, '11th');

-- Select data
SELECT * FROM Students;

-- Update data
UPDATE Students SET grade = '12th' WHERE student_id = 1;

-- Delete data
DELETE FROM Students WHERE student_id = 1;

-- Drop table
DROP TABLE Students;

-- Drop database
DROP DATABASE SchoolDB;

Best Practices for MySQL Commands

Always select the correct database with USE db_name; before creating tables.
Use PRIMARY KEY for unique identification.
Use constraints (NOT NULL, UNIQUE, DEFAULT) to protect data quality.
Backup databases before using DROP.
Follow naming conventions (lowercase, underscores for clarity).

w3htmlschool