Методические указания по их использованию. Пособие содержит большое количество примеров на использование операторов sql, которые могут быть полезны как на этапе освоения материала, так и выступать в качестве вопросов для самопроверки



Download 441,57 Kb.
bet26/71
Sana15.01.2023
Hajmi441,57 Kb.
#899634
TuriМетодические указания
1   ...   22   23   24   25   26   27   28   29   ...   71
Bog'liq
Методичка SQL(14) (оптимизация)

SELECT *
FROM Соcтав
WHERE Блюдо IN ( SELECT ID_блюда
FROM Блюда
WHERE Основа = 'Молоко');
При обработке полного запроса система выполняет, прежде всего, вложенный подзапрос. Этот подзапрос выдает множество номеров блюд, основой которых является молоко, а именно множество (7, 8, 12, 18, 22, 24, 28, 31). Поэтому первоначальный запрос эквивалентен такому простому запросу:
SELECT *
FROM Соcтав
WHERE Блюдо IN (7, 8, 12, 18, 22, 24, 28, 31);
Приведем пример запроса с несколькими уровнями вложенности:
Пример 27.
SELECT p.*, Количество, Цена
FROM Продукты p LEFT JOIN Наличие n
ON p. ID_Продукта = n.Продукт

WHERE p.ID_Продукта IN (SELECT Продукт
FROM Соcтав
WHERE Блюдо IN ( SELECT ID_блюда
FROM Блюда
WHERE Основа = ’Молоко’) );

Результат




ID_Продукта

Про
дукт

Белки

Жиры

Угле
воды

К

Са

Na

B2

PP

C

Коли
чество

Цена

3

Масло

60

825

90

230

220

740

0,1

1

NULL

73

274,61

5

Яйца

127

115

7

1530

550

710

4,4

1,9

NULL

61

111,83

6

Сметана

26

300

28

950

850

320

1

1

2

88

206,60

7

Молоко

28

32

47

1460

1210

1500

1,3

1

10

214

83,80

8

Творог

167

90

13

1120

1640

1410

2,7

4

5

92

82,80

13

Рис

70

6

773

540

240

260

0,4

16

NULL

54

51,17

14

Мука

106

13

732

1760

240

120

1,2

22

NULL

91

43,77

15

Яблоки

4

NULL

113

2480

160

260

0,3

3

NULL

117

189,92

16

Сахар

NULL

NULL

998

30

20

10

NULL

NULL

130

98

96,14

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


В любой ситуации, где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу, если больше чем одно значение выбрано подзапросом. Заметим, что использование IN соответствует операции пересечения реляционной алгебры, а NOT IN разности.


4.2.2. Использование одной и той же таблицы во внешнем и вложенном подзапросе


Пример 28.
Выдать номера Блюд, в состав которых входят продукты, необходимые для приготовления сырников.
SELECT DISTINCT Блюдо
FROM Соcтав
WHERE Продукт IN ( SELECT Продукт
FROM Состав
WHERE Блюдо = 18);
Отметим, что ссылка на Состав во вложенном подзапросе означает не то же самое, что ссылка на Состав во внешнем запросе. В действительности, два имени Состав обозначают различные значения. Чтобы этот факт стал явным, полезно использовать псевдонимы, например, С1 и С2:
SELECT DISTINCT Блюдо
FROM Соcтав С1
WHERE С1. Продукт IN ( SELECT Продукт
FROM Состав С2
WHERE С2.Блюдо = 18);
Здесь С1 и С2 – произвольные псевдонимы таблицы Состав, определяемые во фразе FROM и используемые как явные уточнители во фразах SELECT и WHERE. Напомним, что псевдонимы определены лишь в пределах одного запроса.

4.2.3. Использование агрегатных функций в подзапросах.


Любой запрос, использующий одиночную функцию агрегата без предложения GROUP BY, будет выбирать одиночное значение, а значит, может быть использован для сравнения в основном предикате.
Пример 29.
Необходимо найти самые дешевые продукты:
SELECT p.Продукт, Цена
FROM Продукты p JOIN Наличие n ON p.ID_продукта = n.Продукт
WHERE Цена = ( SELECT MIN (Цена) FROM Наличие);

Результат




Продукт

Цена

Зелень

34,96

Подзапрос ищет минимальную цену в таблице Наличие, а основной запрос выбирает продукты с найденной ценой.


Следует заметить, что если агрегатные функции используются с предложением GROUP BY, то подзапрос может вернуть многочисленные значения. Они, следовательно, не допустимы в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе.
Если не удается построить подзапрос, возвращающий единственное значение вы должны использовать предикаты IN, ANY или ALL. Но следует заметить, что предикаты BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами
Следует отметить все подзапросы в качестве результата должны возвращать одиночный столбец или одиночное выражение (может быть основано на нескольких столбцах). Это обязательное требование, поскольку результат запроса сравнивается с одиночным значением. Исключением из этого правила является ситуация, когда подзапросы используются с оператором EXISTS, который мы будем рассматривать ниже.

