MySQL - Relational Database Management System

MySQL is one of the world's most popular open-source relational database management systems, powering countless web applications, enterprise systems, and data-driven platforms. Originally developed by MySQL AB in 1995 and now owned by Oracle Corporation, MySQL has established itself as a cornerstone of modern web development through its reliability, performance, and ease of use. As part of the classic LAMP (Linux, Apache, MySQL, PHP) stack, MySQL has been instrumental in the growth of dynamic web applications and continues to evolve with modern database requirements including cloud deployment, high availability, and advanced analytics capabilities.

Installation and Setup

Installing MySQL Server

-- Ubuntu/Debian
sudo apt update
sudo apt install mysql-server mysql-client

-- CentOS/RHEL/Fedora
sudo dnf install mysql-server mysql

-- macOS (using Homebrew)
brew install mysql

-- Windows (using MySQL Installer)
-- Download from https://dev.mysql.com/downloads/installer/

-- Start MySQL service
sudo systemctl start mysql
sudo systemctl enable mysql

-- Secure installation
sudo mysql_secure_installation

Initial Configuration

-- Connect to MySQL as root
sudo mysql -u root -p

-- Create a new database
CREATE DATABASE myapp_db;

-- Create a new user
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;

-- Show databases
SHOW DATABASES;

-- Use a database
USE myapp_db;

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

Configuration Files

-- Main configuration file locations
-- Linux: /etc/mysql/mysql.conf.d/mysqld.cnf
-- macOS: /usr/local/etc/my.cnf
-- Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

-- Key configuration sections
[mysqld]
port = 3306
bind-address = 127.0.0.1
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock

-- Performance tuning
innodb_buffer_pool_size = 1G
max_connections = 200
query_cache_size = 64M
tmp_table_size = 64M
max_heap_table_size = 64M

-- Logging
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Database and Table Operations

Database Management

-- Create database with character set
CREATE DATABASE ecommerce_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Show all databases
SHOW DATABASES;

-- Drop database
DROP DATABASE IF EXISTS old_database;

-- Show database information
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'ecommerce_db';

-- Show database size
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'ecommerce_db'
GROUP BY table_schema;

Table Creation and Management

-- Create table with various data types
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    profile_data JSON,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
);

-- Create table with foreign keys
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    shipping_address TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_order_status (order_status),
    INDEX idx_order_date (order_date)
);

-- Show table structure
DESCRIBE users;
SHOW CREATE TABLE users;

-- Show all tables
SHOW TABLES;

-- Show table information
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    CREATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

Altering Tables

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

-- Modify column
ALTER TABLE users 
MODIFY COLUMN phone VARCHAR(25);

-- Change column name and type
ALTER TABLE users 
CHANGE COLUMN phone phone_number VARCHAR(25);

-- Drop column
ALTER TABLE users 
DROP COLUMN phone_number;

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

-- Drop index
ALTER TABLE users 
DROP INDEX idx_last_name;

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

-- Drop foreign key constraint
ALTER TABLE orders 
DROP FOREIGN KEY fk_orders_user_id;

-- Rename table
ALTER TABLE users RENAME TO customers;

Data Types and Storage

Numeric Data Types

-- Integer types
CREATE TABLE numeric_examples (
    tiny_int TINYINT,           -- 1 byte: -128 to 127
    small_int SMALLINT,         -- 2 bytes: -32,768 to 32,767
    medium_int MEDIUMINT,       -- 3 bytes: -8,388,608 to 8,388,607
    regular_int INT,            -- 4 bytes: -2,147,483,648 to 2,147,483,647
    big_int BIGINT,             -- 8 bytes: very large range
    
    -- Unsigned variants
    unsigned_int INT UNSIGNED,   -- 0 to 4,294,967,295
    
    -- Decimal types
    price DECIMAL(10, 2),       -- Exact decimal: 10 digits, 2 decimal places
    percentage FLOAT(7, 4),     -- Floating point: 7 digits, 4 decimal places
    scientific DOUBLE,          -- Double precision floating point
    
    -- Boolean
    is_active BOOLEAN,          -- Synonym for TINYINT(1)
    
    -- Bit
    flags BIT(8)               -- Bit field: 1 to 64 bits
);

-- Examples of numeric operations
INSERT INTO numeric_examples (price, percentage, is_active) 
VALUES (199.99, 15.5000, TRUE);

-- Numeric functions
SELECT 
    ROUND(price, 1) AS rounded_price,
    CEIL(percentage) AS ceiling_percentage,
    FLOOR(percentage) AS floor_percentage,
    ABS(-100) AS absolute_value,
    MOD(17, 5) AS modulo_result
FROM numeric_examples;

String Data Types

-- String types table
CREATE TABLE string_examples (
    -- Fixed length
    country_code CHAR(2),       -- Exactly 2 characters
    
    -- Variable length
    username VARCHAR(50),       -- Up to 50 characters
    description TEXT,           -- Up to 65,535 characters
    long_content LONGTEXT,      -- Up to 4GB
    
    -- Binary data
    file_data BLOB,             -- Binary data up to 65,535 bytes
    large_file LONGBLOB,        -- Binary data up to 4GB
    
    -- Enumeration
    status ENUM('active', 'inactive', 'pending'),
    
    -- Set (multiple values)
    permissions SET('read', 'write', 'execute', 'admin')
);

-- String operations
INSERT INTO string_examples (
    country_code, username, description, status, permissions
) VALUES (
    'US', 'john_doe', 'A sample user description', 'active', 'read,write'
);

-- String functions
SELECT 
    CONCAT(username, ' (', country_code, ')') AS user_display,
    UPPER(username) AS uppercase_username,
    LENGTH(description) AS description_length,
    SUBSTRING(description, 1, 50) AS short_description,
    REPLACE(description, 'sample', 'example') AS modified_description
FROM string_examples;

Date and Time Data Types

-- Date and time types
CREATE TABLE datetime_examples (
    birth_date DATE,            -- YYYY-MM-DD
    appointment_time TIME,      -- HH:MM:SS
    created_at DATETIME,        -- YYYY-MM-DD HH:MM:SS
    updated_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    birth_year YEAR            -- YYYY
);

-- Date and time operations
INSERT INTO datetime_examples (
    birth_date, appointment_time, created_at, birth_year
) VALUES (
    '1990-05-15', '14:30:00', '2024-01-15 10:30:45', 1990
);

-- Date functions
SELECT 
    NOW() AS current_datetime,
    CURDATE() AS current_date,
    CURTIME() AS current_time,
    DATE_ADD(birth_date, INTERVAL 30 YEAR) AS future_date,
    DATEDIFF(NOW(), birth_date) AS days_since_birth,
    YEAR(birth_date) AS birth_year_extracted,
    MONTH(birth_date) AS birth_month,
    DAY(birth_date) AS birth_day,
    DAYNAME(birth_date) AS birth_day_name,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS formatted_date
FROM datetime_examples;

JSON Data Type

-- JSON data type (MySQL 5.7+)
CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    profile_data JSON,
    preferences JSON,
    metadata JSON
);

-- Insert JSON data
INSERT INTO user_profiles (user_id, profile_data, preferences) VALUES
(1, '{"name": "John Doe", "age": 30, "skills": ["PHP", "MySQL", "JavaScript"]}',
 '{"theme": "dark", "notifications": true, "language": "en"}'),
(2, '{"name": "Jane Smith", "age": 25, "skills": ["Python", "PostgreSQL"]}',
 '{"theme": "light", "notifications": false, "language": "es"}');

-- Query JSON data
SELECT 
    user_id,
    JSON_EXTRACT(profile_data, '$.name') AS name,
    JSON_EXTRACT(profile_data, '$.age') AS age,
    JSON_EXTRACT(profile_data, '$.skills[0]') AS first_skill,
    JSON_LENGTH(JSON_EXTRACT(profile_data, '$.skills')) AS skill_count,
    profile_data->'$.name' AS name_shorthand,
    profile_data->>'$.name' AS name_unquoted
FROM user_profiles;

-- Update JSON data
UPDATE user_profiles 
SET profile_data = JSON_SET(profile_data, '$.age', 31)
WHERE user_id = 1;

-- Add to JSON array
UPDATE user_profiles 
SET profile_data = JSON_ARRAY_APPEND(profile_data, '$.skills', 'React')
WHERE user_id = 1;

-- Search in JSON
SELECT * FROM user_profiles 
WHERE JSON_CONTAINS(profile_data, '"PHP"', '$.skills');

CRUD Operations

INSERT Operations

-- Basic insert
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('john_doe', 'john@example.com', 'John', 'Doe');

-- Multiple row insert
INSERT INTO users (username, email, first_name, last_name) VALUES
('jane_smith', 'jane@example.com', 'Jane', 'Smith'),
('bob_wilson', 'bob@example.com', 'Bob', 'Wilson'),
('alice_brown', 'alice@example.com', 'Alice', 'Brown');

