Как мы уже обсуждали, mySQL относится к реляционным базам данных. Реляционные БД характеризуются наличием таблиц и отношений.
Существует 3 типа отношений:
- "Один к одному" - когда одна запись в первой таблице соответствует одной записи во второй таблице. Встречается такая связь довольно редко. Такая связь либо избыточна, т.е. может иметь смысл просто объединить данные в одну таблицу, либо это результат модернизации архитектуры, и такое решение кем-то принято обосновано.
- "Один ко многим" - когда одной записи в первой таблице соответствует несколько записей в другой таблице. К примеру, у клиента магазина может быть несколько номеров. Один клиент - одна запись в таблице клиента, его номера телефонов - записи в таблице телефонов. Один клиент относится ко многим номерам телефонов, но обратная связь - номер телефона к клиенту - многие к одному.
- "Многое ко многим" - когда одной записи в первой таблице соответствует несколько записей во второй таблице, но одной записи второй таблицы может соответствовать несколько записей первой таблицы. Классический пример - книги и авторы, ведь у автора может быть много книг, и у каждой книги может быть несколько авторов. Реализуется за счет создания таблицы связей, куда копируются ключи записей обеих таблиц.
Запросы к одной таблице довольно редки. Чаще всего запросы к базам данных пишуться с целью получить информацию из нескольких таблиц, информация из которых объединяется по определенным условиям.
Создадим таблицы авторов, книг, жанров и таблицу связей для авторов и книг (многие ко многим):
CREATE TABLE authors (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL DEFAULT 'people',
year DATE NOT NULL DEFAULT '1970-01-01'
);
CREATE TABLE books (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL DEFAULT 'noname',
genre_id INT NOT NULL DEFAULT '0'
);
CREATE TABLE genres (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
genre VARCHAR(100) NOT NULL DEFAULT 'unknown'
);
CREATE TABLE authors_books (
author_id INT NOT NULL DEFAULT 0,
book_id INT NOT NULL DEFAULT 0
);Далее следует заполнить таблицу данными:
INSERT INTO genres (genre) VALUES
('SF'),
('novel'),
('story'),
('horror');
INSERT INTO books(title, genre_id) VALUES
("Мастер и Маргарита", 2),
("Фауст", 0),
("Белый клык", 3),
("Дюна", 1),
("Война и мир", 2);
INSERT INTO authors (name) VALUES
('Френк Герберт'),
('Михаил Булгаков'),
('Ждек Лондон'),
('Иоган Гёте'),
('Роберт Хайнлайн');
INSERT INTO authors_books (author_id, book_id) VALUES
(1, 4),
(2, 1),
(3, 3),
(4, 2);
Данные готовы, теперь можно изучать объединения таблиц. Начнем мы с объединения двух таблиц в одном запросе:
SELECT
title,
genre
FROM
books
INNER JOIN genres ON (books.genre_id = genre.id);+------------------------------------+-------+
| title | genre |
+------------------------------------+-------+
| Мастер и Маргарита | novel |
| Белый клык | story |
| Дюна | SF |
| Война и мир | novel |
+------------------------------------+-------+
INNER JOIN выводит записи из левой таблицы (первой из двух таблиц, которые он объединяет), для которых найдется соответствующая запись в правой (второй и последней в списке) таблице. Если соответствия в правой таблице нет, такая запись не выводится. В данном случае можно видеть, что запись о книге "Фауст" не выводится. Это происходит из-за того, что genre_id у этой записи равен нулю, а такого жанра в таблице жанров нет.
Так же в результат не попадает жанр horror, так как нет ни единой книги с таким жанром.
SELECT
title,
genre
FROM
books
LEFT JOIN genres ON (books.genre_id = genre.id);+------------------------------------+-------+
| title | genre |
+------------------------------------+-------+
| Мастер и Маргарита | novel |
| Фауст | NULL |
| Белый клык | story |
| Дюна | SF |
| War and Peace | novel |
+------------------------------------+-------+
LEFT JOIN выводит ВСЕ записи из левой таблицы. Для тех записей, которым находится соответствие в правой, он, аналогично INNER JOIN-у, выведет соответствующие данные из второй таблицы. Для тех же, которым соответствия не нашлось, он выведет в столбцах правой таблицы NULL.
RIGHT JOIN поступает аналогичным образом с правой таблицей: выводит все записи из нее, добавляя записи из левой. Где соответствия не находится, добавляет в столбцах левой таблицы NULL.
SELECT
title,
genre
FROM
books
RIGHT JOIN genres ON (books.genre_id = genre.id);+------------------------------------+--------+
| title | genre |
+------------------------------------+--------+
| Мастер и Маргарита | novel |
| Белый клык | story |
| Дюна | SF |
| War and Peace | novel |
| NULL | horror |
+------------------------------------+--------+
Если у вас возникнет необходимость увидеть все книги и все жанры вне зависимости от наличия соответствующих записей в другой таблице, но где связи есть - связать таблицы, можно объединить результаты двух запросов:
SELECT
title,
genre
FROM
books
LEFT JOIN genres ON (books.genre_id = genre.id)
UNION
SELECT
title,
genre
FROM
books
RIGHT JOIN genres ON (books.genre_id = genre.id);+------------------------------------+--------+
| title | genre |
+------------------------------------+--------+
| Мастер и Маргарита | novel |
| Фауст | NULL |
| Белый клык | story |
| Дюна | SF |
| War and Peace | novel |
| NULL | horror |
+------------------------------------+--------+
Классический вопрос, как посмотреть книги, у которых нет жанра, решается просто:
SELECT
title,
genre
FROM
books
LEFT JOIN genres ON (books.genre_id = genre.id)
WHERE
genre IS NULL;+------------+-------+
| title | genre |
+------------+-------+
| Фауст | NULL |
+------------+-------+
Если поля для связи называются одинаково, например, поле id в таблице genres называется genre_id, запрос можно немного упростить. Для демонстрации переименуем поле и выполним этот запрос:
ALTER TABLE
genres
CHANGE
id genre_id INT NOT NULL;
SELECT
title,
genre
FROM
books
LEFT JOIN genres USING(genre_id);Результат будет тот же, что и у обычного LEFT JOIN запроса, но сам запрос короче.
- Объединить авторов с книгами посредством таблицы связей
- Освоить ключевое слово DISTINCT
- Построить фамильное древо по мужской линии