Массивы в таблицах
Массивы в таблицах
В PostgreSQL предусмотрен синтаксис для определения массивов как фиксированного, так и переменного размера; тем не менее в PostgreSQL 7.1.2 ограничение размера не соблюдается. Это означает, что с точки зрения программы массив может иметь фиксированный размер, но при этом изменение размеров все равно производится динамически. Например, поле таблицы, определенное как массив из трех элементов, может содержать три элемента в одной записи, четыре элемента в другой и вообще не содержать элементов в третьей.
Также допускается определение многомерных массивов, в которых каждый элемент может быть не атомарным значением, а другим массивом. При выборке из многомерных массивов элементы выводятся во вложенных фигурных скобках:
booktown=# SELECT editions FROM myjiotes
WHERE title='The Cat In the Hat';
editions
{{"039480001X"."lst Ed. hard Cover"}.
{"039400014"."1st Ed"}} Cl row)
Обязательная защита идентификаторов
Обязательная защита идентификаторов
Идентификаторы обязательно должны заключаться в кавычки только в двух случаях: если идентификатор объекта базы данных совпадает с ключевым словом или в его имени присутствует хотя бы одна прописная буква. В любом из этих случаев идентификатор должен защищаться как при создании объекта, так и при последующих ссылках на него в командах SELECT, DELETE или UPDATE и т. д.
Если не заключить в кавычки идентификатор, совпадающий с ключевым словом, PostgreSQL выдаст сообщение об ошибке, поскольку идентификатор интерпретируется как ключевое слово. Допустим, имеется таблица с именем select. При попытке обратиться к ней со следующим запросом вы получите сообщение об ошибке:
testdb=# SELECT * FROM select
ERROR: parser: parse error at or near "select"
Как показывает этот пример, запрос к незащищенному идентификатору таблицы select приводит к ошибке. Чтобы идентификатор select воспринимался как имя таблицы, а не как ключевое слово, его необходимо заключить в кавычки. Таким образом, правильный запрос к таблице с именем sel ect должен выглядеть так:
testdb=# SELECT * FROM "select";
selected
1
52 105 (4 rows)
Аналогично следует поступать и с идентификаторами, содержащими хотя бы один символ верхнего регистра. Например, если вы по какой-либо причине создали таблицу с именем ProDucts (обратите внимание на прописные буквы Р и D) и теперь хотите ее уничтожить (еще бы, с таким именем!), идентификатор также следует заключить в кавычки:
D0oktown=# DROP TABLE ProDucts;
iRROR: table "products" does not exist
booktown=# DROP TABLE "ProDucts";
DROP
Защита идентификаторов иногда бывает чрезвычайно полезной, даже если вы зсегда создаете объекты базы данных только с «правильными» именами. Наприпер, при импортировании данных через внешнее соединение ODBC (например, is Microsoft Access) имена созданных таблиц могут быть записаны символами верх-iero регистра. Без защиты идентификаторов вам не удастся использовать такие габлицы в программе.
Операторы
Операторы
Другую категорию специальных символов составляют операторы, предназначенные для выполнения различных операций с идентификаторами или константами. Операторы могут использоваться как для математических вычислений (сложение, вычитание и т. д.), так и для сравнения и логических операций.
Вернемся к таблице books и ее числовому полю author_id. Вспомните, что в этом поле хранится целочисленный код, определяющий автора книги. Теперь представьте, что вследствие модификации системы все коды авторов должны быть увеличены на 1500. Задача решается командой UPDATE и выполнением операции с полем author_id. При этом используется оператор сложения (+). Пример приведен в листинге 3.10.
Предварительное планирование
Предварительное планирование
Прежде чем переходить к непосредственному созданию таблиц, желательно выде-ить немного времени на предварительное планирование объектов базы данных, также на выбор имени, типа и смысла каждого поля в таблице. В результате пла-ирования схема выбора имен становится более стройной и последовательной, это, в свою очередь, приводит к появлению более наглядных и «вразумительных» команд и запросов.
Кроме перечисленных семантических факторов (имена, типы и смысл полей), еобходимо проследить за четким установлением связей между таблицами. Проек-ирование связей является важной частью процесса проектирования таблиц, по-кольку любые ошибки в этой области — как дублирование больших объемов дан-ых, так и случайное исключение важных данных из таблиц — являются крайне ежелательными.
Вернемся к таблице books базы данных booktown, структура которой приведена табл. 3.1. В полях каждой записи хранится внутренний код книги, название, код втора и код темы. Обратите внимание: вместо полного имени автора и текстового писания темы в таблице хранятся простые целочисленные коды, используемые ля связи с двумя другими таблицами: authors и subjects. Содержимое этих таблиц астично иллюстрируют табл. 3.26 и 3.27.
Преобразование типов
Преобразование типов
В PostgreSQL поддерживаются три отдельных варианта синтаксиса преобразования (приведения) типов, то есть механизма приведения данных от одного типа к другому. В команде SQL преобразование типов позволяет явно задать тип создаваемой константы (вместо его косвенного определения по правилам языка).
В общем случае приведение строковой константы к другому типу может выполняться любым из трех способов:
тип 'значение'
'значение': -.тип
CAST ('значение' AS тип)
Числовые константы преобразуются в символьную строку следующими способами:
значение: : тип
CAST (значение AS тип)
Здесь значение представляет константу, тип которой требуется изменить, а тип — новый тип этой константы.
Примечание 1
Примечание 1
Помните, что тип money считается устаревшим, что несколко затрудняет его использование в преобразованиях.
Преобразование к другому типу данных не ограничивается одними константами. Поля набора данных, возвращаемого запросом SQL, также могут преобразовываться к другому типу, при этом используются следующие формы синтаксиса:
идентификатор:: тип
CAST (идентификатор AS тип)
Следует учесть, что не каждый тип данных может быть приведен к любому другому типу. Например, не существует осмысленного преобразования символьной строки abed в двоичную последовательность типа bit. Недопустимые попытки преобразования приводят к ошибкам PostgreSQL. Чаще всего встречаются преобразования символьных строк, типов даты/времени или числовых типов к типу text, а также символьных строк в числа.
Кроме синтаксических форм преобразования типа существуют некоторые функции, позволяющие добиться практически того же результата. Имена этих функций часто совпадают с именами итоговых типов (например, text()), хотя существуют и узкоспециализированные варианты (например, bitfromint4()). В листинге 3.30 приведен пример использования функции text О для преобразования целого числа 1000 в строку типа text, содержащую символы «1000».
Псевдозначение NULL
Псевдозначение NULL
Выше говорилось о том, что с каждым полем ассоциируется определенный тип данных и поле принимает значения только этого типа. Тем не менее существует значение, которое может храниться в любых полях независимо от типа; в SQL оно представлено ключевым словом NULL. Ключевое слово NULL не соответствует конкретному объекту данных и потому вообще не считается типом; это системное ключевое слово, которое указывает базе данных на то, что поле не содержит никакого значения. Единственное исключение из правила об универсальности NULL составляют поля, для которых установлено ограничение NOT NULL.
Ключевое слово NULL часто используется для представления необязательных значений. Оно позволяет обойтись без странных и противоестественных схем, например представления отсутствующих данных в целочисленном поле отрицательными числами. Ситуация со временем может измениться, но NULL — всегда NULL.
NULL можно рассматривать как псевдозначение, то есть признак отсутствия значения, который никогда не может быть эквивалентен значению, отличному от NULL. Одна из распространенных ошибок при работе с псевдозначениями NULL связана с тем, что их часто путают с пустыми символьными строками. Отчасти это связано с тем, что при выборке пустой строки данные в клиенте не выводятся. При выборке NULL происходит то же самое, однако NULL принципиально отличается от пустых строк, и это необходимо хорошо понимать, чтобы ваши запросы правильно работали. Поле строкового типа, содержащее пустую строку, содержит последовательность символов, хотя бы и пустую; таким образом, поле имеет определенное значение. Ключевое слово NULL обозначает полное отсутствие значения в поле.
Это весьма принципиальное различие, поскольку правила выполнения операций SQL с пустыми строками очень отличаются от правил операций с псевдозначениями NULL. Особенно заметно эти различия проявляются при объединениях, рассматриваемых в главе 4.
Примеры выборки NULL и пустых строк приведены в листинге 3.15. Первый запрос SELECT показывает, что записи двух книг были вставлены в таблицу без названий (поле ti tie). Тем не менее из последующих запросов становится видно, что в одной записи (id=100) это поле содержит пустую строку, а в другой записи — NULL
Символьные типы
Символьные типы
Символьные типы используются в любых операциях с символьными данными (например, с фрагментами текста в кодировке ASCII). Обычно они применяются для хранения имен, адресов и т. д.
В стандартном языке SQL поддерживаются два символьных типа character и character varyi ng. В PostgrcSQL к ним добавился обобщенный тип text, не требующий явного задания максимального размера поля. Размер полей типа text автоматически изменяется в соответствии с объемом хранящихся данных и практически не ограничивается (размер поля не может превышать один гигабайт, но на практике поля такого размера почти не встречаются). В табл. 3.12 перечислены символьные типы данных PostgreSQL.
Синтаксические символы
Синтаксические символы
Некоторые специальные символы выполняют в командах SQL примерно те же функции, что и знаки препинания в английском языке. В табл. 3.5 перечислены основные синтаксические символы PostgreSQL.
Системные поля
Системные поля
В PostgreSQL все таблицы содержат системные поля, которые остаются невидимыми для пользователя и не выводятся при выборке (если служебная информация не запрашивается специально). В системных полях хранятся метаданные, описывающие содержимое записей. Некоторые из них позволяют различать кортежи (фиксированные состояния записей) при работе с блоками транзакций (за дополнительной информацией о транзакциях обращайтесь к главе 7).
В табл. 3.25 перечислены системные поля, присутствующие в каждой записи в дополнение к полям, определенным пользователем в структуре таблицы.
Совместимость
Совместимость
Для сохранения совместимости с предыдущими версиями PostgreSQL разработчики продолжают поддерживать типы данных datetime и timespan. Тип datetime эквивалентен timestamp, а тип timespan — типу interval.
К числу типов даты/времени также относятся типы abstime и reltime, обладающие пониженной точностью представления. Тем не менее это внутренние типы PostgreSQL, которые могут исчезнуть в следующих версиях. Старайтесь использовать только SQL-совместимые типы данных и как можно скорее устранить устаревшие типы из существующих приложений.
Специальные символы
Специальные символы
Специальные символы имеют особое синтаксическое значение в PostgreSQL. По этой причине они обычно не могут использоваться в идентификаторах, хотя, как упоминалось выше в пункте «Защищенные идентификаторы» подраздела «Ключевые слова и идентификаторы», данное ограничение обычно удается обойти при помощи кавычек.
Стандарты SQL
Стандарты SQL
В 1986 году язык SQL был стандартизирован Американским национальным институтом стандартов (ANSI), а в 1987 году появился стандарт Международной организации по стандартам (ISO). Стандарт ANSI/ISO был принят в качестве Федерального стандарта по обработке информации (FIPS) правительства США. В 1989 году был опубликован пересмотренный стандарт, который обычно обозначается сокращениями «SQL89» и «SQL1».
По некоторым причинам (отчасти из-за столкновения интересов фирм-разработчиков) стандарт SQL89 был намеренно оставлен незавершенным, а многие возможности были отнесены к разряду «определяемых при реализации». С целью укрепления стандарта комитет ANSI пересмотрел свою предыдущую работу, и в 1992 году был принят стандарт SQL92 (также называемый SQL2). В новом стандарте были исправлены некоторые недостатки SQL89 и намечены концептуальные особенности SQL, которые на тот момент превосходили возможности любых существующих реализаций РСУБД. Кстати, стандарт SQL92 был в шесть раз длиннее своего предшественника. Вследствие расхождений стандарта с текущей ситуацией авторы определили три уровня соответствия SQL92: начальное соответствие (минимальные улучшения в SQL89), промежуточное соответствие (реально достижимый набор принципиальных улучшений) и полное соответствие (стопроцентное выполнение всех положений SQL92).
Позднее, в 1999 году, в ANSI/ISO был опубликован стандарт SQL99, также называемый SQL3. В этом стандарте рассматривались некоторые нетривиальные аспекты современных SQL-систем, в том числе концепции объектно-реляционных баз данных, интерфейсы уровня вызова и обеспечение логической целостности. На смену уровням соответствия SQL92 пришли новые уровни: базовый и расширенный.
В настоящее время PostgreSQL соответствует большинству положений начального уровня стандарта SQL92, а также многим положениям промежуточного и полного уровней. У многих новых положений, появившихся в SQL99, существуют аналоги в области объектно-реляционных концепций PostgreSQL (массивы, функции и наследование).
Строковые константы
Строковые константы
Строковая константа представляет собой произвольную последовательность символов, заключенную в апострофы. Строковые константы часто используются при вставке новых данных в таблицу и при передаче символьной информации другим
объектам базы данных. Ниже приведен пример использования строковых констант при обновлении имен и фамилий таблицы authors базы данных booktown:
booktown=# SELECT * FROM authors;
id | lastjiame | firstjiame
1809 Geisel | Theodor Seuss
1111 | Denham | Ariel
15990 | Bourgeois | Paulette
25041 | Bianco | Margery Williams
16 | Alcott I Luoisa May
115 | Рое | Edgar Allen
(6 rows)
Из результатов запроса видно, что поле firstjiame с кодом id=16, Louisa May, было ошибочно записано в виде Luoi sa May. Ошибка исправляется командой UPDATE со строковой константой, приведенной в листинге 3.4.
Структура имен идентификаторов
Структура имен идентификаторов
Максимальная длина ключевых слов и идентификаторов PostgreSQL равна 31 симюлу. В процессе лексического разбора все ключевые слова и идентификаторы большей длины автоматически усекаются. Идентификаторы начинаются с любой буквы английского алфавита (a-z) или с символа подчеркивания, далее следует фоизвольное сочетание букв, цифр (0-9) и символов подчеркивания. Ключевые лова не могут начинаться или завершаться символом подчеркивания, но для имен щентификаторов это разрешено. Ни ключевые слова, ни идентификаторы не могут начинаться с цифры.
Выше в пункте «Обязательная защита идентификаторов» было показано, что включение идентификатора в кавычки позволяет «преодолеть» правило игнорирования регистра символов. То же относится и к правилу, согласно которому иденификатор не может начинаться с цифры. Хотя без кавычек PostgreSQL не позволит создать таблицу с именем lst_bent_rul e, в кавычках это имя становится приемлемым.
В листинге 3.3 первая команда пытается создать таблицу с недопустимым именем, после чего вторая команда преодолевает это ограничение при помощи кавычек.
Пример таблицы SQL
Таблица 3.1. Пример таблицы SQL
id |
title |
authorjd |
subjected |
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 |
Таблица состоит из четырех столбцов, следующих слева направо в фиксированном порядке. В настоящий момент она содержит шесть записей, также иногда называемых кортежами (tuples). Обратите внимание на очень важное обстоятельство: несмотря на фиксированный порядок столбцов в реляционной базе данных, записи хранятся в произвольном порядке. Как будет показано при описании структуры запросов SQL в главе 4, в SQL существуют средства для упорядочивания записей при выборке, но автоматическое упорядочение записей в самой базе не производится. Если в запросе SQL записи должны следовать в определенном порядке, вы должны явно включить в запрос соответствующую секцию.
Каждая таблица содержит минимум один столбец, однако таблица может не содержать ни одной записи. Каждый вертикальный столбец соответствует фиксированному атрибуту данных, представленных в таблице (как, например, столбец title в таблице books из приведенного выше примера). Без столбцов содержимое соответствующего элемента данных становится неопределенным, тогда как без записей в таблице просто отсутствуют данные. В PostgreSQL 7.1 таблица может содержать до 1600 столбцов и неограниченное количество записей (точнее, ограниченное только аппаратными факторами — например, объемом свободного дискового пространства).
В табл. 3.1 имена столбцов наглядно характеризуют смысл хранящихся в них данных. Впрочем, они выбираются более или менее произвольно, поэтому в процессе планирования имен в таблицах следует помнить о предотвращении возможных конфликтов имен.
Хотя на первый взгляд это и не очевидно, каждый столбец таблицы характеризуется определенным типом данных. Тип данных не только помогает лучше описать информацию, хранящуюся в столбце, но и ограничивает его содержимое. Например, столбец author_id имеет тип Integer; это означает, что любая попытка вставки записи, у которой в этом столбце не находится целое число (например, ПОа), завершится неудачей. Типы данных столбцов подробно описаны в разделе «Типы данных».
В этом разделе были представлены общие принципы логической организации данных в реляционных базах и таблицах. В следующем разделе объясняется, почему все операции с базой осуществляются при помощи команд SQL.
Основные действия PostgreSQL
Таблица 3.2. Основные действия PostgreSQL
Действие |
Описание |
CREATE DATABASE |
Создание новой базы данных |
CREATE INDEX |
Создание нового индекса для столбца таблицы |
CREATE SEQUENCE |
Создание новой последовательности в существующей базе данных |
CREATE TABLE |
Создание новой таблицы в существующей базе данных |
CREATE TRIGGER |
Создание нового определения триггера |
CREATE VIEW |
Создание нового представления для существующей таблицы |
SELECT |
Выборка записей из таблицы |
INSERT |
Вставка одной или нескольких новых записей в таблицу |
UPDATE |
Модификация данных в существующих записях |
DELETE |
Удаление существующих записей из таблицы |
DROP DATABASE |
Уничтожение существующей базы данных |
DROP INDEX |
Удаление индекса столбца из существующей таблицы |
DROP SEQUENCE |
Уничтожение существующего генератора последовательности |
DROP TABLE |
Уничтожение существующей таблицы |
DROP TRIGGER |
Уничтожение существующего определения триггера |
DROP VIEW |
Уничтожение существующего представления |
CREATE USER |
Создание в системе новой учетной записи пользователя PostgreSQL |
ALTER USER |
Модификация существующей учетной записи пользователя PostgreSQL |
DROP USER |
Удаление существующей учетной записи пользователя PostgreSQL |
GRANT |
Предоставление прав доступа к объекту базы данных |
REVOKE |
Лишение прав доступа к объекту базы данных |
CREATE FUNCTION |
Создание новой функции SQL в базе данных |
CREATE LANGUAGE |
Создание нового определения языка в базе данных |
CREATE OPERATOR |
Создание нового оператора SQL в базе данных |
CREATE TYPE |
Создание нового типа данных SQL в базе данных |
Несмотря на явное сходство с языками программирования, разработчики поставили целью сделать язык SQL по возможности удобным и наглядным. В результате команды SQL часто напоминают простые предложения на английском языке. В отличие от обычных языков программирования, команды SQL указывают серверу, какие данные требуется найти, но не сообщают, как это должно происходить. В результате хорошо написанный запрос SQL читается почти так же легко, как обычное предложение.
Примечание 2
Примечание 2
В учебниках SQL термины «команда» и «запрос» часто считаются эквивалентными. В этой книге термин «запрос» используется только по отношению к командам, возвращающим данные (например, SELECT), а не к общим командам SQL, которые также могут создавать и модифицировать данные.
Во внутреннем представлении PostgreSQL структурированные команды SQL интерпретируются в виде последовательности лексем, обычно разделяемых пропусками (пробелами или символами новой строки вне парных ограничителей), хотя некоторые лексемы могут следовать без пропусков, если это не вызывает неоднозначной интеопоетапии (например, опепатопы могут стоять вплотную к идеитификаторам). В данном контексте лексемой считается слово или символ, осмысленно идентифицируемый сервером в процессе разбора (интерпретации) команды SQL.
С технической точки зрения каждая лексема может быть ключевым словом, идентификатором, защищенным идентификатором, константой (также встречается термин «литерал») или одним из специальных символов. К категории ключевых слов PostgreSQL относит слова, имеющие заранее определенный смысл в контексте SQL или PostgreSQL — действия, секции, имена функций и некоторые необязательные составляющие команд SQL (как, например, слово WORK в команде COMMIT). Идентификаторы представляют имена переменных для таблиц, столбцов и других объектов баз данных.
Ключевые слова и идентификаторы относятся к внутренним функциям, значениям и записям, смысл которых определяется PostgreSQL С другой стороны, константы описывают данные, интерпретируемые буквально (например, числа или символьные строки).
Наконец, команды SQL могут содержать специальные символы. К этой категории относятся зарезервированные символы (круглые и квадратные скобки, точка с запятой), влияющие на смысл и расположение ключевых слов, идентификаторов и литералов. Специальные символы можно рассматривать как своего рода «знаки препинания» в командах SQL.
Операторы также относятся к категории специальных символов и используются для применения логических или математических операций между данными (литеральными или представленными в виде идентификаторов). Обычно операторы содержат от одного до четырех символов.
В нескольких ближайших подразделах эти базовые компоненты SQL описываются более подробно.
Служебные последовательности
Таблица 3.3. Служебные последовательности PostareSQL в стиле С
Последовательность |
Описание |
\\ |
Обратная косая черта (литерал) |
V |
Апостроф (литерал) |
\Ь |
Забой |
\f |
Подача листа |
\п |
Новая строка |
\г |
Возврат курсора |
\t |
Табуляция |
\ххх |
ASCII-символ с восьмеричным кодом ххх |
ВНИМАНИЕ
Вследствие того что обратная косая черта имеет особый смысл (см. табл. 3.3), при включении в строку этот символ обязательно экранируется другой косой чертой (например, в строке 'A single backslash is: \\' двойной символ обратной косой черты преобразуется в один).
Если две строковые константы PostgreSQL разделены промежутком, в который входит хотя бы один символ новой строки, они объединяются в одну строковую константу. Пример приведен в листинге 3.5.
Представление величин с плавающей точкой
Таблица 3.4. Представление величин с плавающей точкой
Представление | Пример |
##.## | 6.4 |
##e[+-]## | 8е-8 |
[##].##[e[+-]##] | .04e8 |
##.[##][e[+-]##] | 4e.5 |
В первом формате до десятичной точки и после нее должна стоять хотя бы одна цифра. Это необходимо для того, чтобы модуль лексического анализа PostgreSQL опознал значение как вещественную, а не целочисленную константу. В других форматах хотя бы одна цифра должна стоять до или после экспоненты, обозначенной буквой е. Наличие десятичной точки и/или экспоненты отличает вещественные константы от целочисленных.
В листинге 3.8 использование всех вещественных форматов продемонстрировано на примере простой команды SQL SELECT.
Синтаксические символы
Таблица 3.5. Синтаксические символы
Символ |
Определение |
* (звездочка) |
Выборка всех полей таблицы в команде SELECT, а также подсчет всех записей в агрегатной функции count () |
( ) (круглые скобки) |
Группировка выражений, изменение приоритета операторов и вызов функций. Смысл круглых скобок в значительной степени зависит от контекста |
[ ] (квадратные скобки) |
Выборка конкретного элемента массива или объявление типа массива (например, в команде CREATE TABLE) |
: (точка с запятой) |
Признак завершения команды SQL. Внутри команд может использоваться только в строковых константах и защищенных идентификаторах |
. (запятая) |
Разделитель элементов в списке |
. (точка) |
Десятичный разделитель в вещественных константах (например, 3.1415), а также квалификатор имен полей (например, table name. column name) |
: (двоеточие) |
Определение срезов (slices) в массивах |
$ (знак доллара) |
Обозначение позиционного параметра в определении функции |
Основные операторы PostgreSQL
Таблица 3.6. Основные операторы PostgreSQL
Оператор |
Определение |
Математические операторы |
|
+ |
Сложение двух чисел |
- |
Вычисление разности двух чисел |
/ |
Вычисление частного от деления двух чисел |
* |
Умножение двух чисел |
! |
Факториал целого числа |
§ |
Модуль (абсолютное значение) числа |
Операторы сравнения |
|
= |
Проверка эквивалентности двух величин |
< |
Проверка условия «первое число меньше второго» |
> |
Проверка условия «первое число больше второго» |
~ |
Поиск совпадения регулярного выражения в тексте |
Логические операторы |
|
NOT |
Логическое отрицание |
AND |
Логическая конъюнкция (true, если оба логических операнда равны true) |
OR |
Логическая дизъюнкция (true, если хотя бы один из логических операндов равен true) |
Смысл многих операторов может изменяться в зависимости от контекста, но оператор = играет особенно важную роль в секции SET команды UPDATE.
Хотя в большинстве выражений оператор = требуется для проверки эквивалентности двух величин, в сочетании с идентификатором в секции SET команды UPDATE он становится оператором присваивания и используется для присваивания нового значения существующему идентификатору.
За дополнительной информацией об операторах обращайтесь к разделу «Операторы» в главе 5.
Простой запрос SQL
Таблица 3.7. Простой запрос SQL
SELECT |
id, name |
FROM |
states |
|
Тип лексемы |
Ключевое слово |
Идентификаторы |
Ключевое слово |
Идентификатор |
Описание |
Команда |
Имена полей |
Имя секции |
Имя таблицы |
Как видно из таблицы, команда SELECT содержит ключевые слова SELECT и FROM. Ключевое слово FROM с лексемой states образует секцию, уточняющую смысл команды SELECT.
Лексемы id, name и states в приведенном примере являются идентификаторами. Идентификаторы Id и name определяют выбираемые поля, а идентификатор states определяет имя таблицы, из которой производится выборка. Таким образом, приведенный выше запрос приказывает PostgreSQL выбрать поля Id и name каждой записи таблицы states. В листинге 3.13 показаны результаты выполнения этого запроса.
Команда UPDATE с секцией SET
Таблица 3.8. Команда UPDATE с секцией SET
UPDATE |
states |
SET |
id |
= |
51 |
Ключевое слово |
Идентификатор |
Ключевое слово |
Идентификатор |
Оператор |
Целочисленная константа |
Команда |
Имя таблицы |
Имя секции |
Имя поля |
Присваивание |
Новое значение поля id |
Секция WHERE
Таблица 3.9. Секция WHERE
WHERE |
name |
= |
'Oregon' |
Ключевое слово |
Идентификатор |
Оператор |
Строковая константа |
Имя секции |
Имя поля |
Проверка эквивалентности |
Искомое значение |
Приведенная команда просматривает содержимое поля name каждой записи и проверяет, совпадает ли оно с условием секции WHERE (совпадение со строковой константой ' Oregon'). Затем во всех записях, соответствующих этому условию, полю id присваивается значение 51.
Итак, рассмотренная команда UPDATE содержит три ключевых слова, три идентификатора и две константы. Ключевыми словами являются лексемы UPDATE (выполняемое действие), SET (правило обновления записей) и WHERE (критерий отбора обновляемых записей).
Оба оператора представлены знаком =. В секции SET этот знак используется для присваивания (то есть обновления поля существующей записи) — применение, специфическое для секции SET. С другой стороны, в секции WHERE оператор = используется для сравнения двух значений. В данном примере поле name записи сравнивается со строковой константой Oregon.
Наконец, в примере присутствует целочисленная константа 51 (новое значение поля id) и строковая константа Oregon (сравниваемая с полем name в секции WHERE).
Таким образом, команда UPDATE, приведенная в листинге 3.14, обновляет таблицу states, присваивая значение 51 полю Id всех записей, у которых поле name содержит значение Oregon. Результат проверяется следующей командой SELECT.
Типы данных PostgreSQL
Таблица 3.10. Типы данных PostgreSQL
Тип данных |
Описание |
Стандарт |
Логические и двоичные типы данных |
||
boolean, bool |
Отдельная логическая величина (true или false) |
SQL99 |
bit(n) |
Битовая последовательность фиксированной длины (ровно nбит) |
SQL92 |
bit varying(/7),varbit(rt) |
Битовая последовательность переменной длины (до n бит) |
SQL92 |
Символьные типы | ||
character(n), char(n) |
Символьная строка фиксированной длины (ровно n символов) |
SQL89 |
character varying(n), varchar(n) |
Символьная строка переменной длины (до n символов) |
SQL92 |
text |
Символьная строка переменной или неограниченной длины |
PostgreSQL |
Числовые типы |
||
small int, int2 |
2-байтовое целое со знаком |
SQL89 |
integer, int, int4 |
4-байтовое целое со знаком |
SQL92 |
bigint, int8 |
8-байтовое целое со знаком, до 18 цифр |
PostgreSQL |
real, float4 |
4-байтовое вещественное число |
SQL89 |
double precision, floats, float |
8-байтовое вещественное число |
SQL89 |
numeric(p.s), |
Число из р цифр, содержащее 5 цифр в дробной части |
SQL99 |
money |
Фиксированная точность, представление денежных величин |
PostgreSQL, |
serial |
4-байтовое целое с автоматическим приращением |
PostgreSQL |
Время и дата | ||
date |
Календарная дата (день, месяц и год) |
SQL92 |
time |
Время суток |
SQL92 |
time with time zone |
Время суток с информацией о часовом поясе |
SQL92 |
timestamp |
Дата и время |
SQL92 |
interval |
Произвольный интервал времени |
SQL92 |
Геометрические типы |
||
box |
Прямоугольник на плоскости |
PostgreSQL |
line |
Бесконечная линия на плоскости |
PostgreSQL |
Iseg |
Отрезок на плоскости |
PostgreSQL |
circle |
Круг с заданным центром и радиусом |
PostgreSQL |
path |
Замкнутая или разомкнутая геометрическая фигура на плоскости |
PostgreSQL |
point |
Точка на плоскости |
PostgreSQL |
polygon |
Замкнутый многоугольник на плоскости |
PostgreSQL |
Сетевые типы | ||
cidr |
Спецификация сети IP |
PostgreSQL |
inet |
Сетевой IP-адрес с необязательными битами подсети | PostgreSQL |
macaddr |
МАС-адрес (например, аппаратный адрес адаптера Ethernet) | PostgreSQL |
Системные типы |
||
old |
Идентификатор объекта (записи) |
PostgreSQL |
xid |
Идентификатор транзакции |
PostgreSQL |
В следующих подразделах будут более подробно описаны самые распространенные и наиболее часто используемые типы данных. Нестандартные и/или экзотические типы (например, геометрические, сетевые и битовые) в книге подробно не рассматриваются. Ниже приводится информация о возможностях использования, некоторых аспектах хранения, входных и выходных форматах и общих синтаксических правилах. Но перед описанием конкретных типов данных необходимо сказать несколько слов о ключевом слове NULL.
Логические константы
Таблица 3.11. Логические константы
True |
False |
true |
false |
't' |
Т |
'true' |
'false' |
'У' |
'n' |
'yes' |
'no' |
'1' '0' |
ВНИМАНИЕ
Помните, что все константы, перечисленные в табл. 3.11 (за исключением true и false), должны заключаться в апострофы. В противном случае сервер выдает сообщение об ошибке.
В листинге 3.17 приведен пример создания таблицы da I ly_i inventory с информацией о наличии книг в магазине. В этой таблице код ISBN ассоциируется с логическим признаком. После создания таблица заполняется серией команд INSERT, в которых передается строковая константа (код ISBN) и логические константы в разных форматах.
Символьные типы
Таблица 3.12. Символьные типы
Тип |
Размер |
Описание |
character(rt), chart/7) |
(4+ л) байт |
Символьная строка фиксированной длины, дополненная пробелами до п символов |
character varying(/?), varchar(/?) |
До (4+л) байт |
Символьная строка переменной длины, максимальный размер равен п |
text |
Переменный |
Строка переменной длины, максимальный размер не ограничен |
Параметр п в табл. 3.12 представляет произвольное количество символов и задается для поля при его создании.
Примечание 1
Примечание 1
Тип данных text не предусмотрен в стандарте ANSI/ISO SQL, однако он поддерживается многими реляционными СУБД, в том числе Sybase и MS SQL Server.
Числовые типы PostgreSQL
Таблица 3.13. Числовые типы PostgreSQL
Тип |
Размер |
Допустимые значения |
bigint, int8 |
8 байт |
Целые числа в интервале от -9 223 372 036 854 775 807 до 9 223 372 036 854 775 807 |
double precision, floats, float |
8 байт |
Вещественные числа, 15 значащих цифр, неограниченный размер (с ограниченной точностью) |
integer, int, int4 |
4 байта Переменный |
Целые числа в интервале от -2 147 483 648 до 2 147 483 647 |
real, float4 |
4 байта |
Вещественные числа, шесть значащих цифр, неограниченный размер (с ограниченной точностью) |
small int, int2 |
2 байта |
Целые числа в интервале от -32 768 до 32 767 |
money |
4 байта |
Вещественные числа с двумя цифрами в дробной части в интервале от -21 |
serial |
4 байта |
Целые числа в интервале от 0 до 2 147 483 647 |
Как видно из табл. 3.13, у некоторых типов данных PostgreSQL имеются синонимы, полностью эквивалентные исходным типам. Синонимы были созданы для удобства, хотя иногда это приводит к недоразумениям, поскольку некоторые синонимы встречаются в других языках программирования. Если не знать, с каким типом ассоциируется тот или иной синоним, возможны случайные ссылки на другие типы данных. Например, в PostgreSQL типы real и doubl e представляют значения, которые во многих языках относятся к типу float; при этом у обоих типов имеются синонимы, имена которых содержат слово «float» (float и flot8 относятся к double precision, float4 относится к real). Если вы попытаетесь использовать синоним f I oat, полагая, что он связан с типом real, возникнут проблемы, поскольку в действительности этот синоним связан с типом double precision.
Тип numeric Тип numeric (также называемый типом decimal) предназначен для представления сколь угодно больших или малых значений с фиксированной точностью, задаваемой пользователем. При создании таблицы с полем типа numeric в круглых скобках указываются два значения: точность и масштаб. Точность определяет максимальное количество цифр (включая цифры в дробной части), а масштаб определяет количество цифр только в дробной части. Если параметры не заданы, по умолчанию точность равна 30, а масштаб — 6. Максимальная точность (а следовательно, и максимальный размер), задаваемая таким образом, равна 1000. На практике 1000 цифр обычно вполне достаточно.
Примечание 1
Примечание 1
Предотвращение ошибок переполнения
booktcwn=# INSERT INTO numbers
VALUES (9.99999999);
INSERT 3390697 1
booktown=# SELECT * FROM numbers:
number
10.000000 (1 row)
booktown=# INSERT INTO
numbers VALUES (9999.99999999);
ERROR: overflow on numeric
AMS(value) >= 10*5 for field with precision
11 scale 6 booktown=# INSERT INTO
numbers VALUES (trunc(99999.99999999.6));
INSERT 3390698 1
booktown=# SELECT * FROM
numbers; number
10.000000 99999.999999 (2 rows)
booktown=# INSERT INTO
numbers VALUES (trunc0.99999999. 6));
INSERT 3390699 1
booktown=# SELECT * FROM numbers;
number
10.000000 99999.999999 9.999999
(3 rows)
Типы даты и времени
Таблица 3.14. Типы даты и времени
Тип данных |
Размер |
Описание |
Интервал |
|
date |
4 байта |
Календарная дата (год, месяц и день) |
От 4713 г. до н. э. до 32 767 г. н. э. |
|
time |
4 байта |
Время суток без часового пояса |
От 00:00:00 до 23:59 |
:59.99 |
time with time zone |
4 байта |
Время суток с часовым поясом |
От 00:00:00+12 до 23:59:59.99-12 |
timestamp with time zone, 8 байт Календарная дата и время От 1903 г. н. э.
timestamp с часовым поясом до 2037 г. н. э.
interval 12 байт Общий промежуток От-1780 000 000 лет
времени до 1 780 000 000 лет
Форматы представления даты
Таблица 3.15. Форматы представления даты
Пример |
Описание |
July 1. 2001 |
Название месяца, день и год |
Sunday July 1. 2001 |
Название дня недели, название месяца, день и год |
July 15. 01 BC |
Название месяца, день и год до нашей эры |
2001-07-01 |
Стандартный формат ISO-8601: год, месяц и день в числовом виде |
20010715 |
ISO-8601: полный год, месяц, день |
010715 |
ISO-8601: год из двух цифр, месяц, день |
7/01/2001 |
Американский формат: месяц, день и год |
1/7/2001 |
Европейский формат: день, месяц и год |
2001.182 |
Числовой формат с полным годом и номером дня в году |
При указании имени месяца в датах следует либо ввести полное название месяца, либо выбрать одно из стандартных сокращений, перечисленных в табл. 3.16.
Сокращенные обозначения месяцев
Таблица 3.16. Сокращенные обозначения месяцев
Месяц |
Сокращение |
Январь |
Jan |
Февраль |
Feb |
Март |
Mar |
Апрель |
Apr |
Май |
May |
Июнь |
Jun |
Июль |
Jul |
Август |
Aug |
Сентябрь |
Sep, Sept |
Октябрь |
Oct |
Ноябрь |
Nov |
Декабрь |
Dec |
В табл. 3.17 приведены аналогичные сокращения для дней недели.
Сокращенные обозначения дней недели
Таблица 3.17. Сокращенные обозначения дней недели
День |
Сокращение |
Воскресенье |
Sun |
Понедельник |
Моп |
Вторник |
Tue, Tues |
Среда |
Wed, Weds |
Четверг |
Thu, Thur, Thurs |
Пятница |
Fri |
Суббота |
Sat |
Несмотря на разнообразие форматов представления даты в PostgreSQL, значения всегда хранятся в одном внутреннем представлении. В вашем распоряжении имеются различные способы настройки формата, в котором PostgreSQL возвращает дату и время.
Примечание 2
Примечание 2
Хотя даты можно форматировать при помощи строковых функций (таких, как to_char()), гораздо эффективнее установить по умолчанию как можно более близкое представление и лишь затем применять ручное преобразование типов и форматирование текста.
Общий формат вывода даты/времени устанавливается применением команды SET к переменной DATESTYLE. Переменной может быть присвоено одно из четырех стандартных значений, перечисленных в табл. 3.18.
Константы форматов даты
Таблица 3.18. Константы форматов даты
Общий формат |
Описание |
Пример |
ISO |
Стандарт ISO-8601 |
2001-06-25 12:24:00-07 |
SQL |
Традиционный формат SQL |
06/25/2001 12:24:00.00 РОТ |
Postgres |
Исходный формат PostgreSQL |
Моп 25 Jun 12:24:00 2001 PDT |
German |
Региональный формат для Германии |
25.06.2001 12:24:00.00 РОТ |
Например, формат SQL устанавливается следующей командой SQL:
booktown=# SET DATESTYLE TO SQL;
SET VARIABLE
Если выполнить запрос SELECT current_timestamp после присваивания, PostgreSQL вернет текущее время в формате SQL:
booktown=# SELECT current_timestamp;
timestamp
08/10/2001 13:25:55.00 PDT (1 row)
Вывод текущего значения переменной DATESTYLE во время работы PostgreSQL производится следующей командой:
booktown=# SHOW DATESTYLE;
NOTICE: DateStyle is SQL
with US (NonEuropean)
conventions SHOW VARIABLE
Помимо общих форматов, представление даты в PostgreSQL зависит от другого фактора: порядка перечисления компонентов (табл. 3.19). Этот порядок перечисления определяет, должен ли в выводимой дате день следовать за месяцем или наоборот. Порядок перечисления компонентов применяется к четырем общим форматам знакомой командой SET DATESTYLE и не изменяет в формате ничего, кроме относительного расположения дня и месяца.
Дополнительные форматы вывода даты
Таблица 3.19. Дополнительные форматы вывода даты
Формат |
Описание |
Пример |
European |
День/месяц/год |
12/07/2001 17:34:50.00 МЕТ |
US. NonEuropean |
Месяц/день/год |
07/12/2001 17:34:50.0 PST |
Более того, общий формат и относительный порядок дня/месяца можно задать в одной команде SET с разделением констант запятыми. Порядок перечисления констант в команде SET не важен, если они не являются взаимоисключающими (например, SQL и ISO). Пример приведен в листинге 3.26.
Taблица 3 20 Форматы представления времени
Taблица 3.20. Форматы представления времени
Пример |
Описание |
01:24 |
ISO-8601 с точностью до минут |
01:24 AM |
Эквивалент 01:24 (суффикс AM используется только для наглядности и не влияет на значение) |
01:24 РМ |
Эквивалент 13:24 (для использования суффикса РМ час должен быть меньше либо равен 12) |
13:24 |
24-часовой формат, эквивалент 01:24 РМ |
01:24:11 |
ISO-8601, с точностью до секунд |
01:24:11.112 |
ISO-8601, с точностью до микросекунд |
012411 |
ISO-8601, с точностью до секунд, числовое форматирование |
В дополнение к этим форматам в PostgreSQL предусмотрена возможность уточнения времени в типах time и time with time zone. Дополнительные форматы перечислены в табл. 3.21.
Допустимые форматы часового пояса
Таблица 3.21. Допустимые форматы часового пояса
Пример |
Описание |
01:24:11-7 |
ISO-8601, GMT + 7 часов |
01:24:11-07:00 |
ISO-8601, GMT + 7 часов 0 минут |
01:24:11-0700 |
ISO-8601, GMT + 7 часов 0 минут |
01:24:11 PST |
ISO-8601, тихоокеанское стандартное время (GMT + 7 часов) |
Примечание 3
Примечание 3
В PostgreSQL поддерживаются все сокращенные обозначения часовых поясов, предусмотренные в стандарте ISO.
Tnntime with time zone поддерживается в PostgreSQL в основном для сохранения совместимости с существующими стандартами SQL и другими СУБД. Если вам потребуется работать с часовыми поясами, рекомендуется использовать тип timestamp, описанный в следующем пункте. Это объясняется прежде всего тем, что из-за действия летнего времени осмысленная интерпретация часовых поясов иногда возможна лишь при наличии даты.
Во внутреннем представлении PostgreSQL вся информация о часовых поясах хранится в виде числового смещения от времени по Гринвичу (GMT), также называемого UTC (Universal Coordinated Time). По умолчанию PostgreSQL выводит время в часовом поясе, заданном в файле конфигурации операционной системы. Если вы хотите, чтобы время выводилось в другом часовом поясе, это можно сделать четырьмя способами.
Настройка переменной среды TZ на сервере. Переменная используется для определения часового пояса по умолчанию при запуске postmaster. Например, она может задаваться в файле .bash_profile пользователя postgres командой export TZ='zone'. Настройка переменной среды PGTZ на стороне клиента. Переменная среды PGTZ может быть прочитана любым клиентом, написанным с использованием библиотеки libpq. Значение интерпретируется как стандартный часовой пояс клиента. Команда SQL SET TIMEZONE ТО. Команда устанавливает для текущего сеанса заданный часовой пояс (например, SET TIMEZONE TO UTC). Секция SQL AT TIME ZONE. Согласно стандарту SQL92 значение секции задается в виде обозначения часового пояса (например, PST) или интервала (например, Interval ( -07:00')). Секция AT TIME ZONE включается в команду SQL после значения времени (например, SELECT my_t1mestamp AT TIME ZONT 'PST'). Примечание 4
Примечание 4
Если переменная часового пояса содержит недопустимое значение, в большинстве систем по умолчанию используется время по Гринвичу (GMT). Кроме того, если при компиляции PostgreSQL был задан ключ USE_AUSTRALIAN_RULES, обозначение EST относится к австралийскому восточному стандартному времени (смещение +10.00 часов по отношению к GMT), а не к восточному стандартному времени США.
Примеры данных типа timestamp
Таблица 3.22. Примеры данных типа timestamp
Пример |
Описание |
1980-06-25 11:11-7 |
Формат даты ISO-8601 с точностью до минут, часовой пояс PST |
25/06/1980 12:24:11.112 |
Европейский формат даты с точностью до микросекунд |
06/25/1980 23:11 |
Американский формат даты с точностью до минут в 24-часовом представлении |
25.06.1980 23:11:12 РМ |
Немецкий региональный формат даты с точностью до микросекунд и суффиксом РМ |
ВНИМАНИЕ
Хотя в PostgreSQL поддерживается синтаксис создания полей или значений типа timestamp without time zone, в PostgreSQL 7.1.2 полученный тип данных все равно содержит информацию о часовом поясе.
Константы даты и времени
Таблица 3.23. Константы даты и времени
Константа |
Описание |
current |
Текущее (отложенное) время обработки транзакции. В отличие от now не привязывается к конкретному моменту времени и возвращает текущее системное время |
epoch infinity |
1970-01-01 00:00:00+00 («день рождения» Unix) |
-infinity |
Абстрактная константа, более «ранняя» по сравнению со всеми допустимыми значениями даты и времени |
now |
Фиксированное время обработки транзакции |
today |
Полночь текущего дня |
tomorrow |
Полночь следующего дня |
yesterday |
Полночь предыдущего дня |
В PostgreSQL также поддерживаются три встроенные функции для получения текущего времени, даты и их комбинации. Для них были выбраны подходящие имена current_date, current_time и current_timestamp.
Если судить только по именам, константы now и current на первый взгляд кажутся идентичными. В действительности они принципиально различаются по способу хранения в таблице. Константа now транслируется в системное время на момент выполнения команды (например, время вставки, если константа используется в команде INSERT). С другой стороны, константа current обычно применяется в особых случаях (например, при отслеживании сведений о процессах) для вычисления разности между начальным временем, зафиксированным константой now, и текущим временем; результат определяет время выполнения процесса. В листинге 3.28 константы now и current используются для построения журнала задач. Первая команда создает таблицу с полями для хранения имени задачи, начальной и конечной даты/времени. В таблице создаются записи двух задач, при этом начальное время задается константой now, а конечное время — константой current. Из листинга видно, что обе задачи не завершены.
ВМИМАНИЕ
Константы даты/времени, как показано в листинге 3.28, обязательно заключаются в апострофы.
Геометрические типы
Таблица 3.24. Геометрические типы
Тип |
Размер |
Описание |
Синтаксис |
point |
16 байт |
Точечный объект, характеризуемый только координатами на плоскости. Координаты А- и /представляются вещественными числами |
(А-./) |
Iseg |
32 байта |
Отрезок прямой. Задается координатами начальной и конечной точек |
((xl.yl). (х2.у2)) |
box |
32 байта |
Прямоугольник. Задается координатами двух углов, расположенных по диагонали |
((xl.yl) лх2.у2)) |
path |
4+32хлбайт |
Замкнутая фигура (аналог многоугольника): множество из п точек, соединенных отрезками |
((xl.yl)....) |
path |
4+32х/?байт |
Разомкнутая фигура (аналог многоугольника): множество из п точек, соединенных отрезками |
[(xl.yl),...] |
polygon |
4+32х/7 байт |
Многоугольник (аналог замкнутой фигуры): п конечных точек отрезков, образующих контур многоугольника |
((xl.yl)....) |
circle |
24 байта |
Круг с центром в точке (х.у) и радиусом г |
<(х,у) .r > |
Системные поля
Таблица 3.25. Системные поля
Поле |
Описание |
old | 4-байтовый уникальный идентификатор объекта записи. В пределах одной таблицы значения end никогда не повторяются |
tableoid |
Идентификатор объекта таблицы, содержащей запись. Имя таблицы связывается с идентификатором в системной таблице рд class |
xmin |
Идентификатор транзакции вставки для кортежа |
cmin |
Идентификатор команды, ассоциированной с транзакцией вставки для кортежа |
xmax |
Идентификатор транзакции удаления для кортежа. Для видимых (не удаленных) кортежей равен нулю |
cmax |
Идентификатор команды, ассоциированной с транзакцией удаления для кортежа. По аналогии с xmax равен нулю для видимых кортежей |
ctid |
Идентификатор, описывающий физическое местонахождение кортежа в базе данных. Поле ctid содержит пару чисел: номер блока и индекс кортежа в блоке |
Таблица authors
Таблица 3.26. Таблица authors
id |
last_name |
first_name |
1809 |
Geisel |
Theodor Seuss |
illl |
Denham |
Ariel |
15990 |
Bourgeois |
Paulette |
2031 |
Brown |
Margaret Wise |
25041 |
Margery Williams |
Bianco |
16 |
Alcoa |
Louisa May |
115 |
Poe |
Edgar Allen |
Таблица subjects
Таблица 3.27. Таблица subjects
id |
subject |
location |
1809 |
Arts |
Creativity St |
1111 |
Children's Books |
Kids Ct |
15990 |
Classics |
Academic Rd |
2031 |
Computers |
Productivity Ave |
25041 |
Drama |
Main St |
16 |
Horror |
Black Raven Dr |
115 |
Science Fiction |
Main St |
Вынесение данных об авторе и теме из таблицы books повышает эффективность хранения данных. Если в таблице имеются данные о нескольких книгах, относящихся к одной теме, то вместо нескольких экземпляров полных данных, связанных с темой, в таблице будут храниться только значения subjected. Кроме того, это упрощает модификацию данных, связанных с темой книги (например, информации о размещении этих книг на полках магазина). Такие данные достаточно один раз изменить в одной небольшой таблице вместо того, чтобы обновлять множество записей в основной базе. Аналогичные рассуждения применимы и к таблице authors, связанной с books по полю authorjd.
Тщательное планирование также помогает избежать ошибок при выборе типов данных. Например, таблица editions связывает коды ISBN с кодами книг, хранящимися в таблице booktown. На первый взгляд кажется, что для представления кодов ISBN можно воспользоваться полем типа integer, однако такое решение было бы ошибочным, поскольку коды ISBN иногда содержат символьные данные. Кроме того, в поле типа integer будут теряться начальные нули (код 0451160916 превратится в 451160916).
Из всего сказанного можно сделать вывод, что проектирование таблиц является важной составляющей процесса проектирования базы данных.
Таблицы
Таблицы
Данный раздел посвящен таблицам — одному из важнейших элементов SQL. Таблицы необходимо знать во всех подробностях, поскольку именно в таблицах хранятся все данные. Хорошее знание логической структуры таблиц является обязательным условием правильного планирования и проектирования структур данных SQL и всех программных функций, обеспечивающих доступ к этим данным.
Таблица состоит из строк (записей) и столбцов (полей), пересечения которых называются элементами данных. В электронных таблицах (например, в Excel) элементам данных соответствуют ячейки таблицы. Столбец определяет имя и тип данных, хранящихся в соответствующем элементе данных записи. Каждая запись (строка таблицы) состоит из элементов данных, описываемых именем и типом соответствующего столбца. Таким образом, каждый элемент данных в записи косвенно связан со всеми остальными элементами этой записи. В определенном смысле поле можно рассматривать как описание отдельного элемента записи, а каждую запись — как совокупность данных, удовлетворяющих этим описаниям.
В табл. 3.1 приведено описание структуры простой таблицы books. Ссылки на эту таблицу будут неоднократно встречаться в дальнейших примерах. В каждой записи таблицы хранится информация об отдельной книге: числовой код книги, название, код автора и код темы. Эти характеристики описываются полями 1 d, ti tl e, authoMd и subjected (слева направо).
Таблицы в PostgreSQL
Таблицы в PostgreSQL
Многие программисты (особенно обладающие опытом работы с другими реляционными СУБД на базе SQL) хорошо знакомы с общими концепциями реляционных баз данных, рассмотренными в этой главе. Тем не менее в разных РСУ БД используются разные механизмы работы с таблицами на системном уровне. В этом разделе более подробно описана реализация таблиц в PostgreSQL.
Тип money
Тип money
Тип money предназначен для хранения денежных величин и обычных чисел. На момент написания книги тип money считается устаревшим и использовать его не рекомендуется. В книге он представлен лишь как один из действующих типов, который все еще может встречаться в существующих системах PostgreSQL.
Вместо типа money следует использовать тип numeric с масштабом 2 и точностью, достаточной для представления максимальной необходимой величины (включая две цифры для дробной части). Форматирование, аналогичное типу money, выполняется при помощи функции to_char(), используемой в листинге 3.23. В этом примере продемонстрирован оператор конкатенации и функция форматирования текста ltrim(), о которых рассказано в главе 4.
Тип serial
Тип serial
Хотя тип serial не относится к числу стандартных типов, он часто используется при создании в таблице полей-идентификаторов, содержащих уникальное значение для каждой записи. В типе serial объединены функциональные возможности 4-байтового типа integer, индекса и последовательности. В листинге 3.24 тип serial генерирует уникальный идентификатор для каждой записи в таблице auto_identified.
В листинге 3.25 та же задача решается при помощи поля типа integer, функции nextval() и последовательности (последовательности описаны в главе7). На момент написания книги эти два способа были функционально тождественными.
Тип timestamp
Тип timestamp
Тип timestamp PostgreSQL сочетает функциональные возможности типов date и time. Формат timestamp состоит из даты, за которой следует минимум один пробел, после чего идет время и необязательный часовой пояс.
В этом формате поддерживаются любые сочетания форматов даты и времени, перечисленные в табл. 3.15 и 3.20. Примеры допустимого ввода в формате timestamp приведены в табл. 3.22.
Типы данных
Типы данных
SQL относится к категории языков с сильной типизацией. Это означает, что с любым объектом данных, представленным в PostgreSQL, связывается определенный тип, даже если на первый взгляд это и не очевидно. Тип данных одновременно определяет и ограничивает разновидности операций, которые могут выполняться с этими данными.
Типы не только ассоциируются со всеми данными, но и играют важную роль при создании таблиц. Как упоминалось в разделе «Знакомство с реляционными базами данных», таблицы состоят из одного или нескольких полей. При создании таблицы каждому полю, помимо имени, назначается определенный тип данных.
Примечание 1
Примечание 1
Хотя в PostgreSQL предусмотрен достаточно широкий спектр встроенных типов данных, вы также можете определять собственные типы данных командой CREATE TYPE. За дополнительной информацией обращайтесь к описанию команды CREATE TYPE.
В табл. 3.10 перечислены базовые типы данных PostgreSQL, а также их синонимы (альтернативные имена). Также существует множество внутренних (то есть не предназначенных для нормального использования) и устаревших типов данных, которые не приводятся в таблице.
Хотя большинство типов данных PostgreSQL взято непосредственно из стандартов SQL, существуют и другие, нестандартные типы данных (например, гео-
метрические и сетевые типы). По этой причине у типов данных PostgreSQL не всегда находятся прямые аналоги в других СУБД на базе SQL.
Вещественные константы
Вещественные константы
Вещественные константы обладают определенным сходством с целочисленными константами, но используются для представления не только целых, но и дробных величин.
Существует несколько форматов представления вещественных констант, представленных в табл. 3.4. Запись ## означает одну или несколько цифр.
Встроенные константы даты и времени
Встроенные константы даты и времени
В PostgreSQL предусмотрено несколько специальных констант, представляющих стандартные значения даты и времени. Эти константы перечислены в табл. 3.23.
команда SQL состоит из отдельных
Выводы
Итак, команда SQL состоит из отдельных лексем, каждая из которых может быть ключевым словом, идентификатором, защищенным идентификатором, константой или специальным символом. В табл. 3.7 структура команд SQL поясняется на примере простой команды SELECT.
Защищенные идентификаторы
Защищенные идентификаторы
Хотя обычно это и не требуется, идентификаторы могут заключаться в кавычки, указывающие на их буквальную интерпретацию. Например, просмотр всех полей таблицы с именем states обычно производится следующей простой командой:
booktown=# SELECT * FROM states;
id | name | abbreviation
---+-------+---------------
33|Oregon|OR
42| Washington | WA
(2 rows)
Команда содержит ключевые слова SELECT и FROM, а также идентификаторы * (обозначение всех полей) и states (имя таблицы). Команда производит выборку всех полей таблицы states и выводит все ее содержимое.
Аналогичного эффекта можно добиться, заключив идентификатор в кавычки:
booktown=# SELECT * FROM "states";
id | name I abbreviation
33 | Oregon | OR 42 | Washington | WA (2 rows)
Как показывает этот пример, применение кавычек к идентификаторам, записанным символами нижнего регистра, ни на что не влияет. Однако попытка защитить идентификатор stAtes в следующей команде приводит к неудаче:
booktown=# SELECT * FROM "stAtes";
ERROR: Relation 'stAtes' does not exist
Дело в том, что команда приказывает PostgreSQL найти таблицу с именем stAtes (вместо states). Другими словами, заключая идентификатор в кавычки, мы требуем, чтобы интерпретатор PostgreSQL интерпретировал его буквально.
Все незащищенные идентификаторы преобразуются к нижнему регистру. Любая смешанная комбинация символов разных регистров (stAtEs, STATES) при отсутствии кавычек перед выполнением команды автоматически приводится к виду states.
Примечание 1
Примечание 1
Преобразование незащищенных идентификаторов к нижнему регистру является отличительной особенностью PostgreSQL. В соответствии со стандартом SQL92 незащищенные идентификаторы должны преобразовываться к верхнему регистру. По историческим причинам, а также для удобства чтения PostgreSQL не выполняет требования данной части стандарта SQL92. Это обстоятельство особенно важно для администраторов баз данных, знакомых с другими продуктами SQL, в которых идентификаторы автоматически преобразуются к верхнему регистру (например, Oracle). Чтобы ваши приложения легко адаптировались для других платформ, помните о проблеме регистра во избежание конфликтов.
Модуль лексического разбора нормально воспринимает команды, записанные в смешанном регистре (при условии правильности их синтаксиса). Тем не менее к выбору регистра символов при записи программ следует относиться внимательно, поскольку смена регистра символов при оформлении программы может как упростить, так и затруднить чтение большого объема кода SQL.
В этой книге идентификаторы записываются символами нижнего регистра, а ключевые слова — верхнего. Визуальное отделение фиксированных системных синтаксических элементов от пользовательских объектов данных значительно упрощает чтение и понимание сложных команд SQL.
Знакомство с реляционными базами данных
Знакомство с реляционными базами данных
PostgreSQL относится к категории объектно-реляционных систем управления базами данных (ОРСУБД). Модель ОРСУБД представляет собой усовершенствование более традиционной модели реляционной системы управления базами данных (РСУБД). В РСУБД логически связанные данные хранятся в двумерных структурах, называемых таблицами. Данные могут состоять из элементов, относящихся к различным стандартным типам — целые и вещественные числа, символы, строки, дата/время. В таблице элементы данных образуют «решетку» из столбцов (полей) и строк (записей). Одной из главных особенностей реляционной модели является ее концептуальная простота, причем это может считаться как ее главным достоинством, так и главным недостатком.
Объектно-реляционная специфика PostgreSQL дополняет традиционную реляционную модель данных многочисленными усовершенствованиями. К их числу относится поддержка массивов (хранения нескольких элементов в одном поле), наследования (связей типа «предок—потомок» между таблицами) и функций (программных методов, вызываемых командами SQL). Для опытных программистов в PostgreSQL даже предусмотрены возможности расширения типов данных и использования процедурных языков.
Вследствие объектно-реляционной ориентации таблицы иногда называются классами, а записи и поля могут соответственно именоваться экземплярами (instances) и атрибутами (attributes). В книге эти термины считаются синонимами. Другие структуры данных SQL (такие, как индексы и представления) иногда называются объектами базы данных.
Примечание 1
Примечание 1
Учтите, что термин «объектно-реляционный» не является синонимом термина «объектно-ориентированный», характерного для многих современных языков программирования. Несмотря на поддержку ряда объектных усовершенствований, PostgreSQL все равно формально является реляционной системой управления базами данных (РСУБД).
Знакомство с SQL
Знакомство с SQL
SQL (Structured Query Language) — мощный, универсальный и проверенный временем язык запросов к реляционным базам данных. История SQL восходит к научным разработкам компании IBM в 70-х годах. В нескольких ближайших разделах вы познакомитесь с историей языка SQL, его предшественниками и различными стандартами SQL, появившимися за эти годы.