SQL Basics

Every app needs to store data somewhere. User accounts, blog posts, orders, whatever. That's where databases come in, and SQL is how you talk to them.

SQL (pronounced "sequel" or "ess-queue-ell") is the language for managing relational databases. Once you know it, you can pull exactly the data you want from thousands (or millions) of records.

What Are We Working With?

Imagine a spreadsheet. You've got rows (each record) and columns (each property). That's basically a database table.

Relational databases let you connect tables together. Users table links to Posts table. Orders table links to Customers table. This is the "relational" part.

Creating A Database

CREATE DATABASE myapp;
USE myapp;

First you make the database, then you tell MySQL "hey, I'm working in this one."

Making A Table

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let's break this down:

• INT - whole number
• VARCHAR(100) - text, max 100 characters
• PRIMARY KEY - unique identifier for each row
• AUTO_INCREMENT - automatically picks next number
• NOT NULL - must have a value
• UNIQUE - no duplicates allowed
• DEFAULT - what to use if nothing provided

Adding Data (INSERT)

INSERT INTO users (name, email) VALUES ("cjboon", "test@test.com");
INSERT INTO users (name, email) VALUES ("alice", "alice@test.com");

You specify which columns you're filling, then provide the values in the same order.

Reading Data (SELECT)

This is the big one. SELECT is how you get data out.

-- Get everything
SELECT * FROM users;

-- Just specific columns
SELECT name, email FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE id = 1;

The asterisk (*) means "all columns." You can replace it with specific column names if you only need certain data.

Changing Data (UPDATE)

UPDATE users SET email = "new@test.com" WHERE id = 1;

Pro tip: always include WHERE when updating. Without it, you'd update every single row in the table.

Deleting Data (DELETE)

DELETE FROM users WHERE id = 1;

Same deal—always specify which rows. Otherwise you're wiping the whole table.

Joining Tables Together

This is where it gets powerful. You can combine data from multiple tables.

SELECT users.name, posts.title
FROM users
JOIN posts ON users.id = posts.user_id;

You're saying "give me user names and their post titles, matching each user to their posts."

Types of JOINs

• INNER JOIN - only rows that exist in both tables
• LEFT JOIN - all from left table, matching from right
• RIGHT JOIN - all from right table, matching from left

Most of the time you'll use INNER or LEFT.

Sorting & Limiting

-- Newest users first, limit to 10
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

ORDER BY sorts. DESC means descending (newest first). ASC is ascending (oldest first). LIMIT restricts how many results you get.

Counting & Aggregating

SELECT COUNT(*) FROM users;
SELECT SUM(amount) FROM orders;
SELECT AVG(price) FROM products;

These let you do math across all your data.

Grouping Results

SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id;

GROUP BY groups rows together so you can count or sum per group. This example counts how many posts each user has.

That's The Basics

You can build a lot with just SELECT, INSERT, UPDATE, and DELETE. Practice these commands. Try them on some real data. Once you're comfortable, look into indexes, subqueries, and transactions.