Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 25: использование условий при модификации данных
MySQL |
|
Решение 2.3.5.c (получено на основе решения для Oracle) |
|
|
|||||
1 |
UPDATE |
'subscribers' |
|
|
|
|
|||
2 |
SET |
's_name' = |
|
|
|
|
|||
3 |
|
(SELECT |
|
|
|
|
|
||
4 |
|
|
CASE |
|
|
|
|
|
|
5 |
|
|
WHEN 'x' > 5 THEN |
|
|
|
|
||
6 |
|
|
(SELECT CONCAT('s_name', ' [', 'x', |
'] [RED]')) |
|||||
|
|
|
WHEN 'x' >= 3 AND 'x' <= 5 THEN |
|
|
|
|||
8 |
|
|
(SELECT CONCAT('s_name', ' [', 'x', |
'] [YELLOW]')) |
|||||
9 |
|
|
ELSE (SELECT CONCAT('s_name', ' [', 'x', '] [GREEN]')) |
||||||
10 |
|
END |
|
|
|
|
|
|
|
11 |
|
FROM |
(SELECT 's_id', |
|
|
|
|
||
12 |
|
|
|
|
COUNT('sb_subscriber') AS 'x' |
||||
13 |
|
|
|
FROM |
'subscribers' |
|
|
|
|
14 |
|
|
|
LEFT |
JOIN |
|
|
|
|
15 |
|
|
|
|
(SELECT 'sb_subscriber' |
|
|
||
16 |
|
|
|
|
FROM 'subscriptions' |
|
|
|
|
17 |
|
|
|
|
WHERE 'sb_is_active' = |
'Y') AS 'active_only' |
|||
18 |
|
|
|
|
ON 's_id' = 'sb_subscriber' |
||||
19 |
|
|
|
GROUP |
BY 's_id') AS 'prepared_data' |
||||
|
|
WHERE |
'subscribers' 's_id' = 'prepared data' 's id'l |
||||||
|
|
|
|||||||
MS SQL І Решение 2.3.5.c (получено на основе решения для Oracle) |
| |
|
|||||||
1 |
UPDATE [subscribers] |
|
|
|
|
||||
2 |
SET |
[s_name] = |
|
|
|
|
|||
3 |
|
(SELECT |
|
|
|
|
|
||
4 |
|
|
CASE |
|
|
|
|
|
|
5 |
|
|
WHEN [x] > 5 |
|
|
|
|
|
|
6 |
|
|
THEN (SELECT CONCAT([s_name], ' [', [x], '] [RED]')) |
||||||
|
|
|
WHEN [x] >= 3 AND [x] <= 5 |
|
|
|
|
||
8 |
|
|
THEN (SELECT CONCAT [s_name] ' |
[', [x], '] [YELLOW]')) |
|||||
9 |
|
|
ELSE (SELECT CONCAT([s_name], ' |
[', [x] |
|
'] [GREEN]')) |
|||
10 |
|
END |
|
|
|
|
|
|
|
11 |
|
FROM |
(SELECT [s_id] |
|
|
|
|
||
12 |
|
|
|
|
COUNT [sb_subscriber]1 AS [x] |
||||
13 |
|
|
|
FROM |
[subscribers] |
|
|
|
|
14 |
|
|
|
LEFT |
JOIN |
|
|
|
|
15 |
|
|
|
|
(SELECT [sb_subscriber] |
|
|
||
16 |
|
|
|
|
FROM [subscriptions] |
|
|
|
|
17 |
|
|
|
|
WHERE [sb_is_active] = |
'Y') AS [active_only] |
|||
18 |
|
|
|
|
ON [s_id] = [sb_subscriber] |
||||
19 |
|
|
|
GROUP |
BY [s_id]1 AS [prepared_data] |
||||
|
|
WHERE |
[subscribers] [s_ id] = [prepared data] [s id]I |
||||||
Задание 2.3.5.TSK.A: добавить в базу данных читателей с именами «Сидоров С.С.», «Иванов И.И.», «Орлов О.О.»; если читатель с таким именем уже существует, добавить в конец имени нового читателя порядковый номер в квадратных скобках (например, если при добавлении читателя «Сидоров С.С.» выяснится, что в базе данных уже есть четыре таких читателя, имя добавляемого должно превратиться в «Сидоров С.С. [5]»).
Задание 2.3.5.TSK.B: обновить все имена авторов, добавив в конец имени « [+]», если в библиотеке есть более трёх книг этого автора, или добавив в конец имени « [-]» в противном случае.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 220/545
Пример 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_name |
b_name |
s_id |
||
1 |
Иванов И.И. |
Евгений Онегин |
3Сидоров С.С. Основание и империя
4Сидоров С.С. Язык программирования С++
Ожидаемый результат 3.1.1.b.
Выполнение запроса вида SELECT * FROM {представление} позволяет
получить результат следующего вида (ни при каких условиях здесь не должны отображаться авторы, по которым в библиотеке зарегистрировано менее двух книг):
a_id |
a_name |
books_in_library |
6 |
Б. Страуструп |
2 |
7 |
А.С. Пушкин |
2 |
чРешение 3.1.1 .a210.
Построим решение этой задачи для MySQL на основе следующего уже написанного ранее (см. решение 2.2.9.d{140}) запроса:
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 221/545
Пример 26: выборка данных с использованием некэширующих представлений
MySQL |
Решение |
(исходный запрос, который надо «спрятать» в представлении) |
| |
|||
|
||||||
|
|
3.1.1.S |
|
|
|
|
1 |
SELECT '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'l |
|||
23 |
|
AS 'step 3' |
|
|
|
|
24 |
|
JOIN 'subscribers' |
|
|
|
|
25 |
|
ON |
subscriber' = 's id' |
|
|
|
26 |
|
JOIN 'books' |
|
|
|
|
27 |
|
ON |
book' = 'b id' |
|
|
|
|
|
|
|
|
|
|
В идеале нам бы хотелось просто построить представление на этом запросе. Но MySQL младше версии 5.7.7 не позволяет создавать представления, опирающиеся на запросы, в секции FROM которых есть подзапросы. К сожалению, у нас таких подзапроса аж три — ' step_1', ' step_2', ' step_3'.
Для обхода существующего ограничения есть не очень элегантное, но очень простое решение — для каждого из таких подзапросов надо построить своё отдельное представление. Тогда в секции FROM будет не обращение к подзапросу (что запрещено), а обращение к представлению (что разрешено).
MySQL і Решение 3.1.1.a [
1 -- Замена первого подзапроса представлением:
2 CREATE OR REPLACE VIEW 'first_book_step_1'
3 |
AS |
|
4 |
SELECT 'sb_subscriber', |
|
5 |
|
MIN('sb_start') AS 'min_sb_start' |
6 |
FROM |
'subscriptions' |
|
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 в примерах © Богдан Марчук Стр: 222/545
Пример 26: выборка данных с использованием некэширующих представлений
MySQL I Решение 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 (исходный запрос, который надо «спрятать» в представлении) | |
|
1 |
WITH [step_1] |
|
|
2 |
|
AS (SELECT |
[sb subscriber], |
3 |
|
|
MIN([sb start]) AS [min sb start] |
4 |
|
FROM |
[subscriptions] |
5 |
|
GROUP |
BY [sb subscriber]), |
6 |
|
[step_2] |
|
7 |
|
AS (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] |
|
18 |
|
AS (SELECT |
[subscriptions] [sb subscriber] |
19 |
|
|
[sb book] |
20 |
|
FROM |
[ subscriptions] |
21 |
|
|
JOIN [step 2] |
22 |
|
|
ON [subscriptions] [sb id] = [step 2] [min sb id] |
23 |
SELECT [s id], |
|
|
24 |
|
[s_name] |
|
25 |
|
[b_name] |
|
26 |
FROM [step_3] |
|
|
27 |
|
JOIN [subscribers] |
|
28 |
|
ON [sb subscriber] = [s id] |
|
29 |
|
JOIN [books] |
|
30 |
|
ON [sb book] = [b id] |
|
|
|
|
|
Поскольку в MS SQL Server нет характерных для MySQL ограничений на запросы, на которых строится представление, конечное решение задачи получается добавлением в начало запроса одной строки:
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 223/545
Пример 26: выборка данных с использованием некэширующих представлений
MS SQL і Решение 3.1.1.a |
[ |
1CREATE. VIEW [flrst_book] AS
2{текст исходного запроса, который мы «прячем» в представлении}
Теперь для получения данных достаточно выполнить запрос SELECT * FROM [first_book], что и требовалось по условию задачи.
Решение для Oracle также построим на основе ранее написанного запроса (см. решение 2.2.9.d{140}):
Oracle I |
Решение 3.1.1.a |
(исходный запрос, который надо «спрятать» в представлении) |
| |
|
1 |
WITH "step 1" |
|
|
|
2 |
|
AS (SELECT |
"sb subscriber", |
|
3 |
|
|
MIN("sb start") AS "min sb start" |
|
4 |
|
FROM |
"subscriptions" |
|
5 |
|
GROUP |
BY "sb subscriber"), |
|
6 |
|
"step_2" |
|
|
7 |
|
AS (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" |
|
|
18 |
|
AS (SELECT |
"subscriptions" "sb subscriber" |
|
19 |
|
|
"sb book" |
|
20 |
|
FROM |
"subscriptions" |
|
21 |
|
|
JOIN "step 2" |
|
22 |
|
|
ON "subscriptions" "sb id" = "step 2" "min sb id" |
|
23 |
SELECT "s id", |
|
|
|
24 |
|
"s name" |
|
|
25 |
|
"b name" |
|
|
26 |
FROM |
"step_3" |
|
|
27 |
|
JOIN "subscribers" |
|
|
28 |
|
ON "sb subscriber" = "s id" |
|
|
29 |
|
JOIN "books" |
|
|
30 |
|
ON "sb book" = "b id" |
|
|
|
|
|
|
|
Поскольку в Oracle, как и в MS SQL Server нет характерных для MySQL ограничений на запросы, на которых строится представление, конечное решение задачи получается добавлением в начало запроса одной строки:
Oracle і Решение 3.1.1.a [
1CREATE. OR REPLACE VIEW "fTrSt_b00k" AS
2{текст исходного запроса, который мы «прячем» в представлении}
Теперь для получения данных достаточно выполнить запрос SELECT * FROM "first_book", что и требовалось по условию задачи.
'IT Решение 3.1.1. b{210}.
Решение этой задачи идентично во всех трёх СУБД и сводится к написанию запроса, отображающего данные об авторах и количестве их книг в библиотеке с учётом указанного в задаче условия: таких книг должно быть больше одной. Затем на полученном запросе строится представление.
Имя представления в Oracle не может превышать 30 символов, потому там слово with в имени представления пришлось сократить до одной буквы w.
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 224/545