-- Insert with SELECT
INSERT INTO users (username, email, first_name, last_name)
SELECT CONCAT('user_', id), CONCAT('user', id, '@example.com'), 'Test', CONCAT('User', id)
FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3) AS temp;

-- Insert with ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('john_doe', 'john.doe@example.com', 'John', 'Doe')
ON DUPLICATE KEY UPDATE 
    email = VALUES(email),
    updated_at = CURRENT_TIMESTAMP;

-- Insert and get last inserted ID
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('new_user', 'new@example.com', 'New', 'User');
SELECT LAST_INSERT_ID();

SELECT Operations

-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT id, username, email, created_at FROM users;

-- Select with WHERE clause
SELECT * FROM users 
WHERE is_active = TRUE 
AND created_at > '2024-01-01';

-- Select with LIKE pattern matching
SELECT * FROM users 
WHERE username LIKE 'john%' 
OR email LIKE '%@gmail.com';

-- Select with IN clause
SELECT * FROM users 
WHERE id IN (1, 3, 5, 7);

-- Select with BETWEEN
SELECT * FROM users 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Select with ORDER BY
SELECT * FROM users 
ORDER BY last_name ASC, first_name ASC;

-- Select with LIMIT and OFFSET
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20;

-- Select with aggregate functions
SELECT 
    COUNT(*) AS total_users,
    COUNT(DISTINCT email) AS unique_emails,
    MIN(created_at) AS first_user_date,
    MAX(created_at) AS latest_user_date,
    AVG(YEAR(CURDATE()) - YEAR(date_of_birth)) AS average_age
FROM users 
WHERE is_active = TRUE;

-- Select with GROUP BY and HAVING
SELECT 
    YEAR(created_at) AS registration_year,
    COUNT(*) AS user_count
FROM users 
GROUP BY YEAR(created_at)
HAVING user_count > 10
ORDER BY registration_year DESC;

UPDATE Operations

-- Basic update
UPDATE users 
SET first_name = 'Jonathan' 
WHERE username = 'john_doe';

-- Update multiple columns
UPDATE users 
SET 
    first_name = 'Jane',
    last_name = 'Johnson',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 2;

-- Update with calculation
UPDATE users 
SET username = LOWER(CONCAT(first_name, '_', last_name))
WHERE username IS NULL;

-- Update with JOIN
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.order_date
WHERE o.order_date = (
    SELECT MAX(order_date) 
    FROM orders o2 
    WHERE o2.user_id = u.id
);

-- Conditional update with CASE
UPDATE users 
SET status = CASE 
    WHEN DATEDIFF(NOW(), created_at) > 365 THEN 'veteran'
    WHEN DATEDIFF(NOW(), created_at) > 30 THEN 'regular'
    ELSE 'new'
END;

-- Update with LIMIT
UPDATE users 
SET is_active = FALSE 
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 100;

DELETE Operations

-- Basic delete
DELETE FROM users 
WHERE username = 'test_user';

-- Delete with multiple conditions
DELETE FROM users 
WHERE is_active = FALSE 
AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- Delete with JOIN
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL 
AND u.created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- Delete with LIMIT
DELETE FROM users 
WHERE is_active = FALSE 
ORDER BY created_at ASC 
LIMIT 50;

-- Safe delete with transaction
START TRANSACTION;
SELECT COUNT(*) FROM users WHERE is_active = FALSE;
DELETE FROM users WHERE is_active = FALSE;
-- COMMIT; or ROLLBACK; based on verification

Advanced Queries and Joins

JOIN Operations

-- INNER JOIN
SELECT 
    u.username,
    u.email,
    o.order_number,
    o.total_amount,
    o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date > DATE_SUB(NOW(), INTERVAL 30 DAY);

-- LEFT JOIN
SELECT 
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

-- RIGHT JOIN
SELECT 
    o.order_number,
    o.total_amount,
    u.username,
    u.email
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN (using UNION)
SELECT u.username, o.order_number
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_number
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Self JOIN
SELECT 
    e1.name AS employee,
    e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Multiple JOINs
SELECT 
    u.username,
    o.order_number,
    oi.product_name,
    oi.quantity,
    oi.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.order_date > DATE_SUB(NOW(), INTERVAL 7 DAY);

Subqueries

-- Subquery in WHERE clause
SELECT * FROM users 
WHERE id IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE total_amount > 1000
);

