postgresql обучение с нуля

ТОП-7 Лучших Курсов PostgreSQL с Онлайн Обучением

PostgreSQL — это открытая система управления базами данных (СУБД). Заработная плата специалиста по работе с этой системой по данным HH.ru, составляет от 65 000 до 300 000 рублей. Сегодня рассмотрим ТОП-7 лучших онлайн-курсов, на которых можно пройти обучение PostgreSQL с нуля.

Курс SQL и PostgreSQL для начинающих от BeOnMax

Образовательная онлайн платформа beONmax предлагает практических курс по SQL и PostgreSQL, рассчитанный на новичков. Программа включает 128 видеоуроков и 18 практических заданий по каждой теме курса. К каждому практическому заданию прилагается подробный видео-разбор, который помогает лучше понять теорию и допущенные ошибки. Полученные знания применимы не только для базы PostgreSQL, но и для других популярных баз: MySQL, Microsoft SQL Server, Oracle. Оплата курса производится единовременным платежом, размер которого зависит от срока доступа.

За время обучения вы узнаете, что такое базы данных, каких типов бывают данные. Научитесь устанавливать и настраивать PostgreSQL, создавать запросы и подзапросы, делать выборки, управлять ключами. Изучите логику и функции языка SQL, разберете основные ошибки и как с ними работать. Узнаете, как работать с массивами и индексами, группировать и импортировать данные.

Практический курс для новичков по SQL и PostgreSQL на Udemy

Авторский курс компании DevSchool предназначен для новичков, имеющих базовые навыки работы с компьютером. Программа состоит из 22 разделов и содержит самое необходимое для изучения языка программирования SQL и работы с СУБД PostgreSQL. Теоретическая часть представлена в виде видео-уроков, предусмотрены практические самостоятельные задания. Предоставляется пожизненный доступ к материалам курса.

Курс дает вводные данные по SQL, его концепции, реляционную модель. Вы узнаете, как инсталлировать PostgreSQL, проектировать базы данных, принципы работы с массивами и индексами. Научитесь делать выборки, соединения и представления. Узнаете, как работать с оконными функциями и транзакциями, управлять ключами и обрабатывать ошибки.

Полный курс PostgreSQL от Otus

Обширный углубленный курс по работе с PostgreSQL. Рассчитан на администраторов и разработчиков баз данных. Необходим опыт работы с базами, общее представление об SQL и знание Linux на уровне пользователя. Перед поступлением потребуют пройти предварительное тестирование, тест рассчитан на 30 минут. В случае если тест не пройден, вы не допускаетесь к обучению. Повторный доступ к тестированию предоставляется через 2 недели.

Вы научитесь эффективным методам работы с базой данных PostgreSQL: Узнаете как оптимизировать запросы, управлять доступом и профилировать базы. Научитесь работать с PostgreSQL не только в собственной физической инфраструктуре, но и в виртуальной: облачная инфраструктура AWS, GCP, Azure и кластер Kubernetes. Вы сможете оптимизировать работу баз данных под различные нужды.

SQL и получение данных от Нетологии

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

За время обучения вы познакомитесь с архитектурой и структурой баз данных. Освоите на практике язык запросов SQL, научитесь работать с PostgreSQL и Mongo DB. Сможете самостоятельно получать данные для аналитики из баз данных, группировать, фильтровать и импортировать/экспортировать данные.

Основы баз данных от GeekUniversity

Интенсивный курс, в ходе которого на конкретном примере интернет-магазина будут рассмотрены все этапы создания, настройки и функционирования базы данных с использованием инструментов SQL. Этот курс можно приобрести отдельно, либо изучить в составе других программ. Например, в программе обучения системных администраторов или DevOps-инженеров.

Во время обучения вы узнаете, какие бывают базы данных, чем они отличаются друг от друга, что такое первичный и внешний ключ, согласованность и консистентность данных. Научитесь устанавливать и настраивать работу базы MySql и ее графического приложения Mysql Workbench. Узнаете, как выгружать данные, фильтровать, обновлять, удалять и объединять их. Знания, полученные на курсе, позволять работать не только с MySql, но и другими базами, использующими язык SQL.

SQL для анализа данных от Скиллбокс

Курс разработан онлайн-университетом Skillbox и предназначен для начинающих. Программа содержит 44 онлайн-урока, сгруппированных по тематике в 9 модулей. Теоретические знания закрепляются практическими заданиями, основанными на реальных кейсах. Студентам предоставляется бесплатный доступ на 2 месяца к курсам технического или бизнес английского языка EnglishDom.

По итогам обучения вы освоите язык SQL и научитесь работать с основными базами данных MySQL, MS SQL и PostgreSQL. Узнаете, что собой представляют основные операторы и как при их помощи писать запросы на загрузку, обновление и удаление данных. Научитесь применять функции SQL для фильтрации, сортировки, группировки и объединения данных из разных источников. Сможете готовить данные для интеграции их в Excel в виде сводных таблиц и графиков, проводить аналитический анализ при помощи агрегатных функций.

Курс-тренажер по SQL для анализа данных

Школа SkillFactory предлагает необычный курс в формате онлайн-тренажера для изучения SQL с нуля. Теоретические уроки сопровождается занятиями на SQL-тренажере, в арсенале которого более 240 практических упражнений. Обучение построено по модульной системе, новые модули открываются еженедельно. По окончании курса предусмотрен итоговый проект: предлагается выбрать задачу, с которой вы сталкивались ранее, и выполнить ее с помощью новых освоенных инструментов. Лучшие выпускники по результатам выполнения тестового задания имеют возможность трудоустройства в качестве аналитика в Skyeng.

Вы узнаете особенности работы с базами данных, синтаксис языка SQL и его функционал. Научитесь получать данные и анализировать их. Изучите язык SQL, СУБД PostgreSQL, инструменты QuickDBD и Metabase.

Источник

Курс молодого бойца PostgreSQL

Хочу поделиться полезными приемами работы с PostgreSQL (другие СУБД имеют схожий функционал, но могут иметь иной синтаксис).

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

Данный материал будет полезен тем, кто полностью освоил базовые навыки SQL и желает учиться дальше. Советую выполнять и экспериментировать с примерами в pgAdmin‘e, я сделал все SQL-запросы выполнимыми без разворачивания каких-либо дампов.

1. Использование временных таблиц

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

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

Ключ ON COMMIT DROP автоматически удаляет таблицу (и все связанные с ней объекты) при завершении транзакции.

2. Часто используемый сокращенный синтаксис Postgres

можно записать менее громоздко:

* (две тильды со звездочкой)

Поиск регулярными выражениями (имеет отличный от LIKE синтаксис)
оператор

* (одна тильда и звездочка) регистронезависимая версия

Приведу пример поиска разными способами строк, которые содержат слово text

‘%text%’

* ‘%text%’

3. Общие табличные выражения (CTE). Конструкция WITH

Очень удобная конструкция, позволяет поместить результат запроса во временную таблицу и тут же использовать ее.

Примеры будут примитивны, чтобы уловить суть.

Таким способом можно ‘оборачивать’ какие-либо запросы (даже UPDATE, DELETE и INSERT, об этом будет ниже) и использовать их результаты в дальнейшем.

b) Можно создать несколько таблиц, перечисляя их нижеописанным способом

c) Можно даже вложить вышеуказанную конструкцию в еще один (и более) WITH

По производительности следует сказать, что не стоит помещать в секцию WITH данные, которые будут в значительной степени фильтроваться последующими внешними условиями (за пределами скобок запроса), ибо оптимизатор не сможет построить эффективный запрос. Удобнее всего положить в CTE результаты, к которым требуется несколько раз обращаться.

4. Функция array_agg(MyColumn).

Значения в реляционной базе хранятся разрозненно (атрибуты по одному объекту могут быть представлены в нескольких строках). Для передачи данных какому-либо приложению часто возникает необходимость собрать данные в одну строку (ячейку) или массив.
В PostgreSQL для этого существует функция array_agg(), она позволяет собрать в массив данные всего столбца (если выборка из одного столбца).
При использовании GROUP BY в массив попадут данные какого-либо столбца относительно каждой группы.

