Последнее обновление: 23.11.2022
Для упрощения администрирования на сервере postgresql в базовый комплект установки входит такой инструмент как pgAdmin.
Он представляет графический клиент для работы с сервером, через который мы в удобном виде можем создавать, удалять, изменять базы данных и управлять ими.
Так, на Windows после установки мы можем найти значок pgAdmin в меню Пуск и запустить его:
После этого нам откроется следующая программа pgAdmin. При открытии также отображится окно для ввода пароля для подключения к серверу Postgres:
Здесь необходимо ввести пароль для суперпользователя postgres, который был задан при установке PostgreSQL.
После успешного логина нам откроется содержимое сервера:
В частности, в узле Databases мы можем увидеть все имеющиеся базы данных. По умолчанию здесь есть только одна база данных — postgres.
Также в правой части мы можем увидеть узел Login/Group Roles, который предназначен для управления пользователями и их ролями.
И третий узел — Tablespaces позволяет управлять местом хранения файлов баз данных.
Теперь создадим свою базу данных. Для этого нажмем правой кнопкой мыши на узел Databases. И далее в контекстном меню
выберем Create->Database…
После этого нам отобразится окно для создания базы данных. Введем название для БД, например, test1 и нажмем на кнопку
«Save»:
После этого в древовидном меню слева отобразится содержимое созданной базы данных test1:
Установка
Когда вы изучаете новый язык, самым важным аспектом является практика. Одно дело – прочитать статью и совсем другое – применить полученную информацию. Давайте начнем с установки базы данных на компьютер.
Первый шаг – установить SQL
Мы будем использовать PostgreSQL (Postgres) – достаточно распространенный SQL диалект. Для этого откроем страницу загрузки, выберем операционную систему (в моем случае Windows), и запустим установку. Если вы установите пароль для вашей базы данных, постарайтесь сразу не забыть его, он нам дальше понадобится. Поскольку наша база будет локальной, можете использовать простой пароль, например: admin.
Следующий шаг – установка pgAdmin
pgAdmin – это графический интерфейс пользователя (GUI – graphical user interface), который упрощает взаимодействие с базой данных PostgreSQL. Перейдите на страницу загрузки, выберите вашу операционную систему и следуйте указаниям (в статье используется Postgres 14 и pgAdmin 4 v6.3.).
После установки обоих компонентов открываем pgAdmin и нажимаем Add new server. На этом шаге установится соединение с существующим сервером, именно поэтому необходимо сначала установить Postgres. Я назвал свой сервер home и использовал пароль, указанный при установке.
Теперь всё готово к созданию таблиц. Давайте создадим набор таблиц, которые моделируют школу. Нам необходимы таблицы: ученики, классы, оценки. При создании модели данных необходимо учитывать, что в одном классе может быть много учеников, а у ученика может быть много оценок (такое отношение называется «один ко многим»).
Мы можем создать таблицы напрямую в pgAdmin, но вместо этого мы напишем код, который можно будет использовать в дальнейшем, например, для пересоздания таблиц. Для создания запроса, который создаст наши таблицы, нажимаем правой кнопкой мыши на postgres (пункт расположен в меню слева home → Databases (1) → postgres и далее выбираем Query Tool.
Начнем с создания таблицы классов (classrooms). Таблица будет простой: она будет содержать идентификатор id и имя учителя – teacher. Напишите следующий код в окне запроса (query tool) и запустите (run или F5).
DROP TABLE IF EXISTS classrooms CASCADE;
CREATE TABLE classrooms (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
teacher VARCHAR(100)
);
В первой строке фрагмент DROP TABLE IF EXISTS classrooms удалит таблицу classrooms, если она уже существует. Важно учитывать, что Postgres, не позволит нам удалить таблицу, если она имеет связи с другими таблицами, поэтому, чтобы обойти это ограничение (constraint) в конце строки добавлен оператор CASCADE. CASCADE – автоматически удалит или изменит строки из зависимой таблицы, при внесении изменений в главную. В нашем случае нет ничего страшного в удалении таблицы, поскольку, если мы на это пошли, значит мы будем пересоздавать всё с нуля, и остальные таблицы тоже удалятся.
Добавление DROP TABLE IF EXISTS перед CREATE TABLE позволит нам систематизировать схему нашей базы данных и создать скрипты, которые будут очень удобны, если мы захотим внести изменения – например, добавить таблицу, изменить тип данных поля и т. д. Для этого нам просто нужно будет внести изменения в уже готовый скрипт и перезапустить его.
Ничего нам не мешает добавить наш код в систему контроля версий. Весь код для создания базы данных из этой статьи вы можете посмотреть по ссылке.
Также вы могли обратить внимание на четвертую строчку. Здесь мы определили, что колонка id является первичным ключом (primary key), что означает следующее: в каждой записи в таблице это поле должно быть заполнено и каждое значение должно быть уникальным. Чтобы не пришлось постоянно держать в голове, какое значение id уже было использовано, а какое – нет, мы написали GENERATED ALWAYS AS IDENTITY, этот приём является альтернативой синтаксису последовательности (CREATE SEQUENCE). В результате при добавлении записей в эту таблицу нам нужно будет просто добавить имя учителя.
И в пятой строке мы определили, что поле teacher имеет тип данных VARCHAR (строка) с максимальной длиной 100 символов. Если в будущем нам понадобится добавить в таблицу учителя с более длинным именем, нам придется либо использовать инициалы, либо изменять таблицу (alter table).
Теперь давайте создадим таблицу учеников (students). Новая таблица будет содержать: уникальный идентификатор (id), имя ученика (name), и внешний ключ (foreign key), который будет указывать (references) на таблицу классов.
DROP TABLE IF EXISTS students CASCADE;
CREATE TABLE students (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name VARCHAR(100),
classroom_id INT,
CONSTRAINT fk_classrooms
FOREIGN KEY(classroom_id)
REFERENCES classrooms(id)
);
И снова мы перед созданием новой таблицы удаляем старую, если она существует, добавляем поле id, которое автоматически увеличивает своё значение и имя с типом данных VARCHAR (строка) и максимальной длиной 100 символов. Также в эту таблицу мы добавили колонку с идентификатором класса (classroom_id), и с седьмой по девятую строку установили, что ее значение указывает на колонку id в таблице классов (classrooms).
Мы определили, что classroom_id является внешним ключом. Это означает, что мы задали правила, по которым данные будут записываться в таблицу учеников (students). То есть Postgres на данном этапе не позволит нам вставить строку с данными в таблицу учеников (students), в которой указан идентификатор класса (classroom_id), не существующий в таблице classrooms. Например: у нас в таблице классов 10 записей (id с 1 до 10), система не даст нам вставить данные в таблицу учеников, у которых указан идентификатор класса 11 и больше.
INSERT INTO students
(name, classroom_id)
VALUES
('Matt', 1);
/*
ERROR: insert or update on table "students" violates foreign
key constraint "fk_classrooms"
DETAIL: Key (classroom_id)=(1) is not present in table
"classrooms".
SQL state: 23503
*/
Теперь давайте добавим немного данных в таблицу классов (classrooms). Так как мы определили, что значение в поле id будет увеличиваться автоматически, нам нужно только добавить имена учителей.
INSERT INTO classrooms
(teacher)
VALUES
('Mary'),
('Jonah');
SELECT * FROM classrooms;
/*
id | teacher
-- | -------
1 | Mary
2 | Jonah
*/
Прекрасно! Теперь у нас есть записи в таблице классов, и мы можем добавить данные в таблицу учеников, а также установить нужные связи (с таблицей классов).
INSERT INTO students
(name, classroom_id)
VALUES
('Adam', 1),
('Betty', 1),
('Caroline', 2);
SELECT * FROM students;
/*
id | name | classroom_id
-- | -------- | ------------
1 | Adam | 1
2 | Betty | 1
3 | Caroline | 2
*/
Но что же случится, если у нас появится новый ученик, которому ещё не назначили класс? Неужели нам придется ждать, пока станет известно в каком он классе, и только после этого добавить его запись в базу данных?
Конечно же, нет. Мы установили внешний ключ, и он будет блокировать запись, поскольку ссылка на несуществующий id класса невозможна, но мы можем в качестве идентификатора класса (classroom_id) передать null. Это можно сделать двумя способами: указанием null при записи значений, либо просто передачей только имени.
-- явно определим значение NULL
INSERT INTO students
(name, classroom_id)
VALUES
('Dina', NULL);
-- неявно определим значение NULL
INSERT INTO students
(name)
VALUES
('Evan');
SELECT * FROM students;
/*
id | name | classroom_id
-- | -------- | ------------
1 | Adam | 1
2 | Betty | 1
3 | Caroline | 2
4 | Dina | [null]
5 | Evan | [null]
*/
И наконец, давайте заполним таблицу успеваемости. Этот параметр, как правило, формируется из нескольких составляющих – домашние задания, участие в проектах, посещаемость и экзамены. Мы будем использовать две таблицы. Таблица заданий (assignments), как понятно из названия, будет содержать данные о самих заданиях, и таблица оценок (grades), в которой мы будем хранить данные о том, как ученик выполнил эти задания.
DROP TABLE IF EXISTS assignments CASCADE;
DROP TABLE IF EXISTS grades CASCADE;
CREATE TABLE assignments (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
category VARCHAR(20),
name VARCHAR(200),
due_date DATE,
weight FLOAT
);
CREATE TABLE grades (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
assignment_id INT,
score INT,
student_id INT,
CONSTRAINT fk_assignments
FOREIGN KEY(assignment_id)
REFERENCES assignments(id),
CONSTRAINT fk_students
FOREIGN KEY(student_id)
REFERENCES students(id)
);
Вместо того чтобы вставлять данные вручную, давайте загрузим их с помощью CSV-файла. Вы можете скачать файл из этого репозитория или создать его самостоятельно. Имейте в виду, чтобы разрешить pgAdmin доступ к данным, вам может понадобиться расширить права доступа к папке (в моем случае – это папка db_data).
COPY assignments(category, name, due_date, weight)
FROM 'C:/Users/mgsosna/Desktop/db_data/assignments.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 5
Query returned successfully in 118 msec.
*/
COPY grades(assignment_id, score, student_id)
FROM 'C:/Users/mgsosna/Desktop/db_data/grades.csv'
DELIMITER ','
CSV HEADER;
/*
COPY 25
Query returned successfully in 64 msec.
*/
Теперь давайте проверим, что мы всё сделали верно. Напишем запрос, который покажет среднюю оценку, по каждому виду заданий с группировкой по учителям.
SELECT
c.teacher,
a.category,
ROUND(AVG(g.score), 1) AS avg_score
FROM
students AS s
INNER JOIN classrooms AS c
ON c.id = s.classroom_id
INNER JOIN grades AS g
ON s.id = g.student_id
INNER JOIN assignments AS a
ON a.id = g.assignment_id
GROUP BY
1,
2
ORDER BY
3 DESC;
/*
teacher | category | avg_score
------- | --------- | ---------
Jonah | project | 100.0
Jonah | homework | 94.0
Jonah | exam | 92.5
Mary | homework | 78.3
Mary | exam | 76.0
Mary | project | 69.5
*/
Отлично! Мы установили, настроили и наполнили базу данных.
***
Итак, в этой статье мы научились:
- создавать базу данных;
- создавать таблицы;
- наполнять таблицы данными;
- устанавливать связи между таблицами;
Теперь у нас всё готово, чтобы пробовать более сложные возможности SQL. Мы начнем с возможностей синтаксиса, которые, вероятно, вам еще не знакомы и которые откроют перед вами новые границы в написании SQL-запросов. Также мы разберем некоторый виды соединений таблиц (JOIN) и способы организации запросов в тех случаях, когда они занимают десятки или даже сотни строк.
В следующей части мы разберем:
- виды фильтраций в запросах;
- запросы с условиями типа if-else;
- новые виды соединений таблиц;
- функции для работы с массивами;
Материалы по теме
- 🐘 8 лучших GUI клиентов PostgreSQL в 2021 году
- 🐍🐬 Python и MySQL: практическое введение
- 🐍🗄️ Управление данными с помощью Python, SQLite и SQLAlchemy
Welcome to pgAdmin 4. pgAdmin is the leading Open Source management tool for
Postgres, the world’s most advanced Open Source database. pgAdmin 4 is designed
to meet the needs of both novice and experienced Postgres users alike, providing
a powerful graphical interface that simplifies the creation, maintenance and use
of database objects.
Note
Postgres, PostgreSQL and the Slonik Logo are trademarks or
registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.
На чтение 9 мин Просмотров 1.4к. Опубликовано 24.10.2020
узнал сам – поделись с другими
23 июля 2012 г.
Работа с postgresql при помощи pgadmin
Теперь можно запустить pgAdmin через графический интерфейс. Выбираем в списке локальный сервер БД (у него дефолтный порт 5432) и добавляем уже созданную нами БД с теми параметрами, которые мы задали.
 |
| pgAdmin |
В это локальном сервере помимо узла Databases можно найти также узел под названием Login Roles — все доступные роли.
Выберем из всех доступных баз данных ту, которую мы создали при установке postgresql. В базе данных содержится множество различных типов объектов. Среди особое внимание следует уделить таблицам и последовательностям (sequences).
Чаще всего обычная таблица содержит в себе первичный ключ в виде положительного числового значения. Для каждой строки это значение должно быть уникальным, поэтому чтобы каждый раз не задавать его напрямую в запросе, можно установить в качестве дефолтного значения последовательность.
 |
| Создание последовательности в pgAdmin |
Для начала создадим последовательность. Выбираем Sequences — New Sequence. На первой вкладке вводим имя и указываем в качестве Owner ту роль, которую мы создали. Для других ролей эта последовательность будет недоступна. На второй вкладке в поля Increment и Start вводим по единице (если вам не требуется другое). На последней вкладке диалогового окна можно посмотреть результирующий SQL запрос, который будет выполнен к БД.
После того, как последовательность создана, приступим к созданию таблицы. Также указываем её имя и владельца (owner). На четвёртой вкладке Columns добавляем прежде всего первичный ключ. Кнопка Add, в появившемся окне указываем название столбца, например, id. В качестве типа данных выбираем bigint. На второй вкладке Definition в поле Default Value указываем нашу последовательность. В поле должно быть значение вида nextval(‘message_id_seq’::regclass). То есть каждый раз при добавлении новой строки из последовательности будет браться следующее значение. Добавили другие столбцы по необходимости. Наконец, на вкладке Constraints добавим ограничение на первичный ключ (Primary Key). На последней вкладке можем полюбоваться на результирующий SQL код, который сгенерировал pgAdmin. После нажатия ОК таблица будет создана.
Вместо bigint в качестве первичного ключа можно указать в качестве типа столбца bigserial. Этот тип инкрементируется автоматически при добавлении каждой новой строки, поэтому создавать последовательность для него не нужно. То есть в самом простом случае, если у вас нет каких-либо особых правил для формирования id записи, можно порекомендовать использовать bigserial.
Давайте посмотрим содержимое нашей таблицы. Для этого правой кнопкой мыши нажмём на неё и выберем в контекстном меню View Data — View 100 rows.
Многих начинающих пользователей, администраторов 1С пугают консоли, особенно когда речь идет о бесплатной СУБД PostgreSQL.
Даже простые на первый взгляд операции по бэкапу или восстановлению баз данных 1С, становятся для многих новичков настоящим испытанием.
Страх того что что-то пойдет не так усиливается при виде консольного окна, в котором нужно «строчить» соответствующую команду.
А некоторые и вовсе считают что эта СУБД только для профессионалов.
По этим причинам многие начинающие администраторы отдают предпочтение MS SQL только из-за простоты использования и наличия большого количества справочного материала.
Так как визуальная консоль управления Management Studio (SSMS) действительно существенно упрощает процесс администрирования сервера СУБД.
И лишь не многие знают, что некоторые вещи можно делать и в PostgreSQL.
Что есть визуальная консоль для написания SQL запросов, разработки процедур и функций, а также для администрирования СУБД PostgreSQL.
Конечно, речь идет о pgAdmin 4 (Третья версия к слову уже прекратила поддержку!).
PostgreSQL + pgAdmin 4 (Подключение и настройка)
pgAdmin – это отличный инструмент для администратора 1С, если Ваша СУБД PostgreSQL!
Добавим сюда еще и кросс платформенность!
Да есть консоль pgAdmin и для Linux и Mac и конечно Windows (Десктоп и сервер).
Функционал консоли pgAdmin, огромен и в рамках этой статьи мы успеем разобрать лишь базовые вещи.
Научимся устанавливать pgAdmin 4, подключать консоль к серверу, делать резервное копирование и восстановление баз данных 1С. (статью разобью на две или даже три части).
Здесь следует отметить, что pgAdmin не является единственным визуальным инструментом для администратора этой СУБД.
Есть, к примеру, и phpPgAdmin, где используя обычный веб обозреватель, и мы также можем получить доступ к административным и другим функциям СУБД.
Но сложность реализации веб способа, выше, чем простая установка pgAdmin.
А цель данной статьи показать новичкам иной путь помимо MS SQL!
Развеять мифы о сложности настроек PostgreSQL для работы в 1С Предприятии.
Показать простоту и легкость использования, администрирования PostgreSQL. (При не большом количестве пользователей).
И если сегодня есть альтернатива «Сиквелу» то это определенно PostgreSQL!
Достойное второе место, действительно можно отдать этой СУБД.
Где скачать и как установить PostgreSQL для 1С я уже писал вот здесь.
А все необходимое для администрирования клиент-серверной схемы на этой СУБД уже разберем в этой статье.
И так первым делом скачаем консоль на офф сайте:
На сегодня актуальная версия: v.2.1.
Качать будем для ОС Windows (x86).
x64 пока нет, да собственно и не нужно.
Первый вопрос, где устанавливать ?
Ставить консоль можно на любой ПК или сервер в Вашей локальной сети, это не обязательно должен быть сервер, где стоит СУБД PostgreSQL.
Сам процесс установки очень простой и не требует каких то спец. знаний, все интуитивно понятно, но для «успокоения нервов» ) все же покажу.
Клик по кнопке «Next»
Соглашаемся с условиями лицензии (Продукт с открытым исходным кодом и абсолютно бесплатный).
Выбираем каталог для установки и еще раз «Next»
(Напомню, консоль бывает только 32-х разрядная).
Теперь клик по кнопке «Install».
Установка быстрая, несколько секунд и все готово!
pgAdmin 4 — Вы всегда найдете в меню «Пуск».
Выполним первый запуск консоли.
Вот он рабочий стол pgAdmin 4!
Для начала стоит сменить язык по умолчанию (Английский на Рус).
Сделать это можно через меню «File» — «Preferences».
И в дереве находим «User language» меняем язык на «Russian» или какой Вам удобно.
После обязательно нужно выполнить перезапуск консоли, чтоб изменения вступили в силу.
Вот теперь мы можем приступить к подключению сервера СУБД PostgreSQL.
К слову на этот момент он у Вас уже должен быть установлен!
Иначе к чему подключаться ? )
И так клик по кнопке «Добавить новый сервер» (Это выполняется разово).
И придумаем имя для сервера СУБД к которому мы и подключаем консоль.
У меня на сервере PostgreSQL нет других баз кроме 1С Предприятия, поэтому напишу:
Имя — «Base_1S» — для примера.
Здесь можно писать что угодно.
Пока не пытайтесь кликнуть по кнопке «Сохранить» она все еще не активна!
Чтоб ее активировать, нужно заполнить еще несколько полей на вкладке «Соединение«.
А именно:
1. Имя/адрес сервера — Укажем IP адрес сервера СУБД, тот сервер или ПК, где у Вас работает, установлен PostgreSQL.
2. Порт — Здесь по умолчанию если ничего не трогали 5432.
3. Имя пользователя — postgres это имя нашего рута, если Вы при установке PostgreSQL оставили это имя как есть.
4. Пароль — Пароль пользователя postgres (При установке PostgreSQL указывали).
Рекомендую оставить «птичку» возле «Сохранить пароль«, чтоб каждый раз консоль Вас не спрашивала.
Вот теперь кнопка «Сохранить» активна и мы можем подключится к серверу СУБД PostgreSQL.
После подключения к серверу СУБД, Вы увидите картинку примерно как выше.
Базы данных, Роли, группы и остальные объекты, которые способна отобразить консоль pgAdmin 4.
На панели информации (Справа в окне), мы видим также очень интересную и полезную информацию.
«Активные сеансы» — их количество и состояния: «Активно» — «Простаивает» и «Всего».
Количество «Транзакций в секунду», а также ниже «Сеансы», «Блокировки» и многое другое.
Эти показатели также можно настроить, но об этом и многом другом, будем говорить уже в следующий части статьи.
Как правило, работа с базой данных осуществляется с помощью специального языка запросов — SQL. Рассмотрим, как выполнять простейшие SQL-запросы к базе данных в pgAdmin.
К примеру, возьмем базу данных test1, которая была создана в прошлой теме (или создадим новую) и добавим в нее таблицу и некоторые начальные данные. Для этого нажмем в правой части окна pgAdmin на базу данных правой кнопкой мыши и в появившемся контекстном меню выберем пункт Query Tool :
После этого в центральной части программы откроется поле для ввода кода SQL. Введем следующий набор выражений:
Фактически весь код разбивается на две части. Первая часть — инструкция CREATE TABLE , которая создает таблицу users с тремя столбцами Id, Name и Age. И вторая часть — инструкция INSERT , которая добавляет в строку одну таблицу.
Чтобы выполнить данные инструкции, нажмем над кодом в панели инструментов на значок молнии. И после этого в выбранную базу данных (test1) будет добавлена таблица users, в которую будет добавлена одна строка.
Впоследствии подобным образом будет выполняться любой другой код SQL к базе данных. Также выбирается нужная база данных, выбирается параметр Query Tool, и далее в поле ввода вводится код SQL, который затем выполняется.
Следует отметить, что для каждой таблицы определяется схема. По умолчанию это схема «public». Поэтому чтобы найти таблицу, нам надо обратиться к узлу базы данных, раскрыть его, далее выбрать подузел Schemas , в нем подузел public (название схема), и далее в нем подузел Tables , который представляет все таблицы, ассоциированные со схемой public:
Теперь получим данные из таблицы, которые были добавлены при ее создании. Для этого выполним следующий код:
И внизу программы в поле Data Output мы увидим в табличном представлении те данные, которые ранее были добавлены.
pgAdmin для начинающих
В данной работе показаны начальные приемы работы с pgAdmin
Примечание 1: ЛКМ, ПКМ — левая, правая кнопка мыши
Вопросы:
01 Установка PostgreSQL и PgAdmin
02 Создание базы данных в правильной кодировке
03 Создание таблиц в pgAdmin
04 Создание столбцов
05 PgAdmin — первичный ключ в PostgreSQL
06 Создание базовых колонок
07 Код таблицы DDL в PostgreSQL
08 Поле ID Autoincrement
09 Создание Foreign key – внешний ключ
10 Взаимодействие через внешний ключ
11 pgAdmin — добавление тестовых данных вручную
01 Установка PostgreSQL и PgAdmin — https://www.postgresql.org/download/
Вместе с PostgreSQL уставливается и PgAdmin
Во время установки задаем пароль суперпользователя (мастер-пароль) базы данных.
Locale — English, United States
Проверить установку PostgreSQL можно так: в Windows находим Службы и в них
проверяем наличие службы PostgreSQL.
Служба_PostgreSQL.png
После установки PgAdmin появляется в меню
Пуск>Все программы>PostgreSQL(N)
При запуске программы нужно указать мастер-пароль, который
вы указывали при установке.
Также этот пароль понадобится при заходе в базу данных.
Проверка кодировки
Открываем Database > ПКМ на postgres > Properties > Definition
Параметр Encoding = UTF8
Важно обратить внимание на этот параметр и именно поэтому
мы выбирали локаль
Locale — English, United States
Если у вас кодировка UTF-8, то у вас будет меньше проблем
с экспортом и импортом данных.
Проверка_кодировки.png
02 Создание базы данных в правильной кодировке
Если кодировка вашей системы отличается от UTF-8:
вы все равно сможете создать свою базу c UTF-8
ПКМ На Databases > Create > Database
Во вкладке Definition можно вручную выбрать
Encoding — UTF — 8
Иногда система может потребовать использовать шаблон template0,
тогда его вы тоже можете выбрать в этой вкладке:
База_данных_в_ UTF-8.png
Также вам может потребоваться перенести все ваши таблицы
из старой базы в новую, это вы можете сделать через меню
Tools в верхней части программы — комады Backup и Restore
03 Создание таблиц в pgAdmin
На примере базы данных postgres находим Tables,
ПКМ > Create > Table
Создать_таблицу.png
Задаем название таблицы, эти названия не должны
пересекаться с ключевыми словами баз данных, иначе могут быть
неожиданности, Owner — postgres, в комментариях указываем
назначение таблицы и другую полезную информацию.
Если после создания таблица не отобразилась в списке Tables,
можно обновить данные
ПКМ на Tables > Refresh
Для примера создадим несколько таблиц:
category, priority, task, user_data
04 Создание столбцов
Создать столбцы можно двумя способами,
Кликаем на таблицу ПКМ > Properties > Columns
(можно создать сразу несколько колонок) или
Кликаем на таблицу ПКМ > Create > Column
(создаем по одной колонке)
Создать_колонки.png
Добавим следующие колонки в таблицу user_data:
почта — обязательное значение
пароль — обязательное значение
имя — обязательное значение
Колонки_для_user_data.png
После сохранения колонок можно сделать
ПКМ на таблице > Refresh
и увидеть наши колонки, развернув таблицу и подраздел
Columns.
Для редактирования колонок в дальнейшем
мы точно также делаем:
ПКМ на таблице > Properties > Columns
и в окне, где показаны все столбцы кликаем на значок
редактирования слева от нужного столбца.
05 PgAdmin — первичный ключ в PostgreSQL
В таблице user_data создаем новое поле id типа bigint
(аналог long в Java). Добавляем полю параметр Not Null и
primary key.
id_первичный_ключ.png
Когда мы накладываем ограничение Primary key
в поле id мы можем сохранять только уникальные значения.
Все остальные поля в строке могут быть одинаковыми,
важно, чтобы id был разный. Столбец с Primary key
чаще всего обозначается PK
id_разный.png
06 Создание базовых колонок
Заполните колонками таблицу task:
title — text — not null
completed — numeric — not null
task_date — timestamp without time zone
id — bigint — not null — Primary key
Увидеть результат создания можно так:
Делаем Refresh, через ПКМ на таблице > Refresh
и разворачиваваем таблицу ЛКМ до колонок
Вид_колонок.png
Заполните столбцами таблицу priority:
title — text — not null
color — text — not null
id — bigint — not null — primary key
Заполните столбцами таблицу category:
title — text — not null
id — bigint — not null — primary key
07 Код таблицы DDL в PostgreSQL
Мы создали таблицы в pgAdmin средствами самой программы,
вводя в поля нужные значения и используя переключатели.
Тоже самое можно сделать с помощью
DDL – Data Definition Language (язык описания данных)
Это так называемый SQL запрос для создания таблицы.
Мы можем посмотреть этот код (в режиме чтения), выбрав слева
нужную таблицу, а справа, соотвестствующую вкладку.
В дальнейшем вам нужно научиться создавать таблицы и тем
и другим способом
Код_создания_таблицы.png
08 Поле ID Autoincrement
Автоинкременент или автоматическая нумерация —
это автоматическое увеличение значения в колонке
(чаще всего id) средствами самой базы данных.
Это удобно и часто используется. Программист думает
о заполнении таблицы данными, а нумерацию
делает сама БД.
Давайте отредактируем настройки
наших таблиц и настроим столбец id на автонумерацию.
Автонумерация.png
Для этого как обычно:
ПКМ на названии таблицы > Properties > Columns
Слева от поля id нажимаем значок редактирования,
во вкладке Constraints выбираем Type — IDENTITY,
а Identity — ALWAYS.
После сохранения можно закрыть окно,
перейти во вкладу SQL и посмотреть как изменился
код, который задает создание столбца id.
id_автонумерация.png
Теперь он будет автоматически менять свое значение
при каждом добавлении новой строки с данными.
09 Создание Foreign key – внешний ключ
Foreign key — внешний ключ или можно сказать ссылка на другую таблицу.
Попробуем сделать ссылки из таблицы task на таблицу category и
priority. Смысл этих ссылок — это выражение в таблице,
какая категория у задачи и какой приоритет.
Создаем ключ (поле, столбец, колонку) category_id на таблицу category
и поле id через вкладку Columns и сохраняем его.
Пока поле никуда не ссылается
ПКМ на task > Properties > Columns
Создаем_category_id.png
Следующим шагом заходим в Ограничения и выбираем Внешние ключи
ПКМ на task > Properties > Constraints > Foreign Key
Вводим название Ограничения — category_fkey, далее редактируем это поле
(значок карандаша слева), вкладка Columns,
Local column — указываем нашу колонку — category_id
поле References — надо указать таблицу, на которую ссылаемся
поле Referencing — указываем колонку на которую ссылаемся
Далее нажимаем «+» на уровне Columns и Save.
Связываем_ключ_с_таблицей.png
После закрытия окна, во вкладке SQL можем наблюдать
изменения в коде создания — CONSTRAINT category_fkey.
Теперь при создании очередной строки в task мы должны указывать
только существующие id из таблицы category иначе строку создать не
получится.
10 Взаимодействие через внешний ключ
Давайте добавим внешние ключи для остальных таблиц в pgAdmin.
Начнем с колонки priority_id. Делаем все тоже самое, создаем и сохраняем
сохраняем внешний ключ, потом «привязываем» его к другой таблице.
Тоже самое делаем с таблицей user_data. В таблице task создаем
для неё ключ user_id. Этот ключ должен содержать параметр not null.
Задание должно иметь владельца, иначе оно бессмысленно.
Также это значит, что мы не можем добавить данные в таблицу task
без заполнения таблицы user.
Для таблицы task в pgAdmin должно получиться что-то такое:
task_внешние_ключи.png
В таблице category должны храниться все назначенные категории
всеми пользователями, поэтому в таблице category тоже нужно сделать
внешний ключ на таблицу пользователей. Таблицу priority
связываем с таблицей user_data аналогичным способом.
11 PgAdmin — добавление тестовых данных вручную
После выстраивания всей системы базы данных бывает полезно проверить
правильность настроек добавлением некторого количества тестовых данных.
Для этого сначала заполняем данными таблицу user_data,
т.к. от неё зависят все остальные. Для этого:
заполнение_данными.png
ПКМ на user_data > View/Edit Data > All Rows
и заполняем ячейки вручную в правой нижней части окна
согласно ограничениям, которые мы вписали для этой таблицы
(например id не заполняем, он заполняется автоматически).
После внесения нескольких тестовых пользователей,
их данные можно использовать в других таблицах.
После user_id мы заполняем несколько значений в category и
priority, а потом на основании этих трех таблиц мы можем
сделать несколько тестовых строк в task
в pgAdmin У нас есть возможность каскадно удалять данные.
Если мы хотим удалить в user_data всех пользователей
и удалить все данные в остальных таблицах, которые ссылаются
на эту таблицу мы можем сделать так:
ПКМ на user_data > Truncate > Truncate Cascade
Примечание 2: Спасибо вам за проявленный интерес к данной работе
Вы можете форкнуть данную работу и дополнить или исправить
что-то по своему усмотрению (переделать под себя).