Предисловие

SQL – это структурированный язык запросов (Structured Query Languge). Он был разработан в 70-е годы в коорпорации IBM в качестве стандартизированного метода для выборки данных из Баз Данных различных форматов. Целью было получение языка, который бы не базировался ни на одном из существующих языков программирования и в тоже время мог бы быть использован в любом языке программирования для выборки и обновления информации в Базах Данных.

Синтаксис языка SQL установлен комитетом Американского нацинального института стандартов (ANSI). Наиболее популярным стандартом SQL является SQL-89, опубликованный в 1989 году, уточненная версия стандартов была разработана тремя годами позже – SQL-92. Каждая версия стандартов вкючает три уровня соответствия. Чтобы называться SQL-совместимым продуктом, система управления Базой Данных должна удовлетворять первому соответсвия, второй и третий уровни являются необязательными, однако их достижение повышает возможности взаимодействия между различными системами Баз Данных. Процессор Баз Данных Microsoft Jet, обрабатывающий все запросы SQL языка Visual Basic соответствует стандарту SQL-89 и имеет незначительные различия на втором и третьем уровне.

Команды SQL делятся на две категории:

Части оператора SQL

SQL – оператор состоит из трех частей:

Управляющие операторы бывают следующих видов:

Простой запрос

Наиболее часто используется оператор SELECT_FROM (выбрать…из..) . Он возвращает записи и размещает информацию в динамическом наборе или таблице для дальнейшей ее обработки. Оператор может быть довольно сложным или совершенно простым. Простейшая форма оператора выглядит следующим образом:

strSQL = "SELECT * FROM Publishers"

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

Сложный запрос

Для выбора конкретных полей из таблицы (те, которые нужны вам для обработки) SQL-запрос записывается так:

strSQL = "SELECT Publishers.[Company Name], Publishers.Address, Publishers.State FROM Publishers"

Использовав данный запрос вы получите из таблицы Publishers поля [Company Name] и Address.

Примечание. Все поля которые имеют пробел в имени берутся в квадратные скобки.

Директива ORDER BY

Оператор SELECT_FROM возвращает записи в результирующий набор, в таком порядке в каком они встречаются в таблице, что бывает не совсем удобно. Для представления результирующего набора в удобном для вас виде, т.е. отсортированного, вы можете использовать в операторе SELECT_FROM директиву ORDER BY (по порядку) с добавлением ключевых слов ASC или DESC, где:

В данном случае вы получите записи, которые по полю [Company Name] будут отсортированны в алфавитном порядке.

Присвоение таблицам псевдонимов

Вероятно вы заметили, что при создании SQL-запроса имя таблицы указувается для каждого поля. Это является 'правилом хорошего тона' и будет совсем не плохо, если вы будете придерживаться этого и в дальнейшем, т.к. при составлении более сложных запросов вы можете столкнуться и идентичными именами полей разных таблиц. Но бывают случаи, когда имена таблиц могут оказаться далекими от совершенства, тогда для переименования столбцов результирующего набора вы можете воспользоваться ключевым словом AS. Например вы можете записать SQL-запрос следующим образом:

strSQL = "SELECT Publishers.[Company Name] AS PubCN, Publishers.Address AS PubAd, Publishers.State AS PubSt FROM Publishers ORDER BY Publishers.[Company Name] ASC"

Директива WHERE

Ограничение результирующего набора

Используя оператор SELECT_FROM вы выбираете все записи из необходимых вам полей, а что если вам не нужны все записи? Для этого необходимо ограничить содержимое результирующего запроса и это вы можете сделать с помощью директивы WHERE (где).

strSQL = "SELECT Publishers.[Company Name], Publishers.Address, Publishers.State FROM Publishers WHERE Publishers.State = '" & "CA'"

В таблице Publishers (база данных Biblio.mdb) имеется 727 записей, после выполнения данного SQL-запроса, вы получите всего 18 !

Примечание. Конструкция SQL-запроса используемая в Access и в Visual Basic одна и таже, разница только в синтаксисе написания. В Access используются круглые скобки и кавычки, а в Visual Basic, только одинарные кавычки. Выше приведенный SQL-запрос в Access выглядит следующим образом:

SELECT Publishers.[Company Name], Publishers.Address, Publishers.State FROM Publishers WHERE (((Publishers.State)="CA"));

Далее в статье все SQL-запросы будут приводится так, как они используются в Visual Basic.

Использование логических операторов и операторов сравнения

