Databases Complete Guide

SQL-- Create a database CREATE DATABASE ecommerce; USE ecommerce; -- Create a table CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(150) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, product VARCHAR(200), amount DECIMAL(10,2), status ENUM("pending", "paid", "shipped", "delivered"), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) );

SQL-- CREATE (Insert data) INSERT INTO users (name, email) VALUES ("Suresh", "suresh@example.com"); INSERT INTO orders (user_id, product, amount, status) VALUES (1, "Docker eBook", 499.00, "paid"); -- READ (Query data) SELECT * FROM users; -- All users SELECT name, email FROM users WHERE id = 1; -- Specific user SELECT * FROM orders WHERE status = "pending"; -- Pending orders SELECT COUNT(*) FROM orders; -- Count orders SELECT status, COUNT(*) FROM orders GROUP BY status; -- Count per status -- UPDATE (Modify data) UPDATE orders SET status = "shipped" WHERE id = 1; UPDATE users SET email = "new@email.com" WHERE id = 1; -- DELETE (Remove data) DELETE FROM orders WHERE id = 1; DELETE FROM users WHERE created_at < "2023-01-01"; -- Old users

SQL-- INNER JOIN — only matching rows from both tables SELECT users.name, orders.product, orders.amount FROM users INNER JOIN orders ON users.id = orders.user_id; -- Shows: Suresh | Docker eBook | 499.00 -- LEFT JOIN — all rows from left table, matching from right SELECT users.name, COUNT(orders.id) as order_count FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id; -- Shows ALL users, even those with 0 orders -- Real-world: Find users who spent more than 1000 SELECT users.name, SUM(orders.amount) as total_spent FROM users INNER JOIN orders ON users.id = orders.user_id GROUP BY users.id HAVING total_spent > 1000 ORDER BY total_spent DESC;

SQL-- Create an index CREATE INDEX idx_user_email ON users(email); CREATE INDEX idx_order_status ON orders(status); CREATE INDEX idx_order_date ON orders(created_at); -- Composite index (multiple columns) CREATE INDEX idx_order_user_status ON orders(user_id, status); -- Check query performance EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = "pending"; -- Shows whether the query uses an index or does a full table scan -- Show all indexes on a table SHOW INDEX FROM orders;

TERMINAL# MySQL Backup (mysqldump) mysqldump -u root -p ecommerce > backup_2024-06-01.sql mysqldump -u root -p --all-databases > full_backup.sql # Compressed backup (saves 70-90% space) mysqldump -u root -p ecommerce | gzip > backup_2024-06-01.sql.gz # Restore mysql -u root -p ecommerce < backup_2024-06-01.sql gunzip < backup_2024-06-01.sql.gz | mysql -u root -p ecommerce # PostgreSQL Backup pg_dump ecommerce > backup.sql pg_dump -Fc ecommerce > backup.dump # Custom format (smaller) # Restore PostgreSQL psql ecommerce < backup.sql pg_restore -d ecommerce backup.dump # Automate daily backup (cron) # 0 2 * * * mysqldump -u root ecommerce | gzip > /backups/db_$(date +\%Y\%m\%d).sql.gz

MONGODB// Connect to MongoDB mongosh // Create database and collection use ecommerce // Insert document (no schema needed!) db.users.insertOne({ name: "Suresh", email: "suresh@example.com", skills: ["Docker", "Kubernetes", "AWS"], address: { city: "Guntur", state: "AP" } }) // Query db.users.find({ name: "Suresh" }) db.users.find({ "address.city": "Guntur" }) db.users.find({ skills: "Docker" }) // Update db.users.updateOne( { name: "Suresh" }, { $push: { skills: "Terraform" } } ) // Delete db.users.deleteOne({ name: "Suresh" })

REDIS# Connect to Redis redis-cli # Basic key-value operations SET user:1:name "Suresh" GET user:1:name # "Suresh" SET session:abc123 "user_id:1" EX 3600 # Expires in 1 hour # Increment (great for counters) INCR page:views # 1 INCR page:views # 2 GET page:views # "2" # Lists (for queues) LPUSH jobs "deploy-order-service" LPUSH jobs "build-frontend" RPOP jobs # "deploy-order-service" (FIFO) # Real-world caching pattern: # 1. App checks Redis: GET product:123 # 2. If found (cache hit) → return immediately (1ms) # 3. If not found (cache miss) → query MySQL → store in Redis # 4. SET product:123 "{data}" EX 300 (cache for 5 minutes)