Общие правила написания и синтаксис подзапросов

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

(select [distinct] список_выбора_подзапроса

[from [[database.]owner.]{название_таблицы | название_вьювера}

[({index название_индекса | prefetch size |[lru|mru]})]}

[holdlock | noholdlock] [shared]

[,[[database.]owner.]{ название_таблицы | название_вьювера }

[({index название_индекса | prefetch size |[lru|mru]})]}

[holdlock | noholdlock] [shared]]... ]

[where условия_отбора]

[group by выражение_без_агрегации [,

выражение_без_агрегации]... ]

[having условия_отбора])

Подзапросы могут быть вложенными в конструкциях (предложениях) where или having внешних операторов выбора (select), вставки(insert),обновления(update) или удаления (delete), а также вложенными в другие подзапросы или помещены в список выбора.

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

Ограничения на подзапросы

На подзапросы накладываются следующие ограничения:

· Подзапросы нельзя использовать в списках предложений order by, group by и compute by.

· Подзапрос не может содержать предложения for browse или union.

· Список выбора внутреннего подзапроса, которому предшествует операция сравнения, может содержать только одно выражение или название столбца, и подзапрос должен возвращать единственный результат. При этом тип данных столбца, указанного в конструкции where внешнего оператора, должен быть совместим c типом данных в столбце, указанным в списке выбора подзапроса (правила здесь такие же как и при соединении).

· В подзапросах не допускаются текстовые (text) и графические (image) данные.

· Подзапросы не могут обрабатывать свои результаты внутренним образом, т.е. подзапрос не может содержать конструкций order by, compute, или ключевого слова into.

· Коррелирующиеся (повторяющиеся) подзапросы не допускаются в конструкции selectобновляемого курсора, определенного с помощью declare cursor(определить курсор).

· Количество вложенных уровней для подзапросов не должно превышать 16.

· Максимальное число подзапросов на каждой стороне объединения не больше 16.

При составлении подзапросов необходимо придерживаться следующих правил.

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

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

§ Ключевое слово ORDER BY использовать в подзапросе нельзя, хотя в главном запросе ORDER BY использоваться может. Вместо ORDER BY в подзапросе можно использовать GROUP BY.

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

§ В списке ключевого слова SELECT не допускаются ссылки на значения типа BLOB, ARRAY, CLOB илиNCLOB.

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

§ Операцию BETWEEN по отношению к подзапросу использовать нельзя, но ее можно использовать в самом подзапросе.

26. Использование операторов подзапросов.

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

Операторы ANY, SOME и ALL также позволяют расширить возможности подзапросов и работы с ними. Операторы ANY и SOME чем-то похожи на IN, с которым мы работали. Но чтобы увидеть все преимущества, необходимо посмотреть реальный запрос и разобрать его результат.

Теперь познакомимся поближе со всеми этими операторами и увидим их работу на практике.

EXISTS

По идее, оператор EXISTS нужно было рассмотреть намного раньше, потому что он достаточно прост. Но я решил повременить, потому что его удобно использовать совместно с подзапросами. Например, давайте выведем на экран всех работников, у которых есть хоть какой-либо телефон. Как это сделать? Можно связать две таблицы и те записи работников и получить связанные строки. Это и будут работники, у которых есть номера телефонов.

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

SELECT * FROM tbPeoples plWHERE EXISTS (SELECT * FROM tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples)

В этом запросе мы запрашиваем все записи из таблицы tbPeoples, в качестве условия происходит проверка EXISTS. Для каждой записи работника выполняется запрос, который указан в скобках после EXISTS:

SELECT * FROM tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples

Здесь мы связываем с внешним запросом таблицу tbPhoneNumbers. Если этот запрос найдет хотя бы один телефон для текущего работника, то EXISTS возвращает истину и строка попадает в результат.

Чтобы отобразить все записи работников, которые не имеют телефонов, достаточно перед EXISTS поставить оператор NOT:

SELECT * FROM tbPeoples plWHERE NOT EXISTS (SELECT * FROM tbPhoneNumbers pn WHERE pl.idPeoples=pn.idPeoples)

Благодаря оператору NOT происходит обратная операция, т.е. в результат попадает та строка, для которой запрос после EXISTS не вернул ни одной строки.

ANY, SOME и ALL

Операторы ANY и SOME абсолютно одинаковы. Оба они дают один и тот же результат, поэтому вы можете использовать тот, который больше нравиться. Мне больше нравиться ANY, потому что это слово короче аж на одну букву :).

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

SELECT * FROM tbPhoneNumbersWHERE idPhoneType IN ( SELECT idPhoneType FROM tbPhoneType WHERE vcTypeName in ('Сотовый рабочий', 'Сотовый домашний') )

В данном примере используется IN для сравнения поля "idPhoneType" с одним из значений, возвращаемым подзапросом. То же самое можно сделать с помощью оператора ANY, только используется этот немного по-другому:

SELECT * FROM tbPhoneNumbersWHERE idPhoneType &qt; ANY ( SELECT idPhoneType FROM tbPhoneType WHERE vcTypeName in ('Сотовый рабочий', 'Сотовый домашний') )

Так в чем же разница? А она кроется в том, как читается этот запрос. Попробуем проговорить его: «Выбрать все записи из таблицы tbPhoneNumbers, где поле "idPhoneType" равно любому из значений, полученных подзапросом». Разница в том, что IN работает также как и = ANY, но с оператором ANY можно использовать любые другие операторы сравнения: = или <&qt; или != или &qt; или &qt;= или !&qt; или < или <= или !<. Например, следующий запрос выбирает все телефоны из таблицы tbPhoneNumbers, у которых "idPhoneType" больше любого из значений, полученных в подзапросе:

SELECT * FROM tbPhoneNumbersWHERE idPhoneType &qt; ANY ( SELECT idPhoneType FROM tbPhoneType WHERE vcTypeName in ('Сотовый рабочий', 'Сотовый домашний') )

Если ANY сравнивает с любым из значений и если "idPhoneType" будет больше хотя бы одного, то запись попадает в результат. Если ANY заменить на оператор ALL, то строка попадет в результирующий набор, только если она будет больше всех значений, которые возвращает подзапрос:

SELECT * FROM tbPhoneNumbersWHERE idPhoneType &qt; ALL ( SELECT idPhoneType FROM tbPhoneType WHERE vcTypeName in ('Сотовый рабочий', 'Сотовый домашний') )

Если ANY и SOME требуют, чтобы условие выполнилось хотя бы с одной строкой результата подзапроса, то ALL будет требовать, чтобы условие выполнилось ко всем строкам.

27. Жизненный цикл БД.

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

Этап 1. Проектирование.

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

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

3. Разработка физической модели данных (физический уровень проектирования).

Этап 2. Создание.

2.1. Генерация физической модели данных.

2.2. Подготовка среды хранения.

2.3. Ввод и контроль данных

Этап3. Эксплуатация.

3.1. Поддержка работы приложений.

3.2. Генерация отчетов.

3.3. Администрирование баз данных.