Базы данных. Лекции БД. Лекция 5 Основные понятия информационных систем 5 История развития компьютеризации информационных процессов и систем. 5
 Скачать 1.07 Mb.
|
Лекция 7.8.4.3.Базовые таблицыБазовые таблицы SQL имеют ряд отличий от отношений реляционной теории. Во-первых, в таблицах SQL, в отличие от настоящих отношений, допустимы идентичные строки, поэтому им не требуется иметь каких-либо потенциальных ключей. Во-вторых, в таблицах SQL столбцы рассматриваются в порядке слева направо. Базовые таблицы определяются с помощью оператора CREATE TABLE. Синтаксис выражения следующий: CREATE TABLE base-table (base-table-element-commalist) Здесь каждый элемент базовой таблицы (base-table-element) является либо определением столбца, либо определением ограничения базовой таблицы. Каждое определение столбца (по крайней мере, одно такое определение должно быть) выглядит следующим образом: Column representation [default-definition] Здесь column означает название столбца, representation указывает необходимый тип данных или домен, а необязательное определение по умолчанию (default-definition) указывает значение по умолчанию для столбца, игнорирующее значение по умолчанию, указанное на уровне домена (если такое имеется). Значение по умолчанию имеет вид: DEFAULT { Если для данного столбца не определено значение по умолчанию, а также нет такового, наследуемого из домена, то предполагается, что значение по умолчанию – NULL. Существует три вида определений ограничения базовой таблицы: Определение потенциального ключа; Определение внешнего ключа; Определение «проверочного условия». Каждому ограничению может предшествовать инструкция вида «CONSTRAINT constraint», указывающая название для нового ограничения (то же самое верно и для ограничений доменов). Однако для краткости будем опускать эту опцию. Итак, ограничения столбца описывается следующими синтаксическими правилами: NOT NULL | [ | | CHECK ( Рассмотрим более подробно каждое из ограничений. 8.4.3.1.Потенциальные ключиОпределение потенциального ключа записываются в виде. UNIQUE (column-commalist) или PRIMARY KEY (column-commalist) В обоих случаях список (column-commalist) не должен быть пустым. Для данной базовой таблицы существует не более одной спецификации PRIMARY KEY (первичный ключ) и любое количество спецификаций UNIQUE (альтернативные ключи). В случае первичного ключа для каждого из указанных столбцов дополнительно подразумевается спецификация NOT NULL, даже если эта спецификация не указана явно. 8.4.3.2.Внешние ключиОпределение внешнего ключа записывается следующим образом: FOREIGN KEY (column-commalist) REFERENCES base-table [(column-commalist)] [ON DELETE option] [ON UPDATE option] Здесь option принимает значения NO ACTION, CASCADE, SET DEFAULT или SET NULL. Опция NO ACTION устанавливается по умолчанию и идентична описанной ранее опции RESTRICT. CASCADE и SET NULL соответствуют описанным ранее каскадному удалению (обновлению) и установлению неопределенных значений (NULL) в поле внешнего ключа. Заметим, что второй список столбцов требуется, если внешний ключ ссылается на потенциальный, который не является первичным ключом. 8.4.3.3.Проверочные условияОпределение проверочного условия (проверочного ограничения) имеет вид: CHECK (conditional-expression) Попытка создания строки в базовой таблице В рассматривается как нарушение проверочного условия для таблицы В, если в результате вычисления условного выражения, указанного в этом ограничении, получено значение ложь. Фактически не требуется, чтобы условие ограничения имело ссылки только на таблицу В, оно может иметь ссылки на что угодно в базе данных. Приведем пример создания таблицы CREATE TABLE sp (S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL, PRIMARY KEY (S#, P#), FOREIGN KEY (S#) REFERENCES S ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (P#) REFERENCES P ON DELETE CASCADE ON UPDATE CASCADE, CHECK (QTY > 0 AND QTY <5001)); Здесь подразумевается, что домены S#, P# и QTY уже определены, а S# и P# явно определены как первичные ключи для таблиц S и Р соответственно. Также здесь применяется сокращение по соглашению, благодаря которому проверочное условие вида CHECK (column IS NOT NULL) в определении рассматриваемого столбца можно заменить простой спецификацией NOT NULL. 8.4.3.4.Изменение базовой таблицы.Далее существующая базовая таблица может быть изменена в любое время с помощью оператора ALTER TABLE. Поддерживаются следующие изменения: Добавление новых столбцов; Определение для существующего столбца нового значения по умолчанию (заменяющего предыдущее значение, если оно было); Удаление для столбца существующего значения по умолчанию; Удаление существующего столбца; Указание нового ограничения целостности для базовой таблицы; Удаление существующего ограничения целостности для базовой таблицы. Команда имеет следующий синтаксис: ALTER TABLE <имятаблицы> { ADD [COLUMN] определение столбца} | {ALTER [COLUMN] <имя столбца> <изменяющее действие>} | {DROP [COLUMN] имястолбцаRESTRICT | CASCADE } | {ADD определение ограничения для таблицы} | {DROP CONSTRAINT имяограниченияRESTRICT | CASCADE }; изменяющее действие ::= { SET DEFAULT <значение по умолчанию> } | {DROP DEFAULT} Если при добавлении нового столбца таблица не пуста, то этот столбец будет заполнен значениями по умолчанию (для каждой строки таблицы). При этом подразумевается, что должно существовать значение по умолчанию для добавляемого столбца. Если таковое отсутствует, то берется значение по умолчанию для домена, и, наконец, если последнее отсутствует, принимается значение NULL. Если при добавлении столбца таблица не пуста и столбец имеет ограничение типа NOT NULL, то этот столбец (или домен) должен иметь какое-нибудь значение по умолчанию. DROP COLUMN имеет ограничения: уничтожаемый столбец должен быть не единственным в таблице. (если необходимо уничтожить таблицу, следует воспользоваться оператором DROP TABLE). Если в предложении DROP задан параметр RESTRICT, то в этот момент столбец не должен использоваться как родительский ключ для какого-нибудь внешнего ключа. Если задан параметр CASCADE, то внешние ключи, которые имеют ссылки или ограничения типа FOREIGN KEY, будут также уничтожены. Предупреждение: при использовании CASCADE в предложении DROP COLUMN представления, которые имею ссылки на столбец, не будут модифицироваться; они будут уничтожены. Пример добавления нового столбца: ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT –1; С помощью этого оператора добавляется столбец DISCOUNT (типа INTEGER) в базовой таблице поставщиков S. Все существующие строки в этой таблице расширяются с четырех столбцов до пяти; во всех случаях значение нового пятого столбца равно –1. Наконец, существующая таблица может быть уничтожена с помощью оператора DROP TABLE, который имеет следующий синтаксис: DROP TABLE base-table option; Здесь, как и в случае доменов, опция option будет или RESTRICT, или CASCADE. Если указана опция RESTRICT и на базовую таблицу есть ссылки в каком-нибудь определении представления или ограничения целостности, операция DROP будет отвергнута. Если указана опция CASCADE, то операция будет выполнена (таблица будет удалена со всеми ее строками), а любые определения представлений, которые имеют ссылки на эту таблицу, и ограничения целостности будут также уничтожены. 8.4.4.ИндексыИндекс представляет собой упорядоченный список значений одного или нескольких столбцов; в индексе содержатся дисковые адреса значений включающих строк. Индекс атрибута А – это структура данных, обеспечивающая эффективный поиск кортежей, имеющих фиксированное значение для атрибута А. Индексы, как правило, полезны в запросах, в которых атрибут А сравнивается с константой, например, а =3 или даже А <= 3. при очень большом отношении дорого сканировать все его кортежи в поиске кортежей, удовлетворяющих заданному условию. Рассмотрим пример запроса: SELECT * FROM фильмы WHERE студия = 'Disney' AND год = 1999; Здесь может быть 10 тысяч кортежей «фильмы», из которых только 200 фильмов выпущены в 1999 году. Примитивный способ обработки этого запроса состоит в проверке условия пункта WHERE на каждом из 10 тыс. кортежей. Гораздо эффективнее как-нибудь найти 200 кортежей, относящихся к 1999 г., а затем проверить есть ли в них студия Disney. Еще лучше – сразу найти только десяток кортежей, удовлетворяющих обоим условиям пункта WHERE, но это уже превышает возможности обычных структур данных. Индексы – средство SQL, которое не определяется в стандарте, однако они весьма полезны и используются в большинстве коммерческих систем. Синтаксис команды создания индекса обычно выглядит так: CREATE INDEX <имя индекса> ON <имя таблицы> (<имя столбца> [,<имя столбца >]..) Таблица к этому моменту должна быть уже создана и содержать столбцы, имена которых указаны в команде. Имя индекса должно быть уникальным в базе данных. Будучи однажды созданным, индекс является невидимым для пользователя базы данных. SQL сам решит, когда есть смысл воспользоваться индексом, и сделает это автоматически. Например, если создать атрибут на поле «год» из предыдущего примера, то в дальнейшем процессор запросов SQL может обрабатывать запросы относительно года так, что будут проверяться только те кортежи отношения «фильмы», в которых есть указанный год: CREATE INDEX YearIndex ON фильмы(год); Как видно из синтаксиса оператора в SQL допустимы многоатрибутные индексы с несколькими переменными, позволяющие эффективно находить кортежи с заданными значениями этих переменных. Если «название» и «год» образуют ключ для отношения «фильмы», значит определены значения обоих этих атрибутов, либо не определено ни одно из них. Типичное описание индекса этих атрибутов: CREATE INDEX KeyIndex ON фильмы(название, год); Поскольку (название, год) – ключ, при заданных названии и годе выпуска фильма индекс укажет единственный требуемый кортеж. Напротив, если запрос определяет и год, и название, но при этом доступен лишь индекс YearIndex, система может найти только все фильмы заданного года выпуска, а затем искать среди них нужное название. Лучше иметь индекс на одном атрибуте «название», чем на одном атрибуте «год», так как обычно в один год выпускается множество фильмов, а фильмы с одинаковым названием встречаются крайне редко. В данном примере поиск всех заданных названий фильмов с последующей их проверкой по заданному году требует не намного больше времени, чем применение многоатрибутного индекса для «названия» и «года». 8.4.4.1.Уникальные индексыДля индекса YearIndex из предыдущего примера уникальность не нужна. В данном году может быть выпущено любое количество фильмов. Это становится неприемлемым, если ключевое слово UNIQUE используется перед ключевым словом INDEX. Поле первичного ключа, может быть первым кандидатом на создание уникального индекса: CREATE UNIQUE INDEX Custid ON Customers(cnum); Эта команда будет отвергнута, если в поле cnum имеются одинаковые значения. Наилучший способ работы с индексами – немедленное их создание после создания таблицы и перед занесением в нее значений. Уникальный индекс, создаваемый для более чем одного поля, требует, чтобы комбинация значений во всех столбцах была уникальной. Заметим, что во многих реализациях реляционной СУБД индекс по первичному ключу создается автоматически (естественно, уникальный) при определении первичного ключа в силу исключительной важности в плане эффективности применения ограничения по ключу. Действительно, если ограничение по ключу применяется в отсутствие индекса на ключевых атрибутах, то при поиске кортежа с таким же ключевым значением, что и у добавляемого (или изменяемого) кортежа, система должна просматривать все отношение. Такой процесс требует очень много времени и может сделать практически невозможной модификацию БД с большими отношениями. 8.4.4.2.Удаление индексовОсновная причина именования индексов состоит в их удалении время от времени. При исключении индекса используется такой синтаксис: DROP INDEX <имя индекса> Удаление индекса не изменяет содержимого полей. 8.4.5.Глобальные ограниченияОграничения CHECK могут быть применены при определении поля в таблице, например, Gender CHAR(1) CHECK (gender IN (‘F’, ‘M’)), Такое ограничение называют основанным на атрибуте, поскольку проверка ограничения связана только с рассматриваемым полем, а не с каждым отношением или атрибутом, которые могут быть упомянуты в данном ограничении. Заметим в связи с вышесказанным, что условием в ограничении может быть все, что следует за словом WHERE в SQL-предложении. Поэтому условие может стать ложным, если изменяется элемент не проверяемого, а какого-то другого атрибута. Пример. Попробуем выразить ограничение ссылочной целостности с помощью основанного на атрибуте ограничения CHECK, требующего наличия значения, на которое делается ссылка. Далее показана ошибочная попытка выразить требование, согласно которому значение Псерт# в кортеже отношения Студия(название, адрес, Псерт#) Должно появиться в компоненте серт# отношения Продюсер(имя, адрес, серт#) Это требование естественным образом формулируется как ограничение по внешнему ключу: Псерт# INT REFERENCES продюсер(серт#) Если эту строку заменить строкой Псерт# INT CHECK (Псерт# IN (SELECT серт# FROM продюсер)) это вполне корректное, основанное на атрибуте ограничение CHECK, но результаты его негативны. Невозможно ввести в отношение Студия новый кортеж, имеющий значение компонента Псерт#, не являющееся номером сертификата продюсера фильма. Значение компонента Псерт# кортежа из Студия невозможно заменить новым значением, не являющемся номером сертификата продюсера фильма. Если из отношения Продюсер удалить любой кортеж, то такое изменение останется «невидимым» для данного ограничения. Т.е. удаление разрешено, даже если нарушается ограничение CHECK на атрибут Псерт#. Описание ограничения на кортежи выполняется тоже с помощью служебного слова CHECK, добавляемого в список атрибутов и ограничений в операторе CREATE TABLE P. Условию ограничения CHECK в этом случае должен удовлетворять некоторый кортеж таблицы Р. Условие основанного на кортеже ограничения CHECK проверяется при каждой вставке в Р нового кортежа и при изменении существующего. Если условие не выполняется, то операция отвергается. Однако, если условие упоминает некоторое отношение (даже само Р) в подзапросе и изменение этого отношения приводит к нарушению данного условия для кортежа Р, такое изменение не отвергается. Подобно основанному на атрибуте ограничению CHECK, основанное на кортеже ограничение остается «невидимым» для других отношений. 8.4.6.Операторы контроляКак мы видели, ограничений на атрибуты и на кортежи бывает недостаточно. Нужны ограничения, включающие в себя отношение в целом, например, ограничение суммы или другого объединения значений в одном столбце. Применяются также ограничения, затрагивающие несколько отношений. Фактически к ним относится ограничение по внешнему ключу, соединяющее два отношения. Утверждения (ASSERTION) в SQL 92 (называемые также общими ограничениями) позволяют ввести любое условие (выражение, следующее за WHERE). Утверждения, в отличие от ограничений других типов, сами являются элементами схемы. Утверждение создается следующим оператором: CREATE ASSERTION <имя> CHECK (<условие>) [атрибуты ограничения]; Условие должно всегда выполняться, и нарушающие его изменения БД отвергаются. Утверждения проверяются при изменении любой из упомянутых таблиц. Имя утверждения обязательно должно быть указано. Любые упомянутые в условии поля должны быть введены в описание, как правило, путем указания таблицы, к которой они принадлежат, в выражении типа select-from-where. Предложение, указывающее на атрибуты ограничения, позволяет определить, будет ли данное ограничение отсрочено, а также, будет ли оно отсрочено по умолчанию. Пример. Ниже приведено утверждение, которое действует на отношение фильмы(назв_студии, продолжительность, …) и означает, что общая продолжительность фильмов, выпущенных студией, не должна превышать 10000 мин. CREATE ASSERTION SumLength CHECK (10000>=ALL (SELECT SUM(продолжительность) FROM фильмы GROUP BY назв_студии)); ЗаданияПостроить схему базы данных, состоящую из четырех отношений: Product(maker,model,type) PC(model, speed, ram, hd, cd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) Отношение Product представляет производителя, номер модели и тип (ПК, ПК-блокнот или принтер). Предполагается, что номера моделей уникальны для всех производителей и типов продуктов. В отношении PC для каждого номера модели, обозначающего ПК, указаны скорость (процессора в мегагерцах), общий объем RAM (в мегабайтах), размер диска (в гигабайтах), скорость считывающего устройства CD (например, 4х) и цена. Отношение Laptop аналогично отношению РС за исключением того, что вместо скорости CD содержится размер экрана (в дюймах). В отношении Printer для каждой модели принтера указывается, является ли он цветным (true, если цветной), тип принтера (лазерный, струйный или ленточный) и цена. Определить домен ModelType, значениями которого являются номера моделей; применить эту область в схемах всех четырех отношений. Изменить схему отношения Laptop, состоящего в добавлении атрибута cd; если ПК-блокнот не имеет считывающего CD, используйте для этого атрибута значение по умолчанию ‘none’. Измените схему Printer, удалив атрибут color. Предложите подходящие первичные ключи для отношений БД персональных компьютеров. Измените схему, включив в нее определения этих ключей. Предложите подходящие ограничения ссылочной целостности БД. Измените схему, включив в нее определения этих ключей. Запишите перечисленные ниже ограничения на атрибуты схемы БД: а) Скорость ПК-блокнота должна быть не менее 200 МГц; в) Скорость CD может быть только 8х, 12х, 24х, 32х, 40х; с) Типами принтеров могут быть только лазерный, струйный и матричный; d) Типами продукта могут быть только ПК, ПК-блокноты и принтеры; е) Объем RAM каждого ПК должен составлять не менее 1% объема его жесткого диска. Запишите в виде SQL-выражений перечисленные ниже ограничения БД, основанные на отношениях: а) Ни один производитель ПК не может производить ПК-блокноты; !в) Производитель ПК должен производить ПК-блокноты по меньшей мере с такой же скоростью процессора; !с) Если объем главной памяти ПК-блокнота превышает объем главной памяти ПК, его цена должна быть выше цены ПК; !!d) Ни один номер модели не может появиться дважды в отношениях PC, Laptop, Printer; !!е) если модель и ее тип упоминаются в отношении Product, эта модель должна появиться в отношении, подходящем для данного типа. Запишите следующие основанные на кортеже ограничения CHECK на схему ПК: а) Цена ПК со скоростью процессора менее 800 МГц не должна превышать 1000 дол. в) ПК-блокнот с размером экрана менее 11 дюймов должен иметь жесткий диск не менее 1 Гбайт или продаваться по цене ниже 1500 дол. 8.5.Информационная схемаВ SQL существует аналог того, что принято называть каталогом, - это информационная схема. Каталог в SQL состоит из дескрипторов для отдельной базы данных, а схема – из дескрипторов той части этой базы данных, которая относится к некоторому отдельному пользователю. Таким образом, может быть любое число каталогов, каждый из которых делится на произвольное число схем. Однако требуется, чтобы каждый каталог содержал ровно одну схему, называемую INFORMATION_SCHEMA. Таким образом, информационная схема состоит из набора таблиц SQL, содержимое которых фактически отражает все определения из всех остальных схем рассматриваемого каталога. Приведем некоторые наиболее важные представления информационной схемы:
Отметим, что представление Tables (таблицы) включает информацию обо всех именованных таблицах, представлениях и базовых таблицах; а представление VIEWS (представления) содержит информацию только о представлениях. Лекция 8.8.6.Обработка данных: операции выборки.Основными операторами языка обработки данных (data manipulation language - DML) являются SELECT, INSERT, UPDATE и DELETE. Рассмотрим вначале операции выборки (SELECT). Для простоты будем предполагать, что все операторы вводятся интерактивно (а не посредством встроенного в прикладные программы SQL). Операция выборки – это табличное выражение, которое может быть сколь угодно сложным. Оператор SELECT извлекает строки из одной или нескольких таблиц и имеет следующий синтаксис: SELECT [DISTINCT] {{функция агрегирования.. | выражение для вычисления значения [AS имя столбца]}.,..} | {спецификатор.*} | * FROM {{имя таблицы [AS] [имя корреляции] [(имя столбца.,..)]} | {подзапрос [AS] имя корреляции [имя столбца.,..]} | соединенная таблица } .,.. [WHERE предикат] GROUP BY {{[имя таблицы | имя корреляции.]} имя столбца.,..} [HAVING предикат] [{UNION | INTERSECT | EXCEPT} [ALL] [CORRESPONDING [BY (имя столбца.,..)]] оператор select | {TABLE имя таблицы} | конструктор значений таблицы] [ORDER BY {{ столбец-результат [ASC | DESC]}.,..} | {{положительное целое [ASC | DESC]}.,..}]; Любой из запросов можно применять в качестве подзапроса, чтобы получить значения, которые впоследствии будут использованы другими операторами, включая сам оператор SELECT. Иногда подзапрос выполняется отдельно для каждой строки, обрабатываемой внешним запросом, при этом значения из этой внешней строки будут использоваться в подзапросе. Запросы этого типа называют зависимыми подзапросами. Результат запроса представляет собой таблицу, столбцы которой определяются предложением SELECT как столбцы-результаты. Предложение FROM определяет одну или несколько таблиц, из которых извлекаются данные. Источником данных могут служить временные или постоянные базовые таблицы, представления или выходные данные подзапросов, а также другие операции с данными, результатом которых является таблица. В предложении WHERE определяются критерии, которым должны удовлетворять строки, чтобы их можно было использовать для получения результата. Предложение GROUP BY группирует выходные данные по одинаковым значениям указанных столбцов. При использовании GROUP BY в предложении SELECT могут быть указаны только те столбцы, которые поименованы в фразе GROUP BY, и любые агрегатные функции. Предложение GROUP BY применяется для выполнения агрегатных функций над группами строк, которые имеют одинаковые значения в указанных столбцах. Если GROUP BY отсутствует, то агрегатные функции либо не используются вообще, либо используются для всех выходных столбцов. Если все столбцы используют агрегатные функции, то все строки, удовлетворяющие предложению WHERE, или все строки, извлекаемые предложением FROM (если WHERE не задано), рассматриваются как одна группа для получения агрегатных (групповых) значений. Предложение HAVING определяет критерии, которым должны удовлетворять группы строк, формируемые предложением GROUP BY, чтобы их можно было поместить в выходные данные с помощью запроса. Операции UNION, INTERSECT и EXCEPT применяются для объединения выходных данных нескольких запросов. Далее они будут описаны подробнее. С помощью предложения ORDER BY можно расположить результаты одного или нескольких запросов в определенном порядке. Ниже приведен порядок обработки предложений в операторе SELECT: FROM WHERE GROUP BY HAVING SELECT UNION или EXCEPT INTERSECT ORDER BY 8.6.1.Предложение SELECTПредложение SELECT стоит первым, однако не является первым логически исполняемым шагом. Предложение SELECT определяет, какие столбцы строк, являющихся результатом выполнения других предложений оператора, относятся к выходным данным. SELECT может возвращать как сами значения столбцов, так и использовать их в составе агрегатных функций или других выражений. Выражения значений могут относиться к данным типа NUMERIC, STRING, DATETIME или INTERVAL. Кроме того, они могут включать в себя выражения CAST и CASE, агрегатные функции и подзапросы. Если задан квалификатор DISTINCT, строки сравниваются, и при обнаружении строки-дубликата в результате будет возвращена только одна копия строки. Предложение SELECT может содержать: Агрегатные функции, которые служат для получения единственного значения из группы значений столбца (например, SUM или COUNT). Символ «*», который означает, что все столбцы всех таблиц, приведенные в списке предложения FROM, являются выходными данными и размещаются в порядке их перечисления в предложении FROM. В последовательности «квалификатор.*», где квалификатор означает имя таблицы или имя корреляции, на которое имеется ссылка в предложении FROM. (Корреляция осуществляется через псевдоним для таблицы в зависимом подзапросе). Все столбцы этой таблицы являются выходными данными, исключая общие столбцы соединяемых таблиц. Выражение для вычисления значения оператора SELECT обычно содержит имя столбца одной из таблиц, указанных в предложении FROM. При этом значения столбца либо являются выходными данными без преобразования, либо становятся частью выражения, например, AMOUNT*3. Имя столбца, указанное в предложении AS, является именем столбца выходных данных (столбец-результат). Если выходные столбцы получены из одного (и только одного) столбца, указанного в предложении FROM, то они наследуют имя этого столбца по умолчанию. При желании можно переопределить это имя с помощью предложения AS. Способы присвоения имен столбцов не «напрямую« от исходных зависят от конкретного приложения (например: выражение1 и т.д. в MS Access). Ключевое слово AS согласно стандарту можно и опустить, оно неявно подразумевается. Если агрегатные функции и выражения употребляются совместно, то все выражения должны указываться в предложении GROUP BY. 8.6.2.Предложение FROMПредложение FROM указывает имена исходных таблиц для запроса. Эти таблицы могут быть таблицами или представлениями, непосредственно поименованными, или они могут быть получены в результате подзапроса или явно заданного соединения. «Имена корреляции», входящие в предложение FROM, обеспечивают использование альтернативных имен для таблиц. Имя корреляции записывается после имени таблицы, и его определение действует только в течение исполнения оператора. Эти имена не обязательны для базовых таблиц и представлений, но необходимы при работе с таблицами, которые являются результатом подзапроса. Имена корреляции могут применяться для устранения неоднозначности в используемых в предложении столбцах, в этом случае «имя корреляции» заменяет имя таблицы. Допускается, например, использование соединения таблицы с самой собой, которое будет обрабатываться как соединение двух идентичных таблиц. В этом случае необходимо использовать имя корреляции (псевдоним), чтобы различать две соединяемые копии. Имена корреляции выступают в роли префиксов к именам столбцов и, как обычно, отделяются от имени с помощью точки. Фактически, это переименование столбцов , используемых в предложении SELECT. Однако такие имена используются не для выходных столбцов, а для столбцов, на которые имеется ссылка в оставшейся части оператора, в частности в предложении WHERE. 8.6.2.1.СоединенияКогда в предложении FROM поименованы несколько таблиц, то все они неявно считаются соединяемыми. По сути это означает, что можно получить все возможные комбинации строк (по одной из каждой таблицы), и именно с такой конкатенацией будут работать остальные операторы запроса. Эта конкатенация носит название декартово произведение или перекрестное соединение (cross join). Чаще всего пользователю нужно исключить большинство строк и выделить определенные данные, что обычно реализуется посредством установления отношений (или условий) при помощи предложения WHERE. Другой способ установить отношения – использование встроенных операций соединения, чтобы осуществить внутреннее соединение в предложении FROM. Результат этого соединения есть порожденная таблица, которая и должна обрабатываться остальными операторами запроса. Можно использовать оба способа совместно, однако, чаще всего сложности в «запутанной» логике исполнения оператора намного превышают полученные преимущества. Ниже приведены синтаксические структуры, позволяющие соединять таблицы, которые можно применять в предложении FROM в качестве встроенных операций: Перекрестное соединение::= таблица А CROSS JOIN таблица В Естественное соединение::= таблица А [NATURAL] [тип соединения] JOIN таблица В Соединение объединения::= таблица А UNION JOIN таблица В Объединение посредством предиката::= таблица А [тип соединения] JOIN таблица В ON предикат Объединение посредством имен столбцов::= таблица А [тип соединения] JOIN таблица В USING (имя столбца.,..) Тип соединения::= INNER | {{LEFT | RIGHT | FULL } [OUTER]} Результатом соединения является таблица, которая обрабатывается в качестве исходной остальными операторами запроса. Если две таблицы являются одинаковыми, то их соединение осуществляется как соединение таблицы с ее копией. Такое соединение называется «самосоединением». Ключевое слово OUTER (внешний) не является обязательным, оно употребляется только для уточнения и не используется ни в каких операциях с данными. Соединение CROSS JOIN может быть реализовано согласно одному из различных типов NATURAL JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER. Обратите внимание, что соединение UNION и оператор UNION являются различными понятиями. Оператор UNION служит для объединения выходных данных нескольких операторов SELECT. Многие описания реляционных баз данных определяют NATURAL JOIN как соединение внешнего и родительского ключей таблицы. Однако согласно стандарту это не совсем так. NATURAL JOIN означает операцию соединения по нескольким столбцам с одинаковыми именами. Понятие соединение по столбцу означает, что определяются и выделяются только те строки декартова произведения таблиц А и В, в которых значения этих столбцов одинаковы, и эти столбцы затем соединяются в один общий столбец. Безусловно, типы соединяемых столбцов должны быть такими, чтобы столбцы можно было сравнивать. В естественном соединении, где присутствует по несколько столбцов с одинаковыми именами, соединение выполняется над комбинацией столбцов. Судьба оставшихся строк зависит от вида выполняемого естественного соединения. Поясним это и проиллюстрируем на примерах, использующих две таблицы А и В:
CROSS – перекрестное соединение. Это простое декартово произведение. Используются все комбинации строк (в нашем примере – 12). INNER – внутреннее соединение. Это тип соединения по умолчанию, оно используется когда другой тип соединения не задан. Соединяются только те строки, где найдены совпадающие значения столбца. Внутреннее соединение иллюстрируется следующей таблицей:
LEFT (OUTER) – левое (внешнее). Это соединение включает в себя все строки из таблицы А (совпадающие и несовпадающие) плюс совпадающие значения из таблицы В. для строк из таблицы А, которым не найдено соответствие, значения NULL заносятся в столбцы, извлекаемые из таблицы В. этот тип соединения иллюстрируется следующей таблицей:
RIGHT (OUTER) – правое (внешнее). Как можно предположить, это соединение является обратным предыдущему. Другими словами, две строки из таблицы В (правой таблицы) представлены в соединении, и они дополнены совпадающими строками из таблицы А, а в столбцы для строк, не имеющих совпадения, заносятся значения NULL (смотри таблицу):
FULL (OUTER) – полное (внешнее). Это комбинация левого и правого соединения. Присутствуют все строки из обеих таблиц. Если строки совпадают, то они заполнены реальными значениями. В несовпадающих строках значения столбцов заполняются значениями NULL (смотри таблицу):
UNION – объединение. Этот тип соединения отличается от оператора UNION, который используется для объединения выходной информации нескольких запросов. Соединение типа UNION является обратным по отношению к INNER: оно включает только те строки из таблиц, для которых не найдено совпадений. В них используются значения NULL для столбцов, полученных из другой таблицы. Если взять полное внешнее соединение и удалить из него строки, полученные путем внутреннего соединения тех же таблиц, то в результате получится соединение типа «объединения». Этот тип соединения иллюстрируется нижеприведенной таблицей.
Другие возможные соединения могут быть напрямую заданы с помощью ON или USING. USING определяет соединение по столбцам. Если это естественное соединение, то имена столбцов и типы данных должны совпадать. USING отличается от NATURAL тем, что USING позволяет соединять в таблицах только заданное подмножество столбцов с совпадающими именами, а NATURAL автоматически соединяет все столбцы с одинаковыми именами. ON определяет предикат, который представляет собой выражение, подобное city=’Барселона’ или 3>x. Результатом вычисления выражения могут быть TRUE, FALSE или UNKNOWN (если присутствует значение NULL). Значение предиката в данном случае может вычисляться с использованием значений, взятых из строки-кандидата соединения. Под строкой-кандидатом понимается строка, которая, в свою очередь, получается, как декартово произведение строк таблиц, участвующих в соединении. Возможная путаница с именами столбцов разрешается за счет использования квалификаторов, состоящих из имен таблиц или имен корреляции, т.е. имен с суффиксами. Предикат может содержать подзапросы, однако при использовании предложения FROM этот предикат не должен использовать функций агрегирования. 8.6.3.Предложение WHEREПредложение WHERE содержит предикат, который может включать одно или несколько выражений и принимать одно из трех значений: TRUE, FALSE или UNKNOWN. Сравнение значения NULL с другим значением (в том числе и NULL) дает результат UNKNOWN. Другие значения сравниваются в соответствии с последовательностями сортировки для строк текста, с порядком числовых значений для числовых типов, хронологическим порядком для данных типа дата-время или по величине значения (для данных типа INTERVAL). Сравнения осуществляются с помощью операторов =, <, <=, >, >= и <> (не равно). Применение таких операторов, как * (умножение) или || (конкатенация), зависит от типа данных. В большинстве случаев вместо простых выражений можно использовать конструкторы значений строк. Кроме стандартных операторов сравнения, SQL позволяет использовать специальные операторы предикатов. Предположим, что В и С – параметры выражения для вычисления значений, которые могут быть именами столбцов или прямыми выражениями (разрешается использовать имена столбцов или функции агрегирования) с соответствующим типом данных: B BETWEEN A AND C Это выражение эквивалентно ( A <= B) AND (B <= C). Параметр А должен быть меньше С. выражение B BETWEEN C AND A будет интерпретироваться как (C <= B) AND (B <= A), и оно имело бы значение FALSE при значении выражения (A <= B) AND (B <= C), равном TRUE, за исключением случая, когда все три величины одинаковы. Если один из параметров равен NULL, значение предиката не определено. A IN (C, D.,..) Это выражение будет истинным, если А равняется одному из значений, включенных в список. A LIKE ‘строка’ В этом случае подразумевается, что А – строка символов, и операция заключается в поиске указанной подстроки. При этом можно использовать строку фиксированной длины или строку с шаблоном подстановки A IS NULL Это выражение проверяет, является ли А значением NULL. В отличие от большинства других предикатов результат данного предиката может быть только TRUE или FALSE (не UNKNOWN). A оператор сравнения SOME | ANY подзапрос SOME и ANY имеют одинаковый смысл. Результатом подзапроса является набор величин. Если для какого-нибудь значения V, получаемого из подзапроса, результат операции «А оператор сравнения V» равняется TRUE, то предикат ANY также равняется TRUE. А оператор сравнения ALL подзапрос Исполняется так же, как и ANY, но для всех значений, получаемых из подзапроса, проверка должна удовлетворять результату TRUE для предиката «А оператор сравнения V». EXISTSподзапрос Если в результате подзапроса найдена хотя бы одна строка, предикат равняется TRUE, в противном случае – FALSE. Результат никогда не может быть UNRNOWN. Это выражение имеет смысл только для зависимого подзапроса. UNIQUE подзапрос Если подзапрос не находит идентичных строк, то значение UNIQUE равняется TRUE, в противном случае – FALSE. В этом предикате подразумевается, что идентичные строки не содержат значения NULL. В противном случае строки не идентичны. Конструктор значений строки MATCH [аргументы] подзапрос Этот предикат проверяет присутствие сконструированных строк в таблице, которая получается в результате выполнения подзапроса. Аргументы позволяют задать полное (FULL) или частичное (PARTIAL) совпадение, а также требование уникальности совпадающих строк. Конструктор значений строки OVERLAPS конструктор значений строки Это выражение для предиката, который используется для определения фактов перекрытия двух временных периодов типа «дата» или «время». Он должен использоваться с типами данных DATETIME, допускается также совместное использование с типом INTERVAL. Эти предикаты можно комбинировать, используя условные булевы операторы AND, OR и NOT. Если предикаты имеют значения TRUE и FALSE, то они исполняются как обычно. Когда в логическое выражение входит значение UNKNOWN, то получаются результаты, описанные в разделе Предикаты. Допускается использование скобок, с помощью которых задается порядок вычислений. Таким образом, строки, получаемые с помощью предложения WHERE, могут содержать данные, напрямую выбранные из таблиц, или представлять собой декартовы произведения. Эти данные будут передаваться на обработку, которая задается последующими предложениями. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||