Дэн Щербаков ⚛️
95 subscribers
20 photos
49 links
Канал для фронтенд-разработчиков о том, как развиваться и увеличивать зарплату.

Senior Frontend Developer с 6 годами опыта. За этот период увеличил зарплату почти в 7 раз.

Начинайте тут: https://yangx.top/code_lab/280
加入频道
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
SQL. Оператор HAVING

Представим, что в запросе отфильтрована часть строк с помощью 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-кода:

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
SQL. Создание таблиц. Типы данных.

Таблицы создают через оператор CREATE TABLE. Чтобы избежать дублирования, можно добавить IF NOT EXISTS.

Оператору передают параметры:

1. Название колонки.
2. Тип данных.
3. Ограничители (constraints).
4. Оператор DEFAULT
5. Параметр для предыдущего оператора, определяющий значение колонки по умолчанию.

Обязательны только первые два параметра.

Пример:

CREATE TABLE (
Id INTEGER PRIMARY KEY DEFAULT 0
Title TEXT NOT NULL
)

Типы данных:

INT - целые положительные числа.
BOOL - булевы значения, представленные в SQL числами 0 и 1.
FLOAT, DOUBLE, REAL - различные типы дробных чисел.
CHAR n - текст строго определенной длины n.
VARCHAR n - текст, ограниченный длиной n.
TEXT - текст, ограниченный встроенным ограничителем (например, 65535 символов).
DATE, DATETIME - данные о дате в формате, предусмотренном конкретной БД.
BLOB - бинарные данные.

Типов данных гораздо больше, здесь - наиболее часто используемые.

#SQL
SQL. Синтастические тонкости и table constraints

Спасибо неутомимому @darkgenius за деталь:

Колонка в БД называется поле. Применять слово "колонка" в приличном обществе - значит прослыть деревенщиной! :D

Table constraits - это дополнительные определения поля. То есть, полям можно задать определение или даже поведение, которые помогают избегать ошибок и систематизируют информацию.

Примеры table constrainnts:

PRIMARY KEY. Указывает, что значения в поле - уникальны и могут использоваться, например, как ID строки.

AUTOINCREMENT. Только для числовых значений. Задает автоматический инкремент (аналог ++ в JS) значения при создании новой строки.

UNIQE. Указывает, что каждое значение в поле должно быть уникальным: вставить строку с дублем не удастся. От PRIMARY KEY отличается тем, что такое поле нельзя использовать в качестве хранилища ключей.

NOT NULL. В поле не должно быть NULL.

CHECK (expression). Проверяет строку на соответствие выражению - математическому, соответствую массиву и т.д.

FOREIGN KEY REFERENCES field(name). Последовательно проверяет, чтобы PRIMARY KEY соответстволо указанное поле внешней таблицы. Значения в поле, указанном как FOREIGN KEY, не обязательно должны быть уникальными. Пример - соотношение таблиц авторов и книг по уникальному ID автора и повторяющимся ключам книг.

#SQL
SQL. ALTER TABLE

Оператор ALTER TABLE меняет существующие таблицы, а конкретно - добавлять или удалять новые поля, переименовывать таблицы и так далее.

Синтаксис похож на CREATE TABLE:

ALTER TABLE films
ADD new_column или...
DROP some_column или...
RENAME TO old_films

У разных БД - разные методы. Подробности в документации.

#SQL
SQL. Проектирование БД. Общие моменты.

- Cхема БД - это ее представление в виде взаимосвязи таблиц, в виде картинки или текста. По сути - проект. По схеме разработчик понимает структуру БД независимо от ее размера.

- Реляционные БД - значит, БД, построенные на отношениях (relation). Отношения - это просто таблицы.

- Домены. Это множества возможных значений атрибута. Например, домен атрибута пола состоит из множества {M, Ж}. Не следует путать домены с типами данных.

- Кортежи. То же, что и строки.

- Первичный ключ. Уникальный идентификатор кортежа. Может быть составным, потенциальным, несоставным. В третьей форме первичный ключ не может быть составным.

- Внешний ключ. Ключ к главной внешней таблице. Используется в дочерних таблицах.

- Аномалии. Возникают при неправильном проектировании после операций над таблицами.
Бывают аномалии вставки, модификации, удаления.
Пример: из главной таблицы удалена строка, на которую продолжает ссылаться внешний ключ в дочерней.
Решение: при проектировании закладывается либо каскадное удаление всех зависимых строк по внешнему ключу. Либо, если допустимо присутствие строки без связи с главной таблицей, задается дефолтное значение NULL.

- Теорема Хита:

R (A, B, C) = r1(A, B) join r2(A, C)

Что она означает:
Если в таблице с атрибутами A, B, C имеется зависимость A -> B, то декомпозиция этой таблицы обратима.

То есть, правильная нормализация проводится так, чтобы не потерять данные. Если после операции JOIN выполняется равенство по теореме, значит, декомпозиция проведена правильно.

#SQL
SQL. Пара слов о SQLite

Это легковесный движок реляционной БД. Он хранит БД в одном файле - что сводит миграцию базы до передачи этого файла. Его недостатки:
- Усеченная функциональность;
- Сложнее организовать разграничение доступа к таблицам;
- Только один пользователь может обновлять БД в определенный момент времени, а значит, высоконагруженные сервисы - в пролёте;
- Движок не чекает типы данных. Их можно указать в схеме - и все равно запихнуть неверные.

#SQL
SQL. Switch...case в SQL

В реляционных БД есть и эта знакомая всем логическая конструкция. Выражена она так:

SELECT column,
CASE
WHEN genre = 'romance'
OR genre = 'comedy'
THEN 'Chill'
ELSE 'Intense'
END AS 'Mood'
FROM movies;

Можно заметить, что CASE понимает операторы типа OR. Это полезно для построения логики.

#SQL