SQLсервер в Linux -самостоятельное освоение пакета

         

Анатомия команд SQL



Анатомия команд SQL

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



Базы данных



Базы данных

Хотя РСУБД PostgreSQL часто называют просто «базой данных», этот термин часто приводит к недоразумениям. База данных PostgreSQL представляет собой объектно-реляционную реализацию того, что в стандарте SQL99 формально называется схемой (schema).

Проще говоря, базой данных называется совокупность логически связанных данных, хранящихся вместе. Обычно СУБД обеспечивает доступ к этим данным в многопользовательской среде. В PostgreSQL дело обстоит именно так, хотя доступ ограничивается системой четко определенных прав и ограничений.

PostgreSQL может одновременно предоставлять доступ к нескольким базам данных, у каждой из которых имеется свой владелец, свои таблицы, представления, индексы и функции.

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

Жесткая изоляция основных объектов данных в базах данных снижает опасность возникновения конфликтов имен при выборе имени, ранее зарезервированного для другой цели (например, если два пользователя захотят создать таблицу с именем products для двух разных целей). Это связано с тем, что ни одна база данных не располагает информацией о компонентах других баз и не пытается устанавливать с ними какие-либо логические связи. Более того, это правило распространяется и на объекты данных объектно-реляционных баз, поэтому созданные пользователем функции и языковые определения недоступны для других пользователей, подключающихся к другим базам данных через PostgreSQL.

По умолчанию PostgreSQL создает только одну рабочую базу данных с именем template 1. Любая база данных, созданная после template 1, фактически является ее клоном и наследует от прототипа все характеристики и объекты, включая структуру таблиц, функции, языки и т. д. Для новых пользователей PostgreSQL нередко создается стандартная база данных с именем, соответствующим имени пользователя PostgreSQL, поскольку если при подключении пользователя имя базы данных не указано, PostgreSQL по умолчанию в качестве имени базы данных подставляет имя пользователя.



Битовые последовательности



Битовые последовательности

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

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

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



Целочисленные константы





Целочисленные константы

Целочисленные константы используются гораздо чаще, чем битовые последовательности. В PostgreSQL целочисленной константой считается любая лексема, состоящая из цифр (без десятичной точки) и не заключенная в апострофы. Интервал допустимых значений целочисленных констант зависит в основном от контекста, но в PostgreSQL по умолчанию целочисленный тип представляется четырьмя байтами и принимает значения из интервала от -2 147 483 648 до 2 147 483 647.

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

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

Внесение исправлений начинается с поиска записей, у которых поле id удовлетворяет данному условию. Для этого в команду SELECT включается секция WHERE с целочисленной константой, используемой при проверке условия «меньше».



Числовые типы



Числовые типы

Числовые типы PostgreSQL используются для представления целых и вещественных значений. С общей точки зрения к числовым типам PostgreSQL относятся следующие типы:

2-, 4- и 8-байтовые целые числа; 4- и 8-байтовые вещественные числа; дробные числа с фиксированной точностью.

Помимо перечисленных в PostgreSQL существуют некоторые специальные типы, также относимые к категории числовых, в том числе устаревший тип money и специальная конструкция serial (табл. 3.13).



Дата и время



Дата и время

Следующая категория типов является удобным средством для хранения даты и времени в универсальной структуре SQL. При этом программист избавляется от хлопот, связанных с форматом хранения (как, например, при хранении этих данных в символьных строках). В PostgreSQL все вычисления с датой и временем производятся но юлианскому календарю. Продолжительность года считается равной 365,24 дня, что обеспечивает правильное представление любой даты с 4713 года до нашей эры и далеко в будущее.

В PostgreSQL поддерживаются все типы даты и времени, определенные в стандарте SQL92 (табл. 3.14), а также некоторые вспомогательные типы PostgreSQL, помогающие решить проблемы с представлением часовых поясов в SQL92.



Формат представления даты



Формат представления даты

В PostgreSQL предусмотрено несколько стандартных форматов даты, в том числе формат ISO-8601, традиционный формат SQL, исходный формат PostgreSQL и многие другие. Некоторые форматы даты указаны в табл. 3.15.

Перечисленные форматы относятся к типам данных date и timestamp.



Форматирование команд SQL



Форматирование команд SQL

Как упоминалось в предыдущем подразделе, команды SQL состоят из последовательности элементов — лексем. Менее очевиден тот факт, что лексемы могут находиться в одной или нескольких строках, поскольку модуль лексического разбора PostgreSQL игнорирует лишние пропуски (в том числе разрывы строк).

В листинге 3.1 приведена команда SQL, которая в первом случае задана в одной строке, а во втором повторяется с разбивкой на несколько строк. Оба варианта команды SELECT выводят все содержимое таблицы my_list.



Форматы представления времени



Форматы представления времени

Время, как и дата, может задаваться разными способами. В табл. 3.20 перечислены самые распространенные форматы значений типов time и time with time zone.



Геометрические типы



Геометрические типы

Геометрические типы PostgreSQL предназначены для представления объектов на плоскости. Они не относятся к стандартным типам данных SQL и потому в книге подробно не рассматриваются. В табл. 3.24 приведена краткая сводка поддерживаемых геометрических типов.



Идентификаторы объектов



Идентификаторы объектов

Как было сказано в подразделе «Таблицы» раздела «Знакомство с реляционными базами данных», база данных содержит таблицы, а каждая таблица содержит хотя бы одно именованное поле. Таблица может содержать записи данных, но их наличие не является обязательным. Каждое поле записи, хранящейся в таблице, содержит некоторые данные или NULL.

Один из вопросов, которые приходится решать при операциях с базами данных, — как различить две записи с одинаковыми значениями полей? Для этого в PostgreSQL предусмотрены идентификаторы объектов (object identifiers, OID), никальные в пределах таблицы. Иначе говоря, таблица никогда не содержит записи одинаковыми идентификаторами OID. Таким образом, даже если содержимое пользовательских полей двух записей полностью совпадает, на программном уровне записи можно различить по значению OID. Пример приведен в листинге 3.31.



Интервальный тип



Интервальный тип

В стандарте SQL92 определен тип данных interval, представляющий фиксированный промежуток времени. Сам по себе тип interval представляет лишь количественную величину, не связанную с определенным начальным или конечным моментом. Интервалы часто используются в сочетании с типами даты и времени для вычисления новой величины посредством сложения или вычитания. Кроме

того, они могут пригодиться для быстрого вычисления точного промежутка между двумя датами или моментами времени, для чего одна величина вычитается из другой.

В PostgreSQL поддерживаются два варианта синтаксиса определения интервалов:

длина единице [ago]

длина! единица [. длина2 единица2 ... ]

[ago]

Здесь:

длина — продолжительность интервала, заданная в виде целого или вещественного (для микросекунд) числа. Интерпретация числа определяется следующим параметром. единица — единица, в которой измеряется заданный интервал. Разрешены следующие ключевые слова: second, minute, hour, day, week, month,year, decade, century, millennium. Также допускаются сокращения (любой длины при условии однозначной интерпретации) и формы множественного числа. ago — необязательное ключевое слово ago указывает, что описываемый период времени предшествует некоторому моменту, а не следует после него. Ключевое слово ago можно рассматривать как аналог знака минус (-) в типах даты и времени.

В листинге 3.27 приведены примеры объединения типов date и interval. Как видно из листинга, вычитание инвертированного интервала (с ключевым словом ago) эквивалентно прибавлению обычного интервала по аналогии с вычитанием отрицательных чисел.



Язык SQL и его предшественники



Язык SQL и его предшественники

Язык SQL в значительной степени основан на реляционной алгебре и кортежно-реляционной модели. Реляционная алгебра, представленная Э. Ф. Коддомв 1972 году, определяет базовые принципы обработки синтаксиса SQL; это процедурный способ построения запросов, управляемых данными, который определяет способ достижения поставленной цели. С другой стороны, кортежно-реляционная модель использует декларативные выражения и определяет саму цель при обработке структурированных запросов.

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

Поддержка вставки, модификации и удаления данных. Пользователям разрешается вставлять, удалять и обновлять записи, хранящиеся в базе данных. Математические операторы. В SQL используются математические операторы сложения, вычитания, умножения, деления, а также выражения вида (valuel * 5) + valueZ. Также поддерживаются операторы сравнения (например, values >= value4). Отображение данных. Пользователь может вывести связи, сгенерированные при обработке запроса. Присваивание. Пользователь может переименовать связь, сгенерированную при обработке запроса. Он не ограничен стандартным именем связи, производным от имени поля или функции (в зависимости от запроса). Агрегатные функции. Пользователь может группировать логически связанные записи и вычислять сводные показатели (среднее арифметическое, сумму, количество, максимальное или минимальное значение).

Ключевые слова и идентификаторы



Ключевые слова и идентификаторы

Ключевыми словами называются зарезервированные термины SQL, имеющие особый синтаксический смысл для сервера (INSERT, UPDATE, SELECT, DELETE и т. д.).

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

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

В листинге 3.2 в простую таблицу states включается запись, состоящая из трех полей.



Команды SQL



Команды SQL

