MySQL 101: Everything You Need to Know

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. MySQL is an open-source relational database management system that uses SQL as its query language. Together they power the majority of the web -- from WordPress blogs to Facebook-scale applications.

This guide covers everything from the basics to advanced topics. Bookmark it and come back as you need each section.

1. Basic Concepts

What is a DBMS?

A Database Management System (DBMS) is software that provides a structured way to store, retrieve, and manage data. Different systems are optimised for different use cases:

Relational Databases

A relational database organises data into tables with defined relationships between them. This structure ensures data integrity, enables complex queries, and scales reliably for large datasets.

SQL -- Structured Query Language

SQL provides a standardised set of commands for interacting with relational databases: creating structures, inserting data, querying records, and managing access. It divides into four sublanguages:

• DDL (Data Definition Language) -- defines structure
• DML (Data Manipulation Language) -- manipulates data
• DCL (Data Control Language) -- manages access
• TCL (Transaction Control Language) -- manages transactions

MySQL Features

Installation (Linux/Ubuntu)

# Update package lists
sudo apt update

# Install MySQL server
sudo apt install mysql-server

# Verify the service is running
sudo systemctl status mysql

# Enable on boot
sudo systemctl enable mysql

# Secure the installation
sudo mysql_secure_installation

# Access the MySQL shell
mysql -u root -p

For detailed guides: Ubuntu | Windows

2. Data Definition Language (DDL)

DDL commands define and manage database structure -- tables, indexes, constraints, and schemas.

Creating a Database and Tables

-- Create a database
CREATE DATABASE mystore;
USE mystore;

-- Products table
CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  price       DECIMAL(8,2) NOT NULL,
  description TEXT
);

-- Orders table
CREATE TABLE orders (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  customer_name  VARCHAR(100) NOT NULL,
  order_date     DATE,
  total_amount   DECIMAL(10,2)
);

Modifying Tables

-- Add a column
ALTER TABLE products
ADD COLUMN quantity INT;

-- Modify a column type
ALTER TABLE products
MODIFY COLUMN price DECIMAL(10,2);

-- Rename a column
ALTER TABLE products
CHANGE COLUMN description product_description TEXT;

-- Drop a column
ALTER TABLE products
DROP COLUMN quantity;

Constraints

-- Primary key and unique constraint
CREATE TABLE customers (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE
);

-- Foreign key constraint
CREATE TABLE orders (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  customer_id   INT,
  order_date    DATE,
  total_amount  DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Index Types

3. Data Manipulation Language (DML)

DML commands manipulate the data within tables.

Inserting Data

INSERT INTO products (id, name, price, description)
VALUES
  (1, 'Product A', 10.99, 'This is product A'),
  (2, 'Product B', 19.99, 'This is product B'),
  (3, 'Product C',  5.99, 'This is product C');

Selecting Data

-- All records
SELECT * FROM products;

/*
+----+-----------+---------+-------------------+
| id | name      | price   | description       |
+----+-----------+---------+-------------------+
|  1 | Product A | 10.9900 | This is product A |
|  2 | Product B | 19.9900 | This is product B |
|  3 | Product C |  5.9900 | This is product C |
+----+-----------+---------+-------------------+
*/

-- Filter with WHERE
SELECT name, price FROM products WHERE price < 10.00;

/*
+-----------+--------+
| name      | price  |
+-----------+--------+
| Product C | 5.9900 |
+-----------+--------+
*/

Updating and Deleting

-- Update a record
UPDATE products
SET price = 12.99
WHERE id = 1;

-- Delete a record
DELETE FROM products
WHERE id = 2;

Sorting with ORDER BY

-- Ascending
SELECT * FROM products ORDER BY price ASC;

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns
SELECT * FROM products ORDER BY price ASC, name DESC;

Joins

-- INNER JOIN
SELECT *
FROM products
INNER JOIN orders ON products.id = orders.id;

-- LEFT JOIN
SELECT *
FROM products
LEFT JOIN orders ON products.id = orders.id;

-- RIGHT JOIN
SELECT *
FROM products
RIGHT JOIN orders ON products.id = orders.id;

-- FULL JOIN (MySQL doesn't support FULL JOIN directly -- use UNION)
SELECT * FROM products LEFT JOIN orders  ON products.id = orders.id
UNION
SELECT * FROM products RIGHT JOIN orders ON products.id = orders.id;

4. Data Control Language (DCL)

DCL manages permissions and access rights.

Managing Users

-- Create a user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

-- Grant specific privileges
GRANT SELECT ON mystore.products TO 'myuser'@'localhost';
GRANT INSERT, UPDATE ON mystore.customers TO 'myuser'@'localhost';
GRANT ALL PRIVILEGES ON mystore.* TO 'myuser'@'localhost';

-- Revoke privileges
REVOKE SELECT ON mystore.products FROM 'myuser'@'localhost';
REVOKE ALL PRIVILEGES ON mystore.* FROM 'myuser'@'localhost';

-- Change password
ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'newpassword';

-- View user privileges
SHOW GRANTS FOR 'myuser'@'localhost';

-- Drop user
DROP USER 'myuser'@'localhost';

5. Advanced SQL Queries

Aggregate and Grouping

-- Total sales per customer
SELECT customer_id, SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id;

-- Average salary per department
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

-- Top 5 products by sales
SELECT product_id, SUM(quantity) AS total_sales
FROM order_details
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 5;

Subqueries

-- Customers who ordered in the last 30 days
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
);

-- Customers with no orders
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
  SELECT DISTINCT customer_id FROM orders
);