Сразу опишу еще одну функцию и перейдем к примеру.
array_to_string(array[], ‘;’) позволяет преобразовать массив в строку: первым параметром указывается массив, вторым — удобный нам разделитель в одинарных кавычках (апострофах). В качестве разделителя можно использовать

Выдаст результат:

Выполним обратное действие. Разложим массив в строки при помощи функции UNNEST, заодно продемонстрирую конструкцию SELECT columns INTO table_name. Помещу это в спойлер, чтобы статья не сильно разбухала.

5. Ключевое слово RETURNIG *

указанное после запросов INSERT, UPDATE или DELETE позволяет увидеть строки, которых коснулась модификация (обычно сервер сообщает лишь количество модифицированных строк).
Удобно в связке с BEGIN посмотреть на что именно повлияет запрос, в случае неуверенности в результате или для передачи каких либо id на следующий шаг.

Можно использовать в связке с CTE, организую безумный пример.

Таким образом, выполнится удаление данных, и удаленные значения передадутся на следующий этап. Все зависит от вашей фантазии и целей. Перед применением сложных конструкций обязательно изучите документацию вашей версии СУБД! (при параллельном комбинировании INSERT, UPDATE или DELETE существуют тонкости)

6. Сохранение результата запроса в файл

У команды COPY много разных параметров и назначений, опишу самое простое применение для ознакомления.

7. Выполнение запроса на другой базе

Не так давно узнал, что можно адресовать запрос к другой базе, для этого есть функция dblink (все подробности в мануале)

Если возникает ошибка:

«ERROR: function dblink(unknown, unknown) does not exist»

необходимо выполнить установку расширения следующей командой:

8. Функция similarity

Функция определения схожести одного значения к другому.

Использовал для сопоставления текстовых данных, которые были похожи, но не равны друг другу (имелись опечатки). Сэкономил уйму времени и нервов, сведя к минимуму ручную привязку.
similarity(a, b) выдает дробное число от 0 до 1, чем ближе к 1, тем точнее совпадение.
Перейдем к примеру. С помощью WITH организуем временную таблицу с вымышленными данными (и специально исковерканными для демонстрации функции), и будем сравнивать каждую строку с нашим текстом. В примере ниже будем искать то, что больше похоже на ООО «РОМАШКА» (подставим во второй параметр функции).

Получим следующий результат:

Если возникает ошибка

«ERROR: function similarity(unknown, unknown) does not exist»

необходимо выполнить установку расширения следующей командой:

Сортируем по similarity DESC. Первыми результатами видим наиболее похожие строки (1— полное сходство).

Необязательно выводить значение similarity в SELECT, можно просто использовать его в условии WHERE similarity(c_name, ‘ООО «РОМАШКА»’) >0.7
и самим задавать устраивающий нас параметр.

P.S. Буду признателен, если подскажете какие еще есть способы сопоставления текстовых данных. Пробовал убирать регулярными выражениями все кроме букв/цифр, и сопоставлять по равенству, но такой вариант не срабатывает, если присутствуют опечатки.

9. Оконные функции OVER() (PARTITION BY __ ORDER BY __ )

Почти описав в своем черновике этот очень мощный инструмент, обнаружил (с грустью и радостью), что подобная качественная статья на эту тему уже существует. Не вижу смысла дублировать информацию, поэтому рекомендую обязательно ознакомиться с данной статьей (ссылка — habrahabr.ru/post/268983/, автору низкий поклон ) тем, кто еще не умеет пользоваться оконными функциями SQL.

10. Множественный шаблон для LIKE

Задача. Необходимо отфильтровать список пользователей, имена которых должны соответствовать определенным шаблонам.

Как всегда, представлю простейший пример:

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

Продемонстрирую, как сделать его более компактным:

Можно проделать интересные трюки, используя подобный подход.
Напишите в комментариях, если есть мысли, как еще можно переписать исходный запрос.

11. Несколько полезных функций

