DEV Community

Erlan Akbaraliev
Erlan Akbaraliev

Posted on

База данных 2

  1. Реляционная база данных
  2. Основные типы связей
  3. Диаграмма
  4. Ключи
  5. Подзапросы
  6. IN
  7. Когда НУЖНА третья таблица?
  8. Когда НЕ НУЖНА третья таблица?
  9. JOIN
  10. GROUP BY

Создание новых таблиц

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

DROP TABLE книги;
Enter fullscreen mode Exit fullscreen mode

Скопируйте и выполните все запросы которые имеются в этом файле.
Ссылка на файл

Изпульзуя этот файл мы создали 7 таблиц:

  1. книги
  2. авторы
  3. книги_авторы
  4. переводчики
  5. переводчики_книги
  6. издатели
  7. рейтинг

Если откроете в Pgadmin4 Servers/PostgreSQL 18/Базы Данных/моябазаданных/Схемы/public/Таблицы можете посмотреть на таблицу которые у вас имеются.

У всех этих таблиц есть какое-то взаимосвязь, поэтому эти таблицы называются Реляционными базами данных.

1. Реляционная база данных

Реляционная база данных = Связанные таблицы/база данные
Relation = Реляция = отношение
База данные/таблицы у которых есть отношение, связь с друг-другом.

         авторы                         книги
-----------------------          -----------------------
|     имя             |          |     название        |
| Адания Шибли        |          | Валун               |
| Ахмед Саадави       |          | Кит                 |
| Алия Трабукко Зеран |          | Евангелие           |
| Аманда Свенссон     |          | Стоящий тяжело      |
| Андрей Курков       |          | Убежище времени     |
-----------------------          -----------------------
Enter fullscreen mode Exit fullscreen mode

Просто смотря на эти таблицы как мы можем сказать кто написал какую книгу?

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

И это может привести к дублированию данных — например, если один автор написал несколько книг, его имя будет повторяться, или если книгу написали несколько авторов, данные книги будут повторяться.

            автор.                        книги
----------------------------     -------------------------
| id     имя               |     | id    название          |
|  1   Адания Шибли        |     |  1     Валун            |
|  2   Ахмед Саадави       |     |  2     Кит              |
|  3   Алия Трабукко Зеран |     |  3     Евангелие         |
|  4   Аманда Свенссон     |     |  4     Стоящий тяжело.   |
|  5   Андрей Курков       |     |  5     Первое Солнце     |
                                 |  6     Второе Солнцу     |
 -------------------------                        
                                 ----------------------------
Enter fullscreen mode Exit fullscreen mode

Что мы можем сделать чтобы у нас были две разные связанные таблицы и данные ни в какой таблице не повторялись?
Мы можем создать третью таблицу и назвать её авторы_книги.

         авторы_книги
---------------------------- 
| автор_id     книга_id    |
|  1              1        |
|  2              2        |
|  3              3        |
|  4              4        |
|  5              5        |
|  5              6        |
----------------------------
Enter fullscreen mode Exit fullscreen mode

Автор с id 5, написал книги с id 5 и 6.

Почему используем id, а не имя автора или название книги?
Потому что у разных авторов может быть одинаковое имя, а у разных книг — одинаковое название. id — это уникальный номер для каждого автора и каждой книги, который никогда не повторяется.

Зачем делить одну таблицу на несколько?
Когда таблица одна, хранить много столбцов и данных становится неудобно. Например, можно в таблицу «авторы» добавить столбцы страна (где родился) и год рождения автора.

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

  • Упрощается понимание структуры данных — маленькие таблицы легче читать и использовать.
  • Уменьшается дублирование данных.
  • Улучшается производительность запросов — запросы к маленьким таблицам выполняются быстрее.

2. Основные типы связей

  • один к одному
  • один ко многим
  • многие ко многим

Один к одному
У одного автора есть только одна книга.
One-to-one

Один ко многим
У одного автора имеются несколько книг
One-to-many

Многие ко многим
Не только один автор может написать несколько книг, но и одну книгу могут писать несколько авторов вместе.
Many-to-many

3. Диаграмма

Диаграмма

Связи между таблицами показываются глаголами:

  • Автор (author) пишет (writes) книгу (book)
  • Издатель (publisher) публикует (publishes) книгу
  • Переводчик (translator) переводит (translates) книгу
  • У книги (book) имеется (has) рейтинги

Диаг
Первая (0) означает что нет никаких связей
Вторая что есть только одна связь
Третья несколько связей

Д
У одного автора может быть только одна книга

Д2
Автор может написать одну книгу и у одной книги может быть один автор.

Д3
Один автор может иметь несколько книг, у одной книги могут быть несколько авторов.

4. Ключи

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

         авторы                         книги
----------------------------     -------------------------
| id     имя               |     | id    название        |
|  1   Адания Шибли        |     |  1     Валун          |
|  2   Ахмед Саадави       |     |  2     Кит            |
|  3   Алия Трабукко Зеран |     |  3     Евангелие      |
|  4   Аманда Свенссон     |     |  4     Стоящий тяжело |  
|  5   Андрей Курков       |     |  5     Убежище времени|
                                 |  6     Первое солнце  |
----------------------------     -------------------------
Enter fullscreen mode Exit fullscreen mode 
         авторы_книги
---------------------------- 
| автор_id     книга_id    |
|  1              2        |
|  2              1        |
|  3              5        |
|  4              4        |
|  5              3        |
|  5              6        |
----------------------------
Enter fullscreen mode Exit fullscreen mode

Первичный ключ (Primary Key) — уникальный столбец в таблице, который однозначно идентифицирует каждую строку.
Внешний ключ (Foreign Key) — столбец, который ссылается на первичный ключ другой таблицы и связывает таблицы между собой.

id в таблице "авторы" это уникальный столбец которая идентифицирует каждую строку и тоже самое в таблице "книги". id в обоих таблицах в этой связи явльяются Первычными ключами (Primary keys).

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

5. Подзапросы

Подзапрос - запрос внутри другого запроса.

Выведите все книги которые были опубликованы издателем 'And Other Stories'.

Сперва посмотрите на структуру этих двух таблиц:

  • издатели
  • книги

Как вы видите в таблице издатели есть столбец издатель_id и в таблице издатели есть столбец id.
Эти две таблицы: книги, издатели соеденины по столбцам издатель_id и id

Связь между этими таблицами: один ко многим.
Один издатель может опубликовать несколько книги и одна книги может быть опубликовано только одним издателем.

Теперь к задачу:

  1. Выведите id издателя 'And Other Stories'
  2. Выведите все книги которые были опубликованы этим издателем

С иными словами:

  1. Выведите id из таблицы издатели где издатель='And Other Stories'
  2. Выведите все столбцы из таблицы книги где издатель_id="id издателя 'And Other Stories'"

1.

SELECT id
FROM издатели
WHERE издатель='And Other Stories';
Enter fullscreen mode Exit fullscreen mode

Результат: 1

2.

SELECT *
FROM книги
WHERE издатель_id=1;
Enter fullscreen mode Exit fullscreen mode

Можно получить тот же результат используя подзапрос и это считается хорошой практикой.

SELECT *
FROM книги
WHERE издатель_id=(
        SELECT id
        FROM издатели
        WHERE издатель='And Other Stories'
);
Enter fullscreen mode Exit fullscreen mode

Вот перевод простыми словами:

Обрати внимание:

  • Подзапрос находится в круглых скобках.
    Запрос, который находится глубже всего в скобках, выполняется первым, а затем уже выполняется внешний запрос.

  • Внутренний запрос сделан с отступом.
    Это делается по правилам оформления кода — так подзапрос выглядит аккуратнее и легче читается.

Выведите все рейтинги книги 'Кит'.

  1. Посмотрите на таблицы книги, рейтинг и постарайтесь понять как эти таблицы соединяются
  2. Выведите id книги с названием 'Кит'
  3. Выведите рейтинги где книгаid=_ 
SELECT * 
FROM рейтинг
WHERE книга_id=(
    SELECT id 
        FROM книги
    WHERE название='Кит'
);
Enter fullscreen mode Exit fullscreen mode

Выведите все автора книги 'Валун'

  1. Таблицы книги, авторы, авторы_книги. Тип связи: многое ко многим
  2. Выведите id книги 'Валун'
  3. Выведите авторid где книга_id=_
  4. Выведите имя автора где id=предыдущий запрос

Ответ:

SELECT id
FROM книги
WHERE название='Валун';
Enter fullscreen mode Exit fullscreen mode 
SELECT автор_id
FROM авторы_книги
WHERE книга_id= (
    SELECT id
    FROM книги
    WHERE название='Валун'
);
Enter fullscreen mode Exit fullscreen mode 
SELECT *
FROM авторы
WHERE id=(
    SELECT автор_id
    FROM авторы_книги
    WHERE книга_id= (
        SELECT id
        FROM книги
        WHERE название='Валун'
    )
);
Enter fullscreen mode Exit fullscreen mode

6. IN

IN - в
Это ключевое слово используется, чтобы проверить, есть ли нужное значение в данном списке или множестве значений.

SELECT *
FROM книги
WHERE название IN ('Валун', 'Кит');
Enter fullscreen mode Exit fullscreen mode 
SELECT *
FROM книги
WHERE название='Валун' OR название='Кит';
Enter fullscreen mode Exit fullscreen mode 
SELECT *
FROM книги
WHERE название NOT IN ('Валун', 'Кит');
Enter fullscreen mode Exit fullscreen mode

Выведите все книги 'Ольга Токарчук' используя ключевое слова

  1. Связь между книгами и авторами: многое ко многим. У одного автора могут быть несколько книг, поэтому будем использовать ключевое слово IN
  2. Выведите id автор 'Ольга Токарчук'
  3. Выведите все книгаid где автор_id=_
  4. Выведите все данные книг где id=__ 
SELECT *
FROM книги
WHERE id IN (
    SELECT книга_id
    FROM авторы_книги
    WHERE автор_id= (
        SELECT id
        FROM авторы
        WHERE имя='Ольга Токарчук'
    )
);
Enter fullscreen mode Exit fullscreen mode

Этот запрос даст нам все id книг автора 'Ольга Токарчук' (25,55,78).
Так как книг несколько, нам нужно использовать слово IN.
Выведи все данные книг которые в этом списке id книг.

SELECT книга_id
FROM авторы_книги
WHERE автор_id= (
    SELECT id
    FROM авторы
    WHERE имя='Ольга Токарчук'
);
Enter fullscreen mode Exit fullscreen mode

7. 📌 Когда НУЖНА третья таблица?

Когда связь многие-ко-многим
То есть одна книга может иметь много авторов,
и один автор может написать много книг.

В этом случае создаём третью таблицу:

         авторы                         книги
----------------------------     -------------------------
| id     имя               |     | id    название        |
|  1   Адания Шибли        |     |  1      Валун         |
|  2   Ахмед Саадави       |     |  2      Кит           |
|  3   Алия Трабукко Зеран |     |  3     Евангелие      |
|  4   Аманда Свенссон     |     |  4     Стоящий тяжело |  
|  5   Андрей Курков       |     |  5     Убежище времени|
                                 |  6     Первое солнце  |
----------------------------     -------------------------

         авторы_книги
---------------------------- 
| автор_id     книга_id    |
|  1              2        |
|  2              1        |
|  3              5        |
|  4              4        |
|  5              3        |
|  5              6        |
----------------------------
Enter fullscreen mode Exit fullscreen mode

➡️ Она соединяет авторов и книги.

  1. 📌 Когда НЕ НУЖНА третья таблица? Когда связь один-к-одному или один-ко-многим Один издатель выпускает много книг. Но каждая книга принадлежит только одному издателю.

Один ко многим

       издатели
--------------------------
| id   издатель          |
| 1    And Other Stories |
| 2    Charco Press      |
| 3    Europa Editions   |
| 4    Faber & Faber     |
--------------------------
                 книги       
----------------------------------------
| id     имя                издатель_id |
|  1   Валун                     1      |  
|  2   Кит                       1      |  
|  3   Костёр                    2      |  
|  4   Едва рождённый            2      |   
|  5   Небеса                    2      |  
----------------------------------------
Enter fullscreen mode Exit fullscreen mode

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

❌ Почему внешний ключ НЕ должен быть в таблице издатели?

              издатели
-----------------------------------------
| id   издатель            книга_id     |
| 1    And Other Stories   1            |
| 1    And Other Stories   2            |
| 1    And Other Stories   3            |
| 2    Charco Press        4            |
| 2    Charco Press        5            |
-----------------------------------------
Enter fullscreen mode Exit fullscreen mode

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

Один к одкому
Если бы по каким то правилом в наших таблицах один автор мог бы написать только одну книгу и одной книги мог бы быть только один автор.

              авторы                        
-------------------------------------
| id     имя                книга_id |     
|  1   Адания Шибли             2    |     
|  2   Ахмед Саадави            1    |     
|  3   Алия Трабукко Зеран      5    |     
|  4   Аманда Свенссон          6    |     
|  5   Андрей Курков            3    |     
-------------------------------------     
               книги
-------------------------------------
| id    название            автор_id |
|  1     Валун                  2    |
|  2     Кит                    1    |
|  3     Евангелие              5    |
|  4     Стоящий тяжело         4    |  
|  5     Убежище времени        3    |
-------------------------------------
Enter fullscreen mode Exit fullscreen mode

9. JOIN

JOIN - объединять
Это ключевое слово позволяет объединять две или больше таблицы в один результат.

Выведите название издателя книги 'Небеса'

  1. Тип связи между книги и издатели
  2. Выведите id книги 'Небеса'
  3. Выведите издатель книги с id=_ 
SELECT издатель
FROM издатели
WHERE id=(
    SELECT id
    FROM книги
    WHERE название='Небеса'
);
Enter fullscreen mode Exit fullscreen mode

Выведите название издателя каждой книги

SELECT *
FROM книги к
JOIN издатели и ON к.издатель_id=и.id;
Enter fullscreen mode Exit fullscreen mode

Выведи все столбцы
из таблицы книги, коротко назови таблицу 'к'
объедини таблицей издатели, коротко назови таблицу 'и', по издатель_id из таблицы k и id из таблицы к.

Создайте эти таблицы для следующих примеров:

-- Таблица Миграции
CREATE TABLE миграции (
    id INTEGER PRIMARY KEY,
    расстояние INTEGER,  -- distance
    дней INTEGER         -- days
);

INSERT INTO миграции (id, расстояние, дней) VALUES
(10484, 1000, 107),
(11728, 1531, 56),
(11729, 1370, 37),
(11732, 1622, 62),
(11734, 1491, 58),
(11735, 2723, 82),
(11736, 1571, 52),
(11737, 1957, 92);

-- Таблица животные
CREATE TABLE животные (
    id INTEGER PRIMARY KEY,
    имя TEXT,         -- name
    вид TEXT          -- species
);

INSERT INTO животные (id, имя, вид) VALUES
(10484, 'Ая', 'Залофус калифорнийский'),
(11728, 'Спот', 'Залофус калифорнийский'),
(11729, 'Тигр', 'Залофус калифорнийский'),
(11732, 'Мейбл', 'Залофус калифорнийский'),
(11734, 'Рик', 'Залофус калифорнийский'),
(11790, 'Джоли', 'Залофус калифорнийский');
Enter fullscreen mode Exit fullscreen mode

Выведите все данные животного и миграционные данные в одной таблице

  1. Тип связи и по какому общему столбцу эти две таблицы соединяются 
SELECT *
FROM животные ж
JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

Если в одной таблице есть такие id, которых нет в другой таблице, то эти строки не попадут в результат объединения.
Такой вид объединения называется INNER JOIN (внутреннее соединение).

LEFT JOIN отдаёт приоритет данным из левой (первой) таблицы.
То есть: всё из левой таблицы попадёт в результат, даже если совпадений справа нет.

SELECT *
FROM животные ж
LEFT JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

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

Точно так же RIGHT JOIN сохраняет все строки из правой (второй) таблицы.

SELECT *
FROM животные ж
RIGHT JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

А FULL JOIN показывает все строки из обеих таблиц, даже если для каких-то ID нет совпадений.

SELECT *
FROM животные ж
FULL JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

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

SELECT *
FROM животные ж
NATURAL JOIN миграции м;
Enter fullscreen mode Exit fullscreen mode

10. GROUP BY

GROUP BY = Группируй по

Рассмотрим таблицу рейтинг.
Для каждой книги мы хотим найти её средний рейтинг.

Чтобы это сделать, нам сначала нужно посчитать средний рейтинг внутри каждой группы, а затем сгруппировать все рейтинги по книгам.

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

SELECT книга_id, AVG(рейтинг)
FROM рейтинг
GROUP BY книга_id;
Enter fullscreen mode Exit fullscreen mode

Теперь мы хотим увидеть только те книги, которые хорошо оценены — то есть имеют средний рейтинг выше 3.5.

SELECT книга_id, AVG(рейтинг)
FROM рейтинг
GROUP BY книга_id
HAVING AVG(рейтинг) > 3.5;
Enter fullscreen mode Exit fullscreen mode

Выведи только те группы книг, у которых средний рейтинг больше 3.5.
HAVING - чтобы добавить условие к нашему запросу. Тоже самое как WHERE, но когда используем GROUP BY, после этого нужно использовать слово HAVING чтобы добавить условие к группам.

SELECT книга_id, AVG(рейтинг)
FROM рейтинг
WHERE рейтинг >= 1          -- фильтрация строк
GROUP BY книга_id
HAVING AVG(рейтинг) > 3.5;  -- фильтрация групп
Enter fullscreen mode Exit fullscreen mode

👉 WHERE — фильтрует строки до группировки
👉 HAVING — фильтрует группы после группировки
Что происходит?

WHERE сначала убирает ненужные строки (например, оценки ниже 1).
GROUP BY собирает оставшиеся строки в группы по книге.
HAVING показывает только те группы (книги), которые подходят под условие.

Top comments (0)