Формирование запросов для поиска и сортировки информации в базе данных кратко

Обновлено: 06.07.2024

Часто сталкиваюсь с тем, что дети не верят в то, что могут учиться и научиться, считают, что учиться очень трудно.

Урок 34. Практическая работа № 22. Формирование запросов для поиска и сортировки информации в базе данных.

Практическая работа № 22.

Тема: "Формирование запросов для поиска и сортировки информации в базе данных."

Цель работы: изучение приемов организации и использования запросов

Оборудование: ПК, Windows XP Professional, MS Access.

Задание:

1 В соответствие с заданием организовать запросы по отбору данных.

2 Продемонстрировать на компьютере запросы.

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

4 Сделать вывод о проделанной работе.

Теоретические сведения:

Любая СУБД позволяет выполнять четыре простейшие операции с данными:

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

Для выполнения этих операций используется механизм запросов . Результатом выполнения запросов является либо отобранное по определенным критериям множество записей, либо изменения в таблицах. Запросы к базе формируются на специально созданном для этого языке, который так и называется язык структурированных запросов (SQL — Structured Query Language).

Порядок выполнения:

1. Откройте созданную Вами ранее базу данных Фирма и проведите ее модификацию. Для этого, откройте таблицу Сотрудники и проведите ее редактирование:

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

Затем нажмите ПКМ на звездочку в восьмой строке и выберите команду Вставить

во второй или третьей записи (в зависимости от Вашего пола) измените фамилию на свою;
введите новую запись в режиме Ввод данных, для этого на ленте Главная во вкладке Записи выберите команду Создать (команда Создать будет активна только при выделенной любой строке таблицы!)

2. Создайте запрос, в котором можно просмотреть телефоны сотрудников.

Для создания запроса выполните команду: вкладка ленты Создание — Мастер запросов — Простой запрос.

Введите имя запроса - Телефоны - и нажмите кнопку Готово.

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

2 С помощью Конструктора создайте запрос Адреса клиентов. Для этого выполните команду: вкладка ленты Создание — Конструктор запросов.

6 В диалоговом окне Добавление таблиц выберите таблицу Клиенты и щелкните на кнопке Добавить, а затем - на кнопке Закрыть.

7 Чтобы перенести нужные поля в бланк запроса, необходимо по ним дважды щелкнуть левой кнопкой мыши (ЛКМ) в таблице. Щелкните ЛКМ по полям Код клиента, Название компании, Адрес.

8 Чтобы отсортировать записи в поле Название компании в алфавитном порядке, необходимо в раскрывающемся списке строки Сортировка выбрать пункт по возрастанию.

9 Сохраните запрос с именем Адреса клиентов. Для этого при закрытии запроса в появившемся диалоговом окне сохранения изменения макета выберите команду Да, и в следующем окне введите имя запроса Адреса клиентов.

10 Самостоятельно создайте запрос Дни рождения, в котором можно будет просмотреть дни рождения сотрудников. (для запроса выбрать поля Код сотрудника, Фамилия, Имя, Дата рождения)

11 Если нам нужно узнать, кто из сотрудников родился в конкретном месяце, то придется создать новый запрос или изменить условие в существующем запросе Дни рождения Допустим, мы хотим узнать, у кого из сотрудников день рождения в текущем месяце, например в апреле. Для этого откройте запрос Дни рождения в режиме Конструктора.

13 Закройте Конструктор и просмотрите полученный результат.

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

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

Запустите запрос еще раз и введите значение *.05.*, посмотрите, как изменился запрос.

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

Двойным щелчком ЛКМ выберите нужные поля из таблиц:

18 В условии отбора для логического поля Отметка о выполнении введите Да, чтобы в запросе отображались только выполненные заказы.

19 Сделайте так, чтобы столбец Отметка о выполнении не выводился на экран.

20 Создайте запрос Сумма заказа, в котором будут отображаться заказы на сумму более 50 000 руб.

Для подобных запросов в условии отбора можно использовать операторы сравнения >, =, и логические операторы And, Or, Not и др.

21 Измените запрос, чтобы сумма заказа была от 20 000 до 50 000 руб.

22 Создайте запрос для подсчета подоходного налога по каждой сделке.

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

Для этого откройте запрос Сумма заказа в режиме Конструктора.

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

Перед вами появится окно Построитель выражений, который состоит из трех областей: поля выражения (1), кнопок операторов(2) и элементов выражения(3).

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

23 В левом списке откройте папку Запросы и выделите запрос Сумма заказа. В среднем списке выделите поле Сумма и нажмите кнопку Вставить. Идентификатор этого поля появится в поле выражения Построителя.

24 Щелкните на кнопке * и введите 0,13. Таким образом, мы посчитаем подоходный налог 13 %.

26 Замените Выражение1 на Налог и закройте Конструктор.

27 Откройте запрос и посмотрите, что у вас получилось.

28 Используя Построитель выражений, измените запрос Сумма заказа, чтобы можно было посчитать прибыль от заказов. Уберите Условие отбора в поле Сумма (чтобы выводились все выполненные заказы) и добавьте поле Прибыль, в котором будет вычисляться доход от заказа (т. е. сумма минус налог).

Для этого откройте запрос Сумма заказа в режиме Конструктора. Удалите Условие отбора в поле Сумма (чтобы выводились все выполненные заказы).

29 Замените Выражение1 на Прибыль и закройте Конструктор, сохранив изменения.

Просмотрите полученный результат:

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

31 Покажите работу преподавателю.

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

33 Сделайте вывод о проделанной работе.

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

1 Для чего предназначены запросы?

2 Какие виды запросов вы знаете?

3 С помощью чего можно создавать запросы?

4 Для чего используют запрос с параметром?

5 Как можно сделать вычисления в запросах?

7 Можно ли создавать запросы на основе нескольких таблиц?

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

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

2) выполнение вычислений и представление результатов в виде таблицы (в т.ч. с использованием групповых функций);

Результат обработки исходной информации по запросу Microsoft Access представляет в виде собственной таблицы, в которую включены выбранные из БД сведения, удовлетворяющие критериям запроса.

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

Если выбирается режим КОНСТРУКТОР, то в появившемся окне Добавление таблицы выбрать таблицу, кнопки Добавить, Закрыть .

После этого отображается Окно построения запроса, состоящее из 2-х панелей – подсхемы данных и бланка запроса.

Вывод на экран – если установлен переключатель, поле выводится, если нет – не выводится, но в запросе участвует.

Условие отбора – это инструкция, при помощи которой выбираются записи, включенные в динамический набор данных.

Условие отбора – выражение, состоящее из операторов сравнения и операндов . Условие отбора в разных столбцах на одной строке объединяются по И (AND), на разных строках – ИЛИ (OR).

Если значение в строке записано без оператора, подразумевается " margin-bottom: .0001pt; margin: 0cm; text-align: justify; text-indent: 21.3pt;"> Условие отбора можно установить следующими способами:

Для полей, включенных в запрос дополнительно можно установить Свойста (формат, маска ввода, подпись).

После заполнения бланка запроса необходимо выполнить запрос : Группа Результаты на вкладке Конструктор, на ленте Работа с запросами.

База данных (БД) — организованная по определённым правилам (в соответствии с некоторой схемой) совокупность логически связанных и длительно хранимых в памяти ПК или на информационном носителе данных, характеризующая состояние некоторой предметной области и используемая для удовлетворения информационных потребностей пользователей.

Отличительные признаки БД:

1) БД хранится и обрабатывается в вычислительной системе (т.е. некомпьютерные хранилища информации — архивы, картотеки и прочее — не считаются базами данных);

2) данные в БД логически структурированы (систематизированы) для обеспечения возможности их эффективного поиска и обработки в вычислительной системе;

3) БД включает в себя схему (метаданные), описывающие логическую структуру БД в формальном виде.

Система управления базами данных (СУБД) — комплекс программных и языковых средств для создания и модификации структуры БД, добавления, изменения, удаления, поиска и отбора данных, их представления на экране ПК и в печатном виде, разграничения прав доступа к информации и выполнения других операций с БД.

Нельзя путать понятия базы данных и системы управления базами данных!

База данных — это структурированные данные. Система управления базой данных — это инструмент для работы с базой данных.

Классификацию БД обычно проводят по типам используемых в них моделей данных:

1) иерархическая модель данных — БД представлена в виде древовидной (иерархической) структуры (графа), состоящей из объектов данных различных уровней; при этом объект может включать в себя несколько объектов более низкого уровня.

2) сетевая модель данных состоит из набора записей и набора связей между этими записями; она в чём-то аналогична иерархической модели, но в сетевой БД связи являются направленными и могут соединять объекты разных ветвей дерева.

3) реляционная БД состоит из нескольких взаимосвязанных таблиц:

Рассматривая таблицы, связанные по значениям поля ID, можно определить, что, например, Петров Пётр Петрович (ID = 2 — таблица 1) заказал сканер, № заказа = 111/12 (таблица 2), а также узнать его адрес и телефон (таблица 3).

Связи (“реляции”) между двумя какими-либо таблицами осуществляются через общее для них по смыслу (но не обязательно одинаковое по названию) поле. При этом возможны связи:

• “один к одному” — одной записи первой таблицы соответствует одна, и только одна запись второй таблицы, и наоборот (пример: в ОС MS-DOS полному имени файла однозначно соответствует запись номера начального кластера);

• “один ко многим” — одной записи первой таблицы может соответствовать много записей второй таблицы (пример: один и тот же учитель может вести уроки в нескольких классах);

• “многие к одному” — много записей первой таблицы могут соответствовать одной записи второй таблицы (пример: у нескольких учеников занятия по предмету ведёт один и тот же учитель); связи “многие к одному” и “один ко многим” являются аналогами друг друга;

• “многие ко многим” — много записей в первой таблице могут быть связаны с многими записями второй таблицы (пример: одного и того же ученика могут учить разные учителя, а один и тот же учитель может учить множество учеников). Подобный тип связей в реляционных БД не допускается и при необходимости реализуется как две связи “один ко многим” через промежуточную таблицу (в приведённом только что примере учитель связывается с учеником через номер класса и предмет). Показатель количеств связываемых объектов называют кардинальностью связи:

“многие к одному”

“многие ко многим”

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

Основой реляционной БД является таблица.

Поля БД — это характеристики объектов (сущностей), информация о которых хранится в БД. Поля БД соответствуют столбцам таблицы.

Записи БД — это информация о каждом из объектов (одному объекту соответствует одна запись), выраженная в виде значений соответствующих полей. Записям БД соответствуют строки таблицы.

Характеристики, отражённые в виде полей БД, являются едиными (общими) для всех объектов. Объекты в БД должны различаться хотя бы одним значением какой-либо характеристики.

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

Выборка данных из БД — операция отбора записей БД (строк таблицы), соответствующих заданному условию (запросу на выборку).

Условие (запрос) может быть простым (накладывается на значения какого-то одного поля либо выражено в сравнении двух каких-либо полей) или составным (простые условия объединяются при помощи логических операций И, ИЛИ, НЕ).

Распределённая БД — совокупность логически взаимосвязанных БД, размещённых на различных узлах компьютерной сети.

Практические приёмы работы с БД

1. Поиск (выборка) информации в однотабличной БД

Запрос на поиск задаётся в виде структуры, аналогичной структуре записи БД, где отдельные поля могут быть пусты (пропущены в запросе), содержат константное значение или условие, накладываемое на значение данного поля.

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

Ручной поиск в БД:

• записи БД просматриваются поочерёдно;

• если значение первого по счёту (слева направо) непустого поля запроса совпадает с константой, заданной в том же поле в запросе, либо удовлетворяет условию, заданному в том же поле в запросе, то помечается эта запись БД;

• проверяются в этой записи БД остальные поля (слева направо) на соответствие константам либо условиям в тех же полях запроса;

• если все эти поля записи БД удовлетворяют значениям/условиям в таких же полях запроса, то эта запись БД включается в выборку; если хотя бы одно поле записи не удовлетворяет значению/ условию в таком же поле запроса, то эта запись БД пропускается (не включается в выборку).

2. Сортировка записей БД

Для выполнения сортировки записей БД задаются:

• одно или несколько названий полей, по содержимому которых нужно выполнить сортировку (порядок перечисления полей в запросе на сортировку важен и не обязательно соответствует порядку следования этих полей слева направо в структуре записи БД);

• для каждого такого поля — условие сортировки (для чисел — по возрастанию или по убыванию, для текста — по алфавиту или в порядке, обратном алфавитному).

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

Ручная сортировка в БД

Пусть нужно отсортировать записи БД — адресной книги по полям “Фамилия” (по алфавиту), “Имя” (по алфавиту) и “Отчество” (по алфавиту):

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

Запрос — объект БД, который используется для реализации эффективного поиска и обработки данных.

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

Самый распространенный вид запросов — запрос на выборку. Он предназначен для отбора данных из источника в соответствии с некоторым условием. Условие запроса — это выражение, которое СУБД сравнивает со значениями в полях запроса, чтобы определить, следует ли включать в результат записи, содержащие то или иное значение.

Запрос на выборку позволяет:

1. Просматривать значения только из полей, которые вас интересуют.
2. Просматривать записи, которые отвечают указанным вами условиям.
3. Использовать выражения в качестве полей.

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

Основные режимы работы с запросами в Access:

1. Режим таблицы. Отображает информацию запроса на выборку в режиме таблицы.

2. Конструктор. В этом режиме определяется структура запроса и условия выбора данных (см. Приложение к главе 1).

Создать запрос можно с помощью Мастера запросов либо в Конструкторе (пример 5.2).

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

Основные этапы создания запроса на выборку:

1. Выбор инструмента создания запроса.
2. Определение вида запроса.
3. Выбор источника(ов) данных.
4. Добавление из источника(ов) данных полей, которые должен содержать запрос.
5. Определение условий, которые формируют набор записей в запросе.
6. Добавление группировки, сортировки и вычислений (может отсутствовать).

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

Примеры записи условий в запросах:

Действие в запросе

Поля с числовым типом данных

Выбираются записи, у которых значение в этом поле больше 0 и меньше 8.

Выбираются записи, у которых значение в этом поле не равно 0.

Поля с текстовым типом данных

Если значение в поле записи равно Орша, то запись включается в результат запроса.

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

Если необходимо найти несколько значений полей, можно использовать оператор In . Этот оператор позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках.

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

В режиме конструктора процесс создания запроса находится под вашим контролем, однако здесь есть вероятность допустить ошибку и необходимо больше времени, чем в мастере (пример 5.4).

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

Все запросы, которые рассмотрены в примерах 5.3 и 5.4 , содержат конкретные значения названий, имен, времени и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно будет открыть в конструкторе, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра, в котором пользователь может ввести конкретное значение, а затем получить нужный результат. Параметр запроса определяется в строке Условие отбора конструктора для столбца, содержащего запрашиваемые значения. Параметром является слово или фраза, заключенные в квадратные скобки. Параметр будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса (пример 5.5).

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

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

Наряду с запросами на выборку часто применяются запросы на действие. С помощью таких запросов можно обновлять значения полей записей, добавлять новые или удалять уже существующие записи. В СУБД Access такие запросы можно создать в режиме конструктора, воспользовавшись инструментами группы Тип запроса:

Пример 5.1. Режимы работы с запросами.

Режим SQL позволяет создавать и просматривать запросы с помощью инструкций языка SQL.

SQL (англ. structured query language — язык структурированных запросов). Применяется для создания, редактирования и управления данными в реляционной базе данных.

Пример 5.2. Группа инструментов Запросы вкладки Создание.