Data runs the world. Whether you’re building a website, analyzing customer behavior, or automating tasks, you’ll eventually need to store, retrieve, and manipulate data. That’s where MySQL comes in.

MySQL is an open-source relational database management system (RDBMS) that enables efficient storage, retrieval, and management of structured data using Structured Query Language (SQL). It was created in 1995 by Michael “Monty” Widenius, David Axmark, and Allan Larsson under the Swedish company MySQL AB. Initially designed for speed and reliability, it quickly gained popularity as the backbone of many web applications, including WordPress, Facebook, and Twitter. MySQL was acquired by Sun Microsystems in 2008 and later became part of Oracle Corporation in 2010. Today, it continues to evolve as one of the world’s most widely used databases, with a robust community driving innovations and maintaining its relevance across industries.

This guide takes you from setting up a database to mastering the nuances of queries, functions, indexing, and optimization. Each concept is explained with examples so you not only memorize commands but truly understand their power.

Getting Started

Logging In

To access MySQL, use the command line:

mysql -u root -p

You’ll be prompted for the root password (set during installation).

Setting Up Your Database

Creating a New Database

A database is where all your tables and data live. Let’s create one:

CREATE DATABASE my_database;
USE my_database;

The USE command switches the current context to your newly created database.

Creating a User

Good security practice dictates not always using the root account. Create a specific user for your database:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'secure_password';

Granting Privileges

Define what this user can do:

GRANT ALL PRIVILEGES ON my_database.* TO 'new_user'@'localhost';
FLUSH PRIVILEGES;

The FLUSH PRIVILEGES command ensures changes take effect immediately.

Building and Managing Tables

Creating a Table

A table is a structured format for your data. Let’s create one for storing user information:

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(150) UNIQUE,
 age INT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• AUTO_INCREMENT: Automatically increments the id for each new row.
• NOT NULL: Ensures the column cannot be empty.
• UNIQUE: Ensures no duplicate values for emails.

Adding Data

Insert rows into your table:

INSERT INTO users (name, email, age) 
VALUES
 ('Alice', 'alice@example.com', 25),
 ('Bob', 'bob@example.com', 30);

Querying Your Data

The Basics

Retrieve everything from a table:

SELECT * FROM users;

Retrieve specific columns:

SELECT name, email FROM users;

Filter results using WHERE:

SELECT * FROM users WHERE age > 25;

Using Wildcards with LIKE

Find patterns in your data:

SELECT * FROM users WHERE email LIKE '%@example.com';

• % matches any number of characters.
• _ matches exactly one character.

Updating and Deleting Data

Updating Records

Change data in a row:

UPDATE users
SET age = 29 
WHERE name = 'Alice';

Deleting Records

Remove unwanted data:

DELETE FROM users WHERE name = 'Bob';

Joins, Linking Tables

Relational databases shine when tables work together. Let’s create another table:

CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 product_name VARCHAR(100),
 order_date DATE,
 FOREIGN KEY (user_id) REFERENCES users(id)
);

Insert data into this table:

INSERT INTO orders (user_id, product_name, order_date) 
VALUES
 (1, 'Laptop', '2024-01-01'),
 (1, 'Tablet', '2024-02-01');

Retrieve data across tables with joins:

Inner Join

Only matches with data in both tables:

SELECT users.name, orders.product_name 
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Left Join

Shows all rows from the left table, even if there’s no match in the right table:

SELECT users.name, orders.product_name
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Using Functions

MySQL provides built-in functions for common operations.

String Functions

Concatenate strings:

SELECT CONCAT(name, ' - ', email) AS user_info FROM users;

Aggregate Functions

Perform calculations on data:

• Sum up values:

SELECT SUM(age) AS total_age FROM users;

• Count rows:

SELECT COUNT(*) AS user_count FROM users;

• Find averages:

SELECT AVG(age) AS average_age FROM users;

Indexing for Speed

Indexes improve query performance by allowing MySQL to find data faster.

Creating an Index

CREATE INDEX idx_email ON users(email);

Types of Indexes

• Unique Index: Prevent duplicate values.

CREATE UNIQUE INDEX idx_email_unique ON users(email);

• Composite Index: Optimize queries involving multiple columns.

CREATE INDEX idx_name_email ON users(name, email);

Advanced Query Techniques

Subqueries

Use a query within another query:

SELECT name FROM users
WHERE id = (SELECT user_id FROM orders WHERE product_name = 'Laptop');

Using CASE for Conditional Logic

Add logic to your queries:

SELECT name, 
 CASE 
 WHEN age < 30 THEN 'Young'
 ELSE 'Old'
 END AS age_group 
FROM users;

Cleanup and Maintenance

Dropping a Table

DROP TABLE orders;

Dropping a Database

DROP DATABASE my_database;