Объединение двух таблиц

В предыдущих главах в запросах SELECT работали с одной таблицей. Однако в реляционных базах данных имеется возможность извлекать информацию из нескольких таблиц. Проанализировав таблицы БД prestupnost, вы поняли, что можете выполнять более интересные запросы. Например, получать информацию о преступлениях и совершивших их преступников. В данном разделе речь пойдет о выполнении таких запросов путем объединения двух таблиц.

В работе используется только один синтаксис объединения, хотя их намного больше. В каждом случае предоставляется свой способ объединения данных из нескольких таблиц. Рассмотрим выражение INNER JOIN, которое является самым простым для изучения. Принцип его работы будет объяснен после следующего примера (рис. 5.9):

mysql> SELECT gruppirovka_name, prestup-nik_name

  • -> FROM gruppirovka INNER JOIN prestupnik
  • -> USING (gruppirovka_id);

! gruppirouka_name ! prestupnik_name

I Solnceuskaya

! Solnceuskaya

! Solnceuskaya

! Solnceuskaya

' Potapouskaya

i Potapouskaya

! Aleksandrouskaya

! Aleksandrouskaya

I Aleksandrouskaya

' Matueeuskaya

i Matueeuskaya

! Solopouskaya

! Solopouskaya

I Seuernaya

' Seuernaya

i Andreeu Sergey Ruslanouich ! ! Krauchenko Petr luanouich ! ! luashkin luan Kuzmich ! ! Petrikin Uasiliy Sergeeuich ! ! Anisimou Uitaliy Abramouich ! ! Gouorou luan luanouich ! I Getrou luan Gennadieuich ! ! Kabenou Gamshut Petrouich ! I Makeeu Gennadiy Petrouich I ! Petrou luan Sidorouich ! ! Belou Aleksandr Petrouich ! ! Azizou Baden Pigoreuich ! ! Kopica luan Petrouich ! I Balashou Semen Paulouich I ' Petlyakou Maksim luanouich !

15 rows in set <0.00 sec)

Рис. 5.9. Принцип работы INNER JOIN

В результатах показаны группировки и преступники в них входящие, а именно, сколько преступников входит в каждую группировку.

Как работает выражение INNER JOIN? Оно состоит из двух частей: в первой задаются имена двух таблиц, разделенные ключевыми словами INNER JOIN, а вторая часть содержит ключевое слово USING, указывающее, какой столбец (или столбцы) связывает две таблицы. В данном примере объединены две таблицы, gruppirovka и prestupnik, что определено выражением gruppirovka INNER JOIN prestupnik (для базовой формы INNER JOIN порядок перечисления таблиц не имеет значения, так что выражение prestupnik INNER JOIN gruppirovka выдаст такой же результат). Выражение USING (gruppirovka_id) указывает MySQL, что столбцом, связывающим таблицы, является gruppirovka_id, со-58

гласно проекту. Из таблицы gruppirovka получаем следующие данные (рис. 5.10):

mysql> SELECT * FROM gruppirovka;

! gruppirouka_id ? gruppirouka_name !

I 1 i Solnceuskaya I ! 2 ! Potapouskaya ! ! 3 i Aleksandrovskaya ! ! 4 ? Matueeuskaya ! ! 5 i Solopouskaya ! ! 6 ! Seuernaya !

rows in set <0.00 sec)

Рис. 5.10. Выражение USING

Данные из таблицы prestupnik такие (рис. 5.11):

mysql> SELECT

*

FROM prestupnik;

----------------+---------------+-

! gruppirovka_id i prestupnik_id !

-----------------------------+----------------------------f.

prestupnikлапе ! prestupnik_god_rozhdeniya !

1 1 !

1 1

Andreev Sergey Ruslanovich ?

1959 !

: i :

2 :

Kravchenko Petr Ivanovich 1

1981 1

I 1 !

3 :

Ivashkin Ivan Kuzmich !

1964 :

: i :

4 !

Petrikin Uasiliy Sergeevich !

