База данных. ЭУМК Базы данных. Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск

• Название: Пояснительная записка Теоретический раздел Практический раздел Раздел контроля знаний Вспомогательный раздел Пинск
• Анкор: База данных
• Дата: 26.02.2023
• Размер: 2.33 Mb.
• Имя файла: ЭУМК Базы данных.pdf
• Тип: Пояснительная записка; #956531
• страница: 7 из 18

TOP используется для отображения некоторого количества начальных и конечных записей из результирующего набора. Для ограничения числа записей в результирующем наборе ключевое слово TOP в запросах сочетается с предложением, указывающим порядок сортировки.
Причем ключевое слово TOP можно комбинировать как с числом, означающим количество записей, так и с числом, означающим процентное содержание отображаемых записей.
Например, выбрать из таблицы
СТУДЕНТЫ
(Номер_зачетки,
Шифр_группы, ФИО, Год_выпуска, Средний_балл) 25 лучших студентов выпуска 2005 года:
SELECT TOP 25 ФИО, Шифр_группы
FROM СТУДЕНТЫ
WHERE Год_выпуска = 2005
ORDER BY Средний_балл DESC;

Число, используемое в предикате TOP, должно быть целым без знака. Без директивы ORDER BY в выборку попали бы любые 25 студентов выпуска 2005 года. Предикат TOP не разделяет записи, имеющие одинаковые значения при упорядочивании. Это значит, если 25-й, 26-й, 27-й студенты имеют одинаковый средний балл, то в выборке будет не 25, а 27 записей.
Например, выбрать из таблицы R1 пять студентов с наивысшим средним баллом (средний балл вычисляется на основе поля Оценка, в выборке вычисляемое поле названо Средний балл с помощью конструкции AS):
SELECT TOP 5 R1.ФИО, Avg(R1.Оценка) AS [Средний балл]
FROM R1
GROUP BY R1.ФИО
ORDER BY Avg(R1.Оценка) DESC;
Можно использовать ключевое слово PERCENT для того, чтобы включить в выборку определенный процент из верхней или нижней части диапазона, отсортированного в соответствии с директивой ORDER BY.
Например, выбрать десять процентов записей выпуска 2005 года из таблицы СТУДЕНТЫ:
SELECT TOP 10 PERCENT ФИО, Шифр_группы
FROM СТУДЕНТЫ
WHERE Год_выпуска = 2005
ORDER BY Средний_балл DESC;
С помощью SQL можно вкладывать запросы внутрь друг друга. Обычно внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса (в предложении WHERE или HAVING), определяющего, верно оно или нет. Совместно с подзапросом можно использовать предикат
EXISTS, который возвращает истину, если вывод подзапроса не пуст.

В части FROM оператора SELECT допустимо применять синонимы к именам таблицы, если при формировании запроса требуется более чем один экземпляр некоторого отношения. Синонимы задаются с использованием ключевого слова AS, которое может быть вообще опущено. Поэтому часть
FROM может выглядеть следующим образом:
FROM R1 AS A, R1 AS B
Или
FROM R1 A, R1 B
Оба выражения эквивалентны и рассматриваются как применения оператора SELECT к двум экземплярам таблицы R1.
Запрос, содержащий подзапрос, называется сложным. В процессе его выполнения сначала выполняется подзапрос, а затем основной запрос. При создании сложного запроса необходимо следовать следующим правилам: подзапросы должны заключаться в круглые скобки; предложение ORDER BY может быть использовано только в основном запросе; подзапросы, возвращающие записи, могут использоваться только с условием EXISTS (который определяет пуста или не пуста результирующая выборка записей).
Рассмотрим примеры вложенных запросов.
Пример 1. Сформировать список тех, кто сдал все положенные экзамены.
SELECT ФИО
FROM R1 AS A
WHERE Оценка >2
GROUP BY ФИО
HAVING COUNT(*) = (SELECT COUNT(*) FROM R2,R3
WHERE R2.Группа = R3.Группа AND ФИО = A.ФИО);
Результат:
ФИО

Петров
Сидоров
Трофимов
Иванова
Уткина
Здесь во встроенном запросе определяется общее число экзаменов, которые должен сдавать каждый студент, обучающийся в группе, в которой учится данный студент. При этом это число сравнивается с числом экзаменов, который сдал данный студент.
Пример 2. Сформировать список тех, кто должен был сдавать экзамен по
Моделированию, но пока еще не сдавал:
SELECT ФИО
FROM R2 AS A, R3
WHERE A.Группа = R3.Группа AND R3.Дисциплина = «Моделирование»
AND EXISTS (SELECT ФИО FROM R1
WHERE ФИО=A.ФИО AND Дисциплина =
«Моделирование» AND Оценка IS NULL);
Результат: Миронов
Предикат EXISTS (SubQuery) истинен, когда подзапрос SubQuery не пуст, то есть содержит хотя бы один кортеж, в противном случае предикат EXISTS ложен. Предикат NOT EXISTS обратно – истинен только тогда, когда подзапрос SubQuery пуст.
Пример 3. Пусть имеются два отношения РТ (Номер_поставщика,
Номер_товара) и Т (Номер_товара, Наименование). Найти поставщиков, которые поставляют все товары.
SELECT DISTINCT [Номер_поставщика]
FROM PT AS PT1

