Обновление записей по нескольким источникам

В PostgreSQL команда SQL UPDATE была дополнена мощной нестандартной возможностью — поддержкой секции FROM. Секция FROM позволяет получать входные данные из других наборов данных (таблиц и подзапросов).

В листинге 4.56 команда UPDATE с секцией FROM обновляет данные таблицы stock по данным таблицы stock_backup. Секция WHERE описывает связь между обновляемой таблицей и источником. Каждый раз, когда в таблицах находятся совпадающие значения isbn, поле retail в таблице stock обновляется значением из резервной таблицы stock_backup.

Переименование полей

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

Команда переименования полей имеет следующий синтаксис:

ALTER TABLE таблица

RENAME [ COLUMN ] имя_поля ТО новое_иня_поля

Как и в других командах ALTER TABLE, ключевое слово COLUMN является необязательным. По двум идентификаторам, разделенным ключевым словом ТО, Post-greSQL может определить, что команда переименования относится к одному полю, а не таблице. Пример переименования полей приведен в листинге 4.11.

Переименование таблицы

Переименование таблиц осуществляется командой ALTER TABLE с секцией RENAME, интаксис переименования таблицы:

JER TABLE таблица

RENAME TO новое_иня

Таблицу можно переименовывать сколько угодно раз, это никак не отражается а состоянии хранящихся в ней данных (листинг 4.10). Конечно, в некоторых си-/ацпях переименования нежелательны — например, если таблица используется вешним приложением.

Перекрестные объединения

Результат перекрестного объединения принципиально не отличается от перечисления источников через запятую. Следовательно, в команде выборки с перекрестным объединением практически всегда должна присутствовать секция WHERE, уточняющая связи между объединенными наборами данных. В листинге 4.35 приведен запрос из листинга 4.27, в котором перечисление источников заменено формальным синтаксисом JOIN.

Подзапросы

Поддержка подзапросов, впервые реализованная в PostgreSQL версии 6.3, существенно повысила гибкость команд SQL. Подзапросом называется команда SELECT, заключенная в круглые скобки, которая выполняется в контексте другой команды SQL. Подзапросы чаще используются для возвращения одной записи, но они также могут использоваться для определения подмножества записей.

Подзапросы могут находиться практически в любой части команды SQL — в списке целей, в секции WHERE и т. д. В листинге 4.59 приведен простой пример использования подзапроса для выборки критерия поиска из другой таблицы.

Представления

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

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

Применение представлений

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

Пример создания таблицы

Команда SQL, приведенная в листинге 4.6, создает таблицу books в базе данных booktovvn.

Простая команда SELECT

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

SELECT * FROM имя таблицы;

Как упоминалось при описании синтаксиса SELECT, звездочка (*) является сокращенным обозначением всех несистемных полей. Таким образом, команда SELECT * выбирает из указанной таблицы все поля и все записи, поскольку количество записей в итоговом наборе не ограничивается. В листинге 4.23 приведен пример выборки всех записей (*) из таблицы books базы данных booktown.

Различия команд COPY и \copy

Команда SQL COPY не эквивалентна команде psql \copy. Команда \copy имеет тот же синтаксис (хотя и не завершается точкой с запятой), она выполняет операцию че-

рез клиента psql, а не через серверный процесс postmaster. В результате команда \copy выполняется с правами пользователя, запустившего psql, а не с теми правами, с которыми работает процесс postmaster.

Редактирование буфера запроса

Команда \е открывает текущее содержимое буфера запроса в редакторе, заданном переменной среды EDITOR. Это позволяет просмотреть и при необходимости изменить любую строку запроса перед его обработкой. В листинге 4.5 показано, как задать значение переменной EDITOR. Если переменная EDITOR не задана, используется редактор vi.

Реструктуризация таблиц

Несмотря на возможность включения новых полей в существующую таблицу, следует помнить, что в PostgreSQL (во всяком случае, в версии 7.1.x) не поддерживается удаление полей. Существует два относительно простых способа реструктуризации существующих таблиц. Первый способ основан на использовании команды CREATE TABLE AS, а во втором способе команда CREATE TABLE объединяется с командой INSERT INTO.

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

ВНИМАНИЕ

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

Реструктуризация таблиц командами CREATE TABLE и INSERT INTO

