Язык SQL

8.2 Стандарты и структуры языка, хранимые объекты базы

Аббревиатура SQL означает Structured Query Language, то есть "структурированный язык запросов". Рекомендуемое чтение названия [эс-кью-эл]. Встречается прочтение [сиквел]. Дело в том, что одним из предшественников SQL был язык SEQUEL [сиквел], и поэтому есть своего рода профессиональный признак —те, кто занимается давно и в хорошем коллективе, часто произносят сиквел. Это нечто похожее на то, как Пафнутия Львовича, по-моему в Москве называли Чебышев, а в Петербурге Чебыпгов и по этому произношению сразу видно — мы московские или ближе к питерским.

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

Иногда реализуются

8.2.1 Стандарты SQL

Стандарт языка SQL1, принятый ANSI в 1986 г., описывал только запросы. С ним вы можете поработать в WinRDBI. В настоящее время SQL1 не используется.

Промышленные СУБД основаны на следующих версиях:

Набор последних расширений языка представляют как SQL-2006 и SQL-2008.

Мы будем ориентироваться на версию SQL2, но рассмотрим несколько расширений языка вне этой версии. Очевидно, изучение формальных основ языка важно, но недостаточно получить в результате только ремесленную основу — "делай раз, делай два". Важно понять, почему язык так устроен. Понимая внутреннюю структуру объекта или суть явления, вы всегда будете готовы воспринять их изменения.

Выделяются три уровня SQL—прямой, встроенный и динамический. Первый уровень обеспечивает непосредственное взаимодействие пользователя с СУБД. Встроенный SQL определяет его конструкции, вкладываемые в другие языки. В COS текст встроенного SQL помещается внутрь скобок sql(текст_SQL). Подробности в разделе 8.9. Наконец, динамический SQL позволяет образовывать конструкции прямого SQL "на ходу" и исполнять их.

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

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

Проблема в том, что в разных реализациях СУБД пути достижения результата могут быть совершенно различными. Поэтому, когда вы написали запрос, который дает вам возможность получить нужные данные, вы еще должны подумать над тем, а хорошо ли он исполняется в выбранной СУБД. На общепринятом языке говорят, что вы (или СУБД) должны выбрать оптимальный план исполнения. Ну и, естественно, создание запросов с оптимальными планами исполнения — это еще один слой программистских знаний, более глубокий, нежели просто умение написать запрос.

И еще, мы уже знаем, что на самом деле математические модели дают возможность строить только языки запросов. Потому-то в предыдущих разделах мы не занимались созданием и изменением схемы базы. Считалось, что набор отношений или набор деревьев уже существует, и мы не пытались даже заполнять его исходными данными. Вы, конечно, понимаете, что если мы хотим работать с базой данных, то должны иметь возможность задать схему приложения, изменять ее и заполнять данными. Меняется бизнес, меняются информационные потребности, и, естественно, мы должны каким-то образом всё это отслеживать. Поэтому языки, используемые в реализованных СУБД, кроме возможности писать запросы, позволяют еще создавать, изменять, удалять объекты базы и манипулировать данными. Последнее означает возможность вставлять записи, обновлять их и удалять. Вот такая сложная получается картина.

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

8.2.2 Подъязыки SQL

В SQL определены следующие подъязыки:

Одно замечание по поводу ЯОД. Мы уже обращали внимание на необходимость работы с пользователями, когда говорили о важности учёта того, кто задаёт вопросы о содержимом базы и при каких условиях это возможно. Так вот, права пользователя СУБД определяются привилегиями. Например, привилегия CREATE SESSION позволяет пользователю подключаться к базе, привилегия ALTER TABLE даёт возможность изменять таблицы и т. д.

Вообще, с пользователями обращаются по-разному в различных СУБД. Например, Oracle требует, чтобы все права созданного пользователя были прописаны полностью. То есть, если я создал пользователя, у которого есть имя и пароль, то он даже открыть сессию, то есть подключиться к СУБД, не может. Голенький, как Буратино перед тем как у него появился колпачок с кисточкой. Все привилегии нужно предоставить пользователю явно. Конечно, можно выделить собрания привилегий — роли — и использовать их, можно определить пользователя public, права которого есть у всех пользователей и т. д.

Языки манипулирования данными, позволяют вставлять, обновлять и удалять данные. Обычно язык запросов считают частью языка манипулирования данными, хотя иногда его и выделяют. Дело в том, что язык запросов строится по сути дела на основе одного шаблона SELECT, но он очень сложен.

О языке управления транзакциями мы с вами уже говорили в разделе 6.2. Помните — начало транзакции BEGIN TRANSACTION, завершение транзакции —это COMMIT или ROLLBACK. В действительности есть ещё другие инструкции, но они менее распространены.

8.2.3 Используемая терминология

И несколько замечаний о терминологии. Первое, собственно, связано не с SQL, а с тем, что в реализациях используется табличная терминология, то есть говорят не отношение, а "таблица", не "кортеж", а "строка". Атрибут называется, в зависимости от того, что вам нравится, либо столбец, либо колонка (таблица 8.1).

Следует помнить, что современные версии SQL работают в расширенных реляционных моделях данных. И эти расширения настолько велики, что есть смысл говорить не о реляционных базах, а о базах данных реляционного или табличного типа. Английский термин "statement", определяющий конструкции языка, в русскоязычной литературе переводят как "оператор", "команда", "выражение". Мы будем использовать термин "инструкция", так как "команда" имеет больше процедурного смысла, чем хотелось бы, а термины "оператор" и "выражение" имеют двойной смысл.

Составные части инструкций будем называть фразами.

8.2.4 Хранимые объекты базы

Основу базы реляционного типа образуют хранимые объекты. Это таблицы, представления, индексы, триггеры, последовательности и пользователи. Пройдёмся бегло по этим понятиям. Если вам не всё будет понятно, не смущайтесь — попозже мы их рассмотрим подробнее.

В таблицах хранятся данные. Обратим внимание на вроде бы тривиальное обстоятельство: таблица не сохраняет истории изменения своих данных. Не все таблицы устроены одинаково. То, что просто называется таблица, может оказаться таблицей, организованной как куча (heap). Могут использоваться индексно организованные таблицы (IOT — index organized table). В них данные таблицы хранятся в листовых узлах дерева индекса.

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

Второй хранимый объект — представление (view), на программистском жаргоне — "вьюшка". Но в отличие от таблицы, содержащей данные, в базе хранится только запрос, на котором это представление построено. Представление, как и таблица, имеет имя, и когда мы обращаемся к вьюшке, то образующий её запрос комбинируется с запросом пользователя. Мы потом рассмотрим, как это делается.

Индексы. Это такая организация доступа к данным, которая может ускорить доступ, но всегда замедляет манипулирование данными. Чаще всего используют В*-индексы и побитовые индексы. Подробнее мы рассмотрим индексы в лекции 11.

Триггеры — это специальные процедуры, которые срабатывают при наступлении некоторого события, называемого триггерным.

База называется активной, если она делает что-то сверх того, что ее попросили. Представим, как работает ограничение целостности "первичный ключ". Как только вы пытаетесь занести данные в таблицу, ограничение целостности вызывает срабатывание своей внутренней процедуры, которая должна проверить, не повторяется ли значение первичного ключа. Если да, то ввод не допускается, а если нет — разрешается. Важно понимать, что активное поведение базы в приложениях чаще всего организуется за счёт триггеров.

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

Пользователи (user). С ними связан целый ряд проблем доступа, ограничения доступа. Мы уже говорили о том, что в разных базах пользователи организованы по-разному.

На самом деле, не удаётся решить все практические задачи, используя только язык SQL. Поэтому современные СУБД имеют мощную процедурную часть, которая в разных СУБД существенно различается.

В процедурной части добавляются следующие хранимые объекты:

Существуют ещё один процедурный объект, не сохраняемый в базе. Это курсор (cursor). Вы, возможно, привыкли к тому, что курсор — это такое изображение, которое вы возите по экрану с помощью мыши или тачпада. На самом деле, в стандарте на SQL курсор определён как область памяти, которая предназначена для хранения, во-первых, названия курсора, во-вторых, запроса, на котором основан курсор, и, в третьих, данных, которые курсор выбирает из базы. Имеется указатель на строки результирующих данных.

Заметим, что повторное упоминание триггеров в процедурной части — это не ошибка. Триггеры — это процедуры специального вида.

Процедуры и функции могут группироваться в пакеты.

Замечание. Индексы будут изучаться в лекции 11 "Хранение данных и доступ к ним". Процедурная часть СУБД в настоящем курсе рассматривается только при изучении объектных моделей.

8.2.5 Типы данных в SQL

В стандарте SQL-92 задан набор типов данных, определяемых ключевыми словами: CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME, TIMESTAMP и INTERVAL. В последующих стандартах SQL этот перечень существенно расширился.

Мы будем работать с небольшой частью типов, которые реализуются во всех используемых в книге СУБД. Это типы символьных строк (character strings), точные числовые типы (exact numeric), приближённые числовые типы (approximate numeric), типы даты и времени (datetime). Типы коллекций, типы, определяемые пользователем и ссылочные типы будут рассматриваться в лекции 10 при изучении объектных моделей.

Некоторые другие типы в книге вообще не рассматриваются. Ищите их в документации СУБД, которыми будете пользоваться.

8.2.6 Типы символьных строк

Определение максимальной длины строк стандарты возлагают на реализацию. В Cache максимальная длина обоих типов 32000 символов. В Oracle максимальная длина для CHAR 2000 символов, для VARCHAR — 4000 байт.

Обратите внимание на то, что в Cache типы CHAR и VARCHAR не различимы по поведению, так как пробелы завершающие строку всегда удаляются.

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

Точные числовые типы (типы с фиксированной точкой) и приближённые (с плавающей точкой) сведены в таблицу 8.2:

У Cache в типе NUMBER(n,s) точность n равна 21, но может быть изменена. Тип NUMBER без аргументов определяет целые числа в диапазоне от -9223372036854775807 до +9223372036854775808. В Oracle n<38, а значение s, определяющее положение младшего разряда от —84 до +127.

8.2.8 Типы даты и времени

Используемые в книге типы даты и времени сведены в таблицу 8.3.

Значения типа DATE состоят из трёх компонентов: года, месяца и даты. Значение года определяется летоисчислением от Рождества Христова. Один из возможных выходных форматов "yyyy-mm-dd", где все составляющие — год, месяц и день — представляются десятичными числами. В Cache начальная дата —31.12.1840.

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

В типе TIMESTAMP объединяются данные предыдущих двух типов.

8.3 Язык определения данных. Создание, удаление и изменение таблиц.

Для каждого типа хранимых объектов базы (таблица, последовательность, представление, пользователь) существует "малый джентльменский" набор инструкций CREATE, ALTER, DROP (СОЗДАТЬ, ИЗМЕНИТЬ, УДАЛИТЬ), например:

