Reference Guide

Database Fundamentals
MySQL Installation (Ubuntu 24)
Data Types
DDL - Data Definition Language
DML - Data Manipulation Language
DQL - Data Query Language
JOINs
Aggregate Functions
Subqueries
Indexes
Database Design
Security
Backup & Recovery
Best Practices

1. Database Fundamentals

What is a Database?

Organized collection of structured data stored electronically.

ACID Properties

Atomicity: All or nothing (transaction succeeds or fails completely)
Consistency: Database moves from one valid state to another
Isolation: Concurrent transactions don't interfere
Durability: Committed changes survive system failures

Relational Database Concepts

Table: Collection of related data (rows and columns) Row/Record: Single entry in a table Column/Field: Property of the data Primary Key: Unique identifier for each row Foreign Key: References primary key in another table

Relationships

One-to-Many: One user has many orders

USERS (1) ────< (many) ORDERS

Many-to-Many: Students ↔ Courses (requires junction table)

STUDENTS >────< ENROLLMENTS >────< COURSES

One-to-One: User ↔ UserProfile

USERS (1) ──── (1) USER_PROFILES

2. MySQL Installation (Ubuntu 24)

Installation Steps

# Update system
sudo apt update && sudo apt upgrade -y

# Install MySQL
sudo apt install mysql-server -y

# Check status
sudo systemctl status mysql

# Secure installation
sudo mysql_secure_installation

Security Script Recommendations

Validate password: Yes (MEDIUM level)
Remove anonymous users: Yes
Disable remote root: Yes
Remove test database: Yes
Reload privileges: Yes

Create User

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'SecurePass123!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Service Management

sudo systemctl start mysql      # Start
sudo systemctl stop mysql       # Stop
sudo systemctl restart mysql    # Restart
sudo systemctl status mysql     # Check status

sudo mysql                      # As root
mysql -u admin -p              # As specific user

3. Data Types

Numeric Types

TINYINT         -- 1 byte: -128 to 127
SMALLINT        -- 2 bytes: -32,768 to 32,767
INT             -- 4 bytes: -2 billion to 2 billion
BIGINT          -- 8 bytes: very large numbers
DECIMAL(10,2)   -- Exact: 99999999.99 (use for money)
FLOAT           -- Approximate (avoid for money)

String Types

CHAR(n)         -- Fixed length
VARCHAR(n)      -- Variable length (up to n)
TEXT            -- Up to 65,535 characters
MEDIUMTEXT      -- Up to 16MB
LONGTEXT        -- Up to 4GB

Date/Time Types

DATE            -- YYYY-MM-DD
TIME            -- HH:MM:SS
DATETIME        -- YYYY-MM-DD HH:MM:SS
TIMESTAMP       -- Auto-updating datetime
YEAR            -- YYYY

Boolean

BOOLEAN         -- Actually TINYINT(1): 0 or 1

Common Patterns

-- Primary key
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT

-- Money
price DECIMAL(10,2) NOT NULL

-- Email
email VARCHAR(255) NOT NULL UNIQUE

-- Timestamps
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- Yes/No
is_active BOOLEAN DEFAULT 1

4. DDL - Data Definition Language

CREATE DATABASE

-- Create database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Use database
USE myapp;

-- Check current database
SELECT DATABASE();

CREATE TABLE

