Databases Cheatsheet

• MySQL, PostgreSQL, SQLite
• MongoDB, Redis, Cassandra
• Neo4j, Firebase
• GFG DBMS, TutorialsPoint DBMS

-- SELECT
SELECT * FROM users;
SELECT name, age FROM users WHERE age > 18 ORDER BY age DESC;

-- INSERT
INSERT INTO users (name, age) VALUES ('Alice', 25);

-- UPDATE
UPDATE users SET age = 26 WHERE name = 'Alice';

-- DELETE
DELETE FROM users WHERE age < 18;

-- JOINs
SELECT u.name, o.amount FROM users u
JOIN orders o ON u.id = o.user_id;

-- GROUP BY
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- Subquery
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- Create Index
CREATE INDEX idx_name ON users(name);
-- Unique Key
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE(email);
-- Foreign Key
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id);

Denormalization: combining tables for performance, at the cost of redundancy.

• Atomicity: All or nothing
• Consistency: Valid state only
• Isolation: No interference
• Durability: Survives crash

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

// MongoDB Insert
{
  name: "Alice",
  age: 25,
  skills: ["SQL", "NoSQL"]
}
// Redis Set
SET user:1:name "Alice"
// Cassandra Table
CREATE TABLE users (id UUID PRIMARY KEY, name TEXT);

• Use indexes for frequent queries
• Analyze query plans (EXPLAIN)
• Cache results (Redis, Memcached)
• Partition/shard large tables
• Replicate for high availability
• Optimize schema for access patterns

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';

• Regular backups (mysqldump, pg_dump, mongodump)
• Test restore procedures
• Automate backup schedules
• Store backups offsite/cloud
• Point-in-time recovery (WAL, binlog)

# MySQL
mysqldump -u user -p dbname > backup.sql
# PostgreSQL
pg_dump dbname > backup.sql
# MongoDB
mongodump --db dbname --out /backup/dir

• Use strong passwords, roles, and permissions
• Limit network access (firewall, VPN)
• Encrypt data at rest and in transit
• Sanitize inputs to prevent SQL injection
• Regularly update and patch DBMS

-- Create user with limited privileges
CREATE USER 'readonly'@'%' IDENTIFIED BY 'password';
GRANT SELECT ON dbname.* TO 'readonly'@'%';
-- Prevent SQL injection
SELECT * FROM users WHERE email = ?;

• Design schema for scalability and clarity
• Use proper data types and constraints
• Document schema and relationships
• Monitor performance and errors
• Automate backups and maintenance
• Test disaster recovery regularly