3.5.9. Перекрестные запросы

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

- в конструкции TRANSFORM указывается поле и групповая функция, применяемая к нему. Данное поле выводится на пересечении строк и столбцов;

- в Select указывается поле, выводимое в заголовках строк;

- в From указываются имена таблиц, из которых выбираются данные;

- в конструкции GROUP BY указывается поле, по которому проводится группировка и которое выводится в качестве заголовков строк.

- в конструкции PIVOT указывается поле, значения которого выводятся в качестве заголовков столбцов.

Пример 1. Дана таблица Продажа_бензина (Чек, Марка, Дата_продажи, Оператор, Количество). Определить суммарную продажу каждой марки бензина за каждый день. Марки бензина вывести в столбцах, даты – в строках, суммарную продажу – на пересечении строк и столбцов.

TRANSFORM SUM(Количество) AS SUM_Количество

SELECT Дата_продажи

FROM Продажа_бензина

GROUP BY Дата_продажи

PIVOT Марка;

Пример 2. Даны таблицы Продажа_бензина (Чек, Марка, Дата_продажи, Номер_сотрудника, Количество, Цена) и Сотрудники (Номер_сотрудника, Фамилия, Имя, Отчество, Должность, Оклад). Определить суммарную стоимостную продажу бензина каждым сотрудником за каждый день. В строках вывести даты, в столбцах – фамилии, на пересечении строки и столбца – суммарную стоимостную продажу.

TRANSFORM SUM(Количество * Цена) AS SUM_Стоим

SELECT Дата_продажи

FROM Продажа_бензина INNER JOIN Сотрудники

ON Продажа_бензина.[Номер_сотрудника] = Сотрудники.[Номер сотрудника]

GROUP BY Дата_продажи

PIVOT Фамилия;

3.5.10. Операторы манипулирования данными

В операции манипулирования данными входят три действия: операция удаления записей – ей соответствует оператор DELETE, операция добавления или ввода новых записей – ей соответствует оператор INSERT и операция изменения (обновления полей в записях) – ей соответствует оператор UPDATE.

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

Оператор ввода данных INSERT имеет следующий синтаксис:

INSERT INTO имя_таблицы [(<список столбцов>)] VALUES (<список значений>)

Пример 1. Ввод одной записи в таблицу Экзамены.

INSERT INTO Экзамены (ФИО, Дисциплина, Оценка)

VALUES («Попова», «БД», 3);

Пример 2. При вводе полной строки можно не задавать список имен столбцов.

INSERT INTO Экзамены

VALUES («Попова», «Моделирование», 3);

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

INSERT INTO Экзамены (ФИО, Дисциплина)

VALUES («Бурковский», «Сети ЭВМ»);

В этом примере столбцу Оценка будет присвоено значение NULL.

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

Пример 4. Добавить из таблицы ПОСТАВКА в таблицу АРХИВ_ПОСТАВОК записи за 10 сентября 2012 года:

INSERT INTO АРХИВ_ПОСТАВОК

(Индекс_товара,Код_поставщика,Дата_поступления, Закупочная_цена, Количество)

SELECT

ПОСТАВКА.Индекс_товара,ПОСТАВКА.Код_поставщика,

ПОСТАВКА.Дата_поступления,

ПОСТАВКА.Закупочная_цена, ПОСТАВКА.Количество

FROM ПОСТАВКА

WHERE ПОСТАВКА.Дата_поступления = #10/09/12#;

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

DELETE FROM <имя_таблицы>

[WHERE <условия_отбора>]

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

Например, удалить из таблицы R1 данные о сдаче экзамена студентом Антоновым по дисциплине «БД»:

DELETE Э.*

FROM Экзамены Э

WHERE (ФИО = «Антонов») AND (Дисциплина = «БД»);

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

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

Запрос на обновление может изменить сразу целую группу записей. Этот запрос состоит из трех частей:

- предложение UPDATE, которое указывает на обновляемую таблицу;

- предложение SET, задающее данные для обновления;

- необязательный критерий WHERE, ограничивающий число записей, на которые воздействует запрос на обновление.

Примеры.

Изменить на 3 оценку по дисциплине «БД» у студента Миронова:

UPDATE Экзамены SET R1.Оценка = 3

WHERE (ФИО = «Миронов») AND (Дисциплина = «БД»);

Назначить всем стипендию в 2000 рублей:

UPDATE Студенты SET Стипендия = 2000;

В таблице Сотрудники обновить поле Оклад. Данное поле увеличить на процент, который запрашивается.

UPDATE Сотрудники

SET Оклад = Оклад * (100 + [Введите процент])/100;

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

UPDATE Сотрудники

SET Оклад = Оклад * (100 + [Введите процент])/100

WHERE [Табельный номер] = [Введите табельный номер];

Даны две таблицы Бензин (Марка, Цена, Общее_количество), Продажа (Чек, Марка, Цена_продажи, Дата, Продано). Обновить поле Цена_продажи в таблице Продажа, присвоить ему значение поля Цена из таблицы Бензин для соответствующих марок бензина.

UPDATE Продажа INNER JOIN Бензин

ON Бензин.Марка = Продажа.Марка

SET Продажа.Цена_продажи = Бензин.Цена;