CREATE TABLE users (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- With foreign key
CREATE TABLE orders (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB;

ALTER TABLE

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Modify column
ALTER TABLE users MODIFY COLUMN email VARCHAR(320) NOT NULL;

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Add foreign key
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Add index
ALTER TABLE users ADD INDEX idx_last_name (last_name);

DROP TABLE

-- Drop table
DROP TABLE IF EXISTS temp_data;

-- Drop multiple tables
DROP TABLE IF EXISTS table1, table2, table3;

TRUNCATE TABLE

-- Remove all data, keep structure, reset AUTO_INCREMENT
TRUNCATE TABLE logs;

View Table Structure

DESCRIBE users;
SHOW CREATE TABLE users;
SHOW TABLES;

5. DML - Data Manipulation Language

INSERT

-- Single row
INSERT INTO users (username, email, password_hash)
VALUES ('john', '[email protected]', 'hashed_password');

-- Multiple rows
INSERT INTO users (username, email) 
VALUES 
    ('john', '[email protected]'),
    ('jane', '[email protected]'),
    ('bob', '[email protected]');

-- Get last inserted ID
SELECT LAST_INSERT_ID();

-- Insert from SELECT
INSERT INTO users_backup SELECT * FROM users;

-- On duplicate key update
INSERT INTO products (sku, name, price)
VALUES ('LAP001', 'Laptop', 999.99)
ON DUPLICATE KEY UPDATE name = VALUES(name), price = VALUES(price);

UPDATE

-- Update single column
UPDATE users SET email = '[email protected]' WHERE id = 5;

-- Update multiple columns
UPDATE users 
SET first_name = 'John', last_name = 'Smith', updated_at = NOW()
WHERE id = 5;

-- Update with calculation
UPDATE products SET price = price * 1.10 WHERE category_id = 5;

-- Update with CASE
UPDATE products
SET discount = CASE
    WHEN stock > 100 THEN 20
    WHEN stock > 50 THEN 10
    ELSE 5
END;

-- Update with JOIN
UPDATE orders o
JOIN (
    SELECT order_id, SUM(subtotal) as total
    FROM order_items
    GROUP BY order_id
) oi ON o.id = oi.order_id
SET o.total = oi.total;

DELETE

-- Delete specific rows
DELETE FROM users WHERE id = 5;

-- Delete with condition
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Delete all rows (use TRUNCATE instead)
DELETE FROM temp_data;

-- Delete with JOIN
DELETE o
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.is_deleted = 1;

Transactions

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 99.99);
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity) VALUES (@order_id, 5, 2);
UPDATE products SET stock = stock - 2 WHERE id = 5;

COMMIT;  -- Save changes
-- OR
ROLLBACK;  -- Undo changes

6. DQL - Data Query Language

SELECT Basics

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, email, first_name FROM users;

-- With aliases
SELECT first_name AS 'First Name', email AS 'Email Address' FROM users;

-- Distinct values
SELECT DISTINCT city FROM users;

WHERE Clause

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 100;
SELECT * FROM users WHERE id IN (1, 5, 10);
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE phone IS NULL;

-- Logical operators
SELECT * FROM products WHERE price > 50 AND stock > 0;
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Computers';
SELECT * FROM users WHERE NOT is_deleted;

ORDER BY

-- Ascending (default)
SELECT * FROM users ORDER BY last_name;

-- Descending
SELECT * FROM users ORDER BY created_at DESC;

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

LIMIT

-- First 10 rows
SELECT * FROM products LIMIT 10;

-- Pagination (skip 10, get next 10)
SELECT * FROM products LIMIT 10 OFFSET 10;

-- Top 5 most expensive
SELECT * FROM products ORDER BY price DESC LIMIT 5;

String Functions

-- Concatenate
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Uppercase/Lowercase
SELECT UPPER(email), LOWER(name) FROM users;

-- Substring
SELECT SUBSTRING(email, 1, 5) FROM users;

-- Length
SELECT LENGTH(name), CHAR_LENGTH(name) FROM products;

-- Replace
SELECT REPLACE(phone, '-', '') FROM users;

-- Trim
SELECT TRIM('  hello  ');

Date Functions

-- Current date/time
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;

-- Format date
SELECT DATE_FORMAT(order_date, '%M %d, %Y') FROM orders;

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Date difference
SELECT DATEDIFF(NOW(), order_date) FROM orders;

-- Extract parts
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date) FROM orders;

Numeric Functions

-- Round, ceiling, floor
SELECT ROUND(price, 2), CEIL(price), FLOOR(price) FROM products;

-- Absolute value
SELECT ABS(-10);

-- Random
SELECT RAND();
SELECT * FROM products ORDER BY RAND() LIMIT 5;

Conditional Functions

-- IF
SELECT name, IF(stock > 0, 'In Stock', 'Out of Stock') AS status FROM products;

-- CASE
SELECT 
    name,
    CASE
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Mid-Range'
        ELSE 'Premium'
    END AS category
FROM products;

-- COALESCE (first non-NULL)
SELECT COALESCE(phone, email, 'No contact') FROM users;

7. JOINs

INNER JOIN

Returns only matching rows from both tables.

-- Basic INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Multiple tables
SELECT 
    u.name,
    o.order_date,
    p.name AS product,
    oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

LEFT JOIN

Returns all rows from left table, matched rows from right table (NULL if no match).

-- All users with their orders (including users with no orders)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Find users with NO orders
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

RIGHT JOIN

Returns all rows from right table, matched rows from left table.

-- Usually just use LEFT JOIN instead
SELECT * FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- Same as:
SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

CROSS JOIN

Returns all possible combinations (Cartesian product).

-- All color/size combinations
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

Self JOIN

Join table to itself.

-- Employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

8. Aggregate Functions

Basic Aggregates

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(phone) FROM users;  -- Count non-NULL
SELECT COUNT(DISTINCT city) FROM users;  -- Unique values

-- Sum
SELECT SUM(total) FROM orders;
SELECT SUM(price * stock) FROM products;

-- Average
SELECT AVG(price) FROM products;
SELECT ROUND(AVG(total), 2) FROM orders;

-- Min/Max
SELECT MIN(price), MAX(price) FROM products;
SELECT MIN(order_date), MAX(order_date) FROM orders;

GROUP BY

-- Count orders per user
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Total sales per user
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent,
    AVG(total) AS avg_order
FROM orders
GROUP BY user_id;

-- Sales by month
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS orders,
    SUM(total) AS revenue
FROM orders
GROUP BY month
ORDER BY month DESC;

HAVING

Filters grouped results (WHERE filters before grouping).

-- Users with more than 5 orders
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Categories with avg price > $100
SELECT 
    category_id,
    AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 100;

-- WHERE vs HAVING
SELECT 
    user_id,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'  -- Filter rows first
GROUP BY user_id
HAVING COUNT(*) > 2;               -- Filter groups after

9. Subqueries

Scalar Subquery (Single Value)

-- Products above average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Column Subquery (List of Values)

-- Users who have placed orders
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- Users who haven't placed orders
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

Table Subquery (Multiple Rows/Columns)

-- Join with subquery result
SELECT u.name, stats.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) stats ON u.id = stats.user_id;

Correlated Subquery

-- Products with above-category-average price
SELECT p.name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category_id = p.category_id
);

EXISTS

-- Users with orders (efficient)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders WHERE user_id = u.id
);

-- Products never ordered
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items WHERE product_id = p.id
);

Common Table Expressions (WITH)

-- More readable than nested subqueries
WITH customer_stats AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(total) AS lifetime_value
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.email,
    cs.order_count,
    cs.lifetime_value
FROM users u
JOIN customer_stats cs ON u.id = cs.user_id
WHERE cs.lifetime_value > 1000;

-- Multiple CTEs
WITH 
    top_customers AS (
        SELECT user_id FROM orders GROUP BY user_id HAVING SUM(total) > 1000
    ),
    active_users AS (
        SELECT id FROM users WHERE last_login > DATE_SUB(NOW(), INTERVAL 30 DAY)
    )
SELECT u.email
FROM users u
WHERE u.id IN (SELECT user_id FROM top_customers)
  AND u.id IN (SELECT id FROM active_users);

10. Indexes

Creating Indexes

-- Single column index
CREATE INDEX idx_email ON users(email);

-- Composite index
CREATE INDEX idx_name ON users(last_name, first_name);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Full-text index
CREATE FULLTEXT INDEX idx_content ON posts(title, body);

Viewing Indexes

SHOW INDEXES FROM users;

Dropping Indexes

DROP INDEX idx_email ON users;

When to Index

✅ Create Index:

Columns in WHERE clause
Foreign key columns
JOIN columns
ORDER BY columns
High selectivity (many unique values)

❌ Don't Index:

Small tables (< 1000 rows)
Low selectivity (few unique values like gender)
Frequently updated columns
Rarely queried columns

Query Optimization

-- See query execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- Key columns in EXPLAIN:
-- type: ALL (bad), ref (good), const (best)
-- key: which index used
-- rows: estimated rows scanned

11. Database Design

Design Process

Identify entities (users, products, orders)
Identify attributes (name, price, date)
Identify relationships (user has orders)
Define primary keys
Define foreign keys
Normalize

Normalization

First Normal Form (1NF):

Atomic values (no arrays/lists)
Each row unique
No repeating groups

-- ❌ Violates 1NF
products VARCHAR(500)  -- "Laptop, Mouse, Keyboard"

-- ✅ Conforms to 1NF
CREATE TABLE order_items (
    product_name VARCHAR(100)  -- One product per row
);

Second Normal Form (2NF):

Must be in 1NF
No partial dependencies (all non-key columns depend on entire primary key)

-- ❌ Violates 2NF (product_name depends only on product_id)
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(255),  -- Depends on product_id only
    PRIMARY KEY (order_id, product_id)
);

-- ✅ Conforms to 2NF (separate products table)
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(255));
CREATE TABLE order_items (order_id INT, product_id INT, quantity INT);

Third Normal Form (3NF):

Must be in 2NF
No transitive dependencies (non-key columns don't depend on other non-key columns)

-- ❌ Violates 3NF (department_name depends on department_id)
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    department_name VARCHAR(100)  -- Transitive dependency
);

-- ✅ Conforms to 3NF
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), department_id INT);

Design Patterns

