• доступно о веб-разработке
20.11.2012 Базы Данных, Технологии

Пересечение таблиц

Пост из серии «Спрашивали? Отвечаем!». Иногда бывает реальная необходимость взять информацию из нескольких таблиц сразу. Сразу скажу, чтобы вы запомнили. Не стоит злоупотреблять сложными запросами. И сами запутаетесь, и других разработчиков введете в заблуждение. Лучше сделать два простых запроса, чем один сложный. Или структуру базы данных делать особым образом, но еще не время об этом говорить. Готовы узнать про пересечение таблиц в базе данных?

Знаю, вы любите примеры. Учиться на примерах понятнее и доступнее, чем когда одну теорию выкладывают.

Итак, типичная ситуация. Вас интересуют две таблицы — посты и пользователи.

Давайте упростим её для простоты понимания.

Поля таблицы с постами — id, user_id (идентификатор автора), subject(заголовок статьи),body(содержимое статьи).

В таблице с пользователями у нас поля — id, login(имя пользователя).

Давайте откроем phpMyAdmin и создадим эти таблицы:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `login` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `user_id` int(11) unsigned NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Обратите внимание, что поле id практически всегда  имеет свойство auto_increment. То есть при добавлении новой записи оно используется как счётчик.

Важно, чтобы поля, по которым пересекаются таблицы, совпадали по типу.

Если вы уже достаточно хорошо изучили типы данных в базах, то знаете, что unsigned означает, что в данном поле может храниться только положительное число.

Об этом более подробно написано в официальной документации:

http://www.mysql.ru/docs/man/Column_types.html

[box type=»info»] INT[(M)] [UNSIGNED] [ZEROFILL] Целое число нормального размера. Диапазон со знаком от -2147483648 до 2147483647. Диапазон без знака от 0 до 4294967295.[/box]

Что делать, если нам необходимо в результат получить запись, которая получается от скрещивания этих двух таблиц?

Смотрите пример:

SELECT * FROM posts JOIN users ON users.id = posts.user_id

Что мы получим в результате этого запроса? Пока ничего.

Давайте вставим в таблицу тестовые записи:

--
-- Дамп данных таблицы `posts`
--

INSERT INTO `posts` (`id`, `user_id`, `subject`, `body`) VALUES
(1, 1, 'Test', 'Test body'),
(2, 2, 'Test 2', 'Test 2 body');

--
-- Дамп данных таблицы `users`
--

INSERT INTO `users` (`id`, `login`) VALUES
(1, 'codemotion'),
(2, 'CuamckuyKot');

Если вы не знаете, как вставлять эти команды. Посмотрите видеоурок по работе с phpMyAdmin.

Снова отправляем наш запрос и получаем ответ:

Как видите, мы получили слитую воедино из двух таблицу с данными.
Обратите внимание, что некоторые поля дублируются, что может привести к неразберихе.

Какой? А давайте попробуем отсортировать наш запрос:

SELECT * 
FROM posts
JOIN users ON users.id = posts.user_id
ORDER BY id DESC

Ой! Ошибочка вышла:

Почему? Учитесь читать ошибки. Все же написано прямым текстом — поле id выглядит амбиционзно.

То есть оно есть в двух таблицах, и сервер не знает по какой из них сортировать.

Мы можем исправить ситуацию следующим образом:

SELECT * 
FROM posts
JOIN users ON users.id = posts.user_id
ORDER BY posts.id DESC

Вот теперь все слава Богу!

Но что делать, если нам нужны не все поля второй таблицы, а только поле login?

Указывайте нужные таблицы с полям и и вперед!

SELECT posts.*, users.login 
FROM posts
JOIN users ON users.id = posts.user_id
ORDER BY posts.id DESC

Хотите задать особые имена граф? Пожалуйста! (Не обязательно при JOIN-запросе, можно в любое время)

SELECT posts.body as text, users.login as name 
FROM posts
JOIN users ON users.id = posts.user_id
ORDER BY posts.id DESC

Ожидаемый результат:

Это основы.

Для расширения кругозора даю несколько интересных ссылок:

http://ru.wikipedia.org/wiki/Join_(SQL) Здесь много про типы JOIN-ов. Перекрещивать таблицы можно по-разному.

http://anton-pribora.ru/articles/mysql/mysql-join/ Развернутая статья с примерами.

http://habrahabr.ru/post/44807/ Сложные примеры с картинками. Для опытных.

http://habrahabr.ru/post/122210/ Производительность JOIN-ов при больших объемах данных.

Задание простое — проделайте все примеры из данной статьи.

Поделиться

Комментарии Правила дискуссии

Читайте ранее:
Безопасность при работе с базами данных

Вы должны понимать, что вся суть сайтов заключается в получении и обработке информации от пользователей с последующей выдачей результатов. Данные,...

Закрыть