Date and Time Queries

-- Monthly revenue for 2023
SELECT MONTH(order_date) AS month, SUM(total_amount) AS revenue
FROM orders
WHERE YEAR(order_date) = 2023
GROUP BY MONTH(order_date);

Window Functions

-- Cumulative sales per product over time
SELECT
  order_date,
  product_id,
  SUM(quantity) OVER (
    PARTITION BY product_id
    ORDER BY order_date
  ) AS cumulative_sales
FROM order_details;

UNION

-- Combine employee and customer names into one list
SELECT employee_name AS name FROM employees
UNION
SELECT customer_name AS name FROM customers;

6. Data Integrity and Constraints

-- Primary key
CREATE TABLE employees (
  employee_id   INT PRIMARY KEY,
  employee_name VARCHAR(50) NOT NULL,
  department_id INT
);

-- Foreign key
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Unique constraint
CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  product_name VARCHAR(50),
  sku          VARCHAR(20) UNIQUE
);

-- Check constraint
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  salary      DECIMAL(10,2),
  CONSTRAINT chk_salary CHECK (salary >= 0)
);

-- Default value
CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE
);

Cascading Actions

When a parent record is updated or deleted, cascading actions define what happens to child records:

• CASCADE -- child records are updated or deleted automatically
• SET NULL -- foreign key in child is set to NULL
• SET DEFAULT -- foreign key in child is set to its default value

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
);

CREATE TABLE order_items (
  item_id    INT PRIMARY KEY,
  order_id   INT,
  product_id INT,
  quantity   INT,
  FOREIGN KEY (order_id)
    REFERENCES orders(order_id)
    ON DELETE CASCADE
);

7. Transactions and Concurrency

Transactions group SQL statements into a single atomic unit. MySQL supports ACID properties:

• Atomicity -- all or nothing
• Consistency -- data remains valid before and after
• Isolation -- concurrent transactions do not interfere
• Durability -- committed changes survive system failures

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- All good, save changes
COMMIT;

-- Something went wrong, undo everything
ROLLBACK;

Isolation Levels

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

8. Performance Optimization

Indexing Strategy

-- Add an index on a frequently queried column
CREATE INDEX idx_customer_email ON customers(email);

-- Composite index for multi-column queries
CREATE INDEX idx_order_date_customer ON orders(order_date, customer_id);

-- Check existing indexes
SHOW INDEX FROM orders;

-- Drop an unused index
DROP INDEX idx_customer_email ON customers;

Query Optimization Tips

Use EXPLAIN to analyse how MySQL executes a query:

EXPLAIN SELECT * FROM orders
WHERE customer_id = 5
ORDER BY order_date DESC;

Key things to check in EXPLAIN output: type should be ref or eq_ref not ALL (full scan), rows should be as low as possible, Extra should not show Using filesort or Using temporary if avoidable.

General tips: index columns used in WHERE, JOIN, and ORDER BY clauses; avoid SELECT * in production queries; use LIMIT for pagination; avoid functions on indexed columns in WHERE clauses.

9. Backup and Recovery

# Full database backup
mysqldump -u root -p mystore > mystore_backup.sql

# Backup specific table
mysqldump -u root -p mystore products > products_backup.sql

# Restore from backup
mysql -u root -p mystore < mystore_backup.sql

# Compressed backup
mysqldump -u root -p mystore | gzip > mystore_backup.sql.gz

# Restore compressed backup
gunzip < mystore_backup.sql.gz | mysql -u root -p mystore

10. Stored Procedures and Triggers

Stored Procedure

-- Procedure to get orders for a customer
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(IN cust_id INT)
BEGIN
  SELECT * FROM orders
  WHERE customer_id = cust_id
  ORDER BY order_date DESC;
END //
DELIMITER ;

-- Call it
CALL GetCustomerOrders(5);

Trigger

-- Automatically set updated_at timestamp on row update
DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

11. Database Design and Normalization

Good database design prevents data duplication and anomalies. The normal forms:

• 1NF -- each column holds atomic values, no repeating groups
• 2NF -- 1NF plus no partial dependencies on a composite key
• 3NF -- 2NF plus no transitive dependencies
• BCNF -- stronger version of 3NF

A well-normalised schema is easier to maintain and produces cleaner queries. Over-normalization can hurt read performance -- in that case, selective denormalization with good indexing is the answer.

12. Useful MySQL Tools

Resources

• MySQL Official Documentation
• Digital Ocean MySQL on Ubuntu guide
• Advanced MySQL Queries
• Use The Index, Luke -- deep dive into SQL indexing