MySQL Lessons

-- Connect via CLI
mysql -u root -p
mysql -u username -p -h hostname database_name

-- Show version
SELECT VERSION();

-- Show current user
SELECT USER();

SELECT column1, column2, ...
FROM table_name;

-- Select all columns
SELECT * FROM customers;

-- Select specific columns
SELECT first_name, last_name, email
FROM customers;

-- With calculation
SELECT
    product_name,
    price,
    price * 1.1 AS price_with_tax
FROM products;

-- With string function
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(email) AS email_upper
FROM customers;

-- Get unique cities (ignores duplicates)
SELECT DISTINCT city FROM customers;

-- DISTINCT applies to the COMBINATION of columns
SELECT DISTINCT city, country FROM customers;
-- Returns unique city+country pairs

-- Count distinct values
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

-- DISTINCT with ORDER BY
SELECT DISTINCT category
FROM products
ORDER BY category;

SELECT column1, column2
FROM table_name
WHERE condition;

-- Comparison operators
WHERE price = 99.99       -- equal
WHERE price != 99.99      -- not equal (also: <>)
WHERE price > 100         -- greater than
WHERE price >= 100        -- greater or equal
WHERE price < 50          -- less than
WHERE price <= 50         -- less or equal

-- String comparison
WHERE name = 'Alice'       -- exact match (case-insensitive by default)
WHERE status = 'active'

-- Range
WHERE price BETWEEN 10 AND 100  -- inclusive!
WHERE age BETWEEN 18 AND 65

-- NULL check (NEVER use = NULL!)
WHERE email IS NULL
WHERE email IS NOT NULL

-- Multiple conditions (AND, OR, NOT)
WHERE price > 100 AND category = 'Electronics'
WHERE city = 'NYC' OR city = 'LA'
WHERE NOT status = 'deleted'

-- Sort ascending (default)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC;

-- Sort descending
SELECT * FROM products ORDER BY price DESC;

-- Sort by multiple columns
SELECT * FROM employees
ORDER BY department ASC, salary DESC;
-- Sorts by dept A→Z, then by salary high→low within each dept

-- Sort by column alias
SELECT name, price * 0.9 AS discounted
FROM products
ORDER BY discounted DESC;

-- Sort by expression
SELECT * FROM orders ORDER BY YEAR(created_at) DESC, MONTH(created_at);

-- Sort by column position (avoid in production)
SELECT name, age, salary FROM employees ORDER BY 3 DESC;  -- 3 = salary

-- Basic AND
SELECT * FROM products
WHERE category = 'Electronics' AND price < 100;

-- Multiple AND conditions
SELECT * FROM orders
WHERE status = 'shipped'
  AND total > 500
  AND customer_country = 'US';

-- AND with parentheses (precedence)
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
  AND price BETWEEN 100 AND 500
  AND in_stock = 1;

-- AND with date range
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2026-04-01'
  AND status != 'cancelled';

-- Basic OR
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Computers';

-- Better with IN (cleaner for multiple same-column ORs)
SELECT * FROM products
WHERE category IN ('Electronics', 'Computers', 'Accessories');

-- Mixed AND/OR — use parentheses!
SELECT * FROM orders
WHERE (status = 'pending' OR status = 'processing')
  AND total > 100;

-- Without parentheses (wrong intent!):
-- status='pending' OR (status='processing' AND total>100)
SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing' AND total > 100;

-- Basic NOT
SELECT * FROM products WHERE NOT discontinued;

-- NOT with comparison
SELECT * FROM employees WHERE NOT department = 'HR';
-- Equivalent to:
SELECT * FROM employees WHERE department != 'HR';
SELECT * FROM employees WHERE department <> 'HR';

-- NOT IN
SELECT * FROM products
WHERE category NOT IN ('Deprecated', 'Draft', 'Archive');

-- NOT LIKE
SELECT * FROM customers
WHERE email NOT LIKE '%@spam.com';

-- NOT BETWEEN
SELECT * FROM products
WHERE price NOT BETWEEN 100 AND 500;

-- NOT NULL
SELECT * FROM users WHERE phone IS NOT NULL;

-- NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);  -- customers with no orders

-- Insert specifying columns (recommended!)
INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Alice', 'Smith', 'alice@lab.dev', 'New York');

-- Insert all columns (column order must match table definition)
INSERT INTO customers
VALUES (NULL, 'Bob', 'Jones', 'bob@lab.dev', 'LA', NOW());

-- Insert multiple rows (efficient — one round trip to DB)
INSERT INTO products (name, price, category) VALUES
    ('Laptop', 999.99, 'Electronics'),
    ('Mouse', 29.99, 'Electronics'),
    ('Desk', 349.00, 'Furniture');

-- Insert from SELECT (copy data from another table)
INSERT INTO archive_orders (order_id, customer_id, total)
SELECT id, customer_id, total
FROM orders
WHERE YEAR(created_at) < 2024;

-- NULL arithmetic
NULL + 1        -- NULL
NULL * 0        -- NULL (not 0!)
NULL = NULL     -- NULL (not TRUE!)
NULL != NULL    -- NULL

-- Correct NULL comparisons
WHERE email IS NULL      -- ✓
WHERE email IS NOT NULL  -- ✓
WHERE email = NULL       -- ✗ always NULL (never matches!)

-- Basic UPDATE (always add WHERE!)
UPDATE customers
SET email = 'newemail@lab.dev'
WHERE id = 1;

-- Update multiple columns
UPDATE products
SET price = 899.99,
    stock = stock - 1,
    updated_at = NOW()
WHERE id = 42;

-- Update based on calculation
UPDATE employees
SET salary = salary * 1.10     -- 10% raise
WHERE department = 'Engineering'
  AND performance_rating = 'Excellent';

-- Update with subquery
UPDATE products
SET price = price * 0.9        -- 10% discount
WHERE id IN (
    SELECT product_id FROM promotions
    WHERE active = 1
);

-- ⚠️ DANGEROUS — updates ALL rows:
UPDATE products SET discount = 0;  -- no WHERE!

-- Delete specific rows (always use WHERE!)
DELETE FROM customers WHERE id = 42;

-- Delete based on condition
DELETE FROM orders
WHERE status = 'cancelled'
  AND created_at < '2024-01-01';

-- Delete with LIMIT (safe for large tables)
DELETE FROM logs
WHERE created_at < '2025-01-01'
LIMIT 10000;  -- batch delete

-- ⚠️ Delete ALL rows (keeps table structure):
DELETE FROM session_tokens;

-- TRUNCATE is faster for deleting all rows:
TRUNCATE TABLE session_tokens;
-- Differences from DELETE:
-- 1. Cannot use WHERE
-- 2. Cannot roll back (in most cases)
-- 3. Resets AUTO_INCREMENT counter
-- 4. Much faster (doesn't log row-by-row)

-- Get first N rows
SELECT * FROM products ORDER BY price LIMIT 10;

-- Skip M rows, then get N (pagination)
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;      -- rows 21-30 (page 3 with page size 10)

-- MySQL shorthand: LIMIT offset, count
SELECT * FROM products ORDER BY id LIMIT 20, 10;  -- same as above

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

-- Pagination formula:
-- Page N (0-indexed): LIMIT page_size OFFSET (N * page_size)
-- Page 0: LIMIT 10 OFFSET 0
-- Page 1: LIMIT 10 OFFSET 10
-- Page 2: LIMIT 10 OFFSET 20

-- Basic MIN and MAX
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM products;

SELECT MIN(created_at) AS oldest_order, MAX(created_at) AS newest_order
FROM orders;

-- With WHERE filter
SELECT MIN(salary) AS min_eng_salary, MAX(salary) AS max_eng_salary
FROM employees
WHERE department = 'Engineering';

-- MIN/MAX per group (combine with GROUP BY)
SELECT department,
       MIN(salary) AS min_sal,
       MAX(salary) AS max_sal,
       MAX(salary) - MIN(salary) AS spread
FROM employees
GROUP BY department;

-- Get the row with the MIN value (subquery approach)
SELECT * FROM products
WHERE price = (SELECT MIN(price) FROM products);

-- Or using ORDER BY + LIMIT (often faster):
SELECT * FROM products ORDER BY price ASC LIMIT 1;

SELECT COUNT(*) FROM orders;         -- all rows
SELECT COUNT(email) FROM customers;  -- non-NULL emails only
SELECT COUNT(DISTINCT city) FROM customers;  -- unique cities

-- COUNT with condition (MySQL 8+ style using filter)
SELECT COUNT(*) FILTER (WHERE status='active') AS active_count
FROM users;

-- Traditional approach:
SELECT SUM(status = 'active') AS active_count FROM users;

-- % matches any sequence of characters (including empty)
WHERE name LIKE 'A%'        -- starts with A
WHERE name LIKE '%son'      -- ends with son
WHERE name LIKE '%alice%'   -- contains alice
WHERE email LIKE '%@gmail%' -- gmail users

-- _ matches exactly ONE character
WHERE code LIKE 'A_1'       -- A, any char, 1 (e.g. AB1, AC1)
WHERE phone LIKE '___-___-____'  -- format match
WHERE code LIKE '__-__'     -- XX-XX pattern

-- NOT LIKE
WHERE email NOT LIKE '%@spam.com'

-- Case-sensitive LIKE (use BINARY)
WHERE name LIKE BINARY 'alice%'   -- lowercase only

-- Escape the wildcard characters
WHERE filename LIKE '100%'  ESCAPE ''  -- literal percent sign

-- Matches any string of 0 or more characters
'%'       -- matches everything (any or empty string)
'A%'      -- starts with A: "A", "AB", "Apple", "A123"
'%A'      -- ends with A: "A", "BA", "ABBA"
'%A%'     -- contains A: "A", "BAD", "CABS"
'A%B'     -- starts A, ends B: "AB", "AxxB", "A1234B"

-- IN with literal list
SELECT * FROM products
WHERE category IN ('Electronics', 'Computers', 'Accessories');

-- Equivalent but verbose OR:
WHERE category = 'Electronics'
   OR category = 'Computers'
   OR category = 'Accessories'

-- IN with numbers
SELECT * FROM orders WHERE id IN (1, 5, 8, 22, 100);

-- NOT IN
SELECT * FROM employees
WHERE department NOT IN ('Intern', 'Contractor', 'Temp');

-- IN with subquery (very powerful!)
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'US'
);