Audit Trail:

ALTER TABLE products
ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ADD COLUMN created_by INT,
ADD COLUMN updated_by INT;

Soft Delete:

ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP NULL,
ADD COLUMN deleted_by INT NULL;

-- "Delete" user
UPDATE users SET deleted_at = NOW() WHERE id = 5;

-- Query non-deleted
SELECT * FROM users WHERE deleted_at IS NULL;

12. Security

User Management

-- Create user
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'SecurePass123!';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'webapp'@'localhost';

-- View privileges
SHOW GRANTS FOR 'webapp'@'localhost';

-- Revoke privileges
REVOKE DELETE ON myapp.* FROM 'webapp'@'localhost';

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

-- Apply changes
FLUSH PRIVILEGES;

Preventing SQL Injection

❌ Vulnerable:

$query = "SELECT * FROM users WHERE id = " . $_GET['id'];
// User input: 1 OR 1=1
// Query becomes: SELECT * FROM users WHERE id = 1 OR 1=1

✅ Secure (Prepared Statements):

$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);

Best Practices

✅ Use prepared statements (prevent SQL injection)
✅ Principle of least privilege (minimal permissions)
✅ Strong passwords (12+ chars, mixed case, numbers, symbols)
✅ Disable remote root login
✅ Use SSL/TLS for connections
✅ Regular backups
✅ Keep MySQL updated
✅ Enable audit logging
✅ Encrypt sensitive data

13. Backup & Recovery

mysqldump (Logical Backup)

# Backup database
mysqldump -u root -p myapp > myapp_backup.sql

# Backup with compression
mysqldump -u root -p myapp | gzip > myapp_backup.sql.gz

# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Restore
mysql -u root -p myapp < myapp_backup.sql
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp

Automated Backup Script

#!/bin/bash
DATE=$(date +%Y%m%d)
mysqldump -u backup_user -p'password' myapp | gzip > /backups/myapp_$DATE.sql.gz

# Delete backups older than 30 days
find /backups -name "myapp_*.sql.gz" -mtime +30 -delete

Schedule with Cron

# Daily backup at 2 AM
0 2 * * * /usr/local/bin/backup_mysql.sh

Point-in-Time Recovery

# 1. Restore from backup
mysql -u root -p myapp < last_backup.sql

# 2. Apply binary logs
mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -u root -p myapp

14. Best Practices

Database Design

Always use primary keys
Use foreign keys for relationships
Normalize to 3NF
Choose appropriate data types
Use constraints (NOT NULL, UNIQUE, CHECK)
Create ERD before implementation

SQL Queries

SELECT only needed columns (not *)
Use WHERE to filter early
Use LIMIT for pagination
Use prepared statements (prevent SQL injection)
Use transactions for related operations
Index foreign keys and WHERE columns

Performance

Index frequently queried columns
Don't over-index (slows writes)
Use EXPLAIN to analyze queries
Avoid functions in WHERE on indexed columns
Use covering indexes when possible
Regular OPTIMIZE TABLE

Security

Never use root in applications
Principle of least privilege
Strong passwords
Prepared statements (prevent SQL injection)
Regular backups
Keep MySQL updated

Common Mistakes

No primary key
Wrong data types (VARCHAR for numbers)
Not using foreign keys
SELECT * (returns unnecessary data)
Forgetting WHERE in UPDATE/DELETE
No indexes on foreign keys
SQL injection vulnerabilities

Quick Reference

MySQL Command Line

mysql -u username -p                    # Login
mysql -u username -p database_name      # Login to specific DB
quit or exit                            # Exit MySQL

Essential SQL Commands

SHOW DATABASES;                         -- List databases
USE database_name;                      -- Switch database
SHOW TABLES;                            -- List tables
DESCRIBE table_name;                    -- Show table structure
SELECT DATABASE();                      -- Show current database

Data Types Quick Reference

INT                     -- Whole numbers
DECIMAL(10,2)          -- Money (exact)
VARCHAR(255)           -- Text (variable length)
TEXT                   -- Long text
DATE                   -- Date only
DATETIME               -- Date and time
TIMESTAMP              -- Auto-updating datetime
BOOLEAN                -- True/false (0/1)

Common Patterns

-- Auto-increment primary key
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT

-- Foreign key
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

-- Timestamps
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-- Unique constraint
email VARCHAR(255) NOT NULL UNIQUE

-- Index
INDEX idx_column_name (column_name)

-- Check constraint
price DECIMAL(10,2) CHECK (price >= 0)

Common Query Patterns

Find Duplicates

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

Top N Per Group

