MySQL — Queries & Schema

Schema Definition

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

-- Create table
CREATE TABLE users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL,
  password    VARCHAR(255) NOT NULL,
  role        ENUM('user', 'admin', 'mod') NOT NULL DEFAULT 'user',
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  bio         TEXT,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role_active (role, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Foreign key
CREATE TABLE posts (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(255) NOT NULL,
  slug        VARCHAR(255) NOT NULL UNIQUE,
  body        LONGTEXT NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  status      ENUM('draft', 'published') DEFAULT 'draft',
  published_at DATETIME,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_id (user_id),
  INDEX idx_status_published (status, published_at),
  FULLTEXT idx_ft_search (title, body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Alter table
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);
ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users DROP INDEX idx_name;
ALTER TABLE users RENAME TO app_users;

Queries

-- INSERT
INSERT INTO users (email, name, password) VALUES ('a@b.com', 'Alice', 'hashed');
INSERT INTO users (email, name, password) VALUES
  ('b@b.com', 'Bob', 'hash1'),
  ('c@b.com', 'Carol', 'hash2');

-- INSERT ... ON DUPLICATE KEY UPDATE (upsert)
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice New')
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();

-- REPLACE INTO (deletes + inserts if duplicate key exists)
REPLACE INTO settings (user_id, key, value) VALUES (1, 'theme', 'dark');

-- SELECT
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id AND p.status = 'published'
WHERE u.is_active = 1
GROUP BY u.id
HAVING post_count > 0
ORDER BY post_count DESC
LIMIT 10 OFFSET 20;

-- Subquery
SELECT * FROM users WHERE id IN (
  SELECT DISTINCT user_id FROM posts WHERE status = 'published'
);

-- UPDATE
UPDATE users SET is_active = 0 WHERE last_login_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- DELETE
DELETE FROM posts WHERE user_id = 5 AND status = 'draft';

-- Truncate (fast delete all, resets auto_increment)
TRUNCATE TABLE sessions;

Indexes & Performance

-- EXPLAIN query plan
EXPLAIN SELECT u.name FROM users u JOIN posts p ON p.user_id = u.id WHERE u.is_active = 1;
EXPLAIN FORMAT=JSON ...;    -- JSON format for more detail

-- Key columns in EXPLAIN output:
-- type:   ALL (bad) | range | ref | eq_ref | const (good)
-- key:    which index is used (NULL = none)
-- rows:   estimated rows examined
-- Extra:  Using index (covering), Using filesort, Using temporary (bad)

-- Index types in MySQL
CREATE INDEX idx_email ON users(email);                 -- B-tree (default)
CREATE UNIQUE INDEX idx_email_unique ON users(email);
CREATE INDEX idx_created ON posts(created_at DESC);     -- descending (MySQL 8+)
CREATE FULLTEXT INDEX idx_search ON posts(title, body);
CREATE INDEX idx_composite ON posts(user_id, status, created_at); -- composite

-- Full-text search
SELECT title, MATCH(title, body) AGAINST ('mysql performance' IN NATURAL LANGUAGE MODE) AS score
FROM posts
WHERE MATCH(title, body) AGAINST ('mysql performance' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- Boolean mode FTS
SELECT * FROM posts
WHERE MATCH(title, body) AGAINST ('+mysql -oracle' IN BOOLEAN MODE);

Useful Functions & Tips

-- String
CONCAT(first_name, ' ', last_name)
GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ')   -- aggregate into string
JSON_OBJECT('id', id, 'name', name)            -- build JSON
JSON_ARRAYAGG(name)                            -- aggregate to JSON array

-- Date
NOW(), CURRENT_TIMESTAMP, CURDATE(), CURTIME()
DATE_FORMAT(created_at, '%Y-%m-%d')
DATE_ADD(NOW(), INTERVAL 7 DAY)
DATE_SUB(NOW(), INTERVAL 1 MONTH)
DATEDIFF(end_date, start_date)
TIMESTAMPDIFF(HOUR, created_at, updated_at)

-- Conditional
IF(is_active, 'active', 'inactive')
IFNULL(phone, 'N/A')
COALESCE(nickname, first_name, 'Anonymous')
CASE WHEN salary > 100000 THEN 'senior' WHEN salary > 60000 THEN 'mid' ELSE 'junior' END

-- Window functions (MySQL 8.0+)
SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
FROM employees;

ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(4)
LAG(salary, 1) OVER (ORDER BY hire_date)
SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

-- Transactions
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK;