Горев А., Макашарипов С., Ахаян Р. Эффективная работа с СУБД
Подождите немного. Документ загружается.
Sub Кнопка40_Click()
On Error GoTo Err_Кнопка40_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, ,_ acMenuVer70
Exit_Кнопка40_Click:
Exit Sub
Err_Кнопка40_Click:
MsgBox Err.Description
Resume Exit_Кнопка40_Click
End Sub
По сути, код события Click для этой кнопки просто выполняет команду Найти меню Правка. С
тем же успехом вы могли создать макрос с единственной макрокомандой Команда меню,
имеющей аргументы:
• Строка меню - Форма
• Название меню - Правка
• Команда - Найти
После этого просто перетащите графический образ макроса из вкладки Макрос в любое место
формы, и вы получите кнопку, которая будет выполнять то же самое, что и кнопка, созданная
Мастером, а если вы назовете макрос "Поиск записи", то и надпись на кнопке будет аналогичной.
Ваш макрос делает то же, что и следующая строчка из процедуры, созданной Мастером:
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Выполнить этот макрос, когда не активна строка меню Форма, невозможно, впрочем, как
невозможно воспользоваться и методом объекта DoCmd DoMenuItem из процедуры Мастера. Мы
все время зависим от наличия на экране меню Форма. Единственное преимущество кода события
по сравнению с нашим макросом заключается в том, что первый обработает ошибку, когда
выяснит, что не может выполниться. Будет выдано сообщение, связанное с этой ошибкой, а наш
макрос просто "повиснет", выведя на экран окно обработчика ошибок макросов. Впрочем, даже
если меню присутствует на экране, то совсем не обязательно, что будет найдена запись,
соответствующая введенному образцу.
Если нужная запись не будет найдена, на экран будет выведено стандартное сообщение
Access: "Поиск записей в приложении `Microsoft Access' завершен. Элемент не найден"
В качестве примера организации поиска данных рассмотрим создание своей собственной
формы, которая будет возвращать то сообщение, которое мы хотим получить в случае
неудачного поиска.
Пример формы "ПОИСК" вы найдете в файле базы данных AUTOSTORE.MDB, который
находится на дискете, прилагаемой к данной книге. Форма "AUTOMOBIL_PASSENGER_CAR" имеет
кнопку для поиска данных в текущем текстовом поле. При ее нажатии вызывается форма
"ПОИСК".
В модуле Bookmodule объявляются две переменные: LastForm типа Form и lastcntr типа
Control:
Dim LastForm As Form
Dim lastcntr As Control
Первая служит для передачи переменной формы, а вторая для передачи переменной
последнего активного элемента управления для использования их в форме "ПОИСК".
В событии Click кнопки Поиск формы "AUTOMOBIL_PASSENGER_CAR" записываем следующий
код:
Sub Кнопка40_Click()
Set LastForm = Screen.ActiveForm
Set lastcntr = Screen.PreviousControl
mybook=LastForm.Bookmark
PoiskSub
End Sub
Процедура PoiskSub служит для установки флажка "В текущем поле". Дело в том, что
последний элемент управления, который был активным до нажатия на кнопку Поиск, может быть
converted to PDF by HupBaH9I
не связанным ни с каким полем - для него будет отсутствовать понятие "текущее поле".
Следовательно, флажок нужно сделать недоступным, так как его значение используется для
дальнейшего поиска:
Public Sub PoiskSub()
DoCmd.OpenForm "Поиск"
Select Case lastcntr.ControlType
Case 109, 106, 110, 111
If Not IsNull(lastcntr.ControlSource) Then
Forms![Поиск].[Flag3] = True
Else
Forms![Поиск].[Flag2] = False
Forms![Поиск].[Flag3] = False
End If
Case Else
Forms![Поиск].[Flag2] = False
Forms![Поиск].[Flag3] = False
End Select
End Sub
Таким образом мы передали в процедуру обработки события Click последний активный
элемент и последнюю активную форму как параметры и при этом выяснили, был ли последний
активный элемент управления связанным или нет.
Для поиска мы используем методы из серии Find объекта RecordSet и метод FindRecord объекта
DoCmd.
Набор данных для формы мы создаем с помощью свойства RecordSource. Это может быть
таблица, запрос или выражение SQL. С помощью свойства RecordsetClone мы получаем ссылку на
объект Recordset, указанный в свойстве RecordSource. Если источник данных для формы
"AUTOMOBILE PASSENGER CAR" - таблица с тем же именем, то с помощью свойства RecordsetClone
формы мы ссылаемся на объект, который создается с помощью следующей конструкции:
Dim myDb As DataBase, rst As Recordset
Set Mydb = dbEngine.Workspaces(0).Databases(0)
Set rst = MyDb.OpenRecordset("AUTOMOBIL PASSENGER CAR", _ dbOpenDynaset)
Учтите, что объект Recordset функционирует сам по себе, то есть перемещение с помощью
методов серии Move и Find не влияет на текущую отображаемую в форме запись. С целью
синхронизации перемещения необходимо использовать свойство Bookmark, которое определяет
текущую запись. Для этого свойство Bookmark формы сохраняют в переменной строкового типа,
а потом присваивают это значение свойству Bookmark объекта Recordset, на который ссылаются
с помощью свойства RecordsetClone формы.
В коде кнопки Поиск формы "AUTOMOBIL PASSENGER CAR" имеется следующая строчка:
mybook=LastForm.Bookmark
В коде кнопки Найти для формы "ПОИСК" свойство Bookmark устанавливается следующим
образом:
LastForm.RecordsetClone.Bookmark=mybook
После поиска, если он удачен, значение свойства Bookmark объекта Recordset присваивается
свойству Bookmark формы.
Как вы помните, весь этот процесс мы затеяли для того, чтобы выводить свое собственное
сообщение при неудачном поиске. При этом мы рассмотрели вопросы взаимодействия формы с
объектами доступа к данным (DAO).
Как правило, скорость работы в Access понижается с увеличением числа записей в таблице,
что, впрочем, происходит и во многих других приложениях. Для того чтобы снизить негативные
последствия этого явления, в Access предлагается использовать развитую систему фильтров.
Фильтры можно устанавливать при работе с формой с помощью меню, макрокоманд или методов
объекта DoCmd в программном коде.
Например, используя опцию "Фильтр по выделенному", вы можете, последовательно выделяя
поля, устанавливать достаточно сложные фильтры. Последовательность действий выглядит
следующим образом:
converted to PDF by HupBaH9I
1. Открытие формы.
2. Выделение любого поля или части поля.
3. Выполнение команды Фильтр по выделенному из меню Записи или нажатие кнопки с тем
же названием на панели инструментов Режим Формы.
4. Повторение вышеприведенной последовательности действий с другими полями.
Можно открыть форму в режиме "Форма с установленным фильтром". Это делается с помощью
макрокоманды OpenForm или с помощью метода OpenForm объекта DoCmd:
DoCmd.OpenForm "automobile passenger car",,,"key_model=1"
Кроме того, вы можете установить источником данных для формы параметрический запрос,
который позволяет значительно уменьшить количество записей, обрабатываемых в текущем
сеансе работы формы.
При работе с объектами DAO можно динамически изменять размер выборки, тем самым
значительно ускоряя доступ к нужным вам данным. В качестве примера приведем две
процедуры. Одна ищет запись в неотфильтрованном наборе данных, другая использует фильтр.
Процедура поиска в неотфильтрованном наборе данных:
Public Sub findexp()
*** В данной процедуре ищется запись, и в окне отладки
*** выводится время поиска в секундах
*** Вы можете подставить свою таблицу
*** и свои поля
*** При этом имеет смысл экспериментировать
*** на таблице с количеством записей более 1000,
*** либо использовать WINAPI для фиксирования времени
*** поиска
Dim myDb As DATABASE
Dim myRst As Recordset
Dim t1 As Double
Dim t2 As Double
Set myDb = DBEngine.Workspaces(0).Databases(0)
*** Можете подставить имя вашей таблицы здесь
Set myRst = myDb.OpenRecordset("first", _ dbOpenDynaset)
t1 = Now()
*** Соответственно поле и критерий поиска
*** тоже необходимо подставить свои
myRst.FindFirst "first like 'Чф*'"
t2 = Now()
If Not myRst.NoMatch Then
Debug.Print myRst!First, myRst!Third
Else
Debug.Print "Пролет"
End If
*** Здесь можете использовать свою
*** функцию для форматирования вывода времени поиска
Debug.Print DateDiff("s", t1, t2)
End Sub
Поиск в отфильтрованном наборе данных:
Public Sub findexpFilt()
Dim myDb As DATABASE
Dim myRst As Recordset, NmyRst As Recordset
Dim t1 As Double
Dim t2 As Double
Dim i As Long
Set myDb = DBEngine.Workspaces(0).Databases(0)
Set myRst = myDb.OpenRecordset("first",_ dbOpenDynaset)
myRst.Filter = "third >> 56700 and third << 58000"
Set NmyRst = myRst.OpenRecordset()
t1 = Now()
NmyRst.FindFirst "first like 'Чф*'"
converted to PDF by HupBaH9I
t2 = Now()
If Not NmyRst.NoMatch Then
Debug.Print NmyRst!First, NmyRst!Third
Else
Debug.Print "Пролет"
End If
Debug.Print DateDiff("s", t1, t2)
End Sub
7.2. Создание SQL-запросов
Как уже было отмечено, всем, кто хочет работать с базами данных, необходимо знать язык
SQL, который, по сути, стал стандартом для работы с базами данных.
В этом параграфе вы узнаете:
• сферы наиболее эффективного применения языка SQL при разработке систем обработки
данных;
• основные виды запросов, которые могут быть составлены для работы с данными;
• назначение основных составляющих элементов команд SQL;
• особенности составления команд SQL в рассматриваемых средствах разработки.
К сожалению (а может быть наоборот), рынок программного обеспечения развивается не по
принципам единого планирования. Каждый продукт, который использует SQL, применяет его
диалект, как правило, отличающийся от ANSI-стандарта этого языка. Обидно, - иначе бы мы
могли одинаково обращаться к данным любого продукта, используя одинаковые языковые
конструкции. Но в принципе, все еще может быть, а пока приходится довольствоваться тем, что
есть. А есть масса приложений для разработки баз данных, которые при этом данные хранят в
своем формате, как правило, секрета не представляющем, и все без исключения соревнуются
друг с другом в как можно большем количестве отличий от общепринятых стандартов ANSI SQL.
Однако общаться программам с данными чужих форматов необходимо, и существет несколько
путей для этого общения. Перечислим некоторые из них.
Вы покупаете продукт, который поддерживает несколько форматов. Или - создаете продукт,
который поддерживает несколько форматов. Есть прекрасные примеры: Lotus Approach, Microsoft
Access, продукты фирмы Borland Dbase и Paradox читают форматы друг друга. Но почти
наверняка вы не найдете приложения, которое поддерживает все форматы. А на практике
работать с данными иного формата приходится очень часто. Даже если вы очень упорно будете
избегать встречи с другими форматами, она все равно когда-нибудь произойдет. Бесспорно,
можно воспользоваться следующим способом для работы с внешними форматами, но он таит свои
сюрпризы.
Вы используете операции экспорта или импорта. Опять же необязательно, что у вас будут в
наличии все необходимые конверторы. Но все же допустим, что они есть. Вы импортировали
данные. Отредактировали. Сколько вы поставите на то, что изменения отразились в исходных
данных, то есть в файле, где они хранятся в родном формате? Готовы с вами поспорить на любую
сумму. Можно, конечно, провести обратную операцию и экспортировать отредактированные
данные в исходный формат. Но, согласитесь, что это очень непродуктивный путь для
редактирования одной записи в базе данных, где их 100000, а ведь очень часто их бывает много
больше. Кроме этого, вам становятся недоступными все триггеры, бизнес-правила и хранимые
процедуры для данных внешнего формата, если они, конечно, используются.
Способ, который звучит наиболее современно и который делает вас поистине всесильным, но
оставляющий все же место для всевозможных придирок по отношению к себе. Это, само собой
разумеется, - OLE 2.0. А в OLE 2.0 нас больше всего интересует его составная часть OLE
Automation. Термин, который редко переводится, и мы тоже не возьмемся за это. Придирки здесь
могут возникнуть со стороны консервативных любителей DOS. Дело в том, что технологию OLE
Automation невозможно реализовать в рамках этой операционной оболочки. Позволим себе
лирическое отступление. Вам наверняка придется слышать патетические высказывания,
изрекаемые достаточно образованными людьми. Они будут гласить, что на их программах
работают сотрудники, которым надо только стучать по клавиатуре. Или что наращивание
мощности техники для обработки информации - это от лукавого. Не слушайте их. Нам нужны
различные, мощные, еще мощнее, самые мощные средства обработки данных. При этом неважно,
где вы работаете. Это может быть ЖЭК, банк, спортклуб. Каждый грамм информации имеет право
на существование, даже количество бензина, которое вы сегодня израсходовали, отлучившись на
служебной машине к вашей любовнице. Не слушайте их, приземленных противников прогресса,
которым легче обслуживать свои любительские творения, чем осваивать новые вершины самой
нужной профессии современности - специалиста по обработке информации. Бесспорно, на смену
converted to PDF by HupBaH9I
OLE придет нечто более совершенное, но главное, что это не будет шагом назад. OLE Automation
- способ управлять и считывать информацию об объектах внешнего по отношению к вашему
приложения. Впрочем, разговор об этом чуть ниже.
Следующий способ имеет меньше ограничений. Мы говорим об ODBC (Open Database
Connectivity), что можно перевести как Открытый Доступ к Базам Данных. Конечно, ваше
приложение должно поддерживать эту технологию. Установив связь с исходной базой данных,
далее вы используете набор SQL pass-through функций для выборки, обработки и модификации
исходных данных. Наборы этих функций для разных продуктов неодинаковы, но в целом можно
говорить о наборе различных реализаций SQL pass-through функций. Изучив их реализацию в
одном языке, вы легко можете воспользоваться аналогичной в другом. Теперь вы очень близки к
тому, что во внешней базе данных будут отражаться любые изменения, сделанные в таблицах
при редактировании данных. Во всяком случае, необходимый инструмент у вас есть.
Настоятельно рекомендуем попробовать. Далее мы обязательно обсудим эту технологию более
подробно. Скажем прямо, она-то и является краеугольным камнем всей этой книги. В противном
случае это была бы уже совсем другая книга.
Теперь поговорим о реализации SQL языка в каждом приложении. Для общения с данными
внешнего формата на его родном языке была изобретена технология ODBC. Впрочем, то же самое
мы можем сказать и об OLE 2.0. Запутанно? Осталось только набраться терпения. А дальше мы
будем говорить о SQL языке.
В наше время только ленивый не пытается программировать. Тем более, что основные
операторы любого из языков достаточно просты, что вполне логично. Быстренько набросали
схему данных, загнали все в простую плоскую таблицу, запустили Мастер и пошли искать
заказчиков. Дальше может быть хуже, а может быть лучше. Поток информации разрастается,
одной таблицы мало (при этом кто-то должен подсказать, что данные из разных таблиц можно
связывать), и, когда звучит простой вопрос вашего начальника "А сколько африканского кофе
мы продали в Тьмутараканскую область по цене между 6000 и 7000 рублей?", - вы берете листок
бумаги, счеты (как наиболее надежное средство учета) и ручку. Правильно ли это? Каждая точка
зрения имеет право на существование. В том числе и вашего начальника - в вас начинают
сомневаться. Но не пугайтесь, у вас в руках наша книга. Почти 90% дела вы сделали, осталось
только проявить чуточку усердия и терпеливо дочитать до конца.
Вспомним, что, несмотря на наличие стандарта SQL, существуют различные реализации или
диалекты этого языка, которые являются либо вспомогательными инструментами, либо основой
различных систем управления базами данных. Есть и более сложные варианты, когда некоторые
СУБД имеют специальные библиотеки для доступа к своим данным для таких языков, как C или
Visual Basic с помощью SQL команд приложения. В большинстве случаев к данным любого
приложения надо обращаться на его родном SQL диалекте. То есть, находясь в среде FoxPro, вам
будет сложно сделать запрос SQL к таблице формата Paradox, не используя ODBC и SQL pass-
through. Хотя есть приятные исключения, то есть приложения, которые незаметно для вас
проводят всю тяжелую работу по представлению чужих данных в нужном для запроса формате.
В самом простом варианте команда, которая выбирает все записи из одной таблицы, выглядит
так:
SELECT mytable.* FROM mytable
Впрочем, можно еще короче:
SELECT * FROM mytable
Таким образом, выбраны все записи из таблицы Mytable.
В зависимости от того, какое приложение вы выбрали для изучения SQL, вас ожидают
совершенно разные пути ввода данной команды в память вашего компьютера.
Рассмотрим более сложные варианты выбора нужных данных.
Запросы выборки
Начнем с Visual FoxPro. Здесь у нас есть целых три варианта выполнения команд SQL:
1. Набрать команду в окне Command.
2. Создать программу, в которую включить нужную команду. Этот способ более эффективен,
так как у вас есть возможность сохранить возможно высшей степени изощренную
команду для дальнейшего использования.
3. Способ, наиболее привлекательный для начинающих программистов, - использовать
Relation Query By Example (RQBE) - Реляционный запрос по образцу.
converted to PDF by HupBaH9I
Для того чтобы использовать RQBE, вам достаточно выполнить команду New из меню File, а
затем выбрать тип создаваемого файла - Query. RQBE - это интерактивная среда, в которой вы
формируете запрос в основном с помощью мыши, перетаскивая нужные вам поля в
определенные области диалогового окна.
Результатом будет запрос, оформленный в виде файла с расширением QPR. При этом
выполнить запрос вы сможете с помощью команды DO. Если вы изучаете SQL для использования
в FoxPro, то пользуйтесь любым из вышеизложенных способов, но если вам нравится RQBE, то
все равно заглядывайте в полученный код, потому что это поможет вам лучше понять язык.
Разбирайте каждое слово в полученной команде, и успех в недалеком будущем вас будет
ожидать всенепременно.
В среде СУБД Microsoft Access нет командной строки в том понятии, в котором она существует
в Visual FoxPro. Для того чтобы создать запрос, в контейнере БД выберите страницу Запросы,
нажмите на кнопку Создать. На экране появится Конструктор запросов. При этом вы легко будете
переходить из Конструктора запросов в окно редактирования SQL или к результатам выполнения
запроса.
Среди рассматриваемых средств разработки СУБД Microsoft Access имеет наиболее мощные
средства визуального конструирования запросов. Здесь вы можете создать не только запрос на
выборку, но и запросы обновления, удаления, создания таблиц, добавления. Об этих запросах
мы поговорим ниже.
Visual Basic использует для работы с данными процессор баз данных Microsoft Jet, такой же,
как и Access. Но в связи с тем, что Visual Basic является универсальным средством разработки,
здесь не присутствуют такие развитые средства визуального проектирования, как в Microsoft
Access. В то же время, так как все объекты DAO - объекты по доступу к данных - доступны и из
Visual Basic, то мы вполне можем добиться функциональности, которой добиваемся в Access.
Другое дело, какой ценой мы этого достигнем.
Напомним, что в Microsoft SQL Server для того, чтобы выполнить команду SQL, необходимо
запустить приложение iSQLW. Вы можете набрать требуемую команду и запустить ее на
выполнение. При этом на вкладке Query Results вы увидите полученный результат.
Зная SQL, вы можете значительно увеличить ваши возможности при работе с электронными
таблицами Microsoft Excel. Несмотря на то, что вы можете непосредственно читать некоторые
форматы данных, иногда полезно немного сократить в объеме количество записей перед
обработкой в Excel. В этом вам может помочь приложение Microsoft Query, которое можно
использовать для построения запросов к данным внешнего и внутреннего формата Excel с целью
уменьшения объема данных, необходимых для текущего сеанса работы.
Итак, везде присутствует SQL. Где-то как основное средство работы, где-то как
вспомогательное.
Мы думаем, что теперь вы вполне готовы к разговору о более сложных запросах. Посмотрим
еще раз на команду SQL, о которой мы уже говорили:
SELECT * FROM mytable
Заменим абстрактную таблицу Mytable таблицей из примера к этой книге. Одна из таблиц
называется Model, в ней хранятся данные о всех моделях автомобилей, которые задействованы в
реселлерской деятельности компании. К примеру, нам не нужны для дальнейшей обработки все
поля из этой таблицы. Чтобы выбрать нужные, мы должны их просто перечислить:
SELECT name_model, swept_volume, quantity_drum FROM model
Может случиться, что вам захочется иметь более наглядные заголовки вместо часто
абстрактных наименований английскими буквами или даже на английском языке. Тогда мы
используем ключевое слово AS после названия колонки. Перепишем наш запрос в другом виде:
SELECT name_model AS наименование, ;
swept_volume AS рабочий_объем, ;
quantity_drum AS кол_цилиндров ;
FROM model
Этот синтаксис верен для Visual FoxPro и Microsoft Access. При работе с Microsoft SQL Server
подобная задача выполняется немного по-другому:
SELECT 'наименование'= name_model, :
'рабочий_объем'= swept_volume,;
'кол_цилиндров'= quantity_drum ;
FROM model
converted to PDF by HupBaH9I
Итоговый запрос может содержать дублирующие друг друга по всем полям записи. Если вам не
нужна избыточная информация, то введите после SELECT ключевое слово DISTINCT:
SELECT DISTINCT 'наименование'= name_model, :
'рабочий_объем'= swept_volume,;
'кол_цилиндров'= quantity_drum ;
FROM model
Противоположным по смыслу DISTINCT ключевым словом является ALL, как правило, оно
является значением по умолчанию и его можно не приводить. Но это уже зависит от вашего
стиля программирования. Иногда считается правильным указывать все значения и ключевые
слова для лучшей "читабельности" кода.
Порядок, в котором поля выводятся в итоге, зависит только от того, в какой
последовательности вы перечислите их в своем запросе. Если необходимо во второй колонке
вывести количество цилиндров, то следует изменить порядок указания полей в списке
выводимых результатов:
SELECT name_model, quantity_drum ,swept_volume FROM model
Теперь количество цилиндров будет указываться сразу после наименования модели
автомобиля.
Помимо полей из таблиц, в список выводимых колонок можно включать функции, переменные,
константы и выражения. Например, если вы хотите особо отметить каждую третью запись, вы
можете выполнить следующий запрос:
SELECT IIF(MOD(RECNO(),3)=0,"Y","N"),RECNO(),cost;
FROM "automobile passenger car"
При этом в каждой третьей записи в результирующем курсоре в первой колонке появится
буква "Y".
В запросе можно использовать пользовательскую функцию и выводить ее результаты в
курсор. Правда, здесь накладывается некоторое ограничение: желательно, чтобы функция не
перемещала указатель записи в таблице, из которой выбираются данные.
До сих пор мы рассматривали организацию запросов к одной таблице. В реальной жизни это
довольно редкий случай. Как правило, информацию нужно получать их двух и более таблиц. При
этом нужно учитывать факт, что таблицы должны быть связаны между собой, при этом
необязательно каждая с каждой, можно связать две таблицы через одного или нескольких
посредников.
Если вы хотите узнать, в какой стране находится штаб-квартира фирмы, автомобиль которой
приобрел каждый покупатель, то нужно создать вот такой запрос:
SELECT Customer.name_customer, Country.country_name, ;
Model.name_model,;
Firm.name_firm;
FROM "auto store!customer", "auto store!account",;
"auto store!automobile passenger car" Automobile_passenger_car,;
"auto store!model", "auto store!firm",; "auto store!country";
WHERE Customer.key_customer = Account.key_customer;
AND Automobile_passenger_car.key_auto = Account.key_auto;
AND Model.key_model = ;
Automobile_passenger_car.key_model;
AND Firm.key_firm = Model.key_firm;
AND Country.key_country = Firm.key_country
В итоге получится следующая выборка:
NAME
_CUSTOMER
COUNTRY
NAME
_MODEL
NAME
_FIRM
Безумные медведи Италия 145 1.4 Alfa Romeo
Общество нац. героев
Италия 145 1.4 Alfa Romeo
Пронырливые волки Италия 146 1.9 Alfa Romeo
Угрюмые слоны Германия M3 3.0 BMW
converted to PDF by HupBaH9I
Голодные лисы Германия 740i 4.0 BMW
Голодные лисы Германия 840Ci 4.0
BMW
Угрюмые слоны Германия 840Ci 4.0
BMW
В этом запросе появилось предложение WHERE, о котором мы прежде не упоминали.
Обратившись к теории, скажем, что это слово позволяет нам определить предикат, который будет
обрабатывать каждую строку таблиц, и, в зависимости от его истинности, нужные нам поля из
этих строк окажутся в итоговом запросе. В нашем конкретном случае мы просто определяли, есть
ли соответствующие значения по связующему полю для таблицы в таблице, с которой мы ее
связываем.
Например, строчка
Automobile_passenger_car.key_auto = Account.key_auto
ограничит нашу выборку только теми записями, которые имеют одинаковые значения и в
таблице Automobile_passenger_car, и в таблице Account по полю key_auto. Проведя
соответствующее исследование для других связующих пар, мы поймем, каким образом
ограничивается набор выбранных данных. Вышеприведенный синтаксис правилен для любого
диалекта SQL, используемого в рассматриваемых средствах разработки.
Рассмотрим более простой пример. Нам хочется узнать для автомобиля название его фирмы-
производителя. Запрос будет выглядеть так:
SELECT model.name_model,firm.name_firm ;
FROM model,firm ;
WHERE model.key_firm = firm.key_firm
Далее рассмотрим, как этот запрос будет выглядеть, если мы построим его с помощью
Конструктора запросов Microsoft Access.
SELECT DISTINCTROW firm.name_firm, model.name_model
FROM firm INNER JOIN model ON firm.key_firm = model.key_firm;
В предложении FROM появилась конструкция:
firm INNER JOIN model ON firm.key_firm = model.key_firm
что в дословном переводе означает: внутренняя связь таблицы Firm с таблицей Model по полю
key_firm. Почему нельзя просто все записать в предикат, составленный с помощью предложения
WHERE? Можно. Но полученный запрос не будет модифицирующим, то есть вы не сможете
изменять данные в запросе, а соответственно и редактировать данные в исходной таблице
посредством запроса.
Ядро базы данных Microsoft Jet - достаточно гибкое средство в отношении типов и форматов
SQL выражений, используемых для создания связей между таблицами. В дополнение к связям,
которые вы можете указать в предикате, составляемом с помощью предложения WHERE, Microsoft
Jet SQL может использовать предложение JOIN, чтобы указать внутреннюю, левую внешнюю или
правую внешнюю связь, а также неэквивалентные связи, в которых связующим критерием
является не равенство значений в связующих полях двух таблиц, а выражение условия, которое
должно возвращать истину.
Внутренняя связь может быть сложная, когда вы связываете несколько таблиц, например:
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm ;
В вышеприведенном примере мы ищем штаб-квартиру фирмы-производителя каждой модели.
Помимо внутреннего (INNER JOIN) объединения, вы можете использовать внешние
объединения, при этом различаются левые внешние и правые внешние объединения: LEFT JOIN и
RIGHT JOIN. Используя левое внешнее объединение, вы получаете в итоговом запросе все записи
из левой таблицы и только те записи из правой таблицы, которые имеют соответствующие
значения по связующему полю в левой таблице.
SELECT DISTINCTROW [automobile passenger car].date_issue, account.account
converted to PDF by HupBaH9I
FROM [automobile passenger car] LEFT JOIN account ON [automobile passenger car].key_auto =
account.key_auto;
Какое объединение использовать, зависит только от того, где расположены сравниваемые
поля в предложении объединения. Совершенно никакого различия в скорости выполнения или
наборе полученных записей вы не получите. Предыдущий запрос мы можем переписать
следующим образом без всякой потери производительности:
SELECT DISTINCTROW [automobile passenger car].date_issue, account.account
FROM account LEFT JOIN [automobile passenger car] ON account.key_auto = [automobile passenger
car].key_auto;
Microsoft Jet возвращает специальное значение NULL во всех полях для записей внешней
таблицы, не содержащих одинаковых значений. Вы можете использовать это свойство для
проверки целостности связи в зависимости от присутствия значения типа NULL во внешней
таблице. Если в итоговом запросе присутствуют значения типа NULL, вы можете быть уверены в
наличии несвязанных записей в дочерней таблице, или, наоборот, есть значения в родительской
таблице, для которых отсутствуют соответствующие значения в дочерней таблице. Если вы
хотите узнать, с каким поставщиком у вас не было сделок, например, за последний месяц, то
лучший и самый быстрый способ это сделать - использовать внешнее объединение.
Для сокращения объема выборки, то есть для получения в итоговом курсоре только тех
данных, которые вас интересуют в конкретный момент, вы должны использовать предикат,
составляемый с помощью предложения WHERE, который служит не только для установления
связей, но и для накладывания фильтров на выбираемые данные.
Допустим, мы хотим выбрать все названия моделей и производящих их фирм в пределах для
конкретной страны, например Италии:
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE country.country_name="Италия" ;
В SQL вы можете использовать логические операторы AND, OR и логическое отрицание NOT.
Добавив всего лишь один оператор NOT перед выражением
country.country_name = "Италия"
мы получаем совершенно противоположную выборку, в которой будут присутствовать фирмы
всех стран, кроме Италии.
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE NOT country.country_name="Италия"
При необходимости получить данные для двух стран мы используем оператор OR. Если нам не
нужны данные по автомобилям из Франции и Италии, мы используем следующий запрос:
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE NOT (country.country_name="Италия" OR country.country_name = "Франция")
В этом случае необходимо верно расставить скобки, чтобы оператор NOT относился и к Италии
и к Франции.
Пока мы использовали только один оператор сравнения - равно (=), - на самом деле их
несколько больше:
• >> - больше;
• << - меньше;
• >>= - не меньше;
• <<= - не больше;
converted to PDF by HupBaH9I
• <<>> - не равно.
Еще раз вернемся к предыдущему запросу, оставив прежнюю цель - не выбирать модели из
Франции и Италии, но при этом используя совсем другие операторы:
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE country.country_name<<>>"Италия" AND country.country_name <<>> "Франция"
Обратите внимание, что мы используем AND вместо OR, иначе в итоговом курсоре были бы
выбраны все записи.
Операторы сравнения могут обрабатывать не только числовые значения, но и символьные.
При этом обрабатывается ASCII-код символа. При необходимости выполнить запрос, в котором
мы хотим выбрать страны, названия которых начинаются на букву "И" и последующие буквы в
порядке алфавита, мы можем использовать следующие команды:
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE country.country_name>>="И"
В предикате, который формируется с помощью предложения WHERE, можно и нужно
использовать помимо вышеприведенных еще несколько операторов, а именно: IN, BETWEEN,
LIKE и IS NULL.
Использование оператора IN позволяет нам по-другому выполнить запрос по выборке данных
по Италии и Франции. Вспомните запрос, в котором мы собирали данные для всех стран, кроме
Италии и Франции. Теперь мы можем записать его с помощью оператора IN
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE country.country_name NOT IN ("Италия", "Франция");
Оператор BETWEEN, строго следуя своему дословному переводу, выводит нам данные в
промежутке между значениями, которые мы укажем в качестве его аргументов. Если вам нужно
выбрать информацию по странам, которые расположены в алфавитном порядке между Италией и
Францией, то используйте запрос, подобный нижеприведенному.
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE country.country_name BETWEEN "Италия" AND "Франция"
При использовании этого оператора помните, что краевые значения также попадают в
результаты запроса. Если вам это не нужно, то с помощью комбинации предикатов предложения
WHERE двух последних запросов вы можете добиться необходимого для вашей работы
результата.
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE country.country_name NOT IN ("Италия", "Франция") AND (country.country_name BETWEEN
"Италия" AND "Франция")
Оператор LIKE позволяет использовать в критерии поиска шаблоны. Если необходимо выбрать
только модели из стран, названия которых начинаются на букву "И", то можно использовать
следующий запрос:
SELECT DISTINCTROW firm.name_firm, model.name_model, country.country_name
FROM model INNER JOIN (firm INNER JOIN country ON firm.key_country = country.key_country) ON
model.key_firm = firm.key_firm
WHERE country.country_name LIKE "И*'"
converted to PDF by HupBaH9I