Технология работы с базами данных ЗАПРОСЫ
Вопросы ØНазначение запросов. ØВиды запросов. ØСоздание запроса с помощью Конструктора. Бланк запроса по образцу. ØПостроение запросов на выборку. ØПостроение запросов на изменение. ØВычисляемые поля в запросах.
Назначение запросов Виды обработки данных с помощью запросов: l включение в результирующую таблицу заданных полей; l выбор записей по условиям отбора (И, ИЛИ и другие); l вычисления в каждой из полученных записей; l группировка записей с одинаковыми значениями в одном или нескольких полях для выполнения над нами групповых функций; l обновление полей в выбранных записях; l удаление или добавление выбранного подмножества записей из таблицы БД; l создание новой таблицы БД на основе запроса; l анализ информации, на основе перекрестного запроса, сформированного на двух и более полях из одной или более таблиц (создание кросс-таблицы).
Создание запроса Для создания запроса используются команды области Запросы вкладки Создание. Запрос можно создать с помощью Ø Мастера запросов или Ø Конструктора запросов
Виды запросов Различают два основных вида запросов: l на выборку; l на изменение.
Запрос на выборку позволяет создать таблицу, в которой отображаются только нужные по условию запроса данные из базовых таблиц. Это объект БД, который служит для отображения данных в режиме таблицы. Запрос может получать данные Ø из одной или нескольких таблиц, Ø из существующих запросов Ø из комбинаций таблиц и запросов. Таблицы или запросы, используемые для получения данных, называются источниками записей.
К запросам на выборку относятся: l l l l простой запрос (самый распространенный тип запроса, в результате которого формируются таблицы, отображающие только нужные по условию задачи данные из всей базы, строится с помощью Мастера запросов или Конструктора), запрос с параметром, запрос с условием, итоговый запрос, запрос на выявление записей без подчиненных (строится с помощью Мастера), запрос на выявление повторяющихся записей (строится с помощью Мастера). перекрестный запрос (строится с помощью Мастера),
К запросам на изменение относятся: запрос на добавление, l запрос на обновление, l запрос на удаление, l запрос на создание новой таблицы. l
Создание запроса с помощью Конструктора. Бланк запроса по образцу После включения Конструктора MS Access выводит Бланк запроса по образцу
Бланк запроса по образцу В верхней части бланка осуществляется выбор таблиц (и/или запросов), на основе которых будет строиться текущий запрос. Если окна Добавление таблицы нет на бланке, его следует вызвать одноименной командой из контекстного меню.
Бланк запроса по образцу На нижней панели задается структура запроса, т. е. структура результирующей таблицы, в которой будут содержаться данные, полученные по результатам запроса. Таблица на нижней панели содержит строки: Ø Поле: , Ø Имя таблицы: , Ø Сортировка: , Ø Вывод на экран: , Ø Условие отбора: Ø или: .
Назначение строк нижней части Бланка запроса по образцу Ø Ø Строка Поле: заполняется перетаскиванием мышью названия полей из таблиц верхней части или двойным щелчком по имени поля, или выбором из списка поля. Если переносятся все поля таблицы, то следует щелкнуть по «*» в самом начале списка полей Строка Имя поля: заполняется автоматически. Строка Сортировка: позволяет задать порядок сортировки по определенному полю или полям (по возрастанию, по убыванию, отсутствует). Строка Вывод на экран: регулирует, какие данные будут отображаться в запросе Иногда по полю, которое не будет выведено в запросе, осуществляют сортировку, или оно может содержать конфиденциальные данные, поэтому флажок отключают.
Назначение строк нижней части Бланка запроса по образцу Строка Условие отбора: содержит условие, или критерий отбора, по которому выбирают записи для включения в результирующую таблицу Ø Строка или: позволяет задать несколько условий отбора, связанных логическим ИЛИ (если условия отбора для разных полей записаны в одной строке, то между ними автоматически устанавливается логическое И, т. е. они должны выполняться одновременно). Ø
После перехода в режим Конструктора запросов на экране отображается дополнительная вкладка Работа с запросами – Конструктор, Команды, расположенные на дополнительной вкладке позволяют строить различные виды запросов и выполнять с ними различные действия
Запросы на выборку. Простой запрос строится выбором необходимых полей из таблиц БД и/или запросов. Чтобы увидеть результаты запроса, следует перейти в режим Таблицы Ø выбором из списка кнопки <Режим> или Ø кнопкой <Режим таблицы> в строке состояния
Запросы на выборку. Запрос с параметром Если часто требуется запускать различные варианты одного запроса, лучше воспользоваться запросом с параметрами. Ø При выполнении запроса с параметрами у пользователя запрашиваются значения полей, которые затем используются для создания условий запроса. Ø Для этого в строке Условие отбора: в соответствующее поле вводится конструкция Like[] (или в квадратных скобках указывается фраза, которая будет выдаваться при запуске запроса). После запуска этого запроса будет открыто диалоговое окно, в которое нужно будет ввести значение искомого параметра.
Запросы на выборку. Запрос с условием В запросе с условием заполняется строка Условие отбора: для соответствующих полей. Условие запроса это правило, определяющее, какие записи требуется включить в результаты запроса. Добавлять условия к каждому запросу не обязательно: их следует задавать в том случае, если просматривать нужно не все записи, хранящиеся в базовом источнике данных.
Примеры условий в запросах с условием Условие Результат >25 Это условие применимо к полю типа «Числовой» . При использовании этого условия в результаты запроса включа-ются только те записи, в которых в соответствующем поле содержатся значения больше 25 Date. Diff ("yyyy", [Дата. Рождения], Date()) > 30 Это условие применимо к полям типа «Дата/Время» , например, к полю «Дата. Рождения» . В результаты запроса включаются только те записи, в которых количество лет, прошедших с даты рождения сотрудника до текущей даты, превышает 30.
Примеры условий в запросах с условием Between #01. 2011# And #31. 12. 2011# Возвращает все даты от 1 января 2011 г. до 31 декабря 2011 г. Not «Россия» Возвращает все записи, не содержащие в данном поле значение «Россия» Not «Т*» Возвращает все записи, кроме тех, значения которых в данном поле начинаются с «Т» Not «*т» Возвращает все записи, кроме тех, значения которых в данном поле заканчиваются на «т»
Примеры условий в запросах с условием In (Россия, США) Возвращает все записи, содержащие в данном поле значения из списка (в данном случае – это Россия и США) Like «[А-Д]*» Возвращает все записи, содержащие в данном поле значения, начинающиеся с букв от А до Д Like «*ст*» Возвращает все записи, содержащие в данном поле значения, включающие сочетания букв «ст» . Like «Новосибирск» Возвращает все записи, содержащие в данном поле значение «Новосибирск»
Примеры условий в запросах с условием #31/12/2011# Date() <Date()-30 Is Null Is Not Null «» Возвращает все записи, содержащие в данном поле значение 31 января 2011 г. Возвращает все записи, содержащие в данном поле текущую дату Возвращает все записи, содержащие в данном поле значение даты не позже 30 дневной давности Это условие можно применить к полю любого типа, чтобы отобразить записи с нулевым значением поля Возвращает все записи, имеющие в данном поле непустое значение Возвращает все записи, которые содержат пустые строки
Запросы на выборку. Итоговый запрос позволяет выполнить вычисления по какой-то определенной группе записей, поэтому, прежде чем получать итоги, следует выполнить группировку по какому-либо признаку, нажав кнопку Итоги ( ). В результате в нижнюю часть Бланка по образцу добавится строка Групповая операция: . В тех полях, по которым осуществляется группировка, устанавливается функция Группировка, а в тех полях, в которых необходимо получить итоги, из раскрывающегося списка выбирается соответствующая функция (Sum, Avg, Min, Max, Count и др. ).
Запросы на выборку. Перекрестный запрос позволяет построить сводную таблицу, эскиз которой формирует сам пользователь, задавая: Ø какие поля будут строками сводной таблицы, Ø какие поля будут столбцами сводной таблицы, Ø какие вычисления необходимо произвести. В Перекрестном запросе выводятся строки промежуточных и общих итогов. Он строится с помощью Мастера запросов.
Запросы на выборку. Запрос Записи без подчиненных выявляет, каких данных нет в связанной таблице по отношению к основной. Строится с помощью Мастера запросов. Например, необходимо выяснить наличие вакансий в штатном расписании фирмы. Имеются 2 таблицы Штат и Список Для этого следует: l в Мастере запросов выбрать Записи без подчиненных; l выбрать таблицу, в которой ищутся записи, не имеющие подчиненных в связанной таблице ( «Штат» ); l выбрать таблицу с подчиненными записями ( «Список» ); l определить общие данные в обеих таблицах, по которым установлена связь (код должности); l задать поля, которые будут отображаться в отчете (все поля). l задать имя запроса (например, «Вакансии» ).
Запросы на выборку. Запрос Записи без подчиненных В результате запрос «Вакансии» будет иметь вид (в режиме Таблицы)
Запросы на изменение !!! Построение любого запроса на изменение начинается с построения запроса на выборку !!!
Запросы на изменение. Запрос на создание новой таблицы позволяет построить новую таблицу на основе данных, которые хранятся в других таблицах. Для этого следует: 1. Вызвать Конструктор запросов. 2. Добавить все таблицы, участвующие в формировании новой таблицы в верхнюю часть Бланка запроса по образцу. 3. Выбрать из таблиц необходимые поля (таблицы должны быть связанными, если между ними нет связи, то следует добавить связующую их таблицу, даже если она не участвует в формировании новой таблицы).
Запросы на изменение. Запрос на создание новой таблицы 4. Если присутствует строка «Предупреждение безопасности…» ), то следует нажать кнопку <Включить содержимое>, после чего сохранить запрос и затем открыть его в режиме Конструктора (если строки нет, то этот пункт можно пропустить). 5. Нажать кнопку <Создание таблицы> на дополнительной вкладке Конструктора, задать ее имя и место сохранения. 4. В результате на основе одной или нескольких таблиц будет создана новая таблица, и ее имя будет включено в список таблиц на Панели навигации.
Запросы на изменение. Запрос на обновление используется Ø для изменения (добавления, изменения или удаления) данных в одной или нескольких записях, Ø для ввода условий, указывающих, какие строки следует обновить. Запрос на обновление позволяет просмотреть обновленные данные перед выполнением обновления. Запрос на изменение невозможно отменить. Возможно, перед обновлением следует создать резервные копии всех таблиц, которые будут обновлены запросом на обновление.
Запросы на изменение. Запрос на обновление Например, необходимо увеличить на 10% оклады операторам. Для построения запроса на обновление следует: 1. На основе таблицы «Штат» построить запрос на выборку, в ко-тором в строке Условие отбора: ввести «оператор» . 2. Нажать кнопку <Обновление>, 1. 2. 3. в результате чего появится дополнительная строка Обновление. В этой строке в поле Оклад ввести формулу [Штат]![Оклад]*1, 1 (что позволит увеличить оклад на 10%). Если нужно, Включить содержимое. Нажать кнопку <Выполнить>, в результате чего в таблице «Штат» будет на 10% увеличен оклад оператора.
Вычисления в запросах. Вычисляемые поля. Построитель выражений Для указания условий отбора данных и для создания вычисляемых полей в запросах используются выражения. Выражения это формулы, по которым вычисляются необходимые значения. Для построения выражений при формировании условий отбора применяются: Ø идентификаторы, Ø литералы, Ø операторы, Ø символы-заменители, Ø арифметические вычисления Ø функции.
Идентификатор – это имя, введенное в выражение для резервирования места под значение, которое хранится в поле или элементе управления. Например, обращение к полю «Оклад» в таблице «Штат» выглядит так: [Штат]![Оклад]
Литерал – это точное значение, которое БД использует именно в том виде, как оно вводится. При записи литерала используются специальные символы-ограничители, которые указывают на тип данных литерала: Ø число вводится без ограничителей, (например: 465. 8). Ø текстовый литерал имеет в качестве ограничителя кавычки «» или '. ( «Иванов» или 'Иванов'). Ø дата использует ограничитель # (например: #12. 11. 96#). Ø поле или элемент управления использует ограничители [ ]. ([Фамилия]). Ø константы Да, Нет, Истина, Ложь, Null (для пустых полей) Ø оператор конкатенации & для соединения частей текста, например =[Фамилия] & «» & [Имя];
Операторы обозначают отношения между данными полей и применяются для поиска результата более, чем по одному значению. Оператор указывает действие, которое должно быть выполнено с элементами выражения. Примеры операторов: Ø сравнения =, >, <, >=, <>. Ø логические используются для задания нескольких условий отбора: And (И) – оба условия отбора должны принимать значение Истина; Or (ИЛИ) – хотя бы одно из условий отбора должно принимать значение Истина; Xor (исключающее ИЛИ) – одно из условий отбора, но не оба должно принимать значение Истина; Not (НЕ) – условие отбора не должно принимать значение Истина.
Операторы Ø Операторы SOL: Between…and – для выбора значений из определенного интервала, например, Between #01. 2005# And #31. 03. 2005#. оператор Like – для работы с символами-заменителями «*» и «? » , например Like «А*» оператор In – для определения, содержится ли элемент данных в списке значений. Ø арифметические операторы: * (умножение), + (сложение), - (вычитание), / (деление), ^ (возведение в степень).
Функция – это специальное имя, которое используется для выполнения какой-либо операции и может применяться в выражениях. В Access встроено несколько десятков функций. Аргументы функции должны заключаться в круглые скобки ( ). Скобки могут быть опущены только при нулевом аргументе. Примеры функций, используемые при построении выражений в запросах: Avg( ) – среднее арифметическое значений), Count( ) – количество записей), Sum( ) – сумма всех записей Date() – системное значение даты компьютера
Вычисления в запросах В запросы можно добавлять поля, содержимое которых – результат вычислений, основанных на данных из других полей (или даже таблиц). Эти поля называют вычисляемыми полями. В таких случаях в Бланке запроса по образцу на месте имени поля указывается название нового имени вычисляемого поля и через двоеточие – формула для вычислений. Например, вычисление среднего балла из 4 -х экзаменов в таблице «Сессия» : Средний балл: ([Оценка 1]+[Оценка 2]+[Оценка 3]+[Оценка 4])/4 ! Имена полей с данными в формуле заключают в квадратные скобки. В общем случае перед именем поля следует указывать еще и название таблицы, содержащей это поле, затем знак «!» и имя поля.
Вычисляемые поля Ø Ø Ø По вычисляемому полю так же, как и по другим полям, можно проводить операции: сортировки, назначать условия, проводить другие операции. Вычисляемые поля можно вводить вручную или воспользоваться Построителем выражений. Построитель выражений – специальный инструмент MS Access, позволяющий облегчить ввод данных для выражений. Вывести на экран его можно, например, вызвав в Бланке запроса в строке Условие отбора: контекстное меню и затем, выбрав функцию «Построить» (комбинации клавиш {Ctrl+F 2} или по кнопкe <Построитель>).
Общие сведения о Построителе выражений
Общие сведения о Построителе выражений 2 3 4 5 6 1 – Поле выражения. Здесь можно ввести выражение и добавить его элементы, дважды щелкнув их в доступных ниже списках элементов. – Список «Элементы выражений» . Чтобы просмотреть его категории в списке «Категории выражений» следует щелкнуть тип элемента. – Список «Категории выражений» . Чтобы просмотреть значения категории в списке «Значения выражений» следует щелкнуть категорию. Если в списке «Значения выражений» нет значений, то следует дважды щелкнуть элемент категории, чтобы добавить его в поле выражения. – Список «Значения выражений» . Чтобы добавить значение в поле выражения следует дважды щелкнуть по нему. – Справка и сведения о выделенном значении выражения – Ссылка на инструкции и справку. Позволяет получить сведения о контексте, в котором вводится выражение.
2 Поле выражения находится в верхней части Построителя выражений. Его можно заполнять: Ø вручную, Ø с помощью технологии Intelli. Sense, Ø выбирать элементы из трех списков элементов, расположенных под полем выражения. (Элементы выражения, Категории выражения, Значения выражения)
3 Элементы выражений В списке поля выводятся элементы верхнего уровня, доступные для построения выражения: Ø объекты базы данных, Ø функции, Ø константы, Ø операторы, Ø общие выражения и др. Содержимое этого списка зависит от текущего контекста.
4 Категории выражений В списке Категории выражений (средний столбец содержатся конкретные элементы или категории элементов для элемента, выбранного в списке «Элементы выражений» (левый столбец). Например, если в списке Элементы выражений выбрать пункт Встроенные функции, то в списке Категории выражений будут выведены категории функций.
5 Значения выражений В списке Значения выражений выводятся значения (если они есть) для элементов и категорий, выбранных в левом и среднем списках. Например, если в левом списке выбрать пункт Встроенные функции, а в среднем – категорию функций, то в списке Значения выражений будут выведены все встроенные функции для выбранной категории.
Скачать презентацию Технология работы с базами данных ЗАПРОСЫ Вопросы
Технология работы с базами данных_Запросы.ppt