-- IN with NULL gotcha!
-- NOT IN fails if the list contains NULL:
WHERE dept NOT IN ('HR', NULL)  -- returns NO rows! NULL poisons NOT IN

-- Numeric range (inclusive on both ends)
WHERE price BETWEEN 100 AND 500
-- Equivalent to: WHERE price >= 100 AND price <= 500

-- Date range
WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31'
WHERE created_at BETWEEN '2026-04-01 00:00:00' AND '2026-04-06 23:59:59'

-- String range (alphabetical)
WHERE last_name BETWEEN 'Adams' AND 'Brown'
-- Includes Adams, Al*, Ba*, Br*, Brown but NOT "Browne"

-- NOT BETWEEN
WHERE price NOT BETWEEN 100 AND 500
-- Equivalent to: WHERE price < 100 OR price > 500

-- Age range
SELECT * FROM users WHERE age BETWEEN 18 AND 65;

-- BETWEEN with NULL
-- BETWEEN never matches NULL values

-- AS keyword (optional but recommended for clarity)
SELECT
    first_name AS name,
    last_name AS surname,
    price * 1.2 AS price_with_tax,
    CONCAT(first_name, ' ', last_name) AS full_name,
    COUNT(*) AS total_orders,
    ROUND(AVG(salary), 2) AS average_salary
FROM employees;

-- Alias with spaces: use backticks or quotes
SELECT price * 1.2 AS `Price With Tax`
FROM products;

-- Can you use alias in WHERE? NO — WHERE runs before SELECT
-- This fails:
WHERE price_with_tax > 100   -- ❌ alias not yet defined

-- Use HAVING for aliases (runs after SELECT):
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING avg_sal > 75000;  -- ✓ alias works in HAVING

SELECT t1.col, t2.col
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.foreign_key;

-- ANSI style (preferred, works everywhere):
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id

-- Old comma syntax (avoid):
FROM orders o, customers c
WHERE o.customer_id = c.id

-- Basic INNER JOIN
SELECT customers.name, orders.total, orders.created_at
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

-- With table aliases (cleaner)
SELECT c.name, c.city, o.total, o.status
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Multiple JOINs (chaining)
SELECT c.name, p.name AS product, oi.quantity, oi.price
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY c.name, o.id;

-- INNER JOIN with aggregation
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING total_spent > 1000
ORDER BY total_spent DESC;

-- All customers, even those without orders
SELECT c.name, o.id AS order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- Find customers with NO orders (anti-join pattern)
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;    -- NULL means no matching order

-- Find products never ordered
SELECT p.name, p.price
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

-- Multiple LEFT JOINs
SELECT c.name, COUNT(o.id) AS orders, COUNT(r.id) AS reviews
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id
GROUP BY c.id, c.name;

