Лекция 2 Работа с субд ms access
| Вид материала | Лекция |
- Лабораторная работа по дисциплине «Компьютерные технологии в науке и производстве», 77.14kb.
- Введение в access, 119.48kb.
- Лекция № Современные технические и программные средства информационных технологий, 47.37kb.
- Лекция №1: Стандарты языка sql, 1420.56kb.
- План: Основные сведения, 549.41kb.
- Методическая разработка по дисциплине «Информатика» Тема: Система управления базами, 698.73kb.
- Тема. Поняття про бази даних. Системи управління базами даних (субд) Мета, 35.04kb.
- Комплекс лабораторных работ в среде access microsoft Access является настольной субд, 469.78kb.
- Курсовая работа, 52.16kb.
- Методические указания к курсовой работе по предмету «Организация баз данных», 59.32kb.
Лекция 2_9. Работа с СУБД MS Access.
Содержание темы: Табличные фактографические базы данных в СУБД Access. Таблицы, поля, записи: типы и особенности полей, длина записей. Объекты OLE. Маски ввода. Форма (регистрационная карточка документа). Схема данных. Информационный поиск в БД. Понятие запроса. Стратегии поиска и технологии модификации запросов. Форма представления критерия отбора. Основные сведения о SQL. Расширенное логическое выражение, QBE, поиск по подобию. Основные математические функции. Выборка записей по запросу. Простые и сложные запросы. Формы и отчеты. Создание и использование. Другие СУБД (FoxPro, Oracle).
В фактографических ИС регистрируются факты – конкретные значения данных (атрибутов) об объектах реального мира. Основная идея таких систем заключается в том, что все сведения об объектах (фамилии людей и названия предметов, числа, даты) сообщаются компьютеру в каком-то заранее обусловленном формате (например дата – в виде комбинации ДД.ММ.ГГГГ). Информация, с которой работает фактографическая ИС, имеет четкую структуру, позволяющую машине отличать одно данное от другого, например фамилию от должности человека, дату рождения от роста и т.п. Поэтому фактографическая система способна давать однозначные ответы на поставленные вопросы.
Таблицы – фундаментальные объекты реляционной базы данных, в которых хранится основная часть данных приложения. Отдельная таблица чаще всего хранит информацию по конкретной теме (например, сведения о служащих компании или адреса заказчиков). Информация в таблице организуется в строки (записи) и столбцы (поля). Таблице присущи два компонента: структура таблицы и данные таблицы.
Структура таблицы (также называется определением таблицы) - определяет, какие данные таблица будет хранить, а также правила, ассоциированные с вводом, изменением или удалением данных (бизнес-правила, или ограничения). Структура таблицы доступна через окно конструктора таблиц. Чтобы открыть это окно для существующей таблицы, нужно открыть вкладку Таблицы окна базы данных, выбрать таблицу и нажать кнопку Конструктор.
Данные таблицы – информация, которая сохранена в таблице. Все данные таблицы хранятся в строках, каждая из которых содержит порции информации в столбцах, определенных в структуре таблицы. Данные – та часть таблицы, к которой обычно должны иметь доступ пользователи приложения (например, данные таблицы могут выводиться в элементах управления, размещенных в формах и отчетах, либо предоставляться в режиме таблицы).
Ниже приводятся некоторые базовые принципы разработки таблиц.
- Необходимо избегать дублирования информации. Для каждой категории информации следует использовать отдельную таблицу. Процесс проектирования таблиц, позволяющий исключать дублирование данных, называется нормализацией. В Access процедуры нормализации помогает выполнить мастер анализа.
- Не следует хранить значения, которые могут быть легко вычислены из существующих значений. Например, не нужно хранить сумму всех позиций товарного заказа, так как ее можно вычислить с помощью простой формулы.
- Для полей необходимо выбирать подходящий тип данных. Это поможет уменьшить размеры базы данных и увеличит скорость выполнения операций. При описании поля следует устанавливать для него тип данных наименьшего размера, позволяющий хранить нужные данные.
- В каждой создаваемой таблице нужно стараться включать столбец или набор столбцов первичного ключа. Первичные ключи необходимы для установления между таблицами отношений один-к-многим. Кроме того, многие базы данных поддерживают ограничения по первичному ключу, используя индекс, который может значительно повышать скорость поиска и операций сортировки. В составном ключе, включающем несколько полей, нужно использовать ровно столько полей, сколько для него необходимо.
При выборе типа данных, используемых в поле, необходимо учитывать следующее:
- какие значения должны отображаться в поле. Например, нельзя хранить текст в поле, имеющем числовой тип данных, и нецелесообразно хранить числовые данные в текстовом виде;
- сколько места необходимо для хранения значений в поле;
- какие операции должны производиться со значениями в поле. Например, суммировать
- значения можно в числовых полях и в полях, имеющих валютный формат, а в текстовых полях и полях объектов OLE, – нельзя;
- нужна ли сортировка или индексирование поля. Сортировать и индексировать поля МЕМО, гиперссылки и объекты OLE невозможно;
- будут ли поля использоваться в группировке записей в запросах или отчетах. Поля МЕМО, гиперссылки и объекты OLE использовать для группировки записей нельзя;
- каким образом должны быть отсортированы значения в поле. Числа в текстовых полях сортируются как строковые значения (1, 10, 100, 2, 20, 200 и т. д.), а не как числовые значения. Для сортировки чисел как числовых значений необходимо использовать числовые поля или поля, имеющие денежный формат.
Свойство Размер поля определяет максимальный размер данных, которые могут сохраняться в полях с типом данных Текстовый, Числовой или Счетчик.
Если свойство Тип данных имеет значение "Текстовый", значением данного свойства должно быть целое число в диапазоне от 0 до 255. По умолчанию задается размер 50.
Если свойство Тип данных имеет значение "Счетчик", то допустимыми значениями свойства Размер поля будут "Длинное целое" или "Код репликации".
Если поле имеет тип данных "Числовой", то допустимыми являются следующие значения свойства Размер поля:
Объекты OLE (например, документы Microsoft Word, электронные таблицы Microsoft Excel, рисунки, звуки и другие данные), созданные в других программах, использующих протокол OLE. Объекты могут быть связанными или внедренными в таблицу Microsoft Access. Для отображения объекта OLE в форме или отчете необходимо использовать элемент управления Присоединенная рамка объекта.
Маска ввода (Input Mask) позволяет контролировать ввод данных в таблицу. И не только контролировать, но и упрощать процесс ввода. Маску используют в том случае, когда вводимые данные должны содержать определенные символы в некоторых позициях вводимой строки.
Форма - в то время как таблицы и запросы позволяют отобразить на экране длинные списки записей, формы дают возможность сосредоточиться на конкретной записи. Они облегчают ввод, редактирование и восприятие информации, могут содержать вспомогательные подписи и элементы оформления.
Формами называются настраиваемые диалоговые окна, сохраняемые в базе данных в виде объектов специального типа. Формы Access являются объектами базы данных, так же как таблицы и запросы. Формы используются в приложении для ввода и отображения данных. Формами можно управлять программно с помощью процедур на VBA.
Формы предоставляют более удобный способ просмотра и правки данных в таблицах, чем режим/Таблицы. Формы содержат так называемые элементы управления, с помощью которых осуществляется доступ к данным в таблицах. Элементами управления являются текстовые поля для ввода и правки данных, кнопки, флажки, переключатели, списки, надписи, а также рамки объектов для отображения графики и объектов OLE. Создание форм, содержащих необходимые элементы управления, существенно упрощает процесс ввода данных и позволяет предотвратить ошибки.
Создание схемы данных
Создание схемы данных начинается в окне Базы данных (Database) с выполнения команды Сервис|Схема данных (Tools|Relationships) или нажатия кнопки Схема данных (Relationships) на панели инструментов базы данных.
Создание связей между таблицами. При определении связей в схеме данных удобно использовать информационно-логическую модель в каноническом виде, по которой легко определить главную и подчиненную таблицу каждой одно-многозначной связи, поскольку в такой модели главные объекты всегда размещены выше подчиненных. Эти связи являются основными в реляционных базах данных, т. к. одно-однозначные связи используются лишь в редких случаях, когда приходится разделять большое количество полей, определяемых одним и тем же ключом, по разным таблицам, имеющим разный регламент обслуживания.
Устанавливая в окне схемы данных связи типа 1:М между парой таблиц, надо выделить в главной таблице уникальное ключевое поле, по которому устанавливается связь. Далее, при нажатой кнопке мыши, протащить курсор в соответствующее поле подчиненной таблицы.
При создании связи по составному ключу необходимо выделить все поля, входящие в ключ главной таблицы, и перетащить их на одно из полей связи в подчиненной таблице. Для выделения всех полей, входящих в составной уникальный ключ, необходимо отмечать поля при нажатой клавише . После создания связи откроется окно Изменение связей (Edit Relationships). При этом в строке Тип отношения (Relationship Type) автоматически установится тип один-ко-многим (One-To-Many).
При составном ключе связи в окне Изменение связей (Edit Relationships) необходимо для каждого поля ключа в главной таблице ТАБЛИЦА/ЗАПРОС (Table/Query) выбрать соответствующее поле подчиненной таблицы, названной СВЯЗАННАЯ ТАБЛИЦА/ЗАПРОС (Related Table/Query).
Поиск информации - задача, которую человечество решает уже многие столетия. По мере роста объема информационных ресурсов, потенциально доступных одному человеку (например, посетителю библиотеки), были выработаны все более изощренные и совершенные поисковые средства и приемы, позволяющие найти необходимый документ.
Запросы являются инструментом поиска и структурирования данных. Запрос, адресованный одной или нескольким таблицам, инициирует выборку определенной части данных и их передачу в таблицу, формируемую самим запросом. В результате вы получаете подмножество информационного множества исходных таблиц, сформированное по определенному закону. Если обрабатываемый объем информации велик, выделение необходимых данных в такое подмножество позволяет существенно сократить время их обработки. В системах типа клиент-сервер, где основные базы данных хранятся на файловом сервере, система запросов позволяет уменьшить объем информации, передаваемой через локальную сеть.
Отчет
Одной из основных задач создания и использования баз данных является предоставление пользователям необходимой информации на основе существующих данных. В Access 2002 для этих целей предназначены формы и отчеты. Отчеты позволяют выбрать из базы данных требуемую пользователем информацию и оформить ее в виде документов, которые можно просмотреть и напечатать. Источником данных для отчета может быть таблица или запрос. Кроме данных, полученных из таблиц, в отчете могут отображаться вычисленные по исходным данным значения, например итоговые суммы.
Отчеты и формы Access имеют много общего. Однако, в отличие от форм, отчеты не предназначены для ввода и правки данных в таблицах. Они позволяют лишь просматривать и печатать данные. В отчете невозможно изменить исходные данные с помощью элементов управления, как это можно сделать с помощью форм. Хотя в отчетах можно использовать такие же элементы управления для указания состояния переключателей, флажков и списков.
Отчет, как и форма, может быть создан с помощью мастера. Разделы отчета подобны разделам формы и включают заголовок и примечание отчета, область данных, а также верхний и нижний колонтитулы. В примечание отчета часто помещают поля с итоговыми значениями. Элементы управления могут быть добавлены в отчет с помощью панели инструментов Панель элементов (Toolbox), идентичной той, что используется в режиме Конструктора форм. Форматирование и группировка элементов управления в отчете выполняются аналогично форматированию и группировке элементов управления в форме. Формы могут содержать подчиненные формы, а отчеты могут содержать подчиненные отчеты.
SQL (англ. Structured Query Language — язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. Вопреки существующим заблуждениям, SQL является информационно-логическим языком, а не языком программирования.
SQL основывается на реляционной алгебре.
Язык SQL делится на три части:
- операторы определения данных (Data Definition Language, DDL)
- операторы манипуляции данными (Data Manipulation Language, DML)
- операторы определения доступа к данным (Data Control Language, DCL)
Несмотря на наличие диалектов и различий в синтаксисе, в большинстве своём тексты SQL-запросов, содержащие DDL и DML, могут быть достаточно легко перенесены из одной СУБД в другую. Существуют системы, разработчики которых изначально закладывались на применение по меньшей мере нескольких СУБД (например: система электронного документооборота Documentum может работать как с Oracle Database, так и с Microsoft SQL Server и IBM DB2)
Наличие стандартов и набора тестов для выявления совместимости и соответствия конкретной реализации SQL общепринятому стандарту только способствует «стабилизации» языка.
С помощью SQL программист описывает только какие данные нужно извлечь или модифицировать. То, каким образом это сделать решает СУБД непосредственно при обработке SQL запроса.
Хотя SQL и задумывался как средство работы конечного пользователя, в конце концов он стал настолько сложным, что превратился в инструмент программиста.
Система SQL не предлагает стандартного способа манипуляции древововидными структурами. Некоторые поставщики СУБД предлагают свои решения. Например Oracle использует выражение «CONNECT BY».
ФУНКЦИИ SQL
- Математические функции
- Символьные функции
- Функции даты и времени
- Другие функции
Для SQL в стандарте ANSI вы можете применять агрегатные функции для столбцов или использовать их значения в скалярных выражениях, таких, например, как com * 100. Имеется много других полезных функций, которые вы, вероятно, встречали на практике.
Имеется список некоторых общих функций SQL, отличающихся от стандартных агрегатов. Они могут использоваться в предложениях SELECT - запросов точно так же, как агрегатные функции, но эти функции выполняются для одиночных значений, а не для групповых. В следующем списке они классифицированы согласно типам данных, с которыми они работают. Если нет примечаний, то переменные в этом списке стандартизированы для любого выражения значений соответствующего типа, которые могут быть использованы в предложении SELECT.
МАТЕМАТИЧЕСКИЕ ФУНКЦИИ
Эти функции применяются для работы с числами.
| ФУНКЦИЯ | ЗНАЧЕНИЕ |
| ABX(X) | Абсолютное значение X (преобразование отрицательного или положительного значения в положительное). |
| CEIL (X) | X является десятичным значением, которое будет округляться сверху. |
| FLOOR (X) | X является десятичным значением, которое будет округляться снизу. |
| GREATEST(X,Y) | Возвращает большее из двух значений. |
| LEAST(X,Y) | Возвращает меньшее из двух значений. |
| MOD(X,Y) | Возвращает остаток от деления X на Y. |
| POWER(X,Y) | Возвращает значение X в степени Y. |
| ROUND(X,Y) | Цикл от X до десятичного Y. Если Y отсутствует, цикл до целого числа. |
| SING(X) | Возвращает минус если X < 0, или плюс если X > 0. |
| SQRT (X) | Возвращает квадратный корень из X. |
СИМВОЛЬНЫЕ ФУНКЦИИ
Эти функции могут быть применены для строк текста из столбцов текстовых типов данных, либо из строк литерных текстов, или же из комбинации этих двух.
| ФУНКЦИЯ | ЗНАЧЕНИЕ |
| LEFT( | Возвращает крайние левые (старшие) символы X из строки. |
| RICHT( | Возвращает символы X младшего разряда из строки |
| ASCII( | Возвращает код ASCII, которым строка представляется в памяти компьютера. |
| CHR( | Возвращает принтерные символы кода ASCII. |
| VALUE( | Возвращает математическое значение для строки. Считается, что строка имеет тип CHAR или VARCHAR, но состоит из чисел. VALUE("3") произведёт число 3 типа INTEGER. |
| UPPER( | Преобразует все символы строки в символы верхнего регистра. |
| LOWER( | Преобразует все символы строки в символы нижнего регистра. |
| INlTCAP( | Преобразует начальные символы строки в заглавные буквы. В некоторых реализациях может иметь название PROPER. |
| LENGTH( | Возвращает число символов в строке. |
| | Объединяет две строки в выводе, так чтобы после первой немедленно следовала вторая. (значок || называется оператором сцепления). |
| LPAD( | Дополняет строку слева звездочками "*" или любым другим указанным символом в количестве, определяемом X. |
| RPAD( | То же самое что и LPAD, за исключением того, что дополнение делается справа. |
| SUBSTR( | Извлекает Y символов из строки, начиная с позиции X. |
ФУНКЦИИ ДАТЫ И ВРЕМЕНИ
Эти функции выполняются только для допустимых значений даты или времени.
| ФУНКЦИЯ | ЗНАЧЕНИЕ |
| DAY() | Извлекает день месяца из даты. Подобные же функции существуют для MONTH (МЕСЯЦ), YEAR (ГОД), HOUR (ЧАС), SECOND (СЕКУНДА) и так далее. |
| WEEKDAY() | Извлекает день недели из даты. |
ДРУГИЕ ФУНКЦИИ
Эта функция может быть применена к любому типу данных.
| ФУНКЦИЯ | ЗНАЧЕНИЕ |
| NVL( | NVL (NULL-значение) будет менять на |
Агрегатные функции позволяют выполнять вычисления над группой записей. Сервер поддерживает следующие функции:
- COUNT -- количество записей в выборке;
- SUM -- сумма значений (по числовой колонке);
- AVG -- среднее арифметическое;
- MIN, MAX -- минимальное и максимальное значение.
Операторы SQL
SQL содержит примерно 40 операторов для выполнения различных действий внутри СУБД. Ниже приводится краткое описание категорий этих операторов.
Data Definition Language (DDL)
Data Definition Language содержит операторы, позволяющие создавать, изменять и уничтожать базы данных и объекты внутри них (таблицы, представления и др.). Эти операторы перечислены в табл. 1.
Таблица 1
| Оператор | Описание |
| CREATE TABLE | Применяется для добавления новой таблицы к базе данных |
| DROP TABLE | Применяется для удаления таблицы из базы данных |
| ALTER TABLE | Применяется для изменения структуры имеющейся таблицы |
| CREATE VIEW | Применяется для добавления нового представления к базе данных |
| DROP VIEW | Применяется для удаления представления из базы данных |
| CREATE INDEX | Применяется для создания индекса для данного поля |
| DROP INDEX | Применяется для удаления существующего индекса |
| CREATE SCHEMA | Применяется для создания новой схемы в базе данных |
| DROP SCHEMA | Применяется для удаления схемы из базы данных |
| CREATE DOMAIN | Применяется для создания нового домена |
| ALTER DOMAIN | Применяется для переопределения домена |
| DROP DOMAIN | Применяется для удаления домена из базы данных |
Data Manipulation Language (DML)
Data Manipulation Language содержит операторы, позволяющие выбирать, добавлять, удалять и модифицировать данные. Обратите внимание на то, что эти операторы не обязаны завершать транзакцию, внутри которой они вызваны. Операторы DML представлены в табл. 2.
Таблица 2
| Оператор | Описание |
| SELECT | Применяется для выбора данных |
| INSERT | Применяется для добавления строк к таблице |
| DELETE | Применяется для удаления строк из таблицы |
| UPDATE | Применяется для изменения данных |
Иногда оператор SELECT относят к отдельной категории, называемой Data Query Language (DQL).
Transaction Control Language (TCL)
Операторы Transaction Control Language применяются для управления изменениями, выполненными группой операторов DML. Операторы TCL представлены в табл. 3.
Таблица 3
| Оператор | Описание |
| COMMIT | Применяется для завершения транзакции и сохранения изменений в базе данных |
| ROLLBACK | Применяется для отката транзакции и отмены изменений в базе данных |
| SET TRANSACTION | Применяется для установки параметров доступа к данным в текущей транзакции |
Data Control Language (DCL)
Операторы Data Control Language, иногда называемые операторами Access Control Language, применяются для осуществления административных функций, присваивающих или отменяющих право (привилегию) использовать базу данных, таблицы в базе данных, а также выполнять те или иные операторы SQL. Операторы DCL представлены в табл. 4.
Таблица 4
| Оператор | Описание |
| GRANT | Применяется для присвоения привилегии |
| REVOKE | Применяется для отмены привилегии |
Cursor Control Language (CCL)
Операторы Cursor Control Language используются для определения курсора, подготовки SQL-предложений для выполнения, а также для некоторых других операторов. Операторы CCL представлены в табл. 5.
Таблица 5
| Оператор | Описание |
| DECLARE CURSOR | Применяется для определения курсора для запроса |
| EXPLAIN | Применяется для описания плана запроса. Этот оператор представляет собой расширение SQL для Microsoft SQL Server 7.0. Он не обязан выполняться в других СУБД. Например, в случае Oracle следует использовать оператор EXPLAIN PLAN |
| OPEN CURSOR | Применяется для открытия курсора при получении результатов запроса |
| FETCH | Применяется для получения строки из результатов запроса |
| CLOSE CURSOR | Применяется для закрытия курсора |
| PREPARE | Применяется для подготовки оператора SQL для выполнения |
| EXECUTE | Применяется для выполнения оператора SQL |
| DESCRIBE | Применяется для описания подготовленного запроса |
Все операторы SQL имеют вид, показанный на рисунке.
Каждый оператор SQL начинается с глагола, представляющего собой ключевое слово, определяющее, что именно делает этот оператор (SELECT, INSERT, DELETE...). В операторе содержатся также предложения, содержащие сведения о том, над какими данными производятся операции. Каждое предложение начинается с ключевого слова, такого как FROM, WHERE и др. Структура предложения зависит от его типа — ряд предложений содержит имена таблиц или полей, некоторые могут содержать дополнительные ключевые слова, константы или выражения.
Системы управления базами данных (СУБД)
MySQL, Firebird, InterBase, PostgreSQL, MS SQL Server, SQLite, DB2, Oracle, Informix, DataFlex, OpenOffice.org Base, MS Access, Sybase ASE, Sybase ASA, Sybase IQ, Caché, Paradox, Pervasive SQL, MSDE, ЛИНТЕР, db4o, PrimeBase, Rdb, Ingres, Berkeley DB, HSQLDB.