В предыдущих статьях были рассмотрены вопросы . Рассмотрена технология создания структуры таблиц базы данных «sql_training_st.mdb» на основе SQL запросов. Кроме того, с помощью SQL запросов было осуществлено заполнение таблиц СУБД ACCESS "sql_training_st.mdb".

Известно, что в реляционных базах данных язык SQL предназначен для манипулирования данными, определения структуры баз данных и ее составных частей, управления доступом пользователей к БД и для управления транзакциями или управления изменениями в БД.

Язык SQL состоит из четырех групп:

• язык манипулирования данными DML;
• язык определения данных DDL;
• язык управления данными DCL;
• язык управления транзакциями TCL.

К группе DML относятся четыре основных типа запросов SQL:

• INSERT - предназначен для добавления одной или нескольких записей в конец таблицы;
• UPDATE - предназначен для изменения уже существующих записей в столбцах таблицы или модификации данных в таблице;
• DELETE - предназначен для удаления записей из таблицы;
• SELECT - предназначен для выборки данных из таблиц.

Первые три типа SQL запросов (INSERT, UPDATE, DELETE), которые относятся к корректирующим запросам к базе данных, были рассмотрены на страничке

В данной статье рассмотрим запросы на выборку данных из таблиц БД Access.

Для извлечения информации, хранящейся в базе данных БД Access 2003 или 2007, можно применить запрос SELECT на выборку данных из таблиц.

Составим следующий SQL запрос (инструкцию SQL) на выборку, для этого выберем режим SQL, выполнив команду Вид/Режим SQL. Вводим с клавиатуры следующую инструкцию SQL:

SELECT *
FROM Студенты;

Эта инструкция состоит из двух предложений "SELECT *" и "FROM Студенты". Первое предложение содержит оператор SELECT и идентификатор * ("идентификатор *" означает вывод всех столбцов таблицы). Второе предложение содержит оператор FROM и идентификатор "Студенты".

FROM - определяет таблицу "Студенты", которая содержат поля, указанные в предложении SELECT. Следует отметить, что в запросе на выборку всегда присутствуют два оператора: SELECT и FROM. В зависимости от условий отбора в запросе на выборку могут присутствовать и другие операторы. На рисунке 1 представлен скриншот запроса на выборку данных.

Рис. 1. SQL запрос SELECT на выборку данных

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

Сохраняем запрос с именем "Студенты-запрос1". В результате выполнения команды "Сохранить" в «Области переходов» появится объект - «Запросы: Студенты-запрос1».

После сохранения запроса на выборку необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». Результаты выполнения команды «Выполнить» представлены на рис. 2.

Рис. 2. Выборка данных из всех столбцов таблицы Студенты

Сравнение Microsoft Access SQL и ANSI SQL Microsoft Access SQL в основном отвечает стандарту ANSI-89 (уровень 1) некоторые средства ANSI SQL не используются в Microsoft Access SQL Microsoft Access SQL использует зарезервированные слова и средства, не поддерживаемые ANSI SQL В Access 2000 (MS Jet 4.0) внесены расширения, приближающие язык к стандарту ANSI SQL-92 - режим доступен только при использовании MS OLE DB Provider для Jet

Синтаксис команды SELECT (Microsoft Access) SELECT [ предикат ] { * | таблица.* | [ таблица.] поле_1 [, [ таблица.] поле_2 [,...]]} FROM выражение [,...] Предложение FROM SELECT список Полей FROM выражение Выражение - выражение, определяющее одну или несколько таблиц, откуда извлекаются данные. Это выражение может быть именем отдельной таблицы, именем сохраненного запроса или результатом операции INNER JOIN, LEFT JOIN или RIGHT JOIN. INNER JOIN,LEFT JOIN RIGHT JOIN.

