Язык sql в access

Язык sql в access

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

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

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

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

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

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

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

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

Описание и использование курсора в языке SQL выполняется следующим образом. В описательной части программы выполняют связывание переменной типа курсор (CURSOR) с оператором SQL (обычно с оператором SELECT). В выполняемой части программы производится открытие курсора (OPEN . ), сопровождаемое соответствующей обработкой, и, наконец, закрытие курсора (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 – подпрограммы, вызываемые из других процедур.

Читайте также:  Наушники takstar pro 80

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис инструкции SELECT в MS Access. Ядром языка SQL является инструкция SELECT. Она используется для отбора строк и столбцов из таблиц базы данных и содержит пять основных предложений. В общем случае ее синтаксис можно представить в следующем виде:

[WHERE ] [2] [GROUP BY ]

В MS Access реализованы важные средства расширения языка: инструкция TRANSFORM, позволяющая строить перекрестные запросы; предложение IN, с помощью которого задается связь с удаленной базой данных или указываются имена столбцов перекрестного запроса, и др. Синтаксис:

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

Первую часть имени (включая точку) можно опустить, если поле имеется только в одной из таблиц предложения FROM.

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

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

Пример 10.5. Следующее выражение задает ноле из таблицы [Товары], имя которого Наименование:

Предложение FROM. Задает таблицы или запросы, служащие источниками данных для создаваемого запроса. Синтаксис:

имя-запроса-на-выборку [[AS] псевдоним] I

[IN сспецификация источника>]

имя-запроса-на-выборку [[AS] псевдоним] I

имя-запроса-на-выборку [[AS] псевдоним] I

ON

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

Пример 10.6. Следующий запрос отображает все поля таблицы Товары:

Пример 10.7. Следующий запрос отображает товары, цена которых больше 100 долл, и гарантийный срок которых 36 мес:

SELECT Товары.Наименование, Товары.Цена

WHERE Товары.Цена > 100 AND Товары.[Гарантийный срок] = 36;

Большие возможности языка SQL во многом основаны на его способности объединять информацию из нескольких таблиц или запросов.

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

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

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

Аналогично, операция RIGHT JOIN возвращает все строки из второй логической таблицы, объединенные с теми строками из первой таблицы, для которых выполняется условие объединения.

– выражение, в котором поля первой таблицы сравниваются с полями второй таблицы. В бланке запроса QBE в условии объединения используется только оператор равно (=). Запрос на основе объединения таблиц по неравенству ( , <>, =) можно создать только в режиме SQL.

Читайте также:  Страна назначения товара это

Пример 10.8. Следующий запрос отображает сведения о складах фирмы Citii i nk:

SELECT Склады.Номерсклада, Склады.Адрес,

FROM Фирмы INNER JOIN Склады

ON Фирмы.Кодфирмы = Склады.Кодфирмы

WHERE Фирмы.Название = "Citilink";

Пример 10.9. Запрос отображает список товаров, которые еще не продавались, с указанием наименования товаров и номеров складов, на которых они хранятся:

SELECT Товары.Наименование, Хранение.Номерсклада

FROM (Товары INNER JOIN Хранение

ON Товары.КодТовара = Хранение.КодТовара)

LEFT JOIN Продажи ON Хранение.ID = Продажи.ID

WHERE ((Продажи.Количество) Is Null);

Пример 10.10. Следующая операция возвратит все строки из таблиц "Товары" и "Хранение", для которых совпадают значения в поле КодТовара:

Товары INNER JOIN Хранение ON Товары.КодТовара =

Операция LEFT JOIN возвратит все строки из таблицы, полученной в результате предыдущей операции INNER JOIN, объединенные с теми строками из таблицы "Продажи", для которых выполняется условие объединения. Тогда в с троке товара, который не продавался, в поле [Количество проданного товара] будет значение Null, что будет являться условием отбора для искомого списка.

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

GROUP BY имя-столбца, .

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

Пример 10.11. Следующий запрос отображает общее количество проданного товара каждого наименования:

SELECT Товары.Наименование, Sum(Продажи.Количество)

FROM (Товары INNER JOIN Хранение

ON Товары.КодТовара = Хранение.КодТовара)

INNER JOIN Продажи ON Хранение.Ю=Продажи.ID

GROUP BY Товары.Наименование;

Предложение HAVING. Это специальная форма фразы WHERE. Она относится пе к отдельным строкам, а к группам: предикат во фразе HAVING всегда ссылается (посредством специальных библиотечных функций, таких как, например, SET) на свойства групп, а не строк, и на основе этого предиката группы целиком либо выбираются, либо отбрасываются. Синтаксис:

HAVING "условие отбора>

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

Пример 10.12. Следующий запрос отображает общее количество проданного товара по дням с 10 по 20 декабря 2003 г.:

SELECT Продажи.Дата, Эит(Продажи.Количество)

GROUP BY Продажи.Дата

HAVING ((Продажи.Дата) Between #10.12.03# AND

Предложение ORDER BY. Задает порядок расположения строк, возвращаемых инструкцией SELECT. Синтаксис:

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

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

SELECT Товары.Наименование, Товары.Цена

ORDER BY Товары.Цена DESC;

Инструкция SELECT. Выполняет операции выбора и объединения для создания логической таблицы (набора записей) на базе других таблиц или запросов. Выше был рассмотрен синтаксис инструкции SELECT в общем виде. Рассмотрим его более подробно:

SELECT [ALL I DISTINCT I DISTINCTROW I TOP число

Предикаты ALL, DISTINCT, DISTINCTROW, TOP n или TOP n PERCENT уточняют окончательный набор записей запроса.

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

Предикат DISTINCT требует, чтобы запрос возвратил только строки, отличающиеся от всех остальных. Если инструкция SELECT содержит предикат DISTINCTROW, то в набор записей включаются только те строки, в которых конкатенация первичных ключей из всех таблиц, участвующих в формировании возвращаемых столбцов, является уникальной. В зависимости от того, какие столбцы представлены в наборе записей, иногда можно увидеть повторяющиеся строки, но даже в этом случае каждая строка запроса образована из уникальной (DISTINCT) комбинации строк (ROWS) базовых таблиц.

Чтобы результирующий набор содержал только первые п или первые п процентов записей, используйте предикат ТОР п или TOP п PERCENT. Параметр п должен быть целым числом, не превышающим 100, если используется ключевое слово PERCENT.

Пример 10.14. Следующий запрос отображает список 10 самых дорогих товаров:

SELECT ТОР 10 Товары.Наименование, Товары.Цена

ORDER BY Товары.Цена DESC;

Подчиненный запрос (вложенная выборка). Язык SQL позволяет использовать в условии отбора результаты другой выборки. Уровней вложенности может быть несколько.

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

SELECT Товары.Наименование, Товары.Цена

WHERE ((Товары.Наименование) Like "проц*") AND

Запросы SQL в Microsoft Access, учебные примеры и необходимые для них таблицы в формате .mdb. Примеры запросов SQL на выборку, изменение/обновление и удаление.
Скачав файл IB_Access_20111129.zip, Вы можете самостоятельно проверять и изменять приведенные примеры SQL-запросов для начинающих.

Ссылка на основную публикацию
Adblock detector