Материал: SQL в вопросах и задачах
116
з а п иси
п ред ста в ления
HIGH
_
RATING
из менить
з на ч ение
п оля
RATING
на
з на ч ение
,
отл ич ное
от
300.
Под обные
п роблемы
можно
устранить
п утем
в ключ ения
в
оп ред еление
п ред ста в ления
оп ции
WITH
CHECK
OPTION
.
Э та
оп ция
расп ространяет
услов ие
WHERE
д ля
з а п р оса
на
оп ера ции
обнов ления
и
в ста в ки
в
оп иса ние
п ред ста в ления
.
Н а п ример
:
CREATE
VIEW
HIGH
_
RATUNG
AS
SELECT
*
FROM
UNIVERSITY
WHERE
RATING
=
300
WITH
CHECK
OPTION
;
В
э том
случ а е
в ышеуп омянутые
оп ера ции
в ста в ки
строки
или
коррекции
п оля
RATING
буд ет
отклонены
.
О п ция
WITH
CHECK
OPTION
п омещ а ется
в
оп ред еление
п ред ста в ления
,
а
не
в
кома нд у
DML
,
та к
ч то
в се
кома нд ы
мод ифика ции
в
п р ед ста в лении
буд ут
п ров еряться
.
Рекоменд уется
исп ользов а ть
э ту
оп цию
в о
в сех
случ а ях
,
когд а
нет
п рич ины
раз реша ть
п ред ста в лению
п омещ а ть
в
та блицу
з на ч ения
,
которые
в
нем
са мом
не
могут
быть
в ид ны
.
5.5.5.
О перац ии
м одиф икац ии
в
предст авл ения х
,
м аскирую щ их
строки
и
стол бц ы
Ра ссмотренна я
в ыше
п роблема
в оз ника ет
и
п ри
в ста в ке
строк
в
п ред ста в ление
с
п ред ика том
,
исп ользующ им
п оля
ба з ов ой
та блицы
,
не
п рисутств ующ ие
в
са мом
п р ед ста в лении
.
Н а п р имер
,
ра ссмотрим
п ред ста в ление
CREATE
VIEW
MOSC
_
STUD
AS
SELECT
STUDENT
_
ID
,
SURNAME
,
STIPEND
FROM
STUDENT
WHERE
CITY
=
‘
М о ск
ва
’
;
Вид но
,
ч то
в
д а нное
п ред ста в ление
не
в ключ ено
п оле
CITY
та блицы
STUDENT
.
Ч то
буд ет
п роисход ить
п ри
п оп ытка х
в ста в ки
строки
в
э то
117
п ред ста в ление
?
Та к
ка к
мы
не
можем
ука з а ть
з на ч ение
CITY
в
п ред ста в лении
ка к
з на ч ение
п о
умолч а нию
(
в в ид у
отсутств ия
в
нем
э того
п оля
),
то
э тим
з на ч ением
буд ет
NULL
,
и
оно
буд ет
в в ед ено
в
п оле
CITY
ба з ов ой
та блицы
STUDENT
(
сч ита ем
,
ч то
д ля
э того
п оля
оп ция
NOT
NULL
не
исп ользуется
).
Та к
ка к
в
э том
случ а е
з на ч ение
п оля
CITY
ба з ов ой
та блицы
STUDENT
не
буд ет
рав няться
з на ч ению
‘
М о ск
ва
’
,
в ста в ляема я
стр ока
буд ет
исключ ена
из
са мого
п ред ста в ления
и
,
п оэ тому
,
не
буд ет
в ид на
п ользов а телю
.
Прич ем
та к
буд ет
п р оисход ить
д ля
любо й
в ста в ляемой
в
п ред ста в ление
MOSC
_
STUD
строки
.
Д ругими
слов а ми
,
п ользов а тель
в ообщ е
не
сможет
в ид еть
строки
,
в в од имые
им
в
э то
п ред ста в ление
.
Д а нна я
п роблема
не
реша ется
и
в
случ а е
,
если
в
оп р ед еление
п ред ста в ления
буд ет
д оба в лена
оп ция
WITH
CHECK
OPTION
CREATE
VIEW
MOSC
_
STUD
AS
SELECT
STUDENT
_
ID
,
SURNAME
,
STIPEND
FROM
STUDENT
WHERE
CITY
=
‘
М о ск
ва
’
WITH
CHECK
OPTION
;
Та ким
образ ом
,
в
оп р ед еленном
ука з а нными
сп особа ми
п ред ста в лении
,
можно
мод ифициров а ть
з на ч ения
п олей
или
уд а лять
строки
,
но
нельзя
в ст а в лят ь
строки
.
Исход я
из
э того
,
рекоменд уется
д а же
в
тех
случ а ях
,
когд а
э того
не
требуется
п о
соображениям
п олез ности
(
и
д а же
безоп а сности
)
информа ции
,
п ри
оп ред елении
п ред ста в ления
в ключ а ть
в
него
в се
п оля
,
на
которые
имеется
ссылка
в
п ред ика те
.
Е сли
э ти
п оля
не
д олжны
отобража ться
в
в ыв од е
та блицы
,
в сегд а
можно
исключ ить
их
уже
в
з а п росе
к
п ред ста в лению
.
Д ругими
слов а ми
,
можно
было
бы
оп ред елить
п ред ста в ление
MOSC
_
STUD
п од обно
след ующ ему
:
CREATE
VIEW
MOSC
_
STUD
AS
SELECT
*
FROM
STUDENT
WHERE
CITY
=
‘
М о ск
ва
’
WITH
CHECK
OPTION
;
Э та
кома нд а
з а п олнит
в
п ред ста в лении
п оле
CITY
од ина ков ыми
з на ч ениями
,
которые
можно
п росто
исключ ить
из
в ыв од а
с
п омощ ью
д ругого
з а п роса
уже
к
э тому
сформиров а нному
п ред ста в лению
,
ука з а в
в
з а п росе
только
п оля
,
необход имые
д ля
в ыв од а
.
118
SELECT
STUDENT
_
ID
,
SURNAME
,
STIPEND
FROM
MOSC
_
STUD
;
5.6.
Агрегированны е
представл ения
Соз д а ние
п ред ста в лений
с
исп ользов а нием
а грегиров а нных
функций
и
п ред ложения
GROUP
BY
яв ляется
уд обным
инстр ументом
д ля
неп рерыв ной
обработки
и
интер п рета ции
из в лека емой
информа ции
.
Пред п оложим
,
необход имо
след ить
з а
колич еств ом
студ ентов
,
сд а ющ их
э кз а мены
,
колич еств ом
сд а нных
э кз а менов
,
колич еств ом
сд а нных
п ред метов
,
сред ним
ба ллом
п о
ка жд ому
п ред мету
.
Д ля
э того
можно
сформиров а ть
след ующ ее
п ред ста в ление
CREATE
VIEW
TOTALDAY
AS
SELECT
EXAM
_
DATE
,
COUNT
(
DISTINCT
SUBJ
_
ID
)
AS
SUBJ
_
CNT
,
COUNT
(
STUDENT
_
ID
)
AS
STUD_CNT
,
COUNT
(
MARK
) AS
MARK
_
CNT
,
AVG
(
MARK
)
AS
MARK
_
AVG
,
SUM
(
MARK
)
AS
MARK
_
SUM
FROM
EXAM
_
MARKS
GROUP
BY
EXAM
_
DATE
;
Теп ерь
требуемую
информа цию
можно
ув ид еть
с
п омощ ью
п ростого
з а п роса
к
п ред ста в лению
:
SELECT
*
FROM
TOTALDAY
;
5.7.
П редставл ения
,
основанны е
на
нескол ь ких
т абл иц ах
Пред ста в ления
ч а сто
исп ользуются
д ля
объед инения
нескольких
та блиц
(
ба з ов ых
и
/
или
д ругих
п ред ста в лений
)
в
од ну
большую
в ир туа льную
та блицу
.
Та кое
решение
имеет
ряд
п р еимущ еств
:
•
п ред ста в ление
,
объед иняющ ее
несколько
та блиц
,
может
исп ользов а ться
ка к
п ромежуточ ный
ма кет
п ри
формиров а нии
сложных
отч етов
,
скрыв а ющ ий
д ета ли
объед инения
большого
колич еств а
исход ных
та блиц
.
•
п ред в а р ительно
объед иненные
п оисков ые
и
ба з ов ые
та блицы
119
обесп еч ив а ют
на илуч шие
услов ия
д ля
транз а кций
,
п оз в оляют
исп ользов а ть
комп а ктные
схемы
код ов
,
устраняя
необход имость
на п иса ния
д ля
ка жд ого
отч ета
д линных
объед иняющ их
п роцед ур
.
•
п оз в оляет
исп ользов а ть
п ри
формиров а нии
отч етов
более
на д ежный
мод ульный
п од ход
.
•
п ред в а р ительно
объед иненные
и
п ров еренные
п ред ста в ления
уменьша ют
в ероятность
ошибок
,
св яз а нных
с
неп олным
в ып олнением
услов ий
объед инения
.
М ожно
,
на п ример
,
соз д а ть
п ред ста в ление
,
которое
п ока з ыв а ет
имена
и
на з в а ния
сд а нных
п ред метов
д ля
ка жд ого
студ ента
:
CREATE
VIEW
STUD
_
SUBJ
AS
SELECT
A
.
STUDENT
_
ID
,
C
.
SUBJ
_
ID
,
A
.
SURNAME
,
C
.
SUBJ
_
NAME
FROM
STUDENT
A
,
EXAM
_
MARKS
B
,
SUBJECT
C
WHERE
A
.
STUDENT
_
ID
=
B
.
STUDENT
_
ID
AND
B
.
SUBJ
_
ID
=
C
.
SUBJ
_
ID
;
Теп ерь
в се
п ред меты
студ ента
или
в сех
студ ентов
д ля
ка жд ого
п ред мета
можно
в ыбра ть
с
п омощ ью
п ростого
з а п роса
.
Н а п р имер
,
ч тобы
ув ид еть
в се
п ред меты
,
сд а нные
студ ентом
Ива н о вы м
,
п од а ется
з а п рос
:
SELECT
SUBJ
_
NAME
FROM
STUD
_
SUBJ
WHERE
SURNAME
=
‘
Ива н о в
’
;
5.8.
П редставл ения
и
подз апросы
При
соз д а нии
п ред ста в лений
могут
та кже
исп ользов а ться
и
п од з а п р осы
,
в ключ а я
св яз а нные
п од з а п росы
.
Пред п оложим
,
п ред усма трив а ется
п ремия
д ля
тех
студ ентов
,
которые
имеют
са мый
в ысокий
ба лл
на
любую
з а д а нную
д а ту
.
Пол уч ить
та кую
информа цию
можно
с
п омощ ью
п ред ста в ления
:
CREATE
VIEW
ELITE
_
STUD
AS
SELECT
B
.
EXAM
_
DATE
,
A
.
STUDENT
_
ID
,
A
.
SURNAME
FROM
STUDENT
A
,
EXAM
_
MARKS
B
WHERE
A
.
STUDENT
_
ID
=
B
.
STUDENT
_
ID
AND
B
.
MARK
=
(
SELECT MAX
(
MARK
)
120
FROM
EXAM
_
MARKS
C
WHERE
C
.
EXAM
_
DATE
=
B
.
EXAM
_
DATE
);
Е сли
,
с
д ругой
стороны
,
п ремия
буд ет
на з на ч а ться
только
студ енту
,
который
имел
са мый
в ысокий
ба лл
и
не
меньше
10-
ти
ра з
,
то
необход имо
исп ользов а ть
д р угое
п ред ста в ление
,
основ а нное
на
п ер в ом
:
CREATE
VIEW
BONUS
AS SELECT DISTINCT
STUDENT
_
ID
,
SURNAME
FROM
ELITE
_
STUD
A
WHERE
10
< =
(
SELECT COUNT
(*)
FROM
ELITE
_
STUD
B
WHERE
A
.
STUDENT
_
ID
=
B
.
STUDENT
_
ID
);
Из в леч ение
из
э той
та блицы
з а п исей
о
студ ента х
,
которые
буд ут
п олуч а ть
п ремию
,
в ып олняется
п ростым
з а п росом
:
SELECT
*
FROM BONUS
;
5.9.
О граничения
прим енения
операт ора
SELECT
дл я
соз дания
представл ений
Имеются
некоторые
в ид ы
з а п росов
,
не
д оп устимые
в
оп ред елениях
п ред ста в лений
.
О д иноч ное
п ред ста в ление
д олжно
основ ыв а ться
на
од иноч ном
з а п росе
,
п оэ тому
UNION
и
UNION
ALL
в
п ред ста в лениях
не
раз реша ются
.
Пред ложение
ORDER
BY
та кже
никогд а
не
исп ол ьзуется
в
оп ред елении
п ред ста в лений
.
Пр ед ста в ление
яв ляется
реляционной
та блицей
-
отношением
,
п оэ тому
его
строки
п о
оп ред елению
яв ляются
неуп оряд оч енными
.
5.10.
Удал ение
представл ений
Синта ксис
уд а ления
п р ед ста в ления
из
ба з ы
д а нных
п од обен
синта ксису
уд а ления
ба з ов ых
та бл иц
:
DROP
VIEW
<
и м я
п редст а вл ени я
>