аз№4. пз№4. Методические указания по Лабораторной работе Создание запросов и фильтров

• Название: Методические указания по Лабораторной работе Создание запросов и фильтров
• Дата: 10.06.2022
• Размер: 2.91 Mb.
• Имя файла: пз№4.docx
• Тип: Методические указания; #583502

Методические указания по

Лабораторной работе 4.

«Создание запросов и фильтров»

Цель: научиться создавать запросы и фильтры

Задание:

1.Повторить ход работы в практических сведениях;

2.Оформить отчет;

3.Ответить на контрольные вопросы.

Требования к отчету:

Отчет необходимо оформить в Microsoft Word .

Шрифт Times New Roman, 14пт .

Ориентация по ширине .

Интервал

.

Отступ справа 1,5 .
Перейдём к созданию статических запросов. В обозревателе объектов «Microsoft SQL Server 2008» все запросы БД находятся в папке «Views» (Рис 4.1).

Рис.4.1

Создадим запрос «Запрос Студенты+Специальности», связывающий таблицы «Студенты» и «Специальности» по полю связи «Код специальности». Для создания нового запроса необходимо в обозревателе объектов в БД «Students» щёлкнуть ПКМ по папке «Views», затем в появившемся меню выбрать пункт «New View». Появиться окно «Add Table» (Добавить таблицу), предназначенное для выбора таблиц и запросов, участвующих в новом запросе (Рис.4.2).

Рис.4.2

Добавим в новый запрос таблицы «Студенты» и «Специальности». Для этого в окне «Add Table» выделите таблицу «Студенты» и нажмите кнопку «Add» (Добавить). Аналогично добавьте таблицу «Специальности». После добавления таблиц участвующих в запросе закройте окно «Add Table» нажав кнопку «Close» (Закрыть). Появится окно конструктора запросов (Рис.4.3).

Рис.4.3

Замечание: Окно конструктора запросов состоит из следующих панелей:

1. 
   Схема данных – отображает поля таблиц и запросов, участвующих в запросе, позволяет выбирать отображаемые поля, позволяет устанавливать связи между участниками запроса по специальным полям связи. Эта панель включается и выключается следующей кнопкой на панели инструментов ;
   
2. 
   Таблица отображаемых полей – показывает отображаемые поля (столбец «Column»), позволяет задавать им псевдонимы (столбец «Alias»), позволяет устанавливать тип сортировки записей по одному или нескольким полям (столбец «Sort Type»), позволяет задавать порядок сортировки (столбец «Sort Order»), позволяет задавать условия отбора записей в фильтрах (столбцы «Filter» и «Or…»). Также эта таблица позволяет менять порядок отображения полей в запросе. Эта панель включается и выключается следующей кнопкой
   

на панели инструментов ;

3. 
   Код SQL – код создаваемого запроса на языке T-SQL. Эта панель включается и выключается следующей кнопкой на панели инструментов ;
   
4. 
   Результат – показывает результат запроса после его выполнения. Эта панель
   

включается и выключается следующей кнопкой на панели инструментов .

Замечание: Если необходимо снова отобразить окно «Add Table» для добавления новых таблиц или запросов, то для этого на панели инструментов «Microsoft SQL Server 2008» нужно нажать кнопку .

Замечание: Если необходимо удалить таблицу или запрос из схемы данных, то для этого нужно щёлкнуть ПКМ и в появившемся меню выбрать пункт «Remove» (Удалить).

Теперь перейдём к связыванию таблиц «Студенты» и «Специальности» по полям связи «Код специальности». Чтобы создать связь необходимо в схеме данных перетащить мышью поле «Код специальности» таблицы «Специальности» на такое же поле таблицы «Студенты». Связь отобразиться в виде ломаной линии соединяющей эти два поля связи

(Рис.4.3).

Замечание: Если необходимо удалить связь, то для этого необходимо щёлкнуть по ней ПКМ и в появившемся меню выбрать пункт «Remove».

