Будь умным!


У вас вопросы?
У нас ответы:) SamZan.ru

Создание сложных запросов

Работа добавлена на сайт samzan.ru: 2016-03-13


4 Лабораторная работа № 4 Создание сложных запросов                 4.1 Объединение таблиц в запросах       До сих пор все наши запросы обращались к одной таблице. Однако SQL позволяет в одном запросе обращаться к нескольким таблицам. Именно это свойство и сделало язык  SQL столь популярным. В таких запросах вместе с именами каждого из стобцов указываются имена соответствующих таблиц. Это называется подробным определением столбца в запросе. Подробные определения требуются только для тех столбцов, которые присутствуют в нескольких из указанных в запросе таблиц. Но в операторе обычно указываются подробные определения для всех столбцов:   SELECT  Teacher.Teach_FAM, Chair.Chair_NAME     FROM Teacher, Chair         WHERE Teacher.Chair_ID = Chair.Chair_ID     

Этот оператор возвращает фамилии преподавателей и названия кафедр, на которых они работают. Номер преподавателя присутствует в обеих таблицах, имена двух столбцов таблиц рассматриваются как эквивалентные (что задается равенством).

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

SELECT  Teacher.*,  Chair.Chair_NAME

FROM Teacher, Chair

WHERE Teacher.Chair_ID = Chair.Chair_ID

           Использование псевдонимов для имен таблиц. Псевдонимы назначаются таблицам с целью сокращения объема печатания, а также для использования при рекурсивном связывании таблиц. Пример:     SELECT  A.Teach_ID, B.Chair_NAME

FROM Teacher  A,

    Chair     B

WHERE A.Chair_ID = B.Chair_ID       

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

 SELECT  A.Stud_FAM, B.Stud_FAM        FROM Students A,             Students B           WHERE A.Stud_Star = B.Stud_ID       

         Связывание по нескольким ключам. Предположим вы хотите увидеть,  кто из  студентов, по каким предметам получили оценки:                 SELECT Students. Stud_FAM, Subject.Subj_NAME, Progress.OCENKA   FROM Students, Subject, Progress        WHERE Students.Stud_ID = Progress.Stud_ID    AND                    Subject.Subj_ID = Progress.Subj_ID

 SQL Server проверяет каждую комбинацию строк двух таблиц и проверяет их на условие, указанное в предложении WHERE. Если эта комбинация удовлетворяет ему, то она выводится.       Предположим, что мы хотим найти всех студентов, сдавших хорошо преподавателю Серикову. Для этого требуется связать четыре таблицы:                            SELECT Students.Stud_FAM, 'по предмету ', Subject.Subj_NAME,               'получили оценку ', Progress.OCENKA,                               'у преподавателя ', Teacher.Teach_FAM  FROM Students, Subject, Progress, Teacher        WHERE Students.Stud_ID = Progress. Stud_ID          AND

             Teacher.Teach_ID = Progress.Teach_ID AND 

                          Subject.Subj_ID = Progress.Subj_ID      AND 

                          Progress.Ocenka <>2                              AND

                              Teacher.Teach_FAM =  'Сериков '

 4.2 Использование подзапросов

         Запросы могут управлять другими запросами. Это делается путем помещения запроса внутрь условия другого запроса и использования вывода внутреннего запроса в верном или неверном условии.

Обычно внутренний запрос генерирует значение, которое проверяется в условии внешнего запроса, определяющего верно оно или нет. Например, мы знаем фамилию студента - Сидоров, но не знаем его кода (Stud_ID), и хотим получить все его оценки из таблицы Progress:      

SELECT *

FROM Progress

 WHERE  Stud_ID  = (

 SELECT  Stud_ID

 FROM Students

 WHERE Stud_FAM = 'Сидоров'  )     

       Чтобы выполнить внешний (основной запрос), сначала выполняется внутренний запрос (подзапрос) внутри предложения WHERE. При выполнении подзапроса просматривается таблица Students, в которой выбираются строки, где поле Stud_FAM равно 'Сидоров', затем извлекается значение поля Stud_ID. Пусть единственной строкой будет Stud_ID = 30104. Далее полученное значение помещается в условие основного запроса, вместо самого подзапроса, так что условие примет вид:

 WHERE   Stud_ID  = 301004       

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

Если Вы хотите использовать подзапрос, который возвращает несколько строк, то необходимо использовать оператор IN. Например, если в базе несколько студентов с фамилией Сидоров и все имеют оценки:

SELECT *

FROM Progress

 WHERE  Stud_ID IN (

 SELECT  Stud_ID

 FROM    Students

 WHERE Stud_FAM = 'Сидоров'  )               Найдем все оценки для предмета Информатика:

SELECT *

FROM Progress

 WHERE    Subj_ID   IN (

 SELECT   Subj_ID

 FROM      Subject

 WHERE    Subj_NAME = 'Информатика'  )

Этот результат можно получить и с помощью объединения:

  SELECT Progress.*          FROM   Subject, Progress               WHERE Subject.Subj_ID = Progress.Subj_ID AND            Subject.Subj_NAME = 'Информатика'              Хотя этот запрос эквивалентен предыдущему, SQL Server должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их соответствие условию.

      Все вышеприведенные подзапросы объединяет то, что все они выбирают один единственный столбец. Это обязательно, поскольку их результат сравнивается с одиночным значением. Команды типа SELECT * запрещены в подзапросах.

Подзапросы можно также использовать в предложении HAVING. Эти подзапросы могут использовать собственные предложения GROUP BY или HAVING. Следующий запрос является тому примером:

SELECT Ocenka,  COUNT (DISTINCT Stud_ID  )

FROM Progress

GROUP BY Ocenka

   HAVING Ocenka > (

           SELECT AVG(ocenka )

            FROM Progress

               WHERE Pr_DATE >04/06/02)       

       Эта команда подсчитывает студентов с оценкой выше средней, сдавших экзамен после 04.06.02.

Связанные подзапросы. При использовании подзапросов можно обратиться во вложенном подзапросе к таблицам из внешнего подзапроса. Например, как найти всех  студентов, сдавших экзамены 3 июня : 

        SELECT *

FROM Students C

WHERE '2004-06-03' IN (

   SELECT Pr_DATE

   FROM Progress O

   WHERE O.Stud_ID   = C.Stud_ID  )   

Можно было бы использовать объединение следующего вида:

SELECT C.*

FROM Students C, Progress O

WHERE C.Stud_ID   = O.Stud_ID  AND

O. Pr_DATE = '2004.06.03'

Связанные запросы можно использовать для сравнения таблицы с собой. Например, можно найти всех студентов с баллом выше среднего:

SELECT *

FROM Progress O

WHERE ocenka > (

  SELECT AVG(ocenka )

  FROM Progress O1

  WHERE O1.Stud_ID  = O.Stud_ID )

Использование оператора EXISTS. Оператор EXISTS берет подзапрос, как аргумент, и оценивает его как верный, если подзапрос возвращает какие-либо записи и неверный, если тот не делает этого. Вот как выполняется проверка наличия студентов со стипендией = 1200:

SELECT Stud_ID , Stud_FAM, Stud_STIP

FROM Students

WHERE EXISTS (

  SELECT *

  FROM Students

  WHERE Stud_STIP = 2000  )

Внутренний подзапрос выбирает все данные для всех студентов, у которых стипендия = 1200. Оператор EXISTS во внешнем условии отмечает, что подзапрос вернул некоторые данные, следовательно, условие верное. Подзапрос будет выполнен один раз для всего внешнего запроса и имеет одно значение во всех случаях. Поэтому EXISTS, когда используется таким образом, делает условие верным или неверным для всех строк сразу.

Использование операторов ANY, ALL. Рассмотрим новый способ поиска студента, сдавшего экзамен:          SELECT *            FROM Students

WHERE Stud_ID = ANY (

     SELECT Stud_ID  

        FROM Progress    )

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

В приведенном выше запросе можно было бы использовать оператор IN. Однако оператор ANY можно применять не только с оператором равенства.

Оператор ALL считает условие верным, если каждое значение, выбранное подзапросом, удовлетворяет условию внешнего запроса. Выберем тех студентов, у которых стипендия выше 1500:

SELECT *

FROM Students

WHERE Stud_STIP > ALL(

     SELECT Stud_stip

        FROM Students

        WHERE Stud_STIP = 1500  )

 Использование команды UNION. Команда UNION объединяет вывод нескольких запросов с исключением повторяющихся строк. Например, приведенный ниже запрос выводит всех студентов и преподавателей, чьи фамилии размещены между буквами К и С:         SELECT Stud_FAM           FROM Students           WHERE  Stud_FAM   BETWEEN  'К'  AND 'С'       UNION           SELECT Teach_FAM          FROM Teacher           WHERE Teach_FAM  BETWEEN 'К' AND 'С'           Для применения команды UNION существует 2 правила:

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

типы данных должны быть совместимы

          UNION автоматически исключает дубликаты строк из вывода.   Если вы хотите, чтобы все строки из запросов попали в результат, используйте UNION ALL.:

 

 SELECT  Stud_FAM

FROM Students

 UNION ALL

SELECT Teach_FAM   FROM Teacher

            4.3 Создание и использование представлений

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

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

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

Представление создается с помощью команды CREATE VIEW. Представление можно создать на базе данных одной или нескольких таблиц, а также других представлений. Например, создадим представление StudAddress из таблицы Students:

CREATE VIEW    StudAddress        AS

 SELECT   Stud_ID, Stud_FAM, Stud_IMA,Stud_ADDRESS

        FROM  Students

