- Sets
- Нормализация
- Реляция/связи
- CREATE TABLE
- Типы данных и классы хранения
- Добавлие типов данных в таблицы
- Ограничения таблицы
- Изменение таблицы
- Добавление данных
1. Sets
Set - множество
Intersect - пересечение
Выведите имена людей которые являются авторами и переводчиками в одно и тоже время.
SELECT имя FROM авторы
INTERSECT
SELECT имя FROM переводчики;
Union - объединению
Выведите все имена авторов и переводчиков.
SELECT имя FROM авторы
UNION
SELECT имя FROM переводчики;
Union и JOIN разница
JOIN
- соединяет строки по связям
- соединяет таблицы горизонтально по общему столбцу из двух таблиц (primary, foreign key)
- И выводит одну широкую строку, в которой есть данные из обеих таблиц
UNION
- складывает результаты друг под другом
- объединяет таблицы вертикально
EXCEPT - кроме
Выведите авторов которые только являются авторами.
SELECT имя FROM авторы
EXCEPT
SELECT имя FROM переводчики;
Как мы можем найти множество людей, которые являются либо авторами, либо переводчиками, но не одновременно обоими?
(
SELECT имя FROM авторы
EXCEPT
SELECT имя FROM переводчики
)
UNION
(
SELECT имя FROM переводчики
EXCEPT
SELECT имя FROM авторы
);
2. Нормализация
До этого мы создавали таблицы как авторы с такой схемой. Давайте создадим новую таблицу с новой схемой.
CREATE TABLE авторы (
id INT PRIMARY KEY,
имя VARCHAR(100) NOT NULL,
страна VARCHAR(100) NOT NULL,
год_рождения INT
);
Нам поручено представить систему метро города Бостон.
| id | Имя | Станция | Действие | Тариф | Баланс |
|:---|:--------|:---------------|:---------|:------|:-------|
| 1 | Чарли | Кендалл/MIT | вход | 10 | 5 |
| 2 | Чарли | Джамейка Плейн | выход | 5 | 0 |
| 3 | Алиса | Гарвард | вход | 10 | 20 |
| 4 | Алиса | Парк-стрит | выход | 5 | 15 |
| 5 | Боб | Элуайф | вход | 10 | 30 |
| 6 | Боб | Парк-стрит | выход | 10 | 20 |
Какие проблемы существуют в этой таблице?
Мы можем вынести имена пассажиров в отдельную таблицу и назвать пассажиры, чтобы не дублировать их много раз.
Аналогично, можно переместить станции метро в отдельную таблицу и назвать станции.
Процесс разделения данных таким образом называется нормализацией.
3. Реляция/связи
Теперь нам нужно решить, как наши таблицы (пассажиры и станции) связаны между собой.
Один пассажир, скорее всего, будет посещать несколько станций, а на одной станции метро будет находиться несколько пассажиров.
С учётом этого, связь между ними будет многие-ко-многим.
Здесь видно, что каждый пассажир должен хотя бы раз посетить какую-то станцию, чтобы считаться пассажиром.
Однако станция может не иметь ни одного посетителя, если, например, она временно закрыта.
4. CREATE TABLE
CREATE - создать
TABLE - таблица
Теперь, когда у нас есть схема для двух таблиц, давайте создадим эти таблицы.
CREATE TABLE пассажиры (
id,
имя
);
CREATE TABLE станции (
id,
имя,
линия
);
Здесь мы добавляем столбец "линия", чтобы хранить информацию о том, к какой линии метро принадлежит станция.
| id | имя | линия |
| -- | -------------- | ----- |
| 1 | Ала-Тоо | M1 |
| 2 | Площадь Победы | M1 |
| 3 | Орто-Сай | M2 |
| 4 | Южный рынок | M2 |
| 5 | Дордой | M3 |
То есть в столбце "линия" мы указываем, к какой линии метро относится каждая станция.
Далее мы создадим таблицу, которая будет связывать эти две таблицы.
Такие таблицы часто называют соединительными таблицами.
CREATE TABLE визиты (
пассажир_id,
станция_id
);
5. Типы данных
В postgresql СУБД/DMBS у нас имеются такие типы данных:
- NULL — отсутствие значения
- Integer — номера, целые числа, без дробей (1, 2, 3, 77)
- Real — числа с дробной частью (3.7)
- Text — текст, строки символов, любые тексты
- Blob / BYTEA — хранение бинарных данных, например, изображений
- timestamptz - дата и время с часовым поясам (2025-11-26 14:27:06.843079+06)
- timestampt - дата и время без часового пояса (2025-11-26 14:27:06)
*Есть и другие типы похожие на этиз:
*- BIGINT - номера
- DOUBLE PRECISION - числа с дробной частью (3.7)
- VARCHAR(n) - текст
Разницы
Bigint - 8 байт, для очень больших чисел
INTEGER - 4 байт, для обычных чисел (возраст, количество поездок)
REAL - 4 байт, для обычных чисел с дробю
DOUBLE PRECISION — 8 байт, для высокой точности
NUMERIC - размер зависит от количества цифр (6–20+ байт)
REAL, DOUBLE PRECISION
SELECT 0.1 + 0.2;
-- результат: 0.30000000000000004
NUMERIC
SELECT 0.1 + 0.2;
-- результат: 0.3
REAL, DOUBLE PRECISION - когда нужна скорость, а небольшие ошибки не страшны.
NUMERIC - деньги, цены, проценты, суммы — где ошибка недопустима
Varchar(n) - можно ограничить количество символов
Text - не ограничена
имя VARCHAR(5) -- имя не длиннее 5 символов
имя TEXT -- имя может быть любой длины
SELECT CURRENT_TIMESTAMP;
-- "2025-11-26 14:32:58.21522+06"
SELECT NOW();
-- "2025-11-26 14:33:07.349268+06"
SELECT CURRENT_DATE;
-- "2025-11-26"
SELECT CURRENT_TIME;
-- "14:33:22.193196+06:00"
6. Добавлие типов данных в таблицу
Создайте новую базу данных и выпольните эти запросы:
CREATE TABLE пассажиры (
id INTEGER,
имя TEXT
);
CREATE TABLE станции (
id INTEGER,
имя TEXT,
линия TEXT
);
CREATE TABLE визиты (
пассажир_id INTEGER,
станция_id INTEGER
);
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)
);
Улучшенный код:
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)
);
Как работает 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)
);
8. Изменение таблицы
Давайте изменим схему таблиц на такую:
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,
станция_id INTEGER,
тип TEXT NOT NULL CHECK(тип IN ('вход', 'выход')),
дата_время timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
сумма NUMERIC NOT NULL CHECK(сумма != 0),
PRIMARY KEY(id),
FOREIGN KEY(станция_id) REFERENCES станции(id),
FOREIGN KEY(карта_id) REFERENCES карты(id)
);
Таблица карты: у каждой карты есть id, имя владельца карты
Таблица оплаты: оплата каждого человока хранится здесь, id оплаты, картаid с какой картой была сделана оплата, станция_id в какой станции, тип оплата за вход в станцию или выход, датавремя когда оплата была сделана, сумма сколько пассажир оплатил.
Таблица станции: id и название каждой станции, названия метро линии которая проходит через эту станцию.
Старая схема:
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)
);
- Чтобы иметь новую схему, нам сперва нужно удалить таблицу пассажиры
DROP TABLE пассажиры;
2.Изменим название таблицы визиты на оплаты
ALTER TABLE визиты
RENAME TO оплаты;
3.Добавление нового столбца
ALTER TABLE оплаты
ADD COLUMN тип TEXT;
4.Переименование столбца
ALTER TABLE оплаты
RENAME COLUMN пассажир_id TO карта_id;
ALTER TABLE станции
RENAME COLUMN имя TO название;
5.Удаление столбца
ALTER TABLE оплаты
DROP COLUMN имя_столбца;
6.Добавление primary key
ALTER TABLE станции
ADD PRIMARY KEY (id);
Вы сами потом можете полностью изменить схему таблицы используя такие запросы или просто удалить таблицы пассажиры, станции, визиты (DROP) и создать таблицы карты, оплаты, станции используя новый который находится вверху.
Я просто удалью старые таблицы и создам новые с нуля так как у нас нет никаких важных данных в старых таблицах. Если у нас были бы важные данные, то нам пришлось бы использовать первый вариант.
9. Добавление данных
INSERT INTO карты (id, владелец) VALUES
(1, 'Эрлан'),
(2, 'Нургазы'),
(3, 'Нурайым');
INSERT INTO станции (id, название, линия) VALUES
(1, 'Ала-Тоо', 'M1'),
(2, 'Дордой', 'M1'),
(3, 'Асанбай', 'M1'),
(4, 'Байтик', 'M2'),
(5, 'Московская', 'M2'),
(6, 'Фрунзе', 'M2');
INSERT INTO оплаты (id, карта_id, станция_id, тип, сумма) VALUES
(1, 1, 1, 'вход', 0.10),
(2, 1, 4, 'выход', 0.10)
Top comments (0)