- •Глава 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
- •Рекомендуемая литература
In ([Овощи], [Мясо], [Рыба], [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе])
--указываются конкретные значения в столбце Основа,
-- которые следует использовать в качестве заголовков
) pvt ;
-- алиас для сводной таблицы
Результат |
| |||||||
вид |
Овощи |
Мясо |
Рыба |
Молоко |
Яйца |
Крупа |
Фрукты |
Кофе |
Горячее |
2 |
2 |
1 |
3 |
2 |
2 |
0 |
0 |
Десерт |
0 |
0 |
0 |
1 |
0 |
0 |
3 |
0 |
Закуска |
2 |
2 |
2 |
2 |
0 |
0 |
0 |
0 |
Напиток |
0 |
0 |
0 |
1 |
0 |
0 |
2 |
2 |
Суп |
0 |
1 |
2 |
1 |
0 |
0 |
0 |
0 |
В отличие от сводных таблиц, в операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное ограничение, т.к. для этого нужно знать характер данных, а значит, и применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из справочников).
Синтаксис:
SELECT <несведенный столбец>,
[первый сведенный столбец] AS <имя столбца>,
[второй сведенный столбец] AS <имя столбца>,
...
[последний сведенный столбец] AS <имя столбца>,
FROM
(< запрос SELECT, извлекающий эти данные>)
AS <псевдоним исходного запроса>
PIVOT
(
<статистическая функция>(<статистически обработанный столбец>)
FOR
[<столбец, содержащий значения, которые станут именами столбцов>]
In ( [первый сведенный столбец], [второй сведенный столбец],
... [последний сведенный столбец])
)AS <псевдоним сведенной таблицы>
<необязательное предложение ORDER BY>;
Приведенную таблицу можно заполнить и стандартным способом с использованием оператора CASE:
SELECT s.Вид,
SUM(CASE Основа WHEN 'Овощи' THEN 1 ELSE 0 END) Овощи
, SUM(CASE Основа WHEN 'Мясо' THEN 1 ELSE 0 END) Мясо
, SUM(CASE Основа WHEN 'Рыба' THEN 1 ELSE 0 END) Рыба
,SUM(CASE Основа WHEN 'Молоко' THEN 1 ELSE 0 END) Молоко ,SUM(CASE Основа WHEN 'Яйца' THEN 1 ELSE 0 END) Яйца
,SUM(CASE Основа WHEN 'Крупа' THEN 1 ELSE 0 END) Крупа
,SUM(CASE Основа WHEN 'Фрукты' THEN 1 ELSE 0 END) Фрукты ,SUM(CASE Основа WHEN 'Кофе' THEN 1 ELSE 0 END) Кофе
FROM Блюда b JOIN Справочник_вид_блюда s ON b. Вид = s.ID_ Вид
GROUP BY s. Вид;
Где опреатор Case имеет следующий синтаксис:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
input_expression —представляет собой любое допустимое выражение.
WHEN when_expression — простое выражение, с которым сравнивается аргумент input_expression. Типы данных аргумента input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу.
THEN result_expression — выражение, возвращаемое, если сравнение выражений input_expression и when_expression дает в результате TRUE или выражение Аргумент result expression представляет собой любое допустимое выражение.
ELSE else_result_expression —выражение, возвращаемое, если ни одна из операций сравнения не дает в результате TRUE. Если этот аргумент опущен и ни одна из операций сравнения не дает в результате TRUE, функция CASE возвращает NULL. Аргумент else_result_expression представляет собой любое допустимое выражение. Типы данных аргумента else_result_expression и любого из аргументов result_expression должны быть одинаковыми или неявно приводимыми друг к другу.
Как следует из названия оператора, UNPIVOT выполняет обратную по отношению к PIVOT операцию, т.е. представляет данные, записанные в строке таблицы, в одном столбце.
Пусть мы получили следующий результат, ограничив вывод Вид = ‘Горячее’
Вид |
Овощи |
Мясо |
Рыба |
Молоко |
Яйца |
Крупа |
Фрукты |
Кофе |
Горячее |
2 |
2 |
1 |
3 |
2 |
2 |
0 |
0 |
Развернуть эту таблицу в столбец мы можем следующим запросом
SELECT Основа,
-- заголовок столбца, который будет содержать заголовки строк исходной таблицы
Горячее
-- заголовок столбца, который будет содержать значения из строки исходной таблицы
From ( SELECT Вид, [Овощи], [Мясо], [Рыба]
, [Молоко], [Яйца], [Крупа], [Фрукты], [Кофе]
FROM ( -- pivot-запрос из предыдущего примера
SELECT s.Вид, Основа
FROM Блюда b JOIN Справочник_вид_блюда s
ON b.Вид = s.ID_Вид) a
PIVOT
(COUNT(Основа)
FOR Основа