SQL serves as the universal language for interacting with relational database systems. While implementations vary across vendors, MySQL adheres closely to the ANSI/ISO SQL standard while extending it with practical enhancements for performance, usability, and developer productivity.

Core SQL Language Categories

SQL operations are logically grouped into four functional families:

• Data Definition Language (DDL): Manages schema structure — databases, tables, indexes, views, and constraints. Key statements include CREATE, ALTER, and DROP.
• Data Manipulation Language (DML): Handles data operations — insertion, modification, and retrieval. Primary commands are INSERT, UPDATE, DELETE, and SELECT.
• Data Control Language (DCL): Governs access permissions via GRANT and REVOKE, enabling fine-grained security policies.
• Transaction Control: Ensures data consistency using BEGIN, COMMIT, and ROLLBACK.

Database and Schema Management

To initialize a new database with UTF-8 support and case-insensitive collation:

CREATE DATABASE IF NOT EXISTS analytics_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

Switch context to that database:

USE analytics_db;

Delete it safely only if it exists:

DROP DATABASE IF EXISTS analytics_db;

Table Operations and Constraints

Create a structured table enforcing entity integrity:

CREATE TABLE IF NOT EXISTS users (
  user_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(120),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
  CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$')
) ENGINE = InnoDB;

Add an index on frequently filtered columns:

CREATE INDEX idx_user_status_email ON users(status, email);

Modify column definitions without dropping the table:

ALTER TABLE users 
  MODIFY COLUMN full_name VARCHAR(150) NOT NULL,
  ADD COLUMN last_login TIMESTAMP NULL AFTER created_at;

Indexing Strategy and Performance Analysis

MySQL supports multiple index types optimized for distinct workloads:

• B+Tree: Default for INDEX, UNIQUE, PRIMARY KEY; efficient for range scans and sorting.
• Full-text: Accelerates natural-language text searches using MATCH ... AGAINST.
• Spatial: Enables geospatial queries with POINT, POLYGON, and related functions.

Use EXPLAIN to inspect query execution plans. For example:

EXPLAIN FORMAT=TRADITIONAL
SELECT u.full_name, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.customer_id
WHERE u.status = 'active'
GROUP BY u.user_id, u.full_name
ORDER BY order_count DESC
LIMIT 10;

The type column reveals access efficiency: const and eq_ref indicate optimal index usage; ALL signals a full-table scan — a common performance red flag.

Advanced Query Techniques

Leverage window functions for analytical calculations without self-joins:

SELECT 
  product_id,
  sale_date,
  revenue,
  AVG(revenue) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg_7d,
  RANK() OVER (PARTITION BY YEAR(sale_date) ORDER BY revenue DESC) AS annual_rank
FROM sales;

Generate time-series data using recursive CTEs (MySQL 8.0+):

WITH RECURSIVE date_range AS (
  SELECT '2024-01-01' AS dt
  UNION ALL
  SELECT DATE_ADD(dt, INTERVAL 1 DAY)
  FROM date_range
  WHERE dt < '2024-01-31'
)
SELECT dt, DAYOFWEEK(dt) AS weekday FROM date_range;

Perform conditional aggregation with CASE:

SELECT 
  COUNT(*) AS total_customers,
  COUNT(CASE WHEN signup_source = 'referral' THEN 1 END) AS referred,
  COUNT(CASE WHEN signup_source = 'ads' THEN 1 END) AS from_ads,
  ROUND(
    100.0 * COUNT(CASE WHEN status = 'premium' THEN 1 END) / COUNT(*), 2
  ) AS premium_pct
FROM customers;

Stored Procedures and Functions

Encapsulate reusable logic in server-side routines. A procedure that archives stale records:

DELIMITER $$
CREATE PROCEDURE archive_old_logs(IN cutoff_days INT)
BEGIN
  DECLARE archived_rows INT DEFAULT 0;
  
  START TRANSACTION;
  
  INSERT INTO logs_archive 
    SELECT * FROM application_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL cutoff_days DAY);
  
  GET DIAGNOSTICS archived_rows = ROW_COUNT;
  
  DELETE FROM application_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL cutoff_days DAY);
  
  COMMIT;
  
  SELECT CONCAT('Archived ', archived_rows, ' rows.') AS result;
END$$
DELIMITER ;

A deterministic scalar function for formatting monetary values:

DELIMITER $$
CREATE FUNCTION format_currency(amount DECIMAL(12,2))
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
COMMENT 'Formats decimal as USD with commas and two decimals'
BEGIN
  RETURN CONCAT('$', FORMAT(amount, 2));
END$$
DELIMITER ;

-- Usage:
SELECT user_id, format_currency(total_spent) FROM customers;

Concurrency and Transaction Safety

MySQL defaults to REPEATABLE READ isolation, preventing dirty reads and non-repeatable reads. Explicit transaction control ensures atomicity:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;

-- Verify consistency before committing
SELECT SUM(balance) FROM accounts WHERE account_id IN (123, 456);

COMMIT;

For high-contention scenarios, use SELECT ... FOR UPDATE to lock rows during business logic evaluation.

Data Integrity Enforcement

Define referential integrity using foreign keys with cascading actions:

ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order_id
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
  ON DELETE CASCADE
  ON UPDATE RESTRICT;

Implement soft deletes with triggers:

DELIMITER $$
CREATE TRIGGER tr_soft_delete_customer
  BEFORE DELETE ON customers
  FOR EACH ROW
BEGIN
  UPDATE customers 
  SET deleted_at = NOW(), status = 'deleted' 
  WHERE customer_id = OLD.customer_id;
  
  SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Soft delete applied instead of hard delete';
END$$
DELIMITER ;