Тема Организация запросов к базам данных Выборка данных

Тема: «Организация запросов к базам данных. Выборка данных из нескольких таблиц » Составил зав. кафедрой «Информационные и вычислительные системы» дтн, профессор А. Д. Хомоненко ПГУПС 2011 1

Вопросы 1. 2. 3. 4. 5. 6. Реляционная алгебра Декартово произведение Соединения Внешние соединения Специальные операторы соединения Вложенные подзапросы 2

Литература 1. Хомоненко А. Д. , Цыганков В. М. , Мальцев М. Г. Базы данных. Учебник. 4 -е издание. – СПб. : КОРОНА принт, 2004. – 736 с. 2. Кириллов В. В. , Громов Г. Ю. Структуризированный язык запросов (SQL). ЛИТМО. 3. Дейт К. Дж. Введение в системы баз данных. : Пер. с англ. – 6 -е изд. – К. : Диалектика, 1998. – 784 с. 4. Кузнецов С. Д. Основы баз данных: учебное пособие. – М. : Интернет-Университет Информационных технологий; БИНОМ. 3 Лаборатория знаний, 2007. – 484 с.

Реляционная алгебра. Основные операции Вариант реляционной алгебры Кодда включает основные операции: • объединение, • разность (вычитание), • пересечение, • декартово (прямое) произведение, • выборка (селекция, ограничение), • проекция, • деление, • соединение. 4

Реляционная алгебра. Основные операции 5

Реляционная алгебра. Основные операции 6

Декартово произведение отношения R 1 степени к 1 и отношения R 2 степени к 2 (R 1 TIMES R 2), которые не имеют одинаковых имен атрибутов, есть отношение R степени (к 1+к 2), заголовок которого представляет сцепление заголовков отношений R 1 и R 2, а тело – имеет кортежи, такие, что первые к 1 элементов кортежей принадлежат множеству R 1, а последние к 2 элементов – множеству R 2. 7

Декартово произведение Пример 2. Произведение отношений. Пусть отношение R 1 ─ множество номеров всех текущих поставщиков {S 1, S 2, S 3, S 4, S 5}, а отношение R 2 – множество номеров всех текущих деталей {Р 1, Р 2, Р 3, Р 4, Р 5, Р 6}. Результатом операции R 1 TIMES R 2 является множество всех пар типа "поставщик-деталь", т. е. {(S 1, P 1), (S 1, P 2), (S 1, P 3), (S 1, P 4), (S 1, P 5), (S 1, P 6), (S 2, P 1), . . . , (S 5, P 6)}. 8

Декартово произведение n таблиц - это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, . . . и строки из n -й таблицы. SELECT Вид_блюд. *, Трапезы. * FROM Вид_блюд, Трапезы; Получим таблицу, содержащую 5 х 3 = 15 строк: 9

Декартово произведение

Соединения Соединение ─ это подмножество декартового произведения. Соединение Сf(R 1, R 2) отношений R 1 и R 2 по условию, заданному формулой f, представляет собой отношение R, получаемое путем Декартова произведения отношений R 1 и R 2 с последующим применением к результату операции выборки по формуле f. Другими словами, соединением отношения R 1 по атрибуту А с отношением R 2 по атрибуту В (отношения не имеют общих имен атрибутов) является результат выполнения операции вида: (R 1 TIMES R 2) WHERE A B, где – логическое выражение над атрибутами, 11 определенными на одном домене.

Соединения Операция эквисоединения характеризуется тем, что формула задает равенство операндов. Пример. Запрос SELECT Меню. *, Трапезы. *, Вид_блюд. *, Блюда. * FROM Меню, Трапезы, Вид_блюд, Блюда WHERE Меню. Т = Трапезы. Т AND Меню. В = Вид_блюд. В AND Меню. БЛ = Блюда. БЛ; задает эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда: 12

Соединения 13

Соединения Операция естественного соединения (операция JOIN) применяется к двум отношениям, имеющим общий атрибут (простой или составной). Этот атрибут в отношениях имеет одно и то же имя (совокупность имен) и определен на одном и том же домене (доменах). Результатом операции естественного соединения является отношение R, которое представляет собой проекцию эквисоединения отношений R 1 и R 2 по общему атрибуту на объединенную совокупность атрибутов обоих отношений. 14

Соединения Операция естественного соединения позволяет исключить дублирование столбцов, имеющее место при эквисоединении. Пример. SELECT Т, В, БЛ, Трапеза, Вид, Блюдо, Основа, Выход, Труд FROM Меню, Трапезы, Вид_блюд, Блюда WHERE Меню. Т = Трапезы. Т AND Меню. В = Вид_блюд. В AND Меню. БЛ = Блюда. БЛ; 15

Соединения 16

Композиция Для исключения всех столбцов, по которым проводится соединение таблиц, надо создать композицию. SELECT Трапеза, Вид, Блюдо, Основа, Выход, Труд FROM Меню, Трапезы, Вид_блюд, Блюда WHERE Меню. Т = Трапезы. Т AND Меню. В = Вид_блюд. В AND Меню. БЛ = Блюда. БЛ; 17

Композиция Трапеза Блюдо Вид Основа Выход Труд Завтрак Салат Закуска Овощи 200. 4 Завтрак Мясо Закуска Мясо 250. 3 Завтрак Омлет Горячее Яйца 200. 5 . . . Ужин Драчена Горячее Яйца 180. 4 Ужин Компот Напиток Фрукты 200. 2 Ужин Мол. нап. Напиток Молоко 200. 2 18

Соединение с дополнительным условием При формировании соединения создается рабочая таблица, к которой применимы операции: отбор нужных строк соединения (WHERE фраза), упорядочение получаемого результата (ORDER BY фраза) и агрегатирование данных (SQL-функции и GROUP BY фраза). Например, для получения перечня блюд, предлагаемых в меню на завтрак, можно сформировать запрос на основе композиции: SELECT Вид, Блюдо, Основа, Выход, 'Номер -', БЛ FROM Меню, Трапезы, Вид_блюд, Блюда WHERE Меню. Т = Трапезы. Т AND Меню. В = Вид_блюд. В AND Меню. БЛ = Блюда. БЛ AND Трапеза = ’Завтрак’; 19

Соединение с дополнительным условием Получим: 20

Соединение таблицы со своей копией Временную копию таблицы можно сформировать, указав имя псевдонима за именем таблицы во фразе FROM. Так, с помощью фразы FROM Блюда X, Блюда Y, Блюда Z будут созданы три копии таблицы Блюда с именами X, Y и Z Пример соединения таблицы с ней самой. Сформируем запрос на вывод пар блюд таблицы Блюда, в которых совпадает основа, а название первого блюда пары меньше (по алфавиту), чем номер второго блюда пары. Для этого создадим запрос с одной копией таблицы Блюда (Копия): SELECT Блюдо, Копия. Блюдо, Основа FROM Блюда, Блюда Копия WHERE Основа = Копия. Основа 21 AND Блюдо < Копия. Блюдо; .

Соединение таблицы со своей копией или двумя ее копиями (Первая и Вторая): SELECT Первая. Блюдо, Вторая. Блюдо, Основа FROM Блюда Первая, Блюда Вторая WHERE Первая. Основа = Вторая. Основа AND Первая. Блюдо < Вторая. Блюдо; 22

Соединение таблицы со своей копией Результат: 23

Агрегатные функции (Count, Sum, Round, Min, Max) возвращают результат обработки группы строк. Целесообразны в конструкциях SELECT, ORDER BY, HAVING и в аналитических функциях. Воспользуемся оператором SELECT, чтобы посмотреть содержание таблицы DELIVERY: SELECT component, title, quantity_kg, cost_of_component, date_of_delivery FROM delivery, components, provider WHERE delivery. id_component = components. id_component AND delivery. id_provider = provider. id_provider; 24

Агрегатные функции 25

Агрегатные функции SELECT COUNT (quantity_kg), SUM (quantity_kg), ROUND (AVG(quantity_kg), 2), MIN (quantity_kg), MAX (quantity_kg) FROM delivery; Результат: 26

Конструкции GROUP BY и ORDER BY Если требуется вычислить общую сумму количества каждого из поставляемых компонент, то нужно воспользоваться группировкой данных с помощью фразы GROUP BY, которая инициирует перекомпоновку строк таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. Пример. SELECT component, SUM (quantity_kg) FROM components, delivery WHERE components. id_component = delivery. id_component GROUP BY component ORDER BY SUM (quantity_kg); 27

Конструкции GROUP BY и ORDER BY • • • • • • COMPONENT SUM(QUANTITY_KG) -------------------------кофе_Kopi Luwak 2 лимонная цедра 3 лимон 3 мед 4 ром 5 миндаль 6 сметана 7 ванилин 7 сливки 10 мороженое 11 яйца 12 кофе_Эксцельза 19 молоко 20 кофе_Hawaiian Kona 39 какао 40 кофе_Colombia Excelso 44 кофе_Либерика 68 кофе_Робуста 96 кофе_Арабика 101 19 rows selected 28

