Л.3. Конструирование запросов и работа с ними 1. Характеристика запросов 2. Технология создания запросов на выборку 3. Методика создания запросов других видов 4. Групповые операции в запросе 5. Общие сведения о языке структурированных запросов (SQL) –Литература: [1], стр. 526 – 543 [2], лаб.раб. 5

1. Характеристика запросов Задание на поиск набора величин в БД в соответствии с определенной совокупностью критериев поиска называется запросом (Query). Запрос - основное средство обработки данных в СУБД Access (поиск, отбор, изменение, вычисления и т.д.). Запрос - это формализованное задание СУБД на обработку данных в БД. Средствами запроса можно выполнить следующие действия: - выбрать записи, отвечающие условиям отбора; - включать в результирующую таблицу дополнительные поля; - произвести вычисления; - сгруппировать записи с одинаковыми значениями; - создать новую таблицу; - удалить или добавить записи и т.д. Запросы могут быть: запрос по образцу - QBE - Query By Example; структурированный запрос - SQL - Structured Queries Language. Результат выполнения запроса представляется в виде таблицы

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

2. Технология создания запросов на выборку Для создания запроса необходимо определить: - к каким связанным таблицам обращен запрос; - данные каких полей выводить на экран; - по какому условию производить отбор; - по какому полю выполнить сортировку и в какой последовательности. Результат выполнения запроса представляется в виде таблицы с указанными полями. Для обращения к Коструктору необходимо сделать активным объект Запросы в окне объектов базы данных (м.б. вкладка Запросы) и выполнить команду Создать.

Использование параметров в условиях запроса (параметрический запрос) Параметр - это значение, которое задается непосредственно в ходе выполнения запроса Параметры представляют собой последовательность символов, заключенных в квадратные скобки и записанных в поле условий [Введите наименование поставщика] В одном запросе может быть несколько параметров (выполнение – сверху вниз и слева направо) Параметр может быть использован не только для записи условий, но и для изменений значений в таблицах [Введите новую цену товара]

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

Работа с Конструктором запросов Ожидаемый результат запроса Фамилия ДолжностьОклад Возраст

Расчет возраста В поле «Фамилия» сформировать условие отбора [Введите фамилию работника] В свободном поле с помощью «Построителя выражений» создать формулу Возраст: Int((Date()-[Сотрудники]![Дата Родж])/365) или Возраст: DateDiff("yyyy";[Сотрудники]![Дата Рожд];Date())

3. Методика создания запросов других видов 1. Создать запрос на выборку данных, которые будут использованы в создаваемом запросе 2. Выполнить запрос в Конструкторе ( ) для контроля правильности) 3. Преобразовать запрос на выборку в запрос требуемого вида (меню Запрос или соответствующая кнопка панели инструментов) 4. Выполнить запрос (кнопка в режиме Конструктора)

Запрос на создание таблицы Постановка задачи: Создать таблицу, содержащую список сотрудников старше 50 лет с указанием должности и возраста Выполнение: –создать и проверить соответствующий запрос на выборку –изменить тип запроса в запрос на создание таблицы –выполнить запрос

Запрос на обновление записей Постановка задачи: Увеличить должностные оклады отдельным категориям сотрудников Выполнение: –создать и проверить запрос На выборку по отдельным категориям –изменить тип запроса На обновление –в появившейся строке Обновление бланка запроса ввести необходимое выражение –выполнить запрос

Запрос на каскадное удаление записей В свойствах связи - целостность данных и каскадное удаление!!! В схему данных конструктора запросов - только главную таблицу Постановка задачи: Сократить отдельные должности из штата организации Выполнение: –создать и проверить соответствующий запрос На выборку –изменить тип запроса в запрос на удаление –выполнить запрос

Запрос на добавление записей Поля запроса должны соответствовать полям таблицы, которая дополняется записями. Первоначально создается запрос на выборку. Затем кнопкой выбора вида запроса он преобразуется в запрос на добавление. В Д/О - ввести имя таблицы. При необходимости можно добавить поля и критерии отбора. Выполнить запрос.

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