-- Correlated subquery
SELECT 
    u.username,
    u.email,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count,
    (SELECT MAX(total_amount) FROM orders o WHERE o.user_id = u.id) AS max_order
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.order_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
);

-- Subquery in FROM clause
SELECT 
    avg_order.user_id,
    avg_order.avg_amount,
    u.username
FROM (
    SELECT 
        user_id,
        AVG(total_amount) AS avg_amount
    FROM orders
    GROUP BY user_id
    HAVING AVG(total_amount) > 500
) AS avg_order
JOIN users u ON avg_order.user_id = u.id;

-- Subquery with ANY/ALL
SELECT * FROM orders 
WHERE total_amount > ALL (
    SELECT total_amount 
    FROM orders 
    WHERE order_date < '2024-01-01'
);

Window Functions (MySQL 8.0+)

-- ROW_NUMBER
SELECT 
    username,
    email,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

-- RANK and DENSE_RANK
SELECT 
    user_id,
    total_amount,
    order_date,
    RANK() OVER (PARTITION BY user_id ORDER BY total_amount DESC) AS amount_rank,
    DENSE_RANK() OVER (ORDER BY total_amount DESC) AS overall_rank
FROM orders;

-- LAG and LEAD
SELECT 
    order_date,
    total_amount,
    LAG(total_amount, 1) OVER (ORDER BY order_date) AS previous_amount,
    LEAD(total_amount, 1) OVER (ORDER BY order_date) AS next_amount
FROM orders
ORDER BY order_date;

-- Running totals
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders
ORDER BY order_date;

-- NTILE for percentiles
SELECT 
    username,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent) AS quartile
FROM (
    SELECT 
        u.username,
        COALESCE(SUM(o.total_amount), 0) AS total_spent
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.username
) AS user_spending;

Common Table Expressions (CTEs)

-- Basic CTE
WITH monthly_sales AS (
    SELECT 
        YEAR(order_date) AS year,
        MONTH(order_date) AS month,
        SUM(total_amount) AS total_sales,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT 
    year,
    month,
    total_sales,
    order_count,
    LAG(total_sales) OVER (ORDER BY year, month) AS previous_month_sales
FROM monthly_sales
ORDER BY year, month;

-- Recursive CTE
WITH RECURSIVE employee_hierarchy AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy
ORDER BY level, name;

-- Multiple CTEs
WITH 
high_value_customers AS (
    SELECT user_id, SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY user_id
    HAVING SUM(total_amount) > 5000
),
recent_orders AS (
    SELECT user_id, COUNT(*) AS recent_order_count
    FROM orders
    WHERE order_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
)
SELECT 
    u.username,
    hvc.total_spent,
    COALESCE(ro.recent_order_count, 0) AS recent_orders
FROM users u
JOIN high_value_customers hvc ON u.id = hvc.user_id
LEFT JOIN recent_orders ro ON u.id = ro.user_id
ORDER BY hvc.total_spent DESC;

Indexes and Performance Optimization

Index Types and Creation

-- Primary key index (automatically created)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Single column index
CREATE INDEX idx_products_name ON products(name);

-- Composite index
CREATE INDEX idx_products_category_price ON products(category_id, price);

-- Unique index
CREATE UNIQUE INDEX idx_products_sku ON products(sku);

-- Partial index (functional index)
CREATE INDEX idx_products_active_name ON products(name) WHERE is_active = 1;

-- Full-text index
ALTER TABLE products ADD FULLTEXT(name, description);

-- Show indexes
SHOW INDEXES FROM products;

-- Drop index
DROP INDEX idx_products_name ON products;

-- Analyze index usage
EXPLAIN SELECT * FROM products WHERE name = 'iPhone';
EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 1 AND price > 100;

Query Optimization

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT 
    u.username,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.username;

-- Optimize with proper indexing
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Use covering indexes
CREATE INDEX idx_users_covering ON users(created_at, id, username);

-- Optimize LIMIT queries
SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY order_date DESC 
LIMIT 10;

-- Use EXISTS instead of IN for better performance
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.total_amount > 1000
);

-- Avoid SELECT *
SELECT id, username, email FROM users 
WHERE is_active = 1;

-- Use appropriate data types
-- Use INT instead of VARCHAR for numeric IDs
-- Use ENUM for limited string values
-- Use appropriate VARCHAR length

Performance Monitoring

-- Show slow queries
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- Show process list
SHOW PROCESSLIST;

-- Show table status
SHOW TABLE STATUS LIKE 'users';

-- Analyze table
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Show index statistics
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    NULLABLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME;

-- Query performance schema
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 AS avg_time_seconds,
    SUM_TIMER_WAIT/1000000000 AS total_time_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

Transactions and Concurrency

Transaction Management

-- Basic transaction
START TRANSACTION;

INSERT INTO users (username, email, first_name, last_name) 
VALUES ('new_user', 'new@example.com', 'New', 'User');

INSERT INTO orders (user_id, order_number, total_amount) 
VALUES (LAST_INSERT_ID(), 'ORD-001', 99.99);

COMMIT;

-- Transaction with rollback
START TRANSACTION;

UPDATE users SET email = 'updated@example.com' WHERE id = 1;
UPDATE orders SET total_amount = 199.99 WHERE user_id = 1;

-- Check if everything looks correct
SELECT * FROM users WHERE id = 1;
SELECT * FROM orders WHERE user_id = 1;

-- If something is wrong:
ROLLBACK;
-- If everything is correct:
-- COMMIT;

-- Savepoints
START TRANSACTION;

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
SAVEPOINT sp1;

INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');
SAVEPOINT sp2;

INSERT INTO users (username, email) VALUES ('user3', 'user3@example.com');

-- Rollback to savepoint
ROLLBACK TO SAVEPOINT sp2;

COMMIT;

Isolation Levels

-- Show current isolation level
SELECT @@transaction_isolation;

-- Set isolation level for session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set isolation level for next transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Isolation levels:
-- READ UNCOMMITTED: Allows dirty reads
-- READ COMMITTED: Prevents dirty reads
-- REPEATABLE READ: Prevents dirty and non-repeatable reads (MySQL default)
-- SERIALIZABLE: Prevents dirty, non-repeatable reads and phantom reads

-- Example of isolation level effects
-- Session 1:
START TRANSACTION;
SELECT * FROM users WHERE id = 1;
-- Keep transaction open

-- Session 2:
START TRANSACTION;
UPDATE users SET first_name = 'Updated' WHERE id = 1;
COMMIT;

-- Session 1 (continued):
SELECT * FROM users WHERE id = 1; -- Result depends on isolation level
COMMIT;

Locking

-- Explicit locking
-- Shared lock (read lock)
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- Exclusive lock (write lock)
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- Table-level locking
LOCK TABLES users READ;
SELECT * FROM users;
UNLOCK TABLES;

LOCK TABLES users WRITE;
UPDATE users SET is_active = 1 WHERE is_active = 0;
UNLOCK TABLES;

-- Show locked tables
SHOW OPEN TABLES WHERE In_use > 0;

-- Show current locks
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- Deadlock detection
-- MySQL automatically detects and resolves deadlocks
-- Check deadlock information
SHOW ENGINE INNODB STATUS;

Stored Procedures and Functions

Stored Procedures

-- Basic stored procedure
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
    SELECT 
        o.id,
        o.order_number,
        o.total_amount,
        o.order_date,
        o.order_status
    FROM orders o
    WHERE o.user_id = user_id
    ORDER BY o.order_date DESC;
END //
DELIMITER ;

-- Call stored procedure
CALL GetUserOrders(1);

-- Procedure with parameters and variables
DELIMITER //
CREATE PROCEDURE UpdateUserStatus(
    IN p_user_id INT,
    IN p_status VARCHAR(20),
    OUT p_affected_rows INT
)
BEGIN
    DECLARE v_current_status VARCHAR(20);
    
    -- Get current status
    SELECT status INTO v_current_status 
    FROM users 
    WHERE id = p_user_id;
    
    -- Update if different
    IF v_current_status != p_status THEN
        UPDATE users 
        SET status = p_status, updated_at = NOW() 
        WHERE id = p_user_id;
        
        SET p_affected_rows = ROW_COUNT();
    ELSE
        SET p_affected_rows = 0;
    END IF;
END //
DELIMITER ;

-- Call procedure with output parameter
CALL UpdateUserStatus(1, 'active', @affected);
SELECT @affected;

-- Procedure with error handling
DELIMITER //
CREATE PROCEDURE SafeTransferFunds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts 
    SET balance = balance - amount 
    WHERE id = from_account AND balance >= amount;
    
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    UPDATE accounts 
    SET balance = balance + amount 
    WHERE id = to_account;
    
    COMMIT;
END //
DELIMITER ;

Functions

-- Scalar function
DELIMITER //
CREATE FUNCTION CalculateAge(birth_date DATE) 
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END //
DELIMITER ;

