🦈 Так, начнем #конспект по книгам.

Первая будет "PostgreSQL. Основы языка SQL" Е. П. Моргунов

Данные конспекты особенно будут интересны для повторения теоретической части, а это, в свою очередь, большой плюс для прохождения тех же собесов 1️⃣
Начнем с основ, как это бывает во всех книгах.

1 глава. Введение в базы данных и #SQL

Определение основных понятий реляционной модели
данных: отношение, кортеж, атрибут

В реляционных базах данных пользователь воспринимает данные в виде таблиц. Поэтому термину «файл» соответствует термин «таблица», вместо термина «запись» используется термин «строка», а вместо термина «поле» — термин «столбец» (или «колонка»). Таким образом, таблицы состоят из строк и столбцов, на пересечении которых должны находиться «атомарные» значения, которые нельзя разбить на более мелкие элементы без потери смысла.

В формальной теории реляционных баз данных эти таблицы называют отношениями (relations) — поэтому и базы данных называются реляционными. Отношение — это математический термин. При определении свойств таких отношений используется теория множеств. В терминах данной теории строки таблицы будут называться кортежами (tuples), а колонки — атрибутами. Отношение имеет заголовок, который состоит из атрибутов, и тело, состоящее из кортежей. Количество атрибутов называется степенью отношения, а количество кортежей — кардинальным числом. Кроме теории множеств, одним из оснований реляционной теории является такой раздел математической логики, как исчисление предикатов.

Что такое транзакция?

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

Какие группы операторов выделяются в составе языка SQL?

В языке SQL традиционно выделяются группа операторов определения данных (Data Definition Language — DDL), группа операторов манипулирования данными (Data Manipulation Language — DML) и группа операторов, управляющих привилегиями доступа к объектам базы данных (Data Control Language — DCL).

К операторам языка определения данных (DDL) относятся команды для создания, изменения и удаления таблиц, представлений и других объектов базы данных.

К операторам языка манипулирования данными (DML) относятся команды для выборки строк из таблиц, вставки строк в таблицы, обновления и удаления строк.

Для чего нужны внешние ключи в реляционных таблицах?

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

Что такое потенциальный ключ?

Потенциальный ключ — это комбинация атрибутов таблицы, позволяющая уникальным образом идентифицировать строки в ней. Ключ может состоять только лишь из одного атрибута таблицы. Например, в таблице «Студенты» таким идентификатором может быть атрибут «Номер зачетной книжки». В качестве потенциального ключа данной таблицы могут также служить два ее атрибута, взятые вместе: «Серия документа, удостоверяющего личность» и «Номер документа, удостоверяющего личность». Ни один из них в отдельности не может использоваться в качестве уникального идентификатора. В таком случае ключ будет составным. При этом важным является то, что потенциальный ключ должен быть неизбыточным, т. е. никакое подмножество атрибутов, входящих в него, не должно обладать свойством уникальности. Потенциальный ключ, включающий два упомянутых атрибута, является неизбыточным.

#конспект #SQL

Сегодня кратко (выходные ж завтра) про подготовительные знания для развертывания БД на посгре ☕️

Для доступа к серверу баз данных в комплект PostgreSQL входит интерактивный терминал psql. Для его запуска нужно ввести команду

psql

Для получения краткой справки по всем сервисным командам нужно ввести

\?

Многие такие команды начинаются с символов «\d». Например, для того чтобы просмотреть список всех таблиц и представлений (views), созданных в той базе данных, к которой вы сейчас подключены, введите команду

\dt

Если же вас интересует определение (попросту говоря, структура) какой-либо конкретной таблицы базы данных, например, students, нужно ввести команду

\d students

Для получения списка всех SQL-команд нужно выполнить команду

\h CREATE TABLE

Восстановление БД из sql файла
-f путь до файла
-U пользователь, который будет подключаться к БД

psql -f demo_small.sql -U postgres

Подключение к БД из терминала
-U имя пользователя
-h хост с БД
-d имя БД