-- All orders, even if the customer was deleted
SELECT c.name, o.id, o.total
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- Find orphan orders (orders with deleted customers)
SELECT o.id, o.total, c.name
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL;   -- customer was deleted!

-- CROSS JOIN syntax
SELECT colors.name, sizes.label
FROM colors
CROSS JOIN sizes;

-- If colors has 3 rows (Red, Blue, Green)
-- and sizes has 4 rows (XS, S, M, L)
-- Result: 12 rows (all combinations!)

-- Equivalent (implicit):
SELECT colors.name, sizes.label
FROM colors, sizes;

-- Practical use: generate all possible pairings
SELECT a.team AS home, b.team AS away
FROM teams a
CROSS JOIN teams b
WHERE a.team != b.team;   -- all possible matchups

-- Classic example: employees and their managers
-- (manager_id is a foreign key to the same table's id)
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find employees earning more than their manager
SELECT e.name, e.salary AS emp_salary, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- Find all direct reports for a manager
SELECT e.name AS report, e.dept
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE m.name = 'Alice';

-- UNION: combine two queries, remove duplicates
SELECT name, email FROM current_customers
UNION
SELECT name, email FROM archive_customers;

-- Column names come from the FIRST query
SELECT first_name AS name, email FROM customers_us
UNION
SELECT nombre, correo FROM customers_mx;  -- uses first query's names

-- UNION with ORDER BY (applies to the whole result)
SELECT name FROM employees
UNION
SELECT name FROM contractors
ORDER BY name ASC;

-- UNION with LIMIT
SELECT name FROM vip_customers
UNION
SELECT name FROM regular_customers
ORDER BY name
LIMIT 100;

-- UNION ALL: keep every row
SELECT product_id, quantity FROM warehouse_north
UNION ALL
SELECT product_id, quantity FROM warehouse_south;
-- Shows all records including same product from both warehouses

-- Use UNION ALL when duplicates are meaningful (e.g., transaction logs)
SELECT 'Income' AS type, amount, date FROM income_table
UNION ALL
SELECT 'Expense' AS type, amount, date FROM expense_table
ORDER BY date;

-- UNION ALL is faster than UNION:
-- UNION must sort+compare all rows to find duplicates
-- UNION ALL just concatenates — no overhead

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

-- Revenue by department
SELECT department,
       COUNT(*) AS headcount,
       ROUND(AVG(salary), 0) AS avg_salary,
       SUM(salary) AS total_payroll
FROM employees
GROUP BY department
ORDER BY total_payroll DESC;

-- Group by multiple columns
SELECT YEAR(order_date) AS year, MONTH(order_date) AS month,
       SUM(total) AS monthly_revenue
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

-- GROUP BY with WHERE (filter before grouping)
SELECT dept, AVG(salary) AS avg_sal
FROM employees
WHERE active = 1                   -- filter rows first
GROUP BY dept;                     -- then group

-- Departments with average salary > 75000
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000;

-- Customers who have placed more than 3 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

-- Products that generated > $10,000 in revenue
SELECT p.name, SUM(oi.quantity * oi.price) AS revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
HAVING revenue > 10000
ORDER BY revenue DESC;

-- Using both WHERE and HAVING
SELECT department, ROUND(AVG(salary), 0) AS avg_sal
FROM employees
WHERE active = 1                     -- filter rows first
GROUP BY department
HAVING avg_sal > 70000               -- then filter groups
ORDER BY avg_sal DESC;

-- Customers who have at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- The SELECT 1 (or SELECT *) inside doesn't matter — only presence matters

-- Customers who NEVER ordered (NOT EXISTS)
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- Products with stock in at least one warehouse
SELECT p.name
FROM products p
WHERE EXISTS (
    SELECT 1 FROM inventory i
    WHERE i.product_id = p.id
    AND i.quantity > 0
);

-- EXISTS with complex conditions
SELECT c.name, c.email
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.customer_id = c.id
    AND oi.product_id IN (1, 5, 10)
    AND o.status = 'completed'
    AND o.total > 500
);

-- ANY: true if the condition is true for AT LEAST ONE value
-- Find products more expensive than SOME order total
SELECT name, price
FROM products
WHERE price > ANY (SELECT total FROM orders WHERE status = 'completed');

-- = ANY is the same as IN:
WHERE dept = ANY (SELECT dept FROM managers)
-- same as:
WHERE dept IN (SELECT dept FROM managers)