Для удаления представлений из базы данных используется команда DROP VIEW. У этой команды есть две опцииRESTRICT и CASCADE. Если используется RESTRICT и в условиях имеются зависимые представления, то оператор DROP VIEW возвращает ошибку. При использовании опции CASCADE и наличии зависимых представлений оператор DROP VIEW завершается успешно и все зависимые представления тоже удаляются.

4.4 Задание на лабораторную работу     Вначале выполните все примеры, приведенные в п.п.4.1-4.3.

Получите следующую информацию из базы данных:            4.4.1 Список преподавателей, ведущих дисциплины «Информатика» и  «Физика».       4.4.2 Список студентов, имеющих неудовлетворительные оценки.    4.4.3 Список студентов, не сдавших экзамен по высшей математике.  4.4.4 Список преподавателей кафедры «Информатика».               4.4.5 Список кафедр с указанием фамилий заведующих кафедр.   4.4.6 Список названий групп с указанием фамилий старост этих групп.  4.4.7 Списки студентов каждой группы с их оценками по всем предметам.               4.4.8 В каких группах проводятся занятия по предмету «Информатика»? 4.4.9 Какие виды занятий по Информатике проводятся в первой группе? 4.4.10 Сколько часов занятий по каждому предмету в каждой группе    проводится в семестре?      4.4.11 Создайте представление STUDOСENKA из таблиц Students, Subject

и Progress, в котором отражается список студентов с их оценками по предметам.              4.5 Контрольные вопросы         4.5.1 Можно ли иметь несколько ключевых слов AND в выражении, заданном ключевым словом  WHERE?       4.5.2 Что такое рекурсивное связывание?      4.5.3 При связывании таблиц должны ли они связываться в том же  порядке, в  каком они указаны в выражении ключевого слова FROM?   4.5.4 При использовании в операторе запроса таблицы-связки,  обязательно ли выбирать в запросе ее столбцы?      4.5.5 Можно ли связывать в запросе не один, а несколько столбцов  таблиц?           4.5.6 Какая часть оператора SQL задает условия связывания таблиц?  4.5.7 Что будет, если в запросе указать выборку из двух таблиц, но не связать их?        4.5.8 Для чего используются псевдонимы таблиц?                    4.5.9 Что такое подзапрос? Как он работает?     4.5.10 Что случится, если таблица, на основе которой строится  представление, будет удалена?     

4.5.11 Как представление можно использовать для защиты данных?

СПИСОК ЛИТЕРАТУРЫ         1. Хансен Г., Хансен Д. Базы данных: разработка и управление. –   М.: ЗАО «Издательство БИНОМ», 1999.       2. Тихомиров Ю. Microsoft SQL Server 7.0 – СПб.: Издательство «Питер», 1999.                3. Плю Р., Стефенс Р.,  Райан К. Освой самостоятельно SQL за 24 часа. – М.:  Издательский дом «Вильямс», 2000.       4. Кандзюба С.П., Громов В.Н. Delphi 6/7. Базы данных и приложения. – СПб: ООО «ДиаСофт», 2002.        5. Бобровский С. Delphi 5: учебный курс. – СПб: Издательство «Питер», 2000.  




1. Микроклимат производственного помещения Вентиляция производственных помещений составить конспект [ДОП
2. Менеджмент Уважаемый работодатель В целях повышения эффективности работы по адаптации молодых спе
3. МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ ПО РЕФЕРАТИВНОЙ РАБОТЕ СТУДЕНТОВ Рефераты в учебном заведении являются одним и
4. Миграционные процессы и политика Германии
5. Алкалоиды маклейи
6. 10 R 76.2 Антифосфоліпідний синдром АФС ~ хронічний вазооклюзивний процес з розвитком поліорганної іше
7.  Назвіть захворювання із доброякісним перебігом- Рак Саркома Лейкоз Ліпома Лімфогранулем
8. ЗАЦВЯРДЖАЮ Загадчык кафедры беларускай літаратуры
9. Пепсіко на світовому ринку безалкогольних газованих напоїв можна пояснити- унікальними споживчими
10. С.Медведев СССР В.
11. третьего мира Брежневское руководство поставило три приоритетные задачи в области внешней политики- у
12. сбычести мечт Я с раннего детства задавала себе эти вопросы как любая романтическая девочка верящая в ч
13. Sports in Britain
14. на тему- Виды юридических лиц Франции МОСКВА 2000 г.
15. Гражданская война и интервенция
16. Тема- Поражающее действие современного оружия и характеристика санитарных потерь Актуальность темы
17. Производство ферросплавов
18. Проблемы гражданского права Понятие гражданского права как отрасли права
19. а он жил в VIV веках до н
20. е тем которые оказывают социальные услуги населению и работают в тесном сотрудничестве с государственным