Замечание. В стандарте предусмотрены ещё инструкции для доменов. Здесь они не приведены, так как домены в СУБД обычно не реализуются.

8.3.1 Создание таблицы

Синтаксис инструкции создания таблицы:

CREATE    TABLE имя_таблицы (столбец
[,{столбец|именованное_ограничение_целостности}] ... )

где

столбец::=имя_столбца тип
[неименованное_ограничение_целостности] [значение_по_умолчанию] 
неименованное_ограничение_целостности::=NULL   |   NOT NULL   | UNIQUE |   PRIMARY KEY|  CHECK (условие) 
именованное_ограничение_целостности::=CONSTRAINT имя_ограничения {PRIMARY KEY  |   UNIQUE} (список_столбцов) |   FOREIGN KEY (список_столбцов)
REFERENCES имя_табл (список_столбцов) | CHECK (условие)
значение_по_умолчанию::=DEFAULT выражение

Знак "::=", то есть "два двоеточия и равно" означает "есть по определению"

Замечание. Неименованные ограничения целостности называются ограничениями уровня строки. Они не имеют имени заданного пользователем, но СУБД называет их своими именами.

Замечание. Именованные ограничения целостности называются ещё ограничениями уровня таблицы.

Давайте просмотрим инструкцию создания таблицы как можно подробнее. Первые два слова — создать таблицу (CREATE TABLE), за ними записывается имя таблицы. Почему между словами "имя" и "таблицы" стоит подчеркивание? Этим я хочу сказать, что два слова образуют один литерал, единое имя. Если бы я поставил пробел, вы могли бы воспринимать запись "имя таблицы" так: один литерал — "имя", а второй, почему-то "таблицы", ну, может много этих таблиц. Дальше стоят круглые скобки, которые закрываются только в самом конце выражения. В скобках вот такой перечень: обязательный столбец, затем столбец либо именованное ограничение целостности, причём фигурные скобки означают, что обязательно выбирают одно из них. Запятая после предыдущего элемента ставится, если есть следующий элемент. Эта конструкция (столбец или ограничение) может повторяться сколько угодно раз, что условно показано знаком многоточия, после закрывающей прямой скобки, означающим повтор от нуля раз, до любого числа. В итоге, мы указали, что создаётся таблица, содержащая минимум один столбец и, может быть, именованные ограничения целостности, разделяемые запятыми.

Итак, все понятно, вначале обязательно запишем CREATE TABLE, даль- ше обязательно имя таблицы и некоторый перечень столбцов, либо име- нованных ограничений целостности. Термины "столбец" и "именованное ограничение целостности", расшифровываются следующими двумя текстами. Запись

столбец::=имя_столбца тип [неименованное_ограничение_целостности] [значение_по_умолчанию]

означает, что "столбец" определён как разделённая пробелами последовательность "имя_столбца", "тип" и, может быть, "неименованное_ограниче-ние_цело стно сти".

Термин "неименованное ограничение целостности" тоже нуждается в расшифровке:

неименованное_ограничение_целостности NULL   |   NOT NULL   | UNIQUE |   PRIMARY KEY|   CHECK (условие)

Неименованное ограничение целостности есть по определению либо

слово NULL, либо NOT NULL, либо UNIQUE, либо PRIMARY KEY, либо вот такая конструкция — CHECK, у которой в скобках записано проверяемое условие. Используя его, мы можем задать проверку своих ограничивающих условий. Например, мы вводим столбец "зарплата" и приписываем столбцу ограничение CHECK, в котором указано, что зарплата должна быть меньше меньше какого-то значения. Например, CHECK(зарплата<20000).

Обратите внимание, что CHECK работает только с данными текущей строки.

Остаётся разобрать термин "именованное ограничение целостности":

именованное_ограничение_целостности CONSTRAINT имя_ограничения {PRIMARY KEY  |   UNIQUE} 
(список_столбцов) |   FOREIGN KEY (список_столбцов)
REFERENCES имя_табл (список_столбцов) | CHECK (условие)

Необходимо записать слово CONSTRAINT, за ним следует имя ограничения, а дальше возможны три варианта. В первом записывается либо PRIMARY KEY, либо UNIQUE. За ними следует список столбцов, на которых создаются ограничения.

Во втором варианте, отделённом вертикальной чертой, стоит словосочетание FOREIGN KEY —внешний ключ — и список столбцов, которые этот ключ образуют. За ним обязательно записано слово REFERENCES, указывающее, на что мы ссылаемся. А ссылаемся мы обязательно на имя таблицы через столбцы в обозначенном списке, состоящем не менее чем из одного столбца.

И, наконец, последний вариант — уже известное нам ограничение целостности CHECK с условием.

Обратите внимание, что именованные ограничения целостности записываются после перечисления столбцов.

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

Примеры инструкций CREATE TABLE:

CREATE TABLE qq (c1 NUMBER(3) PRIMARY KEY, c2 CHAR(5))

CREATE TABLE qq  (c1 NUMBER(3),   c2 CHAR(5),
CONSTRAINT pk_c1 PRIMARY KEY(c1))

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

Виды именованных декларативных ограничений целостности, используемых в таблицах:

[CONSTRAINT имя_ограничения]
UNIQUE (столбец1,  столбец2, ...)

[CONSTRAINT имя_ограничения]
PRIMARY KEY (столбец1,  столбец2, ...)

[CONSTRAINT имя_ограничения]
FOREIGN KEY (столбец1, столбец2, ...) REFERENCES таблица (столбец1, [столбец2], ...)

[CONSTRAINT имя_ограничения] CHECK (условие)

Оказывается, что для всех хранимых объектов, кроме таблиц, в инструкции CREATE существует версия CREATE OR REPLACE, то есть создать или заменить. Почему? Если бы работала опция CREATE OR REPLACE TABLE, то таблицу, в которой собирались данные, например, за 10 лет, легко было бы заменить на пустую таблицу с таким же именем и данные были бы утеряны. Конечно, отсутствие опции замены —не гарантия от возможных ошибок, но мы их хотя бы не провоцируем.

А вот теперь вопрос: зачем существуют очень похожие неименованные ограничения целостности и именованные ограничения, тем более, что неименованным ограничениям не даёт имён пользователь, но СУБД их сама назовёт своими именами, правда не очень удобными для человека? Кому это нужно, кроме, конечно, преподавателя, который хочет запутать студентов? Представим себе, что в пустую базу необходимо закачать данные. Обычно эти данные выбираются из существующей базы, в которой когда-то эти ограничения целостности проверялись, но, может быть, не все. При записи данных срабатывают ограничения целостности, и проверка уже приведённых данных может занять много времени. Более того, при некотором порядке записи в связанные таблицы, ограничения не позволят продолжать передачу данных. Например, если не заполнена таблица "отделы", то ограничение FOREIGN KEY не позволит заполнять таблицу "сотрудники", связанную с таблицей "отделы" внешним ключом. Проще временно отключить все или некоторые ограничения целостности, а по окончании заполнения их восстановить. Выборочное отключение ограничений проще сделать по мнемоническим именам. Так что причина сохранения двух возможностей для записи ограничений чисто технологическая, и связана с учётом особенностей восприятия человека-пользователя.

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

CREATE GLOBAL TEMPORARY TABLE ...

В Cache такая таблица доступна всем процессам. Она будет уничтожена только, когда все процессы прекратятся. Можно ещё указать, что таблица доступна только создающему процессу, набрав PRIVATE вместо GLOBAL.

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

FOREIGN KEY (список_столбцов)
REFERENCES имя_табл (список_столбцов) ON   [DELETE   | UPDATE]
[NO ACTION   |   SET DEFAULT   |   SET NULL   | CASCADE]

Поскольку определение внешнего ключа в стандартах SQL достаточно сложно, мы будем рассматривать упрощённую версию, достаточную для работы со многими СУБД.

Ограничение целостности "внешний ключ" допускает, чтобы один или несколько столбцов внешнего ключа содержали NULL. Выполняется оно, если в таблице S, связанной с определяемой таблицей T с помощью внешнего ключа таблицы T, имеется в точности одна строка s такая, что значение её возможного ключа совпадает со значением внешнего ключа в некоторой строке t. Говорят, что строка t ссылается на строку s (английское название referencing table). Заметьте, что проверяются только имеющиеся значения (не NULL).

По умолчанию в SQL предполагается именно этот вариант внешнего ключа, хотя он не вполне соответствует реляционной модели.

При удалении (ON DELETE) возможны следующие варианты ссылочных действий:

При обновлении (ON UPDATE) возможны следующие варианты ссылочных действий:

8.3.2 Удаление и изменение таблиц

Синтаксис инструкции удаления таблицы:

DROP TABLE имя_таблицы

В таблице можно изменять столбцы и ограничения:

ALTER TABLE имя_таблицы
изменение_столбца  | изменение_ограничения

Несколько упрощенные спецификации столбцов и ограничений:

изменение_столбца ::=
ADD (спецификации_столбцов)
| MODIFY (спецификации_столбцов)
{SET определение_умолчания | DROP DEFAULT}
|DROP спецификация_столбца {RESTRICT | CASCADE}

изменение_ограничения ::=
ADD CONSTRAINT
именованное_ограничение_целостности
|DROP CONSTRAINT имя_ограничения

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

Столбец может быть изменён или удалён. Удаление единственного столбца таблицы невозможно. Опция CASCADE заставляет удалять все ограничения целостности, как-то связанные с удаляемым столбцом. При использовании RESTRICT будут удалены только ограничения, в которых используется только удаляемый столбец.

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

Удалять можно и именованные, и неименованные ограничения.

Для последних необходимо найти имя, присвоенное им системой.

Итак, можно изменять различные компоненты таблицы. При этом весьма полезно задумываться о том, правильно ли мы поступили, удаляя или сужая столбец. Ведь урезав длинные тексты, мы можем потерять ценную информацию.

8.4 Язык манипулирования данными

Манипулирование данными выполняют инструкции:

8.4.1 Вставка

Новая строка вводится в таблицу инструкцией INSERT, упрощённый синтаксис которой выглядит так:

INSERT INTO имя_таблицы_или_представления [(столбец    [,столбец]  ... )] [VALUES    (значение [, значение] ...)]

Перечень столбцов после имени таблицы указывает столбцы, в которые вводят значения (по умолчанию ввод во все столбцы). После слова VALUES перечисляют вводимые значения. Для вставки неопределённого значения можно указать его явно: NULL. Можно просто не указывать значения, не забывая запятых.

Пример инструкции INSERT: Предполагается, что таблица создана инструкцией

CREATE TABLE qq (c1 NUMBER(3)  PRIMARY KEY,   c2 CHAR(5))

Если таблица с именем qq уже существует, следует начала удалить её инструкцией

DROP TABLE qq

а затем вновь создать. Вставим строку со значениями "7" в столбце c1 и "A" в столбце c2:

INSERT INTO qq VALUES(7, 'A')

Если вам так удобно, читайте инструкцию как фразу на русском языке: "ВВЕСТИ В qq ЗНАЧЕНИЯ (7, 'A')"

Почему мы написали ЗНАЧЕНИЯ, а не СТРОКУ? Да потому, что вводится именно набор значений столбцов в строке, а не обязательно вся строка. Не введённые значения столбцов вызывают вставку NULL, если это допустимо.

Вставляем ещё одну строку:

INSERT INTO qq VALUES(1, 'В')

Обратите внимание на то, что в одинарные кавычки берутся только строчные значения, но не числовые. Проверим, действительно ли введены эти строки. Для этого инструкцией SELECT * FROM qq выберем все строки из таблицы qq. Символ "*" означает выбор всех столбцов. Результат выполнения инструкций вы можете увидеть в листинге 8.1.

USER>>CREATE TABLE qq (c1 NUMBER(3) PRIMARY KEY,c2 CHAR(5))
0 Rows Affected
-------------------------------------------------------
USER>>INSERT INTO qq VALUES(7,'A')
1 Row Affected
-------------------------------------------------------
USER>>INSERT INTO qq VALUES(1,'B')
1 Row Affected
-------------------------------------------------------
USER>>SELECT * FROM qq
c1 c2
7 A
1 B
2 Rows(s) Affected
-------------------------------------------------------

Замечание. Инструкцию SELECT мы рассмотрим позднее, а пока используем указанный простой вариант запроса.

Для ввода больших объёмов данных может быть полезен формат

INSERT INTO имя_таблицы_или_представления запрос

Пусть создана таблица vv со столбцами v1 типа NUMBER(5) и v2 типа CHAR(5) . В нее можно перенести все содержимое таблицы qq:

INSERT INTO vv SELECT * FROM qq

8.4.2 Обновление

Изменение существующих строк выполняет инструкция UPDATE:

UPDATE имя_таблицы_или_представления
SET   столбец=выражение  [,столбец=выражение] ...
[WHERE условие];

Пример инструкции UPDATE:

Заменим в строке (7, "A") значение 7 на 11. Строку выберем по условию с1=7:

UPDATE qq
SET c1=11 
WHERE c1=7 Проверим 

результат запросом:

SELECT * FROM qq

8.4.3 Удаление

Удаляются строки из таблицы инструкцией DELETE:

DELETE [FROM] имя_таблицы_или_представления [WHERE условие]

Если фраза WHERE отсутствует, будут удалены все строки.

Пример инструкции DELETE:

Удаляем первую строку (11, "A"):

DELETE FROM qq WHERE c2='A'

Тот же результат можно было получить опустив слово FROM:

DELETE qq WHERE c2='A'

Результат выполнения запросов вы можете увидеть в листинге 8.2. Обратите внимание на то, что во всех инструкциях мы были уверены, что выбираем единственную строку. Ошибка в выборе условия может привести к непредусмотренным изменениям данных, может быть, большого объёма.

USER>>UPDATE qq SET c1=11 WHERE c1=7
1 Row Affected
-------------------------------------------------------
USER>>SELECT * FROM qq
c1 c2
11 A
1 B
2 Rows(s) Affected
-------------------------------------------------------
USER>>DELETE FROM QQ WHERE c2='A'
1 Row Affected
-------------------------------------------------------
USER>>SELECT * FROM qq
c1 c2
1 B
1 Rows(s) Affected
-------------------------------------------------------

Заметьте, что для инструкции удаления таблицы используется слово DROP, а для удаления строк слово DELETE. Специально выбраны различные слова, чтобы не перепутать.

8.5 Запросы

Инструкцию "SELECT * FROM таблица" мы уже использовали без объяснения. Перейдём к систематическому изучению запросов.

8.5.1 Запросы в рамках исчисления на кортежах и соответствующие запросы в SQL

Останемся пока строго в рамках исчисления на кортежах. Инструкция SELECT (по-русски "ВЫБРАТЬ") должна состоять минимум из двух фраз SELECT и FROM (по-русски "ИЗ"), а именно:

SELECT DISTINCT
{*  |   { столбец|константа [псевдоним]},    ... } FROM    {таблица,    ... }

Фраза SELECT определяет список столбцов таблицы-результата. Если записаны псевдонимы, то используют их как имена.

Фраза FROM задает список таблиц, из которых производится выборка, а слово DISTINCT позволяет избежать дублирования строк, недопустимого в реляционной модели. Если список содержит более одной таблицы, то запрос строит декартово произведение. Поясним список фразы SELECT:

{*  |   {столбец|константа [псевдоним]},    ...   }

Он может состоять из единственного символа звездочка, означающего "выбрать все столбцы". Можно задать список столбцов, разделяя их запятой. Через пробел к каждому имени можно приписать псевдоним. Последнее соответствует операции переименования.

Во фразе FROM указывают имя таблицы или нескольких таблиц, из которых выбираются данные.

Самый сложный запрос, который можно записать в рамках исчисления на кортежах без использования вложенных подзапросов, имеет формат:

SELECT DISTINCT
{*   |   {столбец|константа  [псевдоним]},     ... } FROM таблица [, псевдоним_табл ] WHERE условие(я)

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

Добавленная фраза WHERE (по-русски "где") определяет условия, которым должны удовлетворять выбираемые кортежи. Для нескольких таблиц во фразе WHERE также записываются условия соединения таблиц.

Замечание. В рамках исчисления на кортежах запросы не содержат в списке SELECT функций от столбцов или констант.

Пример: Запрос в рамках исчисления на кортежах:

SELECT DISTINCT с1 FROM qq WHERE c2<>'A'

Из таблицы qq выбираются строки, в которых значения в столбце c2 отличны от 'A', и выдаётся столбец c1 (листинг 8.3).

USER>> << entering multiline statement mode >>
1>>SELECT DISTINCT c1
2>>FROM qq
3>>WHERE c2<>10
4>>go
c1
1
1 Rows(s) Affected
-------------------------------------------------------

В рамках исчисления допустимы запросы к результатам других запросов, понимаемым как переменная-отношение. В SQL им соответствует использование обычных (не коррелированных) подзапросов. О них поговорим позже.

8.5.2 Простейший запрос SQL

Расширим определение запроса, принятое в рамках исчисления на кор- тежах:

SELECT [DISTINCT]
{*|{столбец|константа|функция [псевдоним]}, ...} FROM     {таблица,    ... } WHERE условие(я) [GROUP BY список_столбцов]
[ORDER BY {столбец|выражение,    ...} [ASC|DESC]]

Символ "*", как вы помните, означает выбор всех столбцов. Подчеркнём, что этот символ может быть только единственным в списке SELECT. Слово DISTINCT теперь не обязательное, так как в SQL допустимы повторы строк.

Фраза ORDER BY ("упорядочить по") задает упорядочение строк и в запросе всегда стоит последней. По умолчанию упорядочение ведётся по возрастанию (ASCENDING — сокращённо ASC), можно задать упорядочение по убыванию (DESCENDING — сокращённо DESC). Если во фразе ORDER BY записано через запятую несколько столбцов, то вначале выполняется упорядочение по первому столбцу, полученные группы строк упорядочиваются по второму столбцу, и т. д. Как вы помните, в реляционной модели строки не упорядочены.

Функции во фразе SELECT могут быть одно- и многострочными. Последние ещё называют групповыми. Способ группирования определяется списком столбцов во фразе GROUP BY.

Напомним, что функции от значений в столбцах в реляционной теории не предусмотрены. Таким образом, использование функций и фраз GROUP BY и ORDER BY выводит нас за пределы реляционной модели.

Учебная схема базы данных

Очень удобно, когда определена всем известная схема базы данных и никому не нужно объяснять её детали. К сожалению, такие схемы распространяются исключительно в рамках одной СУБД или учебника. Мы уже нарушили эту обременительную традицию, загрузив (смотри раздел 8.1) учебную схему scott, известную издавна тем, кто работает в СУБД Oracle. В ней использованы три таблицы: dept (от слова department — отдел) описывает отделы некоторой просто структурированной организации, emp (от слова employee — работник) содержит сведения о работниках, а в salgrade (salary grade — категории оплаты) описана система оплаты.

Ниже расписана структура этих таблиц (таблицы 8.6 и 8.6, 8.7). В верхней строке приводится название столбца, а внизу даны пояснения. Перед именами столбцов первичных ключей помещен признак ключа в виде символа "*". В имя столбца он не входит.

Содержимое учебной базы данных, созданной скриптом demobld.sql, показано в таблицах 8.8 и 8.9, 8.10.

Система оплаты заключается в том, что каждому работнику присваивается категория оплаты grade. В каждой такой категории определена раз и навсегда так называемая вилка, то есть минимальный оклад (losal) и максимальный (hisal). Допустимая для категории заработная плата выбирается из условия:

losal <= заработная_плата <= hisal для данной категории.

Изучим содержимое таблицы emp (таблица8.10). Заметим, что в отделе может не быть сотрудников, если отдел введен, но ещё не сформирован. Но в работающих отделах может быть один и более человек. Так из таблицы emp видно, что в отделе 10 работают Кларк, Кинг и Миллер, а в отделе 30 — Аллен и другие. Соединение таблиц emp и dept может быть реализовано через столбец deptno таблицы dept и столбец с тем же именем deptno в таблице emp. Считается, что все сотрудники, даже президент, относятся к какому-нибудь отделу. Правда, не очень умно подчинять президента начальнику отдела, который подчинён президенту? Ну, может быть, просто зарплата президента отнесена на расходы отдела 10? Потому его и включили в отдел 10.

Теперь проанализируем возможность соединения таблицы emp с собой. Естественно, что президент компании с многозначительной фамилией KING не имеет начальников. В поле mgr (руководитель) у него стоит неопределенное значение NULL. А вот менеджер Блейк (BLAKE) непосредственно подчинен президенту и табельный номер Кинга 7839 стоит в поле mgr во второй строке. В свою очередь, Аллен (ALLEN) подчиняется непосредственно Блейку чей табельный номер 7698 и стоит в столбце mgr второй строки. Значит, соединение таблицы emp с собой с помощью столбцов empno и mgr имеет смысл.

Заметим, что если в emp добавить столбцы, определяющие рост и вес сотрудников, то иерархию на этих столбцах построить можно, но смысла в описываемой предметной области такая связь не имеет, точнее, он есть, но больно уж извращённый.

Заметьте, что у всех таблиц схемы не выделены даже первичные ключи.

Выполнение однотабличного запроса

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

Итак, однотабличный запрос выполняется путём поочерёдного применения фраз, образующих запрос. Запишем алгоритм, используя псевдокод:

Если бы можно было записывать запрос как последовательность фраз FROM, WHERE, SELECT, ORDER BY, что, в отличие от английского, допускается русским языком, то не пришлось бы вспоминать порядок действий.

Сравнение запросов SQL и запросов в языке исчисления на кортежах

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

Перечислим некоторые расширения, частично упомянутые ранее:

SUBSTR(ИМЯ, начальная_позиция, длина)

DUMP (имя  | строка)

DECODE(значение, зн1, рез1,
зн2, рез2, ... результат_по_умолч)

SELECT ename,job,sal,
CASE job WHEN  'CLERK'   THEN 1.10*sal
WHEN  'SALESMAN'   THEN 1.20*sal ELSE 1.05*sal
FROM emp
ename job sal Expression_4
SMITH CLERK 800 880
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1500
JONES MANAGER 2975 3123.75
MARTIN SALESMAN 1250 1500
BLAKE MANAGER 2850 2992.5
CLARK MANAGER 2450 2572.5
SCOTT ANALYST 3000 3150
KING PRESIDENT 5000 5250
TURNER SALESMAN 1500 1800
ADAMS CLERK 1100 1210
JAMES CLERK 950 1045
FORD ANALYST 3000 3150
MILLER CLERK 1300 1430

SELECT ename, sal FROM emp
WHERE sal IN  (800,   1250, 2000)
ename sal
SMITH 800 WARD 1250 MARTIN 1250

SELECT deptno,  SUM(sal) FROM emp GROUP BY deptno 
deptno Aggregate_2
ename     sal
10     8750
20     10875
30     9400

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

8.5.3 Объединение результатов нескольких запросов и соединения таблиц

Объединение результатов нескольких запросов

Результаты нескольких запросов можно объединить операциями UNION и UNION ALL. Объединение возможно, если результирующие таблицы соединяемых запросов имеют одинаковое число столбцов попарно одинаковых типов. Имена соответствующих столбцов могут различаться. В результате обычно используются имена первого из объединяемых запросов.

Структура объединения:

Запрос 1 без ORDER BY
UNION [ALL]
Запрос 2 без ORDER BY
[ORDER BY ...]

Объединение результатов запросов может содержать повторяющиеся строки, UNION удаляет повторы, а чтобы оставить их, следует применить вариант UNION ALL.

Пример: Выбрать сотрудников отдела 20, присоединив к ним клерков из любых отделов. Первый запрос даёт повторы строк (листинг 8.7).

SELECT ename,job, deptno FROM emp WHERE deptno=20
UNION ALL
SELECT ename,job, deptno FROM emp WHERE job='CLERK'
ename job deptno
SMITH CLERK 20
JONES MANAGER 20
SCOTT ANALYST 20
ADAMS CLERK 20
FORD ANALYST 20
SMITH CLERK 20
ADAMS CLERK 20
JAMES CLERK 30
MILLER CLERK 10

Убрав слово ALL, получаем ответ без повторов для Смита и Адамса.

Этот же результат даст единственный запрос со сложным условием:

SELECT DISTINCT ename,job, deptno FROM emp WHERE deptno=20 OR job='CLERK'

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

Выполнение запросов с UNION

Соединения таблиц

Соединения двух и более таблиц могут выполняться в одном запросе с указанием условий соединения. Пример: Запрос в листинге 8.8 выбирает фамилии сотрудников, номера и названия отделов, в которых они работают.

SELECT ename, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno
ename deptno dname
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
SCOTT 20 RESEARCH
KING 10 ACCOUNTING
TURNER 30 SALES
ADAMS 20 RESEARCH
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING

Соединяем те строки таблиц emp и dept, которые имеют одинаковые значения столбца deptno. Поскольку deptno имеется в обеих таблицах, в условии соединения следует уточнить название столбца названием его таблицы, например, emp.deptno. В списке фразы SELECT только для одного столбца необходимо указание таблицы emp.deptno или dept.deptno. Если этого не сделать, появится сообщение об ошибке, потому что транслятор не может "понять" из какой таблицы выбрать deptno. Остальные столбцы ename и dname имеются только в одной таблице. При желании префиксы можно поставить и перед их именами.

Замечание. Различайте связи, объединения и соединения таблиц. Связи реализуются внешними ключами и работают во время манипулирования данными, обеспечивая выполнение ограничений ссылочной целостности. Объединения —это запросы с UNION и UNION ALL. Соединения создаются в запросах пользователя. Их смысл целиком на совести программиста, создающего запрос. СУБД в общем случае не хранит всех смыслов данных и не следит за осмысленностью соединений.

Внутренние и внешние соединения

В последнем рассмотренном примере и в операциях соединения реляционной алгебры (по равенству и не по равенству) соединялись существующие строки двух и более таблиц/отношений. (А как иначе?) Такие соединения называются внутренними. Существуют ещё внешние соединения. В них строка одной таблицы может соединяться с пустой строкой из другой таблицы. Несмотря на кажущуюся странность этой операции, она отражает смысл, имеющийся в моделях бизнеса.

Поясним это на примере. Предварительно необходимо в таблицу emp ввести отдел с номером 50, находящийся в Краснодаре и занимающийся маркетингом. Эти детали несущественны. Важно лишь то, что в новом отделе нет сотрудников.

Пример: Просмотреть список сотрудников во всех отделах, указав названия отделов.

SELECT ename, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno

Это внутреннее соединение. В ответе отсутствует только что введённый в таблицу dept отдел 50. Поэтому пользователь может считать, что такого отдела нет. Но мы же знаем, что отдел существует, только список его сотрудников пустой.

Избежать подобных казусов позволяют внешние соединения.

Внешние соединения

Для задания внешнего соединения до появления стандарта SQL92 во фразе WHERE использовались специальные обозначения, свои для каждого производителя. Например, в Cache используется обозначение =* для левого внешнего соединения и *= для правого внешнего соединения.

Пример: Правильное решение предыдущего примера с использованием правого внешнего соединения.

SELECT ename, dname
FROM emp, dept
WHERE emp.deptno *= dept.deptno

Теперь в ответе присутствует отдел 50, но сотрудников в нём нет. В стандарте существуют:

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

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

Выполнение внешних соединений

Порядок действий при выполнении полного внешнего соединения двух таблиц:

Левое внешнее соединение получится, если не выполнять п. 3. Правое внешнее соединение получится, если не выполнять п. 2.

Соединения в стандарте SQL92

В стандарте SQL92 внешние соединения определяются во фразе FROM, которая получает сложный синтаксис. Мы рассмотрим основные частные случаи.

SELECT список_SELECT
FROM имя_таблицы INNER JOIN имя_таблицы ON условие_соединения

  SELECT ename, emp.deptno, dname
  FROM emp, dept
  WHERE emp.deptno =dept.deptno
  

  SELECT ename,emp.deptno,dname
  FROM emp INNER JOIN dept
  ON emp.deptno =dept.deptno
  

SELECT фраза_SELECT
FROM имя_таблицы NATURAL JOIN имя_таблицы USING (список_столбцов)

SELECT ename, emp.deptno, dname
FROM emp INNER JOIN dept
USING (deptno)
ename deptno dname
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
MARTIN 30 SALES
BLAKE 30 SALES
CLARK 10 ACCOUNTING
SCOTT 20 RESEARCH
KING 10 ACCOUNTING
TURNER 30 SALES
ADAMS 20 RESEARCH
JAMES 30 SALES
FORD 20 RESEARCH
MILLER 10 ACCOUNTING

SELECT список_SELECT FROM имя_таблицы
FULL|LEFT|RIGHT OUTER JOIN имя_таблицы ON условие_соединения

  SELECT ename, dname
  FROM emp, dept
  WHERE emp.deptno =*dept.deptno

  SELECT ename, dname
  FROM emp LEFT JOIN dept
  ON emp.deptno =dept.deptno
  
  SELECT ename, dname
  FROM emp LEFT JOINUSING (deptno)

8.5.4 Запросы с группированием

Фраза GROUP BY, упоминавшаяся ранее, обеспечивает объединение строк с одинаковыми значениями в перечисленных столбцах. Такое преобразование необходимо для получения итоговых данных с помощью многострочных (они же статистические или агрегатные) функций MIN, MAX, SUM, COUNT, AVG и др.

Пример: В листинге 8.10 приведен запрос, который находит суммарную заработную плату по отделам.

SELECT deptno, SUM(sal) salary
FROM emp
GROUP BY deptno
deptno salary 30 8750
20 10875
30 9400

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

Если убрать фразу GROUP BY, то образуется одна группа из всех строк таблицы и ответ состоит из единственной строки, представляющей зарплату всех сотрудников из таблицы emp.

Аргументы функций SUM, AVG и COUNT могут уточняться указанием DISTINCT.

Примеры (не очень умные, но поясняющие суть дела) приведены в листинге 8.11. Первый запрос выдаёт количество сотрудников, получающих зарплату, второй — количество разных зарплат, а третий — количество сотрудников, получающих комиссионные (NULL не учитывается, 0 считается).

SELECT COUNT(sal) FROM emp
Aggregate_1
14
SELECT COUNT(DISTINCT sal) FROM emp
Aggregate_1
12
SELECT COUNT(comm) FROM emp
Aggregate_1
4

Выполнение запросов с группированием

Порядок действий при выполнении однотабличных запросов с фразой GROUP BY:

Замечание (о значениях NULL). Вспомним, что два значения NULL не считаются одинаковыми. При группировании это привело бы к тому, что группу образовывала каждая строка с NULL в столбце группировки. Поэтому в стандарте принято, что при группировке (и только при группировке) NULL'bi равны и потому помещаются в одну группу.

Отбор групп строк — фраза HAVING

Фраза HAVING предназначена для организации отбора групп.

Формат записываемого в ней условия такой же, как во фразе WHERE. Если условие отбора даёт значение TRUE, группа строк остаётся, и в результате для неё создаётся одна строка. Если же проверка даёт FALSE или NULL, группа строк не рассматривается, и результирующая строка для неё не формируется.

Пример:

SELECT job, AVG(sal)
FROM emp
GROUP BY job
HAVING SUM(sal)  > 3100

Фраза HAVING почти всегда используется вместе с фразой GROUP BY, однако некоторые трансляторы допускают применение HAVING в отсутствие GROUP BY. В этом случае образуется одна группа из всех строк таблицы.

Правила работы с NULL'ами такие же как в условиях фразы WHERE. Групповые функции можно использовать только в фразах SELECT, HAVING и ORDER BY.

Выполнение запросов с фразой HAVING

Ограничения на условия отбора групп: операндами в условиях отбора могут быть константы, столбцы группирования, групповые функции и выражения, построенные на этих операндах.

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

Порядок действий при выполнении многотабличных запросов с фразой

HAVING:

8.5.5 Подзапросы

Подзапрос — это инструкция SELECT, вложенная в другую инструкцию SELECT для получения промежуточных результатов. Подзапросы всегда выполняются от внутренних к внешним (за исключением коррелированных подзапросов).

Подзапрос может быть вложен:

Подзапрос может быть помещён во фразу SELECT. Но там имеет смысл использовать только корелированные подзапросы, которые мы рассмотрим ниже.

Синтаксис запроса с простым подзапросом, включённым во фразу WHERE:

SELECT ...
FROM имя_табл1
WHERE имя сравнение (SELECT ...
FROM имя_табл2
WHERE условие )

Вместо имени во фразе WHERE может использоваться выражение. Подзапросы могут использоваться также в инструкциях INSERT, UPDATE и DELETE.

Рассмотрим приём построения запроса с подзапросом методом нисходящего проектирования.

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

В задании обратим внимание на следующую подфразу "зарплату, максимальную для их должности". Такую величину можно вычислить только с помощью вспомогательного запроса, возвращающего список, состоящий из пар значений "максимальная_зарплата, должность". Прекрасно! Считаем, что уже существует такой список и назовём его бесхитростно LIST. Его формат:

LIST=(MAX1(sal),    job1, ...)

Теперь можно написать основной запрос:

SELECT   job, ename, sal FROM emp
WHERE     (sal,   job)   IN LIST ORDER    BY    sal DESC;

Запрос, получающий список LIST

SELECT    MAX(sal), job
FROM emp GROUP   BY job;

Остается вставить этот запрос в предыдущий запрос вместо заглушки LIST. К сожалению, в Cache такой подход напрямую реализовать нельзя, поскольку оператор IN может работать только со скалярными элементами. Однако, используя функцию CONCAT, объединяющую два поля в одну строку, легко получить эквивалентный запрос:

SELECT job, ename, sal
FROM emp
WHERE  {fn CONCAT(sal,job)} IN
(SELECT {fn CONCAT(MAX(sal),job)} FROM emp GROUP BY job) ORDER BY sal DESC

Однострочные подзапросы

Однострочный подзапрос возвращает ровно одну строку. С однострочными подзапросами используются однострочные операторы сравнения: >, =, >=, <, <>, <=.

Пример однострочного подзапроса приведён в листинге 8.12.

Обязательно запишите задание, по которому составлен этот запрос.

SELECT ename, job, sal
FROM emp
WHERE mgr = (SELECT empno FROM emp
WHERE ename='BLAKE')
AND sal < (SELECT sal
FROM emp
WHERE empno=7844)
ename job sal
WARD SALESMAN 1250
MARTIN SALESMAN 1250
JAMES CLERK 950

Многострочные подзапросы

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

Операторы сравнения для многострочных подзапросов:

Пример многострочного подзапроса с оператором сравнения IN:

SELECT ename, sal, deptno FROM emp
WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno)

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

Примеры многострочных подзапросов

В листинге 8.13 приведен пример многострочного подзапроса с оператором сравнения ANY. Сравнение "<ANY"(меньше хотя бы одного из значений) эквивалентно сравнению "меньше максимального значения".

SELECT ename, job, sal
FROM emp
WHERE sal < ANY (SELECT sal FROM emp
WHERE job='SALESMAN')
AND job<>'ANALYST'
ename job sal
SMITH CLERK 800
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300

Многострочный подзапрос с оператором сравнения ALL приведён в листинге 8.14. Сравнение "< ALL" (меньше всех значений)" эквивалентно сравнению "меньше минимального значения".

SELECT ename, job, sal
FROM emp
WHERE sal < ALL (SELECT sal FROM emp
WHERE job='MANAGER')
AND job <> 'CLERK'
ename job sal
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
TURNER SALESMAN 1500

Пример многострочного запроса с оператором сравнения EXISTS приведён ниже. В поиске людей принятых на работу одновременно с Джеймсом мы немного переусердствовали с псевдонимами (e2 можно было не задавать)

 
SELECT ename, job, sal,hiredate
FROM emp AS e1
WHERE EXISTS (SELECT * FROM emp e2
WHERE e1.hiredate=e2.hiredate AND ename='JAMES')
ename job sal hiredate
JAMES CLERK 950 1981-12-03
FORD ANALYST 3000 1981-12-03

Коррелированные подзапросы

Обычный подзапрос выполняется первым, внешний запрос вторым. Коррелированными называются подзапросы, выполняющиеся для каждой строки-кандидата из внешнего запроса (рисунок 8.4).

Отсюда вытекает необходимый признак: коррелированный подзапрос содержит столбец из внешнего запроса.

Пример коррелированного подзапроса: Найти всех работников, которые получают зарплату выше средней в своем отделе:

SELECT ename, sal salary, deptno FROM emp e
WHERE sal >   (SELECT AVG(sal)   FROM emp WHERE deptno=e.deptno)
ORDER BY deptno

Рассмотрим проектирование запроса с коррелированным подзапросом методом сверху вниз.

Пример: Выведите указанную информацию о сотрудниках, у которых зарплата выше средней по их отделу. Упорядочите результат по номерам отделов.

Пусть нам известна средняя заработная плата по каждому отделу Тогда внешний запрос

SELECT ename, sal salary, deptno FROM emp e
WHERE sal > средняя_заработная_плата(отдел) ORDER BY deptno

Псевдоним e для emp подставлен для использования в будущем подзапросе. Мы ведь знаем, что

Остается написать подзапрос, вычисляющий среднюю_заработную_плату для отдела с номером e.deptno, определенным внешним запросом

SELECT AVG(sal) FROM emp
WHERE deptno=e.deptno

Остаётся заменить им заглушку во внешнем запросе.

Во многих СУБД коррелированные подзапросы можно размещать, вопреки традиции и синтаксису, во фразе SELECT. Приведём в качестве примеров, такой совсем неумно составленный, но выполняющийся и в Oracle и в Cache, запрос

SELECT ename,
(SELECT job FROM emp where ename=e1.ename) JOB FROM emp e1

Конечно, последний пример не следует расценивать как призыв, писать не стандартно.

8.6 Иерархические структуры в таблицах

Уже упоминалось, что таблица может хранить дерево. В таблице emp хранится иерархия, изображённая на рисунке 8.5.

Для работы с иерархиями в SQL введены две фразы:

Синтаксис иерархического запроса:

SELECT [LEVEL], список_столбцов_или_выражений FROM имя_таблицы [WHERE условия] [START WITH условия] [CONNECT BY PRIOR условия]

где

условие ::= выражение оператор_сравнения выражение;

Псевдостолбец LEVEL возвращает значение 1 для корня дерева, полученного запросом, 2 для узлов уровня 1 в этом дереве и т.д.

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

Пример запроса, возвращающего поддерево в направлении сверху вниз, начиная с Jones, смотрите в листинге 8.15.

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

SELECT empno, ename, job, mgr
FROM emp
START WITH empno = 7566
CONNECT BY PRIOR mgr = empno
empno ename job mgr
7566 JONES MANAGER 7839
7788 SCOTT ANALYST 7566
7876 ADAMS CLERK 7788
7902 FORD ANALYST 7566
7369 SMITH CLERK 7902

8.7 Триггеры

Триггерами называют специальные процедуры, которые напрямую не вызываются, но срабатывают при наступлении некоторых событий, называемых триггерными. В SQL триггер прикреплён к таблице и может срабатывать до и после наступления триггерного события. Существуют два вида триггеров — уровня строки и уровня таблицы. Строчные триггеры срабатывают при обработке каждой строки, а табличные только один раз при входе в таблицу или выходе из неё. Заметим, что в Cache нет табличных триггеров.

Триггер в Cache создаётся инструкцией

CREATE TRIGGER имя_триггера {BEFORE | AFTER} событие
[ORDER целое]
ON имя_таблицы
[REFERENCING  {OLD   |   NEW}   [ROW AS] alias]
тело триггера

Триггер должен иметь имя. Событие в Cache — это исполнение инструкций INSERT, DELETE или UPDATE. Действие, которое выполняет срабатывающий триггер, может выполняться до вызвавшего события (триггер BEFORE) или после него (триггер AFTER).

Событие UPDATE имеет вариант UPDATE OF. После этих ключевых слов должен быть записан список столбцов, на обновление которых реагирует триггер.

Необязательное слово ORDER, после которого стоит натуральное число, позволяет задать порядок срабатывания нескольких триггеров, построенных для одной таблицы на одно и то же событие и на одно и то же время (BEFORE или AFTER). Триггеры с меньшим порядком (ORDER) срабатывают раньше.

Необязательная фраза REFERENCING позволяет задать алиасы для старых и новых значений. Их используют в разделе "действие" и в теле триггера. Для события INSERT можно работать только с новым значением, для DELETE только со старым, а для UPDATE и со старым и с новым.

Тело триггера может содержать необязательную фразу "WHEN условие", которая позволяет триггеру сработать, только если это условие выполняется. Необязательная фраза "LANGUAGE язык" допускает два варианта

LANGUAGE SQL или LANGUAGE OBJECTSCRIPT

По умолчанию выбирается SQL. В теле только такого триггера допустимы фразы REFERENCING, WHEN и UPDATE OF.

Удаляются триггеры командой

DROP TRIGGER имя_триггера FROM имя_таблицы

И в заключение пример триггера, который ничего не делает, а только сообщает, что он сработал.

В SQL в области USER создаём таблицу CREATE TABLE QQ (c1 CHAR(5)) В студии пишем программу, которая создаёт триггер и вставляет строчку в QQ (листинг 8.16).

DO $SYSTEM.Security.Login("_SYSTEM","SYS")
NEW SQLCODE
sql (CREATE TRIGGER TrigTestQQ AFTER INSERT ON SQLuser.qq LANGUAGE OBJECTSCRIPT
{W "I just fired the trigger",!}
)
W "SQLCODE создания триггера: ", SQLCODE,!
$sql(INSERT INTO SQLUser.qq VALUES ('hello'))
W "SQLCODE срабатывания триггера: ", SQLCODE,!
QUIT

Первая строка программы даёт пользователю привилегии, необходимые для работы со встроенным SQL. Тело триггера заключено в фигурные скобки.

SQLCODE — это код ошибки. Значение 0 означает успешное завершение SQL-инструкции, значение 100 —то же успешное завершение, но данные не найдены. Отрицательные значения SQLCODE это коды ошибок, которые можно найти в документации.

При первом запуске программы всё заканчивается благополучно (листинг 8.17)

USER>D ^tr
SQLCODE создания триггера: 0
I just fired the trigger
SQLCODE срабатывания триггера: 0

Триггер срабатывает, и ошибок нет. При повторном запуске программы появится ошибка —365 за счёт повторного создания триггера.

8.8 Что такое представление (VIEW)

Представления создаются инструкцией, похожей на инструкцию создания таблиц. Формат инструкции создания представления:

CREATE  [OR REPLACE]   [FORCE] VIEW имя_представления [(столбец    [, столбец])    ... ]
AS
запрос
[WITH READ ONLY]
[WITH  [LOCAL   |  CASCADED]  CHECK OPTION]

Запрос может строиться над несколькими таблицами. Фраза ORDER BY в нём не используется.

Фраза WITH READ ONLY означает, что через представление нельзя выполнять инструкции манипулирования данными. Фраза WITH [LOCAL | CASCADED] CHECK OPTION означает возможность манипулирования данными через представление. При указании LOCAL проверка ограничений целостности ведётся только для таблицы, на которой построено представление, а в варианте CASCADED ещё для связанных таблиц.

Представление — хранимый объект. Поскольку данные могут храниться только в таблицах, в базе хранится имя представления, текст запроса, образующего view, и, может быть, описания свойств представления.

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

Пример: Запрос данных через представление. Создадим преставление над таблицей emp:

CREATE OR REPLACE VIEW view_emp AS
SELECT ename, job, sal FROM emp WHERE deptno>10

Обратимся к нему с запросом:

SELECT ename,  job FROM emp WHERE job<>'CLERK'

Поскольку данные хранятся только в таблице emp, в действительности будет выполнен такой запрос:

SELECT ename, job FROM emp
WHERE   (deptno>10)  AND (job<>'CLERK')

Как он получен? Из двух наборов столбцов, определённых представлением (ename, job, sal) и запросом (ename, job), выбрано их пересечение (ename,job). Фильтр для выбора строк определяется двумя составляющими, взятыми из определения представления (deptno>10) и из фразы WHERE в запросе (job<>'CLERK'). Поскольку они должны работать оба, соединяем их связкой AND.

Пример: Вставка данных через представление. Попытаемся ввести строку в таблицу emp через представление view_emp. Однако попытка реализации вставки

INSERT INTO view_emp
VALUES  ('СИДОРОВ',   'ANALYST', 3000)

не удастся, так как она приводит к вставке в emp строки (NULL,'СИДО-РОВ', 'ANALYST', NULL, 3000, NULL, NULL, NULL). А первый NULL на месте ключевого столбца empno недопустим.

8.9 Встроенный SQL

Расширить возможности SQL можно встраивая его в процедурные языки общего назначения. Команды SQL помещают в тело программы вмещающего языка, выделяя специальными фразами, например exec sql в языках типа С и Java. В Cache ObjectScript фразы встроенного SQL имеют формат:

sql(фраза_sql)

Одна из основных проблем встроенных языков заключается в том, что ошибки могут быть обнаружены и во вмещающем и во встроенном языке. В стандарте SQL2 для анализа ошибок встроенного SQL используются стандартные переменные: SQLCODE (код ошибки), SQLERROR (сообщение об ошибке). В новых разработках, рекомендуется заменить их переменной SQLSTATE, состоящей из двух частей — двухсимвольного класса ошибки и трехсимвольного подкласса ошибки. В Cache для анализа ошибок встроенного SQL используется только переменная SQLCODE со стандартными значениями (0 — успех, или запись найдена, 100 — больше нет записей, число < 0 — ошибка).

8.9.1 Примеры со встроенным SQL

sql(CREATE TABLE qq (
c1 SMALLINT PRIMARY KEY,
c2 VARCHAR2(10), c3 VARCHAR2(30))) write  !,"Код ошибки:   ", SQLCODE

'QWE', 'Z')) sql(INSERT INTO qq VALUES  (2,   'АБВГД', 'ЕЖЗ'))

sql(SELECT * FROM qq WHERE c1=1)

sql(SELECT * INTO  :V_C1,   :V_C2, :V_C3
FROM qq
WHERE c1 = 1)
write !,"Код ошибки: ", SQLCODE
write !,"  Результат  "
write !,V_C1_" "_V_C2_" "_V_C3

8.10 Регулярные выражения

8.10.1 Преодоление атомарности

Мы уже отмечали (раздел 4.1), что в реляционной модели используемые типы просты, а значения с позиций модели данных атомарны, хотя в действительности они могут иметь некоторую структуру, недоступную в рамках модели.

В определении первой нормальной формы (радел 5.4) мы обратили внимание что существует непервая нормальная форма, в которой требуется наличие ключей, но допускается существование не атомарных атрибутов. Позднее станет ясно, что в использовании Н1НФ заключается возможность расширения моделей данных до объектных. Достаточно допустить векторные типы данных конструируемые пользователем.

Язык SQL также нарушает принцип атомарности данных. Например, запрос

SELECT ename FROM emp WHERE ename LIKE 'S%'

выбирает все фамилии, начинающиеся с буквы "S".

Правда, шаблоны поиска, помещаемые после слова LIKE, примитивны. Они представляют последовательность обычных символов и двух выделенных символов. Символ подчёркивания "_" означает точно один основной символ, а "%" задаёт любо количество основных символов.

Оператор "не число" (IS NAN) также разбирает операнд по символам.

Существует два пути преодоления ограничения атомарности:

Для работы со значениями данных имеющих внутреннюю структуру во всех возможных вариантах можно использовать регулярные выражения, регламентированные стандартами POSIX, которые, к сожалению, не всегда исполняются в полном объёме.

8.10.2 Что такое регулярные выражения

Простые варианты регулярных выражений вы уже встречали в DOS (помните шаблоны для поиска файлов типа *.doc?). В языке Cache ObjectScript используются шаблоны отмечаемые знаками ? или '?. Мы их не рассматривали из-за того, что с кириллицей они не работают.

Регулярные выражения — это один из возможных способов поиска подстрок (соответствий) в строках. Осуществляется это с помощью просмотра строки в поисках некоторого шаблона.

Типичные примеры использования регулярных выражений: проверка соответствия формату (например телефонного номера, IP-адреса, имени файла), обнаружение лишних пробелов, поиск HTML-тегов, замена полей в строке и многое другое. Самое простое регулярное выражение состоит только из символов, например, "cat". Этот шаблон из трёх букв найдётся в следующих строках: cat, location и Tomcat.

В состав регулярного выражения можно включать метасимволы. Перечислим некоторые из них.

Точка "." в регулярном выражении означает любой символ за исключением символов с кодами ASCII 10-13. Например, шаблон означает "два любых символа ". Строки "aa", "ab", "xv" и "rrrr" этот шаблон содержат.

Шаблон можно привязать к началу или концу строки. Метасимволов привязки два. Это циркумплекс обозначающий начало строки и доллар "$" обозначающий её конец. Например, шаблону "^a.b$", в котором символ "a" привязан к началу строки, а "b" к её концу, соответствуют строки "aab", "abb" или "axb".

А что делать, если метасимвол должен войти в шаблон как простой символ? Например, в конце образца должна стоять точка. Достаточно перед метасимволом поместить обратную косую черту. Так, "." это любой символ, но "\." это символ "точка". А "\[" это "[".

Шаблон "\w" задаёт любой алфавитно-цифровой символ на любом регистре или символ подчёркивания. Для обозначения непечатаемых символов применяют тот же приём. Так "\f" это перевод страницы (form feed), "\n" это перевод строки (line feed). "\r" обозначает перевод каретки (carriage return), "\t" это табуляция (tab), "\v" — вертикальная табуляция (vertical tab). В некоторых случаях возможна инверсия условия. Так, "\D" означает "не цифра", "\W" это все символы кроме определённых шаблоном "\w".

Для задания нескольких вхождений символа в шаблон применяются квантификаторы. Один из них "*" повторяет предшествующее вхождение ноль и более раз. Например, строка из любого числа любых символов, начинающаяся буквой "a" и заканчивающаяся буквой "b" задаётся регулярным выражением ""^a.*b$".

Перечисление задаётся вертикальной чертой разделяющей допустимые варианты. Так, "a|e" позволяет выбрать либо "a" либо "e".

Группировка выполняется путём заключения части шаблона в круглые скобки. Например, шаблон "gray|grey" можно переписать, используя группировку, как "gr(a|e)y".

В одиночных квадратных скобках задаются классы символов. При каждом применении шаблона используется один из символов класса. Например, конструкция "gr[ae]y" даёт тот же результат, что шаблон "gr(a|e)y". Шаблон "\w" эквивалентен "[a-zA-Z0-9_ ]".

В классах можно указывать символы, которых не должно быть в найденной подстроке. Так, шаблон "["1-6]" находит все символы, кроме цифр от 1 до 6. Заметим, что дефис в середине текста класса означает диапазон, но дефис в первой позиции это просто символ дефис. Шаблону "\W" соответствует "[^a-zA-Z0-9_]]".

Кроме того, в список символов, обозначаемый квадратными скобками, может входить класс символов POSIX заключённый в свои квадратные скобки. Например, "[[:alnum:]]" обозначает один символ из класса алфавитно-цифровых символов, "[[:lower:]]" определяет один символ в нижнем регистре, а "[[:lower:]]{3}"" —три символа в нижнем регистре. Приведём пару полезных примеров:

На этом остановимся. Рассмотренных средств достаточно для решения простых задач, требующих использования регулярных выражений. Конечно, многие достаточно сложные конструкции (контекст, жадность алгоритма, юникод и т.д.) мы не затрагивали. Но ведь наша задача предельно ограничена — показать возможность преодоления ограничения атомарности.

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

Заметим, что большие регулярные выражения довольно сложно писать и отлаживать. Ещё труднее разбирать сложные чужие шаблоны. Одна из неприятных особенностей регулярных выражений в том, что изменение одного символа часто приводит не к сообщению об ошибке, а к появлению трудно понимаемого результата.

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

8.10.3 Регулярные выражения в базах данных и Web-клиентах

В Cache

Для работы с регулярными выражениями в Cache, начиная с версии 2012.2, в язык ObjectScript введены функции $LOCATE() и $MATCH(), а в классе %Regex.Matcher добавлены соответствующие методы.

Формат функции $LOCATE():

$LOCATE(строка,рег_выр[,начало][,конец][,значение])

Обязательны только первые два аргумента.

Функция $LOCATE() обнаруживает наличие шаблона "рег_выр" в строке и возвращает в виде целого числа позицию первого вхождения шаблона. Счёт позиций начинается с 1. Если шаблон не найден, вернётся 0. Аргумент "начало" указывает позицию, с которой начинается просмотр строки.

Если вхождение найдено, то аргументу "конец" присваивается номер позиции, следующей за концом найденного вхождения шаблона. Это позволяет в цикле найти все вхождения шаблона.

Атрибут "значение" отмечает, было ли найдено хотя бы одно вхождение шаблона.

Функция $MATCH() с булевым значением обнаруживает саму возможность применения шаблона. Ограничиться столь бедным набором функций удалось потому, что изначально в COS уже имелись средства для работы со списками и строками с разделителями.

В Oracle

Для работы с регулярными выражениями в Oracle существуют четыре функции: REGEXP_LIKE(), REGEXP_INSTR(), REGEXP_SUBSTR() и REGEXP_REPLACE().

Функция

REGEXP_LIKE(стpoкa, рег_выр [, параметр_сопоставления])

используется подобно оператору LIKE во фразе WHERE и в определениях ограничений на таблицу. Параметр_сопоставления позволяет использовать дополнительные параметры, такие как символ перехода на новую строку, многострочное форматирование и обеспечение управления учетом регистра. Функция

REGEXP_INSTR(стpoкa, рег_выр[, начало [, вхождение [, опция_возврата [, параметр_сопоставления ]]]])

Функция подобно INSTR() возвращает позицию символа, находящегося в начале или конце соответствия для шаблона. Атрибут "вхождение" по умолчанию равен 1, но может быть указан поиск последовательных вхождений. Если атрибут "опция_возврата" равен 0, то возвращается начальная

