бд. метод указ Проектирование БД. Методическое указания для выполнение лабораторных работ по дисциплине

• Название: Методическое указания для выполнение лабораторных работ по дисциплине
• Дата: 16.03.2022
• Размер: 0.74 Mb.
• Имя файла: метод указ Проектирование БД.docx
• Тип: Задача; #399109
• страница: 6 из 9

SELECT Students.Stud_FAM, Subject.Subj_NAME , Progress.OCENKA

       FROM Students

JOIN Progress ON Stud.Stud_ID = Progress.Stud_ID

JOIN Subject ON  Subject.subj_id = Progress.subj_id

 

3.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 Progress.OCENKA

                     FROM Progress

                     JOIN subject ON Progress.subj_id= subject.subj_id

                          WHERE    progress.Subj_ID   IN (

                                   SELECT   Subj_ID

                                   FROM     Subject

         WHERE    Subj_NAME = 'ТЭЦ')

 

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

SELECT Progress.OCENKA

                          FROM subject

JOIN progress ON subject.subj_id=progress.subj_id

WHERE 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 >01/09/05)                                

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

 

3.3  Связанные подзапросы

 

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

SELECT *

          FROM Stud C

             WHERE '2008-03-01' IN (

                   SELECT Pr_DATE

                             FROM Progress O

                             WHERE O.Stud_ID = C.Stud_ID  )

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

SELECT Stud_ID

                   FROM Progress O

                   WHERE ocenka > (

                            SELECT AVG(ocenka )

                                      FROM Progress O1

                                         WHERE O1.Stud_ID  = O.Stud_ID )

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

SELECT Grup_ID

   FROM [Gruppa] G

                   WHERE EXISTS (

                    SELECT *

                        FROM Students

                            WHERE Stud.Grup_ID = G.Grup_ID)

 

Внутренний подзапрос выбирает всех студентов которые учатся в группе. Оператор 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                                                        

 

Контрольные вопросы

                                                       

1.  Можно ли иметь несколько ключевых слов AND в выражении, заданном ключевым словом  WHERE?                                                        

2.  Что такое рекурсивное связывание?                                                 

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

4. При использовании в операторе запроса таблицы-связки, обязательно ли выбирать в запросе ее столбцы?                                      

5. Можно ли связывать в запросе не один, а несколько столбцов        таблиц?                       

6.  Какая часть оператора SQL задает условия связывания таблиц?              7.  Что будет, если в запросе указать выборку из двух таблиц, но не связать их?

8.  Для чего используются псевдонимы таблиц?                                             9 . Что такое подзапрос? Как он работает?

        

Лабораторная работа № 6. Представления

 

1  Цель работы: научить студентов создавать представления к базе данных скриптом и в графической среде  СУБД MS SQL Server 2008.

 

2   Задание на лабораторную работу

 

Выполните упражнения, приведенные в п.3.1-3.5.

 

Создайте следующие представления:       

a)     отображающее список групп и старост этих групп;

b)    отображающее список студентов не сдавших экзамен по предмету;

c)     которое отображало бы содержимое таблицы «Успеваемость» в удобном для  пользователя виде (без идентификаторов); 

d)    отображающее список студентов живущих в общежитии;

e)     отображающее форматированный список студентов в одном поле (формат: Сидоров А.С. (группа));

f)      отображающее таблицу «учебный процесс» в удобочитаемом виде (без идентификаторов).           

 

3  Методические указания к выполнению лабораторной работы

                  

3.1 Общие сведения о представлениях

 

Представление (view) является своего рода виртуальной таблицей, обеспечивающей доступ пользователей к некоторому подмножеству данных, составленному из одной или более таблиц. Для пользователя представление как обычная таблица, но при этом само по себе оно не содержит данных.  Физически представление реализовано в виде SQL-запроса, на основе которого производится выборка данных из одной или более таблиц или представлений. Таблицы, на основе которых создается представление, называются базовыми (base table). В самом простом случае можно создать представление на базе одной таблицы, которое будет содержать точно такой же набор данных, как и исходная таблица. На практике такие представления редко используются. Более сложные представления могут объединять столбцы из нескольких таблиц. При этом, задав условие для выборки, можно сделать доступным из представления только ограниченное множество строк из этих таблиц. Когда из представления исключается один или более столбцов базовой таблицы, говорят, что на таблицу наложен вертикальный фильтр. Если в определении представления установлено одно или более условий для выборки строк, говорят, что на таблицу наложен горизонтальный фильтр. 

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

Представления создаются с помощью оператора CREATE VIEW. Аналогично оператору CREATE TABLE, операторCREATE VIEW можно использовать только для создания представления, которого до сих пор не существовало.

 

CREATE VIEW имя_представления 

AS

   SELECT список_выбора

              [ FROM таблицы_источники ]

       [ WHERE условие_отбора]

 

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

 

3.2  Представление на базе одной таблицы:        

 

Создадим представление StudAddress из таблицы Students:

CREATE VIEW    StudAddress

         AS

  SELECT   Stud_ID, Stud_FAM, Stud_IMA,Stud_ADDRESS

                   FROM  Students

 

Создадим представление StudStip из таблицы Students:

CREATE VIEW    StudStip

         AS

   SELECT  distinct stud_fam,stud_stip

     FROM stud

 

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

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

 

3.3  Удаление представления

        

Представления удаляются  с помощью оператора  DROP VIEW             <имя_представления >:

DROP VIEW StudAddress

 

3.4  Представление на базе нескольких таблиц

 

Чаще всего представления используются для упрощения работы с SQL, и нередко это относится к объединениям.

Создадим представление PrepodChair на базе двух таблиц teacher и chair, которое бы содержало фамилии преподавателей, работающих на кафедрах:

CREATE VIEW    PrepodChair

         AS

SELECT teacher.teach_fam, chair.chair_name

    FROM teacher, chair

        WHERE teacher.chair_id=chair.chair_id

 

Создадим представление Studoсenka из таблиц Students, Subject  и Progress, в котором отражается список студентов с их оценками по предметам:

CREATE VIEW Studocenka

AS

SELECT stud.stud_fam, subject.subj_name, progress.ocenka

FROM stud,subject,progress

WHERE (progress.stud_id=stud.stud_id) and    (progress.subj_id=subject.subj_id)

 

Представления чрезвычайно полезны для упрощения иcпользования вычисляемых полей. Создадим представление Otli4n, в котором отражается количество отличников по предметам в группе:

 

CREATE VIEW Otli4n

   AS

               SELECT g.grup_name as [Группа], s.subj_name as [Предмет],

count(p.ocenka) as[Колличество отличников]

                    FROM progress p join gruppa g on g.grup_id = p.grup_id

JOIN subject s on s.subj_id = p.subj_id where p.ocenka in (8,9)

                      GROUP BY g.grup_name, s.subj_name

Таким образом, представления могут значительно упростить сложные операторы SQL. Используя представления, можно один раз записать код SQL и затем повторно использовать его, если возникает такая необходимость.

 

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

 

Можно использовать представления для переформатирования данных.  Создадим представление отражающее преподавателей, работающих на кафедрах в определенном формате: