Базы данных    
Конспект лекций
назад | содержание | вперед

 

Раздел 5. Управление реляционной базой данных с помощью SQL

Тема 5.1. Язык SQL: обзор

 

Последствием публикации статей Кодда, вводящих реляционную модель и реляционные языки (алгебру и исчисление), стал взрыв активности различных организаций по реализации реляционных языков. Тремя наиболее важными языками, появившимися в результате этих усилий, стали SQL (Structured Query Language, язык структурированных запросов, произносится “эс-кью-эль” или “сиквел”), QBE (Query-by-Example, запрос-по-образцу) и QUEL (Query Language, язык запросов). SQL и QBE были созданы IBM в семидесятые годы и выполняли во многом сходные функции, хотя SQL — текстовый язык, a QBE — графический. QUEL — оригинальный язык INGRES, реляционной системы управления базами данных, созданной в семидесятые годы Калифорнийским университетом Беркли.

SQL стал результатом исследовательского проекта System R компании IBM. В конце семидесятых SQL стал доступен для широкого пользования, будучи впервые выпущенным в качестве языка коммерческой системы корпорацией Oracle. В 1983 году фирма IBM выпустила SQL в качестве части СУБД DB2.

В 1986 году был принят первый стандарт ANSI для SQL. Этот стандарт был пересмотрен (несильно) в 1989 и (существенно) в 1992 году. SQL является, и, весьма вероятно, останется, единственным стандартом ANSI реляционного языка баз данных. Более того, SQL является де-факто стандартом в бизнесе, поскольку до сих пор именно он используется в коммерческих системах. С 1980 года различные поставщики стали выпускать приложения SQL.

Кроме перечисленных выше версий SQL для универсальных ЭВМ, существует множество версий типа клиент/сервер, а также версий SQL для персональных компьютеров. Среди них Sybase SQL Server, Microsoft SQL Server, IBM OS/2 Extanded Edition Database Manager, DEC Rdb/VMS и Oracle Server for OS/2 — для систем клиент/сервер, а также версии R:Base и dBASE для персонального компьютера.

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

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

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

РАБОЧИЙ

КОД_

РАБОЧЕГО

ИМЯ_

РАБОЧЕГО

СТАВКА

СПЕЦИАЛЬ-НОСТЬ

КОД_

МЕНЕДЖЕРА

1235

Иванов

12.50

Электрик

1311

1412

Петров

13.75

Штукатур

1520

2920

Волков

10.00

Кровельщик

2920

3231

Зайцев

17.40

Плотник

3231

1520

Маслов

11.75

Штукатур

1520

1311

Гаранин

15.50

Электрик

1311

3001

Никитин

8.20

Плотник

3231

 

РАБОТА

КОД_

РАБОЧЕГО

КОД_

ЗДАНИЯ

ДАТА_

НАЧАЛА

ЧИСЛО_

ДНЕЙ

1235

312

10.10

5

1412

312

01.10

10

1235

515

17.10

22

2920

460

05.10

18

1412

460

08.12

18

2920

435

28.10

10

2920

210

10.11

15

3231

111

10.10

8

1412

435

15.10

15

1412

515

05.11

8

3231

312

25.10

20

1520

515

09.10

14

1311

435

08.10

12

1412

210

15.11

12

1412

111

01.12

4

3001

111

08.10

14

1311

460

23.10

24

1520

312

30.10

17

3001

210

27.10

14

 

ЗДАНИЕ

КОД_

ЗДАНИЯ

АДРЕС

ТИП

УРОВЕНЬ

СТАТУС

312

Ул. Еловая 123

Офис

2

2

435

Ул. Кленовая 450

Магазин

1

1

515

Ул. Дубовая 45

Жилой дом

3

1

210

Ул. Березовая 120

Офис

3

1

111

Ул. Осиновая 80

Офис

4

1

460

Ул. Буковая 23

Склад

3

3

 

Рис. 5.1 База данных строительной компании ПРЕМЬЕР

 

Тема 5.2. Определение схемы и таблиц

Каталог. В SQL-92 набор схем, имеющий имя.

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

 

5.2.1. Определение схемы

В SQL-92 каталогом называется имеющий имя набор схем, однако то, как должен определяться каталог, не оговаривается. Это оставлено СУБД. Однако определение схемы заложено в SQL-92. Схема базы данных в SQL определяется напрямую. Необходимо только указать начало определения схемы командой CREATE SCHEMA с необязательным дополнением AUTHORIZATION, а затем определить каждую область, таблицу, представление данных и т.д. схемы. Приведем пример определения схемы:

CREATE SCHEMA PREMIER_CONSRUCTION
AUTHORIZATION TONY_MELTON
определение областей
определение таблиц
определение представлений данных
и т.д.

Каждая команда CREATE SCHEMA сообщает СУБД, что далее следует схема базы данных. Эта команда также определяет имя схемы - PREMIER_CONSRUCTION в нашем примере. Команда AUTHORIZATION определяет имя владельца схемы. Этот человек известен системе; он может предоставлять другим пользователям право доступа и привилегии обновления, определенные в схеме. Очевидно, эта структура подразумевает, что в одной и той же установленной системе может существовать много схем данных. Их владельцами могут быть различные люди, но под контролем СУБД к каждой схеме могут обращаться и другие пользователи.

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

 

5.2.2. Типы данных и области

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

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

Типы данных. В SQL-92 определены следующие типы данных:

Точные числовые:

  • Integer — целое число
  • Small integer — короткое целое
  • Numeric (p,s) — число
  • Decimal (p,s) — десятичное число

В двух последних типах данных вы указываете общее количество знаков числа (р) и число знаков после запятой (s).

Приблизительные числовые:

  • Real — действительное число
  • Double precision — число с двойной точностью
  • Float — с плавающий запятой

Эти типы данных обычно используются для научных и инженерных расчетов.

Символьные строки:

  • Character (n) — символьная строка
  • Character varying (n)— символьная строка переменной длины

Поля типа character всегда хранят n символов, даже если введенное значение нужно дополнять справа пробелами. Поля типа character varying хранят столько символов, сколько введено.

Двоичные строки:

  • Bit (n) — двоичная строка
  • Bit varying (n)— двоичная строка переменной длины

Эти поля используются для флагов и других двоичных масок.

Дата-время:

  • Date — дата
  • Time— время
  • Timestamp – дата и время
  • Time with time zone – время и часовой пояс
  • Timestamp with time zone – дата, время и часовой пояс

Дата: по порядку год, месяц, день (год содержит 4 знака). Время: часы (0-23), минуты, секунды и десятые доли секунды.

Промежуток:

  • Year-month — год и месяц
  • Day-time— день и время

Промежуток – разность между двумя датами (год-месяц) или между двумя моментами времени (день и время). Например, между декабрем 1994 и январем 1996 интервал один год и один месяц.

Определение (домена) области. Типы данных с определенными ограничительными условиями и значениями по умолчанию могут комбинироваться в определении областей. Определение области – это конкретный тип данных, который может быть определен в схеме и по желанию использоваться при определении столбцов. Предположим, например, что мы хотим определить область идентификаторов элементов списка, которая будет использоваться при определении таких столбцов, как КОД_РАБОЧЕГО или КОД_ЗДАНИЯ. Мы предполагаем, что это определение будет сложным, содержащим тип данных, значение по умолчанию и условие на пустоты; поскольку мы будем снова и снова пользоваться им в схеме базы данных, мы хотели бы упростить работу. Тогда мы создаем следующий домен:

CREATE DOMAIN ITEMS NUMERIC (4) DEFAULT 0 CHECK (VALUE IS NOT NULL)

Это определение говорит, что область с именем ITEMS имеет следующие свойства: ее тип данных – четырехзначное число, значение по умолчанию равно нулю, пустое значение запрещено. Любой столбец, в определении которого будет использован этот тип данных, будет обладать этими свойствами. Обратите внимание, что мы не можем просто поставить в определение NOT NULL. Вместо этого SQL заставляет нас использовать в этих целях ограничение CHECK. Сделав это, мы можем в нашей схеме определять столбцы с типом данных ITEMS.

Ограничительное условие. Правило, ограничивающее значения величин в базе данных.

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

Определение области. Тип данных, определенный в схеме и используемый при определении столбцов.

 

5.2.3. Определение таблиц

Таблицы определяются в три этапа:

  1. Таблице присваивается имя.
  2. Определяется каждый столбец с возможными ограничительными условиями на него.
  3. Определяются ограничительные условия на таблицу.

Приведем определение схемы для базы данных рис.5.1.

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

Определение столбцов. Посмотрим на определения первых трех столбцов таблицы РАБОЧИЙ:

КОД_РАБОЧЕГО ITEMS PRIMARY KEY ,
ИМЯ_РАБОЧЕГО CHAR (12),
СТАВКА NUMERIC (5,2),

двух столбцов таблицы РАБОТА:

 

ДАТА_НАЧАЛА DATE,
ЧИСЛО_ДНЕЙ INTERVAL DAY (3),

 

и двух столбцов таблицы ЗДАНИЕ:

ТИП CHAR (9) DEFAULT ‘Офис’,

CHECK (TYPE IN (‘Офис’,’Склад’,’Магазин’,’Жилой дом’))

СТАТУС NUMERIC (1), DEFAULT 1,

CHECK (СТАТУС > 0 AND СТАТУС < 4))

В определении каждого столбца дается его имя, тип данных, который может быть одним из предопределенных типов данных или определенной пользователем областью, значение по умолчанию, а также относящиеся к нему ограничения (например, NOT NULL (не пусто), PRIMARY KEY (первичный ключ) и ограничительное условие CHECK (проверить)). Первые три столбца в нашем примере получили имена, соответственно, КОД_РАБОЧЕГО, ИМЯ_РАБОЧЕГО и СТАВКА. Их типы данных ITEMS, character и numeric. Два столбца из таблицы РАБОТА ДАТА_НАЧАЛА и ЧИСЛО_ДНЕЙ иллюстрируют два других типа данных: data и interval.

Тип данных numeric означает, что значениями данных столбца должны быть числа, возможно, с десятичной точкой. Тип данных столбца КОД_РАБОЧЕГО — ITEMS, определенная пользователем область, тип данных которой был выбран NUMERIC (4), что означает четыре значащих цифры и ни одной цифры после запятой. СТАВКА имеет тип данных NUMERIC (5,2), то есть пять значащих цифр, две из них после запятой.

Тип данных character означает, что значениями данных столбца будут символьные строки, состоящие из букв алфавита, цифр, возможно, в сочетании со специальными символами. Максимальная длина строки указывается в скобках. Так, ИМЯ_РАБОЧЕГО может содержать символьные строки длины не более 12 знаков.

Тип данных date (тип данных столбца ДАТА_НАЧАЛА) означает, что значениями данных столбца будут даты, состоящие из года (четыре цифры), месяца (две цифры) и дня (две цифры).

Тип данных interval (например, столбец ЧИСЛО_ДНЕЙ) дается в годах (year) и/или месяцах (month) или в днях (day), минутах (minute), часах (hour) и/или секундах (second). Мы выбрали тип данных интервала DAY(3), что означает, что значение столбца ЧИСЛО_ДНЕЙ может лежать в промежутке от 0 до 999 дней. Типы данных date и interval имеют то преимущество, что их можно складывать и вычитать. Так, мы можем прибавить ЧИСЛО_ДНЕЙ к ДАТА_НАЧАЛА и получить дату окончания работы данного человека на данном здании (при этом подразумевается, что с начала и до конца работы у рабочего нет выходных или праздников и он не работает ни на каком другом здании).

На столбец КОД_РАБОЧЕГО также наложены два ограничения: NOT NULL (не пусто) и PRIMARY KEY (первичный ключ). Ограничение NOT NULL унаследовано из определения области и означает, что КОД_РАБОЧЕГО не может содержать пустых значений. PRIMARY KEY означает, что никакие две строки таблицы РАБОЧИЙ не могут иметь одинаковые значения КОД_РАБОЧЕГО и что для ссылок посредством внешних ключей столбец КОД_РАБОЧЕГО выбран в качестве первичного ключа. Ограничение NOT NULL обеспечивает выполнение правила целостности на уровне ссылок, согласно которому ключевые столбцы не должны содержать пустых значений.

Столбцы ТИП и СТАТУС таблицы ЗДАНИЕ имеют определенные значения по умолчанию (DEFAULT). Если к таблице ЗДАНИЕ добавляется кортеж и, например, значение столбца СТАТУС не введено, то система автоматически установит значение столбца СТАТУС для этого кортежа, равное 1. Аналогично, в столбце ТИП автоматически устанавливается 'Офис', если не введено никакое значение. Каждый столбец имеет значение по умолчанию, которое пусто, если не определено в схеме. Обратите внимание, что мы не определили значение по умолчанию для КОД_РАБОЧЕГО, поскольку значение по умолчанию (0) наследуется из определения области.

Столбцы ТИП и СТАТУС также имеют ограничение CHECK (проверить). Это ограничительное условие на значения данных, которые можно вводить в столбец. В частности, столбец ТИП может содержать только значения из множества ('Офис', 'Склад', 'Магазин', 'Жилой дом'), а значения столбца СТАТУС может быть от 1 до 3.

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

Посмотрим на каждое из них. Условие, задающее внешний ключ таблицы РАБОЧИЙ, выглядит так:

FOREIGN KEY КОД_МЕНЕДЖЕРА REFERENCES РАБОЧИЙ

ON DELETE SET NULL)

Оно означает, что КОД_МЕНЕДЖЕРА— рекурсивный внешний ключ, то внешний ключ, ссылающийся на свою собственную таблицу. Команда просто определяет столбец (столбцы) внешнего ключа и указывает, на какую таблицу эти столбцы ссылаются. Команда ON DELETE SET NULL сообщает системе, что если кортеж, на который указывает внешний ключ, удаляется, то значение внешнего ключа должно быть пустым. Например, предположим, что мы удалили строку таблицы РАБОЧИЙ с КОД_РАБОЧЕГО 1311. Тогда КОД_МЕНЕДЖЕРА первой строки (КОД_РАБОЧЕГО 1235) будет указывать на несуществующего работника, то есть на строку, которой больше нет в базе данных. Это будет нарушением целостности на уровне ссылок.

Чтобы предотвратить его, команда ON DELETE SET NULL приказывает СУБД установить пустое значение внешнего ключа (КОД_МЕНЕДЖЕРА строки 1235), если строка, на которую он указывает (строка 1311), удалена.

Рекурсивный внешний ключ. Внешний ключ, ссылающийся на свою собственную таблицу.

Ограничения на таблицу РАБОТА таковы:

Кроме команды ON DELETE CASCADE, которую мы кратко обсудим, все остальное ясно. Условие PRIMARY KEY (КОД_РАБОЧЕГО, КОД_ЗДАНИЯ) сообщает системе, что (КОД_РАБОЧЕГО, КОД_ЗДАНИЯ) — составной первичный ключ таблицы. Следовательно, комбинация значений этих столбцов в каждой строке должна быть своя.

Команда ON DELETE CASCADE аналогична ON DELETE SET NULL в том смысле, что действие происходит в случае удаления строки, на которую ссылается внешний ключ. Рассмотрим, например, строки базы данных рис.5.1.

КОД_РАБОЧЕГО таблицы РАБОТА —- внешний ключ, ссылающийся на таблицу РАБОЧИЙ. ON DELETE CASCADE означает, что если строка таблицы РАБОЧИЙ, на которую ссылается внешний ключ (1235 в нашем примере), будет удалена, то удаление должно быть распространено на все ссылающиеся на нее строки таблицы РАБОТА. В нашем примере на строку с КОД_РАБОЧЕГО 1235 ссылается две строки таблицы РАБОТА. Если строка 1235 таблицы РАБОЧИЙ будет удалена, то система автоматически удалит две соответствующие строки таблицы РАБОТА. Это правильное обращение с таким внешним ключом, так как если строка таблицы РАБОЧИЙ удалена, то соответствующие строки таблицы РАБОТА становятся бессмысленными и должны быть удалены.

Команда ON DELETE (при удалении) аналогична команде ON UPDATE при обновлении), и обе они имеют следующие опции:

  • CASCADE (распространить)
  • SET NULL (установить пустое значение)
  • SET DEFAULT (установить значение по умолчанию)

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

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

Мультимножество. Множество, в котором элементы могут повторяться.

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

Определение схемы. Описание базы данных для СУБД.

Другие команды манипуляции схемой. Кроме команды CREATE TABLE, определяющей новую таблицу, SQL-92 предоставляет другие команды для изменения определений таблиц (ALTER TABLE) или для удаления таблиц из схемы (DROP TABLE). ALTER TABLE (изменить таблицу) может использоваться для добавления столбца к таблице, изменения определения существующего столбца или удаления столбца из таблицы. DROP TABLE (удалить таблицу) удаляет все строки, имеющиеся в данный момент в указанной таблице, а также удаляет все определение таблицы из схемы. Всю схему целиком можно удалить при помощи команды DROP SCHEMA (удалить схему). Однако, поскольку это довольно опасная операция, она требует указания опций CASCADE (распространить) или RESTRICT (ограничить).

DROP SCHEMA имя-схемы CASCADE

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

DROP SCHEMA имя-схемы RESTRICT

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

 

Тема 5.3. Манипуляция данными

SQL содержит широкий спектр возможностей манипуляции данными как для создания запросов, так и для обновления базы данных. Эти возможности опираются только на логическую структуру базы данных, а не на ее физическую структуру, что согласуется с требованиями реляционной модели. Первоначально структура синтаксиса SQL была основана на реляционном исчислении Кодда. Единственной поддерживаемой операцией реляционной алгебры было объединение. В SQL-92 в дополнение к аналогичному реляционному исчислению синтаксису, реализованному ранее, напрямую реализованы объединение, пересечение, разность и соединение. Операции выбора, проектирования и произведения поддерживались (и продолжают поддерживаться) практически напрямую, в то время как операции деления и присвоения поддерживаются в более громоздкой форме.Сначала мы опишем язык запросов SQL, а затем его операции ввода и изменения данных. Операции изменения данных будут описаны в последнюю очередь, поскольку их структура в определенной степени опирается на структуру языка запросов.

 

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

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

Простой запрос. Запрос, который обращается только к одной таблице базы данных

Запрос: Кто работает штукатурами? SELECT ИМЯ_РАБОЧЕГО FROM РАБОЧИЙ WHERE СПЕЦИАЛЬНОСТЬ = 'Штукатур'

Результат:

ИМЯ_РАБОЧЕГО

Петров

Маслов

 

Этот запрос иллюстрирует три наиболее часто встречающиеся фразы SQL: SELECT, FROM и WHERE. Хотя в нашем примере мы поместили их на разные строки, они все могут стоять в одной строке. Они также могут помещаться с разными отступами, а слова внутри фраз могут разделяться произвольным числом пробелов. Рассмотрим характеристики каждой фразы:

Фраза SELECT перечисляет столбцы, которые должны войти в результирующую таблицу. Это всегда столбцы некоторой реляционной таблицы. В нашем примере результирующая таблица состоит из одного столбца (ИМЯ_РАБОЧЕГО), но в общем случае она может содержать несколько столбцов; она также может содержать вычисленные значения или константы. Мы приведем примеры каждого из этих вариантов. Если результирующая таблица должна содержать более одного столбца, то все нужные столбцы перечисляются после команды SELECT через запятую. Например, фраза SELECT КОД_РАБОЧЕГО, ИМЯ_РАБОЧЕГО выдаст в результате таблицу, состоящую из вышеуказанных столбцов.

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

Фраза WHERE содержит условие, на основании которого выбираются строки таблицы (таблиц). В нашем примере условие состоит в том, что столбец СПЕЦИАЛЬНОСТЬ должен содержать константу 'Штукатур', заключенную в апострофы, как это всегда делается с текстовыми константами в SQL. Фраза WHERE — наиболее изменчивая команда SQL; она может содержать множество разнообразных условий. Большая часть нашего изложения будет посвящена иллюстрации различных конструкций, разрешенных в команде WHERE.

Приведенный выше запрос SQL обрабатывается системой в следующем порядке: FROM, WHERE, SELECT. To есть строки таблицы, указанной в команде FROM, помещаются в рабочую область для обработки. Затем к каждой строке последовательно применяется фраза WHERE. Все строки, не удовлетворяющие условию WHERE, исключаются из рассмотрения. Затем те строки, которые удовлетворяют условию WHERE, обрабатываются командой SELECT. В нашем примере из каждой такой строки выбирается ИМЯ_РАБОЧЕГО, и все выбранные значения выводятся в качестве результатов запроса.

 

Запрос: Привести все данные о зданиях офисовSELECT * FROM ЗДАНИЕ WHERE ТИП = 'Офис'

Результат:

КОД_

ЗДАНИЯ

АДРЕС

ТИП

УРОВЕНЬ

СТАТУС

312

Ул. Еловая 123

Офис

2

2

210

Ул. Березовая 120

Офис

3

1

111

Ул. Осиновая 80

Офис

4

1

 

Звездочка (*) в команде SELECT означает “строка целиком”. Это удобное сокращение, которым мы будем часто пользоваться.

Запрос: Какова недельная зарплата каждого электрика? SELECT, 'Недельная зарплата = ', 40*СТАВКА FROM РАБОЧИЙ WHERE СПЕЦИАЛЬНОСТЬ = 'Электрик' ORDER BY ИМЯ_РАБОЧЕГО

Результат:

 

ИМЯ_РАБОЧЕГО Гаранин Недельная зарплата = 620.00

Иванов Недельная зарплата = 500.00

 

Этот запрос иллюстрирует употребление и символьных констант (в нашем примере 'Недельная зарплата = '), и вычислений в команде SELECT. Внутри команды SELECT можно производить вычисления, в которых используются числовые столбцы и числовые константы, а также стандартные арифметические операторы (+, -, *, /), сгруппированные по мере необходимости с помощью скобок. Мы также включили новую команду ORDER BY, которая сортирует результат запроса в возрастающем алфавитно-числовом порядке по указанному столбцу. Если вы хотите упорядочивать результаты по убыванию, то к команде нужно добавить DESC. Фраза ORDER BY может сортировать результаты по нескольким столбцам, по одним — в порядке возрастания, по другим — в порядке убывания. Первым указывается столбец первичного ключа сортировки.

Запрос: У кого почасовая ставка от 10 до 12 долларов?

SELECT * FROM РАБОЧИЙ
    WHERE СТАВКА > = 10 AND СТАВКА < = 12

Этот запрос иллюстрирует некоторые дополнительные возможности команды WHERE: операторы сравнения и булеву операцию AND (И). Для сравнения столбцов с другими столбцами или с константами могут использоваться шесть операторов сравнения (=, <> (не равно), <, >, <=, >=). Для создания составных условий или для отрицания условия могут использоваться булевы операции AND (И), OR (ИЛИ) и NOT (HE). Для группировки условий, как обычно в языках программирования, могут использоваться скобки.

Для формулировки этого запроса также можно было использовать оператор BETWEEN (между):

SELECT * FROM РАБОЧИЙ
    WHERE СТАВКА BETWEEN 10 AND 12

 

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

Запрос: Перечислить штукатуров, кровельщиков и электриков. SELECT * FROM РАБОЧИЙ WHERE IN ('Штукатур', 'Кровельщик', 'Электрик')

Результат:

КОД_

РАБОЧЕГО

ИМЯ_

РАБОЧЕГО

СТАВКА

СПЕЦИАЛЬ-НОСТЬ

КОД_

МЕНЕДЖЕРА

1235

Иванов

12.50

Электрик

1311

1412

Петров

13.75

Штукатур

1520

2920

Волков

10.00

Кровельщик

2920

1520

Маслов

11.75

Штукатур

1520

1311

Гаранин

15.50

Электрик

1311

Этот запрос поясняет использование оператора сравнения IN (В). Условие WHERE считается истинным, если тип специальности строки расположен внутри множества, указанного в скобках, то есть если тип специальности — штукатур, кровельщик или электрик. Мы еще встретимся с оператором IN в подзапросах.

Предположим, что мы не можем точно вспомнить написание специальности: “электрик” или “электронщик” или еще как-то. Символы шаблона, которые замещают неопределенные строки символов, облегчают поиск неточного написания в запросе.

Символы шаблона. Символы, замещающие неопределенные строки символов.

 

Запрос: Перечислить работников, чей тип специальности начинается “Элек”.

SELECT * FROM РАБОЧИЙ
    WHERE СПЕЦИАЛЬНОСТЬ LIKE (’Элек%')

 

В SQL есть два символа шаблона: % (процент) и _ (подчеркивание). Подчеркивание замещает ровно один неопределенный символ. Процент замещает произвольное число символов, начиная с нуля. Когда используются символы шаблона, для сравнения символьных переменных с константами требуется оператор LIKE (как). Другие примеры:ИМЯ_РАБОЧЕГО LIKE ‘_ _ _ _ ов’ИМЯ_РАБОЧЕГО LIKE '_ а%'

Условие в первом примере истинно, если ИМЯ_РАБОЧЕГО состоит из четырех символов, за которыми следует 'ов'. Условие второго примера позволяет найти всех работников, чьи в фамилиях которых вторая буква “а”.

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

Этот запрос иллюстрирует употребление оператора BETWEEN (между) со значениями типа date (дата) и interval (промежуток). CURRENT_DATE — это функция, всегда возвращающая значение сегодняшней даты. ВыражениеCURRENT DATE + INTERVAL '14' DAYприбавляет двухнедельный промежуток к текущей дате. Таким образом, РАБОТА выбирается (в предположении, что сегодня 10.10) в том случае, если в ней значение столбца ДАТА_НАЧАЛА лежит между 10.10 и 25.10. Из этого видно, что мы можем прибавлять к полям дат величины типа interval. Более того, мы можем умножать значения промежутков на целые величины. Например, предположим, что мы хотим выяснить, какое число будет через определенное количество недель (обозначенное переменной NUM_WEEKS). Мы можем это сделать так:CURRENT_DATE + INTERVAL '7' DAY * NUM_WEEKS

 

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

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

Запрос: Каковы специальности рабочих, назначенных на здание 435?

Данные, необходимые для ответа, находятся в двух таблицах: РАБОЧИЙ и РАБОТА. Для решения в SQL требуется перечислить обе таблицы в команде FROM и задать специальный тип условия WHERE:

SELECT СПЕЦИАЛЬНОСТЬ FROM РАБОЧИЙ, РАБОТА
   WHERE РАБОЧИЙ.КОД_РАБОЧЕГО = РАБОТА.КОД_РАБОЧЕГО
      AND КОД_ЗДАНИЯ = 435

Что здесь происходит? Мы должны рассмотреть два этапа обработки системой данного запроса.

1. Как обычно, сначала обрабатывается фраза FROM. Однако в этом случае, поскольку в команде указаны две таблицы, система создает декартово произведение строк этих таблиц. Это означает, что создается (логически) одна большая таблица, состоящая из столбцов обеих таблиц, в которой каждая строка одной таблицы спарена с каждой строкой другой таблицы. В нашем примере, поскольку в таблице РАБОЧИЙ пять столбцов, а в таблице РАБОТА четыре столбца, в декартовом произведении, созданном командой FROM, будет девять столбцов. Общее число строк декартова произведения равно m*n, где m — число строк таблицы РАБОЧИЙ, a n — число строк таблицы РАБОТА. Поскольку в таблице РАБОЧИЙ 7 строк, а в таблице РАБОТА 19 строк, то декартово произведение будет содержать 7x19 или 133 строки. Если в команде FROM перечислено более двух таблиц, то создается декартово произведение всех таблиц, указанных в команде.

2. После создания гигантской реляционной таблицы система, как и прежде, применяет команду WHERE. Каждая строка таблицы, созданной командой FROM, проверяется на выполнение условия WHERE. Строки, не удовлетворяющие условию, исключаются из рассмотрения. Затем к оставшимся строками применяется фраза SELECT. Фраза WHERE в нашем запросе содержит два условия:

  1. РАБОЧИЙ.КОД_РАБОЧЕГО = РАБОТА.КОД_РАБОЧЕГО
  2. КОД_ЗДАНИЯ = 435

Первое из этих условий — условие соединения. Обратите внимание, что поскольку обе таблицы РАБОЧИЙ и РАБОТА содержат столбец с именем КОД_РАБОЧЕГО, их декартово произведение будет содержать два столбца с таким именем. Для того чтобы различать их, мы помещаем перед именем столбца имя исходной таблицы, отделяя его точкой.

Первое условие означает, что в любой выбранной строке значение столбца КОД_РАБОЧЕГО из таблицы РАБОЧИЙ должно совпадать со значением столбца КОД_РАБОЧЕГО из таблицы РАБОТА. В действительности мы соединяем две таблицы по КОД_РАБОЧЕГО. Все строки, в которых значения этих двух столбцов не равны, исключаются из таблицы произведения. В точности то же самое происходит при выполнении операции естественного соединения реляционной алгебры. Однако, некоторое отличие от естественного соединения все же есть: SQL не удаляет автоматически лишний столбец КОД_РАБОЧЕГО. Применение команды SELECT даст в конце концов следующий результат запроса:

СПЕЦИАЛЬНОСТЬ
Штукатур
Кровельщик
Электрик

Теперь мы покажем, как в SQL присоединить таблицу к ней самой.

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

SELECT A.ИМЯ_РАБОЧЕГО, В. ИМЯ_РАБОЧЕГО
FROM РАБОЧИЙ A, РАБОЧИЙ В
WHERE B. КОД_РАБОЧЕГО = A. КОД_МЕНЕДЖЕРА

Фраза FROM в этом примере создает две “копии” таблицы WORKER, давая им псевдонимы А и В. Псевдоним — это альтернативное имя, данное таблице. Затем копии А и В таблицы WORKER соединяются командой WHERE на основании условия равенства КОД_РАБОЧЕГО в В и КОД_МЕНЕДЖЕРА в А. Таким образом, каждая строка из А присоединяется к строке В, содержащей информацию о менеджере строки А (рис.5.2). Выбирая из каждой строки два имени работника, мы получим требуемый список:

Псевдоним. Альтернативное имя, данное таблице.

A.ИМЯ_РАБОЧЕГО

В.ИМЯ_РАБОЧЕГО

Иванов

Гаранин

Петров

Маслов

Волков

Волков

Зайцев

Зайцев

Маслов

Маслов

Гаранин

Гаранин

Никитин

Зайцев

 

A.ИМЯ_РАБОЧЕГО представляет работника, a В.ИМЯ_РАБОЧЕГО представляет менеджера. Обратите внимание, что некоторые работники — сами менеджеры, что следует из выполненного в их строках равенства КОД_РАБОЧЕГО = КОД_МЕНЕДЖЕРА.

В SQL можно за один раз связать более двух таблиц:

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

SELECT ИМЯ_РАБОЧЕГО
FROM РАБОЧИЙ, РАБОТА, ЗДАНИЕ
WHERE РАБОЧИЙ.КОД_РАБОЧЕГО = РАБОТА.КОД_РАБОЧЕГО
AND РАБОТА.КОД_ЗДАНИЯ = ЗДАНИЕ. КОД_ЗДАНИЯ
AND ТИП=’Офис’

Результат:

ИМЯ_РАБОЧЕГО

Иванов

Петров

Волков

Зайцев

Маслов

Никитин

Обратите внимание, что если имя столбца (например, КОД_РАБОЧЕГО или КОД_ЗДАНИЯ) встречается более чем в одной таблице, то для избежания неопределенности мы должны перед именем столбца указать имя исходной таблицы. Но если имя столбца встречается только в одной таблице, как TYPE в нашем примере, то никакой неопределенности нет, поэтому имя таблицы указывать не нужно.Команды SQL этого запроса создают одну таблицу из трех реляционных таблиц базы данных. Первые две таблицы соединяются по КОД_РАБОЧЕГО, после чего к полученной таблице присоединяется третья таблица по КОД_ЗДАНИЯ. УсловиеТИП = 'Офис'

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

 

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

Подзапрос. Запрос внутри запроса

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

Запрос: Каковы специальности рабочих, назначенных на здание 435?

SELECT СПЕЦИАЛЬНОСТЬ FROM РАБОЧИЙ
    WHERE КОД_РАБОЧЕГО IN
    (SELECT КОД_РАБОЧЕГО FROM РАБОТА
    WHERE КОД_ЗДАНИЯ =435)

Подзапрос в этом примере

(SELECT КОД_РАБОЧЕГО FROM РАБОТА WHERE КОД_ЗДАНИЯ =435)

Запрос, в котором содержится подзапрос, называется внешним запросом или главным запросом. Подзапрос приводит к созданию следующего множества ИД (идентификаторов) работников:

КОД_РАБОЧЕГО

2920

1412

1311

Внешний запрос. Главный запрос, в котором содержатся все подзапросы.

Затем это множество ИД занимает место подзапроса во внешнем запросе. С этого момента выполняется внешний запрос, использующий множество, созданное подзапросом. Внешний запрос обрабатывает каждую строку таблицы РАБОЧИЙ в соответствии с условием WHERE. Если КОД_РАБОЧЕГО строки лежит в (IN) множестве, созданном подзапросом, то СПЕЦИАЛЬНОСТЬ строки выбирается и выводится в результирующей таблице:

СПЕЦИАЛЬНОСТЬ

Штукатур

Кровельщик

Электрик

 

Очень важно, что фраза SELECT подзапроса содержит КОД_РАБОЧЕГО и только КОД_РАБОЧЕГО. В противном случае фраза WHERE внешнего запроса, означающая, что КОД_РАБОЧЕГО лежит в множестве ИД работников, не имела бы смысла.

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

Некоррелированный подзапрос. Подзапрос, значение которого не зависит ни от какого внешнего запроса.

Приведем пример подзапроса внутри подзапроса.

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

SELECT ИМЯ_РАБОЧЕГО FROM РАБОЧИЙ WHERE КОД_РАБОЧЕГО IN
    (SELECT КОД_РАБОЧЕГО FROM РАБОТА WHERE КОД_ЗДАНИЯ IN
    (SELECT КОД_ЗДАНИЯ FROM ЗДАНИЕ WHERE ТИП = 'Офис'))

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

Выполнение запроса происходит в порядке изнутри наружу. То есть самый внутренний запрос (или “самый нижний”) выполняется первым, затем выполняется содержащий его подзапрос, а затем внешний запрос.

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

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

Запрос: Перечислить работников, чьи почасовые ставки выше, чем ставки их менеджеров. SELECT ИМЯ_РАБОЧЕГО FROM РАБОЧИЙ A WHERE A.СТАВКА >(SELECT B. СТАВКА FROM РАБОЧИЙ ВWHERE B.КОД_РАБОЧЕГО = A.КОД_МЕНЕДЖЕРА) Результат: ИМЯ_РАБОЧЕГОПетровЛогические этапы выполнения этого запроса таковы:

  1. Система создает две копии таблицы РАБОЧИЙ: копию А и копию В. В соответствии с тем, как мы их определили, А относится к работнику, В — к менеджеру.
  2. Затем система рассматривает каждую строку А. Данная строка выбирается, если она удовлетворяет условию WHERE. Это условие означает, что строка будет выбрана, если величина СТАВКА в ней больше, чем СТАВКА, порожденная подзапросом.
  3. Подзапрос выбирает величину СТАВКА из строки В, КОД_РАБОЧЕГО которой равен КОД_МЕНЕДЖЕРА строки А, в данный момент рассматриваемой главным запросом. Это СТАВКА менеджера.

Обратите внимание, что поскольку A.СТАВКА может сравниваться только с одной величиной, подзапрос должен выдавать только одну величину. Эта величина меняется в зависимости от того, какая строка А рассматривается. Таким образом, подзапрос коррелирует с главным запросом. Мы встретимся с другими примерами коррелированных подзапросов позже, когда будем изучать встроенные функции.

 

5.3.4. EXISTS и NOT EXISTS

Предположим, что мы хотим идентифицировать рабочих, которые не назначены работать на некоторое здание. При поверхностном взгляде кажется, такой запрос легко выполнить при помощи простого отрицания утвердительной версии запроса. Предположим, например, что нас интересует здание с КОД_ЗДАНИЯ 435. Рассмотрим запрос:SELECT КОД_РАБОЧЕГО FROM РАБОТА WHERE КОД_ЗДАНИЯ <> 435

К сожалению, это неверная формулировка решения. Запрос просто выдаст нам ИД работников, работающих на других зданиях. Очевидно, что некоторые из них могут также быть назначены и на здание 435.

В правильно сформулированном решении используется оператор NOT EXISTS (не существует):SELECT FROM РАБОЧИЙ WHERE NOT EXISTS(SELECT * FROM РАБОТА WHERE РАБОТА.КОД_РАБОЧЕГО = РАБОЧИЙ.КОД_РАБОЧЕГО AND КОД_ЗДАНИЯ = 435)Результат:КОД_РАБОЧЕГО 1235323115203001

Операторы EXISTS и NOT EXISTS всегда помещаются перед подзапросом. EXISTS принимает значение “истина”, если множество, порожденное подзапросом, не пусто. Если порожденное подзапросом множество пусто, то EXISTS принимает значение “ложь”. Оператор NOT EXISTS, естественно, работает в точности наоборот. Он истинен, если результат подзапроса пуст, и ложен в противном случае.

Оператор EXISTS. Принимает значение “истина”, если результирующее множество не пусто.

Оператор NOT EXISTS. Принимает значение “истина”, если результирующее множество пусто.В этом примере мы воспользовались оператором NOT EXISTS. Подзапрос выбирает все такие строки таблицы РАБОТА, в которых КОД_РАБОЧЕГО имеет то же значение, что и в строке, рассматриваемой главным запросом, а КОД_ЗДАНИЯ равен 435. Если это множество пусто, тогда строка работника, рассматриваемая главным запросом, выбирается, поскольку это означает, что данный работник не работает на здании 435.

В приведенном нами решении использован коррелированный подзапрос. Если мы воспользуемся вместо NOT EXISTS оператором IN, то можем обойтись некоррелированным подзапросом:

SELECT КОД_РАБОЧЕГО FROM РАБОЧИЙ
    WHERE КОД_РАБОЧЕГО NOT IN (SELECT КОД_РАБОЧЕГО FROM РАБОТА WHERE КОД_ЗДАНИЯ = 435)

Это решение проще, чем решение с оператором NOT EXISTS. Возникает естественный вопрос, зачем нам вообще нужны EXISTS и NOT EXISTS. Ответ состоит в том, что NOT EXISTS является единственным средством решения запросов, содержащих в условии слово “каждый”. Раньше мы видели, что такие запросы решаются в реляционной алгебре с помощью операции деления, а в реляционном исчислении — с помощью квантора всеобщности. Приведем пример запроса, в условии которого есть слово “каждый”:

Запрос: Перечислить работников, назначенных на каждое здание.

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

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

Теперь мы хотим сформулировать решение на SQL. Для того чтобы упростить понимание окончательного решения, мы сначала дадим решение предварительной задачи: задачи идентификации всех зданий, на которые гипотетический работник, “1234” не назначен.

(1) SELECT КОД_ЗДАНИЯ FROM WHERE NOT EXISTS (SELECT * FROM РАБОТА WHERE РАБОТА.КОД_ЗДАНИЯ = ЗДАНИЕ.КОД_ЗДАНИЯ AND РАБОТА. КОД_РАБОЧЕГО = 1234)

Мы пометили этот запрос (1), поскольку мы будем ссылаться на него позже. Если не существует здания, удовлетворяющего этому запросу, то тогда работник 1234 назначен на каждое здание и, следовательно, удовлетворяет условиям исходного запроса. Для того чтобы получить решение исходного запроса, мы должны обобщить запрос (1) с конкретного рабочего 1234 на переменную КОД_РАБОЧЕГО и превратить этот модифицированный запрос в подзапрос большего запроса. Приведем решение:

(2) SELECT КОД_РАБОЧЕГО FROM РАБОЧИЙ WHERE NOT EXISTS
   (SELECT КОД_ЗДАНИЯ FROM ЗДАНИЕ WHERE NOT EXISTS
   (SELECT * FROM РАБОТА
    WHERE РАБОТА.КОД_ЗДАНИЯ = ЗДАНИЕ.КОД_ЗДАНИЯ AND
    РАБОТА.КОД_РАБОЧЕГО = РАБОЧИЙ.КОД_РАБОЧЕГО)

Результат:

КОД_РАБОЧЕГО
1412

Обратите внимание, что подзапрос, начинающийся со второй строки запроса (2), идентичен запросу (1), в котором “1234” заменено на РАБОЧИЙ.КОД_РАБОЧЕГО. Запрос (2) можно прочитать следующим образом:Выбрать КОД_РАБОЧЕГО из РАБОЧИЙ, если не существует здание, на которое КОД_РАБОЧЕГО не назначен.

Это соответствует условиям исходного запроса.

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

 

5.3.5. Встроенные функции

Рассмотрим вопросы такого типа:Каковы максимальная и минимальная почасовые ставки? Каково среднее число дней работы служащих на здании 435? Каково общее число дней, отведенных на штукатурные работы на здании 312? Сколько всего разных специальностей?

Для ответа на эти вопросы требуются статистических функции, которые рассматривают множество строк таблицы и выдают одно значение. В SQL есть пять таких функций, называемых встроенными функциями или функциями множества. Это функции SUM (сумма), AVG - (среднее), COUNT (количество), МАХ (максимум) и MIN (минимум).

Встроенная функция. Статистическая функция, оперирующая множеством строк: SUM (сумма), AVG (среднее), COUNT (количество), МАХ (максимум), MIN (минимум).

Запрос: Каковы максимальная и минимальная почасовые ставки?

SELECT MAX(СТАВКА), MIN(СТАВКА) FROM РАБОЧИЙРезультат: 17.40, 8.20

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

Запрос: Каково среднее число дней работы служащих на здании 435?

SELECT AVG(ЧИСЛО_ДНЕЙ) FROM РАБОТА WHERE КОД_ЗДАНИЯ = 435

Результат: 12.33

Для того чтобы подсчитать искомое среднее, нужно рассматривать только те строки таблицы РАБОТА, которые относятся к зданию 435. Как всегда в SQL, фраза WHERE сводит рассмотрение только к таким строкам.

Запрос: Каково общее число дней, отведенных на штукатурные работы на здании 312?

SELECT SUM(ЧИСЛО_ДНЕЙ) FROM РАБОТА, РАБОЧИЙWHERE РАБОЧИЙ.КОД_РАБОЧЕГО = РАБОТА.КОД_РАБОЧЕГО AND СПЕЦИАЛЬНОСТЬ = 'Штукатур' AND КОД_ЗДАНИЯ = 312

Результат: 27

В решении используется соединение таблиц РАБОТА и РАБОЧИЙ. Это необходимо, так как СПЕЦИАЛЬНОСТЬ находится в таблице РАБОЧИЙ, a КОД_ЗДАНИЯ— в таблице РАБОТА.

Запрос: Сколько всего разных специальностей?

SELECT COUNT (DISTINCT СПЕЦИАЛЬНОСТЬ) FROM РАБОЧИЙ

Результат: 4

Поскольку одна и та же специальность может повторяться в нескольких разных строках, в этом запросе необходимо использовать ключевое слово DISTINCT (различный), чтобы система не сосчитала один и тот же тип специальности более одного раза. Оператор DISTINCT может использоваться с любой из встроенных функций, хотя, разумеется, с функциями МАХ и MIN он избыточен.

DISTINCT. Оператор, исключающий повторяющиеся строки.

Функции SUM и AVG должны использоваться только с числовыми столбцами. Другие функции могут использоваться и с числовыми, и с символьными данными. Все функции, кроме COUNT, можно использовать с вычисляемыми выражениями. Например:

Запрос: Какова средняя недельная зарплата?

SELECT AVG (40 * СТАВКА) FROM РАБОЧИЙ

Результат: 509.14

COUNT может ссылаться на строку целиком, а не на отдельный столбец:

Запрос: Сколько зданий имеют уровень качества 3?

SELECT COUNT (*) FROM ЗДАНИЕ WHERE УРОВЕНЬ = 3

Результат: 3

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

 

5.3.6. GROUP BY и HAVING

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

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

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

SELECT КОД_МЕНЕДЖЕРА, MAX(СТАВКА) FROM РАБОЧИЙ GROUP BY КОД_МЕНЕДЖЕРА

Результат:

При обработке этого запроса система сначала разбивает строки таблицы РАБОЧИЙ на группы по следующему правилу. Строки помещаются в одну группу тогда и только тогда, когда у них совпадает КОД_МЕНЕДЖЕРА. Затем фраза SELECT применяется к каждой группе. Поскольку в данной группе только одно значение КОД_МЕНЕДЖЕРА, то никакой неопределенности КОД_МЕНЕДЖЕРА в группе нет. Для каждой группы фраза SELECT выводит КОД_МЕНЕДЖЕРА, а также вычисляет и выводит значение MAX(СТАВКА). Результат представлен выше.

В команде SELECT со встроенными функциями могут встречаться только те столбцы, которые входят во фразу GROUP BY. Обратите внимание, что КОД_МЕНЕДЖЕРА может использоваться в команде SELECT, поскольку он входит во фразу GROUP BY.

Фраза GROUP BY. Означает, что строки должны быть разбиты на группы с общими значениями указанного столбца (столбцов).

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

AVG(MAX(СТАВКА))

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

С командой GROUP BY можно использовать команду WHERE:

Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1.SELECT ТИП, AVG(УРОВЕНЬ) FROM ЗДАНИЕ

WHERE СТАТУС = 1 GROUP BY ТИП

 

Результат

Фраза WHERE выполняется перед командой GROUP BY. Таким образом, ни одна группа не может содержать строку, в которой статус отличен от 1. Строки статуса 1 группируются по значению ТИП, а затем к каждой группе применяется фраза SELECT.

Фраза HAVING. Накладывает условий на группы.

Мы также можем применять условия и к группам, созданным фразой GROUP BY. Это делается при помощи фразы HAVING. Предположим, например, что мы решили конкретизировать один из предыдущих запросов:

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

Мы можем отразить это условие соответствующей командой HAVING:

SELECT КОД_МЕНЕДЖЕРА, MAX(СТАВКА) FROM РАБОЧИЙ
   GROUP BY КОД_МЕНЕДЖЕРА HAVING COUNT (*) > 1

Результат:

Разница между фразами WHERE и HAVING состоит в том, что WHERE применяется к строкам, в то время как HAVING применяется к группам.

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

Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1. Рассматривать только те типы зданий, максимальный уровень качества которых не превышает 3.

SELECT ТИП, AVG(УРОВЕНЬ) FROM ЗДАНИЕ
WHERE СТАТУС = 1 GROUP BY ТИП
HAVING MAX(УРОВЕНЬ) <= 3

Результат:

ТИП

AVG(УРОВЕНЬ)

Магазин

1

Жилой дом

3

Обратите внимание, что начиная с фразы FROM фразы выполняются по порядку, а затем применяется фраза SELECT. Так, к таблице ЗДАНИЕ применяется фраза WHERE, и все строки, в которых СТАТУС отличен от 1 удаляются. Оставшиеся строки группируются по ТИП; все строки с одинаковым значением ТИП оказываются в одной группе. Таким образом создается несколько групп, по одной для каждого значения ТИП. Затем к каждой группе применяется фраза HAVING, и те группы, в которых максимальное значение уровня качества превышает 3, удаляются. Наконец, к оставшимся группам применяется фраза SELECT.

 

5.3.7. Встроенные функции и подзапросы

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

Запрос: У кого из работников почасовая ставка выше среднего'

SELECT ИМЯ_РАБОЧЕГО FROM РАБОЧИЙ WHERE СТАВКА >
(SELECT AVG(СТАВКА) FROM РАБОЧИЙ)

Результат:

ИМЯ_РАБОЧЕГО
Петров
Зайцев
Гаранин

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

В коррелированных запросах также могут использоваться встроенные функции:

Запрос: У кого из работников почасовая ставка выше средней почасовой ставки среди подчиненных того же менеджера?

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

SELECT A.ИМЯ_РАБОЧЕГО FROM РАБОЧИЙ A WHERE A.СТАВКА >
(SELECT AVG(B.СТАВКА) FROM РАБОЧИЙ ВWHERE B.КОД_МЕНЕДЖЕРА = A. КОД_МЕНЕДЖЕРА)

Результат:

ИМЯ_РАБОЧЕГО
Петров
Зайцев
Гаранин

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

 

5.3.8. Операции реляционной алгебры

Как отмечалось ранее и как вы, вероятно, заметили, SQL напоминает реляционное исчисление наличием целевого списка (фраза SELECT) и определяющего выражения (фраза WHERE). В SQL-92, однако, реализованы некоторые операции реляционной алгебры, которые мы сейчас обсудим. В частности, объединение, пересечение, разность и соединение реализованы в явном виде как операторы SQL. Мы рассмотрим их по очереди.

Операторы UNION (объединение), INTERSECT (пересечение) и EXCEPT (разность). Как и в реляционной алгебре, операторы объедине! пересечения и разности применяются к двум таблицам, которые должны быть объединительно-совместимы. Этот термин в SQL имеет несколько отличающееся значение. Две таблицы объединительно-совместимы, если в них одинаковое число столбцов и соответствующие столбцы имеют совместимые: типы данных, то есть такие типы, которые могут быть легко преобразованы друг в друга. Например, два числовых типа не обязательно должны совпадать, но они должны преобразовываться друг в друга.

Объединительно-совместимые таблицы. Две или более таблицы, имеющие эквивалентные (по количеству и областям) наборы столбцов.

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

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

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

(SELECT * FROM SP_SUBORD) ONION (SELECT * FROM SP_MGR)
или в другом виде
SELECT * FROM (TABLE SP_SUBORD UNION TABLE SP_MGR).

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

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

(SELECT * FROM SP_SUBORD) UNION ALL (SELECT * FROM SP_MGR)
или формой
SELECT * FROM (TABLE SP_SUBORD UNION ALL TABLE SP_MGR)

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

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

INTERSECT. Операция, создающая теоретико-множественное пересечение двух таблиц.
(SELECT * FROM SP_SUBORD) INTERSECT (SELECT * FROM SP_MGR)
или в другой форме
SELECT * FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)

Как и в случае объединения, ни одна строка не появляется в пересечении более одного раза. Однако, если в одной таблице имеется m копий строки, а в другой таблице имеется n копий строки, и m<n, то пересечение будет содержать m копий этой строки, если мы воспользуемся таким синтаксисом:

(SELECT * FROM SP_SUBORD) INTERSECT ALL
(SELECT * FROM SP_MGR)

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

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

EXCEPT. Операция, создающая теоретико-множественную разность двух таблиц.

(SELECT * FROM SP_SUBORD) EXCEPT (SELECT * FROM SP_MGR)
или в другой форме
SELECT * FROM (TABLE SP_SUBORD EXCEPT TABLE SP_MGR)

Если мы воспользуемся синтаксисом
(SELECT * FROM SP_SUBORD) EXCEPT ALL (SELECT * FROM SP_MGR)
в случае, когда в таблице SP_MGR имеется m копий строки, а в таблице SP_SUBORD имеется n копий строки, где m>n, то в результирующей таблице будет m-n копий этой строки. Если же m <= n, то в результирующей таблице не будет ни одной копии этой строки.

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

Запрос: Кто из штукатуров начинает работу 9 октября?

(SELECT * FROM РАБОЧИЙ WHERE СПЕЦИАЛЬНОСТЬ = 'Штукатур')
INTERSECT CORRESPONDING BY (КОД_РАБОЧЕГО)
(SELECT * FROM РАБОТА WHERE ДАТА_НАЧАЛА = '09.10')

Результат:
КОД_РАБОЧЕГО
1520

Мы выделили часть SQL-выражения, важную для нашего обсуждения. Две фразы SELECT определяют две таблицы, которые явно не являются объединительно-совместимыми. Однако мы можем взять их пересечение, оговорив, что рассматриваются только те столбцы, которые есть в обеих таблицах. Мы указываем, какие столбцы рассматривать, в команде CORRESPONDING BY. В нашем случае рассматривается только столбец КОД_РАБОЧЕГО. Система оставит из результатов выполнения команд SELECT только значения в столбце КОД_РАБОЧЕГО. Затем она возьмет пересечение этих двух множеств, и это будет результат запроса. Таким образом, в результате получится список ИД тех работников, которые имеют специальность “штукатур” и которые получили работу, которая должна начинаться 9 октября — в точности это и требовалось.

Такой же подход используется для операторов UNION и EXCEPT. Фраза CORRESPONDING BY следует за оператором и перечисляет столбцы, общие для обеих рассматриваемых таблиц. Рассмотрим еще несколько примеров:

Запрос: Какие здания являются зданиями офисов или же на них назначен рабочий 1412?

(SELECT * FROM ЗДАНИЕ WHERE ТИП = 'Офис')
UNION CORRESPONDING BY (КОД_ЗДАНИЯ)
(SELECT * FROM РАБОТАWHERE КОД_РАБОЧЕГО = 1412)

Результат:
КОД_ЗДАНИЯ
312
210
111
460
435

515

Запрос: На какие здания офисов не назначен рабочий 1235?

(SELECT * FROM ЗДАНИЕ WHERE ТИП = 'Офис') EXCEPT
CORRESPONDING BY (КОД_ЗДАНИЯ)
(SELECT * FROM РАБОТА WHERE КОД_РАБОЧЕГО =1235)

Результат:
КОД_ЗДАНИЯ
210
111

Вы, вероятно, заметили, что эти запросы относительно легко сформу ровать. Мы просто определяем подходящие подмножества строк из двух '. лиц, а затем применяем соответствующий оператор UNION, INTERSECT : EXCEPT. Это существенно расширяет практические возможности языка, скольку многие люди считают, что подобные запросы проще формуляров* , пользуясь именно таким подходом. Например, применим к последнему осу “старый” подход SQL:

SELECT КОД_ЗДАНИЯ FROM ЗДАНИЕ WHERE ТИП = 'Офис' AND
NOT EXISTS (SELECT * FROM РАБОТА
WHERE ЗДАНИЕ.КОД_ЗДАНИЯ = РАБОТА.КОД_ЗДАНИЯ AND КОД_РАБОЧЕГО = 1235)

В этом решении использован коррелированный подзапрос и оператор NOT EXISTS. Естественно предположить, что большинству людей значительно сложнее сформулировать такое решение,. чем решение с оператором EXCEPT. Тем не менее, обратите внимание, что запрос можно было бы реализовать с помощью старого синтаксиса SQL. Это лишь не так просто.

Оператор JOIN (соединение). SQL-92 содержит несколько операторов соединения в явной форме: естественное соединение, внутреннее соединение, несколько внешних соединений, объединительное соединение и кросс-соединение. Мы рассмотрим только естественное и внутреннее соединения.

Естественное соединение. Соответственно названию, естественное соединение имеет то же значение в SQL, что и в реляционной алгебре. Предположим, что мы хотим соединить таблицы РАБОЧИЙ и РАБОТА. Мы можем использовать такой синтаксис:

РАБОЧИЙ NATURAL JOIN РАБОТА

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

SELECT P.КОД_РАБОЧЕГО, ИМЯ_РАБОЧЕГО, СТАВКА, СПЕЦИАЛЬНОСТЬ, КОД_МЕНЕДЖЕРА, КОД_РАБОЧЕГО, КОД_ЗДАНИЯ, ДАТА_НАЧАЛА, ЧИСЛО_ДНЕЙ FROM РАБОЧИЙ, РАБОТА WHERE P.КОД_РАБОЧЕГО = РАБОТА. КОД_РАБОЧЕГО

за исключением того, что первый столбец будет называться КОД_РАБОЧЕГО, а не P. КОД_РАБОЧЕГО. В общем случае естественное соединение связывает две таблицы по всем их общим столбцам, но эти столбцы включаются в результирующую таблицу только один раз. В результирующей таблице сначала располагаются общие столбцы, затем столбцы первой таблицы, затем оставшиеся столбцы второй таблицы.

JOIN USING. Предположим, у нас есть две таблицы А и В, и у них есть общие столбцы К, L, М и N. Предположим, что мы хотим соединить таблицы не по всем общим столбцам, а только по столбцам L и N. Тогда мы можем воспользоваться следующим выражением:

A JOIN В USING (L, N)

JOIN USING. Операция, связывающая таблицы только по указанным общим столбцам, имеющим равные значения.Это выражение даст тот же результат, что и SELECT-выражение, в котором в команде SELECT будут сначала перечислены столбцы L и N, за которыми следуют оставшиеся столбцы А и оставшиеся столбцы В. Фраза WHERE в таком выражении должна иметь вид

WHERE A.L = B.L AND A.N = B.N

JOIN ON. Если мы хотим задать более общее условие соединения двух таблиц, то можем пользоваться этой формой. Например, предположим, что мы хотим присоединить таблицу РАБОЧИЙ к ней самой, связав КОД_РАБОЧЕГО с КОД_МЕНЕДЖЕРА, чтобы получить информацию о менеджере каждого работника. Воспользуемся следующим выражением:РАБОЧИЙ W JOIN РАБОЧИЙ SUPV

ON W. КОД_МЕНЕДЖЕРА = SUPV КОД_РАБОЧЕГО.

JOIN ON. Операция, связывающая таблицы при выполнении условия.В этом примере мы создали две копии таблицы РАБОЧИЙ и дали им псевдонимы W и SUPV, соответственно. Фраза ON содержит условие равенства КОД_МЕНЕДЖЕРА копии W и КОД_РАБОЧЕГО копии SUPV таблицы РАБОЧИЙ.

 

5.3.9. Операции изменения данных

В SQL есть операции изменения данных INSET, UPDATE и DELETE, позволяющие, соответственно, добавлять строки, изменять значения в строках и удалять строки определенной таблицы в базе данных. Каждую из операций мы обсудим отдельно.

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

INSERT. Операция, добавляющая строки к таблице.

INSERT INTO РАБОТА (КОД_РАБОЧЕГО, КОД_ЗДАНИЯ, ДАТА_НАЧАЛА) VALUES (1284, 485, 13.05)

Это выражение вводит в таблицу РАБОТА одну строку. Имена столбцов, для которых задаются соответствующие значения, перечислены в скобках после имени обновляемой таблицы. Поскольку мы опустили ЧИСЛО_ДНЕЙ, в этот столбец будет помещено пустое значение.

Предположим, что мы создали новую таблицу с именем ЗДАНИЕ_2, состоящую из столбцов КОД_ЗДАНИЯ, ТИП и УРОВЕНЬ, и хотим заполнить эту таблицу строками из таблицы ЗДАНИЕ, имеющими СТАТУС 2. Тогда мы воспользуемся второй формой команды INSERT:

INSERT INTO ЗДАНИЕ _2
SELECT КОД_ЗДАНИЯ, ТИП, УРОВЕНЬ FROM ЗДАНИЕ
WHERE СТАТУС = 2

UPDATE. Операция UPDATE (изменить) всегда применяется ко всем строкам, удовлетворяющим условию выражения WHERE. Если мы хотим повысить на 5 процентов ставку каждого подчиненного начальника 1520, то нам потребуется следующее выражение:

UPDATE РАБОЧИЙ SET СТАВКА = 1.05 * СТАВКА
WHERE КОД_МЕНЕДЖЕРА = 1520

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

Если фраза WHERE отсутствует, то операция применяется к каждой строке таблицы. Например, если бы мы хотели повысить на 5 процентов ставку каждого работника, то мы должны были бы просто опустить команду WHERE в выражении UPDATE.

DELETE. Операция DELETE (удалить) также применяется ко всем строкам, удовлетворяющим условию WHERE. Если фраза WHERE отсутствует, то удаляются все строки таблицы. Допустим, что все рабочие, чей начальник имеет индекс 1520, были уволены, и мы хотим удалить соответствующие строки из базы данных. Это сделает следующее выражение:

DELETE FROM РАБОЧИЙ WHERE КОД_МЕНЕДЖЕРА =1520

DELETE. Операция, удаляющая строки из таблицы.

 

5.3.10. Совместное использование SQL с языками обработки данных

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

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

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

Базовый язык. Язык программ, в которые можно погружать SQL-выражения.

Приведем пример встроенной SQL-программы:

ЕХЕС SQL
   DECLARE WORK_ASGNMNT CURSOR
   FOR
   SELECT * FROM РАБОТА
   WHERE КОД_РАБОЧЕГО = REQUESTED-WORKER-ID
END-EXEC

Эта программа может быть встроена в программу, написанную на Коболе в качестве базового языка. Первая строка (ЕХЕС SQL) и последняя строка (END-EXEC) — выражения-флаги; они означают, что строки между ними - SQL-программа. Программа на Коболе, содержащая эту SQL-программу, перед компиляцией будет обработана компилятором предварительного прохода. Компилятор предварительного прохода распознает выражения-флаги и заменяет их CALL-выражениями — вызовами подпрограмм СУБД, которые во время выполнения будут обрабатывать эту SQL-программу. Когда программа на Коболе компилируется, компилятор игнорирует SQL CALL-выражения и компилирует всю остальную программу.

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

Остальная часть нашего примера содержит выражение DECLARE CURSOR, определяющее курсор. Курсор — это разновидность файла, содержимое которого создается во время исполнения программы. Обратите внимание, что в определении курсора участвует выражение SELECT. Фраза WHERE ссылается на столбец таблицы РАБОТА (КОД_РАБОТА) и означает, что строка должна быть выбрана, если значение КОД_РАБОТА равно значению переменной в программе на Коболе (REQUESTED-WORKER-ID). Никакие данные не будут выбраны до тех пор, пока курсор не будет открыт отдельной командой.

Команда OPEN курсор

OPEN WORK_ASGNMNT

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

Курсор. Средство погруженного SQL хранения результатов SQL-запроса для дальнейшей обработки.

Команда OPEN (открыть курсор). Выражение погруженного SQL, заставляющее СУБД обработать запрос курсора и записать результат в курсор.

Команда FETCH (извлечь). Команда, извлекающая одну строку из открытого курсора.

 

Тема 5.4. Определение представлений данных

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

Базовая таблица. Таблица, содержащая основные, или действительные данные.

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

Представление данных. Определение ограниченной части базы данных.CREATE VIEW B_РАБОЧИЙ AS SELECT КОД_РАБОЧЕГО, ИМЯ_РАБОЧЕГО, СПЕЦИАЛЬНОСТЬ, КОД_МЕНЕДЖЕРА FROM РАБОЧИЙ

B_РАБОЧИЙ в нашем примере — это имя вновь созданного представления данных. За именем представления данных может следовать перечисление имен столбцов представления данных, заключенное в скобки. В нашем случае мы опустили имена столбцов, так что имена столбцов будут просто унаследованы из таблицы, откуда они взяты. Часть выражения после слова AS называется спецификацией запроса. В качестве спецификации может использоваться любой правильно сформулированный запрос.

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

Система на самом деле не создает значения данных для B_РАБОЧИЙ до тех пор, пока к нему не обращаются. В этот момент спецификация запроса выполняется, создавая B_РАБОЧИЙ из данных таблицы РАБОЧИЙ, существующих в момент выполнения. Таким образом, данные представления динамически обновляются с изменением данных соответствующих таблиц базы данных.Предположим, что нас часто интересует информация об электриках: здания, на которых они назначены работать, и дата начала работы. Toгда нам подойдет такое представление данных:

CREATE VIEW ELEC_РАБОТА AS SELECT ИМЯ_РАБОЧЕГО,
КОД_ЗДАНИЯ, ДАТА_НАЧАЛА FROM РАБОЧИЙ, РАБОТА
WHERE СПЕЦИАЛЬНОСТЬ = ‘Электрик’ AND
РАБОЧИЙ.КОД_РАБОЧЕГО = РАБОТА.КОД_РАБОЧЕГО

При обращении к представлению данных ELEC_РАБОТА СУБД сначала породит значения его данных. В нашей базе данных ELEC_РАБОТА будет выглядеть следующим образом:

ELEC_РАБОТА

ИМЯ_РАБОЧЕГО

КОД_ЗДАНИЯ

ДАТА_НАЧАЛА

Иванов

312

10.10

Иванов

515

17.10

Гаранин

435

08.10

Гаранин

460

23.10

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

Запрос: Кто из электриков назначен на здание 435 и когда он должен начать работу?

SELECT ИМЯ_РАБОЧЕГО, ДАТА_НАЧАЛА FROM ELEC_РАБОТА WHERE КОД_ЗДАНИЯ = 435

Результат: Иванов 10.08

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

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

CREATE VIEW MAXIMUM (КОД_МЕНЕДЖЕРА, MAX_СТАВКА) AS SELECT КОД_МЕНЕДЖЕРА, MAX(СТАВКА) FROM РАБОЧИЙ GROUP BY КОД_МЕНЕДЖЕРА

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

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

Запрос: У кого есть подчиненные, чья ставка превышает 12 единиц? SELECT КОД_МЕНЕДЖЕРА FROM MAXIMUM WHERE MAX_СТАВКА> 12

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

КОД_МЕНЕДЖЕРА

MAX_СТАВКА

1311

15.50

1520

13.75

2320

10.00

3231

17.40

 

Затем она применит сам запрос. В результате получится

КОД_МЕНЕДЖЕРА

1311

1520

3231

 

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

Представление данных, в спецификации запроса которого участвует фраза GROUP BY, называется группированным представлением данных. В стандарте ANSI SQL существуют строгие ограничения на запросы к группированным представлениям данных. Эти ограничения таковы:

1. Фраза SELECT запроса к группированному представлению данных не может содержать встроенных функций.

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

3. Запрещено использовать команды GROUP BY и HAVING.

4. Подзапрос не может ссылаться на группированное представление данных.

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

  1. В команде SELECT перечислены только имена столбцов (то есть нет вычислений и встроенных функций); отсутствует ключевое DISTINCT.
  2. В команде FROM стоит ссылка только на одну таблицу (эта ссылка не может содержать никаких операций реляционной алгебры — UNION, INTERSECT, EXCEPT, JOIN); таблица, на которую ссылается FROM, должна быть либо базовой таблицей, либо обновляемым представлением данных.
  3. В команде WHERE нет подзапросов, ссылающихся на таблицу команды FROM главного запроса.
  4. Отсутствуют команды GROUP BY и HAVING.

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

 

Тема 5.5. Информационная схема

Один из критериев Кодда полностью реляционной СУБД состоит в том, что системная информация, описывающая базу данных поддерживается в виде реляционных таблиц, как и все другие данные. Эта описательная информация, или метаданные, обычно поддерживается в словаре данных, разновидность которого иногда называется информационной схемой. Информационная схема SQL-92 в основном удовлетворяет этому требованию, поскольку она структурирована в виде набора представлений данных таблиц (возможно, только гипотетических таблиц), каждая строка которых содержит описательную информацию о некотором объекте базы данных, таком, как таблица, столбец или область. В этом разделе мы дадим краткое описание информационной схемы SQL-92.

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

Информационная схема. Схема в каталоге метаданных.

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

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

INFORMATION_SCHEMA_CATALOG_NAME (имя каталога информационной схемы). Базовая таблица; состоит из одной строки и одного столбца; содержит имя схемы.

SCHEMADATA (данные о схемах). Содержит имена схем, созданных текущим пользователем.

DOMAINS (области). Содержит список всех областей, к которым может обращаться текущий пользователь.

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

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

COLUMNS (столбцы). Идентифицирует столбцы таблиц, перечисленных в представлениях данных TABLES.

TABLE_PRIVILEGES (привилегии таблиц)

COLUMN_PRIVILEGES (привилегии столбцов)

USAGE_PRIVILEGES (привилегии пользователя)

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

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

REFERENTIAL_CONSTRAINTS (ограничения ссылок). Содержит ограничительные условия на уровне ссылок, принадлежащие текущему пользователю.

CHECK_CONSTRAINTS (СНЕСК-ограничения). Перечисляет СНЕСК-ограничения, принадлежащие текущему пользователю.

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

ASSERTIONS (утверждения)

CHARACTER_SETS (символьные множества)

COLLATIONS (сортировки)

TRANSLATIONS **VIEW_TABLE_USAGE (использование таблиц в представлениях данных). Идентифицирует таблицы, от которых зависят представления данных, принадлежащие текущему пользователю.

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

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

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

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

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

 

Вопросы для самопроверки

1. Можно ли создать таблицу с помощью языка SQL?
2. Что такое FOREIGN KEY?
3. Позволяет ли язык SQL выполнять операции реляционной алгебры?
4. Для чего используется фраза WHERE?
5. Для чего служит звездочка во фразе SELECT * FROM ?
6. В каких случаях в языке SQL используется декартово произведение?
7. Что такое подзапрос? Какие виды подзапросов вы знаете?
8. Для чего в языке SQL используется фраза HAVING?
9. Можно ли с помощью языка SQL вычислять суммы для групп данных?
10. В каких случаях следует использовать оператор EXISTS?


назад | содержание | вперед