Лк1. Работа в СУБД PostgreSQL

1.

РАБОТА В СУБД
POSTGRESQL

2.

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

3.

Oracle
СУБД
MS SQL Server
SyBase
MySQL
MS Access
PostgreSQL
Firebird
...

4.

Состав СУБД
СУ Б Д с о с то и т и з
я д р а , н а ход я ще го с я в п а м я т и
с е р ве р а
н е о г р а н и ч е н н о го к ол и ч е с т ва
п р о г р а мм к л и е н то в,
в ы п ол н я ю щи х п о с та вл е н н ы е
п ол ь зователя м и з а д ач и

5.

О PostgreSQL
PostgreSQL
—
наиболее
полнофункциональная,
свободно
распространяемая СУБД с открытым
кодом
Современный
PostgreSQL
ведет
происхождение
от
проекта
POSTGRES, который разрабатывался
под
руководством
Майкла
Стоунбрейкера (Michael Stonebraker),
профессора
Калифорнийского
университета в Беркли
Ассоциация вычислительной
техники (англ. Association for
Computing Machinery, ACM)
наградила премией Тьюринга
Майкла Стоунбрейкера
(Michael Stonebraker),
создателя свободной СУБД
PostgreSQL. Размер премии —
миллион долларов
Работа над проектом началась в 1985 году, и до 1988
года
был
опубликован
описывающих
POSTQUEL
модель
(в
то
общепризнанным
хранилища данных
ряд
научных
статей,
данных,
язык
запросов
еще
не
время
SQL
стандартом)
и
был
устройство

6.

Немного истории
Первая
версия
СУБД
была
выпущена в 1989 году
База
данных
с овершенствовалась
на протяжении неск ольких лет, а в
1993 году, когда вышла версия 4.2,
проек т был закрыт
Но,
несмотря
на
официальное
прекращение, открытый к од и BSDлицензия
позволили
выпускник ам
Беркли Эндрю Ю и Джоли Чену в
1994
году
взяться
дальнейшее
за
развитие.
его
Они
заменили язык запросов POSTQUEL
на
ставший
общепринятым
к
том у
SQL,
нарек ли Postgres95.
времени
а
проект
К 1 996 год у ста л о ясн о, ч то наз вани е Postgr es 95 не вы де ржит
исп ытан ие вр е мене м, и бы л о выбра но н овое и мя —
Postgre SQ L , к ото рое отражает с вя з ь и с ор иги нал ь ны м
п р о е к том PO STG R ES, и с п е р еход ом н а SQ L
Н о ва я ве рсия ста ртовала к а к 6.0, пр од олжа я исход н ую
н ум ер аци ю, упр а вл ени е про е к то м взя ла на себя снач ал а
неб ол ь ша я
группа
и ници ати вны х
пол ьзо вателей
и
разр абот ч и к о в, к ото рая пол уч и ла назван ие Глоб альной
г руп пы
ра зраб отк и
Postgr eSQL
( Postg re SQ L
G lo bal
D e ve l o p m e nt G r o u p)
В к лад росс ий ски х ра зраб отч и ков в Postgre SQ L весь ма
зн ач и те лен – это, пож а луй, с а мый к руп н ы й глобальный
проек т с отк ры ты м и сходны м кодом с та ки м широк и м
рос с и й ски м п ре дс та в и те ль с твом

7.

Немного истории
В
2015
астроном
ГАИШ
году
Олег
Барт унов,
и
научный
сотрудник
вместе
Федором
МГУ,
с
Сигаевым
и
Алекс андром
Коротковым
основали
к омпанию
Postgres Professional для развития
отечественной
экспертизы
области
разработки
данных
и
СУБД
с оздания
систем
в
баз
российск ой

8.

Немного истории
Гл обал ьная группа разработки Pos tgreSQL в ыпол няет
подд ержк у ос нов ны х верс ий с ис темы в течение пяти лет
Цик л работы над очеред ной верс ией
с момента в ыпуск а . Эта подд ержк а, к ак и к оорд ин ация
Pos tgreSQL обы чно зани мает около
разработки , осуществляется через списки рассылок
года
За
это
время
от
всех
жел ающих
принимаются на расс мотрение патчи
с
исправл ениями,
измен ениями
и
новым функционал ом
Раньше
номер
данны х,
разв ития
за
свою
на
ак ад емичес к ой
ос нов н ой
верс ии
с ос тоял из двух ч исел , но, начиная с
2017 года, бы л о решено ос тав ит ь
только одно.
Таким образом, за 9.6 посл ед овала
10, а пос лед ней ак т уал ьн ой верс ией
Pos tgreSQL
является
вышедшая в
конце сентября 2021
года
Pos tgreSQL является од ной из с амы х попул ярны х баз
верс ия
14,
бол ее
прочном
чем
20- летнюю
фунд амен те,
разработк ой,
Pos tgreSQL
историю
зал оженном
вы рос ла
в
пол ноценн ую СУБД уров ня пред приятия и с ос тавляет
реальную альтернативу коммерческим базам данных

9.

Характеристики СУБД PostgreSQL
Надёжность и
отказоустойчивость
Горячее резервирование, восстановление на заданный момент времени в
прошлом, репликация данных
Безопасность
Подключение по защищённому каналу, аутентификация (по паролю, с
помощью внешних сервисов), управление пользователями и доступом
(роли, разграничение прав доступа)
Соответствие
стандартам
Высокий уровень соответствия стандарту ANSI SQL и поддержка 170 из 177
обязательных возможностей
Поддержка
транзакционности
Полная поддержка свойств ACID, изоляция транзакций (используется
механизм многоверсионного управления одновременным доступом MVCC),
который позволяет обходиться без блокировок строк

10.

Характеристики СУБД PostgreSQL
Масштабируемость и
производительность
Параллельное выполнение запросов чтения и соединения, ряда
служебных программ. JIT компиляция запросов (перевод в МК во время
выполнения запросов), возможности репликации для горизонтального
масштабирования
Планировщик
запросов
Основан на стоимости запроса, использует собираемую статистику
(дисковые операции, время работы процессора), планировщик
оптимизирует сложные запросы, применяя все методы доступа к данным и
способы выполнения соединений
Возможности
индексирования
Hash индекс, GiST (сбалансирование дерево поиска), SP-GiST
(обобщённое несбалан.дерево), и др., также существует возможность
объединения сразу несколько индексов для ускорения доступа
Кроссплатформенность
PostgreSQL работает на операционных системах семейства Unix (Linux,
FreeBSD, macOS), а также на Windows

11.

Характеристики СУБД PostgreSQL
Расширяемость
Пользователи могут самостоятельно, не меняя базовый код системы
добавлять типы данных, функции, индексные и табличные методы
доступа, подключения к внешним источникам, загружаемые расширения
Доступность
Неограниченное использование СУБД, модификация кода, включение в
состав других продуктов, в том числе закрытых и коммерческих
Независимость
PostgreSQL не принадлежит ни одной компании и развивается
международным сообществом, в том числе и российскими разработчиками

12.

Возможности разработчика
Встроенный PL/pgSQL, С, Perl, Python, Java, JavaScript
Программные интерфейсы для обращения к СУБД из приложений (ODBC,
JDBC)
Объекты БД для реализации логики (последовательности, индексы,
представления (+материализованные), подзапросы и with-запросы
Агрегатные и оконные функции
Хранимый код - процедуры, функции, триггеры
Слабоструктурированные данные в духе NoSQL, хранилище – ключзначение xml, json и др.
Подключение источников данных, включая все основные СУБД в качестве
внешних таблиц по стандарты SQL/MED

13.

Стандарт
SQL/MED
или
SQL/MED,
Data
Management
(управление
расширение
внешними
стандарта
SQL,
of
External
данными)
—
закреплённое
в ISO/IEC 9075-9:2003
SQL/MED реализует расширение SQL за счёт
определения
адапперов
внешних
данных
и
ссылочных типов, позволяющим SQL управлять
внешними данными
Под
внешними
дост уп
к
данными
к оторым
понимаются
данные,
осуществляется
SQL-
ориентированными СУБД, но не управляется ими
Этот
стандарт
может
использоваться
разработке федеративных баз данных
при

14.

SQL ст ал ре зультатом исследовательского проекта компании
IBM, проект включал создание реляционной системы базы
данных
и
языка
SEQUEL
(Structured
English
Query
Language, английский язык ст руктурированных запро сов)
SQL
(англ.
Structured
ст руктурированных
компьютерный
Query
запро сов)
язык,
—
Language
—
язык
универс альный
применяемый
для
создания,
модификации и управления данными в реляционных базах
данных,
в
его
чистом
информационно-логиче ским
(базовом)
языком,
а
виде
является
не
языком
программирования
Ст андарт языка спецификацией SQL/PSM предусматривает
возможно сть
его
процедурных
расширений,
с
учётом
которых язык уже вполне может рассмат риваться в каче стве
языка программирования

15.

1. Начал о 1970-х г. – IBM разрабатывает 1-ю версию
язык а, первая публик ация 1974г. (SEQUEL) для
История
языка SQL
СУБД System/R
2. 1979 г. – завершение исследовательск ого проекта,
язык переименован в SQL
3. 1977 г. фирма Relational Software прист упила к
созданию
промышленной
реляционной
СУБД
на
основе SQL, фирма переименовывается в Oracle
4. Середина
70-х
–
исследовательский
проект
по
созданию эк спериментальной СУБД Ingress
5. 1980-1982
гг.
компания
IBM
создаёт
СУБД
реляционного типа SQL/DS в будущем DB2
6. Середина
80-х
гг.
реляционных СУБД
–
общепринят
стандарт
для

16.

1 9 8 2 год у А м е р и к а н с к и й н а ц и о н а л ь н ы й и н с т и т у т с т а н д а р т о в ( A m e r i c a n
N a t i o n a l S t a n d a r d s I n s t i t u t e — A N S I ) с о з д а л к о м и т ет Х З Н 2 , в з я в з а о с н о ву
д и а л е к т S Q L , р е а л и з о в а н н ы й в СУ Б Д D B 2 , к о м и т ет п о с т а р а л с я е го
о б о б щ и т ь , у ч и т ы ва я во з м о ж н о с т и д р у г и х р ел я ц и о н н ы х СУ Б Д
В 1 9 8 6 год у п р е д л о ж е н н ы й к о м и т ет о м в а р и а н т S Q L б ы л о ф и ц и а л ь н о
у т в е р ж д е н к а к с т а н д а р т A N S I , в 1 9 8 7 го д у б ы л п р и н я т в к а ч е с т в е
с т а н д а рт а
Международной
о р га н и з а ц и е й
стандартов
(International
Standards Organization — ISO)
Стандартизация
SQL
С т а н д а рт A N S I / I S O п р и н я л о п р а в и т ел ь с т в о С Ш А к а к ф ед е р а л ь н ы й
с т а н д а рт в о бл а с т и о б р а б о т к и и н ф о р м а ц и и ( F e d e r a l I n f o r m a t i o n P r o c e s s i n g
Standard — FIPS)
В 1 9 8 9 год у с т а н д а р т б ы л н е з н а ч и т ел ь н о и з м е н е н и п о л у ч и л н а з в а н и е
SQL-89 (или SQL1)
В 1 9 9 2 год у A N S I п р и н я л с т а н д а р т S Q L - 9 2 ( и л и S Q L 2 ) , в к о т о р о м б ы л и
расширены
способы
ограничения
ц ел о с т н о с т и ,
введена
поддержк а
р аз л и ч н ы х
языков
программирования,
предусмотрена
обработк а
т р а н з а к ц и й и м н о го е д р у го е
В 1 9 9 2 год у с т а н д а р т S Q L - 9 9 ( и л и S Q L 3 ) , S Q L с т а л п о д д е р ж и ва т ь м о д ел ь
д а н н ы х , в ы х од я щ у ю з а р а м к и р ел я ц и о н н о й , я ч е й к и т а бл и ц м о г у т б ы т ь
м н о гоз н ач н ы м и , ч т о п о з в о л я ет п р е д с т а вл я т ь и е р а р х и ч е с к у ю и с ет е ву ю
м од ел и , я з ы к р а с ш и р е н д о в о з м о ж н о с т и п р е д с т а вл е н и я о бъ е к т н о й м о д ел и
д а н н ы х и м а н и п ул и р о в а н и я е ю , S Q L с т а л с о с т о я т ь и з о тд ел ь н ы х ч а с т е й
( p a r t s ) , с о с т а вл я ю щ и х е го о с н о ву ( f o u n d a t i o n ) , к о т о р а я д о п о л н я ет с я
н ез а в и с и м о о п р ед ел е н н ы м и м од ул я м и ( p a c k a g e s )

17.

Введены функции, связанные с XML (SQL / XML), оконные
SQL: функции, стандартизированные последовательности и
2003
2003 столбцы с автоматически генерируемыми значениями
(включая столбцы идентификаторов)
Стандартизация
SQL
ISO / IEC 9075-14: 2006 определяет способы, которыми SQL
может использоваться с XML, определяет способы импорта и
хранения
XML-данных
в
базе
данных
SQL,
манипулирования ими в базе данных и публикации как XML,
SQL:2
2006
так и обычных SQL-данных в форме XML
006
Позволяет приложениям интегрировать запросы в свой код
SQL с помощью XQuery, языка запросов XML, опубликованного
Консорциумом World Wide Web (W3C), для одновременного
доступа к обычным SQL-данным и XML-документам
Упорядочивает порядок с помощью определений внешних
SQL:
2008
курсоров, добавляет оператор TRUNCATE, предложение
2008
FETCH
2011
SQL: Добавляет временные данные (ПЕРИОД ДЛЯ)
2011 Улучшения для оконных функций и предложения ВЫБОРКИ
2016
SQL: Добавляет сопоставление шаблонов строк, полиморфные
2016 табличные функции, JSON
2019
SQL: Добавлена часть 15, многомерные массивы (тип MDarray и
2019 операторы)

18.

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

19.

1. DML (Data Manipulation Language) Работа
с данными
Категории
языка SQL
2. DDL (Data Definition Language ) –работа с
объектами БД
3. TCL (Transaction Control Language) работа
с транзакциями
4. DCL (Data Control Language) доступ к
данным

20.

DDL
Операторы
DDL
Definition
Language)
(Data
-
операторы определения объектов
(Data
базы данных (CREATE TABLE,
Definition
ALTER TABLE, DROP TABLE ...)
Language)
Эти
операторы
позволяют
создавать, изменять и уничтожать
базы данных.

21.

DML
(Data
Операторы
DML
Manipulation
Language)
операторы
манипулирования
(Data
-
данными
(SELECT,
INSERT,
Manipulation
UPDATE,
DELETE)]
выбор,
Language)
вставка,
изменение,
удаление
данных

22.

TCL
(Transaction
Control
Language)
Операторы TCL (Transaction Control
Language)
применяется
для
управления изменениями, защиты и
управления данными.
Операторы этой группы Commit завершение транзакции и сохранение
изменений,
Rollback
откат
транзакции,
отмена
изменений,
Transaction - Установка параметров
доступа
к
данным
текущей
транзакции

23.

DCL
Операторы
DCL
Language
или
(Data
Control
Access
Control
(Data Control
Language)
Language или
осуществления административных
Access Control
Language)
функций:
применяются
Grant
привилегий,
привилегий
-
Revoke
для
присвоение
-
отмена

24.

Клиент для
PostgreSQL

25.

1. Консольный клиент
psql
Клиенты
2. Графические
СУБД
pgAdmin