SQL в Visual Basic

В Visual Basic язык SQL применяется в основном для выполнения запросов, хотя очень удобно также удалять, добавлять и обновлять записи баз данных. Используя SQL-запросы, можно выбирать из таблиц базы данных только необходимые записи. При этом мы можем получить доступ не просто к одной таблице, а к сложной выборке из связанных между собой таблиц или наборов данных.

Для начала работы с SQL нужно выбрать в левой части главного окна Access в меню «Объекты» опцию «Запросы» и дважды щёлкнуть командуСоздание запроса в режиме конструктора.

Рис. 11 Диалоговое окно «Создание запроса в режиме конструктора»

Появившееся окно «Добавление таблицы» нужно закрыть. Выберем команду «Режим SQL» в меню Вид.

Синтаксис самой простой SELECT-инструкции (SQL — запроса) будет иметь вид: SELECT * FROM table (* - выбор значений всех полей).

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

SELECT fieldl [, field2 [,…]] FROM table.

Hp: SELECT КодДетали, НаимДетали FROM Детали;

Рис. 12 Запрос 2 (результат предыдущего запроса)

Следующая инструкция позволяет исправить ситуацию с «плохими» наименованиями в запросе:

SELECT КодДетали AS [Код детали], Наим Детали AS [Наименование детали] FROM Детали;

Рис. 13 Запрос З

Ключевое слово WHERE в синтаксисе инструкции SELECT позволяет указывать определенные типы записей, которые должны попадать в набор. При этом инструкция SELECT имеет следующий синтаксис:

SELECT {* fieldl [AS alias 1] [, field2 [AS alias2] [,…]]} FROM table [WHERE criteria].

Например можно запросить те детали, цены которых не более 500 денежных единиц:

SELECT Код Детали AS [Код детали], Наим Детали AS [Наименование детали], ЦенаДетали AS [Цена детали] FROM Детали WHERE ЦенаДетали<500;

Рис. 14 Запрос 4

В области слова WHERE можно располагать довольно сложное условное выражение с использованием знаков логических операций и функций.

Hp: SELECT Код Детали AS [Код детали], Наим Детали AS [Наименование детали], ЦенаДетали AS [Цена детали] FROM Детали.

WHERE НаимДетали Like «SPC*» ;

Позволяет получить только те записи, у которых в наименовании первые пять символов совпадают со строкой «SPC» .

Рис. 14 Запрос 5

Оператор LIKE можно использовать для контекстного поиска, например, если ввести текстовую строку, содержащую часть наименования деталей, то эту строку легко использовать в виде шаблона. Следующая инструкция выполняет поиск записей, в которых наименование содержит в качестве подстроки строку «Воу» SELECT Код Детали AS [Код детали], НаимДетали AS [Наименование детали], ЦенаДетали AS [Цена детали] FROM Детали WHERE НаимДетали Like «*Boy*» ;

Рис. 15 Запрос 6

Результирующие данные запроса можно форматировать с использованием, например, функции Format.

SELECT НаимДетали AS [Наименование детали], Рогта1(ЦенаДетали*1.2," #####0.00р") AS [Цена детали] FROM Детали WHERE ЦенаДеталиООО;

Функция Format в качестве первого аргумента получает произведение ЦенаДетали*1.2. Таким образом, можно получить другую цену из некоторой базовой.

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

SELECT 8и" Сот/(НаимДетали, 1) AS [Наименование детали], ЦенаДетали AS [Цена детали] FROM Детали;

Рис. 17 Запрос 8

Следующая инструкция позволяет получить наименования (из таблицы Детали) и количество (из таблицы ИнфОДетали) деталей:

SELECT Детали. Наим Детали AS [Наименование детали], ИнфОДетали.Количество.

FROM Детали, ИнфОДетали.

WHERE Детали. КодДетали=ИнфОДетали.КодДетали;

Рис. 18 Запрос 9

Выражение WHERE Детали. КодДетали=ИнфОДетали.КодДетали используется для связи таблиц Детали и ИнфОДетали по ключевому полю КодДетали.

Результат выполнения предыдущей инструкции напоминает инвентаризационную ведомость без указания места, где находится деталь. Следующая инструкция позволяет получить наименование (из таблицы Детали) и количество (из таблицы ИнфОДетали) только для тех кодов деталей, записи которых в таблице ИнфОДетали в поле № Склада содержат строку «2»:

SELECT Детали. Наим Детали AS [Наименование детали], ИнфОДетали. Количество AS Количество FROM Детали, ИнфОДетали.

WHERE ИнфОДетали.№Склада-2' And Детали. Код Детали^ ИнфОДетали. КодДетали;

При помощи локальных псевдонимов можно сократить предыдущую инструкцию:

SELECT а. НаимДетали AS [Наименование детали], Ь. Количество AS Количество.

FROM Детали AS а, ИнфОДетали AS b.

WHERE Ь.№Склада-2' And а. КодДетали=Ь.КодДетали;

