- •Глава 1. Историческая справка. 10
- •Глава 2. Пример бд «Ресторан». 20
- •Глава 3. Выборка данных. 25
- •Глава 4. Подзапросы и производные таблицы 65
- •Глава 5. Функции ранжирования. 86
- •Глава 1. Историческая справка.
- •Стандарты.
- •1.2. Структура sql.
- •Глава 2. Пример бд «Ресторан».
- •2.1. Описание.
- •2.2. Диаграмма.
- •2.3. Данные в таблицах.
- •Глава 3. Выборка данных.
- •3.1. Оператор select. Синтаксис.
- •3.2. Примеры запросов с использованием единственной таблицы.
- •3.2.1. Выборка без использования фразы where.
- •3.2.1.1. Простейшие примеры.
- •3.2.1.2. Исключение дубликатов (distinct).
- •3.2.1.3. Выборка вычисляемых значений.
- •3.2.2. Выборка c использованием фразы where.
- •3.2.2.1. Использование операторов сравнения.
- •3.2.2.2. Сравнение с null.
- •3.2.2.3. Использование between.
- •3.2.2.4. Использование in (not in).
- •3.2.2.5. Использование like.
- •3.2.2.6. Выборка с упорядочением.
- •3.2.3. Использование агрегатных функций для подведения итогов.
- •3.2.3.1. Агрегатные функции без использования фразы group by.
- •3.2.3.2. Фраза group by.
- •3.2.3.3. Раздел having.
- •3.3. Примеры запросов с использованием нескольких таблиц.
- •3.3.1. Соединения «с условием where».
- •3.3.2. Соединение таблиц с дополнительными условиями.
- •3.3.2.1. Соединение таблицы со своей копией.
- •3.4. Соединения нескольких таблиц, используя join.
- •3.4.1. Внутреннее соединение.
- •3.4.2. Внешнее соединение.
- •3.4.2.1. Левое внешнее соединение.
- •3.4.2.2. Правое внешнее соединение.
- •3.4.2.3. Полное внешнее соединение.
- •3.4.2.4. Перекрёстное соединение.
- •3.4.3. Реальные примеры соединений.
- •Isnull(cast(n.Количество as varchar), ’нет’) as на_складе
- •Isnull(cast(n.Количество as varchar),’нет’) as на_складе
- •Глава 4. Подзапросы и производные таблицы
- •4.1.Производная таблица.
- •4.2. Вложенные подзапросы
- •4.2.1 Простые вложенные подзапросы
- •4.2.2. Использование одной и той же таблицы во внешнем и вложенном подзапросе
- •4.2.3. Использование агрегатных функций в подзапросах.
- •4.2.4. Подзапросы в предложении having.
- •4.3. Соотнесенные подзапросы.
- •4.4 Использование оператора exists.
- •4.5. Использование операторов any и all.
- •4.6. Объединение запросов union.
- •4.6.1. Union и устранение дубликатов.
- •4.6.2. Использование строк и выражений с union.
- •4.6.3. Использование union с order by.
- •4.6.4. Реализация внешнего полного соединения через запросы с union.
- •Глава 5. Функции ранжирования.
- •5.1. Функция row_number.
- •5.2. Функции rank() и dense_rank()
- •Глава 6. ИспользованиеPivoTиUnpivot.
- •In ([Овощи], [Мясо], [Рыба], [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе])
- •In ( [первый сведенный столбец], [второй сведенный столбец],
- •In ([Овощи], [Мясо], [Рыба], [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе])
- •Insert into Продукты (id_Продукта, Продукт, Белки)
- •Values (18, 'Горох', 180 );
- •Insert into Продукты (Продукт, Белки, Жиры)
- •7.4. Оператор update.
- •Глава 8. Представление.
- •Insert into Список_блюд values (36, 'Рагу', 3, 20);
- •Глава 9. Создание, изменение и удаление таблиц.
- •9.1. Оператор create table
- •6. Ограничение identity (автоинкрементное поле).
- •9.2. Изменение таблицы после того как она была создана (alter table).
- •9.3. Удаление таблицы (drop table).
- •9.4. Операторы создания и удаления индексов.
- •9.5. Понятие домена
- •Глава 10. Обобщенные табличные выражения (сте).
- •Представления, производные таблицы и выражения cte.
- •Рекурсивные запросы.
- •Id_father integer foreign key references Tree (id),
- •Values (1, null, 'all'), (2, 1, 'sea'), (3, 1, 'earth'),
- •Деревья без рекурсии.
- •Пример использования сте для решения задачи Коммивояжера.
- •Insert into tur select to_town, from_town, miles from tur;
- •Глава 11. Этапы выполнения командыSql.
- •11.1. Оптимизация запросов.
- •Приложение 1. Реализация реляционной алгебры средствами оператора select (Реляционная полнота sql).
- •Intersect
- •Рекомендуемая литература
4.2.1 Простые вложенные подзапросы
Обычно, внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса, определяющего верно оно или нет.
Пример 25.
Мы знаем название блюда: Сырники, но не знаем значение ID, и хотим извлечь все Продукты из таблицы Продукты, которые участвуют в его приготовлении. Можно сделать это, выполнив операцию соединения, но существует и иной способ:
SELECT *
Результат |
| |
Блюдо |
Продукт |
Вес |
18 |
5 |
10 |
18 |
6 |
30 |
18 |
8 |
140 |
18 |
14 |
15 |
18 |
16 |
15 |
WHERE Блюдо = ( SELECT ID_блюда
FROM Блюда
WHERE Блюдо = 'Сырники');
Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (или подзапрос) внутри предложения WHERE. Единственной найденной строкой естественно будет ID_блюда = 18. Однако SQL, не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предиката принял вид
WHERE Блюдо = 18
Основной запрос затем выполняется как обычно. Конечно же, подзапрос должен выбрать один и только один столбец, а тип данных этого столбца должен совпадать с тем значением, с которым он будет сравниваться в предикате. При использовании подзапросов в предикатах, основанных на операторах сравнения ( =, <>, < и т.д.), вы должны убедиться, что использовали подзапрос, который будет выдавать одну и только одну строку вывода (в некоторых случаях, можно использовать DISTINCT, чтобы вынудить подзапрос генерировать одиночное значение).
Если заменить условие WHERE Блюдо=’Сырники’ на условие WHERE Основа = 'Молоко’, мы получим в результате подзапроса несколько строк. Это сделает условие основного запроса невозможным для оценки, и команда выдаст ошибку. Если вы используете подзапрос, который не выводит никаких значений вообще, команда будет выполнена; но основной запрос не выведет никаких значений. Подзапросы, которые не производят никакого вывода (или нулевой вывод) вынуждают рассматривать предикат ни как верный, ни как неверный, а как неизвестный. Однако, неизвестный предикат имеет тот же самый эффект что и неверный: никакие строки не выбираются основным запросом.
Синтаксис использования простого подзапроса
< столбец | выражение > < оператор > < подзапрос >
Варианты < подзапрос > < оператор > < столбец | выражение > или, < подзапрос > < оператор > < подзапрос > недопустимы.
Если не удается построить подзапрос, возвращающий единственное значение необходимо использовать предикат IN.
Пример 26:
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 разности.