Слайд 1Базы Данных
Операции реляционной алгебры. Язык SQL.
Слайд 2Операции реляционной алгебры
Данное определение – неформальное описание математического понятия.
Опр. Алгебра
(алгебраическая система) – это
множество объектов (основное множество) и
совокупность операций над
этими объектами, замкнутыми относительно основного множества.
Пример. Множество целых чисел Z с совокупностью двух операций {+, –} – алгебра.
1. Операции реляционной алгебры
Слайд 3Операции реляционной алгебры
Реляционная теория баз данных основана на понятиии реляционной
алгебры (алгебры отношений). Объектами в ней выступают отношения.
Э.Ф. Кодд
предложил 8 операций реляционной алгебры (реляционных операций). Каждая из них основана на отношениях как аргументах и результатом является всегда отношение.
R = f(R1, R2, ..., Rn)
Слайд 4Операции реляционной алгебры
Операции реляционной алгебры:
Теоретико-множественные:
1. Объединение
2. Пересечение
3. Разность
4. Расширенное декартово
произведение
Специальные:
5. Фильтрация (горизонтальный выбор)
6. Проекция (вертикальный выбор)
7. Условное соединение
8.
Деление.
Слайд 5Операции реляционной алгебры
Опр. Пусть R – некоторое отношение. Схемой отношения
R (SR)назвается перечень имен атрибутов с указанием доменов, к которым
они относятся.
SR = (A1, A2, ..., An), Ai ⊆ Di, i=1, 2, ..., n.
Пример.
Абитуриент (НомерА, Фамилия, ДатаРожд, БаллЕГЭ) – схема отношения Абитуриент. Атрибуты и их домены:
НомерА – Числовой
Фамилия – Текстовый
ДатаРожд – Даты
БаллЕГЭ - Числовой
Слайд 6Операции реляционной алгебры
Опр. Схемы двух отношений R1 и R2 называются
эквивалентными (SR1~SR2), если они имеют одинаковое количество атрибутов и возможно
такое их упорядочение, что на одинаковых местах в схеме будут находиться атрибуты, прнимающие значения из одного домена.
Пример.
R1 (Фамилия, Имя, ОценкаБД, ДатаРожд)
R2(ОценкаМатАн, ДатаР, ИмяСтудента, ФамилияСтудента)
Слайд 7Операции реляционной алгебры
Теоретико-множественные операции
Слайд 8Операции реляционной алгебры
1. Объединение
Опр. Объединением двух отношений с эквивалентыми схемами
называют отношение, которое состоит их кортежей, принадлежащих первому, либо второму
отношениям, либо обоим одновременно.
R1 U R2 = {r | r R1 v r R2}
где SR1 ~ SR2
Слайд 9Операции реляционной алгебры
2. Пересечение
Опр. Пересечением двух отношений с эквивалентыми схемами
называют отношение, которое состоит их кортежей, принадлежащих одновременно первому и
второму отношениям.
R1 ∩ R2 = {r | r R1 ^ r R2}
где SR1 ~ SR2
Слайд 10Операции реляционной алгебры
3. Разность
Опр. Разностью двух отношений с эквивалентыми схемами
называют отношение, которое состоит их кортежей, принадлежащих первому отношению и
не принадлежащих второму отношению.
R1 \ R2 = {r | r R1 ^ r ∉ R2}
где SR1 ~ SR2
Слайд 12Операции реляционной алгебры
4. Раширенное декартово произведение.
Опр. Расширенным декартовым произведением отношения
R1 и отношения R2 называется отношение, кортежи которого получены сцеплением
каждого кортежа отношения R1 с каждым кортежем отношения R2.
R1(A1, A2, ... , An)
R2(B1, B2, ... , Bm)
R1⊗R2 = {(p, q) | p R1 ^ q R2}
Слайд 13Операции реляционной алгебры
Пример.
Слайд 14Операции реляционной алгебры
Специальные операции
Слайд 15Операции реляционной алгебры
5. Фильтрация (горизонтальный выбор, выборка)
Опр. Фильтрацией отношения R
по условию α(r) называется отношение, содержащее только те кортежы из
R, для которых истинно α(r).
R[α(r)] = {r | r R ^ α(r)}
Условие α(r) может содержать термы сравнения (=, <>, <=, >=, <, >), константы, логические связки, скобки.
Слайд 16Операции реляционной алгебры
Пример.
Слайд 17Операции реляционной алгебры
6. Проекция (вертикальный выбор)
Опр. Проекцией отношения R(A1, A2,
... , An) по подмножеству его атрибутов B = (Ai1,
Ai2, ... , Aik) ⊆ (A1, A2, ... , An) называется отношение со схемой, соответствующей набору атрибутов В, содержащее кортежи, получаемые их кортежей исходного отношения R путем удаления из них значений, не принадлежащих атрибутам из В .
SR=(A1, A2, ... , An),
B = (Ai1, Ai2, ... , Aik) ⊆ SR
R[B] = {r | r = ^ rit Ait ^
(∃pR: p= ∀j1..k ∃z: rij = rz )}
Слайд 18Операции реляционной алгебры
Пример
Слайд 19Операции реляционной алгебры
7. Условное соединение
Опр. Условным соединением отношения R1(A1, A2,
... , An) и отношения R2(B1, B2, ... , Bm),
некоторые атрибуты которых сравнимы, по условию α(r) называется подмножество декартова произведения R1 ⊗R2, кортежи которого удовлетворяют условию α(r).
R1(A1, A2, ... , An), R2(B1, B2, ... , Bm), и пусть
B=(Ai1, Ai2, ... , Aik), B ⊆ SR1 ^ B ⊆ SR2, α(r) - предикат на B. Тогда
R1 [α(r)] R2 = { | r1 R1 ^ r2 R2 ^ α(r1 ∩ r2)}
Слайд 20Операции реляционной алгебры
Пример.
Слайд 21Операции реляционной алгебры
Поясняющие диаграммы (1-2):
1. Объединение
2. Пересечение
Слайд 22Операции реляционной алгебры
Поясняющие диаграммы (3-4):
3. Разность
4. Декартово
произведение
А
Б
В
Г
Д
⊗
=
АГ
АД
БГ
БД
ВГ
ВД
Слайд 23Операции реляционной алгебры
Поясняющие диаграммы (5-6):
5. Фильтрация
6. Проекция
Слайд 24Операции реляционной алгебры
Поясняющие диаграммы (7-8):
7. Условное
соединение
А
Б
В
=
Г
Г
Д
Г
Д
Е
Ж
З
И
А
Б
В
Г
Г
Д
Ж
Ж
З
8. Деление
А
А
А
Б
В
В
Г
Д
Е
Д
Е
Д
Г
Д
=
А
:
Слайд 25Язык SQL
2. Язык SQL
Structured Query Language – Язык Структурированных Запросов
- универсальный компьютерный язык, применяемый для создания, модификации и управления
данными в реляционных базах данных.
В СУБД System R компании IBM Research (к. 1970 гг.)
Первый стандарт – 1989 год (SQL1)
Второй стандарт – 1992 год (SQL2)
Третий стандарт – 1999 год (SQL3) – объектная модель, обработка событий, триггеров, сложные типы данных, переносимость
Четвертый – SQL2003 (с небольшими последующим модификациями) – XML данные, оконные функции.
Операторы SQL могут встраиваться в программы традиционных языков программирования (C++, Pearl, Delphi, Java)
Слайд 26Операции реляционной алгебры
Разделы SQL
DDL
Data Definition Language
DML
Data Manipulaition Language
DQL
Data Query Language
Другие
средства
Транзакции, админ., прогр.
Слайд 27Язык SQL
Data Definition Language
- язык описания (определения) данных
Слайд 28Язык SQL
Таблица (TABLE) – основное информационное хранилище базы данных в
реляционной модели;
Представление (VIEW) - виртуальная (логическая) таблица, представляющая собой поименованный
запрос В отличие от обычных таблиц реляционной БД, представление не является самостоятельной частью набора данных, хранящегося в базе. Содержимое представления динамически вычисляется на основании данных, находящихся в реальных таблицах;
Индекс (INDEX) - объект базы данных, создаваемый с целью повышения производительности поиска данных.
Слайд 29Язык SQL
Data Manipulation Language
- язык манипулирования данными
Слайд 30Язык SQL
Data Query Language
- язык запросов
Слайд 31Язык SQL
3. Оператор SELECT
Этот оператор реализует все операции реляционной алгебры.
Синтаксис SELECT:
Слайд 32Язык SQL
Пояснения:
ALL – в результат включаются все строки запроса (в
том числе одинаковые; по умолчанию); DISTINCT – в результат включаются
только различные строки.
* означает выбор всех полей таблиц; иначе – только тех, которые указаны в разделе SELECT.
В разделе FROM задается перечень исходных таблиц, над которыми выполняется декартово произведение.
В разделе WHERE задаются условия запроса: либо условие выборки (предикат фильтрации), либо условного соединения.
Слайд 33Язык SQL
Пояснения:
В разделе GROUP BY задается список полей, по значениям
которых выполняется группировка записей.
В разделе HAVING задаются условия, накладываемые на
каждую группу записей.
В разделе ORDER BY перечисляются поля, определяющие порядок сортировки записей в результате.
Имена полей – составные:
ИмяТаблицы.ИмяПоля
В разделе SELECT можно задавать псевдонимы полей (AS); также AS можно использовать в разделе FROM для задания псевдонимов таблиц.
Слайд 34Язык SQL
Условия (предикаты) в разделе WHERE:
Сравнения =, ,
>, >=
Between A and B – значение между A и
B
Not Between A and B – значение не между A и B
In (множество) – принадлежность множеству
Not In (множество) – непринадлежность множеству
Like “шаблон” – сравнение с шаблоном (образцом):
_ - любой одиночный символ
% - любая последовательность символов
другие символы обозначают сами себя
Is Null – сравнение с неопределенным значением
Not Is Null – отрицание неопределенного значения
Слайд 35Язык SQL
Пример. Создание запросов к базе данных на SQL
Схема данных:
Слайд 44Язык SQL
Вопрос: как изменится результат запроса, если
убрать DISTINCT?
Слайд 47Язык SQL
4. Группировка и агрегатные функции
Группировка – разбиение всего множества
записей таблицы на группы, в которые собираются записи, имеющие одинаковые
значения полей группировки.
Если группировать записи по полю «Пол», то они будут разбиты на две группы записей («М» и «Ж»).
Если группировать записи по полю «Группа», то они будут разбиты на три группы записей («39-21», «ИТ-11» и «ИТ-21»).
Слайд 48Язык SQL
Агрегатная функция – вычисляет обобщенное групповое значение для всех
записей каждой группы.
Слайд 49Язык SQL
Агрегатные функции используются подобно именам полей в SELECT
Аргументами агрегатных
функций являются имена полей (либо * для COUNT)
При использовании
группировки в разделе SELECT можно использовать только имена полей группировки либо агрегатные функции.
Можно применять агрегатные функции и без группировки, тогда вся таблица рассматривается как одна группа.
Слайд 58Язык SQL
5. Внутреннее и внешнее соединение таблиц
Соединение (JOIN) – формирование
представления таблиц сцеплением их записей.
Ранее были введены операции расширенного декартова
произведения (каждый кортеж с каждым) и условного соединения (только те кортежи, для которых истинен предикат соединения).
JOIN – специальная операция SQL, позволяющая указать способ соединения двух или более таблиц в разделе FROM.
Слайд 59Язык SQL
Виды соединений
JOIN
соединение
INNER JOIN
внутреннее соединение
OUTER JOIN
внешнее соединение
LEFT JOIN
левое внешнее
соединение
RIGHT JOIN
правое внешнее
соединение
FULL JOIN
полное внешнее
соединение
Слайд 60Язык SQL
Синтаксис оператора соединения:
Таблица1 JOIN Таблица2 ON УсловиеСоединения
Слайд 61Язык SQL
1. Внутреннее соединение INNER JOIN
Соединяются только те записи из
T1, для которых существуют записи из T2 и наоборот.
SELECT *
FROM
T1 INNER JOIN T2 ON T1.B1=T2.B2;
Слайд 62Язык SQL
2. Левое внешнее соединение LEFT JOIN
Соединяются все записи из
T1 с соответствующими записями из T2. Отсутствующие записи заменяются на
NULL.
SELECT *
FROM T1 LEFT JOIN T2 ON T1.B1=T2.B2;
Слайд 63Язык SQL
3. Правое внешнее соединение RIGHT JOIN
Соединяются все записи из
T2 с соответствующими записями из T1. Отсутствующие записи заменяются на
NULL.
SELECT *
FROM T1 RIGHT JOIN T2 ON T1.B1=T2.B2;
Слайд 64Язык SQL
4. Полное внешнее соединение FULL JOIN
Соединяются все записи из
T1 и все записи из T2. Отсутствующие записи заменяются на
NULL.
SELECT *
FROM T1 FULL JOIN T2 ON T1.B1=T2.B2;
Слайд 65Язык SQL
Пример.
Требуется написать запрос: «Количество детей у каждого сотрудника»
Слайд 66Язык SQL
Версия 1: без соединений.
SELECT MAX(Сотрудники.Фамилия) AS Фам, COUNT (*)
AS Детей
FROM Сотрудники, Дети
WHERE Сотрудники.КодС=Дети.КодС
GROUP BY Сотрудники.КодС;
Слайд 67Язык SQL
Версия 2: с помощью внутреннего соединения.
SELECT MAX(Сотрудники.Фамилия) AS Фам,
COUNT (*) AS Детей
FROM Сотрудники INNER JOIN Дети ON Сотрудники.КодС=Дети.КодС
GROUP
BY Сотрудники.КодС;
Результат тот же. Но где же Сидоров?
Слайд 68Язык SQL
Версия 2: с помощью левого внешнего соединения.
SELECT MAX(Сотрудники.Фамилия) AS
Фам, COUNT (*) AS Детей
FROM Сотрудники LEFT JOIN Дети ON
Сотрудники.КодС=Дети.КодС
GROUP BY Сотрудники.КодС;
Но у Сидорова нет детей! Почему 1?
Слайд 69Язык SQL
Версия 2: с помощью левого внешнего соединения.
SELECT MAX(Сотрудники.Фамилия) AS
Фам,
COUNT (Дети.Имя) AS Детей
FROM Сотрудники LEFT JOIN Дети
ON Сотрудники.КодС=Дети.КодС
GROUP BY Сотрудники.КодС;
Вот теперь правильно. Отличие Count(*) от Count(Поле) состоит в том, что
вторая не учитывает нулевые значения.
Слайд 70Язык SQL
В SQL еще масса различных возможностей и средств.
Рекомендуется изучать
литературу и интернет.
http://www.sql-ex.ru/
http://citforum.ru
http://www.sql.ru/
и др.
