Самоучитель по SQL-сервер в Linux



Типы данных

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

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

ПРИМЕЧАНИЕ

Хотя в PostgreSQL предусмотрен достаточно широкий спектр встроенных типов данных, вы также можете определять собственные типы данных командой CREATE TYPE. За дополнительной информацией обращайтесь к описанию команды CREATE TYPE.

В табл. 3.10 перечислены базовые типы данных PostgreSQL, а также их синонимы (альтернативные имена). Также существует множество внутренних (то есть не предназначенных для нормального использования) и устаревших типов данных, которые не приводятся в таблице.

Хотя большинство типов данных PostgreSQL взято непосредственно из стандартов SQL, существуют и другие, нестандартные типы данных (например, гео-

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

Таблица 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),
decimal (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.

Псевдозначение 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

Листинг 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 с датой публикации книги.

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

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

Логическим значением называется простая структура данных, представляющая одну из двух величин: 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, интерпретируются сервером одинаково.

Таблица 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.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.

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

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

booktown=# UPDATE dailyjnventory

SET in_stock = Т WHERE in_stock IS NULL;

UPDATE 1


Книжный магазин