SQL basics: SELECT, INSERT, UPDATE, DELETE – a complete guide for developers

Preparation: creating a test table

Before we start working with the data, let's create a simple table for examples:

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

SELECT: reading data

The SELECT command is used to retrieve data from a table. This is the most common operation when working with databases.

Basic syntax

SELECT column1, column2 FROM table_name;

Examples of use

Select all columns:

SELECT * FROM users;

Select specific columns:

SELECT name, email FROM users;

Using WHERE to filter:

SELECT * FROM users WHERE age > 25;

Sorting results with ORDER BY:

SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC;

Limit the number of results:

SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20; -- пагинация

Using LIKE to search by template:

SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name LIKE 'А%'; -- имена на букву А

Aggregate functions:

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;

Grouping with GROUP BY:

SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age 
HAVING COUNT(*) > 1;

INSERT: adding data

The INSERT command allows you to add new records to a table.

Basic syntax

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Examples of use

Inserting a single record:

INSERT INTO users (name, email, age) 
VALUES ('Alexey', 'alexey@example.com', 28);

Inserting multiple records:

INSERT INTO users (name, email, age) VALUES 
    ('Maria', 'maria@example.com', 25),
    ('Dmitry', 'dmitry@example.com', 32),
    ('Elena', 'elena@example.com', 29);

Insert without specifying all columns:

INSERT INTO users (name, email) 
VALUES ('Ivan', 'ivan@example.com');
-- age будет NULL, created_at заполнится автоматически

Insert with return ID:

-- MySQL
INSERT INTO users (name, email, age) 
VALUES ('Olga', 'olga@example.com', 27);
SELECT LAST_INSERT_ID();

-- PostgreSQL
INSERT INTO users (name, email, age) 
VALUES ('Olga', 'olga@example.com', 27)
RETURNING id;

UPDATE: data update

The UPDATE command modifies existing records in a table.

Basic syntax

UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;

Examples of use

Updating a single record:

UPDATE users 
SET age = 29 
WHERE id = 1;

Update of several fields:

UPDATE users 
SET name = 'Alexey Petrov', age = 30 
WHERE id = 1;

Update multiple records:

UPDATE users 
SET age = age + 1 
WHERE age < 30;

Using calculations:

UPDATE users 
SET email = LOWER(email);

Conditional update with multiple conditions:

UPDATE users 
SET age = 25 
WHERE name = 'Maria' AND email LIKE '%@example.com';

Important warning

Always use WHERE when UPDATE! Without this condition, all records in the table will be updated:

-- ОПАСНО! Обновит все записи
UPDATE users SET age = 25;

-- ПРАВИЛЬНО! Обновит только нужные записи
UPDATE users SET age = 25 WHERE id = 1;

DELETE: deleting data

The DELETE command deletes records from the table.

Basic syntax

DELETE FROM table_name WHERE condition;

Examples of use

Deleting one record:

DELETE FROM users WHERE id = 1;

Deletion by condition:

DELETE FROM users WHERE age < 18;

Deletion with multiple conditions:

DELETE FROM users 
WHERE created_at < '2024-01-01' AND age IS NULL;

Delete all records (caution!):

DELETE FROM users; -- удалит все записи
TRUNCATE TABLE users; -- быстрее, но нельзя откатить в транзакции

Important warning

As with UPDATE, always check for WHERE with DELETE:

-- ОПАСНО! Удалит все записи
DELETE FROM users;

-- ПРАВИЛЬНО! Удалит только нужные записи
DELETE FROM users WHERE id = 1;

Transactions: secure data handling

When performing INSERT, UPDATE, and DELETE operations, it is recommended to use transactions to ensure data integrity:

START TRANSACTION;

UPDATE users SET age = 30 WHERE id = 1;
UPDATE users SET age = 28 WHERE id = 2;

-- Если всё хорошо:
COMMIT;

-- Если нужно отменить изменения:
ROLLBACK;

Practical advice

Use SELECT before UPDATE/DELETE

Before changing or deleting data, perform SELECT with the same condition to make sure you are working with the correct records:

-- Сначала проверяем
SELECT * FROM users WHERE age < 18;

-- Если всё верно, удаляем
DELETE FROM users WHERE age < 18;

Performance indices

Create indexes for columns that are often used in WHERE:

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_age ON users(age);

Avoid SELECT *

In the production code, specify specific columns instead of an asterisk:

-- Плохо
SELECT * FROM users;

-- Хорошо
SELECT id, name, email FROM users;

Use LIMIT

When working with large tables, limit the number of returned records:

SELECT * FROM users LIMIT 100;

Parameterized queries

When working with SQL from code, always use parameterized queries to protect against SQL injections:

JavaScript (Node.js)

const userId = 1;
const query = 'SELECT * FROM users WHERE id = ?';
db.query(query, [userId]);

Python

user_id = 1
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))

PHP (PDO)

$userId = 1;
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$userId]);

Team integration: a practical example

Let's create a small application for managing users:

-- Создание пользователя
INSERT INTO users (name, email, age) 
VALUES ('Anna', 'anna@example.com', 26);

-- Получение ID нового пользователя
SET @user_id = LAST_INSERT_ID();

-- Чтение данных пользователя
SELECT * FROM users WHERE id = @user_id;

-- Обновление возраста
UPDATE users SET age = 27 WHERE id = @user_id;

-- Проверка обновления
SELECT name, age FROM users WHERE id = @user_id;

-- Удаление пользователя
DELETE FROM users WHERE id = @user_id;

-- Проверка удаления
SELECT COUNT(*) FROM users WHERE id = @user_id; -- должно вернуть 0

Conclusion

The SELECT, INSERT, UPDATE, and DELETE commands are the basis for working with SQL. Having mastered these operations, you will be able to effectively manage data in any relational database. Remember about security when working with UPDATE and DELETE, always use parameterized queries in the code and do not forget about transactions for critical operations.

Join the educational platform Code — here you will find interactive courses in Python, JavaScript, HTML, CSS, SQL and other programming languages. All materials are created specifically for novice developers with practical examples and step-by-step explanations.

Codica also has an active Telegram community, where developers communicate, share experiences, and help each other solve problems.

Join us to us and start your journey in programming!