ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 19.03.2024
Просмотров: 188
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
СОДЕРЖАНИЕ
Глава 3. Основные операции с таблицами
Контрольные вопросы и задания
1. Попробуйте ввести в таблицу aircrafts строку с таким значением атрибута
«Код самолета» (aircraft_code), которое вы уже вводили, например:
INSERT INTO aircrafts
VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 );
Обратите внимание, что в этой команде мы не привели список атрибутов, что вполне допустимо при задании значений атрибутов в том же порядке, в котором атрибуты следуют в определении таблицы. Но в ваших прикладных программах так поступать все же не следует, поскольку в случае возможной реструктуриза- ции таблицы и изменения порядка следования атрибутов в ней ваши команды
INSERT могут перестать работать корректно.
Вы получите сообщение об ошибке.
ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "aircrafts_pkey"
ПОДРОБНОСТИ: Ключ "(aircraft_code)=(SU9)" уже существует.
Подумайте, почему появилось сообщение. Если вы забыли структуру таблицы aircrafts, то можно вывести ее определение на экран с помощью команды
\d aircrafts
2. Предложение ORDER BY команды SELECT позволяет отсортировать данные при выводе. По умолчанию сортировка выполняется по возрастанию значений ат- рибута, указанного в этом предложении. Но можно упорядочить строки и по убыванию значения атрибута. Для этого нужно после имени атрибута в пред- ложении ORDER BY добавить ключевое слово DESC (это сокращение от слова descendant — убывающий порядок). Самостоятельно напишите команду для вы- борки всех строк из таблицы aircrafts, чтобы строки были упорядочены по убы- ванию значения атрибута «Максимальная дальность полета, км» (range).
3. Команда UPDATE позволяет в процессе обновления выполнять арифметические действия над значениями, находящимися в строках таблицы. Представим себе,
что двигатели самолета Sukhoi SuperJet стали в два раза экономичнее, вслед- ствие чего дальность полета этого лайнера возросла ровно в два раза. Команда
UPDATE позволяет увеличить значение атрибута range в строке, хранящей ин- формацию об этом самолете, даже не выполняя предварительно выборку с це- лью выяснения текущего значения этого атрибута. При присваивании нового
48
Контрольные вопросы и задания
значения атрибуту range можно справа от знака «=» написать не только чис- ловую константу, но и целое выражение. В нашем случае оно будет простым:
range = range * 2. Самостоятельно напишите команду UPDATE полностью, при этом не забудьте, что увеличить дальность полета нужно только у одной моде- ли — Sukhoi SuperJet, поэтому необходимо использовать условие WHERE. Затем с помощью команды SELECT проверьте полученный результат.
4. Если в предложении WHERE команды DELETE вы укажете логически и синтак- сически корректное условие, но строк, удовлетворяющих этому условию, в таб- лице не окажется, то в ответ СУБД выведет сообщение
DELETE 0
Такая ситуация не является ошибкой или сбоем в работе СУБД. Например, ес- ли после удаления какой-то строки вы повторно попытаетесь удалить ее же, то получите именно такое сообщение.
Самостоятельно смоделируйте описанную ситуацию, подобрав условие, кото- рому гарантированно не соответствует ни одна строка в таблице «Самолеты»
(aircrafts).
49
1 2 3 4 5 6 7 8 9 ... 20
Глава 4
Типы данных СУБД PostgreSQL
После первоначального знакомства с языком SQL имеет смысл немного упорядочить полученные вами знания. Речь идет о типах данных, применяемых в СУБД PostgreSQL. Вообще, типы данных —
это одно из базовых понятий любого языка программирования, и язык SQL в этом плане не является исключением.
PostgreSQL имеет очень разнообразный набор встроенных типов данных, т. е. тех типов, которые
СУБД предоставляет в распоряжение пользователя, как говорят, по умолчанию. Мы намеренно упо- требили здесь термин «встроенные», поскольку пользователь имеет возможность создавать и свои собственные типы данных, которые затем можно включить в систему и использовать их так же, как и встроенные. Такая возможность адаптации системы типов данных к конкретным ситуациям является одной из отличительных черт PostgreSQL.
В этой главе мы расскажем лишь о самых основных типах данных, поскольку в вашем распоряже- нии всегда имеется полная документация. Настоящее учебное пособие не является ее заменой, оно призвано лишь помочь вам сделать первые шаги в освоении богатого мира типов данных PostgreSQL.
Типы данных объединены в группы, в рамках этих групп они имеют некоторые общие свойства, но также они имеют и различия.
4.1. Числовые типы
Группа числовых типов данных включает в себя целый ряд разновидностей: цело- численные типы, числа фиксированной точности, типы данных с плавающей точкой,
последовательные типы (serial).
В составе целочисленных типов находятся следующие представители: smallint,
integer, bigint. Если атрибут таблицы имеет один из этих типов, то он позволяет хранить только целочисленные данные. При этом перечисленные типы различают- ся по количеству байтов, выделяемых для хранения данных. В PostgreSQL существу- ют псевдонимы для этих стандартизированных имен типов, а именно: int2, int4 и int8. Число байтов отражается в имени типа.
51
Глава 4. Типы данных СУБД PostgreSQL
При выборе конкретного целочисленного типа принимают во внимание диапазон допустимых значений и затраты памяти. Зачастую тип integer считается оптималь- ным выбором с точки зрения достижения компромисса между этими показателями.
Числа фиксированной точности представлены двумя типами — numeric и decimal.
Однако они являются идентичными по своим возможностям. Поэтому мы будем про- водить изложение на примере типа numeric. Для задания значения этого типа ис- пользуются два базовых понятия: масштаб (scale) и точность (precision). Масштаб показывает число значащих цифр, стоящих справа от десятичной точки (запятой).
Точность указывает общее число цифр как до десятичной точки, так и после нее. На- пример, у числа 12.3456 точность составляет 6 цифр, а масштаб — 4 цифры.
Параметры этого типа данных указываются в круглых скобках после имени типа:
numeric(точность, масштаб). Например, numeric(6, 2).
Его главное достоинство — это обеспечение точных результатов при выполнении вы- числений, когда это, конечно, возможно в принципе. Это оказывается возможным при выполнении сложения, вычитания и умножения. Числа типа numeric могут хра- нить очень большое количество цифр: 131 072 цифры — до десятичной точки (запя- той), 16 383 — после точки. Однако нужно учитывать, что такая точность достигается за счет замедления вычислений по сравнению с целочисленными типами и типами с плавающей точкой. При этом для хранения числа затрачивается больше памяти, чем в случае целых чисел.
Данный тип следует выбирать для хранения денежных сумм, а также в других случа- ях, когда требуется гарантировать точность вычислений.
Представителями типов данных с плавающей точкой являются типы real и double precision. Они представляют собой реализацию стандарта IEEE «Standard 754 for
Binary Floating-Point Arithmetic». Тип данных real может представить числа в диа- пазоне, как минимум, от 1E−37 до 1E+37 с точностью не меньше 6 десятичных цифр.
Тип double precision имеет диапазон значений примерно от 1E−307 до 1E+308
с точностью не меньше 15 десятичных цифр.
При попытке записать в такой столбец слишком большое или слишком маленькое значение будет генерироваться ошибка. Если точность вводимого числа выше допу- стимой, то будет иметь место округление значения. А вот при вводе очень маленьких чисел, которые невозможно представить значениями, отличными от нуля, будет ге- нерироваться ошибка потери значимости, или исчезновения значащих разрядов (an underflow error).
52
4.1. Числовые типы
При работе с числами таких типов нужно помнить, что сравнение двух чисел с пла- вающей точкой на предмет равенства их значений может привести к неожиданным результатам.
Например:
SELECT 0.1::real * 10 = 1.0::real;
?column?
---------- f
(1 строка)
В дополнение к обычным числам эти типы данных поддерживают и специальные значения Infinity (бесконечность), −Infinity (отрицательная бесконечность) и NaN (не число).
PostgreSQL поддерживает также тип данных float, определенный в стандарте SQL.
В объявлении типа может использоваться параметр: float(p). Если его значение лежит в диапазоне от 1 до 24, то это будет равносильно использованию типа real,
а если же значение лежит в диапазоне от 25 до 53, то это будет равносильно исполь- зованию типа double precision. Если же при объявлении типа параметр не исполь- зуется, то это также будет равносильно использованию типа double precision.
Последним из числовых типов является тип serial. Однако он фактически реализо- ван не как настоящий тип, а просто как удобная замена целой группы SQL-команд.
Тип serial удобен в тех случаях, когда требуется в какой-либо столбец вставлять уникальные целые значения, например, значения суррогатного первичного ключа.
Синтаксис для создания столбца типа serial таков:
CREATE TABLE имя-таблицы ( имя-столбца serial );
Эта команда эквивалентна следующей группе команд:
CREATE SEQUENCE имя-таблицы_имя-столбца_seq;
CREATE TABLE имя-таблицы
(
имя-столбца integer NOT NULL
DEFAULT nextval( 'имя-таблицы_имя-столбца_seq' )
);
ALTER SEQUENCE имя-таблицы_имя-столбца_seq
OWNED BY имя-таблицы.имя-столбца;
53
Глава 4. Типы данных СУБД PostgreSQL
Для пояснения вышеприведенных команд нам придется немного забежать впе- ред. Одним из видов объектов в базе данных являются так называемые последова- тельности. Это, по сути, генераторы уникальных целых чисел. Для работы с этими последовательностями-генераторами используются специальные функции. Одна из них — это функция nextval, которая как раз и получает очередное число из последо- вательности, имя которой указано в качестве параметра функции. В команде CREATE
TABLE ключевое слово DEFAULT предписывает, чтобы СУБД использовала в качестве значения по умолчанию то значение, которое формирует функция nextval. Поэтому если в команде вставки строки в таблицу INSERT INTO не будет передано значение для поля типа serial, то СУБД обратится к услугам этой функции. В том случае, ко- гда в таблице поле типа serial является суррогатным первичным ключом, тогда нет необходимости указывать явное значение для вставки в это поле.
В заключение скажем, что кроме типа serial существуют еще два аналогичных типа: bigserial и smallserial. Им фактически, за кадром, соответствуют типы bigint и smallint. Поэтому при выборе конкретного последовательного типа нуж- но учитывать предполагаемое число строк в таблице и частоту удаления и вставки строк, поскольку даже для небольшой таблицы может потребоваться большой диа- пазон, если операции удаления и вставки строк выполняются часто.
4.2. Символьные (строковые) типы
Стандартные представители строковых типов — это типы character varying(n)
и character(n), где параметр указывает максимальное число символов в строке,
которую можно сохранить в столбце такого типа. При работе с многобайтовыми ко- дировками символов, например UTF-8, нужно учитывать, что речь идет о символах,
а не о байтах. Если сохраняемая строка символов будет короче, чем указано в опреде- лении типа, то значение типа character будет дополнено пробелами до требуемой длины, а значение типа character varying будет сохранено так, как есть.
Типы character varying(n) и character(n) имеют псевдонимы varchar(n) и char(n) соответственно. На практике, как правило, используют именно эти краткие псевдонимы.
PostgreSQL дополнительно предлагает еще один символьный тип — text. В столбец этого типа можно ввести сколь угодно большое значение, конечно, в пределах, уста- новленных при компиляции исходных текстов СУБД.
54
4.2. Символьные (строковые) типы
Документация рекомендует использовать типы text и varchar, поскольку такое отличительное свойство типа character, как дополнение значений пробелами, на практике почти не востребовано. В PostgreSQL обычно используется тип text.
Константы символьных типов в SQL-командах заключаются в одинарные кавычки.
SELECT 'PostgreSQL';
?column?
------------
PostgreSQL
(1 строка)
В том случае, когда в константе содержится символ одинарной кавычки или обратной косой черты, их необходимо удваивать.
Например:
SELECT 'PGDAY''17';
?column?
----------
PGDAY'17
(1 строка)
В том случае, когда таких символов в константе много, все выражение становится трудно воспринимать. На помощь может прийти использование удвоенного символа
«$». Эти символы выполняют ту же роль, что и одинарные кавычки, когда в них за- ключается строковая константа. При использовании символов «$» в качестве ограни- чителей уже не нужно удваивать никакие символы, содержащиеся в самой константе:
ни одинарные кавычки, ни символы обратной косой черты.
Например:
SELECT $$PGDAY'17$$;
?column?
----------
PGDAY'17
(1 строка)
Возможность использования символов доллара в роли ограничителей символь- ной константы не является частью стандарта SQL. Это расширение, предлагаемое
PostgreSQL. Подробно об этом написано в разделе документации 4.1.2.4 «Строковые константы, заключенные в доллары».
55
Глава 4. Типы данных СУБД PostgreSQL
PostgreSQL предлагает еще одно расширение стандарта SQL — строковые константы в стиле языка C. Чтобы иметь возможность их использовать, нужно перед начальной одинарной кавычкой написать символ «E».
Например, для включения в константу символа новой строки нужно сделать так:
SELECT E'PGDAY\n17';
?column?
----------
PGDAY
+
17
(1 строка)
При использовании C-стиля необходимо удваивать обратную косую черту, если тре- буется поместить ее в константу буквально. А для включения в содержимое констан- ты символа обратной кавычки можно либо удвоить ее, либо сделать так:
SELECT E'PGDAY\'17';
?column?
----------
PGDAY'17
(1 строка)
Подробно об использовании C-стиля написано в разделе документации 4.1.2.2 «Стро- ковые константы со спецпоследовательностями в стиле C».
4.3. Типы «дата/время»
PostgreSQL поддерживает все типы данных, предусмотренные стандартом SQL для даты и времени. Даты обрабатываются в соответствии с григорианским календарем,
причем это делается даже в тех случаях, когда дата относится к тому моменту време- ни, когда этот календарь в данной стране еще не был принят. Для этих типов данных предусмотрены определенные форматы для ввода значений и для вывода. Причем эти форматы могут не совпадать. Важно помнить, что при вводе значений их нужно заключать в одинарные кавычки, как и текстовые строки.
Начнем рассмотрение с типа date. Рекомендуемый стандартом ISO 8601 формат вво- да дат таков: «yyyy-mm-dd», где символы «y», «m» и «d» обозначают цифру года, ме- сяца и дня соответственно. PostgreSQL позволяет использовать и другие форматы
56