-- > ANY: greater than the minimum value
WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'Junior')
-- Returns employees earning more than at least one junior

-- Archive old orders to another table
INSERT INTO archive_orders (id, customer_id, total, created_at)
SELECT id, customer_id, total, created_at
FROM orders
WHERE created_at < '2024-01-01';

-- Copy a table structure and data
CREATE TABLE products_backup LIKE products;
INSERT INTO products_backup SELECT * FROM products;

-- Insert with transformation
INSERT INTO customers_report (name, total_orders, total_spent)
SELECT
    c.name,
    COUNT(o.id),
    SUM(o.total)
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Insert from multiple tables
INSERT INTO audit_log (action, user, entity_id, timestamp)
SELECT 'order_created', c.email, o.id, NOW()
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE DATE(o.created_at) = CURDATE();

-- Simple CASE: compare one value against multiple conditions
SELECT name,
       CASE status
           WHEN 'A' THEN 'Active'
           WHEN 'I' THEN 'Inactive'
           WHEN 'D' THEN 'Deleted'
           ELSE 'Unknown'
       END AS status_label
FROM customers;

-- Return alt if expr is NULL, otherwise return expr
SELECT name, IFNULL(phone, 'N/A') AS contact FROM customers;
SELECT IFNULL(SUM(total), 0) AS revenue FROM orders WHERE date = TODAY();

-- Create a stored procedure
DELIMITER $$

CREATE PROCEDURE GetCustomerOrders(IN customer_id INT)
BEGIN
    SELECT o.id, o.total, o.status, o.created_at
    FROM orders o
    WHERE o.customer_id = customer_id
    ORDER BY o.created_at DESC;
END $$

DELIMITER ;

-- Call the procedure
CALL GetCustomerOrders(42);

-- This is a single-line comment (ANSI standard)
SELECT * FROM users; -- inline comment after statement

# This is also a single-line comment (MySQL-specific)
SELECT name # comment after column
FROM users;

-- Best practice: use -- (works in all SQL databases)

+   addition          SELECT 5 + 3       -- 8
-   subtraction       SELECT 10 - 4      -- 6
*   multiplication    SELECT 3 * 4       -- 12
/   division          SELECT 10 / 4      -- 2.5000
DIV integer division  SELECT 10 DIV 4   -- 2
%   modulo            SELECT 10 % 3     -- 1 (MOD is synonym)
MOD modulo synonym    SELECT 10 MOD 3   -- 1

-- Show all databases
SHOW DATABASES;

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

-- Create only if it doesn't exist (safe)
CREATE DATABASE IF NOT EXISTS myapp;

-- Select a database to use
USE myapp;

-- Show current database
SELECT DATABASE();

-- Show database info
SHOW CREATE DATABASE myapp;

-- Drop a database (PERMANENT — no undo!)
DROP DATABASE myapp;

-- Safe version (no error if doesn't exist)
DROP DATABASE IF EXISTS myapp;

-- Shows what you're about to drop:
SHOW CREATE DATABASE myapp;

-- Example workflow:
-- 1. Double-check which database is active
SELECT DATABASE();

-- 2. List tables inside it
SHOW TABLES;

-- 3. Take a backup first!
-- mysqldump -u root -p myapp > backup.sql

-- 4. Then drop
DROP DATABASE myapp;

CREATE TABLE users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    role        ENUM('admin','user','guest') DEFAULT 'user',
    active      BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    price       DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock       INT DEFAULT 0 CHECK (stock >= 0),
    category_id INT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Drop a table (permanent — all data lost!)
DROP TABLE products;

-- Safe version
DROP TABLE IF EXISTS products;

-- Drop multiple tables at once
DROP TABLE IF EXISTS temp_data, import_stage, old_logs;

-- Rename a table (does not drop — safer!)
RENAME TABLE old_name TO new_name;
ALTER TABLE old_name RENAME TO new_name;

-- Drop the data but keep structure (TRUNCATE)
TRUNCATE TABLE session_tokens;  -- faster than DELETE, resets AUTO_INCREMENT

-- Add a column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER email;

-- Drop a column
ALTER TABLE users DROP COLUMN phone;

-- Rename a column (MySQL 8.0+)
ALTER TABLE users RENAME COLUMN username TO login_name;

-- Change column definition (type, size, constraints)
ALTER TABLE users MODIFY COLUMN email VARCHAR(320) NOT NULL;

-- Change column name AND definition
ALTER TABLE users CHANGE COLUMN old_name new_name VARCHAR(100) NOT NULL;

-- Add/remove constraints
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);
ALTER TABLE orders DROP FOREIGN KEY fk_customer;
ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0);

-- Add/remove index
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users DROP INDEX idx_email;

-- Rename the table
ALTER TABLE users RENAME TO customers;

-- Combine multiple changes
ALTER TABLE products
    ADD COLUMN sku VARCHAR(50) UNIQUE,
    ADD COLUMN tags JSON,
    MODIFY COLUMN description TEXT NOT NULL,
    ADD INDEX idx_category (category_id);

NOT NULL     -- value required
UNIQUE       -- no duplicates (NULL allowed unless NOT NULL)
PRIMARY KEY  -- NOT NULL + UNIQUE + row identifier
FOREIGN KEY  -- references another table
CHECK        -- custom expression (MySQL 8.0.16+)
DEFAULT      -- fallback value when none provided

-- Define NOT NULL at column level
CREATE TABLE users (
    id       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    email    VARCHAR(255) NOT NULL,
    phone    VARCHAR(20),         -- NULL allowed (optional)
    bio      TEXT                  -- NULL allowed (optional)
);

-- Add NOT NULL to an existing column (requires all existing rows to have values)
ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2) NOT NULL;

-- Remove NOT NULL (allow NULL)
ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2) NULL;

-- Check for NOT NULL columns in schema
SELECT column_name, is_nullable, data_type
FROM information_schema.columns
WHERE table_name = 'users'
  AND table_schema = 'myapp';

-- Column-level UNIQUE
CREATE TABLE users (
    id    INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50) UNIQUE
);

-- Table-level UNIQUE (can span multiple columns)
CREATE TABLE product_reviews (
    user_id    INT,
    product_id INT,
    rating     TINYINT,
    UNIQUE KEY uq_user_product (user_id, product_id)  -- one review per user per product
);

-- Add UNIQUE constraint to existing table
ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT uq_username UNIQUE (username);

-- Remove UNIQUE constraint
ALTER TABLE users DROP INDEX uq_username;

-- UNIQUE index is automatically created by UNIQUE constraint
SHOW INDEX FROM users;

-- Single column PK
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Named PK (at table level)
CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    CONSTRAINT pk_users PRIMARY KEY (id)
);

-- Composite primary key (no AUTO_INCREMENT with composite)
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Natural key (meaningful data as PK)
CREATE TABLE countries (
    code  CHAR(2) PRIMARY KEY,  -- 'US', 'GB', 'DE'
    name  VARCHAR(100) NOT NULL
);

-- Add PK to existing table
ALTER TABLE users ADD PRIMARY KEY (id);

-- Change PK (drop old, add new)
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (new_id);

CREATE TABLE orders (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    total       DECIMAL(10,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE CASCADE     -- delete orders when customer deleted
        ON UPDATE CASCADE     -- update FK when customer ID changes
);

-- ON DELETE / ON UPDATE actions:
ON DELETE CASCADE    -- delete child rows automatically
ON DELETE SET NULL   -- set FK to NULL (column must be NULLable)
ON DELETE RESTRICT   -- refuse to delete parent if children exist
ON DELETE NO ACTION  -- same as RESTRICT (default)
ON DELETE SET DEFAULT -- set to default value (rarely used)

-- Add FK to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE;

-- Remove FK
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;

-- Show FKs
SELECT * FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'customers';

CREATE TABLE employees (
    id      INT PRIMARY KEY,
    name    VARCHAR(100) NOT NULL,
    age     INT CHECK (age >= 18 AND age <= 65),
    salary  DECIMAL(10,2) CHECK (salary > 0),
    email   VARCHAR(255) CHECK (email LIKE '%@%.%'),
    gender  CHAR(1) CHECK (gender IN ('M','F','N'))
);

-- Named CHECK constraint (recommended)
CREATE TABLE products (
    id       INT PRIMARY KEY,
    price    DECIMAL(10,2),
    discount DECIMAL(5,2),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount >= 0 AND discount <= price),
    CONSTRAINT chk_price_positive CHECK (price - discount >= 0)
);

-- Cross-column CHECK
CREATE TABLE events (
    id         INT PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date   DATE NOT NULL,
    CONSTRAINT chk_dates CHECK (end_date >= start_date)
);

