DDL Commands Overview

Data Definition Language (DDL) commands are used to define and modify database structures. These are the building blocks of your database architecture! 🏗️

Creating Databases

-- Basic database creation
CREATE DATABASE my_app;

-- With character set and collation
CREATE DATABASE my_app
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Check if database exists (safe creation)
CREATE DATABASE IF NOT EXISTS my_app
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Show all databases
SHOW DATABASES;

-- Show database creation statement
SHOW CREATE DATABASE my_app;

-- Use the database
USE my_app;

-- Show current database
SELECT DATABASE();

-- Alter database settings
ALTER DATABASE my_app
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

-- Drop database (DANGEROUS!)
DROP DATABASE my_app;

-- Safe drop
DROP DATABASE IF EXISTS my_app;

-- Database information
SELECT 
    SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'my_app';

Creating Tables

-- Basic table creation
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Comprehensive table with all features
CREATE TABLE products (
    -- Primary key
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- Basic columns
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    
    -- Foreign key reference
    category_id INT UNSIGNED,
    brand_id INT UNSIGNED,
    
    -- Numeric fields
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    cost DECIMAL(10,2),
    stock_quantity INT UNSIGNED DEFAULT 0,
    
    -- Status fields
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    is_featured BOOLEAN DEFAULT FALSE,
    
    -- JSON field for flexible data
    specifications JSON,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    -- Constraints
    CONSTRAINT fk_category
        FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
        
    CONSTRAINT fk_brand
        FOREIGN KEY (brand_id) REFERENCES brands(brand_id)
        ON DELETE SET NULL,
        
    CONSTRAINT chk_price
        CHECK (price >= 0),
        
    CONSTRAINT chk_stock
        CHECK (stock_quantity >= 0),
        
    -- Unique constraints
    UNIQUE KEY unique_sku (sku),
    
    -- Indexes for performance
    INDEX idx_category (category_id),
    INDEX idx_brand (brand_id),
    INDEX idx_status (status),
    INDEX idx_featured (is_featured),
    INDEX idx_price (price),
    FULLTEXT ft_search (name, description)
    
) ENGINE=InnoDB 
  AUTO_INCREMENT=1000 
  DEFAULT CHARSET=utf8mb4 
  COLLATE=utf8mb4_unicode_ci
  COMMENT='Product catalog table';

-- Create table from SELECT (copy structure and data)
CREATE TABLE users_backup AS
SELECT * FROM users;

-- Create table with same structure (no data)
CREATE TABLE users_archive LIKE users;