WHERE NOT EXISTS
(SELECT Номер_товара FROM T
WHERE NOT EXISTS
(SELECT * FROM PT AS PT2
WHERE
PT2.Номер_поставщика=
PT1.Номер_поставщика
AND
PT2.Номер_товара = T.Номер_товара));
Фактически запрос был переформулирован так: «Найти поставщиков таких, что не существует детали, которую бы они не поставляли». Данный запрос может быть реализован и через агрегатные функции с подзапросом:
SELECT DISTINCT PT.Номер_поставщика,
COUNT(PT.Номер_товара) AS [COUNT- Номер_товара]
FROM PT
GROUP BY PT.Номер_поставщика
HAVING (((Count(PT.Номер_товара))=
(SELECT Count(Номер_товара) FROM T)));
Пример 4. Даны две таблицы: Товары (Код_товара, Наименование, Цена) и
Продажи (Чек, Код товара, Дата, Продано). Из таблицы Товары требуется отобрать товары (указать поля Наименование и Цена), для которых поле
Продано превышает 10.
SELECT Наименование, Цена FROM Товары
WHERE EXISTS (SELECT [Код товара] FROM Продажи
WHERE (Продано > 10) AND (Товары.[Код_товара]=
Продажи.[Код_товара]);
Пример 5. Выбрать из таблицы Продажи (указать поля Чек и Продано) товары с наименование «Шоколад Путешествие».
SELECT Чек, Продано FROM Продажи
WHERE [Код_товара] = (SELECT [Код_товара] FROM Товары WHERE
Наименование = “Шоколад Путешествие”);
Поскольку в таблице Продажи не содержится наименование товара, то сначала с помощью подзапроса обращаются к таблице Товары и определяют

код товара заданного наименования, а затем в основном запросе выбирают поля из таблицы Продажи, в которых код товара совпадает с кодом, полученным в результате выполнения подзапроса.
Стандарт SQL2 расширил понятие условного объединения. В стандарте
SQL1 при объединении отношений использовались только условия, задаваемые в части WHERE оператора SELECT, и в этом случае в результирующее отношение попадали только сцепленные по заданным условиям кортежи исходных отношений, для которых эти условия были определены и истинны.
Однако в действительности часто необходимо объединять таблицы таким образом, чтобы в результат попали все строки из первой таблицы, а вместо тех строк второй таблицы, для которых не выполнено условие соединения, в результат попадали бы неопределенные значения. Или наоборот, включаются все строки из правой (второй) таблицы, а отсутствующие части строк из первой таблицы дополняются неопределенными значениями. Такие объединения были названы внешними в противоположность объединениям, определенным стандартом SQL1, которые стали называться внутренними.
Внутреннее объединение (INNER JOIN) возвращает записи из двух таблиц, если значение первичного ключа первой таблицы соответствует значению внешнего ключа второй таблицы, связанной с первой.
В общем случае синтаксис части FROM в стандарте SQL2 выглядит следующим образом:
FROM <список исходных таблиц>|
<выражение естественного объединения>|
<выражение объединения>|
<выражение перекрестного объединения>|
<выражение запроса на объединение>
где
<список исходных таблиц> -
<имя таблицы1> [имя синонима таблицы1] […]
[,<имя таблицыN> [имя синонима таблицыN]];

<выражение естественного объединения> -
<имя таблицы1> NATURAL { INNER | FULL [OUTER]
LEFT [OUTER] | RIGHT [OUTER]} JOIN <имя таблицы2>|
<выражение объединения> -
<имя таблицы1> { INNER | FULL [OUTER] | LEFT [OUTER] | RIGHT
[OUTER]} JOIN { ON условие | [USING
(список столбцов)]} <имя таблицы2>
<выражение перекрестного объединения> -
<имя таблицы1> CROSS JOIN <имя таблицы2>
<выражение запроса на объединение> -
<имя таблицы1> UNION JOIN <имя таблицы2>
В этих определениях INNER – означает внутреннее объединение, LEFT – левое объединение, то есть в результат входят все строки первой таблицы, а части результирующих кортежей, для которых не было соответствующих значений во второй таблице, дополняются значениями NULL (не определено).
Ключевое слово RIGHT означает правое внешнее соединение, и в отличие от левого соединения в этом случае в результирующее отношение включаются все строки второй таблицы, а недостающие части из первой таблицы дополняются неопределенными значениями. Ключевое слово FULL определяет полное внешнее объединение: левое и правое. При полном внешнем объединении выполняются и правое и левое внешние объединения и в результирующее отношение включаются все строки из первой таблицы, дополненные неопределенными значениями, и все строки из второй таблицы, также дополненные неопределенными значениями. Ключевое слово OUTER означает внешнее объединение, но если заданы ключевые слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.
Примеры внутренних и внешних объединений

Пример внутреннего объединения с помощью выражения эквивалентности между двумя полями – объединить R2 и R3 по полю Группа. В отношение R2 добавлены два студента – Попова из группы АИ21 и Бурковский – из АИ22.
SELECT R2.ФИО, R2.Группа, R3.Дисциплина
FROM R2, R3
WHERE R2.Группа =R3.Группа;
Результат:
ФИО
Группа
Дисциплина
Петров
АИ41
БД
Петров
АИ41
Моделирование
Сидоров
АИ41
БД
Сидоров
АИ41
Моделирование
Миронов
АИ41
БД
Миронов
АИ41
Моделирование
Трофимов
АИ42
Сети ЭВМ
Иванова
АИ42
Сети ЭВМ
Уткина
АИ42
Сети ЭВМ
Попова
АИ41
БД
Попова
АИ41
Моделирование
Бурковский
АИ42
Сети ЭВМ
Пример внутреннего объединения с помощью ключевых слов INNER
JOIN:
SELECT R2.ФИО, R2.Группа, R3.Дисциплина
FROM R2 INNER JOIN R3 ON R2.Группа =R3.Группа;
Результат будет таким же, как и в предыдущем примере.
Пример левого объединения. Полный список студентов с указанием сданных экзаменов, если студент сдавал экзамены и вывод фамилий и групп студентов, не сдававших экзамен.
SELECT [R2].[ФИО], [R2].[Группа],
[R1].[Дисциплина], [R1].[Оценка]
FROM R2 LEFT JOIN R1 ON [R2].[ФИО]=[R1].[ФИО];
Результат:
ФИО
Группа
Дисциплина
Оценка
Бурковский АИ42
Иванова
АИ42
Сети ЭВМ
5
Миронов
АИ41
БД
2
Миронов
АИ41
Моделирование
Петров
АИ41
БД
5

Петров
АИ41
Моделирование 5
Попова
АИ41
Сидоров
АИ41
БД
4
Сидоров
АИ41
Моделирование 4
Трофимов
АИ42
Сети ЭВМ
4
Уткина
АИ42
Сети ЭВМ
5
Пример правого объединения (список студентов, сдававших экзамены).
SELECT DISTINCT R1.ФИО
FROM R2 RIGHT JOIN R1 ON R2.ФИО = R1.ФИО;
Результат:
ФИО
Иванова
Миронов
Петров
Сидоров
Трофимов
Уткина
Внешние объединения в Delphi записываются следующим образом:
Левое внешнее объединение, при котором в выборку включаются все записи таблицы, имя которой указано слева от оператора OUTER JOIN.
…
FROM <табл. 1> LEFT OUTER JOIN <табл. 2>
ON <условие соединения таблиц>
Правое внешнее объединение, при котором в выборку включаются все
записи таблицы, имя которой указано справа от оператора OUTER JOIN.
…
FROM <табл. 1> RIGHT OUTER JOIN <табл. 2>
ON <условие соединения таблиц>
Полное внешнее объединение означает, что в выборку включаются
все записи из правой и левой таблиц.
…
FROM <табл. 1> FULL OUTER JOIN <табл. 2>
ON <условие соединения таблиц>

Пример. Выбрать из таблицы Товары список всех товаров, а из таблицы
Продажи – суммарное количество проданных товаров:
SELECT Товары.Наименование, Sum(Продажи.Продано) AS [Всего продано]
FROM Товары LEFT OUTER JOIN Продажи
ON Товары.[Код товара] = Продажи.[Код товара]
GROUP BY Товары.Наименование;
Так как таблица Товары указана слева от оператора LEFT JOIN, то результирующая выборка будет содержать полный список товаров, включая те, что ни разу не были проданы.
Перекрестный запрос – способ группировки данных по двум измерениям, позволяющий отображать итоги в компактном результирующем наборе. В перекрестном запросе группировка выполняется по одному полю, а итоговая функция применяется к другому полю. Структура перекрестного запроса следующая.
В конструкции 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 Фамилия;
Манипулирование данными осуществляется с помощью трех операций: операция удаления записей – ей соответствует оператор 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);
Результат выполнения последних четырех запросов: