Спросите экспертов

Как писать запросы Microsoft Access SQL с нуля

Как писать запросы Microsoft Access SQL с нуля

Microsoft Access, пожалуй, самый мощный инструмент во всем пакете Microsoft Office, однако он загадывает (и иногда пугает) опытных пользователей Office. С более крутой кривой обучения, чем Word или Excel, как кто-то должен думать об использовании этого инструмента?

Читатель спрашивает:

У меня проблемы с написанием запроса в Microsoft Access.

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

Я хочу выяснить, какие продукты из таблицы A можно найти в таблице B. Я хочу добавить столбец с именем Results, который содержит имя продукта из таблицы A, если он существует, и имя продукта из таблицы B, если он не существует. в таблице А.

Есть ли у вас какие-либо рекомендации?

Брюс ответ:

Microsoft Access — это система управления базами данных (СУБД), предназначенная для использования на компьютерах под управлением Windows и Mac. Он использует ядро ​​базы данных Microsoft Jet для обработки и хранения данных. Он также предоставляет графический интерфейс для пользователей, что почти исключает необходимость понимания языка структурированных запросов (SQL).

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

Отправная точка

Если вы еще не знакомы с Access или другой RDBMS, я бы посоветовал вам начать с этих ресурсов, прежде чем продолжить:

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

Отношения базы данных и нормализация

Представьте, что вы управляете компанией, продающей 50 различных типов виджетов по всему миру. У вас есть клиентская база в 1250 человек, и в среднем за месяц вы продаете 10 000 виджетов этим клиентам. В настоящее время вы используете одну электронную таблицу для отслеживания всех этих продаж — фактически одну таблицу базы данных. И каждый год добавляет тысячи строк в вашу таблицу.

плоский стол-1 плоский стол-2

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

Если Джоан Смит выходит замуж за Теда Бэйнса и берет его фамилию, все строки, содержащие ее имя, теперь необходимо изменить. Проблема усугубляется, если у вас есть два разных клиента с именем «Джоан Смит». Просто стало намного сложнее поддерживать согласованность данных о продажах из-за довольно распространенного события.

Используя базу данных и нормализуя данные, мы можем разделить элементы на несколько таблиц, таких как запасы, клиенты и заказы.

нормализация

Просто глядя на клиентскую часть нашего примера, мы удалили столбцы для Client Name и Client Address и поместили их в новую таблицу. На изображении выше, я также разбил вещи лучше для более детального доступа к данным. Новая таблица также содержит столбец для первичного ключа (ClientID) — число, которое будет использоваться для доступа к каждой строке в этой таблице.

В исходной таблице, в которой мы удалили эти данные, мы добавили бы столбец для внешнего ключа (ClientID), который ссылается на соответствующую строку, содержащую информацию для этого конкретного клиента.

Теперь, когда Джоан Смит меняет свое имя на Джоан Бэйнс, это изменение нужно сделать только один раз в таблице «Клиент». Любая другая ссылка из объединенных таблиц вытащит правильное имя клиента, а в отчете, который просматривает то, что Джоан купила за последние 5 лет, будут получены все заказы как по ее девичьей, так и по фамилии, без необходимости изменения способа создания отчета. ,

В качестве дополнительного преимущества это также уменьшает общий объем используемого хранилища.

Типы соединения

SQL определяет пять различных типов объединений: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS. Ключевое слово OUTER является необязательным в выражении SQL.

Microsoft Access позволяет использовать INNER (по умолчанию), LEFT OUTER, RIGHT OUTER и CROSS. FULL OUTER не поддерживается как таковой, но с помощью LEFT OUTER, UNION ALL и RIGHT OUTER его можно подделать за счет увеличения числа циклов ЦП и операций ввода-вывода.

Вывод соединения CROSS содержит каждую строку левой таблицы в паре с каждой строкой правой таблицы. Единственный раз, когда я видел соединение CROSS, это нагрузочное тестирование серверов баз данных.

Давайте посмотрим, как работают основные объединения, а затем изменим их в соответствии с нашими потребностями.

Начнем с создания двух таблиц ProdA и ProdB со следующими свойствами проектирования.

доступ столовых DEFS

AutoNumber — это автоматически увеличивающееся длинное целое число, назначаемое элементам по мере их добавления в таблицу. Параметр «Текст» не был изменен, поэтому он будет принимать текстовую строку длиной до 255 символов.

Теперь заполните их некоторыми данными.

доступ столы

Чтобы показать различия в работе трех типов соединений, я удалил записи 1, 5 и 8 из ProdA.

Затем создайте новый запрос, выбрав « Создать»> «Дизайн запроса» . Выберите обе таблицы в диалоговом окне «Показать таблицу» и нажмите «Добавить» , затем « Закрыть» .

новый запрос

Нажмите ProductID в таблице ProdA, перетащите его в ProductID в таблице ProdB и отпустите кнопку мыши, чтобы создать связь между таблицами.

design_view

Щелкните правой кнопкой мыши линию между таблицами, представляющими отношения между элементами, и выберите Свойства соединения .

join_properties

По умолчанию выбран тип соединения 1 (INNER). Вариант 2 — это соединение слева и справа, а 3 — соединение справа.

Сначала мы рассмотрим ВНУТРЕННЕЕ объединение, поэтому нажмите ОК, чтобы закрыть диалоговое окно.

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

дизайн-представление-2

Когда мы запустим запрос (красный восклицательный знак на ленте), он отобразит поле ProductName из обеих таблиц со значением из таблицы ProdA в первом столбце и ProdB во втором.

внутреннее соединение

Обратите внимание, что результаты показывают только значения, в которых ProductID одинаков в обеих таблицах. Даже если в таблице ProdB есть запись для ProductID = 1, она не отображается в результатах, поскольку ProductID = 1 не существует в таблице ProdA. То же самое относится к ProductID = 11. Он существует в таблице ProdA, но отсутствует в таблице ProdB.

дизайн-лента

Используя кнопку «Вид» на ленте и переключившись в представление SQL, вы можете увидеть запрос SQL, сгенерированный конструктором, который использовал эти результаты.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; 

Возвращаясь к представлению «Дизайн», измените тип соединения на 2 (ВЛЕВО НАРУЖНО). Запустите запрос, чтобы увидеть результаты.

left_outer_join

Как видите, каждая запись в таблице ProdA представлена ​​в результатах, в то время как в результатах отображаются только те записи в ProdB, которые имеют соответствующую запись ProductID в таблице ProdB.

Пустое пространство в столбце ProdB.ProductName является специальным значением (NULL), поскольку в таблице ProdB нет совпадающего значения. Это окажется важным позже.

 SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; 

Попробуйте то же самое с третьим типом соединения (ВПРАВО).

right_outer_join

Результаты показывают все из таблицы ProdB, в то время как она показывает пустые (известные как NULL) значения, где таблица ProdA не имеет совпадающего значения. Пока что это приближает нас к желаемым результатам в вопросе нашего читателя.

 SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; 

Использование функций в запросе

Результаты функции также могут быть возвращены как часть запроса. Мы хотим, чтобы в нашем наборе результатов появился новый столбец с именем «Результаты». Его значением будет содержимое столбца ProductName таблицы ProdA, если ProdA имеет значение (не NULL), в противном случае его следует взять из таблицы ProdB.

Функция Immediate IF (IIF) может использоваться для получения этого результата. Функция принимает три параметра. Первое — это условие, которое должно принимать значение True или False. Второй параметр — это значение, которое будет возвращено, если условие истинно, а третий параметр — это значение, которое будет возвращено, если условие ложно.

Полная функциональная конструкция для нашей ситуации выглядит следующим образом:

 IIF(ProdA.ProductID Is Null, ProdB.ProductName,ProdA.ProductName) 

Обратите внимание, что параметр условия не проверяет равенство. Значение Null в базе данных не имеет значения, которое можно сравнить с любым другим значением, включая другое значение Null. Другими словами, Null не равен Null. Когда-либо. Чтобы обойти это, мы вместо этого проверяем значение с помощью ключевого слова «Is».

Мы могли бы также использовать ‘Is Not Null’ и изменить порядок параметров True и False, чтобы получить тот же результат.

Когда вы помещаете это в Query Designer, вы должны ввести всю функцию в поле Field:. Чтобы получить его для создания столбца «Результаты», вам нужно использовать псевдоним. Для этого перед функцией добавьте «Результаты:», как показано на следующем снимке экрана.

правые внешние псевдонимы-результаты-дизайн

Эквивалентный код SQL для этого будет:

 SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null,ProdB.ProductName,ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID; 

Теперь, когда мы запустим этот запрос, он выдаст эти результаты.

right_outer_with_aliased_results

Здесь мы видим для каждой записи, где у таблицы ProdA есть значение, это значение отражается в столбце Результаты. Если в таблице ProdA нет записи, запись из ProdB отображается в результатах. Это именно то, о чем просил наш читатель.

Дополнительные ресурсы для изучения Microsoft Access см. В статье Джоэла Ли « Как изучать Microsoft Access» : 5 бесплатных онлайн-ресурсов

Похожие посты
WindowsСпросите экспертов

Как отключить автоматическое обновление приложений в Windows 10

БезопасностьСпросите экспертов

Как просто и эффективно справиться с троянами удаленного доступа

Спросите экспертов

Как мне разблокировать Sprint IPhone 6?

Спросите экспертов

Как исправить ошибки времени выполнения Windows Visual C ++