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

»

Составил зав. кафедрой «Информационные и вычислительные системы»
дтн, профессор А.Д.Хомоненко

ПГУПС 2011

– СПб.: КОРОНА принт, 2004. – 736 с.
Кириллов В.В., Громов

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

операции:
объединение,
разность (вычитание),
пересечение,
декартово (прямое) произведение,
выборка (селекция,

ограничение),
проекция,
деление,
соединение.

степени к2 (R1 TIMES R2), которые не имеют одинаковых имен

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

всех текущих поставщиков {S1, S2, S3, S4, S5}, а отношение

R2 – множество номеров всех текущих деталей {Р1, Р2, Р3, Р4, Р5, Р6}. Результатом операции R1 TIMES R2 является множество всех пар типа "поставщик-деталь", т. е. {(S1,P1), (S1,P2), (S1,P3), (S1,P4), (S1,P5), (S1,P6), (S2,P1), ... , (S5,P6)}.

возможные строки r, такие, что r является сцеплением какой-либо строки

из первой таблицы, строки из второй таблицы, ... и строки из n-й таблицы.

SELECT Вид_блюд.*, Трапезы.*
FROM Вид_блюд, Трапезы;

Получим таблицу, содержащую 5 х 3 = 15 строк:

R1 и R2 по условию, заданному формулой f, представляет собой

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

Меню.*, Трапезы.*, Вид_блюд.*, Блюда.*
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE

Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;

задает эквисоединение таблиц Меню, Трапезы, Вид_блюд и Блюда:

общий атрибут (простой или составной). Этот атрибут в отношениях имеет

одно и то же имя (совокупность имен) и определен на одном и том же домене (доменах).
Результатом операции естественного соединения является отношение R, которое представляет собой проекцию эквисоединения отношений R1 и R2 по общему атрибуту на объединенную совокупность атрибутов обоих отношений.

эквисоединении.
Пример.

SELECT Т, В, БЛ, Трапеза, Вид, Блюдо, Основа, Выход,

Труд
FROM Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;

надо создать композицию.

SELECT Трапеза, Вид, Блюдо, Основа, Выход, Труд FROM

Меню, Трапезы, Вид_блюд, Блюда
WHERE Меню.Т = Трапезы.Т
AND Меню.В = Вид_блюд.В
AND Меню.БЛ = Блюда.БЛ;

Основа Выход Труд
Завтрак Салат Закуска Овощи

200. 4
Завтрак Мясо Закуска Мясо 250. 3
Завтрак Омлет Горячее Яйца 200. 5
. . .
Ужин Драчена Горячее Яйца 180. 4
Ужин Компот Напиток Фрукты 200. 2
Ужин Мол. нап. Напиток Молоко 200. 2

к которой применимы операции: отбор нужных строк соединения (WHERE фраза),

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

имя псевдонима за именем таблицы во фразе FROM. Так, с

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

Вторая):

SELECT Первая.Блюдо, Вторая.Блюдо, Основа
FROM Блюда Первая, Блюда Вторая
WHERE

Первая.Основа = Вторая.Основа
AND Первая.Блюдо < Вторая.Блюдо;

обработки группы строк. Целесообразны в конструкциях 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;

(quantity_kg), MAX (quantity_kg)
FROM delivery;

Результат:
 

количества каждого из поставляемых компонент, то нужно воспользоваться группировкой данных

с помощью фразы 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);

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

групп, что фраза WHERE для строк, т.е. используется для исключения

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

delivery.id_component
GROUP BY component
HAVING COUNT(*) > 2
ORDER BY component;
 
Результат: 
COMPONENT

COUNT(*)
------------------------------ ----------------------
кофе_Colombia Excelso 3
кофе_Арабика 5
кофе_Либерика 3
кофе_Робуста 4
4 rows selected

содержатся записи, в которых значения в связанных полях совпадают.
Формат:
SELECT column_name(s) FROM

table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
Пример.
SELECT ProductName, CategoryName
FROM Products
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID

данных включаются также записи ведущей таблицы соединения, которые объединяются с

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

имеющие совпадающих значений в общих столбцах отношения S, также включаются

в результирующее отношение.
Пример SELECT R.a1, R.a2, S.b1, S.b2
FROM R LEFT JOIN S ON R.a2=S.b1

Пример SELECT R.a1, R.a2, S.b1, S.b2
FROM R RIGHT JOIN

S ON R.a2=S.b1

обоих отношений, а для обозначения несовпадающих значений кортежей используются определители

NULL.

Пример. Вывести информацию о всех товарах.

SELECT Товар.*, Сделка.*
FROM Товар LEFT JOIN Сделка
ON Товар.КодТовара=Сделка.КодТовара;

Для проданных товаров будет указана дата сделки и количество. Для непроданных товаров эти поля останутся пустыми.

в соединение отношений R и S.

R.a2
FROM R INNER JOIN S ON R.a2=S.b1

и S можно получить в результате их конкатенации с образованием

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

строки, которые имеются в первой таблице R, во второй таблице

S или в обеих таблицах сразу.
Пример. Объединение отношений в SQL.
SELECT R.a1, R.a2
FROM R
UNION
SELECT S.b2, S.b1
FROM S

содержит кортежи, присутствующие как в отношении R, так и в

отношении S.
Отношения R и S должны быть совместимы по объединению.
Пересечением двух таблиц R и S является таблица, содержащая все строки, присутствующие в обеих исходных таблицах одновременно.
Пример.
SELECT R.a1, R.a2
FROM R,S
WHERE R.a1=S.b1 AND R.a2=S.b2

(SELECT S.b1 FROM S
WHERE S.b1=R.a1) AND R.a2

IN
(SELECT S.b2
FROM S
WHERE S.b2=R.a2)

круглые скобки и вложенный во фразу WHERE (HAVING) предложения SELECT

или других предложений, использующих фразу WHERE .
Вложенный подзапрос может содержать в своей WHERE (HAVING) фразе другой вложенный подзапрос и т.д.
Вложенный подзапрос позволяет при отборе строк таблицы, сформированной основным запросом, использовать данные из других таблиц.

обычный запрос SELECT, включающий обычные компоненты списка выборки;
обычное предложение FROM,

включающее одно или более имен таблиц или представлений;
необязательное предложение WHERE;
необязательное предложение GROUP BY;
необязательное предложение HAVING.

в таблицу на одно выполнение оператора INSERT;
определять данные, включаемые

в представление, создаваемое оператором CREATE VIEW ;
определять значения, модифицируемые оператором UPDATE;
указывать одно или несколько значений во фразах WHERE и HAVING оператора SELECT;
определять во фразе FROM таблицу как результат выполнения подзапроса;
применять коррелированные подзапросы.

которых является сужением предыдущего:
табличный подзапрос, возвращающий набор строк и

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

обрабатываются "снизу вверх". Первым обрабатывается вложенный подзапрос самого нижнего уровня.

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

которых осуществляется в предикате IN.
Пример: требуется выдать название и статус

поставщиков продукта с номером 11, т.е. помидоров.
SELECT Название, Статус
FROM Поставщики
WHERE ПС IN
( SELECT ПС
FROM Поставки
WHERE ПР = 11 );

названием 'кофе_Арабика':
 
SELECT title
FROM provider
WHERE id_provider IN
(SELECT

id_provider
FROM delivery
WHERE id_component IN
(SELECT id_component
FROM components
WHERE component = 'кофе_Арабика'));

--------------------
ALEF coffee_tea
ProfiTrade

Wintergreen
Julius Meinl
Jardin
 
5 rows selected

на столбцы внешнего запроса. Такой подзапрос выполняется для каждой строки

таблицы, определяя условие ее вхождения в формируемый результирующий набор.
Пример коррелированного подзапроса:
SELECT * from tbl1 t1
WHERE f2 IN (SELECT f2 FROM tbl2 t2
WHERE t1.f3=t2.f3);
Для каждой строки таблицы tbl1 будет проверяться условие, что значение поля f2 совпадает со значением строки таблицы tbl2, где значение поля f3 равно значению поля f3 внешней таблицы (tbl1).

агрегирующие функции, так как при выполнении подзапроса в качестве проверяемой

строки выступает результат группирования строк на основе агрегирующих функций основного запроса.
Пример:
SELECT f1, COUNT(*), SUM(f2) from tbl1 t1
GROUP BY f1
HAVING SUM(f2)> (SELECT MIN(f2)*4
FROM tbl1 t1_in
WHERE t1.f1=t1_in.f1);