позиция найденного вхождения шаблона, если 1, то позиция символа, следующего за шаблоном.

В отличие от INSTR() функция REGEXP_INSTR() работает только вперёд от начала строки.

Функция

REGEXP_SUBSTR(HCxoflHaH_CTpoKa, шаблон[, позиция [,  вхождение  [,параметр_сопоставления]]])

возвращает подстроку, которая соответствует шаблону. Функция

REGEXP_REPLACE(исходная_строка, шаблон [,  строка_замены[, позиция [,  вхождение,   [параметр_сопоставления]]]])

заменяет все вхождения шаблона во входной строке на значение, указанное в атрибуте "строка_замены".

Web-интерфейс

Проще всего продемонстрировать регулярные выражения в языке Java- Script. Скопируйте контейнер <script > </script>, приведенный ниже на рисунке, в текстовый редактор, например, WordPad. Сохраните файл как текстовый с расширением .html и откройте его любым браузером. В его окне появится фраза "Регулярные выражения" (рисунок 8.6).

Обратите внимание на то, что шаблон, состоящий из единственной кириллической буквы "р" задан не совсем стандартным способом.

8.11 Хранение деревьев и сетей в таблицах

Для работы с деревьями необходимо вводить в SQL рекурсию, либо использовать процедурные расширения языка.

Простейшая разметка, позволяющая хранить дерево в одной таблице, была рассмотрена на примере таблицы emp. Однако, для полноценной работы с деревьями необходимо ещё реализовать такие действия, как удаление, добавление ветвей, поиск в глубину и ширину и другие. Необходимо работать с лесами деревьев. Поэтому используются другие способы моделирования деревьев, в том числе двухтабличные.

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

В последние годы пропагандируется подход к СУБД, при котором необходимо не моделировать одни структуры данных в других, а реализовывать каждую модель данных непосредственно, добиваясь максимальной эффективности.

Подробнее с представлениями деревьев и сетей в SQL можно познакомиться в книге Джо Селко "SQL для профессионалов. Программирование". М.: "Лори", 2004.

8.12 Многомерные данные в SQL

Выясним, что такое многомерные данные, где они используются и почему так важны. Может показаться странным, но многомерными данными всегда оперируют бухгалтеры и экономисты, даже если они сами не знают об этом. Когда говорят, скажем, о прибыли в разрезе филиалов и видов деятельности, имеются в виду именно данные, представляемые многомерными параллелепипедами. В нашем примере имеется один показатель "прибыль" и, по крайней мере, две координатных оси "название филиала" и "вид деятельности". Не оговорена, но заведомо предполагается третья ось. Назовём её "период времени". В соответствии с традицией используем термин "показатель" (measure), координатные оси будем называть измерениями (dimension), а конкретный набор значений измерений — фактом.

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

Показатели представляют собой функции многих переменных (измерений). Их можно представлять многомерными (n=1, 2, 3, ...) параллелепипедами, которые, видимо для благозвучия, принято называть гиперкубами.

Понятно, что координатные оси могут существенно отличаться от физических величин. Могут использоваться и количественные характеристики, измеренные в различных шкалах, и качественные характеристики (теоретическая модель — решётка), и просто наименования (в теории — измерения в шкале порядка).

Введя в язык SQL средства для работы с многомерными данными, мы позволяем решать в нём задачи анализа деятельности систем. В важности этого класса задач сомневаться не приходится.

8.12.1 Откуда в табличной модели многомерные данные и как с ними работать

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

Более точно, любая реляционная таблица с ключом и с дискретными доменами столбцов может считаться представлением гиперкуба. Ключевые столбцы представляют измерения, не ключевые — показатели. В примере, приведенном в таблицах 8.9 и 8.10, реляционная таблица с двумя ключевыми столбцами "Год" и "Товар" образует двумерный гиперкуб, а столбец "Продано" — показатель.

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

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

Проблемы адресации

При переходе к многомерному представлению меняется способ адресации данных. Понятно, что для выбора одной ячейки гиперкуба $$m(d_1,d_2,\dots,d_n)$$ достаточно задать соответствующий факт, то есть набор значений всех измерений $$d_1,d_2,\dots,d_n$$.Тут вроде бы ничего нового — чтение по заданному значению ключа. Допуская произвольные значения для $$s$$ координат ($$1<s<n$$), задаем гиперкуб размерности $$п — s$$, называемый обычно срезом. Ограничивая значения некоторых координат, получаем подкуб с тем же числом измерений.

Для задания областей сложной формы, в том числе многосвязных, необходимо определять принадлежность фактов размерности $$n$$ или меньшей к некоторому списку. Нетрудно догадаться, что иногда такой список может быть не известен заранее и его придётся формировать специальным подзапросом.

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

Теперь можно перейти к реализации многомерной модели на примере СУБД Oracle 10-й или 11-й версий. Можете, зайдя на сайт книги, установить Oracle XE и пользуясь имеющимися на сайте материалами выполнить все последующие примеры. Но лучше отложить конкретную работу до изучения раздела 10.3 "Объектно-реляционная модель данных Oracle".

Сейчас нам важно понять, как был изменён синтаксис SQL для работы с многомерными данными. На не менее важный вопрос: "На какой модели данных построен SQL?" мы ответим в конце главы.

8.12.2 Конструкция MODEL

Конструкция MODEL приписывается к запросу, подготавливающему исходные данные для многомерной модели. В сильно упрощённом виде синтаксис выглядит так:

<инструкция SELECT> MODEL
DIMENSION BY  (<список_столбцов >) MEASURES  (<список_столбцов >) [RULES (список_правил)]

Фраза DIMENSION BY определяет размерности (то есть координатные оси) гиперкуба, одну или более. Фраза MEASURES задаёт измеряемые величины. Их может быть от одной и более. В секции RULES помещается множество правил, может быть пустое.

Простейший пример одномерного куба над таблицей emp выглядит так:

SELECT empno, ename FROM emp t MODEL
DIMESION BY (empno) MEASURES (ename)
RULES () ORDER BY empno;

В нём:

Результат работы, как и следовало ожидать, тривиальный (таблица 8.11).

В секции MEASURES можно записывать константы и выражения. Пример:

SELECT empno, ename, sal, date_now
FROM emp
MODEL
 
DIMENSION BY (empno)
MEASURES (ename, sal * 100 as sal, sysdate as date_now)
RULES ()
ORDER BY empno;

8.12.3 Правила и адресация многомерных данных

Правила из секции RULES позволяют изменять любые значения показателей. Каждое правило состоит из левой части, определяющей ячейку или группу ячеек и соединённой с ней знаком присваивания (=) правой части. В правой части могут использоваться выражения, содержащие ячейки массива, литералы, функции языка SQL. Ячейки адресуются позиционно или символьно. Например, для функции sales, зависящей от prod и year, позиционная адресация sales['Book', 2011], а символьная sales[prod='Book', year=2011]. От способа адресации зависит обработка NULL^. Позиционная адресация позволяет обратиться к ячейке с NULL, а символическая нет.

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

Функция cv() в правой части присваивания дает доступ к текущему значению координаты (dimension).

Пример (создание показателя, которого нет в исходных данных):

SELECT * FROM emp MODEL
DIMENSION BY (empno)
MEASURES (job,ename, 0 sub_empno)
RULES (sub_empno[any] = cv(empno)  * 10
)
ORDER BY empno;

Результат запроса в таблице 8.12.

Пример более сложных правил в одномерном гиперкубе:

SELECT empno, job, ename FROM emp MODEL
DIMENSION BY (empno) MEASURES (job, ename) RULES(
job[7839] ='Boss',
job[empno <> 7 839] = 'Employee',
ename[empno BETWEEN 7369 and 7 4 99 ] =
 
INITCAP(ename[CV(empno)])) order by empno;

Обратите внимание, ename[7839] указывает адрес ячейки в одномерном массиве. В остальных правилах задаются диапазоны. Структура правил в последнем примере:

Условные выражения определяют множество значений, например, ename[empno < 7788] или ename[hiredate between 1999 and 2000].

Задание списка возможных значений может использовать следующие конструкции:

В последнем случае при каждом повторе цикла "значение1" увеличивается либо уменьшается на "значение3", до тех пор пока не достигнет "значение2".

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

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

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

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

8.13 Шаблоны запросов языка SQL и сложные запросы

Запросы SQL можно представлять построенными в шаблонах, создаваемых на некотором наборе основных шаблонов. Сразу оговоримся, что эти шаблоны никакого отношения к регулярным выражениям не имеют. Конечно, вводимое представление не отменяет и не заменяет синтаксис языка. Речь идёт о восприятии запросов человеком.

8.13.1 Структура шаблонов SQL

В предыдущих разделах мы изучали синтаксис для некоторых типов запросов. Обратим внимание на то, что каждая такая запись представляет шаблон, состоящий из служебных слов, предусмотренных заранее и записанных в определённом порядке, и заполняемых полей. Мы говорили о том, что запрос представляет набор фраз.

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

SELECT DISTINCT {*  |{столбец|константа [псевдоним]}, ... }
FROM {таблица,    ... } WHERE условие(я)

состоит из трёх расположенных последовательно фраз, помеченных метками (лейблами) SELECT DISTINCT, FROM и WHERE. За каждой меткой следует поле, предназначенное для ввода информации пользователя, своей для каждого конкретного запроса.

Обратите внимание на то, что для транслятора SQL любая инструкция это строка, а для человека удобнее двумерное графическое представление, в котором фразы как-то выделены и структурированы.

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

Выделим два типа шаблонов —простой и рекурсивный (рисунок 8.7). Простой шаблон состоит из текстовых полей меток, обозначенных на рисунке буквой "л", и полей заполнения, помеченных "п". В рекурсивном шаблоне часть его конструкции может быть повторена. Рекурсивное употребление самого шаблона задается правилами сочетания шаблонов.

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

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

8.13.2 Классификация запросов

Чего можно добиться, представляя сложные запросы с помощью структурированных шаблонов? Во-первых, создание классификации запросов. С помощью графического представления сделаем её удобной для восприятия человеком и потому обозримой. Во-вторых, и это главное, построим систему правил, позволяющую быстро писать любые запросы. Заметьте, я не говорю об алгоритме написания запросов, потому, что многие из предлагаемых правил трудно формализуемы, содержат исключения и неопределённости, а пути решения могут выбираться неоднозначно. Тем не менее, они позволяют навести некоторый порядок.

Выделим три основных класса запросов (рисунок 8.8):

Сразу уточняем приведённые понятия. Прежде всего, "запрос без подзапросов" включает в себя три категории:

Дальнейшая детализация запросов без подзапросов приведена на рисунке 8.9. Все приведенные на нём разновидности запросов вам уже известны. Лучше будет, если вы внимательно рассмотрите рисунок и по тем видам запросов, которые вы подзабыли, вернётесь к предыдущим разделам.

