УМКД. БД. и эксперт УМКД+. Учебнометодический комплекс по дисциплине базы данных и экспертные системы
 Скачать 1.37 Mb.
|
|
Тема: Запросы с использованием единственной таблицы. Использование предложения SELECT. При получении данных с использованием предложения SELECT и одной единственной таблицы, в результате будет 1 таблица. К полученной таблице можно снова применять SELECT. Предложение SELECT можно использовать как: Самостоятельная команда на получение и вывод строк таблицы, которая сформирована из столбцов и строк одной или нескольких таблиц. Предложение SELECT может использоваться как WHERE или HAVING условий и тем самым будет реализовываться вложенный запрос. Предложение SELECT может использоваться как фраза выбора в командах CREATE VIEW, DECLARE CURSOR, INSERT. Предложение SELECT может использоваться как средство присвоения глобальным переменным строк из сформированной таблицы. INTO – фраза. Выборка без использования фразы WHERE (простая выборка). Пансионат. Выдать запрос на выдачу названия, статуса и адреса поставщиков. SELECT название, статус, адрес FROM Поставщики; SELECT * FROM Поставщики; * указывает на то, что необходимо выбрать все имена полей таблицы, имя которых указано в фразе FROM и в том порядке, как они описаны в таблице. Если необходимо выбрать основу всех блюд – SELECT основа FROM блюда; Для того, чтобы исключить дублирование и одновременно упорядочить перечень, необходимо дополнить запрос ключевым словом DISTINCT - SELECT DISTINCT Основа FROM блюда; Вывод вычисляемых значений. Если необходимо получить значение калорийности всех продуктов, то надо сформулировать выражение, в котором идет пересчет. Надо учесть, что при расщеплении 1г углеводов или белков в организме выделяется 1,4 кКл, жиров – 9,3 кКл. SELECT Продукты, ((Белки + Углеводы) * 4,1 + Жиры * 9,3) FROM Продукты;
SELECT Продукты “Калорий =”, ((Белки + Углеводы) * 4,1 + Жиры * 9,3) FROM Продукты; В том случае, если какой-либо член выражения не определен, т.е. имеет значение «Нуль», то СУБД сама присваивает этому полю значение NULL. Его код воспроизводится специальным образом (символом/набором символов).
Выборка с использованием фразы WHERE. Использование операторов сравнения. Для получения перечня продуктов, не содержащих углеводов, сформируем запрос: SELECT Продукт, Белки, Жиры, Углеводы, К, Na… FROM Продукты WHERE Углеводы = 0 And Na = 0 And Продукт <> ‘Судак’; Можно использовать логические операции AND, OR, AND NOT, OR NOT. Использование этих операций позволяет проводить более детальный отбор строк. Использование BETWEEN … AND … Использование «В интервале от … до …», в котором находятся строки, можно отобрать строки, в которых значения какого-либо столбца находятся в заданном диапазоне. Например, выдать перечень продуктов, в которых значение белка находится в диапазоне от 10 до 50. SELECT Продукты, Белки FROM Продукты WHERE Белки BETWEEN 10 AND 50; BETWEEN удобен при работе, задаваемые интервалы которых (начало и конец которых) расположены в разных столбцах.
Лекция №13 Тема: Использование IN в SELECT WHERE
SELECT * FROM Блюда WHERE Основа IN (Крупа, Овощи, Мясо); Форма IN является краткой записью в последовательности отдельных сравнений, соединенных оператором ИЛИ (OR). SELECT * FROM Блюда WHERE основа = Овощи OR Основа = Крупа; Форма NOT IN. Выдает с отсутствием этих компонентов – SELECT * FROM Блюда WHERE Основа NOT IN (Крупа, Овощи, Мясо); Форма LIKE. Позволяет отыскивать все значения указательного столбца, которые соответствуют образцу, заданному текстовой константой. SELECT Блюдо FROM Блюда WHERE Блюдо LIKE ‘Салат%’; Символы текстовой константы в форме LIKE интерпретируются следующим образом: 1 – Символ подчеркивания «_» в текстовой константе заменяет любой одиночный символ. 2 – «%» заменяет любую последовательность из N символов. При этом N может быть равно 0. Форма NULL. Неопределенное значение. Если при загрузке данных не введено значение в какое-либо поле таблицы, СУБД поместит в него NULL значение. Аналогичное значение можно ввести в поле таблицы, выполняя операцию изменения данных. SELECT DISTINCT ПР FROM Наличие WHERE Количество IS NULL; Используются условия Столбец IS NULL или Столбец IS NOT NULL. Следует помнить, что 0=0, NULL  NULL.Выборка с упорядочиванием. Простейший вариант этой фразы – упорядочивание строк результата по значению одного из столбцов с указанием порядка сортировки или без указания.
SELECT Продукт, Белки, Жиры, Углеводы FROM Продукты ORDER BY Белки DESC; При этом надо отметить, что сортировка будет осуществляться по значению первого столбца. При совпадении нескольких значений в этом столбце, сортировка будет осуществляться по следующему столбцу. Кроме того, в список ORDER BY можно включать не только имя столбца, но и его порядковую позицию в функции SELECT. Агрегирование данных В SQL существует ряд стандартных специальных функций, и кроме случая COUNT (*), каждая из этих функций оперирует совокупностью значений столбца этой таблицы и создает единственное значение, определяемое следующим образом: COUNT – число значений в таблице. SUM – сумма значений в столбце. AVG – среднее значение в столбце. MAX – максимальное значение в столбце. MIN – минимальное значение в столбце. Эти столбцы должны быть числовыми! Следует отметить, что в этом случае столбец – это столбец виртуальной таблицы, в которой могут содержаться значения не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования И (ИЛИ) связывания символами арифметических операций, значений из одного или нескольких столбцов. При этом выражение, определяющее столбец в такой таблице может быть сколь угодно сложным, но не должно содержать SQL-функций. То есть SQL-функции не допускаются. Аргументом всех функций кроме COUNT (*) может предшествовать ключевое слово DISTRICT, которое указывает, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться специальная функция. Специальная функция COUNT (*) служит для подсчета всех без исключения строк в таблице. Функции без использования GROUP BY Если не используется фраза GROUP BY, то в перечень элементов SELECT можно включать лишь SQL-функции, или выражения, которые содержат такие функции. Другими словами нельзя иметь списки-столбцы, которые не являются SQL-функциями.
SELECT SUM(Количество), COUNT (Количество) FROM Поставки ЦРУКУ ПР=10; Если для вывода в результат необходимо включить и номер продукта, то необходимо сформировать запрос следующего вида: SELECT ПР, SUM(Количество), COUNT (Количество) FROM Поставки ЦРУКУ ПР=10; Мы получили бы сообщение об ошибке. Это связано с тем, что SQL-функция создает одно единственное значение и для «свободного» столбца должно было быть выдано всё множество его значений. Без специального указания, которое задается фразой GROUP BY, SQL не будет выяснять одинаковые ли значения этого множества, или различные. В столбце-аргументе перед употреблением любой функции, исключаются все неопределенные значения. Если оказывается, что аргумент есть пустое множество, COUNT принимает значение 0, а остальные – NULL. Например: Для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных каким-либо кооперативом, а также для получения количества продуктов, которые могут поставляться этим кооперативом, необходимо составить следующий запрос: SELECT SUM(Цена), AVG(Цена), COUNT(Цена), COUNT(DISTINCT Цена), COUNT (*) FROM Поставки WHERE ПС=5;
Пусть требуется вычислить общую массу продуктов, которые поставляются поставщиками. SELECT ПР, SUM(Количество) FROM Поставки GROUP BY ПР; Фраза GROUP BY инициирует перекомпоновку указанной в операторе FROM таблице по группам, каждая из которых имеет одинаковое значение в столбце, который указан в GROUP BY. Лекция № 14 Тема: Автоматизация приложений с помощью Visual Basic VBA имеет отличный от обычного VB синтаксис. Многочисленные средства Access, предназначенные для интерактивной разработки БД и решения задач обработки данных является основной для построения практических приложений пользователя. Однако, если в приложениях необходимо отслеживать и обрабатывать ошибки, создавать свои функции, в ходе выполнения приложений создавать объекты, работать с отдельными записями, а не с наборами записей, необходимо использовать вшитый в Access объектно-ориентированный язык программирования VBA. VBA позволяет создавать в среде Access диалоговые приложения пользователя, которые предоставляют ему широкие возможности для управления и контроля за ходом выполнения прикладных задач. Приложения, которые разрабатываются в VBA, могут выполняться только в той среде, в которой может поддерживаться VBA. VBA обеспечивает простоту использования объектов, которые создаются средствами Access. Поэтому при разработке приложений, как правило, программируются только те функции, которые не могут быть реализованы средствами Access. Основу ООП составляет работа с объектами. Основными объектами являются: таблица, запрос, форма, отчет, макрос, модуль, страница доступа. Эти объекты строятся на основе мелких объектов элементов управления, которые объединяются в более крупные объекты – семейства, то есть набор однотипных объектов. Все объекты имеют сохраняемый набор свойств, изменяя которые можно управлять объектами. С каждым объектом связывается и ряд методов выполняемых инструкций. Один и тот же метод может использоваться для разработки различных объектов. Все многообразие свойств методов обработки сводится в иерархические структуры. Основные иерархические структуры объектов, которые используются в Access: Модель объектов доступа к данным. DAO – Data Access Object, которая обеспечивает доступ и обработку данных в БД, управление БД и их объектами с помощью свойств и методов, а также позволяет создавать новые объекты и изменять структуру БД. Модель объектов в Access, которая используется для работы с формами, отчетами, модулями выполняющегося приложения Access, объектами других приложений, их свойствами и методами, а также для управления текущим сеансом работы с Access и создание интерфейса приложения. В программах необходимо правильно указывать местоположение объекта в иерархии модели объектов. При создании новых объектов нужно размещать в соответствующих семействах. Сохранение ссылок на объекты в объектных переменных, которые будут использоваться в инструкциях программы. При работе с объектами возникают события, определенные в Access. Обычно события возникают в результате действий пользователя. Для каждого события может быть предусмотрена процедура обработки данного события. И на этом механизме строится большая часть пользовательских приложений, поскольку основой диалогового режима являются формы и отчеты. Программы, реализующие данные приложения, называются событийно-управляемыми, т.е. они выполняются в ответ на события, которые инициируются пользователем при работе с формами, отчетами и элементами их управления. В таком приложении пользователь или некоторое системное событие запускает процедуру обработки событий, которая написана на языке VBA. Порядок выполнения программ зависит от порядка возникновения событий. В простых приложениях в программе на VBA ограничиваются созданием процедур обработки событий и процедур функций, которые используются в выражении. Программа на VBA записывается как совокупность процедур типа Sub и Function. Процедура состоит из последовательности инструкций и методов, с помощью которых решаются задачи, требуемые действия или производятся расчеты. Процедуры, входящие в состав приложения, хранятся в модулях в БД, однако запросить выполнение модуля в целом невозможно. Выполняться могут только процедуры, содержащиеся в модуле. Модули содержат набор процедур и описание переменных, констант и объектов, которые используются в процедурах. Для удобного объединения процедур по их функциональному назначению или привязке к форме или отчету, модули можно разделить на 3 вида: Стандартные. Модули форм. Модули отчетов. Стандартные модули являются отдельными объектами БД и представлены в окне БД на вкладке «Модули». Процедуры стандартного модуля могут быть общими (Public) и личными (Private). По умолчанию процедуры являются общими и доступны из любых других процедур БД. Модули конкретных форм и отчетов сохраняются с конкретной формой и отчетом. Любая форма или отчет могут содержать один модуль. Такой модуль предназначен для сохранения процедур, связанных с обработкой событий форм или отчетов. Процедуры обработки событий – процедуры типа Sub. Они всегда являются личными и связаны с конкретным элементом и с конкретным событием. Наряду с процедурами обработки событий такой модуль может содержать обычные процедуры для выполнения типовых действий. Процедуры обработки событий можно создавать для каждого события, каждого элемента формы или отчета и для каждого из возможных событий самой формы или отчета. Процедура обработки событий имеет составное имя, включает в себя имя объекта (имя элемента) и имя события. Эта процедура выполняется при наступлении в объекте того события, с обработкой которого она связана. Событие может быть вызвано системой, программой или действиями пользователя. Чтобы имеющаяся процедура выполнялась при возникновении события для некоторого элемента, в его свойствах для данного события должно быть установлено значение процедуры обработки события. Модуль формы или отчета создается автоматически при создании первой процедуры обработки события в форме или отчете либо при выполнении команды Вид | Программы. Модуль существует до тех пор, пока существует форма или отчет. Он копируется и удаляется вместе с ними. Для разработки и отладки программ на VBA предназначен редактор, в котором в виде проекта собраны все модули, существующие в БД. В окне проекта отображаются формы или отчеты, с которыми связаны процедуры обработки событий и стандартные модули. При этом обеспечивается обзор всех программ, составляющих проект. Программы на VBA создаются не только пользователем, но и автоматически при работе мастеров Access. П  усть в форме для просмотра информации о группе по желанию пользователя можно открывать список студентов группы. Рассмотрим, каким образом это будет выглядеть на VBA.1. Создадим форму «Группа» на основе одноименной таблицы, воспользовавшись режимом «автоформы в столбец», а также форму «Студент», используя «автоформу ленточную». 2. откроем форму «Группа» в режиме конструктора и добавим кнопку для открытия формы «Список Студентов». Для отображения в открываемой форме только связанных записей, надо при создании кнопки выбрать в окне мастера строку «открыть форму для отображения записей» и выбрать ‘ИГ’. По этому полю осуществляется связь форм и отбор записей подчиненной формы. Кнопке даем название «Список Студентов». Процедура, сформированная мастером для открытия формы «Студент» имеет стандартную структуру, в которую включены: Инструкция по перехвату ошибок; Описаны переменные, им присвоенные значения; Выполнены основные действия; Вставлена программа обработки ошибок. Private Sub CC_Click() On Error GoTo Err_CC_Click Dim StDocName As String Dim StLinkCriteria As String StDocName=”Студент” StLinkCriteria=”[НГ]” &” ’ “ &Me![НГ]&” ‘ DoCmd.OpenForm.StDocName , , , StLinkCriteria Exit CC_Click: Exit Sub Err_CC_Click: MsgBox Err.Description Resume Exit CC_Click Exit Sub Процедура обработки событий является личной. Инструкция Sub объявляет процедуру – подпрограмму с составным именем CC_Click (название кнопки и имя события). DoCmd.OpenForm.StDocName,,,StLinkCriteria реализует главную функцию процедуры – открывает форму «Студент». DoCmd – является объектом активного приложения Access, предназначенным для выполнения макрокоманд. OpenForm – метод, применяемый к данному объекту и реализующий макрокоманду «Открыть форму». Методу передаются 2 аргумента: Первый аргумент задает имя открываемой формы. Второй передает строковое выражение, которое задает условие отбора записей в открываемой форме. В данной инструкции для хранения значения аргументов используются переменные строкового типа. Прежде чем использовать эти 2 переменные, мы должны присвоить им первоначальные значения. Для ссылок на элементы текущей формы или отчета в процедурах VBA используется ключевое слово Me, заменяющее полный идентификатор текущего объекта. Аргументы инструкции разделяются запятыми. Аргументы до Cmd.OpenForm могут отсутствовать и заменяться запятыми. В конце инструкции отсутствие недостающих аргументов не помечается. Е  сли при работе с формой «Студент» необходимо установить режим, который обеспечивает только просмотр данных, необходимо записать 5м аргументом в инструкции acFormReadOnly. Приставка ас указывает на то, что в качестве агрумента используется значение встроенной константы Access. |