Базы Данных Операции реляционной алгебры Язык SQL 1

Базы Данных Операции реляционной алгебры. Язык SQL. 1

Операции реляционной алгебры 1. Операции реляционной алгебры n n 1. 2. n Данное определение – неформальное описание математического понятия. Опр. Алгебра (алгебраическая система) – это множество объектов (основное множество) и совокупность операций над этими объектами, замкнутыми относительно основного множества. Пример. Множество целых чисел Z с совокупностью двух операций {+, –} – алгебра. 2

Операции реляционной алгебры n n n Реляционная теория баз данных основана на понятиии реляционной алгебры (алгебры отношений). Объектами в ней выступают отношения. Э. Ф. Кодд предложил 8 операций реляционной алгебры (реляционных операций). Каждая из них основана на отношениях как аргументах и результатом является всегда отношение. R = f(R 1, R 2, . . . , Rn) 3

Операции реляционной алгебры n n n Операции реляционной алгебры: Теоретико-множественные: 1. Объединение 2. Пересечение 3. Разность 4. Расширенное декартово произведение Специальные: 5. Фильтрация (горизонтальный выбор) 6. Проекция (вертикальный выбор) 7. Условное соединение 8. Деление. 4

Операции реляционной алгебры n n n n Опр. Пусть R – некоторое отношение. Схемой отношения R (SR)назвается перечень имен атрибутов с указанием доменов, к которым они относятся. SR = (A 1, A 2, . . . , An), Ai ⊆ Di, i=1, 2, . . . , n. Пример. Абитуриент (Номер. А, Фамилия, Дата. Рожд, Балл. ЕГЭ) – схема отношения Абитуриент. Атрибуты и их домены: Номер. А – Числовой Фамилия – Текстовый Дата. Рожд – Даты Балл. ЕГЭ - Числовой 5

Операции реляционной алгебры n n Опр. Схемы двух отношений R 1 и R 2 называются эквивалентными (SR 1~SR 2), если они имеют одинаковое количество атрибутов и возможно такое их упорядочение, что на одинаковых местах в схеме будут находиться атрибуты, прнимающие значения из одного домена. Пример. R 1 (Фамилия, Имя, Оценка. БД, Дата. Рожд) R 2(Оценка. Мат. Ан, Дата. Р, Имя. Студента, Фамилия. Студента) 6

Операции реляционной алгебры Теоретикомножественные операции 7

Операции реляционной алгебры n n 1. Объединение Опр. Объединением двух отношений с эквивалентыми схемами называют отношение, которое состоит их кортежей, принадлежащих первому, либо второму отношениям, либо обоим одновременно. R 1 U R 2 = {r | r R 1 v r R 2} где SR 1 ~ SR 2 8

Операции реляционной алгебры n n 2. Пересечение Опр. Пересечением двух отношений с эквивалентыми схемами называют отношение, которое состоит их кортежей, принадлежащих одновременно первому и второму отношениям. R 1 ∩ R 2 = {r | r R 1 ^ r R 2} где SR 1 ~ SR 2 9

Операции реляционной алгебры n n 3. Разность Опр. Разностью двух отношений с эквивалентыми схемами называют отношение, которое состоит их кортежей, принадлежащих первому отношению и не принадлежащих второму отношению. R 1 R 2 = {r | r R 1 ^ r ∉ R 2} где SR 1 ~ SR 2 10

Операции реляционной алгебры О (отличники) М (мужчины) NСБ Фамилия 01 Иванов 02 Петров 04 Кузнецова 03 Сидоров МUО М∩О NСБ Фамилия 02 Петров NСБ Фамилия 01 Иванов 02 Петров NСБ Фамилия 03 Сидоров 01 Иванов 04 Кузнецова 03 Сидоров МО 11

Операции реляционной алгебры n n n 4. Раширенное декартово произведение. Опр. Расширенным декартовым произведением отношения R 1 и отношения R 2 называется отношение, кортежи которого получены сцеплением каждого кортежа отношения R 1 с каждым кортежем отношения R 2. R 1(A 1, A 2, . . . , An) R 2(B 1, B 2, . . . , Bm) R 1⊗R 2 = {(p, q) | p R 1 ^ q R 2} 12