Рис. 19 Запрос 10

Наименования товаров в наших таблицах и выводимых наборах не отсортированы. Это бывает особенно заметно, когда данных очень много, а нужно найти только некоторые из них. Для сортировки данных в инструкции SELECT имеются слова ORDER BY.

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

SELECT а. Наим Детали AS [Наименование детали], Ь. Количество AS Количество FROM Детали AS а, ИнфОДетали AS b.

WHERE Ь.№Склада='2' And а. КодДетали=Ь.КодДетали ORDER BY а. НаимДетали;

Рис. 20 Запрос 11

В инструкциях языка ANSI SQL предусмотрены так называемые агрегирующие функции, которые определяют количество записей, вычисляют суммы всех значений полей в наборе, находят минимальные или максимальные, а также средние значения. К агрегирующим функциям относятся функции COUNT, SUM, MAX, MIN и AVG.

Функция COUNT используется для определения количества записей в запросе. Например, можно вычислить количество записей в таблице ИнфО Детали:

SELECT СОШЧГГ (НаимДетали) AS [Всего наимнований] FROM ИнфОДетали;

Рис. 21 Запрос 12

Следующая инструкция вычисляет количество записей, в которых значение поля ЦенаДетали меньше 200:

SELECT СОШЧТ (НаимДетали) AS [Всего наимнований].

FROM Детали.

WHERE ЦенаДетали<200;

Рис. 22 Запрос 13

Функция SUM позволяет для группы строк вычислять итоговую сумму значений некоторого поля, например:

SELECT 8ЦМ (Количество) AS [Общее количество товаров] FROM ИнфОДетали;

Рис. 23 Запрос 14

При использовании функции SUM можно ограничить количество записей запроса при помощи некоторого условия, например:

SELECT 8ЦМ (Количество) AS [Общее количество товаров] FROM ИнфОДетали WHERE №Склада=Т;

Рис. 24 Запрос 15

Функция AVG в инструкции SELECT позволяет найти среднее значение для строк, входящих в запрос. Например, следующая инструкция определяет среднюю цену на товар, наименование которых начинаются со строки «(S*)» :

SELECT Рогта1(АУО (ЦенаДетали)," #####0.00р") AS [Среднее значение].

FROM Детали WHERE НаимДетали LIKE 'S*';

Рис. 25 Запрос 16

В инструкции SELECT для объединения значений в группы используется предложение GROUP BY.

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

SELECT №Склада, 81Ж (Количество) AS Количества.

FROM ИнфОДетали.

GROUP BY №Склада;

Рис. 26 Запрос 17

Следующая инструкция позволяет получить список складов и суммарное количество товаров на них. Причем в список включаются только те склады, на которых суммарное количество товаров меньше чем 13 :

SELECT №Склада, 8ЦМ (Количество) AS Количества.

FROM ИнфОДетали.

GROUP BY №Склада.

HAVING SUM (Количество)<13;

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

Рис. 27 Запрос 18

Подзапросы в инструкции SELECT.

Подзапрос — это запрос, который размещается внутри другого запроса, а точнее, является частью предложения WHERE (или HAVING) основного запроса и заключается в круглые скобки. Запрос, содержащий подзапрос, называется сложным запросом. При выполнении сложного запроса сначала выполняется подзапрос (он для этого и заключается в скобки), а затем — основной запрос.

Если необходимо получить список товаров склада, на котором их больше всего, то для начала нам нужно определить этот склад. Для начала создается запрос, который возвратит код склада с наибольшим количеством товаров: SELECT ТОР 1 №Склада FROM ИнфОДетали GROUP BY №Склада ORDER BY SUM (Количество) Предложение TOP 1 указывает на то, что в результирующий набор попадает только одна запись, а поскольку набор сортируется (по убыванию) по суммарным количествам деталей, то в результате мы получаем код склада с наибольшим количеством деталей. Остается использовать ранее рассмотренный запрос, который возвращает список деталей для указанного кода склада, но если ранее мы указывали этот код, то теперь определяем его в подзапросе:

SELECT а. НаимДетали AS [Наименование детали], Ь. Количество AS Количество FROM Детали AS а, ИнфОДетали AS b WHERE а. КодДетали=Ь.КодДетали AND №Склада= (SELECT TOP 1 FROM ИнфОДетали GROUP BY №Склада ORDER BY SUM (Количество) DESC);

Рис. 28 Запрос 19

Целью следующего запроса является получение ведомости деталей склада, где имеется больше всего наименований (кодов) деталей.

SELECT а. НаимДетали AS [Наименование детали], Ь. Количество AS Количество FROM Детали AS а, ИнфОДетали AS b WHERE а. КодДетали=Ь.КодДетали AND Ь.№Склада= (SELECT TOP 1 №Склада FROM ИнфОДетали GROUP BY №Склада ORDER BY COUNT (Количество) DESC); Здесь в подзапросе вместо функции SUM используется COUNT — счетчик количества записей.

Рис. 29 Запрос20