Аргументы инструкции SELECT Предикат (ALL, DISTINCT, DISTINCTROW или TOP) - одно из условий отбора. Предикаты используются для ограничения числа возвращаемых записей. Если они отсутствуют, по умолчанию используется предикат ALL (отбираются все записи, соответствующие условиям, заданным в инструкции SQL).инструкции SQL DISTINCT - исключает записи, которые содержат повторяющиеся значения в отобранных полях. DISTINCTROW - опускает данные, основанные на целиком повторяющихся записях, а не отдельных повторяющихся полях. Предикат DISTINCTROW игнорируется, если запрос содержит только одну таблицу или все поля всех таблиц. TOP n - возвращает определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью предложения ORDER BY. SELECT ]] FROM таблица 5 самых многочисленных кафедр: SELECT TOP 5 сотрудник.[Код кафедры], Count(сотрудник.ФИО) AS число_сотрудников FROM сотрудник GROUP BY сотрудник.[Код кафедры] ORDER BY Count(сотрудник.ФИО) DESC;

Аргументы инструкции SELECT Таблица - имя таблицы, из которой должны быть отобраны записи. WITH OWNERACCESS OPTION - используется в многопользовательской среде с составе защищенной рабочей группы, для предоставления пользователю, работающему с запросом, разрешений, соответствующих разрешениям владельца запроса. поле_1, поле_2 - имена полей, из которых должны быть отобраны данные. Если включить несколько полей, они будут извлекаться в указанном порядке. Псевдоним_1, псевдоним_2 - имена, которые станут заголовками столбцов вместо исходных названий столбцов в таблице. Выражение -имена одной или нескольких таблиц, которые содержат отбираемые данные Внешняя БазаДанных - имя базы данных, которая содержит таблицы, указанные с помощью аргумента выражение, если они не находятся в текущей базе данных. SELECT сотрудник.ФИО, [оклад]*0.5 AS Премия FROM сотрудник; SELECT Avg(сотрудник.оклад) AS Средний_оклад FROM сотрудник;

Совместная обработка нескольких таблиц SELECT сотрудник.ФИО, предмет.[наименование предмета краткое] FROM сотрудник INNER JOIN (предмет INNER JOIN [владение предметами] ON предмет.[Код предмета] = [владение предметами].[код дисциплины]) ON сотрудник.Код = [владение предметами].[код сотрудника];

Конструкция Between...And выражение 1 BETWEEN выражение 2 AND выражение 3 (в Microsoft Access SQL выражение 2 может быть больше, чем выражение 3, а в ANSI SQL – нет). SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Between 1000 And 2000)); Запрос: SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Between 2000 And 1000)); не выдает ошибки и дает тот же ответ SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) 15000)); SELECT сотрудник.ФИО, сотрудник.оклад FROM сотрудник WHERE (((сотрудник.оклад) Not Between 1000 And 2000));

Символы шаблона Разные символы шаблона используются с предикатом Like. Символ шаблона MS Access SQL ANSI SQL один символ? _ (подчеркивание) группа символов * % одиночный знак, входящий в список_знаков [ список_знаков] отсутствует одиночный знак, не входящий в список_знаков [! список_знаков] отсутствует Две последние возможности - только для Access В Access 2000 в режиме ANSI SQL-92 возможно использование подстановочных знаков ANSI. В одном запросе смешивать знаки нельзя SELECT сотрудник.ФИО FROM сотрудник WHERE (((сотрудник.ФИО) Like "Д*"));

Предложение GROUP BY SELECT список Полей FROM таблица WHERE условие Отбора где группируемые Поля - имена полей (до 10), которые используются для группировки записей. Порядок имен полей в аргументе группируемые Поля определяет уровень группировки для каждого из этих полей. Используйте предложение WHERE для исключения записей из группировки, а предложение HAVING для применения фильтра к записям после группировки. HAVING При использовании предложения GROUP BY все поля в списке полей инструкции SELECT должны быть либо включены в предложение GROUP BY, либо использоваться в качестве аргументов статистической функции SQL. Кафедры, на которых работает более 5 сотрудников: SELECT сотр.[Код кафедры], Count(сотр.ФИО) AS [Число_сотрудников] FROM сотр GROUP BY сотр.[Код кафедры] HAVING (((Count(сотр.ФИО))>5)); 5));">

