Применительно к нашей ситуации имеет смысл использовать операцию IIf – Если. Ее формат таков: IIf(условие;действие_если_истина; действие_ если_ложь). Можно также использовать вложенные условия – их нужно вставлять вместо действий – IIf(условие; IIf(вложенное_условие; действие_если_истина;действие_если_ложь); действие_если_ложь)
Наш запрос будет выглядеть так:
SELECT *, (IIf(Покупки > 5; 0.06; 0)) AS Скидка From Постоянные_клиенты;
Он даст нам источник записей, который содержит все поля и записи таблицы «Постоянные_клиенты», а в придачу к ним – уже рассчитанные скидки в соответствующем поле.
Более подробно о поддерживаемых вычислительных операциях можно прочитать в соответствующем разделе справки MicrosoftAccess.
Access поддерживает также групповые (итоговые) операции. Это значит, что можно рассчитать, исходя из набора данных, некоторые статистические и другие характеристики, такие как сумма, дисперсия, математическое ожидание и другие. В запросах этим пользуются не очень часто, но пользуются. Поля с групповыми вычислениями объявляются так же, как и обычные вычисляемые поля. Например, запрос
SELECTSum(Покупки) AS Количество FROM Постоянные_клиенты;
Вернет нам одну-единственную запись с общим числом покупок, сделанным всеми постоянными клиентами.
Иногда для удобства анализа данных их необходимо группировать. Т.е. если в источнике записей есть записи, повторяющиеся по каким-либо критериям, подчас лучше привести их к одной и вывести их количество.
Итак, есть две таблицы: «Ассортимент», содержащая список товаров, которыми торгует гипотетическая фирма «Рога_и_копыта», и безграмотно составленная таблица «Склад», в которую при нахождении каждого экземпляра товара добавляли запись. Надо сделать так, чтобы данные из этих таблиц отобразались наглядно.
Таблица «Ассортимент» содержит поля «ID_товара» и «Наименование», а связанная с ней таблица «Склад» - только «ID_товара».
Вначале создадим запрос «Склад_гр», который сгруппирует повторяющиеся записи в таблице «Склад» и выведет количество повторений для каждой записи. Для этого применим групповую операцию Count и конструкцию GROUPBY.
В общем виде группировка выглядит так:
SELECT (список_полей) FROM (список_таблиц) GROUPBY (список_полей);
В нашем случае мы получим такой запрос:
SELECTID_товара, Count(ID_товара) AS Количество FROM Склад
GROUPBYID_товара;
Этот запрос даст нам уже сгруппированную таблицу, и мы легко сможем увидеть, сколько экземпляров каждого товара лежит на складе. Но теперь желательно было бы просмотреть наименования товаров, лежащих на складе. Для этого воспользуемся уже знакомой операцией INNERJOIN.
SELECT Количество FROM Склад_гр INNERJOIN Ассортимент ON (Склад_гр.ID_товара = Ассортимент.ID_товара);
Возвращенный таким запросом источник записей будет иметь следующую структуру:
| Количество | ID_товара | Наименование |
Как раз то, что нужно.
2.5. Сортировка полученных результатов
Для того, чтобы сделать вывод упорядоченным, язык SQLпредлагает использование конструкции ORDER [ASCEND|DESCEND] BY (поле). ASCEND/DESCEND – это направление сортировки – по возрастанию или убыванию, соответственно. Модернизируем наш предыдущий запрос таким образом, чтобы он при выводе сортировал данные по полю «Наименование»:
SELECT Количество FROM Склад_гр INNERJOIN Ассортимент ON (Склад_гр.ID_товара = Ассортимент.ID_товара)
ORDERASCENDBY Наименование;
На этом закончим рассмотрение (несколько поверхностное) предложений SELECT.
3. Запросы на добавление
В дальнейшем мы будем рассматривать запросы, которые управляют непосредственно данными в таблицах, а то и самими таблицами. При их разработке и выполнении следует соблюдать осторожность, т.к. изменения, вносимые такими запросами в данные, зачастую необратимы. Естественно, что источниками данных для таких запросов могут являться только таблицы.
Иногда, зачастую в серверных базах данных или при использовании элементов программирования в MicrosoftAccess требуется динамически добавить запись в таблицу. Язык SQL предлагает использовать для этих целей предложение INSERTINTO.
Общая структура этого типа запросов такова:
INSERTINTOИМЯ_ТАБЛИЦЫ VALUES (список значений);
Список значений всегда должен быть равен по количеству элементов количеству полей в изменяемой таблице. Если же надо ввести только некоторые поля, для остальных 0 или NULL для строковых полей. Также можно использовать выражения.
Например, есть таблица «товары»
| ID_товара | Наименование | Количество |
Допустим, нам надо добавить запись в эту таблицу, но беда в том, что поле ID_товара – ключевое и иммет тип «Счетчик». Нарушать его нельзя, поэтому воспользуемся групповой операцией Max().
INSERTINTOТовары 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 <значение>