Лабораторные_БД_ЭВМ_20 (AutoRecovered). Методические указания по выполнению лабораторных работ по дисциплине (модулю)
 Скачать 0.75 Mb.
|
|
Подзапросы 1. Цель и задачи работы Целью лабораторной работы является изучение и практическое применение подзапросов. 2. Порядок выполнения работы - ознакомится с теоретическими сведениями; - выполнить задание; - оформить отчет; - ответить на контрольные вопросы, заданные преподавателем. 3. Оформление отчета Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе. 4. Теоретические сведения SQL позволяет использовать одни запросы внутри других запросов, то есть вкладывать запросы друг в друга. Предположим, известна фамилия студента ("Петров"), но неизвестно значение поля STUDENT _ ID для него. Чтобы извлечь данные обо всех оценках этого студента, можно записать следующий запрос: SELECT * FROM EXAM_MARKS WHERE STUDENT_ID = (SELECT STUDENT_ID FROM STUDENT SURNAME =' Петров '); Как работает запрос SQL со связанным подзапросом? • Выбирается строка из таблицы, имя которой указано во внешнем запросе. • Выполняется подзапрос и полученное в результате его выполнения значение применяется для анализа этой строки в условии предложения WHERE внешнего запроса. • По результату оценки этого условия принимается решение о включении или не включении строки в состав выходных данных. • Процедура повторяется для следующей строки таблицы внешнего запроса. Следует обратить внимание, что приведенный выше запрос корректен только в том случае, если в результате выполнения указанного в скобках подзапроса возвращается единственное значение. Если в результате выполнения подзапроса будет возвращено несколько значений, то этот подзапрос будет ошибочным. В данном примере это произойдет, если в таблице STUDENT будет несколько записей со значениями поля surname = 'Петров'. В некоторых случаях для гарантии получения единственного значения в результате выполнения подзапроса используется DISTINCT. Одним из видов функций, которые автоматически всегда выдают в результате единственное значение для любого количества строк, являются агрегирующие функции. Оператор IN также широко применяется в подзапросах. Он задает список значений, с которыми сравниваются другие значения для определения истинности задаваемого этим оператором предиката. Данные обо всех оценках (таблица EXAM _ MARKS) студентов из Воронежа можно выбрать с помощью следующего запроса: SELECT * FROM EXAM_MARKS WHERE STUDENT_ID IN (SELECT STUDENT_ID FROM STUDENT WHERE CI TY = ' Воронеж '); Подзапросы можно применять внутри предложения HAVING. Пусть требуется определить количество предметов обучения с оценкой, превышающей среднее значение оценки студента с идентификатором 301: SELECTCOUNT (DISTINCT SUB JID) , MARK FROM EXAM_MARKS GROUP BY MARK HAVING MARK >( SELECT AVG (MARK) FROM EXAM_MARKS WHERE STUDENT _ ID = 301); Формирование связанных подзапросов. При использовании подзапросов во внутреннем запросе можно ссылаться на таблицу, имя которой указано в предложении FROM внешнего запроса. В этом случае такой связанный подзапрос выполняется по одному разу для каждой строки таблицы основного запроса. Пример: выбрать сведения обо всех предметах обучения, по которым проводился экзамен 20 января1999 г. SELECT * FROM SUBJECT SU WHERE'20/01/1999' IN (SELECT EXAM_DATE FROM EXAM_MARKS EX WHERE SU.SUB J_ID = EX.SUB J_ID); В некоторых СУБД для выполнения этого запроса, возможно, потребуется преобразование значения даты в символьный тип. В приведенном запросе SU и ЕХ являются псевдонимами (алиасами), то есть специально вводимыми именами, которые могут быть использованы в данном запросе вместо настоящих имен. В приведенном примере они используются вместо имен таблиц SUBJECT и EXAMJMARKS. Эту же задачу можно решить с помощью операции соединения таблиц: SELECTDISTINCT SU.SUB J_ID,SUBJ_NAME, HOUR, SEMESTER FROM SUBJECT FIRST, EXAM_MARKS SECOND WHERE FIRST.SUBJ_ID= SECOND.SUB J_ID AND SECOND.EXAM_DATE = '20/01/1999'; В этом выражении алиасами таблиц являются имена FIRST и SECOND. Можно использовать подзапросы, связывающие таблицу со своей собственной копией. Например, надо найти идентификаторы, фамилии и стипендии студентов, получающих стипендию выше средней на курсе, на котором они учатся. SELECTDISTINCT STUDENT_ID,SURNAME, STIPEND FROM STUDENT El WHERE STIPEND > (SELECT AVG(STIPEND) FROM STUDENT E2 WHEREEl . KURS = E 2. KURS ); Тот же результат можно получить с помощью следующего запроса: SELECTDISTINCT STUDENT_ID, SURNAME, STIPEND FROM STUDENT El, (SELECT KURS, AVG(STIPEND) AS AVG_STIPEND FROM STUDENT E2 GROUP BY E2.KURS) E3 WHERE E1.STIPEND>AVG_STIPENDANDE1.KURS=E3.KURS; Обратите внимание - второй запрос будет выполнен гораздо быстрее. Дело в том, что в первом варианте запроса агрегирующая функция AVG выполняется над таблицей, указанной в подзапросе, для каждой строки внешнего запроса. В другом варианте вторая таблица (алиас Е2) обрабатывается агрегирующей функцией один раз, в результате чего формируется вспомогательная таблица (в запросе она имеет алиас ЕЗ), со строками которой затем соединяются строки первой таблицы (алиас Е1). Следует иметь в виду, что реальное время выполнения запроса в большой степени зависит от оптимизатора запросов конкретной СУБД. Пусть, например, необходимо по данным из таблицы EXAM _ MARKS определить сумму полученных студентами оценок (значений поля MARK), сгруппировав значения оценок по датам экзаменов и исключив те дни, когда число студентов, сдававших в течение дня экзамены, было меньше 10. SELECT EXAM_DATE, SUM(MARK) FROM EXAM_MARKS A GROUP BY EXAM DATE HAVING 10 < (SELECTCOUNT(MARK) FROM EXAM_MARKS В WHERE A.EXAM_DATE = B.EXAM_DATE); Подзапрос вычисляет количество строк с одной и той же датой, совпадающей с датой, для которой сформирована очередная группа основного запроса. Используемый в SQL оператор EXISTS (СУЩЕСТВУЕТ) генерирует значение истина или ложь, подобно булеву выражению. Используя подзапросы в качестве аргумента, этот оператор оценивает результат выполнения подзапроса как истинный, если этот подзапрос генерирует выходные данные, то есть в случае существования (возврата) хотя бы одного найденного значения. В противном случае результат подзапроса-ложный. Оператор EXISTS не может принимать значение unknown (неизвестно). Пусть, например, нужно извлечь из таблицы EXAM MARKS данные о студентах, получивших хотя бы одну неудовлетворительную оценку. SELECT DISTINCT STUDENT_ID FROM EXAM_MARKS A WHERE EXISTS (SELECT * FROM EXAM_MARKS В WHERE MARK < 3 AND B.STUDENT_ID=A.STUDENT_ID); При использовании связанных подзапросов предложение EXISTS анализирует каждую строку таблицы, на которую имеется ссылка во внешнем запросе. Главный запрос получает строки-кандидаты на проверку условия. Для каждой строки-кандидата выполняется подзапрос. Как только подзапрос находит строку, где в столбце MARK значение удовлетворяет условию, он прекращает выполнение и возвращает значение истина внешнему запросу, который затем анализирует свою строку-кандидата. Например, требуется получить идентификаторы предметов обучения, экзамены по которым сдавались не одним, а несколькими студентами: SELECT DISTINCT SUBJID FROM EXAM_MARKS A WHEREEXISTS (SELECT * FROM EXAM_MARKS ВWHERE A.SUBJ_ID =B.SUBJ_ID AND A.STUDENT_ID<>B.STUDENT_ID); Часто EXISTS применяется с оператором NOT (по-русски NOT EXISTS интерпретируется, как "не существует..."). Если предыдущий запрос сформулировать следующим образом - найти идентификаторы предметов обучения, которые сдавались одним и только одним студентом (другими словами, для которых не существует другого сдававшего студента), то достаточно просто поставить NOT перед EXISTS. Следует иметь в виду, что в подзапросе, указываемом в операторе EXISTS, нельзя использовать агрегирующие функции. Возможности применения вложенных запросов весьма разнообразны. Например, пусть из таблицы STUDENT требуется извлечь строки для каждого студента, сдавшего более одного предмета. SELECT * FROM STUDENT FIRST WHERE EXISTS (SELECT SUBJ_ID FROM EXAM_MARKS S E COND GROUP BY SUBJID HAVINGCOUNT(SUBJID) > 1 WHERE FIRST.STUDENT_ID= SECOND.STUDENT_ID); Операторы сравнения с множеством значений имеют следующий смысл. IN Равно любому из значений, полученных во внутреннем запросе. NOT IN He равно ни одному из значений, полученных во внутреннем запросе. = ANY To же, что и IN . Соответствует логическому оператору OR . > ANY , > = ANY Больше, чем (либо больше или равно) любое полученное число. Эквивалент- но > или > = для самого меньшего полученного числа. < ANY , < = ANY Меньше, чем (либо меньше или равно) любое полученное число. Эквивалент < или < = для самого большего полученного числа. = ALL Равно всем полученным значениям. Эквивалентно логическому оператору AND . > ALL , > = ALL Больше, чем (либо больше или равно) все полученные числа. Эквивалент > или > = для самого большего полученного числа. < ALL , < = ALL Меньше, чем (либо меньше или равно) все полученные числа. Эквивалентно < или < =самого меньшего полученного числа. Следует иметь в виду, что в некоторых СУБД поддерживаются не все из этих операторов. Примеры запросов с использованием приведенных операторов. Выбрать сведения о студентах, проживающих в городе, где расположен университет, в котором они учатся. SELECT * FROM STUDENT S WHERE CITY = ANY (SELECT CITY FROM UNIVERSITY U WHERE U.UNIV_ID = S.UNIV_ID); Другой вариант этого запроса SELECT * FROM STUDENT S WHERE CITYIN (SELECT CITY FROM UNIVERSITY U WHERE U.UNIV_ID = S.UNIV_ID); Выборка данных об идентификаторах студентов, у которых оценки превосходят величину, по крайней мере, одной из оценок, полученных ими же 6 октября 1999 года. SELECT DISTINCT STUDENT_ID FROM EXAM_MARKSWHERE MARK > ANY (SELECT MARK FROM EXAM_MARKS WHERE EXAMDATE = '06/10/1999'); Оператор ALL, как правило, эффективно используется с неравенствами, а не с равенствами, поскольку значение равно всем, которое должно получиться в этом случае в результате выполнения подзапроса, может иметь место, только если все результаты идентичны. Такая ситуация практически не может быть реализована, так как, если подзапрос генерирует множество различных значений, то никакое одно значение не может быть равно сразу всем значениям в обычном смысле. В SQL выражение <> ALL реально означает не равно ни одному из результатов подзапроса. Подзапрос, выбирающий данные о названиях всех университетов с рейтингом более высоким, чем рейтинг любого университета в Воронеже: SELECT * FROM UNIVERSITY WHERE RATING > ALL (SELECT RATING FROM UNIVERSITY WHERE CI TY= ' Воронеж '); В этом запросе вместо ALL можно также использовать ANY. (Проанализируйте, как в этом случае изменится смысл приведенного запроса?) SELECT * FROM UNIVERSITY WHERE NOTRATING>ANY (SELECT RATING FROM UNIVERSITY WHERECITY = ' Воронеж '); Необходимо иметь в виду, что при обработке NULL -значений следует учитывать различие реакции на них операторов EXISTS, ANY и ALL . Когда правильный подзапрос не генерирует никаких выходных данных, оператор ALL автоматически принимает значение истина, а оператор ANY - значение ложь. Запрос SELECT * FROM UNIVERSITY WHERE RATING > ANY ( SELECT RATING FROM UNIVERSITY WHERE CITY = ' New York'); не генерирует выходных данных (подразумевается, что в базе нет данных об университетах из города NewYork ), в то время как запрос SELECT * FROM UNIVERSITY WHERE RATING > ALL (SELECT RATING FROM UNIVERSITY WHERE CITY = ' New York'); полностью воспроизведет таблицу UNIVERSITY. Использование NULL-значений создает определенные проблемы для рассматриваемых операторов. Когда в SQL сравниваются два значения, одно из которых NULL -значение, результат принимает значение UNKNOWN (неизвестно). Предикат UNKNOWN, так же как и FALSE -предикат, создает ситуацию, когда строка не включается в состав выходных данных, но результат при этом будет различен для разных типов запросов, в зависимости от использования в них ALL или ANY вместо EXISTS. Рассмотрим в качестве примера две реализации запроса: найти все данные об университетах, рейтинг которых меньше рейтинга любого университета в Москве. SELECT * FROM UNIVERSITY WHERE RATING < ANY (SELECT RATING FROM UNIVERSITY WHERE CITY= ' Москва '); SELECT * FROM UNIVERSITYA WHERE NOT EXISTS (SELECT * FROM UNIVERSITY В WHERE A. RATING >= В.RATING AND B.CITY=' Москва '); При отсутствии в таблицах NULL оба эти запроса ведут себя совершенно одинаково. Пусть теперь в таблице UNIVERSITY есть строка с NULL - значениями в столбце RATING. В версии запроса с ANY в основном запросе, когда выбирается поле RATING с NULL, предикат принимает значение UNKNOWN и строка не включается в состав выходных данных. Во втором же варианте запроса, когда NOT EXISTS выбирает эту строку в основном запросе, NULL -значение используется в предикате подзапроса, присваивая ему значение UNKNOWN. Поэтому в результате выполнения подзапроса не будет получено ни одного значения и подзапрос примет значение ложь. Это в свою очередь сделает NOT EXISTS истинным, и, следовательно, строка с NULL значением в поле RATING попадет в выходные данные. По смыслу запроса такой результат является неправильным, так как на самом деле рейтинг университета, описываемого данной строкой может быть и больше рейтинга какого-либо московского университета (он просто неизвестен). Указанная проблема связана с тем, что значение EXISTS всегда принимает значения истина или ложь, и никогда – UNKNOWN. Это является доводом для использования в таких случаях оператора ANY вместо EXISTS. При отсутствии NULL-значений оператор EXISTS может быть использован вместо ANY и ALL. Также вместо EXISTS и NOT EXISTS могут быть использованы те же самые подзапросы, но с использованием COUNT (*) в предложении SELECT. Например, запрос SELECT * FROM UNIVERSITY A WHERENOT EXISTS (SELECT* FROM UNIVERSITY В WHERE A.RATING > = В.RAT ING AND B .CITY = ' Москва '); может быть представлен и в следующем виде SELECT * FROM UNIVERSITY A WHERE 1 > (SELECTCOUNT(*) FROM UNIVERSITY В WHERE A.RATING > = В.RATING AND B.CITY= ' Москва '); 5. Оборудование Персональный компьютер с установленной операционной системой WindowsXP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБДPostgreSQL. 6. Задание на работу 1. Напишите пример запроса, в котором вместо любой из констант выражения, определяющего условие WHERE, используется скалярный подзапрос. 2. Напишите запрос с векторным подзапросом (ключевое слово IN) 3. Напишите два запроса с ключевыми словами ANY и ALL, делающие одно и то же. 4. Напишите пример запроса с табличным подзапросом (ключевое слово EXISTS). 5. Напишите пример запроса несколькими уровнями вложенности 6. Напишите пример запроса, в котором вместо таблицы, указываемой после ключевого слова FROM, используется подзапрос. 7. Напишите пример связанного подзапроса. Указания к выполнению заданий 1-5. а) сформулируйте смысл запроса на языке, понятном пользователю в данной предметной области, например б) приведите сам запрос в) приведите результат выполнения запроса. Данные должны быть подобраны таким образом, чтобы в результирующей выборке было 3-4 записи. 7. Контрольные вопросы Даны таблицы
Вывести список пассажиров, не летающих в Самару. Даны таблицы
Определить авторов, не печатающих свои книги в издательстве <АСТ>. Даны таблицы
Вывести список городов, телефонные тарифы которых выше среднего. Даны таблицы
Определить блюдо, которое можно приготовить быстрее всех остальных блюд. Даны таблицы
Определить список пассажиров, покупающих билеты на самые дальние рейсы. Лабораторная работа №9 Представления 1. Цель и задачи работы Целью лабораторной работы является изучение и практическое представлений. 2. Порядок выполнения работы - ознакомится с теоретическими сведениями; - выполнить задание; - оформить отчет; - ответить на контрольные вопросы, заданные преподавателем. 3. Оформление отчета Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе. 4. Теоретические сведения До сих пор речь шла о таблицах, обычно называемых базовыми таблицами. Это - таблицы, которые содержат данные. Однако имеется и другой вид таблиц, называемый VIEW или ПРЕДСТАВЛЕНИЯ. Таблицы-представления не содержат никаких собственных данных. Фактически представление — это именованная таблица, содержимое которой является результатом запроса, заданного опри описании представления. Причем данный запрос выполняется всякий раз, когда таблица-представление становится объектом команды SQL. Вывод запроса при этом в каждый момент становится содержанием представления. Представления позволяют: • ограничивать число столбцов, из которых пользователь выбирает или в которые вводит данные; • ограничивать число строк, из которых пользователь выбирает или в которые вводит данные; • выводить дополнительные столбцы, преобразованные из других столбцов базовой таблицы; • выводить группы строк таблицы. Благодаря этому представления дают возможность гибкой настройки выводимой из таблиц информации в соответствии с требованиями конкретных пользователей, позволяют обеспечивать защиту информации на уровне строк и столбцов, упрощают формирование сложных отчетов и выходных форм. Представление определяется с помощью команды CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ). Например: CREATE VIEW MOSCSTUD AS SELECT * FROM STUDENT WHERE CITY = ' Москва '; Данные из базовой таблицы, предъявляемые пользователю в представлении, зависят от условия (предиката), описанного в SELECT -запросе при определении представления. В созданную в результате приведенного выше запроса таблицу- представление MOSC _ STUD передаются данные из базовой таблицы STUDENT, но не все, а только записи о студентах, для которых значение поля CITY равно 'Москва'. К таблице MOSC _ STUD можно теперь обращаться с помощью запросов так же, как и к любой другой таблице базы данных. Например, запрос для просмотра представления MOSC _ STUD имеет вид: SELECT * FROM MOSC _ STUD; Различают представления таблиц и представления столбцов. В простейшем представлении таблиц выбираются все строки и столбцы базовой таблицы. CREATE VIEW NEW_STUD_TAB AS SELECT * FROM STUDENT; Такое представление, по сути, эквивалентно применению синонима, но является менее эффективным, поэтому применяется редко. В простейшем виде представление столбцов выбирает все строки и столбцы, подобно представлению таблиц; кроме того, в качестве имен столбцов применяются псевдонимы. Например: 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, KURS, CITY, BIRTHDAY, UNIV_ID FROM STUDENT; Представление столбцов является простым способом организации общей таблицы для группы пользователей или прикладных задач, которые используют собственные имена полей и таблицы. Данные, предъявляемые пользователю через представление, могут изменяться с помощью команд модификации DML, но при этом фактическая модификация данных будет осуществляться не в самой виртуальной таблице- представлении, а будет перенаправлена к соответствующей базовой таблице. Например, запрос на обновление представления NEW _ STUDENT UPDATE NEW _ STUDENT SETCITY =' Москва ' WHERE STUDENT _ ID = 1004; эквивалентен выполнению команды UPDATE над базовой таблицей STUDENT. Следует, однако, обратить внимание на то, что в общем случае, из-за того, что обычно в представлении отображаются данные из базовой таблицы в преобразованном или усеченном виде, применение команд модификации к таблицам-представлениям имеет некоторые особенности, рассматриваемые ниже. Данный вид представлений ограничивает число столбцов базовой таблицы, к которым возможен доступ. Например, представление CREATE VIEW STUD AS SELECT STUDENT_ID, SURNAME, CITY FROM STUDENT; дает доступ пользователю к полям STUDENT _ ID, SURNAME , CITY базовой таблицы STUDENT , полностью скрывая от него как содержимое, так и сам факт наличия в базовой таблице полей NAME , STIPEND , KURS , BIRTHDAY и UNIV _ ID . Представления, как уже отмечалось выше, могут изменяться с помощью команд модификации DML, но при этом модификация данных будет осуществляться не в самой таблице-представлении, а в соответствующей базовой таблице. В связи с этим, с представлениями, маскирующими столбцы, функции вставки и удаления работают несколько иначе, чем с обычными таблицами. Оператор INSERT, примененный к представлению, фактически осуществляет вставку строки в соответствующую базовую таблицу, причем во все столбцы этой таблицы независимо от того, видны они пользователю через представление или скрыты от него. В связи с этим, в столбцах, не включенных в представление, устанавливается NULL – значение или значение по умолчанию. Если не включенный в представление столбец имеет опцию NOT NULL, то генерируется сообщение об ошибке. Любое применение оператора DELETE удаляет строки базовой таблицы независимо от их значений. Представления могут также ограничивать доступ к строкам. Охватываемые представлением строки базовой таблицы задаются условием(предикатом) в конструкции WHERE при описании представления. Доступ через представление возможен только к строкам, удовлетворяющим условию. Например, представление CREATEVIEW MOSCSTUD AS SELECT * FROM STUDENT WHERE CITY = ' Москва '; Показывает пользователю только те строки таблицы STUDENT, для которых значение поля CITY равно 'Москва'. Каждая включенная в представление строка доступна для вывода, обновления и удаления. Любая допустимая для основной таблицы строка вставляется в базовую таблицу независимо от ее включения в представление. При этом может возникнуть проблема, состоящая в том, что значения, введенные пользователем в базовую таблицу через представление, значений, будут отсутствовать в представлении, оставаясь при этом в базовой таблице. Рассмотрим такой случай: CREATEVIEW HIGH_RATING AS SELECT * FROM UNIVERSITY WHERE RAT ING = 300; Это представление является обновляемым. Оно просто ограничивает доступ пользователя к определенным столбцам и строкам в таблице UNIVERSITY. Предположим, необходимо вставить с помощью команды INSERT следующую строку: INSERT INTO HIGH _ RATING VALUES (180,'Новый университет', 200, 'Воронеж'); Команда INSERT допустима в этом представлении. С помощью представления HIGH _ RATING строка будет вставлена в базовую таблицу UNIVERSITY. Однако, после появления этой строки в базовой таблице, из самого представления она исчезнет, поскольку значение поля RATING неравно 300, и, следовательно, эта строка не удовлетворяет условию предложения WHERE для отбора строк в представление. Для пользователя такое исчезновение только что введенной строки является неожиданным. Действительно, не понятно, почему после ввода строки в таблицу ее нельзя увидеть и, например, тут же удалить. Тем более, что пользователь вообще может не знать - работает он в данный момент с базовой таблицей или с таблицей-представлением. Аналогичная ситуация возникнет, если в какой-либо существующей записи представления HIGH _ RATING изменить значение поля RATING назначение, отличное от 300. Подобные проблемы можно устранить путем включения в определение представления опции WITH CHECK OPTION. Эта опция распространяет условие WHERE для запроса на операции обновления и вставки в описание представления. Например: CREATE VIEW HI GH_RATUNG AS SELECT * FROM UNIVERSITY WHERE RATING = 300 WITH CHECK OPTION; В этом случае вышеупомянутые операции вставки строки или коррекции поля RATING будет отклонены. Опция WITH CHECK OPTION помещается в определение представления, а не в команду DML, так что все команды модификации в представлении будут проверяться. Рекомендуется использовать эту опцию во всех случаях, когда нет причины разрешать представлению помещать в таблицу значения, которые в нем самом не могут быть видны. Рассмотренная выше проблема возникает и при вставке строк в представление с предикатом, использующим поля базовой таблицы, не присутствующие в самом представлении. Например, рассмотримпредставление: CREATE VIEW MOSCSTUD AS SELECT STUDENT_ID, SURNAME, STIPEND FROM STUDENT WHERE CITY = ' Москва '; Видно, что в данное представление не включено поле CITY таблицы STUDENT. Что будет происходить при попытках вставки строки в это представление? Так как мы не можем указать значение CITY в представлении как значение по умолчанию (ввиду отсутствия в нем этого поля), то этим значением будет NULL, и оно будет введено в поле CITY базовой таблицы STUDENT (считаем, что для этого поля опция NOT NULL не используется). Так как в этом случае значение поля CITY базовой таблицы STUDENT не будет равняться значению 'Москва', вставляемая строка будет исключена из самого представления и, поэтому, не будет видна пользователю. Причем так будет происходить для любой вставляемой в представление MOSC _ STUD строки. Другими словами, пользователь вообще не сможет видеть строки, вводимые им в это представление. Данная проблема не решается и в случае, если в определение представления будет добавлена опция WITH CHECK OPTION CREATE VIEW MOSCSTUD AS SELECT STUDENT_ID,SURNAME, STIPEND FROM STUDENT WHERE CITY = ' Москва ' WITH CHECK OPTION; Таким образом, в определенном указанными способами представлении, можно модифицировать значения полей или удалять строки, но нельзя вставлять строки. Исходя из этого, рекомендуется даже в тех случаях, когда этого не требуется по соображениям полезности (и даже безопасности) информации, при определении представления включать в него все поля, на которые имеется ссылка в предикате. Если эти поля не должны отображаться в выводе таблицы, всегда можно исключить их уже в запросе к представлению. Другими словами, можно было бы определить представление MOSC _ STUD подобно следующему: CREATEVIEW MOSCSTUD AS SELECT * FROM STUDENT WHERE CITY= ' Москва ' WITH CHECK OPTION; Эта команда заполнит в представлении поле CITY одинаковыми значениями, которые можно просто исключить из вывода с помощью другого запроса уже к этому сформированному представлению, указав в запросе только поля, необходимые для вывода. SELECT STUDENT_ID,SURNAME, STIPEND FROM M0SC_STUD; Создание представлений с использованием агрегированных функций и предложения GROUP BY является удобным инструментом для непрерывной обработки и интерпретации извлекаемой информации. Предположим, необходимо следить за количеством студентов, сдающих экзамены, количеством сданных экзаменов, количеством сданных предметов, средним баллом по каждому предмету. Для этого можно сформировать следующее представление CREATE VIEW TOTALDAY AS SELECT EXAM_DATE, COUNT(DISTINCT SUBJ_ID) AS SUBJ_CNT, COUNT(STUDENT_ID) AS STUD_CNT, COUNT(MARK) ASMARK_CNT, AVG(MARK) AS MARK_AVG, SUM(MARK) AS MARK_SUM FROM EXAM_MARKS GROUPBY EXAM_DATE; Теперь требуемую информацию можно увидеть с помощью простого запроса к представлению: SELECT * FROM TOTALDAY; нескольких таблиц (базовых и/или других представлений) в одну большую виртуальную таблицу. Такое решение имеет ряд преимуществ: • представление, объединяющее несколько таблиц, может использоваться как промежуточный макет при формировании сложных отчетов, скрывающий детали объединения большого количества исходных таблиц. • предварительно объединенные поисковые и базовые таблицы обеспечивают наилучшие условия для транзакций, позволяют использовать компактные схемы кодов, устраняя необходимость написания для каждого отчета длинных объединяющих процедур. • позволяет использовать при формировании отчетов более надежный модульный подход. • предварительно объединенные и проверенные представления уменьшают вероятность ошибок, связанных с неполным выполнением условий объединения. Можно, например, создать представление, которое показывает имена и названия сданных предметов для каждого студента: CREATE VIEW STUDSUBJ AS SELECT A.STUDENT_ID, C.SUBJ_ID, A.SURNAME, C.SUBJ_NAME FROM STUDENT A, EXAM_MARKS B, SUBJECT С WHERE A.STUDENT_ID = B.STUDENT_ID AND B.SUBJ_ID = C.SUBJ_ID; Теперь все предметы студента или всех студентов для каждого предмета можно выбрать с помощью простого запроса. Например, чтобы увидеть все предметы, сданные студентом Ивановым, подается запрос: SELECT SUBJ _ NAME FROMSTUD _ SUBJ WHERE SURNAME = 'Иванов'; При создании представлений могут также использоваться и подзапросы, включая связанные подзапросы. Предположим, предусматривается премия для тех студентов, которые имеют самый высокий балл на любую заданную дату. Получить такую информацию можно с помощью представления: CREATE VIEW ELITE _ STUD AS SELECT B.EXAM_DATE, A.STUDENT_ID, A. SURNAME FROM STUDENT A, EXAM_MARKS В WHERE ASTUDENT_ID = B.STUDENT_ID AND B.MARK = (SELECT MAX(MARK) FROM EXAM_MARKS С WHERE C.EXAM_DATE = B.EXAM_DATE); Если, с другой стороны, премия будет назначаться только студенту, который имел самый высокий балл и не меньше 10-ти раз, то необходимо использовать другое представление, основанное на первом: CREATE VIEW BONUS AS SELECT DISTINCTSTUDENT_ID, SURNAME FROM ELITE_STUDA WHERE 10 < = (SELECT COUNT (*) FROM ELITE_STUD ВWHERE ASTUDENT_ID =B.STUDENT_ID); Извлечение из этой таблицы записей о студентах, которые будут получать премию, выполняется простым запросом: SELECT * FROM BONUS; Имеются некоторые виды запросов, не допустимые в определениях представлений. Одиночное представление должно основываться на одиночном запросе, поэтому UNION и UNION ALL в представлениях не разрешаются. Предложение ORDER BY также никогда не используется в определении представлений. Представление является реляционной таблицей-отношением, поэтому его строки по определению являются неупорядоченными. Синтаксис удаления представления из базы данных подобен синтаксису удаления базовых таблиц: DROP VIEW < имя представления > Как уже говорилось, использование команд модификации языка SQL - INSERT (ВСТАВИТЬ), UPDATE (ЗАМЕНИТЬ), и DELETE (УДАЛИТЬ)- применительно для представлений имеет ряд особенностей. В дополнение к аспектам, рассмотренным выше, следует отметить, что не все представления могут модифицироваться. Если команды модификации могут выполняться в представлении, то представление является обновляемым (модифицируемым); в противном случае оно предназначено только для чтения при запросе. Каким образом можно определить, является ли представление модифицируемым? Критерии обновляемости представления можно сформулировать следующим образом. • Представление строится на основе одной и только одной базовой таблицы. • Представление должно содержать первичный ключ базовой таблицы. • Представление недолжно иметь никаких полей, которые представляют собой агрегирующие функции. • Представление недолжно содержать DISTINCT в своем определении. • Представление недолжно использовать GROUP BY или HAVING в своем определении. • Представление недолжно использовать подзапросы. • Представление может быть использовано в другом представлении, но это представление должно быть также модифицируемыми. • Представление не должно использовать в качестве полей вывода константы или выражения значений. Суть этих ограничений в том, что обновляемые представления фактически подобны окнам в базовых таблицах. Они показывают информацию из базовой таблицы, ограничивая определенные ее строки (использованием соответствующих предикатов) или специально именованные столбцы (с исключениями). Но при этом представления выводят значения без их обработки с использованием агрегирующих функций и группировки. Они также не сравнивают строки таблиц друг с другом (как это имеет место в объединениях и подзапросах, или при использовании DISTINCT). Различия между модифицируемыми(обновляемыми) представлениями и представлениями "только для чтения' не случайны. Обновляемые представления в основном используются аналогично базовым таблицам. Пользователи могут даже не знать, является ли запрашиваемый ими объект базовой таблицей или представлением. Это превосходный механизм защиты для скрытия частей таблицы, которые являются конфиденциальными или не предназначены данному пользователю. Не модифицируемые представления, с другой стороны, позволяют более рационально получать и переформатировать данные. С их помощью формируются библиотеки сложных запросов, которые могут затем использоваться в запросах для получения информации самостоятельно (например, в объединениях). Эти представления могут также иметь значение при решении задач защиты и безопасности данных. Например, можно предоставить некоторым пользователям возможность получения агрегатных данных (таких, как усредненное значение оценки студента), не показывая конкретных значений оценок и, тем более, не позволяя их модифицировать. Относительно использования предложения WITH CHECK OPTION следует отметить, что в стандарте SQL это предложение не предусматривает каскадного изменения, то есть оно применяется только в представлениях, в которых оно определено, но не распространяется на другие представления, основанные на этом представлении. Например, в предыдущем примере CREATE VIEW HIGH_RATING AS SELECT UNIV_ID, RATING FROM UNIVERSITY WHERE RAT ING >=400 WITH CHECK OPTION; Попытка вставить или обновить значения поля RATING, отличные от 400, будет отвергнута, поскольку присутствует указание WITH CHECK OPTION. Однако, если создается второе представление (с тем же содержанием), основанное на первом: CREATEVIEW MYRATING AS SELECT * FROM HIGH_RATING; То ввод в поле RATING с помощью нижеприведенного запроса значений, отличающихся от 400, уже не будет отвергнуто как ошибочное. То есть следующий запрос UPDATE MYRATING SET RATING = 200 WHERE UNIV_ID = 18; не будет отвергнут как не корректный, и, после его выполнения, строки с обновленными данными исчезнут из как из представления MYRATING, так и из представления HIGH _ RATING . Предложение WITH CHECK OPTION просто гарантирует, что любое обновление в представлении осуществляется в соответствии со значениями, указанными именно для этого представления. Обновление других представлений, базирующихся на первом текущем, при этом допустимым, если эти представления не защищены предложениями WITH CHECK OPTION, заданными именно для них. Предложения WITH CHECK OPTION проверяют предикаты только того представления, в котором они содержатся. Приэто мне является выходом из положения и создание представления MYRATING с помощью запроса CREATE VIEW MYRATING AS SELECT * FROM HIGH_RATING WITH CHECK OPTION; 5. Оборудование Персональный компьютер с установленной операционной системой WindowsXP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБДPostgreSQL. 6. Задание на работу |