Размещение информации в базе данных PostgreSQL

Андрей Волков

Андрей Волков

Системное, сетевое администрирование +DBA. И немного программист!))  Профиль автора.

Как происходить запись данных в БД PostgreSQLТеперь, когда структура базы данных выбрана, пришло время сохра­нить наши данные в базе. Позже мы еще вернемся к механизму опре­деления таблицы базы данных, хранению и доступу к данным. На рис. 1 представлены данные таблицы PostgreSQL (для просмотра применялась простая утилита командной строки psql на машине с опе­рационной системой Linux):

 


Оглавление статьи[Показать]


psql для просмотра таблицы PostgreSQL

Рис. 1. Использование psql для просмотра таблицы PostgreSQL

Обратите внимание, что добавлен дополнительный столбец, customer_id, обеспечивающий однозначность ссылок на клиента. Он являет­ся первичным ключом для этой таблицы. Данные разбиты на строки и столбцы и напоминают электронную таблицу.

Обращаться к таблицам PostgreSQL можно не только из командной строки. Покажем ту же самую таблицу в графической программе pgAccess (рис. 2):

Просмотр таблиц PostgreSQL с помощью pgAccess

Рис. 2. Просмотр таблицы PostgreSQL с помощью pgAccess

 

Сетевой доступ к базе данных PostgreSQL

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

PostgreSQL - это база данных на выделенном сервере, и, как было упо­мянуто ранее, после соответствующей настройки она принимает за­просы от клиентов по сети. Естественно, запрос может поступить и с той же машины, на которой работает сервер базы данных, но это мало­вероятно при многопользовательском доступе. Под Microsoft Windows можно применять драйвер ODBC. Таким образом можно по сети свя­зать любое настольное приложение под Windows, поддерживающее ODBC, с сервером, хранящим наши данные.

На рис. 3 представлена база данных MS Access, содержащая связан­ные внешние таблицы (по ODBC) для доступа к нашей базе данных PostgreSQL, установленной на машине с операционной системой Linux:

База данных MS Access

Рис. 3. База данных MS Access

Итак, доступ к одним и тем же данным может одновременно осущест­вляться по сети с нескольких машин. Данные при этом существуют в единственном экземпляре и находятся в надежном хранилище - на центральном сервере, который доступен по сети нескольким настоль­ным компьютерам, работающим под управлением различных опера­ционных систем. Как и все реляционные СУБД, PostgreSQL автомати­чески гарантирует невозможность конфликта обновлений данных в базе. Пользователям кажется, что все они имеют неограниченный до­ступ ко всем данным, на самом же деле «за кулисами» PostgreSQL сле­дит за обновлениями и предотвращает конфликты.

Способность предоставлять многим пользователям доступ к данным и при этом всегда гарантировать непротиворечивость данных являет­ся одним из важнейших свойств СУБД. Когда кто-то вносит измене­ния в столбец, мы видим либо то, что было до начала редактирования, либо его конечный результат, но никогда не получим частично изме­ненных данных.

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

Реляционные СУБД, и в том числе PostgreSQL, скрывают все проме­жуточные состояния, так что другие пользователи не могут их уви­деть. Возникает понятие изоляция - отчет о состоянии балансов изо­лирован от операции перевода денег, поэтому он выполняется или до, или после нее, но не в тот же самый момент. В главе 9 об изоляции бу­дет рассказано подробнее.

 

Выборка данных

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

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

Рассмотрим выбор подмножества строк. Предположим, что нам нуж­на информация обо всех клиентах, живущих в городе Бингаме (Bing­ham). Используем psql, стандартную утилиту командной строки Post­greSQL, для того чтобы показать, как применять язык SQL для обра­щения к серверу с целью получения нужных данных.

Команда SQL, необходимая в данном случае, выглядит очень просто:

SELECT * FROM customer WHERE town = 'Bingham';

Если команда вводится в psql или каком-то другом клиентском прило­жении с графическим интерфейсом, следует добавить точку с запятой, чтобы обозначить конец команды (т. к. существуют длинные коман­ды, занимающие несколько строк). Как правило, в этой книге будут ставиться точки с запятой, поскольку для тех, кто будет выполнять примеры, используя графический интерфейс, ввод точки с запятой обязателен.

PostgreSQL в ответ на команду возвращает все строки таблицы клиен­тов, в которых в столбце, отведенном для города, указано «Bingham» (рис. 4):

Пример выборки подмножества строк

Рис. 4. Пример выборки подмножества строк

Как видите, все было очень просто. Пока не думайте о конкретном ви­де операторов SQL. Обратите внимание на добавленный столбец customer_id, он будет использован позднее, когда в базе данных будут сохраняться заказы.

Итак, это была выборка, при которой из таблицы отбирались опреде­ленные строки. Теперь займемся проекцией, или, проще говоря, выбо­ром, отдельных столбцов из таблицы.

Пусть нас интересуют только имена и фамилии клиентов из таблицы customer. Столбцы с этими данными были названы fname и lname. Коман­да для извлечения указанных столбцов также чрезвычайно проста:

SELECT fname, lname FROM customer;

Получив команду, PostgreSQL возвращает соответствующие столбцы (рис. 5):

Пример выборки столбцов (rows)

Рис. 5. Пример выборки подмножества столбцов (проекции)

Разумно было бы предположить, что иногда бывает необходимо вы­полнить обе операции с данными одновременно, то есть выбрать кон­кретные столбцы и только из определенных строк. В SQL и это нетруд­но. Например, пусть нам нужны имена и фамилии тех клиентов, кото­рые живут в городе Бингаме (Bingham). Просто объединим два опера­тора SQL в одной команде:

SELECT fname, lname FROM customer WHERE town = 'Bingham';