Использование фразы HAVING Фраза HAVING играет такую же роль для групп, что фраза WHERE для строк, т. е. используется для исключения групп точно так же, как WHERE используется для исключения строк, следовательно, может применяться только при наличии фразы GROUP BY. Например, нужно выдать количество каждого поставляемого компонента, включив в список только те, которые поставляются более, чем от двух поставщиков: 29

Использование фразы HAVING SELECT component, COUNT(*) FROM components, delivery WHERE components. id_component = delivery. id_component GROUP BY component HAVING COUNT(*) > 2 ORDER BY component; Результат: COMPONENT COUNT(*) --------------- кофе_Colombia Excelso 3 кофе_Арабика 5 кофе_Либерика 3 кофе_Робуста 4 rows selected 30

Внутренние соединения Внутреннее соединение (inner join) означает, что в результирующем наборе данных содержатся записи, в которых значения в связанных полях совпадают. Формат: SELECT column_name(s) FROM table_name 1 INNER JOIN table_name 2 ON table_name 1. column_name=table_name 2. column_name Пример. SELECT Product. Name, Category. Name FROM Products INNER JOIN Categories 31 ON Products. Category. ID = Categories. Category. ID

Внешние соединения Внешнее соединение похоже на внутреннее, но в результирующий набор данных включаются также записи ведущей таблицы соединения, которые объединяются с пустым множеством записей другой таблицы. Какая из таблиц будет ведущей, определяет вид соединения. LEFT ─ левое внешнее соединение, ведущей является таблица, расположенная слева от вида соединения; RIGHT ─ правое внешнее соединение, ведущая таблица расположена справа от вида соединения. 32

Исходные отношения 33

Левое внешнее соединение Это соединение, при котором кортежи отношения R, не имеющие совпадающих значений в общих столбцах отношения S, также включаются в результирующее отношение. Пример SELECT R. a 1, R. a 2, S. b 1, S. b 2 FROM R LEFT JOIN S ON R. a 2=S. b 1 34

Правое внешнее соединение В результирующем отношении содержатся все кортежи правого отношения. Пример SELECT R. a 1, R. a 2, S. b 1, S. b 2 FROM R RIGHT JOIN S ON R. a 2=S. b 1 35

Полное внешнее соединение В его результирующее отношение помещаются все кортежи из обоих отношений, а для обозначения несовпадающих значений кортежей используются определители NULL. Пример. Вывести информацию о всех товарах. SELECT Товар. *, Сделка. * FROM Товар LEFT JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара; Для проданных товаров будет указана дата сделки и количество. Для непроданных товаров эти поля останутся пустыми. 36

Полусоединение Определяет отношение, содержащее те кортежи отношения R, которые входят в соединение отношений R и S. 37

Полусоединение SELECT R. a 1, R. a 2 FROM R, S WHERE R. a 2=S. b 1 или SELECT R. a 1, R. a 2 FROM R INNER JOIN S ON R. a 2=S. b 1 38

Объединение (UNION) отношений R и S можно получить в результате их конкатенации с образованием одного отношения с исключением кортежей-дубликатов. Отношения R и S должны быть совместимы, т. е. иметь одинаковое количество полей с совпадающими типами данных. Отношения должны быть совместимы по объединению. 39

Объединение Пример. Даны два отношения А и В 40

Объединение Результат объединения А UNION В 41

Объединением двух таблиц R и S является таблица, содержащая все строки, которые имеются в первой таблице R, во второй таблице S или в обеих таблицах сразу. Пример. Объединение отношений в SQL. SELECT R. a 1, R. a 2 FROM R UNION SELECT S. b 2, S. b 1 FROM S 42

Операция пересечения (INTERSECT) R ∩ S=R-(R-S) определяет отношение, которое содержит кортежи, присутствующие как в отношении R, так и в отношении S. Отношения R и S должны быть совместимы по объединению. Пересечением двух таблиц R и S является таблица, содержащая все строки, присутствующие в обеих исходных таблицах одновременно. Пример. SELECT R. a 1, R. a 2 FROM R, S WHERE R. a 1=S. b 1 AND R. a 2=S. b 2 43

