Введение
Как я обнаружил, очень эффективно обобщать знания после прохождения какого-то курса или изучения чего-то нового.
Так вы сможете еще раз вспомнить, что выучили, структурировать свои знания и, конечно же, поделитесь своими знаниями с другими.
Недавно прошел курс по postgresql на Udemy. Кстати, это был один из лучших онлайн-курсов, которые я когда-либо проходил. Вот ссылка, если вам тоже интересно. Курс охватывает postgresqlот самого начала до продвинутого уровня знаний.
Итак, эта статья — мои заметки о postgresql.
Что такое база данных и как она работает внутри?
Ну, база данных — это специальная программа (кстати, postgresql написан на языке программирования C. Код тоже с открытым исходным кодом, его можно посмотреть на GitHub ), который отвечает за хранение данных. База данных состоит из таблиц, а таблицы состоят из строк и столбцов. Вы можете спросить: Почему я не могу хранить данные просто в файле? И ответ: Можно!.
Вы определенно можете хранить свои данные в файле в любом формате, который хотите (например, JSON), но база данных делает это более эффективно, а также имеет множество полезных инструментов для работы с данными.
Хорошо. Как postgresql работает внутри? Есть сервер и клиент. Сервер Postgresql ожидает запросов от клиента postgresql и выполняет эти запросы. Сервер хранит данные в специальном формате в специальных файлах. (Я не буду писать об этом в этой статье, но эта тема хорошо описана в этом курсе Удеми). Не очень важно знать, как postgresql хранит данные внутри. Все, что нам нужно знать — есть сервер, который управляет базами данных и каким-то образом хранит данные. И есть клиент (например, pgadmin) — программа, которая отправляет запросы к серверу postgresql.SQL (язык структурированных запросов) — это язык для выполнения запросов к базе данных( на сервер, который управляет базами данных) от клиента. С помощью клиента вы можете выполнять операции CRUD (создавать, читать, обновлять, удалять) с вашими данными в базе данных.
Postgresql может иметь множество баз данных. Базы данных состоят из таблиц. Базы данных могут иметь много таблиц. Таблицы состоят из столбцов и строк. Столбцы отвечают за сохраняемое имя и тип данных. Строки — это записи (фрагменты данных), которые хранятся в таблице.
Основные операции
В этом разделе я буду писать об основных запросах. Надеюсь, вы уже знакомы с синтаксисом SQL.
CREATE DATABASE имя_базы_данных;
создает базу данных с именем имя_базы_данных
СОЗДАТЬ ТАБЛИЦУ citys(
название VARCHAR(50),
страна VARCHAR(50),
население INTEGER,
район INTEGER
);
создает таблицу городов со столбцами имя(строка[50]), страна(строка[50]), население(целое), площадь(целое)
ВСТАВИТЬ В города (название, страна, население, площадь)
ЗНАЧЕНИЯ
(«Токио», «Япония», 38505000, 8223)
(«Киев», « Украина', 4000000, 10000);
добавить 2 записи в таблицу
ВЫБРАТЬ * ИЗ городов
выбирает все записи из таблицы городов
ВЫБЕРИТЕ имя, область ИЗ городов;
выбирает столбец имени и области из таблицы городов
ВЫБРАТЬ население/район ИЗ городов;
возвращает население/район для каждой записи
Также есть операторы:
+, -, *, /, %, ^(показатель степени), |/(квадратный корень), @(абсолютное значение)
ВЫБЕРИТЕ население/район КАК плотность населения ИЗ городов;
Ключевое слово КАК используется для создания псевдонима
ОБНОВИТЬ города УСТАНОВИТЬ население = 300000, ГДЕ имя = 'Токио';
устанавливает население = 300000 в записях, где имя = Токио в таблице городов.
УДАЛИТЬ ИЗ городов, ГДЕ name=’Tokyo’;
удаляет записи, где name=Tokyo, из таблицы городов.
Операции со строками:
SELECT name || ', ' || страна ИЗ городов;
возврат -› «Киев, Украина»
|| — конкатенация
CONCAT(str, str) — также конкатенация
LOWER( )/UPPER() — изменить нижний/верхний регистр
LENGTH()— длина строки
Фильтрация записей
Вот запрос:
ВЫБЕРИТЕ название, район ИЗ городов, ГДЕ район › 4000;
ИЗ городов — выполняется первый блок.
ГДЕ область › 4000 — выполняется второй блок.
SELECT имя, область — выполняется третий блок .
Оператор WHERE фильтрует записи по условию.
Операторы сравнения:
=,›,‹,‹= ,›=,IN, ‹› (не равно), !=(не равно), МЕЖДУ,НЕ В
И, ИЛИ
Примеры:
ВЫБРАТЬ название, район ИЗ городов, ГДЕ район МЕЖДУ 2000 И 5000;
выбирает название и район из таблицы городов, где район › 2000 и ‹ 5000
ВЫБЕРИТЕ название, район ИЗ городов, ГДЕ название В ("Гастроном", "Киев") и район = 90;
Работа с таблицами
Существует 4 типа отношений между таблицами:
Отношение "один ко многим". Пример. У пользователя Instagram много фотографий.
Отношение "многие к одному". Пример. Многие фотографии связаны с одним пользователем.
Отношение "один к одному". Пример. У одной лодки один капитан.
Отношение "многие ко многим". Пример: У многих учеников много разных классов.
Первичные ключи и внешние ключи:
Первичный ключ — однозначно идентифицирует запись в таблице. (id)
Внешний ключ — идентифицирует запись (обычно в другой таблице), с которой связана эта строка.
SERIAL означает, что это значение будет автоматически увеличиваться
Пользователи CREATE TABLE (id SERIAL PRIMARY KEY, имя VARCHAR(50));
CREATE TABLE photos(
id SERIAL PRIMARY KEY,
url VARCHAR(100),
user_id INTEGER REFERENCES users(id) # сделать внешний ключ. Пример один-один отношения
);
Параметры удаления:
Что происходит, когда мы удаляем запись, на которую ссылается другой пользователь.
Пример: фотографии имеют поле user_id, которое ссылается на пользователя. Представьте, что мы удаляем пользователя.
1. ПРИ УДАЛЕНИИ ОГРАНИЧЕНИЯ -> выдает ошибку
2. ПРИ УДАЛЕНИИ НЕТ ДЕЙСТВИЙ -› выбросить ошибку
3. НА УДАЛИТЬ КАСКАД-› удалить фото тоже
4. ON DELETE SET NULL -› установить для 'user_id' фотографии значение NULL
5. ON DELETE SET DEFAULT -› Установить 'user_id' фотографии на значение по умолчанию, если оно предоставлено.
СОЗДАТЬ ТАБЛИЧНЫЕ фотографии (
id SERIAL PRIMARY KEY,
url VARCHAR(200),
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
СОЕДИНЕНИЕ и агрегирование
СОЕДИНЕНИЕ — получение значений путем объединения строк из разных связанных таблиц.
Агрегирование – рассматривает многие строк и вычисляет одно значение.
ВЫБЕРИТЕ комментарии.id КАК comment_id, photos.id ИЗ фотографий КАК p
ПРИСОЕДИНЯЙТЕ комментарии к p.id = comments.photos_id;
Объединяет записи две таблицы при условии p.id = comments.photos_id
4 различных типа соединений:
a. Внутреннее соединение (ключевое слово JOIN)
Объединяет таблицы вместе по условию (comments.user_id == users.id)
если comments.user_id или есть пользователь с id=5, но no comment.user_id=5,
эти записи отображаться не будут.
b. Left Join (ключевое слово LEFT JOIN)
таблицы: фотографии, пользователи
если в таблицах фотографий есть записи, которые не представлены в пользователях,
они все равно представлены в соединении.
фотографии (url: «url», user_id: NULL) по-прежнему будут представлены
c. Правое соединение (ключевое слово RIGHT JOIN)
напротив левого соединения.
d. Полное соединение (ключевое слово FULL JOIN)
объединяет все записи в таблице. отсутствующие записи обнуляются.
ВЫБЕРИТЕ user_id , комментарий
ИЗ комментариев
СГРУППИРОВАТЬ ПО user_id
Создайте список всех уникальных значений user_id. Группирует все записи в таблице по группам user_id.
Агрегирующие функции:
1. COUNT()
2. SUM()
3. AVG()
4. MIN()
5 .МАКС()
ВЫБЕРИТЕ МАКС(id) ИЗ комментариев;
выбирает макс. идентификатор таблицы комментариев.
ВЫБЕРИТЕ COUNT(user_id) ИЗ фотографий;
подсчитывает количество записей с user_id. Записи, в которых user_id=NULL, не учитываются.
ВЫБЕРИТЕ СЧЕТЧИК(*) ИЗ фотографий;
подсчитывает количество всех записей (включая NULL)
ВЫБРАТЬ photo_id, COUNT(*)
ИЗ комментариев
ГДЕ photo_id ‹ 3
СГРУППИРОВАТЬ ПО photo_id
ИМЕЕТ COUNT(*) › 2;
WHERE — фильтрует набор строк.
HAVING — фильтрует набор групп.
Сортировка
ВЫБРАТЬ * ОТ пользователей ORDER BY id ASC;
выбрать все записи пользователей, отсортированные по идентификатору в порядке возрастания.
SELECT * FROM users ORDER BY id DESC;
выбрать все записи пользователей, отсортированные по идентификатору в порядке убывания.
SELECT * FROM users OFFSET 40;
начать с 40-й записи.
SELECT * FROM users LIMIT 5;
выберите первые 5 записей.
Объединения и пересечения
(ВЫБЕРИТЕ * ИЗ ПРОДУКТОВ СОРТИРОВАТЬ ПО ЦЕНЕ DESC LIMIT 4) ОБЪЕДИНЕНИЕ (ВЫБЕРИТЕ * ИЗ ПРОДУКТОВ ORDER BY price/weight DESC LIMIT 4)
удалит дубликаты из 2 выбранных запросов.
(выражение) UNION ALL (выражение)
дубликаты не удаляются.
ОБЪЕДИНЕНИЕ— объединяет результаты двух запросов и удаляет дубликаты
ОБЪЕДИНЯЕТ ВСЕ— объединяет результаты двух запросов и не удаляет дубликаты
INTERSECT — найти общие строки в результатах двух запросов. Удалить дубликаты
Пересекать все — найти общие строки в результатах двух запросов. Без удаления дубликатов
ИСКЛЮЧАЯ — найти строки, которые присутствуют в первом запросе, но отсутствуют во втором запросе. Удалить дубликаты
ИСКЛЮЧАЯ – найти строки, которые присутствуют в первом запросе, но отсутствуют во втором запросе. Не удаляет дубликаты
Сборка запросов с подзапросами
ВЫБЕРИТЕ имя, цену
ИЗ товаров
ГДЕ цена › (ВЫБЕРИТЕ МАКС(цену) ИЗ товаров ГДЕ отдел='Игрушки');
Выбрать отдельные записи
ОТЛИЧНЫЕ — выбрать все уникальные значения внутри столбцов.
ВЫБРАТЬ ОТДЕЛЬНЫЕ отделы ОТ продуктов;
ВЫБЕРИТЕ ОТДЕЛЬНОЕ отделение, название ИЗ продуктов;
выберите все уникальные комбинации
Вспомогательные операторы, ключевые слова и функции
SELECT GREATEST(200, 10, 30)
возвращает 200
ВЫБРАТЬ НАИМЕНЕЕ(1,2,45,6,8,2554)
выбирает 1
ВЫБЕРИТЕ название, цену,
СЛУЧАЙ
КОГДА цена › 600, ТОГДА «высокая»
КОГДА цена › 300, ТОГДА «средняя»
ИНАЧЕ «дешевая»
КОНЕЦ
ИЗ продуктов;
Проверка данных и ограничения
а. Значение не NULL
a.1 при создании таблицы
CREATE table products (
id SERIAL PRIMARY KEY,
price INTEGER NOT NULL
);
a.2 После создания таблицы
ALTER TABLE products
ALTER COLUMN price
SET NOT NULL;
обновление значений в базе данных
ОБНОВИТЬ товары
УСТАНОВИТЬ цену = 1000
ГДЕ цена IS NULL;
б. значения столбца по умолчанию
СОЗДАТЬ таблицу продуктов (
id SERIAL PRIMARY KEY,
цена DEFAULT 1000
);
в. уникальные значения
CREATE table products (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
если мы хотим изменить существующую таблицу.
ALTER TABLE products ADD UNIQUE (name);
д. удалить ограничения
ALTER TABLE products DROP CONSTRAINT products_name_key(name_of_constraint);
напр. Валидация
CREATE TABLE products (
id SERIAL PRIMARY KEY,
price INTEGER CHECK (цена › 0)
);
Продукты ALTER TABLE ДОБАВИТЬ ПРОВЕРКУ (цена › 0);
Внутренние ресурсы Postgres
ПОКАЗАТЬ каталог_данных;
показать, где postgres хранит данные
ВЫБЕРИТЕ oid, имя_даты FROM pg_database;
распечатать идентификатор всех баз данных в postgres
SELECT * FROM pg_class;
распечатать идентификатор таблиц.
Примечания
Тип данных INTEGER ограничен 4 байтами. Таким образом макс. целочисленное значение = 2147483647