Пример. n Операции реляционной. Sалгебры ⊗G S (студенты) NСБ Фамилия Индекс Специальность 01 Иванов ИТ-21 Информационные технологии NСБ Фамилия 01 Иванов 02 Петров 01 Иванов ПИ-21 Прикладная информатика 03 Сидоров 02 Петров ИТ-21 Информационные технологии 02 Петров ПИ-21 Прикладная информатика 03 Сидоров ИТ-21 Информационные технологии 03 Сидоров ПИ-21 Прикладная информатика G (группы) Индекс Специальность ИТ-21 Информационные технологии ПИ-21 Прикладная информатика 13

Операции реляционной алгебры Специальные операции 14

Операции реляционной алгебры n n 5. Фильтрация (горизонтальный выбор, выборка) Опр. Фильтрацией отношения R по условию α(r) называется отношение, содержащее только те кортежы из R, для которых истинно α(r). R[α(r)] = {r | r R ^ α(r)} Условие α(r) может содержать термы сравнения (=, <>, <=, >=, <, >), константы, логические связки, скобки. 15

Операции реляционной алгебры n Пример. S S [Пол = “Ж”] Фамилия Пол Иванов М Петрова Ж Кузнецова Ж Сидоров М Кузнецова Ж 16

Операции реляционной алгебры n n 6. Проекция (вертикальный выбор) Опр. Проекцией отношения R(A 1, A 2, . . . , An) по подмножеству его атрибутов B = (Ai 1, Ai 2, . . . , Aik) ⊆ (A 1, A 2, . . . , An) называется отношение со схемой, соответствующей набору атрибутов В, содержащее кортежи, получаемые их кортежей исходного отношения R путем удаления из них значений, не принадлежащих атрибутам из В. SR=(A 1, A 2, . . . , An), B = (Ai 1, Ai 2, . . . , Aik) ⊆ SR R[B] = {r | r =<ri 1, ri 2, . . . , rik> ^ rit Ait ^ (∃p R: p=<r 1, r 2, . . . , rn> ∀j 1. . k ∃z: rij = rz )} n 17

Операции реляционной алгебры n Пример Book Инв. N Автор Название Из-во Год. Вып 01234 Попов В. Н. Язык Паскаль Фи. С 1994 23567 Вирт Н. Алгоритмы и структуры данных Мир 1987 34562 Вирт Н. Алгоритмы и структуры данных Мир 2003 91873 Карпова Т. Базы данных Питер 2002 Book [Автор, Название] Автор Назв Попов В. Н. Язык Паскаль Вирт Н. Алгоритмы и структуры данных Карпова Т. Базы данных 18

Операции реляционной алгебры n n n 7. Условное соединение Опр. Условным соединением отношения R 1(A 1, A 2, . . . , An) и отношения R 2(B 1, B 2, . . . , Bm), некоторые атрибуты которых сравнимы, по условию α(r) называется подмножество декартова произведения R 1 ⊗R 2, кортежи которого удовлетворяют условию α(r). R 1(A 1, A 2, . . . , An), R 2(B 1, B 2, . . . , Bm), и пусть B=(Ai 1, Ai 2, . . . , Aik), B ⊆ SR 1 ^ B ⊆ SR 2, α(r) - предикат на B. Тогда n R 1 [α(r)] R 2 = {<r 1, r 2> | r 1 R 1 ^ r 2 R 2 ^ α(r 1 ∩ r 2)} 19

Операции реляционной алгебры 1. Пример. S NСБ Фамилия S [S. Группа=P. Группа] P Группа NСБ Фамилия Группа Предмет 01 Иванов 39 -21 Базы Данных 02 Петров 39 -21 01 Иванов 39 -21 МИР 03 Сидоров ИТ-21 02 Петров 39 -21 Базы Данных 04 Кузнецов Ит-21 02 Петров 39 -21 МИР 03 Сидоров ИТ-21 Алгебра 04 Кузнецов Ит-21 Алгебра P Группа Предмет 39 -21 Базы Данных 39 -21 МИР ИТ-11 Алгебра 20

