SQL, манипуляция данными. Часть 1.
БД можно представить трехмерной фигурой из двухмерных слоев-таблиц, наподобие листов Excel. Сами слои состоят из ячеек. В ячейках - числа или строки.
SQL - язык гибкого взаимодействия с этой фигурой.
Пример простых взаимодействий с БД - выборка данных (SELECT) и вставка (INSERT). К слову, капслок в операторах - не более чем условность для простоты разделения операторов и оперндов. Сам я её соблюдаю, но и в lowercase все работает.
Сейчас рассмотрим выборку.
#SQL
БД можно представить трехмерной фигурой из двухмерных слоев-таблиц, наподобие листов Excel. Сами слои состоят из ячеек. В ячейках - числа или строки.
SQL - язык гибкого взаимодействия с этой фигурой.
Пример простых взаимодействий с БД - выборка данных (SELECT) и вставка (INSERT). К слову, капслок в операторах - не более чем условность для простоты разделения операторов и оперндов. Сам я её соблюдаю, но и в lowercase все работает.
Сейчас рассмотрим выборку.
#SQL
SQL, манипуляция данными. Часть 2.
Возьмем пример выборки:
SELECT login, post_count FROM wp_logs WHERE post_count NOT BETWEEN 5 AND 9;
- SELECT: оператор, указывающий, что операция будет по выборке (а не вставке и др.).
- login, post_count: названия столбцов таблицы, которыми будет ограничена выборка.
- WHERE: условный ограничитель по значениям в таблице. В примере ограничены значения для столбца post_count.
- NOT BETWEEN: комбинация операторов NOT и BETWEEN. Без отрицательной частицы BETWEEN выбирает данные внутри множества, с ней - вне неё:
1) BETWEEN 5 AND 10 = {5...10}
2) NOT BETWEEN 5 AND 10: {∞...5} и {10...∞}
- AND: просто "И". Аналог && в си-подобных языках.
#SQL
Возьмем пример выборки:
SELECT login, post_count FROM wp_logs WHERE post_count NOT BETWEEN 5 AND 9;
- SELECT: оператор, указывающий, что операция будет по выборке (а не вставке и др.).
- login, post_count: названия столбцов таблицы, которыми будет ограничена выборка.
- WHERE: условный ограничитель по значениям в таблице. В примере ограничены значения для столбца post_count.
- NOT BETWEEN: комбинация операторов NOT и BETWEEN. Без отрицательной частицы BETWEEN выбирает данные внутри множества, с ней - вне неё:
1) BETWEEN 5 AND 10 = {5...10}
2) NOT BETWEEN 5 AND 10: {∞...5} и {10...∞}
- AND: просто "И". Аналог && в си-подобных языках.
#SQL
SQL, манипуляция данными. Часть 3.
Числовые операторы SQL:
- стандартные нумерические операторы. != и т.п.
- (NOT) BETWEEN ... AND ... .
- (NOT) IN (...). В скобках листинг значений для выборки, в том числе отрицательной.
Строковые операторы:
=: точное совпадение, аналог ===.
!= или <>: отрицательное точное совпадение.
(NOT) LIKE: нечувствительное к регистру совпадение.
%: аналог *. в регулярных выражениях. Иначе говоря - выборка неорганиченного множества символов до или после операнда. Используется только с LIKE.
_ (нижнее подчеркивание): аналог *, или указатель одного любого символа. Только с LIKE.
[]: диапазон символов или последовательность.
[^]: исключающий диапазон.
(NOT) IN: то же, что с числами, но для строк.
Строковые аргументы обязательно обязательно заключаются в двойные кавычки: "mozilla".
#SQL
Числовые операторы SQL:
- стандартные нумерические операторы. != и т.п.
- (NOT) BETWEEN ... AND ... .
- (NOT) IN (...). В скобках листинг значений для выборки, в том числе отрицательной.
Строковые операторы:
=: точное совпадение, аналог ===.
!= или <>: отрицательное точное совпадение.
(NOT) LIKE: нечувствительное к регистру совпадение.
%: аналог *. в регулярных выражениях. Иначе говоря - выборка неорганиченного множества символов до или после операнда. Используется только с LIKE.
_ (нижнее подчеркивание): аналог *, или указатель одного любого символа. Только с LIKE.
[]: диапазон символов или последовательность.
[^]: исключающий диапазон.
(NOT) IN: то же, что с числами, но для строк.
Строковые аргументы обязательно обязательно заключаются в двойные кавычки: "mozilla".
#SQL
SQL, манипуляция данными. Часть 4.
Сортировка выборки.
Для сортировки используют операторы: DISTINCT, GROUP BY, ORDER BY, LIMIT, OFFSET и некоторые другие. Рассмотрим их отдельно.
- DISTINCT:
полностью удаляет любые дубликаты. Используется после ключевого оператора SELECT.
- ORDER BY:
упорядочивает выборку по столбцу, принимая опциональные условия ASC/DESC (по-восходящему / по нисходящему). Пример: ORDER BY column DESC отсортирует строки от 4 к 1.
- LIMIT и OFFSET:
LIMIT задает лимит строк с совпадениями в выборке.
OFFSET устанавливает точку отсчета: например, OFFSET 5 значает "вывести совпадения, начиная с шестого". Почему с шестого? Пятый входит в ограничение.
Эта пара употребляется после всех прочих операторов.
#SQL
Сортировка выборки.
Для сортировки используют операторы: DISTINCT, GROUP BY, ORDER BY, LIMIT, OFFSET и некоторые другие. Рассмотрим их отдельно.
- DISTINCT:
полностью удаляет любые дубликаты. Используется после ключевого оператора SELECT.
- ORDER BY:
упорядочивает выборку по столбцу, принимая опциональные условия ASC/DESC (по-восходящему / по нисходящему). Пример: ORDER BY column DESC отсортирует строки от 4 к 1.
- LIMIT и OFFSET:
LIMIT задает лимит строк с совпадениями в выборке.
OFFSET устанавливает точку отсчета: например, OFFSET 5 значает "вывести совпадения, начиная с шестого". Почему с шестого? Пятый входит в ограничение.
Эта пара употребляется после всех прочих операторов.
#SQL
SQL, манипуляция данными. Часть 5: нормализация
Если грубо и вкратце, то нормализация БД - разбитие данных по разным таблицам, чтобы сократить дублирование информации. БД становится меньше, работать с данными - проще.
Допустим, есть данные об авторах и их книгах. Если хранить все в одной таблице, очевидно, что имена авторов будут дублироваться относительно их книг:
ПЛиО 1 | Мартин
ПЛиО 2 | Мартин
Подробное руководство по JS | Флэнеган
Вместо этого можно составить две таблицы - авторов и книг по жанрам.
При нормализации строки в разбитых таблицах объединяют ключом - например, числовым ID.
Чтобы работать с данными из нескольких таблиц, их нужно объединять. Для этого используют оператор JOIN. Джойнинг используется не только так, поэтому уместно перед JOIN использовать оператор INNER.
Чтобы указать, по каким столбцам с ключами определять совпадения, используют оператор ON.
Пример объединяющего запроса:
SELECT *
FROM authors
INNER JOIN fantasy
ON authors.id = fantasy.book_id
#SQL
Если грубо и вкратце, то нормализация БД - разбитие данных по разным таблицам, чтобы сократить дублирование информации. БД становится меньше, работать с данными - проще.
Допустим, есть данные об авторах и их книгах. Если хранить все в одной таблице, очевидно, что имена авторов будут дублироваться относительно их книг:
ПЛиО 1 | Мартин
ПЛиО 2 | Мартин
Подробное руководство по JS | Флэнеган
Вместо этого можно составить две таблицы - авторов и книг по жанрам.
При нормализации строки в разбитых таблицах объединяют ключом - например, числовым ID.
Чтобы работать с данными из нескольких таблиц, их нужно объединять. Для этого используют оператор JOIN. Джойнинг используется не только так, поэтому уместно перед JOIN использовать оператор INNER.
Чтобы указать, по каким столбцам с ключами определять совпадения, используют оператор ON.
Пример объединяющего запроса:
SELECT *
FROM authors
INNER JOIN fantasy
ON authors.id = fantasy.book_id
#SQL
SQL, манипуляция данными. Часть 6: тонкости JOIN-инга
Есть два типа джойнинга: INNER (внутренний) и OUTER (внешний). Для каждого есть одноименные операторы.
По умолчанию JOIN выполняется как INNER JOIN. Префикс INNER можно опускать, но обычно его добавляют для лучшего понимания запроса. Во внешние запросы можно добавлять OUTER, но это тоже не обязательно.
INNER JOIN выводит строки, если имеется совпадение по ключу. Если нужно вывести содержимое независимо от совпадений, используют операторы:
LEFT JOIN
RIGHT JOIN
FULL JOIN
В чем разница?
LEFT и RIGHT указывают, какую таблицу выводить целиком. Просто держи в уме, какая таблицу отмечена оператором FROM, а какая помещена в JOIN, и указывай соответствующий префикс.
FULL JOIN выводит полностью обе таблицы.
А вот общий алгоритм джойнинга:
1. Выполняется INNER JOIN
2. Добавляются данные по оператору LEFT или RIGHT
3. Если используется FULL JOIN, то LEFT и RIGHT используются последовательно.
Пустующие поля заполняются NULL-лями. По ним, кстати, тоже можно делать выборку.
Спасибо @Yura0217 за фидбек о типах джойнинга. Расширил пост. :)
#SQL
Есть два типа джойнинга: INNER (внутренний) и OUTER (внешний). Для каждого есть одноименные операторы.
По умолчанию JOIN выполняется как INNER JOIN. Префикс INNER можно опускать, но обычно его добавляют для лучшего понимания запроса. Во внешние запросы можно добавлять OUTER, но это тоже не обязательно.
INNER JOIN выводит строки, если имеется совпадение по ключу. Если нужно вывести содержимое независимо от совпадений, используют операторы:
LEFT JOIN
RIGHT JOIN
FULL JOIN
В чем разница?
LEFT и RIGHT указывают, какую таблицу выводить целиком. Просто держи в уме, какая таблицу отмечена оператором FROM, а какая помещена в JOIN, и указывай соответствующий префикс.
FULL JOIN выводит полностью обе таблицы.
А вот общий алгоритм джойнинга:
1. Выполняется INNER JOIN
2. Добавляются данные по оператору LEFT или RIGHT
3. Если используется FULL JOIN, то LEFT и RIGHT используются последовательно.
Пустующие поля заполняются NULL-лями. По ним, кстати, тоже можно делать выборку.
Спасибо @Yura0217 за фидбек о типах джойнинга. Расширил пост. :)
#SQL
SQL, часть 7.
NULL
По умолчанию ячейки с отсутствующими значениями заполняются NULL. По NULL можно делать выборку оператором IS (NOT) NULL.
Также можно установить иное дефолное значение пустой ячейки - например, число 0 или любую строку.
Выражения и алиасы
В SELECT можно указать не только имя столбца, но и встроенные в БД функции, и выражения вроде математических. Например:
SELECT col_name / 2 AS divided_col_name
Сразу после SELECT следует выражение. За ним можно увидеть новый оператор - AS - и новую строчку.
AS - это оператор присваивания алиаса. В свою очередь, алиас - сокращенное или, наоборот, более подробное название имени столбца или выражения.
Юзкейсы:
- сократить длинное неудобное название столбца;
- обозначить выражение коротким словом для построения дальнейшей логики.
#SQL
NULL
По умолчанию ячейки с отсутствующими значениями заполняются NULL. По NULL можно делать выборку оператором IS (NOT) NULL.
Также можно установить иное дефолное значение пустой ячейки - например, число 0 или любую строку.
Выражения и алиасы
В SELECT можно указать не только имя столбца, но и встроенные в БД функции, и выражения вроде математических. Например:
SELECT col_name / 2 AS divided_col_name
Сразу после SELECT следует выражение. За ним можно увидеть новый оператор - AS - и новую строчку.
AS - это оператор присваивания алиаса. В свою очередь, алиас - сокращенное или, наоборот, более подробное название имени столбца или выражения.
Юзкейсы:
- сократить длинное неудобное название столбца;
- обозначить выражение коротким словом для построения дальнейшей логики.
#SQL
SQL, часть 8.
Тонкости.
- DISTINCT и GROUP BY работают идентично, если в запросе нет агрегатных функций. Однако с DISTINCT эти функции использовать не удастся. Впрочем, такой кейс - вырожденный пример и в реальной практике операторы не взаимозаменяемы.
- Агрегатные функции - это разновидность функций, встроенных в язык, вроде [].join(). Конкретно агрегатные функции - объединяют данные: aggregation значит «объединение».
- Как работают GROUP BY с агрегатными функциями? Представим таблицу заказов, где также есть колонка продавцов с уникальными ID.
Задача - посчитать сумму всех заказов для каждого продавца. Делаем запрос:
SELECT Seller_id, SUM(orders) AS «Summ per seller»
FROM order_table
GROUP BY Seller_id;
Как результат - таблица сумм всех заказов для каждого уникального продавца.
Спасибо @darkgenius за жесткие правки, дополнил пост 😉
#SQL
Тонкости.
- DISTINCT и GROUP BY работают идентично, если в запросе нет агрегатных функций. Однако с DISTINCT эти функции использовать не удастся. Впрочем, такой кейс - вырожденный пример и в реальной практике операторы не взаимозаменяемы.
- Агрегатные функции - это разновидность функций, встроенных в язык, вроде [].join(). Конкретно агрегатные функции - объединяют данные: aggregation значит «объединение».
- Как работают GROUP BY с агрегатными функциями? Представим таблицу заказов, где также есть колонка продавцов с уникальными ID.
Задача - посчитать сумму всех заказов для каждого продавца. Делаем запрос:
SELECT Seller_id, SUM(orders) AS «Summ per seller»
FROM order_table
GROUP BY Seller_id;
Как результат - таблица сумм всех заказов для каждого уникального продавца.
Спасибо @darkgenius за жесткие правки, дополнил пост 😉
#SQL
SQL. Оператор HAVING
Представим, что в запросе отфильтрована часть строк с помощью WHERE, а затем сгруппирована через GROUP BY. Как отфильтровать результат?
Для этого применяют оператор HAVING. Ему передается условие групповой сортировки.
#SQL
Представим, что в запросе отфильтрована часть строк с помощью WHERE, а затем сгруппирована через GROUP BY. Как отфильтровать результат?
Для этого применяют оператор HAVING. Ему передается условие групповой сортировки.
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;
Оператор фильтрации групп есть смысл применять, только если данные группируются, то есть, применен GROUP BY.#SQL
SQL. Порядок выполнения
Представим строку JS-кода:
1) b * с
2) bc + a
3) const mul = bc + a
То же в SQL. Схема такая:
1) FROM и JOIN. Происходит первичная выборка данных, формируется временная таблица.
2) WHERE. Строки фильтруются по условию. На этом шаге недоступны алиасы из оператора SELECT - будут применены позже.
3) GROUP BY. Оставшиеся строки группируются.
4) HAVING. Сгруппированный результат дополнительно фильтруется.
5) SELECT. Применяются выражения и алиасы, указанные для самого первого оператора.
6) DISTINCT. Удаляются дубликаты в выбранной колонке.
7) ORDER BY. На этом шаге имеется готовая выборка. Теперь можно управлять её видом - в частности, порядком вывода...
8) LIMIT / OFFSET. ... и ограничением диапазона.
Оговорюсь, что порядок не обязателен: движки могут менять его на свое усмотрение.
#SQL
Представим строку JS-кода:
const mul = a + b * c
Код выполняется здесь не линейно, а по правилам языка и математики:1) b * с
2) bc + a
3) const mul = bc + a
То же в SQL. Схема такая:
1) FROM и JOIN. Происходит первичная выборка данных, формируется временная таблица.
2) WHERE. Строки фильтруются по условию. На этом шаге недоступны алиасы из оператора SELECT - будут применены позже.
3) GROUP BY. Оставшиеся строки группируются.
4) HAVING. Сгруппированный результат дополнительно фильтруется.
5) SELECT. Применяются выражения и алиасы, указанные для самого первого оператора.
6) DISTINCT. Удаляются дубликаты в выбранной колонке.
7) ORDER BY. На этом шаге имеется готовая выборка. Теперь можно управлять её видом - в частности, порядком вывода...
8) LIMIT / OFFSET. ... и ограничением диапазона.
Оговорюсь, что порядок не обязателен: движки могут менять его на свое усмотрение.
#SQL
SQL. Изменение, вставка, удаление строк
Сразу оговорюсь. Здесь строки - не тип данных, а rows в БД.
Вставку делают оператором INSERT. Таблицы к нему присоединяет INTO.
После перечисляют значения - с помощью оператора VALUES.
Значений должно быть столько же, сколько и столбцов, иначе компилятор не пропустит операцию. Исключение - если присутствуют данные по умолчанию. Другие исключения и подробности - позже.
Пример:
INSERT INTO films
(id, raiting, title)
VALUES(5, 7, "Bruce Lee")
UPDATE делает именно то, о чем вы подумали. Используется так:
UPDATE table
SET column = new_value,
another_column = an_value
WHERE condition_of_row
То есть, сначала выбрали таблица, затем пишем код обновления, а в конце определяем условие фильтрации строк.
DELETE удаляет значения. Используется с операторами FROM и WHERE.
UPDATE и DELETE - потенциально опасные операции. Не сделал бекапа - выполни перед ними SELECT, чтобы проверить, верно ли написал выборку!
#SQL
Сразу оговорюсь. Здесь строки - не тип данных, а rows в БД.
Вставку делают оператором INSERT. Таблицы к нему присоединяет INTO.
После перечисляют значения - с помощью оператора VALUES.
Значений должно быть столько же, сколько и столбцов, иначе компилятор не пропустит операцию. Исключение - если присутствуют данные по умолчанию. Другие исключения и подробности - позже.
Пример:
INSERT INTO films
(id, raiting, title)
VALUES(5, 7, "Bruce Lee")
UPDATE делает именно то, о чем вы подумали. Используется так:
UPDATE table
SET column = new_value,
another_column = an_value
WHERE condition_of_row
То есть, сначала выбрали таблица, затем пишем код обновления, а в конце определяем условие фильтрации строк.
DELETE удаляет значения. Используется с операторами FROM и WHERE.
UPDATE и DELETE - потенциально опасные операции. Не сделал бекапа - выполни перед ними SELECT, чтобы проверить, верно ли написал выборку!
#SQL