What Is MySQL? Beginner Guide, Uses & Examples

What is MySQL?

MySQL is the world's most popular open-source Relational Database Management System (RDBMS). It stores data in structured tables with rows and columns, and uses SQL (Structured Query Language) to query and manipulate that data. MySQL is fast, reliable, scalable, and easy to use - making it the go-to database for millions of web applications worldwide.

MySQL is developed, distributed, and supported by Oracle Corporation. It runs on virtually every platform including Linux, Windows, and macOS, and powers some of the largest websites on the internet including Facebook, Twitter, YouTube, and Wikipedia.

Core SQL Operations

Most MySQL application work uses four operations: SELECT reads rows, INSERT creates rows, UPDATE changes matched rows, and DELETE removes matched rows. Always identify the target table, columns, filter, and expected affected-row count before running a write.

Use explicit column lists, parameterized values, transactions for related writes, and restrictive WHERE clauses. Test write statements inside a rollback-capable environment before applying them to important data.

Basic SQL Syntax

-- Select all rows from a table
SELECT * FROM customers;

-- Select specific columns with a condition
SELECT first_name, last_name, email
FROM customers
WHERE active = 1;

-- Insert a new row
INSERT INTO customers (first_name, last_name, email, active)
VALUES ('Alice', 'Johnson', 'alice@example.com', 1);

-- Update a row
UPDATE customers
SET email = 'alice.j@example.com'
WHERE customer_id = 1;

-- Delete a row
DELETE FROM customers
WHERE customer_id = 1;

MySQL vs Other RDBMS

MySQL is one of several popular relational database systems. Here's how it compares to the most common alternatives:

Feature	MySQL	PostgreSQL	SQLite	SQL Server
License	GPL / Commercial	PostgreSQL (Open)	Public Domain	Commercial
Developed by	Oracle	PostgreSQL Global Dev Group	D. Richard Hipp	Microsoft
ACID Compliant	Yes (InnoDB)	Yes	Yes	Yes
JSON Support	Yes (5.7+)	Yes (JSONB)	Limited	Yes
Full-Text Search	Yes	Yes	Limited	Yes
Replication	Yes	Yes	No	Yes
Best For	Web apps, LAMP	Complex queries, GIS	Embedded, mobile	Enterprise, .NET
Default Port	3306	5432	File-based	1433

Key Features of MySQL

MySQL ships with a rich set of features that make it suitable for everything from small hobby projects to large-scale enterprise applications:

ACID Compliance - The InnoDB storage engine provides full ACID (Atomicity, Consistency, Isolation, Durability) transactions, ensuring data integrity even during failures.
Replication - MySQL supports master-slave and master-master replication, enabling high availability and read scaling across multiple servers.
Partitioning - Large tables can be split into smaller partitions (RANGE, LIST, HASH, KEY) to improve query performance and manageability.
Full-Text Search - Built-in FULLTEXT indexes allow efficient natural-language and boolean-mode searches across text columns.
JSON Support - Since MySQL 5.7, a native JSON data type with a rich set of JSON functions enables document-style storage alongside relational data.
Stored Procedures & Triggers - Business logic can be encapsulated in the database layer using stored procedures, functions, and triggers.
Views - Virtual tables built from SELECT queries simplify complex queries and add a security layer over raw tables.
Multiple Storage Engines - Pluggable storage engines (InnoDB, MyISAM, MEMORY, CSV, ARCHIVE) let you choose the right engine per table.

Checking MySQL Version and Features

-- Check MySQL version
SELECT VERSION();
-- Output: 8.0.35

-- Check available storage engines
SHOW ENGINES;

-- Check current user and database
SELECT USER(), DATABASE();

-- List all databases on the server
SHOW DATABASES;

-- Check global variables (e.g. max connections)
SHOW VARIABLES LIKE 'max_connections';

Common Use Cases

MySQL is the backbone of countless applications across industries:

Web Applications (LAMP Stack) - Linux + Apache + MySQL + PHP is one of the most widely deployed web stacks in the world. Frameworks like Laravel, CodeIgniter, and WordPress all use MySQL by default.
E-Commerce - Platforms like Magento, WooCommerce, and OpenCart rely on MySQL to manage products, orders, customers, and inventory.
Content Management Systems - WordPress, Drupal, and Joomla all use MySQL to store posts, pages, users, and settings.
Analytics & Reporting - MySQL's aggregation functions and partitioning make it suitable for business intelligence dashboards and reporting systems.
SaaS Applications - Multi-tenant SaaS products use MySQL for its reliability, connection pooling, and replication capabilities.

MySQL Architecture

Understanding MySQL's architecture helps you write better queries and tune performance. MySQL is organized into three main layers:

Client Layer - Applications connect to MySQL using connectors (PHP PDO, JDBC, Python mysql-connector, etc.) or the mysql CLI. The client sends SQL statements and receives result sets.
Server Layer - The MySQL server handles connection management, query parsing, query optimization (the query optimizer rewrites your SQL for efficiency), caching, and execution. The server layer is storage-engine agnostic.
Storage Engine Layer - The storage engine handles the actual reading and writing of data to disk. MySQL supports pluggable storage engines per table: InnoDB - The default engine since MySQL 5.5. Supports ACID transactions, row-level locking, foreign keys, and crash recovery. Best for most use cases.
MyISAM - The older default engine. Faster for read-heavy workloads but lacks transactions and foreign key support. Uses table-level locking.
MEMORY - Stores data in RAM for ultra-fast access. Data is lost on restart. Useful for temporary tables and caching.
CSV - Stores data as comma-separated values files. Useful for data exchange.
ARCHIVE - Optimized for storing large amounts of infrequently accessed historical data with high compression.

Working with Storage Engines

-- Create a table using InnoDB (default)
CREATE table orders (
    order_id   INT          NOT NULL AUTO_INCREMENT,
    customer_id INT         NOT NULL,
    total      DECIMAL(10,2) NOT NULL,
    created_at DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create a read-optimized table using MyISAM
CREATE table page_views (
    view_id    BIGINT NOT NULL AUTO_INCREMENT,
    page_url   VARCHAR(500) NOT NULL,
    viewed_at  DATETIME NOT NULL,
    PRIMARY KEY (view_id)
) ENGINE=MyISAM;

-- Check which engine a table uses
SHOW table STATUS LIKE 'orders';

-- Convert a table from MyISAM to InnoDB
ALTER table page_views ENGINE=InnoDB;

Before you move on