SQL: читаем и пишем данные

SQL — декларативный язык запросов к реляционной базе: SELECT читает данные, INSERT добавляет строки, UPDATE и DELETE меняют и удаляют их.

Язык вопросов к базе

Мы научились делать таблицы. Теперь нужен способ задавать им вопросы и вносить в них изменения. Для этого придуман SQL (произносят «эс-ку-эль» или «сиквел»), Structured Query Language, язык структурированных запросов. Его придумали в 1970-х в IBM для реляционных баз: за основу взяли идеи математика Эдгара Кодда о таблицах и связях. С тех пор он стал международным стандартом, и вы встретите его почти в каждой базе: PostgreSQL, MySQL, SQLite, Oracle и других. Диалекты слегка различаются, но основа одна.

SQL декларативный: вы пишете, что хотите получить, а не как искать. Вы не говорите «пройди по всем строкам и сравни цену». Вы пишете «дай услуги дороже тысячи», а как это сделать, решает сама база. Это большое облегчение, и за него придётся заплатить привычкой: SQL читают не сверху вниз, как обычный код, а по смыслу (мы дойдём до этого).

Создать таблицу

Таблица описывается один раз командой CREATE TABLE.

CREATE TABLE items (
  id    serial PRIMARY KEY,
  title text NOT NULL,
  price integer,
  category text
);

Для каждого столбца пишут имя и тип: integer (целое число), text (текст), numeric (число с дробной частью, для денег), boolean (да/нет), date и timestamp (дата и момент времени). serial это целое число, которое база выдаёт сама по порядку. PRIMARY KEY делает столбец первичным ключом. NOT NULL запрещает оставлять поле пустым.

Добавить строки

INSERT INTO items (title, price, category) VALUES
  ('Первая услуга', 3000, 'основная'),
  ('Вторая услуга', 1500, 'дополнительная');

Сначала имя таблицы и список столбцов, потом после VALUES строки в скобках. Текст всегда в одинарных кавычках, числа без них. Столбец id мы не называем: его выдаст serial.

Выбрать: SELECT

Самая частая команда в SQL это SELECT: «покажи». Подвигайте песочницу ниже: у неё есть таблица из ваших услуг (цены и категории придуманы) и готовые запросы. Меняйте запросы, ломайте их, читайте ошибки.

Это учебный разборщик, который понимает несколько основных команд. Он не PostgreSQL: сообщения похожи на настоящие, но написаны здесь. Цены и категории придуманы, названия взяты из вашего брифа.

Все строкиДороже 1000Две самые дорогиеПо категориямИзменить однуУдалить однуUPDATE без WHERE

Таблица items

id	title	price	category

Сбросить таблицу

Запрос SELECT title, price FROM items WHERE price > 1000

Выполнитьили Ctrl+Enter

{W.press}

Текстовая альтернатива: слева таблица услуг с идентификатором, названием, ценой и категорией, справа поле запроса и результат. Под результатом перечислены шаги выполнения SELECT с числом оставшихся строк.

Теперь по частям.

SELECT title, price     -- какие столбцы показать
FROM items              -- из какой таблицы
WHERE price > 1000      -- какие строки оставить
ORDER BY price DESC     -- как упорядочить (DESC: от большего к меньшему)
LIMIT 3;                -- сколько строк оставить

• SELECT * значит «все столбцы». Удобно, пока вы смотрите; в настоящей программе лучше называть нужные столбцы: так запрос не сломается, когда в таблицу добавят новый.
• В WHERE сравнивают: =, <> (не равно), <, >, <=, >=. Условия соединяют AND и OR. LIKE 'М%' ищет текст, начинающийся с «М» (знак % значит «что угодно»), а IN ('а', 'б') проверяет, входит ли значение в список.
• ORDER BY без слова DESC упорядочивает по возрастанию.
• LIMIT пригодится, когда нужны «три самых дорогих» или одна страница из длинного списка.

NULL: «неизвестно»

Если в столбце ничего нет, там лежит особое значение NULL. Это не ноль и не пустая строка: это «значение неизвестно». Из этого следует странность: сравнение с неизвестным тоже даёт «неизвестно». Поэтому WHERE price = NULL не найдёт ни одной строки. Пустые значения ищут словами IS NULL, а непустые словами IS NOT NULL. Это одна из самых частых ловушек начинающих.

Проверьте себя: как найти услуги без цены?

WHERE price IS NULL. Запись price = NULL не вернёт ничего, потому что любое сравнение с NULL даёт «неизвестно».

Порядок, в котором SQL выполняется

Посмотрите на ленту «В каком порядке выполняется SELECT» под результатом в песочнице. Запрос написан сверху вниз, а выполняется иначе:

1. FROM: взять таблицу (или несколько).
2. WHERE: оставить подходящие строки.
3. GROUP BY: собрать оставшиеся в группы.
4. SELECT: выбрать столбцы и посчитать выражения.
5. ORDER BY: упорядочить.
6. LIMIT: обрезать.

От этого порядка зависят две вещи, которые сбивают всех. Во-первых, в WHERE нельзя написать COUNT(*): когда WHERE работает, групп ещё нет, считать нечего. Во-вторых, в WHERE нельзя пользоваться псевдонимом из SELECT (SELECT price * 2 AS double, WHERE double > 100 не сработает), потому что SELECT выполнится позже.

Посчитать: GROUP BY

Допустим, нужно узнать, сколько услуг в каждой категории.

SELECT category, COUNT(*) AS n
FROM items
GROUP BY category
ORDER BY n DESC;

GROUP BY собирает строки с одинаковой категорией в группы, а COUNT(*) считает, сколько строк в каждой. Кроме COUNT есть SUM (сумма), AVG (среднее), MIN и MAX. Они называются агрегатами: сворачивают много строк в одно число.

Правило, которое настоящий PostgreSQL проверяет строго: в SELECT могут стоять только столбцы из GROUP BY и агрегаты. Иначе непонятно, какую из строк группы показать, и база откажет. А если условие нужно поставить на посчитанное («только категории, где больше двух услуг»), для этого есть HAVING, он работает после группировки.

Изменить и удалить

UPDATE items SET price = price + 500 WHERE id = 2;
DELETE FROM items WHERE id = 5;

Теперь внимание: без WHERE оба запроса затронут все строки. DELETE FROM items; очистит таблицу целиком, и база не переспросит. Правила, которые спасают на практике:

• Сначала напишите тот же запрос как SELECT * FROM items WHERE id = 5 и убедитесь, что нашлась именно та строка. Потом замените SELECT * на DELETE.
• Условие пишите по первичному ключу (id), а не по названию: ключ не повторяется.
• Перед рискованным изменением заверните его в транзакцию, которую можно отменить (урок 4).

В песочнице кнопка «UPDATE без WHERE» показывает, что бывает, а «Сбросить таблицу» возвращает всё как было. В настоящей базе возвращать было бы нечем, кроме резервной копии.

Как не открыть дверь злоумышленнику

Самая известная дыра в сайтах с базой называется SQL-инъекцией. Пример: сайт ищет услуги по слову из строки поиска и собирает запрос склейкой текста: «SELECT … WHERE title = ’» плюс слово посетителя плюс «’». Если посетитель введёт слово с кавычкой и дальше команду, кавычка закроет кавычки запроса, и остаток станет частью команды. Так крали данные и удаляли таблицы у тысяч сайтов.

Лекарство одно: параметризованные запросы. Команда и значение отправляются базе раздельно (в тексте команды стоит заглушка вроде $1, а слово посетителя передают отдельно), и значение уже не может стать командой, что бы в нём ни было. Любая библиотека для работы с базой это умеет. Никогда не склеивайте запрос из слов посетителей: это правило без исключений.

Что здесь настоящее, а что нет

Честная оговорка. Песочница в этом уроке (и поля в практике ниже) это учебный разборщик, который понимает основные команды. Он написан для курса, живёт в памяти страницы и пропадёт при перезагрузке. Настоящий PostgreSQL умеет несравнимо больше, а ошибки говорит другими словами. Если хотите попробовать настоящий: PGlite это PostgreSQL, собранный для браузера (ссылка в источниках), а на SQLBolt есть интерактивные упражнения со своим набором данных. Всё, чему вы научились здесь (SELECT, WHERE, GROUP BY, UPDATE, DELETE), работает там так же.

Практика

Вы в эпохе 2010 года. Сайты на PHP уже хранят данные в базе MySQL, а услуги и цены можно менять без правки HTML. Создайте таблицу услуг вашего проекта и задайте ей вопросы.

В странице шесть полей для запросов. Заполните их по порядку: создайте таблицу, добавьте не меньше пяти строк (названия возьмите из своих услуг, остальные придумайте), отберите и упорядочите, посчитайте по категориям, измените одну запись и удалите другую. Под каждым полем песочница покажет результат. В последней строке назовите запрос, который без WHERE был бы опасен.

Что было не так с 2010 годом

В 2010-м самый обычный PHP-код собирал запрос склейкой: mysql_query("SELECT ... WHERE id = " . $_GET['id']). Это и есть SQL-инъекция, и из-за неё за десятилетие утекли огромные базы. Расширение mysql_* в PHP объявили устаревшим в версии 5.5 и убрали в 7.0, а теперь везде принято работать через параметры. Принцип остался тем же: команда и значение никогда не смешиваются.