Вложенный запрос. Предикат Exists. SELECT сотр.фирма, Count(сотр.сотр) AS число_аттест_сотр FROM сотр WHERE (((Exists (SELECT DISTINCT сотр.фирма, сотр.сотр FROM сотр INNER JOIN экз ON сотр.сотр = экз.сотр))False)) GROUP BY сотр.фирма; False)) GROUP BY сотр.фирма;"> False)) GROUP BY сотр.фирма;"> False)) GROUP BY сотр.фирма;" title="Вложенный запрос. Предикат Exists. SELECT сотр.фирма, Count(сотр.сотр) AS число_аттест_сотр FROM сотр WHERE (((Exists (SELECT DISTINCT сотр.фирма, сотр.сотр FROM сотр INNER JOIN экз ON сотр.сотр = экз.сотр))False)) GROUP BY сотр.фирма;"> title="Вложенный запрос. Предикат Exists. SELECT сотр.фирма, Count(сотр.сотр) AS число_аттест_сотр FROM сотр WHERE (((Exists (SELECT DISTINCT сотр.фирма, сотр.сотр FROM сотр INNER JOIN экз ON сотр.сотр = экз.сотр))False)) GROUP BY сотр.фирма;">

Создание подчиненного запроса с использованием построителя запросов QBE Если подчиненный запрос используется для определения условий для поля, введите инструкцию SELECT в ячейку строки Условие отбора в столбце этого поля. Инструкцию SELECT необходимо заключить в круглые скобки.

Удаление записей. Запрос на SQL (сгенерированный) DELETE DISTINCTROW студент.ФИО FROM студент WHERE (((студент.ФИО)="Бурлак Г. Н."));

Инструкция DELETE Запрос на удаление удаляет записи целиком, а не только содержимое указанных полей. Чтобы удалить данные конкретного поля, создайте запрос на обновление записей, который заменяет имеющиеся значения на значения Null Запрос на удаление без заданных условий поиска удалит все записи из таблицы. В отличии от команды DROP структура таблицы и все свойства сохраняются

Удаление записей. Запрос на SQL Запрос: DELETE * FROM студент WHERE студент.ФИО="Бурлак Г. Н."; дает аналогичный результат.

35

41

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

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

Это статья из цикла статей о языке SQL для Access. В ней описаны основы использования SQL для выборки данных и приведены примеры синтаксиса SQL.

В этой статье

Что такое SQL?

SQL - это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования, SQL удобочитаем и понятен даже новичкам. Как и многие языки программирования, SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO и ANSI .

На языке SQL описываются наборы данных, помогающие получать ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис - это набор правил, позволяющих правильно сочетать элементы языка. Синтаксис SQL основан на синтаксисе английского языка и имеет много общих элементов с синтаксисом языка Visual Basic для приложений (VBA).

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

SELECT Last_Name
FROM Contacts
WHERE First_Name = "Mary";

Примечание: Язык SQL используется не только для выполнения операций над данными, но еще и для создания и изменения структуры объектов базы данных, например таблиц. Та часть SQL, которая используется для создания и изменения объектов базы данных, называется языком описания данных DDL. Язык DDL не рассматривается в этой статье. Дополнительные сведения см. в статье Создание и изменение таблиц или индексов с помощью запроса определения данных .

Инструкции SELECT

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

таблицы, в которых содержатся данные;

связи между данными из разных источников;

поля или вычисления, на основе которых отбираются данные;

условия отбора, которым должны соответствовать данные, включаемые в результат запроса;

необходимость и способ сортировки.

Предложения SQL

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

Предложение SQL	Описание	Обязательное
	Определяет поля, которые содержат нужные данные.
	Определяет таблицы, которые содержат поля, указанные в предложении SELECT.
	Определяет условия отбора полей, которым должны соответствовать все записи, включаемые в результаты.
	Определяет порядок сортировки результатов.
	В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение.	Только при наличии таких полей
	В инструкции SQL, которая содержит статистические функции, определяет условия, применяемые к полям, для которых в предложении SELECT вычисляется сводное значение.

Термины SQL

Каждое предложение SQL состоит из терминов, которые можно сравнить с частями речи. В приведенной ниже таблице указаны типы терминов SQL.

Термин SQL	Сопоставимая часть речи	Определение	Пример
идентификатор	существительное	Имя, используемое для идентификации объекта базы данных, например имя поля.	Клиенты.[НомерТелефона]
оператор	глагол или наречие	Ключевое слово, которое представляет действие или изменяет его.
константа	существительное	Значение, которое не изменяется, например число или NULL.
выражение	прилагательное	Сочетание идентификаторов, операторов, констант и функций, предназначенное для вычисления одного значения.	>= Товары.[Цена]

