Query, design, migrate, and optimize SQL databases. Use when working with SQLite, PostgreSQL, or MySQL — schema design, writing queries, creating migrations, indexing, backup/restore, and debugging slow queries.
Work with relational databases directly from the command line. Covers SQLite, PostgreSQL, and MySQL with patterns for schema design, querying, migrations, indexing, and operations.
mysql -h localhost -u root -p mydb
mysql -h localhost -u root -p -e "SELECT NOW();" mydb
-- Auto-increment table
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- JSON type (MySQL 5.7+)
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
metadata JSON,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Query JSON
SELECT * FROM orders WHERE JSON_EXTRACT(metadata, '$.source') = 'web';
SELECT * FROM orders WHERE metadata->>'$.source' = 'web';
-- Inner join
SELECT u.name, o.total, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.created_at > '2026-01-01';
-- Left join with count
SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
-- Running total
SELECT date, revenue,
SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_sales;
-- Rank within groups
SELECT user_id, total,
RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank
FROM orders;
-- Moving average (last 7 entries)
SELECT date, revenue,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_sales;
-- Monthly revenue with growth %
WITH monthly_revenue AS (
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
SUM(total) AS revenue
FROM orders WHERE status = 'paid'
GROUP BY 1
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly_revenue ORDER BY month;
-- Recursive (org chart / tree)
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.depth + 1
FROM employees e JOIN org_tree t ON e.manager_id = t.id
)
SELECT REPEAT(' ', depth), name FROM org_tree ORDER BY depth, name;
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
What to look for:
ALL in type column (full table scan) → needs indexrows being high → index doesn't cover the filterUsing filesort → consider index on ORDER BY columnsUsing temporary → GROUP BY without index-- Single column
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Composite (equality filters first, range filters last)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Covering index (avoids table lookup)
CREATE INDEX idx_orders_covering ON orders(user_id, status, total, created_at);
-- Partial index via filtered query (MySQL doesn't have native partial, use generated col)
ALTER TABLE orders ADD COLUMN is_pending TINYINT GENERATED ALWAYS AS (IF(status='pending',1,NULL));
CREATE INDEX idx_orders_pending ON orders(is_pending, user_id);
-- Check unused indexes
SELECT s.table_name, s.index_name, s.rows_selected
FROM information_schema.statistics s
LEFT JOIN sys.schema_unused_indexes u
ON s.table_schema = u.object_schema AND s.table_name = u.object_name AND s.index_name = u.index_name
WHERE s.table_schema = 'mydb'
ORDER BY s.rows_selected;
# Full dump
mysqldump -h localhost -u root -p mydb > backup.sql
# Specific tables
mysqldump -h localhost -u root -p mydb users orders > partial.sql
# Compressed
mysqldump -h localhost -u root -p mydb | gzip > backup.sql.gz
# Restore
mysql -h localhost -u root -p mydb < backup.sql
gunzip -c backup.sql.gz | mysql -h localhost -u root -p mydb
# Export to CSV (from MySQL)
mysql -h localhost -u root -p mydb -e "SELECT * FROM users" | sed 's/\t/,/g' > users.csv
# One-liner query
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users WHERE created_at > '2026-01-01';"
# Import CSV
sqlite3 mydb.sqlite ".mode csv" ".import data.csv mytable" "SELECT COUNT(*) FROM mytable;"
# Export to CSV
sqlite3 -header -csv mydb.sqlite "SELECT * FROM orders;" > orders.csv
# Interactive mode
sqlite3 -header -column mydb.sqlite
# Backup
sqlite3 mydb.sqlite ".backup backup.sqlite"
EXPLAIN before deploying any query that runs on large tablesANALYZE tablename; if EXPLAIN estimates are way off from realitysqlite3 :memory: ".mode csv" ".import file.csv t" "SELECT * FROM t LIMIT 5;"