15823

Основы Transact SQL: Сложные (многотабличные запросы)

Лекция

Информатика, кибернетика и программирование

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

Русский

2013-06-18

173.5 KB

22 чел.

Основы Transact SQL: Сложные (многотабличные запросы)

Основы Transact SQL: Сложные (многотабличные запросы)

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

 

  •  вложением SQL-выражения запроса в SQL-выражение другого запроса. Первый из них называют подзапросом, а второй — внешним или основным запросом;
  •  применением к SQL-запросам операторов объединения и соединения наборов записей, возвращаемых запросами. Эти операторы называют теоретико-множественными или реляционными.

 Подзапросы

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

Простые подзапросы

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

  •  подзапросы, возвращающие единственное значение;
  •  подзапросы, возвращающие список значений из одного столбца таблицы;
  •  подзапросы, возвращающие набор записей.

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

Подзапросы, возвращающие единственное значение

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

SELECT * FROM Customer WHERE IdCity = (SELECT idCity FROM City WHERE CityName = 'Казань')

В данном запросе сначала выполняется подзапрос (SELECT idCity FROM City WHERE CityName = 'Казань'). Он возвращает единственное значение (а не набор записей, поскольку по полю City организовано ограничение уникальности) – уникальный идентификатор города Казань. Если сказать точнее, то данный подзапрос возвращает единственную запись, содержащую единственное поле. Далее выполняется внешний запрос, который выводит все столбцы таблицы Customer и записи, в которых значение столбца IdCity равно значению, полученному с помощью подзапроса. Таким образом, сначала выполняется подзапрос, а затем внешний запрос, использующий результат подзапроса.

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

 Подзапросы, возвращающие список значений из одного столбца таблицы

Подзапрос, вообще говоря, может возвращать несколько записей. Чтобы в этом случае в условии внешнего оператора WHERE можно было использовать операторы сравнения, требующие единственного значения, используются кванторы, такие как ALL (все) и SOME (или ANY) (некоторый).
Рассмотрим общий случай использования запросов с кванторами ALL и SOME. Пусть имеются две таблицы: T1, содержащая как минимум столбец A, и T2, содержащая, по крайней мере, один столбец B. Тогда запрос с квантором ALL можно сформулировать следующим образом:

 SELECT A FROM T1 WHERE A оператор_сравнения ALL (SELECT B FROM T2)

 Здесь оператор_сравнения обозначает любой допустимый оператор сравнения. Данный запрос должен вернуть список всех тех значений столбца A, для которых оператор сравнения истинен для всех значений столбца B.
Запрос с квантором SOME, очевидно, имеет аналогичную структуру. Он должен вернуть список всех тех значений столбца A, для которых оператор сравнения истинен хотя бы для какого-нибудь одного значения столбца B.
Запрос: Список всех клиентов, проживающих в городах Казань или Елабуга.

 SELECT * FROM Customer WHERE IdCity = SOME(SELECT IdCity FROM City WHERE CityName IN ('Казань', 'Елабуга'))

 Предыдущий запрос может быть также реализован и с использованием оператора IN, который рассматривался в разделе “Фильтрация данных”.

 SELECT * FROM Customer WHERE IdCity IN (SELECT IdCity FROM City WHERE CityName IN ('Казань', 'Елабуга'))

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

 SELECT * FROM Customer WHERE IdCity NOT IN (SELECT IdCity FROM City WHERE CityName IN ('Казань', 'Елабуга'))

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

 SELECT * FROM Customer WHERE IdCity != ALL(SELECT IdCity FROM City WHERE CityName IN ('Казань', 'Елабуга'))

 Задание для самостоятельной работы: Cформулируйте запрос, возвращающий список всех клиентов (с указанием фамилии и имени), совершивших заказ за определенный период времени.

 Подзапросы, возвращающие набор записей

Подзапрос можно вставлять не только в операторы WHERE и HAVING, но и в оператор FROM.

 SELECT t.столбец1, t.столбец2, ... , t.столбецn FROM (SELCT ... ) t WHERE ...

 Здесь таблице, возвращаемой подзапросом в операторе FROM, присваивается псевдоним t, а внешний запрос выделяет столбцы этой таблицы и, возможно, записи в соответствии с некоторым условием, которое указано в операторе WHERE.

Связанные (коррелированные) подзапросы

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

 SELECT * FROM Customer c WHERE 1 < (SELECT COUNT(*) FROM [Order] r WHERE r.IdCust = c.IdCust)

 Ссылка на c.idCust в самом конце подзапроса - это то, что делает этот подзапрос связанным. Чтобы подзапрос мог выполняться, основной запрос должен поставлять значения для с.IdCust. В данном случае основной запрос извлекает из таблицы Customer все строки и выполняет по одному подзапросу для всех клиентов, передавая в него соответствующий Id клиента при каждом выполнении. Если подзапрос возвращает значение большее одного, условие фильтрации выполняется и строка добавляется в результирующий набор.