Если таблица, создаваемая командой CREATE TABLE AS, вас почему-либо не устраивает (например, если в таблице необходимо установить ограничения полей), то одну команду CREATE TABLE AS можно заменить двумя командами SQL. Сначала команда CREATE TABLE создает новую таблицу, а затем команда INSERT INTO с запросом SELECT заполняет ее данными (листинг 4.15).

Реструктуризация таблиц командой CREATE TABLE AS

Распространенная методика реструктуризации таблиц основана на использовании команды CREATE TABLE с секцией AS в сочетании с запросом SQL. Команда создает временную таблицу на основании существующей таблицы, после чего временная таблица переименовывается. Физическое создание новой таблицы может сопровождаться удалением полей и изменением порядка их следования с одновременным заполнением данными из исходной таблицы.

В приведенном ниже описании синтаксиса этой усеченной версии команды CREATE TABLE запрос представляет собой команду SELECT для выборки данных, переносимых в новую таблицу. Типы данных всех создаваемых полей определяются типами данных соответствующих полей, выбранных в результате выполнения запроса.

CREATE [ TEMPORARY | TEMP ] TABLE таблица [ ( имя_поля [....])] AS запрос

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

Допустим, из таблицы books удаляется лишнее поле publication, созданное в пункте «Создание полей» подраздела «Модификация таблицы командой ALTER TABLE». Для этого мы создаем усеченную версию таблицы (с перечислением нужных полей) с указанием соответствующей команды SELECT в секции AS команды CREATE TABLE, после чего старая таблица удаляется командой DROP TABLE (листинг 4.14).

Секция WITH OIDS

Файлы, содержащие идентификаторы объектов (созданные командой COPY TO с секцией WITH OIDS), загружаются командой COPY FROM, в которую также включается секция WITH OIDS. Попытки использования команды COPY FROM с секцией WITH OIDS по отношению к файлу, в котором значения OID не были сохранены при создании, завершаются неудачей.

Экспортирование данных с идентификаторами объектов принадлежит к числу специальных возможностей, присущих только команде COPY. Значения OID являются системными и поэтому не могут изменяться командами INSERT и UPDATE. Если не принять меры предосторожности, в таблице могут появиться две записи с одинаковыми значениями OID.

Синонимы источников данных в секции FROM

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

дущем разделе. Наличие синонима для набора данных позволяет обращаться к нему при помощи точечной записи, что делает команды SQL более компактными и наглядными. В листинге 4.29 приведен запрос из листинга 4.27 с упрощением точечной записи при помощи секции AS.

Синтаксис команды CREATE TABLE

Синтаксис команды CREATE TABLE выглядит так:

CREATE [ TEMPORARY | TEMP ] TABLE имя_таблицы (

{ имя_поля тип [ограничение_поля [... ] ] | ограничение_таблицы }

[. ..Т ] ) [ INHERITS (бдзовая_тзблица [....])]

Ниже поясняется смысл компонентов команды.

TEMPORARY | TEMP. Таблица, созданная с ключевым словом TEMPORARY или TEMP, автоматически уничтожается в конце текущего сеанса. Имя временной таблицы может совпадать с именем существующей таблицы; в этом случае все ссылки на таблицу с этим именем будут относиться к временной таблице (до момента ее уничтожения). Все индексы таблицы также являются временными и уничтожаются в конце сеанса. Имя_таблицы. Имя для ссылок на таблицу после ее создания. Имя_поля тип [ограничение _поля ] | ограничение ^таблицы. После имени таблицы в круглых скобках перечисляются определения полец таблицы, разделенные запятыми. Определение поля состоит из имени, которое представляет собой синтаксически правильный идентификатор допустимого типа, а также необязательного ограничения. Ограничения полей описаны в подразделе «Ограничения в таблицах» раздела «Нетривиальное использование таблиц» главы 7. Ограничения полей и таблицы могут чередоваться, хотя на практике обычно сначала перечисляются ограничения полей, а затем следуют ограничения таблицы. [....]. После определения поля может следовать запятая, за которой указано новое определение. Многоточие означает произвольное количество определений полей (вплоть до максимального значения 1600). Помните, что за последним элементом списка не может следовать запятая, как разрешено в языках типа Perl; это приведет к ошибке лексического разбора. INHERITS ( базовая _таблица [. ...] ). Объектные возможности PostgreSQL позволяют задать одну или несколько таблиц (в виде списка, разделенного запятыми), базовых по отношению к создаваемой (производной) таблице. При наличии этого необязательного элемента объявления между таблицами устанавливается связь типа «предок-потомок». Механизм наследования является относительно новой возможностью РСУБД и подробно описывается в подразделе «Наследование» раздела «Нетривиальное использование таблиц» главы 7.