psql -U postgres -h localhost -d demo

Если есть какие-то команды, которые используете часто тож пишите в каменты, я что-то знал, что-то добавил от себя еще в конспект выше 1️⃣

#конспект #SQL

Пропустим пару глав об основных командах создания таблиц и добавлении строк, а так же о типах переменных 🥱

Перейдем к различным видам ограничений

CHECK
Это ограничение бывает двух видов: уровня атрибута и уровня таблицы. Различие между ними только в синтаксическом оформлении: в обоих случаях в выражении могут содержаться обращения не только к одному, но и к нескольким атрибутам таблицы

CREATE TABLE progress
( ...
term numeric( 1 ) CHECK ( term = 1 OR term = 2 ),
mark numeric( 1 ) CHECK ( mark >= 3 AND mark <= 5 ),
...
);

Можно придумать для них осмысленные имена, которые облегчат понимание причин появления сообщений об ошибках

CREATE TABLE progress
( ...
mark numeric( 1 ),
CONSTRAINT valid_mark CHECK ( mark >= 3 AND mark <= 5 ),
...
);

NOT NULL
Означает, что в столбце таблицы, на который наложено это ограничение, должны обязательно присутствовать какие-либо определенные значения

UNIQUE
Такое ограничение, наложенное на конкретный столбец, означает, что все значения, содержащиеся в этом столбце в различных строках таблицы, должны быть уникальными, т. е. не должны повторяться. Ограничение уникальности может включать в себя и несколько столбцов. В этом случае уникальной должна быть уже комбинация их значений

CREATE TABLE students
( record_book numeric( 5 ) UNIQUE,
...
);

Это ограничение можно было бы записать и так, дав ему осмысленное имя:

CREATE TABLE students
( record_book numeric( 5 ),
...
CONSTRAINT unique_record_book UNIQUE ( record_book ),
...
);

В следующем примере комбинация значений серии и номера документа, удостоверяющего личность, является уникальной. Поэтому можно модифицировать определение таблицы таким образом:

CREATE TABLE students
( ...
doc_ser numeric( 4 ),
doc_num numeric( 6 ),
...
CONSTRAINT unique_passport UNIQUE ( doc_ser, doc_num ),
...
);

Первичный ключ PRIMARY KEY
Ключ может быть как простым, т. е. включать только один атрибут, так и составным, т. е. включать более одного атрибута.
При этом в отличие от уникального ключа, определяемого с помощью ограничения UNIQUE, атрибуты, входящие в состав первичного ключа, не могут иметь значений NULL. Таким образом, определение первичного ключа эквивалентно определению уникального ключа, дополненного ограничением NOT NULL. Однако не стоит в реальной работе заменять первичный ключ комбинацией ограничений UNIQUE и NOT NULL, поскольку теория баз данных требует наличия в каждой таблице именно первичного ключа

CREATE TABLE students
( record_book numeric( 5 ) PRIMARY KEY,
...
);

В случае создания составного первичного ключа имена столбцов, входящих в его состав, перечисляются в выражении PRIMARY KEY через запятую:

PRIMARY KEY ( имя-столбца1, имя-столбца2, ...)

Внешний ключ FOREIGN KEY
Внешние ключи являются средством поддержания так называемой ссылочной целостности (referential integrity) между связанными таблицами.

Создать внешний ключ можно в формате ограничения уровня атрибута следующим образом:

CREATE TABLE progress
( record_book numeric( 5 ) REFERENCES students ( record_book ),
...
);

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

CREATE TABLE progress
( record_book numeric( 5 ) REFERENCES students,
...
);

Можно определить внешний ключ и в форме ограничения уровня таблицы:

CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
);

Варианты удаления связанных записей из ссылающейся таблицы:
ON DELETE CASCADE - при удалении записи на которую ссылаются записи из другой таблицы они будут так же удалены
ON DELETE RESTRICT или ON DELETE NO ACTION - запрет удаления строки из таблицы А, если в таблице Б есть хотя бы одна строка, ссылающаяся на удаляемую строку в таблице А
ON DELETE SET NULL - присваивание атрибутам внешнего ключа в строках таблицы значения NULL
ON DELETE SET DEFAULT - присваивание атрибутам внешнего ключа в строках таблицы значения по умолчанию (DEFAULT), если оно, конечно, было предписано при создании таблицы

