Запросы

Сегодня мы подробно поговорим о запросах в Access.

Запросы, как вы уже знаете, нужны для работы с данными, находящимися в таблицах:
Чтобы создать запрос…
1) …в окне базы данных открываете Запросы
2) …и создаете запрос с помощью конструктора.

Пожалуйста, НЕ пользуйтесь для создания запросов Мастером, потому что он позволяет делать только самые простые запросы, а переделывать их потом в более продвинутые даже сложнее, чем создать запрос в конструкторе «с нуля».

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

Запросы на выборку

В запросах на выборку вы просите отобразить на экране определенные данные из таблиц.
Например, клички, породы собак и дату их рождения. На видео показано, как создать и запустить запрос:
В этой версии БД немного сокращена схема даны, чтобы упростить запросы: НЕТ отдельной таблицы для пород tbBreed.

Загрузить видео в rar-архиве: http://informatika.delayu.ru/app/video/10-access-query.rar

Итак, вот главные элементы конструктора запросов:
1 – область данных (таблиц);
2 – область условий отбора;
3 – кнопка запуска запроса.

Условия отбора

В запросах на выборку можно задавать условия. Для этого в СООТВЕТСТВУЮЩЕМ СТОЛБЦЕ нужно указать в строке Условие отбора нужное значение.
Найдем всех шар-пеев.

Будьте внимательны! Компьютер ищет ТОЧНЫЕ совпадения, поэтому если вы случайно сделали опечатку, поставили лишний пробел или забыли дефис, то запрос может не сработать.

При задании условия отбора можно использовать операторы >, <, =, >= (больше или равно),<= (меньше или равно), <> (не равно).
Найдем всех молодых (моложе 2 лет) собак.

Обратите внимание! Также как и Excel, Access работает с разными типами данных: строковыми, числовыми, временными, логическими и др. Чтобы показать, к какому типу он отнес данное условие отбора, Access автоматически заключает строки в кавычки («шар-пей»), даты – в решетки (#01.05.2008#), числа оставляет, как есть (15), а логические проверяет, чтобы были либо ИСТИНА, либо ЛОЖЬ.

Составные условия отбора

Если нужно задать несколько условий, то условия для оператора «И» записывайте в одной строчке, а для оператора «ИЛИ» в одном в столбце друг под другом.
Здесь мы находим всех английских сеттеров и сеттеров-гордонов.

А здесь всех щенков (моложе года) шар-пеев.

А кого мы найдем здесь?

а) всех щенков шар-пеев и щенков английских сеттеров;
б) всех щенков шар-пеев и всех английских сеттеров;
в) всех шар-пеев и всех щенков английских сеттеров;

Проверьте! Сформулируйте правило, по которому Access из записей в ячейках формирует составные условия отбора.

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

Всех юниоры (которым больше года, но меньше двух лет).

Оператор LIKE

На выставке организована секция сеттеров. Для участия в ней нам необходимо отобрать всех представителей этой группы. Если мы просто напишем в условиях отбора «сеттер», то запрос не отберет ни одной записи, поскольку у нас нет просто сеттеров, а есть английские, ирландские сеттеры, сеттеры-гордоны, а Access требует полного совпадения значения в ячейке со значением, указанным в условии отбора. Перечислять их всех слишком долго, к тому же можно кого-нибудь забыть.
В этом случае нам пригодится оператор LIKE, который позволяет делать отбор по фрагменту строки. Использование LIKE перед условием отбора, это все равно, что включение режима подстановочных знаков в Word (* – любая последовательность символов; ? – любой символ). Значит, чтобы Access мог отыскать английских, ирландских сеттеров и сеттеров-гордонов, нужно написать в условии отбора LIKE *сеттер*.

Кто и почему будет отобран, если мы напишем в условии отбора LIKE *сеттер ?

Запросы из нескольких таблиц

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

Чтобы добавить в запрос еще одну таблицу, вызовите контекстное меню на области таблиц и в нем пункт Добавить таблицу.

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

Отсев пустых строк

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

Почему это происходит?
Дело в том, что у нас в таблице tbPerson наряду с владельцами собак записаны и судьи (Петровская, Елец, Терещук). Судьи не имеют права привозить своих собак на выставку, поэтому в строках с их фамилиями пустые ячейки с кличками собак.
Есть два способа удалить пустые строки.
1. Поставить условие на значение клички собаки Is not Null, т.е. НЕ ПУСТА.

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

Вопрос вам: какие настройки нужно поменять в диалоговом окне параметров объединения?

Запросы с вычислениями

До сих пор мы только отбирали записи по различным условиям. Но Access позволяет не только просматривать данные, записанные в таблицах, но и производить ВЫЧИСЛЕНИЯ: по дате рождения определять возраст; из имени, фамилии и отчества делать фамилию с инициалами; по цене единицы товара и его количества определять общую стоимость покупки; по дате выдачи книги в библиотеке определять величину штрафа при задолженности и многое другое. Для вычислений используются встроенные функции (похожие на те, что есть в Excel).

Простейшая операция – это сложение строк. Напишем в ячейке выражение для вывода такой фразы: владелец из города город.
Для этого запишем в верхней строке нового столбца области условий: [LastName] + “ из города ” + [City].

Имена полей записываются в квадратных скобках, фрагменты строк – в кавычках, между ними стоят знаки сложения.

Выражения для вычислений записываются в самой верхней строке (Поле) области условий. До сих пор мы писали условия в нижних строках (Условия отбора).

Чтобы вам не запутаться: в верхней строке мы пишем, ЧТО выводить на экран, а затем (в нижней) – при КАКОМ УСЛОВИИ.

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

Построитель выражений

Чтобы удобнее было редактировать выражения, существует специальный редактор – «Построитель выражений». Он выглядит так:

И вызывается с помощью контекстного меню: нужно поставить курсор на ту ячейку, куда вы будете записывать выражение:

В построителе выражений вы можете выбирать из библиотеки функции:

и данные из таблиц (можно пользоваться ТОЛЬКО теми таблицами, которые используются в данном запросе и отображены в области данных):

Когда вы двойным щелчком мыши выбираете из списка имя поля или функцию, 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(([Age]<=1); «щенок»; «взрослый») аналог функции ЕСЛИ из Ecxel.
и другие…

Задание: напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
Задание: напишите выражение, которое вычисляет, сколько собаке лет по дате ее рождения.
Задание: напишите выражение, которое определяет по возрасту, в какой возрастной категории выступает собака: «щенок» – до года; «юниор» – от года до двух лет; «сеньор» – старше двух лет.
Дополнение: Есть два способа вычислить возраст собаки: один более точный, другой менее:
1) из текущего года вычесть год рождения собаки;
2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня. Примените в одном из заданий один способ, в другом – другой.

Запросы с параметром

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

Устроен этот запрос так:

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

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

Запросы с группировкой

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

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

Теперь сгруппируем собак по породе и посчитаем количество разных кличек в каждой группе:

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

Добавляем в область таблиц таблицу с оценками (tbMarks). Группируем оценки по турнирному номеру собаки и среди групповых операций выбираем среднее значение Avg (от англ. average – среднее).

Выполните запрос и в режиме просмотра обратите внимание, что столбцы с групповыми операциями имеют двойное название (операция + имя поля). Нам это пригодится при подсчете суммы баллов.

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

Можно также округлить значения до одного знака после запятой: Round([Avg-Exterior]+[Avg-Training];1)

Задание: выясните, какая собака вызвала наиболее противоречивые мнения судей. Для этого надо из максимального балла вычесть минимальный.

Запросы на изменение, удаление, добавление

На первом уроке мы уже говорили о том, что запросы позволяют не только просматривать данные из таблиц, но и редактировать записи: добавлять новые, удалять, изменять. Тип запроса можно изменить с помощью списка запросов на панели инструментов.

Оператору базы данных накануне выставки пришла новая информация:
1) собака Дези заболела и не сможет принять участия в выставке;
2) по ошибке Гардиен, являющийся на самом деле английским сеттером, был указан как ирландский сеттер;
3) владелец Мигунова подала заявку на участие в выставке еще одной своей собаки (кличка: Гарри, порода: сеттер-гордон, пол: м, дата рождения: 15.09.07).

Приступим к редактированию базы данных.
1) Удалить запись с Дези.
Создаем запрос quDelDog. Тип запроса – на удаление. Со сменой типа запроса несколько изменяется и область условий. Появилась новая ячейка Удаление. Под ней вы указываете условие, по которому нужно отобрать записи, подлежащие удалению. Даже если вы указываете условие отбора для одного поля, удалена будет ВСЯ запись.

После того, как вы нажали на «восклицательный знак», на экране появится сообщение о том, что запись удалена. Теперь откройте таблицу tbDog и убедитесь, что Дези в ней нет.

2) У Гардиена изменить ирландский сеттер на английский сеттер.
Создаем запрос quUpdateDog. Тип запроса – на обновление. Находим Гардиена и обновляем у него породу.

Откройте таблицу tbDog и убедитесь, что у Гардиена порода – английский сеттер.

3) Добавить запись с Гарри.
Создаем запрос quAddDog. Тип запроса – на добавление. У запросов на добавление есть одна особенность: в области таблиц отображены НЕ те таблицы, КУДА вы добавляете запись, а ОТКУДА вы берете данные (если это требуется). Целевую таблицу (в которую добавляются записи) вы указываете в диалоговом окне, которое появляется сразу, как только вы установите тип запроса (на добавление):

Поскольку мы не берем данные из других таблиц, а создаем новую запись, то область таблиц должна быть ПУСТОЙ! (там не должно быть ни одной таблицы). В области условий в строке Поле вы пишете, ЧТО добавить (новое значение для каждого поля), а в строке Добавление КУДА (имена полей):

Откройте таблицу tbDog и убедитесь, что Гарри в ней появился!

Язык запросов SQL

Когда вы нажимаете на «восклицательный знак», запрос выполняется. Так это представляется на взгляд новичка.
Профессионалы знают, что на самом деле в этот момент выполняется инструкция на специальном языке запросов 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.

Их немало! Но, коль скоро вы их освоили, вы сможете отыскать в базе данных любую информацию.

Проверьте свои знания! Выполните в вашей базе данных следующие запросы (или переименуйте уже выполненные так, чтобы их имена совпадали с именами задач):

1. выборка

quSelectDog: Найдите всех шар-пеев и сеттеров-гордонов из регионов (НЕ из Москвы); используйте оператор «не равно».

2. оператор Like

quLike: Найдите всех абонентов МТС (те, у кого номер мобильного телефона начинается с 8(916)…).

3. выражения

quEvalText: Напишите выражение, которое из фамилии, имени и отчества делает фамилию с инициалами. Иванов Иван Иванович -> Иванов И.И.
quEvalDate: напишите выражение, которое вычисляет, сколько собаке лет по дате ее рождения.
quEvalIif: напишите выражение, которое определяет по возрасту, в какой возрастной категории выступает собака: «щенок» – до года; «юниор» – от года до двух лет; «сеньор» – старше двух лет.
Дополнение: Примените в одном задании один способ вычислить возраст собаки, в другом – другой: 1) из текущего года вычесть год рождения собаки; 2) с помощью функции DateDiff посчитать, сколько дней прошло с рождения до сегодняшнего дня.

4. параметр

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

5. группировка

quGroup: Выясните, какая собака вызвала наиболее противоречивые мнения судей.

6. редактирование БД

Незадолго до выставки владелец Гороховец уехал на ПМЖ (постоянное место жительства) в Германию и передал всех собак своему другу Карпову Михаилу Игоревичу. Требуется:
1) quAddOwner: добавить запись о новом владельце;
2) quUpdateOwner: изменить у собак Гороховца ID владельца на ID Карпова;
3) quDelOwner: удалить Гороховца из базы данных.

Жду ваших БД с выполненными заданиями, а также шаблоны SQL-инструкций для запросов на обновление, изменение, удаление.

ВСЕ!