-- Temporary table (exists only for session)
CREATE TEMPORARY TABLE temp_calculations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value DECIMAL(10,2),
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Partitioned table (for large datasets)
CREATE TABLE logs (
    log_id BIGINT AUTO_INCREMENT,
    log_date DATE NOT NULL,
    message TEXT,
    PRIMARY KEY (log_id, log_date)
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

ALTER TABLE Operations

-- ========================================
-- ADDING COLUMNS
-- ========================================

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

-- Add column with position
ALTER TABLE users 
ADD COLUMN middle_name VARCHAR(50) AFTER first_name;

-- Add column at beginning
ALTER TABLE users 
ADD COLUMN row_id BIGINT FIRST;

-- Add multiple columns
ALTER TABLE users 
ADD COLUMN address TEXT,
ADD COLUMN city VARCHAR(100),
ADD COLUMN postal_code VARCHAR(10);

-- Add column with constraints
ALTER TABLE products
ADD COLUMN discount_percent DECIMAL(5,2) DEFAULT 0 
    CHECK (discount_percent BETWEEN 0 AND 100);

-- ========================================
-- MODIFYING COLUMNS
-- ========================================

-- Change data type
ALTER TABLE users 
MODIFY COLUMN email VARCHAR(320);  -- Increase size

-- Change column name and type
ALTER TABLE users 
CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;

-- Add/Remove NOT NULL
ALTER TABLE users 
MODIFY COLUMN phone VARCHAR(20) NOT NULL;

ALTER TABLE users 
MODIFY COLUMN phone VARCHAR(20) NULL;

-- Set default value
ALTER TABLE products 
ALTER COLUMN status SET DEFAULT 'active';

-- Remove default value
ALTER TABLE products 
ALTER COLUMN status DROP DEFAULT;

-- Change column position
ALTER TABLE users 
MODIFY COLUMN email VARCHAR(255) AFTER username;

-- ========================================
-- DROPPING COLUMNS
-- ========================================

-- Drop single column
ALTER TABLE users 
DROP COLUMN middle_name;

-- Drop multiple columns
ALTER TABLE users 
DROP COLUMN address,
DROP COLUMN city,
DROP COLUMN postal_code;

-- ========================================
-- CONSTRAINT OPERATIONS
-- ========================================

-- Add primary key
ALTER TABLE orders 
ADD PRIMARY KEY (order_id);

-- Drop primary key
ALTER TABLE orders 
DROP PRIMARY KEY;

-- Add foreign key
ALTER TABLE orders 
ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE CASCADE;

-- Drop foreign key
ALTER TABLE orders 
DROP FOREIGN KEY fk_customer;

-- Add unique constraint
ALTER TABLE users 
ADD UNIQUE KEY unique_email (email);

-- Drop unique constraint
ALTER TABLE users 
DROP INDEX unique_email;

-- Add check constraint (MySQL 8.0+)
ALTER TABLE products 
ADD CONSTRAINT chk_price_positive 
    CHECK (price > 0);

-- Drop check constraint
ALTER TABLE products 
DROP CONSTRAINT chk_price_positive;

-- ========================================
-- INDEX OPERATIONS
-- ========================================

-- Add index
ALTER TABLE users 
ADD INDEX idx_created (created_at);

-- Add composite index
ALTER TABLE orders 
ADD INDEX idx_customer_date (customer_id, order_date);

-- Add fulltext index
ALTER TABLE articles 
ADD FULLTEXT ft_content (title, body);

-- Drop index
ALTER TABLE users 
DROP INDEX idx_created;

-- ========================================
-- TABLE OPTIONS
-- ========================================

-- Change storage engine
ALTER TABLE users ENGINE = InnoDB;

-- Change character set
ALTER TABLE users 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Change auto_increment value
ALTER TABLE users AUTO_INCREMENT = 10000;

-- Add table comment
ALTER TABLE users 
COMMENT = 'Main user accounts table';

-- Rename table
ALTER TABLE users RENAME TO user_accounts;
-- Or
RENAME TABLE users TO user_accounts;

-- ========================================
-- COMPLEX ALTER OPERATIONS
-- ========================================

-- Multiple operations in one statement
ALTER TABLE products
    ADD COLUMN supplier_id INT UNSIGNED,
    ADD CONSTRAINT fk_supplier 
        FOREIGN KEY (supplier_id) 
        REFERENCES suppliers(supplier_id),
    ADD INDEX idx_supplier (supplier_id),
    MODIFY COLUMN description MEDIUMTEXT,
    DROP COLUMN old_field,
    ENGINE = InnoDB,
    AUTO_INCREMENT = 5000;

-- Online DDL (minimize locking)
ALTER TABLE large_table 
ADD COLUMN new_field VARCHAR(100), 
ALGORITHM=INPLACE, 
LOCK=NONE;

Managing Indexes

-- ========================================
-- CREATING INDEXES
-- ========================================

-- Simple index
CREATE INDEX idx_email ON users(email);

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

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

-- Fulltext index (for text searching)
CREATE FULLTEXT INDEX ft_search 
ON articles(title, content);

-- Prefix index (index part of column)
CREATE INDEX idx_email_prefix ON users(email(50));

-- Descending index (MySQL 8.0+)
CREATE INDEX idx_created_desc ON posts(created_at DESC);

-- Functional index (MySQL 8.0+)
CREATE INDEX idx_month 
ON orders((MONTH(order_date)));

-- ========================================
-- VIEWING INDEXES
-- ========================================

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

-- Detailed index information
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    CARDINALITY,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'my_database' 
    AND TABLE_NAME = 'users';

-- ========================================
-- DROPPING INDEXES
-- ========================================

-- Drop index by name
DROP INDEX idx_email ON users;

-- Drop primary key
ALTER TABLE users DROP PRIMARY KEY;

-- Drop foreign key (first drop the constraint)
ALTER TABLE orders DROP FOREIGN KEY fk_customer;
ALTER TABLE orders DROP INDEX fk_customer;

-- ========================================
-- INDEX HINTS (force/ignore index)
-- ========================================

-- Force use of specific index
SELECT * FROM users 
USE INDEX (idx_created)
WHERE created_at > '2025-01-01';

-- Ignore specific index
SELECT * FROM users 
IGNORE INDEX (idx_email)
WHERE email LIKE '%@gmail.com';

-- Force index for ORDER BY
SELECT * FROM users 
FORCE INDEX FOR ORDER BY (idx_name)
ORDER BY last_name, first_name;

-- ========================================
-- ANALYZING INDEX USAGE
-- ========================================

-- Check if query uses index
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Detailed execution plan
EXPLAIN FORMAT=JSON 
SELECT u.*, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;

-- Update index statistics
ANALYZE TABLE users;

-- ========================================
-- INDEX BEST PRACTICES
-- ========================================

-- Good composite index (leftmost prefix)
CREATE INDEX idx_covering 
ON orders(customer_id, order_date, status);
-- Can be used for:
-- WHERE customer_id = ?
-- WHERE customer_id = ? AND order_date = ?
-- WHERE customer_id = ? AND order_date = ? AND status = ?

-- Bad: low cardinality columns
-- Don't index boolean or status with few values
-- CREATE INDEX idx_bad ON users(is_active); -- Bad!

-- Good: high cardinality columns
CREATE INDEX idx_good ON users(email); -- Good!

-- Covering index (includes all needed columns)
CREATE INDEX idx_covering_query
ON orders(customer_id, order_date, total_amount)
WHERE status = 'completed'; -- Filtered index (MySQL 8.0+)

Creating and Managing Views

-- ========================================
-- CREATING VIEWS
-- ========================================

-- Simple view
CREATE VIEW active_users AS
SELECT user_id, username, email, created_at
FROM users
WHERE status = 'active';

-- View with joins
CREATE VIEW order_summary AS
SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    c.email,
    SUM(oi.quantity * oi.unit_price) as total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

-- View with subquery
CREATE VIEW top_customers AS
SELECT 
    c.*,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) as order_count,
    (SELECT SUM(total_amount) FROM orders WHERE customer_id = c.customer_id) as total_spent
