Соединение таблиц в SQL и реализация теоретико-множественных операций.
Соединения
FROM<источник данных>
В общем виде предложение FROM может содержать различные источники данных
<источник данных> ::=
<имя таблицы> [[AS] <псевдоним>] |
<подзапрос> [AS] <псевдоним>|
<соединение> | …
<соединение> ::=
<источник данных><тип соединения><источник данных>
ON<предикат соединения> |
<источник данных>CROSSJOIN<источник данных>
<тип соединения> ::=
[INNER | {{LEFT | RIGHT | FULL} [OUTER]}] JOIN
INNER – (по умолчанию) внутреннее соединение. Выбираются пары строк, для которых выполняется условие соединения, заданное предикатом (ON<предикат>)
LEFT – левое внешнее соединение: в результат включается внутреннее соединение таблиц, к которому добавляются строки из левой таблицы, не вошедшие во внутреннее соединение. Строки из левой таблицы, не вошедшие во внутреннее соединение, дополняются значениями NULL в соответствии со схемой результирующей таблицы.
RIGHT – правое внешнее соединение: в результат включается внутреннее соединение таблиц, к которому добавляются строки из правой таблицы, не вошедшие во внутреннее соединение. Строки из правой таблицы, не вошедшие во внутреннее соединение, дополняются значениями NULL в соответствии со схемой результирующей таблицы.
FULL – полное открытое соединение: в результат включается внутреннее соединение таблиц, к которому добавляются строки из левой таблицы, не вошедшие во внутреннее соединение, и строки из правой таблицы, не вошедшие во внутреннее соединение, которые дополняются значениями NULL в соответствии со схемой результирующей таблицы.
Концептуальная схема базы данных для примеров
E=<[ФИО], [Дисц], [Оценка]> - результаты сдачи экзаменов
G=<[ФИО], [Группа]> - состав группы
P=<[Группа], [Дисц]> - набор дисциплин, по которым надо сдавать экзамены группам
Получить список студентов, которым надо сдавать экзамены
SELECT [ФИО], [Дисц]
FROM G INNER JOIN P ON G.[Группа] = P.[Группа]
Получить список студентов имеющих несколько двоек
SELECT DISTINCT a.[ФИО]
FROM E aINNER JOIN E b ON a.[ФИО] = b.[ФИО]
WHERE a.[Дисц] <> b.[Дисц] AND a.[Оценка] <=2 AND b.[Оценка] <=2
Получить план сдачи экзаменов для всех студентов
SELECT G.[ФИО], P.[Дисц]
FROM G LEFT JOIN P ON G.[Группа] = P.[Группа]
Эквивалентная реализация соединения
Получить список студентов, которым надо сдавать экзамены
SELECT [ФИО], [Дисц] FROM G,P WHERE G.[Группа] = P.”Группа”
SELECT [ФИО], [Дисц] FROM G CROSS JOIN P WHERE G.[Группа] = P.”Группа”
SELECT [ФИО], [Дисц] FROM G JOIN P ON G.[Группа] = P.”Группа”
Операция соединения выполняется быстрее декартова произведения.
Получить результаты сдачи сессии для всех студентов (включая тех, кто должен был сдавать, но не сдавал экзамены)
SELECT G.[ФИО], P.[Дисц], E.[Оценка]
FROM G INNER JOIN P ON G.[Группа] = P.[Группа]
LEFT JOIN E ON G.[ФИО] = E.[ФИО] AND P.[Дисц] = E.[Дисц]
Для всех экземпляров книг и для всех читателей показать, какие книги есть у читателей и где находятся книги
SELECT Readers.name, Exemplars.inv
FROM Readers FULL JOIN Exemplars
ON Readers.reader_id = exemplars.reader_id
Агрегатные функции и группировка данных в SQL.
Агрегатные функции (обобщающие функции):
a) COUNT({[ALL | DISTINCT] <имя атрибута> | *}) – количество строк с непустыми значениями атрибута. Если *, то количество всех строк таблицы, не зависимо от содержания. Для числовых и символьных атрибутов
b) SUM([ALL | DISTINCT] <имя атрибута>) – сумма значений. Для числовых атрибутов
c) AVG([ALL | DISTINCT] <имя атрибута>) – среднее значение. Для числовых атрибутов
d) MIN(<имя атрибута>) – минимальное значение. Для числовых и символьных атрибутов. [ALL | DISTINCT] можно использовать, но не имеет смысла
e) MAX(<имя атрибута>) – максимальное значение. Для числовых и символьных атрибутов. [ALL | DISTINCT] можно использовать, но не имеет смысла
Подсчитать по каждой дисциплине количество студентов, сдававших экзамен
SELECT [Дисц], COUNT(*) AS counter
FROM E
GROUPBY [Дисц]
По каждой дисциплине посчитать количество положительных оценок
SELECT [Дисц], COUNT(*)
FROM E
WHERE [Оц] > 2
GROUP BY [Дисц]
| Имя | Дисц | Оц |
| Иванов | БД | 4 |
| Петров | БД | 2 |
| Иванов | ОС | 5 |
| Сидоров | ОС | 3 |
12. Вложенные запросы в SQL.
С вложенными запросами используются предикаты:
EXISTS (<подзапрос>) – равен true, если результат подзапроса не пуст
<выражение><оператор сравнения> ALL (<подзапрос>) – равен true, если оператор сравнения возвращает true для всех возвращаемых подзапросом значений
<выражение><оператор сравнения> ANY (<подзапрос>) – равен true, если оператор возвращает true хотя бы для одного значения, возвращаемого подзапросом (эквивалент SOME)
<выражение> [NOT] IN (<подзапрос>) – возвращает true, если результат подзапроса содержит результат выражения
Получить список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал его
SELECT [ФИО]
FROM G a JOIN P
ONG.[Группа] = P.[Группа]
WHERE [Дисц] = ‘БД’ AND
NOT EXISTS (SELECT [ФИО]
FROM E
WHERE [ФИО] = a.[ФИО] AND
[Дисц] = ‘БД’)
Здесь подзапрос возвращает либо одно значение (a.[ФИО] того, кто сдал экзамен), либо ни одного
Модель сущность-связь.
Сущность – класс однотипных объектов. Он имеет набор атрибутов
Атрибут – характеристика, определяющая свойство экземпляра сущности
Ключевой набор атрибутов – набор атрибутов, однозначно идентифицирующий экземпляр сущности
Связь – указывает как экземпляры сущностей соотносятся или взаимодействуют друг с другом
Тип связи (ассоциация) – обобщение связей между экземплярами сущностей
Дочерняя сущность (слабая сущность) – это сущность, которая зависит от другой сущности (не может существовать без другой сущности)
Родительская сущность (сильная сущность) – это сущность, которая не зависит от другой сущности
Дефект«разветвление»: имеет место тогда, когда модель отображает связь между сущностями, но путь между отдельными сущностями определен не однозначно (две и более ассоциации 1:N исходят из одной сущности)
Дефект«разрыв»: имеет место тогда, когда предполагается связь между сущностями, но нет пути между экземплярами этих сущностей.
Дефект возникает, если существует одна или несколько связей с минимальной кратностью, равной нулю (которая обозначает необязательное участие), и эти связи составляют часть пути между взаимосвязанными сущностями. Дефект устраняется введением недостающей ассоциации
Дата добавления: 2018-08-06; просмотров: 392; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!