В директиве WHERE вы можете использовать логические операторы – AND или OR. Так, для более 'жесткого' отбора вы можете создать SQL-запрос, где вам необходимо знать имена всех компаний, которые находятся в штате CA, по адресу - 10662 Los Vaqueros Circle:

strSQL = "SELECT Publishers.[Company Name], Publishers.Address, Publishers.State FROM Publishers WHERE Publishers.Address ='" & "10662 Los Vaqueros Circle'" & " AND Publishers.State='" & "CA'"

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

Кроме логических операторов, вы можете использовать операторы сравнения –'<>', '<', '>', '<=', '>='.

Использование предиката BETWEEN_AND

Для выборки данных распложенных в каком-то диапазоне используется предикат BETWEEN_AND (между…и….). К примеру вам необходим общий список литературы выпущенный между 1985 и 1988 годом (таблица Titles, Biblio.mdb), таблица содержит 8659 записей. Применив SQL-запрос:

strSQL = "SELECT Titles.Title, Titles.[Year Published], Titles.ISBN, Titles.PubID, Titles.Description, Titles.Notes, Titles.Subject, Titles.Comments FROM Titles WHERE Titles.[Year Published] Between " & "1985" & " And " & "1988"

вы получите всего 714 записей удовлетворяющих ваш запрос.

Использование предиката IN

Если вам необходимы записи, которые содержали бы несколько разных значений, то для этого в предложении WHERE используют предикат IN, а за ним в скобках следуют требуемые значения, разделенные запятыми. К примеру вам необходимо выбрать всех издателей из нескольких штатов – Калифорния, Нью-Йорк:

strSQL = "SELECT Publishers.[Company Name], Publishers.Address, Publishers.State FROM Publishers WHERE Publishers.State IN ('" & "NY'" & ", '" & "CA" & "')"

В результате этого вы получите результирующий набор в 60 записей (из 727), в котором будут отображены все издатели, но только из указанных в запросе штатов.

Использование предиката LIKE

С помощью предиката LIKE, можно сравнить выражение (значение поля)с указанным шаблоном. Предикат LIKE может выполнить сравнение типа – фамилия начинается с буквы С, в результирующий набор записей войдут только те, которые начинаются с этой буквы. Или к примеру, требуется получить набор записей, который содержит слова из восьми букв начинающиеся с буквы 'В' и оканчивающиеся на букву 'р' – LIKE 'В?????р' .

В предикате LIKE могут использоваться символы шаблонов и списки/диапазоны символов. При создании шаблона можно комбинировать символы шаблонов и списки символов. Списки символов должны удовлетворять следующим критериям:

• Список должен заключаться в квадратные скобки;
• Первый и последний символ списка разделяются дефисом - [a-z];
• Диапазон символов должен определятся только по возростанию;

В таблице приведены шаблоны символов и списков:

И возвращаясь к Базе Данных Biblio.mdb получим имена всех компаний которые начинаются на букву 'S' .

strSQL = "SELECT Publishers.[Company Name], Publishers.Address, Publishers.State FROM Publishers WHERE Publishers.[Company Name] LIKE '" & "S*'"

Из общего числа записей (727) мы получили искомые 60…..

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

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

Агрегирующие функции

Благодаря применению агрегирующих функций SQL, оператор SELECT может использоваться для выполнения расчетов в таблице. Посредством процессора Баз Данных Microsoft Jet для использования доступны следующие агрегирующие функции языка:

• AVG – возвращает арифметическое среднее для всех значений в столбце удовлетворяющих директиве WHERE;
• Count – возвращает число строк удовлетворяющих директиве WHERE;
• MAX – возвращает наибольшее значение в столбце, удовлетворяющих директиве WHERE;
• MIN - возвращает наименьшее значение в столбце, удовлетворяющих директиве WHERE;
• SUM – возвращает сумму всех значений в столбце, удовлетворяющих директиве WHERE;
• FIRST – возвращает значение поля для первой записи в наборе;LAST – возвращает значение поля для последней записи в наборе

Для рассмотрения SQL-запроса в действии воспользуемся стандартной Базой Данных Nwind.mdb .

strSQL = "SELECT COUNT (UnitPrice) AS RecordCount, AVG (UnitPrice) AS RecordAVG, SUM (UnitPrice) AS RecordSUM, MAX (UnitPrice) AS RecordMAX, MIN (UnitPrice) AS RecordMIN, FIRST (UnitPrice) AS RecordFIRST, LAST (UnitPrice) AS RecordLAST From Products"

В полученной результирующей записи мы получили :