Операции реляционной алгебры 1. Поясняющие диаграммы (1 -2): 1. Объединение 2. Пересечение 21

Операции реляционной алгебры 1. Поясняющие диаграммы (3 -4): 3. Разность 4. Декартово произведение А Б В ⊗ Г Д = АГ АД БГ БД ВГ ВД 22

Операции реляционной алгебры 1. Поясняющие диаграммы (5 -6): 5. Фильтрация 6. Проекция 23

Операции реляционной алгебры 1. Поясняющие диаграммы (7 -8): 7. Условное соединение 8. Деление А Г Б Г В Д А А А Б В В Г Д Е Д Г Ж Д З Е И : Г Д = = А Г Ж Б Г Ж В Д З А 24

Язык SQL 2. Язык SQL • Structured Query Language – Язык Структурированных Запросов - универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. • В СУБД System R компании IBM Research (к. 1970 гг. ) • Первый стандарт – 1989 год (SQL 1) • Второй стандарт – 1992 год (SQL 2) • Третий стандарт – 1999 год (SQL 3) – объектная модель, обработка событий, триггеров, сложные типы данных, переносимость • Четвертый – SQL 2003 (с небольшими последующим модификациями) – XML данные, оконные функции. • Операторы SQL могут встраиваться в программы традиционных языков программирования (C++, Pearl, Delphi, Java) 25

Операции реляционной алгебры DDL Data Definition Language DML Data Manipulaition Language Разделы SQL Data Query Language Другие средства Транзакции, админ. , прогр. 26

Язык SQL • Data Definition Language • - язык описания (определения) данных Оператор Действие CREATE TABLE Создать таблицу DROP TABLE Удалить таблицу ALTER TABLE Изменить таблицу CREATE VIEW Создать представление DROP VIEW Удалить представление ALTER VIEW Изменить представление CREATE INDEX Создать индекс DROP INDEX Удалить индекс 27

Язык SQL • Таблица (TABLE) – основное информационное хранилище базы данных в реляционной модели; • Представление (VIEW) - виртуальная (логическая) таблица, представляющая собой поименованный запрос В отличие от обычных таблиц реляционной БД, представление не является самостоятельной частью набора данных, хранящегося в базе. Содержимое представления динамически вычисляется на основании данных, находящихся в реальных таблицах; • Индекс (INDEX) - объект базы данных, создаваемый с целью повышения производительности поиска данных. 28

Язык SQL • Data Manipulation Language • - язык манипулирования данными Оператор Действие DELETE Удаление записей из таблицы в соответствии с условиями запроса Вставляет записи в таблицу из другой таблицы или запроса INSERT UPDATE Обновляет значения одного или нескольких полей в записях, соответствующих условиям запроса 29

Язык SQL • Data Query Language • - язык запросов Оператор SELECT Действие Формирует новое отношение (результат запроса) в соответствии с условиями запроса 30

Язык SQL • 3. Оператор SELECT • Этот оператор реализует все операции реляционной алгебры. Синтаксис SELECT: SELECT (ALL | DISTINCT)(<список_полей>|*) проекция FROM <список_таблиц> декартово произведение WHERE <условие_выборки/соединения> фильтрация/ условное соединение GROUP BY <поля_группировки> группировка HAVING фильтрация групп <условие_на_группу> ORDER BY <поля_сортировки> сортировка 31

Язык SQL • Пояснения: 1. ALL – в результат включаются все строки запроса (в том числе одинаковые; по умолчанию); DISTINCT – в результат включаются только различные строки. * означает выбор всех полей таблиц; иначе – только тех, которые указаны в разделе SELECT. В разделе FROM задается перечень исходных таблиц, над которыми выполняется декартово произведение. В разделе WHERE задаются условия запроса: либо условие выборки (предикат фильтрации), либо условного соединения. 2. 3. 4. 32

Язык SQL • Пояснения: 5. В разделе GROUP BY задается список полей, по значениям которых выполняется группировка записей. В разделе HAVING задаются условия, накладываемые на каждую группу записей. В разделе ORDER BY перечисляются поля, определяющие порядок сортировки записей в результате. Имена полей – составные: Имя. Таблицы. Имя. Поля В разделе SELECT можно задавать псевдонимы полей (AS); также AS можно использовать в разделе FROM для задания псевдонимов таблиц. 6. 7. 8. 9. 33

Язык SQL • Условия (предикаты) в разделе WHERE: • • • Сравнения =, <>, <, <=, >, >= Between A and B – значение между A и B Not Between A and B – значение не между A и B In (множество) – принадлежность множеству Not In (множество) – непринадлежность множеству Like “шаблон” – сравнение с шаблоном (образцом): • • • _ - любой одиночный символ % - любая последовательность символов другие символы обозначают сами себя Is Null – сравнение с неопределенным значением Not Is Null – отрицание неопределенного значения 34

Язык SQL • • Пример. Создание запросов к базе данных на SQL Схема данных: 35

Язык SQL • Таблицы: 36

Язык SQL 01. Список всех SELECT * студентов (копия FROM Студенты; таблицы Студенты) 37

Язык SQL 02. Фамилии студентов SELECT Студенты. Фамилия FROM Студенты; 38

Язык SQL 03. Список студентов в алфавитном порядке SELECT Студенты. Фамилия FROM Студенты ORDER BY Студенты. Фамилия 39

Язык SQL 04. Список студентов и групп SELECT Студенты. Фамилия, Группы. Индекс. Группы FROM Группы, Студенты WHERE Группы. Код. Группы=Студенты. Код. Группы; 40

Язык SQL 04*. Список студентов и групп (без соединения!) SELECT Студенты. Фамилия, Группы. Индекс. Группы FROM Группы, Студенты; 41

Язык SQL 05. Список групп, где есть студент Иванов SELECT DISTINCT Группы. Индекс. Группы FROM Группы, Студенты WHERE Группы. Код. Группы=Студенты. Код. Группы AND Студенты. Фамилия="Иванов"; 42

Язык SQL 06. Список групп специальности «Прикладная информатика» SELECT Группы. Индекс. Группы, Группы. Специальность FROM Группы WHERE Группы. Специальность= "Прикладная информатика"; 43

Язык SQL 07. Список предметов, по которым есть четверки SELECT DISTINCT Предметы. Название. Предмета FROM Предметы, Сдача. Предметов WHERE Предметы. Код. Предмета = Сдача. Предметов. Код. Предмета AND Сдача. Предметов. Оценка=4; Вопрос: как изменится результат запроса, если убрать DISTINCT? 44

Язык SQL 08. Список студентов, получивших 4 по предмету «Базы Данных» SELECT Студенты. Фамилия FROM Предметы, Студенты, Сдача. Предметов WHERE Предметы. Код. Предмета = Сдача. Предметов. Код. Предмета AND Студенты. Код. Студента = Сдача. Предметов. Код. Студента AND Предметы. Название. Предмета="Базы Данных" AND Сдача. Предметов. Оценка=4; 45

Язык SQL 09. Список студентов, имеющих несколько четверок (больше одной) SELECT DISTINCT Студенты. Фамилия FROM Студенты, Сдача. Предметов AS A, Сдача. Предметов AS B WHERE Студенты. Код. Студента = A. Код. Студента AND Студенты. Код. Студента = B. Код. Студента AND A. Код. Предмета<>B. Код. Предмета AND A. Оценка=4 AND B. Оценка=4; 46

Язык SQL • 4. Группировка и агрегатные функции • Группировка – разбиение всего множества записей таблицы на группы, в которые собираются записи, имеющие одинаковые значения полей группировки. S Фамилия Пол Группа Иванов М 39 -21 Петрова Ж 39 -21 Сидоров М ИТ-11 Кузнецова Ж ИТ-21 Логов М ИТ-11 Симанова Ж ИТ-21 Если группировать записи по полю «Пол» , то они будут разбиты на две группы записей ( «М» и «Ж» ). Если группировать записи по полю «Группа» , то они будут разбиты на три группы записей ( « 39 -21» , «ИТ 11» и «ИТ-21» ). 47

Язык SQL • Агрегатная функция – вычисляет обобщенное групповое значение для всех записей каждой группы. ФУНКЦИЯ COUNT() РЕЗУЛЬТАТ Количество записей группы / непустых значений поля SUM() AVG() Сумма значений данного поля MIN() MAX() Наименьшее из значений данного поля Среднее арифметическое значений данного поля Наибольшее из значений данного поля 48

Язык SQL • Агрегатные функции используются подобно именам полей в SELECT • Аргументами агрегатных функций являются имена полей (либо * для COUNT) • При использовании группировки в разделе SELECT можно использовать только имена полей группировки либо агрегатные функции. • Можно применять агрегатные функции и без группировки, тогда вся таблица рассматривается как одна группа. 49

Язык SQL 10. Общее число студентов SELECT Count (*) FROM Студенты; 50

Язык SQL 10*. Общее число студентов (с заголовком) SELECT Count (*) AS Количество FROM Студенты; 51

Язык SQL 11. Количество студентов группы 39 -11 SELECT Count (*) AS Количество FROM Студенты, Группы WHERE Студенты. Код. Группы= Группы. Код. Группы AND Группы. Индекс. Группы="39 -11"; 52

Язык SQL 12. Количество студентов каждой группы SELECT Группы. Индекс. Группы, Count (*) AS Количество FROM Студенты, Группы WHERE Студенты. Код. Группы= Группы. Код. Группы GROUP BY Группы. Индекс. Группы; 53

Язык SQL 13. Количество студентов, сдававших экзамены по каждому предмету SELECT Предметы. Название. Предмета, Count(*) AS Колич. Сдававших FROM Предметы, Сдача. Предметов WHERE Предметы. Код. Предмета= Сдача. Предметов. Код. Предмета GROUP BY Предметы. Название. Предмета; 54

Язык SQL 14. Количество студентов, сдавших экзамены по каждому предмету (получивших оценку ≥ 3). SELECT Предметы. Название. Предмета, Count(*) AS Колич. Сдавших FROM Предметы, Сдача. Предметов WHERE Предметы. Код. Предмета= Сдача. Предметов. Код. Предмета AND Сдача. Предметов. Оценка>=3 GROUP BY Предметы. Название. Предмета; 55

Операции реляционной алгебры 15. Средняя оценка по каждому предмету SELECT Предметы. Название. Предмета, AVG(Сдача. Предметов. Оценка) AS Средняя. Оценка FROM Предметы, Сдача. Предметов WHERE Предметы. Код. Предмета = Сдача. Предметов. Код. Предмета GROUP BY Предметы. Название. Предмета; 56

Операции реляционной алгебры 16. Средняя оценка каждого студента SELECT MAX(Студенты. Фамилия) AS Фамилия, AVG(Сдача. Предметов. Оценка) AS Средняя. Оценка FROM Студенты, Сдача. Предметов WHERE Студенты. Код. Студента = Сдача. Предметов. Код. Студента GROUP BY Студенты. Код. Студента; 57

Язык SQL • 5. Внутреннее и внешнее соединение таблиц • Соединение (JOIN) – формирование представления таблиц сцеплением их записей. • Ранее были введены операции расширенного декартова произведения (каждый кортеж с каждым) и условного соединения (только те кортежи, для которых истинен предикат соединения). • JOIN – специальная операция SQL, позволяющая указать способ соединения двух или более таблиц в разделе FROM. 58

Язык SQL Виды соединений JOIN соединение INNER JOIN OUTER JOIN внутреннее соединение внешнее соединение LEFT JOIN RIGHT JOIN FULL JOIN левое внешнее соединение правое внешнее соединение полное внешнее соединение 59

Язык SQL Синтаксис оператора соединения: Таблица 1 JOIN Таблица 2 ON Условие. Соединения 60

Язык SQL • 1. Внутреннее соединение INNER JOIN • Соединяются только те записи из T 1, для которых существуют записи из T 2 и наоборот. T 1 A 1 B 1 1 10 2 20 3 30 T 2 A 2 B 2 1 20 2 30 3 40 SELECT * FROM T 1 INNER JOIN T 2 ON T 1. B 1=T 2. B 2; Q 1 A 1 B 1 A 2 B 2 2 20 1 20 3 30 2 30 61

Язык SQL • 2. Левое внешнее соединение LEFT JOIN • Соединяются все записи из T 1 с соответствующими записями из T 2. Отсутствующие записи заменяются на NULL. SELECT * FROM T 1 LEFT JOIN T 2 ON T 1. B 1=T 2. B 2; T 1 A 1 B 1 1 10 2 20 3 30 T 2 A 2 B 2 1 20 2 30 3 40 Q 2 A 1 B 1 A 2 B 2 1 10 2 20 1 20 3 30 2 30 62

Язык SQL • 3. Правое внешнее соединение RIGHT JOIN • Соединяются все записи из T 2 с соответствующими записями из T 1. Отсутствующие записи заменяются на NULL. SELECT * FROM T 1 RIGHT JOIN T 2 ON T 1. B 1=T 2. B 2; T 1 A 1 B 1 1 10 2 20 3 30 T 2 A 2 B 2 1 20 2 30 3 40 Q 3 A 1 B 1 A 2 B 2 2 20 1 20 3 30 2 30 3 40 63

Язык SQL • 4. Полное внешнее соединение FULL JOIN • Соединяются все записи из T 1 и все записи из T 2. Отсутствующие записи заменяются на NULL. SELECT * FROM T 1 FULL JOIN T 2 ON T 1. B 1=T 2. B 2; T 1 A 1 B 1 1 10 2 20 3 30 T 2 A 2 B 2 1 20 2 30 3 40 Q 4 A 1 B 1 A 2 B 2 1 10 2 20 1 20 3 30 2 3 30 40 64

Язык SQL • Пример. Сотрудники Код. С Фамилия 1 Иванов 2 Петров 3 Сидоров Код. Р 1 2 Дети Имя Вася Коля Код. С 1 2 3 4 5 Маша Зина Артем 1 2 2 Требуется написать запрос: «Количество детей у каждого сотрудника» 65

Язык SQL Версия 1: без соединений. SELECT MAX(Сотрудники. Фамилия) AS Фам, COUNT (*) AS Детей FROM Сотрудники, Дети WHERE Сотрудники. Код. С=Дети. Код. С GROUP BY Сотрудники. Код. С; Q 1 Фам Иванов Петров Детей 2 3 66

Язык SQL Версия 2: с помощью внутреннего соединения. SELECT MAX(Сотрудники. Фамилия) AS Фам, COUNT (*) AS Детей FROM Сотрудники INNER JOIN Дети ON Сотрудники. Код. С=Дети. Код. С GROUP BY Сотрудники. Код. С; Q 2 Фам Иванов Петров Детей 2 3 Результат тот же. Но где же Сидоров? 67

Язык SQL Версия 2: с помощью левого внешнего соединения. SELECT MAX(Сотрудники. Фамилия) AS Фам, COUNT (*) AS Детей FROM Сотрудники LEFT JOIN Дети ON Сотрудники. Код. С=Дети. Код. С GROUP BY Сотрудники. Код. С; Q 3 Фам Иванов Петров Детей 2 3 Сидоров 1 Но у Сидорова нет детей! Почему 1? 68

Язык SQL Версия 2: с помощью левого внешнего соединения. SELECT MAX(Сотрудники. Фамилия) AS Фам, COUNT (Дети. Имя) AS Детей FROM Сотрудники LEFT JOIN Дети ON Сотрудники. Код. С=Дети. Код. С GROUP BY Сотрудники. Код. С; Q 4 Фам Иванов Петров Детей 2 3 Сидоров 0 Вот теперь правильно. Отличие Count(*) от Count(Поле) состоит в том, что вторая не учитывает нулевые значения. 69

Язык SQL В SQL еще масса различных возможностей и средств. Рекомендуется изучать литературу и интернет. http: //www. sql-ex. ru/ http: //citforum. ru http: //www. sql. ru/ и др. 70