4. Групповые операции в запросе Объединяет записи в группы с одним значением,а для каждой группы использует статистическую функцию Последовательность конструирования: - включить в запрос на выборку поля для группировки и поля, по которым выполняется статистическая обработка - выполнить команду Вид-Групповая операция, или щелчок на кнопке Σ, или из КЗМ - Σ - в бланке запроса в строку «Гр.опер.» нужных полей записать «Группировка» - в каждом вычисляемом поле в строке «Группировка» может быть установлена своя групповая операция (Count, Max, Min, Sum и т.д.)

Создание простого запроса Мастером Шаг 1 - выбор базовой таблицы (запроса) и полей из списка Доступные поля Шаг 2 - выбор типа запроса из альтернатив: подробный с выводом каждой записи или итоговый Шаг 3 - определение имени запроса и выбор дальнейших действий из альтернатив: открыть запрос с результатами или изменить структуру запроса с помощью Конструктора

5. Общие сведения о языке структурированных запросов (SQL) Рассмотренный выше режим построения запросов по образцу не позволяет создавать все типы запросов. В ряде случаев целесообразно использовать язык SQL (Structure Query Language). Некоторые синтаксические правила: –в бланке SQL можно записать и сохранить только один запрос; –запись SQL-инструкций можно производить в несколько строк; –разделитель между словами – пробел; –элементы списка разделяются запятыми; –признак завершения SQL-инструкции – точка с запятой; –ключевые и зарезервированные слова набираются прописными буквами; –переменные, задаваемые пользователем, набираются курсивом; –при описании синтаксиса в угловые скобки заключают элемент синтаксиса (пользователь угловые скобки не вводит); –при описании синтаксиса в прямые скобки [ ] заключают необязательные элементы, перечисляемые через вертикальные линии | (пользователь прямые скобки и вертикальные линии не вводит);.

Области применения структурированных запросов В СУБД MS Access есть несколько типов запросов, которые невозможно создать Мастером или Конструктором запросов. К ним относятся, например: –запрос на объединение данных нескольких запросов и таблиц; – управляющие запросы, которые позволяют создавать, изменять и удалять таблицы, создавать индексы; – запросы на создание подчиненных запросов (можно создавать в режиме Конструктора запросов, но в строке «Условия отбора» использовать инструкции SQL); –запросы серверу и др.

Основные элементы языка SQL Директива SELECT является центральной при формировании запроса на выборку. Она может содержать большое количество различных параметров. Общий синтаксис инструкции SELECT следующий: SELECT – выбирать, отбирать FROM – из [WHERE ] – где, куда, условия [GROUP BY ] – группировать по… [HAVING ] – наличие [ORDER BY ] – упорядочить Рассмотрим назначение основных параметров. - указывает, какие строки выводятся в запросе: ALL – все, DISTINGTROW – уникальные записи, DISTINGT – уникальные значения; - содержит список выводимых полей (* - все поля); - определяются имена таблиц, участвующих в запросе, с указанием связей между ними (JOIN – соединять, объединять)

Основные элементы языка SQL (продолжение) Параметр WHERE определяет условия для отбора данных. –В условиях можно применять операторы сравнения (, =, =, <>) и логические операторы OR (или), AND (и), NOT (не), ALL (все), ANY (какой- нибудь), BETWEEN (между), EXISTS (существующие), LIKE (подобный). Параметр ORDER BY обеспечивает сортировку данных при выполнении запроса. –При этом необходимо указать имя таблицы и имя поля для сортировки, а также – порядок сортировки: ASC – по возрастанию или DESC – по убыванию. Запрос, созданный с помощью бланка запросов, можно просмотреть в режиме SQL и при необходимости изменить, выполнив команду меню Вид/Режим SQL. Для изучения языка SQL полезно любой запрос, созданный с помощью Конструктора запросов, рассмотреть в режиме SQL, а затем постепенно его усложнять.