🕋 Стыдно такое не знать...

...спустя 3 года работы в IT. И даже не не хочется оправдываться онлайн курсами)

#конспект #SQL

Представления

Работая с SQL запросами я понимал, когда читал уже написанные или если задача была сложнее обычного селекта из одной таблицы, то я просто обращался к ИИ за помощью 🫠
Но какие же вещи я упускал и не понимал...

При работе с базами данных зачастую приходится многократно выполнять одни и те же запросы, которые могут быть весьма сложными и требовать обращения к нескольким таблицам. Чтобы избежать необходимости многократного формирования таких запросов, можно использовать так называемые представления (views).

CREATE VIEW имя-представления [ ( имя-столбца [, ...] ) ] AS запрос;

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

SELECT * FROM имя-представления;

В отличие от таблиц, представления не содержат данных. При каждом обращении к представлению в команде SELECT данные выбираются из таблиц, на основе которых это представление создано.

СУБД PostgreSQL предлагает свое расширение команды CREATE VIEW, а именно — фразу OR REPLACE. Если представление уже существует, то можно его не удалять, а просто заменить новой версией. Однако нужно помнить о том, что при создании новой версии представления (без явного удаления старой с помощью команды DROP VIEW) должны оставаться неизменными имена столбцов представления. Если же вы хотите изменить имя хотя бы одного столбца, то сначала нужно удалить представление с помощью команды DROP VIEW, а уже затем создать его заново.

PostgreSQL предлагает свое расширение — так называемое материализованное представление. Упрощенный синтаксис команды CREATE MATERIALIZED VIEW, предназначенной для создания материализованных представлений, таков:

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] имя-мат-представления
  [ ( имя-столбца [, ...] ) ]
  AS запрос
  [ WITH [ NO ] DATA ];

В момент выполнения команды создания материализованного представления оно заполняется данными, но только если в команде не было фразы WITH NO DATA. Если же она была включена в команду, тогда в момент своего создания представление остается пустым, а для заполнения его данными нужно использовать команду REFRESH MATERIALIZED VIEW.

Материализованное представление очень похоже на обычную таблицу. Однако оно отличается от таблицы тем, что не только сохраняет данные, но также запоминает запрос, с помощью которого эти данные были собраны.

Если впоследствии вам потребуется обновить данные в материализованном представлении, то выполните команду

REFRESH MATERIALIZED VIEW имя-мат-представления;

Кстати, пишу конспекты уже прочитав книгу, поэтому пробегаюсь по ней вторым заходом и нахожу места, которые пропустил/не так понял. Полезное закрепление получается 😅

#SQL #конспект

Я думал пропустить этот раздел, так как он небольшой и вроде как простой. Но я хочу чтобы он был обязательно в конспекте и я его запомнил 👐

Схемы базы данных

Схема — это логический фрагмент базы данных, в котором могут содержаться различные объекты: таблицы, представления, индексы и др. В базе данных обязательно есть хотя бы одна схема. При создании базы данных в ней автоматически создается схема с именем public.

В каждой базе данных может содержаться более одной схемы. Их имена должны быть уникальными в пределах конкретной базы данных. Имена объектов базы данных (таблиц, представлений, последовательностей и др.) должны быть уникальными в пределах конкретной схемы, но в разных схемах имена объектов могут повторяться. Таким образом, можно сказать, что схема образует так называемое пространство имен.

Посмотреть список схем в базе данных можно так:

\dn

Список схем
Имя       | Владелец
----------+----------
bookings  | postgres
public    | postgres
(2 строки)

По дефолту в БД создается public схема. После создания своей схемы к ней можно обратиться так:

SELECT * FROM bookings.aircrafts;

Но что, если я создал схему для основного или частого использования? Каждый раз писать схему такое себе. Делаем так:

SET search_path = bookings;

Теперь имя схемы писать не обязательно. Можно указать сразу несколько схем для поиска таблиц при обращении к ним:

SET search_path = bookings, public;

ВАЖНО понимать, что так определяется очередность поиска таблиц по схемам - сначала таблица ищется в bookings, потом в public.

Посмотреть текущие search_path:

SHOW search_path;

search_path
-------------
bookings
(1 строка)

Посмотреть имя текущей схемы:

SELECT current_schema;

current_schema
----------------
bookings
(1 строка)

#конспект #SQL

Сегодня, наверное, тема о самом популярном, что делаем в SQL 😏

Запросы

Для начала поставим перед собой такую задачу: выбрать все самолеты компании Airbus. В этом нам поможет оператор поиска шаблонов LIKE:

SELECT * FROM aircrafts WHERE model LIKE 'Airbus%';

Знак "%" соответствует любым символам (или их отсутствие). Его можно использовать и в начале маски для поиска.

aircraft_code  |model            | range
---------------+-----------------+-------
320            | Airbus A320-200 | 5700
321            | Airbus A321-200 | 5600
319            | Airbus A319-100 | 6700
(3 строки)

И обратное:

SELECT * FROM aircrafts
WHERE model NOT LIKE 'Airbus%'
AND model NOT LIKE 'Boeing%';

Знак "_" соответствует любому одному знаку.

SELECT airport_name FROM airports WHERE airport_name LIKE '___';

Вернет нам UFA, которое соответствует 3 знакам.

Существует ряд операторов для работы с регулярными выражениями POSIX. Эти операторы имеют больше возможностей, чем оператор LIKE. Для того чтобы выбрать, например, самолеты компаний Airbus и Boeing, можно сделать так:

SELECT * FROM aircrafts WHERE model ~ '^(A|Boe)';

aircraft_code  |model            | range
---------------+-----------------+-------
773            | Boeing 777-300  | 11100
763            | Boeing 767-300  | 7900
320            | Airbus A320-200 | 5700
321            | Airbus A321-200 | 5600
319            | Airbus A319-100 | 6700
733            | Boeing 737-300  | 4200
(6 строк)

Оператор ~ ищет совпадение с шаблоном с учетом регистра символов. Символ «^» в начале регулярного выражения означает, что поиск совпадения будет привязан к началу строки.

Ну и так далее, про регулярки тут не буду углубляться ✍️

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

SELECT * FROM aircrafts WHERE range BETWEEN 3000 AND 6000;

aircraft_code  |model                | range
---------------+---------------------+-------
SU9            | Sukhoi SuperJet-100 | 3000
320            | Airbus A320-200     | 5700
321            | Airbus A321-200     | 5600
733            | Boeing 737-300      | 4200
(4 строки)

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

SELECT model, range, round( range / 1.609, 2 ) AS miles
FROM aircrafts;

Считаем значения дальности полета в милях и округляем до 2 знаков после запятой:

model         | range | miles
--------------+-------+---------
Boeing 777-300| 11100 | 6898.69
Boeing 767-300| 7900  | 4909.88
...

Есть возможность с помощью условных операторов сделать дополнительный столбец со своими значениями:

SELECT model, range,
  CASE WHEN range < 2000 THEN 'Ближнемагистральный'
    WHEN range < 5000 THEN 'Среднемагистральный'
    ELSE 'Дальнемагистральный'
  END AS type
FROM aircrafts
ORDER BY model;

model               | range | type
--------------------+-------+---------------------
Airbus A319-100     | 6700  | Дальнемагистральный
Boeing 737-300      | 4200  | Среднемагистральный
Boeing 767-300      | 7900  | Дальнемагистральный
Boeing 777-300      | 11100 | Дальнемагистральный
Bombardier CRJ-200  | 2700  | Среднемагистральный
(5 строк)

Сегодня, как мне кажется, ничего сложного. Ну или мне уже привычно этим пользоваться 😭

#конспект #SQL

Я решил пропустить раздел с соединениями, ибо он очень объемный и это база, которую давали даже мне на онлайн курсе) Кому то она окажется тяжелой (например мне 😄) и он ее поймет спустя пару лет (например я😄), но тут лучше практикой, чем чтением. Так же, пропускаю маленькую тему про агрегатные функции типа max/min/avg.

Оконные функции (window functions)

Эти функции предоставляют возможность производить вычисления на множестве строк, логически связанных с текущей строкой, т. е. имеющих то или иное отношение к ней.
При работе с оконными функциями используются концепции раздела (partition) и оконного кадра (window frame).

Пример запроса с оконной функцией:

SELECT b.book_ref,
       b.book_date,
       extract( 'month' from b.book_date ) AS month,
       extract( 'day' from b.book_date ) AS day,
       count( * ) OVER (
         PARTITION BY date_trunc( 'month', b.book_date )
         ORDER BY b.book_date
       ) AS count
FROM ticket_flights tf
JOIN tickets t ON tf.ticket_no = t.ticket_no
JOIN bookings b ON t.book_ref = b.book_ref
WHERE tf.flight_id = 1
ORDER BY b.book_date;

Конструкция для вызова оконной функции:

count( * ) OVER (
  PARTITION BY date_trunc( 'month', b.book_date )
  ORDER BY b.book_date
) AS count

В этой конструкции обязательным является ключевое слово OVER. Функция count — это обычная агрегатная функция, но если вслед за ней идет это ключевое слово, то она становится оконной функцией. Предложение PARTITION BY задает правило разбиения строк выборки на разделы. Предложение ORDER BY предписывает порядок
сортировки строк в разделах.
Обобщая приведенные объяснения, можно сказать, что раздел включает в себя все строки выборки, имеющие в некотором смысле одинаковые свойства, например, одинаковые значения определенных выражений, задаваемых с помощью предложения PARTITION BY. Это могут быть выражения, построенные на основе одного или нескольких столбцов таблицы (или таблиц, участвующих в соединении).

Кстати, date_trunc — это функция, для усечения даты по выбранному критерию, в нашем случае b.book_date мы усекаем с начала и по конец месяца, где находится дата из переменной.
(Да, это я лично вставил длинное тире, а не ИИ 😎)

В этом запросе в предложении OVER ( PARTITION BY timezone ... ) указывается, что строки относятся к одному разделу на основе совпадения значений в столбце timezone. Обратите внимание, что хотя в предложении OVER задан порядок сортировки, действующий в пределах каждого окна, тем не менее, с помощью предложения ORDER BY указан также и порядок сортировки на уровне всего запроса.

SELECT airport_name,
       city,
       round( latitude::numeric, 2 ) AS ltd,
       timezone,
       rank() OVER (
         PARTITION BY timezone
         ORDER BY latitude DESC
       )
FROM airports
WHERE timezone IN ( 'Asia/Irkutsk', 'Asia/Krasnoyarsk' )
ORDER BY timezone, rank;

airport_name   | city          | ltd   | timezone         | rank
---------------+---------------+-------+------------------+------
Усть-Илимск    | Усть-Илимск   | 58.14 | Asia/Irkutsk     | 1
Усть-Кут       | Усть-Кут      | 56.85 | Asia/Irkutsk     | 2
Братск         | Братск        | 56.37 | Asia/Irkutsk     | 3
Иркутск        | Иркутск       | 52.27 | Asia/Irkutsk     | 4
...
Абакан         | Абакан        | 53.74 | Asia/Krasnoyarsk | 5
Барнаул        | Барнаул       | 53.36 | Asia/Krasnoyarsk | 6
Горно-Алтайск  | Горно-Алтайск | 51.97 | Asia/Krasnoyarsk | 7
Кызыл          | Кызыл         | 51.67 | Asia/Krasnoyarsk | 8
(13 строк)

Дайте реакций, как обычно 🤑