Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 26: выборка данных с использованием некэширующих представлений
Раздел 3: Использование представлений
3.1. Выборка данных с использованием представлений
3.1.1.Пример 26: выборка данных с использованием некэширующих представлений
Классические представления не содержат в себе данных, они лишь являются способом обращения к реальным таблицам базы данных. Альтернативой являются т.н. кэширующие (материализованные, индексированные) представления, которые будут рассмотрены в следующем разделе{215}.
Задача 3.1.1.a{210}: упростить использование решения задачи 2.2.9.d{132} так, чтобы для получения нужных данных не приходилось использовать представленные в решении{140} объёмные запросы.
Задача 3.1.1.b{213}: создать представление, позволяющее получать список авторов и количество имеющихся в библиотеке книг по каждому автору, но отображающее только таких авторов, по которым имеется более одной книги.
Ожидаемый результат 3.1.1.a.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить ожидаемый результат задачи 2.2.9.d{132}, т.е.:
s_id |
s_name |
b_name |
1 |
Иванов И.И. |
Евгений Онегин |
3 |
Сидоров С.С. |
Основание и империя |
4 |
Сидоров С.С. |
Язык программирования С++ |
Ожидаемый результат 3.1.1.b.
Выполнение запроса вида SELECT * FROM {представление} позволяет получить результат следующего вида (ни при каких условиях здесь не должны отображаться авторы, по которым в библиотеке зарегистрировано менее двух книг):
a_id |
a_name |
books_in_library |
6 |
Б. Страуструп |
2 |
7 |
А.С. Пушкин |
2 |
Решение 3.1.1.a{210}.
Построим решение этой задачи для MySQL на основе следующего уже написанного ранее (см. решение 2.2.9.d{140}) запроса:
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 210/545
Пример 26: выборка данных с использованием некэширующих представлений
MySQL |
Решение 3.1.1.a (исходный запрос, который надо «спрятать» в представлении) |
1SELECT `s_id`,
2`s_name`,
3`b_name`
4 |
|
FROM (SELECT `subscriptions`.`sb_subscriber`, |
|||
5 |
|
|
`sb_book` |
|
|
6 |
|
FROM |
`subscriptions` |
|
|
7 |
|
|
JOIN (SELECT `subscriptions`.`sb_subscriber`, |
||
8 |
|
|
|
MIN(`sb_id`) AS `min_sb_id` |
|
9 |
|
|
FROM |
`subscriptions` |
|
10 |
|
|
|
JOIN (SELECT `sb_subscriber`, |
|
11 |
|
|
|
|
MIN(`sb_start`) AS `min_sb_start` |
12 |
|
|
|
FROM |
`subscriptions` |
13 |
|
|
|
GROUP |
BY `sb_subscriber`) |
14 |
|
|
|
AS `step_1` |
|
15 |
|
|
|
ON `subscriptions`.`sb_subscriber` = |
|
16 |
|
|
|
`step_1`.`sb_subscriber` |
|
17 |
|
|
|
AND `subscriptions`.`sb_start` = |
|
18 |
|
|
|
`step_1`.`min_sb_start` |
|
19 |
|
|
GROUP |
BY `subscriptions`.`sb_subscriber`, |
|
20 |
|
|
|
`min_sb_start`) |
|
21 |
|
|
AS `step_2` |
|
|
22 |
|
|
ON `subscriptions`.`sb_id` = `step_2`.`min_sb_id`) |
||
|
|
|
|
|
|
23AS `step_3`
24JOIN `subscribers`
25ON `sb_subscriber` = `s_id`
26JOIN `books`
27ON `sb_book` = `b_id`
Видеале нам бы хотелось просто построить представление на этом запросе. Но MySQL младше версии 5.7.7 не позволяет создавать представления, опирающиеся на запросы, в секции FROM которых есть подзапросы. К сожалению, у нас
таких подзапроса аж три — `step_1`, `step_2`, `step_3`.
Для обхода существующего ограничения есть не очень элегантное, но очень простое решение — для каждого из таких подзапросов надо построить своё отдельное представление. Тогда в секции FROM будет не обращение к подзапросу (что запрещено), а обращение к представлению (что разрешено).
MySQL Решение 3.1.1.a
1-- Замена первого подзапроса представлением:
2CREATE OR REPLACE VIEW `first_book_step_1`
3AS
4SELECT `sb_subscriber`,
5MIN(`sb_start`) AS `min_sb_start`
6 |
|
FROM |
`subscriptions` |
7 |
|
GROUP |
BY `sb_subscriber` |
8 |
|
|
|
9-- Замена второго подзапроса представлением:
10CREATE OR REPLACE VIEW `first_book_step_2`
11AS
12SELECT `subscriptions`.`sb_subscriber`,
13MIN(`sb_id`) AS `min_sb_id`
14 |
|
FROM |
`subscriptions` |
|
15 |
|
|
JOIN |
`first_book_step_1` |
16 |
|
|
ON |
`subscriptions`.`sb_subscriber` = |
17 |
|
|
|
`first_book_step_1`.`sb_subscriber` |
18 |
|
|
|
AND `subscriptions`.`sb_start` = |
19 |
|
|
|
`first_book_step_1`.`min_sb_start` |
20 |
|
GROUP |
BY `subscriptions`.`sb_subscriber`, |
|
21 |
|
|
`min_sb_start` |
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 211/545
Пример 26: выборка данных с использованием некэширующих представлений
MySQL Решение 3.1.1.a
22-- Замена третьего подзапроса представлением:
23CREATE OR REPLACE VIEW `first_book_step_3`
24AS
25SELECT `subscriptions`.`sb_subscriber`,
26`sb_book`
27 |
|
FROM `subscriptions` |
|
28 |
|
JOIN |
`first_book_step_2` |
29 |
|
ON |
`subscriptions`.`sb_id` = `first_book_step_2`.`min_sb_id` |
30 |
|
|
|
31-- Создание основного представления:
32CREATE OR REPLACE VIEW `first_book`
33AS
34SELECT `s_id`,
35`s_name`,
36`b_name`
37 |
|
FROM `subscribers` |
|
38 |
|
JOIN |
`first_book_step_3` |
39 |
|
ON |
`sb_subscriber` = `s_id` |
40 |
|
JOIN |
`books` |
41 |
|
ON |
`sb_book` = `b_id` |
Обратите внимание, что каждое следующее представление в этом наборе опирается на предыдущее.
Теперь для получения данных достаточно выполнить запрос SELECT * FROM `first_book`, что и требовалось по условию задачи.
Решение для MS SQL Server также построим на основе ранее написанного запроса (см. решение 2.2.9.d{140}):
MS SQL |
Решение 3.1.1.a (исходный запрос, который надо «спрятать» в представлении) |
1WITH [step_1]
2AS (SELECT [sb_subscriber],
3 |
|
|
MIN([sb_start]) |
AS [min_sb_start] |
4 |
|
FROM |
[subscriptions] |
|
5 |
|
GROUP |
BY [sb_subscriber]), |
|
6[step_2]
7AS (SELECT [subscriptions].[sb_subscriber],
8 |
|
|
MIN([sb_id]) AS [min_sb_id] |
9 |
|
FROM |
[subscriptions] |
10 |
|
|
JOIN [step_1] |
11 |
|
|
ON [subscriptions].[sb_subscriber] = |
12 |
|
|
[step_1].[sb_subscriber] |
13 |
|
|
AND [subscriptions].[sb_start] = |
14 |
|
|
[step_1].[min_sb_start] |
15 |
|
GROUP |
BY [subscriptions].[sb_subscriber], |
16 |
|
|
[min_sb_start]), |
17[step_3]
18AS (SELECT [subscriptions].[sb_subscriber],
19 |
|
|
[sb_book] |
|
20 |
|
FROM |
[subscriptions] |
|
21 |
|
|
JOIN |
[step_2] |
22 |
|
|
ON |
[subscriptions].[sb_id] = [step_2].[min_sb_id]) |
23SELECT [s_id],
24[s_name],
25[b_name]
26 FROM [step_3]
27JOIN [subscribers]
28ON [sb_subscriber] = [s_id]
29JOIN [books]
30ON [sb_book] = [b_id]
Поскольку в MS SQL Server нет характерных для MySQL ограничений на запросы, на которых строится представление, конечное решение задачи получается добавлением в начало запроса одной строки:
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 212/545
Пример 26: выборка данных с использованием некэширующих представлений
MS SQL Решение 3.1.1.a
1CREATE VIEW [first_book] AS
2{текст исходного запроса, который мы «прячем» в представлении}
Теперь для получения данных достаточно выполнить запрос SELECT * FROM
[first_book], что и требовалось по условию задачи.
Решение для Oracle также построим на основе ранее написанного запроса (см. решение 2.2.9.d{140}):
Oracle |
Решение 3.1.1.a (исходный запрос, который надо «спрятать» в представлении) |
1WITH "step_1"
2AS (SELECT "sb_subscriber",
3 |
|
|
MIN("sb_start") |
AS "min_sb_start" |
4 |
|
FROM |
"subscriptions" |
|
5 |
|
GROUP |
BY "sb_subscriber"), |
|
6"step_2"
7AS (SELECT "subscriptions"."sb_subscriber",
8 |
|
|
MIN("sb_id") AS "min_sb_id" |
9 |
|
FROM |
"subscriptions" |
10 |
|
|
JOIN "step_1" |
11 |
|
|
ON "subscriptions"."sb_subscriber" = |
12 |
|
|
"step_1"."sb_subscriber" |
13 |
|
|
AND "subscriptions"."sb_start" = |
14 |
|
|
"step_1"."min_sb_start" |
15 |
|
GROUP |
BY "subscriptions"."sb_subscriber", |
16 |
|
|
"min_sb_start"), |
17"step_3"
18AS (SELECT "subscriptions"."sb_subscriber",
19 |
|
|
"sb_book" |
|
20 |
|
FROM |
"subscriptions" |
|
21 |
|
|
JOIN |
"step_2" |
22 |
|
|
ON |
"subscriptions"."sb_id" = "step_2"."min_sb_id") |
23SELECT "s_id",
24"s_name",
25"b_name"
26 FROM "step_3"
27JOIN "subscribers"
28ON "sb_subscriber" = "s_id"
29JOIN "books"
30ON "sb_book" = "b_id"
Поскольку в Oracle, как и в MS SQL Server нет характерных для MySQL ограничений на запросы, на которых строится представление, конечное решение задачи получается добавлением в начало запроса одной строки:
Oracle Решение 3.1.1.a
1CREATE OR REPLACE VIEW "first_book" AS
2{текст исходного запроса, который мы «прячем» в представлении}
Теперь для получения данных достаточно выполнить запрос SELECT * FROM "first_book", что и требовалось по условию задачи.
Решение 3.1.1.b{210}.
Решение этой задачи идентично во всех трёх СУБД и сводится к написанию запроса, отображающего данные об авторах и количестве их книг в библиотеке с учётом указанного в задаче условия: таких книг должно быть больше одной. Затем на полученном запросе строится представление.
Имя представления в Oracle не может превышать 30 символов, потому там слово with в имени представления пришлось сократить до одной буквы w.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 213/545
Пример 26: выборка данных с использованием некэширующих представлений
MySQL Решение 3.1.1.b
1CREATE OR REPLACE VIEW `authors_with_more_than_one_book`
2AS
3SELECT `a_id`,
4`a_name`,
5COUNT(`b_id`) AS `books_in_library`
6 |
|
FROM |
`authors` |
7 |
|
|
JOIN `m2m_books_authors` USING (`a_id`) |
8 |
|
GROUP |
BY `a_id` |
9 |
|
HAVING |
`books_in_library` > 1 |
MS SQL Решение 3.1.1.b
1CREATE VIEW [authors_with_more_than_one_book]
2AS
3SELECT [authors].[a_id],
4[a_name],
5COUNT([b_id]) AS [books_in_library]
6 |
|
FROM |
[authors] |
7 |
|
|
JOIN [m2m_books_authors] |
|
|
|
|
8 |
|
|
ON [authors].[a_id] = [m2m_books_authors].[a_id] |
9 |
|
GROUP |
BY [authors].[a_id], |
10 |
|
|
[a_name] |
11 |
|
HAVING |
COUNT([b_id]) > 1 |
Oracle Решение 3.1.1.b
1CREATE OR REPLACE VIEW "authors_w_more_than_one_book"
2AS
3SELECT "a_id",
4"a_name",
5COUNT("b_id") AS "books_in_library"
6 |
|
FROM |
"authors" |
7 |
|
|
JOIN "m2m_books_authors" USING ("a_id") |
8 |
|
GROUP |
BY "a_id", "a_name" |
9 |
|
HAVING |
COUNT("b_id") > 1 |
Задание 3.1.1.TSK.A: упростить использование решения задачи 2.2.8.b{127} так, чтобы для получения нужных данных не приходилось использовать представленные в решении{129} объёмные запросы.
Задание 3.1.1.TSK.B: создать представление, позволяющее получать список читателей с количеством находящихся у каждого читателя на руках книг, но отображающее только таких читателей, по которым имеются задолженности, т.е. на руках у читателя есть хотя бы одна книга, которую он должен был вернуть до наступления текущей даты.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 214/545