Лекция_5

1.

ВВЕДЕНИЕ В БАЗЫ ДАННЫХ
ЛЕКЦИЯ № 5
Преподаватель: Гладкова Екатерина Сергеевна
Должность: инженер-программист 1 категории
Департамент: Разработка аналитических систем
Подразделение: Группа разработки веб АЦР

2. Повторение. Базовый синтаксис SQL запроса. Получение диапазона строк

ПОВТОРЕНИЕ. БАЗОВЫЙ СИНТАКСИС
SQL ЗАПРОСА. ПОЛУЧЕНИЕ ДИАПАЗОНА СТРОК
Оператор LIMIT позволяет извлечь определенное количество строк:
SELECT *
FROM divisions
ORDER BY division_name
LIMIT 5
Оператор OFFSET указывает, сколько строк с начала выборки нужно
пропустить:
SELECT *
FROM divisions
ORDER BY division_name
LIMIT 3 OFFSET 2
Если надо выбрать вообще все строки, пропуская несколько первых, то
оператор LIMIT можно опустить:
SELECT *
FROM divisions
ORDER BY division_name
OFFSET 2
после LIMIT указать ключевое слово ALL:
SELECT *
FROM divisions
ORDER BY division_name
LIMIT ALL OFFSET 2
2

3.

Домашнее задание из Лекции № 3
(написать запрос самостоятельно на бумаге):
3. Выведите все столбцы из таблицы Должности
(posts), у которых зарплата (salary) от 50000 до
80000. Отсортируйте результат по зарплате и
выведите только первые 5 строк.
3

4. Домашнее Задание. Решение

ДОМАШНЕЕ ЗАДАНИЕ. РЕШЕНИЕ
3. SELECT *
FROM posts
WHERE salary BETWEEN 50000 AND 80000
ORDER BY salary
LIMIT 5
4

5. Соединение таблиц внутреннее соединение (INNER JOIN)

СОЕДИНЕНИЕ ТАБЛИЦ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN)
Нередко возникает ситуация, когда нам надо получить данные из
нескольких таблиц. Для соединения данных из разных таблиц можно
использовать оператор INNER JOIN или JOIN. Он представляет так
называемое внутреннее соединение. Его формальный синтаксис:
SELECT столбцы
FROM таблица1
JOIN таблица2 ON условие1
После оператора JOIN идет название второй таблицы, данные которой надо
добавить в выборку. Далее после ключевого слова ON указывается условие
соединения. Это условие устанавливает, как две таблицы будут сравниваться. Как
правило, для соединения применяется первичный ключ одной таблицы и внешний
ключ другой таблицы.
В результате остаются только те строки, для которых нашлось соответствие.
5

6. Соединение таблиц. ПРИМЕР

СОЕДИНЕНИЕ ТАБЛИЦ. ПРИМЕР
Рассмотрим подробный пример как вывести список сотрудников
(employees) с названиями занимаемых ими должностей (posts), используя
внутреннее соединение таблиц (INNER JOIN):
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
post_id
SERIAL PRIMARY KEY
patronymic
VARCHAR (30)
post_name
VARCHAR (80)
birthdate
DATE
salary
INTEGER
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
posts
6

7. Соединение таблиц. ПРИМЕР

СОЕДИНЕНИЕ ТАБЛИЦ. ПРИМЕР
В качестве Таблицы 1 возьмем таблицу Сотрудники (employees):
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
patronymic
VARCHAR (30)
birthdate
DATE
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
Таблица 1
7

8. Соединение таблиц. ПРИМЕР

СОЕДИНЕНИЕ ТАБЛИЦ. ПРИМЕР
В качестве Таблицы 2 возьмем таблицу Должности (posts):
posts
post_id
SERIAL PRIMARY KEY
post_name
VARCHAR (80)
salary
INTEGER
Таблица 2
8

9. Соединение таблиц. ПРИМЕР

СОЕДИНЕНИЕ ТАБЛИЦ. ПРИМЕР
Выведем все столбцы обеих таблиц, используя
внутреннее соединение таблиц (INNER JOIN)
Сотрудники (employees) и Должности (posts) по условию
совпадения идентификатора должности (post_id):
INNER JOIN
(внутреннее соединение)
9

10. Соединение таблиц. ПРИМЕР

СОЕДИНЕНИЕ ТАБЛИЦ. ПРИМЕР
INNER JOIN
(внутреннее соединение)
Таблица 1
10

11. Соединение таблиц. ПРИМЕР

СОЕДИНЕНИЕ ТАБЛИЦ. ПРИМЕР
INNER JOIN
(внутреннее соединение)
Таблица 2
11

12. Соединение таблиц. ПРИМЕР

СОЕДИНЕНИЕ ТАБЛИЦ. ПРИМЕР
INNER JOIN
(внутреннее соединение)
Совпадение
12

13. Лекция 5. Тренировка

ЛЕКЦИЯ 5. ТРЕНИРОВКА
4.0 Попробуйте самостоятельно соединить таблицы
Подразделения (divisions) и Департаменты (departments),
с выводом всех столбцов обоих таблиц.
divisions
division_id
SERIAL PRIMARY KEY
division_name
VARCHAR (80)
division_head
INTEGER
department_id
INTEGER
departments
department_id
SERIAL PRIMARY KEY
department_name VARCHAR (80)
department_head
INTEGER
13

14. Соединение таблиц внутреннее соединение (INNER JOIN)

СОЕДИНЕНИЕ ТАБЛИЦ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN)
Возьмем таблицы Сотрудники (employees) и Подразделения (divisions):
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
division_id
SERIAL PRIMARY KEY
patronymic
VARCHAR (30)
division_name
VARCHAR (80)
birthdate
DATE
division_head
INTEGER
post_id
INTEGER
department_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
divisions
Используя JOIN, выберем фамилии (last_name) всех сотрудников и добавим к
ним названия подразделений (division_name) в которых они работают:
SELECT employees.last_name, divisions.division_name
FROM employees
JOIN divisions ON divisions.division_id = employees.division_id
Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании
столбцов для выборки указывается их полное имя вместе с именем таблицы.
14

15. Соединение таблиц внутреннее соединение (INNER JOIN)

СОЕДИНЕНИЕ ТАБЛИЦ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN)
SELECT employees.last_name, divisions.division_name
FROM employees
JOIN divisions ON employees.division_id = divisions.division_id
Запрос выполняется следующим образом:
Для каждой строки из таблицы Сотрудники (employees) отбираются строки из
таблицы Подразделения (divisions) по условию, что внешний ключ подразделения
из таблицы Сотрудники (employees.division_id) соответствует первичному ключуидентификатору из таблицы Подразделения (divisions.division_id).
В результирующей выборке получается столько строк, сколько строк нашлось
в таблицах по условиям соединения.
15

16. Соединение таблиц внутреннее соединение (INNER JOIN)

СОЕДИНЕНИЕ ТАБЛИЦ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN)
SELECT employees.last_name, divisions.division_name
FROM employees
JOIN divisions ON divisions.division_id = employees.division_id
Порядок ключей в условии ON не имеет значения.
Результат:
16

17. Соединение таблиц Псевдонимы таблиц

СОЕДИНЕНИЕ ТАБЛИЦ
ПСЕВДОНИМЫ ТАБЛИЦ
В условиях соединения ON имя таблицы обычно не пишут, т.к. читать длинные
строки кода весьма утомительно. Подобно псевдонимам столбцов в списке
выборки SELECT можно задать псевдонимы таблицам. Для этого после
названия таблицы через пробел нужно написать псевдоним таблицы. После
того, как таблице назначен псевдоним, обращаться к ней по названию уже
нельзя - только по псевдониму.
Перепишем предыдущий запрос с использованием псевдонимов для таблиц. Было:
SELECT employees.last_name, divisions.division_name
FROM employees
JOIN divisions ON divisions.division_id = employees.division_id
стало:
SELECT e.last_name, div.division_name
FROM employees e
JOIN divisions div ON div.division_id = e.division_id
Часто таблицам дают псевдонимы по первым буквам слов из названия таблицы.
17

18. Лекция 5. Тренировка

ЛЕКЦИЯ 5. ТРЕНИРОВКА
4.1 Выведите столбец с именами подразделений
(division_name) из таблицы Подразделения (divisions) и столбец
с именами департаментов (department_name), в которые
входят подразделения, из таблицы Департаменты
(departments). Отсортируйте выборку по возрастанию имен
подразделений и ограничьте вывод первыми 10 записями.
SELECT
FROM
JOIN … ON
ORDER BY
LIMIT
divisions
division_id
SERIAL PRIMARY KEY
division_name
VARCHAR (80)
division_head
INTEGER
department_id
INTEGER
departments
department_id
SERIAL PRIMARY KEY
department_name VARCHAR (80)
department_head
INTEGER
18

19. Лекция 5. Тренировка. Решение

ЛЕКЦИЯ 5. ТРЕНИРОВКА. РЕШЕНИЕ
4.1 SELECT division_name, department_name
FROM divisions div
JOIN departments dep ON dep.department_id =
div.department_id
ORDER BY division_name
LIMIT 10
19

20. Лекция 5. Тренировка

ЛЕКЦИЯ 5. ТРЕНИРОВКА
4.2 Выведите ФИО руководителей подразделений в одном
столбце (конкатенация полей last_name, first_name и patronymic
с разделителями- пробелами) из таблицы Сотрудники
(employees) и столбец с именами подразделений (division_name)
из таблицы Подразделения (divisions), которыми они руководят.
Отсортируйте выборку по фамилиям.
SELECT
employees
FROM
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
patronymic
VARCHAR (30)
birthdate
DATE
division_id
SERIAL PRIMARY KEY
post
VARCHAR (80)
division_name
VARCHAR (80)
office_phone
VARCHAR (30)
division_head
INTEGER
division_id
INTEGER
department_id
INTEGER
JOIN … ON
ORDER BY
divisions
20

21. Лекция 5. Тренировка. Решение

ЛЕКЦИЯ 5. ТРЕНИРОВКА. РЕШЕНИЕ
4.2 SELECT last_name || ' ' || first_name || ' ' ||
patronymic, division_name
FROM employees e
JOIN divisions div ON div.division_head =
e.employee_id
ORDER BY last_name
21

22. Соединение таблиц

СОЕДИНЕНИЕ ТАБЛИЦ
Принцип различных типов соединений наглядно отражен на схеме:
INNER JOIN
(внутреннее соединение)
LEFT JOIN
(левое внешнее соединение)
RIGHT JOIN
(правое внешнее соединение)
FULL JOIN
(полное внешнее соединение)
22

23. Соединение таблиц внешнее соединение (OUTER JOIN)

СОЕДИНЕНИЕ ТАБЛИЦ
ВНЕШНЕЕ СОЕДИНЕНИЕ (OUTER JOIN)
OUTER JOIN или внешнее соединение позволяет возвратить все строки одной
или двух таблиц, которые участвуют в соединении. Его формальный синтаксис:
SELECT столбцы
FROM таблица1
{LEFT|RIGHT|FULL} JOIN таблица2 ON условие1
Перед оператором JOIN указывается одно из ключевых слов, которое
определяет тип соединения:
LEFT: выборка будет содержать все строки из первой или левой таблицы
RIGHT: выборка будет содержать все строки из второй или правой таблицы
FULL: выборка будет содержать все строки из обеих таблиц
Перед оператором JOIN может указываться ключевое слово OUTER, но его
применение необязательно. После JOIN указывается присоединяемая таблица,
а затем идет условие соединения после оператора ON.
23

24. Соединение таблиц внешнее соединение. LEFT JOIN

СОЕДИНЕНИЕ ТАБЛИЦ
ВНЕШНЕЕ СОЕДИНЕНИЕ. LEFT JOIN
LEFT JOIN – левое внешнее соединение. Работает как JOIN, но если для
строки таблицы, находящейся по левую сторону ключевого слова LEFT JOIN,
не нашлось ни одной строки в таблице, находящейся по правую сторону
LEFT JOIN, то строка все равно добавляется в результат, а значения
столбцов правой таблицы равны null.
Перепишем запрос из второго домашнего задания с использованием
LEFT JOIN:
SELECT e.last_name || ' ' || e.first_name || ' ' || e.patronymic, div.division_name
FROM employees e
LEFT JOIN divisions div ON div.division_head = e.employee_id
ORDER BY e.last_name
24

25. Соединение таблиц внешнее соединение. LEFT JOIN

СОЕДИНЕНИЕ ТАБЛИЦ
ВНЕШНЕЕ СОЕДИНЕНИЕ. LEFT JOIN
SELECT e.last_name || ' ' || e.first_name || ' ' || e.patronymic, div.division_name
FROM employees e
LEFT JOIN divisions div ON div.division_head = e.employee_id
ORDER BY e.last_name
Результат:
25

26. Соединение таблиц внешнее соединение. RIGHT JOIN

СОЕДИНЕНИЕ ТАБЛИЦ
ВНЕШНЕЕ СОЕДИНЕНИЕ. RIGHT JOIN
RIGHT JOIN – правое внешнее соединение. Работает так: если для строки
таблицы, находящейся по правую сторону ключевого слова RIGHT JOIN, не
нашлось ни одной строки в таблице, находящейся по левую сторону RIGHT
JOIN, то строка все равно добавляется в результат, а значения столбцов
левой таблицы равны null.
Запрос с LEFT JOIN:
SELECT e.last_name || ' ' || e.first_name || ' ' || e.patronymic, div.division_name
FROM employees e
LEFT JOIN divisions div ON div.division_head = e.employee_id
ORDER BY e.last_name
можно легко переписать на RIGHT JOIN, просто поменяв таблицы местами:
SELECT e.last_name || ' ' || e.first_name || ' ' || e.patronymic, div.division_name
FROM divisions div
RIGHT JOIN employees e ON div.division_head = e.employee_id
ORDER BY e.last_name
Результат запроса останется прежним
26

27. Соединение таблиц внешнее соединение. LEFT или RIGHT JOIN

СОЕДИНЕНИЕ ТАБЛИЦ
ВНЕШНЕЕ СОЕДИНЕНИЕ. LEFT ИЛИ RIGHT JOIN
На практике RIGHT JOIN почти не используется, так как LEFT JOIN
воспринимается в запросах гораздо проще (если в предложении FROM
сначала идет основная таблица, от которой строится логика получения
остальных данных).
LEFT JOIN
(левое внешнее соединение)
RIGHT JOIN
(правое внешнее соединение)
Более понятный для восприятия
Редко используется
27

28. Соединение таблиц внешнее соединение. FULL JOIN

СОЕДИНЕНИЕ ТАБЛИЦ
ВНЕШНЕЕ СОЕДИНЕНИЕ. FULL JOIN
FULL JOIN – полное внешнее соединение. Если для какой-либо из
таблиц не нашлось строки в другой таблице, то строка все равно попадает в
результат, а значения столбцов другой таблицы равны null.
Рассмотрим как работает FULL JOIN на примере. Пусть у нас есть две
таблицы:
table1
table2
id_1 value_1
id_2 value_2
1
One
2
Two
2
Two
3
Three
3
Three
4
Four
5
Five
28

29. Соединение таблиц внешнее соединение. FULL JOIN

СОЕДИНЕНИЕ ТАБЛИЦ
ВНЕШНЕЕ СОЕДИНЕНИЕ. FULL JOIN
На этих данных выполним запрос:
SELECT *
FROM table1 t1
FULL JOIN table2 t2 ON t1.id_1 = t2. id_2
ORDER BY id_1, id_2
Результат:
id_1 value_1
table1
table2
id_1 value_1
id_2 value_2
1
One
2
Two
2
Two
3
Three
3
Three
4
Four
5
Five
id_2 value_2
1
One
2
Two
2
Two
3
Three
3
Three
4
Four
5
Five
29

30. Соединение таблиц

СОЕДИНЕНИЕ ТАБЛИЦ
Принцип различных типов соединений наглядно отражен на схеме:
INNER JOIN
(внутреннее соединение)
LEFT JOIN
(левое внешнее соединение)
RIGHT JOIN
(правое внешнее соединение)
FULL JOIN
(полное внешнее соединение)
30

31. Лекция 5. Тренировка

ЛЕКЦИЯ 5. ТРЕНИРОВКА
• Выполните все известные соединения таблицы
Сотрудники (employees) и таблицы Департаменты
(departments) и посмотрите как меняется выборка.
employees
employee_id
SERIAL PRIMARY KEY
last_name
VARCHAR (30)
first_name
VARCHAR (30)
department_id
patronymic
VARCHAR (30)
department_name VARCHAR (80)
birthdate
DATE
department_head
post_id
INTEGER
office_phone
VARCHAR (30)
division_id
INTEGER
departments
SERIAL PRIMARY KEY
INTEGER
SELECT
FROM
JOIN / LEFT JOIN / RIGHT JOIN / FULL JOIN … ON
ORDER BY
31