- •Глава 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.3.1. Соединения «с условием where».
Вообще, соединения - это подмножества декартова произведения. Так как декартово произведение n таблиц - это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, ... и строки из n-й таблицы (а мы уже научились выделять с помощью SELECT любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц, надо указать в предложении FROM перечень перемножаемых таблиц, а в предложении SELECT – все их столбцы.
Пример 17.
Предположим нам необходимо получить состав продуктов для каждого блюда.
Информация для этого запроса хранится в таблицах Блюда, Состав и Продукты. Для получения декартова произведения таблиц Блюда(n-строк), Состав (m-строк) и Продукты (k - строк) надо написать запрос
SELECT Блюда.*, Состав.*, Продукты.*
FROM Блюда, Состав, Продукты;
Получим таблицу, содержащую (n × m × k) строк:
ID_блюда |
Блюдо |
Вид |
Основа |
Вес |
Труд |
Блюдо |
Продукт |
Вес |
ID_продукта |
Продукт |
Белки |
Жиры |
Углеводы |
К |
Са |
Na |
B2 |
PP |
C |
1 |
Салат летний |
1 |
Овощи |
200 |
3 |
1 |
4 |
15 |
1 |
Говядина |
189 |
124 |
NULL |
3150 |
90 |
600 |
1,5 |
28 |
0 |
1 |
Салат летний |
1 |
Овощи |
200 |
3 |
1 |
11 |
100 |
1 |
Говядина |
189 |
124 |
NULL |
3150 |
90 |
600 |
1,5 |
28 |
0 |
1 |
Салат летний |
1 |
Овощи |
200 |
3 |
1 |
12 |
5 |
1 |
Говядина |
189 |
124 |
NULL |
3150 |
90 |
600 |
1,5 |
28 |
0 |
1 |
Салат летний |
1 |
Овощи |
200 |
3 |
1 |
15 |
80 |
1 |
Говядина |
189 |
124 |
NULL |
3150 |
90 |
600 |
1,5 |
28 |
0 |
… |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Результат далек от нужного, так как содержит лишние строки. Если из декартова произведения убрать ненужные строки и столбцы, то можно получить актуальные таблицы, соответствующие операций соединение.
Очевидно, что отбор актуальных строк обеспечивается вводом в запрос фразы WHERE, в которой устанавливается соответствие между кодами блюд в таблицах Блюда и Состав (Блюда.ID_Блюда = Состав.Блюдо) и кодами продуктов в таблицах Состав и Продукты (Состав.Продукт = Продукты.ID_Продукта).
Такой скорректированный запрос имеет вид:
SELECT Блюда.Блюдо, Продукты.Продукт, Состав.Вес
FROM Блюда, Состав, Продукты
WHERE Блюда.ID_Блюда = Состав.Блюдо
and Состав.Продукт = Продукты.ID_Продукта;
Запрос позволяет получить эквисоединение таблиц (соединение при равенстве значений столбцов) Блюда, Состав и Продукты. В строке SELECT указаны лишь те столбцы, которые необходимы в результате:
Результат |
|
|
Блюдо |
Продукт |
Вес |
Салат летний |
Майонез |
15 |
Салат летний |
Помидоры |
100 |
Салат летний |
Зелень |
5 |
Салат летний |
Яблоки |
80 |
Салат мясной |
Говядина |
65 |
Салат мясной |
Майонез |
20 |
Салат мясной |
Яйца |
20 |
Салат мясной |
Морковь |
40 |
Салат мясной |
Помидоры |
35 |
Салат мясной |
Зелень |
20 |
Салат витаминный |
Сметана |
50 |
Салат витаминный |
Лук |
15 |
Салат витаминный |
Помидоры |
55 |
Салат витаминный |
Зелень |
20 |
… |
|
|
Рассмотренный вид соединения является внутренним, так как исключает несовпадающие по значению строки. То есть, если в БД занесено блюдо, но для него ещё не указан состав, то информация о таком блюде не появится в результирующей таблице, а иногда такая информация необходима.
Решить эту проблему помогают внешние соединения. Для получения внешнего соединения применяют один из трех подходов:
Использование подзапроса с предикатом EXISTS
Объединение двух запросов с помощью UNION
Применение специальной конструкции JOIN в предложении FROM
Все они будут рассмотрены далее.
Следует отметить, что в предложении SELECT доступны все поля соединённых таблиц, даже если они не используются в запросе. Каждое имя поля уточняется с помощью точечной нотации, при которой имя поля следует через точку за именем таблицы. Такое уточнение необходимо, когда одно имя поля в запросе встречается более одного раза. (Конечно, эти поля будут находиться в разных таблицах, ведь невозможно создать два поля с одинаковыми именами в одной таблице.) Если не идентифицировать однозначно эти поля, то получим синтаксическую ошибку о неопределённости имён. Уточнение имён необходимо производить вне зависимости от того, ссылается ли запрос на оба поля или нет — каждая ссылка должна быть уточнена.
Когда поле в запросе встречается всего один раз, уточнение имён становится необязательным. Поэтому, мы могли получить такой же результат, воспользовавшись следующим кодом:
SELECT Блюда.Блюдо, Продукты.Продукт, остав.Вес
FROM Блюда, Состав, Продукты
WHERE ID_Блюда = Состав.Блюдо
and Состав.Продукт = ID_Продукта;
Тем не менее, указывать полные имена полей — это хорошая практика, поскольку иначе, взглянув на запрос, вы не всегда сможете сказать, к какой таблице относится каждое поле.
В некотором смысле, такой запрос будет самодокументированным — выполняемые им действия очевидны, поэтому его легче описать в документации.
Псевдонимы таблиц
Другой способ задания имён полей — это использование псевдонимов таблиц. Псевдоним — это альтернативное имя, присвоенное таблице в запросе. Как правило, псевдоним короче реального имени таблицы. Вот как наше соединение записывается с помощью псевдонимов:
SELECT b.Блюдо, p.Продукт, s.Вес
FROM Блюда b, Состав s, Продукты p
WHERE b.ID_Блюда = s.Блюдо and s.Продукт = p.ID_Продукта;
Здесь, таблице Блюда присвоен псевдоним b, таблице Состав — псевдоним s, а таблице Продукты – псевдоним p. Вы можете использовать в качестве псевдонима любые имена, псевдонимы создаются лишь на время выполнения запроса. Многие программисты стараются использовать однобуквенные псевдонимы по мере возможности, поскольку так уменьшается объём кода и повышается его читаемость. Единственная тонкость состоит в том, что как только вы определили псевдоним для таблицы, то в текущем запросе уже не можете использовать реальное имя таблицы, можно указывать только её псевдоним. Псевдонимы действуют только на протяжении одного запроса.