Лекция13

1. Лекция 13 Соединение и объединение таблиц. Встроенные функции.

Технология разработки и защиты баз данных
Преподаватель: Сынкова Алина Денисовна

2. Упражнение: MERGE

Абитуриенты подавали заявления для поступления в ВУЗ. Их информация
вносилась в БД приемной комиссии в таблицу Абитуриенты. Затем
проводился отбор поступивших в две волны. Все поступившие находятся в
таблице Поступившие. Поступившие в первой волне уже внесены в таблицу
Студенты БД ВУЗА.
ID абитуриента везде одинаковый. Из Абитуриентов данные не удаляются.
Заполните таблицы тестовыми данными. Подавало заявления 5
абитуриентов. 3 из них поступили, один в первой волне (уже в Студентах),
двое во второй. Поступишему в первой волне недавно сменили номер
группы в таблице Поступившие, в Студентах он остался старым.
Ваша задача с помощью команды MERGE перенести данные поступивших
в таблицу Студенты. Используйте в качестве SOURCE запрос к таблицам
Поступившие и Абитуриенты. Если данные о поступившем ученике уже
есть в таблице Студенты - обновите всю информацию о нем из SOURCE.
2

3.

3

4. Объединение таблиц и запросов. UNION

Результаты выполнения SQL запросов можно объединять. Для этого
существует оператор UNION.
Общая структура запроса с оператором UNION:
SELECT поля_таблиц FROM список_таблиц ...
UNION [ALL]
SELECT поля_таблиц FROM список_таблиц ... ;
4

5.

UNION по умолчанию убирает повторения в результирующей
таблице. Для отображения с повторением есть необязательный
параметр ALL.
Не путайте операции объединения запросов с операциями
объединения таблиц. Для этого служит оператор JOIN.
Не путайте операции объединения запросов с подзапросами.
Подзапросы выполняются для связанных таблиц.
5

6. Особенности UNION

Объединение таблиц оператором UNION выполняется для
таблиц никак не связанных, но со схожей структурой.
Для того, чтобы UNION корректно сработал нужно: чтобы
результирующие таблицы каждого из SQL запросов имели
одинаковое число столбцов, с одним и тем же типом данных и в
той же самой последовательности.
6

7. INTERSECT и EXCEPT

Существует два других оператора, чьё поведение крайне схоже с
UNION:
INTERSECT комбинирует два запроса SELECT, но возвращает
записи только первого SELECT, которые имеют совпадения во
втором элементе SELECT.
EXCEPT комбинирует два запроса SELECT, но возвращает
записи только первого SELECT, которые не имеют совпадения
во втором элементе SELECT.
…Да, это объединение, пересечение и вычитание из теории
множеств и реляционной алгебры.
7

8. Неявное соединение таблиц

Одним из способов соединения таблиц в запросе является неявное соединение.
Оно производится через перечисления таблиц в FROM через запятую:
SELECT поля_таблиц
FROM таблица_1, таблица_2
Без условия WHERE результатом будет декартово произведение. Для связи:
SELECT поля_таблиц
FROM таблица_1, таблица_2
WHERE таблица_1.поле_1=таблица_2.поле_2 [AND
другие_условия]
8

9. JOIN. Явное соединение

Основной способ реализации многотабличных запросов.
SELECT поля_таблиц
FROM таблица_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN
таблица_2
ON условие_соединения
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN
таблица_n
ON условие_соединения]
9

10. INNER JOIN. Внутреннее соединение

Внутреннее соединение — соединение, при котором находятся пары записей
из двух таблиц, только полностью удовлетворяющие условию соединения, тем
самым образуя новую таблицу, содержащую поля из первой и второй
исходных таблиц.
SELECT поля_таблиц
FROM таблица_1
[INNER] JOIN таблица_2
ON условие_соединения
[INNER] JOIN таблица_n
ON условие_соединения]
10

11. Пример

SELECT Orders.CreatedAt, Customers.FirstName,
Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
JOIN Customers ON Customers.Id=Orders.CustomerId
WHERE Products.Price < 45000
ORDER BY Customers.FirstName
11

12. ON

ON не обязательно включает в себя только сравнение столбцов:
SELECT Orders.CreatedAt, Customers.FirstName,
Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId AND
Products.Manufacturer='Apple'
JOIN Customers ON Customers.Id=Orders.CustomerId
ORDER BY Customers.FirstName
12

13. OUTER JOIN. Внешнее соединение

Внешнее соединение может быть трёх типов:
LEFT: выборка будет содержать все строки из первой (левой) таблицы
RIGHT: выборка будет содержать все строки из второй (правой) таблицы
FULL: выборка будет содержать все строки из обоих таблиц
Главным отличием внешнего соединения от внутреннего является то, что оно
обязательно возвращает все строки одной (LEFT, RIGHT) или двух таблиц (FULL).
SELECT столбцы
FROM таблица1
{LEFT|RIGHT|FULL} [OUTER] JOIN таблица2 ON условие1
[{LEFT|RIGHT|FULL} [OUTER] JOIN таблица3 ON условие2]
13

14. LEFT OUTER JOIN. Внешнее левое соединение

Соединение, которое возвращает все значения из левой таблицы,
соединённые с соответствующими значениями из правой таблицы, если они
удовлетворяют условию соединения, или заменяет их на NULL в обратном
случае.
Пример:
SELECT FirstName, CreatedAt, ProductCount, Price
FROM Customers LEFT JOIN Orders
ON Orders.CustomerId = Customers.Id
14

15. RIGHT OUTER JOIN. Внешнее правое соединение

Соединение, которое возвращает все значения из правой
таблицы, соединённые с соответствующими значениями из
левой таблицы, если они удовлетворяют условию соединения,
или заменяет их на NULL в обратном случае.
15

16. FULL OUTER JOIN. Внешнее полное соединение

Соединение, которое выполняет внутреннее соединение записей
и дополняет их левым внешним соединением и правым
внешним соединением.
Алгоритм работы полного соединения:
1. Формируется таблица на основе внутреннего соединения
(INNER JOIN)
2. В таблицу добавляются значения не вошедшие в результат
формирования из левой таблицы (LEFT OUTER JOIN)
3. В таблицу добавляются значения не вошедшие в результат
формирования из правой таблицы (RIGHT OUTER JOIN)
Соединение FULL JOIN реализовано не во всех СУБД.
16

17. Встроенные функции

В SQL Server множество различных встроенных функций, которые делятся на
следующие основные группы:
Агрегатные функции
Строковые функции
Математические функции
Функции для работы с датой и временем
Функции преобразования
Логические функции
Ранжирующие функции
И др.
Рассмотрим некоторые из них.
17

18. Условная логика

SQL, подобно многим языкам программирования, позволяет
писать условную логику, чтобы в зависимости от набора условий
возвращать одно из множества возможных значений. Для этого
используются операторы CASE и IF.
Обратите внимание! Данные операторы в обычном SQL и T-SQL
(непосредственно язык программирования) немного
отличаются.
18

19. CASE

Функция CASE проверяет значение некоторого выражение, и в зависимости от
результата проверки может возвращать тот или иной результат.
CASE принимает следующие формы:
CASE выражение
WHEN значение_1 THEN результат_1
WHEN значение_2 THEN результат_2
.................................
WHEN значение_N THEN результат_N
[ELSE альтернативный_результат]
END
CASE
WHEN выражение_1 THEN результат_1
WHEN выражение_2 THEN результат_2
.................................
WHEN выражение_N THEN результат_N
[ELSE альтернативный_результат]
END
19

20. Пример

20

21.

SELECT name,
SELECT name,
CASE
CASE SUBSTRING(name, 1,
INSTR(name, ' '))
WHEN SUBSTRING(name, 1,
INSTR(name, ' ')) IN (10, 11) THEN
"Старшая школа"
WHEN 11 THEN "Старшая школа"
WHEN 10 THEN "Старшая школа"
WHEN SUBSTRING(name, 1,
INSTR(name, ' ')) IN (5, 6, 7, 8, 9)
THEN "Средняя школа"
WHEN 9 THEN "Средняя школа"
ELSE "Начальная школа"
WHEN 7 THEN "Средняя школа"
END AS stage
WHEN 6 THEN "Средняя школа"
FROM Class
WHEN 5 THEN "Средняя школа"
WHEN 8 THEN "Средняя школа"
ELSE "Начальная школа"
END AS stage
FROM Class
21

22. IIF

Функция IIF в зависимости от результата условного выражения возвращает
одно из двух значений. Общая форма функции выглядит следующим образом:
IIF(условие, значение_1, значение_2)
Пример:
SELECT ProductName, Manufacturer,
IIF(ProductCount>3, 'Много товара', 'Мало товара')
FROM Products
22

23. NEWID

Для генерации объекта UNIQUEIDENTIFIER, то есть некоторого уникального
значения, используется функция NEWID().
Например, мы можем определить для столбца первичного ключа тип
UNIQUEIDENTIFIER и по умолчанию присваивать ему значение функции
NEWID:
CREATE TABLE Clients
(
Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Phone NVARCHAR(20) NULL,
Email NVARCHAR(20) NULL
)
23

24. IFNULL и NULLIF

Помимо функции IF, в SQL также есть более простые, но менее универсальные
функции IFNULL и NULLIF, направленные на обработку NULL значений.
Синтаксис IFNULL:
IFNULL(значение, альтернативное_значение);
Функция IFNULL возвращает значение, переданное первым аргументом, если
оно не равно NULL, иначе возвращает альтернативное_значение.
Синтаксис NULLIF
NULLIF(значение_1, значение_2);
Функция NULLIF возвращает NULL, если значение_1 равно значению_2, в
противном случае возвращает значение_1.
24

25. COALESCE

COALESCE
Функция COALESCE принимает список значений и возвращает первое из них,
которое не равно NULL:
COALESCE(выражение_1, выражение_2, выражение_N)
Например, выберем из таблицы Clients пользователей и в контактах у них
определим либо телефон, либо электронный адрес, если они не равны NULL:
SELECT FirstName, LastName,
COALESCE(Phone, Email, 'не определено') AS
Contacts
FROM Clients
25

26. Преобразование данных

SQL Server может выполнять неявные преобразования от типа с
меньшим приоритетом к типу с большим приоритетом. Таблица
приоритетов (чем выше, тем больший приоритет):
1.datetime
2.smalldatetime
3.float
4.real
5.decimal
6.money
7.smallmoney
8.int
9.smallint
10.tinyint
11.bit
12.nvarchar
13.nchar
14.varchar
15.char
То есть SQL Server автоматически может преобразовать число 100.0
(float) в дату и время (datetime).
26

27. Явное преобразование

В тех случаях, когда необходимо выполнить преобразования от типов с
высшим приоритетом к типам с низшим приоритетом, то надо выполнять
явное приведение типов. Для этого в T-SQL определены две функции:
CONVERT и CAST.
Функция CAST преобразует выражение одного типа к другому. Она имеет
следующую форму:
CAST(выражение AS тип_данных)
Большую часть преобразований охватывает функция CAST. Если же
необходимо какое-то дополнительное форматирование, то можно
использовать функцию CONVERT. Она имеет следующую форму:
CONVERT(тип_данных, выражение [, стиль])
27

28. Стили

Стили для форматирования даты и
времени:
0 или 100 - формат даты "Mon dd yyyy
hh:miAM/PM" (значение по умолчанию)
1 или 101 - формат даты "mm/dd/yyyy"
3 или 103 - формат даты "dd/mm/yyyy"
7 или 107 - формат даты "Mon dd, yyyy
hh:miAM/PM"
8 или 108 - формат даты "hh:mi:ss"
времени)
Некоторые стили для форматирования
данных типа money в строку:
0 - в дробной части числа остаются
только две цифры (по умолчанию)
1 - в дробной части числа остаются
только две цифры, а для разделения
разрядов применяется запятая
2 - в дробной части числа остаются
только четыре цифры
10 или 110 - формат даты "mm-dd-yyyy"
14 или 114 - формат даты
"hh:mi:ss:mmmm" (24-часовой формат
28

29. Дополнительные функции преобразования

STR(float [, length [,decimal]]): преобразует число в строку. Второй
параметр указывает на длину строки, а третий - сколько знаков в дробной
части числа надо оставлять
CHAR(int): преобразует числовой код ASCII в символ. Нередко
используется для тех ситуаций, когда необходим символ, который нельзя
ввести с клавиатуры
ASCII(char): преобразует символ в числовой код ASCII
NCHAR(int): преобразует числовой код UNICODE в символ
UNICODE(char): преобразует символ в числовой код UNICODE
29