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

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

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

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

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

Пример1. Вычислить средние оценки по каждой дисциплине в каждой группе.

TRANSFORM Avg(R1.Оценка) AS [Avg-Оценка]

SELECT R3.Дисциплина

FROM (R2 INNER JOIN R1 ON R2.ФИО = R1.ФИО)

INNER JOIN R3 ON R2.Группа = R3.Группа

GROUP BY R3.Дисциплина

PIVOT R2.Группа;

Результат:

Дисциплина	АИ41	АИ42
БД	4,20
Моделирование	4,20
Сети ЭВМ		4,67

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

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

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

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

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

PIVOT Марка;

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

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

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

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

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

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

PIVOT Фамилия;

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

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

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

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

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

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

INSERT INTO R1(ФИО, Дисциплина, Оценка)

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

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

INSERT INTO R1

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

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

INSERT INTO R1(ФИО, Дисциплина)

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

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

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

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

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

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

SELECT ПОСТАВКА.[Индекс_товара], ПОСТАВКА.[Код_поставщика], ПОСТАВКА.[Дата_поступления],

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

FROM ПОСТАВКА

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

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

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

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

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

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

DELETE *

FROM R1

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

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

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

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

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

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

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

Примеры.

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

UPDATE R1 SET R1.Оценка = 3

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

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

UPDATE R5 SET R5.Стипендия = 400;

Назначить повышенную стипендию тем, кто имеет две пятерки:

UPDATE R5 SET R5.Стипендия = [R5].[Стипендия]*1.5

WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1 WHERE R1.Оценка =5 GROUP BY R1.ФИО HAVING COUNT(*) =2);

Назначить обычную стипендию тем, кто не имеет троек и сдавал все экзамены:

UPDATE R5 SET R5.Стипендия = [R5].[Стипендия]

WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1 WHERE R1.Оценка >=4 AND R1.ФИО NOT IN ( SELECT A.ФИО FROM R1 AS A WHERE A.Оценка <= 3 OR A.Оценка IS NULL));

Снять стипендию с тех, кто получил двойку или не сдавал экзамен:

UPDATE R5 SET R5.Стипендия = 0

WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1 WHERE R1.Оценка <= 2 OR R1.Оценка IS NULL);

Результат выполнения последних четырех запросов:

ФИО	Группа	Стипендия
Иванова	АИ42	400
Миронов	АИ41	0
Петров	АИ41	600
Сидоров	АИ41	400
Трофимов	АИ42	400
Уткина	АИ42	400

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

UPDATE Сотрудники

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

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

UPDATE Сотрудники

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

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

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

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

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

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