Skip to content

MySQL - Foundational Concepts and Syntax Guide

1. What is MySQL?

  • MySQL = open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage and manipulate relational data
  • supports transactions, constraints, and relationships between tables using foreign keys
  • known for its fast performance and reliability
  • developed by Oracle Corporation
  • widely used for web applications and online publishing

2. MySQL Language Categories

MySQL commands are grouped into four major categories:

Category Description
DDL (Data Definition Language) Defines and modifies database structure (tables, schemas, indexes)
DML (Data Manipulation Language) Manages data inside tables (insert, update, delete, query)
DCL (Data Control Language) Manages permissions and access control
TCL (Transaction Control Language) Manages transactions (begin, commit, rollback)

3. Core Concepts

3.1 Databases and Tables

  • database = a container of tables and related objects
  • table = a collection of rows and columns, representing a real-world entity
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- example of creating a database and table

-- create a database
CREATE DATABASE mydb;
USE mydb;

-- create a table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(255) UNIQUE
);

3.2 Data Types

Type Use
INT, BIGINT Whole numbers
DECIMAL(10,2) Fixed-point decimals
VARCHAR(n) Variable-length text
TEXT Long-form text
DATE, DATETIME, TIMESTAMP Date and time values
BOOLEAN True/false (alias for TINYINT(1) in MySQL)

Tip - Always size your types appropriately to prevent performance issues.

3.3 Constraints

  • PRIMARY KEY – Uniquely identifies rows
  • UNIQUE – Ensures all values in a column are distinct
  • NOT NULL – Prevents null values
  • CHECK – Validates column values (MySQL 8+)
  • FOREIGN KEY – Maintains referential integrity between tables
1
2
3
4
5
6
-- example of creating a foreign key constraint
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES users(id)
);

4. SQL Syntax Overview

4.1 Inserting Data

1
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
  • Columns not specified must be nullable or have default values

4.2 Updating Data

1
UPDATE table_name SET col1 = val1 WHERE condition;
  • Without WHERE, all rows are updated — dangerous.

4.3 Deleting Data

1
DELETE FROM table_name WHERE condition;
  • Without WHERE, deletes all rows — similar to TRUNCATE

4.4 Selecting Data

SELECT column1, column2 FROM table_name WHERE condition;
SELECT * FROM table_name ORDER BY column DESC LIMIT 10;
  • Use WHERE, ORDER BY, LIMIT, GROUP BY, HAVING for control and filtering

5. Joins - Relational Thinking

  • Joins combine rows from two or more tables based on related columns
Join Type Description
INNER JOIN Only matching rows
LEFT JOIN All from left table, matched or NULL on right
RIGHT JOIN All from right table, matched or NULL on left
FULL OUTER JOIN Not supported natively in MySQL — emulate with UNION
CROSS JOIN Cartesian product (all combinations) 
1
2
3
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.customer_id;

6. Indexes

  • Indexes improve read performance, but slow down writes
1
CREATE INDEX idx_email ON users(email);
  • Use for search-heavy fields, but not blindly
  • Don’t over-index — they take space and must be updated on writes

7. Transactions

  • transaction = sequence of SQL statements that are treated as a single unit
1
2
3
4
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;
  • Requires InnoDB engine (not MyISAM)
  • Use COMMIT to save or ROLLBACK to discard changes

8. Views and Stored Objects

Views

1
2
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = 1;
  • Read-only by default (some updates possible with limitations)

Stored Procedures

1
2
3
4
5
6
7
DELIMITER //
CREATE PROCEDURE get_users()
BEGIN
  SELECT * FROM users;
END;
//
DELIMITER ;
  • Useful for encapsulating business logic on the database side.

9. Common Pitfalls

Issue Tip
Forgetting WHERE in UPDATE or DELETE Always double-check!
Implicit type conversion MySQL is lenient — can cause bugs
Using ENUM Often better to normalize into a lookup table
Too many indexes Slows down inserts/updates
Null logic NULL != NULL, use IS NULL to check
Silent truncation MySQL may silently truncate data if not strict

Tip - Always enable STRICT_ALL_TABLES in production environments to avoid silent failures.

  • Use PRIMARY KEY on every table.
  • Prefer INT + AUTO_INCREMENT for keys unless UUIDs are justified.
  • Normalize until it hurts, denormalize when it hurts more.
  • Always back up before DROP, TRUNCATE, or mass updates.
  • Don’t put logic into views or procedures unless justified — favor simplicity and transparency.

Final Thoughts

  • MySQL is deceptively simple but full of traps if misunderstood.
  • Understanding how commands work and what they affect (structure vs data, transactional vs non-transactional) is essential for writing reliable and performant SQL.
  • If you’re building a system from scratch or refactoring, always step back and ask:
  • Does this structure represent the real-world relationship?
  • Is the data integrity enforced by the schema?
  • Would I understand this code a year from now?