Перейдём к запросам с подзапросами. Вы, конечно, помните, что любые подзапросы могут помещаться во фразы FROM, WHERE и HAVING (рисунок 8.10). Однако, коррелированные подзапросы могут находиться только во фразах SELECT, WHERE и HAVING. Дело в том, что фраза FROM в запросе обрабатывается первой и постоянные переходы между таблицами, характерные для этого типа подзапросов не соответствуют назначению фразы FROM.

Заметим, что, как правило, транслятор позволяет писать обычные подзапросы во фразе SELECT. Но как-то трудно обосновать полезность результата со столбцом, заполненным одинаковыми значениями.

Шаблоны фраз SELECT, FROM, WHERE и т.д. контекстны. Иначе говоря, они зависят от структуры основного шаблона и, может быть, друг от друга. Поясним это свойство на примере первых семи основных шаблонов. Будем обозначать шаблон первыми буквами ключевых слов инструкции SELECT, например, SF это имя шаблона инструкции SELECT . . . FROM . . .

В первую группу шаблонов входят SF, SFO, SFW и SFWO. Во второй группе SFGO, SFWG и SFWGO.

Шаблоны отличаются не только синтаксисом, но и семантикой. Так минимальный шаблон SF имеет смысл, который можно описать так: "выборка всех строк таблицы/декартова произведения таблиц с вырезанием указанных столбцов, добавлением вычисленных столбцов и столбцов-констант и, возможно переименованием столбцов результата".

В семантику шаблона SFW следует внести "выбор строк" и "соединение таблиц, если их больше одной".

Шаблоны с фразой ORDER BY, кроме прочего, упорядочивают выходной набор.

Шаблоны с фразой GROUP BY дополнительно группируют строки и вычисляют итоговые значения.

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

На самом деле для шаблонов первой группы (и в Cache, и в Oracle) можно использовать ещё групповые функции, но к определению смысла запроса в этом случае следует добавить, что создаваемая группа строк единственная.

Фраза FROM для обеих групп шаблонов содержит список имён таблиц или представлений, разделяемых запятой и, может быть, снабжённых псевдонимами, приписываемыми к именам таблиц через пробелы. Может содержать подфразу, определяющую соединение таблиц (INNER JOIN, OUTER JOIN и т.д.)

Фраза WHERE для обеих групп шаблонов содержит логические выражения, использующие операторы (IN, BETWEEN, LIKE и др.) и заданные на именах столбцов, константах и однострочных функциях от этих операндов. Эти выражения определяют условия выбора строк, и, может быть, условия соединения таблиц. В случае самосоединения использование квалифицированных имён обязательно. Использовать групповые функции во фразе WHERE нельзя, так как предполагается отбор строк, но не групп строк.

Условия соединения во фразах FROM и WHERE в одном запросе не совместимы.

Фраза ORDER BY для обеих групп шаблонов содержит разделённый запятыми список имён столбцов или формул, построенных на этих столбцах и константах.

Фраза GROUP BY для второй группы шаблонов содержит список столбцов по которым производится группирование. Использование агрегатных функций запрещено.

Обратим внимание на связь между фразами SELECT и GROUP BY. Включение столбцов, по которым производится группирование во фразу SELECT не обязательно, но их отсутствие делает ответ малоинформативным.

Расширим систему шаблонов, приведенную на рисунке 8.9 добавив условие отбора групп.

Фраза HAVING обеспечивает отбор групп строк. Содержит условия, которые обязательно должны использовать групповые функции. Без них фраза определяет условие отбора строк, а не их групп и потому может быть заменена условием во фразе WHERE. Предполагается использование HAVING вместе с фразой GROUP BY.

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

Вы сейчас находитесь в положении молодого Самюэля Клеменса (Марк Твен), когда от лоцмана Биксби он узнал, что должен помнить все населённые пункты на всей реке Миссисипи. Как вы помните, Биксби успокоил новичка, сказав "Ты парень не беспокойся. Раз я за тебя взялся, я тебя либо убью, либо выучу".

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

8.13.3 Правила построения запросов

Работа с заданиями на естественном языке

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

Всё так, у нас не перевод художественного текста (как там, на английском "Немь лукает луком немным в закричальности зари"). Но, все-таки, мы исходим из текста на естественном языке. Конечно, никто не станет начинать задание на запрос так: "Не будет ли Вам благоугодно предоставить сведения о . . . ". Но, тем не менее, подмножество естественного языка, достаточное для написания заданий на составление инструкций SQL, и не требующее изучения человеком никто не определил.

Конечно, можно потребовать обязательного пользования некоторой тер-миносистемой (кто бы её создал) отсутствия во фразе задания метафоричности, расплывчатости, неоправданного использования синонимов и т.д. Но, по-видимому, нельзя запретить задание несколькими фразами, использование названий объектов базы на естественном языке, использование умолчаний, чрезмерно общих понятий ("выбрать сотрудников, у которых . . . ") и прочие прелести.

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

Попытаемся выполнить такое задание: "Начислить заработную плату за январь 1982 года". Если вспомнить, что процесс начисления заработной платы требует исходить из отсутствующих у нас (в схеме scott) документов, подтверждающих выполнение работы и зависящих от принятого способа оплаты, что перед выдачей ведомости на оплате необходимо рассчитать подоходный налог, и т.д., то задачу следует признать неразрешимой в имеющейся у нас базе. Теперь уместно спросить, а вообще, какой смысл имеет таблица emp. Очевидно, это всего лишь набор записей о приёме на работу. Запись об увольнении сделать невозможно. Нет соответствующего столбца, какого-нибудь firedate. Поэтому работник, если верить таблице emp, никогда не увольняется, даже в случае смерти. Эдакий список лиц допущенных к работе посмертно. Правда, можно просто стереть запись об уволенном сотруднике. Но как тогда ответить на вопрос: "Работал ли X в марте 1981 года?". А что тогда означает уникальность табельного номера? Если сотру дника уволить, стерев запись о нём, то при повторном приёме, кто вспомнит его табельный номер, который, кстати, может быть уже занят. Невозможно перевести сотрудника на другую должность, так как нет столбца "Дата перевода".

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

Выяснение семантики всегда требует значительного объёма работы и хорошего знания бизнеса.

Если задание состоит из нескольких фраз, необходимо установить связи между ними. Лингвисты говорят об анафоре, когда существует связь вперед и катафоре для связи назад.

Примеры заданий:

Очевидно, оба задания должны быть трансформированы к следующему виду:

"Выбрать фамилии и должности сотрудников, удовлетворяющих следующим условиям: 
место работы — отделы 20 и 30, зарплата выше средней по своему отделу."

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

Свойства шаблонов

Для того, чтобы различать шаблоны построим систему их свойств, обладающую тремя свойствами:

На верхнем уровне классификации мы уже выделили три класса шаблонов:

Существование вложенных структур можно выявить из спецификации семантики таблиц и по некоторым особенностям задания. Например, для иерархических структур это указания на подчинённость. Работа со структурой, не предусмотренной семантикой данных возможна, но бессмысленна. Пример: иерархия образующая дерево по столбцам "рост" и "вес" в таблице "пациенты".

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

Вспомним, что к запросам без подзапросов мы отнесли объединения нескольких запросов с помощью операций над множествами (UNION и др.). В задании для таких запросов должно быть одно из двух:

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

Если же во всех объединяемых запросах таблица одна, столбцы совпадают и нет группировок, то эта конструкция сводится к одному запросу со сложным условием во фразе WHERE.

Перечисленных свойств достаточно для выделения классов верхнего уровня.

Теперь разберёмся с одиночным запросом к одной таблице. Простейший шаблон SF реализуется, если необходимо выбрать некоторые столбцы для всех строк таблицы. Если же в задании обнаружено условие отбора строк, используется шаблон SFW.

Если необходимо упорядочить результат по каким-то его столбцам, переходим к шаблону SFO или SFWO.

Если необходимо считать итоговые результаты по группам, то группы выделяются фразой GROUP BY, а во фразе SELECT используются многострочные функции. Группирование и упорядочение могут существовать в одном запросе.

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

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

Признак коррелированного подзапроса: данные подзапроса должны быть свои для каждой строки основного запроса.

Признак обычного подзапроса: данные подзапроса пригодны для всех строк основного запроса.

Следует помнить, что обычные подзапросы следует использовать во фразах FROM, WHERE и HAVING, а коррелированные только во фразах SELECT, WHERE и HAVING. Дело в том, что обработка основного запроса начинается с фразы FROM и повторно вернуться к ней уже нельзя.

Приступаем к написанию запроса

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

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

На первом этапе, используя описанную выше систему признаков, выясняем, к какому из трёх основных классов относится запрос: без подзапросов, с подзапросами или запрос с учётом вложенных структур. Не забываем, что запросы без подзапросов включают в себя одиночные запросы к одной или нескольким таблицам и объединения результатов нескольких запросов как множеств.

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

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

Насколько мне удалось выяснить, в практике работы со сложными информационными системами глубина вложенности подзапросов больше трех не применяется. Число вложенных подзапросов на один основной запрос обычно не превышает 5-7.

Что делать, если не удаётся пройти этот путь до конца? Останавливайтесь и пытайтесь выяснить любые подробности. Впоследствии они вам пригодятся. Затем, уже с уточнённым восприятием задания, пытайтесь продолжить работу.

К сожалению, мы освоили только небольшую часть современного языка SQL. Мы не изучали великое множество функций, в том числе аналитических. Фраза GROUP BY нами освоена в простейшем варианте. Существуют опции GROUP BY CUBE, GROUP BY ROLLUP, используются множества группирования (GROUPING SETS) и т.д. Мы не изучали фразу WITH, позволяющую вынести подзапросы в отдельную секцию помещаемую перед фразой SELECT. Многомерная модель нами только намечена.

Тем не менее, предложенный подход к написанию запросов распространяется на весь язык SQL.

8.14 На каких моделях построен SQL

Вернёмся к первому эпиграфу восьмой главы и постараемся понять, так ли достойны сожаления отступления SQL от реляционной модели.

Шаблон запроса, в точности соответствующего запросу исчисления на кортежах, и не содержащего никаких функций, рассмотрен в разделе 8.13.1. Даже используя результат запроса как новую таблицу, получаем слишком узкий класс запросов. Так что мир исчисления на кортежах вплетён в табличный мир, в котором можно включать в запрос процедурные элементы, использовать регулярные выражения, упорядочивать строки, группировать их, вычисляя агрегатные функции. Можно использовать аналитические функции, в таблицах можно хранить какие-то структуры данных, можно работать с широким классом подзапросов, не выразимых в реляционном мире, и т.д.

Мы прикоснулись к части мира многомерных данных (в разделе 8.12), сплетённого с табличным миром и поняли принципиальную возможность создания других подобных миров.

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

Так что не стоит жалеть об упущенном реляционном счастье.

Что же касается дальнейших расширений, то мы подозреваем их пришествие, но по совету Яджнявалкьи из Брихадараньяка-упанишады не говорим о них слишком много.