Замечание: После связывания таблиц (а также при любых изменениях в запросе) в области кода T-SQL будет отображаться T-SQL код редактируемого запроса.

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

Замечание: Если необходимо сделать поле невидимым при выполнении запроса, то нужно убрать галочку, расположенную слева от имени поля на схеме данных. Для этого просто щёлкните мышью по галочке.

Замечание: Если необходимо отобразить все поля таблицы, то необходимо установить галочку слева от пункта «* (All Columns)» (Все поля), принадлежащего соответствующей таблице на схеме данных.

Определите отображаемые поля нашего запроса, как это показано на рисунке 4.3 (Отображаются все поля кроме полей с кодами, то есть полей связи).

На этом настройку нового запроса можно считать законченной. Перед сохранением запроса проверим его работоспособность, выполнив его. Для запуска запроса на панели инструментов нажмите кнопку . Либо щёлкните ПКМ в любом месте окна конструктора запросов и в появившемся меню выберите пункт «Execute SQL» (Выполнить SQL). Результат выполнения запроса появиться в виде таблицы в области результата

(Рис.4.3).

Замечание: Если после выполнения запроса результат не появился, а появилось сообщение об ошибке, то в этом случае проверьте, правильно ли создана связь. Ломаная линия связи должна соединять поля «Код специальности» в обеих таблицах. Если линия связи соединяет другие поля, то её необходимо удалить и создать заново, как это описано выше.

Если запрос выполняется правильно, то необходимо сохранить. Для сохранения запроса закройте окно конструктора запросов, щёлкнув мышью по кнопке закрытия , расположенной в верхнем правом углу окна конструктора (над схемой данных). Появиться окно с вопросом о сохранении запроса (Рис.4.4).

Рис.4.4

В данном окне необходимо наддать кнопку «Yes» (Да). Появится окно «Choose Name» (Выберите имя) (Рис.4.5).

Рис.4.5

В данном окне зададим имя нового запроса «Запрос Студенты+Специальности» и нажмём кнопку «Ok». Запрос появиться в папке «Views» БД «Students» в обозревателе объектов (Рис..4.6).

Рис.4.6

Проверим работоспособность созданного запроса вне конструктора запросов. Запустим вновь созданный запрос «Запрос Студенты+Специальности» без использования конструктора запросов. Для выполнения уже сохранённого запроса необходимо щёлкнуть ПКМ по запросу и в появившемся меню выбрать пункт «Select top 1000 rows» (Отобразить первые 1000 записей). Выполните эту операцию для запроса «Запрос

Студенты+Специальности». Результат представлен на рисунке 4.6.

Перейдём к созданию запроса «Запрос Студенты+Оценки». В обозревателе объектов в БД «Students» щелкните ПКМ по папке «Views», затем в появившемся меню выберите пункт «New View». Появиться окно «Add Table» (Рис.4.2).

В запросе «Запрос Студенты+Оценки» мы связываем таблицы «Студенты» и «Оценки» по полям связи «Код студента». Следовательно, в окне «Add Table» в новый запрос добавляем таблицы «Студенты» и «Оценки». Более того, в данном запросе таблица «Оценки» связывается с таблицей «Предметы» не по одному полю, а по трём полям. То есть поля «Код предмета 1», «Код предмета 2» и «Код предмета 3» таблицы «Оценки» связаны с полем «Код предмета» таблицы «Предметы». По этому добавим в запрос три экземпляра таблицы «Предметы» (по одному экземпляру для каждого поля связи таблицы оценки). В итоге в запросе должны участвовать таблицы «Студенты», «Оценки» и три экземпляра таблицы «Предметы» (в запросе они будут называться «Предметы», «Предметы_1» и «Предметы_2»). После добавления таблиц закройте окно «Add Table», появится окно конструктора запросов.

В окне конструктора запросов установите связи между таблицами и определите отображаемые поля, как показано на рисунке 4.7.

Рис.4.7

