Связанные подзапросы

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

Строка внешнего запроса, для которой выполняется внутренний запрос, называется текущей строкой - кандидатом. Алгоритм выполнения связанного подзапроса состоит в следующем:

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

2. Сохранить значения этой строки в алиасе, имя которого указано в предложении FROM внешнего запроса.

3. Выполнить подзапрос. Использование в подзапросе значения из строки-кандидата внешнего запроса называется внешней ссылкой.

4. Оценить предикат внешнего запроса на основе результатов подзапроса, выполненного на шаге 3. Это позволяет определить, будет ли строка кандидат включена в состав выходных данных.

5. Повторять процедуру для следующей строки-кандидата таблицы до тех пор, пока не будут проверены все строки в таблице.

найти всех клиентов сделавших заказы 26.02.11

SELECT *

FROM Clients as outer

WHERE 26/02/2011 IN

(SELECT Odate

FROM Orders as inner

WHERE outer.Client_num=inner.Client_num)

insert into secondpart (uniq_id)

select firstpart.uniq_id from firstpart

where

not exists

(Select seconpart.uniq_id from secondpart Where

firstpart.uniq_id = secondpart.uniq_id)

Рассмотрим в подробностях пример записанный выше.

Добавляет записи во вторую таблицу с кодом uniq_id - такие что, записи с этим uniq_id существуют в первой части (first_part) и не существуют во второй.

Оператор Select используемый в операторе Insert в свою очередь использует связанный подзапрос для нахождения таких строк из первой таблицы для которых нет соответствия во второй. Результатом выполнения оператора Select является столбец из uniq_id’ов которые добавляются во вторую таблицу. Для оценки результатов самого внутреннего запроса используется оператор EXISTS.

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

EXISTS - принимает значение “истина” если подзапрос используемый в качестве аргумента генерирует выходные данные, и “ложь” в противном случае. В отличии от прочих операторов и предикатов, он не может принимать значения “неизвестно” (unknown). В примере EXISTS выбирает один столбец, аналогично предыдущим примерам. На самом деле несущественно сколько столбцов извлекает EXISTS поскольку он не применяет полученных значений, а только фиксирует наличие данных.

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

Объединение множества запросов в один.

Все предшествующие примеры показывают различные варианты запросов с расположением “один внутри другого”. Существует другой способ объединения множества запросов - их объединение с использованием предложения UNION.

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

Для выполнения команды UNION столбцы запросов входящие в состав выходных данных должны быть совместимы по объединению (union compatible).

- одинаковое количество столбцов (столбцы должны быть сравнимы по объединению)

Одинаковые типы данных(тип и длина - числовые)

Для символьных данных (тип и длина - строгость ограничений зависит от конкретного продукта)

Если для одного столбца установлено ограничение NOT NULL то это ограничение должно быть у соответствующих столбцов других запросов.

Синтаксис:

select -without-order-by

... UNION [ALL] select-without-order-by

... [ UNION [ALL] select-without-order-by ]...

... [ ORDER BY integer [ ASC | DESC ],... ]

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

alter procedure

yura.get_param(in sta_ char(15),in typ char(20),in metall char(15),in otpr char(20),in pol char(20),in otvets char(20),in from_ char(20),in to_ char(20),in stn_o char(20),in stn_n char(20),in kontr char(20))

result(stats smallint,"\\x27num_fr\\x27" char(6))

begin

(select status.stats,'status' from status

where status.name_s=sta_

union select types.typ_num,'types ' from types

where types.name_t=typ

union select owners.own_num,'owners' from owners

where owners.name_otv=otvets

order by 2 asc

Команды изменения данных DML

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

Format 1

INSERT INTO [ owner.]table-name [(column-name,...)]

... VALUES (expression | DEFAULT,...)

Format 2

INSERT INTO [ owner.]table-name [(column-name,...)]

... select-statement

Назначение

Для добавления одной записи используется формат 1.

Предложение DEFAULT может быть использовано для присвоения столбцу значений заданных для него по умолчанию. Если необязательный список имен столбцов задан то, значения из списка переносятся в указанные столбцы. Если список столбцов не указан - значения записываются в столбцы в том порядке в котором они были созданы (такой же порядок получается при использовании SELECT *). Записи добавляются в таблицу в произвольную позицию. (В реляционных БД таблицы не упорядочены.)

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

Ограничения доступа:

Пользователь должен иметь доступ по INSERT к указанной таблице table.

Формат 2 позволяет пользователю одной операцией добавлять в таблицу результаты сгенерированные оператором SELECT общего вида(без ограничений).Записи добавляются в произвольном порядке вне зависимости от того содержит ли оператор SELECT предложение ORDER BY. Столбцы в операторе SELECT должны совпадать со столбцами указанными в списке оператора INSERT или физическому порядку столбцов в таблице.(порядку в котором они были созданы (такой же порядок получается при использовании SELECT *)).

Examples

Insert into Fam Values (123,’Склеймин’)

Вставка NULL значений

В команде Insert могут быть указаны имена столбцов:

Insert into Fam (fam_cod, fam_val) Values (123,’Склеймин’)

INSERT INTO department (dept_id, dept_name)

VALUES (230, 'Eastern Sales')

Команда обновления значений столбцов

UPDATE table-list

... SET column-name = expression,...

... [ WHERE search-condition ]

... [ ORDER BY expression [ ASC | DESC ],... ]

Ограничение по правам доступа:

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

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

Стандарт SQL(старый) не допускает изменения нескольких таблиц одной командой Update, т.к. в выражении <column-name> нельзя указывать имя таблицы.

Каждый указанный столбец принимает значение выражения указанного справа от знака равенства. Структура выражения никак не ограничена. <column-name> может быть использовано в выражении — существующие значение будет использовано.

Если предложение Where не указано будут изменены все записи в таблице.

Если Where присутствует в команде - будут обновлены только те записи, которые удовлетворяют предикату <search-cond>.

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

Update разрешает использование подзапросов внутри предиката.

Счета в таблице нумеруются начиная с ID 2001.

Запрос перенумеровывает все существующие счета вычитанием 2000 из поля id.

UPDATE sales_order_items AS items,

sales_order AS orders

SET items.id = items.id - 2000,

orders.id = orders.id - 2000;

Update cust Set raiting=200

Обновление нескольких столбцов одной командой

Update cust Set raiting=200, city=‘Москва’ WHERE snum=101

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

Контрольные вопросы

1. В чем особенность выражений с подзапросами?

2. Каковы преимущества использования агрегатных функций в подзапросах?

3. Сформулируйте алгоритм выполнения связанных подзапросов.

4. Перечислите ограничения при выполнении оператора Union.

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

Дата добавления: 2015-06-25