II. Использование функций

1. Агрегатные функции

Среди наиболее часто используемых функций отметим:

Sum - сумма значений по столбцу;

Avg - среднее значение в столбце;

Max - максимальное значение в столбце;

Min - минимальное значение в столбце.

Выдать общее количество поставщиков

Select count (*) from S

Результат: 5

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

Select count ( distinct номер_поставщика ) from SP

Результат: 4

Выдать количество поставок для детали P2.

Select count (*) from SP

where номер_детали='P2'

Результат: 4

Выдать общее количество поставляемых деталей 'P2'.

Select sum (количество) from SP

where номер_детали='P2'

Результат: 1000

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

Select avg(количество) as average,

min(количество) as minimum,

max(количество) as maximum

from SP where номер_поставщика='S1'

2. Строковые функции

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

Substr(s,n,[l]) - функция возвращает подстроку s, начинающуюся с n длиной l;

Lower(s) - функция возвращает строку s, преобразованную к нижнему регистру;

Length(s) - функция возвращает длину строки s.

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

Select Substr(lower(name), 1, 2) from s

III. Запросы, использующие соединения

1. Простое эквисоединение.

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

Select S.*,P.* from S, P

where S.город=P.город

Всего 10 строк.

2. Эквисоединение с дополнительным условием.

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

Select S.номер_поставщика, p.номер_детали, рейтинг

from S, P

where S.город=P.город and S.рейтинг<>20

3. Соединение таблицы с ней самой.

Выдать все пары поставщиков из одного города.

Select one.номер_поставщика, two.номер_поставщика

from S one, S two

where one.город = two.город

and one.номер_поставщика < two.номер_поставщика

4. Соединение трех таблиц.

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

Select distinct S.город, P.город

from S, SP, P

where S.номер_поставщика = SP.номер_поставщика

and P.номер_детали = SP.номер_детали

IV. Группирование

1. Оператор group by группирует таблицу, представленную фразой from в группы т.о., чтобы в каждой группе все строки имели одно и тоже значение поля, указанного во фразе group by. Далее, к каждой группе перекомпанованной таблицы ( а не к каждой строке исходной таблицы) применяется фраза select, в результате чего, каждое выражение во фразе select принимает единственное значение для группы.

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

Select номер_детали, sum( количество)

from SP

where номер_поставщика <>'S1'

group by номер_детали

2. Фраза having.

Фраза having играет ту же роль для групп, что и фраза where для строк и используется для того, чтобы исключать группы, точно так же, как where используется для исключения строк. Выражение во фразе having должно принимать единственное значение для группы.

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

Select номер_детали

from SP

group by номер_детали

having count(*) > 1

V. Построение внешнего соединения и представления

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

1. Простое (внутреннее) соединение

Select S.номер_поставщика, S.фамилия, SP.количество

from S, SP

where S.номер_поставщика=SP.номер_поставщика

Для построения внутренних и внешних соединений стандарт SQL2 предусматривает следующую конструкцию во фразе from:

источник1 тип соединения источник2 [on (условие [,...])]

• Источник1. Первый из соединяемых наборов данных (имя таблицы или подзапрос).
• Тип соединения Возможные виды соединений:
• [Inner] Join - внутреннее соединение;
• Left [Outer] Join - левое внешнее соединение;
• Right [Outer] Join - правое внешнее соединение;
• Full [Outer] Join - полное внешнее соединение;
• Источник2. Второй из соединяемых наборов данных (имя таблицы или подзапрос).
• On (условие [,...]) Отношение между источниками - критерий, аналогичный тому, который задается в конструкции Where.

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

Select S.номер_поставщика, S.фамилия, SP.количество

from S inner join SP

on (S.номер_поставщика=SP.номер_поставщика)

2. Простое внешнее соединение двух таблиц.

Select S.номер_поставщика, S.фамилия, SP.количество

from S left outer join SP

on (S.номер_поставщика=SP.номер_поставщика)

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

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

3. Внешнее соединение простого соединения с третьей таблицей.

Для получении внешнего соединения будем использовать представление (View). Представление можно рассматривать как хранимый запрос, на основании которого создается объект базы данных. Этот объект схож с таблицей, но в его содержимом динамически отражаются только те записи, которые были заданы при создании. Создается представление командой Create view

Create view имя_представления as запрос,

а удаляется командой Drop view.

create view z1 as

select sp.номер_поствщика, sp.номер_детали, p.название, p.цвет, p.вес

from SP, P

where SP.номер_детали = P.номер_детали

and цвет in ('Красный', 'Зеленый');

select S.n_post, S.name, z1.n_det, z1.name, z1.cvet, z1.ves

from s left join z1

on (s.n_post=z1.n_post)

Первым оператором выполняет простое соединение таблиц SP и P, а затем оператором Select выполняется внешнее соединение как комбинирование этой информации с данными из главной таблицы S.

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

Оператор select, вложенный в спецификатор where другого оператора select (или одного из операторов insert, delete, update), называется подзапросом. В состав каждого подзапроса должны входить спецификаторы select и from. Кроме того, каждый подзапрос должен быть заключен в круглые скобки, чтобы указать серверу баз данных на то, что эту операцию следует выполнить первой.

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

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

Подзапрос включается в спецификатор where оператора select с помощью следующих ключевых слов:

• ALL
• ANY
• IN
• EXISTS

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

1. Фраза ALL.

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

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

Select x.номер_поставщика, x.фамилия, x.рейтинг

from S x

where x.рейтинг > all

(select y.рейтинг

from S y

where y.город='Лондон')

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

2. Фраза ANY.

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

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

Select x.номер_поставщика, x.фамилия, x.рейтинг

from S x

where x.рейтинг > any

(select y.рейтинг from S y where y.город='Париж')

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

3. Фраза IN.

3.1. Простой подзапрос.

Выдать фамилии поставщиков, поставляющих деталь P2.

Select фамилия

from S

where номер_поставщика in

(Select номер_поставщика

from SP

where номер_детали ='P2')

Сначала выполняется внутренний подзапрос, его результатом является выборка (S1, S2, S3, S4), затем - внешний запрос, который после подстановки результатов внутреннего подзапроса имеет вид:

Select фамилия

from S

where номер_поставщика in ('S1', 'S2', 'S3', 'S4')

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

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

Select фамилия

from S

where номер_поставщика in

(Select номер_поставщика

from SP

where номер_детали in

(select номер_детали

from P

where цвет='Красный'))

Сначала осуществляется самый внутренний подзапрос, дающий выборку (P1, P4, P6). После подстановки его результатов выполняется второй по вложенности подзапрос, дающий выборку (S1, S2, S4). Подстановка результатов второго выполненного подзапроса во внешний запрос приводит к окончательному результату.

3.3. Использование одной и той же таблицы в подзапросе внешнем запросе.

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

Select distinct номер_поставщика

from SP spx

where spx.номер_детали in

(Select spy.номер_детали

from SP spy

where spy.номер_поставщика='S2')

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

3.4. Подзапрос с оператором сравнения отличным от IN.

Выдать номера поставщиков, находящихся в том же городе, что и поставщик S1.

Select номер_поставщика

from S

where город =

(Select город

from S

where номер_поставщика ='S1')

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

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

3.5. Простой коррелированный подзапрос.

Выдать фамилии поставщиков, поставляющих деталь P2.

Seleсt фамилия

from S

where 'P2' in

(Select номер_детали

from SP

where номер_поставщика= S.номер_поставщика)

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

• выбирается первая строка из S (номер_поставщика='S1");
• выполняется подзапрос:

(Select номер_детали

from sp

where номер_поставщика='S1')

результатом подзапроса является выборка (P1, P2, P3, P4, P5, P6);

• завершается обработка запроса для первой строки из S, при выполнении которого проверяется условие

'P2' in ('P1', 'P2', 'P3', 'P4', 'P5', 'P6')

• поскольку проверяемое условие - истина, результатом обработки запроса для первой строки из S является фамилия "Смит";
• аналогично повторяется обработка для остальных строк таблицы S.

3.6. Коррелированный подзапрос с использованием в коррелированном и внешнем запросе одной и той же таблицы.

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

Select distinct spx.номер_детали

from SP spx

where spx.номер_детали in

(Select spy.номер_детали

from SP spy

where spy.номер_поставщика<>spx.номер_поставщика)

4. Фраза EXISTS.

4.1. Квантор существования EXISTS.

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

EXISTS (select * from...)

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

Выдать фамилии поставщиков, поставляющих деталь P2.

Select фамилия

from S

where exists

(Select *

from SP

where номер_поставщика = S.номер_поставщика

and номер_детали = 'P2')

Последовательность обработки запроса:

• выбирается первая строка из S (номер_поставщика='S1');
• поскольку условие номер_поставщика = 'S1' и номер_детали = 'P2' - истина, результат обработки запроса для первой строки - фамилия Смит.

4.2. Запрос, реализующий квантор общности.

Квантор общности FORALL в SQL не поддерживается, однако он может быть выражен через квантор существования при помощи тождества

FORALL x(p)=NOT(EXISTS x(NOT(p))).

Выдать фамилии поставщиков, которые поставляют все детали.

Эквивалентная формулировка задачи может звучать так:

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

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

Select фамилия

from S

where not exists

(Select * from P

where not exists

(Select * from SP

where номер_поставщика=S.номер_поставщика

and номер_детали=P.номер_детали))

5. Использование функций в подзапросе.

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

Select номер_поставщика from S

where рейтинг <

(Select max(рейтинг) from S)

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

Select номер_поставщика, рейтинг, город

from S sx

where рейтинг >=

(Select avg(рейтинг)

from S sy

where sy.город=sx.город)

VIII. ОБЬЕДЕНЕНИЕ

Объединяемые оператором UNION таблицы должны быть совместны по объединению:

• иметь одинаковое число столбцов;
• соответствующие столбцы должны иметь одинаковые типы.

Любое число предложений select может быть соединено оператором union. Избыточные дубликаты исключаются из результата объединения.

Выдать номера деталей, которые имеют вес более 16 фунтов, либо поставляются поставщиком S2.

Select номер_детали

from P

where вес>16

union

Select номер_детали

from SP

where номер_поставщика='S2'

IX. Оператора манипулирования данными. Удаление данных

Общая форма оператора удаления:

delete from таблица [where предикат]

1. Удаление единственной записи.

Удалить сведения о поставщике S1.

delete from S

where номер_поставщика='S1'

Результат: таблица S с отсутствующей строкой о поставщике S1.

2. Удаление множества записей.

Удалить сведения обо всех поставщиках из Лондона.

delete from S

where город='Лондон'

Результат: таблица S с отсутствующими строками о поставщиках из Лондона.

3. Удаление с подзапросом.

Удалить все поставки для поставщиков из Лондона.

delete from SP

where 'Лондон'=

(Select город from S

where S.номер_поставщика=SP.номер_поставщика)

Результат: таблица SP с отсутствующими строками о поставках для поставщиков из Лондона.

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

delete from S

X. Оператора манипулирования данными. Вставка данных

Общая форма оператора вставки.

Insert into таблица [(поле [,поле]...)]

values ( константа [,константа]...) или подзапрос

1. Вставка единственной записи.

Вставить новую поставку с номером поставщика S2, номером детали P4 и количеством 1000 на дату "30 ноября 1995 г.".

Insert into SP values ('S2', 'P4', '30.11.2005', 1000)

Результат: таблица SP с добавленной строкой о поставке поставщиком S2 детали P4.

2. Вставка множества записей.

Восстановить таблицу S.

Insert into S values ('S1', 'Смит', 20, 'Лондон');

Insert into S values ('S2', 'Джонс', 10, 'Париж');

Insert into S values ('S3', 'Блейк', 30, 'Париж');

Insert into S values ('S4', 'Кларк', 20, 'Лондон');

Insert into S values ('S5', 'Адамс', 30, 'Атенс')

Результат: восстановленная таблица S.

3. Перечисление имен столбцов.

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

Вставить строку о новом поставщике, занеся лишь номер поставщика, фамилию и город.

Insert into S(номер_поставщика, фамилия, город)

values ('S6', 'Боб', 'Нью-Йорк')

Результат: добавленная строка в таблице S.

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

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

Create table temp

(номер_детали char(6),

объем поставки smallint);

Insert into temp (номер_детали,обьем_поставки)

Select номер_детали, sum(количество)

from SP

group by номер_детали

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

5. Построение внешнего соединения с использованием оператора Insert.

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

Create table outside_t

(номер_поставщика char(5),

фамилия char(20),

рейтинг smallint,

город char(15),

номер_детали char(6));

Insert into outside_t

Select S.*, SP.номер_детали

from S, SP

where S.номер_поставщика=SP.номер_поставщика;

Insert into outside_t

Select S.*, 'NN'

from S

where not exists

(Select *

from SP

where SP.номер_поставщика =

S.номер_поставщика)

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

XI. Операторы манипулирования данными. Обновление данных

Общая форма оператора обновления

Update таблица

set поле=выражение [,поле=выражение]...[where предикат]

1. Обновление единственной записи.

Изменить цвет детали P2 на желтый, увеличить ее вес на 5 и установить значение города "неопределен".

Update P set цвет='желтый',

вес=вес+5,

город=NULL

where номер_детали='P2'

Результат: Таблица S c внесенными изменениями.

2. Обновление множества записей.

Удвоить рейтинг всех поставщиков в Лондоне.

Update S set рейтинг=2*рейтинг

where город='Лондон'

Результат: Таблица S с увеличенным рейтингом для поставщиков из Лондона.

3. Обновление с подзапросом.

Установить объем поставок, равный нулю для поставщиков из Лондона.

update SP set количество=0

where 'Лондон'=

(Select город

from S

where S.номер_поставщика=SP.номер_поставщика)

Результат: Таблица SP с внесенными изменениями.