Операция пересечения или SELECT R. a 1, R. a 2 FROM R WHERE R. a 1 IN (SELECT S. b 1 FROM S WHERE S. b 1=R. a 1) AND R. a 2 IN (SELECT S. b 2 FROM S WHERE S. b 2=R. a 2) 44

Вложенные подзапросы [Кириллов SQL] Вложенный подзапрос - это подзапрос, заключенный в круглые скобки и вложенный во фразу WHERE (HAVING) предложения SELECT или других предложений, использующих фразу WHERE. Вложенный подзапрос может содержать в своей WHERE (HAVING) фразе другой вложенный подзапрос и т. д. Вложенный подзапрос позволяет при отборе строк таблицы, сформированной основным запросом, использовать данные из других таблиц. 45

Вложенные подзапросы Вложенный во внешнюю инструкцию SELECT запрос, имеет следующие компоненты: • обычный запрос SELECT, включающий обычные компоненты списка выборки; • обычное предложение FROM, включающее одно или более имен таблиц или представлений; • необязательное предложение WHERE; • необязательное предложение GROUP BY; • необязательное предложение HAVING. 46

Вложенные подзапросы Подзапрос позволяет решать следующие задачи: • определять набор строк, добавляемый в таблицу на одно выполнение оператора INSERT; • определять данные, включаемые в представление, создаваемое оператором CREATE VIEW ; • определять значения, модифицируемые оператором UPDATE; • указывать одно или несколько значений во фразах WHERE и HAVING оператора SELECT; • определять во фразе FROM таблицу как результат выполнения подзапроса; • применять коррелированные подзапросы. 47

Вложенные подзапросы Условно подзапросы иногда подразделяют на три типа, каждый из которых является сужением предыдущего: • табличный подзапрос, возвращающий набор строк и столбцов; • подзапрос строки, возвращающий только одну строку, но, возможно, несколько столбцов (такие подзапросы часто используются во встроенном SQL); • скалярный подзапрос, возвращающий значение одного столбца в одной строке. 48

Вложенные подзапросы Типы вложенных подзапросов: простые и коррелированные Простые вложенные подзапросы обрабатываются "снизу вверх". Первым обрабатывается вложенный подзапрос самого нижнего уровня. Множество значений, полученное в результате его выполнения, используется при реализации подзапроса более высокого уровня и т. д. Коррелированные вложенные подзапросы обрабатываются «сверху вниз» . Сначала выбирается первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значения столбцов, которые используются во вложенных подзапросах. Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т. д. 49

Вложенные подзапросы Простые вложенные подзапросы используются для представления множества значений, исследование которых осуществляется в предикате IN. Пример: требуется выдать название и статус поставщиков продукта с номером 11, т. е. помидоров. SELECT Название, Статус FROM Поставщики WHERE ПС IN ( SELECT ПС FROM Поставки WHERE ПР = 11 ); 50

Вложенные подзапросы Пример простого подзапроса. Требуется посмотреть список поставщиков компонента, под названием 'кофе_Арабика': SELECT title FROM provider WHERE id_provider IN (SELECT id_provider FROM delivery WHERE id_component IN (SELECT id_component FROM components WHERE component = 'кофе_Арабика')); 51

Вложенные подзапросы Результат: TITLE ---------- ALEF coffee_tea Profi. Trade Wintergreen Julius Meinl Jardin 5 rows selected 52

Вложенные подзапросы Коррелированные подзапросы: в операторе SELECT внутреннего подзапроса можно ссылаться на столбцы внешнего запроса. Такой подзапрос выполняется для каждой строки таблицы, определяя условие ее вхождения в формируемый результирующий набор. Пример коррелированного подзапроса: SELECT * from tbl 1 t 1 WHERE f 2 IN (SELECT f 2 FROM tbl 2 t 2 WHERE t 1. f 3=t 2. f 3); Для каждой строки таблицы tbl 1 будет проверяться условие, что значение поля f 2 совпадает со значением строки таблицы tbl 2, где значение поля f 3 равно значению поля f 3 внешней таблицы (tbl 1). 53

Вложенные подзапросы Для коррелированного подзапроса во фразе HAVING можно использовать только агрегирующие функции, так как при выполнении подзапроса в качестве проверяемой строки выступает результат группирования строк на основе агрегирующих функций основного запроса. Пример: SELECT f 1, COUNT(*), SUM(f 2) from tbl 1 t 1 GROUP BY f 1 HAVING SUM(f 2)> (SELECT MIN(f 2)*4 FROM tbl 1 t 1_in WHERE t 1. f 1=t 1_in. f 1); 54