Примечание 1
Примечание 1

Параметры ограничение_поля и ограничение_таблицы в приведенном выше объявлении могут соответствовать достаточно сложным синтаксическим конструкциям. Синтаксис различных ограничений подробно описан в подразделе «Ограничения в таблицах» раздела «Нетривиальное использование таблиц» главы 7.

Синтаксис psql

При запуске psql выводится краткая сводка четырех основных команд psql:

\h — справка по SQL; \? — справка по командам psql; \д — выполнение запросов; \q — выход из psql после завершения работы.

Все команды psql начинаются с символа \ (обратная косая черта). Результат выполнения команды \? приведен в листинге 4.2.

Сложные объединения

Хотя одна секция JOIN объединяет всего два набора данных, на практике объединения не ограничиваются двумя источниками. За набором, созданным посредством объединения, может следовать новая секция JOIN — по аналогии с тем, как перечисляются через запятую источники данных.

Элементы комбинированных объединений рекомендуется заключать в круглые скобки. Явная группировка гарантирует отсутствие неоднозначности (как для PostgreSQL, так и для разработчика) относительно того, какие наборы и в каком порядке объединяются. Пример объединения нескольких источников данных приведен в листинге 4.39.

Смена владельца

По умолчанию создатель таблицы автоматически становится ее владельцем. Владелец обладает всеми правами, связанными с таблицей, в том числе правами предоставления и отзыва прав командами GRANT и REVOKE (см. главу 10). Смена владельца производится командой ALTER TABLE с секцией OWNER. Команда имеет следующий синтаксис:

ALTER TABLE таблица

OWNER TO новый_владелец

Пример смены владельца командой ALTER TABLE приведен в листинге 4.13. Новым владельцем таблицы employee назначается пользователь corwin.

Сортировка записей

Как упоминалось в главе 3, записи хранятся в таблицах в произвольном порядке. Более того, даже повторное выполнение запроса никоим образом не гарантирует одинакового порядка следования возвращаемых записей. Однако упорядочение данных играет важную роль при выборке, поэтому в SQL поддерживается инструкция ORDER BY, являющаяся гибким средством сортировки итогового гонораpa.

Секции ORDER BY передается список полей, разделенный запятыми (или выражений, в которых используются поля). Переданный список задает критерий сортировки. Для каждого критерия сортировки могут дополнительно указываться ключевые слова ASC, DESC и USING, управляющие типом сортировки.

ASC. Записи сортируются по возрастанию заданного критерия (то есть числа сортируются от меньших к большим, а текст — по алфавиту от «а» до «z»). Ключевое слово ASC эквивалентно конструкции USING <. По умолчанию выбирается именно этот способ сортировки, поэтому ASC используется лишь для наглядности. DESC. Записи сортируются по убыванию заданного критерия (то есть числа сортируются от больших к меньшим, а текст — по алфавиту от «z» до «а»). Ключевое слово DESC эквивалентно конструкции USING >. USING оператор. Позволяет задать оператор сравнения полей при определении очередности записей. Особенно часто используется при нестандартных критериях сортировки.