-- Top 3 products per category by price
SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rn
    FROM products
) ranked
WHERE rn <= 3;

Pagination

-- Page 1 (rows 1-10)
SELECT * FROM products LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT * FROM products LIMIT 10 OFFSET 10;

Running Total

SELECT 
    order_date,
    total,
    SUM(total) OVER (ORDER BY order_date) as running_total
FROM orders;

Conditional Counting

SELECT 
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending
FROM orders;

PreviousReference Guide NextReference Guide

Last updated 1 month ago

Good morning

hashtagTable of Contents

hashtag1. Database Fundamentals

hashtagWhat is a Database?

hashtagACID Properties

hashtagRelational Database Concepts

hashtagRelationships

hashtag2. MySQL Installation (Ubuntu 24)

hashtagInstallation Steps

hashtagSecurity Script Recommendations

hashtagCreate User

hashtagService Management

hashtagLogin

hashtag3. Data Types

hashtagNumeric Types

hashtagString Types

hashtagDate/Time Types

hashtagBoolean

hashtagCommon Patterns

hashtag4. DDL - Data Definition Language

hashtagCREATE DATABASE

hashtagCREATE TABLE

hashtagALTER TABLE

hashtagDROP TABLE

hashtagTRUNCATE TABLE

hashtagView Table Structure

hashtag5. DML - Data Manipulation Language

hashtagINSERT

hashtagUPDATE

hashtagDELETE

hashtagTransactions

hashtag6. DQL - Data Query Language

hashtagSELECT Basics

hashtagWHERE Clause

hashtagORDER BY

hashtagLIMIT

hashtagString Functions

hashtagDate Functions

hashtagNumeric Functions

hashtagConditional Functions

hashtag7. JOINs

hashtagINNER JOIN

hashtagLEFT JOIN

hashtagRIGHT JOIN

hashtagCROSS JOIN

hashtagSelf JOIN

hashtag8. Aggregate Functions

hashtagBasic Aggregates

hashtagGROUP BY

hashtagHAVING

hashtag9. Subqueries

hashtagScalar Subquery (Single Value)

hashtagColumn Subquery (List of Values)

hashtagTable Subquery (Multiple Rows/Columns)

hashtagCorrelated Subquery

hashtagEXISTS

hashtagCommon Table Expressions (WITH)

hashtag10. Indexes

hashtagCreating Indexes

hashtagViewing Indexes

hashtagDropping Indexes

hashtagWhen to Index

hashtagQuery Optimization

hashtag11. Database Design

hashtagDesign Process

hashtagNormalization

hashtagDesign Patterns

hashtag12. Security

hashtagUser Management

hashtagPreventing SQL Injection

hashtagBest Practices

hashtag13. Backup & Recovery

hashtagmysqldump (Logical Backup)

hashtagAutomated Backup Script

hashtagSchedule with Cron

hashtagPoint-in-Time Recovery

hashtag14. Best Practices

hashtagDatabase Design

hashtagSQL Queries

hashtagPerformance

Table of Contents

1. Database Fundamentals

What is a Database?

ACID Properties

Relational Database Concepts

Relationships

2. MySQL Installation (Ubuntu 24)

Installation Steps

Security Script Recommendations

Create User

Service Management

Login

3. Data Types

Numeric Types

String Types

Date/Time Types

Boolean

Common Patterns

4. DDL - Data Definition Language

CREATE DATABASE

CREATE TABLE

ALTER TABLE

DROP TABLE

TRUNCATE TABLE

View Table Structure

5. DML - Data Manipulation Language

INSERT

UPDATE

DELETE

Transactions

6. DQL - Data Query Language

SELECT Basics

WHERE Clause

ORDER BY

LIMIT

String Functions

Date Functions

Numeric Functions

Conditional Functions

7. JOINs

INNER JOIN

LEFT JOIN

RIGHT JOIN

CROSS JOIN

Self JOIN

8. Aggregate Functions

Basic Aggregates

GROUP BY

HAVING

9. Subqueries

Scalar Subquery (Single Value)

Column Subquery (List of Values)

Table Subquery (Multiple Rows/Columns)

Correlated Subquery

EXISTS

Common Table Expressions (WITH)

10. Indexes

Creating Indexes

Viewing Indexes

Dropping Indexes

When to Index

Query Optimization

11. Database Design

Design Process

Normalization

Design Patterns

12. Security

User Management

Preventing SQL Injection

Best Practices

13. Backup & Recovery

mysqldump (Logical Backup)

Automated Backup Script

Schedule with Cron

Point-in-Time Recovery

14. Best Practices

Database Design

SQL Queries

Performance

Security