Любая концептуальная информация о реляционных базах данных и таблицах приносит пользу лишь в том случае, если вы знаете, как организовать взаимодействие с данными. Язык SQL состоит из структурированных команд, предназначенных для добавления, модификации и удаления данных из базы. Эти команды образуют основу для взаимодействия с сервером PostgreSQL

Этот раздел посвящен «анатомии» команд SQL. В нем рассматриваются структурные компоненты команд, описываются функции каждого компонента и их взаимные связи. Стандартный клиент командной строки PostgreSQL, psql, выводит результаты выполнения команд в приведенных примерах.

Большинство примеров команд SQL относится к базе данных booktown. Весь вывод psql снабжается префиксом вида booktown=#

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

Клиент psql подробно описан в главе 4. Здесь он упоминается лишь для пояснения стиля примеров команд SQL.

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

Схема базы данных booktown (вместе с примерами записей) находится в файле booktown.sql на компакт-диске. Чтобы установить эту базу данных, введите в приглашении командной строки команду psql -U postgres template! -f /mnt/cdrom/booktown.sql, где /mnt/cdrom — путь к смонтированному компакт-диску, a postgres — имя суперпользователя PostgreSQL.



Комментарием называется



Комментарии

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

Константы



Константы

При работе с базами данных многие объекты хранятся на диске, а для обращения к ним используются идентификаторы (имена таблиц, полей и функций). Однако неизбежно настанет момент, когда в систему потребуется передать новые данные — например, при вставке новых записей, при формировании секций с критериями удаления или модификации или при вычислениях на базе существующих записей. Такие данные передаются в виде констант, также иногда называемых «литералами». Константы предназначены для «буквального» представления данных в командах SQL (вместо ссылки на них по идентификатору).

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

строковые константы; битовые последовательности; целочисленные константы; вещественные константы; логические константы.

Краткая история SQL



Краткая история SQL

В 1970 году доктор Э. Ф. Кодд (Е. F. Codd) из компании IBM в статье «A Relational Model of Data for Large Shared Data Banks» дал формальное определение реляционной модели, образующей концептуальную основу SQL. Данная статья вызвала большой интерес как в плане общей приемлемости, так и в плане практических коммерческих приложений подобных систем.

В 1974 году компания IBM начала работу над проектом System/R. Группа под руководством Дональда Чемберлина (Donald Chamberlin) разработала язык SEQUEL (Structured English Query Language). В 1974-75 годах проект System/R был реализован в прототипе SEQUEL-XRM. Затем в 1976-77 годах проект был полностью переделан, в него была включена поддержка многотабличных и многопользовательских средств. Переработанная система сначала называлась «SEQUEL 2», а затем по соображениям авторских прав была переименована в «SQL».

В 1978 году началось доскональное тестирование системы. Оно наглядно продемонстрировало ее удобство и практическую полезность, в результате чего компания IBM начала разработку коммерческих SQL-продуктов, основанных на прототипе System R, включая SQL/DS (1981 год) и DB2 (1983 год).

Другие разработчики программного обеспечения также обратили внимание на растущую популярность реляционной модели и анонсировали свои продукты на базе SQL. К их числу относились СУБД Oracle, Sybase и Ingres (на базе проекта Ingres Калифорнийского университета в Беркли).

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

Как нетрудно догадаться по названию, СУБД PostgreSQL связана с Ingres. Обе СУБД происходят от проекта Ingres Калифорнийского университета.



Краткий курс SQL



Краткий курс SQL

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




Пробелы и переводы строк



Листинг 3.1. Пробелы и переводы строк

testdb-* SELECT * FROM mylist;

todos
---------------------------------------

Pick up laundry. Send out bills.

Wrap up Grand Unifying Theory for publication.

(3 rows)

testdb-# SELECT * testdb-* FROM

testdb-# mylist;

todos
--------------------------------------

Pick up laundry. Send out bills.

Wrap up Grand Unifying Theory for publication.

(3 rows)

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



Ключевые слова и команды



Листинг 3.2. Ключевые слова и команды

booktown=# INSERT INTO states VALUES (33, 'Oregon', 'OR');

INSERT 3389701 1

В листинге 3.2 команда SQL INSERT INTO содержит ключевые слова SQL INSERT, INTO и VALUES.

Команда INSERT INTO модифицирует таблицу, заданную идентификатором states. В данном случае модификация сводится к вставке новой записи.



Преодоление ограничений



Листинг 3.3. Преодоление ограничений

booktown=# CREATE TABLE lst_bent_rule (rule_name text);

ERROR: parser: parse error at or near "1"

booktown=# CREATE TABLE "lst_bent_rule" (rule_name text);

CREATE

