Запрос на добавление записей

С помощью этого типа запроса блоки данных одной таблицы (все или отобранные запросом) можно присоединить в конец другой таблицы.

• 1. Создайте новый запрос на выборку тех блоков данных, которые будут добавлены в некоторую таблицу, и проверьте его корректность, перейдя в режим Таблицы.
• 2. Преобразуйте запрос на выборку в запрос на добавление, для чего, вернувшись в режим Конструктор, выберите команду Добавление (вкладка ТИП ЗАПРОСА).
• 3. В появившемся окне введите имя таблицы, к которой нужно присоединить данные и нажмите ОК.
• 4. Выполните запрос.

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

• 1. Создайте новый запрос на выборку удаляемых блоков данных; отбор блоков данных выполняется в соответствии с заданными в строке Условие критериями.
• 2. Проверьте корректность сформулированных условий, перейдя в режим Таблицы.
• 3. Преобразуйте запрос па выборку в запрос па удаление записей, для чего, вернувшись в режим Конструктор, выберите команду Удалить (вкладка ТИП ЗАПРОСА)
• 4. В появившейся строке Удалить установите критерии отбора.
• 5. Выполните запрос.

Рассмотрим создание запросов на модификацию на примерах.

Пример 3.1

Создадим запрос на создание таблицы «Мониторы», полученной из таблицы «Товары». Таблица «Мониторы» должна содержать все поля таблицы «Товары» и только те записи, в которых наименование товара — Монитор.

1. Создадим запрос на выборку данных о мониторах из таблицы «Товары» (рис. 3.45), предварительно создав копию таблицы «Товары».

Рис. 3.45. Запрос на выборку данных о мониторах

Для выбора всех полей таблицы «Товары» использована «*». Условие отбора задано для ноля Наименование. Во избежание дублирования поля Наименование_у в таблице «Мониторы» отсутствует «галочка» в строке Вывод на экран.

2. Изменим тип запроса на выборку в запрос на создание таблицы, при этом появится окно для ввода имени создаваемой таблицы (рис. 3.46).

Рис. 3.46. Запрос на создание таблицы

3. Выполним запрос, при этом появится предупредительное окно (рис. 3.47).

Рис. 3.47. Предупредительное окно.

4. Подтвердим создание новой таблицы с выбранными записями, в результате в списке таблиц БД увидим новую таблицу «Мониторы» (рис. 3.48).

Рис. 3.48. Таблица «Мониторы».

Пример 3.2.

Создадим запрос на обновление цен товаров с учетом сезонных скидок на 10% (рис. 3.49).

1. Предварительно создадим копию таблицы «Товары», присвоив ей имя «КопияТовары».^[1][2][3][4]

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

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

SELECT^[5][6][7] FROM.

| WHERE Спецификация—отбора—строк>] [GROUP BY Спецификация—группировки>].

[ HAVING Спецификация—отбора—групп>|.

[ORDER BY Спецификация—сортировки>].

Синтаксис

[[[{имя—таблицы имя—запроса—на—выборку псевдопим}]. имя— поля[]

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

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

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

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

Пример 3.3

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

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

Синтаксис

FROM {имя—таблицы [[AS] псевдоним ] имя—запроса—на—выборку [[AS] псевдоним ].

<�таблица—объединения^ …

[IN Спецификация источника>] где <�таблица—объединения^.

[имя—таблицы [[AS] псевдоним ] имя—запроса—на—выборку [[AS] псевдоним ] ()}.

(INNER | LEFT | RIGHT} JOIN {имя—таблицы [[AS] псевдоним ] имя—запроса—на—выборку [[AS] псевдоним ] (<�таблица—объединений)}.

ON.

Пример 3.4

Следующий запрос отображает все поля таблицы «Товары»: SELECT Товары.*.

FROM Товары;^[8]

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

Пример 3.5

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

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

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

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

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

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

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

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

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

Пример 3.6

Следующий запрос отображает сведения о складах фирмы Citilink:

SELECT Склады. НомерСклада, Склады. Адрес, Склады. Телефон FROM Фирмы INNER JOIN Склады ON Фирмы. КодФирмы = Склады. КодФирмы WHERE Фирмы. Название = «Citilink»;

Пример 3.7

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

SELECT Товары. Наименование, Хранение. НомерСклада FROM (Товары INNER JOIN Хранение ON Товары. КодТовара = Хранение. КодТовара) LEFT JOIN Продажи ON Хранение. Ш = Продажи. ID WHERE ((Продажи.Количество) Is Null);

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

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

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

Синтаксис

GROUP BY имя—столбца,…

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

Пример 3.8

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

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

AS [Sum—Количество].

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

INNER JOIN Продажи ON Хранение.10=Продажи.ID GROUP BY Товары. Наименование;

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

Синтаксис

HAVING.

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

Пример 3.9.

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

SELECT Продажи. Дата, Sum (Продажи.Количество)Л8 [Sum—Количество] FROM Продажи GROUP BY Продажи.Дата.

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

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

Синтаксис

ORDER BY {имя—столбца | номер—столбца [ASC | DESC]},…

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

Пример 3.10.

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

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

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

Синтаксис

SELECT [ALL | DISTINCT | DISTINCTROW | TOP число.

[PERCENT]].

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

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

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

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

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

Пример 3.11

Следующий запрос отображает список 10 самых дорогих товаров: SELECT ТОР 10 Товары. Наименование, Товары. Цена FROM Товары.

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

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

Пример 3.12

Необходимо получить список процессоров, цена которых ниже средней цены процессоров:

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

FROM Товары.

WHERE ((Товары.Наименование) Like «проц*») AND ((Товары.Rena)<(SELECT Avg (Tовары.Цена) AS СредняяЦена.

FROM Товары.

WHERE ((Товары.Наименование) Like «проц*»)));

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

• [1] Создадим запрос на выборку, отображающий поле Цена (выбираем только тополе, значения которого должны быть обновлены).
• [2] Изменим тип запроса на выборку в запрос на обновление.
• [3] В строке Обновление введем выражение, обновляющее значение поля с помощью Построителя выражений.
• [4] Выполним запрос.
• [5] В угловые скобки заключается обязательный элемент синтаксиса.
• [6] В квадратные скобки заключаются один или несколько необязательных элементов, разделенных символом «вертикальная черта» (|).
• [7] В фигурные скобки заключаются один или несколько элементов, разделенных символом «вертикальная черта». Следует выбрать один из перечисленных элементов.
• [8] Запрос должен заканчиваться символом «точка с запятой» (;).