Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Практическая работа №7
по курсу «Организация управления базами данных»
Теоретическая часть
Подзапрос - это оператор выбора, который содержится внутри другого оператора выбора, вставки, обновления или удаления, внутри условного оператора или внутри другого подзапроса.
Подзапросы обычно содержатся в предложениях where или having SQL оператора или в списке выбора этого оператора. С помощью подзапросов можно проводить дальнейший отбор данных из результатов других запросов. Оператор, содержащий подзапрос, может обрабатывать строки некоторой таблицы, основываясь на результатах вычисления списка выбора подзапроса, который в свою очередь может ссылаться на эту же таблицу как внешний запрос, или обращаться к другой таблице.
Операторы выбора, содержащие один или несколько подзапросов, называются также составными запросами или составными операторами выбора.
SQL оператор, который включает подзапросы, называемые также внутренними запросами, можно иногда заменить соединением. Есть вопросы, которые можно сформулировать только с помощью подзапросов.
Если нужно найти все книги, имеющие ту же цену, что и книга Straight Talk About Computers, то это можно сделать за два шага. Во-первых найти цену этой книги:
select price
from titles
where title = "Straight Talk About Computers"
Затем, используя этот результат во втором запросе, уже можно найти все книги, имеющие ту же стоимость, что и Straight Talk:
select title, price
from titles
where price = $19.99
С помощью подзапроса эта задача решается одним оператором:
select title, price
from titles
where price =
(select price
from titles
where title = "Straight Talk About Computers")
Общие правила написания и синтаксис подзапросов
Оператор выбора в подзапросе всегда должен быть заключен в круглые скобки.
Подзапросы могут быть вложенными в конструкциях (предложениях) where или having внешних операторов выбора (select), вставки (insert), обновления (update) или удаления (delete), а также вложенными в другие подзапросы или помещены в список выбора.
На подзапросы накладываются следующие ограничения:
Подзапросы нельзя использовать в списках предложений order by, group by.
Список выбора внутреннего подзапроса, которому предшествует операция сравнения, может содержать только одно выражение или название столбца, и подзапрос должен возвращать единственный результат. При этом тип данных столбца, указанного в конструкции where внешнего оператора, должен быть совместим c типом данных в столбце, указанным в списке выбора подзапроса (правила здесь такие же как и при соединении).
Подзапросы не могут обрабатывать свои результаты внутренним образом, т.е. подзапрос не может содержать конструкций order by
Количество вложенных уровней для подзапросов не должно превышать 16.
Максимальное число подзапросов на каждой стороне объединения не больше 16.
В следующем примере столбец pub_id в конструкции where внешнего запроса неявно определяется таблицей publishers из конструкции from этого запроса. Обращение к столбцу pub_id в списке выбора подзапроса определяется конструкцией from подзапроса, т.е. таблицей titles:
select pub_name
from publishers
where pub_id in
(select pub_id
from titles
where type = "business")
Общее правило таково: названия столбцов в операторе неявно определяются таблицей, которая указана в конструкции from этого уровня вложенности.
Если раскрыть все неявные предположения, то запрос будет выглядеть следующим образом:
select pub_name
from publishers
where publishers.pub_id in
(select titles.pub_id
from titles
where type = "business")
Никогда нелишне явно указывать название таблицы и всегда можно заменить неявные предположения явным использованием расширенных названий столбцов вместе с названием таблицы.
Коррелирующиеся (согласующиеся) названия таблиц необходимы в самосоединениях, поскольку таблица, присоединенная сама к себе, выступает в двух различных ролях. Коррелирующиеся названия могут также использоваться во вложенных запросах, которые ссылаются на одну и ту же таблицу, как во внутреннем, так и во внешнем запросе.
Например, с помощью следующего подзапроса можно найти писателей, живущих в одном городе с Ливией Карсен:
select au1.au_lname, au1.au_fname, au1.city
from authors au1
where au1.city in
(select au2.city
from authors au2
where au2.au_fname = "Livia"
and au2.au_lname = "Karsen")
Явное использование коррелирующихся названий позволяет понять, что как внешний, так и внутренний запрос ссылаются на одну и ту же таблицу authors.
Без явной корреляции подзапрос выглядит следующим образом:
select au_lname, au_fname, city
from authors
where city in
(select city
from authors
where au_fname = "Livia"
and au_lname = "Karsen")
Вышеприведенный запрос и другие операторы, в которых подзапрос и внешний запрос ссылаются на одну и ту же таблицу, могут быть заменены самосоединением:
select au1.au_lname, au1.au_fname, au1.city
from authors au1, authors au2
where au1.city = au2.city
and au2.au_lname = "Karsen"
and au2.au_fname = "Livia"
Подзапрос, замененный соединением, может выдавать результаты в другом порядке и потребовать ключевого слова distinct для исключения повторений.
Подзапрос может содержать в себе один или несколько подзапросов следующего уровня. Оператор может содержать подзапросы 16 уровней вложенности.
Рассмотрим следующую задачу, которая может быть решена с помощью оператора с подзапросами нескольких уровней: "Найти имена писателей, которые принимали участие в написании, по крайней мере, одной популярной компьютерной книги".
select au_lname, au_fname
from authors
where au_id in
(select au_id
from titleauthor
where title_id in
(select title_id
from titles
where type = "popular_comp") )
Самый внешний запрос выбирает имена и фамилии всех писателей. Запрос следующего уровня находит идентификационные номера писателей, а самый внутренний запрос возвращает идентификационные номера книг PC1035, PC8888 и PC9999.
Этот запрос также можно выразить с помощью соединения:
select au_lname, au_fname
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
and type = "popular_comp"
Подзапроcы могут быть вложенными в операторах модификации (update), удаления (delete) и вставки (insert) так же, как и в операторе выбора.
Замечание: Выполнение следующих примеров изменит содержимое базы данных pubs2. Следует обратиться к системному администратору, чтобы получить исходную копию этой базы.
В следующем запросе удваиваются цены всех книг, изданных компанией New Age Books. Этот оператор модифицирует таблицу titles, а подзапрос обращается к таблице publishers.
update titles
set price = price * 2
where pub_id in
(select pub_id
from publishers
where pub_name = "New Age Books")
Эквивалентный предыдущему оператор модификации, в котором используется соединение, выглядит следующим образом:
update titles
set price = price * 2
from titles, publishers
where titles.pub_id = publishers.pub_id
and pub_name = "New Age Books"
Можно удалить все записи о продажах книг по бизнесу с помощью следующего вложенного оператора выбора:
delete salesdetail
where title_id in
(select title_id
from titles
where type = "business")
Эквивалентный предыдущему оператор удаления, использующий соединение, выглядит следующим образом:
delete salesdetail
from salesdetail, titles
where salesdetail.title_id = titles.title_id
and type = "business"