1. Тип подключения к нескольким таблицам
1. Декартово произведение (перекрёстное соединение)В MySQL это может быть CROSS JOIN или опустить CROSS или JOIN, или использовать ',', например:
SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2
SELECT * FROM users CROSS JOIN articles;
SELECT * FROM users JOIN articles;
SELECT * FROM users, articles;
Поскольку возвращаемый результат является произведением двух связанных таблиц данных, обычно не рекомендуется использовать его при наличии условий WHERE, ON или USING, потому что при слишком большом количестве элементов таблицы данных это будет очень медленно. Обычно используйте LEFT [OUTER] JOIN или RIGHT [OUTER] JOIN.
2. Внутреннее соединение INNER JOINВ MySQL ВНУТРЕННЕЕ СОЕДИНЕНИЕ называется равноправным соединением, то есть необходимо указать условия равноправного соединения, в MySQL CROSS и ВНУТРЕННЕЕ СОЕДИНЕНИЕ делятся вместе.
SELECT * FROM users as u INNER JOIN articles as a where u.id = a.user_id
3. Внешние соединения в MySQLОно делится на левое внешнее соединение и правое соединение, то есть помимо возврата результатов, удовлетворяющих условиям соединения, оно также возвращает результаты, не соответствующие условиям соединения в левой таблице (left join) или правой таблице ( правильное соединение).
пример:таблица пользователей:
+----+----------+----------+--------------------+
| id | username | password | email |
+----+----------+----------+--------------------+
| 1 | junxi | 123 | xinlei3166@126.com |
| 2 | tangtang | 456 | xinlei3166@126.com |
| 3 | ceshi3 | 456 | ceshi3@11.com |
| 4 | ceshi4 | 456 | ceshi4@qq.com |
| 5 | ceshi3 | 456 | ceshi3@11.com |
| 6 | ceshi4 | 456 | ceshi4@qq.com |
| 7 | ceshi3 | 456 | ceshi3@11.com |
| 8 | ceshi4 | 456 | ceshi4@qq.com |
| 9 | ceshi3 | 333 | ceshi3@11.com |
| 10 | ceshi4 | 444 | ceshi4@qq.com |
| 11 | ceshi3 | 333 | ceshi3@11.com |
| 12 | ceshi4 | 444 | ceshi4@qq.com |
+----+----------+----------+--------------------+
таблица пользовательской информации:
+----+-------+--------+-------------+----------------+---------+
| id | name | qq | phone | link | user_id |
+----+-------+--------+-------------+----------------+---------+
| 1 | 君惜 | 666666 | 16616555188 | www.junxi.site | 1 |
| 2 | 糖糖 | 777777 | 17717555177 | www.weizhi.com | 2 |
| 3 | 测试3 | 333333 | 13313333177 | www.ceshi3.com | 3 |
+----+-------+--------+-------------+----------------+---------+
Оператор SQL:
SELECT * FROM users as u LEFT JOIN userinfos as i on u.id = i.user_id;
Результаты:
+----+----------+----------+--------------------+------+-------+--------+-------------+----------------+---------+
| id | username | password | email | id | name | qq | phone | link | user_id |
+----+----------+----------+--------------------+------+-------+--------+-------------+----------------+---------+
| 1 | junxi | 123 | xinlei3166@126.com | 1 | 君惜 | 666666 | 16616555188 | www.junxi.site | 1 |
| 2 | tangtang | 456 | xinlei3166@126.com | 2 | 糖糖 | 777777 | 17717555177 | www.weizhi.com | 2 |
| 3 | ceshi3 | 456 | ceshi3@11.com | 3 | 测试3 | 333333 | 13313333177 | www.ceshi3.com | 3 |
| 4 | ceshi4 | 456 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | ceshi3 | 456 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | ceshi4 | 456 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 7 | ceshi3 | 456 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 8 | ceshi4 | 456 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 9 | ceshi3 | 333 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 10 | ceshi4 | 444 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 11 | ceshi3 | 333 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 12 | ceshi4 | 444 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
+----+----------+----------+--------------------+------+-------+--------+-------------+----------------+---------+
анализировать: Пользователи с идентификатором больше 3 в таблице пользователей не имеют соответствующих записей в userinfos, но отображаются в наборе результатов. Поскольку сейчас используется левое соединение, вся работа основана на левом соединении. Результаты 1, 2 и 3 являются записями как в левой, так и в правой таблицах, а 4, 5, 6, 7, 8, 9, 10, 11 и 12 являются записями только в левой, а не в правой таблице.
Принцип работы: Прочитайте одну из левой таблицы, выберите все записи правой таблицы (n), которые совпадают, чтобы сформировать n записей (включая повторяющиеся строки), если справа нет таблицы, которая соответствует условию on, связанные поля все равно null. Затем перейдите к следующему элементу. Расширение: Мы можем использовать правило отображения нуля, если правая таблица не имеет соответствия, чтобы узнать все записи в левой таблице, но не в правой таблице.Обратите внимание, что столбец, используемый для суждения, должен быть объявлен не нулевым. Такие как: SQL: (Уведомление: 1. Если значение столбца равно null, вы должны использовать is null вместо =NULL. 2. Здесь столбец i.user_id должен быть объявлен NOT NULL. )
SELECT * FROM users as u LEFT JOIN userinfos as i on u.id = i.user_id WHERE i.user_id is NULL;
Результаты:
+----+----------+----------+---------------+------+------+------+-------+------+---------+
| id | username | password | email | id | name | qq | phone | link | user_id |
+----+----------+----------+---------------+------+------+------+-------+------+---------+
| 4 | ceshi4 | 456 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | ceshi3 | 456 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 6 | ceshi4 | 456 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 7 | ceshi3 | 456 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 8 | ceshi4 | 456 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 9 | ceshi3 | 333 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 10 | ceshi4 | 444 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 11 | ceshi3 | 333 | ceshi3@11.com | NULL | NULL | NULL | NULL | NULL | NULL |
| 12 | ceshi4 | 444 | ceshi4@qq.com | NULL | NULL | NULL | NULL | NULL | NULL |
+----+----------+----------+---------------+------+------+------+-------+------+---------+
Общее использование: А. ЛЕВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ: Помимо возврата результатов, удовлетворяющих условиям подключения, также необходимо отображать в левой таблице столбцы данных, не соответствующие условиям подключения, соответствующие NULL
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
Б. ПРАВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ: Разница между RIGHT и LEFT JOIN заключается в том, что в дополнение к отображению результатов, соответствующих условиям соединения, также необходимо отображать столбцы данных в правой таблице, которые не соответствуют условиям соединения, и использовать NULL соответственно.
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
Tips:
- на a.c1 = b.c1 эквивалентно использованию (c1)
- INNER JOIN и , (запятая) семантически эквивалентны
- Когда MySQL извлекает информацию из таблицы, вы можете подсказать ей, какой индекс выбрать. Эта функция полезна, если EXPLAIN показывает, что MySQL использует неправильный индекс из списка возможных индексов. Указав USE INDEX (key_list), вы указываете MySQL использовать наиболее подходящий из возможных индексов для поиска строк в таблице. Необязательный двоичный синтаксис IGNORE INDEX (key_list) может использоваться, чтобы указать MySQL не использовать определенный индекс. Такие как:
mysql> SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
Во-вторых, ограничения объединения таблиц
Добавить условие отображения ГДЕ, ВКЛ, ИСПОЛЬЗОВАНИЕ
1. ГДЕ пункт
SELECT * FROM table1,table2 WHERE table1.id=table2.id;
SELECT * FROM users, userinfos WHERE users.id=userinfos.user_id;
2. ON
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
SELECT * FROM users LEFT JOIN articles ON users.id = articles.user_id;
SELECT * FROM users LEFT JOIN userinfos ON users.id = userinfos.user_id LEFT JOIN articles ON users.id = articles.user_id;
3. Предложение USING, если два столбца условия соединения двух соединяемых таблиц имеют одно и то же имя, можно использовать USING.
Например:
SELECT FROM LEFT JOIN USING ()
Пример объединения более двух таблиц:
SELECT users.username, userinfos.name, articles.title FROM users LEFT JOIN userinfos ON users.id = userinfos.user_id LEFT JOIN articles ON users.id = articles.user_id;
Результаты:
mysql> SELECT users.username, userinfos.name, articles.title FROM users LEFT JOIN userinfos ON users.id = userinfos.user_id LEFT JOIN articles ON users.id = articles.user_id;
+----------+-------+------------+
| username | name | title |
+----------+-------+------------+
| junxi | 君惜 | 中国有嘻哈 |
| tangtang | 糖糖 | 星光大道 |
| ceshi3 | 测试3 | 平凡的真谛 |
| junxi | 君惜 | python进阶 |
| ceshi3 | NULL | NULL |
| ceshi3 | NULL | NULL |
или
SELECT users.username, userinfos.name, articles.title FROM users LEFT JOIN userinfos ON users.id = userinfos.user_id LEFT JOIN articles ON users.id = articles.user_id WHERE (articles.user_id IS NOT NULL AND userinfos.user_id IS NOT NULL);
Результаты:
mysql> SELECT users.username, userinfos.name, articles.title FROM users LEFT JOIN userinfos ON users.id = userinfos.user_id LEFT JOIN articles ON users.id = articles.user_id WHERE (articles.user_id IS NOT NULL AND userinfos.user_
id IS NOT NULL);
+----------+-------+------------+
| username | name | title |
+----------+-------+------------+
| junxi | 君惜 | 中国有嘻哈 |
| tangtang | 糖糖 | 星光大道 |
| ceshi3 | 测试3 | 平凡的真谛 |
| junxi | 君惜 | python进阶 |
+----------+-------+------------+
или
SELECT users.username, userinfos.name, articles.title FROM users LEFT JOIN userinfos ON users.id = userinfos.user_id LEFT JOIN articles ON users.id = articles.user_id WHERE (userinfos.name = '君惜');
Результаты:
mysql> SELECT users.username, userinfos.name, articles.title FROM users LEFT JOIN userinfos ON users.id = userinfos.user_id LEFT JOIN articles ON users.id = articles.user_id WHERE (userinfos.name = '君惜');
+----------+------+------------+
| username | name | title |
+----------+------+------------+
| junxi | 君惜 | 中国有嘻哈 |
| junxi | 君惜 | python进阶 |
+----------+------+------------+
Еще один момент, который следует отметитьКогда в MySQL задействован многотабличный запрос, необходимо решить, какой метод подключения использовать более эффективно в зависимости от ситуации с запросом.
- Перекрестное соединение (декартово произведение) или внутреннее соединение [INNER | CROSS] JOIN
- Левое внешнее соединение LEFT [OUTER] JOIN или правое внешнее соединение RIGHT [OUTER] JOIN Обратите внимание на указанные условия соединения WHERE, ON, USING.
3. Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN
В MySQL процесс выполнения A LEFT JOIN B join_condition выглядит следующим образом: 1) · Установите таблицу B в соответствии с таблицей A и всеми таблицами, от которых зависит A. 2) Установить таблицу A на основе всех таблиц (кроме B), используемых в условии LEFT JOIN. 3) Условие LEFT JOIN используется для определения того, как искать строки из таблицы B. (Другими словами, без использования каких-либо условий в предложении WHERE). 4) · Все стандартные объединения могут быть оптимизированы за исключением таблиц, читаемых из всех таблиц, от которых оно зависит. Если возникает циклическая зависимость, MySQL выдает ошибку. 5) Выполните все стандартные оптимизации WHERE. 6) · Если в A есть строка, соответствующая предложению WHERE, но ни одна строка в B не соответствует условию ON, создайте другую строку B со всеми столбцами, установленными в NULL. 7) Если вы используете LEFT JOIN, чтобы найти строки, которые не существуют в некоторых таблицах, и выполняется следующий тест: col_name IS NULL в части WHERE, где col_name — столбец, объявленный как NOT NULL, MySQL находит соответствующее LEFT JOIN условие Остановить поиск других строк после одной строки (для определенной комбинации ключевых слов). Реализация RIGHT JOIN аналогична LEFT JOIN, но роли таблиц меняются местами.
Оптимизатор соединения вычисляет порядок, в котором таблицы должны быть объединены. Порядок чтения таблицы, установленный с помощью LEFT JOIN и STRAIGHT_JOIN, может помочь оптимизатору соединения работать быстрее, поскольку проверяется меньше обменов таблицами. Обратите внимание, что это означает, что MySQL выполняет полное сканирование b, если выполняется следующий тип запроса, потому что LEFT JOIN заставляет его читать перед d:
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
В этом случае исправление выполняется в обратном порядке a и b в предложении FROM:
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
MySQL может выполнить следующую оптимизацию LEFT JOIN: если условие WHERE всегда ложно для сгенерированных строк NULL, LEFT JOIN становится обычным соединением. Например, в следующем запросе, если t2.column1 имеет значение NULL, предложение WHERE будет ложным:
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
Поэтому безопасно преобразовать запрос в обычное соединение:
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
Это может быть быстрее, потому что MySQL может использовать таблицу t2 перед таблицей t1, если это может улучшить запрос. Чтобы обеспечить порядок таблиц, используйте RIGHT_JOIN.