sql — Teletype

Нормалые формы

Вместо кучи правил можно сказать просто - декомпозируй!

(они наследуются. Чтобы достичь второй нужно к таблице, приведённой к первой НФ добавить правила второй)

1

Простые со скалярными значениями, нет повторений.

2

Имеется значащий первичный ключ, все поля связаны с полным первичным ключом.

Если есть меньшее количество атрибутов первичного ключа, от которого зависит какой-либо атрибут, то его его нужно вынести в отдельную таблицу.

M5  BMW  5500000  5%
X5M  BMW  6000000  5%

*подразумевается, что скидка на всю продукция BMW, поэтому нужно вынести это в отдельную таблицу*

3

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

Вот такого не надо.

(Это не работает когда есть 2+ композитных (2+ атрибутов) ключа и они перескаются)

НФБК - её выделяют, но по-моему для неё работает сумма 2 и 3 NF. Т.е. у нас не должно быть зависимостей одного ключа от другого. Их нужно ДЕТЕРМИНИРОВАТЬ.

4

Опять же очень похоже на НФБК. Другими словами, если есть 3 множества, где одно из них включено в другое, то нужно вынести множество, которое в себя включает что-то в другую таблицу.

5

Дальше тяжело реализуемые формы нормализации.

Нужно, чтобы между атрибутами не было циклической зависимости. Т.е. 1 зависит от 2, 2 от 3, а 3 от 1.

6

«декомпозиция до конца»

БАЗА

*По большей части это выжимка из доки постгреса, так что возможно, я случайно сюда притащил его фишки, не относящиеся к другим БД*

SELECT

Обработка функции:

Сначала полностью высчитывается WITH конструкции.
Потом отрабатывает FROM
Отрабатывает WHERE
GROUP BY
Отрабатывает фильтровка SELECT'ом (какие именно поля нужно взять)
Удаляются дубликаты, если SELECT DISTINCT
Соеденяются SELECT запросы при помощи UNION и тп.
ORDER BY
LIMIT/FETCH
FOR и прчоая пакость

Синтаксис

WITH name AS ( )

Создать временную таблицу, к которой потом сделать запрос. можно сделать подзапрос.

WITH Sales AS (

SELECT product_id, SUM(quantity) AS total_sold

FROM order_details

GROUP BY product_id

)

SELECT [ALL | DISTINCT ON (table.row)] (все по умолчанию или уникальные, если указать.
FROM table_name (или тут могут быть скобочки с выражением)

ONLY - сканировать только эту таблицу, исключая дочернии. Они сканируются по умолчанию.
alias. Переименовывание. FROM table AS t. Теперь обращаться к таблице как к t
TABLESAMPLE - брать случайные значения. Типо, когда данных много, а нужен примерный анализ но хз где это применимо. FROM sales TABLESAMPLE SYSTEM (10);
(select) - В скобочках может быть другой select запрос, который будет использован.
with query - название CTL
func()
join*

WHERE (работает со строками).
GROUP BY [ ALL по умолчанию | DISTINCT ] - работает с агрегатными функциями.

RULLUP (группирует, по, тому, что, указано, через, запятую, тут)
CUBE - все возможные группировки того, что в скобках
GROUPING SETS ((сюда, через скобки), (писать нужные группы))

HAVING - (работает с группами строк) отличается от where тем, что может работать с агрегатными функциями и применяется после выборки данных.
WINDOW или [OVER . AS . то же самое, только инлайново, не создаёт название) Позволяет создать набор строк, для которого будет применяться операция.
PARTITION BY указывает по каким параметрам разбивать на группы. После группировки окнные функции (avg, sum) будут выполняться для каждой группы.

[Способ объединения запросов]

UNION - объединяет, удаляя дубликатs
INTERSECT - вывод, если присутсвует в обоих запросах.
EXCEPT - только присутсвуют в первом, но отсутсвуют во втором.

[К ним добавляется ALL и DISTINCT соотвественно добавляя дубликаты или нет.]

ORDER BY (поле) [ ASC | DESC ] NULLS [FIRST | LAST ] ... ASC - от меньшего к большему, DESC - от большего к меньшему. NULLS - выдавать записи с нулами в начале или в конце.
LIMIT - сколько строк нужно собирать

в 2008 стандарте SQL добавили новый синтаксис для того же.

OFFSET - сколько строк пропустить перед сбором ответа.
FETCH - согласно стандарту должно быть переменной, но некоторые бд поддерживают и выражения.
[Это лучше всегои использовать вместе с ORDER BY для предсказуемой последовательности]

ROW and ROWS as well as FIRST and NEXT are noise words that don't influence the effects of these clauses. (xd)

WITH TIES - если есть несколько дубликатов со одинаковыми значениями, то берёт все из них, переходя через лимит FETCH.'а [only по умолчанию, работает как и должно]

*JOIN

Общий синтаксис - [type] join [when].

Т.е. SELECT table1.id, table2.name FROM table JOIN table2 ON table1.id = table2.owner.

INNER JOIN где у нас находится совпадение из первой таблицы во второй по условию ON.

LEFT OUTER JOIN. Собираются все значения с левой (которая после SELECT таблицы, и добавляются значения с правой таблицы. Если их нет, то NULL.

RIGHT OUTER JOIN. Собираются все значения с правой (после JOIN). К ним добавляются, если есть, значения с левой таблицы, если нет, то NULL.

FULL OUTER JOIN. Собираются значения из обоих таблиц, null если нет пары.

CROSS JOIN - соединение каждой записи из левой таблицы с каждой записью из правой. ON не принимает.

USING(id) алиас к ON table1.id = table2.id

NATURAL - использует для объединения совпадающие названия, кринж, не юзаем.

Типы соединений

[INNER] JOIN - если не писать суффикс к join, то по умолчанию будет он, включащий пересечения, где работают оба.
LEFT OUTER JOIN -
RIGHT OUTER JOIN
FULL OUTER JOIN

Уловия

ON
USING
NATURAL

INSERT

[with]

INSERT INTO table_name (col1, col2) [DEFAULT] VALUES (val1, val2,) ON CONFLICT ?

OVERRIDING [SYSTEM|USER] VALUE - вставляет игнорируя пользовательские данные/правила таблицы значения по умолчанию/пользовательские данные (да, даже если противоречат)

ON CONFLICT [DO NOTHING | SO UPDATE SET ] - изменяет существующую строку в случае конфликта (например уникальности). В случае с конфликтом что делать. Или игнорировать, или обновлять как сказано.

По умолчанию возвращает количество успешно вставленных записей.

В конец можно добавить RETURNING, тогда будет возвращать таблицу, как select из того, что вставило.

UPDATE

[with]

UPDATE table1 SET column = 1 FROM table2 WHERE table1.id = table2.owner

ONLY - сделать только в текуйще таблице, но не дочерникх

Можно указать DEFAULT в set, чтобы вернуть значение по умолчанию

WHERE CURRENT OF cursor. Обновить по записи курсора, про них читать тут. https://www.postgresql.org/docs/16/sql-declare.html

По умолчанию возвращает количество записей, подходивших под условие обновления.

Можно добавить RETURNING, тогда будет возвращать таблицу из обновлённых записей.

CREATE

Для временных таблиц, которые фактически ен нужны после появления CTE

[ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ]

TABLE

name, type *options

DEFAULT n - п
NOT NULL - не может быть нелевым значением
CHECK ( expression ) [ NO INHERIT ] - создаёт ограничение. NO INHERIT делает так, чтобы дочерние таблицы не наследовали ограничение. Например d > 30 check может быть.
UNIQUE [ NULLS [ NOT ] DISTINCT - гарантирует уникальность значения. Если добавить NULLS DISTINCT, то NULL будут рассматриваться как разные значения.
EXCLUDE expression USING method (name WITH expression) - гарантируется что expression для всех записей должно дать разные значения. Можно например сумму 3 полей проверять -если такая уже есть, то не запишется.
CONSTRAINT - позволяет создать название для проверки. CONSTRAINT NAME ... проверка.

IF NOT EXISTS - если существует, проигнорировать..

STORAGE + тип сжатия

PLAIN - "простой", нет
EXTERNAL - внешнее хранение данных
EXTENDED - сильное сжатие
MAIN - компромисс между крутым сжатием и быстрым поиском.
DEFAULT - наследовать настройки от всей таблицы/бд.

COMPRESSION - метод сжатия.

COLLATE - правила для данных в этом поле. Например можно быть "en_US.utf8"" для текста.

LIKE - позволяет создавать таблицу, основываясь на другой. { INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } хуйня(?)

PARTITION BY [OF] - как сегментировать таблицу. ДОБАВЛЮ ПОЗЖЕ.

WITH параметры (https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS)

WITHOUT OIDS - без идентификаторов объекта (хз, вроде быстрее, но какие-то подводные камни будут)

[USING INDEX ] TABLESPACE - тейблспейс это пространства хранения на диске. Указать их можно для индекса (с префиксом) и для всей таблицы.

FOREIGN KEY column REFERENCES table_name(key)

ON DELETE *options

ON UPDATE;

options - отвечает за то, что происходит при соответсвующем действии.

NO ACTION - по умолчанию, нечего не делает
RESTRICT - не даёт удалить FK.
CASCADE - удаляет все записи, азвисящие от ключа.
*set не везде есть*
SET NULL - при обновлении поля, на который references поставится NULL
SET DEFAULT - а тут значение по умолчанию (соотвественно они должны быть + корректы)

CREATE INDEX

CREATE [ UNIQUE ] INDEX ON table - создаёт или обычный индекс, или индекс с уникальными значениеями.

CONCURRENTLY - если указан, то бд не будет блокировать таблицу на время создания индекса.

IF NOT EXISTS - понятно

ON [ONLY] table (columns) - говорит, что не нужно создавать индексы для партиций.

USING method - По умолчаниюbtree, ещё есть hash, gist, spgist, gin, brin.Не хочу читать, btree по дефолту хватит, оно крутое.

DESC | ASC - сортировать по возрастанию или убыванию

INCLUDE (что) - в индекс добавляются данные, благодаря чему при поиске по тому, на чём создан индекс, данные получаются быстрее, потому что не нужно обращаться к таблице.Но данные просто валяются, их никак при поиске нельзя использовать. Дедубликация не работает в b-деревьях.

NULLS FIRST|LAST

NULLS [NOT] DISTINCT

COLLATE - параметры не по умолчанию. Например выбрать другой язык при поиске.

WITH (тут параметры предеать типо fastupdate = off)

WHERE

TABLESPACE

ПРАВА

применяются для всех вышеперечисленных

FOR UPDATE - блокирует все выбранные записи для чтения и записи.

FOR UPDATE OF name - блокирует только поле name.

FOR SHARE - можно читать, но не обновлять.

+ NOWAIT - выдаст ошибку, если нельзя получить блокировку сразу.

+ SKIP LOCKED - пропускает заблокированне.

DROP

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

ALTER

самое актуальное

ALTER TABLE table_name

чё сделать [add, drop, enable. disable, rename]

И дописать то что из доки. В принципе меняется всё, поэтому распиывать очень много.

Можно добавить RECURSIVE, тогда можно будет найти, например всех подчинённых сотрудника.

А дальше можно SELECT name FROM Sales WHERE product_id = 123 LIMIT 1;

GRANT'/REVOKE - хуйня для привелегий.

COMMIT/ROLLBACK - для работы с транзакциями

ПРОВЕРКИ

=: Строгое равенство.
<> или !=: Не равно.
<: Меньше чем.
<=: Меньше или равно.
>: Больше чем.
>=: Больше или равно.
BETWEEN: Значение находится в заданном диапазоне.
LIKE: Сравнение с шаблоном (обычно используется с символами подстановки % и _).
IN: Значение находится в списке значений.
IS NULL: Проверка на NULL.
IS NOT NULL: Проверка на не NULL.
EXISTS: Проверка наличия результатов подзапроса.
NOT EXISTS: Проверка отсутствия результатов подзапроса.
ALL: Сравнение со всеми значениями результата подзапроса.
ANY или SOME: Сравнение с любым значением результата подзапроса.
UNIQUE: Сравнение с уникальными значениями результата подзапроса.
ROW или =: Сравнение двух кортежей или строк.
CAST или CONVERT: Преобразование типов данных.
EXTRACT: Извлечение компонентов даты или времени.
OVERLAPS: Проверка пересечения интервалов дат.
NOT: Отрицание логического выражения.

хз поддерживается ли везде

&&: Перекрывание. Проверяет, что диапазоны или интервалы перекрываются.
&<: Начинается перед. Проверяет, что начало одного интервала находится до начала другого интервала.
&>: Заканчивается после. Проверяет, что конец одного интервала находится после конца другого интервала.
<<: Строго слева от. Проверяет, что один интервал находится строго слева от другого интервала.
>>: Строго справа от. Проверяет, что один интервал находится строго справа от другого интервала.
&<|: Не пересекается слева. Проверяет, что один интервал находится полностью слева от другого интервала.
|&>: Не пересекается справа. Проверяет, что один интервал находится полностью справа от другого интервала.
&&: Перекрывается. Проверяет, что интервалы перекрываются.

ДОПОЛНИТЕЛЬНО

ПОИСК ПО СТРОКАМ

LIKE - простая искалка. Работает только с 2 шаблонами - % (аналог *) и _ (аналог .)

ILIKE - [есть не везде] - то же самое, только вне зависимости от регистра.

REGEXP_LIKE - ищет по регулярке. Принимает флаги, вроде i, которые убирают зависимость от регистра.

SIMILAR TO - вроде как устаревшая версия REGEX_LIKE с sql99 стандартом режексов.

Терминология

Домен - то, что может принимать атрибут.

Агрегатная функция - функция, возвращающая одно значение для группы строк. Например MAX(), COUNT() и проче.

Клауза - это кусок команды. Например SELECT и UNION - клаузы.

CTE - common table expression - то, что выдаёт with.