ОСНОВЫSQL
Что такое SQL
SQL (Structured Query Language) — это структурированный язык запросов к реляционным базам данных. На этом языке можно формулировать выражения (запросы), которые извлекают требуемые данные, модифицируют их, создают таблицы и изменяют их структуры, определяют права доступа к данным и многое другое.
Запросы выполняются системой управления базой данных (СУБД). Если вы не являетесь специалистом по разработке и администрированию баз данных, то вполне можете быть их пользователем, который просматривает или/и изменяет данные в уже имеющихся таблицах. Во многих случаях эти и другие операции с базой данных выполняются с помощью специальных приложений, предоставляющих пользователю удобный интерфейс. Обычно приложения пишутся на специальных языках программирования (С, Pascal, Visual Basic и т. п.) и чаще всего создаются с помощью интегрированных сред разработки, например, Delphi, C++ Builder и др. Однако доступ к базе данных можно получить и без них — с помощью только SQL. Следует заметить также, что и специализированные приложения обычно используют SQL-фрагменты кода при обращениях к базе данных.
Таким образом, SQL — широко распространенный стандартный язык работы с реляционными базами данных. Синтаксис этого языка достаточно прост, чтобы его могли использовать рядовые пользователи, а не только программисты. В настоящее время обычный пользователь компьютера должен владеть, по крайней мере, текстовым редактором (например, Microsoft Word) и электронными таблицами (например, Microsoft Excel). Неплохо, если он также умеет пользоваться базами данных. Различных СУБД существует много, а универсальное средство работы с базами данных одно — SQL. Знание SQL, хотя бы его основ, и умение его применять для поиска и анализа данных является фундаментальной частью компьютерной грамотности даже рядовых пользователей.
Первые разработки систем управления реляционными базами данных (реляционных СУБД) были выполнены в компании IBM в начале 1970-х годов. Тогда же был создан язык данных, предназначенный для работы в этих системах. Экспериментальная версия этого языка называлась SEQUEL — от англ. Structured English QUEry Language (структурированный английский язык запросов). Однако официальная версия была названа короче — SQL (Structured Query Language). Точнее говоря, SQL — это подъязык данных, поскольку СУБД содержит и другие языковые средства.
В 1981 году IBM выпускает реляционную СУБД SQL/DS. К этому времени компания Relation Software Inc. (сегодня это Oracle Corporation) уже выпустила свою реляционную СУБД. Эти продукты сразу же стали стандартом систем, предназначенных для управления базами данных. В состав этих продуктов вошел и SQL, который фактически стал стандартом для подъязыков данных. Производители других СУБД выпустили свои версии SQL. В них имелись не только основные возможности продуктов IBM. Чтобы получить некоторое преимущество для "своей" СУБД, производители вводили некоторые расширения SQL. Вместе с тем, начались работы по созданию общепризнанного стандарта SQL.
В 1986 году Американский национальный институт стандартов (American National Standards Institute, ANSI) выпустил официальный стандарт SQL-86, который в 1989 году был обновлен и получил новое название SQL-89. В 1992 году этот стандарт был назван SQL-92 (ISO/IEC 9075:1992). Последней версией стандарта SQL является SQL:2003 (ISO/IEC 9075X:2003).
Любая реализация SQL в конкретной СУБД несколько отличается от стандарта, соответствие которому объявлено производителем. Так, многие СУБД (например, Microsoft Access 2003, PostgreSQL 7.3) поддерживают SQL-92 не в полной мере, а лишь с некоторым уровнем соответствия. Кроме того, они поддерживают и элементы, которые не входят в стандарт. Однако разработчики СУБД стремятся к тому, чтобы новые версии их продуктов как можно в большей степени соответствовали стандарту SQL.
Внимание. В данном пособии описаны элементы SQL2003, не все из которых поддерживаются существующими СУБД. Прежде чем применять их на практике, следует убедиться, что они будут работать в вашей СУБД. Об этом можно узнать из технической документации. Большинство описанных элементов соответствуют и более ранним версиям SQL, в частности, широко распространенному SQL-92.
SQL задумывался как простой язык запросов к реляционной базе данных, близкий к естественному (точнее, к английскому) языку. Предполагалось, что близость по форме к естественному языку сделает SQL средством, доступным для широкого применения обычными пользователями баз данных, а не только программистами. Первоначально SQL не содержал никаких управляющих структур, свойственных обычным языкам программирования. Запросы, синтаксис которых довольно прост, вводились прямо с консоли последовательно один за другим и в этой же последовательности выполнялись. Однако SQL так и не стал инструментом банковских служащих, продавцов авиа- и железнодорожных билетов, экономистов и других служащих различных фирм, использующих информацию, хранимую в базах данных. Для них простой SQL оказался слишком сложным и неудобным, несмотря на свою близость к естественному языку вопросов.
На практике с базой данных обычно работают посредством приложений, написанных программистами на процедурных языках, например, на С, Visual Basic, Pascal, Java и др. Часто приложения создаются в специальных средах визуальной разработки, таких как Delphi, Microsoft Access, Visual dBase и т. п. При этом разработчику приложения практически не приходится писать коды программ, поскольку за него это делает система разработки. Во всяком случае, работа с программным кодом оказывается минимальной. Эти приложения имеют удобный графический интерфейс, не вынуждающий пользователя непосредственно вводить запросы на языке SQL. Вместо него это делает приложение. Впрочем, приложение может как использовать, так и не использовать SQL для обращения к базе данных. SQL не единственное, хотя и очень эффективное средство получения, добавления и изменения данных, и если есть возможность использовать его в приложении, то это следует делать.
Реляционные базы данных могут существовать и действительно существуют вне зависимости от приложений, обеспечивающих пользовательский интерфейс. Если по каким-либо причинам такого интерфейса нет, то доступ к базе данных можно осуществить с помощью SQL, используя консоль или какое-нибудь приложение, с помощью которого можно соединиться с базой данных, ввести и отправить SQL-запрос (например, Borland SQL Explorer).
Язык SQL считают декларативным (описательным) языком, в отличие от языков, на которых пишутся программы. Это означает, что выражения на языке SQL описывают, что требуется сделать, а не каким образом.
Например, для того чтобы выбрать из таблицы сотрудники сведения о фамилиях и должностях сотрудников 102 отдела, достаточно выполнить следующий запрос:
SELECT Фамилия, Должность FROM Сотрудники WHERE Отдел=102;
По-русски данное выражение звучит так:
ВЫБРАТЬ Фамилия, Должность ИЗ Сотрудники ПРИ УСЛОВИИ, ЧТО Отдел = 102;
Чтобы изменить значение " Иванов " на " Петров " столбца Фамилия, достаточно выполнить следующий запрос:
UPDATE Сотрудники SET Фамилия = 'Петров' WHERE Фамилия = 'Иванов';
По-русски данное выражение выглядит так:
ОБНОВИТЬ Сотрудники УСТАНОВИВ Фамилия РАВНЫМ ' Петров ' ГДЕ Фамилия = 'Иванов';
Вам не нужно подробно описывать действия, которые должна выполнить СУБД, чтобы выбрать из таблицы указанные в запросе данные. Вы просто описываете, что желаете получить. В результате выполнения запроса СУБД возвращает таблицу, содержащую запрошенные вами данные. Если в базе данных не оказалось данных, соответствующих запросу, то будет возвращена пустая таблица.
Однако последние версии SQL поддерживают операторы управления вычислениями, свойственные процедурным языкам управления (операторы условного перехода и цикла). Поэтому SQL сейчас это не чисто декларативный язык.
Кроме выборки, добавления, изменения и удаления данных из таблиц, SQL позволяет выполнять все необходимые действия по созданию, модификации и обеспечению безопасности баз данных. Все эти возможности распределены между тремя компонентами SQL:
· DML (Data Manipulation Language — язык манипулирования данными) предназначен для поддержки базы данных: выбора (SELECT), добавления (INSERT), изменения (UPDATE) и удаления (DELETE) данных из таблиц. Эти операторы (команды) могут содержать выражения, в том числе и вычисляемые, а также подзапросы — запросы, содержащиеся внутри другого запроса. В общем случае выражение запроса может быть настолько сложным, что сразу и не скажешь, что он делает. Однако сложный запрос можно мысленно разбить на части, которые легче анализировать. Аналогично, сложные запросы создаются из относительно простых для понимания выражений (подзапросов).
· DDL (Data Definition Language — язык определения данных) предназначен для создания, модификации и удаления таблиц и всей базы данных. Примерами операторов, входящих в DDL, являются CREATE TABLE (создать Таблицу)," CREATE VIEW (создать представление), CREATE SHEMA (создать схему), ALTER TABLE (изменить таблицу), DROP (удалить) и др.
· DCL (Data Control Language — язык управления данными) предназначен для обеспечения защиты базы данных от различного рода повреждений. СУБД предусматривает некоторую защиту данных автоматически. Однако в ряде случаев следует предусмотреть дополнительные меры, предоставляемые DCL.
Внимание
Ключевые слова в выражениях на языке SQL могут записываться как прописными, так и строчными буквами, в одну или несколько строк. В конце выражения должна стоять точка с запятой (;). Однако во многих системах, обеспечивающих ввод, редактирование и отправку на выполнение SQL-выражений, в случае ввода одного такого выражения допускается не указывать признак окончания (;). Если же требуется выполнить блок из нескольких SQL-выражений, то они обязательно должны быть разделены точкой с запятой.
Все ключевые слова SQL (команды, операторы и проч.) являются зарезервированными и не должны использоваться в качестве имен таблиц, столбцов, переменных и т. п. Вот пример неправильного использования ключевых слов:
SELECT SELECT FROM WHERE WHERE SELECT=UPDATE;
Типы данных
Во всех языках программирования, а также в SQL важное место занимают поддерживаемые типы данных. Данные, которые хранятся в памяти компьютера и подвергаются обработке, можно отнести к различным типам. Понятие типа данных возникает естественным образом, когда необходимо применить к ним операции обработки. Например, операция умножения применяется к числам, т. е. к данным числового типа. А что получится, если умножить слово "Вася" на число 25? Поскольку трудно дать вразумительный ответ на этот вопрос, то напрашивается вывод: некоторые операции не следует применять к разнотипным данным. Мы также не знаем, что должно получиться в результате умножения слов "Саша" и "Маша", поэтому заключаем, что определенные операции вообще не применимы к данным некоторых типов.
С другой стороны, существуют операции, результат которых зависит от типа данных. Например, операция сложения, обозначаемая символом (+), может применяться и к двум числам, и к двум строкам, состоящим из произвольных слов. В первом случае результатом применения этой операции будет некоторое число, а во втором — строка, получающаяся путем приписывания второй строки к концу первой. В случае строк операцию сложения еще называют склейкой или конкатенацией. Операции, применимые к различным типам данных, но обозначаемые одним и тем же символом, называют перегруженными. Так, операция, обозначаемая символом (+), является перегруженной: применительно к числам она выполняет арифметическое сложение, а применительно к строкам символов — склейку.
Типы данных столбцов таблиц базы данных могут отличаться от типов данных, поддерживаемых SQL. Это обстоятельство необходимо учитывать при составлении SQL-запросов. Так, например, если в таблице Сотрудники столбец Зарплата определен по каким-либо причинам как символьный, то следующий запрос приведет к ошибке из-за несоответствия типов сравниваемых данных:
SELECT Фамилия FROM Сотрудники WHERE Зарплата > 25000;
Данный запрос пытается получить список всех сотрудников, у которых зарплата больше 25 000. Однако в таблице сотрудники столбец Зарплата содержит символьные, а не числовые данные. В условии запроса (оператор WHERE) сравниваются символьное и числовое значения. На практике данная проблема легко преодолима с помощью функции CAST () приведения значения к требуемому типу. Кроме того, между некоторыми типами столбцов таблицы и данных, участвующих в SQL-выражениях, существуют соответствия, допускающие их совместное использование. Такие типы называют согласованными.
Различные СУБД поддерживают несколько отличающиеся наборы типов данных для столбцов таблиц базы данных. Аналогичная ситуация и с типами данных, поддерживаемых различными версиями и реализациями SQL. Вместе с тем, всегда имеются типы данных, которые поддерживаются всеми реализациями SQL.
В спецификации SQL:2003 признаны пять предопределенных общих типов, внутри которых могут быть подтипы:
• строковый (символьный):
v CHARACTER ( илиCHAR);
v CHARACTER VARYING ( илиVARCHAR);
v CHARACTER LARGE OBJECT ( илиCLOB);
• числовой:
v точные числовые типы:
1. INTEGER;
2. SMALLINT;
3. BIGINT;
4. NUMERIC;
5. DECIMAL;
v приблизительные числовые типы:
1. REAL;
2. DOUBLE PRECISION;
3. FLOAT;
• логический (булевский) — BOOLEAN;
• даты-времени:
v DATE;
v TIME WITHOUT TIME ZONE;
v TIME WITH TIME ZONE;
v TIMESTAMP WITHOUT TIME ZONE;
v TIMESTAMP WITH TIME ZONE;
• интервальный.
Кроме того, существуют особые типы: ROW (запись), ARRAY (массив) и MULTISET (мультимножество).
СТРОКИ
Строковые данные (последовательности символов) имеют три главных строковых типа. Для столбца таблицы можно указать тип CHARACTER (n) или CHAR (n) (строка фиксированной длины), где n — максимальное количество символов, содержащихся в строке. Если (n) не указано, то предполагается, что строка состоит из одного символа. Если в столбец типа CHARACTER (n)вводится m < nсимволов, то оставшиеся позиции заполняются пробелами.
Тип данных CHARACTER VARYING (n) или VARCHAR (n) ( строка переменной длины) применяется тогда, когда вводимые данные имеют различную длину и нежелательно дополнять их пробелами. При этом сохраняется только то количество символов, которое ввел пользователь. В данном случае указание максимального количества символов обязательно (в отличие от CHARACTER). Данные типов CHARACTERи CHARACTER VARYING могут участвовать в одних и тех же строковых операциях.
Тип данных CHARACTER LARGE OBJECT (CLOB - большой символьный объект) используется для представления очень больших символьных строк (например, статей, книг и т. п.). В некоторых СУБД данный тип называется MEMO, а в других — TEXT. С данными этого типа можно выполнять не все операции, предусмотренныедля типовCHARACTER и CHARACTER VARYING. Так,их нельзяиспользовать в операциях сравнения, за исключением равенства и неравенства. Кроме того, столбцы этого типа не могут быть первичными и внешними ключами, а также быть объявлены как имеющие уникальные значения. Иначе говоря, при создании таблиц с помощью оператора CREATE и объявлении столбцов типа СLOBнельзя использовать ключевые слова PRIMARY KEY, FOREIGN, KEY и UNIQUE.
В следующем примере создается таблица с обычным символьным столбцом и столбцом типа СLOB, значения которого могут содержать 100 000 символов:
CREATE TABLE myTable
(
FIELD1 CHARACTER (60),
FIELD2 CLOB (100000)
);
Здесь оператор CREATE TABLE создает таблицу с именем myTable, которая состоит из двух столбцов с именами FIELD1 и FIELD2, типы столбцов указаны рядом с их именами.
Различные языки используют различные наборы символов. Даже английский и немецкий наборы отличаются, не говоря уж о русском и китайском. Система может быть настроена на некоторый набор символов, принимаемый по умолчанию. Однако при этом можно использовать и другие национальные символьные наборы. Так, в следующем примере создается таблица, в которой столбец FIELD1 объявляется как строковый с набором символов, принятым по умолчанию, а столбец FIELD2 — как строковый с греческим набором символов:
CREATE TABLE myTable
(
FIELDI CHARACTER (60),
FIELD2 CHARACTER VARYING (80) CHARACTER SET GREEK
);
Значения строкового типа в SQL-выражениях заключаются в одинарные кавычки. Например, 'Иванов Иван Иванович', '12345 рублей', 'тел. (812) 123-4567'. Пустая строка не содержит ни одного символа и имеет вид: ' '. Строка, содержащая один или более пробелов, не является пустой.
Иногда бывает так, что строковый столбец в таблице содержит только числа (точнее, строки, содержащие цифры, знаки числа и разделительные точки). Чтобы использовать такие данные в операциях с числами, необходимо привести данные одного типа к другому типу с помощью функции CAST () .
Значения типа CHARACTER и CHARACTER VARYING (VARCHAR) совместимы в том смысле, что они могут участвовать как операнды в строковых операциях и операциях сравнения.
ЧИСЛА
Числовой тип данных может быть двух видов — точный и приблизительный. Точные числовые типы позволяют точно выразить значение числа. Некоторые величины имеют очень большой диапазон значений, и в таких случаях достаточно ограничиться некоторым приближенным их представлением с учетом технических возможностей компьютера (размеров регистра).
К точным числовым данным относятся следующие пять типов:
· INTEGER — целое (без дробной части) число. Количество разрядов (точность) зависит от реализации SQL. В некоторых реализациях числа этого типа лежат в диапазоне от -2 147 483 648 до 2 147 483 647 (четырехбайтное целое число);
· SMALLINT — малое целое число. Количество разрядов зависит от реализации SQL, но не больше количества разрядов INTEGER в этой же реализации. В некоторых реализациях числа этого типа лежат в диапазоне от -32 768 до 32 767 (двухбайтное целое число);
· BIGINT — большое целое число. Количество разрядов зависит от реализации SQL и превышает количество разрядов числа типа INTEGER;
· NUMERIC (х, у) — число, в котором всего х разрядов (точность), из которых у разрядов (масштаб) отводится для дробной части. Если у не указано (NUMERIC (x)), то для дробной части отводится количество разрядов, установленное в системе по умолчанию. Если не указаны ни х, ни у (NUMERIC), то принимаются обе эти величины, установленные по умолчанию. Например, если указан тип NUMERC (6, 2), то максимальное значение числа равно 9999.99;
· DECIMAL (х, у) — десятичное число, в котором всего х разрядов, из которых у разрядов отводятся для дробной части. Если х или/и у не указаны, то принимаются значения по умолчанию. Этот тип очень похож на NUMERIC. Отличие состоит в том, что если в DECIMAL (x, у) указанные х и у меньше, чем допустимые реализацией SQL, то будут использоваться последние. Если х и у не указаны, то применяется система умолчаний. Например, вы задали для столбца тип DECIMAL (6, 2). Если реализация SQL позволяет, то в этот столбец можно ввести числа, превышающие 9999.99. В отличие от DECIMAL (x, у), тип NUMERIC (x, у) жестко задает диапазон возможных значений числовой величины.
К приблизительным числовым типам относятся следующие три типа:
· REAL — вещественное число одинарной точности с плавающей разделительной точкой (эта точка "плавает", появляясь в различных местах числа). Например, 5.25, 5.257, 5.2573. Точность представления числа зависит от реализации SQL и оборудования. Например, 32-битовый компьютер дает большую точность, чем 16-битовый;
· DOUBLE PRECISION — вещественное число двойной точности с плавающей разделительной точкой. Точность представления числа зависит от реализации SQL и оборудования. Применяется для представления научных данных (например, результатов измерений) в широком диапазоне значений, т. е. как очень малых (близких к 0), так и очень больших;
· FLOAT (x) — вещественное число с плавающей разделительной точкой и минимальной точностью х, занимающее не более 8 байтов. Если компьютер может поддержать указанную точность, используя аппаратную одинарную точность, то система будет использовать арифметику одинарной точности. Если указанная точность требует арифметики с двойной точностью, то система будет использовать ее. Данный тип следует применять, если предполагается возможность переноса базы данных на другую аппаратную платформу, отличающуюся размерами регистров. Пример значения типа FLOAT: 5.318Е-24 (т. е. 5.318, умноженное на 10 в степени -24). Такую же форму представления имеют и числа типа REAL и DOUBLE PRECISION.
При создании таблиц целочисленные типы применяются для столбцов, содержащих разного рода идентификаторы, например, номера (коды) клиентов, товаров, заказов и т. п. Разумеется, если содержимое столбца должно быть целым числом (например, количество ящиков, бутылок, штук и т. п.), то тип этого столбца естественно определить как INTEGER, SMALLINT или BIGINT.
Допустим, в таблице Клиенты имеется столбец ID_клиента, содержащий уникальные идентификаторы клиентов. Если количество клиентов не превышает 32 000, то тип столбца можно определить как SMALLINT. Если в вашей таблице будут храниться сведения о сотнях тысяч клиентов, то тип столбца ID_клиента следует определить как INTEGER.
Если столбец в проектируемой таблице должен содержать числа с дробной частью, то для него можно задать какой-нибудь нецелочисленный тип. Если вы не уверены, что применить: точные числовые типы или приблизительные, выбирайте точные (NUMERIC, DECIMAL). Они требуют меньше ресурсов и дают точные результаты. Если в столбце предполагается хранить данные из очень широкого диапазона (и очень малые, и очень большие числа), то используйте приблизительные типы данных (FLOAT, REAL).
Обратите внимание, что строка, содержащая число (например, '12345.47'), является данным строкового, а не числового типа. Чтобы в SQL-выражениях сравнивать строковые и числовые данные, необходимо их привести к одному типу с помощью функции CAST ().
ЛОГИЧЕСКИЕ ДАННЫЕ
В этой части математической логики, основоположником которой был английский математик Джон Буль, данные имеют только два значения — ИСТИНА и ЛОЖЬ, обозначаемые как trueи false соответственно. Данные логического типа получаются в результате операций сравнения. Например, результатом вычисления выражения 3 < 5 является ИСТИНА, а выражения 2 + 3 = 10 —ЛОЖЬ.
В SQL тип данных BOOLEAN (булевский) имеет три значения — TRUE, FALSE и UNKNOWN. Значение unknown (неизвестное) было введено для обозначения результата, получающегося при сравнении со значением NULL (неопределенное). Если пользователь еще не ввел в ячейку таблицы никакого значения, то эта "пустая" ячейка содержит значение NULL, интерпретируемое как неизвестное или неопределенное значение.
Результатом любой операции сравнения true или false с NULL или с unknown всегда является unknown.
В SQL-выражениях логические значения заключаются в кавычки, например, ' TRUE ' или ' true'.
ДАТА И ВРЕМЯ
Тип DATA (дата) предназначен для хранения значений даты, элементы которых расположены в следующем порядке: год (4 цифры), дефис (-), месяц (2 цифры), дефис, день (2 цифры). Таким образом, значения даты занимают 10 позиций, например, 2005-10-02.
Данные этого типа могут содержать любую дату с 0001 года по 9999 год.
Для представления времени предусмотрены два типа:
· TIME WITHOUT TIME ZONE (время без часового пояса) предназначен для хранения значений времени, элементы которых расположены в следующем порядке: часы, двоеточие, минуты, двоеточие, секунды. Часы и минуты представляются двумя цифрами, а секунды могут быть представлены двумя и более цифрами (если требуется дробная часть), например 18:35:19.547. Длина дробной части секунд зависит от реализации, но внутреннее представление времени должно иметь не менее 6 цифр. По умолчанию время данного типа представляют без дробной части секунд. Чтобы указать, что время должно быть представлено с n цифрами после разделительной точки, достаточно использовать такой синтаксис: TIME WITHOUT TIME ZONE (n). Например, чтобы кроме секунд указывались еще и миллисекунды, следует определить тип как TIME WITHOUT TIME ZONE (3). Длина данных рассматриваемого типа без дробной части равна 8 символам, а с дробной частью — 9 плюс количество цифр после разделительной точки. Для задания времени без указания часового пояса с использованием установок по умолчанию можно использовать короткий синтаксис — TIME;
· TIME WITH TIME ZONE (время с часовым поясом) — такой же тип данных, как и TIME WITHOUT TIME ZONE. Отличие заключается лишь в том, что к значению времени добавляется еще и информация о разности между местным и всемирным временем. Всемирное время (Universal Time Coordinated, UTC) — это время по Гринвичу, т. е. время нулевого меридиана, проходящего через г. Гринвич в Великобритании (Greenwich Mean Time, GMT), Значение разности между локальным и всемирным временем находится в диапазоне от -12:59 до 13:00. Длина данных рассматриваемого типа равна длине данных типа TIME WITHOUT TIME ZONE плюс 6, поскольку дополнительная информация о разности времен занимает 6 позиций (дефис, знак (+) или (-), 2 цифры для часов, двоеточие, 2 цифры для минут).
Для одновременного представления даты и времени служат следующие два типа:
· TIMESTAMP WITHOUT TIME ZONE (дата и время без часового пояса). Элементы данных этого типа имеют такие же характеристики, как и для данных типа DATE и TIME WITHOUT TIME ZONE, за исключением одного: данные типа TIMESTAMP WITHOUT TIME ZONE по умолчанию имеют 6 цифр в дробной части секунд, а не 0, как в типе TIME WITHOUT TIME ZONE. Для указания количества цифр в дробной части используется синтаксис TIMESTAMP WITHOUT TIME ZONE (n). Если дробной части нет, то данные занимают 19 позиций: 10 позиций для даты, один пробел и 8 позиций для времени. Если определена дробная часть, то длина данных равна 20 плюс количество цифр в дробной части секунд;
· TIMESTAMP WITH TIME ZONE (дата и время с часовым поясом) — такой же тип данных, как и TIMESTAMP WITH TIME ZONE. Отличие состоит в том, что к значению времени добавляется еще и информация о разности между местным и всемирным временем (см. TIME WITH TIME ZONE). Дополнительная информация занимает 6 позиций. Данные типа TIMESTAMP WITH TIME ZONE без дробной части занимают 25 позиций, с дробной частью — 26 плюс количество цифр в дробной части секунд.
Чтобы представить в SQL-выражении дату, время или дату-время, необходимо использовать функцию CAST () приведения к заданному типу. Допустим, в таблице Продажи имеется столбец дата типа data. Чтобы получить сведения из этой таблицы за период после 2005-09-30, следует выполнить такой запрос:
SELECT * FROM Продажи WHERE Дата > CAST('2005-09-30' AS DATE);
Здесь строка, содержащая дату, приводится к типу DATA, и полученный результат участвует в операции сравнения с данными столбца Дата.
В языке SQL имеются три функции, которые возвращают текущие дату и время:
· CURRENT_DATE — возвращает текущую дату (тип DATE). Например, 2005-06-18;
· CURRENT_TIME(число) — возвращает текущее время (тип TIME). Целочисленный параметр число указывает точность представления секунд. Например, при число = 2 секунды будут представлены с точностью до сотых (две цифры в дробной части): 12:39:45.27;
· CURRENT_TIMESTAMP (число) — возвращает дату и время (тип TIMESTAMP), например 2005-06-18 12:39:45.27. Целочисленный параметр число указывает точность представления секунд.
ИНТЕРВАЛЫ
Интервал представляет собой разность между двумя значениями типа дата-время. SQL поддерживает два типа интервалов: год-месяц и день-время. Интервал типа год-месяц — это количество лет и месяцев между двумя датами, а интервал день-время — количество дней, часов, минут и секунд между двумя моментами в пределах одного месяца. Нельзя смешивать вычисления, использующие интервал год-день, с вычислениями, в которых используется интервал день-время. Интервал времени можно задать двумя способами: в виде начального и конечного моментов или в виде начального момента и длительности, например:
· (TIME '12:25:30', TIME '14:30:00') — интервал, заданный начальным и конечным моментами;
· (TIME 12:45:00', INTERVAL '5' HOUR) — Интервал, заданный начальным моментом и длительностью в часах.
Чтобы задать значение типа интервал, используется такой синтаксис:
INTERVAL 'длина' YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
Здесь длина — длина интервала, после которой указывается единица измерения (возможные значения указаны через вертикальную черту):
· YEAR — год;
· MONTH — месяц;
· DAY — день;
· HOUR — час;
· MINUTE — минута;
· SECOND — секунда.
Например, для задания интервала длиной 1 5 дней следует использовать выражение INTERVAL ' 5 ' DAY.
Еще один пример использования интервалов (предикат OVERLAPS).
СПЕЦИАЛЬНЫЕ ТИПЫДАННЫХ
К специальным типам данных относятся следующие типы:
· ROW — запись;
· ARRAY — массив;
· MULTISET — мультимножество.
Использование этих типов данных нарушает принцип, согласно которому таблицы реляционной базы данных должны находиться в первой нормальной форме. Любой столбец таблицы в первой нормальной форме не может содержать структурированные данные, например, набор полей: значения столбцов должны быть едиными и неделимыми. Тем не менее, жизнь неоднократно предлагает отступить от теории в пользу эффективности. Если вы не отступаете от первой нормальной формы, то возникающие недоразумения в поддержке реляционных баз данных вы можете объяснить нарушениями законов реляционнойтеории. В противном случае для получения объяснений требуются более тщательные расследования происшедшего. Рассмотрим специальные типы данных подробнее.
Тип ROW (запись) позволяет объявить набор полей (запись) в качестве значения столбца таблицы. Таким образом, строки таблиц могут содержать записи.
Во многих базах данных имеются таблицы, содержащие сведения об адресах. Адрес, как известно, состоит из нескольких элементов, таких как почтовый индекс, страна, город, улица и т. д. Если в таблице завести один символьный столбец Адрес, в котором поместить все элементы адреса просто как символьную строку, то дальнейшая работа с отдельными элементами адреса будет довольно хлопотным делом (вам придется использовать функции разбора строк). Поэтому на практике для адреса обычно заводят несколько столбцов, по одному на каждый элемент (например, столбцы Город, Улица и т. д.). Если же использовать тип ROW, то можно хранить все элементы адреса как значения одного столбца. В следующем примере сначала на основе типа ROW создается тип addr, а затем он назначается столбцу Адрес при создании таблицы Клиенты:
CREATE ROW TYPE addr
(
Postcode VARCHAR (9),
City VARCHAR (30),
Street VARCHAR (30),
House VARCHAR (10)
);
CREATE TABLE Клиенты
(
ID_клиента INTEGER PRIMARY KEY,
Имя VARCHAR (25),
Адрес addr,
Телефон VARCHAR (15)
);
Тип данных ROW(запись) появился впервые в SQL:1999, а раньше без него прекрасно обходились.
Тип ARRAY (массив) также впервые появился в SQL: 1999 и нарушает принцип первой нормальной формы, но несколько иначе, чем тип ROW. Тип ARRAY дает возможность одному из обычных типов иметь множество значений внутри значения одного табличного столбца. Например, клиент может иметь несколько номеров телефонов и вы можете пожелать хранить их все как одно многозначное значение единственного столбца Телефон, а не как обычные значения нескольких столбцов Телефон1, Телефон2 и т. п. Вот пример, в котором каждая ячейка столбца Телефон может содержать до трех номеров телефона:
CREATE TABLE Клиенты
(
ID_клиента INTEGER PRIMARY KEY,
Имя VARCHAR (25),
Адрес addr,
Телефон VARCHAR (15) ARRAY [3]
);
Следующее SQL-выражение добавляет в таблицу Клиенты новую запись и вводит в нее значения столбцов:
INSERT INTO Клиенты (Имя, Телефон)
VALUES ('Петров Петр Петрович', {'444-4444', '123-4567', '777-8899'});
Тип данных MULTISET (мультимножество) появился впервые в SQL:2003. Если массив — это упорядоченное множество элементов, однозначно связанных с их местом в массиве посредством индекса, то мультимножество — неупорядоченная совокупность элементов. Этот тип данных используется очень редко.
ПОЛЬЗОВАТЕЛЬСКИЕ ТИПЫДАННЫХ
Пользовательские типы данных (User Defined Types, UDT) определяются пользователем по своему усмотрению для каких-то своих целей. Данная возможность впервые появилась в SQL: 1999. Одно из важнейших направлений применения заключается в устранении некоторых несоответствий между основными типами данных SQL и типами данных в базовом языке, на котором пишутся приложения. К созданию своих типов данных обычно прибегают программисты, а не рядовые пользователи баз данных, даже владеющие языком SQL. Тем не менее, им также не помешает ознакомиться в общих чертах с этой возможностью SQL.
Отдельные типы
Простейшей формой пользовательских типов являются так называемые отдельные (различающиеся) типы. Они создаются на основе ранее определенных типов, например, основных типов SQL. Синтаксис создания отдельного типа данных такой:
CREATE DISTINCT TYPE имяТипа AS определенныйТип;
Определим в качестве примера тип MONEY для хранения денежных сумм. С этой целью можно воспользоваться уже определенным числовым типом DECIMAL:
CREATE DISTINCT TYPE MONEY AS DECIMAL (9,2);
Все отдельные типы, даже созданные на базе одного и того же типа, не могут сравниваться ни друг с другом, ни с базовым типом. Таким образом, данные типа MONEY нельзя сравнивать с данными типа DECIMAL, а также с данными других числовых и нечисловых типов. Создав специальный тип для денег, мы тем самым исключили возможность сравнивать денежные суммы с количеством других вещей. По аналогии с типом MONEY можно создать типы данных для различных валют, чтобы исключить возможность сравнения их просто как обычных чисел:
CREATE DISTINCT TYPE EURO AS DECIMAL (9,2);
CREATE DISTINCT TYPE USD AS DECIMAL (9,2);
CREATE DISTINCT TYPE RU AS DECIMAL (9,2);
Чтобы сравнить денежные суммы в различных валютах, придется сначала выполнить конвертацию одной валюты в другую. В SQL это можно сделать спомощью функции приведения типов CAST ().
Допустим, имеется таблица Прайс_лист (Товар, Цена_USD, Цена_RU), в которой столбцы Цена_USD и Цена_RU определены как столбцы типов USD и RU соответственно. Эти столбцы содержат цены товаров в американских долларах и рублях. Предположим, что курс доллара к рублю изменился, и мы хотим изменить цены в рублях. Если бы Столбцы Цена_USD и Цена_RU были одногои того же типа, то данную операцию можно было бы выполнить с помощью следующего SQL-выражения:
UPDATE Прайс_лист SET Цена_RU = k * Цена_USD;
Здесь k — коэффициент конвертации.
Но поскольку столбцы Цена_USD и Цена_RU имеют различные типы, то необходимо выполнить такое выражение:
UPDATE Прайс_дист
SET CAST(Цена_RU AS DECIMAL (9,2) = k * CAST(Цена_USD AS DECIMAL (9,2));
Структурированные типы
Другая форма пользовательских типов данных — структурированные типы. Структурированный тип определяется не на основе какого-то базового (ранее определенного типа), а путем задания своих атрибутов и методов. Эта конструкция пришла в SQL из объектно-ориентированного программирования (ООП). Она представляет средст