SQL в вопросах и задачах

5. Пр едста влен и я (VIEW)

5.1. П редставл ения – им енованны е з апросы

п ред став ления не сод ержат ника ких собств енных д а нных. Фактич ески

пр едст а в ление –эт о им енов а нна я т а бли ца , со д ер ж и м о е ко т о р о йявляет ся

момент станов итсясод ержаниемп ред став ления. Пред ста в ленияп оз в оляют:

∙огранич ив атьч исло столбцов , из которых п ольз ов ательв ыбирает или в которыев в од ит д а нные;

∙ огранич ив ать ч исло строк, из которых п ольз ов атель в ыбира ет или в которыев в од ит д а нные;

∙в ыв од итьд оп олнительныестолбцы, п реобраз ов анныеиз д ругих столбцов баз ов ой та блицы;

∙в ыв од итьгруп п ы стр ок таблицы.

Б ла год аря этому п р ед ста в ления д а ют в оз можностьгибкой настройки

(СОЗДАТЬ ПРЕДСТАВЛЕНИЕ). Н а п ример :

CREATE VIEW MOSC_STUD AS

SELECT *

FROM STUDENT

WHERE CITY = ‘М о сква ’;

Н а п ример, з а п рос д ляп росмотра п ред став ленияMOSC_STUD имеет в ид :

SELECT *

FROM MOSC_STUD;

5.2. П редставл ения т абл иц

Раз лич ают п ред став лениятаблиц и п р ед ста в лениястолбцов .

В п ростейшем п ред став лении та блиц в ыбираютсявсе строки и столбцы баз ов ой таблицы.

CREATE VIEW NEW_STUD_TAB AS SELECT *

FROM STUDENT;

Такоеп ред став ление, п о сути, э кв ив алентно п рименению синонима, но яв ляетсяменееэффектив ным, п оэтому п рименяетсяред ко.

5.3. П редставл ения ст ол бц ов

В п ростейшем в ид е п ред ста в ление столбцов в ыбирает в се строки и столбцы, п од обно п ред ста в лению таблиц; кроме того, в кач еств е имен столбцов п рименяютсяп сев д онимы. Н а п ример :

CREATE VIEW NEW_STUDENT

(NEW_STUDENT_ID, NEW_SURNAME, NEW_NAME, NEW_STIPEND, NEW_KURS, NEW_CITY, NEW_BIRTHDAY, NEW_UNIV_ID)

AS SELECT STUDENT_ID, SURNAME, NAME, STIPEND,

113

KURS, CITY, BIRTHDAY, UNIV_ID FROM STUDENT;

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

5.4. Модиф иц ирование предст авл ений

Д анные, п ред ъяв ляемые п ольз ов ателю ч ерез п ред став ление, могут из менятьсяс п омощ ьюкома нд мод ифика ции DML, но п ри этомфа ктич еская мод ифика цияд а нных буд етосущ еств лятьсянев самой в иртуальной таблице- п ред став лении, а буд ет п ерена п рав лена к соотв етств ующ ей баз ов ой таблице. Н а п ример, з а п рос на обнов лениеп ред став ленияNEW_STUDENT

UPDATE NEW_STUDENT

SET CITY = ‘М о сква ’

WHERE STUDENT_ID = 1004;

5.5. Маскирую щ ие предст авл ения

5.5.1. П редст авл ения , м аскирую щ ие ст ол бц ы

Д анный в ид п ред став лений огранич ив ает ч исло столбцов ба з ов ой таблицы, к которымв оз можен д оступ . Н а п ример, п ред став ление

CREATE VIEW STUD AS

SELECT STUDENT_ID, SURNAME, CITY FROM STUDENT;

д ает д оступ п ольз ов ателю к п олям STUDENT_ID, SURNAME, CITY ба з ов ой

114

таблицы STUDENT, п олностью скрыв а я от него как сод ержимое, так и сам факт налич ияв баз ов ой та блицеп олей NAME, STIPEND, KURS, BIRTHDAY и UNIV_ID.

5.5.2.О перац ии м одиф икац ии в предст авл ения х, м аскирую щ их ст ол бц ы

Пред став ления, как ужеотмеч алосьв ыше, могут из менятьсяс п омощ ью

п ред став лениестолбец имеет оп цию NOT NULL, то генерируетсясообщ ение об ошибке.

Л юбоеп рименениеоп ер атора DELETE уд а ляет строки баз ов ой таблицы нез а в исимо от их з нач ений.