В листинге 4.42 приведен пример сортировки таблицы editions с использова-[ием секции ORDER BY. Сортировка осуществляется по значению поля publ ication, [ричем в команде явно указан порядок сортировки — по возрастанию (ASC).

Создание полей

Для создания нового поля в команду ALTER TABLE включается секция ADD COLUMN. Синтаксис команды ALTER TABLE с секцией ADD COLUMN:

ALTER TABLE таблица

ADD [ COLUMN ] имя_поля тип_поля

таблица — имя таблицы, в которой создается новое поле; имя_поля — имя создаваемого поля; тип_поля — тип создаваемого поля.

Ключевое слово COLUMN не является обязательным и включается в команду лишь для наглядности.

Предположим, в таблицу books базы данных booktown потребовалось включить новое поле publication для хранения даты публикации. Листинг4.8 показывает, как это делается.

Создание представления

Представления создаются командой CREATE VIEW, синтаксис которой выглядит следующим образом:

CREATE VIEW представление

AS запрос

представление. Имя (идентификатор) создаваемого представления. запрос. Полная команда SQL SELECT, определяющая содержимое представления.

Предположим, таблица shipments связывает уникальный код поставки с кодом клиента, кодом ISBN книги и датой отправки заказа. Структуру таблицы shipments иллюстрирует табл. 4.1.

Создание таблиц на базе существующих таблиц

Команда SELECT с секцией INTO TABLE создает новую таблицу, структура и содержимое которой определяются итоговым набором запроса. Синтаксис:

SELECT цели_выборки

INTO [ TABLE ] новая_таблица FROM старая_таблица

В этом варианте команда SELECT косвенно выполняет команду CREATE TABLE. Имена и типы полей, а также содержимое таблицы определяются параметрами итогового набора. Возвращаемое в ответ сообщение «SELECT» указывает на то, что команда была успешно выполнена, а в базе данных создана новая таблица. В листинге 4.52 приведен пример создания таблицы stock_backup по данным таблицы stock.

Создание таблицы командой CREATE TABLE

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

SQL в PostgreSQL

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

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

При запуске службы (service) PostgreSQL процесс postmaster начинает работать в фоновом режиме, прослушивая заданный порт TCP/IP в ожидании подключений со стороны клиентов. По умолчанию postmaster ведет прослушивание порта 5432.

Существует несколько интерфейсов, через которые клиент подключается к процессу postmaster. В примерах этой книги используется psql — самый универсальный и доступный клиент, входящий в комплект поставки PostgreSQL.

В этой главе описаны основные принципы работы с psql, процедуры создания и использования таблиц, а также некоторые операции с данными в таблицах. Кроме того, в ней рассматриваются подзапросы и представления SQL.

Сравнение наборов записей

Если объединения используются в SQL для слияния полей двух источников, то ключевые слова UNION, INTERSECT и EXCEPT сравнивают значения полей в двух наборах и строят новый итоговый набор на основании результатов сравнения. Каждое из перечисленных ключевых слов может использоваться в конце синтаксически правильного запроса SQL, а за ним может следовать второй запрос; в этом случае итоговые наборы двух запросов сравниваются и записи либо включаются в результат, либо игнорируются.

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

UNION. Все различающиеся записи двух наборов включаются в один набор данных. Совпадающие записи не дублируются. INTERSECT. Все записи, не входящие в оба набора данных, игнорируются. Таким образом, результат состоит только из записей, присутствующих в обоих наборах. EXCEPT. Все записи, входящие в оба набора данных, игнорируются. Таким образом, результат состоит только из тех записей набора, указанного слева от ключевого слова EXCEPT, которые не входят в набор, указанный справа от него.

В листингах 4.46-4.48 показаны результаты применения этих операций к двум наборам данных. В листинге 4.46 итоговый набор формируется слиянием фамилий авторов с названиями книг, для чего используется ключевое слово UNION.

В листинге 4.47 продемонстрирована выборка кодов ISBN из таблицы editions. Выборка ограничивается записями, которые упоминаются более чем в двух поставках в таблице shi pments. Наконец, в листинге 4.48 из первого запроса исключаются все записи, входящие во второй запрос.

Таблица shipments

Таблица 4.1. Таблица shipments

Поле	Тип	Модификатор
id	integer	NOT NULL DEFAULT nextval ( 'shipments_ship_id_seq' )
customer_id	integer
isbn	text
ship_date	timestamp

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

booktown=# SELECT COUNT(*) FROM shipments;

count

(1 row)

Звездочка (*) в этом запросе просто указывает PostgreSQL на необходимость эдсчета всех записей вместе со значениями NULL, которые могли бы присутствоваъ в поле с явно заданным именем. Запрос подсчитывает общее количество записей, то есть количество зарегистрированных поставок.

Предположим, данные из таблиц editions и books были объединены секцией JOIN, чтобы в выходные данные входили названия всех книг. Более того, в запрос была включена секция GROUP BY, обеспечивающая группировку поставок по названиям книг.

Вспомните, о чем говорилось выше в этой главе, — при группировке по полю title функция count() подсчитывает количество записей в каждой группе (в данном случае — для каждого названия книги). Наконец, для поля ship_date таблицы shipments вызывается функция max(), чтобы в результатах запроса выводилась дата последней поставки по каждой книге и количество экземпляров:

booktown=# SELECT count(*) AS num_shipped, max(ship_date), title

booktown-# FROM shipments

booktown-# JOIN editions USING (isbn)

booktown-# NATURAL JOIN books AS b (book_id)

booktown-# GROUP BY b.title

booktown-# ORDER BY num_shipped DESC;

num_shipped | max | title

5 | 2001-08-13 09:47:04-07 | The Cat in the Hat

5 | 2001-08-14 13:45:51-07 | The Shining

4 | 2001-08-11 09:55:05-07 | Bartholomew and the Oobleck

3 | 2001-08-14 13:49:00-07 | Franklin in the Dark

3 | 2001-08-15 11:57:40-07 | Goodnight Moon

3 | 2001-08-14 13:41:39-07 | The Tell-Tale Heart

2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey

2 | 2001-08-14 08:42:58-07 | Dune

2 | 2001-08-07 13:00:48-07 | Little Women

2 | 2001-08-09 09:30:46-07 | The Velveteen Rabbit

1 | 2001-08-14 07:33:47-07 | Dynamic Anatomy

(11 rows)

Запрос выдает полезную информацию, но синтаксис получается слишком громоздким, и часто вводить его вручную нежелательно. В листинге 4.62 показано, как на базе этого запроса создать представление командой CREATE VIEW.

Удаление дубликатов и ключевое слово DISTINCT

Необязательное ключевое слово DISTINCT исключает дубликаты из итогового набора. Если ключевое слово ON отсутствует, из результатов запроса с ключевым словом DISTINCT исключаются записи с повторяющимися значениями целевых полей. Проверяются только поля, входящие в целевой список SELECT.

Предположим, таблица books содержит 15 записей, в каждой из которых присутствует поле authorjd. Некоторые коды авторов многократно встречаются в таблице books. Включение в запрос ключевого слова DISTINCT (листинг 4.31) гарантирует, что итоговый набор будет содержать не более одной записи для каждого автора.

Удаление таблиц командой DROP TABLE

В SQL таблицы удаляются командой DROP TABLE. Команда имеет следующий синтаксис (таблица — имя удаляемой таблицы):

DROP TABLE таблица

Использование команды DROP TABLE требует осторожности, поскольку удаление таблицы приводит к уничтожению всех хранящихся в ней данных.

Примечание 4
Примечание 4

Уничтожение таблицы с неявно созданным индексом приводит к уничтожению всех связанных с ней индексов.

Удаление записей командой DELETE

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

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

DELETE FROM [ ONLY ] таблица [ WHERE условие ]

DELETE FROM [ ONLY ] таблица. Ключевое слово ONLY означает, что обновляется только заданная таблица, но не ее производные таблицы. Применяется лишь в том случае, если таблица использовалась в качестве базовой при наследовании. WHERE условие. В секции WHERE задается критерий, по которому в таблице выбираются удаляемые записи. При отсутствии секции WHERE из таблицы удаляются все записи.

Секция WHERE почти всегда присутствует в команде DELETE. В ней определяются условия отбора удаляемых записей, выраженные в такой же синтаксической форме, как и при использовании команды SELECT.

Перед выполнением команды DELETE рекомендуется выполнить команду SELECT с соответствующей секцией WHERE и просмотреть удаляемые данные перед их фактическим уничтожением. Пример приведен в листинге 4.57.

Уничтожение представлений

Команда уничтожения представления имеет следующий синтаксис (представление — имя уничтожаемого представления):

DROP VIEW представление

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

Уточнение запросов

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

Предположим, вы хотите получить список всех книг о компьютерных технологиях в базе данных booktown. У этих книг поле subject_id равно 4. Соответственно в секцию WHERE включается оператор =, который проверяет это условие. Пример приведен в листинге 4.32.

Внешние объединения

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

Существуют три разновидности внешних объединений.

Левое внешнее объединение. Всегда содержит как минимум один экземпляр каждой записи из набора, указанного слева от ключевого слова JOIN. Отсутствующие поля из правого набора заполняются значениями NULL. Правое внешнее объединение. Всегда содержит как минимум один экземпляр каждой записи из набора, указанного справа от ключевого слова JOIN. Отсутствующие поля из левого набора заполняются значениями NULL. Полное внешнее объединение. Всегда содержит как минимум один экземпляр каждой записи каждого объединяемого набора. Отсутствующие поля в записях нового набора заполняются значениями NULL.

Вернемся к таблицам books и editions из базы данных booktown. Если в таблице books содержится общая информация о книгах, то в таблице editions хранятся данные, относящиеся к конкретному изданию — код ISBN, издатель и дата публикации. В таблицу editions входит поле book_id, связывающее ее с полем id, которое является первичным ключом таблицы books.

Допустим, вы хотите получить информацию о каждой книге вместе со всеми имеющимися кодами ISBN. Запрос с внутренним объединением таблиц books и editions вернет набор данных с названиями книг и кодами ISBN, но, как видно из листинга 4.38, если у книги нет печатного издания (или информация об этом издании еще не занесена в базу данных booktown), информация о ней не включается в результат.

Вторая команда в листинге 4.38 использует внешнее объединение и возвращает 20 записей. У трех записей в итоговом наборе отсутствуют коды ISBN, но эти записи все равно включаются в результат.

Внутренние и внешние объединения

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

источник! [ NATURAL ] тип_объединения источник2

[ ON ( условие [. ...] ) I USING ( поле [, ...] ) ]

источник! Первый из объединяемых наборов данных (имя таблицы или подзапрос). [ NATURAL ]. Два набора данных объединяются по равным значениям одноименных полей (например, если обе таблицы содержат поле с именем id, то объединяются записи с совпадающими значениями полей id). При наличии ключевого слова NATURAL учитываются синонимы полей (если они были назначены), а секции ON и USING становятся не только ненужными, но и недопустимыми. тип_объединепия. В данном контексте допустимы следующие типы объединений: [INNER] JOIN (то есть JOIN без уточнения подразумевает INNER JOIN), LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN и FULL [OUTER] JOIN. источник2. Второй из объединяемых наборов данных (имя таблицы или подзапрос). ON ( условие [. ... ] ). Отношение между источниками. В секции ON можно задать произвольный критерий по аналогии с тем, как задаются условия в секции WHERE. В критерии могут использоваться синонимы таблиц и полей. USING ( поле [, ... ]). Одноименные поля источников, по совпадающим значениям которых производится объединение. В отличие от NATURAL JOIN позволяет ограничиться некоторыми одноименными полями, тогда как NATURAL проводит объединение по всем одноименным полям. По аналогии с NATURAL в параметрах секции USING учитываются синонимы полей.

Внутренние объединения

Конструкция INNER JOIN была включена в стандарт SQL92 для того, чтобы условия объединения источников данных (условия JOIN) можно было отличить от условий принадлежности записей к итоговому набору (условия WHERE). Рассмотрим две команды SELECT, приведенные в листинге 4.36.

Вставка данных из других таблиц командой SELECT

Команда INSERT INTO применяется и в другой ситуации — когда данные, сохраняемые в таблице, уже присутствуют в другой таблице (или неекольких таблицах). В этом случае команда имеет следующий синтаксис:

INSERT INTO таблица

[ ( имя_поля [. ...] ) ] запрос

По аналогии с синтаксисом INSERT INTO, представленным в предыдущем подразделе, команда содержит необязательный список полей, которым присваиваются новые значение. Тем не менее в этой форме INSERT INTO секция VALUES заменяется полной командой SQL SELECT.

Предположим, база данных booktown содержит таблицу book_queue с информацией о книгах, ожидающих поступления в продажу. После подтверждения данные переносятся из таблицы book_queue в обычную таблицу books. Пример решения этой задачи продемонстрирован в листинге 4.18.

Вставка новых данных

Ниже приведен синтаксис команды INSERT INTO при вставке новых данных:

INSERT INTO таблица

[ ( имя_поля [, ...] ) ] VALUES ( значение [. ...] )

Ниже перечислены параметры команды.

таблица. Имя таблицы, в которую вставляются данные командой SQL INSERT. ( имя_поля [. ...] ). Необязательный группированный список полей новой записи, которым присваиваются значения. VALUES. Ключевое слово SQL, за которым следует группированный список значений. ( значение [, ... ] ). Обязательный группированный список значений полей. Для каждого поля указывается ровно одно значение, элементы списка разделяются запятыми. Элемент списка может быть выражением (например, операцией с двумя операндами) или константой.

Тип каждого значения в секции VALUES должен соответствовать типу поля, которому оно присваивается. Если необязательный список полей отсутствует, PostgreSQL предполагает, что секция VALUES содержит значения всех полей в структуре таблицы в порядке их определения. Если количество значений меньше количества полей, PostgreSQL пытается использовать значение по умолчанию (или NULL при его отсутствии) для каждого пропущенного элемента.

В листинге 4.16 приведен пример создания новой записи в таблице books базы данных booktown.

Ввод запросов в приглашении psql

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

testdb=#

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

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

Выбор интервалов записей

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

Вероятно, выделение некоторой части выборки легко реализуется на программном уровне, но в SQL предусмотрены ключевые слова LIMIT и OFFSET, упрощающие выборку заданной части итогового набора.

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

Как показано в листинге 4.45, первый запрос, содержащий простую секцию LIMIT, ограничивается выборкой первых пяти записей из результатов объединения таблиц editions и books. Обычно это объединение содержит 17 записей.

Выбор источников в секции FROM

В секции FROM указывается источник данных — таблица или итоговый набор. Секция может содержать несколько источников, разделенных запятыми. Результат подобного перечисления функционально эквивалентен перекрестному объединению, о котором рассказано в подразделе «Объединение наборов данных».

Использование нескольких источников данных в PostgreSQL требует осторожности. В результате выполнения команды SELECT для нескольких источников без секций WHERE и JOIN, уточняющих связи между источниками, возвращается полное декартово произведение источников. Иначе говоря, итоговый набор содержит все возможные комбинации записей из всех источников.

Обычно для уточнения связей между источниками, перечисленными через запятую в секции FROM, используется секция WHERE. Пример приведен в листинге 4.27 (за дополнительной информацией о секции WHERE обращайтесь к подразделу «Уточнение запросов»).

Выбор полей

Команда SELECT * является хорошим примером простейшего запроса, но на прак-гике необходимая информация нередко ограничивается несколькими полями габлицы. Чтобы повысить эффективность выборки и сделать запрос более наглядным, рекомендуется явно перечислить все необходимые поля вместо полной вы-эорки с символом *. В частности, ограничение выборки особенно актуально при использовании секции JOIN, подробно рассматриваемой ниже в подразделе «Объединение наборов данных».

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

Выборка данных командой SELECT

Центральное место в SQL занимает команда SELECT, предназначенная для построения запросов и выборки данных из таблиц и представлений. Данные, возвращаемые в результате запроса, называются итоговым набором; как и таблицы, они состоят из записей и полей.

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

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

SELECT [ ALL | DISTINCT [ ON ( выражение [. ...] ) ] ] цель [ AS имя ] [. ...] [ FROM источник [. ... ] ]

[ [ NATURAL ] тип_обьединения источник

[ ON условие \ USING ( список_полей ) ] ]

[. ...]

[ WHERE условие ] [ GROUP BY критерий [. ...] ] [ HAVING условие [. ...] ]

[ { UNION | INTERSECT | EXCEPT } [ ALL ] подзапрос ] [ ORDER BY выражение

[ ASC | DESC | USING оператор ] [. ...] ]

[ FOR UPDATE [ OF таблица [. ...]]] [ LIMIT { число | ALL } [ { OFFSET | . } начало ] ]

В этом описании источник представляет собой имя таблицы или подзапрос. Эти общие формы имеют следующий синтаксис:

FROM { [ ONLY ] таблица [ [ AS ] синоним [ ( синоним_поля [....])]]] ( запрос )
[ AS ] синоним [ ( синоним_поля [. ...] ) ] }

ALL. Необязательное ключевое слово ALL указывает на то, что в выборку включаются все найденные записи. DISTINCT [ ON ( выражение [, ...] ) ]. Секция DISTINCT определяет поле или выражение, значения которого должны входить в итоговый набор не более одного раза. цель [ AS имя ] [, ...]. В качестве цели обычно указывается имя поля, хотя цель также может быть константой, идентификатором, функцией или общим выражением. Перечисляемые цели разделяются запятыми, существует возможность динамического назначения имен целей в секции AS. Звездочка (*) является сокращенным обозначением всех несистемных полей, вместе с ней в списке могут присутствовать и другие цели. FROM источник [. ... ]. В секции FROM указывается источник, в котором PostgreSQL ищет заданные цели. В данном случае источник является именем таблицы или подзапроса. Допускается перечисление нескольких источников, разделенных запятыми (примерный аналог перекрестного запроса). Синтаксис секции FROM подробно описан ниже. [ NATURAL ] тип_объединения источник [ ON условие \ USING ( список_полей ) ]. Источники FROM могут группироваться в секции JOIN с указанием типа объединения (INNER, FULL, OUTER, CROSS). В зависимости от типа объединения также может потребоваться уточняющее условие или список полей. WHERE условие. Секция UHERE ограничивает итоговый набор заданными критериями. Условие должно возвращать простое логическое значение (true или false), но оно может состоять из нескольких внутренних условий, объединенных логическими операторами (например, AND или OR). GROUP BY критерий [, ... ]. Секция GROUP BY обеспечивает группировку записей по заданному критерию. Причем критерий может быть простым именем поля или произвольным выражением, примененным к значениям итогового набора. HAVING условие [. ... ]. Секция HAVING похожа на секцию WHERE, но условие проверяется на уровне целых групп, а не отдельных записей. { UNION | INTERSECT | EXCEPT } [ ALL ] подзапрос. Выполнение одной из трех операций, в которых участвуют два запроса (исходный и дополнительный); итоговые данные возвращаются в виде набора с обобщенной структурой, из которого удаляются дубликаты записей (если не было задано ключевое слово ALL): UNION — объединение (записи, присутствующие в любом из двух наборов); INTERSECT — пересечение (записи, присутствующие одновременно в двух наборах); EXCEPT — исключение (записи, присутствующие в основном наборе SELECT, но не входящие в подзапрос). ORDER BY выражение. Сортировка результатов команды SELECT по заданному выражению. [ ASC | DESC | USING оператор ]. Порядок сортировки, определяемой секцией ORDER BY выражение: по возрастанию (ASC) или по убыванию (DESC). С ключевым словом USING может задаваться оператор, определяющий порядок сортировки (например, < или >). FOR UPDATE [ OF таблица [. ... ] ]. Возможность монопольной блокировки возвращаемых записей. В транзакционных блоках FOR UPDATE блокирует записи указанной таблицы до завершения транзакции. Заблокированные записи не могут обновляться другими транзакциями. LIMIT { число \ ALL }. Ограничение максимального количества возвращаемых записей или возвращение всей выборки (ALL). { OFFSET | ,} начало. Точка отсчета записей для секции LIMIT. Например, если в секции LIMIT установлено ограничение в 100 записей, а в секции OFFSET — 50, запрос вернет записи с номерами 50-150 (если в итоговом наборе найдется столько записей).

Ниже описаны компоненты секции FROM.

[ ONLY ] таблица. Имя таблицы, используемой в качестве источника для команды SELECT. Ключевое слово ONLY исключает из запроса записи всех таблиц-потомков. [ AS ] синоним. Источникам FROM могут назначаться необязательные псевдонимы, упрощающие запрос (например, на таблицу books можно ссылаться по псевдониму Ь). Ключевое слово AS является необязательным. ( запрос ) [ AS ] синоним. В круглых скобках находится любая синтаксически правильная команда SELECT. Итоговый набор, созданный запросом, используется в качестве источника FROM так, словно выборка производится из статической таблицы. При выборке из подзапроса обязательно должен назначаться синоним. ( синоним_поля [. ...] ). Синонимы могут назначаться не только всему источнику, но и его отдельным полям. Перечисляемые синонимы полей разделяются запятыми и группируются в круглых скобках за синонимом источника FROM. Синонимы перечисляются в порядке следования полей в таблице, к которой они относятся.

Выполнение запросов

В psql существует два способа ввода и исполнения запросов (в psql этот термин означает команду SQL вообще). В интерактивном режиме запросы обычно вводятся непосредственно в приглашении командной строки (то есть из стандартного ввода). Команда psql \i читает файл локальной файловой системы и использует его содержимое в качестве входных данных.

Выражения константы и синонимы

Выражения, константы и синонимы

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

Команда SELECT также может использоваться для простого вычисления и вывода результатов выражений и констант. В этом случае она не содержит секции FROM или имен столбцов (листинг 4.25).

Запуск psql

Перед запуском psql убедитесь в том, что двоичный файл psql находится в стандартном каталоге исполняемых файлов (например, /usr/bin), либо путь к каталогу двоичных файлов PostgreSQL (например, /usr/local/pgsql/bin) включен в список ката-

логов переменной среды PATH. За дополнительной информацией обращайтесь к главе 2.

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

$ export PATH=$PATH:/usr/local/pgsql/bin

В интерпретаторах csh или tcsh используется несколько иной синтаксис:

$ set path=(Spath /usr/local/pgsql/bin)

Знакомство с psql

Клиент psql работает в режиме командной строки и входит в комплект поставки PostgreSQL. Его часто называют интерактивным монитором или интерактивным терминалом. Этот простой, но мощный инструмент позволяет напрямую работать с сервером PostgreSQL и потому особенно хорошо подходит для экспериментов.