Более того, имена таблиц могут содержать некоторые символы, которые обычно считаются недопустимыми (например, пробелы или амперсанды, хотя присутствие кавычек, разумеется, запрещено). Хотя стандарт ANSI/ISO SQL не позволяет создавать идентификаторы с именами, совпадающими с ключевыми словами SQL, PostgreSQL (как и ряд других реализаций SQL) достаточно либерально относится к этому ограничению.— такие имена допустимы, но они должны заключаться в кавычки.

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



Использование строковых констант



Листинг 3.4. Использование строковых констант

booktown=# UPDATE authors

booktown-# SET firstjiame = 'Louisa May'

booktown-l WHERE firstjiame = 'Luoisa May'

UPDATE 1

booktown-# SELECT * FROM authors;

id | lastjiame | firstjiame

1809 | Geisel | Theodor Seuss

1111 | Denham | Ariel 15990 | Bourgeois | Paulette

25041 | Bianco j Margery Williams

115 I Poe I Edgar Allen

16 j Alcott I Louisa May

(6 rows)

Команда UPDATE в листинге 3.4 использует строковые константы Louisa May и Luoisa May в сочетании с ключевыми словами SET и WHERE. Как видно из результатов запроса, команда обновляет содержимое таблицы, заданной идентификатором authors, и исправляет опечатку.

Тот факт, что строковые константы заключаются между апострофами, порождает очевидную семантическую проблему: если в самой последовательности символов встречается апостроф, граница строковой константы будет определена неверно. Чтобы экранировать апостроф в строке (то есть обеспечить его интерпретацию как литерала), следует поставить два апострофа подряд. Модуль лексического анализа воспринимает удвоенный апостроф в строковой константе как один апостроф-литерал. PostgreSQL также позволяет экранировать апострофы обратной косой чертой, в стиле языка С:

testdb=# SELECT 'PostgreSQL"s great!' AS example;

example

PostgreSQL's great! (1 row)

booktown=# SELECT 'PostgreSQLN's

С-style slashes are great!' AS example;

example

PostgreSQL's C-style slashes are great!

(1 row)

В PostgreSQL также поддерживаются служебные последовательности языка С, перечисленные в табл. 3.3.



Разбиение строковых констант



Листинг 3.5. Разбиение строковых констант

booktown=# SELECT 'book'

booktown-#

booktown-# 'end' AS example;

example

bookend (1 row)

booktown=# SELECT 'bookend' AS example;

example

bookend

(1 row)

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

booktown=# SELECT 'book' 'end' AS example;

ERROR: parser: parse error at or near .....

Дело в том, что без разрыва строки PostgreSQL считает, что вы ссылаетесь на две отдельные константы. Объединение двух строковых констант в одной строке выполняется оператором конкатенации 11, описанным в главе 5:

booktown=# SELECT 'book.' || 'end1 AS example; example

bookend (1 row)



Использование битовых последовательностей



Листинг 3.6. Использование битовых последовательностей

testdiH* INSERT INTO my_bytes VALUES (B'OOOOOOOO'):

testdb=# SELECT my_byte FROM my_bytes:

my_byte

10000000

10000001

10000101

11111111

00000000

(5 rows)



Использование целочисленных констант



Листинг 3.7. Использование целочисленных констант

booktown=# SELECT * FROM

authors WHERE id < 100;

id | lastjiame | firstjiame

16 | Alcott | Louisa May (1 row)

booktown=# SELECT * FROM authors WHERE id = 100:

id | lastjiame | firstjiame

(0 rows)

booktown=# UPDATE authors

booktown-* SET id = 116

booktown-tf WHERE id = 16:

UPDATE 1

booktown=# SELECT * FROM authors WHERE id = 116:

id | last_name firstjiame

116 | Alcott | Louisa May (1 row)

В листинге 3.7 секция WHERE команды SELECT сравнивает идентификатор поля id с целочисленной константой 100. Результат состоит из одной записи. После обнаружения записи с недопустимым значением id вводится вторая команда SELECT, которая проверяет, существуют ли в таблице записи с кодом i d=116. Мы убеждаемся в том, что код 116 не задействован в таблице authors, поскольку значения в поле id должны быть уникальными. Операция завершается командой UPDATE, также содержащей целочисленные константы в секциях SET и WHERE.



Допустимые вещественные значения



Листинг 3.8. Допустимые вещественные значения

booktown=# SELECT .04 AS small_float.

booktown-# -16.63 AS negative_float,

booktown-# 4e3 AS exponent!al_float,

booktown-# 6.1e2 AS negative_exponent:

small_float | negative_float |

exponential__float | negative_exponent

0.04 -16.63 4000 | 0.061

