Теперь, когда структура базы данных выбрана, пришло время сохранить наши данные в базе. Позже мы еще вернемся к механизму определения таблицы базы данных, хранению и доступу к данным. На рис. 1 представлены данные таблицы PostgreSQL (для просмотра применялась простая утилита командной строки psql на машине с операционной системой Linux):
Рис. 1. Использование psql для просмотра таблицы PostgreSQL
Обратите внимание, что добавлен дополнительный столбец, customer_id, обеспечивающий однозначность ссылок на клиента. Он является первичным ключом для этой таблицы. Данные разбиты на строки и столбцы и напоминают электронную таблицу.
Обращаться к таблицам PostgreSQL можно не только из командной строки. Покажем ту же самую таблицу в графической программе pgAccess (рис. 2):
Рис. 2. Просмотр таблицы PostgreSQL с помощью pgAccess
Сетевой доступ к базе данных PostgreSQL
Конечно же, если бы доступ к данным был возможен только с той машины, на которой они хранятся, ситуация практически не отличалась бы от той, когда существует обычный файл, доступ к которому разрешен нескольким пользователям.
PostgreSQL - это база данных на выделенном сервере, и, как было упомянуто ранее, после соответствующей настройки она принимает запросы от клиентов по сети. Естественно, запрос может поступить и с той же машины, на которой работает сервер базы данных, но это маловероятно при многопользовательском доступе. Под Microsoft Windows можно применять драйвер ODBC. Таким образом можно по сети связать любое настольное приложение под Windows, поддерживающее ODBC, с сервером, хранящим наши данные.
На рис. 3 представлена база данных MS Access, содержащая связанные внешние таблицы (по ODBC) для доступа к нашей базе данных PostgreSQL, установленной на машине с операционной системой Linux:
Рис. 3. База данных MS Access
Итак, доступ к одним и тем же данным может одновременно осуществляться по сети с нескольких машин. Данные при этом существуют в единственном экземпляре и находятся в надежном хранилище - на центральном сервере, который доступен по сети нескольким настольным компьютерам, работающим под управлением различных операционных систем. Как и все реляционные СУБД, PostgreSQL автоматически гарантирует невозможность конфликта обновлений данных в базе. Пользователям кажется, что все они имеют неограниченный доступ ко всем данным, на самом же деле «за кулисами» PostgreSQL следит за обновлениями и предотвращает конфликты.
Способность предоставлять многим пользователям доступ к данным и при этом всегда гарантировать непротиворечивость данных является одним из важнейших свойств СУБД. Когда кто-то вносит изменения в столбец, мы видим либо то, что было до начала редактирования, либо его конечный результат, но никогда не получим частично измененных данных.
Классическим примером является банковская база данных для перевода денег с одного расчетного счета на другой. Если во время перевода денег кто-то пытается вывести отчет о состоянии балансов всех счетов, чрезвычайно важно, чтобы итоговая сумма была правильной. Для отчета не имеет значения, на каком из двух счетов находились деньги в момент запуска отчета, важно только, чтобы в нем не отразилось то промежуточное состояние, когда с одного счета сумма уже снята, а на другой еще не поступила.
Реляционные СУБД, и в том числе PostgreSQL, скрывают все промежуточные состояния, так что другие пользователи не могут их увидеть. Возникает понятие изоляция - отчет о состоянии балансов изолирован от операции перевода денег, поэтому он выполняется или до, или после нее, но не в тот же самый момент. В главе 9 об изоляции будет рассказано подробнее.
Выборка данных
Теперь, когда мы узнали, что не составляет труда получить доступ к данным, хранящимся в базе, поговорим о том, как же это сделать. Обычно к большим объемам данных применяются две основные операции.
Первая - это выборка строк, соответствующих некоторому набору значений, а вторая - выборка подмножества столбцов. В терминах баз данных такие операции называются выборкой и проекцией соответственно, эти понятия чуть более формальны, но все так же просты и понятны.
Рассмотрим выбор подмножества строк. Предположим, что нам нужна информация обо всех клиентах, живущих в городе Бингаме (Bingham). Используем psql, стандартную утилиту командной строки PostgreSQL, для того чтобы показать, как применять язык 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):
Рис. 5. Пример выборки подмножества столбцов (проекции)
Разумно было бы предположить, что иногда бывает необходимо выполнить обе операции с данными одновременно, то есть выбрать конкретные столбцы и только из определенных строк. В SQL и это нетрудно. Например, пусть нам нужны имена и фамилии тех клиентов, которые живут в городе Бингаме (Bingham). Просто объединим два оператора SQL в одной команде:
SELECT fname, lname FROM customer WHERE town = 'Bingham';PostgreSQL в ответ на запрос возвращает выбранные столбцы (рис. 6):
Рис. 6. Одновременная выборка строк и столбцов
Есть одна вещь, на которую стоит обратить внимание. Во многих традиционных языках программирования, таких как C или Java, нам пришлось бы написать некоторое количество кода для просмотра всех строк таблиц, остановки при нахождении строки с нужным городом и вывода запрошенных имен и фамилий. Даже если бы и удалось уместить все это в одну строку кода, эта строка была бы очень длинной и сложной. Дело в том, что C, Java и другие подобные языки по существу являются процедурными. В них приходится указывать, как компьютер должен себя вести. А в SQL, который является декларативным языком, надо просто сказать машине, что требуется, и внутренняя магия 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):
Рис. 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):
Рис. 9. Более сложный пример использования операции объединения
Некоторым специалистам баз данных может нравиться вводить операторы SQL в окне утилиты командной строки (а в некоторых случаях без нее и не обойтись), но надо признать, что у пользователей могут быть и другие предпочтения. Например, если кому-то нравится графический интерфейс Windows, то никаких трудностей это не создаст, доступ к базе данных можно будет получить при помощи драйвера ODBC, а запросы создавать графически. Далее (рис. 10) представлен тот же самый запрос, реализованный в StarOffice на машине с операционной системой Windows NT (StarOffice Base представляет собой альтернативу СУБД Microsoft Access в Windows).
Данные все так же хранятся на машине под управлением Linux, но пользователю едва ли необходимо знать технические подробности. Как правило, при изучении SQL в этой книге будет рассматриваться командная строка, потому что так читатели смогут выучить основы, прежде чем придет время переходить к более сложным командам SQL, работа с которыми при помощи средств графического интерфейса не всегда удобна. Конечно, ваше право предпочесть средствам командной строки ввод команд SQL в графическом интерфейсе, - поступайте так, как вам удобнее.
Рис. 10. Запрос, реализованный в StarOffice