195? :

: 2 !

i :

Anisimov Uitaliy Abramovich 1

1972 1

: 2 :

2 !

Govorov Ivan Ivanovich !

1968 !

: з :

i :

Getrov Ivan Gennadievich 1

1973 I

I 3 !

2 I

Kabenov Gamshut Petrovich !

1983 :

: 4 :

i :

Petrov Ivan Sidorovich !

1969 :

: 4 !

2 :

Belov Aleksandr Petrovich !

1977 i

: 5 :

1 :

Azizov Baden Pigorevich 1

1981 i

: 5 :

2 :

Kopica Ivan Petrovich 1

1966 I

i 6 :

i :

Balashov Semen Pavlovich !

1987 I

: s :

2 :

Petlyakov Maksim Ivanovich !

1977 :

: з !

з :

Makeev Gennadiy Petrovich !

1985 i

L5 rows in set <0.00 sec)

Рис. 5.11. Данные из таблицы «преступник»

В ответ на этот запрос MySQL определит пары строк из таблицы gruppirovka и prestupnik с одними и теми же значениями gruppirovka_id. Для каждого значения gruppirovka_id в таблице gruppirovka (используем одно в качестве примера, рис. 5.12).

! gruppirouka_id ! gruppirouka_name ! +----------------+------------------+ ! 1 ! Solnceuskaya i +----------------+------------------+

1 row in set <0.00 sec)

Рис. 5.12. Пример запроса с одними и теми же значениями

Сервер найдет в таблице prestupnik все записи со значением artist_id = 7 (рис. 5.13).

I gruppirovka_id I prestupnik_id I prestupnik jane I prestupnik_god_rozhdeniya I +---------------+---------------+---------------------------+---------------------------+ I II II Andreev Sergey Ruslanovich I 1959 I I II 21 Kravchenko Petr Ivanovich I 1981 I I II 31 Ivashkin Ivan Kuzmich I 1964 I I II 41 Petrikin Uasiliy Sergeevich I 195? I +----------------+---------------+-----------------------------+---------------------------+ 4 rows in set <0.00 sec)

Рис. 5.13. Запрос всех записей со значением Id 1

После этого сервер может сформировать из этих двух набо-ров новую временную таблицу (рис. 5.14)._____________________

gruppirouka_id I gruppirovkajiame I prestupnik_id I prestupnikjiame I prestupnik_god_rozhdeniya

  • ----4-----4-----4-------4
  • 1 I Solncevskaya I 1 I Andreev Sergey Ruslanovich I 1959
  • 1 I Solncevskaya I 2 I Kravchenko Petr Ivanovich I 1981
  • 1 I Solncevskaya I 3 I Ivashkin Ivan Kuzmich I 1964
  • 1 I Solncevskaya I 4 I Petrikin Uasiliy Sergeevich I 195?
  • ----------------4------------------+---------------4-----------------------------4---------------------------4 rows in set <0.00 sec)

Рис. 5.14. Временная таблица из двух наборов

По завершении обработки всех различных значений gruppi-rovka_id сервер выбирает для отображения столбцы, которые

запрашивали, в данном prestupnik_name (рис. 5.15).

случае gruppirovka_name и

! gruppirovka_name ! prestupnik_name ! +------------------+-----------------------------+ ! Solncevskaya ! Andreev Sergey Ruslanovich ! ! Solncevskaya ! Kravchenko Petr Ivanovich ! ! Solncevskaya ! Ivashkin Ivan Kuzmich ! ! Solncevskaya ! Petrikin Uasiliy Sergeevich !

4 rows in set

Рис. 5.15. Запрашиваемые столбцы

Перед тем как использовать базовый синтаксис выражения INNER JOIN, следует ознакомиться со следующими правилами.

  • • Он работает только в том случае, когда две таблицы совместно используют столбец с одним именем в качестве условия объединения. В противном случае нужно применять альтернативный синтаксис. Отметим, что MySQL не может автоматически определять столбец, желаемый для использования для объединения (даже если в двух таблицах присутствуют столбцы с одним и тем же именем), так что его следует задавать явным образом.
  • • В результатах отображаются строки, где объединяющий столбец (или столбцы) совпадает в обеих таблицах. Строки из одной таблицы, для которых нет соответствия в другой таблице, игнорируются.
  • • Не стоит забывать о выражении USING. В MySQL оно не является обязательным, но если его пропустить, то результаты будут бессмысленными, поскольку получим декартово произведение, т. е. каждой записи в одной таблице будет поставлена в соответствие каждая запись из другой таблицы.
  • • Столбец (или столбцы), указанный после выражения USING, должен быть заключен в круглые скобки. В случае объединения по нескольким столбцам нужно разделить их имена запятыми.

Если следовать этим правилам, то выполнение объединений с помощью выражения INNER JOIN не вызовет никаких проблем. Теперь рассмотрим еще несколько примеров.

Предположим, нужно перечислить преступников и их преступления. Путем анализа таблиц prestupnik и prestuplenie определим, что объединение следует выполнить по двум столбцам gruppirovka_id и prestupnik_id. Проведем эту операцию объединения (рис. 5.16):

mysql> SELECT prestupnik_name, prestuple-nie_name

  • -> FROM prestupnik INNER JOIN prestuplenie
  • -> USING (gruppirovka_id, prestupnik_id) LIMIT 15;

1. . ,1

? prestupnik_name !

1 pre s t uple n ie _n ame ?

! Andreev Sergey Ruslanovich 1

* Andreeu Sergey Ruslanovich !

! Kravchenko Petr Ivanovich !

! Ivashkin Ivan Kuzmich !

! Petrikin Uasiliy Sergeevich !

I Petrikin Uasiliy Sergeevich !

! Petrikin Uasiliy Sergeevich !

' Petrikin Uasiliy Sergeevich

! Anisimov Uitaliy Abramovich

! Govorov Ivan Ivanovich

' Govorov Ivan Ivanovich

' Getrov Ivan Gennadievich

! Getrov Ivan Gennadievich

! Kabenov Gamshut Petrovich

! Petrov Ivan Sidorovich

  • 1 Razboynoe napodenie !
  • 1 Razboy ?

! Ubiystvo '

! Ograblenie magazina !

! Moshennichestvo !

! Razboynoe napadenie !

! Kvartirnaya krazha !

! Ubiystvo i

! Uooruzhennoe napadenie na sotrudnika policii !

! Kvartirnaya krazha !

  • 1 Razboynoe napadenie !
  • 1 Ugon avtomobilya !

! Ugon avtomobilya !

! Skupka ugnannih avtomobileq !

1 Moshennichestvo !

15 rows in set <0.00 sec>

Рис. 5.16. Объединение по двум столбцам

В выражении USING указаны два объединяющих столбца, разделенных запятыми: USING (gruppirovka_id, prestupnik_id). В результате показаны преступники и преступления, которые они совершили. Для экономии места ограничим выходные данные 15 строками с помощью выражения LIMIT, описанного в разделе «Выражение LIMIT».

Усовершенствуем предыдущий пример путем добавления выражения ORDER BY. Имеет смысл отобразить преступников в алфавитном порядке, а преступления — в том порядке, в котором они совершались, так что модифицируем предыдущий запрос следующим образом (рис. 5.17):

mysql> SELECT prestupnik_name, prestuplenie_name

  • -> FROM prestupnik INNER 30IN prestuplenie
  • -> USING (gruppirovka_id, prestupnik_id)
  • -> ORDER BY prestupnik_name,
  • ->prestuplenie_id LIMIT 15;_______________________

! prestupnik_name !

i---------------------------------+

1 prestuplenie_name !

' Andreeu Sergey Ruslanovich !

! Andreev Sergey Ruslanovich !

! Anisimov Uitaliy Abramovich !

i Azizov Baden Pigorevich !

' Balashov Semen Pavlovich !

