DEV Community

Erlan Akbaraliev
Erlan Akbaraliev

Posted on

База Данных 3

  1. Sets
  2. Нормализация
  3. Реляция/связи
  4. CREATE TABLE
  5. Типы данных и классы хранения
  6. Добавлие типов данных в таблицы
  7. Ограничения таблицы

1. Sets

Set - множество

Intersect - пересечение

Выведите имена людей которые являются авторами и переводчиками в одно и тоже время.

SELECT имя FROM авторы
INTERSECT
SELECT имя FROM переводчики;
Enter fullscreen mode Exit fullscreen mode

Intersect

Union - объединению

Выведите все имена авторов и переводчиков.

SELECT имя FROM авторы
UNION
SELECT имя FROM переводчики;
Enter fullscreen mode Exit fullscreen mode

Union

Union и JOIN разница
JOIN

  • соединяет строки по связям
  • соединяет таблицы горизонтально по общему столбцу из двух таблиц (primary, foreign key)
  • И выводит одну широкую строку, в которой есть данные из обеих таблиц

UNION

  • складывает результаты друг под другом
  • объединяет таблицы вертикально

EXCEPT - кроме

Выведите авторов которые только являются авторами.

SELECT имя FROM авторы
EXCEPT
SELECT имя FROM переводчики;
Enter fullscreen mode Exit fullscreen mode

EXCEPT

Как мы можем найти множество людей, которые являются либо авторами, либо переводчиками, но не одновременно обоими?

(
    SELECT имя FROM авторы
    EXCEPT
    SELECT имя FROM переводчики
)
UNION
(
    SELECT имя FROM переводчики
    EXCEPT
    SELECT имя FROM авторы
);
Enter fullscreen mode Exit fullscreen mode

ExceptUnion

2. Нормализация

До этого мы создавали таблицы как авторы с такой схемой. Давайте создадим новую таблицу с новой схемой.

CREATE TABLE авторы (
    id INT PRIMARY KEY,
    имя VARCHAR(100) NOT NULL,
    страна VARCHAR(100) NOT NULL,
    год_рождения INT
);
Enter fullscreen mode Exit fullscreen mode

Нам поручено представить систему метро города Бостон.

| id | Имя     | Станция        | Действие | Тариф | Баланс |
|:---|:--------|:---------------|:---------|:------|:-------|
| 1  | Чарли   | Кендалл/MIT    | вход     | 10    | 5      |
| 2  | Чарли   | Джамейка Плейн | выход    | 5     | 0      |
| 3  | Алиса   | Гарвард        | вход     | 10    | 20     |
| 4  | Алиса   | Парк-стрит     | выход    | 5     | 15     |
| 5  | Боб     | Элуайф         | вход     | 10    | 30     |
| 6  | Боб     | Парк-стрит     | выход    | 10    | 20     |
Enter fullscreen mode Exit fullscreen mode

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

Аналогично, можно переместить станции метро в отдельную таблицу и назвать станции.

Процесс разделения данных таким образом называется нормализацией.

3. Реляция/связи

Теперь нам нужно решить, как наши таблицы (пассажиры и станции) связаны между собой.
Один пассажир, скорее всего, будет посещать несколько станций, а на одной станции метро будет находиться несколько пассажиров.
С учётом этого, связь между ними будет многие-ко-многим.

Диаграмма

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

4. CREATE TABLE

CREATE - создать
TABLE - таблица

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

CREATE TABLE пассажиры (
    id,
    имя
);

CREATE TABLE станции (
    id,
    имя,
    линия
);
Enter fullscreen mode Exit fullscreen mode

Здесь мы добавляем столбец "линия", чтобы хранить информацию о том, к какой линии метро принадлежит станция.

| id | имя            | линия |
| -- | -------------- | ----- |
| 1  | Ала-Тоо        | M1    |
| 2  | Площадь Победы | M1    |
| 3  | Орто-Сай       | M2    |
| 4  | Южный рынок    | M2    |
| 5  | Дордой         | M3    |
Enter fullscreen mode Exit fullscreen mode

То есть в столбце "линия" мы указываем, к какой линии метро относится каждая станция.

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

CREATE TABLE визиты (
    пассажир_id,
    станция_id
);
Enter fullscreen mode Exit fullscreen mode

5. Типы данных

В postgresql СУБД/DMBS у нас имеются такие типы данных:

  • NULL — отсутствие значения
  • Integer — номера, целые числа, без дробей (1, 2, 3, 77)
  • Real — числа с дробной частью (3.7)
  • Text — текст, строки символов, любые тексты
  • Blob / BYTEA — хранение бинарных данных, например, изображений