(1 row)



Различия между true и 'true'



Листинг 3.9. Различия между true и 'true'

testdb=# SELECT true AS boolean_t. testdb-# 'true' AS string_t,

testdb-# false AS boolean_f,

testdb-# 'false' AS string_f;

boo1_t | string_t | bool_f | string_f

t | true | f | false

(1 row)

Как показано в листинге 3.9, PostgreSQL выводит значения логического типа в виде t или f, однако это вовсе не означает, что символы t и f могут использоваться в качестве логических констант. PostgreSQL не сможет правильно интерпретировать их, что приведет к ошибке.



Операторы в командах SQL



Листинг 3.10. Операторы в командах SQL

booktown=# SELECT * FROM books;

id | title author_id | subject_id

7808 | The Shining |4156 | 9

156 | The Tell-Tale Heart| 15| 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

(6 rows)

booktown=# UPDATE books SET author_id = author _id + 1500;

UPDATE 6

booktown=# SELECT * FROM books;

id title | author_id | subject_id

7808 | The Shining | 5656 | 9

156 | The Tell-Tale Heart 1515| 9

4513| Dune 3366 | 15

4267 | 2001; A Space Odyssey 3501 | 15

1608 | The Cat in the Hat | 3309 |2

1590 | Bartholomew and the Oobleck | 3309 | 2

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

Вероятно, вы уже знакомы с основными математическими операторами: + (суммирование двух числовых величин), - (вычитание) и т. д. Существуют и другие, более экзотические операторы — например, поразрядные операторы & и |, которые модифицируют значения на уровне двоичного представления.

Некоторые ключевые слова SQL также часто относятся к категории операторов. В первую очередь это логические операторы AND, OR и NOT. Формально являясь ключевыми словами, они причисляются к операторам, поскольку предназначаются для выполнения операций с константами и идентификаторами.

Основные операторы PostgreSQL перечислены в табл. 3.6.



Однострочные комментарии



Листинг 3.11. Однострочные комментарии

testdb=# SELECT 'Test1 -- This can follow valid SQL tokens.

testdb-# -- or be on a line of its own. testdb-#

AS example; example

Test

(1 row)

Многострочные комментарии начинаются с последовательности /* и завершаются последовательностью */. Такой способ оформления комментариев хорошо знаком программистам С, но между интерпретатором PostgreSQL и компилятором С существует одно принципиальное отличие: комментарии PostgreSQL могут быть вложенными. Иначе говоря, если внутри многострочного комментария имеется другой многострочный комментарий, то закрывающая последовательность */ внутреннего комментария не закрывает внешний комментарий. Пример многострочного комментария приведен в листинге 3.12.



Многострочные комментарии



Листинг 3.12. Многострочные комментарии

testdb=# SELECT 'Multi /* This comment extends across

testdb-# * numerous lines, and can be

testdb-# * /* nested safely */ */

testdb-# | '-test' AS example; example

Multi-test (1 row)

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

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

Звездочка (без смежного символа косой черты) не имеет особой интерпретации в комментариях. Дополнительные звездочки были включены в листинг 3.12 исключительно по эстетическим сообщениям.



Пример запроса SQL



Листинг 3.13. Пример запроса SQL

booktown=# SELECT Id, name FROM states;

id | name

42 | Washington 51 I Oregon

(2 rows)

booktown=#

В табл. 3.8 и 3.9 анализируется другая, более сложная команда SQL. В ней используется действие UPDATE с секциями SET и WHERE, которые определяют соответственно правило изменения записей и критерий их отбора.



Обновление таблицы командой UPDATE



Листинг 3.14. Обновление таблицы командой UPDATE

booktown=# UPDATE states

booktown-# SET id = 51

booktown-# WHERE

name = 'Oregon';

UPDATE 1

booktown=# SELECT * FROM states

booktown-$ WHERE

name='Oregon':

Id | name | abbreviation

51 | Oregon | OR

(1 row)

booktown=#



Пустые строки и NULL



Листинг 3.15. Пустые строки и NULL

booktown=# SELECT id. title FROM books:

id [ title

7808 | The Shining 156 | The Tell-Tale

Heart 4513 | Dune

100 |

101 | (5 rows)

booktown=# SELECT id. title FROM

books WHERE title = ": id | title

100 (1 row)

Dooktown=# SELECT id, title FROM

books WHERE title IS NULL;

id title

101 |

(1 row)

В листинге 3.16 продемонстрировано более практичное (и реальное) применение ключевого слова NULL в таблице editions, связывающей код ISBN с датой публикации книги.



Пример использования NULL



Листинг 3.16. Пример использования NULL

booktown=# SELECT isbn, publication FROM editions:

isbn | publication

039480001X | 1957-03-01

0394800753 | 1949-03-01

0385121679 | (3 rows)

booktown=# SELECT isbn. publication FROM

editions WHERE publication IS NULL:

isbn | publication

0385121679 |

(1 row)

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



Простая таблица с логическими значениями



Листинг 3.17. Простая таблица с логическими значениями

booktown=# CREATE TABLE daily_inventory

(isbn text. in_stock boolean);

CREATE

booktown=# INSERT INTO

dailyjnventory VALUES ('0385121679', true);

INSERT 3390926 1

booktown=# INSERT INTO dailyjnventory

VALUES ('039480001X'. 't');

INSERT 3390927 1

booktown=# INSERT INTO dailyjnventory

VALUES ('044100590X'. 'true');

INSERT 3390928 1

booktown=# INSERT INTO dailyjnventory

VALUES С0451198492', false);

INSERT 3390929 1

booktown=# INSERT INTO dailyjnventory

VALUES С0394900014', '0');

INSERT 3390930 1

booktown=# INSERT INTO dailyjnventory

VALUES ('0441172717'. '!');

INSERT 3390931 1

booktown=# INSERT INTO dailyjnventory

VALUES ('0451160916');

INSERT 3390932 1

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



Выборка данных по логическому признаку



Листинг 3.18. Выборка данных по логическому признаку

booktown=# SELECT * FROM dailyjnventory WHERE in_stock='yes':

isbn in_stock

0385121679 | t

039480001X | t

044100590X t

0441172717 I t

(4 rows)

В PostgreSQL предусмотрена возможность неявной выборки по значению true в логическом поле; для этого имя поля указывается без операторов или ключевых слов-модификаторов. В хорошо спроектированных таблицах такой подход помогает создавать более наглядные запросы. Пример приведен в листинге 3.19.



Неявная выборка по логическому значению



Листинг 3.19. Неявная выборка по логическому значению

true booktown=# SELECT * FROM dailyjnventory WHERE in_stock;

isbn | in_stock

0385121679 | t

039480001X | t

044100590X | t

0441172717 | t

(4 rows)

Хотя в этом запросе не указана конкретная логическая величина, при отсутствии оператора сравнения подразумевается значение true.

Для выборки по значению false можно либо сравнить значение поля с любой из логических констант, перечисленных в табл. 3.11, либо поставить перед именем поля ключевое слово SQL NOT. Оба способа продемонстрированы в листинге 3.20.



Выборка по логическому значению false



Листинг 3.20. Выборка по логическому значению false

booktown=# SELECT * FROM dailyjnventory WHERE in_stock = 'no';

isbn | in_stock

0451198492 f 0394900014 f (2 rows)

booktown=# SELECT * FROM dailyjnventory

WHERE NOT in_stock:

isbn | in_stock

0451198492 | f

0394900014 | f

(2 rows)

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

Сравнение значений логических полей с константами из табл. 3.11 может осуществляться при помощи оператора неравенства != (например, WHERE in_stock != 't'). Таким образом, следующие три синтаксические формы эквивалентны:

SELECT * FROM daily_iinventory

WHERE NOT injtock: SELECT * FROM

dailyjnventory WHERE in_stock = 'no1:

SELECT * FROM dailyjnventory WHERE in_stock != 't':

Возможно, вы обратили внимание на то, что в листинге 3.17 в таблицу вставляется семь записей, а суммарное количество записей при двух выборках (для поля i n_stock, равного true и fal se) только шесть. Дело в том, что в последней операции вставки в листинге 3.17 значение поля i n_stock не указано, поэтому в записи книги с кодом ISBN равным 0451160916 поле in_stock равно NULL.

Как упоминалось выше, величина NULL не интерпретируется как true или fal se, поэтому для выборки по значению NULL необходимо использовать условие IS NULL. Также можно воспользоваться оператором ! =, но тогда возникают проблемы с адаптацией программы для других СУБД. Пример запроса SQL с условием IS NULL:

booktown=# SELECT * FROM

dailyjnventory WHERE in_stock IS NULL:

isbn | 1n_stock

0451160916 |

(1 row)

Поскольку IS NULL является обычным условием SQL, для обновления всех случайных значений NULL в поле in_stock можно воспользоваться командой UPDATE, приведенной в листинге 3.21.



Исправление случайных значений NULL



Листинг 3.21. Исправление случайных значений NULL

booktown=# UPDATE dailyjnventory

SET in_stock = Т WHERE in_stock IS NULL;

UPDATE 1



Использование типа numeric вместо money



Листинг 3.23. Использование типа numeric вместо money

booktown=# CREATE TABLE money_example (money_cash money.

booktown(# numeric_cash numeric(10,2)):

CREATE

booktown=# INSERT INTO money_example VALUES C$12.241, 12.24);

INSERT 3391095 1

booktown=# SELECT * FROM money_example;

money_cash | numeric_cash

$12.24 | 12.24 (1 row)

booktown=# SELECT money_cash,

booktown-# '$' | ltrim(to_char(numenc_cash. '9999.99'))

booktown-# AS numeric_cashif1ed

booktown-# FROM money_example;

money_cash | numeric_cashified

$12.24 j 12.24

(1 row)



Использование типа serial



Листинг 3.24. Использование типа serial

booktown=# CREATE TABLE autojdentified (id_serial);

NOTICE: CREATE TABLE will create implicit sequence '

auto Jdentif led Jd_seq'

for SERIAL column 'auto_identified.id'

NOTICE: CREATE TABLE/UNIQUE will create

Implicit index 'auto_identified_1d_key' for table

'autojdentified'

CREATE



Решение задачи «вручную»



Листинг 3.25. Решение задачи «вручную»

booktown=# CREATE SEQUENCE autojdentified id_seq;

CREATE

booktown=# CREATE TABLE autojdentified

booktown-# (id integer UNIQUE DEFAULT

nextval('autojdentifiedjd_seq'));

NOTICE: CREATE TABLE/UNIQUE will create implicit index

'autojdentifiedjdjcey' for table

'autojdentified'

CREATE

ВНИМАНИЕ

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



Выбор формата даты



Листинг 3.26. Выбор формата даты

booktown=# SET DATESTYLE TO ISO,US;

SET VARIABLE

booktown=# SHOW DATESTYLE;

NOTICE: DateStyle is ISO with

US (NonEuropean) conventions

SHOW VARIABLE

booktown=f SET DATESTYLE TO

NONEUROPEAN. GERMAN;

SET VARIABLE

booktown=# SHOW DATESTYLE;

NOTICE: DateStyle is German

with European conventions

SHOW VARIABLE

Если дополнительный формат не указан, по умолчанию выбирается подходящее значение (например, для регионального формата German выбирается значение European).

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

Изменение переменной среды PGDATESTYLE на сервере, на котором работает процесс postmaster. Например, при использовании командного интерпретатора bash можно добавить строку PGDATESTYLE="SQL US" в файл .bash_profile пользователя postgres. При запуске процесса postmaster пользователем postgres переменная PGDATESTYLE будет автоматически применяться ко всем операциям форматирования даты и времени, выполняемым PostgreSQL. Изменение переменной среды PGDATESTYLE, используемой клиентским приложением (при условии, что оно было написано с применением библиотеки libpq) в начале сеанса. Этот вариант выбирается в том случае, если формат вывода должен настраиваться клиентом, а не сервером. Например, присваивая значение переменной PGDATESTYLE командой export в приглашении bash перед запуском клиента psql, вы задаете формат, который будет использоваться в работе psql.

Операции с интервалами



Листинг 3.27. Операции с интервалами booktown=# SELECT date('1980-06-25');


date

1980-06-25

(1 row)

booktown=# SELECT interval С 21 years 8 days');

interval

21 years 8 days (1 row)

booktown=# SELECT date('1980-06-25') + interval

('21 years 8 days') booktown-# AS spanned_date:

spanned_date

2001-07-03 00:00:00-07

(1 row)

booktown=# SELECT date ('1980-06-25') -

interval ('21 years 8 days ago1)

booktown-# AS twice_inverted_interval_date;

twice_ nverted_interval_date

2001-07-03 00:00:00-07

(1 row)



Использование констант current и now



Листинг 3.28. Использование констант current и now

booktown=# CREATE TABLE tasklog

booktown=# (taskname char(15),

booktown=# timebegun timestamp,

booktown=# timeflnished timestamp);

CREATE

booktown=# INSERT INTO tasklog VALUES

booktown=# ('delivery', 'now', 'current'):

INSERT 169936 1

booktown=# INSERT INTO tasklog

VALUES booktown=# ('remodeling', 'now', 'current');

INSERT 169937 1

booktown=# SELECT taskname, tlmefnished - timebegun

booktown-# AS timespent FROM tasklog;

taskname | timespent

delivery | 00:15-.32

remodeling [ 00:04:42

(2 rows)

Итак, константа now обычно используется при сохранении в таблице фиксированного момента времени, который не изменяется при последующих ссылках. Как видно из листинга 3.29, плохое понимание различий между константами now и current приводит к потенциальным ошибкам программирования SQL. В листинге приведены две команды INSERT; в первой команде используется now, а во второй — current. Сравнение результатов показывает, что в первой строке при каждом запросе время обновляется, а во второй строке оно всегда остается одинаковым.



Сравнение констант now и current



Листинг 3.29. Сравнение констант now и current

booktown=# INSERT INTO shipments (customer_id. isbn, ship_date)

Ccustomer_id, isbn, ship_date)

booktown-# VALUES (1. '039480001X', 'current');

INSERT 3391221 1

booktown=# INSERT INTO shipments (customer_id. isbn, ship_date)

booktown-# VALUES (2. '0394800753'. 'now');

INSERT 3391222 1

booktown=# SELECT isbn. ship_date FROM shipments: isbn | ship_date

039480001X [ current 0394800753 | 2001-08-10 18:17:49-07

(2 rows)

booktown=# SELECT isbn.

booktown-# to_char(ship_date. 'YYYY-MM-DD HH24:MI:SS')

booktown-# AS value

booktown-# FROM shipments; isbn value

039480001X | 2001-08-10 18:21:22 0394800753 | 2001-08-10 18:17:49

(2 rows)

booktown=# SELECT isbn.

booktown-# to_char(ship_date. 'YYYY-MM-DD HH24:MI:SS')

booktown-# AS value

booktown-# FROM shipments;

isbn | value

039480001X | 2001-08-10 18:22:35 0394800753 | 2001-08-10 18:17:49

(2 rows)



Использование функции преобразования типа



Листинг 3.30. Использование функции преобразования типа

booktown=# SELECT text(1000)

booktown-# AS explicitjtext;

explicit_text



Идентификация записей по OID



Листинг 3.31. Идентификация записей по OID

3Stdb=# SELECT * FROM my_list;

todos
----------------------------------
Correct redundancies In my_list.

Correct redundancies in my_list.

(1 rows)

testdb=# SELECT *. old FROM my_list:

todos | old
----------------------------------------
Correct redundancies in my list. | 3391263

Correct redundancies In my list. | 3391264

( 2 rows)

testdb=# DELETE FROM my_list

testdb-# WHERE old = 3391264;

DELETE 1

testdb=# SELECT *.oid FROM my_list;

todos old
----------------------------------------------

Correct redundancies in my list. | 3391263

(1 row)



Логические константы



Логические константы

Логические (булевы) константы гораздо проще всех остальных типов констант PostgreSQL, поскольку они принимают всего два допустимых значения: true и false. Встретив любое из этих значений, не заключенное в апострофы, PostgreSQL интерпретирует его как логическую константу. Пример показан в листинге 3.9.



Логические значения



Логические значения

Логическим значением называется простая структура данных, представляющая одну из двух величин: true или f al se. В PostgreSQL поддерживается тип данных boo! ean, определенный в стандарте SQL99, с нестандартным синонимом bool.

Логическим переменным, как и другим типам данных, может присваиваться значение NULL, Логическая переменная, равная NULL, никогда не интерпретируется как true или false; она интерпретируется только как NULL. Если вы хотите проверить, равна ли логическая переменная псевдозначению NULL, не пытайтесь сравнивать ее с false, это бессмысленно. Для этой цели следует использовать конструкцию IS NULL. Способность логической переменной принимать значения true, false и NULL (и правила, в соответствии с которыми NULL считается отличным от этих двух величин) называется тройственной логикой.

В табл. 3.11 приведены допустимые обозначения логических констант, правильно опознаваемые PostgreSQL. Выбор обозначений является делом вкуса. Все разновидности значения true, как и все разновидности значения fal se, интерпретируются сервером одинаково.



Массивы



Массивы

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

Массив представляет собой совокупность элементов с общим идентификатором. Элементы массива могут относиться как к встроенному, так и к пользовательскому типу данных, но они обязательно должны быть однотипными. При обращении к элементам массива используется индексная запись с квадратными скобками (например, ту_аггау[0]). Элементы массивов-констант перечисляются в фигурных скобках, заключенных в апострофы (например, '{value_one.value_two,value_three}').



Массивы константы



Массивы - константы

При вставке в поле таблицы нового значения, которое представляет собой массив, в команде SQL необходимо перечислить входящие в него элементы. В соответствии с синтаксисом массивов-констант перечисляемые элементы ограничиваются символами-разделителями (запятыми для встроенных типов) и заключаются в фигурные скобки, которые, в свою очередь, заключаются в апострофы: '{ элемент!, элемент2 [ . ...] }'

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

В PostgreSQL эта проблема решается заключением строковых констант в кавычки в тех случаях, когда вне контекста массива обычно используются апострофы: '{"valuel","value 2. which contains a comma" }'

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