跳到主要内容

SQL Basics

SQL = language for managing relational databases

What is SQL?​

"Structured Query Language"

SQL (Structured Query Language) is a language for managing and querying relational databases.

"SQL is a domain-specific language used for managing relational databases, including querying, inserting, updating, and deleting data."

Core Concepts​

Database · Tables · Rows · Columns

  • Database: Collection of tables
  • Table: Collection of rows and columns
  • Row: Record (one entity)
  • Column: Field (attribute)

"SQL organizes data in databases containing tables, with rows representing records and columns representing attributes."

Basic Queries​

SELECT · FROM · WHERE

SELECT column1, column2
FROM table_name
WHERE condition;

"Basic SQL queries use SELECT to choose columns, FROM to specify table, and WHERE to filter rows."

SELECT Statement​

SELECT = what to retrieve

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT name, email FROM users;

-- Calculated columns
SELECT name, age, age * 365 AS days_old FROM users;

"SELECT specifies which columns to retrieve, with * for all columns or specific column names."

WHERE Clause​

WHERE = filter rows

SELECT * FROM users
WHERE age > 18;

SELECT * FROM users
WHERE name = 'John' AND age > 25;

SELECT * FROM users
WHERE city IN ('NYC', 'LA');

"WHERE clause filters rows based on conditions using operators like =, >, <, AND, OR, IN."

INSERT Statement​

INSERT = add new rows

-- Single row
INSERT INTO users (name, email, age)
VALUES ('John', 'john@example.com', 30);

-- Multiple rows
INSERT INTO users (name, email, age)
VALUES
    ('John', 'john@example.com', 30),
    ('Jane', 'jane@example.com', 25);

"INSERT adds new rows to tables, specifying columns and values to insert."

UPDATE Statement​

UPDATE = modify existing rows

UPDATE users
SET age = 31, email = 'newemail@example.com'
WHERE id = 1;

⚠️ Always use WHERE clause to avoid updating all rows.

"UPDATE modifies existing rows, with SET for new values and WHERE to specify which rows (always include WHERE)."

DELETE Statement​

DELETE = remove rows

DELETE FROM users
WHERE id = 1;

⚠️ Always use WHERE clause to avoid deleting all rows.

"DELETE removes rows from tables, with WHERE to specify which rows (always include WHERE)."

9️⃣ JOINs​

Combine data from multiple tables

-- INNER JOIN (matches only)
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (all from left)
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

"JOINs combine data from multiple tables: INNER (matches only), LEFT (all left + matches), RIGHT, FULL."

Aggregate Functions​

COUNT · SUM · AVG · MAX · MIN

SELECT
    COUNT(*) AS total_users,
    AVG(age) AS average_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users;

"Aggregate functions perform calculations: COUNT, SUM, AVG, MAX, MIN, often used with GROUP BY."

GROUP BY​

Group rows by column

SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

"GROUP BY groups rows by column values, typically used with aggregate functions."

ORDER BY​

Sort results

SELECT * FROM users
ORDER BY age DESC;

SELECT * FROM users
ORDER BY name ASC, age DESC;

"ORDER BY sorts results by specified columns, ASC (ascending) or DESC (descending)."

Primary Keys​

Unique identifier for rows

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);
  • Unique identifier
  • Cannot be NULL
  • One per table

"Primary keys uniquely identify rows, cannot be NULL, and ensure data integrity."

Foreign Keys​

Reference to another table

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

"Foreign keys reference primary keys in other tables, establishing relationships and ensuring referential integrity."

Indexes​

Speed up queries

CREATE INDEX idx_email ON users(email);
  • Improves query performance
  • Trade-off: slower inserts/updates

"Indexes improve query performance by creating data structures for faster lookups, with trade-off of slower writes."

"SQL manages relational databases with tables containing rows and columns. Core operations include SELECT (query), INSERT (add), UPDATE (modify), DELETE (remove). JOINs combine tables, aggregate functions (COUNT, SUM, AVG) perform calculations, GROUP BY groups data, ORDER BY sorts results. Primary keys uniquely identify rows, foreign keys establish relationships, and indexes improve performance."

🧠 Ultra-Short Cheat Sheet​

SELECT, INSERT, UPDATE, DELETE
WHERE (filter)
JOINs (combine tables)
Aggregate functions
GROUP BY, ORDER BY
Primary keys (unique)
Foreign keys (relationships)
Indexes (performance)