Основные предложения SQL: SELECT, FROM и WHERE

Общий формат инструкций SQL:

SELECT field_1
FROM table_1
WHERE criterion_1
;

Примечания:

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

Каждая инструкция SELECT заканчивается точкой с запятой (;). Точка с запятой может стоять как в конце последнего предложения, так и на отдельной строке в конце инструкции SQL.

Пример в Access

В приведенном ниже примере показано, как в Access может выглядеть инструкция SQL для простого запроса на выборку.

1. Предложение SELECT

2. Предложение FROM

3. Предложение WHERE

Разберем пример по предложениям, чтобы понять, как работает синтаксис SQL.

Предложение SELECT

SELECT , Company

Это предложение SELECT. Оно содержит оператор (SELECT), за которым следуют два идентификатора ("[Адрес электронной почты]" и "Компания").

Если идентификатор содержит пробелы или специальные знаки (например, "Адрес электронной почты"), он должен быть заключен в прямоугольные скобки.

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

В инструкции SELECT предложение SELECT всегда стоит перед предложением FROM.

Предложение FROM

FROM Contacts

Это предложение FROM. Оно содержит оператор (FROM), за которым следует идентификатор (Контакты).

В предложении FROM не указываются поля для выборки.

Предложение WHERE

WHERE City="Seattle"

Это предложение WHERE. Оно содержит оператор (WHERE), за которым следует выражение (Город="Ростов").

С помощью предложений SELECT, FROM и WHERE можно выполнять множество действий. Дополнительные сведения об использовании этих предложений см. в следующих статьях:

Сортировка результатов: ORDER BY

Как и в Microsoft Excel, в Access можно сортировать результаты запроса в таблице. Используя предложение ORDER BY, вы также можете указать способ сортировки результатов при выполнении запроса. Если используется предложение ORDER BY, оно должно находиться в конце инструкции SQL.

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

Предположим, например, что результаты сначала нужно отсортировать по полю "Компания" в порядке убывания, а затем, если присутствуют записи с одинаковым значением поля "Компания", - отсортировать их по полю "Адрес электронной почты" в порядке возрастания. Предложение ORDER BY будет выглядеть следующим образом:

ORDER BY Company DESC,

Примечание: По умолчанию Access сортирует значения по возрастанию (от А до Я, от наименьшего к наибольшему). Чтобы вместо этого выполнить сортировку значений по убыванию, необходимо указать ключевое слово DESC.

Дополнительные сведения о предложении ORDER BY см. в статье Предложение ORDER BY .

Работа со сводными данными: предложения GROUP BY и HAVING

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

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

Задание полей, которые не используются в агрегатной функции: предложение GROUP BY

При использовании агрегатных функций обычно необходимо создать предложение GROUP BY. В предложении GROUP BY указываются все поля, к которым не применяется агрегатная функция. Если агрегатные функции применяются ко всем полям в запросе, предложение GROUP BY создавать не нужно.

Предложение GROUP BY должно следовать сразу же за предложением WHERE или FROM, если предложение WHERE отсутствует. В предложении GROUP BY поля указываются в том же порядке, что и в предложении SELECT.

Продолжим предыдущий пример. Пусть в предложении SELECT агрегатная функция применяется только к полю [Адрес электронной почты], тогда предложение GROUP BY будет выглядеть следующим образом:

GROUP BY Company

Дополнительные сведения о предложении GROUP BY см. в статье Предложение GROUP BY .

Ограничение агрегированных значений с помощью условий группировки: предложение HAVING

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

Предположим, например, что к первому полю в предложении SELECT применяется функция AVG (которая вычисляет среднее значение):

SELECT COUNT(), Company

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

HAVING COUNT()>1

Примечание: Запрос может включать и предложение WHERE, и предложение HAVING, при этом условия отбора для полей, которые не используются в статистических функциях, указываются в предложении WHERE, а условия для полей, которые используются в статистических функциях, - в предложении HAVING.

Дополнительные сведения о предложении HAVING см. в статье Предложение HAVING .

Объединение результатов запроса: оператор UNION

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

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