PostgreSQL в ответ на запрос возвращает выбранные столбцы (рис. 6):

Одновременная выборка строк и столбцов

Рис. 6. Одновременная выборка строк и столбцов

Есть одна вещь, на которую стоит обратить внимание. Во многих тра­диционных языках программирования, таких как C или Java, нам пришлось бы написать некоторое количество кода для просмотра всех строк таблиц, остановки при нахождении строки с нужным городом и вывода запрошенных имен и фамилий. Даже если бы и удалось умес­тить все это в одну строку кода, эта строка была бы очень длинной и сложной. Дело в том, что C, Java и другие подобные языки по сущест­ву являются процедурными. В них приходится указывать, как компь­ютер должен себя вести. А в SQL, который является декларативным языком, надо просто сказать машине, что требуется, и внутренняя ма­гия PostgreSQL сама справится с решением.

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

Сохранение информации в базе данных PostgreSQL 

Добавление информации в базу данных

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

 

Несколько таблиц

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

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

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

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

Таблица с информацией о заказах, содержащая некоторые данные, представлена (рис. 7) в графическом средстве pgAccess, которое, как было показано ранее, позволяет получить доступ к данным в базе дан­ных PostgreSQL из графического интерфейса:

Таблица заказов

Рис. 7. Таблица заказов

В таблицу специально помещено не слишком много данных, т. к. эк­спериментировать удобнее с небольшими объемами.

 

Отношения между таблицами

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

Предположим, что требуется получить перечень всех заказов и раз­местивших их клиентов. При использовании процедурного языка, та­кого как C, пришлось бы написать программу, которая просматривала бы одну таблицу, вероятно, сначала customer, а затем для каждого ин­тересующего нас клиента вывести все размещенные им заказы. Это не­сложно, но поглощает немало времени, к тому же кодирование доста­точно утомительно. Вам наверняка будет приятно узнать, что в SQL сделать это гораздо проще благодаря операции объединения. От поль­зователя требуется лишь ответить на три вопроса:

  • Какие столбцы его интересуют?
  • Из каких таблиц нужно извлекать данные?
  • Как две таблицы связаны между собой?

По-прежнему не стоит уделять особого внимания конкретному виду операторов SQL. Команда, которая потребуется в данном случае, при­водилась в качестве примера в предыдущей главе:

 
SELECT * FROM customers, orderinfo WHERE customer.customer_id =
orderinfo.customer_id;

 

Как несложно догадаться, команда содержит запрос на вывод всех строк из наших двух таблиц, а также сообщает SQL, что столбец custo- mer_id таблицы customer (обозначение таблица.столбец позволяет ука­зать как имя таблицы, так и название столбца внутри нее) содержит ту же информацию, что и customer_id в таблице orderinfo.

Теперь, когда в базе данных уже несколько таблиц с данными, посмот­рим, как PostgreSQL отреагирует на ввод команды (рис. 8):

Пример использования операции объединения (JOIN)

Рис. 8. Пример использования операции объединения

Выглядит не очень аккуратно, поскольку приходится переносить стро­ки для того, чтобы не выходить за границы экрана, однако все же можно понять, как PostgreSQL ответила на запрос (притом, что не бы­ло точных указаний относительно того, как должна быть решена эта задача). Символ «*» можно заменить названиями столбцов, если тре­буется выбрать лишь указанные столбцы (например, если нас интере­суют только фамилии и суммы заказов).

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

 
SELECT customer.title, customer.fname, customer.lname,
count(orderinfo.orderinfo_id) AS "Number of orders" FROM customer, orderinfo
WHERE customer.customer_id = orderinfo.customer_id group by customer.title,
customer.fname, customer.lname;

Выглядит сложнее, чем то, что встречалось ранее, но если не вдаваться в детали, видно, что, опятьтаки, мы не говорим, как решить задачу, а просто очень подробно описываем ее, используя SQL. При этом все умещается в одном операторе. Вот как отвечает PostgreSQL (рис. 9): 

Рис. 2.12. Более сложный пример использования операции объединения (JOIN)

Рис. 9. Более сложный пример использования операции объединения

Некоторым специалистам баз данных может нравиться вводить опера­торы SQL в окне утилиты командной строки (а в некоторых случаях без нее и не обойтись), но надо признать, что у пользователей могут быть и другие предпочтения. Например, если кому-то нравится графи­ческий интерфейс Windows, то никаких трудностей это не создаст, до­ступ к базе данных можно будет получить при помощи драйвера ODBC, а запросы создавать графически. Далее (рис. 10) представлен тот же самый запрос, реализованный в StarOffice на машине с опера­ционной системой Windows NT (StarOffice Base представляет собой альтернативу СУБД Microsoft Access в Windows).

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

Запрос, реализованный в StarOffice

Рис. 10. Запрос, реализованный в StarOffice

Вас заинтересует / Intresting for you:

Что такое база данных  и СУБД?
Что такое база данных и СУБД? 5853 просмотров Светлана Mon, 21 Oct 2019, 17:58:45
База данных как объект правово...
База данных как объект правово... 748 просмотров Денис Wed, 27 Mar 2019, 03:16:24
Перенос корпоративных баз данн...
Перенос корпоративных баз данн... 1253 просмотров Дэн Fri, 27 Sep 2019, 07:52:18
Что такое SQL? Плюсы и минусы ...
Что такое SQL? Плюсы и минусы ... 4121 просмотров Андрей Васенин Tue, 21 Nov 2017, 13:17:28
Войдите чтобы комментировать

AidaU аватар
AidaU ответил в теме #9123 30 июль 2018 07:10
Добротная вводная статья. Для студентов полезный материал. Взяла на заметку Ваш блог!))