NULLIF(a,b)
Возникают ситуации, когда определенное значение нужно трактовать как NULL.
Например, строки нулевой длины ( » — пустые строки) или ноль(0).
Можно написать CASE, но лаконичнее использовать функцию NULLIF, которая имеет 2 параметра, при равенстве которых возвращается NULL, иначе выводит исходное значение.

COALESCE выбирает первое не NULL значение

GREATEST выбирает наибольшее значение из перечисленных

LEAST выбирает наименьшее значение из перечисленных

PG_TYPEOF показывает тип данных столбца

PG_CANCEL_BACKEND останавливаем нежелательные процессы в базе

12. Экранирование символов

Начну с основ.
В SQL строковые значения обрамляются ‘ апострофом (одинарной кавычкой).
Числовые значения можно не обрамлять апострофами, а для разделения дробной части нужно использовать точку, т.к. запятая будет воспринята как разделитель

результат:

Все хорошо, до тех пор пока не требуется выводить сам знак апострофа ‘
Для этого существуют два способа экранирования (известных мне)

результат одинаковый:

В PostgreSQL существуют более удобный способ использовать данные, без экранирования символов. В обрамленной двумя знаками доллара $$ строке можно использовать практически любые символы.

получаю данные в первозданном виде:

Если этого мало, и внутри требуется использовать два символа доллара подряд $$, то Postgres позволяет задать свой «ограничитель». Стоит лишь между двумя долларами написать свой текст, например:

Увидим наш текст:

Для себя этот способ открыл не так давно, когда начал изучать написание функций.

Заключение

Надеюсь, данный материал поможет узнать много нового начинающим и «средничкам». Сам я не являюсь разработчиком, а могу лишь назвать себя любителем SQL, поэтому то, как использовать описанные приемы — решать Вам.

Желаю успехов в изучении SQL. Жду комментариев и благодарю за прочтение!
UPD. Вышло продолжение

Источник

Работа с PostgreSQL: от полного нуля до полного просветления

Работа с PostgreSQL не отличается от работы с любой другой СУБД, но знать синтаксис все-таки полезно. Предлагаем вашему вниманию вводный курс по основам.

PostgreSQL – это опенсорсная реляционная СУБД. В статье будет рассматриваться процесс установки, настройки / управления, а также базовые операции с БД.

Установка

Если на вашей машине стоит MacOS, то процесс установки можно запустить командой:

На Linux СУБД устанавливается так:

Если у вас другая ОС, есть непонятные моменты или вопросы – обращайтесь в официальный хелп.

После того, как все загружено и установлено, можно проверить, все ли в порядке, и какая стоит версия PostgreSQL. Для этого выполните следующую команду:

Разбираемся с настройками

Работа с PostgreSQL может быть произведена через командную строку (терминал) с использованием утилиты psql – инструмент командной строки PostgreSQL. Попробуйте ввести следующую команду:

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

Если вам нужна помощь, введите \help (или -h) в psql-терминале. Появится список всех доступных параметров справки. Вы можете ввести \help [имя команды], если вам нужна помощь по конкретной команде. Например, если ввести \help UPDATE в консоли psql, вы увидите синтаксис команды update.

Если у вас возникает много вопросов – не стоит отчаиваться. Поиск в интернете предоставит массу примеров, ну и официальную документацию psql никто не отменял.

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

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

Чтобы вывести список всех пользователей, выполните команду \du. Атрибуты пользователя postgres говорят нам, что он суперпользователь.

Основные операции с БД

Чтобы выполнять базовые действия в СУБД, нужно знать Structured Query Language (SQL).

Создание базы данных

Для создания базы данных используется команда create database. В приведенном ниже примере создается база данных с именем proglib_db.

Если вы забыли точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-», как показано на рисунке ниже. Это зачастую указывает на то, что необходимо завершить (дописать) запрос.

На картинке нам сообщают об ошибке из-за того, что в нашем случае база уже создана. Вы поймете, что к чему, когда начнете писать более длинные запросы.

Создание нового юзера

Для создания пользователя существует команда create user. В приведенном ниже примере создается пользователь с именем author.

При создании пользователя отобразится сообщение CREATE ROLE. Каждый пользователь имеет свои права (доступ к базам, редактирование, создание БД / пользователей и т. д.). Вы могли заметить, что столбец Attributes для пользователя author пуст. Это означает, что пользователь author не имеет прав администратора. Он может только читать данные и не может создать другого пользователя или базу.

Вы можете установить пароль для существующего пользователя. С этой задачей справится команда \password:

Чтобы задать пароль при создании пользователя, можно использовать следующую команду:

Удаление базы или пользователя

Для этой операции используется команда drop: она умеет удалять как пользователя, так и БД.

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

Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:

Давайте войдем в базу proglib_db под пользователем author. Нажмите \q, чтобы выйти из текущей БД, а затем выполните следующую команду:

Дополнительная литература

Надеемся, что наш небольшой туториал помог вам разобраться с основами.

Источник

Работа с базой данных PostgreSQL

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

В этой статье мы разберемся, как работать с PostgreSQL. В качестве примера я буду использовать Ubuntu 18.04.

Установка PostgreSQL

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

Для выполнения команды нужны права суперпользователя. Так что придется вспомнить пароль.

После установки апдейтов инсталлируем PostgreSQL:

PostgreSQL установится вместе с пакетом contrib, в котором содержится дополнительная функциональность, а также утилиты для работы СУБД.

Чтобы убедиться, что все работает, проверим версию:

При установке автоматически создается роль и пользователь postgres.

Настройка PostgreSQL

Работать с PostgreSQL мы будем через терминал с помощью встроенной утилиты psql. Запускаем ее следующей командой:

Можно установить сторонние инструменты для администрирования PostgreSQL, но в этом мало смысла — psql справляется со всеми основными задачами.

Чтобы получить поддержку, вводим в терминале команду:

Если нужна справка по конкретной команде, пишем:

Выйти из psql можно командой \q.

Управление пользователями

В PostgreSQL используется концепция ролей. Одну роль можно рассматривать как отдельного пользователя или как группу пользователей. Роли могут владеть объектами БД и выдавать разрешения другим ролям.

По умолчанию была создана роль postgres. Давайте создадим еще одну роль. Для этого и з консоли системы выполняем команду:

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

Если вы уже зашли в psql, то создать новую роль можно командой:

Затем задаем пароль:

Вывести список всех ролей можно командой /du. Кроме имен отобразятся привилегии каждого роли.

Чтобы закрыть список ролей, выполняем команду q.

Для удаления пользователя выполняем команду:

Это можно также сделать из консоли системы с помощью команды:

Чтоб сменить пароль пользователя, подключаемся к psql с правами суперпользователя. Затем выполняем следующую команду:

Работа с базами данных в PostgreSQL

Создать базу данных из консоли можно следующей командой:

Если вы используете терминальный клиент psql, то команда будет немного отличаться:

Чтобы посмотреть список всех БД, выполняем команду \l.

Для удаления базы данных используется та же команда, что и для удаления роли — drop. В терминале системы синтаксис будет таким:

В клиенте psql синтаксис похожий:

Подключение к базе данных

По умолчанию psql подключается от имени текущего пользователя Linux к БД с таким же названием. Если эти данные совпадают, достаточно выполнить запуск самого терминального клиента:

Чтобы вывести информацию о текущем соединении, выполняем команду:

Если имя базы данных отличается от имени пользователя, нужно указать его явно:

Если имя роли не совпадает с именем пользователя в Linux, прописываем дополнительные параметры.

Имя роли и название БД совпадают:

Название базы данных отличается от имени роли:

Чтобы переключиться на другую базу данных внутри psql используем команду:

Создание резервной копии и восстановление из бэкапа

Для создания резервной копии базы данных используется сложная команда:

Чтобы было проще разобраться, рассмотрим каждый параметр:

Выглядит это примерно так:

Для выполнения этой команды нужно ввести пароль, который используется при входе в psql от имени указанной роли (mybase в приведенном примере).

Восстановление из резервной копии выполняется аналогичным образом:

Параметры похожие, отличия минимальные. Важно знать хост, помнить формат и путь к бэкапу.

Мы разобрались с основными действиями и настройками PostgreSQL. На этом все!

Источник