5.5.3. П редст авл ения , м аскирую щ ие ст роки

Пред став ления могут также огр анич ив ать д оступ к строкам. О хв атыв аемые п ред став лением строки баз ов ой таблицы з ад а ются услов ием

115

п оказ ыв ает п ольз ов ателю только тестроки та блицы STUDENT, д лякоторых з нач ениеп оляCITY рав но ‘М о сква ’.

5.5.4. О перац ии м одиф икац ии в предст авл ения х, м аскирую щ их ст роки

К ажд ая в ключ енная в п ред став ление строка д оступ на д ля в ыв од а, обнов ления и уд а ления. Л юба я д оп устима я д ля основ ной таблицы строка в ста в ляетсяв ба з ов уютаблицу нез а в исимо от еев ключ енияв п ред став ление. При этом может в оз никнуть п роблема , состоящ ая в том, ч то з нач ения, в в ед енныеп ольз ов а телем баз ов уютаблицу ч ерез п р ед ста в ление, з нач ений, буд ут отсутств ов атьв п ред став лении, остав а ясьп ри этомв баз ов ой таблице. Рассмотримтакой случ а й:

CREATE VIEW HIGH_RATING AS SELECT *

FROM UNIVERSITY

WHERE RATING = 300;

Э то п р ед ста в ление яв ляется обнов ляемым. О но п росто огранич ив ает

ув ид етьи, на п ример, тут жеуд а лить. Тем более, ч то п ольз ов ательв ообщ е может нез нать– р аботает он в д анный момент с баз ов ой та блицей или с таблицей-п ред став лением.

А налогич ная ситуа ция в оз никнет, если в ка кой-либо сущ еств ующ ей

116

з ап иси п ред став ления HIGH_RATING из менитьз на ч ение п оля RATING на з нач ение, отлич ноеот 300.

Под обныеп роблемы можно устранитьп утем в ключ енияв оп ред еление

буд ут п ров еряться. Рекоменд уется исп ольз ов а тьэ ту оп цию в о в сех случ аях, когд а нет п рич ины раз реша тьп ред ста в лению п омещ а тьв та блицу з нач ения, которыев немсамомнемогут бытьв ид ны.

5.5.5.О перац ии м одиф икац ии в предст авл ения х, м аскирую щ их ст роки и ст ол бц ы

исп ольз уется). Так ка к в этом случ аез нач ениеп оля CITY баз ов ой таблицы STUDENT небуд ет рав нятьсяз нач ению ‘М о сква ’, в ста в ляемаястр ока буд ет

вст а влят ь строки. Исход яиз этого, рекоменд уетсяд ажев тех случ аях, когд а этого не требуется п о соображениям п олез ности (и д аже без оп асности) информации, п ри оп ред елении п ред ста в ления в ключ атьв него все п оля, на которыеимеетсяссылка в п ред ика те. Е сли эти п олянед олжны отображаться

CREATE VIEW MOSC_STUD AS

SELECT *

FROM STUDENT

WHERE CITY = ‘М о сква ’

WITH CHECK OPTION;

з ап росетолько п оля, необход имыед ляв ыв од а.

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.П редставл ения , основанны е на нескол ь ких т абл иц ах

Пред став ленияч а сто исп ольз уютсяд ляобъед инениянескольких таблиц (баз ов ых и/или д ругих п ред став лений) в од ну большую в ир туа льную таблицу. Такоерешениеимеетряд п р еимущ еств :

∙ п ред ста в ление, объед иняющ ее несколько таблиц, может исп ольз ов аться как п ромежуточ ный макет п ри формиров а нии сложных отч етов , скрыв а ющ ий д етали объед инениябольшого колич еств а исход ных таблиц.

∙ п ред в арительно объед иненные п оисков ые и баз ов ые таблицы

М ожно, нап ример, соз д атьп ред став ление, котороеп оказ ыв а ет имена и наз в аниясд анных п ред метов д лякажд ого студ ента :

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 дл я соз дания предст авл ений

Имеются некоторые в ид ы з ап росов , не д оп устимые в оп ред елениях

оп ред елении п ред став лений. Пр ед ста в лениеяв ляетсяреляционной таблицейотношением, п оэ тому его строки п о оп ред елению яв ляются неуп оряд оч енными.

5.10. Удал ение предст авл ений

Синта ксис уд аленияп р ед став ленияиз баз ы д анных п од обен синта ксису уд алениябаз ов ых таблиц:

DROP VIEW <и мя п редст а вл ени я>