Теперь поменяем порядок отображаемых полей в запросе, для этого в таблице отображаемых полей необходимо перетащить поля мышью вверх или вниз за заголовок строки таблицы (столбец перед столбцом «Column»). Расположите отображаемые поля в в таблице отображаемых полей как показано на рисунке 4.8.

Рис.4.8

Задайте псевдонимы для каждого из полей, просто записав псевдонимы в столбце «Alias» таблицы отображаемых полей, как на рисунке 4.8.

Проверьте работоспособность нового запроса, выполнив его. Обратите внимание на то, что реальные названия полей были заменены их псевдонимами. Закройте окно конструктора запросов. В появившемся окне «Choose Name» задайте имя нового запроса «Запрос Студенты+Оценки» (Рис.4.9).

Рис.4.9

Проверьте работоспособность нового запроса вне конструктора. Для этого запустите запрос. Результат выполнения запроса «Запрос Студенты+Оценки» должен выглядеть как на рисунке 4.10.

Рис.4.10

На этом мы заканчиваем рассмотрение обычных запросов и переходим к созданию фильтров.

На основе запроса «Запрос Студенты+Специальности» создадим фильтры, отображающие студентов отдельных специальностей. Создайте новый запрос. Так как он будет основан на запросе «Запрос Студенты+Специальности», то в окне «Add Table» перейдите на вкладку «Views» и добавьте в новый запрос «Запрос

Студенты+Специальности» (Рис.4.11). Затем закройте окно «Add Table».

Рис.4.11

В появившемся окне конструктора запросов определите в качестве отображаемых полей все поля запроса «Запрос Студенты+Специальности» (Рис.4.12).

Рис.4.12

Замечание: Для отображения всех полей запроса, в данном случае, мы не можем использовать пункт «* (All Columns)» (Все поля). Так как в этом случае мы не можем устанавливать критерий отбора записей в фильтре, а также невозможно установить сортировку записей.

Теперь установим критерий отбора записей в фильтре. Пусть наш фильтр отображает только студентов имеющих специальность «ММ». Для определения условия отбора записей в таблице отображаемых полей в строке, соответствующей полю, на которое накладывается условие, в столбце «Filter», необходимо задать условие. В нашем случае условие накладывается на поле «Наименование специальности». Следовательно, в строке «Наименование специальности», в столбце «Filter» нужно задать следующее условие отбора «=’ММ’» (Рис.4.12).

В заключение настроим сортировку записей в фильтре. Пусть при выполнении фильтра сначала происходит сортировка записей по возрастанию по полю «Очная форма обучения», а затем по убыванию по полю «Курс». Для установки сортировки записей по возрастанию, в таблице определяемых полей, в строке для поля «Очная форма обучения», в столбце «Sort Type» (Тип сортировки), задайте «Ascending» (По возрастанию), а в строке для поля «Курс» - задайте «Descending» (По убыванию). Для определения порядка сортировки для поля «Очная форма обучения» в столбце «Sort Order» (Порядок сортировки) поставьте 1, а для поля «Курс» поставьте 2 (Рис.4.12). То есть, при выполнении запроса записи сначала сортируются по полю «Очная форма обучения», а затем по полю «Курс».

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

После установки сортировки записей в фильтре проверим его работоспособность, выполнив его. Результат выполнения фильтра должен выглядеть как на рисунке 4.12. Закройте окно конструктора запросов. В качестве имени нового фильтра в окне «Choose Name» задайте «Фильтр ММ» (Рис.4.13) и нажмите кнопку «Ok».

Рис.4.13

Фильтр «Фильтр ММ» появиться в обозревателе объектов. Выполните созданный фильтр вне окна конструктора запросов. Результат должен быть таким же как на рисунке 4.14.

Рис.4.14

Самостоятельно создайте фильтры для отображения других специальностей. Данные фильтры создаются аналогично фильтру «Фильтр ММ» (смотри выше). Единственным отличием является условие отбора, накладываемое на поле «Наименование специальности», оно должно быть не «=’ММ’», а «=’ПИ’», «=’СТ’», «=’МО’» или «=’БУ’». При сохранении фильтров задаём их имена соответственно их условиям отбора, то есть «Фильтр ПИ», «Фильтр СТ», «Фильтр МО» или «Фильтр БУ». Проверьте созданные фильтры на работоспособность.