• Общее количество записей участвующих в запросе;Среднее значение по полю UnitPrice;
• Сумму всех значений по полю UnitPrice;
• Самое большое число (максимальное) в поле UnitPrice;
• Самое маленькое число (минимальное) в поле UnitPrice;
• Значение в первой записи поля UnitPrice;
• Значение в последней записи поля UnitPrice;

Примечание. Язык SQL не допускает использование агрегирующих функций в дерективе WHERE.

Предикаты DISTINCT и DISTINCTROW

Бывают ситуации, когда при выборе данных из таблицы в результирующий набор попадает несколько одинаковых строк. Чтобы исключить дубликаты, можно использовать предикат DISTINCT. Рассмотрим самый простой пример (Biblio.mdb):

strSQL = "SELECT DISTINCT [Title Author].Au_ID FROM [Title Author]"

При открытии таблицы без предиката DISTINCT мы получаем 16056 записей, которые содержат и дубликаты. Использовав вышеприведенный SQL-запрос мы получим 'очищенные' данные в 6239 записей.

Существуют ситуации, когда нужно выбрать строки, которые отличаютсядруг от друга сразу во всех полях. Для этого используется предикат DISTINCTROW :

strSQL = "SELECT DISTINCTROW * FROM [Title Author] ORDER BY AU_ID"

К сожалению в обеих Базах Данных я не смог найти более интересный пример, для демонстрации работы предиката DISTINCTROW . Предикаты DISTINCT и DISTINCTROW позволяют вам ограничивать содержимое результирующего набора на основании уникальности одного или нескольких столбцов таблицы данных.

Предикаты TOP n и TOP n PERCENT

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

strSQL = "SELECT TOP 7 * From Products ORDER BY UnitPrice DESC"

Предикат TOP n PERCENT возвращает n процентов первых записей.

strSQL = "SELECT TOP 7 PERCENT * From Products ORDER BY UnitPrice"

Примечание. Применяя предикат TOP n вы обязаны использовать предложение ORDER BY для того, чтобы иметь отсортированный результирующий набор. Если в таблице существуют однаковые записи, то они попадают в результирующий набор увеличивая тем самым количество записей.

Директива GROUP BY _HAVING

Предложение GROUP BY_HAVING одно из мощных директив языка SQL. Оно позволяет с помощью агрегирующих функций создавать результирующие наборы содержащие список промежуточных итоговых сумм для числовых полей физической таблицы данных. Директива GROUP BY требует, чтобы все числовые столбцы в списке полей выборки оператора SELECT были аргументами какой-нибудь агрегирующей функции SQL. Кроме того, в этом случае в списке полей выборк оператора SELECT нельзя использовать '*'. Логически запрос выполняет следующие шаги:

• Просуммировать (к примеру) данные по столбцу;
• Записать результаты во временную таблицу;
• Отобразить только те результаты которые подходят по условию;

Используя ключевое слово HAVING в директиве GROUP BY избавит вас реализовывать вышеописанные шаги в виде отдельных SQL-запросов. Ключевое слово HAVING действует точно так же, как и деректива WHERE , с той лишь разницей, что действие HAVING распространяется на результирующие столбцы созданные директивой GROUP BY, а не на столбцы физической таблицы.

StrSQL = "SELECT Title, SUM(Unit) AS UnitSold FROM BookSales GROUP BY Titles_

HAVING SUM(Unit)>" & "20"

Примечание. Структура HAVING подчиняется тем же правилам, что и структура директивы WHERE, т.е. вы можете использовать логические операторы.

Директива JOIN

Директива JOIN является мощным дополнительным средством языка SQL. В отличае от директивы WHERE, которая позволяет связывать две таблицы, но результирующий набор нельзя обновлять, директива JOIN позволяет создавать обновляемый результирующий набор, содержащий столбцы из разных таблиц. В Microsoft Jet SQL существует три типа директив JOIN:

• INNER JOIN – внутреннее соединение;
• LEFT JOIN – соединение слева;
• RIGHT JOIN – соединение справа;

Директива INNER JOIN

Директиву INNER JOIN можно использовать для создания результирующего набора, содержащего записи из двух таблиц, если они совпадают по некоторому объединяющему столбцу.

strSQL = "SELECT Titles.Title, Titles.[Year Published],Publishers.Name, Publishers.[Company Name] FROM Titles INNER JOIN Publishers ON Publishers.PubID=Titles.PubID"

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

Директива LEFT JOIN

Директива LEFT JOIN возвращает все записи первой таблицы списка и те записи правой таблицы, которые совпадают с с записями левой таблицы по объединяющему столбцу.