FROM customers c
HAVING order_count > 5;

-- Updatable view (can INSERT/UPDATE through it)
CREATE VIEW user_profiles AS
SELECT user_id, username, email, bio
FROM users
WITH CHECK OPTION;  -- Ensures updates meet view criteria

-- Replace existing view
CREATE OR REPLACE VIEW active_users AS
SELECT user_id, username, email, last_login
FROM users
WHERE status = 'active' 
    AND last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);

-- ========================================
-- USING VIEWS
-- ========================================

-- Query a view like a table
SELECT * FROM active_users;

-- Join views with tables
SELECT au.username, COUNT(o.order_id) as orders
FROM active_users au
LEFT JOIN orders o ON au.user_id = o.user_id
GROUP BY au.user_id;

-- Update through updatable view
UPDATE user_profiles 
SET bio = 'Updated bio' 
WHERE user_id = 1;

-- ========================================
-- MANAGING VIEWS
-- ========================================

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

-- Show view definition
SHOW CREATE VIEW active_users;

-- View information from information schema
SELECT 
    TABLE_NAME,
    VIEW_DEFINITION,
    IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'my_database';

-- Alter view
ALTER VIEW active_users AS
SELECT user_id, username, email
FROM users
WHERE status = 'active';

-- Drop view
DROP VIEW IF EXISTS active_users;

-- ========================================
-- ADVANCED VIEW CONCEPTS
-- ========================================

-- Cascaded views (view based on another view)
CREATE VIEW recent_orders AS
SELECT * FROM order_summary
WHERE order_date > DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Security with views (hide sensitive columns)
CREATE VIEW public_user_info AS
SELECT 
    user_id,
    username,
    CONCAT(LEFT(email, 3), '***@***') as masked_email,
    created_at
FROM users;

-- Performance view (pre-calculated aggregates)
CREATE VIEW monthly_sales AS
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') as month,
    COUNT(DISTINCT order_id) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

DROP Operations

-- ========================================
-- DROP OPERATIONS
-- ========================================

-- Drop database (deletes everything!)
DROP DATABASE my_database;

-- Safe drop with IF EXISTS
DROP DATABASE IF EXISTS my_database;

-- Drop table
DROP TABLE users;

-- Safe drop
DROP TABLE IF EXISTS users;

-- Drop multiple tables
DROP TABLE IF EXISTS 
    temp_table1, 
    temp_table2, 
    temp_table3;

-- Drop with CASCADE (if supported)
-- Drops dependent objects
DROP TABLE users CASCADE;  -- Not in MySQL, but in PostgreSQL

-- Drop view
DROP VIEW IF EXISTS user_summary;

-- Drop index
DROP INDEX idx_email ON users;

-- Drop stored procedure
DROP PROCEDURE IF EXISTS calculate_totals;

-- Drop function
DROP FUNCTION IF EXISTS get_user_age;

