Файл: Освой самостоятельно программирование для MS Access 2002 за 24 часа [П.Киммел].pdf

SELECT * FROM Music

ORDER BY Artist DESC, Title ASC

По умолчанию, т.е. в случае отсутствия одного из компонентов (ASC или DESC),

предлагается порядок сортировки по возрастанию.

Группировка столбцов

Предложение GROUP BY применяется для группировки данных в столбцах. К нему

необходимо обращаться при использовании так называемых

 функций

языка SQL, например

Предложение GROUP BY, подобно ORDER BY, в своей общей форме требует задания

списка наименований полей, разделенных запятыми.

Группируя данные по определенным столбцам возвращаемого набора, следует

включить в группу либо все столбцы набора данных, либо те из них, которые не ис-
пользованы в качестве аргументов агрегирующих функций.

Предложение GROUP BY применяется в тех случаях, когда необходимо получить

только одну строку из группы строк, в определенных столбцах которых хранятся

идентичные значения. Например, таблица TRACKS содержит внешний ключ

MUSIC_ID, ссылающийся на первичный ключ ID таблицы MUSIC. Выполнив запрос

SELECT

 FROM TRACKS,

мы получим столько значений, сколько композиций описано в таблице TRACKS, при-

чем числа будут повторяться. Чтобы избежать повторений одинаковых величин (т.е. еди-
ножды сослаться на каждый музыкальный альбом), необходимо использовать команду

SELECT

 FROM Tracks GROUP BY

В результате мы получим набор уникальных значений. Представим следующий за-

прос — он вычисляет количество композиций каждого музыкального альбома (теперь
без GROUP BY просто не обойтись):

SELECT

 Music_Id )

FROM Tracks

GROUP BY Music_Id

Указанная команда возвратит набор записей, перечисляющих уникальные коды

музыкальных альбомов наряду с числом композиций в каждом из них. Функция
COUNT вычисляет количество записей с одинаковыми значениями

 а пред-

ложение GROUP BY

 гарантирует, что будет возвращена только одна запись

для каждой группы совпадающих значений MUSIC_ID.

Использование предложения HAVING

SQL не разрешает ссылаться на агрегирующие функции в контексте предложения

WHERE. Но иногда необходимо гарантировать соответствие возвращаемого набора данных
определенным условиям. Предложение HAVING подобно WHERE, в частности, оно помогает
ограничить объем множества данных, получаемых в результате выполнения SELECT.

HAVING позволяет включать любое число предикатов, объединенных посредством

булевых логических операторов.

Листинг 16.3. демонстрирует пример использования предложения HAVING и оправ-

данного применения вложенного запроса.

Листинг 16.3. Пример использования предложения HAVING и вложенного запроса

SELECT * FROM Music WHERE Id

(SELECT Music Id FROM Tracks

280

Часть V. Программирование и базы данных Access

GROUP BY
HAVING

 >

| Строка 1 содержит заголовок внешнего запроса, а текст подчиненного запро-

са расположен в строках

 Подзапрос группирует записи таблицы TRACKS

в соответствии со значениями поля

 Предложение HAVING в стро-

ке 4 осуществляет сравнение суммы продолжительности звучания всех ком-
позиций каждого альбома с константой, равной шести минутам.

В результате выполнения всего запроса будет возвращен набор записей таблицы

MUSIC, для каждой из которых существует внешний ключ из таблицы TRACKS и удовле-
творяется условие подчиненного запроса. Строка 4 демонстрирует пример употребления
встроенной SQL-функции CDATE, выполняющей преобразование числа или строки в
значение типа DATETIME. В нашем случае с помощью функции CDATE осуществляется
сопоставление суммы временных интервалов (длительностей), выраженных в секундах, с

литеральной константой, равной шести минутам. Примеры использования некоторых
встроенных функций SQL рассмотрены ниже, в одноименном разделе этой главы.

Объединение таблиц

Одна из основных целей, которая была поставлена и достигнута разработчиками мо-

дели реляционной базы данных, связана с необходимостью исключения неоправданных

потерь пространства для хранения данных. В прежние времена программисты, которые

создавали базы данных, должны были заранее фиксировать число полей таблицы, тре-

буемых для хранения элементов информации определенных типов — скажем, телефон-

ных номеров. В связи с этим проблем возникало немало: программист предусмотрел,

например, два таких поля, а в некоторых случаях следовало бы иметь три.

Что делал несчастный с подобной "плоской" базой данных, в которой все данные

о некотором объекте должны были содержаться в пределах единой монолитной запи-

си? Да, он вынужден был изменять структуру таблицы, добавляя в нее недостающие

поля. Через какое-то время все мучения повторялись. А что происходило с теми запи-

сями, в которых по-прежнему хранились два телефонных номера, а не три или четы-

ре? Добавленные поля оставались пустыми, невостребованными, т.е. место в таблице

(и на диске) безвозвратно терялось.

Те, кто старался предвидеть ход событий, сознательно проектировали таблицы, со-

держащие множество "дыр". Приверженцы

 крайности, пытавшиеся учесть ка-

ждый лишний байт, вынуждены были постоянно "упражняться" с процедурами рест-

руктуризации базы данных и все время исправлять тексты прикладных программ.

Мрачная картина, не правда ли? Но тут на помощь пришли реляционные базы данных

(от англоязычного термина Relational Databases —

 —

 Реляционная

модель позволяет распределять порции информации, имеющей отношение к определен-

ному объекту, по нескольким таблицам вместо одной, монолитной и нерасчленимой. Ка-

ждая таблица предназначена для хранения определенного подмножества данных.

Например, база данных о покупателях может состоять, скажем, из двух таблиц, од-

на из которых содержит некоторые "статические" поля (имя, почтовый адрес и пр.), а

другая предназначена для хранения переменного числа записей, относящихся к каж-

дому покупателю (например, информации о номерах кредитных карточек). Теперь на-

бор кредитных карточек каждого покупателя будет описываться именно таким коли-
чеством записей таблицы, какое нужно, — не больше и не меньше.

Реляционные базы данных предполагают наличие механизма объединения храня-

щейся в нескольких таблицах информации об объекте в целостную "картинку". Пре-

жде для решения подобной задачи достаточно было переместиться к конкретной за-

писи единственной таблицы, — конечно, такой способ намного проще, но он сопря-

жен с потерями, о которых мы говорили выше.

16-й час. Применение языка SQL 281

Число таких таблиц реляционной базы данных, которые содержат однородные

порции определенной информации (имеющей отношение к объектам одного типа), не
ограничено — их может быть две, три и более. Объединение выполняется с помощью

дополнительных

 ключевых

 столбцов и, безусловно, более трудоемко в практической

реализации по сравнению с методами обработки "плоских" таблиц — но выигрыш,
тем не менее, очевиден.

Процесс и результат сбора таких данных об определенном объекте, которые хранятся

в нескольких таблицах, принято называть

 объединением

 таблиц. В разделе "Предложение

WHERE и вложенные команды SELECT" речь шла об одном из вариантов этого меха-
низма — так называемом неявном объединении, когда несколько однородных полей

данных из нескольких таблиц сопоставляются в контексте предложения WHERE. В этом

случае возвращаемый набор данных будет содержать все записи с найденными соответ-
ствиями. Разумеется, это полезно. Однако ситуация может усложниться, как в примере,
рассмотренном выше (в нем речь шла о таблицах,

 сведения о покупателях и

их кредитных карточках), в случае, если у каких-либо покупателей просто нет кредит-
ных карт. Просмотрите такой гипотетический запрос:

SELECT

 FROM Customer, Credit_Cards

WHERE

 =

(Выражение Customer. Id означает ссылку на поле ID таблицы CUSTOMER. Это

пример использования общеупотребительного — полного — синтаксиса обращения к
полю таблицы.) Как известно,

 — внешний ключ, ссы-

лающийся на поле CUSTOMER. ID. Если таблица

 не содержит сведений

о кредитных карточках какого-либо покупателя, предложение WHERE не "сработает" и
в возвращенном наборе данных этот покупатель вообще не будет упомянут. Теперь

представьте последствия подобного поведения программы, обрабатывающей счета за

услуги, которые фирма предоставила потребителям: "незамеченные" счастливчики

торжествуют, вы, автор шедевра, уволены, у шефа инфаркт, компания — на грани
банкротства. ("А в остальном ... все

Это как раз тот случай, когда вас выручит выражение на основе служебного слова

JOIN. Оператор JOIN работает с двумя аргументами-таблицами: первую называют

 ле-

вой,

 а вторую —

 правой.

 Существует три разновидности конструкций JOIN — INNER

JOIN, LEFT JOIN и RIGHT JOIN. Каждая из них служит определенной цели, и о них

будет рассказано более подробно.

Оператор INNER JOIN

Конструкция INNER JOIN равнозначна условию эквивалентности, используемому в

предложении WHERE. Оператор INNER JOIN позволяет возвратить все записи, для ко-
торых выполняется условие равенства содержимого столбцов двух объединяемых таб-
лиц. Приведем соответствующий пример:

SELECT *

FROM Music INNER JOIN Tracks ON

 =

Данная команда возвратит все записи таблиц MUSIC и TRACKS, для которых

MUSIC. ID =

 ID. Она равносильна следующему выражению:

SELECT

 FROM Music, Tracks

WHERE

 =

Оператор

 JOIN

Оператор LEFT JOIN применяется в тех случаях, когда следует вернуть все записи

левой таблицы и только те строки правой, значения полей которых соответствуют

данным левой таблицы. Поэтому в полях

 таблицы возвращенного множества

данных допускаются значения null. Рассмотрим пример:

282 Часть V. Программирование и базы данных Access

SELECT *

FROM Music LEFT JOIN Tracks On

 =

В результате выполнения указанного запроса будут возвращены все записи табли-

цы MUSIC — даже те, для которых в таблице TRACKS нет соответствий. В последнем
случае поля результата, относящиеся к таблице TRACKS, окажутся пустыми.

Оператор RIGHT JOIN

Конструкция RIGHT JOIN прямо противоположна по назначению оператору LEFT

JOIN, рассмотренному выше. При использовании RIGHT JOIN возвращенный набор

данных будет содержать все записи правой таблицы и только те строки левой, для ко-
торых в правой таблице имеются соответствия. Например:

SELECT *

FROM Music RIGHT JOIN Tracks On

Результат выполнения запроса будет содержать все записи таблицы TRACKS, вклю-

чая те, для которых отсутствуют ключевые значения в таблице MUSIC.

Объединение запросов

Два запроса SQL, возвращающие одинаковое число полей совместимых типов,

разрешается объединять с помощью служебного слова UNION. Запросы не зависят от
соседних, но выполняются вместе, как одна команда SQL, давая в результате единый
набор данных. По умолчанию оператор UNION устраняет из возвращенного множества
данных повторяющиеся строки. Чтобы в результат включались все записи, после опе-
ратора UNION необходимо добавить служебное слово ALL.

Никто не запрещает вам использовать конструкции UNION, действующие как единст-

венное выражение SELECT с оператором OR, хотя это не самый удачный выбор. Лучший
вариант применения связан с необходимостью сочетания в одном возвращенном наборе

данных, близких по природе, но расположенных в разных таблицах. Приведенный ниже

пример возвращает значения столбца ID из таблиц MUSIC и TRACKS.

SELECT Id FROM Music

UNION

SELECT Id FROM Tracks

В результате будут получены все значения столбцов ID таблиц MUSIC и TRACKS.

Конечно, этот пример нельзя назвать полезным с практической точки зрения — он
просто демонстрирует технику работы. Но если предположить, что у вашего друга есть
собственная фонотека (и своя таблица Access такой же структуры MUSIC) и вы хотели
бы составить общий каталог записей, тогда UNION — как раз то, что нужно.

Переименование столбцов результата

Встречаются ситуации, когда бывает полезным объединить данные двух или более

столбцов результата выборки в один столбец. Более детально рассмотрим столбец
ARTIST таблицы MUSIC. Если необходимо отсортировать записи по фамилии испол-
нителя, без дополнительного кода сделать это будет довольно трудно. Невольно на-
прашивается вывод о необходимости разбиения столбца ARTIST на два — скажем,

FIRST_NAME и LAST_NAME. Действительно, это достаточно гибкое решение. Но возни-

кают

 удастся ли в дальнейшем получать такие же результаты (т.е. с кор-

ректно отформатированным полным именем исполнителя), как и прежде.

Проблема решается с помощью средств переименования столбцов результата за-

проса. SQL позволяет объединять данные нескольких столбцов в один и присваивать
ему новое имя посредством оператора AS. Предположим, что мы все-таки расчленили

 час. Применение языка SQL 283

столбец ARTIST на два,

 и

 предназначенных для хранения

имен и фамилий. Тогда выражение запроса, возвращающего полные имена исполни-

телей, могло бы выглядеть так:

SELECT First_Name + ' ' + Last_Name AS Artist FROM Music

ORDER BY

В новой редакции таблицы

 уже нет столбца с именем

 — вместо

него созданы столбцы FIRST_NAME и LAST_NAME. Приведенный выше запрос
"склеивает" значения имен и фамилий в единое целое под привычным именем -

ARTIST. Поскольку фамилии и имена хранятся отдельно, стало возможным использо-
вать предложение ORDER BY

Оператор AS особо пригодится в тех случаях, когда результат отбора
содержит вычисляемые значения или неудачно названные столбцы
таблиц.

Разбиение столбца ARTIST на две составляющие — пример, полезный сам по себе.

В этом случае вы получаете более гибкие возможности упорядочения и поиска данных.

Добавление записей

Команда INSERT INTO позволяет добавлять записи в таблицу базы данных и до-

пускает несколько способов применения. Все они рассмотрены ниже.

Добавление данных в указанные поля

Наиболее употребительный вариант использования команды INSERT INTO преду-

сматривает добавление записи в существующую таблицу с указанием списка полей.

Ниже приведена синтаксическая формула подобного выражения:

INSERT INTO

 [,

  . . . ] )

VALUES

 [, Значение2,

В верхнем регистре набраны служебные слова SQL. После фразы INSERT INTO

указывается имя таблицы, за которым следует список наименований полей, заклю-
ченный в круглые скобки. Список может содержать только те поля, в которые вы хо-
тите занести значения (если поле помечено признаком обязательного заполнения, его
имя должно присутствовать в списке. —

 Прим.

 Количество значений, перечис-

ленных в круглых скобках после служебного слова VALUES, и их типы должны соот-

ветствовать содержимому списка полей.

Следующий пример иллюстрирует процедуру пополнения реестра музыкальной

коллекции, рассматриваемой ранее, данными о новом приобретении — очередном

компакт-диске Джонни Кэша (Johnny Cash).
INSERT INTO Music

 Title, Format, Publisher)

VALUES

 CASH AT FOLSOM PRISON AND

После выполнения этой команды в таблицу MUSIC будет добавлена запись со сле-

дующими значениями полей: FIRST_NAME =

 =

=

 CASH AT FOLSOM PRISON AND SAN

 FORMAT =

PUBLISHER =

 Обратите внимание на то, что не указано имя поля пер-

вичного ключа ID и соответствующего ему значения, поскольку это поле снабжено
признаком автоматического заполнения

284 Часть V. Программирование и базы данных Access