Теперь на основе запроса «Запрос Студенты+Специальности» создадим фильтры, отображающие студентов имеющих отдельных родителей. Для начала создадим фильтр для студентов, из родителей только «Отец». Создайте новый запрос и добавьте в него запрос «Запрос Студенты+Специальности» (Рис.4.11). После закрытия окна «Add Table» сделайте отображаемыми все поля запроса (Рис.4.15).

Рис.4.15

В таблице отображаемых полей в строке для поля «Родители», в столбце «Filter», задайте условие отбора равное «=’Отец’». Проверьте работу фильтра, выполнив его. В результате выполнения фильтра окно конструктора запросов должно выглядеть как на рисунке 4.15.

Закройте окно конструктора запросов. В окне «Choose Name» задайте имя нового фильтра как «Фильтр Отец» (Рис.4.16).

Рис.4.16

Выполните фильтр «Фильтр Отец» вне конструктора запросов. Результат должен быть аналогичен рисунку 4.17.

Рис.4.17

Создайте фильтры для отображения студентов с другими вариантами родителей. Данные фильтры создаются аналогично фильтру «Фильтр Отец» (смотри выше). Единственным отличием является условие отбора, накладываемое на поле «Родители», оно должно быть не «=’Отец’», а «=’Мать’», «=’Отец, Мать’» или «=’Нет’». При сохранении фильтров задаём их имена соответственно их условиям отбора, то есть «Фильтр Мать», «Фильтр Отец и Мать» или «Фильтр Нет родителей». Проверьте созданные фильтры на работоспособность.

Наконец создадим фильтры для отображения студентов очной и заочной формы обучения. Начнём с очной формы обучения. Создайте новый запрос и добавьте в него запрос «Запрос Студенты+Специальности». Как и ранее сделайте все поля запроса отображаемыми (Рис.4.18).

Рис.4.18

В таблице отображаемых полей в столбце «Filter», в строке для поля «Очная форма обучения» установите условие отбора равное «=1»

Замечание: Поле «Очная форма обучения» является логическим полем, оно может принимать значения либо «True» (Истина), либо «False» (Ложь). В качестве синонимов этих значений в «Microsoft SQL Server 2008» можно использовать 1 и 0 соответственно.

Установите сортировку по возрастанию, по полю курс, задав в строке для этого поля, в столбце «Sort Type», значение «Ascending».

Проверьте работу фильтра, выполнив его. После выполнения фильтра окно конструктора запросов должно выглядеть точно также как на рисунке 4.18.

Закройте окно конструктора запросов. Сохраните фильтр под именем «Фильтр очная форма обучения» (Рис.4.19).

Рис.4.19

После появления фильтра «Фильтр очная форма обучения» в обозревателе объектов выполните фильтр вне окна конструктора запросов. Результат выполнения фильтра «Фильтр очная форма обучения» представлен на рисунке 4.20.

Рис.4.20

Самостоятельно создайте фильтр для отображения студентов заочной формы обучения. Данный фильтр создаётся точно также как и фильтр «Фильтр очная форма обучения». Единственным отличием является условие отбора, накладываемое на поле «Очная форма обучения», оно должно быть не «=1», а «=0». При сохранении фильтра задайте его имя как «Фильтр заочная форма обучения». Проверьте созданный фильтр на работоспособность.

В итоге, после создания всех запросов и фильтров окно обозревателя объектов должно выглядеть следующим образом (Рис.4.21):

Рис.4.21

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

1.Способы создания запросов и фильтров?

2.Как добавить новый запрос в таблицу?

3.Как выполнить сохраненный запрос?

4. Как задать имя запросу или фильтру?

5.Как сделать поле таблицы ключевым?