РАБОТАЕМ С ДАННЫМИ
КОМПЬЮТЕРНАЯ ГАЗЕТА
Существует на свете такой афоризм: чтобы получить умный ответ, нужно задать умный вопрос, а чтобы задать умный вопрос нужно знать, по крайней мере, две трети ответа. И это вовсе не игра словами. Невозможно, например, объяснить методику решения интегрального уравнения человеку, незнакомому с таблицей умножения. С СУБД дела обстоят примерно аналогичным образом. База данных может содержать в себе необозримое количество нужной и полезной информации, которая, тем не менее, остается абсолютно бесполезной без удобного механизма манипулирования ею. Как упоминалось ранее, в любой СУБД информация хранится в таблицах, в которых ее можно и посматривать, и даже до некоторой степени управлять ею посредством некоторого набора универсальных фильтров. Однако для повседневной работы этого слишком мало, особенно в случаях, когда заранее неизвестно, есть ли в базе данных интересующая информация или нет. Для того чтобы пользователи (а если быть более точными - разработчики баз данных) могли свободно манипулировать информацией, в СУБД был встроен механизм запросов, предназначенный, так сказать, для тонкого управления содержимым любой базы данных.
Запрос-выборка просто генерирует ответ на заданный пользователем вопрос и на этом заканчивает свою работу. После выполнения запроса СУБД создает виртуальную таблицу, в которую заносит выбранную информацию и хранит ее до тех пор, пока сгенерированная таблица не будет закрыта. Фактически, механизм запроса-выборки работает следующим образом: до тех пор, пока конкретный запрос не инициализирован, он представляет собой всего лишь безжизненный набор каких-то инструкций, которые к тому же могут оказаться еще и неправильными. Когда пользователь или заранее написанный модуль обращается к конкретному запросу, его набор инструкций немедленно выполняется, в оперативной памяти компьютера возникает виртуальная итоговая таблица, которая, в свою очередь, сама может служить источником данных для другого запроса или пользовательской формы. Когда этот запрос закрывается, полученная таблица уничтожается, освобождая занимаемую память.
В отличие от запроса-выборки, запрос-действие оставляет после себя нечто конкретное и ощутимое. При помощи запроса-действия можно автоматически создать новую таблицу, внести данные в уже имеющуюся таблицу, а также удалить из существующей таблицы какой-либо набор записей.
Во избежание совершения непреднамеренных действий с данными, которые нельзя отменить, разработчики СУБД Microsoft Access 97 настроили систему таким образом, что любой создаваемый запрос автоматически считается запросом-выборкой и работает в соответствии с этим. В том случае, когда пользователю или разработчику нужно получить запрос-действие, системе требуется об этом "сказать" отдельно.
Механизм запросов является самым универсальным решением, позволяющим, с одной стороны, как угодно манипулировать данными, вплоть до того, что в зависимости от своего состояния база данных выбирала какие-то определенные действия, а с другой - снизить потребность СУБД в системных ресурсах вообще, так как активизация базы данных не приводит к проведению всех имеющихся в ней запросов и прочих автоматических функций, как это имеет место, например, в электронных таблицах. Те или иные запросы выполняются компьютером только тогда, когда это действительно необходимо. Правда, медаль имеет и оборотную сторону, исходя из своей идеологии запрос чрезвычайно сильно похож на моментальное фото, отражающее текущее состояние базы СТРОГО на момент выполнения данного запроса. Иными словами, если в данных произошли перемены ПОСЛЕ того, как запрос закончил свою работу, то указанные изменения в уже созданной выборке отражены не будут, так как виртуальная таблица с самими данными никакой связи не имеет. Это означает, что, во-первых, при проектировании любой базы данных следует обращать особое внимание на очередность запуска конкретных запросов, а во-вторых, если по каким-либо причинам пользователь нуждается в постоянном мониторинге в реальном режиме времени, то единственным способом достичь этого является повторение одного и того же запроса через короткие промежутки времени.
Для того чтобы создать запрос-выборку, нужно перейти на вкладку ЗАПРОСЫ и нажать экранную кнопку " СОЗДАТЬ". Это приведет к появлению мастера запросов, который желает знать, каким именно алгоритмом разработчик желает воспользоваться: одним из типовых или специализированным конструктором, в котором буквально весь запрос создается пользователем вручную (см. рис. 1). Тут уместно вспомнить, что разработчики Microsoft Access заранее позаботились о том, чтобы в типовых ситуациях пользователь мог воспользоваться стандартным видом запроса. В том случае, когда пользователь желает выбрать некие данные только из одной таблицы, то лучше всего воспользоваться вариантом " Простой запрос". В том случае, если пользователю необходимо нечто близкое к итоговому отчету, например, из всего объема продаж товаров и услуг за всю историю предприятия необходимо выбрать показатели по конкретным позициям за конкретный год, несколько определенных месяцев или кварталов, следует воспользоваться вариантом " Перекрестный вопрос". В том случае, когда из всего массива данных одной или нескольких таблиц нужно выбрать лишь те данные, которые повторяются, например, выбрать те марки автомобилей, количество продаж которых "больше чем один", следует воспользоваться вариантом " Повторяющиеся записи". В том случае, когда нужно найти среди всех записей базы данных те, которые не имеют подчиненных, следует выбирать последний вариант, " Записи без подчиненных". Это достаточно типовая ситуация для любой "торговой" базы данных, фиксирующей торговые операции по конкретным клиентам или по конкретным товарам. Незачем хранить в общем массиве, например, информацию по конкретной поставке товара, если сам товар уже давно продан "до последней крошки", как незачем хранить в списке "постоянных клиентов" фамилии тех людей, которые уже длительное время не появлялись в вашем магазине или вообще переехали в другой город. Очевидно, что чем меньше записей приходится просматривать, тем быстрее работает вся СУБД, а значит периодическое "прореживание" информации имеет вовсе не один только академический смысл.
Как конструировать запросы, уже упоминалось ранее, и повторять это излишне. А вот на том, каким образом в рамках запроса организовывается отбор необходимых данных, следует остановиться особо.
Предположим, что нам нужно выбрать из нашей базы данных только те записи, которые относятся, например, к рубрике "научная фантастика". Для этого в режиме конструктора создаем запрос по связанным таблицам, в поля которого перетаскиваем из таблицы Рубрикатор поле " Наименование рубрики", а из таблицы Литература - поля " Автор", " Наименование", " Стоимость" (см. рис. 2). В бланке запроса, в нижней части окна конструктора, есть строка " Условие отбора" и непосредственно следующая за ней строка " Или". Нас интересуют именно они, так как их содержимое определяет, по какому критерию запрос будет отбирать информацию. Первый критерий заносится в строку " Условие отбора". В том случае, когда разработчик желает произвести отбор по двум и более несвязанным критериям, применяется алгоритм "или-или-или", согласно которому в результирующую таблицу попадут только те записи, которые соответствуют либо первому условию, либо второму, и так далее. Чтобы разобраться, как сие работает, нужно сравнить два результата: то, что запрос "выдаст" в текущем его состоянии (см. рис. 3), и после того, как в строке " Условие отбора" столбца " Наименование рубрики" с клавиатуры будет введена следующая запись "Научно-техническая" (именно в кавычках) (см. рис. 4). Как видно из примера, из всего массива записей теперь запрос выбирает лишь те строки, в которых содержатся записи, относящиеся к рубрике научно-техническая литература. Аналогичным образом конструктор запроса может задавать несколько условий не только для одного поля, но и для нескольких полей одновременно, например выбрать все книги, автором которых является Том Клэнси и цена которых не превышает, к примеру, двадцати рублей.
Приступая к конструированию запросов, стоит потратить немного времени на освоение системы логических операторов, используемых в вычислительной технике. Целью работы логической операции должно быть значение "ИСТИНА" для того, чтобы запись "попала в набор", в противном случае она будет отвергнута. Все условия, перечисленные в одной строке, будь то " Условие отбора" или " Или", Microsoft Access автоматически суммирует, то есть применяет к ним логический оператор "И". Тонкость тут заключается в том, что "в набор" попадают только те записи, которые удовлетворяют абсолютно ВСЕМ приведенным условиям. Стоит "промахнуться" хотя бы в одном из них - и "финита ля комедия", запрос данную запись "в упор не увидит". Зато к наборам условий двух соседних строк (" Условие отбора" и " Или") Microsoft Access применяет логический оператор "ИЛИ", что означает, что "в набор" попадет только та запись, которая соответствует набору условий одной либо другой строки. Вкраце сочетание логических условий выглядит следующим образом:
| "И" | Истина | Ложь |
| Истина | Попадает в набор | Не попадает в набор |
| Ложь | Не попадает в набор | Не попадает в набор |
| "ИЛИ" | Истина | Ложь |
| Истина | Попадает в набор | Попадает в набор |
| Ложь | Попадает в набор | Не попадает в набор |
Правда, как показывает практика, если условия отбора применяются одновременно для нескольких полей (например, "все автомобили марки "олсмобиль", цвет которых "красный" или "мокрый асфальт", вариант отделки салона "кожаный", проданные в период с 31 декабря 1999 года по 1 января 2000 года"), то следует особенно строго следить за логическим соответствием между всеми ограничениями. Например, в приведенном выше примере существует явный ляп, хотя теоретически все чисто. В подавляющем большинстве стран мира 31 декабря и 1 января являются выходными днями, а значит фирма, может быть, и торгует "олсмобилями" такого типа, но, вероятнее всего, в указанный период все ее сотрудники вместе с потенциальными покупателями дружно отмечают Новый год.
Кроме типовых логических операторов, описанных выше, в Microsoft Access существует еще несколько полезных вариантов.
Оператор BETWEEN. С его помощью можно создать некоторый диапазон, который станет условием отбора. Например, Between 10 And 20. Это соответствует конструкции =10 And 20, или, говоря по-русски, диапазон от 10 до 20 включительно. Скептики могут возразить, мол, зачем городить огород, если все эти выкрутасы можно написать и на базовом наборе логических операторов? Это совершенно справедливо, однако тушить пожар при помощи современной системы пожаротушения гораздо удобнее и эффективнее, чем делать это как встарь, плеская водой из ведра.
По тем же причинам имеет смысл воспользоваться еще и такими операторами, как IN (вариант транскрипции: IN ("условие 1", "условие 2", "условие 3")), а также LIKE (позволяет осуществлять отбор записей, в которых есть определенные символы, вариант транскрипции: LIKE "?[a-k]d[0-9]*". Это обозначает, что "в набор" попадает только та запись, в данном поле которой первым может стоять любой символ, вторым - только из промежутка от "а" до "к" английского алфавита, третьим - строго латинская буква "d", четвертым - строго цифра от 0 до 9, а далее любое количество любых символов в любом порядке).
Кроме логических, при указании условий отбора могут применяться условия даты и времени: Day (дата), Month (месяц), Year (год), Weekday (день недели), Hour (номер часа), Datepart (номер квартала или номер недели, в зависимости от формата написания), Date (текущая системная дата). Вообще говоря, то, что перечислено в данном абзаце, является не совсем оператором, это скорее маленькие удобные программы, которыми нелишне воспользоваться для достижения своих целей. Стоит также помнить, что все, заключенное между символами "#" (например, #14.01.2002#) в Microsoft Access считается датой, а значит может быть использовано как условие отбора.
Помимо всего прочего, в качестве критериев отбора могут также применяться и величины, которые получаются в результате каких-либо вычислений. В качестве оператора в любой формуле на бланке запроса в этом случае применяются наименования полей, сформированные по следующему правилу: в том случае, если вы оперируете полями одной таблицы, причем именно той, которая занесена в бланк запроса, то следует просто написать имя этого поля, взятое в квадратные скобки (пример, [Наименование рубрики]), если для получения результата в формуле должно присутствовать поле из другой таблицы, то перед его именем следует набрать наименование таблицы тоже в квадратных скобках и отделить его восклицательным знаком (пример, [Имя таблицы или запроса]![Имя поля]). Ну, а далее - все совершенно очевидно, применение конструкции ОПЕРАТОР МАТЕМАТИЧЕСКОЕ ВЫРАЖЕНИЕ ОПЕРАТОР, почти как в детской считалке, про "А" "И" "Б", которые "сидели на трубе". В дополнение к обычной математике (сложение, вычитание, деление и умножение), Microsoft Access позволяет оперировать такими действиями, как "возведение в степень" (что возводить, символ "^", степень, в которую нужно возводить); MOD (округляет оба оператора до ближайшего целого, делит первое число на второе и возвращает остаток в качестве результата); & (присоединяет один оператор к другому и возвращает результат; как правило, этот оператор применяется для соединения нескольких текстовых строк в нечто целое, например, подстрока "город", подстрока "пробел" и подстрока "Минск" при помощи конструкции "[город]","&[ ]","&[Минск]" получается текстовая строка "город Минск"). В последнем примере для объединения строки применялись конечные величины постоянного характера, что несколько бессмысленно с точки зрения СУБД, так как проще сразу написать "город Минск", однако стоит вместо "города" и "Минска" ввести наименования полей, содержащих текстовые данные, в зависимости от конкретной ситуации одна и та же логическая конструкция на выходе может дать и "город Минск", и "город Витебск", и "город Вашингтон".
В общем, изложить в рамках одной статьи все возможности и принципы создания запросов чрезвычайно сложно и даже невозможно. Я осветил лишь основные принципы разработки запросов, а дальше разбираться в деталях вам придется самим, некоторое время с этим делом поэкспериментировав.
Александр Запольскис E-mail: leshy@nestor.minsk.by