Примечание: В запросах на объединение числовой и текстовый типы данных являются совместимыми.

Используя оператор UNION, можно указать, должны ли в результаты запроса включаться повторяющиеся строки, если таковые имеются. Для этого следует использовать ключевое слово ALL.

Запрос на объединение двух инструкций SELECT имеет следующий базовый синтаксис:

SELECT field_1
FROM table_1
UNION
SELECT field_a
FROM table_a
;

Предположим, например, что имеется две таблицы, которые называются "Товары" и "Услуги". Обе таблицы содержат поля с названием товара или услуги, ценой и сведениями о гарантии, а также поле, в котором указывается эксклюзивность предлагаемого товара или услуги. Несмотря на то, что в таблицах "Продукты" и "Услуги" предусмотрены разные типы гарантий, основная информация одна и та же (предоставляется ли на отдельные продукты или услуги гарантия качества). Для объединения четырех полей из двух таблиц можно использовать следующий запрос на объединение:

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services
;

Дополнительные сведения об объединении инструкций SELECT с помощью оператора UNION см. в статье

Язык SQL не обладает функциями полноценного языка разработки, а ориентирован на доступ к данным, поэтому его включают в состав средств разработки программ. В этом случае его называют встроенным SQL. Стандарт языка SQL поддерживают современные реализации следующих языков программирования: PL/1, Ada, С, COBOL, Fortran, MUMPS и Pascal.

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

Различают два основных метода использования встроенного SQL: статический и динамический.

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

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

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

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

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

Описание и использование курсора в языке SQL выполняется следующим образом. В описательной части программы выполняют связывание переменной типа курсор (CURSOR) с оператором SQL (обычно с оператором SELECT). В выполняемой части программы производится открытие курсора (OPEN <имя курсора», перемещение курсора по записям (FETCI-1 <имя курсора>...), сопровождаемое соответствующей обработкой, и, наконец, закрытие курсора (CLOSE <имя курсора>).

В реляционных СУБД для выполнения операций над отношениями используются две группы языков, имеющие в качестве своей математической основы теоретические языки запросов, предложенные Э. Коддом:

Реляционная алгебра;

Реляционное исчисление.

В реляционной алгебре операнды и результаты всех действий являются отношениями. Я зыки реляционной алгебры являются процедурными, так как отношение, являющееся результатом запроса к реляционной БД, вычисляется при выполнении последовательности реляционных операторов, применяемых к отношениям. Операторы состоят из операндов, в роли которых выступают отношения, и реляционных операций.

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

Языки исчислений являются непроцедурными (описательными или декларативными) и позволяют выражать запросы с помощью предиката первого порядка (высказывания в виде функции), которому должны удовлетворять кортежи или домены отношений. Запрос к БД, выполненный с использованием подобного языка, содержит лишь информацию о желаемом результате. Для этих языков характерно наличие наборов правил для записи запросов. В частности, к языкам этой группы относится SQL.

Особенности применения запросов SQL

Запросом SQL называют запрос, создаваемый с помощью инструкции SQL. Примерами запросов SQL являются запросы на объединение, запросы к серверу, управляющие и подчиненные запросы.

Запрос на объединение – такой запрос, в котором объединяются поля (столбцы) одной или нескольких таблиц или запросов в одно поле или столбец в результирующем наборе записей. Например, шесть продавцов каждый месяц представляют руководству описи имеющихся товаров. Создав запрос на объединение, можно объединить эти описи в результирующем наборе записей, а затем разработать запрос на создание таблицы, основанный на запросе на объединение.

Запрос к серверу выполняет передачу через ODBC команд SQL – серверу, например, Microsoft SQL Server. Запросы к серверу позволяют непосредственно работать с таблицами на сервере вместо их присоединения. Результатом выполнения запроса к серверу может быть загрузка записей или изменение данных.

Управляющий запрос создает или изменяет объекты базы данных, такие как таблицы Access или SQL Server.

Подчиненный запрос состоит из инструкции SQL SELECT, находящейся внутри другого запроса на выборку или запроса на изменение. Эти инструкции вводятся в строку «Поле» бланка запроса для определения нового поля или в строку «Условие отбора» для определения условия отбора поля. Подчиненные запросы используются для выполнения следующих действий:

Проверка в подчиненном запросе существования некоторых результатов с помощью зарезервированных слов EXISTS или NO EXISTS;

Поиск в главном запросе любых значений, которые равны, больше или меньше значений, возвращаемых в подчиненном запросе (с помощью зарезервированных слов ANY, IN или ALL);

Создание подчиненных запросов внутри подчиненных запросов (вложенных подчиненных запросов).

Язык SQL в Access может применяться при разработке экранных форм, отчетов, а также при создании макрокоманд и программ на VBA.

Связь языков QBE и SQL

В Access между языками QBE и SQL имеется тесная связь. Запросные таблицы (бланки, формы) на языке QBE, заполняемые пользователем, перед непосредственным выполнением преобразуются в выражения SQL. То есть язык SQL является внутренним стандартом на выполнение запросов. Такой механизм имеет преимущество, поскольку позволяет внутри системы Access унифицировать подготовку запросов к выполнению на локальном и удаленном компьютерах. В последнем случае SQL – сообщение реально передается к компьютеру – серверу запроса.

В Access запрос может находиться в одном из трех режимов (состояний): Конструктора, SQL и таблицы. Режим конструктора применяют для разработки нового запроса с чистого листа (без использования Мастеров или других средств) или для изменения макета существующего запроса. Режим SQL применяют для ввода или просмотра инструкций SQL. Режим таблицы применяют для работы с результатами выполнения запроса.

SQL в формах и отчетах

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

SQL в макрокомандах

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

Макросы вызываются из окна БД или автоматически при наступлении определенных событий. Событием, по которому вызывается макрос, может быть, например, нажатие кнопки в области формы или открытие окна БД. Наряду с выполнение некоторых действий над объектами БД макросы могут вызывать другие макросы, программы на Visual Basic и внешние приложения.

Из множества макрокоманд с SQL непосредственно связаны две макрокоманды: ЗапускЗапроса SQL (Run SQL) и ОткрытьЗапрос (OpenQuery)

Макрокоманда ЗапускЗапроса SQL запускает запрос на изменение или управляющий запрос Access с помощью соответствующей инструкции SQL. Эта макрокоманда делает возможным выполнение действий в макросе без предварительного сохранения запросов. С помощью макрокоманды можно выполнять и сохраненные запросы.

Запросами на изменение являются инструкции SQL, реализующие следующие функции: добавление (INSERT INTO), удаление (DELETE), создание таблицы (SELECT…INTO) и обновление (UPDATE)

Управляющими запросами являются инструкции SQL, выполняющие следующие функции: создание таблицы (CREATE TABLE), изменение таблицы (ALTER TABLE), удаление таблицы (DROP TABLE), создание индекса (CREATE INDEX) и удаление индекса (DROP INDEX)

Единственным и обязательным аргументом макрокоманды ЗапускЗапроса SQL является инструкция SQL. Аргумент макрокоманды в виде текста SQL – инструкции вводится вручную в окне ввода макрокоманды или копируются из окна SQL, что часто удобнее.

Макрокоманда ОткрытьЗапрос позволяет открывать запрос на выборку или перекрестный запрос (в режимах таблицы, конструктора и предварительного просмотра), запускать запрос на изменение или ввод данных.

В макрокоманде задаются три аргумента: имя запроса, режим и режим данных. Первый аргумент задает имя открываемого запроса и является обязательным. Второй аргумент задает режим открытия запроса («Таблица», «Конструктор» и «Просмотр»). Третий аргумент описывает режим ввода данных в запрос («Добавление», «Изменение» и «Только чтение»)

SQL в программах на VBA

VBA, как и макросы, предназначены для автоматизации выполнения повторяющихся операций над объектами БД Access.

В Access существуют следующие способы запуска программ VBA:

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

Вызов функции в выражении;

Вызов процедуры Sub в другой процедуре или в окне отладки;

Выполнение макрокоманды ЗапускПрограммы (RunCode) в макросе.

Функции применяются в выражениях, определяющих вычисляемые поля в формах, отчетах или запросах. Выражения используются для указания условий в запросах и фильтрах, а также в макросах, в инструкциях и методах VBA, а также в инструкциях SQL. В процедуру Sub можно включать общедоступные VBA – подпрограммы, вызываемые из других процедур.

Рассмотрим выполнение запроса к базе данных с помощью инструкций SQL в программе на Visual Basic для приложений.

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

Существуют следующие способы выполнения запросов:

Вызов метода Execute (для выполнения запросов SQL на изменение);

Создание и выполнение специального объекта QueryDef;

Использование инструкции SQL в качестве аргумента метода OpenRecordset;

Выполнение метода OpenRecordset для существующего объекта QueryDef;

Вызов методов RunSQL и OpenQuery.

Метод Execute используется, если требуется выполнить такое изменение в БД, при котором не возвращаются записи. Например, операции вставки или удаления записей.

Объект QueryDef представляет собой сохраненное определение запроса в базе данных. Его можно рассматривать как откомпилированную инструкцию SQL.

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

Метод RunSQL выполняет макрокоманду ЗапускЗапроса SQL в программе VBA

Метод OpenQuery выполняет макрокоманду ОткрытьЗапрос (OpenQuery) в программе VBA. С его помощью можно открыть запрос в режиме таблицы, Конструктора или просмотра. При этом устанавливается один из следующих режимов работы с данными: добавление, изменение или только чтение.

Выбор варианта выполнения запроса определяется программистом с учетом особенностей решаемой задачи.

Язык структурированных запросов (SQL) является стандартным средством для работы с базами данных. Может использоваться как для интерактивной работы с БД, так и включаться в языки программирования. В CУБД Access SQL позволяет:

· создавать, модифицировать или удалять таблицы в базе данных Access;

· создавать или удалять ключи, индексы для таблиц в базе данных Access;

· вставлять, удалять или модифицировать записи таблиц;

· получать сводную информацию о данных в таблицах (число записей, суммы, средние, минимальные, максимальные значения и др.);

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

Рассмотрим основные операторы языка SQL для поиска и извлечения информации из базы данных. Для этого используется оператор SELECT

SELECT список имен полей 1

FROM список имен таблиц

SELECT, FROM, WHERE, ORDER BY, GROUP BY - ключевые слова;

список имен полей 1 - набор имен полей, которые выбираются из одной или нескольких таблиц, указанных в списке имен таблиц, вычисляемых полей, агрегированных функций (например, count, sum, min, max, avg и т.д.). Для выбора всех полей из списка таблиц вместо перечисления их имен можно использовать символ «*». Если имя поля таблицы содержит пробел, т.е. состоит из нескольких слов, то это имя должно заключаться в квадратные скобки;

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

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

Примеры:

SELECT * FROM Книга - выбор всех полей всех книг из таблицы «Книга» базы данных по библиографии.

SELECT Название, [Год издания] FROM Книга - выбор полей заголовков (Название ) и года издания (Год издания ) всех книг из таблицы «Книга» . Имя поля года издания указано в квадратных скобках (имеет пробел).

Таблица «Издательство» Таблица «Книга» Таблица «Автор»

Рис. 2.1. Структура и схема БД по библиографии

Для поиска информации, удовлетворяющей некоторому условию, в операторе SELECT используется раздел WHERE, который имеет следующий синтаксис:

SELECT список имен полей FROM список имен таблиц WHERE условие,

где условие определяет критерий поиска информации.

В условии используются имена полей, операции сравнения (<, <=, =, >, >=, <>) и специальные операции сравнения IN, LIKE, BETWEЕN. Эти операции могут объединяться с помощью логических операций (And, Or) и задавать сложные условия поиска информации.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] > 1991 определяет выбор названий книг, год выпуска которых позже 1991.

WHERE Фамилия = ”Иванов” определяет выбор полей имен и фамилий авторов из таблицы «Автор », фамилии которых совпадают с фамилией Иванов. Здесь символ “ используется для задания текстовых констант.

Операция IN сравнивает содержимое поля со списком заданных значений, определяющих критерий поиска информации.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] IN (1995, 1996) определяет выбор названий и года издания книг, опубликованных в 1995 или в 1996 гг.

SELECT Фамилия, Имя, Город FROM Автор

WHERE Город IN (“Омск”, “Москва”, “Киев”) определяет выбор авторов, живущих в городах Омск, Москва или Киев.

Операция LIKE сравнивает содержимое поля со значением образца. Для записи образца используются строковые константы, символы шаблона и списки диапазона символов.

Символами шаблона являются: *, ?, #. Символ * соответствует цепочке символов, ? - соответствует одному символу, # - соответствует одной цифре.

Примеры:

Р* - возможные результаты поиска рука, Роза.

КИ? - возможные результаты поиска кий, кит.

12345# - возможные результаты поиска 123455, 123457.

Список диапазона заключается в квадратные скобки, первый и последний символы диапазона отделяются дефисом (-). Диапазон задается в возрастающем порядке.

Примеры:

SELECT Фамилия, Имя, Отчество FROM Автор