-- Use function
SELECT username, CalculateAge(date_of_birth) AS age 
FROM users;

-- Function with conditional logic
DELIMITER //
CREATE FUNCTION GetDiscountRate(total_amount DECIMAL(10,2))
RETURNS DECIMAL(5,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE discount_rate DECIMAL(5,2);
    
    CASE
        WHEN total_amount >= 1000 THEN SET discount_rate = 0.15;
        WHEN total_amount >= 500 THEN SET discount_rate = 0.10;
        WHEN total_amount >= 100 THEN SET discount_rate = 0.05;
        ELSE SET discount_rate = 0.00;
    END CASE;
    
    RETURN discount_rate;
END //
DELIMITER ;

-- Show procedures and functions
SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- Drop procedure or function
DROP PROCEDURE IF EXISTS GetUserOrders;
DROP FUNCTION IF EXISTS CalculateAge;

Triggers

-- BEFORE INSERT trigger
DELIMITER //
CREATE TRIGGER users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.username = LOWER(NEW.username);
    SET NEW.email = LOWER(NEW.email);
    
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
END //
DELIMITER ;

-- AFTER UPDATE trigger
DELIMITER //
CREATE TRIGGER users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit_log (
        user_id, 
        action, 
        old_values, 
        new_values, 
        changed_at
    ) VALUES (
        NEW.id,
        'UPDATE',
        JSON_OBJECT('username', OLD.username, 'email', OLD.email),
        JSON_OBJECT('username', NEW.username, 'email', NEW.email),
        NOW()
    );
END //
DELIMITER ;

-- BEFORE DELETE trigger
DELIMITER //
CREATE TRIGGER orders_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO deleted_orders_archive 
    SELECT * FROM orders WHERE id = OLD.id;
END //
DELIMITER ;

-- Show triggers
SHOW TRIGGERS;

-- Drop trigger
DROP TRIGGER IF EXISTS users_before_insert;

Backup and Recovery

Backup Strategies

-- mysqldump for logical backups
-- Full database backup
mysqldump -u root -p --single-transaction --routines --triggers ecommerce_db > backup.sql

-- Backup specific tables
mysqldump -u root -p ecommerce_db users orders > tables_backup.sql

-- Backup with compression
mysqldump -u root -p --single-transaction ecommerce_db | gzip > backup.sql.gz

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

-- Backup structure only
mysqldump -u root -p --no-data ecommerce_db > structure_only.sql

-- Backup data only
mysqldump -u root -p --no-create-info ecommerce_db > data_only.sql

-- Backup with custom options
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --opt \
  ecommerce_db > full_backup.sql

Restore Operations

-- Restore from backup
mysql -u root -p ecommerce_db < backup.sql

-- Restore compressed backup
gunzip < backup.sql.gz | mysql -u root -p ecommerce_db

-- Restore specific tables
mysql -u root -p ecommerce_db < tables_backup.sql

-- Create database and restore
mysql -u root -p -e "CREATE DATABASE ecommerce_db_restored;"
mysql -u root -p ecommerce_db_restored < backup.sql

-- Restore with progress monitoring
pv backup.sql | mysql -u root -p ecommerce_db

-- Point-in-time recovery using binary logs
-- Enable binary logging in my.cnf:
-- log-bin = mysql-bin
-- server-id = 1

-- Show binary logs
SHOW BINARY LOGS;

-- Show binary log events
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- Extract SQL from binary log
mysqlbinlog mysql-bin.000001 > binlog.sql

-- Point-in-time recovery
mysql -u root -p ecommerce_db < full_backup.sql
mysqlbinlog --start-datetime="2024-01-15 10:00:00" \
           --stop-datetime="2024-01-15 11:00:00" \
           mysql-bin.000001 | mysql -u root -p ecommerce_db

Replication Setup

-- Master configuration (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = ecommerce_db

-- Slave configuration (my.cnf)
[mysqld]
server-id = 2
relay-log = mysql-relay-bin

-- Create replication user on master
CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

-- Get master status
SHOW MASTER STATUS;

-- Configure slave
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'replication_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- Start slave
START SLAVE;

-- Check slave status
SHOW SLAVE STATUS\G

-- Stop slave
STOP SLAVE;

MySQL's comprehensive feature set, from basic CRUD operations to advanced replication and performance optimization, makes it an excellent choice for a wide range of applications. Its mature ecosystem, extensive documentation, and strong community support continue to make it one of the most popular database systems worldwide. Understanding these core concepts and commands provides a solid foundation for building robust, scalable database-driven applications.