strSQL = "SELECT Publishers.Name, Titles.Title FROM Publishers LEFT JOIN Titles ON Publishers.PubID = Titles.PubID"

В полученном результирующем наборе (в данном случае) некоторые поля Title пусты. Директиву LEFT JOIN удобно применять тогда, когда требуется получить все записи главной таблицы и соответствующие им записи зависимой таблицы.

Директива RIGHT JOIN

Директива RIGHT JOIN работает так же, как и директива LEFT JOIN, с той лишь разницей, что результирующий набор формируется по правой таблице.

Директива UNION

Директива UNION создает объединение двух таблиц или запросов SQL, которые содержат сходные, но не связанные между собой данные. Объединенные запросы удобны в тех случаях, когда требуется сопоставить информацию из двух запросов в едином результирующем наборе.

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

Как строится запрос. (используется База Данных Nwind.mdb)

Обычно строится SQL-запрос для первой таблицы, затем строится запрос для второй таблицы и только потом оба запроса объединяются директивой UNOIN .

strSQL = "SELECT Customers.CompanyName, Customers.ContactName, Customers.Address, Customers.Region FROM Customers WHERE Customers.Country ='" & "USA'"

strSQL1 = "SELECT Suppliers.CompanyName, Suppliers.ContactName, Suppliers.Address, Suppliers.Region FROM Suppliers WHERE Suppliers.Country ='" & "USA'"

Data1.RecordSource = strSQL & " UNION " & strSQL1

Примечание. Запросы объединяемые с помощью директивы UNOIN должны иметь одинаковое количество столбцов. Используя директиву UNOIN, будьте осторожны, т.к. SQL-запрос не обращает внимания на тип данных при размещении результатов в столбцах. При формировании результирующего запроса используются имена столбцов первого запроса.

Директиву UNION можно использовать и для объединения запросов одной и тойже таблицы данных, например возвратить результат лидеров и аутсайдеров ....

Директива TRANSFORM_PIVOT (Перекрестные таблицы)

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

Как это работает. К примеру у вас имется таблица данных содержащая наименование товара и информацию по квартальным продажам. Вам необходимо создать список товаров с данными о продажах за каждый квартал, причем наименование товара должно располагаться слева, а справа - данные о всех его квартальных продажах. Это можно сделать с помощью сложного набора SQL-запросов, но можно сделать 'одним махом' употребив директиву TRANSFORM_PIVOT.

Форма записи следующая:

strSQL = "TRANSFORM SUM(Products.UnitPrice) SELECT Products.ProductName FROM Products GROUP BY Products.ProductName PIVOT Products.UnitsOnOrder"

Первым идет оператор TRANSFORM и сразу же должна следовать агрегирующая функция (даже если вы в действительности никакие итоговые вычисления выполнять не будете). Далее следует оператор SELECT_FROM и наконец ключевое слово PIVOT, за которым следует столбец, используемый в качестве набора заголовков.

Выполняемые запросы не возвращают наборов записей, они функционируют подобно подпрограммам. Поэтому, после выполнения запроса, управление перейдет к следующему оператору.

Использование оператора DELETE

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

Dim WS1 As Workspace

Dim DB1 As Database

Set WS1 = DBEngine.Workspaces(0)

Set DB1 = WS1.OpenDatabase(App.Path & "\Sinoptic.mdb", False, False)

strSQL1 = "DELETE * FROM StationInfo WHERE StationName LIKE '" & "А*'"

DB1.Execute strSQL1

DB1.Close

WS1.Close

Set DB1 = Nothing

Set WS1 = Nothing

Мы из таблицы StationInfo удалили все записи, которуе начинались с буквы А. Как вы заметили, сдесь используется метод Execute.

Примечание. После выполнения оператора DELETE записи полностью удаляются и больше не востанавливаются.

Использование оператора INSERT

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

Dim WS1 As Workspace

Dim DB1 As Database

Set WS1 = DBEngine.Workspaces(0)

Set DB1 = WS1.OpenDatabase(App.Path & "\Sinoptic.mdb", False, False)

strSQL = "SELECT * FROM Station WHERE StationName LIKE '" & "А*'"

strSQL1 = "INSERT INTO StationInfo "

strSQL1 = strSQL1 & strSQL

DB1.Execute strSQL1

DB1.Close

WS1.Close

Set DB1 = Nothing

Set WS1 = Nothing

Оператор INSERT также может применяться для обновления таблиц.

Использование оператора UPDATE

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