Если вам нужно выбрать определенные данные из одного или нескольких источников, можно воспользоваться запросом на выборку. Запрос на выборку позволяет получить только необходимые сведения, а также помогает объединять информацию из нескольких источников. В качестве источников данных для запросов на выборку можно использовать таблицы и другие такие же запросы. В этом разделе вкратце рассматриваются запросы на выборку и предлагаются пошаговые инструкции по их созданию с помощью Мастера запросов либо в Конструктор.
Если вы хотите узнать больше о принципах работы запросов на примере базы данных Northwind, ознакомьтесь со статьей Общие сведения о запросах .
Когда возникает потребность в каких-то данных, редко бывает необходимо все содержимое одной таблицы. Например, если вам нужна информация из таблицы контактов, как правило, речь идет о конкретной записи или только о номере телефона. Иногда бывает необходимо объединить данные сразу из нескольких таблиц, например совместить информацию о клиентах со сведениями о заказчиках. Для выбора необходимых данных используются запросы на выборку.
Запрос на выборку - это объект базы данных, который показывает информацию в режим таблицы. Запрос не хранит данные, но содержит данные, которые хранятся в таблицах. В запросе можно отобразить данные из одной или нескольких таблиц, из других запросов или из двух сочетаний.
Запрос позволяет выполнять перечисленные ниже задачи.
Просматривать значения только из полей, которые вас интересуют. При открытии таблицы отображаются все поля. Вы можете сохранить запрос, который выдает лишь некоторые из них.
Примечание: Запрос только возвращает данные, но не сохраняет их. При сохранении запроса вы не сохраняете копию соответствующих данных.
Объединять данные из нескольких источников. В таблице обычно можно увидеть только те сведения, которые в ней хранятся. Запрос позволяет выбрать поля из разных источников и указать, как именно нужно объединить информацию.
Использовать выражения в качестве полей. Например, в роли поля может выступить функция, возвращающая дату, а с помощью функции форматирования можно управлять форматом значений из полей в результатах запроса.
Просматривать записи, которые отвечают указанным вами условиям. При открытии таблицы отображаются все записи. Вы можете сохранить запрос, который выдает лишь некоторые из них.
Вы можете создать запрос на выборку с помощью мастера или конструктора запросов. Некоторые элементы недоступны в мастере, однако их можно добавить позже из конструктора. Хотя это разные способы, основные этапы аналогичны.
Выберите таблицы или запросы, которые хотите использовать в качестве источников данных.
Укажите поля из источников данных, которые хотите включить в результаты.
Также можно задать условия, которые ограничивают набор возвращаемых запросов записей.
Создав запрос на выборку, запустите его, чтобы посмотреть результаты. Чтобы выполнить запрос на выборку, откройте его в режиме таблицы. Сохранив запрос, вы сможете использовать его позже (например, в качестве источника данных для формы, отчета или другого запроса).
Мастер позволяет автоматически создать запрос на выборку. При использовании мастера вы не полностью контролируете все детали процесса, однако таким способом запрос обычно создается быстрее. Кроме того, мастер иногда обнаруживает в запросе простые ошибки и предлагает выбрать другое действие.
Если вы используете поля из источников данных, которые не связаны между собой, мастер запросов предлагает создать между ними отношения. Он откроет окно отношений , однако если вы внесете какие-то изменения, то вам потребуется перезапустить мастер. Таким образом, перед запуском мастера имеет смысл сразу создать все отношения, которые потребуются вашему запросу.
Дополнительную информацию о создании отношений между таблицами можно найти в статье Руководство по связям между таблицами .
На вкладке Создание в группе Запросы нажмите кнопку Мастер запросов .
В диалоговом окне Новый запрос выберите пункт Простой запрос и нажмите кнопку ОК .
Теперь добавьте поля. Вы можете добавить до 255 полей из 32 таблиц или запросов.
Для каждого поля выполните два указанных ниже действия.
Если вы не добавили ни одного числового поля (поля, содержащего числовые данные), перейдите к действию 9. При добавлении числового поля вам потребуется выбрать, что именно вернет запрос: подробности или итоговые данные.
Выполните одно из указанных ниже действий.
В диалоговом окне Итоги укажите необходимые поля и типы итоговых данных. В списке будут доступны только числовые поля.
Для каждого числового поля выберите одну из перечисленных ниже функций.
Sum - запрос вернет сумму всех значений, указанных в поле.
Avg - запрос вернет среднее значение поля.
Min - запрос вернет минимальное значение, указанное в поле.
Max - запрос вернет максимальное значение, указанное в поле.
Если вы хотите, чтобы в результатах запроса отобразилось число записей в источнике данных, установите соответствующий флажок Подсчет числа записей в (название источника данных) .
Нажмите ОК , чтобы закрыть диалоговое окно Итоги .
Если вы не добавили в запрос ни одного поля даты и времени, перейдите к действию 9. Если вы добавили в запрос поля даты и времени, мастер запросов предложит вам выбрать способ группировки значений даты. Предположим, вы добавили в запрос числовое поле ("Цена") и поле даты и времени ("Время_транзакции"), а затем в диалоговом окне Итоги указали, что хотите отобразить среднее значение по числовому полю "Цена". Поскольку вы добавили поле даты и времени, вы можете подсчитать итоговые величины для каждого уникального значения даты и времени, например для каждого месяца, квартала или года.
Выберите период, который хотите использовать для группировки значений даты и времени, а затем нажмите кнопку Далее .
На последней странице мастера задайте название запроса, укажите, хотите ли вы открыть или изменить его, и нажмите кнопку Готово .
Если вы решили открыть запрос, он отобразит выбранные данные в режиме таблицы. Если вы решили изменить запрос, он откроется в режиме конструктора.
В режиме конструктора можно вручную создать запрос на выборку. В этом режиме вы полнее контролируете процесс создания запроса, однако здесь легче допустить ошибку и необходимо больше времени, чем в мастере.
В режиме конструктора источники данных и поля добавляются на разных этапах, так как для добавления источников используется диалоговое окно Добавление таблицы . Однако вы всегда можете добавить дополнительные источники позже.
Если между добавляемыми источниками данных уже заданы отношения, они автоматически добавляются в запрос в качестве соединений. Соединения определяют, как именно следует объединять данные из связанных источников. Access также автоматически создает соединение между двумя таблицами, если они содержат поля с совместимыми типами данных и одно из них - первичный ключ.
Вы можете настроить соединения, добавленные приложением Access. Access выбирает тип создаваемого соединения на основе отношения, которое ему соответствует. Если Access создает соединение, но для него не определено отношение, Access добавляет внутреннее соединение.
В некоторых случаях вы можете присоединиться к двум копиям одной и той же таблицы или запроса, которые называются самосоединение, и будут объединять записи из той же таблицы, если в Объединенных полях есть совпадающие значения. Например, предположим, что у вас есть таблица Employees, в которой поле "подчиняется" для записи каждого сотрудника отображает его идентификатор своего руководителя вместо имени. Вы можете использовать самосоединение для отображения имени руководителя в записи каждого сотрудника.
При добавлении источника данных во второй раз Access присвоит имени второго экземпляра окончание "_1". Например, при повторном добавлении таблицы "Сотрудники" ее второй экземпляр будет называться "Сотрудники_1".
Если источники данных, добавленные в запрос, уже имеют отношения, Access автоматически создает внутреннее соединение для каждой связи. Если используется целостность данных, Access также отображает "1" над линией соединения, чтобы показать, какая таблица находится на стороне "один" элемента отношение "один-ко-многим" и символ бесконечности (∞ ), чтобы показать, какая таблица находится на стороне "многие".
Если вы добавили в запрос другие запросы и не создали между ними отношения, Access не создает автоматических соединений ни между ними, ни между запросами и таблицами, которые не связаны между собой. Если Access не создает соединения при добавлении источников данных, как правило, их следует создать вручную. Источники данных, которые не соединены с другими источниками, могут привести к проблемам в результатах запроса.
Кроме того, можно сменить тип соединения с внутреннего на внешнее соединение, чтобы запрос включал больше записей.
После создания соединений можно добавить выводимые поля: они будут содержать данные, которые должны отображаться в результатах.
Вы можете легко добавить поле из любого источника данных, добавленного в действии 1.
Для этого перетащите поле из источника в верхней области окна конструктора запросов вниз в строку Поле бланка запроса (в нижней части окна конструктора).
При добавлении поля таким образом Access автоматически заполняет строку Таблица в таблице конструктора в соответствии с источником данных поля.
Совет: Чтобы быстро добавить все поля в строку "Поле" бланка запроса, дважды щелкните имя таблицы или запроса в верхней области, чтобы выделить все поля в нем, а затем перетащите их все сразу вниз на бланк.
Вы можете использовать выражение в качестве выводимого поля для вычислений или создания результатов запроса с помощью функции. В выражениях могут использоваться данные из любых источников запроса, а также функции, например Format или InStr, константы и арифметические операторы.
Это необязательно.
С помощью условий можно ограничить количество записей, которые возвращает запрос, выбирая только те из них, значения полей в которых отвечают заданным критериям.
Условия можно задать для нескольких полей. В этом случае для включения записи в результаты должны выполняться все условия в соответствующей строке Условия отбора либо Или .
Вы можете добавить в запрос поле, но не включать его значения в выводимые результаты. Это позволяет использовать содержимое поля для ограничения результатов, но при этом не отображать его.
Добавьте поле в таблицу запроса.
Снимите для него флажок в строке Показывать .
Задайте условия, как для выводимого поля.
Этот этап является необязательным.
Вы также можете вычислить итоговые значения для числовых данных. Например, может потребоваться просмотреть среднюю цену или общие продажи.
Для расчета итоговых значений в запросе используется строка Итого . По умолчанию строка Итого не отображается в режиме конструктора.
Чтобы увидеть результаты запроса, на вкладке "Конструктор" нажмите кнопку Выполнить . Access отобразит результаты запроса в режиме таблицы.
Чтобы вернуться в режим конструктора и внести в запрос изменения, щелкните Главная > Вид > Конструктор .
Настраивайте поля, выражения или условия и повторно выполняйте запрос, пока он не будет возвращать нужные данные.
Чтобы сделать результаты запроса доступными в браузере, нужно добавить представление запроса на экране выбора таблиц. Чтобы добавить новое представление в заголовок на экране выбора таблиц, выполните указанные ниже действия.
Сегодня мы подробно поговорим о запросах в Access.
Запросы, как вы уже знаете, нужны для работы с данными, находящимися в таблицах.
Чтобы создать запрос…
1) …в окне базы данных открываете Запросы
2) …и создаете запрос с помощью конструктора.
Пожалуйста, НЕ пользуйтесь для создания запросов Мастером , потому что он позволяет делать только самые простые запросы, а переделывать их потом в более продвинутые даже сложнее, чем создать запрос в конструкторе «с нуля».
Когда связываются несколько таблиц, то могут возникать пустые строки.
Почему это происходит?
Дело в том, что у нас в таблице tbPerson наряду с владельцами собак записаны и судьи (Петровская, Елец, Терещук). Судьи не имеют права привозить своих собак на выставку, поэтому в строках с их фамилиями пустые ячейки с кличками собак.
Есть два способа удалить пустые строки.
1. Поставить условие на значение клички собаки Is not Null
, т.е. НЕ ПУСТА.
2. Или поменять тип связи между таблицами в области таблиц: нужно на линии связи, дающей неточный результат, вызвать контекстное меню, и поменять Параметры объединения .
Вопрос вам: какие настройки нужно поменять в диалоговом окне параметров объединения?
До сих пор мы только отбирали записи по различным условиям. Но Access позволяет не только просматривать данные, записанные в таблицах, но и производить ВЫЧИСЛЕНИЯ: по дате рождения определять возраст; из имени, фамилии и отчества делать фамилию с инициалами; по цене единицы товара и его количества определять общую стоимость покупки; по дате выдачи книги в библиотеке определять величину штрафа при задолженности и многое другое. Для вычислений используются встроенные функции (похожие на те, что есть в Excel).
Простейшая операция – это сложение строк
. Напишем в ячейке выражение для вывода такой фразы: владелец
из города город
.
Для этого запишем в верхней строке нового столбца области условий: + “ из города ” + .
Имена полей записываются в квадратных скобках, фрагменты строк – в кавычках, между ними стоят знаки сложения.
Выражения для вычислений записываются в самой верхней строке (Поле ) области условий. До сих пор мы писали условия в нижних строках (Условия отбора ).
Чтобы вам не запутаться: в верхней строке мы пишем, ЧТО выводить на экран, а затем (в нижней) – при КАКОМ УСЛОВИИ .
Задание : Напишите выражение для вывода в одной ячейке фамилии владельца и в скобках города, в котором он живет. Вот так: Иванов (Москва) . Город и фамилия должны подставляться из таблицы.
Чтобы удобнее было редактировать выражения, существует специальный редактор – «Построитель выражений». Он выглядит так:
И вызывается с помощью контекстного меню: нужно поставить курсор на ту ячейку, куда вы будете записывать выражение:
В построителе выражений вы можете выбирать из библиотеки функции:
и данные из таблиц (можно пользоваться ТОЛЬКО теми таблицами, которые используются в данном запросе и отображены в области данных):
Когда вы двойным щелчком мыши выбираете из списка имя поля или функцию, Access часто вставляет еще слово «выражение», показывая, что НА ЭТО МЕСТО можно вставить другие функции и имена полей. Не забывайте удалять лишние слова «выражение»!
Мы познакомимся с текстовыми и временными функциями, а также с условным оператором Iif(condition; if-true; if-false).
Текстовые
функции позволяют преобразовывать строковые переменные:
Left(«Иванов»; 2) = «Ив» оставляет n левых символов
LCase(«Иванов») = иванов делает все буквы строчными
InStr(1; «Иванов»; «но») = 4 находит подстроку (третий аргумент) в строке (второй аргумент), и равняется позиции (от начала) подстроки в строке
Len («Иванов») = 6 выводит количество символов в строке
StrComp(«Иванов»; «Петров») = -1 сравнивает две строки: если они равны, то выдает 0
и другие…
Временные
позволяют работать с временными переменными:
Month(#12.04.2007#) = 4
Year(#12.04.2007#) = 2007
Day(#12.04.2007#) = 12.
Now() = 28.04.2008 14:15:42 (текущие дата и время)
Date() = 28.04.2008 (сегодняшняя дата)
DateDiff(«d»; #12.04.2007#; #28.04.2007#) = 16 находит разницу между двумя датами («d» – в днях, ww- в неделях, m – в месяцах, yyyy – в годах и др.)
и другие…
Логические
обрабатывают условные выражения:
Iif(<=1; «щенок»; «взрослый») аналог функции ЕСЛИ из Ecxel.
и другие…
Задание
: напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
Задание
Задание
Дополнение
: Есть два способа вычислить возраст собаки: один более точный, другой менее:
1) из текущего года вычесть год рождения собаки;
2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня. Примените в одном из заданий один способ, в другом – другой.
Когда вы запускаете запрос с параметром , он, в отличие от обычного запроса на выборку, не выполняется сразу, а сначала в диалоговом окне просит вас уточнить некоторые условия отбора. Например, мы хотим получить полную информацию о собаке с определенным турнирным номером.
Устроен этот запрос так:
На том месте, где обычно стоит условие отбора, теперь записан вопрос (в квадратных скобках), который будет задан пользователю. А ответ пользователя, как вы уже догадались, будет подставлен в эту ячейку в качестве условия отбора.
Задание : создайте запрос, который будет выводить всех собак по фамилии владельца, являющейся свободным параметром.
С помощью построителей выражений мы можем производить операции над одной строкой: складывать значения в ячейках, преобразовывать данные.
Но что, если нам надо обработать сразу несколько строк: посчитать сумму баллов, найти количество строк с одинаковым значением заданного поля?
Для этого используется группировка (она очень похожа на подведение итогов в Excel).
Посчитаем, сколько собак каждой породы приехали на выставку. Для этого перебросим в область условий всего два поля: кличка и порода – и вызовем дополнительную строку групповые операции (через контекстное меню на области условий):
Теперь сгруппируем собак по породе и посчитаем количество разных кличек в каждой группе:
Подведем итоги выставки и посчитаем средний балл за экстерьер, средний балл за дрессировку и их сумму.
Добавляем в область таблиц таблицу с оценками (tbMarks). Группируем оценки по турнирному номеру собаки и среди групповых операций выбираем среднее значение Avg (от англ. average – среднее).
Выполните запрос и в режиме просмотра обратите внимание, что столбцы с групповыми операциями имеют двойное название (операция + имя поля). Нам это пригодится при подсчете суммы баллов.
Можно также округлить значения до одного знака после запятой: Round(+;1)
Задание : выясните, какая собака вызвала наиболее противоречивые мнения судей. Для этого надо из максимального балла вычесть минимальный.
На первом уроке мы уже говорили о том, что запросы позволяют не только просматривать данные из таблиц, но и редактировать записи: добавлять новые, удалять, изменять. Тип запроса можно изменить с помощью списка запросов на панели инструментов.
Оператору базы данных накануне выставки пришла новая информация:
1) собака Дези заболела и не сможет принять участия в выставке;
2) по ошибке Гардиен, являющийся на самом деле английским сеттером, был указан как ирландский сеттер;
3) владелец Мигунова подала заявку на участие в выставке еще одной своей собаки (кличка: Гарри, порода: сеттер-гордон, пол: м, дата рождения: 15.09.07).
Приступим к редактированию базы данных.
1) Удалить запись с Дези.
Создаем запрос quDelDog. Тип запроса – на удаление. Со сменой типа запроса несколько изменяется и область условий. Появилась новая ячейка Удаление
. Под ней вы указываете условие, по которому нужно отобрать записи, подлежащие удалению. Даже если вы указываете условие отбора для одного поля, удалена будет ВСЯ запись.
После того, как вы нажали на «восклицательный знак», на экране появится сообщение о том, что запись удалена. Теперь откройте таблицу tbDog и убедитесь, что Дези в ней нет.
2) У Гардиена изменить ирландский сеттер на английский сеттер.
Создаем запрос quUpdateDog. Тип запроса – на обновление. Находим Гардиена и обновляем у него породу.
Откройте таблицу tbDog и убедитесь, что у Гардиена порода – английский сеттер.
3) Добавить запись с Гарри.
Создаем запрос quAddDog. Тип запроса – на добавление. У запросов на добавление есть одна особенность: в области таблиц отображены НЕ те таблицы, КУДА вы добавляете запись, а ОТКУДА вы берете данные (если это требуется). Целевую таблицу (в которую добавляются записи) вы указываете в диалоговом окне, которое появляется сразу, как только вы установите тип запроса (на добавление):
Поскольку мы не берем данные из других таблиц, а создаем новую запись, то область таблиц должна быть ПУСТОЙ! (там не должно быть ни одной таблицы). В области условий в строке Поле вы пишете, ЧТО добавить (новое значение для каждого поля), а в строке Добавление КУДА (имена полей):
Откройте таблицу tbDog и убедитесь, что Гарри в ней появился!
Когда вы нажимаете на «восклицательный знак», запрос выполняется. Так это представляется на взгляд новичка.
Профессионалы знают, что на самом деле в этот момент выполняется инструкция на специальном языке запросов SQL. Дело в том, что Access – не единственная система управления базами данных (СУБД). Может быть, вы слышали о таких СУБД в Интернете как MySQL, FreeBSD??? Access просто предлагает очень удобный интерфейс для работы с БД, а в других СУ нет никакой кнопочки с восклицательным знаком. Зато там обязательно есть специальное окошко, в котором можно писать SQL-инструкции.
Access тоже позволяет редактировать запросы в режиме SQL-инструкций:
Правила языка SQL не так сложны. Вы можете сами в этом убедиться! Сделайте простой запрос на выборку (например, выведите кличку, породу и дату рождения собаки по кличке Гарри). Теперь откройте запрос на поиск Гарри в режиме SQL!
Инструкция устроена очень просто:
SELECT поле1, поле2,…
FROM таблица1, таблица2,…
WHERE условие1, условие2,…
Откройте теперь запросы на обновление, изменение, удаление (quDelDog, quUpdateDog, quAddDog) в режиме SQL и выпишите на листе бумаги шаблоны их SQL-инструкций (как это только что было сделано для запроса на выборку).
Запросы с параметром, запросы на группировку, запросы с вычислениями представляют собой такие же SQL-запросы, но только с несколько более сложными условиями отбора. Язык SQL – незаменимый инструмент для тех, кто работает с базами данных!
Вот те темы, которые мы рассмотрели:
- простые и составные условия отбора
- оператор LIKE
- выборка из нескольких таблиц
- построитель выражений
- запросы с параметром
- запросы на группировку
- запросы на обновление, добавление, удаление
- язык запросов SQL.
Их немало! Но, коль скоро вы их освоили, вы сможете отыскать в базе данных любую информацию.
Проверьте свои знания! Выполните в вашей базе данных следующие запросы (или переименуйте уже выполненные так, чтобы их имена совпадали с именами задач):
quSelectDog : Найдите всех шар-пеев и сеттеров-гордонов из регионов (НЕ из Москвы); используйте оператор «не равно».
quLike : Найдите всех абонентов МТС (те, у кого номер мобильного телефона начинается с 8(916)…).
quEvalText
: Напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
quEvalDate
: напишите выражение, которое вычисляет, сколько собаке лет по дате ее рождения.
quEvalIif
: напишите выражение, которое определяет по возрасту, в какой возрастной категории выступает собака: «щенок» - до года; «юниор» - от года до двух лет; «сеньор» - старше двух лет.
Дополнение
: Примените в одном задании один способ вычислить возраст собаки, в другом – другой: 1) из текущего года вычесть год рождения собаки; 2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня.
quParametre : Создайте запрос, который будет выводить всех собак по фамилии владельца, являющейся свободным параметром.
quGroup : Выясните, какая собака вызвала наиболее противоречивые мнения судей.
Незадолго до выставки владелец Гороховец уехал на ПМЖ (постоянное место жительства) в Германию и передал всех собак своему другу Карпову Михаилу Игоревичу. Требуется:
1) quAddOwner
: добавить запись о новом владельце;
2) quUpdateOwner
: изменить у собак Гороховца ID владельца на ID Карпова;
3) quDelOwner
: удалить Гороховца из базы данных.
Жду ваших БД с выполненными заданиями, а также шаблоны SQL-инструкций для запросов на обновление, изменение, удаление.
Губская Ольга Васильевна
Тема урока : Создание запросов в СУБД Access
11 класс
Цель урока : Повторить основные понятия темы Базы данных, Системы управления базами данных; познакомить учащихся с различными способами создания запросов в БД; научить отбирать записи по нескольким условиям, использовать выражения в запросах
Тип урока, продолжительность урока.
Комбинированный урок, 45 минут.
Средства обучения .
Технические средства: - мультимедийный проектор.
Программные средства обучения: - программа MS Access 2007, MS PowerPoint 2007.
Информационные средства: - созданная учителем база данных «Школьная библиотека», презентация «Создание запросов в СУБД Access 2007», задания для компьютерного практикума и самостоятельной работы; тестовые задания.
План урока:
Организационный момент (1 минута )
Постановка темы и целей урока (1 минута)
Фронтальный опрос (7 минут)
Ознакомление с новым материалом (15 минут)
Первичное осмысление и применение изученного.
- Компьютерный практикум
(10 минут)
- Самостоятельная работа, тестирование по теме (8 минут
)
Подведение итогов урока, выставление оценок (2 минуты)
Домашнее задание (1 минута )
Ход урока
1. Организационный момент
2. Постановка темы и целей урока
Слайд 1
Мы продолжаем изучать базы данных, на прошлых уроках вы познакомились с понятием базы данных, с понятием СУБД, с различными классификациями баз данных, с полями и записями, с типами данных и с операциями над информацией в базах данных. Тема сегодняшнего урока: «Создание запросов в СУБД Access 2007»
Слайд 2
Итак, на этом уроке мы:
1 . Повторим основные понятия темы Базы данных, Системы управления базами данных;
2. Познакомимся с различными способами создания запросов в БД;
3. Научимся отбирать записи по нескольким условиям, использовать выражения в запросах.
3. Фронтальный опрос
Слайд 3
Чтобы повторить основные понятия темы, я предлагаю вам «прогуляться» по полю понятий. Выбираете номер овала, появляется термин и вы формулируете соответствующее правило. (Приложение 1)
4. Ознакомление с новым материалом
Вы научились создавать таблицы, формы. На очереди запросы. Так что же такое запрос?
Слайд 4
Запрос – это требование на отбор записей, хранящихся в таблицах или на выполнение определённых действий с ними.
Запрос это временная таблица. Это значит, что данные в запросах не хранятся постоянно, а только временно вызываются из таблицы по заданному шаблону, в момент активизации запроса. Поэтому в базе данных постоянно сохраняется только шаблон вызова данных, а не сама информация. Следовательно, запрос не занимает много места в БД.
Запросы позволяют :
выбирать нужные поля из разных таблиц БД;
фильтровать и сортировать данные;
производить расчёты;
группировать записи, рассчитывая различные итоговые значения;
вносить изменения сразу в несколько записей;
создавать таблицы как результат запроса.
Слайд 5
Какими бывают запросы?
Запрос на выборку. Осуществляет загрузку и отображение записей в режиме таблицы.
Запрос на изменение. Выполняет операции с записями, удовлетворяющими заданным критериям.
а) запрос на удаление (удаляет заданные записи из таблицы);
б) запрос на обновление (изменяет соответствующие записи таблицы);
в) запрос на добавление (добавляет в конец таблицы новые записи соответствующие условиям отбора).
Запрос с параметрами. П осле запуска на выполнение просит ввести значения определенных параметров.
Перекрёстный запрос. Позволяет выполнить подсчёты по данным одного поля таблицы и группировать результаты.
Слайд 6
Способы создания запросов
SQL;
Конструктор;
Мастер.
Существует универсальный язык запросов, на котором формулируются запросы во многих СУБД. Он называется SQL (Structured Query Language [ strʌk.tʃəd ˈkwɪə.ri ˈlæŋɡwɪdʒ ]) – структурированный язык запросов. В большинстве современных СУБД существуют вспомогательные средства для составления запросов, позволяющие обходиться без изучения SQL . В MS Access это конструктор запросов .
Простой запрос на выборку или один из 3-х специальных запросов можно создать с помощью Мастера , но конструктор является более универсальным способом создания запросов.
Слайд 7
Итак, запрос на выборку - это производная таблица , которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки), и формируется на основе фактических данных системы.
Например, есть база данных Школьная библиотека, в которой создана таблица Книги. Запрос на выборку записей по полям Автор, Название, Год издания будет выглядеть как на слайде.
Рассмотрим процесс создания этого запроса с помощью Мастера.
Слайд 8
1 шаг – на вкладке Создание выбираем Мастер запросов.
2 шаг – в диалоге Новый запрос выбираем Простой запрос à ОК
Слайд 9
3 шаг – в диалоге Создание простых запросов выбираем из списка таблицу
Слайд 10
4 шаг – перемещаем из списка Доступные поля в список Выбранные поля нужные имена полей à нажимаем кнопку Далее >
Слайд 11
5 шаг – выбираем один из вариантов отображения записей с данными: подробный (без выполнения операции сведения) или итоговый с использованием сведения (нахождение суммы, среднего значения, минимума, максимума для определенных записей) à нажимаем кнопку Далее >
Слайд 12
6 шаг – вводим имя запроса à нажимаем кнопку Готово.
Слайд 13
Теперь рассмотрим пример создания запроса при помощи Конструктора.
1 шаг – на вкладке Создание выбираем Конструктор запросов.
2 шаг – в диалоге Добавление таблицы выбираем таблицы, поля из которых будут использоваться в запросе à Добавить à Закрыть
Появляется бланк запроса, в верхней части которого отображаются макеты выбранных таблиц. А нижняя часть содержит такие строки:
Поле , в которой будут отображаться имена выбранных полей;
Имя таблицы , в которой будет отображаться имена таблиц, в состав которых входят соответствующие поля;
Сортировка , в которой при необходимости можно выбрать вид сортировки;
Вывод на экран , содержащая флажок, установленная метка которого свидетельствует о том, что данное будет отображаться в запросе;
Условие отбора , которая может содержать условие – выражение для выборки данных из соответствующего поля;
или – для записи еще одного выражения, которое является частью составного условия.
Слайд 14
3 шаг – выбираем нужные поля.
Чтобы выбрать поле, можно выполнить двойной щелчок по этому полю, или перетащить его из макета таблицы в строку Поле.
Слайд 15
4 шаг – вводим условие отбора и нажимаем кнопку Выполнить .
Слайд 16
5 шаг – закроем вкладку запроса à в окне сохранения структуры запроса выберем Да à введем имя запроса à ОК
Слайд 17
Логическое выражение, представляет собой операцию отношения, при записи которого используются символы >,<,=, ≠, ≤, ≥. Условие отбора одного поля может быть составным, при этом два логических выражения соединяются знаком логической операции.
Рассмотрим несколько примеров
Слайд 18
Из таблицы Книги выбрать рассказы А.П.Чехова. Условий отбора будет два: по полю Автор и по Полю Наименование. Если условия отбора записаны в одной строке, то они соединяются операцией «и».
Слайд 19
Запрос на выборку: Книги, изданные в 1987 году, или в которых больше 300 страниц. Условия отбора по полям Год издания и Количество страниц записываются на разных строках, поэтому они соединяются операцией «или».
Слайд 20
Чтобы создать запрос с параметром, надо в строку отбора ввести в квадратных скобках текст, который будет отображаться в диалоговом окне, открываемом в начале выполнения запроса.
Слайд 21
Рассмотрим некоторые условия отбора.
Слайд 22
И примеры использования символов в шаблонах.
5. Первичное осмысление и применение изученного
.
- Компьютерный практикум
1) Создание запроса в режиме Мастера
Создать запрос на выборку фамилии, имени и класса обучения: откройте базу данных Школьная библиотека откройте таблицу Школа ознакомьтесь с содержанием таблицы выберите кнопку Мастер запросов группы Другие вкладки Создание выберите в окне Новый запрос в списке видов мастеров запросов Простой запрос выберите в списке Таблицы и запросы окна Создание простых запросов таблицу Школа переместите из списка Доступные поля в список Выбранные поля имена полей Фамилия , Имя , Класс Далее Введите имя запроса Класс Готово откройте запрос в режиме Конструктора задайте сортировку по полю Класс посмотрите как выглядит запрос в режиме SQL . Для этого на вкладке запроса вызовите контекстное меню Режим SQL .
2) Создание запроса в режиме Конструктора
Создать запрос на выборку учащихся, у кого номер класса кратен 2 и он живет на улице Пушкина или Ленина: выберите кнопку Конструктор запросов группы Другие вкладки Создание добавьте таблицу Школа закройте окно Добавления таблицы в макете таблицы двойным щелчком по полю переместите в бланк запроса поля Фамилия , Имя , Улица , Класс в строку Условие отбора под полем Улица введите Пушкина Оr Ленина , под полем Класс введите 8 Оr 10 нажмите кнопку Выполнить закройте вкладку 8 класс.
3) Создание запроса с параметрами ( в режиме Конструктора )
Из таблицы Школа возьмите поля Фамилия , Имя , Класс , Хобби . В условие отбора поля Хобби введите =[Введите хобби] нажмите кнопку Выполнить введите значение параметра «футбол» ОК сохраните запрос под именем Футбол .
4) Создание запроса с вычислением (в режиме Конструктора)
Из таблицы Оценки возьмите поля Фамилия , І_семестр , ІІ_семестр в строке Поле в столбце, следующем за столбцом ІІ_семестр , введите выражение Годовая оценка: ([І_семестр]+[ ІІ_семестр])/2 нажмите кнопку Выполнить закройте вкладку запроса, сохранив его под именем Годовая.
- Самостоятельная работа
По таблице Школа создайте запросы:
С ?
Кто не занимается тяжелой атлетикой?
Кто увлекается аэробикой или футболом?
Кто учится в старших классах?
Кто из мальчиков живет на улице Фрунзе?
Кто из мальчиков с голубыми глазами занимается каратэ?
-Тестирование по теме
Тест:
1. База данных служит для:
хранения и упорядочения информации
ведения расчетно-вычислительных операций
обработки текстовой документации
обработки графической информации
2. Длина поля измеряется в:
миллиметрах
пикселях
байтах
символах
3. Записями считаются:
заголовки
столбцы
строки
таблицы
4. Текстовое поле позволяет занести информацию до:
255 символов
20 символов
65536 символов
1 символа
5. Логические данные - это:
денежные данные
текст
одно из двух значений
числа
6. Свойство автоматического наращивания имеет поле:
числовое
счетчик
МЕМО
логическое
7. Ключевое поле должно быть:
непременно счетчиком
обязательно числовым
уникальным
не должно содержать длинных записей
8. Укажите неправильный тип данных в Access
текстовый
параметрический
числовой
логический
9. Что не является элементом СУБД Access?
модули
запросы
макросы
таблицы
ключи
отчеты
запросы
10. Для чего предназначены запросы?
для хранения данных базы
для отбора и обработки данных базы
для ввода данных базы и их просмотра
для автоматического выполнения группы команд
для вывода обработанных данных базы на принтер.
11. Запишите, какую маску для поиска надо использовать, чтобы найти:
слова: кит, кот, кут ______
числа: 123, 1256000, 12,7 ______
даты: 12.12.2011, 03.12.20013, 26.12.2014 ______.
12. Какие условные выражения надо использовать, чтобы найти:
все числа, не меньшие 105 ______
все слова, кроме физика ______
все даты, ранее 12.11.2013 ______.
6. Подведение итогов урока, выставление оценок
7. Домашнее задание: 3.8, № 3, 6
Ответы:
- Самостоятельная работа
У кого фамилия начинается на букву С ? (Запрос возвращает 4 записи) (1 балл)
Кто учится в старших классах? (Запрос возвращает 2 записи) (1 балл)
У кого группа здоровья основная и он занимается футболом или каратэ? (Запрос возвращает 5 записей) (2 балла)
Кто из мальчиков живет на улице Фрунзе? (Запрос возвращает 2 записи) (2 балла)
Кто из мальчиков с голубыми глазами занимается каратэ? (Запрос возвращает 2 записи) (2 балла)
-Тестирование по теме
>=105
Not физика
< 12.11.2013
16-13 ответов – 4 балла
12-9 ответов – 3 балла
8- 5 ответов – 2 балла
4-1 ответ – 1 балл
К одной из таких программ относится MS Access.
Они применяются с целью сортировки, добавления, изменения, фильтрации, удаления определенной информации в БД. С их помощью производится отбор тех записей, которые нужны для составления определенных отчетов или форм, то есть работа осуществляется с частью базы данных.
При создании запросов в Access могут соединяться таблицы, группироваться и отбираться записи, подключаться расчетные операции.
Выделяют следующие виды запросов:
Создание запросов в Access данного вида предполагает построение таблицы, содержащей такие же структурные элементы, как и обычная. Она создается на базе фактических данных.
Результаты представляют собой динамический набор данных, в связи с чем при закрытии набора записи "исчезают", оставаясь в первоначальных таблицах. Сохранение данных запросов означает сохранение их структуры.
Данные запросы формируются указанием полей и таблиц, их содержащих, включаемых в запрос, описанием рассчитываемых полей, совершаемых групповых операций над первоначальными записями, и формированием условий отбора (например, с какой по какую дату осуществлялась реализация определенной группы товаров).
Создание запросов в MS Access данного вида предполагает, что их можно создать вручную или при помощи "Мастера создания запросов".
Для определения полей и таблиц, включаемых в запрос, переходим в режим конструктора.
Для перехода в режим конструктора в Access 2013 нужно в области навигации кликнуть контекстной кнопкой мыши на имени формы и выбрать "Конструктор". Нажав ALT+F8, можно вызвать "Список полей", из которых поля можно перетащить непосредственно в форму.
Эти виды запросов представляют собой некую разновидность первого вида, но нужны они для изменения данных, которые были извлечены. Они в Access помечены восклицательным знаком. В Access создание запросов на изменение возможно четырех типов:
Поэтому данные действия могут разрушить базу данных, в связи с чем необходимо сначала проверять результат исполнения запроса, перейдя в режим таблицы.
Создание новых таблиц используется при архивировании данных, проведении бэкапов или экспорте данных.
Разновидностью запросов на изменение являются запросы на обновление, при использовании которых обновляются абсолютно все записи, которые удовлетворяют какому-либо заданному условию. Если в комплекс данных необходимо внести изменения, то используют данную разновидность.
Еще одной разновидностью являются запросы на удаление, которые уничтожают все записи, удовлетворяющие каким-либо заданным условиям.
Еще одной разновидностью рассматриваемых запросов являются запросы на добавление, при которых происходит добавление данных из одной таблицы в другую.
Данные виды применяют с целью объединения в перекрестную таблицу денежных или числовых данных, хранящихся в исходной таблице. Перекрестный запрос в Access создается при помощи мастера запроса, используя инструкции которого, можно легко создать данный вид. По своей сути перекрестный запрос похож на сводные таблицы Excel.
Перекрестная таблица создается при указании заголовков строк, столбцов, значений и групповой операции.
Схема построения данного типа таблицы сводится к следующему. С помощью СУБД осуществляется группировка данных по групповым полям. Данная операция проводится над числами в поле значений. В ячейку таблицы, находящуюся на пересечении столбца и строк, помещается итоговое значение этой группы.
Если запустить данный запрос, то выйдет диалоговое окно, в котором будет содержаться предложение ввода параметров.
В Access 2013 и других версий запросы выполняются при помощи языка структурированных запросов SQL.
Для создания SQL-запросов необходимо перейти в режим конструктора, затем на кнопке "Вид" выбрать "Режим SQL". В результате появится диалоговое окно, в котором в поле Select вводим названия столбцов, а в поле From - то, что будет выводиться в строках, например названия поставщиков (если первый столбец "Название").
В Access используется не чистый SQL, а его диалект Jet-SQL. Основными инструкциями для запросов в этом языке являются: SELECT, по которому осуществляется выборка из записей по определенным условиям (названия полей исходных таблиц, переносящихся в результирующую таблицу), UPDATE - используется с целью редактирования записей, DELETE - для удаления каких-либо указанных записей, CREATE - для создания новых объектов БД. В также используются TRANSFORM для построения перекрестных запросов, WITH OWNER-ACCESS OPTION для создания специальных запросов пользователем, не имеющем доступа к таблицам, к которым должен быть доступ у этого запроса, IN (для обеспечения связи с удаленной БД), DISTINCTROW (создание запроса с возможным объединением данных). Также могут применяться итоговые функции SQL, встроенные функции Access и VBA.
Создание запросов в Access происходит для формирования записей для других запросов, отчетов или форм. При помощи определенных действий собираются данные из нескольких таблиц. Запросы позволяют включить в формируемую таблицу отобранные поля, осуществить расчеты в каждой новой записи, отобрать записи, которые необходимы для удовлетворения неких условий, сгруппировать выражения с одинаковыми значениями в некоторых или одном поле, сформировать новую таблицу данных на основе существующих, добавить, удалить или обновить некоторые записи.
В простейшем случае запрос реализует выбор из одной таблицы нужных полей, записей, соответствующих заданным условиям отбора, и просмотр результатов выполнения запроса.
Рассмотрим запросы на выборку в Access на примере получения информации из таблицы ТОВАР базы данных Поставка товаров .
Задача 1 . Пусть необходимо выбрать ряд характеристик товара по его наименованию.
В результате выполненных действий в окне конструктора запросов (рис. 4.1) в верхней панели появится схема данных запроса, которая включает выбранные для данного запроса таблицы. В данном случае одну таблицу ТОВАР. Таблица представлена списком полей. Первая строка в списке полей таблицы, отмеченная звездочкой (*), обозначает все множество полей таблицы. Нижняя панель является бланком запроса, который нужно заполнить.
Кроме того, на ленте появляется и автоматически активизируется новая вкладка (Query Tools | Design) (на рис. 4.3 представлена на часть этой вкладки), на которой цветом выделен тип созданного запроса - Выборка (Select). Таким образом, по умолчанию всегда создается запрос на выборку. Команды этой вкладки представляют инструментарий для выполнения необходимых действий при создании запроса. Эта вкладка открывается, когда в режиме конструктора создается новый запрос или редактируется существующий.
ЗАМЕЧАНИЕ
Окно запроса в режиме таблицы аналогично окну просмотра таблицы базы данных. Через некоторые таблицы запроса может производиться изменение данных базовой таблицы, лежащей в основе запроса. Запрос, просматриваемый в режиме таблицы, в отличие от таблицы базы данных Access 2010, не имеет столбца Щелкнуть для добавления
(Click to Add), предназначенного для изменения структуры таблицы. В этом режиме на вкладке ленты Главная
(Home) доступны те же кнопки, что и при открытии таблицы базы данных.
Задача 2. Пусть надо выбрать товары, цена которых не более 1000 руб., и НДС не более 10%, а также выбрать товары, цена которых более 2500 руб. Результат должен содержать наименование товара (НАИМ_ТОВ), его цену (ЦЕНА) и НДС (СТАВКА_НДС).
Задача 3 . Пусть надо выбрать все накладные за заданный период. Результат должен содержать номер накладной (НОМ_НАК), код склада (КОД_СК), дату отгрузки (ДАТА_ОТГР) и общую стоимость отгруженного товара (СУММА_НАКЛ).
Для закрепления смотрим видеоурок:
Вычисляемые поля в запросах Access .