*Есть и другие типы похожие на этиз:
*- BIGINT - номера

  • DOUBLE PRECISION - числа с дробной частью (3.7)
  • VARCHAR(n) - текст

Разницы
Bigint - 8 байт, для очень больших чисел
INTEGER - 4 байт, для обычных чисел (возраст, количество поездок)

REAL - 4 байт, для обычных чисел с дробю
DOUBLE PRECISION — 8 байт, для высокой точности

Varchar(n) - можно ограничить количество символов
Text - не ограничена

имя VARCHAR(5)   -- имя не длиннее 5 символов
имя TEXT   -- имя может быть любой длины
Enter fullscreen mode Exit fullscreen mode

Какой тип данных мы можем использовать для хранения стоимости проезда?

  • Integer: мы можем хранить проезд в 10 центов как число 10, но при этом не очень ясно, это 10 центов или 10 долларов.
  • Text: мы можем хранить стоимость как текст, например “$0.10”. Однако тогда будет сложно выполнять математические операции, например суммировать все проезды пассажира.
  • Real: мы можем хранить стоимость с помощью числа с плавающей точкой, например 0.10.

6. Добавлие типов данных в таблицу 

CREATE TABLE пассажиры (
    id INTEGER,
    имя TEXT
);

CREATE TABLE станции (
    id INTEGER,
    имя TEXT,
    линия TEXT
);

CREATE TABLE визиты (
    пассажир_id INTEGER,
    станция_id INTEGER
);
Enter fullscreen mode Exit fullscreen mode

Diagram2

7. Ограничения таблицы

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

  • Например, столбец первичного ключа (primary key) должен содержать уникальные значения. Для этого используется ограничение PRIMARY KEY.
  • Аналогично, ограничение на внешний ключ (foreign key) требует, чтобы значение находилось в столбце первичного ключа связанной таблицы. Это ограничение называется FOREIGN KEY. 
CREATE TABLE пассажиры (
    id INTEGER,
    имя TEXT,
    PRIMARY KEY(id)
);

CREATE TABLE станции (
    id INTEGER,
    имя TEXT,
    линия TEXT,
    PRIMARY KEY(id)
);

CREATE TABLE визиты (
    пассажир_id INTEGER,
    станция_id INTEGER,
    FOREIGN KEY(пассажир_id) REFERENCES пассажиры(id),
    FOREIGN KEY(станция_id) REFERENCES станции(id)
);
Enter fullscreen mode Exit fullscreen mode

Улучшенный код:

CREATE TABLE пассажиры (
    id SERIAL PRIMARY KEY,
    имя TEXT
);

CREATE TABLE станции (
    id SERIAL PRIMARY KEY,
    имя TEXT,
    линия TEXT
);

CREATE TABLE визиты (
    пассажир_id INTEGER REFERENCES пассажиры(id),
    станция_id INTEGER REFERENCES станции(id)
);
Enter fullscreen mode Exit fullscreen mode

Как работает SERIAL?
Тип SERIAL — это просто удобный способ сказать PostgreSQL: "Сделай этот столбец INTEGER, и автоматически создай и используй счетчик (sequence), чтобы присваивать следующее уникальное число (1, 2, 3...) при каждой вставке".

Резюме: Без SERIAL PostgreSQL не сгенерирует никакой ID, что приведет к ошибке при попытке вставить новую строку без явного указания уникального ID вручную

8. Ограничение столбца

Ограничение столбца — это тип ограничения, который применяется к конкретному столбцу таблицы.

  • CHECK — проверяет условие, например все значения в столбце должны быть больше 0
  • DEFAULT — использует значение по умолчанию, если для строки не указано значение
  • NOT NULL — запрещает вставлять в столбец пустое или NULL-значение
  • UNIQUE — требует, чтобы все значения в столбце были уникальными 
CREATE TABLE пассажиры (
    id INTEGER,
    имя TEXT,
    PRIMARY KEY(id)
);

CREATE TABLE станции (
    id INTEGER,
    имя TEXT NOT NULL UNIQUE,
    линия TEXT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE визиты (
    пассажир_id INTEGER,
    станция_id INTEGER,
    FOREIGN KEY(пассажир_id) REFERENCES пассажиры(id),
    FOREIGN KEY(станция_id) REFERENCES станции(id)
);
Enter fullscreen mode Exit fullscreen mode

Top comments (0)