SQL в PostgreSQL
Наше знакомство с языком SQL продолжается. На этот раз основное внимание мы уделим практическим аспектам его применения. В этой главе будет показано, как при помощи команд SQL создавать таблицы, заполнять их данными и модифицировать эти данные.
Система PostgreSQL, как и большинство сетевых СУБД, основана па парадигме «клиент-сервер». Центральное место в PostgreSQL занимает процесс postmaster, предназначенный не для прямого взаимодействия с пользователем, а для обслуживания подключений со стороны различных клиентов.
При запуске службы (service) PostgreSQL процесс postmaster начинает работать в фоновом режиме, прослушивая заданный порт TCP/IP в ожидании подключений со стороны клиентов. По умолчанию postmaster ведет прослушивание порта 5432.
Существует несколько интерфейсов, через которые клиент подключается к процессу postmaster. В примерах этой книги используется psql — самый универсальный и доступный клиент, входящий в комплект поставки PostgreSQL.
В этой главе описаны основные принципы работы с psql, процедуры создания и использования таблиц, а также некоторые операции с данными в таблицах. Кроме того, в ней рассматриваются подзапросы и представления SQL.
Знакомство с psql
Клиент psql работает в режиме командной строки и входит в комплект поставки PostgreSQL. Его часто называют интерактивным монитором или интерактивным терминалом. Этот простой, но мощный инструмент позволяет напрямую работать с сервером PostgreSQL и потому особенно хорошо подходит для экспериментов.
Запуск 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)
Листинг 4.1, относящийся к интерпретатору bash, иллюстрирует процедуру назначения системного пути для клиента psql.
Листинг 4.1. Назначение системного пути для psql
[user@host user]$ psql
bash: psql: command not found
[user@host user]$ echo $PATH
/b1n:/usr/bin:/usr/local/bin:/usr/bin/Xll:/usr/XHR6/tnn
[user@host user]$ export PATH=$PATH:/usr/local/pgsql/bin
[user@host user]$ psql testdb
Welcome to psql. the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit
testdb=#
После настройки переменной PATH интерактивный терминал PostgreSQL запускается командой psql, за которой следует имя базы данных.
ВНИМАНИЕ
После выхода из psql изменения переменной среды будут потеряны. Чтобы изменения переменной PATH сохранялись после выхода из программы, команда настройки PATH включается в стартовый сценарий командного интерпретатора (например, ~/.bash_profile).
Синтаксис psql
При запуске psql выводится краткая сводка четырех основных команд psql:
\h — справка по SQL; \? — справка по командам psql; \д — выполнение запросов; \q — выход из psql после завершения работы.Все команды psql начинаются с символа \ (обратная косая черта). Результат выполнения команды \? приведен в листинге 4.2.
Листинг 4.2. Получение списка команд psql booktown=# \?
\а toggle between unaligned and aligned mode
\c[onnect] [dbname|- [user]]
connect to new database (currently 'booktown')
\C <title> table title
\copy... perform SQL COPY with data stream to the client machine
\copyright show PostgreSQL usage and distribution terms
\d <table> describe table (or view, index, sequence)
\d{t|i|s|v} list tables/indices/sequences/views
\d{p|S|l} list permissions/system tables/lobjects
\da list aggregates
\dd [object] list comment for table, type, function, or operator
\df list functions
\do list operators
\dT list data types
\e [file] edit the current query buffer or [file] with external editor
\echo <text> write text to stdout
\encoding <encoding> set client encoding
\f <sep> change field separator
\g [file] send query to backend (and results in [file] or (pipe)
\h [cmd] help on syntax of sql commands. * for all commands
\H toggle HTML mode (currently off)
\i<file> read and execute queries from <file>
\l list all databases
\lo_export. \lo_import, \lo_list. \lo_unlink
Targe object operations
\o [file] send all query results to [file], or (pipe
\p show the content of the current query buffer
\pset <opt> set table output <opt> = {format|border|expanded|fieldsep|
null|recordsep|tuples_only|title|tableattr|pagerj
\q quit psql
\qecho <text> write text to query output stream (see \o) .
\r reset (clear) the query buffer
\s [file] print history or save it in [file] \set <var> <value> set internal variable
\t show only rows (currently off)
\T <tags> HTML table tags
\unset <var> unset (delete) internal variable
\w <file> write current query buffer to a <file>
\x toggle expanded output (currently off)
\z list table access permissions
\! [cmd] shell escape or command
Выполнение запросов
В psql существует два способа ввода и исполнения запросов (в psql этот термин означает команду SQL вообще). В интерактивном режиме запросы обычно вводятся непосредственно в приглашении командной строки (то есть из стандартного ввода). Команда psql \i читает файл локальной файловой системы и использует его содержимое в качестве входных данных.
Ввод запросов в приглашении psql
Запустите psql и убедитесь в том, что вы подключены к нужной базе данных (и зарегистрированы в системе с нужными параметрами). На экране появляется приглашение, которое по умолчанию совпадает с именем текущей базы данных. Приглашение выглядит примерно так:
testdb=#
Чтобы передать PostgreSQL команду SQL, просто введите ее в приглашении. Весь вводимый текст накапливается до тех пор, пока ввод не будет завершен символом точки с запятой (:). Ввод команды не прерывается даже разрывами строк,
что позволяет распределить запрос по нескольким строкам. Пример многострочного запроса приведен в листинге 4.3.
Листинг 4.3. Ввод команд в psql
testdb=# SELECT * FROM employees
testdb-# WHERE firstname = 'Michael';
Запрос из листинга 4.3 возвращает записи обо всех работниках с именем «Michael» из таблицы empl oyees. Деление по строкам использовано лишь для удобства чтения. Запрос передается для обработки только после ввода завершающего символа «точка с запятой». Если в предыдущей строке присутствует символ, требующий парного завершителя (например, круглой скобки или кавычки), этот символ включается в приглашение следующей строки. Например, если начать команду CREATE TABLE с открывающей круглой скобкой и перейти на другую строку, то приглашение будет выглядеть так, как показано в листинге 4.4.
Листинг 4.4. Включение открывающих символов в приглашение psql
testdb=# CREATE TABLE employees (
testdb(#
Ввод команды продолжается. Открывающая круглая скобка в приглашении psql напоминает о том, что в команду необходимо включить закрывающую круглую скобку.
Редактирование буфера запроса
Команда \е открывает текущее содержимое буфера запроса в редакторе, заданном переменной среды EDITOR. Это позволяет просмотреть и при необходимости изменить любую строку запроса перед его обработкой. В листинге 4.5 показано, как задать значение переменной EDITOR. Если переменная EDITOR не задана, используется редактор vi.
Листинг 4.5. Присваивание значения переменной EDITOR
# set EDITOR='joe'
$ export EDITOR
Команда \е также может применяться для сохранения текущего содержимого буфера в файле. При вводе команды \е клиент psql запускает редактор и загружает содержимое буфера запроса, словно оно было прочитано из файла. Выполните все необходимые операции, сохраните результат в файле командой Save и вернитесь в psql. Команда \е создает временный файл с расширением .tmp; чтобы сохранить запрос в обычном файле, воспользуйтесь командой Save As и введите имя файла.
Использование таблиц
Таблицы являются основными блоками хранения данных в базе. Перед любыми операциями создания, выборки или модификации записей необходимо сначала создать таблицу, в которой эти записи будут храниться.
В этом разделе рассматриваются процедуры создания, модификации и удаления таблиц командами CREATE TABLE, ALTER TABLE n DROP TABLE SQL (вопросам создания баз данных посвящена глава 9).
Создание таблицы командой CREATE TABLE
В языке SQL таблицы создаются командой CREATE TABLE. Минимальный набор параметров включает имя таблицы и описания полей, состоящие из имени поля и типа данных. Команде CREATE TABLE также могут передаваться некоторые необязательные параметры: ограничения полей (правила, которые определяют, какие данные могут или не могут присутствовать в поле) и ограничения таблицы (общие ограничения и связи, определенные для самой таблицы).
Синтаксис команды CREATE TABLE
Синтаксис команды CREATE TABLE выглядит так:
CREATE [ TEMPORARY | TEMP ] TABLE имя_таблицы (
{ имя_поля тип [ограничение_поля [... ] ] | ограничение_таблицы }
[. ..Т ] ) [ INHERITS (бдзовая_тзблица [....])]
Ниже поясняется смысл компонентов команды.
TEMPORARY | TEMP. Таблица, созданная с ключевым словом TEMPORARY или TEMP, автоматически уничтожается в конце текущего сеанса. Имя временной таблицы может совпадать с именем существующей таблицы; в этом случае все ссылки на таблицу с этим именем будут относиться к временной таблице (до момента ее уничтожения). Все индексы таблицы также являются временными и уничтожаются в конце сеанса. Имя_таблицы. Имя для ссылок на таблицу после ее создания. Имя_поля тип [ограничение _поля ] | ограничение ^таблицы. После имени таблицы в круглых скобках перечисляются определения полец таблицы, разделенные запятыми. Определение поля состоит из имени, которое представляет собой синтаксически правильный идентификатор допустимого типа, а также необязательного ограничения. Ограничения полей описаны в подразделе «Ограничения в таблицах» раздела «Нетривиальное использование таблиц» главы 7. Ограничения полей и таблицы могут чередоваться, хотя на практике обычно сначала перечисляются ограничения полей, а затем следуют ограничения таблицы. [....]. После определения поля может следовать запятая, за которой указано новое определение. Многоточие означает произвольное количество определений полей (вплоть до максимального значения 1600). Помните, что за последним элементом списка не может следовать запятая, как разрешено в языках типа Perl; это приведет к ошибке лексического разбора. INHERITS ( базовая _таблица [. ...] ). Объектные возможности PostgreSQL позволяют задать одну или несколько таблиц (в виде списка, разделенного запятыми), базовых по отношению к создаваемой (производной) таблице. При наличии этого необязательного элемента объявления между таблицами устанавливается связь типа «предок-потомок». Механизм наследования является относительно новой возможностью РСУБД и подробно описывается в подразделе «Наследование» раздела «Нетривиальное использование таблиц» главы 7.ПРИМЕЧАНИЕ
Параметры ограничение_поля и ограничение_таблицы в приведенном выше объявлении могут соответствовать достаточно сложным синтаксическим конструкциям. Синтаксис различных ограничений подробно описан в подразделе «Ограничения в таблицах» раздела «Нетривиальное использование таблиц» главы 7.
Пример создания таблицы
Команда SQL, приведенная в листинге 4.6, создает таблицу books в базе данных booktovvn.
Листинг 4.6. Создание таблицы books
booktown=# CREATE TABLE books (
booktown(# id integer UNIQUE.
booktown(# title text NOT NULL,
booktown(# authoMd integer,
booktowntf subjected integer,
booktown(# CONSTRAINT books_id_pkey PRIMARY KEY (id));
NOTICE: CREATE TABLE/PRIMARY KEY
will create implicit index 'books_id_pkey' for table
'books'
CREATE
После выполнения команды CREATE выводится сообщение с информацией об успешном создании таблицы. Если вы получите сообщение об ошибке, проверьте правильность расстановки знаков препинания и написания всех ключевых слов. Если сообщения вообще нет, вероятно, в команде осталась незакрытая круглая скобка, апостроф или другой парный символ.
Кроме того, в сообщении NOTICE говорится о том, что при создании описанной таблицы был построен скрытый индекс books_id_pkey.
Анализ структуры таблицы
Команда \d (с указанием имени таблицы) предназначена для вывода структуры таблицы и ее ограничений, если они имеются. В листинге 4.7 приведены выходные данные команды \d для таблицы books, созданной в предыдущем разделе.
Обратите внимание: каждая строка результата описывает одно из полей (то есть столбцов) таблицы; таким образом, таблица фактически разворачивается па 90°. Такое представление выбрано для наглядности, поскольку многие таблицы содержат большое количество полей, не помещающихся на экране (или на печатной странице) по горизонтали. В книге этот формат будет использоваться при описании структуры таблиц.
Листинг 4.7. Выходные данные команды \d
booktown=# \d books
Table "books" Attribute | Type | Modifier
id integer | not null
title text I not null
authorjd j integer j subjectjd | integer |
Index: books_id_pkey
Ниже приведены более подробные описания полей и атрибутов, встречающихся в листинге 4.7.
Id. В поле Id хранится числовой код, уникальный для каждой книги. Поле id определяется с типом Integer и для него устанавливаются следующие ограничения: UNIQUE. Ограничение гарантирует уникальность значений поля. В общем случае поле с ограничением уникальности может содержать NULL, но попытки вставки дубликатов завершаются неудачей. Поле Id также используется в качестве первичного ключа. PRIMARY KEY. Хотя в выходных данных команды \d об этом не упоминается, из исходной команды CREATE TABLE видно, что поле id также назначено первичным ключом таблицы. Установка ограничения первичного ключа для поля также неявно подразумевает установку ограничений NOT NULL и UNIQUE. NOT NULL. Автоматически устанавливается при назначении ограничения PRIMARY KEY. Ограничение гарантирует, что поле id всегда содержит значение, отличное от NULL. Поле никогда не остается пустым, и любые попытки вставки псевдозначения NULL завершаются неудачей. title. Поле title содержит символьные данные типа text. Тип text обладает большей гибкостью по сравнению с varchar и хорошо подходит для данного поля, поскольку не требует задания максимального количества символов. Для поля titl e установлено ограничение NOT NULL; это означает, что поле всегда содержит значение, отличное от NULL. authorjd. Поле author_id содержит значения типа integer и используется для связи с таблицей authors. Ограничения для этого поля не устанавливаются, поскольку в таблице могут встречаться книги, написанные неизвестным автором, что исключает ограничение NOT NULL. С другой стороны, один автор может написать несколько книг, поэтому ограничение UNIQUE также не подходит. subject_id. Поле subject_id аналогично полю author_id— оно тоже содержит значения типа integer и используется для установки связи с таблицей subjects. Ограничения для этого поля также отсутствуют, поскольку некоторые книги не принадлежат ни к одной категории, а категории, как правило, содержат более одной книги.Структура таблицы может изменяться после ее создания, но возможности мо-(ификаций ограничены. Например, к их числу относится переименование табли-(ы, переименование существующих и добавление новых полей. PostgreSQL 7.1.x ie позволяет удалять поля из таблиц, поэтому структуру таблицы следует тща-•ельно продумать перед ее созданием.
Модификация таблицы командой ALTER TABLE
В большинстве современных РСУБД предусмотрена возможность модификации таблиц командой ALTER TABLE. Реализация ALTER TABLE в PostgreSQL 7.1.x поддер-кивает шесть типов модификации:
создание полей; назначение и отмена значений по умолчанию; переименование таблицы; переименование полей; дооавление ограничении; смена владельца.Создание полей
Для создания нового поля в команду ALTER TABLE включается секция ADD COLUMN. Синтаксис команды ALTER TABLE с секцией ADD COLUMN:
ALTER TABLE таблица
ADD [ COLUMN ] имя_поля тип_поля
таблица — имя таблицы, в которой создается новое поле; имя_поля — имя создаваемого поля; тип_поля — тип создаваемого поля.Ключевое слово COLUMN не является обязательным и включается в команду лишь для наглядности.
Предположим, в таблицу books базы данных booktown потребовалось включить новое поле publication для хранения даты публикации. Листинг4.8 показывает, как это делается.
Листинг 4.8. Добавление поля
booktown=# ALTER TABLE books
booktown-# ADD publication date;
ALTER
booktown=# \d books
Table "books"
Attribute | Type | Modifier
id integer | not null
title text I not null
authorjd j integer |
subjectjd integer |
publication date j Index: books_id_pkey
Листинг 4.8 показывает, что в таблице books появилось новое поле с именем pub! I cation и типом date. Кроме того, он дает типичный пример плохой координации планирования между разработчиками: в базе данных booktown из нашего примера дата публикации уже хранится в таблице editions, поэтому включать его в таблицу books не нужно. Изменение структуры таблиц после подобных ошибок рассматривается ниже в подразделе «Реструктуризация таблиц».
Назначение и отмена значений по умолчанию
При модификации существующих таблиц наибольшая свобода действий предоставляется при выборе значений по умолчанию. Программист может относительно легко назначать и отменять эти значения для отдельных полей, для чего используется команда ADD TABLE с секцией ALTER COLUMN.
В PostgreSQL команда ALTER TABLE, назначающая или отменяющая значение по умолчанию для поля имя_поля, имеет следующий синтаксис:
ALTER TABLE таблица
ALTER [ COLUMN ] имя_поля
( SET DEFAULT значение ] DROP DEFAULT }
Как и в предыдущем разделе, ключевое слово COLUMN является необязательным включается в команду лишь для наглядности. В листинге 4.9 приведен пример азначения и отмены простой последовательности значений по умолчанию для оля id таблицы books.
Листинг 4.9. Изменение значений по умолчанию
Doktown=# ALTER TABLE books
3oktOwn-# ALTER COLUMN id
x>ktown-# SET DEFAULT nextvalС books.ids'):
JER
}oktown=# \d books
TABLE "books" Attribute | Type | Modifier
id integer not null default nextval('books.ids'::text)
1tle | text not null
juthorjd | integer ;ubject_id | integer j idex: books_id_pkey
)oktown=# ALTER TABLE books )oktown-# ALTER id
)oktown-# DROP DEFAULT;
JER
joktown=# \d books
TABLE "books" Attribute | Type | Modifier
id | integer | not null
;itle | text not null
iuthor_id j integer ;ubject_id j integer idex: books_id_pkey
Переименование таблицы
Переименование таблиц осуществляется командой ALTER TABLE с секцией RENAME, интаксис переименования таблицы:
JER TABLE таблица
RENAME TO новое_иня
Таблицу можно переименовывать сколько угодно раз, это никак не отражается а состоянии хранящихся в ней данных (листинг 4.10). Конечно, в некоторых си-/ацпях переименования нежелательны — например, если таблица используется вешним приложением.
Листинг 4.10. Переименование таблицы
oktown=# ALTER TABLE books RENAME TO literature;
TER
oktown=# ALTER TABLE literature RENAME TO books;
TER
Переименование полей
PostgreSQL позволяет изменять имена полей без изменения данных, хранящихся таблице. Впрочем, переименование полей — дело рискованное, поскольку существующие приложения могут содержать ссылки на имена полей. Если программа обращается к полю по имени, то переименование может нарушить ее работоспособность.
Команда переименования полей имеет следующий синтаксис:
ALTER TABLE таблица
RENAME [ COLUMN ] имя_поля ТО новое_иня_поля
Как и в других командах ALTER TABLE, ключевое слово COLUMN является необязательным. По двум идентификаторам, разделенным ключевым словом ТО, Post-greSQL может определить, что команда переименования относится к одному полю, а не таблице. Пример переименования полей приведен в листинге 4.11.
Листинг 4.11. Переименование поля
booktown=# \d daily_inventory
Table "daily_inventory"
Attribute | Type | Modifier
isbn | text
in_stock | boolean |
booktown=# ALTER TABLE daily_inventory
booktown-# RENAME COLUMN in_stock TO is_in_stock;
ALTER
booktown=# ALTER TABLE daily_inventory
booktown-l RENAME COLUMN is_in_stock TO is_stocked;
ALTER
Добавление ограничений
После создания таблицы сохраняются некоторые возможности добавления ограничении. В PostgreSQL 7.1.x команда ALTER TABLE с секцией ADD CONSTRAINT позволяет определять для полей существующих таблиц только ограничения внешнего ключа и проверки. Команда создания новых ограничений имеет следующий синтаксис:
ALTER TABLE таблице
ADD CONSTRAINT имя_ограничения определение
Синтаксис определения зависит от типа ограничения. В листинге 4.12 продемонстрирован синтаксис создания ограничения внешнего ключа для таблицы editions (связанной с полем id таблицы books) и ограничения проверки для поля type.
Листинг 4.12. Создание новых ограничений в существующей таблице
booktown=# ALTER TABLE editions
booktown-# ADD CONSTRAINT foreign_book
booktown-# FOREIGN KEY (book_id) REFERENCES books (id);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
booktown=# ALTER TABLE editions
booktown-# ADD CONSTRAINT hard_or_paper_back
booktown-# CHECK (type = 'p' OR type = 'h'):
ALTER
Установка ограничения внешнего ключа приводит к тому, что любое значение book_i d в таблице edi ti ons также должно существовать в таблице books. Кроме того, вследствие установленного ограничения проверки поле type в таблице editions может содержать только значения р или Ь.
ПРИМЕЧАНИЕ
Ограничение уникальности также неявно устанавливается при создании уникального индекса командой CREATE INDEX (см. раздел «Индексы» в главе 7).
Дополнительная информация об ограничениях, их назначении и синтаксисе приведена в главе 7.
Смена владельца
По умолчанию создатель таблицы автоматически становится ее владельцем. Владелец обладает всеми правами, связанными с таблицей, в том числе правами предоставления и отзыва прав командами GRANT и REVOKE (см. главу 10). Смена владельца производится командой ALTER TABLE с секцией OWNER. Команда имеет следующий синтаксис:
ALTER TABLE таблица
OWNER TO новый_владелец
Пример смены владельца командой ALTER TABLE приведен в листинге 4.13. Новым владельцем таблицы employee назначается пользователь corwin.
Листинг 4.13. Смена владельца таблицы
booktown=# ALTER TABLE employees booktown-# OWNER TO corwin;
ALTER
ПРИМЕЧАНИЕ
Смена владельца таблицы может осуществляться либо текущим владельцем, либо суперпользователем PostgreSQL.
Реструктуризация таблиц
Несмотря на возможность включения новых полей в существующую таблицу, следует помнить, что в PostgreSQL (во всяком случае, в версии 7.1.x) не поддерживается удаление полей. Существует два относительно простых способа реструктуризации существующих таблиц. Первый способ основан на использовании команды CREATE TABLE AS, а во втором способе команда CREATE TABLE объединяется с командой INSERT INTO.
Фактически оба способа сводятся к созданию новой таблицы требуемой структуры, заполнению ее данными из существующей таблицы и переименованию. Таким образом, новая таблица занимает место старой.
ВНИМАНИЕ
При реструктуризации таблиц следует помнить, что идентификаторы объектов (OID) изменятся, а старые индексы, сгенерированные для исходной таблицы, не будут автоматически действовать в новых таблицах. Все индексы необходимо удалить и построить заново.
Реструктуризация таблиц командой 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).
Листинг 4.14. Реструктуризация таблицы командой CREATE TABLE AS
booktown=# \d books
Table "books" Attribute | Type | Modifier
id | integer | not null
title text I not null
authorjd | integer |
subjectjd integer j
publication j date | Index: books_id_pkey
booktown=# CREATE TABLE new_books
booktown-# (id, title. authorjd, subjectjd)
booktown-# AS SELECT id, title, authorjd, subjectjd
booktown-f FROM books;
SELECT
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books;
ALTER
booktown=# \d books
Table "books" Attribute | Type | Modifier
id | integer |
title | text |
authorjd j integer j
subjectjd | integer |
booktown=# DROP TABLE books;
DROP
ВНИМАНИЕ
В PostgreSQL 7.1.x присутствие в команде SELECT необязательного списка полей, заключенного в круглые скобки, исключает использование специального символа *. Недостаток должен быть исправлен в PostgreSQL 7.2.
Реструктуризация таблиц командами CREATE TABLE и INSERT INTO
Если таблица, создаваемая командой CREATE TABLE AS, вас почему-либо не устраивает (например, если в таблице необходимо установить ограничения полей), то одну команду CREATE TABLE AS можно заменить двумя командами SQL. Сначала команда CREATE TABLE создает новую таблицу, а затем команда INSERT INTO с запросом SELECT заполняет ее данными (листинг 4.15).
Листинг 4.15. Реструктуризация таблицы командами CREATE TABLE и INSERT INTO
booktownHf CREATE TABLE new_books (
booktown(# id integer UNIQUE,
booktown(# title text NOT NULL.
booktown(# authorjd integer.
booktown(# subjectjd integer.
booktown(# CONSTRAINT books_id_pkey PRIMARY КЕУ (id)
booktown(# ):
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index ' books Jd_pkey'
for table 'new_books'
CREATE
booktown=# INSERT INTO new_books
booktown-# SELECT id, title, author_id, subjectjd
booktown-f FROM books;
INSERT 0 12
booktown=# ALTER TABLE books RENAME TO old_books;
ALTER
booktown=# ALTER TABLE new_books RENAME TO books:
ALTER
booktown=# \d books
Table "books" Attribute | Type | Modifier
id | integer | not null
title ( text I not null
authorjd integer j
subjectjd integer | Index: booksjd_pkey
За дополнительной информацией об использовании команды INSERT INTO с командой SELECT обращайтесь к подразделу «Вставка данных из других таблиц командой SELECT» раздела «Добавление данных командами INSERT и COPY». Команда SELECT рассматривается в разделе «Выборка данных командой SELECT».
Удаление таблиц командой DROP TABLE
В SQL таблицы удаляются командой DROP TABLE. Команда имеет следующий синтаксис (таблица — имя удаляемой таблицы):
DROP TABLE таблица
Использование команды DROP TABLE требует осторожности, поскольку удаление таблицы приводит к уничтожению всех хранящихся в ней данных.
ПРИМЕЧАНИЕ
Уничтожение таблицы с неявно созданным индексом приводит к уничтожению всех связанных с ней индексов.
Добавление данных командами INSERT и COPY
После создания таблицы с заданной структурой наступает следующий этап — заполнение таблицы данными. В PostgreSQL имеются три общих способа заполнения таблиц данными:
вставка новых группированных данных командой INSERT INTO; вставка существующих данных из другой таблицы командой INSERT INTO в сочетании с командой SELECT; вставка данных из внешнего файла командой COPY (или \copy).Вставка новых данных
Ниже приведен синтаксис команды INSERT INTO при вставке новых данных:
INSERT INTO таблица
[ ( имя_поля [, ...] ) ] VALUES ( значение [. ...] )
Ниже перечислены параметры команды.
таблица. Имя таблицы, в которую вставляются данные командой SQL INSERT. ( имя_поля [. ...] ). Необязательный группированный список полей новой записи, которым присваиваются значения. VALUES. Ключевое слово SQL, за которым следует группированный список значений. ( значение [, ... ] ). Обязательный группированный список значений полей. Для каждого поля указывается ровно одно значение, элементы списка разделяются запятыми. Элемент списка может быть выражением (например, операцией с двумя операндами) или константой.Тип каждого значения в секции VALUES должен соответствовать типу поля, которому оно присваивается. Если необязательный список полей отсутствует, PostgreSQL предполагает, что секция VALUES содержит значения всех полей в структуре таблицы в порядке их определения. Если количество значений меньше количества полей, PostgreSQL пытается использовать значение по умолчанию (или NULL при его отсутствии) для каждого пропущенного элемента.
В листинге 4.16 приведен пример создания новой записи в таблице books базы данных booktown.
Листинг 4.16. Вставка новой записи в таблицу books
booktown=# INSERT INTO books (Id, title, author_id, subject_id)
booktown-# VALUES (41472, 'Practical PostgreSQL', 1212, 4);
INSERT 3574037 1
Команда SQL, приведенная в листинге 4.16, вставляет новую запись с кодом (id) 41472, названием «Practical PostgreSQL», кодом автора 1212 и кодом темы 4. Обратите внимание на завершающее сообщение, начинающееся со слова INSERT, — оно указывает на то, что операция вставки была выполнена успешно. Первое число после INSERT является идентификатором объекта (OID) созданной записи, а второе число обозначает количество созданных записей (в нашем примере,— 1).
В приведенном примере необязательный список полей совпадает с порядком следования полей в структуре таблицы (слева направо). В данном случае этот список можно опустить, поскольку команда INSERT предполагает, что значения присваиваются в естественном порядке следования полей таблицы. Поля в списке можно переставить, но в этом случае порядок значений в секции VALUES тоже должен измениться, как показано в листинге 4.17.
Листинг 4.17. Изменение порядка перечисления полей
booktown=# INSERT INTO books (subjected, author_id, id, title)
booktown-# VALUES (4, 7805, 41473, 'Programming Python');
INSERT 3574041 1
Вставка данных из других таблиц командой SELECT
Команда INSERT INTO применяется и в другой ситуации — когда данные, сохраняемые в таблице, уже присутствуют в другой таблице (или неекольких таблицах). В этом случае команда имеет следующий синтаксис:
INSERT INTO таблица
[ ( имя_поля [. ...] ) ] запрос
По аналогии с синтаксисом INSERT INTO, представленным в предыдущем подразделе, команда содержит необязательный список полей, которым присваиваются новые значение. Тем не менее в этой форме INSERT INTO секция VALUES заменяется полной командой SQL SELECT.
Предположим, база данных booktown содержит таблицу book_queue с информацией о книгах, ожидающих поступления в продажу. После подтверждения данные переносятся из таблицы book_queue в обычную таблицу books. Пример решения этой задачи продемонстрирован в листинге 4.18.
Листинг 4.18. Вставка данных из другой таблицы
booktown-# INSERT INTO books (id, title, author_id, subject_id)
booktown-# SELECT nextval('book_ids', title, author_id, subject_id
booktown-# FROM book_queue WHERE approved;
INSERT 0 2
В приведенном примере запрос SELECT, включенный в команду INSERT INTO, переносит две записи из таблицы book_queue в таблицу books. В этом контексте допускается использование любой синтаксически правильной команды SELECT. В нашем примере в выборку включается результат вызова функции nextval () для последовательности bookj ds, за которым следуют значения полей title, author_id и subject_id из таблицы book_queue.
На этот раз команда создает сразу несколько новых записей, поэтому в сообщении об успешном выполнении операции вместо значения OID, которое выводилось бы при вставке одной записи, выводится 0. Второе число, как и в случае с обычной командой INSERT INTO, равно количеству созданных записей (в данном случае — 2).
Копирование данных из внешних файлов командой COPY
В PostgreSQL поддерживается и такая полезная возможность, как прямое импортирование данных в таблицу из внешних файлов командой COPY. Файл, содержащий входные данные команды COPY, хранится либо в стандартном текстовом формате ASCII с ограничением полей специальным символом-разделителем, либо в двоичном формате таблиц PostgreSQL. В ASCII-файлах в качестве разделителя обычно используется символ табуляции или запятая. При импортировании данных из ASCII-файла каждая строка файла интерпретируется как отдельная запись данных, а каждый компонент строки — как значение соответствующего поля записи.
Команда COPY FROM работает значительно быстрее обычной команды INSERT, поскольку данные передаются прямо в приемную таблицу за одну транзакцию. С другой стороны, к формату исходного файла предъявляются чрезвычайно жесткие требования, поэтому ошибка всего в одной строке приводит к сбою всей команды COPY.
Синтаксис команды COPY FROM:
COPY [ BINARY ] таблица [ WITH 0IDS ]
FROM { 'имя_файла' \ stdin }
[ [USING] DELIMITERS 'разделитель' ]
[ WITH NULL AS 'строка_nulГ ]
Ниже поясняется смысл параметров команды.
BINARY. Признак импортирования входных данных из двоичного файла, ранее созданного командой COPY TO. таблица. Имя таблицы, в которую импортируются данные. WITH OIDS. Из первой строки файла загружаются значения всех идентификаторов OID импортируемой таблицы. FROM { 'имя_файла' \ stdin }. Источник, из которого PostgreSQL получает входные данные — файл с заданным именем либо стандартный ввод (stdin). [ USING ] DELIMITERS 'разделитель'. Символ, используемый в качестве разделителя при разборе входных данных. Не используется для файлов, выведенных в двоичном формате PostgreSQL. WITH NULL AS ' строка_null. Заданная строка должна интерпретироваться как значение NULL. He используется для файлов, выведенных в двоичном формате PostgreSQL.При подготовке к импортированию файла проследите за тем, чтобы этот файл был доступен для чтения процессом postmaster (то есть пользователем, запустившим PostgreSQL). Кроме того, разрешены только абсолютные имена файлов; при попытке передать относительное имя происходит ошибка.
При работе с входными файлами в формате ASCII в секции DELIMITERS передается символ, используемый в качестве разделителя значений полей в строках файла. Если разделитель не указан, PostgreSQL считает, что значения разделяются символом табуляции. Необязательная секция WITH NULL определяет формат, в котором передаются значения NULL. Если секция отсутствует, PostgreSQL интерпретирует последовательность \N как NULL (например, пустые поля исходного файла по умолчанию интерпретируются как пустые строковые константы, а не как NULL).
Если данные вводятся вручную или передаются терминалу другой программой, в качестве источника в секции FROM можно указать стандартный ввод (stdin). При получении данных из стандартного ввода входной поток должен завершаться последовательностью \. (обратная косая черта плюс точка), за которой немедленно следует символ новой строки.
В листинге 4.19 приведено содержимое файла, выведенного PostgreSQL в формате ASCII. Поля разделяются запятыми, а для представления NULL используется строка \null.B файле сохранены данные из таблицы subjects базы данных booktown.
Листинг 4.19. Пример копируемого ASCII-файла
1.Business.Productivity Ave
2.Children's Books,Kids Ct
3.Classics.Academic Rd
4,Computers,Productivity Ave
5,Cooking.Creativity St
12.Religion.\null
8.Hi story.Academic Rd
9.Horror.Black Raven Dr
10.Mystery.Black Raven Dr
11.Poetry.Sunset Dr
13.Romance.Main St
14.Science.Productivity Ave
15.Science Fiction.Main St
0.Arts.Creativity St
6.Drama.Main St
7.Entertainment.Main St
Следующая команда (листинг 4.20) импортирует содержимое файла /tmp/ subjects.sql в таблицу subjects базы данных booktown.
Листинг 4.20. Копирование ASCII-файла
booktown=# COPY subjects FROM '/tmp/subjects.sql'
booktown-# USING DELIMITERS '.' WITH NULL AS '\null;
COPY
Двоичный формат
Команда COPY также позволяет выполнять операции ввода и вывода с данными в двоичном формате. Если команда COPY FROM содержит ключевое слово BINARY, входной файл должен быть создан командой COPY TO в двоичном формате PostgreSQL. Двоичные файлы загружаются быстрее ASCII-файлов, но в отличие от последних их нельзя читать и редактировать в простых текстовых редакторах.
В листинге 4.21 приведена команда COPY, предназначенная для вставки записей из двоичного файла в таблицу subjects базы данных booktown.
Листинг 4.21. Копирование двоичного файла
booktown=# COPY BINARY subjects FROM '/tmp/subjects.sql';
COPY
Различия команд COPY и \copy
Команда SQL COPY не эквивалентна команде psql \copy. Команда \copy имеет тот же синтаксис (хотя и не завершается точкой с запятой), она выполняет операцию че-
рез клиента psql, а не через серверный процесс postmaster. В результате команда \copy выполняется с правами пользователя, запустившего psql, а не с теми правами, с которыми работает процесс postmaster.
COPY TO
Синтаксис команды COPY FROM практически аналогичен синтаксису команды, предназначенной для экспортирования данных в файл, просто ключевое слово FROM заменяется ключевым словом ТО. Кроме того, ключевое слово stdin заменяется ключевым словом stdout, если выходные данные вместо файла направляются в стандартный вывод (например, на экран в psql). В листинге 4.22 приведен пример экспортирования таблицы books в ASCII-файл.
Листинг 4.22. Экспортирование таблицы books в файл ASCII
booktown=# COPY books TO 'filename';
COPY
Секция WITH OIDS
Файлы, содержащие идентификаторы объектов (созданные командой COPY TO с секцией WITH OIDS), загружаются командой COPY FROM, в которую также включается секция WITH OIDS. Попытки использования команды COPY FROM с секцией WITH OIDS по отношению к файлу, в котором значения OID не были сохранены при создании, завершаются неудачей.
Экспортирование данных с идентификаторами объектов принадлежит к числу специальных возможностей, присущих только команде COPY. Значения OID являются системными и поэтому не могут изменяться командами INSERT и UPDATE. Если не принять меры предосторожности, в таблице могут появиться две записи с одинаковыми значениями OID.
Выборка данных командой 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 ] синоним [ ( синоним_поля [. ...] ) ] }
Ниже описаны компоненты секции FROM.
[ ONLY ] таблица. Имя таблицы, используемой в качестве источника для команды SELECT. Ключевое слово ONLY исключает из запроса записи всех таблиц-потомков. [ AS ] синоним. Источникам FROM могут назначаться необязательные псевдонимы, упрощающие запрос (например, на таблицу books можно ссылаться по псевдониму Ь). Ключевое слово AS является необязательным. ( запрос ) [ AS ] синоним. В круглых скобках находится любая синтаксически правильная команда SELECT. Итоговый набор, созданный запросом, используется в качестве источника FROM так, словно выборка производится из статической таблицы. При выборке из подзапроса обязательно должен назначаться синоним. ( синоним_поля [. ...] ). Синонимы могут назначаться не только всему источнику, но и его отдельным полям. Перечисляемые синонимы полей разделяются запятыми и группируются в круглых скобках за синонимом источника FROM. Синонимы перечисляются в порядке следования полей в таблице, к которой они относятся.Простая команда SELECT
В простейшем случае команда SELECT выбирает из заданной таблицы все данные (то есть все записи и все поля). Полная выборка данных производится командой
SELECT * FROM имя таблицы;
Как упоминалось при описании синтаксиса SELECT, звездочка (*) является сокращенным обозначением всех несистемных полей. Таким образом, команда SELECT * выбирает из указанной таблицы все поля и все записи, поскольку количество записей в итоговом наборе не ограничивается. В листинге 4.23 приведен пример выборки всех записей (*) из таблицы books базы данных booktown.
Листинг 4.23. Выборка всех записей из таблицы books
300ktown=# SELECT * FROM books;
id | title | author_id | subject_id
7808 | The Shining | 4156 | 9
4513 | Dune | 1866 | 15
4267 | 2001: A Space Odyssey | 2001 | 15
1608 | The Cat in the Hat | 1809 | 2
1590 | Bartholomew and the Oobleck | 1809 | 2
25908 | Franklin in the Dark | 15990 | 2
1501 | Goodnight Moon | 2031 | 2
190 | Little Women | 16 | 6
1234 | The Velveteen Rabbit | 25041 | 3
2038 | Dynamic Anatomy | 1644 | 0
156 | The Tell-Tale Heart | 115 | 9
41472 | Practical PostgreSQL | 1212 | 4
41473 | Programming Python | 7805 | 4
41477 | Learning Python 7805 | 4
41478 | Perl Cookbook | 7806 | 4
(15 rows)
Выбор полей
Команда SELECT * является хорошим примером простейшего запроса, но на прак-гике необходимая информация нередко ограничивается несколькими полями габлицы. Чтобы повысить эффективность выборки и сделать запрос более наглядным, рекомендуется явно перечислить все необходимые поля вместо полной вы-эорки с символом *. В частности, ограничение выборки особенно актуально при использовании секции JOIN, подробно рассматриваемой ниже в подразделе «Объединение наборов данных».
Поля, включаемые в итоговый набор, перечисляются после ключевого слова SELECT. Запрос возвращает данные только для полей, входящих в этот список. По-эядок перечисления полей не обязан совпадать с их порядком в структуре табли-ды; допускается как многократное вхождение, так и отсутствие некоторых полей в :писке. Пример приведен в листинге 4.24.
Листинг 4.24. Изменение порядка следования полей при выборке
booktown=# SELECT Id, author_id, title, id
booktown-# FROM books;
id | authorjd | title | id
7808 | 4156 | The Shining | 7808
4513 | 1866 | Dune | 4513
4267 | 2001 | 2001: A Space Odyssey | 4267
1608 | 1809 | The Cat in the Hat | 1608
1590 | 1809 | Bartholomew and the Oobleck | 1590
25908 | 15990 | Franklin in the Dark | 25908
1501 | 2031 | Goodnight Moon | 1501
190 | 16 | Little Women | 190
1234 | 25041 | The Velveteen Rabbit | 1234
2038 | 1644 | Dynamic Anatomy | 2038
156 | 115 | The Tell-Tale Heart | 156
41472 | 1212 | Practical PostgreSQL | 41472
41473 | 7805 | Programming Python | 41473
41477 | 7805 | Learning Python | 41477
41478 | 7806 | Perl Cookbook | 41478
(15 rows)
Как нетрудно убедиться, команды SELECT в листингах 4.23 и 4.24 возвращают одинаковые наборы данных. Во втором наборе используется другой порядок следования полей (поле subject_id отсутствует, а поле id встречается дважды), что связано с явным перечислением полей после ключевого слова SELECT.
Выражения, константы и синонимы
Целями команды SELECT могут быть не только простые поля, но и произвольные выражения (включающие вызовы функций или различные операции с идентификаторами) и константы. Синтаксис команды практически не изменяется, появляется лишь одно дополнительное требование — все самостоятельные выражения, , идентификаторы и константы должны разделяться запятыми. В списке разрешены произвольные комбинации разнотипных целей.
Команда SELECT также может использоваться для простого вычисления и вывода результатов выражений и констант. В этом случае она не содержит секции FROM или имен столбцов (листинг 4.25).
Листинг 4.25. Выражения и константы
testdb=# SELECT 2+2,
testdb-# pi (),
testdb-# 'PostgreSQL is more than a calculator!';
?column?| pi | ?column?
4 | 3.14159265358979 | PostgreSQL
is more than a calculator!
(1 row)
Для каждой цели в списке может задаваться необязательная секция AS, которая назначает синоним (новое произвольное имя) для каждого поля в итоговом наборе. Имена синонимов подчиняются тем же правилам, что и имена обычных идентификаторов (в частности, они могут содержать внутренние пробелы или совпадать с ключевыми словами при условии заключения их в апострофы и т. д.)
Назначение синонима не влияет на исходное поле и действует лишь в контексте итогового набора, возвращаемого запросом. Секция AS особенно удобна при «выборке» выражений и констант, поскольку синонимы позволяют уточнить смысл неочевидных выражений или констант. В листинге 4.26 приведены те же результаты, что и в листинге 4.25, но с другими названиями полей.
Листинг 4.26. Секция AS с выражениями и константами
booktown=# SELECT 2 + 2 AS "2 plus 2",
booktown-# pi() AS "the pi fnction",
booktown-# 'PostgreSQL is more than a calculator!' AS comments;
2 plus 2 | the pi function | comments
4 | 3.14159265358979 | PostgreSQL is more than a calculator!
(1 row)
Выбор источников в секции FROM
В секции FROM указывается источник данных — таблица или итоговый набор. Секция может содержать несколько источников, разделенных запятыми. Результат подобного перечисления функционально эквивалентен перекрестному объединению, о котором рассказано в подразделе «Объединение наборов данных».
Использование нескольких источников данных в PostgreSQL требует осторожности. В результате выполнения команды SELECT для нескольких источников без секций WHERE и JOIN, уточняющих связи между источниками, возвращается полное декартово произведение источников. Иначе говоря, итоговый набор содержит все возможные комбинации записей из всех источников.
Обычно для уточнения связей между источниками, перечисленными через запятую в секции FROM, используется секция WHERE. Пример приведен в листинге 4.27 (за дополнительной информацией о секции WHERE обращайтесь к подразделу «Уточнение запросов»).
Листинг 4.27. Выборка из нескольких таблиц
booktown=# SELECT books.id, title, authors_id, last_name
booktown-# FROM books, authors
booktown-# WHERE books.authored = authors.id;
id | title | id | last_name
190 | Little Women | 16 | Alcott
156 | The Tell-Tale Heart | 115 | Рое
41472 | Practical PostgreSQL | 1212 | Worsley
2038 | Dynamic Anatomy | 1644 | Hogarth
1608 | The Cat in the Hat | 1809 | Geisel
1590 |Bartholomew and the Oobleck | 1809 | Geisel
4513 | Dune | 1866 | Herbert
4267 | 2001: A Space Odyssey | 2001 | Clarke
1501 | Goodnight Moon | 2031 | Brown
7808 | The Shining | 4156 | King
41473 |Programming Python | 7805 | Lutz
41477 |Learning Python | 7805 | Lutz
41478 | Perl Cookbook | 7806 | Christiansen
25908 | Franklin in the Dark | 15990 | Bourgeois
1234 | The Velveteen Rabbit | 25041 | Bianco
(15 rows)
При использовании ссылок на имена полей, относящихся к разным источникам, может возникнуть неоднозначность. Предположим, команда SELECT получает исходные данные из таблиц books и authors. В каждой из этих таблиц имеется поле с именем id. Без дополнительных уточнений PostgreSQL не сможет определить, к какой таблице относится ссылка на поле i d в следующей команде:
booktown=# SELECT id FROM books, authors
ERROR: Column reference "id" is ambiguous
Для предотвращения неоднозначности в «полные» имена столбцов включается имя таблицы. При этом используется специальный синтаксис, называемый точечной записью (название связано с тем, что имя таблицы отделяется от имени поля точкой). Например, books .id означает поле id таблицы books.
Точечная запись обязательна только при наличии неоднозначности между наборами данных. Как показано в листинге 4.27, ссылка может состоять только из имени поля — при условии, что это имя уникально во всех наборах данных, перечисленных в секции FROM. В приведенном примере поле title присутствует только в таблице books, а поле last_name входит только в таблицу authors, поэтому на их имена можно ссылаться без уточнения.
Если в качестве источника используется итоговый набор, созданный подзапросом, то весь подзапрос заключается в круглые скобки. По наличию круглых скобок PostgreSQL узнает о том, что команда SELECT интерпретируется как подзапрос, и выполняет ее перед выполнением внешней команды SELECT.
В листинге 4.28 приведен несколько необычный запрос, который производит выборку значений всех полей (*) таблицы books с использованием подзапроса. Затем из полученного набора «выбирается» строковая константа test и значение поля id.
Листинг 4.28. Выборка из подзапроса
booktown=# SELECT 'test' AS test, id
booktown-# FROM (SELECT * FROM books)
booktown-# AS example_sub_query;
test | id
test | 7808
test | 4513
test | 4267
test | 1608
test | 1590
test | 25908
test | 1501
test | 190
test | 1234
test | 2038
test | 156
test | 41472
test | 41473
test | 41477
test | 41478
(15 rows)
Странность запроса в листинге 4.28 заключается в том, что общий результат принципиально не отличается от прямой выборки из таблицы books, поскольку результат подзапроса идентичен ее содержимому. Строковая константа из одной команды SELECT комбинируется с данными из набора, полученного другой командой SELECT. Более реалистичный пример использования подзапросов приведен ниже в разделе «Подзапросы», но сначала необходимо лучше разобраться в том, как работает команда SELECT.
ПРИМЕЧАНИЕ
Необязательное ключевое слово ONLY перед именем таблицы, которая является базовой по отношению к другим таблицам, означает, что выборка из производных таблиц не выполняется (наследование рассматривается в главе 7).
Синонимы источников данных в секции FROM
Источникам данных в секции FROM — таблицам, подзапросам и т. д. — можно назначать синонимы в секции AS (по аналогии с отдельными полями). Синонимы часто используются для упрощения точечной записи, о которой говорилось в преды-
дущем разделе. Наличие синонима для набора данных позволяет обращаться к нему при помощи точечной записи, что делает команды SQL более компактными и наглядными. В листинге 4.29 приведен запрос из листинга 4.27 с упрощением точечной записи при помощи секции AS.
Листинг 4.29. Определение синонимов для источников данных
booktown=# SELECT b.id, title, a.id, last_name
booktown-# FROM books AS b, authors AS a
booktown-# WHERE b.author_id = a.id;
id | title | id lastjname
190 | Little Women | 16 | Alcott
156 | The Tell-Tale Heart | 115 | Рое
41472 | Practical PostgreSQL | 1212 | Worsley
2038 | Dynamic Anatomy | 1644 | Hogarth
1608 | The Cat in the Hat | 1809 | Geisel
1590 | Bartholomew and the Oobleck | 1809 | Geisel
4513 | Dune | 1866 | Herbert
4267 | 2001: A Space Odyssey | 2001 | Clarke
1501 | Goodnight Moon | 2031 | Brown
7808 | The Shining | 4156 | King
41473 | Programming Python | 7805 | Lutz
41477 | Learning Python | 7805 | Lutz
41478 | Perl Cookbook | 7806 | Christiansen
25908 | Franklin in the Dark 15990 | Bourgeois
1234 | The Velveteen Rabbit | 25041 | Bianco
(15 rows)
Синонимы можно назначать не только для источников данных в секции FROM, но и для отдельных полей внутри этих источников. Для этого за синонимом источника данных приводится список синонимов полей, разделенный запятыми и заключенный в круглые скобки. Список синонимов полей представляет собой последовательность идентификаторов, перечисленных в порядке следования полей в структуре таблицы (слева направо).
Список синонимов полей не обязан содержать данные обо всех полях. К тем полям, для которых не были заданы синонимы, можно обращаться по обычным именам. Если единственное поле, которому требуется назначить синоним, находится после других полей, вам придется включить в список все предшествующие поля (синоним поля может совпадать с его именем). В противном случае PostgreSQL не сможет определить, какому полю назначается синоним, и решит, что речь идет о первом поле таблицы.
ПРИМЕЧАНИЕ
Ключевое слово AS не является обязательным. Если оно отсутствует в команде, PostgreSQL считает, что все идентификаторы после ключевого слова FROM являются синонимами.
В листинге 4.30 приведен запрос из листинга 4.29, в котором полям id обеих таблиц назначаются уникальные идентификаторы, что позволяет ссылаться на них непосредственно (то есть без применения точечной записи). Синтаксис остается прежним, но на этот раз синоним назначается только полю id таблицы books, в результате ссылка на поле id таблицы authors становится однозначной.
Листинг 4.30. Определение синонимов для полей
booktown=# SELECT the_book_id, title, id, last_name
booktown-# FROM books AS b (the_book_id), authors
booktown-# WHERE author_id = id;
id | title id | last_name
190 | Little Women | 16 | Alcott
156 | The Tell-Tale Heart | 115 Рое
41472 | Practical PostgreSQL | 1212 | Worsley
2038 | Dynamic Anatomy | 1644 | Hogarth
1608 | The Cat in the Hat | 1809 | Geisel
1590 | Bartholomew and the Oobleck | 1809 | Geisel
4513 | Dune | 1866 | Herbert
4267 | 2001: A Space Odyssey | 2001 | Clarke
1501 | Goodnight Moon | 2031 | Brown
7808 | The Shining | 4156 | King
41473 | Programming Python | 7805 | Lutz
41477 | Learning Python | 7805 | Lutz
41478 | Perl Cookbook | 7806 | Christiansen
25908 | Franklin in the Dark | 15990 | Bourgeois
1234 | The Velveteen Rabbit | 25041 | Bianco
(15 rows)
Удаление дубликатов и ключевое слово DISTINCT
Необязательное ключевое слово DISTINCT исключает дубликаты из итогового набора. Если ключевое слово ON отсутствует, из результатов запроса с ключевым словом DISTINCT исключаются записи с повторяющимися значениями целевых полей. Проверяются только поля, входящие в целевой список SELECT.
Предположим, таблица books содержит 15 записей, в каждой из которых присутствует поле authorjd. Некоторые коды авторов многократно встречаются в таблице books. Включение в запрос ключевого слова DISTINCT (листинг 4.31) гарантирует, что итоговый набор будет содержать не более одной записи для каждого автора.
Листинг 4.31. Ключевое слово DISTINCT
booktown=# SELECT DISTINCT author_id
booktown-# FROM books;
author_id
16
115
1212
1644
1809
1866
2001
2031
4156
7805
7806
15990
25041
(13 rows)
booktown=# SELECT DISTINCT ON (author_id)
booktown-# author_id. title
booktown-# FROM books;
author_id | ntitle
16 | Little Women
115 | The Tell-Tale Heart
1212 | Practical PostgreSQL
1644 | Dynamic Anatomy
1809 | The Cat in the Hat
1866 | Dune
2001 | 2001: A Space Odyssey
2031 | Goodnight Moon
4156 | The Shining
7805 | Programming Python
7806 | Perl Cookbook
15990 |Franklin in the Dark
25041 | The Velveteen Rabbit
(13 rows)
Первый запрос в листинге 4.31 возвращает только 13 записей из таблицы books, хотя таблица содержит 15 записей. Два автора, написавшие по две книги, вошли в итоговый набор лишь в одном экземпляре.
Во втором запросе использована другая форма DISTINCT с явным перечислением полей (или выражений), проверяемых на наличие дубликатов. В этом случае запрос также возвращает 13 записей, поскольку секция ON указывает, что дубликаты проверяются по значению поля author_i d. Без секции ON запрос верн)и бы все 15 записей, поскольку по умолчанию PostgreSQL проверяет полное совпадение всех полей.
В общем случае PostgreSQL выбирает записи, исключаемые из итогового набора при наличии секции ON, по своему усмотрению. Если в запрос вместе с DISTINCT входит секция ORDER BY, вы можете самостоятельно задать порядок выборки полей так, чтобы нужные записи оказались в начале. Сортировка записей рассматривается в подразделе «Сортировка записей».
Если вместо исключения всех дубликатов достаточно сгруппировать записи с повторяющимися значениями некоторого критерия, воспользуйтесь секцией GROUP BY, описанной в подразделе «Группировка записей».
Уточнение запросов
В секции WHERE задается логическое условие, которому должны удовлетворять записи итогового набора. На практике команда SELECT почти всегда содержит как минимум одну уточняющую секцию WHERE.
Предположим, вы хотите получить список всех книг о компьютерных технологиях в базе данных booktown. У этих книг поле subject_id равно 4. Соответственно в секцию WHERE включается оператор =, который проверяет это условие. Пример приведен в листинге 4.32.
Листинг 4.32. Простая секция WHERE
booktown=# SELECT * FROM books
booktown-# WHERE subject_id = 4;
id | title | author_id | subject_id
41472 | Practical PostgreSQL | 1212 | 4
41473 | Programming Python | 7805 | 4
41477 | Learning PostgreSQ L | 7805 | 4
41478 | Perl Cookbook | 7806 | 4
(4 rows)
Запрос из листинга 4.32 возвращает только те записи, у которых поле subject_id совпадает с целочисленной константой 4. Итоговый набор содержит всего 4 записи книг о компьютерах вместо 15 записей, приведенных в листинге 4.23.
Секция WHERE может содержать несколько условий, объединенных логическими операторами (например, AND или OR) и возвращающими одно логическое значение. Допустим, вас интересуют все записи для книг о компьютерах, которые, кроме того, что они о компьютерах, написаны Марком Лутцем. Запрос уточняется объединением двух условий при помощи логического оператора AND. Возможен и другой вариант — например, поиск всех книг, посвященных компьютерным технологиям или искусству; в этом случае два условия объединяются логическим оператором OR. В листинге 4.33 продемонстрированы оба сценария с ключевыми словами AND и OR.
Листинг 4.33. Объединение условий в секции WHERE
booktown=# SELECT title FROM books
booktown-# WHERE subject_id = 4
booktown-# AND author_id = 7805;
title
Programming Python
Learning Python
(2 rows)
booktown=# SELECT title FROM books
booktown-# WHERE subject_id = 4
booktown-# AND author_id = 0;
title
Dynamic Anatomy
Practical PostgreSQL
Programming Python
Learning Python
Perl Cookbook
(5 rows)
Первая команда SELECT в листинге 4.33 содержит два условия, объединенных логическим оператором AND. Первое условие проверяет, что книга посвящена компьютерным технологиям (поле subject_id равно 4), а второе — что автором книги является Марк Лутц (поле author_id равно 7805). Объединение условий уменьшает объем итогового набора — в него входят всего две записи, удовлетворяющие обоим условиям.
Во второй команде SELECT в листинге 4.33 прежнее первое условие (книги по компьютерной тематике) объединяется со вторым условием: книги по искусству (поле subject_id равно 0). В результате объем итогового набора увеличивается до пяти записей, каждая из которых удовлетворяет хотя бы одному из этих условий.
Количество условий, объединяемых в секции WHERE, не ограничено, хотя при наличии двух и более условий обычно выполняется группировка при помощи круглых скобок, наглядно демонстрирующая логическую связь между условиями. Листинг 4.34 показывает, как простое добавление круглых скобок влияет на результат выполнения команды.
Листинг 4.34. Группировка условий при помощи круглых скобок
booktown=# SELECT * FROM books
booktown-# WHERE author_id = 1866
booktown-# AND subject_id = 15
booktown-# OR subject_id = 3;
id | title | autho_id | subject_id
4513 | Dune | 1866 | 15
1234 | The Velveteen Rabbit | 25041 | 3
(2 rows)
booktown=# SELECT * FROM books
booktown-# WHERE author_id = 1866
booktown-# AND (subject_id = 15
booktown-# OR subject_id = 3);
id | title | author_id | subject_id
4513 | Dune | 1866 | 15
(1 row)
В этом примере продемонстрированы две попытки выборки из базы данных booktown записей, у которых поле author_id равно 1866. Кроме того, поле subject_id должно быть равно либо 15, либо 3. Как видно из результатов первой команды, при перечислении всех трех условий без круглых скобок команда интерпретируется неправильно. Добавление круглых скобок приводит к тому, что вычисления в скобках производятся до проверки внешних условий.
Объединение наборов данных
Как было показано в примере использования секции WHERE для выборки из двух таблиц (см. подраздел «Выбор источников в секции FROM»), существует возможность выборки данных из разных источников с объединением их полей. В SQL этот процесс формально называется объединением (join).
В результате объединения двух или более наборов данных создается новый набор записей, состоящих из всех полей исходных наборов. Базовый вариант объединения представляет собой декартово произведение, то есть совокупность всех возможных комбинаций двух наборов. Далее из этого произведения отбирается часть записей по критериям, заданным в секции JOIN.
Существуют три разновидности объединений.
Перекрестные объединения (CROSS JOIN). Декартово (перекрестное) произведение двух наборов данных. Произведение не определяет отношений между наборами, а лишь содержит все возможные комбинации записей объединяемых наборов. Внутренние объединения (INNER JOIN). Подмножество декартова произведения двух наборов данных с критерием, используемым для объединения записей. Критерий возвращает логическую величину — признак вхождения записи в объединенный набор. Внешние объединения (OUTER JOIN). Как и внутренние объединения, содержат критерий объединения записей, но обязательно возвращают минимум один экземпляр каждой записи заданного набора. Это может быть левый набор (источник данных слева от ключевого слова JOIN), правый набор (источник данных справа от ключевого слова JOIN) или оба набора в зависимости от конкретной разновидности внешнего объединения. Пустые поля в тех частях записей, которые не отвечают критерию объединения, содержат NULL.Перекрестные объединения
Результат перекрестного объединения принципиально не отличается от перечисления источников через запятую. Следовательно, в команде выборки с перекрестным объединением практически всегда должна присутствовать секция WHERE, уточняющая связи между объединенными наборами данных. В листинге 4.35 приведен запрос из листинга 4.27, в котором перечисление источников заменено формальным синтаксисом JOIN.
Листинг 4.35. Простой перекрестный запрос
booktown=# SELECT b.id, title, a.id, last_name
booktown-# FROM books AS b CROSS JOIN authors AS a
booktown-# WHERE b.author_id = a.id;
id | title | id | last_name
190 | Little Women | 16 | Alcott
156 | The Tell-Tale Heart | 115 | Рое
41472 | Practical PostgreSQL | 1212 | Worsley
2038 | Dynamic Anatomy | 1644 | Hogarth
1608 | The Cat in the Hat | 1809 | Geisel
1590 |Bartholomew and the Oobleck | 1809 | Geisel
4513 | Dune | 1866 | Herbert
4267 | 2001:Space Odyssey | 2001 | Clarke
1501 | Goodnight Moon | 2031 | Brown
7808 | The Shining | 4156 | King
41473 | Programming Python | 7805 | Lutz
41477 | Learning Python | 7805 | Lutz
41478 | Perl Cookbook | 7806 | Christiansen
25908 | Franklin in the Dark | 15990 | Bourgeois
1234 | The Velveteen Rabbit | 25041 | Bianco
(15 rows)
Синтаксис CROSS JOIN всего лишь более формально выражает отношения между двумя наборами данных. Между синтаксисом CROSS JOIN и простым перечислением таблиц через запятую нет никаких функциональных различий.
Внутренние и внешние объединения
На практике чаще используются внутренние и внешние объединения, при которых секция 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.
Листинг 4.36. Сравнение команд INNER JOIN и WHERE
booktown=# SELECT title, last_name, first_name
booktown-# FROM books, authors
booktown-# WHERE (books.author_id = authors.id)
booktown-# AND last_name = 'Geisel':
title last_name | first_name
The Cat in the Hat | Geisel | Theodor Seuss
Bartholomew and the Oobleck | Geisel | Theodor Seuss
(2 rows)
booktown=# SELECT title, last_name, first_name
booktown-# FROM books AS b INNER JOIN authors AS a
booktown-# ON (b.author_id = a.id)
Dooktown-# WHERE last_name = 'Geisel';
title | last_name | first_name
The Cat in the Hat | Geisel | Theodor Seuss
Bartholomew and the Oobleck | Geisel | Theodor Seuss
(2 rows)
Две синтаксические формы в листинге 4.36 функционально идентичны и возвращают одинаковые результаты. Синтаксис INNER JOIN позволяет отделить критерий связи источников от критерия выбора записей, поскольку связи определяются только в секции ON. Это существенно упрощает чтение и модификацию запросов, поскольку программисту не приходится разбираться в смысле каждого условия в секции WHERE.
Обратите внимание: во втором запросе продемонстрировано назначение синонимов а и b в секции ON для таблиц books и authors соответственно. Подобное использование синонимов в секции ON абсолютно законно, более того — часто оно предпочтительно, поскольку программа становится более наглядной.
В случае простых объединений по совпадающим значениям вместо ON иногда бывает удобнее использовать секции USING и NATURAL. Впрочем, они применимы лишь к наборам данным, содержащим одноименные поля. Если поля, по которым устанавливается связь между наборами, имеют разные имена, все равно остается возможность использования секций USING и NATURAL благодаря назначению синонимов полей (листинг 4.37).
Листинг 4.37. Секции NATURAL и USING
booktown=# SELECT title. last_name, first_name
booktown-# FROM books INNER JOIN authors AS a (author_id)
booktown-# USING (author_id)
booktown-# WHERE last_name = 'Geisel';
title | last_name | first_name
The Cat in the Hat | Geisel | Theodor Seuss
Bartholomew and the Oobleck | Geisel | Theodor Seuss
(2 rows)
booktown=# SELECT title, last_name, first_name
booktown-# FROM books NATURAL INNER JOIN authors AS a (author_id)
booktown-# WHERE lastjiame = 'Geisel';
title | last_name | first_name
The Cat in the Hat | Geisel | Theodor Seuss
Bartholomew and the Oobleck | Geisel | Theodor Seuss
(2 rows)
Первая команда SELECT в листинге4.37 назначает синоним authorjd первому полю таблицы authors (хотя в действительности это поле называется id). Идентификатор author_id передается в секцию USING, после чего PostgreSQL ищет в каждом наборе идентификатор поля с этим именем для объединения записей.
Внутренние объединения часто применяются на практике, но в некоторых ситуациях требуется выборка всех необходимых данных с использованием внешнего объединения. Чтобы попять суть различий между внутренними и внешними объединениями, достаточно разобраться, что происходит с записями, не входящими в установленную связь.
При внутреннем объединении все записи, для которых не находится соответствующего значения в других наборах (заданных при помощи ON или USING), просто игнорируются.
Внешние объединения
С другой стороны, внешнее объединение может сохранить записи, для которых не находится соответствия в других наборах. В этом случае недостающие поля заполняются значениями 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, но эти записи все равно включаются в результат.
Листинг 4.38. Внутренние и внешние объединения
booktown=# SELECT title, isbn
booktown-# FROM books INNER JOIN editions
booktown-# ON (books.id = editions.book_id);
title | isbn
The Tell-Tale Heart | 1885418035
The Tell-Tale Heart | 0929605942
Little Women | 0760720002
The Velveteen Rabbit | 0679803335
Goodnight Moon | 0694003611
Bartholomew and the Oobleck | 0394800753
The Cat in the Hat | 039480001X
The Cat in the Hat | 0394900014
Dynamic Anatomy | 0823015505
2001: A Space Odyssey | 0451457994
2001: A Space Odyssey | 0451198492
Dune | 0441172717
Dune | 044100590X
The Shining | 0451160916
The Shining | 0385121679
Franklin in the Dark | 0590445065
Programming Python | 0596000855
(17 rows)
booktown=# SELECT title, isbn
booktown-# FROM books LEFT OUTER JOIN editions
booktown-# ON (books.id = editions.book_id);
title | bisbn
The Tell-Tale Heart | 1885418035
The Tell-Tale Heart | 0929605942
Little Women | 0760720002
The Velveteen Rabbit | 0679803335
Goodnight Moon | 0694003611
Bartholomew and the Oobleck | 0394800753
The Cat in the Hat | 039480001X
The Cat in the Hat | 0394900014
Dynamic Anatomy | 0823015505
2001: A Space Odyssey | 0451457994
2001: A Space Odyssey | 0451198492
Dune | 0441172717
Dune | 044100590X
The Shining | 0451160916
The Shining | 0385121679
Franklin in the Dark | 0590445065
Practical PostgreSQL
Programming Python | 0596000855
Learning Python Perl | Cookbook
(20 rows)
Во второй команде, приведенной в листинге 4.38, использовано левое внешнее объединение (LEFT OUTER JOIN). Выбор объясняется тем, что запрос должен вернуть названия книг, для которых существуют (или не существуют) коды ISBN. Поскольку таблица books стоит слева от ключевого слова JOIN, задача решается при помощи левого внешнего объединения. Если бы помимо названий, не имеющих кодов ISBN, нас интересовали коды ISBN, не имеющие названий, следовало бы воспользоваться полным внешним запросом FULL OUTER JOIN.
Различия между внутренними и внешними объединениями, продемонстрированные в листинге 4.38, чрезвычайно важны, поскольку неправильный выбор объединения приводит к получению лишней информации или потере нужных данных.
ПРИМЕЧАНИЕ
Ключевое слово OUTER во внешних объединениях PostgreSQL является необязательным. Определения LEFT JOIN, RIGHT JOIN и FULL JOIN подразумевают внешнее объединение.
Сложные объединения
Хотя одна секция JOIN объединяет всего два набора данных, на практике объединения не ограничиваются двумя источниками. За набором, созданным посредством объединения, может следовать новая секция JOIN — по аналогии с тем, как перечисляются через запятую источники данных.
Элементы комбинированных объединений рекомендуется заключать в круглые скобки. Явная группировка гарантирует отсутствие неоднозначности (как для PostgreSQL, так и для разработчика) относительно того, какие наборы и в каком порядке объединяются. Пример объединения нескольких источников данных приведен в листинге 4.39.
Листинг 4.39. Объединение нескольких источников данных
booktown=# SELECT a.last_name, p.name AS publisher, e.isbn, s.subject
booktown-# FROM ((((authors AS a INNER JOIN books AS b</userinput>
booktown(# ON (a.id = b.author_id))</userinput>
booktown(# INNER JOIN editions AS e ON (e.book_id = b.id))
booktown(# INNER JOIN publishers AS p ON (p.id = e.publisher_id))
booktown(# INNER JOIN subjects AS s ON (s.id = b.subjected));
1ast_name | publisher | isbn | subject
Hogarth | Watson-Guptill Publications | 0823015505 | Arts
Brown | HarperCollins | 0694003611 | Children's Books
Geisel | Random House | 0394800753 | Children's Books
Geisel | Random House | 039480001X | Children's Books
Geisel | Random House | 0394900014 | Children's Books
Bourgeois | Kids Can Press | 0590445065 | Children's Books
Bianco | Penguin | 0679803335 | Classics
Lutz | O'Reilly & Associates | 0596000855 | Computers
Alcott | Henry Holt & Company. Inc. | 0760720002 | Drama
Рое | Mojo Press | 1885418035 | Horror
Рое | Books of Wonder | 0929605942 | Horror
King | Doubleday | 0451160916 | Horror
King | Doubleday | 0385121679 | Horror
Clarke | Roc | 0451457994 | Science Fiction
Clarke | Roc | 0451198492 | Science Fiction
Herbert | Ace Books | 0441172717 | Science Fiction
Herbert | Ace Books | 044100590X | Science Fiction
(17 rows)
В листинге 4.39 можно заметить одно любопытное обстоятельство: хотя таблица books участвует в объединении, ни одно из ее полей не входит в итоговый набор. Включение таблицы books в секции JOIN предоставляет критерии для объединения других таблиц. Все таблицы, поля которых возвращаются в результате запроса, связываются с другими таблицами через поле id таблицы books (кроме таблицы publishers, которая связывается с таблицей editions по полю publisher_id).
Группировка записей
Секция GROUP BY представляет чрезвычайно мощную концепцию SQL — агрегирование. На практике агрегирование запросов SQL приводит к тому, что все записи с одинаковыми значениями выражения, заданного в секции GROUP BY, группируются в одну агрегатную запись. Выражение GROUP BY может быть простым полем таблицы, но оно также может представлять собой произвольную операцию с полем. При перечислении нескольких полей или выражений, разделенных запятыми, группировка записей осуществляется лишь при совпадении записей по всем критериям.
Чтобы эффективно пользоваться агрегированием, необходимо понимать, что все целевые поля, участвующие в агрегирующем запросе, но не указанные в секции GROUP BY, доступны лишь при выборке через агрегатную функцию. Агрегатная функция получает имя поля (или выражение, в котором участвует хотя бы одно имя поля), представляющее несколько значений (например, в нескольких сгруппированных записях), выполняет с ними некоторую операцию и возвращает одно значение.
Самые распространенные агрегатные функции:
count () — возвращает количество записей в наборе; тах () — возвращает максимальное значение в наборе; min () — возвращает минимальное значение в наборе.Агрегатные функции работают только с записями итогового набора, поэтому они выполняются после обработки всех условных объединений и секций WHERE.
Предположим, вы хотите вывести количество книг, хранящихся в базе данных booktown для каждого издательства. Название издательства связывается с названиями опубликованных книг простым объединением таблиц editions и publishers, однако подсчитывать записи вручную весьма утомительно, а при очень больших объемах итоговых наборов — вообще неприемлемо.
В листинге 4.40 выполняется стандартное объединение двух таблиц базы данных booktown, но в нем присутствуют два новых элемента: вызов функции count () и секция GROUP BY.
Листинг 4.40. Группировка записей
booktown=# SELECT count(e.isbn) AS "number of books",
booktown-# p.name AS publisher
booktown-# FROM editions AS e INNER JOIN publishers AS p
booktown-# ON (e.publisher_id = p.id)
booktown-# GROUP BY p.name;
number of books | publisher
2 | Ace Books
1 | Books of Wonder
2 | Doubleday
1 | HarperCollins
1 | Henry Holt & Company. Inc.
1 | Kids Can Press
1 | Mojo Press
1 | O'Reilly & Associates
1 | Penguin
3 | Random House
2 | Roc
1 | Watson-Guptill Publications
(12 rows)
Секция GROUP BY в листинге 4.40 указывает PostgreSQL на то, что записи объединенного набора данных должны группироваться по имени р. name, которое в данном запросе является ссылкой на имя name таблицы publishers. Все записи с одинаковым названием издательства группируются, после чего функция count() подсчитывает в каждой группе количество значений isbn из таблицы editions и возвращает результат — количество записей, объединенных в каждую группу для одного издательства.
Учтите, что в листинге 4.40 аргумент (поле isbn таблицы editions) функции count () был выбран только для того, чтобы дать наглядное представление о смысле команды (подсчет количества книг для одного издателя). Его можно было заменить любым другим полем, поскольку функция count() всегда возвращает количество записей в текущей агрегатной группе.
При проектировании агрегатных запросов следует помнить, что секция WHERE не может содержать агрегатных функций, поэтому вместо нее следует использовать секцию HAVING. Секция HAVING работает аналогично секции WHERE, но ее условия должны быть основаны на агрегатных функциях, а не на условиях для отдельных записей. С точки зрения синтаксиса секция HAVING должна следовать за секцией GROUP BY (листинг 4.41).
Листинг 4.41. Использование секции HAVING
booktown=# SELECT count(e.isbn) AS "number of books",
booktown-# p.name AS publisher
booktown-# FROM editions AS e INNER JOIN publishers AS p
booktown-# ON (e.publisher_id - p.id)
booktown-# GROUP BY publisher
booktown-# HAVING count(e.isbn) > 1;
number of books | publisher
2 | Ace Books
2 | Doubleday
3 | Random House
2 | Roc
(4 rows)
В листингах 4.40 и 4.41 набор данных создается внутренним объединением таблиц editions и publishers, однако листинг4.41 ограничивает результат теми издательствами, которые представлены в базе данных booktown двумя и более книгами. Задача решается при помощи секции HAVING.
ПРИМЕЧАНИЕ
Если поле итогового набора связывается ключевым словом AS с синонимом, совпадающим с именем уществующего поля в одном из исходных наборов данных, то при использовании этого имени в секции GROUP BY PostgreSQL предполагает, что имя относится к исходному полю, а не к синониму.
Сортировка записей
Как упоминалось в главе 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).
Листинг 4.42. Использование секции ORDER BY
booktown=# SELECT isbn, edition, publication
booktown-# FROM editions
ooktown-# ORDER BY publication ASC;
Isbn | edition | publication
0760720002 | 1 | 1868-01-01
0679803335 | 1 | 1922-01-01
0694003611 | 1 | 1947-03-04
0394800753 | 1 | 1949-03-01
0394900014 | 1 | 1957-01-01
039480001X | 1 | 1957-03-01
0823015505 | 1 | 1958-01-01
0451160916 | 1 | 1981-08-01
0590445065 | 1 | 1987-03-01
0385121679 | 2 | 1993-10-01
1885418035 | 1 | 1995-03-28
0441172717 | 2 | 1998-09-01
0929605942 | 2 | 1998-12-01
044100590X | 3 | 1999-10-01
0451198492 | 3 | 1999-10-01
0451457994 | 3 | 2000-09-12
0596000855 | 2 | 2001-03-01
(17 rows)
Листинг 4.42 показывает, что записи возвращаются в порядке возрастания, от старых дат к новым. Следует помнить, что при сортировке и выборке допускается использование полей, отсутствующих в целевом списке команды SELECT. Более того, если запрос связан с агрегированием, секция ORDER BY может содержать вызовы агрегатных функций и выражения. Возможность сортировки по разнообразным источникам обеспечивает значительную гибкость при упорядочении результатов.
ВНИМАНИЕ
Если синоним поля итогового набора совпадает с именем существующего поля в исходном наборе данных, из которого производится выборка, то при использовании этого имени в секции ORDER BY PostgreSQL предполагает, что имя относится к итоговому набору, а не к полю источника. Такое поведение противоречит стандартному поведению секции GROUP BY, соответствующему стандарту SQL92.
При сортировке по нескольким выражениям PostgreSQL сначала упорядочивает итоговый набор по первому (левому) критерию и продолжает применять дальнейшие критерии лишь в том случае, если сортировка по первому критерию не обеспечивает однозначного результата. Пример приведен в листинге 4.43.
Листинг 4.43. Секция ORDER BY с несколькими выражениями
booktown=# SELECT edition, publication
booktown-# FROM editions
booktown-# ORDER BY edition ASC,
booktown-# publication DESC;
edition | publication
1 | 1995-03-28
1 | 1987-03-01
1 | 1981-08-01
1 | 1958-01-01
1 | 1957-03-01
1 | 1957-01-01
1 | 1949-03-01
1 | 1947-03-04
1 | 1922-01-01
1 |1868-01-01
2 | 2001-03-01
2 | 1998-12-01
2 | 1998-09-01
2 | 1993-10-01
3 | 2000-09-12
3 | 1999-10-01
3 | 1999-10-01
(17 rows)
Запрос выбирает поля edition и publication всех записей таблицы editions. Затем секция ORDER BY определяет два поля, по которым должна осуществляться сортировка результата: edition (по возрастанию) и publication (по убыванию).
Как видно из результатов в листинге 4.43, сначала записи сортируются по номеру издания, а при совпадении номеров дальнейшая сортировка осуществляется по дате публикации — от новых к старым.
Сортировка играет важную роль при использовании ключевого слова DISTINCT, упоминавшегося в подразделе «Удаление дубликатов и ключевое слово DISTINCT». Если вы хотите ограничиться просмотром последнего издания для каждого значения поля edition таблицы editions, комбинация ORDER BY и DISTINCT позволит добиться эффекта, аналогичного применению секции GROUP BY (листинг 4.44).
Листинг 4.44. Использование секции DISTINCT с ORDER BY
booktown=# SELECT DISTINCT ON (edition)
booktown-# edition, publication
booktown-# FROM editions
booktown-# ORDER BY edition ASC,
booktown-# publication DESC;
edition | publication
1 | 1995-03-28
2 | 2001-03-01
3 | 2000-09-12
(3 rows)
booktown=# SELECT edition, max(publication)
booktown-# FROM editions
booktown-# GROUP BY edition;
edition | max
1 | 1995-03-28
2 | 2001-03-01
3 | 2000-09-12
(3 rows)
Поскольку секция ORDER BY обрабатывается перед удалением дубликатов ключевым словом DISTINCT, общий эффект очень похож на вызов агрегатной функции max() или min() в секции GROUP BY. Подобная методика бывает чрезвычайно эффективной, хотя все зависит от сложности агрегирования и сортировки.
ПРИМЕЧАНИЕ
Вместо имен полей или выражений секция ORDER BY может содержать целочисленные константы. Такие константы интерпретируются как номера позиций в целевом списке; отсчет ведется слева направо, начиная с 1. Таким образом, ORDER BY 1 ASC означает сортировку по первому полю итогового набора.
Выбор интервалов записей
В PostgreSQL количество записей, выбираемых запросом SQL, не ограничивается. Обработка запроса, возвращающего несколько миллионов записей, займет много времени, но сервер не остановится, пока не вернет весь итоговый набор (или процесс не будет прерван извне).
Вероятно, выделение некоторой части выборки легко реализуется на программном уровне, но в SQL предусмотрены ключевые слова LIMIT и OFFSET, упрощающие выборку заданной части итогового набора.
Секция LIMIT ограничивает максимальное количество записей в итоговом наборе (хотя размер итогового набора вполне может быть меньше заданной величины). При наличии секции OFFSET в итоговом наборе пропускается количество записей, заданное параметром секции. Если заданы оба ключевых слова, то отсчет ограничения, указанного в секции LIMIT, начинается после пропуска записей в соответствии с секцией OFFSET.
Как показано в листинге 4.45, первый запрос, содержащий простую секцию LIMIT, ограничивается выборкой первых пяти записей из результатов объединения таблиц editions и books. Обычно это объединение содержит 17 записей.
Листинг 4.45. Использование секций LIMIT и OFFSET
booktown=# SELECT isbn, title, publication
booktown-# FROM editions NATURAL JOIN books AS b (book_id)
booktown-# ORDER BY publication DESC
booktown-# LIMIT 5;
isbn | title | publication
0596000855 | Programming Python | 2001-03-01
0451457994 | 2001: A Space Odyssey | 2000-09-12
0451198492 | 2001: A Space Odyssey | 1999-10-01
044100590X | Dune | 1999-10-01
0929605942 | The Tell-Tale Heart | 1998-12-01
(5 rows)
booktown=# SELECT isbn, title, publication
booktown-# FROM editions NATURAL JOIN books AS b (book_id)
booktown-# ORDER BY publication DESC
booktown-# LIMIT 5
booktown-# OFFSET 2;
isbn | title | publication
0451198492 | 2001: A Space Odyssey | 1999-10-01
044100590X | Dune | 1999-10-01
0929605942 | The Tell-Tale Heart | 1998-12-01
0441172717 | Dune | 1998-09-01
1885418035 | The Tell-Tale Heart | 1995-03-28
(5 rows)
Второй запрос в листинге 4.45 содержит секцию OFFSET, вследствие чего начало отсчета смещается на две записи вниз. Последние три записи в итоговом наборе первого запроса совпадают с первыми тремя записями итогового набора второго запроса. Секция ORDER BY обеспечивает единый порядок следования записей в итоговых наборах обоих запросов.
Сравнение наборов записей
Если объединения используются в SQL для слияния полей двух источников, то ключевые слова UNION, INTERSECT и EXCEPT сравнивают значения полей в двух наборах и строят новый итоговый набор на основании результатов сравнения. Каждое из перечисленных ключевых слов может использоваться в конце синтаксически правильного запроса SQL, а за ним может следовать второй запрос; в этом случае итоговые наборы двух запросов сравниваются и записи либо включаются в результат, либо игнорируются.
Сравниваемые наборы данных должны содержать одинаковое количество полей, относящихся к соответствующим типам. При этом не требуется, чтобы поля имели одинаковые имена или принадлежали к одной таблице или источнику данных.
UNION. Все различающиеся записи двух наборов включаются в один набор данных. Совпадающие записи не дублируются. INTERSECT. Все записи, не входящие в оба набора данных, игнорируются. Таким образом, результат состоит только из записей, присутствующих в обоих наборах. EXCEPT. Все записи, входящие в оба набора данных, игнорируются. Таким образом, результат состоит только из тех записей набора, указанного слева от ключевого слова EXCEPT, которые не входят в набор, указанный справа от него.В листингах 4.46-4.48 показаны результаты применения этих операций к двум наборам данных. В листинге 4.46 итоговый набор формируется слиянием фамилий авторов с названиями книг, для чего используется ключевое слово UNION.
В листинге 4.47 продемонстрирована выборка кодов ISBN из таблицы editions. Выборка ограничивается записями, которые упоминаются более чем в двух поставках в таблице shi pments. Наконец, в листинге 4.48 из первого запроса исключаются все записи, входящие во второй запрос.
Листинг 4.46. Использование секции UNION
booktown=# SELECT title FROM books
booktown-# UNION
booktown-# SELECT last_name FROM authors
booktown-# LIMIT 11;
title
2001: A Space Odyssey Alcott
Bartholomew and the Oobleck
Bianco
Bourgeois
Brautigan
Brite
Brown
Christiansen Clarke Denham
(11 rows)
Листинг 4.47. Использование секции INTERSECT
booktown=# SELECT isbn FROM editions
booktown-# INTERSECT
booktown-# SELECT isbn FROM shipments
booktown-# GROUP BY isbn
booktown-# HAVING count(id) > 2;
isbn
039480001X
0394800753
0451160916
0590445065
0694003611
(5 rows)
Листинг 4.48. Использование секции EXCEPT
booktown=# SELECT last_name, first_name
booktown-# FROM authors
booktown-# EXCEPT
booktown-# SELECT lastjiame, first_name
booktown-# FROM authors AS a (author_id)
booktown-# NATURAL INNER JOIN books
booktown-# ORDER BY first_name ASC;
last_name | first_name
Denham | Ariel
Gorey | Edward
Brite | Poppy Z.
Brautigan | Richard
(4 rows)
Команда из листинга 4.48 возвращает только те записи, которые не входят во второй запрос. Фактически это приводит к тому, что итоговый набор состоит из записей об авторах, у которых нет ни одной книги в таблице books. Это связано с присутствием секции INNER JOIN, исключающей из второго запроса всех авторов, коды которых (author_id) отсутствуют в таблице books.
Хотя присутствие этих ключевых слов в запросе SQL не позволяет использовать в нем секцию LIMIT, этот запрет легко обходится благодаря поддержке подзапросов в PostgreSQL. Для этого достаточно заключить в круглые скобки каждый из запросов, участвующих в операции UNION, INTERSECT или EXCEPT, и сравнить итоговые наборы подзапросов, как показано в листинге 4.49.
Листинг 4.49. Сравнение результатов двух подзапросов
booktown=# (SELECT title FROM books ORDER BY title DESC LIMIT 7)
booktown-# EXCEPT
booktown-# (SELECT title FROM books ORDER BY title ASC LIMIT 11)
booktown-# ORDER BY title DESC;
title
The Velveteen Rabbit
The Tell-Tale Heart
The Shining
The Cat in the Hat
(4 rows)
Запрос, использованный в листинге 4.49, создает по данным таблицы books набор, отсортированный по названию в алфавитном порядке, и выбирает из него семь последних записей. Затем при помощи ключевого слова EXCEPT из набора исключаются начальные 11 записей при сортировке в алфавитном порядке по возрастаиию. Результат состоит из четырех последних записей таблицы books, отсортированных в обратном алфавитном порядке завершающей секцией ORDER BY.
Конструкции CASE
Чтобы программа SQL могла принимать простейшие решения, не прибегая к процедурным языкам, в PostgreSQL поддерживаются конструкции CASE, предусмотренные стандартом SQL Ключевые слова SQL CASE, WHEN, THEN и END позволяют выполнять простые условные преобразования записей.
Вся конструкция CASE включается в целевой список команды SELECT. По умолчанию итоговому полю конструкции CASE присваивается имя case, но ему можно назначить синоним, как любому обычному полю. Общий синтаксис конструкции CASE в списке целей команды SELECT выглядит следующим образом:
CASE WHEN условие! THEN результат! WHEN условие2 THEN результат2
[ ... ]
[ ELSE результат_по_умопчанию END [ AS синоним ]
Конструкция CASE-WHEN-THEN-ELSE отчасти напоминает условные команды f-then-else в традиционных языках программирования (листинг 4.50). Условия секций WHEN должны возвращать логический результат.
Если условие в секции WHEN выполняется, результат соответствующей секции THEN возвращается в поле итогового набора. Если ни одно условие не выполнено, можно задать значение по умолчанию в секции ELSE. Если при отсутствии секции ELSE результат остается неопределенным, возвращается NULL.
Листинг 4.50. Конструкции CASE в командах
booktown=# SELECT isbn,
booktown-# CASE WHEN cost > 20 THEN 'over $20.00 cost'
booktown-# WHEN cost = 20 THEN '$20.00 cost1
booktown-# ELSE 'under $20.00 cost'
booktown-# END AS cost_range
booktown-# FROM stock
booktown-# LIMIT 8;
Isbn | cost_range
0385121679 | over $20.00 cost
039480001X | over $20.00 cost
044100590X | over $20.00 cost
0451198492 | over $20.00 cost
0394900014 | over $20.00 cost
0441172717 | under $20.00 cost
0451160916 | over $20.00 cost
0679803335 | $20.00 cost
(8 rows)
Подзапросы PostgreSQL расширяют возможности конструкций CASE (см. раздел «Подзапросы»). Как показано в листинге 4.51, в качестве результата условного выражения в конструкции может быть задан подзапрос.
Листинг 4.51. Конструкции CASE в подзапросах
booktown=# SELECT isbn,
booktown-# CASE WHEN cost > 20 THEN 'N/A - (Out of price range)'
booktown-# ELSE (SELECT title FROM books b JOIN editions e
booktown(# ON (b.id = e.book_id)
booktown(# WHERE e.isbn = stock.isbn)
booktown-# END AS cost_range
booktown-# FROM stock
booktown-# ORDER BY cost_range ASC
booktown-# LIMIT 8;
isbn | cost_range
0451457994 | 2001: A Space Odyssey
0394800753 | Bartholomew and the Oobleck
0441172717 | Dune
0760720002 | Little Women
0385121679 | N/A - (Out of price range)
039480001X | N/A - (Out of price range)
044100590X | N/A - (Out of price range)
0451198492 | N/A - (Out of price range)
(8 rows)
Для всех книг, цена которых не превышает 20, запрос возвращает название книги (подзапрос к таблице books) и код ISBN (основной запрос к таблице stock).
Создание таблиц на базе существующих таблиц
Команда SELECT с секцией INTO TABLE создает новую таблицу, структура и содержимое которой определяются итоговым набором запроса. Синтаксис:
SELECT цели_выборки
INTO [ TABLE ] новая_таблица FROM старая_таблица
В этом варианте команда SELECT косвенно выполняет команду CREATE TABLE. Имена и типы полей, а также содержимое таблицы определяются параметрами итогового набора. Возвращаемое в ответ сообщение «SELECT» указывает на то, что команда была успешно выполнена, а в базе данных создана новая таблица. В листинге 4.52 приведен пример создания таблицы stock_backup по данным таблицы stock.
Листинг 4.52. Создание таблицы на основе данных другой таблицы
booktown=# SELECT * INTO
stock_backup booktown=# FROM stock;
SELECT
Таблица, указанная в секции INTO, не должна существовать, иначе происходит ошибка. В этом случает данные не сохраняются, а запрос завершается неудачей. Следует помнить, что ключевое слово TABLE является необязательным.
Модификация записей командой UPDATE
После того как записи сохранены в базе данных, вы можете обновить их поля командой SQL UPDATE. Новые значения полей задаются в виде констант, идентификаторов других баз данных или выражений. Допускается обновление как поля в целом, так и подмножества его значений в соответствии с заданными условиями. Синтаксис команды UPDATE:
UPDATE [ ONLY ] таблица SET
поле = выражение [. ...] [ FROM источник ] [ WHERE условие ]
UPDATE [ ONLY ] таблица. Ключевое слово ONLY означает, что обновляется только заданная таблица, но не ее производные таблицы. Применяется лишь в том случае, если таблица использовалась в качестве базовой при наследовании. SET поле = выражение [. ...]. Обязательная секция SET содержит перечисленные через запятую условия, определяющие новые значения обновляемых полей. Условия всегда имеют форму поле = выражение, где поле — имя обновляемого поля (не допускаются ни синонимы, ни точечная запись), а выражение описывает новое значение поля. FROM источник. Секция FROM принадлежит к числу нестандартных расширений PostgreSQL и позволяет обновлять поля значениями, взятыми из других наборов. WHERE условие. В секции WHERE задается критерий, по которому в таблице выбираются обновляемые записи. Если секция WHERE отсутствует, поле обновляется во всех записях. По аналогии с командой SELECT может использоваться для уточнения выборки из источников, перечисленных в секции FROM.В листинге 4.53 приведен пример простой команды UPDATE. Команда заполняет поле retail таблицы stock вещественной константой 29.95. Секция WHERE ограничивает обновление записями, соответствующими заданному критерию.
Листинг 4.53. Простая команда UPDATE
booktown=# SELECT retail FROM stock
booktown-# WHERE isbn = '0590445065';
retail
23.95
(1 row)
booktown=# UPDATE stock
booktown-# SET retail = 25.95
booktown-# WHERE isbn = '0590445065';
UPDATE 1
booktown=# SELECT retail FROM stock
booktown-# WHERE isbn = '0590445065';
retail
25.95
(1 row)
Итоговое сообщение UPDATE 1 в листинге 4.53 означает, что одна запись была успешно обновлена. Даже если новое значение поля совпадает со старым, операция все равно считается обновлением, а файлы базы данных на диске модифицируются.
Обновление поля во всех записях таблицы
При отсутствии секции WHERE команда UPDATE обновляет заданное поле во всех записях таблицы. Обычно в этой ситуации новое значение поля задается выражением, а не константой. Выражение, указанное в секции SET, вычисляется заново для каждой записи, а новое значение поля определяется динамически. Пример приведен в листинге 4.54, в котором команда UPDATE обновляет поле retail таблицы stock. Повышение розничной цены для всех книг, имеющихся в наличии, вычисляется при помощи математического выражения. Выражение состоит из нескольких компонентов, а круглые скобки обеспечивают нужный порядок их вычисления.
Подвыражение (retail / cost) определяет текущую удельную прибыль, которая увеличивается на 10 % при помощи оператора + и вещественной константы 0.1. Конструкция 0.1:: numeric выполняет явное преобразование вещественной константы к типу numeric; необходимость преобразования объясняется тем, что частное при делении retail /cost относится к типу numeric. Наконец, новая удельная прибыль умножается на стоимость единицы товара из поля cost. Результат равен новой цене, сохраняемой в поле retail.
Листинг 4.54. Обновление поля во всех записях
booktown=# SELECT isbn, retail, cost
booktown-# FROM stock
booktown-# ORDER BY isbn ASC
booktown-# LIMIT 3;
isbn | retail | cost
0385121679 | 36.95 | 29.00
039480001X | 32.95 | 30.00
0394800753 | 16.95 | 16.00
(3 rows)
booktown=# UPDATE stock
booktown-# SET retail =
booktown-# (cost * ((retail / cost) + 0.1::numeric));
UPDATE 16
booktown=# SELECT isbn, retail, cost
booktown-# FROM stock
booktown-# ORDER BY isbn ASC
booktown-# LIMIT 3;
isbn | retail | cost
0385121679 | 39.85 | 29.00
039480001X | 35.95 | 30.00
0394800753 | 18.55 | 16.00
(3 rows)
Команда UPDATE, приведенная в листинге 4.54, не содержит секции WHERE, поэтому обновляются все записи таблицы stock.
Обновление нескольких полей
Перечисление команд присваивания в секции SET через запятую позволяет обновить несколько полей таблицы в одной команде. В листинге 4.55 продемонстрировано одновременное изменение полей name и address таблицы publ i shers для записи с полем id, равным ИЗ.
Листинг 4.55. Выполнение команды UPDATE с обновлением нескольких полей
booktown=# UPDATE publishers
booktown-# SET name = 'OVReilly & Associates',
booktown-# address = 'OVReilly & Associates. Inc. '
booktown-# || '101 Morris St, Sebastopol, CA 95472'
booktown-# WHERE id = 113;
UPDATE 1
booktown=# SELECT name, substrtaddress, 1, 40) || '...' AS short_address
booktown-# FROM publishers
booktown-# WHERE id = 113;
name | short_address
O'Reilly & Associates | O'Reilly & Associates. Inc. 101 Morris S...
(1 row)
В этой команде UPDATE значения обоих полей, name и address, заданы в виде строковых констант. Обратите внимание: внутренние апострофы в строках экранируются обратной косой чертой. Команда SELECT позволяет убедиться в правильности выполненного обновления.
Листинг 4.55 также служит примером практического использования оператора конкатенации || и функции substr(). Полю address присваиваются две строковые константы, объединенные оператором || для предотвращения выхода за пределы командной строки терминала. Затем функция substr() усекает выводимый адрес, чтобы он не переносился на другую строку. В данном случае это сделано лишь для удобства чтения выходных данных. Конечно, если вас интересует полное содержимое поля, усекать его не следует.
Обновление записей по нескольким источникам
В PostgreSQL команда SQL UPDATE была дополнена мощной нестандартной возможностью — поддержкой секции FROM. Секция FROM позволяет получать входные данные из других наборов данных (таблиц и подзапросов).
В листинге 4.56 команда UPDATE с секцией FROM обновляет данные таблицы stock по данным таблицы stock_backup. Секция WHERE описывает связь между обновляемой таблицей и источником. Каждый раз, когда в таблицах находятся совпадающие значения isbn, поле retail в таблице stock обновляется значением из резервной таблицы stock_backup.
Листинг 4.56. Команда UPDATE с несколькими источниками
booktown=# UPDATE stock .
booktown-# SET retail = stock_backup.retail
booktown-# FROM stock_backup
booktown-# WHERE stock.isbn = stock_backup.isbn;
UPDATE 16
Секция FROM поддерживает все разновидности синтаксиса JOIN, описанные в разделе «Выборка данных командой SELECT», что открывает широкие возможности обновления данных в существующих наборах. Более того, как упоминалось выше, в качестве источника данных в секциях FROM могут использоваться подзапросы.
Удаление записей командой DELETE
Удаление записей из таблиц производится стандартной командой SQL DELETE. Вызов DELETE приводит к необратимым последствиям (исключение составляют тщательно спланированные транзакционные блоки), поэтому удаление данных из базы требует крайней осторожности.
Команда удаления одной или нескольких записей из базы имеет следующий синтаксис:
DELETE FROM [ ONLY ] таблица [ WHERE условие ]
DELETE FROM [ ONLY ] таблица. Ключевое слово ONLY означает, что обновляется только заданная таблица, но не ее производные таблицы. Применяется лишь в том случае, если таблица использовалась в качестве базовой при наследовании. WHERE условие. В секции WHERE задается критерий, по которому в таблице выбираются удаляемые записи. При отсутствии секции WHERE из таблицы удаляются все записи.Секция WHERE почти всегда присутствует в команде DELETE. В ней определяются условия отбора удаляемых записей, выраженные в такой же синтаксической форме, как и при использовании команды SELECT.
Перед выполнением команды DELETE рекомендуется выполнить команду SELECT с соответствующей секцией WHERE и просмотреть удаляемые данные перед их фактическим уничтожением. Пример приведен в листинге 4.57.
Листинг 4.57. Удаление записей из таблицы
booktown=# SELECT * FROM stock
booktown-# WHERE stock = 0;
isbn | cost | retail | stock
0394800753 | 16.00 | 16.95 | 0
0394900014 | 23.00 | 23.95 | 0
0451198492 | 36.00 | 46.95 | 0
0451457994 | 17.00 | 22,95 | 0
(4 rows)
booktown=# DELETE FROM stock
booktown-# WHERE stock = 0;
DELETE 4
При отсутствии секции WHERE команда DELETE удаляет из таблицы все записи (листинг 4.58).
Листинг 4.58. Удаление всех записей из таблицы
booktown=# DELETE FROM stock_backup;
DELETE 16
Подзапросы
Поддержка подзапросов, впервые реализованная в PostgreSQL версии 6.3, существенно повысила гибкость команд SQL. Подзапросом называется команда SELECT, заключенная в круглые скобки, которая выполняется в контексте другой команды SQL. Подзапросы чаще используются для возвращения одной записи, но они также могут использоваться для определения подмножества записей.
Подзапросы могут находиться практически в любой части команды SQL — в списке целей, в секции WHERE и т. д. В листинге 4.59 приведен простой пример использования подзапроса для выборки критерия поиска из другой таблицы.
Листинг 4.59. Простой подзапрос
booktown=# SELECT title FROM books
30oktown-# WHERE author_id = (SELECT id FROM authors)
booktown(# WHERE last_name='Geisel'
booktown(# AND first_name='Theodor Seuss');
title
The Cat in the Hat
Bartholomew and the Oobleck
(2 rows)
Оператор = сравнивает поле id подзапроса к таблице authors с полем author_id габлицы books. Подзапрос находит в таблице authors запись об авторе с именем c<Theodor Seuss» и фамилией «Geisel»; сравнивая поле id этой записи с полем Author_id таблицы books, мы отбираем все книги, написанные этим автором.
Построение подзапросов требует осторожности: чтобы результат запроса проверялся простым оператором, подзапрос должен возвращать только одну запись. Например, если использовать для выборки кода автора более общий запрос, возвращающий несколько записей, PostgreSQL выдаст сообщение об ошибке:
booktown=# SELECT title FROM books
booktown-# WHERE authoMd = (SELECT id FROM authors
booktown(# WHERE last_name ` 'G');
ERROR: More than one tuple returned by a subselect used as an expression.
Обычные операторы не могут сравнивать отдельную величину с несколькими шачениями, поэтому сравнение поля authoMd с несколькими полями приводит к ошибке. Проблема легко решается включением в подзапрос секции LIMIT 1, благодаря которой подзапрос никогда не вернет более одной записи.
Если требуется проверить присутствие отдельной величины в заданном наборе, замените оператор = ключевым словом IN. Подзапрос, приведенный в листинге 4.60, выбирает несколько значений, сравниваемых с полем author_id (для авторов, имена которых начинаются с букв А-Е). Сравнение осуществляется при юмощи ключевого слова IN. Средства поиска по регулярному выражению рассматриваются в разделе «Операторы» главы 5.
Листинг 4.60. Подзапрос с ключевым словом IN
booktown=# SELECT title FROM books
booktown-# WHERE authorjd IN (SELECT id FROM authors
booktown(# WHERE last_name - '"[A-E]');
title
2001: A Space Odyssey
Franklin in the Dark
Goodnight Moon
Little Women
The Velveteen Rabbit
Perl Cookbook
(6 rows)
Благодаря ключевому слову IN запрос находит в таблице данные о книгах нескольких авторов, коды которых были выбраны подзапросом. Хотя ключевое сло-ю IN позволяет проверить значение по нескольким записям, количество сравниваемых полей должно быть одинаковым.
Если вы хотите, чтобы секция IN сравнивала несколько полей, сгруппируйте их имена в круглых скобках в секции WHERE непосредственно перед IN. Сгруппированные поля должны соответствовать полям целевого списка как по количеству, так и по типу.
В листинге4.61 приведен подзапрос к таблице editions, который группирует поле i sbn с целочисленной константой 0 для всех книг в бумажной обложке (type=' р'). Возвращаемые подзапросом записи сравниваются с полем isbn и полем stock таблицы stock с использованием ключевого слова IN. В результате команда SELECT выбирает данные обо всех книгах в бумажной обложке, отсутствующих на складе.
Листинг 4.61. Многоцелевой подзапрос в секции IN
booktown=# SELECT isbn, cost, retail FROM stock
booktown-# WHERE (isbn, stock)
booktown-# IN (SELECT isbn, 0 FROM editions
booktown(# WHERE type = 'p');
isbn | cost | retail
0394800753 | 16.00 | 16.95
0394900014 | 23.00 | 23.95
0451457994 | 17.00 | 22.95
(3 rows)
Представления
При работе с SQL нередко возникают ситуации, когда один и тот же запрос приходится использовать повторно. Ничто не раздражает так, как необходимость многократного ввода больших и сложных запросов в psql. Кроме того, было бы крайне неэффективно пересылать большие наборы данных по сети на сервер PostgreSQL при выполнении стандартных процедур.
В подобных ситуациях обычно используются представления (views). Представление можно рассматривать как хранимый запрос, на основе которого создается объект базы данных. Этот объект очень похож на таблицу, но в его содержимом динамически отражается состояние только тех записей, которые были заданы при создании. Представления весьма гибки и универсальны; они могут строиться на основе как простых и стандартных запросов к одной таблице, так и чрезвычайно сложных запросов, в которых задействовано несколько таблиц.
Создание представления
Представления создаются командой CREATE VIEW, синтаксис которой выглядит следующим образом:
CREATE VIEW представление
AS запрос
представление. Имя (идентификатор) создаваемого представления. запрос. Полная команда SQL SELECT, определяющая содержимое представления.Предположим, таблица shipments связывает уникальный код поставки с кодом клиента, кодом ISBN книги и датой отправки заказа. Структуру таблицы shipments иллюстрирует табл. 4.1.
Таблица 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
32
(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.
Листинг 4.62. Создание представления
booktown=# CREATE VIEW recent_shipments
booktown-# AS 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;
CREATE
Ответ сервера CREATE подтверждает, что представление было успешно создано. В результате в базе данных booktown создается представление recent_shipments с информацией обо всех поставках книг, о количестве заказанных экземпляров и дате последней поставки по каждой позиции.
Применение представлений
Представления значительно упрощают получение нужных данных. Вместо того чтобы вводить длинный запрос, достаточно ввести простую команду SELECT (листинг 4.63).
Листинг 4.63. Использование представления
booktown=# SELECT * FROM recent_shipments;
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)
booktown=# SELECT * FROM recent_shipments
booktown-# ORDER BY max DESC
booktown-# LIMIT 3;
num_shipped | max | title
2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey
3 | 2001-08-15 11:57:40-07 | Goodnight Moon
3 | 2001-08-14 13:49:00-07 | Franklin in the Dark
(3 rows)
Листинг 4.63 демонстрирует еще одно важное обстоятельство: хотя при создании представления используется секция ORDER BY, итоговый набор можно заново отсортировать при выводе. Для этого в команду SELECT, осуществляющую выборку из представления, включается другая секция ORDER BY.
ПРИМЕЧАНИЕ
При попытке вызова команд DELETE или UPDATE для представления происходит ошибка, поскольку представление не содержит собственных данных. Несмотря на внешнее сходство с таблицами, представления всего лишь обеспечивают механизм обращения к другому набору данных, поэтому их модификация невозможна.
Уничтожение представлений
Команда уничтожения представления имеет следующий синтаксис (представление — имя уничтожаемого представления):
DROP VIEW представление
Уничтожение представления не отражается на данных, которые использовались
представлением. Представление всего лишь обеспечивает доступ к данным
других таблиц и потому может уничтожаться без потери данных (хотя запрос,
на котором оно основано, конечно, теряется).
Другие возможности SQL
В этой главе были представлены фундаментальные концепции применения языка SQL в PostgreSQL. В частности, были рассмотрены вопросы создания таблиц и выполнения с ними различных операций, включая выборку и модификацию данных. В главе 5 будут подробно описаны функции и операторы PostgreSQL, многие из которых уже встречались в этой главе.