WHERE Фамилия LIKE "И*" определяет выбор авторов, фамилии которых начинаются с буквы И.

SELECT Название FROM Книга

WHERE Название LIKE “*база данных*” AND [Год издания] = 1996 определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных».

Операция BETWEEN проверяет принадлежность значения поля диапазону значений и является включающим значением (выбираются записи, содержащие поле со значением, равным границе диапазона). Границы значений объединяются операцией AND.

Примеры:

SELECT Название, [Год издания] FROM Книга

WHERE [Год издания] Between #01.01.1994# And #30.06.1994# определяет выбор книг, изданных в первой половине 1994 года. Здесь символ # используется для задания констант типа дата.

SELECT [Название издат], адрес, телефон FROM Издательство

WHERE [Название издат] Between "Нау" And "Фин" определяет выбор информации об издательских фирмах, начальные буквы названий которых, расположенные в алфавитном порядке, находятся в диапазоне между "Нау" и "Фин".

Для определения порядка , в котором представляются результаты поиска записей, используется раздел ORDER BY , синтаксис которого следующий:

SELECT список имен полей FROM список имен таблиц ORDER BY имя поля 1 [ASC | DESC][, имя поля 2[ASC | DESC]] ,

где имя поля1, имя поля2 – поля (из списка имен полей), по которым производится упорядочение. Опция ASC устанавливает порядок сортировкипо возрастанию (данный порядок установлен по умолчанию), DESC устанавливает порядок сортировки по убыванию. Упорядочение может вестись по нескольким полям (например, по фамилии, а затем по имени)

Пример:

SELECT Название FROM Книга

WHERE Название LIKE “*база данных*” AND [Год издания] = 1996

ORDER BY Название определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных», и упорядочивает названия в алфавитном порядке.

Для группировки записей используется раздел GROUP BY , синтаксис которого следующий:

SELECT список имен полей FROM список имен таблиц GROUP BY имя поля 1 [, имя поля 2],

где имя поля1, имя поля2 – поля (из списка имен полей), по которым производится группировка записей. Порядок следования полей в этом предложении определяет уровни их группировки. Группировку можно производить для выражений (например, вычисляемых полей), тогда в списке полей этому выражению должно быть присвоено какое-нибудь имя. Для агрегированных функций (count, sum, min, max, avg и т.д.) группировка обязательна.

Пример:

SELECT Фамилия, [Год издания], Sum (Цена) AS [Стоимость ]

GROUPBY Фамилия, [Год издания]

ORDER BY Фамилия, [Год издания] определяет суммарную цену (Стоимость ) книг года издания для каждого автора.

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

SELECT список имен полей FROM список имен таблиц WHERE список связей,

где список связей определяет, как таблицы в списке имен таблиц связаны между собой. Например, имя_таблицы1.имя_поля1 = имя_таблицы2.имя_поля2.

Примеры:

SELECT Книга.Название, Издательство. [Название издат]

FROM Книга, Издательство

WHERE Книга. [Код издат]= Издательство. [Код издательства] And

Издательство. [Название издат]=”Наука”

Этот запрос определяет выбор названий книг (Название ) из таблицы «Книга » и названий издательств (Название издат ) из таблицы издательств (Издательство ). Раздел WHERE осуществляет следующее:

· определяет связь между таблицами (выбирается название книги и номер издательства «Код издат » из таблицы «Книга» , по номеру издательства «Код издательства » в таблице издательств «Издательство» находится название издательства);

· задает условие для выбора книг, изданных только в издательстве «Наука».