-- Drop trigger
DROP TRIGGER IF EXISTS before_user_update;

-- ========================================
-- TRUNCATE vs DROP vs DELETE
-- ========================================

-- DELETE: Removes rows, can be rolled back, triggers fire
DELETE FROM users WHERE status = 'inactive';

-- TRUNCATE: Removes all rows, cannot rollback, faster, resets AUTO_INCREMENT
TRUNCATE TABLE user_logs;

-- DROP: Removes entire table structure and data
DROP TABLE user_logs;

-- Comparison example
-- If you want to remove all data but keep structure:
TRUNCATE TABLE large_table;  -- Fast, resets AUTO_INCREMENT

-- If you want to remove all data with conditions:
DELETE FROM large_table WHERE created_at < '2024-01-01';  -- Slower, logs each row

-- If you want to remove everything:
DROP TABLE large_table;  -- Removes structure and data

-- ========================================
-- SAFETY PROCEDURES
-- ========================================

-- Always check before dropping
SELECT COUNT(*) FROM users;  -- Check data
SHOW CREATE TABLE users;     -- Save structure

-- Create backup before dropping
CREATE TABLE users_backup AS SELECT * FROM users;

-- Or use mysqldump from command line
-- mysqldump -u root -p database_name users > users_backup.sql

-- Rename instead of drop (safer)
RENAME TABLE users TO users_archived_20250115;

-- Disable foreign key checks if needed
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE parent_table;
SET FOREIGN_KEY_CHECKS = 1;

Practice Exercise

-- 1. Create database
CREATE DATABASE IF NOT EXISTS blog_system
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE blog_system;

-- 2. Create users table
CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash CHAR(60) NOT NULL,
    display_name VARCHAR(100),
    bio TEXT,
    avatar_url VARCHAR(500),
    role ENUM('admin', 'editor', 'author', 'subscriber') DEFAULT 'subscriber',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_role (role),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Create categories table
CREATE TABLE categories (
    category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id INT UNSIGNED NULL,
    
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
        ON DELETE CASCADE,
    INDEX idx_parent (parent_id),
    INDEX idx_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Create posts table
CREATE TABLE posts (
    post_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    excerpt TEXT,
    content MEDIUMTEXT,
    featured_image VARCHAR(500),
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    view_count INT UNSIGNED DEFAULT 0,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (author_id) REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON DELETE SET NULL,
    
    INDEX idx_author (author_id),
    INDEX idx_category (category_id),
    INDEX idx_status (status),
    INDEX idx_published (published_at),
    FULLTEXT ft_search (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Create comments table
CREATE TABLE comments (
    comment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED,
    parent_comment_id INT UNSIGNED,
    author_name VARCHAR(100),
    author_email VARCHAR(255),
    content TEXT NOT NULL,
    status ENUM('pending', 'approved', 'spam') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE SET NULL,
    FOREIGN KEY (parent_comment_id) REFERENCES comments(comment_id)
        ON DELETE CASCADE,
    
    INDEX idx_post (post_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_parent (parent_comment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Create tags table (many-to-many with posts)
CREATE TABLE tags (
    tag_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Create post_tags junction table
CREATE TABLE post_tags (
    post_id INT UNSIGNED,
    tag_id INT UNSIGNED,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Create view for published posts
CREATE VIEW published_posts AS
SELECT 
    p.post_id,
    p.title,
    p.slug,
    p.excerpt,
    p.content,
    p.featured_image,
    p.view_count,
    p.published_at,
    u.display_name AS author_name,
    u.avatar_url AS author_avatar,
    c.name AS category_name,
    c.slug AS category_slug
FROM posts p
JOIN users u ON p.author_id = u.user_id
LEFT JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'published'
    AND p.published_at <= NOW()
ORDER BY p.published_at DESC;

-- 9. Alter table to add new features
ALTER TABLE posts
    ADD COLUMN meta_description VARCHAR(160),
    ADD COLUMN meta_keywords VARCHAR(255),
    ADD COLUMN allow_comments BOOLEAN DEFAULT TRUE,
    ADD COLUMN is_featured BOOLEAN DEFAULT FALSE,
    ADD INDEX idx_featured (is_featured);

ALTER TABLE users
    ADD COLUMN last_login TIMESTAMP NULL,
    ADD COLUMN login_count INT UNSIGNED DEFAULT 0;

-- 10. Create a performance monitoring index
CREATE INDEX idx_performance 
ON posts(status, published_at, view_count);