- •Глава 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
- •Рекомендуемая литература
Глава 2. Пример бд «Ресторан».
Во всех примерах, приводимых в данной работе, будет рассматриваться следующая предметная область.
2.1. Описание.
Кладовая ресторана периодически пополняется продуктами (таблица Продукты). Каждый продукт имеет следующие характеристики: уникальный номер, название, основные пищевые вещества (белки, жиры и углеводы даны в граммах), минеральные вещества (калий, кальций, натрий) и витамины (B2, PP, C) (в миллиграммах из расчета на 1кг продукта).
В таблице Блюда представлены уникальные номера блюд, их названия, коды видов, основной продукт (столбец Основа), масса порции в граммах и приведенная стоимость в рублях приготовления одной порции (столбец Труд).
В таблице Рецепты приведена технология приготовления блюд. Их выделение в отдельную таблицу произведено потому, что одно и то же блюдо может иметь несколько разных рецептов.
Таблица Состав связывает между собой таблицы Блюда и Продукты, оговаривая, какая масса (в граммах) того или иного продукта (столбец Вес) должна входить в состав одной порции блюда.
Шеф-повар ежедневно получает от администратора сведения о количестве (в килограммах) имеющихся продуктов и их текущей стоимости (таблица Наличие). Используя эти сведения, он определяет по таблице Состав перечень тех блюд, которые можно приготовить из этих продуктов, а также калорийность и стоимость таких блюд. При этом стоимость блюда складывается из стоимости и массы продуктов, необходимых для приготовления одной его порции, а также из трудозатрат на ее приготовление (таблица Блюда). Калорийность же определяется по массе и калорийности каждого из продуктов блюда. (Для получения значения калорийности продукта исходят из того, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров - 9.3 ккал.)
На каждый день шеф-повар составляет меню. В этом меню (таблица Меню) предлагается по несколько альтернативных блюд каждого вида. Заказы клиентов фиксируются в таблице Заказы.
2.2. Диаграмма.
2.3. Данные в таблицах.
Таблица Продукты.
ID_про дукта |
Продукт |
Белки |
Жиры |
Углеводы |
К |
Са |
Na |
B2 |
PP |
C |
1 |
Говядина |
189 |
124 |
NULL |
3150 |
90 |
600 |
1,5 |
28 |
0 |
2 |
Судак |
190 |
80 |
0 |
1870 |
270 |
NULL |
1,1 |
10 |
30 |
3 |
Масло |
60 |
825 |
90 |
230 |
220 |
740 |
0,1 |
1 |
NULL |
4 |
Майонез |
31 |
670 |
26 |
480 |
280 |
NULL |
NULL |
NULL |
NULL |
5 |
Яйца |
127 |
115 |
7 |
1530 |
550 |
710 |
4,4 |
1,9 |
NULL |
6 |
Сметана |
26 |
300 |
28 |
950 |
850 |
320 |
1 |
1 |
2 |
7 |
Молоко |
28 |
32 |
47 |
1460 |
1210 |
1500 |
1,3 |
1 |
10 |
8 |
Творог |
167 |
90 |
13 |
1120 |
1640 |
1410 |
2,7 |
4 |
5 |
9 |
Морковь |
13 |
1 |
70 |
2000 |
510 |
210 |
0,7 |
9,9 |
50 |
10 |
Лук |
17 |
NULL |
95 |
1750 |
310 |
180 |
0,2 |
2 |
100 |
11 |
Помидоры |
6 |
NULL |
42 |
290 |
140 |
400 |
0,4 |
5,3 |
250 |
12 |
Зелень |
9 |
NULL |
20 |
340 |
275 |
75 |
1,2 |
4 |
380 |
13 |
Рис |
70 |
6 |
773 |
540 |
240 |
260 |
0,4 |
16 |
NULL |
14 |
Мука |
106 |
13 |
732 |
1760 |
240 |
120 |
1,2 |
22 |
NULL |
15 |
Яблоки |
4 |
NULL |
113 |
2480 |
160 |
260 |
0,3 |
3 |
NULL |
16 |
Сахар |
NULL |
NULL |
998 |
30 |
20 |
10 |
NULL |
NULL |
130 |
17 |
Кофе |
127 |
36 |
9 |
9710 |
180 |
180 |
0,3 |
1,8 |
NULL |
Таблица Блюда.
ID_блюда |
Блюдо |
Вид |
Основа |
Вес |
Труд |
1 |
Салат летний |
1 |
Овощи |
200 |
3 |
2 |
Салат мясной |
1 |
Мясо |
200 |
4 |
3 |
Салат витаминный |
1 |
Овощи |
200 |
4 |
4 |
Салат рыбный |
1 |
Рыба |
200 |
4 |
5 |
Паштет из рыбы |
1 |
Рыба |
120 |
5 |
6 |
Мясо с гарниром |
1 |
Мясо |
250 |
3 |
7 |
Сметана |
1 |
Молоко |
140 |
1 |
8 |
Творог |
1 |
Молоко |
140 |
2 |
9 |
Суп харчо |
2 |
Мясо |
500 |
5 |
10 |
Суп-пюре из рыбы |
2 |
Рыба |
500 |
6 |
11 |
Уха из судака |
2 |
Рыба |
500 |
5 |
12 |
Суп молочный |
2 |
Молоко |
500 |
3 |
13 |
Баструма |
3 |
Мясо |
300 |
5 |
14 |
Бефстроганов |
3 |
Мясо |
210 |
6 |
15 |
Судак по-польски |
3 |
Рыба |
160 |
5 |
16 |
Драчена |
3 |
Яйца |
180 |
4 |
17 |
Морковь с рисом |
3 |
Овощи |
260 |
3 |
18 |
Сырники |
3 |
Молоко |
220 |
4 |
19 |
Омлет с луком |
3 |
Яйца |
200 |
5 |
20 |
Каша рисовая |
3 |
Крупа |
210 |
4 |
21 |
Пудинг рисовый |
3 |
Крупа |
160 |
6 |
22 |
Вареники ленивые |
3 |
Молоко |
220 |
4 |
23 |
Помидоры с луком |
3 |
Овощи |
260 |
4 |
24 |
Суфле из творога |
3 |
Молоко |
280 |
6 |
25 |
Рулет с яблоками |
4 |
Фрукты |
200 |
5 |
26 |
Яблоки печеные |
4 |
Фрукты |
160 |
3 |
27 |
Суфле яблочное |
4 |
Фрукты |
220 |
6 |
28 |
Крем творожный |
4 |
Молоко |
160 |
4 |
29 |
"Утро" |
5 |
Фрукты |
200 |
5 |
30 |
Компот |
5 |
Фрукты |
200 |
2 |
31 |
Молочный напиток |
5 |
Молоко |
200 |
2 |
32 |
Кофе черный |
5 |
Кофе |
200 |
1 |
33 |
Кофе на молоке |
5 |
Кофе |
200 |
2 |
Таблица Состав.
Блюдо |
Продукт |
Вес |
Блюдо |
Продукт |
Вес |
Блюдо |
Продукт |
Вес |
1 |
4 |
15 |
9 |
1 |
80 |
20 |
16 |
10 |
1 |
11 |
100 |
9 |
3 |
15 |
21 |
3 |
20 |
1 |
12 |
5 |
9 |
10 |
30 |
21 |
5 |
20 |
1 |
15 |
80 |
9 |
11 |
25 |
21 |
6 |
30 |
2 |
1 |
65 |
9 |
12 |
15 |
21 |
13 |
70 |
2 |
4 |
20 |
9 |
13 |
35 |
21 |
16 |
15 |
2 |
5 |
20 |
10 |
2 |
70 |
22 |
5 |
8 |
2 |
9 |
40 |
10 |
3 |
20 |
22 |
6 |
30 |
2 |
11 |
35 |
10 |
7 |
250 |
22 |
8 |
140 |
2 |
12 |
20 |
10 |
12 |
5 |
22 |
16 |
15 |
3 |
6 |
50 |
10 |
14 |
15 |
23 |
3 |
20 |
3 |
10 |
15 |
11 |
2 |
100 |
23 |
10 |
65 |
3 |
11 |
55 |
11 |
3 |
5 |
23 |
11 |
250 |
3 |
12 |
20 |
11 |
9 |
20 |
24 |
3 |
10 |
3 |
15 |
55 |
11 |
10 |
20 |
24 |
5 |
40 |
3 |
16 |
5 |
11 |
12 |
2 |
24 |
6 |
30 |
4 |
2 |
50 |
12 |
3 |
5 |
24 |
7 |
100 |
4 |
4 |
40 |
12 |
7 |
350 |
24 |
8 |
80 |
4 |
5 |
20 |
12 |
13 |
35 |
24 |
14 |
10 |
4 |
9 |
35 |
12 |
16 |
5 |
24 |
16 |
20 |
4 |
11 |
50 |
13 |
1 |
180 |
25 |
3 |
20 |
4 |
12 |
5 |
13 |
3 |
5 |
25 |
8 |
20 |
5 |
2 |
80 |
13 |
10 |
40 |
25 |
14 |
30 |
5 |
3 |
25 |
13 |
11 |
100 |
25 |
15 |
120 |
5 |
9 |
40 |
13 |
12 |
20 |
25 |
16 |
35 |
5 |
12 |
5 |
14 |
1 |
90 |
26 |
3 |
2 |
6 |
1 |
80 |
14 |
3 |
5 |
26 |
15 |
150 |
6 |
4 |
30 |
14 |
6 |
20 |
26 |
16 |
20 |
6 |
11 |
150 |
14 |
7 |
50 |
27 |
3 |
2 |
6 |
12 |
10 |
14 |
10 |
10 |
27 |
5 |
80 |
7 |
6 |
125 |
14 |
12 |
5 |
27 |
7 |
150 |
7 |
16 |
15 |
14 |
14 |
3 |
27 |
15 |
50 |
8 |
6 |
50 |
15 |
2 |
100 |
27 |
16 |
35 |
8 |
8 |
75 |
15 |
3 |
20 |
28 |
3 |
10 |
8 |
16 |
15 |
15 |
5 |
20 |
28 |
5 |
20 |
15 |
12 |
5 |
15 |
9 |
20 |
28 |
6 |
20 |
16 |
3 |
5 |
15 |
10 |
10 |
28 |
8 |
100 |
16 |
5 |
120 |
18 |
8 |
140 |
28 |
16 |
15 |
16 |
6 |
15 |
18 |
14 |
15 |
29 |
9 |
200 |
16 |
7 |
35 |
18 |
16 |
15 |
29 |
15 |
150 |
16 |
14 |
9 |
19 |
3 |
15 |
29 |
16 |
15 |
17 |
3 |
20 |
19 |
5 |
120 |
30 |
15 |
70 |
17 |
7 |
50 |
19 |
7 |
45 |
30 |
16 |
10 |
17 |
9 |
150 |
19 |
10 |
20 |
31 |
7 |
150 |
17 |
12 |
10 |
20 |
3 |
5 |
31 |
15 |
150 |
17 |
13 |
25 |
20 |
7 |
75 |
31 |
16 |
25 |
17 |
14 |
5 |
20 |
13 |
50 |
32 |
17 |
8 |
18 |
5 |
10 |
20 |
14 |
20 |
33 |
7 |
75 |
18 |
6 |
30 |
20 |
15 |
75 |
33 |
16 |
25 |
Таблица Наличие. Таблица Меню.
Продукт |
Количество |
Цена |
1 |
108 |
429,84 |
2 |
0 |
0,00 |
3 |
73 |
274,61 |
4 |
39 |
97,46 |
5 |
61 |
111,83 |
6 |
88 |
206,60 |
7 |
214 |
83,80 |
8 |
92 |
82,80 |
9 |
0 |
0,00 |
10 |
77 |
46,30 |
11 |
46 |
51,70 |
12 |
13 |
34,96 |
13 |
54 |
51,17 |
14 |
91 |
43,77 |
15 |
117 |
189,92 |
16 |
98 |
96,14 |
17
|
37 |
166,50 |
Блюдо |
Вид |
Дата | |
1 |
1 |
2011-01-02 | |
2 |
1 |
2011-01-02 | |
2 |
1 |
2011-01-03 | |
4 |
1 |
2011-01-03 | |
9 |
2 |
2011-01-02 | |
10 |
2 |
2011-01-03 | |
12 |
2 |
2011-01-02 | |
14 |
3 |
2011-01-02 | |
15 |
3 |
2011-01-02 | |
15 |
3 |
2011-01-03 | |
16 |
3 |
2011-01-03 | |
25 |
4 |
2011-01-03 | |
27 |
4 |
2011-01-02 | |
30 |
5 |
2011-01-03 | |
33 |
5 |
2011-01-02 | |
… |
|
|
Таблица Заказ. Таблица Справочник_вид_блюда.
ID_заказ |
Блюдо |
Количество_порций |
Дата |
1 |
1 |
10 |
2011-01-02 |
1 |
14 |
4 |
2011-01-02 |
1 |
15 |
6 |
2011-01-02 |
2 |
27 |
2 |
2011-01-02 |
2 |
33 |
2 |
2011-01-02 |
3 |
2 |
3 |
2011-01-03 |
3 |
15 |
2 |
2011-01-03 |
3 |
16 |
1 |
2011-01-03 |
4 |
25 |
1 |
2011-01-03 |
5 |
4 |
3 |
2011-01-03 |
… |
|
|
|
ID_вид |
Вид |
1 |
Закуска |
2 |
Суп |
3 |
Горячее |
4 |
Десерт |
5 |
Напиток |