Построение сложных запросов в среде MS Access 2007

Построение сложных запросов в среде MS Access 2007

Вопросы лекции: 1. Понятие подзапроса; 2. Виды соединений; 3. Изменение данных посредством запросов;

Сложные запросы получаются из других запросов следующими способами: • Вложением SQL-выражения запроса в SQLвыражение другого запроса. Первый из них называют подзапросом, а второй – внешним или основным запросом; • Применением к SQL-запросам операторов объединения или соединения наборов записей, возвращаемых запросами.

Подзапросы Подзапрос – это SQL-выражение, начинающееся с оператора SELECT, которое содержится в условии оператора WHERE или HAVING для другого запроса.

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

Простые подзапросы Выделяют три частных случая простых подзапросов: 1. Подзапросы, возвращающие единственное значение; 2. Подзапросы, возвращающие список значений из одного столбца таблицы; 3. Подзапросы, возвращающие набор записей;

Работа с единственным значением Например, необходимо выбрать из таблицы Клиенты те записи, сумма заказов которых больше среднего значения. SELECT * FROM Клиенты WHERE Сумма заказа > (SELECT AVG (Сумма заказа ) FROM Клиенты);

Связанные подзапросы Связанные (коррелированные) подзапросы ссылаются на таблицу, которая упоминается в основном запросе и должен выполнятся в некотором контексте с текущим состоянием основного запроса. SELECT A FROM T 1 WHERE B= (SELECT B FROM T 2 WHERE C=T 1. C);

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

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

Внутреннее соединение (эквисоединение) Если в подчиненной таблице записи с заданной величиной отсутствуют, то соответствующие записи из главной таблицы в результирующее множество не включаются. Такие соединения между таблицами создаются автоматически, если: 1. В таблицах имеются поля с одинаковыми именами и согласованными типами данных, причем, одно из полей является ключевым; 2. Соединение было явно создано в окне схемы данных.

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

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

Внешние соединения Различают два вида внешних соединений: • Левое внешние соединение LEFT JOIN; • Правое внешние соединение RIGHT JOIN.

Пример создания LEFT JOIN Диалоговое окно параметра объединения Результат объединения

Пример создания RIGHT JOIN Диалоговое окно параметра объединения Результат объединения

Тэта-соединения Если необходимо связать данные любым отношением, кроме отношения равенства, используют соединения по отношению или тэта-соединения. Например, необходимо отобрать клиентов, у которых атрибуты Адрес клиента и Адрес получателя не совпадают: Select Клиенты. Адрес, Заказы. Адрес получателя From Клиенты INNER JOIN Заказы ON Клиенты. ИД = Заказы. ИД клиента Where Заказы. Адрес получателя<>Клиенты. Адрес;

Рекурсивные соединения Для связывания данных в одной таблице применяют рекурсивные соединения. Для этих целей в конструкторе запроса помещают копию таблицы (Таблица 1) и связывают поля идентичных таблиц.

Пример рекурсивного соединения

Запросы на изменение MS Access позволяет изменять данные посредством запросов. Такие запросы называются запросами на изменение и делятся на четыре категории:

1. Запросы на создание таблиц Создают таблицы на основе данных, содержащихся в результирующем множестве запросов. Чаще всего такой тип запросов используется при экспорте информации в другие приложения. В SQL запрос на создание таблицы добавляется оператором INTO [новая таблицы] внутри инструкции SELECT.

2. Запросы на добавление записей Запрос на добавление данных используется часто с целью копирования данных из одной таблицы в другую. На SQL запрос на добавление начинается с оператора INSERT. Например, INSERT INTO [Личные. Дела 1] ( лд. Тип, лд. Предложил, лд. Дата. Назначено ) SELECT Личные. Дела. лд. Тип, Личные. Дела. лд. Предложил, Личные. Дела. лд. Дата. Назначено FROM Личные. Дела;

Сообщение приложения о выполнении добавления данных

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

Пример запроса на обновление Например, необходимо изменить адрес улицы с им. Фрунзе на проспект Славы в таблице Клиенты. UPDATE Клиенты SET Клиенты. Адрес = "проспект Славы" WHERE (((Клиенты. Адрес) =“им. Фрунзе”));

4. Запросы на удаление записей Позволяет отобрать требуемые записи и удалить их за один прием. Однако, удаляя данные из связанных таблиц необходимо помнить о целостности данных. Оператор SQL эквивалентный удалению DELETE.

Перекрестные запросы Это запросы в которых происходит статистическая обработка данных. Перекрестные запросы обладают рядом достоинств: • Возможность обработки большого объема данных и вывода их в формате, удобном для автоматического создания графиков и диаграмм; • Простота и скорость разработки сложных запросов.

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