-- Add CHECK to existing table
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

-- Drop CHECK
ALTER TABLE users DROP CHECK chk_age;

CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    status     VARCHAR(20) DEFAULT 'active',          -- literal string
    score      INT DEFAULT 0,                          -- literal number
    role       ENUM('admin','user') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    -- function
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    metadata   JSON DEFAULT (JSON_OBJECT()),           -- expression (MySQL 8.0+)
    uuid       VARCHAR(36) DEFAULT (UUID())            -- generated UUID
);

-- Omitting the column uses the default
INSERT INTO users (name) VALUES ('Alice');
-- status='active', score=0, role='user', created_at=now, etc.

-- Explicitly use DEFAULT keyword
INSERT INTO users (name, status) VALUES ('Bob', DEFAULT);

-- Alter existing column to add default
ALTER TABLE products MODIFY COLUMN stock INT NOT NULL DEFAULT 0;

-- Remove a default
ALTER TABLE products MODIFY COLUMN stock INT NOT NULL;

-- Create index
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_last_name ON customers(last_name);

-- Unique index (enforces uniqueness + speed)
CREATE UNIQUE INDEX idx_username ON users(username);

-- Composite index (multi-column)
CREATE INDEX idx_dept_salary ON employees(department, salary);
-- Useful for: WHERE dept='...' OR WHERE dept='...' AND salary > ...
-- NOT useful for: WHERE salary > ... alone (left-prefix rule!)

-- Full-text index (for MATCH ... AGAINST queries)
CREATE FULLTEXT INDEX idx_content ON articles(title, body);

-- Show indexes on a table
SHOW INDEX FROM users;
SHOW CREATE TABLE users;  -- shows all indexes

-- Drop index
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX idx_email;  -- alternative

-- Force/ignore an index in a query
SELECT * FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 1 ORDER BY created_at;

CREATE TABLE users (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Inserts without specifying id
INSERT INTO users (name) VALUES ('Alice');  -- id = 1
INSERT INTO users (name) VALUES ('Bob');    -- id = 2

-- Get last inserted ID
SELECT LAST_INSERT_ID();  -- returns id of last INSERT in this session

-- Check current auto_increment value
SHOW TABLE STATUS LIKE 'users';
SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_name = 'users';

-- Set custom starting value
ALTER TABLE users AUTO_INCREMENT = 1000;

-- Reset after data load
INSERT INTO users (name) VALUES ('Carol'); -- id = 1000

DATE        '2026-04-06'                  -- date only
TIME        '14:30:00'                    -- time only
DATETIME    '2026-04-06 14:30:00'         -- no timezone
TIMESTAMP   '2026-04-06 14:30:00'         -- auto timezone conversion
YEAR        2026                           -- year only

-- Create a view
CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';

-- Use like a table
SELECT * FROM active_users WHERE name LIKE 'A%';

-- Create or replace existing view
CREATE OR REPLACE VIEW high_value_orders AS
SELECT o.id, c.name AS customer, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.total > 1000
ORDER BY o.total DESC;

-- View with aggregation
CREATE VIEW customer_stats AS
SELECT
    c.id, c.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent,
    MAX(o.created_at) AS last_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- Drop a view
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;

-- Show all views
SHOW FULL TABLES WHERE Table_type = 'VIEW';

// MYSQL production readiness checklist (simulation)
const checklist = [
  { item: "Strict SQL mode enabled", ok: true },
  { item: "utf8mb4 charset/collation configured", ok: true },
  { item: "Backups tested with restore", ok: false },
  { item: "Slow query log reviewed weekly", ok: true },
  { item: "High-impact indexes validated via EXPLAIN", ok: false },
  { item: "Connection pooling configured", ok: true },
];

console.log("MYSQL production checklist:");
checklist.forEach((c, i) => console.log((i + 1) + '. ' + (c.ok ? 'PASS' : 'FAIL') + ' - ' + c.item));

const pass = checklist.filter(c => c.ok).length;
const pct = Math.round((pass / checklist.length) * 100);
console.log('\nReadiness: ' + pct + '%');
if (pct < 100) console.log("Action: close FAIL items before launch.");

EXPLAIN ANALYZE
SELECT o.customer_id, SUM(o.total) AS revenue
FROM orders o
WHERE o.status = 'paid'
  AND o.created_at >= '2026-01-01'
GROUP BY o.customer_id
ORDER BY revenue DESC
LIMIT 10;