- •Базы данных
- •Введение
- •Часть 1. Проектирование баз данных
- •1.1. Некоторые понятия и определения
- •1. 2. Модели данных
- •1.2.1. Иерархическая модель данных
- •1.2.2. Сетевая модель данных
- •1.2.3. Реляционная модель данных Основные определения
- •Типы связей между отношениями
- •1.3. Классификация баз данных
- •1.4. Цели проектирования баз данных
- •1.5. Проектирование баз данных с использованием универсального отношения
- •1.5.1. Универсальное отношение
- •1.5.2. Проблемы, вызываемые использованием универсального отношения
- •Проблема вставки
- •Проблемы обновления
- •Проблемы удаления
- •1.5.3. Нормальная форма Бойса -Кодда
- •Функциональные зависимости
- •Возможный ключ и детерминант
- •Общий подход к декомпозиции
- •Анализ исходных аномалий
- •1.5.4. Возможные потери фз при декомпозиции
- •1.5.5. Избыточные функциональные зависимости
- •Приемы удаления избыточных фз
- •Минимальное покрытие
- •Модернизированный алгоритм проектирования бд
- •1.5. Метод er - проектирования
- •1.5.1. Сущности и связи
- •1.5.2. Степень связи
- •1.5.3. Переход от диаграмм er – типа к отношениям
- •Предварительные отношения для бинарных связей степени 1:1
- •Предварительные отношения для бинарных связей степени 1:n.
- •Предварительные отношения для бинарных связей степени n:m
- •1.5.4. Дополнительные конструкции, используемые в er - методе
- •Необходимость связей более высокого порядка
- •Предварительные отношения для трехсторонних связей
- •Использование ролей
- •1.5.5. Последовательность проектирования бд при использовании er- метода
- •1.5.5. Проверка отношений на завершающейся фазе проектирования
- •1.7. Другие нормальные формы
- •Часть 2. Специальные аспекты работы с базами данных
- •2.1. Защита данных в базе
- •2.2.1. Общие вопросы защиты данных
- •2.2.2. Реализация защиты данных в различных системах
- •Управление доступом в sql
- •Реализация системы защиты в ms sql Server
- •2.2. Обеспечение целостности данных
- •2.3. Организация параллельных процессов обработки данных
- •2.4. Восстановление бд
- •2.4.1. Уровни восстановления.
- •2.4.2. Восстановление и логический элемент работы
- •Требования к лэр
- •2.4.3. Промежуточное восстановление
- •2.4.4. Длительное восстановление
- •2.5. Математический аппарат, используемый при работе с реляционной базой данных
- •2.5.1. Теоретико-множественные операции реляционной алгебры
- •2.5.2. Специальные операции реляционной алгебры
- •Часть 3. Разработка приложений для работы с базами данных
- •3.1. Краткий обзор субд
- •3.2. Субд Access
- •3.2.1. Вводные замечания
- •3.2.2. Создание базы данных
- •3.2.3. Создание и работа с таблицами
- •3.2.4. Работа с запросами
- •3.2.5. Создание форм
- •3.2.5. Отчеты в Access
- •3.2.7. Макросы в Access
- •Преобразование макросов в программы на Visual Basic
- •3.2.8. Работа с внешними данными
- •3.3. Программирование в Access
- •3.3.1. Вводные замечания
- •3.3.2. Объявление переменных
- •3.3.3. Константы
- •3.3.4. Тип данных Variant
- •3.3.5. Пользовательские типы данных
- •3.3.5.Операторы, команды и выражения в vba
- •3.3.7. Процедуры vba
- •3.3.8. Управляющие структуры в vba
- •Работа с управляющими структурами
- •3.3.9. Объекты в Access
- •3.3.10. Классы в Access
- •3.3.11. Работа с ошибками в vba
- •3.4.Работа в ms sql –Server
- •3.4.1. Основные количественные показатели системы sql-сервер
- •3.4.2. Создание баз данных
- •3.4.3. Создание таблицы
- •3.4.4. Извлечение данных
- •3.4.5. Добавление данных
- •3.4.5. Изменение данных
- •3.4.7. Удаление данных
- •Цитированная литература
- •Оглавление
- •Часть 1. Проектирование баз данных 3
- •Часть 2. Специальные аспекты работы с базами данных 70
- •Часть 3. Разработка приложений для работы с базами данных 113
3.2.4. Работа с запросами
Вопросы, формулируемые по отношению к базе данных, называются запросами.
Запросы предназначены для просмотра, изменения и анализа данных. Они используются также в качестве источника записей при создании форм и отчетов. Одним из наиболее распространенных запросов является запрос на выборку, который выполняет отбор данных из одной или нескольких таблиц в соответствии с заданными пользователем критериями.
В Access, как и во многих современных СУБД, для формирования запросов используется "Запрос по образцу" (Query by Example или QBE).
Запрос по образцу - это таблично ориентированный язык для выбора данных из одной или нескольких таблиц.
Для создания запроса необходимо открыть базу данных, в окне базы перейти на вкладку Запросы и щелкнуть по кнопке Создать.
На экране появится диалоговое окно Новый запрос, предназначенное для выбора способа построения запроса. Могут быть использованы следующие способы создания запросов:
Конструктор – самостоятельное создание нового запроса;
Простой запрос – создание запроса на выборку из определенных полей;
Перекрестный запрос – создание запроса, выводящего данные в компактной форме, подобном формату электронной таблицы;
Повторяющиеся записи – создание запроса на поиск повторяющихся записей в простой таблице или запросе;
Записи без подчиненных – создание запроса на поиск записей, которым не соответствует ни одна запись в подчиненной таблице.
Мастер Простой запрос служит для создания простых запросов. Запрос создается при помощи Мастера по шагам. При этом имеется два варианта запроса: в запрос включаются все поля таблицы, включая и поля связанных между собой таблиц; в запрос включаются итоговые значения из таблицы (например, подсчитаны суммы по записям, определены средние арифметические и т.д.). Выбор варианта запроса производится на втором шаге Мастера установкой флажка Подробный или Итоговый.
Мастер Перекрестного запроса работает тоже по шагам, создавая перекрестный запрос. Такой запрос – это операция построения таблицы для вычисления итоговых значений на основе существующей таблицы или запроса. Перекрестный запрос создается в предположении, что исходная таблица содержит необходимые данные для формирования заголовков строк и столбцов.
Запросы для выборки повторяющихся записей и записей, которым не соответствует ни одна запись в подчиненной таблице не требуют особых пояснений. Смысл их очевиден из названия.
Конструктор запросов является основным средством по работе с запросами, поэтому рассмотрим работу с ним.
.Для запуска его необходимо выбрать значение Конструктор в окне Новый запрос и нажать на кнопку ОК.
В результате на экране появятся два окна: окно конструктора запросов Запрос на выборку и окно выбора таблиц Добавление таблицы.
Окно Добавление таблицы состоит из трех вкладок, содержащих перечни объектов предлагаемых программой для проектирования запроса: Таблицы, Запросы и Таблицы и запросы. Выбрав соответствующую вкладку необходимо выбрать нужную таблицу и затем щелкнуть по кнопкам Добавить и Закрыть.
В окне конструктора запросов появится небольшое окно с заголовком таблицы и списком ее полей.
Для переноса нужного поля в бланк запроса необходимо выполнить двойной щелчок на нем. В результате имя этого поля будет помещено в строку Поля бланка запроса (нижняя половина окна конструктора запросов). В бланке запроса указываются параметры запроса и данные, которые нужно отобрать, а также определяется способ их отображения на экране.
Имя поля (и даже несколько маркированных имен) можно перенести в бланк запроса методом Drag and Drop.
Для упорядочения отобранных записей по алфавиту целесообразно воспользоваться возможностями сортировки самого запроса. Для этого в строке Сортировка нужно щелкнуть в первой колонке, открыть список значений и задать способ сортировки данных.
Если больше никаких условий не требуется, то создание запроса можно завершить, закрыв окно конструктора запросов и присвоив запросу имя, в появившемся окне Сохранение .
Имя сохраненного запроса программа включает в список, находящийся на вкладке Запросы окна базы данных. Результат выполнения запроса отображается после нажатия кнопки Открыть в окне базы данных или кнопки Запуск (кнопка с изображением восклицательного знака) на панели инструментов Создание запросов, а также после двойного щелчка на имени запроса. При этом не имеет значения, открыта таблица с данными или нет.
В таблице с результатами выполнения запроса данные обрабатываются так же, как и в обыкновенной таблице. При этом можно использовать команды как из контекстного меню окна запроса, так и из меню Формат.
Access позволяет отменить отображение полей в запросе еще при его создании. Для этой цели предназначены контрольные индикаторы в строке Вывод на экран бланка запроса. После выполнения запроса на экране отображаются только те поля, для которых в бланке установлены флажки.
Сортировка данных в запросе. В Access существует несколько способов сортировки данных, отобранных посредством запроса. Быстро выполнить сортировку в окне запроса позволяют команда Сортировка из меню Записи, а также кнопки По возрастанию и По убыванию панели инструментов. Для этого следует включить в запрос поля таблицы, по которым будут рассортированы записи, и определить способ сортировки - по возрастанию или по убыванию. Данные можно упорядочить по алфавиту, а также по убыванию или возрастанию. При алфавитно-цифровой сортировке по возрастанию данные сортируются в таком порядке: сначала - элементы, начинающиеся со знаков пунктуации или специальных символов, затем - элементы, начинающиеся с цифр, а затем - элементы, которые начинаются с букв.
Сортировка данных в таблице по содержимому только одного столбца не всегда приводит к желаемым результатам, поэтому иногда требуется осуществлять сортировку по содержимому нескольких полей.
Чтобы ускорить сортировку, следует размещать рядом поля, данные которых необходимо рассортировать.
Через окно запроса в исходную таблицу можно вставить новые записи, как при заполнении таблицы. Добавляемые или изменяемые данные помещаются в таблицу, на основе которой создан запрос. В результате ввода новых данных в таблицу может нарушиться порядок следования данных в запросе. Для восстановления порядка необходимо повторить вызов запроса и сортировку данных.
Выполнить сортировку данных можно и в окне конструктора запросов. Для этого необходимо нажать кнопку Открыть на вкладке Запросы окна базы данных. В окне выбора таблиц отметить имя таблицы, в которой следует выполнить сортировку данных, после чего нажать кнопки Добавить и Закрыть. Отметить в списке все имена полей, дважды щелкнув на заголовке списка, и переместить его в QBE-область. Для нужного поля задать в строке Сортировка способ сортировки. Сохранить запрос.
Применение специальных критериев. До сих пор мы рассматривали запросы, выполняющие отбор полей таблицы. При создании запроса можно задать дополнительные критерии, вследствие чего он будет осуществлять отбор только нужных сведений в каждом поле. Для формирования такого запроса необходимо в QBE-области ввести значение в ячейку, расположенную на пересечении строки Условия отбора и колонки с нужным именем.
Критерии, устанавливаемые в QBE-области, необходимо заключать в кавычки. Если Access идентифицирует введенные символы как критерий отбора, то заключает их в кавычки автоматически, а если нет, то сообщает о синтаксической ошибке. Программа Access не может распознать как критерий комбинацию символов и знаков подстановки.
В запросах символы подстановки * и ? применяются так же, как и во всех приложениях Microsoft Office. Символ звездочки заменяет любое количество букв или цифр, а знак вопроса - только один символ.
Все строки в QBE-области, расположенные под строкой Условия отбора, служат для задания критериев отбора. Таким образом, для одного поля можно определить два, три и более критериев отбора данных. По умолчанию все элементы критерия объединяются оператором ИЛИ. Это значит, что запрос выберет те записи, которые соответствуют хотя бы одному критерию.
Чтобы объединить несколько условий отбора оператором И, следует привести их в одной строке. Например, если необходимо выбрать записи из таблицы Зарплата, в которых указаны оклады от 1000 до 1500, в столбце Оклад надлежит ввести следующий критерий: Between 1000 and 1500. Другой формой записи этого критерия является выражение >1000 And <1500.
В результате будут отобраны только те записи, которые удовлетворяют обоим условиям, т.е. оклады, значения которых находятся в интервале от 1000 до 1500.
Если необходимо отобрать несколько диапазонов значений критерий для каждого диапазона следует указать в отдельной строке.
Исключить группу данных из состава анализируемых запросом записей (например, оклад 1100) позволяет следующий критерий: Not 1100. Другая форма записи этого критерия: <>1100.
В этом случае можно не использовать кавычки.
Операторы And и Or применяются как отдельно, так и в комбинации. Следует помнить, что условия, связанные оператором And, выполняются раньше условий, объединенных оператором Or.
Вычисления в запросе. Запросы становятся еще эффективнее, если в них включены вычисления. Над полученными в результате выполнения запроса данными можно провести вычисления, используя предоставляемые Access функции.
Запросы к нескольким таблицам. Запросы можно создавать для отбора данных как из одной, так и из нескольких таблиц. При этом следует учитывать наличие связей между таблицами.
Модель реляционной базы данных работает правильно лишь в том случае, если корректно установлено отношение между полями таблиц базы. Обязательным условием установки связи между таблицами является наличие двух одинаковых полей.
Для установки связи между полями таблиц необходимо выполнить следующие действия:
- Закрыть все таблицы, между которыми существует связь.
- Активизировать команду Схема данных в меню Сервис или нажать соответствующую кнопку на панели инструментов.
- Если вместо диалогового окна Добавление таблицы откроется окно Схема данных (это может произойти при наличии связей между таблицами, установленных ранее), необходимо выбрать в меню команду Добавление таблицы или нажать соответствующую кнопку на панели инструментов.
- Выбрать нужную таблицу в списке, расположенном на вкладке Таблицы или Таблицы и запросы, и нажать кнопку Добавить. Затем добавить другую таблицу и нажать кнопку Закрыть. В окне Схема данных появятся два небольших окна со списками полей выбранных таблиц.
- С помощью мыши перетащить нужное поле из списка полей первой таблицы к соответствующему полю второй таблицы. Ключевое поле, принадлежащее главной таблице выделяется в списке жирным шрифтом.
- На экране появится диалоговое окно Связи, в котором отображаются имена связываемых полей обеих таблиц.
При необходимости их можно изменить.
- Активизировать опцию Обеспечение целостности данных. При этом будет установлено отношение One-To-Many (один ко многим), что отразится в области Тип отношения. Это означает, что одной записи главной таблицы могут быть поставлены в соответствие несколько записей подчиненной таблицы. Такое отношение является наиболее распространенным в реляционных базах данных.
- В заключение нажать кнопку Создать. В окне Связи программа графически обозначит связь между полями таблиц.
Запрос для таблиц, между которыми установлена связь, создается следующим образом:
- Открыть окно конструктора запросов, нажав кнопку Создать на вкладке Запросы окна базы данных.
- Выполнить двойной щелчок на имени главной таблицы, в диалоговом окне Запрос на выборку, вследствие чего она будет помещена в QBE-область.
- Повторить эту операцию для подчиненной таблицы.
- Закрыть диалоговое окно Добавление таблицы посредством кнопки Закрыть.
- Включить в запрос нужные поля.
- Запустить запрос. Благодаря однозначности отношения между полями таблиц осуществляется корректное сопоставление записей, идентифицируемых посредством поля соединенного поля.