Pgadmin руководство пользователя

The pgAdmin documentation for the current development code, and recent releases of the application is available
for online browsing. Please select the documentation version you would like to view from the options below.

The documentation is automatically imported from the pgAdmin GIT
source code repository, and is only available in English.

pgAdmin 4

Note

Postgres, PostgreSQL and the Slonik Logo are trademarks or
registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

© Copyright 2002 — 2016, The pgAdmin Development Team.
Created using Sphinx 1.4.8.

Установка

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

Первый шаг – установить 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

pgAdmin4 это популярное приложение для управления Postgres базы данных. Это приложение поддерживает все типы функций PostgreSQL. Три основные части pgAdmin4 клиент строка меню pgAdmin, Элемент управления деревом pgAdmin и управление браузером с вкладками. Каждая часть используется для выполнения различных типов задач управления. Пользователь может легко создать нового пользователя или роль и базу данных postgres, используя Элемент управления деревом pgAdmin. В этом руководстве показано, как создать нового пользователя и базу данных с таблицами под этим пользователем.

Перед тем, как начать это руководство, вам необходимо подтвердить, что pgAdmin4 установлен и правильно работает в вашей операционной системе Ubuntu. Если pgAdmin4 не установлен в вашей системе, вы можете выполнить шаги из следующего руководства, чтобы установить pgAdmin4 сначала и запустите этот урок.

Установите PgAdmin4 в Ubuntu

После успешной установки pgAdmin4, откройте следующую ссылку в любом браузере, чтобы открыть pgAdmin4 клиент.

http://localhost: 5050

Если развернуть Базы данных и Вход / Групповые роли разделы из Элемент управления деревом pgAdmin тогда будет показан весь ранее созданный список баз данных и роли. Здесь одна база данных с именем «Postgres» и две роли входа / группы, ‘Pg_signal_backend’ и «Postgres» созданы во время pgAdmin4 монтаж.

Каждая база данных связана с конкретным пользователем или группой пользователей. Вы можете создать базу данных, связавшись с любым существующим пользователем / группой или любым новым пользователем после создания. В этом руководстве новый пользователь по имени ‘админ’Создан со всеми привилегиями. Выполните следующие шаги, чтобы создать нового пользователя.

Шаг 1:

Щелкните правой кнопкой мыши на Вход / Групповые роли, Выбрать Создавать и нажмите на Вход / Групповые роли… для создания нового пользователя.

Шаг 2:

Появится следующее диалоговое окно Create. Тип ‘админ в Имя поле в качестве имени пользователя и нажмите Определение таб.

Шаг 3:

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

Шаг 4:

Чтобы установить все разрешения на админ пользователь делает все параметры для ‘да’. Нажмите ‘Сохранить’ кнопку для создания пользователя.

«Админ» запись пользователя будет отображаться в Вход / Групповые роли раздел.

Создать новую базу данных

Предположим, вам нужно создать базу данных с именем ‘Online_Library ’. Выполните следующие шаги для создания базы данных.

Шаг 1:

Щелкните правой кнопкой мыши на Базы данных, Выбрать Создавать и нажмите База данных… возможность создания новой базы данных.

Шаг 2:

Следующее диалоговое окно появится для создания новой базы данных. Тип «Online_Library» в База данных поле и выберите «Admin» как владелец базы данных. Нажмите ‘Сохранить’ кнопку для создания базы данных.

«Online_Library» база данных будет показана в разделе Базы данных.

Создать таблицы

Предполагать, «Online_Library» база данных содержит две таблицы ‘Book_type’ и ‘Список книг’. Таблицы с примерами данных приведены ниже.

Таблица: book_type

Я БЫ	Категория	Тип
1	CSE	Программирование
2	MBBS	Анатомия
3	BBA	Финансы

Согласно данным таблицы, book_type таблица имеет три поля, которые Я БЫ, Категория и Тип. Здесь значение Я БЫ поле является числовым и будет увеличиваться автоматически, Категория и Тип поля являются строковыми. Первичный ключ этой таблицы Я БЫ и все поля обязательны.

Таблица: book_list

Я БЫ	Book_Name	Имя автора	Версия	Type_ID
E001	Java — полный справочник	Герберт Шильдт	7th	1
M001	Анатомические поезда	Томас В. Майерс	3rd	2
E002	Поваренная книга Python	Брайан Джонс и Дэвид Бизли	3rd	1
B001	Финансовый учет для BBA	Д-р С. Н. Махешвари и К. А. Шарад К. Махешвари	3

Согласно данным таблицы, Список книг таблица имеет 5 полей, которые ID, имя книги, имя автора, Версия и Type_ID. Здесь, ID, Book_Name и Имя автора поля не могут быть пустыми и Версия поля могут быть пустыми. Я БЫ поле является первичным ключом и Type_ID поле является внешним ключом для этой таблицы. Теперь выполните следующие шаги, чтобы создать эти таблицы в «Online_Library» база данных с использованием pgAdmin4.

Шаг 1:

Нажмите на ‘Схемы’Раздела‘Online_Library ’ база данных. Расширять «Общественный» раздел и узнайте «Таблицы» раздел.

Шаг 2:

Щелкните правой кнопкой мыши «Таблицы», выберите ‘Создавать’ и нажмите ‘Таблица…’ вариант.

Шаг 3:

Появится следующее диалоговое окно для создания новой таблицы. Тип ‘Book_type’ в Имя поле выберите Владелец таблицы (здесь админ выбран как владелец) и выберите Схема и Табличное пространство.

Шаг 4:

Выбирать «Столбцы» вкладку и нажмите на ‘+’ кнопку, чтобы создать новое поле для таблицы.

Шаг — 5:

Добавьте три обязательных поля ‘Book_type’ таблицу, как в следующем диалоговом окне. Задавать ‘bigserial’В качестве типа данных (для добавления атрибута автоматического увеличения) и установите‘да‘ в ‘Ненулевой’ и ‘Первичный ключ’ поля Я БЫ столбец. Значение Категория и Тип столбец может отличаться. Так что выберите ‘характер меняющийся » как тип данных для этих полей. Нажмите ‘Сохранить’ кнопку для создания таблицы.

Шаг 6:

Снова откройте следующее диалоговое окно для создания другой таблицы с именем ‘Список книг’. Тип ‘Список книг’ в Имя поле и выберите другие значения других полей, например шаг 3.

Шаг 7:

Нажмите на Столбец вкладку и добавьте поля в соответствии с требованиями таблицы. Здесь, Я БЫ столбец будет содержать данные фиксированной длины (4 символа). За это, ‘персонаж’ тип данных выбран для Я БЫ столбец. Значение Book_Name, Имя автора и Версия может изменяться. Так, ‘характер меняющийся » для этих столбцов выбран тип данных. ‘Ненулевой’ опция установлена ​​на ‘Да’ для Версия столбец для принятия Значение NULL значение, когда данные не будут отправлены.

Шаг 8:

Нажмите ‘Ограничения вкладка для установки первичного и внешнего ключей. Эта вкладка содержит еще 5 вкладок. Первая вкладка ‘Первичный ключ’. На этой вкладке будет отображаться информация о первичном ключе таблицы. Один первичный ключ установлен на предыдущем шаге, который отображается здесь. Если вы хотите добавить или удалить какой-либо первичный ключ, вы можете выполнить эту задачу на этой вкладке.

Шаг 9:

Эта таблица содержит внешний ключ на основе информации таблицы, упомянутой выше. Нажмите ‘Иностранный ключ’ вкладка для определения внешнего ключа. Нажмите кнопку «+», чтобы добавить внешний ключ.

Шаг-10:

Щелкните по кнопке Edit (), чтобы открыть вкладку для создания внешнего ключа.

Шаг 11:

Введите имя внешнего ключа в Имя поле. Здесь, ‘Book_Type ’ устанавливается как имя внешнего ключа. Далее нажмите Столбцы таб.

Шаг 12:

Выбирать Локальный столбец, использованная литература и Ссылка из раскрывающегося списка. Для этой таблицы Type_ID поле является внешним ключом и установлено как Локальный столбец. ‘book_typeТаблица выбрана как Справка стол и Я БЫ поле ‘book_type’Выбран как Ссылка столбец. После настройки нажмите кнопку «+», чтобы добавить внешний ключ.

Шаг 13:

Информация о добавленном внешнем ключе будет отображаться внизу. Нажмите на ‘Действие’, Чтобы добавить конкретное действие к Обновить и Удалить данные справочной таблицы.

Шаг 14:

Выбирать ‘КАСКАД’Вариант для операции обновления и удаления. Когда Я БЫ значение book_type таблица обновит или удалит связанную запись (и) Список книг таблица будет произведена. Нажмите ‘Сохранить’ кнопку, чтобы завершить задачу создания таблицы.

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

После создания базы данных и таблиц вы можете выполнять различные типы команд SQL, такие как выбор, вставка, обновление и удаление в определенной таблице. Вы также можете выполнять другие операции с базой данных, такие как создание функции, представления, триггера и т. Д. в pgAdmin4. Надеюсь, вы сможете выполнять основные операции с базой данных в pgAdmin4 после прочтения этого руководства.