' Balashov Semen Pavlovich

! Belov Aleksandr Petrovich 1

! Belov Aleksandr Petrovich !

! Getrov Ivan Gennadievich

' Getrov Ivan Gennadievich

! Govorov Ivan Ivanovich !

! Govorov Ivan Ivanovich

! Ivashkin Ivan Kuzmich

! Kabenov Gamshut Petrovich 1

! Kopica Ivan Petrovich 1

1 Razboynoe napodenie !

I Razboy !

  • 1 Uooruzhennoe napadenie na sotrudnika policii !
  • 1 Ubiystvo !
  • 1 Razboynoe napadenie '

I Iznasilovanie !

I Moshennichestvo !

  • 1 Ograblenie banka !
  • 1 Ugon avtomobilya !
  • 1 Ugon avtomobilya !
  • 1 Kvartirnaya krazha !
  • 1 Razboynoe napadenie !
  • 1 Ograblenie magazina !
  • 1 Skupka ugnannih avtomobileq !
  • 1 Ubiystvo !

15 rows in set <0.00 sec>

Рис. 5.17. Объединение выражения ORDER BY

Выражение ORDER BY, что стоит в запросе после условия объединения, сортирует преступников и преступления в требуемом порядке.

Теперь попробуем выполнить другой запрос. Предположим, что требуется определить, в какой преступной группировке состоит самый пожилой преступник. Это можно сделать путем объединения таблиц gruppirovka (содержащей поле gruppi-rovka_name) и prestupnik (содержащей поле prestup-nik_god_rozhdeniya) используя в условии объединения столбцы поле gruppirovka_id. Тогда запрос будет выглядеть так (рис. 5.18):

mysql> SELECT gruppirovka_name? prestupnikjiame.,

  • -> prestupnik_god_rozhdeniya FROM
  • -> gruppirovka INNER JOIN prestupnik USING (gruppi-rovka_id)
  • -> ORDER BY prestupnik_god_rozhdeniya LIMIT 1;

gruppirovka_name ? prestupnik_name 1 prestupnik_god_rozhdeniya i Solncevskaya ! Petrikin Uasiliy Sergeevich ! 195? ! row in set <0.00 sec)

Рис. 5.18. Определение самого пожилого преступника

Произвели сортировку по полю prestupnik_god_rozhdeniya, где находится год рождения преступников, и ограничили выборку одной записью.

Необходимо знать еще об одной функции, которая используется для агрегирования значений. Предположим, что требуется определить какое количество лет осуждены преступники входящие в состав Солнцевской преступной группировки (Solncevskaya). Это можно сделать путем суммирования звучания отдельных столбцов с помощью функции SUM() из SQL (рис. 5.19);

mysql> SELECT SUM(prestuplenie_srok) FROM

  • -> gruppirovka INNER JOIN prestuplenie
  • -> USING (gruppirovka_id)
  • -> WHERE gruppirovka_id=l;

! SUM ! <•------------------------+ ! 47.ШШ ! +------------------------+

L row in set <О.ШШ sec)

Рис. 5.19. Суммирование отдельных столбцов

Таким образом, искомое значение равно 47 годам. Функция SUM() определяет сумму всех значений столбца, заключенного в круглые скобки (в данном случае это столбец prestuplenie_srok/ а не отдельных значений. Поскольку использовалось выражение WHERE для выбора строк лишь для группировки «Solncevskaya», суммой значений «prestuplenie_srok» будет искомая величина.

Контрольные вопросы

  • 1. Какое выражение языка SQL организует сортировку данных?
  • 2. Как организовать сортировку по алфавиту и в обратном порядке? К полям каких типов применяется сортировка?
  • 3. С помощью какого выражения языка SQL ограничивается выборка? Какова структура выражения LIMIT?
  • 4. С помощью какого оператора организуется объединение таблиц?
  • 5. Назначение выражения USING. Можно ли объединять больше 2 таблиц в одном запросе?
 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ ОРИГИНАЛ   След >