Язык SQL

<

110313 2222 SQL1 Язык SQL Одним из языков, появившихся в результате разработки реляционной модели данных, является язык SQL (Structured Query Language), который в настоящее время получил очень широкое распространение и практически превратился в стандартный язык реляционных баз данных. Стандарт язык SQL был выпущен Американским национальным институтом стандартов в 1986 году, а в 1987 году Международная организация стандартов принял этот стандарт в качестве международного. В настоящее время язык SQL поддерживается многими десятками СУБД различных типов, разработанных для самых разнообразных вычислительных платформ.

9.1. Введение в язык SQL

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

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

  • создавать базы данных и
    таблицы с полным описанием их структуры;
  • выполнять основные операции манипулирования данными, такие как вставка, модификация и
    удаления данных из таблиц;
  • выполнять простые и
    сложные запросы, осуществляющие преобразование необработанных данных в необходимую информацию.

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

    Язык SQL имеет два основных компонента:

  • язык DDL (Data Definition Language), предназначенный для определения структур базы данных;
  • язык DML (Data Manipulation Language), предназначенный для выборки и обновления данных.

    В стандарте ANSI предусматривается разделение языка DDL на два компонента: собственно язык DDL, предназначенный для определения структуры базы данных, и язык DCL (Data Control Language), используемый для управления доступом к данным.

    Язык SQL включает только команды определения и манипулирования данными — в нем отсутствуют какие-либо команды управления ходом вычислений. Другими словами, в этом языке нет команд IF … THEN … ELSE, GO TO, DO … WHILE и любых других, предназначенных для управления ходом вычислительного процесса. Подобные задачи должны решаться с помощью языков программирования или управления заданиями либо интерактивно, в результате действий, выполняемых самим пользователем. По причине подобной незавершенности в плане организации вычислительного процесса язык SQL может использоваться двумя способами. Первый предусматривает интерактивную работу, заключающуюся в вводе пользователем с терминала отдельных SQL- операторов. Второй состоит во внедрении SQL-операторов в программы, разработанные на процедурных языках.

    Язык SQL относительно прост в изучении.

  • Это не процедурный язык, поэтому в нем необходимо указывать, какая информация должна быть получена, а не как ее можно получить. Иначе говоря, язык SQL не требует указания методов доступа к данным.
  • Как и большинство современных языков, SQL поддерживает свободный формат записи операторов. Это означает, что при вводе отдельные элементы операторов не связаны с фиксированными позициями экрана.
  • Структура команд задается набором ключевых слов, представляющих собой обычные слова английского языка — такие, как CREATE TABLE (Создать таблицу), INSERT (Вставить), SELECT (Выбрать).
  • Язык SQL может использоваться широким кругом пользователей, включая администраторов баз данных (АБД), прикладными программистами, руководящим персоналом компании и конечными пользователями.

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

    История реляционной модели данных (и косвенно языка SQL) началась в 1970 году с публикации основополагающей статьи Е. Ф. Кодда. В 1974 году Д. Чамберлин, публикует определение языка, получившего название SEQUEL. В последствии его название пришлось изменить на SQL (официально ее рекомендуется читать как «эс-кью-эл») из юридических соображений — аббревиатура SEQUEL уже использовалась ранее.

    В 1976 году на базе языка SEQUEL/2 корпорация IBM выпустила прототип СУБД, имевший название «System B». Назначение этой пробной версии состояло в проверке осуществимости реляционной модели. Помимо прочих положительных аспектов, одним из результатов выполнения этого проекта можно считать разработку собственно языка SQL.

    В конце 70-х годов компанией, которая ныне превратилась в корпорацию CLE, была выпущена СУБД ORACLE. Пожалуй, это самая первая из коммерческих реализаций реляционной СУБД, построенной на использовании языка SQL.

    В 1992 году была выпущена первая, существенно пересмотренная версия стандарта ISO, которую иногда называют SQL2 (или SQL 92). Хотя некоторые из функций были определены в этом стандарте впервые, многие из них уже были полностью или частично реализованы в одной или более коммерческих реализаций языка SQL.

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

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

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

    Стандарт ISO SQL не поддерживает таких формальных терминов, как «отношение», «атрибут» и «кортеж», вместо них применяются термины «таблица», «столбец» и «строка». Кроме того, следует отметить, что стандарт SQL не придерживается жестко определений реляционной модели данных. Например, в языке SQL допускается, что созданная в результате выполнения операции таблица может содержать дублирующиеся строки, устанавливается упорядоченность столбцов, а пользователю разрешается упорядочивать строки в таблице.

    9.2. Запись SQL-операторов

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

    Слова, определяемые пользователем, задаются самим пользователем (в соответствии с определенными синтаксическими правилами) и представляют собой имена различных объектов базы данных — таблиц, столбцов, представлений, индексов и т.д. Слова в операторе размещаются в соответствии с установленными синтаксическими правилами. Хотя в стандарте это не указано, многие диалекты языка SQL требуют задания в конце оператора некоторого символа, обозначающего окончание его текста (как правило, с этой целью используется символ точка с запятой (;)).

    Большинство компонентов SQL-операторов не чувствительно к регистру.

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

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

    Для определения формата SQL-операторов мы будем применять следующую расширенную форму BNF-нотации (Backus Naur Form).

  • Прописные буквы будут использоваться для записи зарезервированных слов и должны указываться в операторах точно так же, как это будет показано.
  • Строчные буквы будут использоваться для записи слов, определяемых пользователем.
  • Вертикальная черта ( | ) указывает на необходимость выбора одного из нескольких приведенных значений;
  • Фигурные скобки определяют обязательный элемент — например, {а}.
  • Квадратные скобки определяют необязательный элемент — например, [а].
  • Многоточие (… ) используется для указания необязательной возможности повторения конструкции.

    На практике для определения структуры базы данных (т.е. ее таблиц) используются DDL-операторы, а для заполнения этих таблиц данными и выборки из них информации с помощью запросов — DML-операторы.

    Литералы

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

    9.3. Манипулирование данными

    В этом разделе обсуждаются следующие операторы языка SQL DML:

  • SELECT — выборка данных из базы;
  • INSERT — вставка данных в таблицу;
  • UPDATE — обновление (изменение) данных в таблице;
  • DELETE — удаление данных из таблицы.

    Для построения примеров SQL-операторов будем использовать пример учебной базы данных «АГЕНСТВО»:

    B(KodB, City, Street, Pind, Tel)

    S(KodS, Lname, Fname, Pol, DR, Dol, ZP, KodB)

    P(KodP, City, Street, Type, Rooms, Rent, KodO, KodS, KodB)

    R(KodR, Name, Address, Type, МахRent, KodB)

    O(KodO, Name, Address, Tel)

    V(KodR, KodP, Date, Comment)

    9.3.1. Простые запросы

    Назначение оператора SELECT состоит в выборке и отображении данных одной или более таблиц базы данных. Это исключительно мощный оператор, способный выполнять действия, эквивалентные операторам реляционной алгебры selection, projection и join, причем в пределах единственной выполняемой команды.

    Общий формат оператора SELECT имеет следующий вид:

    SELECT [DISTINCT | ALL] {* | [column_expression [AS new_name]] [,… ]}
    FROM table_name [alias] [,… ]
    [WHERE condition]
    [GROUP BY со1umп_list] [HAVING condition][ORDER BY column_list]

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

    Фразы оператора SELECT определяют:

    SELECT 

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

    FROM

    имена используемой таблицы или нескольких таблиц

    WHERE

    фильтрацию строк объекта в соответствии с заданными условиями

    GROUP BY

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

    HAVING

    фильтрацию группы строк объекта в соответствии с указанным условием

    ORDER BY

    упорядоченность результатов выполнения оператора

    Порядок фраз в операторе SELECT не может быть изменен. Только две фразы — SELECT и FROM — являются обязательными, все остальные могут быть опущены. Операция SELECT является закрытой: результат представляет собой другую таблицу. Существует множество вариантов записи данного оператора, что иллюстрируется приведенными ниже примерами.

    Выбор всех строк

    Пример 9.1. Получит детальные сведения о каждом из сотрудников.

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

    SELECT Kods, Lname, Fname, Pol, DR, Dol, ZP, Kodb
    FROM S;

    Поскольку выборка всех имеющихся в таблице столбцов выполнятся достаточно часто, в языке SQL определен упрощенный вариант записи значения «все столбцы» — вместо имен столбцов указывается символ *. Приведенный ниже оператор полностью эквивалентен первому и представляет собой упрощенный вариант записи того же самого запроса:

    SELECT * FROM S;

    Результат выполнения этого запроса представлен в табл. 9.1.

    Таблица 9.1. Результат выполнения запроса из примера 9.1

    KodS 

    Lname 

    Fname 

    Pol 

    DR 

    Dol 

    ZP 

    KodB 

    S21

    Иванов 

    Иван 

    М 

    01.02.70 

    Руководитель 

    500 

    B5

    S37

    Петрова 

    Татьяна 

    Ж 

    12.11.72 

    Агент

    250 

    B3

    S14

    Сидоров 

    Стеман 

    М 

    06.05.68 

    Менеджер 

    400 

    B3

    S09

    Зотова 

    Ирина 

    Ж 

    11.03.75 

    Агент 

    240 

    B7

    S05

    Степанова 

    Елена 

    Ж 

    16.07.71 

    Секретарь 

    200 

    B3

    S41

    Петров 

    Игорь 

    М 

    02.02.68 

    Менеджер 

    450 

    B5

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

    SELECT Kods, Lname, Fname, Dol, ZP
    FROM S;

    Результат:

    KodS 

    Lname 

    Fname 

    Dol 

    Zp 

    S21 

    Иванов 

    Иван 

    Руководитель 

    500 

    S37

    Петрова 

    Татьяна 

    Агент 

    250 

    S14

    Сидоров 

    Стеман 

    Менеджер 

    400 

    S09

    Зотова 

    Ирина 

    Агент 

    240 

    S05

    Степанова

    Елена 

    Секретарь 

    200 

    S41

    Петров 

    Игорь 

    Менеджер 

    450 

     

    Пример 9.3. Получить сведения (код) о осмотренных клиентами объектах

    SELECT Kodp
    FROM V

    Результат:

    KodP 

     

    P14 

     

    P04 

     

    P04 

     

    P14 

     

    P36 

    SELECT DISTINCT Kodp
    FROM V

    Результат:

    KodP 

    P14 

    P04 

    P36

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

    SELECT Kods, Lname, Fname, ZP*12 As SZP
    FROM S;

    Результат: 

    KodS

    Lname

    Fname 

    SZP 

     

    S21

    Иванов 

    Иван 

    6000 

     

    S37

    Петрова

    Татьяна 

    3000 

     

    S14

    Сидоров 

    Стеман 

    4800 

     

    S09

    Зотова 

    Ирина 

     
     

    S05

    Степанова 

    Елена 

     
     

    S41

    Петров 

    Игорь 

     

    Стандарт ISO позволяет явным образом задавать имена столбцов результирующей таблицы, для чего применяется фраза AS.

    Выбор строк (предложение WHERE)

    В приведенных выше примерах в результате выполнения операторов SELECT выбирались все строки указанной таблицы. Однако очень часто требуется тем или иным образом ограничить набор строк, помещаемых в результирующую таблицу. Это достигается с помощью указания в запросе предложения WHERE. Оно состоит из ключевого слова WHERE, за которым следует перечень условий поиска, определяющих те строки, которые должны быть выбраны при выполнении запроса. Существует пять основных типов условий поиска (или предикатов, если пользоваться терминологией ISO).

    Сравнение.

    Сравниваются результаты вычисления одного выражения с результатами вычисления другого выражения.

    Пример 9.5. Сравнение. Получить сведения о сотрудниках, заработная плата которых превышает 300.

    SELECT Kods, Lname, Fname, Dol, ZP
    FROM S

    WHERE ZP > 300;

    В этом запросе используется таблица S и предикат ZP > 300.

    Результат:

    KodS 

    Lname 

    Fname 

    Dol 

    ZP 

    S21

    Иванов 

    Иван 

    Руководитель 

    500 

    S14

    Сидоров 

    Стеман 

    Менеджер 

    400 

    S41

    Петров 

    Игорь 

    Менеджер 

    450 

    В языке SQL можно использовать следующие операторы сравнения:

    = 

    равенство 

    < 

    меньше 

    > 

    больше 

    <= 

    меньше или равно 

    >= 

    больше или равно 

    <> 

    не равно (стандарт ISO) 

    != 

    не равно (используется в некоторых диалектах) 

    Более сложные предикаты могут быть построены с помощью логических операторов AND, OR или NOT, а также с помощью скобок, используемых для определения порядка вычисления выражения для устранения любой возможной неоднозначности. Вычисление выражений в условиях выполняется по следующим правилам:

  • выражение вычисляется слева направо;
  • первыми вычисляются подвыражения в скобках;
  • операторы NOT выполняются до выполнения операторов AND и OR;
  • операторы AND выполняются до выполнения операторов OR.

    Пример 9.6. Сложные условия поиска. Получить список всех отделений в городах Тюмень и.

    SELECT KodB, City, Street, Pind
    FROM B

    WHERE City = ‘Тюмень’ OR Сity = ‘Нижневартовск’ ;

    Результат 

    KodB 

    City 

    Street 

    Pind 

     

    B5 

    Тюмень

    Береговая, 22

    625002 

     

    B7 

    Нижневартовск 

    Новая, 15 

    625234 

     

    B3

    Тюмень 

    Северная, 34 

    625026 

    Диапазон (BETWEEN / NOT BETWEEN).

    Проверяется, попадает ли результат вычисления выражения в заданный диапазон значений.

    Пример 9.7. Использование диапазонов. Получить список сотрудников с заработной платой от 200 до 300.

    SELECT Kods, Lname, Fname, Dol, ZP
    FROM S

    WHERE ZP BETWEEN 200 AND 300;

    KodS 

    Lname 

    Fname 

    Dol 

    ZP 

    S37

    Петрова 

    Татьяна 

    Агент 

    250 

    S09

    Зотова 

    Ирина 

    Агент 

    240 

    S05

    Степанова 

    Елена 

    Секретарь 

    200 

    Наличие ключевого слова BETWEEN требует задания границ диапазона значений. В данном случае результаты проверки будут положительными для всех работников компании с годовой заработной платой от 200 до 300. Имеется и негативная версия проверки диапазона значений (NOT BETWEEN).

    Приведенный выше запрос можно переписать следующим образом:

    SELECT Kods, Lname, Fname, Dol, ZP FROM S

    WHERE ZP <= 200 AND ZP >= 300;

    Принадлежность и множеству (IN / NOT IN).

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

    Пример 9.8. Условия поиска с проверкой вхождения в множество. Получит список всех руководителей и менеджеров.

    SELECT Kods, Lname, Fname, Dol FROM S

    WHERE Dol IN ( ‘Руководитель’ , ‘Менеждер’ );

    Проверка вхождения результата вычисления выражения в заданное множество организуется с помощью ключевого слова IN. При этом проверяется, соответствует ли результат вычисления выражения одному из значений в предоставленном списке. Существует и отрицательная версия этой проверки (NOT IN).

    Соответствие шаблону (LIKE/NОТ LIKE).

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

    % 

    символ процента представляет любую последовательность из нуля или более символов 

    _ 

    символ подчеркивания представляет любой одиночный символ 

    Примеры использования знаков шаблона:

    Address LIKE ‘Т%’

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

    Address LIKE ‘Н_ _ _’

    шаблон означает, что значение должно иметь длину, равную четырем символам, причем первым символом обязательно должен быть символ ‘H’,

    Address LIKE ‘%а’

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

    Address LIKE ‘%Тюмень%’

    шаблон означает, что нас интересует любая последовательность символов, включающая подстроку Тюмень;

    Address NOT LIKE ‘Н%’

    шаблон указывает, что требуются любые строки, которые не начинаются с символа Н. 

    Пример 9.9. Условия поиска с указанием шаблона. Получить список всех арендаторов, проживающих в Тюмени.

    SELECT KodR, Name, Address
    FROM R
    WHERE Address LIKE ‘%Тюмень%’;

    Если требуемая строка должна включать служебный символ, используемый в качестве символа подстановки, то следует применить некоторый «ESCAPE» символ, поместив его перед символом подстановки и указав его в конце фразы. Например, для проверки значений на соответствие строке ‘15%’ можно воспользоваться таким предикатом:

    LIKE ’15!%’ ESCAPE ‘!’

    Значение NULL (IS NULL / NOT IS NULL).

    Проверяется, содержит ли данный столбец определитель Null (неизвестное значение).

    Пример 9.10. Использование значения NULL. Получить список осмотров помещений, по которым не сделано никаких заключений.

    SELECT KodR, KodP, Comment FROM V
    WHERE Comment IS NULL

    Результат 

    KodR 

    KodP 

    Comment

     

    R56

    P04

     
     

    R56

    P36

     

    Использование условия Comment=» не эквивалентно Comment IS NULL!

     

    9.3.2. Сортировка результатов (фраза ORDER BY)

    Фраза ORDER BY позволяет упорядочить выбранные записи в порядке возрастания (ASC) или убывания (DESC) значений любого столбца или комбинации столбцов.

    Фраза ORDER BY всегда должна быть последней в операторе SELECT.

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

    SELECT KodS, Fname, Lname, ZP
    FROM S
    ORDER BY ZP DESC;

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

    Пример 9.12.  Список сдаваемых в аренду объектов, упорядоченный по их типу.

    SELECT KodP, City, Type, Rooms, Rent FROM P
    ORDER BY Type

    Результат:

    KodP 

    City 

    Type 

    Rooms 

    Rent 

    P14

    Тюмень 

    дом

    6 

    650 

    P04

    Тюмень 

    дом 

    4 

    350 

    P94

    Нижневартовск 

    кварт. 

    4 

    400 

    P36

    Ишим 

    кварт. 

    2 

    375 

    P21

    Надым 

    кварт. 

    3 

    600 

    P16

    Сургут 

    кварт. 

    2 

    450 

     

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

    SELECT KodP, City, Type, Rooms, Rent FROM P
    ORDER BY Type, Rooms DESC;

    Результат:

    KodP

    City

    Type 

    Rooms 

    Rent 

    P14

    Тюмень 

    дом 

    6 

    650 

    P04

    Тюмень 

    дом 

    4 

    350 

    P94

    Нижневартовск

    кварт. 

    4 

    400 

    P21

    Надым 

    кварт. 

    3 

    600 

    P36

    Ишим 

    кварт. 

    2 

    375 

    P16

    Сургут 

    кварт. 

    2 

    450 

     

    9.3.3. Использование обобщающих функций языка SQL

    Стандарт ISO содержит определение следующих пяти обобщающих функций.

    COUNT

    Возвращает количество значений в указанном столбце

    SUM

    Возвращает сумму значений в указанном столбце 

    AVG

    Возвращает усредненное значение в указанном столбце 

    MIN

    Возвращает минимальное значение в указанном столбце 

    MAX 

    Возвращает максимальное значение в указанном столбце

     

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

    Для исключения дублирующихся значений, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. Стандарт ISO допускает использование ключевого слова ALL.

    Обобщающие функции могут использоваться только в списке предложения SELECT и в составе предложения HAVING.

    Пример 9.13. Использование функции COUNT (*). Определить, сколько сдаваемых в аренду объектов имеют ставку арендной платы более 350.

    SELECT COUNT(*) AS C FROM P WHERE Rent > 350;

    Пример 9.14. Определить, сколько различных сдаваемых в аренду объектов были осмотрены.

    SELECT COUNT(KodP) AS C FROM V ;

    SELECT COUNT(DISTINCT KodP) AS C FROM V ;

    Пример 9.15. Определить общее количество менеджеров компании и вычислить сумму их зарплаты.

    SELECT COUNT(KodS) AS C, SUM(ZP) AS SZP FROM S WHERE Dol = ‘Manager’;

    Пример 9. 16. Использование функций Min, Мах и AVG. Вычислить значение минимальной, максимальной и средней заработной платы.

    SELECT MIN(ZP) AS MinZP, MAX(ZP) AS MaxZP, AVG(ZP) AS AZP FROM S;

     

    9.3.4. Группирование результатов (фраза GROUP ВУ)

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

    имена столбцов;

    обобщающие функции;

    константы;

    выражения, включающие комбинации перечисленных выше элементов.

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

    Пример 9.16. Использование фразы GROUP BY.

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

    SELECT KodB, COUNT(KodS) AS C, SUM(ZP) AS SZP FROM S
    GROUP BY KodB
    ORDER BY KodB;

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

    1. Строки таблицы S распределяются в группы в соответствии со значениями в столбце номера отделения компании.

    KodB 

    KodS 

    ZP 

    B3

    B3

    B3 

    S37

    S14

    S05 

    250

    400

    200 

    B5

    B5 

    S41

    S21 

    450

    500 

    B7 

    S09 

    240 

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

    KodB 

    C 

    SZP 

    B3 

    3 

    850 

    B5 

    2 

    950 

    B7 

    1 

    240 

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

    Результат:

    KodB 

    C 

    SZP 

    B3 

    3 

    850 

    B5 

    2 

    950 

    B7 

    1 

    240 

     

    Ограничения на выполнение группирования (фраза HAVING)

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

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

    SELECT KodB, COUNT(KodS) AS C, SUM (ZP) AS SZP FROM S
    GROUP BY KodB HAVING COUNT(KodS) > 1
    ORDER BY KodB;

    Результат выполнения запроса:

    KodB 

    C 

    SZP 

    B3 

    3 

    850 

    B5 

    2 

    950 

    Ограничения на выполнение группирования (фраза HAVING)

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

    Фраза HAVING не является необходимой частью языка SQL — любой запрос, написанный с использованием фразы HAVING, может быть представлен в ином виде, без ее применения.

    Пример 13.18. Использование фразы HAVING

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

    SELECT KodB, COUNT(KodS) AS C, SUM (ZP) AS SZP
    FROM S
    GROUP BY KodB
    HAVING COUNT(KodS) > 1
    ORDER BY KodB;

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

    KodB 

    C 

    SZP 

    B3 

    3 

    850 

    B5 

    2 

    950 

     

    9.3.5. Подзапросы

    В этом разделе рассматривается использование операторов SELECT, внедренных в тело другого оператора SELECT. Внешний (второй) оператор SELECT использует результат выполнения внутреннего (первого) оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены в предложения WHERE и HAVING внешнего оператора SELECT — в этом случае они получают название подзапросов, или вложенных запросов. Текст подзапроса должен быть заключен в скобки. Кроме того, внутренние операторы SELECT могут использоваться в операторах INSERT, UPDATE и DELETE. Существует три типа подзапросов.

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

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

    • Табличный подзапрос возвращает значения одного или больше столбцов таблицы, размещенные в более чем одной строке. Табличный подзапрос может использоваться везде, где допускается указывать таблицу — например, как операнд предиката IN

    Пример 9.19. Использование подзапроса. с проверкой на равенство.

    Получить список персонала, работающего в отделении компании, расположенном по адресу «Северная, 34».

    SELECT KodS, Lname, Fname, Dol
    FROM S
    WERE KodB =
    (SELECT KodB FВОМ B WHERE Street = ‘Северная, 34’);

    Внутренний оператор предназначен для определения номера отделения компании, расположенного по указанному адресу. Внутренний оператор SELECT возвращает таблицу, состоящую из единственного значения ‘В3’.

    Результаты выполнения этого запроса представлены в табл. ____.

    KodS 

    Lname 

    Fname 

    Dol 

    S37 

    Петрова 

    Татьяна 

    Агент 

    S14

    Сидоров 

    Стеман 

    Менеджер 

    S05

    Степанова 

    Елена

    Секретарь 

    Подзапрос представляет собой инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Подзапрос может указываться непосредственно после операторов сравнения (т.е. операторов =, <, >, <=, >=, <>) в предложениях WHERE и HAVING.

    Пример 9.20. Использование подзапросов с обобщающими функциями.

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

    SELECT KodS, Fname, Lname, Dol,
    ZP -(SELECT avg(ZP) FROM S) AS Dzp
    FROM S
    WERE ZP > (SELECT avg(ZP) FROM S);

    Необходимо отметить, что нельзя прямо использовать предложение WHERE ZP > avg(ZP) поскольку применять обобщающие функции в предложениях WHERE запрещено.

    Результаты выполнения запроса представлены в табл. _____ (среднее значение равно 340)

    KodS 

    Lname 

    Fname 

    Dol 

    Dzp 

    S21 

    Иванов 

    Иван 

    Руководитель 

    160 

    S14

    Сидоров 

    Стеман 

    Менеджер 

    60 

    S41

    Петров 

    Игорь 

    Менеджер 

    110 

    К подзапросам применяются следующие правила и ограничения.

    1. В подзапросах не должна использоваться фраза ORDER BY, хотя она может присутствовать во внешнем запросе.

    2. Список в предложении SELECT подзапроса должен состоять из имен отдельных столбцов или составленных из них выражений — за исключением случая, когда и подзапросе используется ключевое слово EXISTS (см. ниже).

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

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

    Пример 9.21. Вложенные подзапросы и использование предиката IN

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

    SELECT KodP, City, Street, Type, Rooms, Rent,
    FROM P
    WHERE KodO IN
    (SELECT KodS FROM S WНERE KodB IN
    (SELECT KodB FВОМ B WHERE City = ‘Тюмень’)),

    KodP 

    <

    City 

    Street 

    Type 

    Rooms 

    Rent 

    P94

    Нижневартовск 

     

    кварт. 

    4 

    400 

    P04

    Тюмень 

     

    дом 

    4 

    350 

    P36

    Ишим 

     

    кварт. 

    2 

    375 

    P21

    Надым 

     

    кварт. 

    3 

    600 

    P16

    Сургут 

     

    кварт. 

    2 

    450 

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

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

    9.3.6. Ключевые слова ANY и ALL

    Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими столбец чисел. Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным только в том случае, если оно выполняется для всех значений в результирующем столбце подзапроса. Если записи подзапроса предшествует ключевое слово ANY, то условие сравнения будет считаться выполненным, если оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса. Если в результате выполнения подзапроса будет получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY — невыполненным. Согласно стандарту ISO дополнительно можно использовать ключевое слово SOME, являющееся синонимом ключевого слова ANY.

    Пример 9.22. Использование ключевого слова ANY

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

    SLECT KodS, Lname, Fname, Dol, ZP FROM S
    ZP > ANY (SELECT ZP FROM S WHERE KodB = ‘B5’);

    Внутренний подзапрос создает набор числовых значений (350, 500), а внешний запрос выбирает сведения о тех работниках, чья зарплата больше хотя бы одного из значений в этом наборе.

    Результаты выполнения запроса представлены в табл. ______.

    KodS 

    Lname 

    Fname 

    Dol 

    ZP 

    S21

    Иванов 

    Иван 

    Руководитель 

    500 

    S14

    Сидоров 

    Стеман 

    Менеджер 

    400 

    S41

    Петров 

    Игорь 

    Менеджер

    350 

    Пример 9.23. Использование ключевого слова ALL.

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

    SLECT KodS, Lname, Fname, Dol, ZP FROM S
    ZP > ANY (SELECT ZP FROM S WHERE KodB = ‘B3’);

    Результаты выполнения запроса представлены в табл. ____.

    KodS 

    Lname 

    Fname 

    Dol 

    ZP 

    S21

    Иванов 

    Иван 

    Руководитель 

    500 

    9.3.7. Многотабличные запросы

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

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

    Пример 9.24. Простое соединение.

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

    SELECT R.KodR, Name, KodP, Comment
    FROM Renter R, Vieving V
    WHERE R.KodR = V.KodR

    В этом отчете требуется представить сведения как из таблицы Renter, так и из таблицы Viewing, поэтому при построении запроса мы воспользуемся механизмом соединения таблиц. В предложении SELECT перечисляются все столбцы, которые должны быть помещены в результирующую таблицу запроса. Обратите внимание что для столбца с номером клиента (KodR) необходимо указать квалификатор — этот столбец может быть выбран из любой соединяемой таблицы, поэтому необходимо точно указать, значения какой таблицы нас интересуют. Уточнение имени осуществляется посредством указания перед именем столбца имени той таблицы (или ее псевдонима), из которой он выбирается.

    Для построения результирующих строк используются те строки исходных таблиц, которые имеют одинаковое значение в столбце KodR. Это условие определяется посредством задания условия поиска R.KodR = V.KodR. Подобные столбцы исходных таблиц называют сочетаемыми столбцами.

    Результаты выполнения запроса представлены в табл. _____.

    KodR 

    Name 

    KodP 

    Comment 

    R56

    Рубин Степан 

    P14

    Мала 

    R56

    Рубин Степан 

    P04

     

    R56

    Рубин Степан 

    P36

     

    R62

    Зимина Елена 

    P14

    Дорого 

    R76 

    Саблев Иван

    P04

    Далеко 

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

    Стандарт SQL2 дополнительно предоставляет следующие способы определения данного соединения:

    FROM Renter R JOIN Vieving V ON R.KodR = V.KodR

    FROM Renter JOIN Vieving USING KodR

    FROM Renter NATURAL JOIN Vieving

    В каждом случае предложение FROM замещает исходные предложения FROM и WHERE.

    Пример 9.26. Соединение трех таблиц

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

    SELECT B.KodB, B.City, S.KodS, Fname, Lname, KodP
    FROM B, S, P
    WHERE B.KodB = S.KodB AND S KodS=P.KodS
    ORDER BY B.KodB, S.KodS, KodP.

    В результирующую таблицу необходимо поместить столбцы из трех исходных таблиц — B, S и P. Поэтому в запросе следует выполнить соединение этих таблиц. Таблицы B и S могут быть соединены с помощью:, :условия B.KodB=S.KodB, в результате чего отделения компании будут связаны с работающим в них персоналом. Таблицы S и P могут быть соединены с помощью условия S.KodS=P.KodS. В результате каждый работник будет связан с теми сдаваемыми в аренду объектами, за которые он отвечает. Результаты выполнения запроса представлены в табл. ____.

    KodB 

    City 

    KodS 

    Fname 

    Lname 

    KodP 

    B3

    Тюмень

    S14 

    Сидоров 

    Стеман 

    P04

    B3

    Тюмень 

    S14 

    Сидоров 

    Стеман 

    P16

    B3

    Тюмень 

    S37 

    Петрова 

    Татьяна 

    P21

    B3

    Тюмень 

    S37 

    Петрова 

    Татьяна 

    P36

    B5

    Тюмень 

    S41 

    Петров 

    Игорь 

    P94

    B7

    Нижневартовск 

    S09

    Зотова 

    Ирина 

    P14

    Пример 9.27. Группирование по нескольким столбцом.

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

    SELECT S.KodB, S.KodS, COUNT(*) AS C
    FROM S, P
    WHERE S.KodS = P.KodS
    GROUP BY S.KodB, S.KodS
    ORDER BY S.KodB, S.KodS;

    Чтобы составить требуемый отчет, прежде всего необходимо выяснить, какие из работников компании отвечают за сдаваемые в аренду объекты. Эту задачу можно решить посредством соединения таблиц S и P по ключу KodS.

    Затем необходимо сформировать группы, состоящие из номера отделения и табельных номеров его работников, для чего следует определить предложение GROUP BY. Наконец, результирующая таблица должна быть упорядочена с помощью задания предложения ORDER BY. Результаты выполнения запроса представлены в табл. ______.

    KodB 

    KodS 

    C 

    B3

    S14 

    2 

    B3 

    S37 

    2 

    B5 

    S41 

    1 

    B7

    S09

    1 

    Выполнение соединений

    Соединение является подмножеством более общей комбинации данных двух таблиц, называемой их декартовым произведением. Декартово произведение двух таблиц представляет собой другую таблицу, состоящую из всех возможных пар строк, входящих в состав обеих таблиц. Набор столбцов результирующей таблицы представляет собой все столбцы первой таблицы, за которыми следуют все столбцы второй таблицы. Если ввести запрос к двум таблицам без задания предложения WHERE, результат выполнения запроса в среде SQL будет представлять собой декартово произведение содержимого этих таблиц. Кроме того, стандарт ISO предусматривает специальный формат оператора SELECT, позволяющий вычислить декартово произведение двух таблиц:

    SELECT [DISTINCT | ALL] {column_list}
    FROM table_name1 CROSS JOIN table_name2

    Процедура генерации результирующей таблицы содержащего соединение оператора SELECT состоит в следующем.

    1.
    Формируется декартово произведение таблиц, указанных в предложении FROM.

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

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

    4. Если в исходном запросе присутствует фраза SELECT DISTINCT, из результирующей таблицы удаляются все строки-дубли. В реляционной алгебре действия, выполняемые на 3 и 4 этапах, эквивалентны операции проекции по столбцам, заданным в списке предложения SELECT.

    Открытые соединения

    При выполнении операции соединения данные из двух таблиц комбинируются с образованием пар связанных строк, в которых значения сопоставляемых столбцов одинаковые. Если строка одной из таблиц не находит себе соответствия в другой, то она не попадает в результирующий набор данных. Именно это правило применялось во всех рассмотренных выше примерах соединения таблиц. Стандартом ISO предусмотрен и другой набор операторов соединений, называемых открытыми соединениями. При открытом соединении в результирующую таблицу помещаются также строки, не удовлетворяющие условию соединения. Чтобы понять особенности выполнения операций открытых соединений, воспользуемся упрощенными таблицами B и P, содержимое которых представлено в табл. 13.31 и 13.32.

    B

    KodB 

    Bcity 

    B3

    Надым 

    B4

    Ишим 

    B2

    Тюмень 

     

    P

    KodP

    Pcity

    P14

    Сургут 

    P94

    Тюмень 

    P04

    Надым 

    Обычное (закрытое) соединение этих таблиц выполняется с помощью следующего SQL-оператора:

    SELECT B.*, P.* FROM B, P WHERE B.Bcity = Р.Pcity;

    Результаты выполнения этого запроса:

    KodB 

    Bcity 

    KodP

    Pcity

    B3 

    Надым

    P04

    Надым 

    B2

    Тюмень

    P94

    Тюмень 

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

    Пример 9.28. Левое открытое соединение.

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

    Используем левое открытое соединение этих двух таблиц, которое выглядит следующим образом:

    SELECT B.*, P.*
    FROM B LEFT JOIN P ON B,Bcity = P.Pcity;

    Результаты выполнения этого запроса:

    KodB 

    Bcity 

    KodP

    Pcity

    B3 

    Надым

    P04

    Надым 

    B4

    Ишим 

    Null 

    Null 

    B2

    Тюмень 

    P94

    Тюмень 

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

    Пример 9.29. Правое открытое соединение.

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

    Используем правое открытое соединение этих двух таблиц, которое выглядит следующим образом:

    SELECT B.*, P.*
    FROM B RIGHT JOIN P ON B,Bcity = P.Pcity;

    Результаты выполнения этого запроса:

    KodB 

    Bcity 

    KodP

    Pcity

    Null 

    Null 

    P14 

    Сургут

    B3 

    Надым

    P04

    Надым 

    B2

    Тюмень 

    P94

    Тюмень 

     

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

    Пример 9.30. Полное открытое соединение.

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

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

    SELECT B.*, P.*
    FROM B FULL JOIN P ON B,Bcity = P.Pcity;

    Результаты выполнения этого запроса:

    KodB 

    Bcity 

    KodP 

    Pcity 

    Null 

    Null 

    P14 

    Сургут

    B3 

    Надым

    P04

    Надым 

    B4 

    Ишим

    Null 

    Null 

    B2

    Тюмень 

    P94

    Тюмень 

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

    9.3.8. Ключевые слова EXISTS и NOT EXIST

    Ключевые слова EXISTS и NOT EXISTS предназначены для использования только совместно с подзапросами. Результат их обработки представляет собой логическое значение TRUE или FALSE. Для ключевого слова EXISTS результат равен TRUE в том и только в том случае, если в возвращаемой подзапросом результирующей таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результатом обработки ключевого слова EXISTS будет значение FALSE. Для ключевого слова NOT EXISTS используются правила обработки, обратные по отношению к ключевому слову EXISTS.

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

    Пример 9.31. Запрос с использованием ключевого слова EXISTS

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

    SELECT KodS, Lname, Fname, Dol
    FROM S
    WHERE EXISTS
    (SELECT * FROM B WHERE S.KodB = B.KodB AND City = ‘Тюмень’);

    Результаты выполнения запроса:

    KodS 

    Lname 

    Fname 

    Dol 

    S21

    Иванов 

    Иван 

    Руководитель 

    S41

    Петров 

    Игорь 

    Менеджер 

    Обратите внимание, что первая часть условия поиска, S.KodB = B.KodB, необходима для получения гарантий того, что для каждого работника будет анализироваться корректная строка данных об отделении компании. Если опустить это условие, то в результирующую таблицу запроса будут помещены сведения обо всех работниках компании, поскольку подзапрос всегда будет возвращать не менее
    одной строки и
    проверка существования в каждом случае будет давать значение TRUE.

    Кроме того, этот запрос можно записать, используя методы соединения:

    SELECT KodS, Lname, Fname, Dol FROM S, B
    WHERE S.KodB = B.KodB AND City = ‘Тюмень’;

    13.3.9. Комбинирование результирующих таблиц (операции UNION, INTERSECT и EXCEPT)

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

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

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

    Разностью двух таблиц А и В называется таблица, содержащая все строки, которые присутствуют в таблице А, но отсутствуют в таблице В.

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

    Три операции над множествами, предусмотренные стандартом ISO, носят название UNION, INTERSECT и EXCEPT. В каждом случае формат предложения с операцией над множествами должен быть следующим:

    operator [ALL] [CORRESPONDING [BY (column1 [,… ]))]

    При указании фразы CORRESPONDING BY операция над множествами выполняется для указанных столбцов. Если задано только ключевое слово CORRESPONDING, а фраза BY отсутствует, операция над множествами выполняется для столбцов, которые являются общими для обеих таблиц. Если указано ключевое слово ALL, результирующая таблица может содержать дублирующиеся строки. Одни диалекты языка SQL не поддерживают операций INTERSECT и EXCEPT, а в других вместо ключевого слова EXCEPT используется ключевое слово MINUS.

    Пример 9.32. Использование операции UNION

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

    (SELECT City FROM B) UNION (SELECT City FROM P)

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

    Результат выполнения запроса

    City

    Тюмень 

    Нижневартовск 

    Ишим 

    Надым 

    Сургут 

     

    ние операции INTERSECT

    ; Создайте список всех городов, в которых располагаются и отделения компании,! ‘ и сдаваемые в аренду объекты.

    ‘, (SELECT city ила (SELECT *

    ; FROM branch) FROM branch)

    ; INTERSECT INTERSECT CORRESPONDING BY city

    ‘, (SELECT city (SELECT *

    : FROM property for rent); FROM property for rent);

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

    ‘ Таблица 13.39. Результат выполнения запроса из примера 13.33

    Aberdeen Glasgow London

    (3 строки)

    ‘ Этот запрос можно записать и без использования операции INTERSECT: :; (SELECT Ь.city nna SELECT DISTINCT city

    , ‘РВОМ branch Ь, property for rent p FROM branch Ь ] 1 , ,’WHERE b.city = p.city; WHERE EXISTS

    (SELECT *

    FROM property for rent p WHERE p.city = b.city);

    ‘ ,Возможность создавать запросы в нескольких эквивалентных формах является од- ‘ ,,’ ним из самых существенных недостатков языка SQL.

    : Пример 13.34. ИспользоВание операции ЕХСКРТ

    I Создайте список всех городов, в которых имеется отделение компании, но нет ‘ сдаваемых в аренду объектов.

    ! (SELECT city или (SELECT ~

    FROM branch) FROM branch)

    EXCEPT EXCEPT CORRESPONDING BY city (SELECT city (SELECT *

    FROM property for rent); FROM property for rent);

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

    : Таблица 13.40. Результат выполнения запроса из примера 13.34

    Bristol

    (1 строка)

    ,’ Этот запрос можно записать и без использования операции EXCEPT:

    SELECT DISTINCT city или SELECT DISTINCT city FROM branch FROM branch b WHERE city NOT IN (SELECT city

    ‘ FROM property for rent); WHERE NOT EXISTS (SELECT *

    FROM property for rent p WHERE p.city = Ь, city);

     

    . Изменение содержимого базы данных

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

  • INSERT — предназначен для добавления данных в таблицу;
  • UPDATE — предназначен для модификации уже помещенных в таблицу данных;
  • DELETE — позволяет удалять из таблицы строки данных.

    Добавление новых данных в таблицу (оператор INSERT)

    Существует две формы оператора INSERT. Первая предназначена для вставки единственной строки в указанную таблицу. Эта форма оператора INSERT имеет следующий формат:

    INSERT INTO table_name [(column_list)] VALUES (data_value_list)

    Здесь параметр table name — имя таблицы — может представлять либо имя таблицы базы данных, либо имя обновляемого представления.

    Параметр column_list
    — список столбцов — представляет собой список имен столбцов. Параметр является необязательным. Если он опущен, то предполагается использование списка из имен всех столбцов таблицы, указанных в том порядке, в котором они были описаны в операторе CREATE TABLE. Если в операторе INSERT указывается конкретный список имен столбцов, то любые опущенные в нем столбцы должны быть объявлены при создании таблицы как допускающие значение NULL — за исключением случаев, когда при описании столбца использовался параметр DEFAULT.

    Параметр data_value_list — список значений данных — должен следующим образом соответствовать параметру column_list:

    количество элементов в обоих списках должно быть одинаковым;

    должно существовать прямое соответствие между позициями элементов в обоих списках (первый элемент списка data_value_list полагается относящимся к первому элементу списка column_list и т. д.);

    типы данных элементов списка data value list должны быть совместимы с типом данных соответствующих столбцов таблицы.

    Пример 9.35. Использование конструкции INSERT

    Добавить в таблицу S новую запись, содержащую данные во всех столбцах.

    INSERT INTO S VALUES
    (‘S16’, ‘Кузьмин’, ‘Петр’, ‘М’, DATE ‘1957-05-25’, ‘Агент’, 430, ‘ВЗ’);

    Пример 9.36. Вставка новой записи с использованием значений, принимаемых по умолчанию

    Добавить в таблицу S новую запись, содержащую данные во всех обязательных столбцах KodS , LName, FName, Dol, ZP и KodB.

    INSERT INTO S (KodS , LName, FName, Dol, ZP и KodB)
    VALUES (‘S44’, ‘Семенова’, ‘Ирина’, ‘Агент’, 480, ‘B5’);

    Поскольку данные вставляются только в определенные столбцы таблицы, необходимо указать имена столбцов, в которые эти данные будут помещаться. Порядок следования имен столбцов несущественен, однако более естественно указать их в том порядке, в каком они следуют в таблице. Кроме того, оператор INSERT можно было бы записать и таким образом (явное указание, что в столбцы Pol и DR должны быть помещены значения NULL):

    INSERT INTO S
    VALUES (‘S44’, ‘Семенова’, ‘Ирина’, NULL, NULL, ‘Агент’, 480, ‘B5’);

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

    INSERT INTO table_name [(column_list)] SELECT …

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

    Пример 9.37. Использование конструкции INSERT … SEIECT

    Предположим, что существует таблица SP, у которой имеются столбцы для хранения кода сотрудника, фамилии, имени работников компании и количества сдаваемых в аренду объектов, за которые они отвечают:

    SP(KodS, Lname, Fname, Pcount)

    Необходимо заполнить таблицу SP данными, используя информацию из таблиц S и P

    INSERT INTO SP
    (SELECT S.KodS, Lname, Fname, Pcount, COUNT(*)
    FROM S, P
    WHERE S.KodS = P.KodS
    GROUP BY S.KodS, Lname, Fname)
    UNION
    (SELECT KodS, Lname, Fname, 0
    FROM S WHERE KodS NOT IN
    (SELECT DISTINCT KodS FROM P));

    Если опустить вторую часть операции UNION, то будет создан список только тех работников компании, которые в настоящее время отвечают хотя бы за один объект. Иначе говоря, из результатов будут исключены все работники, которые в данный момент не отвечают ни за один сдаваемый в аренду объект. По этой причине для составления полного списка работников компании необходимо использовать оператор UNION, в котором второй оператор SELECT предназначен для выборки сведений именно о таких работниках, причем в столбец Pcount соответствующих строк помещается значение 0.

    KodS 

    Lname 

    Fname 

    Pcount 

    S21

    Иванов 

    Иван 

    0 

    S37

    Петрова 

    Татьяна 

    2 

    S14

    Сидоров 

    Степан 

    2 

    S09

    Зотова 

    Ирина 

    1 

    S05

    Степанова 

    Елена 

    0 

    S41

    Петров 

    Игорь 

    1 

    Модификация данных в базе (оператор UPDATE)

    Оператор UPDATE позволяет изменять содержимое уже существующих строк указанной таблицы. Этот оператор имеет следующий формат:

    UPDATE имя_таблицы
    SET имя_столбца1 = значение1
    [, имя_столбца2 = значение2 … ] [WHERE предикат]

    Здесь параметр имя_таблицы представляет либо имя таблицы базы данных, либо имя обновляемого представления. В предложении SET указываются имена одного или более столбцов, данные в которых необходимо изменить.

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

    Пример 9.38. Обновление всех строк таблицы

    Повысить всему персоналу заработную плату на 3%.

    UPDATE S SET ZP = ZP*1.03;

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

    Пример 9.39. Обновление некоторых строк таблицы.

    Повысить заработную плату всем менеджерам компании на 5%.

    UPDATE S SET ZP = ZP*1.05 WHERE Dol = ‘Менеджер’;

    Пример 9.40. Обновление нескольких столбцов

    Перевести Петрова Игоря (KodS= ‘S41’) на должность руководителя и повысьте ему зарплату до 550.

    UPDATE S SET Dol = ‘Руководитель’, ZP = 550 WHERE KodS= ‘S41’;

    Удаление данных из базы (оператор DELETE)

    Оператор DELETE позволяет удалять строки данных из указанной таблицы. Этот оператор имеет следующий формат:

    DELETE FROM имя_таблицы [WHERE предикат]

    Как и в случае операторов INSERT и UPDATE, параметр имя_таблицы может представлять собой либо имя таблицы базы данных, либо имя обновляемого представления. Предложение WHERE является необязательным — если оно опущено, из таблицы будут удалены все
    существующие в ней строки (сама таблица удалена не будет). Если необходимо удалить не только содержимое таблицы, но и ее определение, следует использовать оператор DROP TABLE. Если предложение WHERE присутствует, из таблицы будут удалены только те строки, которые удовлетворяют условию отбора.

    Пример 9.41. Удаление определенных строк таблицы.

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

    DELETE FROM V WHERE KodP = ‘P4’;

    Пример 9.42. Удаление всех строк таблицы.

    Удалить все строки из таблицы V.

    DELETE FROM М;

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

    9.4. Определение данных

    Язык DDL (Data Definition Language), будучи частью языка SQL, позволяет создавать и уничтожать различные объекты базы данных — например, схемы, домены, таблицы, представления или индексы. Рассмотрим операторы создания и удаления схемы, таблицы и индексы.

    Основными операторами языка SQL, предназначенными для определения данных, являются следующие:

    Создание 

    Модификация 

    Удаление 

    CREATE SCHEMA

     

    DROP SCHEMA 

    CREATE DOMAIN 

    ALTER DOMAIN 

    DROP DOMAIN 

    CREATE TABLE 

    ALTER TABLE 

    DROP TABLE 

    CREATE VIEW

     

    DROP VIEW

    Эти операторы используются для создания, изменения и уничтожения структур из которых состоят концептуальные схемы.

    Кроме того, многие реализации языка SQL дополнительно включают следующие операторы CREATE INDEX и DROP INDEX

    9.4.1. Идентификаторы языка SQL

    Идентификаторы языка SQL предназначены для обозначения объектов в базе данных и являются именами таблиц, представлений и столбцов. Символы, которые могут использоваться в создаваемых пользователем идентификаторах языка SQL, должны быть определены как набор символов. Стандарт ISO задает набор символов, который должен использоваться по умолчанию, — он включает строчные и прописные буквы латинского алфавита (А-Z, a-z), цифры (0-9) и символ подчеркивания (_). Допускается использование и альтернативного набора символов. На формат идентификаторов накладываются следующие ограничения:

    идентификатор может иметь длину до 128 символов (большинство диалектов предусматривает более жесткие ограничения);

    идентификатор должен начинаться с буквы;

    идентификатор не может содержать пробелов.

    9.4.2. Типы данных языка SQL, определенные стандартом ISO

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

    Символьные данные (тип character)

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

    CHARACTER [VARYING] [length]

    могут использоваться сокращения:

    CHARACTER    —     CHAR,

    CHARACTER VARYING    —     VARCHAR.

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

    Например, столбец Pind таблицы B имеет фиксированную длину в 6 символов и может быть объявлен следующим образом:

    Pind CHAR(6)

    Столбец Address таблицы B имеет переменную длину значения (максимум до 30 символов), поэтому он может быть объявлен следующим образом:

    Address VARCHAR(30)

    Битовые данные (тип bit)

    Битовый тип данных используется для определения битовых строк, т.е. последовательности двоичных цифр (битов), каждая из которых может иметь значение либо 0, либо 1. Для определения данных битового типа используется формат, сходный с определением символьных данных:

    ВIT [VARYING] [length]

    Например, для сохранения битовой строки с фиксированной длиной и значением ‘0011’ может быть объявлен столбец BS:

    BS BIT(4)

    Точные числа (тип exact numeric)

    Тип точных числовых данных используется для определения чисел, которые имеют точное представление в компьютере. Числа состоят из цифр, необязательной десятичной точки и необязательного символа знака. Данные точного числового типа определяются значностью (precision) и длиной дробной части (scale). Значность задает общее количество значащих десятичных цифр числа, в которое входят длина целой и дробной частей, но без учета самой десятичной точки. Дробная часть указывает количество дробных десятичных разрядов числа. Особой разновидностью точных чисел являются целые числа. Существует несколько способов определения данных точного числового типа:

    NUMERIC [ precision [, scale] ]

    DECIMAL [ precision [, scale] ]

    INTEGER

    SMALLINT

    INTEGER может быть сокращено до INT, а DECIMAL до DEC.

    Типы NUMERIC и DECIMAL предназначены для хранения чисел в десятичном формате. По умолчанию длина дробной части равна нулю, а принимаемая по умолчанию значность зависит от реализации. Тип INTEGER используется для хранения больших положительных или отрицательных целых чисел. Тип SMALLINT используется для хранения небольших положительных или отрицательных целых чисел. При использовании этого типа данных расход внешней памяти существенно сокращается. Максимальное абсолютное
    значение числа, которое может сохраняться в столбцах с типом данных SMALLINT, составляет 32 767. Для столбца Rooms таблицы P, в котором сохраняются сведения о количестве комнат сдаваемого в аренду объекта, можно выбрать тип SMALLINT и объявить его следующим образом:

    Rooms SMALLINT

    Столбец ZP
    таблицы S может быть объявлен следующим образом:

    ZP DECIMAL (6,2)

    В этом случае максимальное значение составит 9 999.99.

    Округленные числа (тип approximate numeric)

    Тип округленных чисел используется для описания действительных чисел. Округленные числа или числа с плавающей точкой представляются в научной нотации, при которой число записывается с помощью мантиссы и порядка, например: 10ЕЗ, +5.2Е6, -0.2Е-4. Существует несколько способов определения данных с типом округленных чисел:

    FLOAT [precision]

    REAL

    DOUBLE PRECISION

    Параметр precision задает значность мантиссы. Значность определений типа REAL и DOUBLE PRECISION зависит от конкретной реализации.

    Дата и время (тип datetime)

    Тип данных «дата/время» используется для определения моментов времени с некоторой установленной точностью. Примерами являются даты, отметки времени и время суток. Стандарт ISO разделяет тип данных «дата/время» на подтипы YEAR (Год), MONTH (Месяц), DAY (День), HOUR (Час), MINUTE (Минута), SECOND (Секунда)). Поддерживается три типа полей даты/времени:

    DATE

    TIME [time_precision]

    TIMESTAMP [time_precision]

    Тип данных DATE используется для хранения календарных дат, включающих поля YEAR, MONTH и DAY. Тип данных TIME используется для хранения отметок времени, включающих поля HOUR, MINUTE и SECOND. Тип данных TIMESTAMP используется для совместного хранения даты и момента времени. Параметр time_precision
    задает количество дробных десятичных знаков, определяющих точность сохранения значения в поле SECOND. Если этот параметр опускается, по умолчанию его значение для столбцов типа TIME принимается равным нулю (т.е. сохраняются целые секунды), тогда как для полей типа TIMESTAMP он принимается равным 6 (т.е. отметки времени сохраняются с точностью до миллисекунд). Например, столбец Date таблицы Viewing, представляющий дату (день, месяц и год) осмотра клиентом сдаваемого в аренду объекта, может быть определен следующим образом:

    date DATE

    Данные типа interval

    Данные с типом интервала используются для представления периодов времени. Любой интервальный тип данных состоит из набора отдельных полей: YEAR, MONTH, DAY, HOUR, MINUTE и SECOND. Существует два класса данных с интервальным типом: интервалы год-месяц и интервалы день-время. В
    первом случае данные включают только два поля — YEAR и/или MONTH. Данные второго типа могут состоять из произвольной последовательности полей DAY, HOUR, MINUTE, SECOND. Данные интервального типа определяются следующим образом:

    INTERVAL ((start field TO end field) single datetime field)
    start field = YEAR 1 MONTH 1 DAY 1 HOUR 1 MINUTE
    [(interval leading field precision)
    [ end field = YEAR 1 MONTH 1 DAY 1 HOUR 1 MINUTE 1 ЯЕСОНО [(fractional seconds precision) ] single datetime field = start field 1 SECOND [ (i n terval leading fi el d precision (,fractional seconds precision])]

    Во всех случаях для параметра start field необходимо указать размерность первого поля (interval leading field precision), которая по умолчанию принимается равной двум. Например:

    INTERVAL YEAR(2) TO MONTH

    Это объявление описывает интервал времени, значение которого может находиться между 0 годом, 0 месяцем и 99 годом, 11 месяцем. Еще один пример: INTERVAL HOUR TO SECOND(4)

    Это объявление описывает интервал времени, значение которого может меняться от 0 часов, 0 минут, 0 секунд до 99 часов, 59 минут 59.9999 секунды. (Значность первого поля по умолчанию устанавливается равной 2, а число дробных десятичных знаков для секунд явно задано равным 4.)

    Скалярные операторы

    Язык SQL включает некоторое количество встроенных скалярных операторов и функций, которые могут использоваться для построения скалярных выражений, т.е. выражений, вычисление которых дает скалярный результат. Помимо обычных арифметических операторов (+, -, * и /) в языке определены и другие операторы, представленные в табл. ___.

    Таблица ___. Скалярные операторы языка SQL

    Оператор 

    Назначение 

    BIT_LENGTH 

    Возвращает длину заданной строки в битах. Например, результат вычисления выражения BIT_LENGTH(X’FFFF’) равен 16

    OCTET_LENGTH 

    Возвращает длину заданной строки в октетах (длина в битах, деленная на 8). Например, результат вычисления выражения OCTET_LENGTH(X’FFFF’) равен 2 

    CHAR_LENGTH

    Возвращает длину заданной строки в символах (или в октетах, если строка является битовой строкой). Например, результат вычисления выражения CHAR_LENGTH(‘Beech’) равен 5 

    CAST( … AS …)

    Преобразует значение выражения, построенного из данных одного типа, в значение другого типа данных.

    || 

    Оператор конкатенации. Соединенные этим оператором две символьные или битовые строки преобразуются в одну строку. Например, выражение Lname || Fname

    LOWER

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

    UPPER 

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

    TRIM

    Функция удаляет ведущие (LEADING), конечные (TRAILING) или те и другие (BOTH) указанные символы из заданной строки. Например, вычисление выражения
    TRIM (BOTH ‘*’ FROM ‘**Hello World *’)
    даст результат ‘Hello World’

    POSITION 

    Функция возвращает позицию одной строки в пределах другой строки. Например, в результате вычисления выражения POSITION (‘ее’ IN ‘Beech’) будет получено значение 2

    SUBSTRING

    Функция выполняет выделение подстроки из заданной строки, Например, в результате вычисления выражения
    SUBSTRING( Bеесh’ FROM 1 TO 3) будет получено значение ‘Bee’

    САSЕ

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

    CASE type
    WHEN ‘дом’ THEN 1
    WHEN ‘квартира’ THEN 2
    ELSE 0
    END

    CURRENT_DАТЕ

    Функция возвращает текущую дату

    CURRENT_TIME

    Функция возвращает текущее время. Например, выражение CURRENT TIME(6) возвратит текущее время с точностью до микросекунд

    EXTRACT

    Функция возвращает значение указанного поля из значения типа даты или времени. Например, в результате вычисления выражения EXTRACT(YEAR FROM staff dob) будет извлечено значение года в колонке Dob записи таблицы Staff

    9.4.3. Создание баз данных

    В различных СУБД процедура создания баз данных существенно отличается. В многопользовательских системах право создания баз данных обычно закрепляется только за администратором базы данных (АБД). В однопользовательских системах принимаемая по умолчанию база данных может быть создана непосредственно в процессе установки и настройки параметров самой СУБД.

    В соответствии со стандартом ISO, таблицы и другие объекты базы данных существуют в некоторой среде (environment). Помимо всего прочего, каждая среда состоит из одного или более каталогов (catalog), а каждый каталог — из набора схем (shema). Схема представляет собой поименованную коллекцию объектов базы данных, которые некоторым образом связаны друг с другом (все объекты в базе данных должны быть описаны в той или иной схеме). Объектами схемы могут быть таблицы, представления, домены, утверждения, сопоставления, толкования и наборы символов. Все объекты схемы имеют одного и того же владельца и множество общих значений, принимаемых по умолчанию.

    Стандарт оставляет право выбора конкретного механизма создания и уничтожения каталогов за разработчиком СУБД.

    однако регламентирует механизм создания и удаления схем. Оператор определения схемы имеет следующий формат (упрощенно):

    CREATE SCHEMA [name | AUTHORIZATION creator-identifier]

    Таким образом, если создателем схемы под именем sql test будет пользователь Smith, то данный оператор будет выглядеть следующим образом:

    CREATE SCHEMA sql tests AUTHORIZATION Smith;

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

    Схема удаляется с помощью оператора DROP SCHEMA, который имеет следующий формат:

    DROP SCHEMA паше [ RESTRICT I CASCADE ]

    Если указано ключевое слово RESTRICT (именно оно принимается по умолчанию), схема должна быть пустой, иначе выполнение операции будет отменено. Если указа- но ключевое слово CASCADE, при выполнении оператора будут автоматически удалены все связанные с удаляемой схемой объекты, причем в порядке, указанном выше. Если одна из этих операций удаления будет завершена неудачно, выполнение всего оператора DROP SCHEMA будет отменено. Общий эффект от выполнения оператора DROP SCHEMA с параметром CASCADE может оказаться весьма обширным, поэтому подобные операторы должны вводиться с исключительной осторожностью.

    В настоящее время операторы CREATE SCHEMA и DROP SCHEMA реализованы в очень немногих СУБД.

    Для создания БД используется оператор SQL, имеющий формат:

    CREATE {DATABASE | SCHEMA} «<имя_файла>»
    [ USER «имя_пользователя» [PASSWORD «пароль»]][PAGE_SIZE [=] целое][LENTH [=] целое][DEFAULT CHARACTER SET набор символов]

    «<имя_файла>» 

    Имя файла, в котором будет храниться содержимое БД

    «имя_пользователя»
    «пароль»

    Имя пользователя и пароль, которые проверяются при соединении пользователя с сервером 

    PAGE_SIZE [=] целое

    Размер страницы БД в байтах (1024, 2048, 4096, 8192)

    LENTH [=] целое 

    Длина файла в страницах (>50, по умолчанию 75)

    DEFAULT CHARACTER SET набор символов

    Определяет набор символов, применяемый в БД. Например, WIN1251

    9.4.4. Создание таблиц (оператор CREATE TABLE)

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

    CREATE TABLE ИмяТаблицы (
    <определение_столбца1> [<ограничение1>] [<определение_столбца2]| [<ограничение2>]…)

    <определение_столбца> = имя_столбца
    {тип_данных | COMPUTED [BY] (<выражение>) | домен}
    [DEFAULT { литерал |NULL | USER}] [NOT NULL] [<ограничение>]

    Здесь:

    имя_столбца 

    допустимое имя столбца 

    тип_данных 

    допустимый тип данных 

    COMPUTED [BY] (<выражение>) 

    служит для определения столбца вычисляемых значений

    DEFAULT 

    определяет значение, которое по умолчанию присваивается столбцу новой записи 

    [<ограничение> 

    ограничения, накладываемые на значения в столбце 

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

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

    Пример 9.43. Создание таблицы

    Для иллюстрации процесса разработки таблиц мы рассмотрим операторы создания двух таблиц приложения: Staff (S) и Property_for_Rent (P):

    CREATE TABLE Staff(
    KodS    VARCHAR(5)     NOT NULL
    Lname    VARCHAR(15)     NOT NULL,
    Fname    VARCHAR(15)     NOT NULL,
    Address VARCHAR(50),
    Pol    СНАR,        
    DR     DATETIME
    Dol    VARCHAR (10)    NOT NULL,
    ZP    DECIMAL(7,2)    NOT NULL,
    KodB    CHAR(9)        NoT NULL;

    CREATE TABLE Property_for_Rent(
    KodP    VARCHAR(5)    NOT NULL,
    City    VARCHAR(15)    NOT NULL,
    Street    VARCHAR(25)    NOT NULL,
    Type    СНАR        NOT NULL
    Rooms    SMALLINT        NOT NULL,
    Rent    DЕСIМАL(6,2)    NOT NULL,
    KodO    VARCHAR(5)    NOT NULL,
    KodS    VARCHAR(5),
    KodB    VARCHAR(3)     NOT NULL)

    В случае таблицы Property_for_Rent, хотя столбец с табельным номером работника KodS является внешним ключом, для него не указывается ключевое слово NOT NULL, поскольку могут существовать определенные периоды времени, когда сдаваемый в аренду объект не
    будет отвечать ни один из работников компании. (Например, подобная ситуация возможна при первичной регистрации нового объекта.) Однако остальные внешние ключи этой таблицы — KodO (номер владельца) и KodB (номер отделения компании) — должны быть определены с указанием этого ключевого слова.

    9.4.5. Удаление таблиц (оператор DROP TABLE)

    Таблицы удаляются из базы данных с помощью оператора DROP TABLE, имеющего следующий формат:

    DROP TABLE имя_таблицы [RESTRICT | CASCADE]

    Например, для удаления таблицы Property for Rent можно использовать следующий оператор:

    DROP TABLE Property_for_Rent;

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

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

    13.4.6. Создание индекса (оператор CREATE INDEX)

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

    CREATE [UNIQUE] INDEX <имя_индекса> ON <имя_таблицы>
    (столбцы [ASC | DESC] [,… ])

    Указанные в операторе столбцы составляют ключ индекса и должны быть перечислены в возрастающем или убывающем порядке. Индексы могут создаваться только для таблиц базы данных, но не для представлений. Если в операторе указано ключевое слово UNIQUE, уникальность значений ключа индекса будет автоматически поддерживаться системой. Требование уникальности значений обязательно для первичных ключей, а также, возможно, и для других столбцов таблицы (например, для альтернативных ключей). Хотя создание индексов возможно в любой момент, при построении индекса для уже заполненной данными таблицы могут возникнуть проблемы, связанные с дублированием данных в различных строках. Следовательно, имеет смысл создавать уникальные индексы (по крайней мере для первичного ключа) непосредственно при создании таблицы. В результате система возьмет на себя контроль за уникальностью значений данных в соответствующих столбцах.

    Для таблиц Staff и Property_for_Rent должны быть созданы, по крайней мере, следующие индексы:

    CREATE UNIQUE INDEX KodS_ind ON Staff (KodS);

    CREATE UNIQUE INDEX KodP_ind ON Property_for_Rent (KodP);

    Для каждого из ключевых столбцов может быть указан порядок следования значений — по возрастанию (ASC) или по убыванию (DESC), причем значение ASC используется по умолчанию. Например, для таблицы Property for Rent можно создать следующий индекс:

    CREATE INDEX Rent_ind ON Property_for_Rent (Type, Rooms);

    При обработке этого оператора будет создан файл (Paradox) под именем Rent_ind, содержащий данные вновь созданного индекса. Строки в этом файле будут расположены в порядке возрастания значений столбца Type, а внутри них — в порядке возрастания значений столбца Rooms.

    9.4.7. Удаление индекса (оператор DROP INDEX)

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

    DROP INDEX имя_индекса

    10. Представления (виды, просмотры)

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

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

    10.1. Создание представлений (оператор CREATE VIEW)

    Оператор CREATE VIEW имеет следующий формат:

    CREATE VIEW имя_представления
    [(имя_столбца1 [,… ])]
    AS <оператор_Select>
    [WITH CHECK OPTION]

    Существует необязательная возможность присвоения собственного имени (параметр имя_столбца) каждому из столбцов представления. Если указывается список имен столбцов, то он должен иметь количество элементов, равное количеству столбцов в результирующей таблице запроса, заданного параметром <оператор_Select>.
    Если список имен столбцов опущен, каждый столбец представления будет иметь имя соответствующего столбца результирующей таблицы запроса. Список имен столбцов должен обязательно задаваться в том случае, если в именах столбцов результирующей таблицы имеет место неоднозначность. Подобная ситуация возникает в тех случаях, когда подзапрос включает вычисляемые поля, а фраза AS с именами столбцов результирующей таблицы не содержит для них имен или же когда результирующая таблица создается с помощью операции соединения и включает столбцы с одинаковыми именами.

    Заданный параметром <оператор_Select>
    подзапрос принято называть определяющим запросом. Если указана фраза WITH СНЕСК OPTION, то гарантируется, что в тех случаях, когда вводимая строка данных не удовлетворяет условию, указанному в предложении WHERE определяющего запроса представления, она не будет добавлена в его базовую таблицу.

    Пример 10.1. Создание горизонтального представления

    Создайте представление, позволяющее менеджеру отделения компании с номером; ‘B3’ иметь доступ к данным только тех сотрудников, которые работают в этом отделении.

    CREATE VIEW M3_S AS
    SELECT * FROM Staff WHERE KodB = ‘B3’;

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

    В результате выполнения этого оператора будет создано представление, включающее все столбцы таблицы Staff, но содержащее только те ее строки, в которых номер отделения компании равен ‘В3’. (В этом, представлении столбец KodB не является необходимым и вполне может быть исключен, поскольку он всегда будет содержать одно и то же значение — ‘B3’.)

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

    Пример 10.2. Создание вертикального представления.

    Создайте представление, содержащее данные о работниках отделения компании с номером ‘B3’, но не включающее сведений об их зарплате, поскольку сведения о зарплате работников должны быть доступны только менеджерам тех отделений, в которых они работают.

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

    CREATE VIEW S3
    AS SELECT KodS, Lname, Fname, Pol, DR,Dol FROM Staff
    WHERE KodB = ‘BЗ’;

    Обратите внимание, что определение этого представления можно переписать, воспользовавшись вместо таблицы Staff представлением MЗ_S:

    CREATE VIEW S3
    AS SELECT KodS, Lname, Fname, Pol, DR,Dol FROM MЗ_S;

    В том и другом случае будет создано представление под именем S3, содержащее все столбцы таблицы Staff, за исключением столбцов ZP и KodB.

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

    Пример 10.3. Представление с группированием и соединением

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

    CREATE VIEW staff prop cnt (branch по, staff по, cnt) AS SELECT s.bno, s.sno, COUNT(*) FROM staff s, property for rent p WHERE s.sno = р.sno GROUP BY s.bno, в.sno;

    Этот пример иллюстрирует использование подзапроса, содержащего предложение: GROUP BY (в результате чего создается представление, называемое группированным: представлением) и обращающегося к данным нескольких таблиц (подобные представления называют соединенными представлениями). Одной из важнейших при-:, чин использования представлений является стремление к упрощению многотабличных запросов. После определения представления с соединением нескольких таблиц можно будет использовать простейшие однотабличные запросы к этому представлению вместо сложных запросов с выполнением того же самого многотабличного соединения. Отметим, что в приведенное выше определение представления потребовалось включить список имен столбцов, поскольку в нем использована I ; обобщающая функция Count.

    10.1.2. Удаление представлений (оператор DROP UIEW)

    Представление удаляется из базы данных с помощью оператора DROP VIEW, имеющего следующий формат:

    DROP VIEW <имя> [REEPRICP | CRECRDEI]

    Если указано ключевое слово CASCADE, при выполнении оператора DROP VIEW будут удалены все связанные с ним или зависящие от него объекты. Другими словами, будут удалены все объекты, содержащие ссылки на удаляемое представление. Если указывается ключевое слово RESTRICT и существуют любые прочие объекты, зависящие от существования удаляемого представления, выполнение этого оператора блокируется. По умолчанию принимается значение RESTRICT.

     

    10.1.3. Разрешение представлений

    Познакомившись с тем, как представления создаются и используются, давайте детально проанализируем процедуру выполнения запроса, обращающегося к некоторому представлению. Для иллюстрации процесса разрешения представления рассмотрим приведенный ниже запрос, предназначенный для подсчета сдаваемых в аренду объектов, за которые отвечает каждый из работников отделения компании с номером ‘ВЗ’. Этот запрос обращается к представлению Staff Prop Cnt, определение которого приведенного в описании примера 10.3:

    SELECT staff во, cnt FROM staff prop cnt

    WHERE branch во = ‘B3’ ORDER BY staff no;

    Разрешение представления заключается в слиянии приведенного выше запроса с определяющим запросом представления Staff Prop Cnt и выполняется следующим образом.

    1. Имена столбцов, указанные в списке предложения SELECT запроса, транслиру- ются в соответствующие им имена .столбцов определяющего запроса. В результате предложение SELECT приобретает следующий вид:

    SELECT s.sno AS staff по, COUNT(*) AS cnt

    2. Имена представлений, указанные в предложении FROM запроса, замешаются соответствующими списками из предложений FROM определяющего запроса: FROM staff s, property for rent p

    3. Предложение WHERE исходного запроса объединяется с предложением WHERE из определяющего запроса представления с помощью логического оператора AND: WHERE я.sno = р.sno AND bno = ‘B3’

    4. Предложения GROUP BY и HAVING из определяющего запроса представления про- сто копируются в исходный запрос. В нашем примере в определяющем запро-

    Г’ се присутствует только предложение GROUP BY:

    GROUP BY s.sno, в.bno

    5. Наконец, в объединенный запрос копируется предложение ORDER BY из исходного запроса, в котором имена столбцов представления транслируются в имена столбцов определяющего запроса: ORDER BY s.sno

    6. В результате всех перечисленных выше операций объединенный запрос при- обретает следующий вид:

    SELECT s.sno AS staff во, COUNT(*) AS cnt FROM staff s, property for rent p WHERE s.sno = р.sno AND bno = ВЗ- ~~011Р ву s.sno, s.bno ORDER BY s.sno;

    Результаты выполнения объединенного запроса представлены .

    Таблица 10.4. Результирующая таблица запроса, полученного в результате разрешения представления

    10.1.4. Ограничения на использование представлений

    Стандарт ISO включает несколько важных ограничений на создание и использо- вание представлений, хотя в этом отношении между разными диалектами языка НЧ1 существуют определенные отличия.

    Если столбец в представлении создается с использованием обобщающей функции, то этот столбец может указываться только в предложениях SELECT и ORDER BY тех запросов, в которых осуществляется доступ к данно- му представлению. В частности, подобный столбец не может использовать- ся в предложении WHERE, а также не может быть аргументом в обобщающей функции любого из запросов, обращающихся к данному представлению. Например, рассмотрим представление Staff Prop Cnt, определение которого дано в примере 10.3. В этом представлении столбец Cnt содержит результа- ты применения обобщающей функции COUNT. По этой причине приведен- ный ниже запрос является некорректным: SELECT COUNT(cnt)

    FROM staff prop cnt;

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

    FROM staff prop cnt WHERE cnt ) 2;

    В данном случае ошибка заключается в том, что в предложение ИНЕИ зя. проса помещен столбец Cnt представления, создаваемый с помощью обоб. щающей функции.

    Сгруппированное представление никогда не должно соединяться с табли. цами базы данных или другими представлениями. Например, представлю. ние Staf Prop Cnt является сгруппированным, поэтому любая попыткя выполнить соединение этого представления с таблицей или другим прея. ставлением будет расценена системой как ошибка.

    10.1.5. Обновление данных в представлениях

    Все обновления, выполненные в таблице базы данных, немедленно отображаются во всех представлениях, включающих обращения к этой таблице. Подобным же образом, можно ожидать, что если данные были изменены в представлении, то это изменение будет отображено и в той таблице (таблицах) базы данных, на которой око построено. Однако это не так. Еще раз обратимся к представлению Staff Prop Cnt, определенному в примере 10.3:

    CREATE VIEW staff prop cnt (branch по, staff по, cnt) AS SELECT s.bno, s.sno, COUNT(*)

    FROM staff s, property for rent p WHERE в.sno = р.sno

    GROUP BY s.bno, s.sno;

    Оно содержит данные, представленные в табл. 10.5.

    Таблица 10.5. Данные, доступные в представлении Staf f Prop Cnt

    Рассмотрим, что произойдет, если попытаться вставить в данное представления запись, отмечающую, что в отделении с номером ‘B3’ работник с личным номерок ‘SQ5’ отвечает за 2 сдаваемых в аренду объекта. Для этой цели воспользуемся следующим оператором:

    INSERT INTO staff prop cnt

    VALUES (‘B3’, ‘SG5’, 2);

    При обработке этого оператора потребуется вставить в таблицу Property for Rent яяя записи, отмечающие тот факт, что работник с личным номером ‘SG5 отвечает за явя объекта недвижимости. Однако это невозможно будет сделать, поскольку неизвестнО, за какие именно сдаваемые в аренду объекты отвечает данный работник. Ведь мы ке знаем даже значений первичного ключа записей таблицы Property .for Rent, не говоря уже о других ее столбцах. Попробуем изменить определение представления и заменил столбец с количеством записей столбцом с номерами сдаваемых в аренду объектов:

    CREATE VIEW staff prop list (branch по, staff по, property по) AS SELECT s.bno, s.sno, р.рпо

    FROM staff s, property for rent p WHERE в.sno = р.sno;

    А теперь попробуем добавить в представление новую запись: «‘ INSERT INTO staff prop list

    ‘Д VALUES (‘B3’, ‘SG5’, ‘PG19’);

    Однако и в этом случае вставка новой записи в таблицу окажется невозможной,

    поскольку в определении таблицы Property for Rent указано, что все ее столбцы (за исключением Area, Pcode и Sno) не могут содержать значения NULL (см. пример 13.43). Однако представление Staff Prop List не содержит никаких полей таблицы Prop- erty for Rent, за исключением номера объекта, поэтому у нас нет возможности за- дать значения всех остальных обязательных столбцов этой таблицы.

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

    в его определении не используется ключевое слово DISTINCT, т.е. из резуль- татов определяющего запроса не исключаются дублирующиеся строки;

    каждый элемент в списке предложения SELECT определяющего запроса представляет собой имя столбца (а не константу, выражение или обоб- щающую функцию), причем имя каждого из столбцов упоминается ‘в этом списке не более одного раза;

    в предложении FROM указана только одна таблица — т.е. представление должно быть создано на базе единственной таблицы, по отношению к которой пользователь должен обладать необходимыми правами доступа. Если исходная таблица сама является представлением, то это представление также должно отвечать указанным условиям. Данное требование исключает возможность обновления любых представлений, построенных на базе соединения, объединения (UNION), пересечения (INTERSECT) или разности (EXCEPT) таблиц;

    предложение WHERE не должно включать никаких вложенных запросов типа SELECT, которые ссылаются на таблицу, указанную в предложении FROM;

    определяющий запрос не должен содержать предложений GROUP BY и HAVING. Кроме того, любая строка данных, добавляемая через представление, не должна нарушать требований поддержки целостности данных, установленных для исходной таблицы представления. Например, при добавлении через представление новой стро- ки таблицы во все столбцы таблицы, отсутствующие в этом представлении, будут введены значения NULL. Однако при этом должны соблюдаться все требования NOT NULL, указанные в описании исходной таблицы представления. Основ ную концепцию, используемую при формулировании обсуждаемых ограничений, можно выразить с помощью следующего определения.

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

    10.1.6. Использование фразы WITH CHECK OPTION

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

    А теперь попробуем добавить в представление новую запись: «‘ INSERT INTO staff prop list

    ‘Д VALUES (‘B3’, ‘SG5’, ‘PG19’);

    Однако и в этом случае вставка новой записи в таблицу окажется невозможной, поскольку в определении таблицы Property for Rent указано, что все ее столбцы (за исключением Area, Pcode и Sno) не могут содержать значения NULL (см. пример 13.43). Однако представление Staff Prop List не содержит никаких полей таблицы Property for Rent, за исключением номера объекта, поэтому у нас нет возможности за- дать значения всех остальных обязательных столбцов этой таблицы.

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

    в его определении не используется ключевое слово DISTINCT, т.е. из резуль- татов определяющего запроса не исключаются дублирующиеся строки;

    каждый элемент в списке предложения SELECT определяющего запроса представляет собой имя столбца (а не константу, выражение или обоб- щающую функцию), причем имя каждого из столбцов упоминается ‘в этом списке не более одного раза;

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

    • (UNION), пересечения (INTERSECT) или разности (EXCEPT) таблиц;

    предложение WHERE не должно включать никаких вложенных запросов типа SELECT, которые ссылаются на таблицу, указанную в предложении FROM;

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

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

    Обновляемое представление Для того чтобы представление было обновляемым, СУБД долж-

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

    10.1.б. Использование фразы WITH CHECK OPTION

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

    ‘.~’. FROM staff FROM Хою salary . FROM high salary ~p; WHERE salary > 9000; WHERE salary > 10000 . WHERE bno = ‘B3’; WITH НОСА? СНЕСК OPTION;

    Теперь попытаемся выполнить в представлении Мазадег3 Staff следующий оператор: 0РОАТЕ вава9ег3 staff SET salary = 9500

    WHERE sno = ‘SG37’;

    Однако это нам не удастся. Суть в том, что после выполнения данного оператора, измененная строка исчезнет только из представления High Salary, но сохранится в ,. представлении low Salary, на базе которого представление High Salary было определено. Если же при обновлении установить новое значение заработной платы ‘ равным 8000 фунтов стерлингов, то эта операция будет успешной, поскольку измененная строка должна будет исчезнуть и из представления Low Salary. Если же при определении представления High Salary указать фразу WITH CASCADED CHECK OPTION, то при указании любого из упомянутых значений заработной платы — как 9500, так и 8000 фунтов стерлингов в год — выполнение данного обновления будет заблокировано, поскольку измененная строка должна будет исчезнуть из предcтавления High Salary. Следовательно, для того чтобы получить гарантию, что подобные аномалии обновления никогда не будут иметь места, каждое из создаваемых представлений должно содержать фразу WITH CASCADED СНЕСК OPTION.

    10.1.7. Преимущества и недостатки представлений

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

    Преимущества

    В случае выполнения СУБД на отдельно стоящем персональном компьютере ис- пользование представлений обычно имеет целью лишь упрощение структуры запро- сов к базе данных. Однако в случае многопользовательской сетевой СУБД представ- ления играют ключевую роль в определении структуры базы данных и организации ‘ защиты информации. Основные преимущества использования представлений в по- добной среде заключаются в следующем.

    Независимость от данных

    С помощью представлений можно создать согласованную, неизменную картину структуры базы данных, которая будет оставаться стабильной даже в случае изменения формата исходных таблиц (например, добавления или удаления столбцов, изменения связей, разделения таблиц, их реструктуризации или переименований). Если в .таблицу добавляются или из нее удаляются столбцы, не используемые в представлении, то изменять определение этого представления не потребуется. Если структура исходной таблицы переупорядочивается или эта таблица разделяется, то можно будет создать представление, позволяющее пользователям работать с виртуальной таблицей прежнего формата. В случае разделения исходной таблицы, прежний формат может быть виртуально восстановлен с помощью представления, построенного на основе соединения вновь созданных таблиц — конечно, если это будет возможно. Последнее условие можно обеспечить с помощью помещения во все вновь созданные таблицы первичного ключа прежней таблицы. Допустим, в исходном виде таблица Renter имела следующий вид:

    Renter (Rno, FName, LName, Address, Tel Но, Pref Туре, Max Rent, Bno)

    Допустим также, что возникла необходимость разбить эту исходную таблицу на две новые таблицы Renter Details и Renter Reqts:

    Renter Details (Rno, FName, LName, Address, Tel Но, Bno) Renter Reqts (Rno, Pref Туре, Max Rent)

    Пользователи и приложения смогут по-прежнему иметь доступ к данным с использованием формата исходной таблицы, если определить представление Renter, построенное на базе соединения таблиц Renter Details и Renter Reqts, выполненного по ключу Rno:

    CREATE VIEW renter

    AS SELECT rd.rno, fname, 1name, address, tel по, pref type, max rent, bno FROM renter details rd, renter reqts rq WHERE rd.rno = rq.rno;

    Актуальность

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

    ПоВышение защищенности данных

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

    Снижение сложности

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

    Дополнительные удобстВа

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

    Возможность настройки

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

    Обеспечение целостности данных

    Если в операторе CREATE VIEW будет указана фраза WITH CHECK OPTION, то СУБД бу- дет осуществлять контроль за тем, чтобы в исходные таблицы базы данных не была введена ни одна из строк, не удовлетворяющих предложению WHERE в определяющем запросе. Этот механизм гарантирует целостность данных в представлении.

    Недостатки

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

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

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

    Структурные ограничения

    Структура представления устанавливается в момент его создания. Если определяющий запрос представлен в форме SELECT * FROM…, то символ * ссылается на все столбцы, существующие в исходной таблице на момент создания представления. Если впоследствии в исходную таблицу базы данных будут добавлены новые столбцы, то они не появятся в данном представлении до тех пор, пока это представление не будет удалено и вновь создано.

    Снижение произВодительности

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

    Все достоинства и недостатки, связанные с использованием представлений,. приведены в табл. 10.6.

    Таблица 10.6. Достоинства и недостатки использования представлений в языке SQL Достоинства

    Независимость от данных Актуальность

    Повышение защищенности данных Снижение сложности

    Дополнительные удобства Возможность настройки

    Обеспечение целостности данных

    Недостатки

    Ограниченные возможности обновления

    Структурные ограничения

    Снижение производительности

    10.2. Средства поддержки целостности данных

    В этом разделе мы познакомимся с функциями, предназначенными для поддержки целостности данных, которые предусмотрены стандартом ISO на язык SQL (версия 1992 г.). Поддержка целостности данных включает средства задания ограничений, которые .вводятся с целью защиты базы от нарушения согласованности сохраняемых в ней данных. В разделе 8.1 мы познакомились с такими типами ограничений поддержки целостности данных, как:

    обязательные данные;

    ограничения для доменов атрибутов;

    целостность сущностей;

    ссылочная целостность;

    требования данного предприятия.

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

<

Комментирование закрыто.

WordPress: 23.47MB | MySQL:120 | 5,718sec