- •1. Форматирование выходных данных запросов. 39
- •Тема 1.
- •1. Введение. История развития баз данных.
- •1. Введение. История развития баз данных
- •2. Основные понятия и определения
- •Тема 2.
- •1. Архитектура базы данных. Физическая и логическая независимость.
- •1. Архитектура базы данных. Физическая и логическая независимость
- •2. Разработка приложений в среде Microsoft Windows. Системы быстрой разработки приложений. Субд. Модели данных.
- •3. Основные этапы проектирование базы данных.
- •Тема 3.
- •1. Дополнительные общие рекомендации по проектированию базы данных.
- •2. Разработка приложений в среде Microsoft Windows.
- •1. Дополнительные общие рекомендации по проектированию базы данных.
- •2. Разработка приложений в среде Microsoft Windows.
- •Тема 4.
- •1. Построение таблиц.
- •2. Запросы в Microsoft Access. Параметры запросов на выборку данных.
- •3. Операции реляционной алгебры.
- •1. Построение таблиц
- •2. Запросы в Microsoft Access. Параметры запросов на выборку данных.
- •3. Операции реляционной алгебры.
- •Тема 5.
- •1. Понятие технологии «Клиент-сервер». Общие сведения о языке запросов sql.
- •2. Структура sql.
- •1. Понятие технологии «Клиент-сервер». Общие сведения о языке запросов sql.
- •2. Структура sql.
- •Тема 6.
- •1. Запрос выборки в языке sql. Выборка из одной таблицы.
- •2. Суммирование данных с помощью функций агрегирования (групповых функций).
- •1. Запрос выборки в языке sql. Выборка из одной таблицы.
- •2.Суммирование данных с помощью функций агрегирования (групповых функций).
- •Тема 7.
- •1. Форматирование выходных данных запросов.
- •2.Соединение таблиц
- •3.Вложенные подзапросы
- •4.Связанные подзапросы. Оператор exists
- •5.Вложенные и связанные подзапросы. Операторы any, all, come
- •Тема 8.
- •1. Форматирование выходных данных запросов.
- •1. Форматирование выходных данных запросов.
- •Тема 9.
- •1. Запросы обновления таблиц.
- •2. Создание, модификация и уничтожение таблиц. Ограничение на множество допустимых значений данных. Значение по умолчанию.
- •3. Создание и уничтожение индексов. Поддержка ссылочной целостности.
- •1. Запросы обновления таблиц.
- •2. Создание, модификация и уничтожение таблиц. Ограничение на множество допустимых значений данных. Значение по умолчанию.
- •3. Создание и уничтожение индексов. Поддержка ссылочной целостности.
- •Тема 10.
- •1. Создание представлений .
- •2. Определение правд доступа к данным.
- •1. Создание представлений.
- •2. Определение правд доступа к данным.
- •Тема 11.
- •1. Определение синонимов объектов. Понятие транзакций. Управление параллелизмом.
- •Тема 12.
- •1. Встроенный sql, основные понятия
- •1. Встроенный sql, основные понятия.
4.Связанные подзапросы. Оператор exists
Когда в SQL используются подзапросы во внутреннем вложенном запросе можно ссылаться на таблицу, имя которой указано в предложении FROM внешнего запроса, тем самым формируя связанный подзапрос.
Пример: Построить список студентов, сдававших экзамен 11/01/01
Sname |
C-2 |
C-3 |
WHERE 11/01/01 IN
(SELECT odate FROM оценка WHERE snum = osnum);
Поскольку значение в поле snum внешние запросы изменяются, внутренний запрос должен выполнятся отдельно для каждой строки внешнего запроса. Таким образом схемы выполнения связанного подзапроса имеют вид:
Из таблицы внешнего запроса выбирается очередная строка-кандидат
Выполняется подзапрос с использованием значения строки-кандидата.
Оцениваются условия внешнего запроса, по результатам чего строка-кандидат включается в результат или нет.
Если не конец таблицы внешнего запроса, то переход к пункту 1.
Следует отметить, что связанные подзапросы являются очень мощным средством SQL, т.к. позволяют записать или получить результаты с использованием сложных функций при достаточно компактных командах.
Подзапросы также могут использоваться для связывания таблиц со своей копией.
Пример: получить оценки студентов, превышающие их средний балл.
opnum |
osnum |
odate |
Ocen |
003 |
003 |
13/01/01 |
4 |
WHERE ocen >
(SELECT AVG(ocen) FROM оценка02
WHERE 02.osnum=01.osnum);
Связанные подзапросы могут использоваться в предложении HAVING при группировке данных.
Пример: пусть требуется показать средний балл, выставленный преподавателем только для тех преподавателей, у которых средний балл отличается от максимального более чем на 1 балл.
SELECT opnum, AVG(ocen) FROM оценка01
opnum |
AVG |
003 |
3,67 |
HAVING AVG(ocen)<
(SELECT MAX(ocen)-1 FROM оценка 02
WHERE 01.opnum=02.opnum);
Связанные запросы имеют много общего с соединениями(через декартово произведение), т.к. оба варианта включают сравнение одной строки таблицы с каждой строкой другой таблицы. Сходство заключается и в том, что многие операции, которые можно выполнить с помощью одного варианта, можно выполнить и с помощью другого варианта. Хотя, есть и различие. Например, подзапросы могут использовать функции агрегирования, а соединения позволяют включать в результат столбцы из двух таблиц. При этом лучше использовать ту форму запроса, которая является более понятной, но необходимо знать оба варианта, если один из них окажется неприемлемым.
При использовании вложенных подзапросов в условных выражениях внешнего подзапроса могут использоваться служебные операторы EXIST, ANY, ALL, COME.
EXIST – это оператор, генерирующий значения истина\ложь. Это значит, что его можно использовать самостоятельно в условиях или комбинировать с логическими выражениям с помощью AND, OR, NOT. Используя подзапрос в качестве аргумента, этот оператор возвращает истину, если результат подзапроса не пуст.
Пример: получить фамилии преподавателей, принимавших экзамен с 10/01/01 по 11/01/01.
Pname |
П-1 |
WHERE EXIST
(SELECT*FROM оценка
WHERE odate >=10/01/01 AND pnnum=opnum);
В этом запросе не имеет значения какие поля заданы в предложении SELECT внутреннего подзапроса. Важно в принципе наличие строк таблицы ОЦЕНКА, удовлетворяющих условию. Эту задачу можно решить и с помощью соединения таблиц:
SELECT DISTINCT FROM преподаватель, оценка
WHERE odate >=10/01/01 AND =<11/01/01 AND pnum=opnum;
Данный запрос выглядит короче, но с EXIST – эффективнее работает, по следующим причинам:
Для каждой записи таблицы ПРЕПОДАВАТЕЛЬ, таблица ОЦЕНКА просматривается только до первого выполнения условия.
Из выходных данных внешнего запроса не требуется удалять совпадающие значения.
Рассмотрим другой пример использования EXIST: получить коды преподавателей, имеющих более одного дипломника.
SELECT DISTINCT spdp FROM студент С1
Spdp |
001 |
(SELECT FROM студент С2
WHERE C1.snum <> C2.snum
AND C1.spdp=C2.spdp);
EXIST также можно комбинировать соединениями.
Пример: получить фамилии преподавателей, имеющих более 1 дипломника.
SELECT DISTINCT pname
Pname |
П-1 |
Студент С1 ON pnum-spdp
WHERE EXIST
(SELECT*FROM студент C2
WHERE C1.snum <> C2.snum
AND C1.spdp=C2.spdp);
Рассмотрим еще один пример вложенных подзапросов: получить фамилии преподавателей, имеющих студентов, которые сдавали экзамен более 1 раза.
SELECT pname FROM преподаватель
Pname |
П-3 |
(SELECT*FROM студент
WHERE 1<(SELECT count(*)
FROM оценка WHERE snum=osnum
AND pnum=opnum));