4.2.4. Подзапросы в предложении HAVING.


Подзапросы могут использоваться внутри предложения HAVING.
Пример 30.
Найти самые дешевые блюда.
SELECT b.Блюдо,
cast(SUM (s.Вес*n.Цена/1000)+b.Труд as numeric(6,2)) as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда =s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд
HAVING (SUM (s.Вес*n.Цена/1000)+b.Труд) = ( SELECT MIN(стоимость)
FROM (SELECT b.Блюдо, SUM (s.Вес*n.Цена/1000)+b.Труд as Стоимость
FROM (Блюда b JOIN Состав s ON b.ID_блюда = s.Блюдо )
JOIN Наличие n ON s.Продукт = n.Продукт
GROUP BY b.Блюдо, b.Труд ) a
);

Результат




Блюдо

Цена

Кофе черный

2,33

Этот запрос ищет для каждого блюда стоимость (псевдоним a), затем из этих данных выбирает минимум (подается на вход HAVING). Этот минимум используется для отбора блюд для вывода результата основного запроса.


4.3. Соотнесенные подзапросы.


Соотнесенным называется подзапрос, который ссылается на данные внешнего запроса. Соотнесенный подзапрос - один из большого количества тонких понятий в SQL из-за сложности в его оценке. Если вы сумеете овладеть им, вы найдете что он очень мощный, потому что может выполнять сложные функции с помощью очень лаконичных указаний. Соотнесенный подзапрос выполняется неоднократно, по одному разу для каждой строки таблицы основного запроса.
Пример 31.
Рассчитать стоимость блюд на основе молока.
SELECT b.Блюдо, ( SELECT SUM (s.Вес*n.Цена/1000)
FROM Состав s JOIN Наличие n ON s.Продукт = n.Продукт
WHERE s.Блюдо = b.ID_Блюда) + b.Труд as Стоимость
FROM Блюда b
WHERE Основа = 'Молоко';

Результат




Блюдо

Стоимость

Сметана

28,2671

Творог

19,9821

Суп молочный

35,9746

Сырники

25,0069

Вареники ленивые

24,1267

Суфле из творога

36,7818

Крем творожный

22,8368

Молочный напиток

45,4615

Такой подзапрос отличается от рассмотренного выше, тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения b.ID_Блюда, а оно изменяется по мере того, как система проверяет различные строки таблицы Блюда. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:

  1. Система проверяет первую строку таблицы Блюда. Предположим, что это строка удовлетворяет условию Основа = 'Молоко'

  2. Теперь система может передать b.ID_Блюда в подзапрос и вычислить стоимость блюда.

  3. Далее система будет повторять обработку такого рода для следующего блюда и т.д. до тех пор, пока не будут рассмотрены все строки таблицы Блюда.

Рассмотрим пример использования одной и той же таблицы во внешнем подзапросе и соотнесенном вложенном подзапросе.
Пример 32.
Выдать номера всех продуктов, которые используются только в одном блюде.

Download 441,57 Kb.

Do'stlaringiz bilan baham:
1   ...   22   23   24   25   26   27   28   29   ...   71




Ma'lumotlar bazasi mualliflik huquqi bilan himoyalangan ©hozir.org 2024
ma'muriyatiga murojaat qiling

kiriting | ro'yxatdan o'tish
    Bosh sahifa
юртда тантана
Боғда битган
Бугун юртда
Эшитганлар жилманглар
Эшитмадим деманглар
битган бодомлар
Yangiariq tumani
qitish marakazi
Raqamli texnologiyalar
ilishida muhokamadan
tasdiqqa tavsiya
tavsiya etilgan
iqtisodiyot kafedrasi
steiermarkischen landesregierung
asarlaringizni yuboring
o'zingizning asarlaringizni
Iltimos faqat
faqat o'zingizning
steierm rkischen
landesregierung fachabteilung
rkischen landesregierung
hamshira loyihasi
loyihasi mavsum
faolyatining oqibatlari
asosiy adabiyotlar
fakulteti ahborot
ahborot havfsizligi
havfsizligi kafedrasi
fanidan bo’yicha
fakulteti iqtisodiyot
boshqaruv fakulteti
chiqarishda boshqaruv
ishlab chiqarishda
iqtisodiyot fakultet
multiservis tarmoqlari
fanidan asosiy
Uzbek fanidan
mavzulari potok
asosidagi multiservis
'aliyyil a'ziym
billahil 'aliyyil
illaa billahil
quvvata illaa
falah' deganida
Kompyuter savodxonligi
bo’yicha mustaqil
'alal falah'
Hayya 'alal
'alas soloh
Hayya 'alas
mavsum boyicha


yuklab olish