SQL Basics — CREATE, SELECT, INSERT, UPDATE, DELETE

After completing this topic

You will be able to create tables, insert data, retrieve data, modify data, and delete data using SQL.

What is SQL?

SQL (Structured Query Language) is a language for asking questions to a database. It's slightly different from a "programming language" – instead of loops or functions, it's a declarative language where you request "give me this data."

SQL is used in most relational databases, such as MySQL, PostgreSQL, and SQLite. The syntax is almost the same, so once you learn one, you can use the others.

CREATE TABLE — Creating a table

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

A table is a blueprint for the data. Once created, you can only insert data that fits that structure.

INSERT — Inserting data

-- Add one row
INSERT INTO users (name, email) VALUES ('Kim Hoon', 'hoon@example.com');

-- Add multiple rows at once
INSERT INTO users (name, email) VALUES
  ('Lee Soo', 'su@example.com'),
  ('Park Jin', 'jin@example.com');

id and created_at are not specified, but they are automatically filled in thanks to AUTO_INCREMENT and DEFAULT.

SELECT — Reading data

-- Retrieve all
SELECT * FROM users;

-- Retrieve only specific columns
SELECT name, email FROM users;

-- Search with a condition
SELECT * FROM users WHERE name = 'Kim Hoon';

-- Search for partial matches
SELECT * FROM users WHERE email LIKE '%@naver.com';

-- Sort
SELECT * FROM users ORDER BY created_at DESC;

-- Limit the number of results
SELECT * FROM users ORDER BY id DESC LIMIT 10;

SELECT is the most frequently used command in SQL. Use WHERE to set conditions, ORDER BY to sort, and LIMIT to limit the number of results.

* means "all columns." It's convenient, but in practice, it's better to specify only the necessary columns to reduce unnecessary data transfer.

UPDATE — Modifying data

-- Change the email of a specific user
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Modify multiple columns at once
UPDATE users SET name = 'Kim Hoon2', email = 'hoon2@example.com' WHERE id = 1;

Caution: If you omit the WHERE clause, all rows will be modified. This is one of the most dangerous situations that can occur by mistake.

-- This will change the email for all users!
UPDATE users SET email = 'oops@example.com';

DELETE — Deleting data

-- Delete a specific row
DELETE FROM users WHERE id = 3;

-- Delete multiple rows that match a condition
DELETE FROM users WHERE created_at < '2025-01-01';

Similar to UPDATE, if you run it without WHERE, it will empty the entire table. Irreversible.

-- Delete everything — cannot be undone!
DELETE FROM users;

Command summary

When learning SQL for the first time, remember: SELECT is safe, and UPDATE/DELETE is disastrous without WHERE.