Связанные подзапросы часто используются с условиями сравнения (в предыдущем примере <) и вхождения в диапазон, но самый распространенный оператор, применяемый в условиях со связанными подзапросами, - это оператор EXISTS (существует). Оператор EXISTS применяется, если требуется показать, что связь есть, а количество связей при этом не имеет значения. Например, следующий запрос возвращает список всех товаров, которые когда-либо заказывали.

 SELECT IdProd, [Description] FROM Product p  WHERE EXISTS (SELECT * FROM OrdItem oi WHERE oi.IdProd = p.IdProd)

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

 SELECT IdProd, [Description] FROM Product p WHERE NOT EXISTS (SELECT * FROM OrdItem oi WHERE oi.IdProd = p.IdProd)

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


 

А также другие работы, которые могут Вас заинтересовать

45434. Применение метода оценочной функции при реализации интеллектуальных функций. Уровни интеллектуальности поведения 61.5 KB
  Черепаха представляет собой трехколесную тележку на которой размещены аккумуляторы система реле и электронная ламповая схема. Схема отрегулирована таким образом что при низком потенциале анода лампы Л1 запирается лампа Л2 и реле Р2 устанавливается так что исключается одновременное нахождение под током реле P1 и Р2. При умеренном освещении фотоэлемента лампа Л2 приоткрывается однако проводимый ею ток недостаточен для срабатывания реле P1 хотя уменьшение напряжения на аноде лампы и приводит к отпусканию реле Р2. Замыкание...
45435. Модели языка. Синтез и анализ языковых фрагментов. Проблема представления знаний 351 KB
  Проблема представления знаний Язык Человек лингвизирует свой мир живет в мире пересотворяемом с помощью его собственного языка. Границы языка границы мира. Если два языка подобны некой системе то они подобно друг другу.
45436. Понятие ядра и процесса, состояние процесса, подпроцессы. Межпроцессное взаимодействие 175.5 KB
  Межпроцессное взаимодействие Резидентная в RM часть OS UNIX называется ядром. Все работы вне ядра оформлены в виде процессов выполнения системных и прикладных программ. Под процессом понимается единица вычислительной работы потребляющая ресурсы предоставляемые ядром для обработки системных и прикладных программ которые оформлены как командные или выполняемые файлы на внешнем устройстве.
45437. Понятия приоритета и очереди процессов. Диспетчеризация и синхронизация процессов 128.5 KB
  Диспетчеризация и синхронизация процессов. Алгоритмы планирования процессов Планирование процессов включает в себя решение следующих задач: определение момента времени для смены выполняемого процесса; выбор процесса на выполнение из очереди готовых процессов; переключение контекстов старого и нового процессов. Средства аппаратной поддержки управления памятью и многозадачной среды в микропроцессорах Intel 80386 80486 и Pentium .
45438. Программирование на Shell. Скрипты 227 KB
  Shell интерпретатор командного языка В этом разделе описаны команды и символы имеющие специальное значение которые позволяют: находить с помощью шаблона и манипулировать группами файлов; запускать команду в фоновом режиме или в определенное время; выполнять последовательно группу команд; перенаправлять стандартный ввод и вывод; завершать работающие программы. Таблица 20 Метасимволы Символ Функция [ ] Эти метасимволы позволяют указывать сокращенные имена файлов при поиске по шаблону Означает что команда будет выполняться...
45439. Управление оперативной памятью (распределение и защита) в многозадачной ОС. Механизм реализации виртуальной памяти 211 KB
  Механизм реализации виртуальной памяти. От выбранных механизмов распределения памяти между выполняющимися процессорами в значительной степени зависит эффективность использования ресурсов системы ее производительность а также возможности которыми могут пользоваться программисты при создании своих программ. С другой стороны поскольку любой процесс имеет потребности в операциях вводавывода и процессор достаточно часто переключается с одной задачи на другую желательно в оперативной памяти расположить достаточное количество активных задач с...
45440. Планирование и диспетчеризация процессов и задач 611 KB
  Долгосрочный планировщик решает какой из процессов находящихся во входной очереди в случае освобождения ресурсов памяти должен быть переведен в очередь процессов готовых к выполнению. Они определяются не только переключениями контекстов задач но и при переключении на потоки другого приложения перемещениями страниц виртуальной памяти а также необходимостью обновления данных в кэше коды и данные одной задачи находящиеся в кэше не нужны другой задаче и будут заменены что приведет к дополнительным задержкам. От выбранных механизмов...
45441. Расчет системы управления автомобилем на базе технологии CAN 277 KB
  Узлы системы Батарея BTTERY Контроллер CONT Контроллер двигателя MOTOR Дисплей панели инструментов DISP Управление водителя DRIVE Тормоза BRKES Управление коробкой передач TRNS Сеть оперирует 32 сообщениями которые делятся на различные группы: Спорадические сигналы.0 BTTERY CONT 2 Ток батареи 8 0.0 BTTERY CONT 3 Температура батареи 8 0.0 BTTERY CONT 4 Параметры батареи 10 1.
45442. Расчет системы «Интеллектуальное здание» на базе технологии EIB 315 KB
  Узлы системы Контроллер CONT Система управления светом LIGHT Система управления теплом HET Система управления вентиляцией VENT Система управления дверью DOOR Охранная система SECUR Пожарная система FIRE Сеть оперирует 30 сообщениями которые делятся на различные группы: Спорадические сигналы. Номер сигнала Описания сигнала Размер в битах Задержка в мсек J Период выполнения T мсек Тип сообщения Крайний срок выполнения Dмсек Источник Приемник 1 Сигнал датчика двери 8 01 50 S 20 DOOR CONT 2 Проверка...