Группировка условий при помощи круглых скобок

Листинг 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. Как видно из результатов первой команды, при перечислении всех трех условий без круглых скобок команда интерпретируется неправильно. Добавление круглых скобок приводит к тому, что вычисления в скобках производятся до проверки внешних условий.

показывает как простое

Листинг 4.34 показывает, как простое добавление круглых скобок влияет на результат выполнения команды.

Простой перекрестный запрос

Листинг 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 и простым перечислением таблиц через запятую нет никаких функциональных различий.

Сравнение команд INNER JOIN и WHERE

Листинг 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).

Секции NATURAL и USING

Листинг 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), просто игнорируются.

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

Листинг 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, чрезвычайно важны, поскольку неправильный выбор объединения приводит к получению лишней информации или потере нужных данных.

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

Ключевое слово OUTER во внешних объединениях PostgreSQL является необязательным. Определения LEFT JOIN, RIGHT JOIN и FULL JOIN подразумевают внешнее объединение.

Объединение нескольких источников данных

Листинг 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).

Группировка записей

Листинг 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).

Использование секции HAVING

Листинг 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.

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

Если поле итогового набора связывается ключевым словом AS с синонимом, совпадающим с именем уществующего поля в одном из исходных наборов данных, то при использовании этого имени в секции GROUP BY PostgreSQL предполагает, что имя относится к исходному полю, а не к синониму.

Использование секции ORDER BY

Листинг 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.

Секция ORDER BY с несколькими выражениями

Листинг 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

(17 rows)

Запрос выбирает поля edition и publication всех записей таблицы editions. Затем секция ORDER BY определяет два поля, по которым должна осуществляться сортировка результата: edition (по возрастанию) и publication (по убыванию).

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

Сортировка играет важную роль при использовании ключевого слова DISTINCT, упоминавшегося в подразделе «Удаление дубликатов и ключевое слово DISTINCT». Если вы хотите ограничиться просмотром последнего издания для каждого значения поля edition таблицы editions, комбинация ORDER BY и DISTINCT позволит добиться эффекта, аналогичного применению секции GROUP BY (листинг 4.44).

Использование секции DISTINCT с ORDER BY

Листинг 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. Подобная методика бывает чрезвычайно эффективной, хотя все зависит от сложности агрегирования и сортировки.

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

Вместо имен полей или выражений секция ORDER BY может содержать целочисленные константы. Такие константы интерпретируются как номера позиций в целевом списке; отсчет ведется слева направо, начиная с 1. Таким образом, ORDER BY 1 ASC означает сортировку по первому полю итогового набора.

Использование секций LIMIT и OFFSET

Листинг 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 обеспечивает единый порядок следования записей в итоговых наборах обоих запросов.

Использование секции UNION

Листинг 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)

Использование секции INTERSECT

Листинг 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)

Использование секции EXCEPT

Листинг 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 в командах

Листинг 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, в качестве результата условного выражения в конструкции может быть задан подзапрос.

Конструкции CASE в подзапросах

Листинг 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).

Создание таблицы

Листинг 4.52. Создание таблицы на основе данных другой таблицы

booktown=# SELECT * INTO

stock_backup booktown=# FROM stock;

SELECT

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

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

Листинг 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 означает, что одна запись была успешно обновлена. Даже если новое значение поля совпадает со старым, операция все равно считается обновлением, а файлы базы данных на диске модифицируются.

Обновление поля во всех записях

Листинг 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.

также служит примером

Листинг 4.55 также служит примером практического использования оператора конкатенации || и функции substr(). Полю address присваиваются две строковые константы, объединенные оператором || для предотвращения выхода за пределы командной строки терминала. Затем функция substr() усекает выводимый адрес, чтобы он не переносился на другую строку. В данном случае это сделано лишь для удобства чтения выходных данных. Конечно, если вас интересует полное содержимое поля, усекать его не следует.

Выполнение команды

Листинг 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 позволяет убедиться в правильности выполненного обновления.

Команда UPDATE с несколькими источниками

Листинг 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 могут использоваться подзапросы.

Удаление записей из таблицы

Листинг 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

Простой подзапрос

Листинг 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.

Подзапрос с ключевым словом IN

Листинг 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 выбирает данные обо всех книгах в бумажной обложке, отсутствующих на складе.

Многоцелевой подзапрос в секции IN

Листинг 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)

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

Листинг 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 с информацией обо всех поставках книг, о количестве заказанных экземпляров и дате последней поставки по каждой позиции.

демонстрирует еще

Листинг 4.63 демонстрирует еще одно важное обстоятельство: хотя при создании представления используется секция ORDER BY, итоговый набор можно заново отсортировать при выводе. Для этого в команду SELECT, осуществляющую выборку из представления, включается другая секция ORDER BY.

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

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

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

Листинг 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)

Модификация таблицы командой ALTER TABLE

В большинстве современных РСУБД предусмотрена возможность модификации таблиц командой ALTER TABLE. Реализация ALTER TABLE в PostgreSQL 7.1.x поддер-кивает шесть типов модификации:

создание полей; назначение и отмена значений по умолчанию; переименование таблицы; переименование полей; дооавление ограничении; смена владельца.

Модификация записей командой 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 ограничивает обновление записями, соответствующими заданному критерию.

Назначение и отмена значений по умолчанию

При модификации существующих таблиц наибольшая свобода действий предоставляется при выборе значений по умолчанию. Программист может относительно легко назначать и отменять эти значения для отдельных полей, для чего используется команда ADD TABLE с секцией ALTER COLUMN.

В PostgreSQL команда ALTER TABLE, назначающая или отменяющая значение по умолчанию для поля имя_поля, имеет следующий синтаксис:

ALTER TABLE таблица

ALTER [ COLUMN ] имя_поля

( SET DEFAULT значение ] DROP DEFAULT }

Как и в предыдущем разделе, ключевое слово COLUMN является необязательным включается в команду лишь для наглядности. В листинге 4.9 приведен пример азначения и отмены простой последовательности значений по умолчанию для оля id таблицы books.

Объединение наборов данных

Как было показано в примере использования секции WHERE для выборки из двух таблиц (см. подраздел «Выбор источников в секции FROM»), существует возможность выборки данных из разных источников с объединением их полей. В SQL этот процесс формально называется объединением (join).

В результате объединения двух или более наборов данных создается новый набор записей, состоящих из всех полей исходных наборов. Базовый вариант объединения представляет собой декартово произведение, то есть совокупность всех возможных комбинаций двух наборов. Далее из этого произведения отбирается часть записей по критериям, заданным в секции JOIN.

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

Перекрестные объединения (CROSS JOIN). Декартово (перекрестное) произведение двух наборов данных. Произведение не определяет отношений между наборами, а лишь содержит все возможные комбинации записей объединяемых наборов. Внутренние объединения (INNER JOIN). Подмножество декартова произведения двух наборов данных с критерием, используемым для объединения записей. Критерий возвращает логическую величину — признак вхождения записи в объединенный набор. Внешние объединения (OUTER JOIN). Как и внутренние объединения, содержат критерий объединения записей, но обязательно возвращают минимум один экземпляр каждой записи заданного набора. Это может быть левый набор (источник данных слева от ключевого слова JOIN), правый набор (источник данных справа от ключевого слова JOIN) или оба набора в зависимости от конкретной разновидности внешнего объединения. Пустые поля в тех частях записей, которые не отвечают критерию объединения, содержат NULL.

Обновление нескольких полей

Перечисление команд присваивания в секции SET через запятую позволяет обновить несколько полей таблицы в одной команде. В листинге 4.55 продемонстрировано одновременное изменение полей name и address таблицы publ i shers для записи с полем id, равным ИЗ.

Обновление поля во всех записях таблицы

При отсутствии секции WHERE команда UPDATE обновляет заданное поле во всех записях таблицы. Обычно в этой ситуации новое значение поля задается выражением, а не константой. Выражение, указанное в секции SET, вычисляется заново для каждой записи, а новое значение поля определяется динамически. Пример приведен в листинге 4.54, в котором команда UPDATE обновляет поле retail таблицы stock. Повышение розничной цены для всех книг, имеющихся в наличии, вычисляется при помощи математического выражения. Выражение состоит из нескольких компонентов, а круглые скобки обеспечивают нужный порядок их вычисления.

Подвыражение (retail / cost) определяет текущую удельную прибыль, которая увеличивается на 10 % при помощи оператора + и вещественной константы 0.1. Конструкция 0.1:: numeric выполняет явное преобразование вещественной константы к типу numeric; необходимость преобразования объясняется тем, что частное при делении retail /cost относится к типу numeric. Наконец, новая удельная прибыль умножается на стоимость единицы товара из поля cost. Результат равен новой цене, сохраняемой в поле retail.