SQL для начинающих: основы работы с базами данных

Введение

SQL (Structured Query Language) - это язык программирования для работы с реляционными базами данных. В этой статье мы рассмотрим основы SQL и научимся работать с базами данных.

Что такое SQL?

• Структурированный язык: Четкий синтаксис
• Универсальность: Работа с разными СУБД
• Мощность: Сложные запросы
• Простота: Легкий для изучения

Основы SQL

1. Создание базы данных

Создание базы данных

-- Создание базы данных
CREATE DATABASE my_database;

-- Использование базы данных
USE my_database;

-- Удаление базы данных
DROP DATABASE my_database;

Создание таблиц

-- Создание таблицы
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Создание таблицы с внешним ключом
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2. Основные операции

Вставка данных

-- Вставка одной записи
INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

-- Вставка нескольких записей
INSERT INTO users (username, email) VALUES 
    ('jane_doe', 'jane@example.com'),
    ('bob_smith', 'bob@example.com');

Обновление данных

-- Обновление одной записи
UPDATE users 
SET email = 'new_email@example.com' 
WHERE id = 1;

-- Обновление нескольких записей
UPDATE users 
SET username = CONCAT(username, '_updated') 
WHERE created_at < '2023-01-01';

Удаление данных

-- Удаление одной записи
DELETE FROM users 
WHERE id = 1;

-- Удаление всех записей
DELETE FROM users;

Запросы

1. SELECT

Базовый SELECT

-- Выбор всех полей
SELECT * FROM users;

-- Выбор конкретных полей
SELECT username, email FROM users;

-- Выбор с условием
SELECT * FROM users 
WHERE created_at > '2023-01-01';

Сортировка и группировка

-- Сортировка
SELECT * FROM users 
ORDER BY created_at DESC;

-- Группировка
SELECT COUNT(*) as user_count, 
       DATE(created_at) as date 
FROM users 
GROUP BY DATE(created_at);

2. Соединение таблиц

JOIN

-- INNER JOIN
SELECT posts.title, users.username 
FROM posts 
INNER JOIN users ON posts.user_id = users.id;

-- LEFT JOIN
SELECT users.username, COUNT(posts.id) as post_count 
FROM users 
LEFT JOIN posts ON users.id = posts.user_id 
GROUP BY users.id;

Продвинутые запросы

1. Подзапросы

Использование подзапросов

-- Подзапрос в WHERE
SELECT * FROM users 
WHERE id IN (
    SELECT user_id 
    FROM posts 
    WHERE created_at > '2023-01-01'
);

-- Подзапрос в SELECT
SELECT username, 
       (SELECT COUNT(*) FROM posts WHERE user_id = users.id) as post_count 
FROM users;

2. Агрегатные функции

Статистика

-- Подсчет
SELECT COUNT(*) as total_users 
FROM users;

-- Среднее значение
SELECT AVG(post_count) as avg_posts 
FROM (
    SELECT user_id, COUNT(*) as post_count 
    FROM posts 
    GROUP BY user_id
) as user_posts;

-- Максимальное значение
SELECT MAX(created_at) as latest_post 
FROM posts;

Индексы и оптимизация

1. Создание индексов

Типы индексов

-- Простой индекс
CREATE INDEX idx_username ON users(username);

-- Составной индекс
CREATE INDEX idx_user_post ON posts(user_id, created_at);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_email ON users(email);

2. Оптимизация запросов

EXPLAIN

-- Анализ запроса
EXPLAIN SELECT * FROM users 
WHERE username LIKE 'john%';

-- Анализ с деталями
EXPLAIN ANALYZE 
SELECT * FROM users 
WHERE username LIKE 'john%';

Транзакции

1. Управление транзакциями

Базовые операции

-- Начало транзакции
START TRANSACTION;

-- Подтверждение транзакции
COMMIT;

-- Отмена транзакции
ROLLBACK;

2. Изоляция транзакций

Уровни изоляции

-- Установка уровня изоляции
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Пример транзакции
START TRANSACTION;
    UPDATE users SET email = 'new@example.com' WHERE id = 1;
    INSERT INTO posts (user_id, title) VALUES (1, 'New Post');
COMMIT;

Безопасность

1. Управление пользователями

Создание пользователей

-- Создание пользователя
CREATE USER 'app_user'@'localhost' 
IDENTIFIED BY 'password';

-- Назначение прав
GRANT SELECT, INSERT ON my_database.* 
TO 'app_user'@'localhost';

-- Отзыв прав
REVOKE INSERT ON my_database.* 
FROM 'app_user'@'localhost';

2. Подготовленные запросы

Защита от SQL-инъекций

-- Создание подготовленного запроса
PREPARE stmt FROM 
'SELECT * FROM users WHERE id = ?';

-- Выполнение запроса
SET @id = 1;
EXECUTE stmt USING @id;

-- Удаление подготовленного запроса
DEALLOCATE PREPARE stmt;

Заключение

SQL - это мощный язык для работы с базами данных. Понимание основ SQL и правильное использование его возможностей поможет эффективно работать с данными и создавать надежные приложения.