Практическая работа No1

Начало работы с PostgreSQL

Цель работы: познакомиться с особенностями применения SQL в инжиниринге данных

Теоретическое обоснование

Системные требования

PostgreSQL 17 можно установить не на все версии Windows, в частности официально поддерживаются следующие версии и только 64 битные:

•   Windows Server 2012 R2;
•   Windows Server 2016;
•   Windows Server 2019.Как видим, в официальном перечне нет Windows 10, однако установка данную систему проходит без проблем, как и последующее функционирование PostgreSQL.

  Кроме этого есть и другие требования:
   Процессор как минимум с частотой 1 гигагерц;
   2 гигабайта оперативной памяти;
   Как минимум 512 мегабайт свободного места на диске (рекомендуется

  больше для установки дополнительных компонентов);
   Также рекомендовано, чтобы все обновления операционной системы Windows

  были установлены.

  Ссылка для скачивания и установки: PostgreSQL Downloads
  Выберите вам нужную операционную систему, на которую будете устанавливать PostgreSql, например, Windows, (рис.1)

  Рис. 1. Выбор ОС для установки
  На следующей странице выберите ссылку загрузки для инсталляции (рис.2)

Рис. 2. Выбор ссылки загрузки для инсталяции

На следующей странице вам следует выбрать версию PostgreSQL, загрузить инсталляцию на свой диск и установить программу, следуя ее диалоговым окнам. Перед тем как двигаться дальше, давайте разберемся в основах архитектуры системы PostgreSQL. Составив картину взаимодействия частей PostgreSQL, будет проще понять материал этой части. Говоря техническим языком, PostgreSQL реализован в архитектуре клиент-сервер. Рабочий сеанс PostgreSQL включает следующие взаимодействующие процессы (программы):

 Главный серверный процесс, управляющий файлами баз данных, принимающий подключения клиентских приложений и выполняющий различные запросы клиентов к базам данных. Эта программа сервера БД называется postgres.

 Клиентское приложение пользователя, желающее выполнять операции в базе данных. Клиентские приложения могут быть очень разнообразными: это может быть текстовая утилита, графическое приложение, веб-сервер, использующий базу данных для отображения веб страниц, или специализированный инструмент для обслуживания БД. Некоторые клиентские приложения поставляются в составе дистрибутива PostgreSQL, однако большинство создают сторонние разработчики.

Как и в других типичных клиент-серверных приложениях, клиент и сервер могут располагаться на разных компьютерах. В этом случае они взаимодействуют по сети TCP/IP. Важно не забывать это и понимать, что файлы, доступные на клиентском компьютере, могут быть недоступны (или доступны только под другим именем) на компьютере-сервере.

Сервер PostgreSQL может обслуживать одновременно несколько подключений клиентов. Для этого он запускает («порождает») отдельный процесс для каждого подключения. Можно сказать, что клиент и серверный процесс общаются, не затрагивая главный процесс postgres. Таким образом, главный серверный процесс всегда работает и ожидает подключения клиентов, принимая которые, он организует взаимодействие клиента и отдельного серверного процесса. (Конечно всё это происходит незаметно для пользователя, а эта схема рассматривается здесь только для понимания).

Методика выполнения

Далее мы будем использовать командную строку (рис. 3) в качестве клиентского приложения для взаимодействия с сервером.

Рис. 3 Командная строка
После запуска SQL Shell нас (рис.4) встречает надпись Server [local|host]

Рис. 4. Запуск SQL Shell

В квадратных скобках введено стандартное значение. Вводим Enter. Появляется надпись (рис. 5) Database [postgres]:

Рис. 5. Надпись Database [postgres]

Нажимаем Enter.

Следующая строка (рис. 6) Porf [5432]: Порт по умолчанию — 5432. Если PostgreSQL устанавливается впервые, то он скорее всего свободен. Если окажется, что этот порт уже занят другим экземпляром PostgreSQL, то можно указать другое значение, например 5433.

Рис. 6. Строка Porf [5432]

Нажимаем Enter.

Появляется строка ввода логина и потом строка (рис. 7) пароля. Пароль вводится тот, который был введен при установке.

Рис. 7. Строка ввода логина
В строке Username [postgres]: нажимаете Enter. В строке вводите Пароль вводится

тот, который был введен при установке. При вводе пароля вы не увидите символов. Это так задумано для защиты пароля от посторонних глаз.

Нажимает Enter.

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

Рис. 8. Версия postgres
В строке консоли наберите следующую команду: \! chcp 1251 (рис. 9)

Рис. 9. Команда: \! chcp 1251
Таким образом мы изменили кодовую страницу на 1251. Данную команду

рекомендуется вводить в начале каждой сессии. Заметим, что команды, начинающиеся с обратного слеша (\) обращаются не к базе данных, а к окну консоли. Чтобы узнать список всех доступных команд (рис. 10), нужно ввести команду \?.

Рис. 10. Ввод команды \?.
Появится список всех команд, разделенных на разделы. Просмотрите эти команды.

Введите команду \l – это команда. Эта команда позволит (рис. 11) вам увидеть все доступные базы данных.

Рис. 11. Ввод команды \l
По умолчанию их будет уже три, но для обучения давайте создадим свою

собственную базу.
Для создания базы данных необходимо ввести команду CREATE DATABASE. CREATE DATABASE starter;
starter – название новой базы данных. В конце команды необходимо поставить

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

Рис. 12. Starter – название новой базы данных
Повторим команду \l и увидим в списке уже новую созданную нами (рис. 13) базу

данных.

Рис. 13. Новая база данных
Для работы с этой базой данных нам необходимо сначала присоединиться к ней. В

начале строки консоли мы сейчас видим postgres=# — это название базы к которой мы сейчас подключены. Чтобы подключиться к другой базе данных (рис. 14) используем команду \с starter

Рис. 14. Подключение к другой базе
При верно введенной команде видим сообщение об успешном подключении к базе

данных. Здесь мы видим название текущей базы и текущего пользователя. И как можно заметить начало строки в консоле заменилась на starter=#

Чтобы вернуться обратно нужно ввести \с postgres Если необходимо удалить какую- нибудь базу данных, то используется команда DROP DATABASE. Она полностью удаляет выбранную базу данных и использовать ее нужно с осторожностью.

Не пытайтесь удалять одну из трех первоначальных баз данных, так как это может вызвать необратимые последствия и придется переустанавливать postgres.

Критерии оценки:

Вопросы опроса по теме:

1. Дайте определение понятий «база данных», «предметная область»
2. Каковы предпосылки создания баз данных
3. Определите соотношение понятий «информация» и «данные».
4. Какие технические средства используются для создания баз данных.
5. Дайте определение системе управления базами данных.
6. Определите основные функции и назначение СУБД
7. Перечислите основные категории пользователей баз данных.
8. Дайте определение понятий «база данных», «предметная область»
9. Каковы предпосылки создания баз данных
10. Определите соотношение понятий «информация» и «данные».
11. Какие технические средства используются для создания баз данных.
12. Дайте определение системе управления базами данных.
13. Определите основные функции и назначение СУБД
14. Перечислите основные категории пользователей баз данных.
15. Какие группы операторов выделяются в составе языка SQL?

17. Для чего нужны внешние ключи в реляционных таблицах?
18. Что такое потенциальный ключ?Практическая работа No2

Цель работы: освоить интерфейс pgAdmin4 и использовать при работе с данными Теоретическое обоснование

pgAdmin 4 это новая реализация бесплатного инструмента управления СУБД PostgreSQL. pgAdmin 4 используется для написания SQL запросов, разработки процедур, функций, а также для администрирования PostgreSQL. Этот инструмент был установлен в комплекте инструментов postgres. Давайте посмотрим на то, как мы начать работать с ним.

Методика выполнения

1. Запустить pgAdmin4 можно (рис. 1) через меню пуск. Он находится на вкладке Пуск-PostgreSQL14-pgAdmin4.

Рис. 1. Запуск pgAdmin4

2. После запуска, вас встретит окно ввода (рис. 2) пароля. Сюда необходимо ввести тот пароль, что вы указали при установке : “********”.

16. Дайте неформальное определение основных понятий реляционной модели данных: отношение, кортеж, атрибут.

Рис. 2. Окно ввода пароля

3. Если вы забыли пароль, то его можно поменять, нажав на кнопку Reset Master Password. В окне Reset Master Password вводим (рис. 3) новый пароль и применяем изменения.

Рис. 3. Смена пароля

4. После входа, вас встретит пустой экран, без какой-либо информации. Мы не видим её так как не подключены ни к одному серверу. Нажмите на вкладку Servers в левой части экрана, а затем сделайте двойной щелчок по вкладке PostgreSQL14.

5. Нам предлагают повторно (рис. 4) ввести пароль. Сюда мы тоже вводим то, что придумали при установке: “***”.

Рис. 4. Повторный ввод пароля
6. После ввода пароля мы сможем увидеть все (рис. 5) созданные нами базы

данных. Для отображения метрик по нужной вам базе данных, нажмите на неё. Они находятся под вкладкой «Databases».

Рис. 5. Ввод пароля

8. Для каждой из этих баз можно просматривать текущие метрики (рис. 6) по запросам во вкладке Dashboard. Эта вкладка выбрана у вас автоматически.

Задание

Рис. 6. Просмотр текущих метрик

1. Выполните процедуру установки СУБД PostgreSQL в среде выбранной вами операционной системы.

2. Ознакомьтесь с утилитой psql с помощью встроенной справки, а также с помощью справки, вызываемой по команде

psql –help
3. Кроме утилиты psql существуют и другие универсальные программы для работы

с сервером баз данных PostgreSQL, например, pgAdmin. Это мощная утилита с графическим интерфейсом. Самостоятельно установите программу pgAdmin и изучите основные приемы работы с ней.

4. Разверните учебную базу данных. Попробуйте подключиться к ней с помощью утилиты psql. Для выхода из утилиты используйте команду \q.

Вопросы опроса по теме:

1. Зачем нужен Docker?
2. Что такое PostgreSQL?
3. Зачем использовать PostgreSQL в контейнере?
4. Как подключиться к PostgreSQL в контейнере?

Критерии оценки:

Практическая работа No3

Цель работы: освоить работу с таблицами в PostgreSQL

Методика выполнения

Откроем консоль SQL Shell и повторим все процедуры подключения. Подключимся к ранее созданной базе starter (рис. 1) с помощью уже знакомой команды \с starter.

Рис. 1. Подключение к базе данных
Создадим таблицу в базе с использованием (рис. 2 и рис. 3) команды CREAT

TABLE.

Рис. 2. Создание таблицы

Рис. 3. Создание таблицы

При успешном создании таблицы после нажатия Enter будет выведено сообщение CREATE TABLE. Чтобы посмотреть какие таблицы (рис. 4) и какие отношения между ними существуют используется команда \d выводит список отношений между схемами. Введите эту команду

Рис. 4. Список отношений и таблиц

Появится таблица со всеми нами созданными элементами таблицы и последовательностью.

Если нам необходимо узнать (рис. 5) информацию о таблице student, то используется команда \d [имя_таблицы], которая показывает характеристики таблицы c данным именем.

Пример: \d student;

Рис. 5. Характеристики таблицы

Для удаления таблицы используется команда: DROP TABLE

Синтаксис этой команды следующий: Команда DROP TABLE [имя_таблицы] удаляет таблицу с названием [имя_таблицы].

Пример:

DROP TABLE student; Выполните эту команду и удалите таблицу student. В этом задании мы научимся управлять данными внутри таблицы. В предыдущем задании мы удалили таблицу student. Давайте пересоздадим эту таблицу и заполним ее данными.

Но для начала давайте отобразим ее содержимое с использованием команды SELECT * FROM [имя_таблицы]. Эта команда выводит в консоль всё содержимое таблицы с данным именем.

Пример: SELECT * FROM student;
Знак * означает выбор всех столбцов (рис. 6).

Рис. 6. Выбор всех столбцов

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

Давайте заполним ее какими-нибудь данными. Вставляем данные построчно, т.е. по одному набору за раз. Это можно сделать с помощью команды INSERT INTO [имя_таблицы] ([перечисление имён столбцов]) VALUES ([наполнение для перечисленных столбцов]) помещает данные в таблицу. [перечисление имён столбцов] в данном случае уточняет, в какие столбцы данные будут записаны, а [наполнение для перечисленных столбцов] помещает данные в нужном порядке.

Пример : INSERT INTO student(name, surname, gender, mobile, birthday) VALUES (‘Michael’, ‘Scott’, ‘male’, ‘+79884561237’, ‘2000-04-12’);

Обратите внимание (рис. 7) на формат ввода даты дня рождения.

Рис. 7. Ввод даты рождения

После ввода Enter при удачном вводе данных появляется надпись INSERT 0 1 Давайте теперь проверим содержимое таблицы c помощью уже знакомой команды SELECT * FROM student;

И убедимся, что таблица содержит (рис. 8) только что введенные данные.

Рис. 8. Ввод данных

Обратите внимание, что поле id пустое. Это произошло из-за того, что мы не указали столбец id для заполнения его какими-либо данными. Мы заполнили одну строку, ну что делать, если нам надо заполнить большее количество строк. «Ручное» заполнение довольно трудоемкое. Для автоматической генерации данных можно воспользоваться специальными сайтами для генерации таблиц данными mockaroo.com, databasetestdata.com, generatedata.com или воспользоваться уже сгенерированным файлом, прикрепленный к этому заданию.

При генерации укажите число рядов 500 и задайте новой таблице имя student_auto. Для загрузки файла в базу в консоле введите команду \i
Синтаксис этой команды \i ‘[путь к файлу]’ позволит импортировать готовые

функции из файла на устройстве.
Пример : \i ‘D:/ИнжинирингДанных/Postgresql/students_auto.sql’ Обратите внимание

(рис. 9) на формат ввода пути к файлу.

Рис. 9. Формат ввода пути к файлу

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

Когда консоль забивается информацией, то необходимо ее очистить. Для этого используется команда \! cls

Выберем все строки (рис. 10) новой таблицы student_auto SELECT * FROM student_auto:

Рис. 10. Выбор строк

Мы можем уточнить (рис. 11), какие именно столбцы мы выбираем, заменив * на перечисление имен столбцов.

Пример: SELECT name, surname FROM student;

Рис. 11. Выборка столбцов

Выборка данных. Скрипты

В данном задании разберем процесс выборки данных. Для начала сделаем выборку всех данных из нашей таблицы student.

Выбираем все данные командой SELECT *FROM, но в конце добавляем новые критерии SELECT *FROM student_auto OFFSET 10 LIMIT 50: Командой OFFSET мы пропускаем 10 строк с начала таблицы. Количество выводимых (рис. 12) строк ограничим командой LIMIT.

Рис. 12. Выборка данных
Как мы видим id начинается с 11 позиции (рис. 13) и выведены только 50 строк.

Рис. 13. Выборка данных

Итак, LIMIT Команда LIMIT [целое_число] ограничивает (рис. 14) количество выводимых строк до значения числа.

Пример: SELECT * FROM travelers LIMIT 25;

Рис. 14. Команда LIMIT

OFFSET Команда OFFSET [целое_число] пропускает количество записей в таблице с начала, равное значению числа. Пример: SELECT surname FROM travelers OFFSET 15;

Существует и другая команда (рис. 15) для ограничения вывода строк. FETCH FIRST [] ROW ONLY Команда FETCH FIRST [целое_число] ROW ONLY показывает только количество первых строк, равное значению числа

Пример: SELECT name, email FROM travelers FETCH FIRST 20 ROW ONLY;

Рис. 15. Команда для ограничения вывода строк

Выполним запрос (рис. 16) с этой командой к таблице student_auto. SELECT * FROM student_auto OFFSET 100 FETCH FIRST 10 ROW ONLY; \

Рис. 16. Выполнение запроса

Как мы видим, полученный результат идентичен результату команды LIMIT.

Не однократно мы видели результаты команды SELECT со «*»в виде копии таблицы, которая хранится на сервере базы данных. Но что, ели мы хотим вывести не все столбцы, а некоторые из них. В таком случае «*» нужно заменить (рис. 17) на уточняющее условие. Давайте попробуем вывести столбцы только имени и фамилии. Для этого замени «*» на name, surname, разделенных запятой.

SELECT name, surname FROM student_auto;

Рис. 17. Выполнение запроса

В результате, мы видим только те столбцы, которые указали. Сделаем еще один (рис. 18) запрос по полю birthday SELECT birthday FROM student_auto;

Рис. 18. Выполнение запроса

Чтобы задать другой порядок вывода данных (рис. 19) используется команда ORDER BY [ ] ASC/DESC Команда ORDER BY [имя_столбца] показывает выборку, отсортированную по выбранному столбцу. С помощью приписки ASC или DESC можно расположить выборку в возрастающем или убывающем порядке соответственно.

Пример: SELECT name, surname FROM travelers ORDER BY name ASC;

Рис. 19. Порядок вывода данных

Выполним запрос (рис. 20) по полю birthday с упорядочением в убывающем порядке: SELECT * FROM student_auto ORDER BY birthday DESC;

Рис. 20. Выполнение запроса
Выполним еще один (рис. 21) запрос по полю name, но расположим данные по

возрастанию. SELECT name FROM student_auto ORDER BY birthday ASC;

Рис. 21. Выполнение запроса

В результате мы получим список фамилий в алфавитном порядке. Но в этом списке, видим дубликаты. Выполним запрос, который уберет дубликаты (рис. 22) с помощью команды DISTRING. В следующем запросе введем команду DISTINCT, которая уберет дублирующие значения.

SELECT DISTINCT birthday FROM student_auto ORDER BY birthday ASC;

Рис. 22. Выполнение запроса

Можно уточнить запрос и например, вывести, данные (рис. 23) только о девушках. Для этого используется команда WHERE Команда WHERE логическое_выражение сравнивает значения в строках таблицы с выбранной логикой. В зависимости от верности или не верности логического выражения, команда включит строку в выборку.

Пример запроса: SELECT * FROM student_auto WHERE gender = ‘Female’;

Рис. 23. Выполнение запроса

Как видим из последнего рисунка, мы получили данные только о девушках. Для составления логических выражений используются, следующие знаки:

=
<>
<
<=
>
>= BETWEEN IS NULL

Равно
Не равно Меньше Меньше или равно Больше
Больше или равно Между двумя значениями Отсутствует запись

Логические утверждения WHERE могут быть записаны в виде составных условий выбора данных. Например, запрос SELECT * FROM student_auto WHERE gender = ‘Female’ and name=’Stavros’; позволяет (рис. 24) сузить диапазон запроса.

Рис. 24. Выполнение запроса

Далее, создадим еще одну таблицу методом генерации, как это было сделано на предыдущих уроках или используйте готовую таблицу из файла, прикрепленного к работе travelers.sql. Проведем импорт таблицы из данного (рис. 25) файла в нашу БД, используя знакомую нам уже команду \i

Вывод INSERT 0 1 говорит об успешном импорте данных.

Рис. 25. Импорт данных
Давайте проверим успешности импорта таблицы с помощью выборки всех (рис.

26) данных таблицы travelers.sql SELECT * FROM travelers;

Рис. 26. Выборка всех данных
Как результат, мы видим точную копию таблицы, которая хранится на сервере.

Допустим нам нужна информация из таблицы travelers по путешественникам из разных стран. Для этого после слова WHERE после слова in мы в скобках через запятую добавляем список стран, которые хоим вывести в запросе. Ключевое слово in сравнивает значения в скобках со значениями с поле country.

Пример запроса SELECT * FROM travelers WHERE country in (‘Russia’, ‘Kongo’, ‘Israel’); Консоль выведет значения (рис. 27) с теми данными, которые были перечислены в скобках.

Рис. 27. Выполнение запроса

Попробуем сделать запрос (рис. 28) по буквам страны в ее названии. Для этого воспользуемся ключевым словом BETWEEN. SELECT * FROM travelers WHERE country BETWEEN ‘A’ and ‘D’;

Рис. 28. Выполнение запроса
В результате в консоль выведутся значения, которые начинаются с А, В, С, D.

Можно отсортировать даже по последовательности символов. Например, нам нужны путешественники у которых почта заканчивается только на .edu. Используя команду LIKE мы оставим только те строки, в которых (рис. 29) почта имеет символы внутри одинарных кавычек. Знак % — это специальный символ. Он заменяет весь текст, который следует до искомой нами комбинации. SELECT * FROM travelers WHERE email LIKE ‘%.edu’;

Рис. 29. Применение команды LIKE
Можно сделать комбинированные запросы. Объединим запрос (рис. 30) по

определенным странам и с выполненным, только что запросов с почтой, только выберем теперь строки с почтой .com SELECT * FROM travelers WHERE (country BETWEEN ‘A’ and ‘D’) AND (email LIKE ‘%.com’);

Рис. 30. Комбинированный запрос
Давайте теперь попробуем посчитать (рис. 31, 32) на сколько уникальны имена

наших путешественников. Для этого используем команду COUNT. Команда COUNT(*) сосчитает количество одинаковых значений в столбце выборки до неё. Пример:

Рис. 31. Подсчет имени

Рис. 32. Подсчет имени
Как мы видим, функция COUNT (*) возвращает количество строк, возвращаемых

оператором SELECT, т.е. количество строк таблицы. Выполним другой запрос с (рис. 33) группировкой по полю name SELECT name, COUNT(*) FROM travelers GROUP BY name;

Рис. 33. Выполнение запроса

Давайте выполним запрос (рис. 34) и узнаем откуда путешественников больше всего. И воспользуемся командой HAVING Команда HAVING очень похожа на команду WHERE. Её основное отличие в том, что она применяется сразу к строкам, сгруппированным командой GROUP BY().

Пример:

Рис. 34. Выполнение запроса
SELECT name, surname FROM travelers GROUP BY count(name) > 1; Выполним

следующий (рис. 35) запрос: SELECT country, COUNT(*) FROM travelers GROUP BY country HAVING COUNT(*)>25;

Рис. 35. Выполнение запроса

Как мы видим, больше всего путешественников из Индонезии и Китая. Но для больших таблиц, такой просмотр очень неудобный для поиска наибольших значений. Давайте отсортируем (рис. 36) по убыванию, используя запрос: SELECT country, COUNT(*) FROM travelers GROUP BY country HAVING COUNT(*)>25 ORDER BY COUNT(*) DESC;

Рис. 36. Сортировка

Теперь давайте поучимся (рис. 37) переименовывать столбцы таблицы.

Рис. 37. Переименование
Допустим, мы хотим переименовать (рис. 38) столбец id на n, surname на s. Для

этого используется команда AS SELECT id As n, surname As s FROM travelers;

Рис. 38. Переименование

Вопросы опроса по теме:

1. Таблицы в SQL: типы и операции.

2. Что такое таблицы в SQL. 3. Обычные таблицы.

Критерии оценки: