Національний технічний університет „Харківський Політехнічний інститут”
Факультет „Компьютерні та інформаційні технології”
Кафедра „Обчислювальна техніка та програмування”
Реферат
З курсу „Організація баз даних”
Тема: можливості SQL-запитів у MicrosoftAccess
Виконав: ст. гр. xxxxxxxxxxxxxxxxx.
Перевірив: xxxxx
Харків
2006
Содержание:
| 1. Введение | 3 |
| 2. Запросы на выборку | 4 |
| 4 | |
| 5 | |
| 6 | |
| 8 | |
| 11 | |
| 3. Запросы на добавление | 11 |
| 4. Запросы на обновление | 12 |
| 5. Запросы на удаление | 13 |
| 6. Параметрические запросы | 14 |
| 7. Управляющие запросы ядра MicrosoftJet | 14 |
| 8. Формирование и выполнение запросов в реальном времени (инструментарий VisualBasicForApplications) | 17 |
| 9. Выводы | 20 |
| 10. Список использованной литературы | 21 |
1. Введение
Как известно, в основном системы управления реляционными базами данных (СУРБД) делатся на два класса – серверные и пользовательские. MicrosoftAccess, являющаяся предметом рассмотрения данного реферата, принадлежит ко второму классу систем, т.к. из-за непроработанной системы совместного доступа к данным не может выступать в роли полноценной серверной СУРБД (хотя соответствующие драйверы ODBC существуют и работают). Также в пользу того, что Access принадлежит к пользовательским системам, говорит факт интегрированности хранилища данных (таблиц) и средств создания пользовательского интерфейса (хранимые запросы, формы, отчеты, программы)
Однако по другим своим возможностям, в частности, по возможностям выполнения сложных запросов SQL, Access намного превосходит другие программные продукты своего класса. К тому же, легкость объединения с другим ПО, поставляемым в комплекте OfficeProfessional – SQLServerDesktopEngine – делает Access вообще недосягаемой для конкурентов системой.
Данный реферат посвящен проблематике, неизбежно возникающей при проектировании БД – проектировании системы поиска и модификации существующих данных. Показано, как выполнять эти операции, используя для этого один лишь встроенный интерпретатор языка SQL – такой путь является наиболее универсальным, и, по прошествию некоторого времени – самым легким способом организовать любое требуемое представление имеющихся данных.
2. Запросы на выборку
Данный тип запросов является одним из наиболее часто применяемых не только в MSAccess, но и вообще во всех СУРБД.
Основные задачи таких запросов таковы:
а. Выбрать некоторые поля из таблицы
б. Отфильтровать содержимое таблицы по некоторым критериям
в. Объединить несколько разнородных таблиц, используя связи типа «один-к-одному», «один-ко-многим»
г. Сформировать из нескольких однородных таблиц один источник записей.
д. Сгруппировать данные таблицы и/или вычислить некоторые характеристики этих данных.
2.1. Особенности использования инструкций SELECT
Запросы на выборку всегда начинаются с предложения SQLSELECT (список полей) FROM (список таблиц), например:
SLECT
Название, Город
FROM
заказчики
Однако даже такая конструкция имеет разветвления:
- SELECT
DISTINCT
– используется для отбора уникальных записей по полям, содержащимся в запросе
- SELECT
DISTINCTROW
– то же самое, что и DISTINCT, но для определения уникальности используются
- SELECT
TOP
[число или количество процентов]
В SQL-запросах можно объединять несколько таблиц, даже если они содержат разное количество записей (хотя, как правило, такое объединение будет бессмысленым). Для выбора всех полей из таблицы применяется символ *.
Примеры простейших SQL-запросов:
SELECT
*
From
Заказчики
– выбирает все записи и поля за таблицы «Заказчики».
SELECT
DISTINCT
Город
FROM
Заказчики
– выбирает по 1 разу каждый город, в котором находится один или более заказчик.
SELECT
TOP
10
PERCENT
*
FROM
Заказчики
– выбирает первые 10% записей в таблице.
Если же в запросе присутствует несколько таблиц, то в части FROM их необходимо перечислить через запятую. Если из таблиц в таком запросе выбираются не все поля, то выбираемые нужно вводить в формате [Имя_таблицы].[Имя_поля], например:
SELECT Table1.Field1, Table2.Field1 FROM Table1, Table2;
Кроме того, имена полей или таблиц на языке, отличающемся от английского, рекомендуется брать в квадратные скобки. Имена же, содержащие пробелы, подлежат обязательному взятию в квадратные скобки.
2.2. Использование критериев отбора
Допустим, у нас есть таблица «Заказчики» со следующими полями:
| №п/п | Название | Город | Адрес | К_во_заказов |
Если необходимо выбрать из нее не все записи, а только те, которые удовлетворяют некоторым условиям. Например, только тех из них, которые размещены в Харькове. Для этого используется инструкция WHERE. Условий может быть несколько, тогда они объединяются логическими операциями И, ИЛИ, ИСКЛЮЧАЮЩЕЕ ИЛИ (XOR). Для инвертирования части или всего условия (т.е. отбора тех записей, которые данному условию не удовлетворяют) используется операция NOT.
Общий синтаксис простого запроса с условием таков:
SELECT
Список_полей
FROM
Список_Таблиц
WHERE
(условие1) лог_оп (условие2) лог_оп (условие3);
Пример:
SELECT
*
FROM
Заказчики
WHERE
(Город=’Харьков’);
SELECT
*
FROM
Заказчики
WHERE
(Город=’Харьков’)
AND
(
К_во_заказов
> 5);
2.3. Объединение нескольких источников данных
Иногда бывает необходимо объединить разнородные таблицы, чтобы заняться анализом данных, избегая ненужного дублирования данных и лишних операций по поиску.
Допустим, к нашей базе данных присоединена внешняя таблица «Реестр», в которой хранятся сведения обо всех субъектах предпринимательской деятельности в Украине. А нам надо просмотреть все сведения только о наших заказчиках. Опять-таки, всех. Для таких случаях существует инструкция JOIN – объединение таблиц по одному полю. Различают внутренние (INNER) и внешние (LEFT, RIGHT) объединения. Мы рассмотрим лишь внутренние – это наиболее жизненная ситуация.
Общий вид объединения таков:
SELECT
(список_полей_главной_таблицы)
FROM
(Главная_таблица) <Вид_объединения>
JOIN
(Подчиненная_таблица)
ON
(
Главная.Поле1 = Подчиненная.Поле1)
Итак, таблица «Реестр» имеет следующую структуру:
| Название | Код_ЕДРПОУ | Расч_счет | ИНН |
Теперь присоединим ее к таблице «Заказчики»:
SELECT
*
FROM
Заказчики
INNER
JOIN
Реестр
ON
(Заказчики.Название = Реестр.Название)
В результате мы будем иметь источник записей с количеством записей, равным размеру таблицы «Заказчики», и содержащий все поля таблиц «Заказчики» и «Реестр». Так же, как и в конструкции WHERE, объединение может быть по нескольким полям, с использованием различных условий, однако следует иметь в виду, что использование нестандартных (неподдерживаемых конструктором запросов) действий может привести к непредсказуемым результатам.
Во избежание непонимания стоит заметить, что использование одной конструкции SQL практически никогда не налагает запрет на использование другой. Т.е. запрос типа
SELECT
*
FROM
Заказчики
INNER
JOIN
Реестр
ON
(Заказчики.Название = Реестр.Название)
WHERE
(Город=’Харьков’)
AND
(
К_во_заказов
> 5);
будет воспринят MSAccess вполне нормально.
Возможна и другая ситуация.
Допустим, у нас есть таблица «Сотрудники_офиса» следующей структуры:
| Таб_номер | ФИО | Телефон |
Также есть таблица «Сотрудники_филиала» с точно такой же структурой. Необходимо в каком-нибудь итоговом отчете представить эти таблицы вместе. Для этого используется конструкция UNION. При ее использовании можно склеить сколько угодно таблиц. Точнее, таблицы необязательно должны иметь одинаковую структуру. Необходимо лишь, чтобы из всех таблиц выбиралось одинаковое количество полей, и чтобы эти поля были полностью эквивалентны.
Общая структура такого запроса такова:
SELECT
Таблица1.Поле1, Таблица1.Поле
N
FROM
Таблица1
UNION
SELECT
Таблица2.Поле1, Таблица2.Поле
N
FROM
Таблица2
UNION
SELECT
Таблица
K
.Поле1,Таблица
K
.Поле
N
FROM
Таблица
K
;
В нашем случае это будет выглядеть так:
SELECT
Сотрудники_офиса.*
FROM
Сотрудники_офиса
UNION
SELECT
Сотрудники_филиала
.*
FROM
Сотрудники_филиала
В результате мы получим источник записей, содержащий данные по всем сотрудникам, работающим и в офисе, и в филиале.
Иногда бывает необходимо объединить несколько таблиц (более 2) с помощью операции JOIN. Сделать это в рамках одного запроса весьма проблематично, однако не следует забывать, что MSAccess в состоянии использовать запросы как источник записей. Т.е. объедините две таблицы в одном запросе, а затем обхедините третью таблицу и запрос во втором.
2.4. Групповые операции и вычисляемые поля
Допустим, есть у нас таблица «Постоянные_клиенты». И нам, в зависимости от количества покупок надо дать им скидки – например, те, кто сделал более 5 покупок, получает скидку 6%. Исходная таблица имеет такой вид:
| Номер | ФИО | Покупки |
Чтобы выполнять подобные операции, не прибегая к искусству программирования, в запросах MicrosoftJet (именно так называется ядро баз данных Access) реализована возможность реализации вычисляемых полей.
Объявляются эти поля точно так же, как и обычные, в конструкции SELECT:
SELECT
(список_обычных_полей), (операция_с_полями_или_ числа-ми)
AS
вычисляемое_поле
FROM
(список_таблиц);
Применительно к нашей ситуации имеет смысл использовать операцию IIf – Если. Ее формат таков: IIf(условие;действие_если_истина; действие_ если_ложь). Можно также использовать вложенные условия – их нужно вставлять вместо действий – IIf(условие; IIf(вложенное_условие; действие_если_истина;действие_если_ложь); действие_если_ложь)
Наш запрос будет выглядеть так:
SELECT
*, (
IIf
(Покупки > 5; 0.06; 0))
AS
Скидка
From
Постоянные_клиенты;
Он даст нам источник записей, который содержит все поля и записи таблицы «Постоянные_клиенты», а в придачу к ним – уже рассчитанные скидки в соответствующем поле.
Более подробно о поддерживаемых вычислительных операциях можно прочитать в соответствующем разделе справки MicrosoftAccess.
Access поддерживает также групповые (итоговые) операции. Это значит, что можно рассчитать, исходя из набора данных, некоторые статистические и другие характеристики, такие как сумма, дисперсия, математическое ожидание и другие. В запросах этим пользуются не очень часто, но пользуются. Поля с групповыми вычислениями объявляются так же, как и обычные вычисляемые поля. Например, запрос
SELECT
Sum
(Покупки)
AS
Количество
FROM
Постоянные_клиенты;
Вернет нам одну-единственную запись с общим числом покупок, сделанным всеми постоянными клиентами.
Иногда для удобства анализа данных их необходимо группировать. Т.е. если в источнике записей есть записи, повторяющиеся по каким-либо критериям, подчас лучше привести их к одной и вывести их количество.
Итак, есть две таблицы: «Ассортимент», содержащая список товаров, которыми торгует гипотетическая фирма «Рога_и_копыта», и безграмотно составленная таблица «Склад», в которую при нахождении каждого экземпляра товара добавляли запись. Надо сделать так, чтобы данные из этих таблиц отобразались наглядно.
Таблица «Ассортимент» содержит поля «ID_товара» и «Наименование», а связанная с ней таблица «Склад» - только «ID_товара».
Вначале создадим запрос «Склад_гр», который сгруппирует повторяющиеся записи в таблице «Склад» и выведет количество повторений для каждой записи. Для этого применим групповую операцию Count и конструкцию GROUPBY.
В общем виде группировка выглядит так:
SELECT
(список_полей)
FROM
(список_таблиц)
GROUP
BY
(список_полей);
В нашем случае мы получим такой запрос:
SELECT
ID
_товара,
Count
(
ID
_товара)
AS
Количество
FROM
Склад
GROUP
BY
ID
_товара;
Этот запрос даст нам уже сгруппиро
SELECT
Количество
FROM
Склад_гр
INNER
JOIN
Ассортимент
ON
(Склад_гр.
ID
_товара = Ассортимент.
ID
_товара);
Возвращенный таким запросом источник записей будет иметь следующую структуру:
| Количество | ID_товара | Наименование |
Как раз то, что нужно.
2.5. Сортировка полученных результатов
Для того, чтобы сделать вывод упорядоченным, язык SQLпредлагает использование конструкции ORDER [ASCEND|DESCEND] BY (поле). ASCEND/DESCEND – это направление сортировки – по возрастанию или убыванию, соответственно. Модернизируем наш предыдущий запрос таким образом, чтобы он при выводе сортировал данные по полю «Наименование»:
SELECT
Количество
FROM
Склад_гр
INNER
JOIN
Ассортимент
ON
(Склад_гр.
ID
_товара = Ассортимент.
ID
_товара)
ORDER
ASCEND
BY
Наименование;
На этом закончим рассмотрение (несколько поверхностное) предложений SELECT.
3. Запросы на добавление
В дальнейшем мы будем рассматривать запросы, которые управляют непосредственно данными в таблицах, а то и самими таблицами. При их разработке и выполнении следует соблюдать осторожность, т.к. изменения, вносимые такими запросами в данные, зачастую необратимы. Естественно, что источниками данных для таких запросов могут являться только таблицы.
Иногда, зачастую в серверных базах данных или при использовании элементов программирования в MicrosoftAccess требуется динамически добавить запись в таблицу. Язык SQL предлагает использовать для этих целей предложение INSERTINTO.
Общая структура этого типа запросов такова:
INSERT
INTO
ИМЯ_ТАБЛИЦЫ
VALUES
(список значений);
Список значений всегда должен быть равен по количеству элементов количеству полей в изменяемой таблице. Если же надо ввести только некоторые поля, для остальных 0 или NULL для строковых полей. Также можно использовать выражения.
Например, есть таблица «товары»
| ID_товара | Наименование | Количество |
Допустим, нам надо добавить запись в эту таблицу, но беда в том, что поле ID_товара – ключевое и иммет тип «Счетчик». Нарушать его нельзя, поэтому воспользуемся групповой операцией Max().
INSERT
INTO
Товары
VALUES
(
MAX
(
ID
_Товара)+1, ‘Какой-то товар’, 10);
Таким образом, мы сохранили порядок счета.
Следует заметить, что предложение INSERT является одним из самых простых в SQL. Никаких дополнительных конструкций в нем не используется.
4. Запросы на обновление
Иногда, опять-таки, зачастую при использовании элементов программирования, может потребоваться динамическое изменение всех или части записей в таблице. Для этого применяется предложение UPDATE.
Допустим, со склада раз в месяц осуществляется отгрузка. Отгружают по одному артикулу каждого товара. Если какой-то товар на складе отсутствует, его, соответственно, не отгружают.
Общая структура запросов на обновление:
UPDATE
Имя_таблицы
SET
(список значений или список выражений «Поле=значение»);
По умолчанию UPDATE налагает изменения на все записи таблицы. Чтобы ограничить количество затрагиваемых записей, следует использовать конструкцию WHEREтак же, как и в запросах на выборку.
Наш запрос на отгрузку будет выглядеть так:
UPDATE
Товары
SET
(Количество=Количество – 1)
WHERE
(Количество > 0);
Он-то и выполнит все необходимые действия по обработке результатов отгрузки.
5. Запросы на удаление
Встречаются ситуации, когда данные в таблицах устаревают. Допустим, есть база финансовой отчетности предприятия. И документы, которые были введены туда до 1 января 2004 года, надо удалить.
Для этого используется предложение DELETE.
Структура таблицы «Документы»
| Номер | Название | Текст | Дата_регистрации |
Запросы на удаление имеют следующую структуру:
DELETE
*
FROM
Имя_таблицы
WHERE
(список условий);
В нашем случае запрос будет иметь следующий вид:
DELETE
*
FROM
Документы
WHERE
(Дата_регистрации
< 01.01.2004
)
;
Как видим, диалект языка SQL, используемый в MicrosoftAccess, весьма демократичен.
6. Параметрические запросы
Access позволяет сделать запросы более гибкими – т.е. сделать так, чтобы при изменении критериев в запросе не приходилось править его в конструкторе. Для этого используется механизм параметров.
Например, у нас есть таблица «Список_жильцов», и нам нужно отобразить тех из них, возраст которых превышает некоторую заранее неизвестную величину. Для этого используем запрос
SELECT
*
FROM
Список_жильцов
WHERE
(Возраст > Критерий_ возраста);
При попытке открытия такого запроса Access в специальном диалоговом окне попросит ввести значение параметра «Критерий_возраста»
Параметров может быть сколько угодно, но имейте в виду, что пользователю для открытия такого запроса придется ответить на соответствующее количество диалоговых окон.
7. Управляющие запросы ядра MicrosoftJet
Эта небольшая группа запросов позволяет производить операции на уровне как отдельной таблицы, так и базы данных в целом.
а. Запрос DROP
Этот запрос позволяет удалить из базы данных таблицу или индекс. Его структура –
DROPTABLEИмя_таблицы
DROPINDEXИмя_индекса
б. Запрос ALTERTable
Этот запрос позволяет редактировать структуру таблицы.
Его структура:
Alter table <имя таблицы> <описание изменения>
В случае, когда надо сделать несколько изменений, они указываются через запятую. Перечислю типы изменений:
ADD <описание колонки>
Позволяет добавить новую колонку в таблицу. Описание колонки такое же, как и в Create table. После слова ADD можно указать слово COLUMN, которое позволяет сделать текст запроса более читабельным. После описания колонки можно указать слово FIRST, в этом случае колонка будет первой в таблице, или слово AFTER, после которого указывается название колонки, в этом случае навая колонка будет добавлена после указанной.
Можно также указать несколько описаний колонки через запятую и внутри круглых скобок. В этом случае указать расположение колонки нельзя.
ADD INDEX (<имя колонки>,... )
Позволяет добавить новый индекс. В скобках указываются названия колонок, входящих в индекс. Между словом INDEX и скобкой можно указать название индекса.
ADD PRIMARY KEY (<имя колонки>,... )
Позволяет добавить новый первичный ключ (или сменить его). В скобках указываются названия колонок, которые входят в ключ.
ADD UNIQUE (<имя колонки>,... )
Позволяет добавить новый индекс с уникальными значениями. В скобках указываются названия колонок, которые входят в индекс. Между словом UNIQUE и скобкой можно указать название индекса.
ADD FULLTEXT (<имя колонки>... )
Позволяет добавить новый индекс с полнотекстовым поиском. В скобках указываются названия колонок, которые входят в индекс. Между словом FULLTEXT и скобкой можно указать название индекса.
ALTER COLUMN <имя колонки> SET DEFAULT <значение>
ALTER COLUMN <имяколонки> DROP DEFAULT
Запрос позволяет создать или удалить значение по умолчанию для колонки. При этом слово COLUMN можно опустить.
CHANGE COLUMN <имя колонки> <описание колонки>
Запрос изменяет указанную колонку на новую, описание такое же, как и в CREATE TABLE. Слово COLUMN можно опустить. В описании колонки указывается имя колонки, так что имя может быть изменено.
MODIFY COLUMN <описание колонки>
Изменяет колонку на новую, описание такое же, как и в CREATE TABLE. Слово COLUMN моно опустить.
DROP COLUMN <имя>- Удаляет колонку.
DROP PRIMARY KEY - Удаляет первичный ключ
DROP INDEX <имя индекса> - Удаляет индекс
RENAME TO <новое имя таблицы> - Переименовывает таблицу
в. Запрос CREATETABLE
Этот запрос позволяет динамически создать новую таблицу в базе данных. Например:
В следующем управляющем запросе
с помощью инструкции CREATE TABLE создается таблица «Друзья». Приведенная инструкция определяет имена и типы данных для полей таблицы и создает для поля «Код» индекс, делающий это поле ключевым.
CREATE TABLE Друзья([Код] integer,[Фамилия] text,[Имя] text,[ДатаРождения] date,[Телефон] text,[Заметки] memo,CONSTRAINT [Индекс1] PRIMARY KEY ([Код]));
8. Формирование и выполнение запросов в реальном времени (инструментарий VisualBasicForApplications)
Для того, чтобы запрограммировать автоматизацию действий с запросами, MicrosoftAccess предлагает два варианта:
а. Использование объектов доступа к данным (DAO)
б. Использование объекта-макрокоманды DoCmd.
Первый путь позволяет нам создавать хранимые запросы в базе данных (они будут появляться во вкладке «Запросы») или же работать с их результатами напрямую. Объект DoCmd, а точнее, его метод RunSQL используется исключительно для запуска запросов на изменение данных или управляющих запросов MSJet.
Покажем общие приемы создания и исполнения запросов с помощью DAO. Для этого будем использовать объект Application.CurrentDB и, для создания/удаление хранимых запросов – коллекцию Queries, в этом объекте хранящуюся. Для работы с данными в реальном времени применим объект Recordset.
За основу возьмем запросы на выборку, показанные в разделе 2 данного реферата.
Option Compare Database
Option Explicit
Public Sub QRY_Example1()
Dim s As String
S = “SELECT
Количество
FROM
Склад
_
гр
INNER JOIN “ + _ “
Ассортимент
ON (
Склад
_
гр
.ID
_товара = Ассортимент.
ID_
товара
) ”+ _
“ORDER ASCEND BY
Наименование
;”
Application.CurrentDb.CreateQueryDef("Query1", s)
End
Sub
Такая процедура создаст в базе данных новый запрос с именем Query1 и текстом, хранящимся в переменной S. А следующая – удалит его:
Public Sub QRY_Example2()
Application.CurrentDb.QueryDefs.Delete("Query1")
End
Sub
Теперь покажем, как работать с полученными из запроса данными напрямую (эта процедура выведет сообщения, в которых перечислит количество и наименование всех товаров):
Public Sub QRY_Example3()
Dim s As String
Dim rst As New Recordset
S = “SELECT
Количество
FROM
Склад
_
гр
INNER JOIN “ + _ “
Ассортимент
ON (
Склад
_
гр
.ID
_товара = Ассортимент.
ID_
товара
) ”+ _
“ORDER ASCEND BY
Наименование
;”
set rst = Application.CurrentDb.OpenRecordset(s, dbOpenDynaset, dbReadOnly)
rst.MoveFirst
for I = 1 to rst.RecordCount do
MsqBox
“На складе хранится “ +
str
(
rst
.
Fields
(“Количество”)) +” “ +
rst
.
Fields
(“наименование”)
Rst
.
MoveNext
Next
End
Sub
По поводу свойств и методов объектов доступа к данным подробные сведения можно получить во встроенной справке VBA.
С объектом DoCmd все намного проще:
Public Sub QRY_Example4()
Dim s As String
Dim s2 As String
Dim rst As New Recordset
S
=
InputBox
“Введите наименование товара”, “Наша_БД”
S
2 =
InputBox
“Введите количество этого товара”, “Наша_БД”
S = “INSERT INTO
Товары
VALUES “ + _
“(MAX(ID_
Товара
)+1, “ + “‘” + s + ”’,” + s2 + “);”
DoCmd
.
RunSQL
s
End
Sub
Эта процедура в запросит в диалоговом окне ввести наименование товара и его количество, а затем добавит эти значения в виде новой записи в таблицу «Товары».
На этом демонстрацию возможностей ядра MicrosoftJet в области выполнения SQL-запросов можно считать оконченной.
9. Выводы
В данном реферате были рассмотрены основные типы запросов, которые поддерживаются MicrosoftAccess – запросы на выборку, добавление, удаление и обновление записей и управляющие запросы. Также освещена тема динамического формирования и выполнения запросов SQL средствами VisualBasicforApplications.
10. Списоклитературы:
1. Microsoft Access 2002 Bible – Cary Prague and Michael Irwin – N.Y.: Hungry Minds, 2002.
2. Mirosoft Access 2002 Help system © 1991-2002 Microsoft Corp.
3. Alter table – лекция © RSC-Team 2001-2005 (http://rsc-team.ru/bk/index.pl?rzd=2&group=lection&ind=103)