- •Глава 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
- •Рекомендуемая литература
3.4.3. Реальные примеры соединений.
Внутреннее соединение.
Пример 21.
Получить информацию о стоимости и количестве каждого продукта на складе
SELECT Продукты.Продукт, Наличие.Количество, Наличие.Цена
FROM Продукты JOIN Наличие
ON Продукты.ID_Продукта = Наличие.Продукт;
Результат |
| ||||
Продукт |
Количество |
Цена | |||
Говядина |
108 |
429,84 | |||
Судак |
0 |
0,00 | |||
Масло |
73 |
274,61 | |||
Майонез |
39 |
97,46 | |||
Яйца |
61 |
111,83 | |||
Сметана |
88 |
206,60 | |||
Молоко |
214 |
83,80 | |||
Творог |
92 |
82,80 | |||
Морковь |
0 |
0,00 | |||
Лук |
77 |
46,30 | |||
Помидоры |
46 |
51,70 | |||
Зелень |
13 |
34,96 | |||
Рис |
54 |
51,17 | |||
Мука |
91 |
43,77 | |||
Яблоки |
117 |
189,92 | |||
Сахар |
98 |
96,14 | |||
Кофе |
37 |
166,50 |
Таблица Продукты соединяется с таблицей Наличие с помощью ключевого слова JOIN(INNER по умолчанию). После ON задаётся условие соединения, которое указывает, каким образом строки таблиц должны соответствовать друг другу, чтобы принять участие в соединении.
Между таблицами Продукты и Наличие существует связь «один к одному». Это означает, что для каждой строки из таблицы Продукты, в таблице Наличие будет найдено не более одной соответствующей строки (может не быть ни одной). В результат запроса попадут только те строки из Таблицы продукты, для которых соответствие найдено (продукт есть на складе).
Пример 22.
Более сложный запрос: Рассчитать стоимость блюда
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.Труд;
Результат |
|
Блюдо |
Стоимость |
Кофе черный |
2,332 |
Сметана |
28,2671 |
Компот |
16,2558 |
Кофе на молоке |
12,0205 |
Молочный напиток |
45,4615 |
Творог |
19,9821 |
Морковь с рисом |
14,5298 |
… |
|
Для того чтобы определить состав каждого блюда, таблица Блюда соединяется с таблицей Состав. Между таблицами Блюда и Состав существует связь «один ко многим». Что означает, что в состав каждого блюда может входить несколько продуктов. В результате внутреннего соединения получаем промежуточную таблицу. Для расчета стоимости полученную промежуточную таблицу соединяем с таблицей Наличие для определения цены продукта. Группировка по полю Блюдо позволяет каждое блюдо рассматривать как независимый набор строк, к которому применяется формула подсчета стоимости. Группировка по полю Труд позволяет учесть трудозатраты на изготовление одной порции блюда.
Левое внешнее соединение.
Запрос с левым внешним соединением идентичен уже рассмотренному запросу с внутренним соединением (Пример 21), за исключением того, что используются ключевые слова LEFT OUTER JOIN.
SELECT p.Продукт, n.Количество, n.Цена
FROM Продукты p LEFT JOIN Наличие n ON p.ID_Продукта = n.Продукт;
Результат |
| |
Продукт |
Количество |
Цена |
Говядина |
108 |
429,84 |
Судак |
0 |
0,00 |
Масло |
73 |
274,61 |
Майонез |
39 |
97,46 |
Яйца |
61 |
111,83 |
Сметана |
88 |
206,60 |
Молоко |
214 |
83,80 |
Творог |
92 |
82,80 |
Морковь |
0 |
0,00 |
Лук |
77 |
46,30 |
Помидоры |
46 |
51,70 |
Зелень |
13 |
34,96 |
Рис |
54 |
51,17 |
Мука |
91 |
43,77 |
Яблоки |
117 |
189,92 |
Сахар |
98 |
96,14 |
Кофе |
37 |
166,50 |
Масло растительное |
NULL |
NULL |
Единственное различие между левым внешним объединением и внутренним в наличии лишней строки для продукта масло растительное результате. Этого продукта нет на складе (т.е. не выполняется условие внутреннего соединения). Строка включается потому, что использовано именно левое внешнее объединение, при котором все строки левой таблицы, Продукты, должны попасть в результат.
Если мы хотим узнать, какие продукты отсутствуют на складе, необходимо дополнить запрос условием:
SELECT p.Продукт,