Создание запроса к серверу
Вы можете использовать Access как переднее приложение, а затем связывать таблицы на сервере, например с SQL Server. По умолчанию большинство запросов работают локально в ячеке баз данных Access ACE. В некоторых случаях в зависимости от синтаксиса SQL Server SQL и других факторов Access может выполнить запрос удаленно на SQL Server. Дополнительные сведения см. в документации по теме «ЯД БАЗ ДАННЫХ JET версии 3.0: подключение ODBC» в красных документах, открытых в jet и ODBC.
Однако часто необходимо явно выполнить запрос к серверу базы данных. Это помогает повысить производительность: серверы баз данных имеют большую вычислительную мощность, чем настольный компьютер, и вы можете возвращать меньшее подмножество данных по сетевому подключению. Чтобы выполнить запрос к серверу базы данных, используйте запрос к серверу — это SQL Transact SQL(TSQL), подключающийся к SQL Server с помощью строки подключения ODBC. Например, вы можете передать заявление SELECT, чтобы вернуть данные в таблицу, форму, отчет или список, выполнить эквивалент запросов на действия (DML), хранимых процедур вызовов и выполнения административных задач (DDL).
Примечание Результаты запроса к проверке находятся только для чтения. Чтобы непосредственно редактировать данные в таблице или форме, создайте связанную таблицу в индексируемом представлении. Дополнительные сведения см. в теме «Создание индексных представлений».
Подключение к базе данных сервера.
Дополнительные сведения о связывание с SQL Server см. в дополнительных сведениях об импорте или связывание данных в базе данных SQL Server, а также связывание или импорт данных из базы данных Azure SQL Server.
На вкладке Создание нажмите кнопку Конструктор запросов.
При необходимости закройте диалоговое окно «Добавление таблиц» (добавление таблицы в Access 2013 ).
Access скроет сетку конструктора запросов и отобразит SQL представлении.
Если лист свойств запроса не отображается, нажмите F4, чтобы отобразить его.
На окне свойств щелкните поле свойства ODBC Connect Str и сделайте одно из следующего:
Получение строки подключения
В области навигации щелкните правой кнопкой мыши таблицу, связанную с нужным источником данных, и выберите «Диспетчер связанных таблиц».
В диалоговом окне «Диспетчер связанных таблиц» выберите поле для источника данных и выберите «Изменить».
В диалоговом окне «Изменение ссылки» скопируйте текст в поле строки подключения.
Выберите » 
«.
Следуйте запросам, чтобы создать файл DSN, содержащий сведения о строке подключения. Подробные инструкции см. в инструкциях по импорту или связываированию данных в базе данных SQL Server, а также связывать или импортировать данные из базы данных Azure SQL Server.
После создания подключения к файлу DSN строка подключения ODBC будет храниться внутри базы данных Access. Вот как можно получить копию этой строки:
Найдите имя связанной таблицы в области навигации, используя нужное подключение ODBC.
Нажмите CTRL+G. чтобы открыть окно VBA Immediate.
Введите следующий код:
Но замените имя связанной таблицы из шага а.
Скопируйте строку, возвращаемую в свойство ODBC Connect Str.
Введите запрос в SQL представлении.
Примечание Между access SQL и TSQL SQL Server есть важные различия, особенно с синтаксисом, именами функций и аргументами функции. Дополнительные сведения см. в статье Сравнение языков Access SQL и SQL Server TSQL.
Совет. Сначала постройте запрос TSQL в SQL Server Management Studio, а затем скопируйте его и в SQL в SQL View. Это гарантирует правильность синтаксиса запроса. Если у вас нет удобного доступа к базе данных SQL Server, вы можете установить на компьютере выпуск Microsoft SQL Server Express, который поддерживает до 10 ГБ, и это бесплатный и удобный способ проверить миграцию.
Вы также думайте о настройке дополнительных связанных свойств.
Возвращает записи. Некоторые SQL возвращают записи, например SELECT, а другие — нет, например UPDATE. Если запрос возвращает записи, установите для этого свойства для этого свойства конечное свойство «Да»; если запрос не возвращает записи, для этого свойства установите для этого свойства свойство «Нет».
Примечание Запросы, которые передают данные, могут возвращать несколько наборов результатов, но в таблице, форме, отчете или переменной набора записей используется только первый набор результатов. Чтобы получить несколько наборов результатов, используйте запрос на таблицу, как показано в следующем примере:
Журнал сообщений Укажите, следует ли возвращать сообщения из базы SQL в таблице сообщений Access. Имя таблицы имеет формат «имя пользователя — nn», где имя пользователя — это имя для регистрации текущего пользователя, а «nn» — число, начиная с 00.
Время и времяообнабдения ODBC Укажите, в течение каких секунд произойдет ошибка времени ожидания при запуске запроса. По умолчанию установлен интервал 60 секунд. Могут возникнуть задержки из-за сетевого трафика или интенсивного использования сервера базы данных.
Max Records Укажите максимальное количество возвращаемых записей. Возможно, у вас мало ресурсов в системе или вы хотите протестировать результаты с небольшим объемом данных.
Завершив ввод запроса, выберите «Конструктор > Выполнить». Запрос отправляется на сервер базы данных для обработки.
6. Запросы MS Access
6.1. Что такое запросы
Все запросы делятся на запросы-выборки и запросы-действия.
После выполнения запроса-выборки СУБД создает виртуальную таблицу, в которую заносит выбранную информацию и хранит ее до тех пор, пока сгенерированная таблица не будет закрыта. Когда этот запрос закрывается, полученная таблица уничтожается, освобождая занимаемую память.
В отличие от запроса-выборки, запрос-действие вносит изменения в существующие данные. При помощи запроса-действия можно автоматически создать новую таблицу, внести данные в уже имеющуюся таблицу, а также удалить или изменить какой-либо набор записей из существующей таблицы.
Любой вновь создаваемый запрос в MS Access автоматически считается запросом-выборкой. В том случае, когда пользователю или разработчику нужно получить запрос-действие, следует специально это указать. Это помогает избежать случайных действий с данными, которые нельзя отменить.
Фактически, запрос в MS Access является инструкцией на языке SQL (Structured Query Language), который является на сегодняшний день основным средством составления запросов в наиболее распространенных реляционных СУБД. Посредством механизма SQL становится возможным как обращаться к содержимому баз MS Access из других приложений (в том числе и приложений от других производителей), так и получать данные для MS Access из внешних приложений, поддерживающих SQL.
Запросы в MS Access доступны в трех режимах: табличном, в котором запрос выглядит так же, как обычная таблица, режиме конструктора, где запрос предстает в виде схемы связанных объектов, и в виде инструкций SQL.
6.2. Краткие сведения об SQL
Небольшой набор команд SQL и определенный синтаксис составления выражений позволяет совершать сколь угодно сложные операции. Самой распространенной из них является выборка связанных данных из одной или нескольких таблиц при помощи оператора SELECT, однако в случае необходимости SQL может вносить информацию, корректировать хранящиеся записи и создавать новые объекты базы данных. SQL применяется не только в MS Access, но и во всех современных СУБД.
SQL-запросы построены на лексическом диалекте, основанном на семантике обычного языка (английского). При создании языка предполагалось, что конструкцию на нем сможет написать любой неподготовленный пользователь, руководствуясь исключительно своими потребностями и исходя из существующей структуры данных. Среди базовых инструкций, на которых основываются запросы, стоит выделить следующие наиболее распространенные команды:
Вспомогательные конструкции языка, например, WHERE задает критерий уточняющего поиска среди отобранных записей.
В языке запросов, реализованном в Microsoft Access, поддерживаются четыре дополнительных расширения: TRANSFORM (позволяет строить перекрестные запросы), IN (создание связи с удаленной базой данных), DISTINCTROW (определение запроса-объединения с потенциальной возможностью объединения данных), WITH OWNER-ACCESS OPTION (позволяет создавать специализированные запросы, которые может выполнять пользователь, не имеющий права доступа к таблицам, которыми оперирует данный запрос). В качестве функции могут быть использованы итоговые функции SQL, например такие, как AVG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR или VARP. Кроме того, в качестве функции могут применяться встроенные функции MS Access и встроенного языка VBA.
6.3. Создание запросов
Для того чтобы создать запрос-выборку, нужно перейти на вкладку «Запросы» в окне базы данных и нажать кнопку «Создать», в результате откроется диалоговое окно, представленное на рисунке 6.3.1.
При создании запроса с помощью Конструктора разработчику необходимо выбрать таблицы или запросы, содержащие нужные данные и заполнить бланк запроса. Бланк запроса расположен в нижней части окна конструктора и содержит описание запроса в табличной форме (см. рисунок 6.3.2). Этот вариант следует выбирать для создания запроса «с нуля» в режиме конструктора.
Каждая колонка в бланке запроса соответствует одному полю. Строки «Поле» и «Имя таблицы» содержат списки, которые позволяют определить нужное поле. Таблица бланка запросов расширяется вправо автоматически при добавлении в запрос новых полей. Контекстное меню, связанное с бланком запроса, позволяет скрывать или, наоборот, показывает строки «Имя таблицы» и «Групповая операция». Под «Групповой операцией» понимается обработка и обобщение значений данного поля при помощи определенной функции (Например функция «Count» позволяет подсчитать число записей).
Если воспользоваться вариантом Простой запрос, то откроется диалоговое окно Мастера простых запросов, в котором следует выбрать участвующие в запросе поля из любых таблиц или запросов, входящих в базу данных. В последнем диалоговом окне Мастера пользователю предлагается выбор выполнить запрос или просмотреть его структуру в режиме конструктора. Основное преимущество этого способа заключается в том, что Мастер автоматически заполняет бланк запроса ). Если полученный запрос не соответствует требованиям, в него можно внести изменения в режиме конструктора.
В том случае, когда из всего массива данных одной или нескольких таблиц нужно выбрать лишь те данные, которые повторяются, следует воспользоваться вариантом «Повторяющиеся записи». Например, выбрать те товары, количество продаж которых «больше чем один».
Если нужно найти среди всех записей базы данных те, которые не имеют подчиненных, следует выбирать вариант, «Записи без подчиненных». Например, это достаточно типовая ситуация для любой базы данных, фиксирующей торговые операции по конкретным клиентам или по конкретным товарам. Незачем хранить информацию по конкретной поставке товара, если сам товар уже давно продан. Также незачем хранить в списке «постоянных клиентов» фамилии тех людей, которые уже длительное время не осуществляли никаких покупок. Очевидно, что чем меньше записей приходится просматривать, тем быстрее работает вся СУБД.
Существует ряд вычислений, которые можно выполнить в запросе, например, найти сумму или среднее по значениям одного поля, перемножить значения двух полей или вычислить дату, отстоящую на заданное количество месяцев от текущей даты.
Результаты вычислений, выводящиеся в поле, не запоминаются в базовой таблице. Вместо этого, вычисления снова производятся всякий раз, когда выполняется запрос, поэтому результаты всегда представляют текущее содержимое базы данных. Обновить вычисленные результаты вручную невозможно.
Для определения вычисляемого поля можно использовать встроенные функции MS Access или выражения, создаваемые пользователем. Для каждого вычисляемого поля следует выбрать одно выражение. Выражения, определяющие вычисляемые поля, создаются с помощью мастера простых запросов или вводятся пользователем в строку «Групповая операция» бланка запроса, в которой допускается выбор статистических функций для выполняемых над полем вычислений. В бланке запроса задают также условия отбора, с помощью которых определяются группы, для которых вычисляются итоговые значения, записи, включаемые в вычисления, или результаты, отображаемые после выполнения расчетов.
Для расчетов с использованием формул, определяемых пользователем, требуется создать новое вычисляемое поле прямо в бланке запроса. Вычисляемое поле создается с помощью выражения, которое вводится в пустую ячейку «Поле» в бланке запроса. Результаты вычислений не обязательно должны отображаться в поле. Вместо этого, их можно использовать в условиях отбора для определения записей, которые выбираются в запросе, или для определения записей, над которыми производятся какие-либо действия.
6.4. Условия отбора записей
Условия отбора записей могут задаваться для одного или нескольких полей в строке «Условие отбора»бланка запроса (см. рисунок 6.3.2). Условием отбора является выражение, которое состоит из операторов сравнения и операндов, используемых для сравнения. В качестве операндов выражения могут использоваться: литералы и константы.
Литералом называют значение в явном представлении, например, число, строковое значение или дату. Примерами значений в явном представлении могут служить «Москва», 100 и #1-янв-94#. Даты необходимо заключать в символы (#), а строковые значения в прямые кавычки («).
Константа представляет не изменяющееся значение. True, False, Истина, Ложь и Null являются примерами констант, автоматически определяемых в MS Access. Допускается также использование подстановочных знаков, представленных в следующей таблице.
Пример
Использование
Оператор BETWEEN. AND определяет принадлежность значения выражения указанному диапазону
Оператор IN проверяет, совпадает ли значение выражения с одним из элементов указанного списка, который задается в круглых скобках.
Оператор AND требует одновременного выполнения всех выражений, которые он соединяет.
Оператор OR требует выполнения хотя бы одного из тех выражений, которые он соединяет.
Оператор NOT требует невыполнения того выражения, перед которым он стоит.
Примеры определения диапазонов значений с помощью операторов представлены в следующей таблице
Пример выражения
Описание
Еще одним встроенным элементом поиска нужной информации в Access являются функции. Функция возвращает значение, которое является результатом расчетов или выполнения других операций. В Microsoft Access определен ряд встроенных функций, например:
функция Sum возвращает сумму набора значений поля; функция Avg вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса; функция Count вычисляет количество записей, возвращаемых запросом.
6.5. Запросы-действия
Запрос на создание таблицы-создает новую таблицу на основе всех или части данных из одной или нескольких таблиц и заносит в новую таблицу выбранные записи. Этот тип запроса может быть полезен при создании резервных копий информации. Например, отобрать все старые записи для копирования в отдельную резервную таблицу, после чего все эти записи удалить с помощью запроса на удаление из основной таблицы.
Запрос на добавление— добавляет группу записей из одной или нескольких таблиц в одну или несколько других.
При работе с запросом в режиме конструктора можно преобразовать существующий запрос к одному из этих четырех типов. Для это следует выбрать тип запроса из меню » Запрос» (см. Рисунок 6.5.1).
При преобразовании запроса к другому типу состав строк бланка запросов соответствующим образом меняется. В двух типах запросов «Создание таблицы» и «Добавление» MS Access выводит диалоговое окно для получения дополнительной информации перед переходом в режим конструктора.
Пользователь может открыть в режиме конструктора различные запросы: запрос на выборку, перекрестный запрос и запрос на изменение. Запрос на выборку и перекрестный запрос также можно открыть в режиме таблицы для просмотра результатов. Запрос на изменение нельзя открыть в режиме таблицы. Однако в этом режиме можно выполнить предварительный просмотр данных, которые будут задействованы в запросе на изменение. Такой запрос отличается тем, что в окне базы данных рядом с его именем стоит восклицательный знак (!).
6.6. Запросы SQL
Запрос управление создает или вносит изменения в объекты базы данных, такие как таблицы Microsoft Access или Microsoft SQL Server.
2 Создание SQL запросов в СУБД Access
«Создание структурированных запросов в СУБД Access»
· создавать SQL запросы на выборку в СУБД Access;
· сортировать результаты запросов с помощью ключевого слова ORDER BY;
· создавать запросы SQL с определенным условием с помощью ключевого слова WHERE.
Запросы вы будете создавать в БД “Movies” с которой вы уже работали на прошлом уроке. Вы можете использовать уже использованную БД или взять готовую БД без запросов у учителя.
Для создания запросов SQL в СУБД Access вам нужно:
1. Перейти во вкладку «Создание», выбрать команду «Конструктор запросов».
2. Далее вам необходимо закрыть окно «Добавление таблицы».
3. В любом пустом месте окна щелкните правой кнопкой мыши и в контекстном меню выберите «Режим SQL»
во вкладке «Конструктор» можете нажать на кнопку «Режим SQL».
4. В появившемся окне вы можете вводить свои команды. По умолчанию здесь уже указана команда «SELECT».
SQL запрос на выборку 1.
Первый запрос самый простой: вывести на экран все данные из таблицы tblFilms. В режиме SQL введите следующую команду:
Нажмите кнопку «Выполнить».
В результате выйдет вся информация о фильмах из таблицы tblFilms.
Символ «*» после команды SELECT означает, что будут выводиться все поля, данные в которых соответствуют условию отбора.
SQL запрос на выборку 2.
Для того, чтобы вывести только конкретные поля из таблицы нам нужно их перечислить через запятую после команды SELECT.
Например, нам необходимо вывести только поля ActorName и ActorAge из таблицы tblActors. Для этого в режиме SQL вводим следующую команду:
SQL запрос на выборку 3.
В некоторых случаях необходимо выводить данные отсортированные по алфавиту. Для этого используется ключевое слово ORDER BY после которого указывается имя столбца, по которому будет происходить сортировка. Синтаксис следующий:
SELECT имя_столбца FROM имя_таблицы ORDER BY имя_столбца_сортировки;
Например, чтобы вывести сведения об актерах и их возрасте в порядке от самого молодого до самого старого необходимо использовать следующую команду:
Результат выполнения запроса:
По умолчанию сортировка идет по возрастанию, но это можно изменить, добавив ключевое слово DESC:
Теперь наши данные отсортированы в порядке по убыванию.
Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие фильмы были выпущены в 1957 году. Для этого в SQL есть ключевое слово WHERE, синтаксис у такого запроса следующий:
SELECT имя_столбца FROM имя_таблицы WHERE условие;
Для нашего примера условием является год выпуска фильма, т.е. нам нужны только те строки, в столбце FilmYear которых стоит 1957:
Если вы хотите отобрать все фильмы, которые были выпущены после 2000 года, вам необходимо использовать оператор «> (больше)»:
Аналогично, для того, чтобы отобразить фильмы выпущенные до 1960 года используется оператор «
А для того, чтобы отобрать фильмы, выпущенные с 1998 года по 2003 год используется оператор BETWEEN (между):
Если у вас осталось время, вы можете самостоятельно выполнить следующие запросы:
1. Создайте SQL запрос, позволяющий отобразить сведения об актерах, состоящих в браке. (Подсказка, условие отбора ActorMarried =true ).
2. Создайте SQL запрос, позволяющий отобразить отсортированные по алфавиту сведения об актерах, имеющих рейтинг 9.
3. Создайте SQL запрос, позволяющий отобразить только фильмы, в жанре Action, отсортированные по году выпуска от самого нового фильма до самого старого.
Если у вас возникли трудности, обратитесь к учителю.