Семантика SEQUEL и реляционная алгебра.

Центр дистанционного обучения Лекция 5 Семантика SEQUEL и реляционная алгебра online.mirea.ru Центр дистанционного обучения Вопросы занятия  Семантика SQL запроса.  Основы реляционной алгебры.  Реляционные операции в контексте реляционной алгебры и SQL.  Агрегатные операторы инструкций SQL.  Операторы инструкций SQL: Order by и Having.  Триггеры в языке SQL. online.mirea.ru Центр дистанционного обучения Основы SQL запроса SELECT [DISTINCT] target-list FROM relation-list WHERE qualification relation-list – список имен отношений. Допускает добавление к имени переменной для использования в запросе target-list – список атрибутов отношений, которые указаны в relation-list Qualification – условие сравнения (Отн-Значение или Отн1-Отн2, по условию <, >, =, ≤, ≥, комбинированные с AND, OR, NOT) online.mirea.ru Центр дистанционного обучения Принцип работы семантики SQL запроса 1. Рассчитать перечисленные отношения 2. Исключить из результата кортежи, которые не попадают в условия Qualification 3. Исключить атрибуты, отсутствующие в target-list 4. Если имеется группировка или иная сортировка данных, провести ее на последнем этапе. p.s. Это не обязательно наиболее эффективная стратегия работы запроса! Оптимизация. online.mirea.ru Центр дистанционного обучения Реляционное исчисление в языке SQL SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid = 103 online.mirea.ru Центр дистанционного обучения Традиционная реляционная алгебра Операция Приоритет RENAME 4 WHERE 3 PROJECT 3 TIMES 2 JOIN 2 INTERSECT 2 DIVIDE BY 2 UNION 1 MINUS 1 online.mirea.ru Центр дистанционного обучения Схематичное изображение операции RENAME RENAME = переименование (иногда - присвоение). Для операции нужен только один операнд. Переименовывается не само отношение (таблица), а ее атрибуты. R RENAME Atr1, Atr2… AS NewAtr1, NewAtr2… online.mirea.ru Центр дистанционного обучения Реализация RENAME в TSQL USE AdventureWorks2012; GO EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr'; GO USE AdventureWorks2012; GO EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID’, 'COLUMN'; GO online.mirea.ru Центр дистанционного обучения Схематичное изображение операции WHERE WHERE = выборка по условию. Для операции достаточно только одного операнда. В тоже время, могут участвовать несколько операндов. В результате строится новое отношение, в которое входят только те значения, которые при подстановке в условие where дают значение true. A WHERE c online.mirea.ru Центр дистанционного обучения Схематичное изображение операции PROJECT PROJECT = вертикальная выборка по выбранным атрибутам. Для операции достаточно только одного операнда. В результате строится новое отношение, в которое входят значения невычеркнутых в перечислении атрибутов. Результатом, например, может стать таблица с единственным выбранным атрибутом. online.mirea.ru Центр дистанционного обучения Схематичное изображение операции TIMES TIMES = умножение одной таблицы на другую. Для операции обязательно наличие двух операндов. Само перемножение называется конкатенацией. В результате строится новое отношение, в которое входят перемноженные друг на друга значения двух таблиц. A TIMES B. online.mirea.ru Центр дистанционного обучения Схематичное изображение операции JOIN JOIN = комбинация произведения двух таблиц и заранее определенного условия. Для операции обязательно наличие двух операндов. В результате строится новое отношение, в которое входят перемноженные друг на друга значения двух таблиц ограниченные выбранным условием. (A TIMES B) WHERE P. online.mirea.ru Центр дистанционного обучения Схематичное изображение операции INTERSECT INTERSECT = ищет в двух таблицах совпадающие значения. Для операции обязательно наличие двух операндов. В результате строится новое отношение, в которое входят кортежи, принадлежащие и первому и второму операнду. A INTERSECT B. online.mirea.ru Центр дистанционного обучения Схематичное изображение операции DIVIDE BY DIVIDE BY = Результатом операции деления является набор кортежей (строк) первого отношения, которые соответствуют комбинации всех кортежей второго отношения. Для этого нужно, чтобы во втором отношении была часть атрибутов (можно и один), которые есть в первом отношении. В результирующем отношении присутствуют только те атрибуты первого отношения, которых нет во втором. A DIVIDE BY B. online.mirea.ru Центр дистанционного обучения Пример деления -- Uses AdventureWorks SELECT s.BusinessEntityID AS SalesPersonID, FirstName, LastName, SalesQuota, SalesQuota/12 AS 'Sales Target Per Month' FROM Sales.SalesPerson AS s JOIN HumanResources.Employee AS e ON s.BusinessEntityID = e.BusinessEntityID JOIN Person.Person AS p ON e.BusinessEntityID =p.BusinessEntityID; online.mirea.ru Центр дистанционного обучения Схематичное изображение операции UNION UNION = оставит значения принадлежащие первой, второй таблице или обеим таблицам. Для операции обязательно наличие двух операндов. В результате строится новое отношение, в которое входят кортежи, первого и второго операндов без повторений. A UNION B. online.mirea.ru Центр дистанционного обучения Схематичное изображение операции MINUS MINUS = оставит значения принадлежащие первой таблицы и не принадлежащие второй таблице. Для операции обязательно наличие двух операндов. В результате строится новое отношение, в которое входят кортежи первого операнда при условии, что их нет во втором операнде. A MINUS B. online.mirea.ru Центр дистанционного обучения Реализация MINUS в TSQL -- Uses AdventureWorks SELECT ProductID FROM Production.Product EXCEPT SELECT ProductID FROM Production.WorkOrder ; online.mirea.ru Центр дистанционного обучения Следующий пример для демонстраций Sailors Reserves Boats online.mirea.ru Центр дистанционного обучения Выражения и строки SELECT S.age, age1=S.age-5, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE ‘B_%B’ 1. Арифметические выражения. Тут – формируются три столбца. Возраст и два других, по арифметическому выражению. 2. Связка в target-list может формироваться как с использованием присваивания, так и с помощью оператора AS. 3. Оператор LIKE используется для управления выборкой из строковых переменных. Тут: выбрать все Name, начинающиеся с B, заканчивающиеся на B и имеющие хотя бы три символа. online.mirea.ru Центр дистанционного обучения Реляционные операции в проекции SQL (запрос с комбинированным условием vs. UNION) Вывести имена морячков, которые взяли зеленую или красную лодку. SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’) online.mirea.ru Центр дистанционного обучения Реляционные операции в проекции SQL Что будет выведено в результат? SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ online.mirea.ru Центр дистанционного обучения Реляционные операции в проекции SQL (запрос с комбинированным условием vs. INTERSECT) Вывести id тех морячков, что брали и зеленую, и красную лодку. SELECT S.sid FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND (B1.color=‘red’ AND B2.color=‘green’ online.mirea.ru Центр дистанционного обучения Реляционные операции в проекции SQL Что будет выведено в результат? SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ online.mirea.ru Центр дистанционного обучения Агрегатные операторы SQL SELECT COUNT (*) FROM Sailors S SELECT AVG (S.age) FROM Sailors S WHERE S.rating=10 SELECT COUNT (DISTINCT S.rating) FROM Sailors S WHERE S.sname=‘Bob’ SELECT AVG ( DISTINCT S.age) FROM Sailors S WHERE S.rating=10 online.mirea.ru Центр дистанционного обучения Запросы с GROUP BY и HAVING SELECT [DISTINCT] target-list FROM relation-list WHERE qualification GROUP BY grouping-list HAVING group-qualification grouping-list – атрибуты, подлежащие группировке group-qualification – условие фильтра группировки, должно быть только одно. online.mirea.ru Центр дистанционного обучения Пример группировки с условием Найти возраст самого юного морячка, которому больше 18 лет для рейтинга, которым обладают как минимум 2 экземпляра морячков. SELECT S.rating, MIN (S.age) FROM Sailors S WHERE S.age >= 18 GROUP BY S.rating HAVING COUNT (*) > 1 online.mirea.ru Центр дистанционного обучения Еще группировка Для каждой красной лодки узнать, сколько раз ее брали напрокат. SELECT B.bid, COUNT (*) AS scount FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid online.mirea.ru Центр дистанционного обучения Триггеры Процедуры, которые запускаются автоматически, если определенное событие произойдет в СУБД. Состоят из трех частей: - Событие (запускает триггер), AFTER/INSTEAD OF - Условие (проверяет, когда должен быть запущен триггер), INSERT/UPDATE/DELETE - Действие (что произойдет, когда триггер сработает), SQL оператор online.mirea.ru Центр дистанционного обучения Пример триггера Всех юных моряков будем сохранять отдельно копией записи. CREATE TRIGGER youngSailorUpdate AFTER INSERT ON SAILORS REFERENCING NEW TABLE NewSailors FOR EACH STATEMENT INSERT INTO YoungSailors(sid, name, age, rating) SELECT sid, name, age, rating FROM NewSailors N WHERE N.age <= 18 online.mirea.ru Центр дистанционного обучения Чтение на дом • Русский Кренке, стр. 287-300, стр. 305-324. • Английский Кренке, 68-137. • MIT, стр. 100-124, стр. 130-173, стр. 478-506 online.mirea.ru Центр дистанционного обучения Вопросы для самостоятельного изучения • 1. Можно ли вложить одну выборку SELECT в другую, в одном запросе? Если да, то есть ограничение на количество вложений? • 2. Какие еще есть агрегатные функции у оператора SELECT приведите примеры кода с ними, на основании таблицы из лекции. • 3. Напишите код полезного триггера для БД из лекции. • 4. Запишите операции реляционной алгебры в строгом алгебраическом виде. online.mirea.ru Центр дистанционного обучения Спасибо за внимание! online.mirea.ru