Пара 2.1 (1). Borovikovdl
 Скачать 36.2 Kb.
|
|
Нужно будет ознакомиться с материалом, выслать практическое применение на почту, borovikov-dl@mail.ru Готовые варианты решений высылаются 06.02.2021 до 21:00. После этого времени отметка снижается. JOIN У вас две и более таблицы связанные между собой внешними ключами, для того чтобы выбрать информацию из нескольких страниц вам понадобится оператор SELECT, который будет использовать объединение «JOIN» между выборками из одной и более таблиц. MySQL поддерживает следующий JOINсинтаксис для table_referencesчасти SELECTоператоров и операторов с несколькими таблицами DELETEи UPDATE: table_references: escaped_table_reference [, escaped_table_reference] ... escaped_table_reference: { table_reference | { OJ table_reference } } table_reference: { table_factor | joined_table } table_factor: { tbl_name [PARTITION (partition_names)] [[AS] alias] [index_hint_list] | [LATERAL] table_subquery [AS] alias [(col_list)] | ( table_references ) } joined_table: { table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification] | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor } join_specification: { ON search_condition | USING (join_column_list) } join_column_list: column_name [, column_name] ... index_hint_list: index_hint [, index_hint] ... index_hint: { USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) | {IGNORE|FORCE} {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) } index_list: index_name [, index_name] ... Ссылка на таблицу также известна как выражение соединения. Ссылка на таблицу (когда она относится к многораздельной таблице) может содержать PARTITIONопцию, включая список разделенных запятыми разделов, подразделов или и того, и другого. Эта опция следует за именем таблицы и предшествует любому объявлению псевдонима. Эффект от этой опции заключается в том, что строки выбираются только из перечисленных разделов или подразделов. Любые разделы или подразделы, не указанные в списке, игнорируются. Синтаксис table_factorMySQL расширен по сравнению со стандартным SQL. Стандарт принимает только их table_reference, а не список в круглых скобках. Это консервативное расширение, если каждая запятая в списке table_referenceэлементов рассматривается как эквивалент внутреннего соединения. Например: SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c) эквивалентно: SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c) В MySQL JOIN, CROSS JOINи INNER JOINявляются синтаксические эквиваленты (они могут заменять друг друга). В стандартном SQL они не эквивалентны. INNER JOINиспользуется с ONпредложением, CROSS JOIN используется иначе. В общем случае круглые скобки можно игнорировать в выражениях соединения, содержащих только внутренние операции соединения. MySQL также поддерживает вложенные соединения. Можно указать подсказки индекса, чтобы повлиять на то, как оптимизатор MySQL использует индексы. . Подсказки оптимизатора и optimizer_switchсистемная переменная - это другие способы повлиять на использование индексов оптимизатором. В следующем списке описаны общие факторы, которые следует учитывать при написании объединений: Ссылка на таблицу может иметь псевдоним, используя или : tbl_name AS alias_nametbl_name alias_name SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name; - A table_subqueryтакже известен как производная таблица или подзапрос в FROM предложении. Такие подзапросы должны включать псевдоним, чтобы дать результату подзапроса имя таблицы, и могут дополнительно включать список имен столбцов таблицы в круглых скобках. Ниже приводится тривиальный пример: SELECT * FROM (SELECT 1, 2, 3) AS t1; Максимальное количество таблиц, на которые можно ссылаться в одном объединении, - 61. Это включает объединение, обрабатываемое путем слияния производных таблиц и представлений в FROM предложении во внешний блок запроса INNER JOINи , (запятая) семантически эквивалентны при отсутствии условия соединения: оба производят декартово произведение между указанными таблицами (то есть каждая строка в первой таблице присоединяется к каждой строке во второй таблице). Тем не менее, приоритет оператора запятой меньше , чем INNER JOIN, CROSS JOIN, LEFT JOIN, и так далее. Если вы смешиваете соединения через запятую с другими типами соединений, когда есть условие соединения, может возникнуть ошибка формы . Информация о решении этой проблемы представлена далее в этом разделе. Unknown column 'col_name' in 'on clause' search_conditionИспользуются с ONлюбым условным выражением формы , которые могут быть использованы в WHEREстатье. Как правило, ONпредложение служит для условий, которые определяют, как объединять таблицы, а WHEREпредложение ограничивает, какие строки включать в набор результатов. Если для правой таблицы в ONили USINGчасти в a нет соответствующей строки, для правой таблицы LEFT JOINиспользуется строка со всеми столбцами, установленными на NULL. Вы можете использовать этот факт, чтобы найти строки в таблице, которые не имеют аналогов в другой таблице: SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL; В этом примере left_tbl выполняется поиск всех строк со idзначением, которого нет в right_tbl(то есть, все строки left_tblбез соответствующей строки right_tbl). Предложение называет список столбцов, которые должны существовать в обеих таблицах. Если таблицы и обе содержат столбцы , и , следующее соединение сравнивает соответствующие столбцы из двух таблиц: USING(join_column_list)abc1c2c3 a LEFT JOIN b USING (c1, c2, c3) Эти NATURAL [LEFT] JOINдве таблицы определены как семантически эквивалентные выражению INNER JOINили LEFT JOINс USINGпредложением, которое именует все столбцы, существующие в обеих таблицах. RIGHT JOINработает аналогично LEFT JOIN. Чтобы сохранить переносимость кода между базами данных, рекомендуется использовать LEFT JOINвместо RIGHT JOIN. { OJ ... }Синтаксис показан в соединении синтаксис описания существует только для совместимости с ODBC. Фигурные скобки в синтаксисе следует писать буквально; они не являются метасинтаксисом, который используется где-либо в описаниях синтаксиса. SELECT left_tbl.* FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id } WHERE right_tbl.id IS NULL; Вы можете использовать другие типы объединений внутри { OJ ... }, например INNER JOINили RIGHT OUTER JOIN. Это помогает обеспечить совместимость с некоторыми сторонними приложениями, но не является официальным синтаксисом ODBC. STRAIGHT_JOINаналогичен JOIN, за исключением того, что левая таблица всегда читается перед правой таблицей. Это можно использовать для тех (немногих) случаев, для которых оптимизатор объединения обрабатывает таблицы в неоптимальном порядке. Некоторые присоединяются к примерам: SELECT * FROM table1, table2; SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; SELECT * FROM table1 LEFT JOIN table2 USING (id); SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id LEFT JOIN table3 ON table2.id = table3.id; Естественные соединения и соединения с USING, включая варианты внешнего соединения, обрабатываются в соответствии со стандартом SQL: 2003: Избыточные столбцы NATURALобъединения не отображаются. Рассмотрим этот набор утверждений: CREATE TABLE t1 (i INT, j INT); CREATE TABLE t2 (k INT, j INT); INSERT INTO t1 VALUES(1, 1); INSERT INTO t2 VALUES(1, 1); SELECT * FROM t1 NATURAL JOIN t2; SELECT * FROM t1 JOIN t2 USING (j); В первом SELECT операторе столбец jпоявляется в обеих таблицах и, таким образом, становится столбцом соединения, поэтому, согласно стандартному SQL, он должен появляться в выводе только один раз, а не дважды. Точно так же во втором операторе SELECT столбец jназван в USINGпредложении и должен появляться в выводе только один раз, а не дважды. Таким образом, операторы производят такой вывод: +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ +------+------+------+ | j | i | k | +------+------+------+ | 1 | 1 | 1 | +------+------+------+ Исключение избыточных столбцов и упорядочение столбцов происходит в соответствии со стандартным SQL, создавая следующий порядок отображения: Сначала объединились общие столбцы двух объединенных таблиц в том порядке, в котором они встречаются в первой таблице. Во-вторых, столбцы, уникальные для первой таблицы, в том порядке, в котором они встречаются в этой таблице. В-третьих, столбцы, уникальные для второй таблицы, в том порядке, в котором они встречаются в этой таблице. Единственный столбец результатов, который заменяет два общих столбца, определяется с помощью операции объединения. То есть для двух t1.aи t2.aрезультирующий столбец единственного соединения aопределяется как a = COALESCE(t1.a, t2.a), где: COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END) Если операция соединения - это любое другое соединение, столбцы результатов объединения состоят из конкатенации всех столбцов соединенных таблиц. Следствием определения объединенных столбцов является то, что для внешних соединений объединенный столбец содержит значение не NULLстолбца, если один из двух столбцов всегда NULL. Если ни один столбец или оба столбца не являются NULL, оба общих столбца имеют одинаковое значение, поэтому не имеет значения, какой из них выбран в качестве значения объединенного столбца. Простой способ интерпретировать это - учесть, что объединенный столбец внешнего соединения представлен общим столбцом внутренней таблицы файла JOIN. Предположим, что таблицы t1(a, b)и t2(a, c)имеют следующее содержимое: t1 t2 ---- ---- 1 x 2 z 2 y 3 w Затем для этого соединения столбец aсодержит значения t1.a: mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2; +------+------+------+ | a | b | c | +------+------+------+ | 1 | x | NULL | | 2 | y | z | +------+------+------+ Напротив, для этого соединения столбец a содержит значения t2.a. mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2; +------+------+------+ | a | c | b | +------+------+------+ | 2 | z | y | | 3 | w | NULL | +------+------+------+ Сравните эти результаты с другими эквивалентными запросами JOIN ... ON: mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a); +------+------+------+------+ | a | b | a | c | +------+------+------+------+ | 1 | x | NULL | NULL | | 2 | y | 2 | z | +------+------+------+------+ mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a); +------+------+------+------+ | a | b | a | c | +------+------+------+------+ | 2 | y | 2 | z | | NULL | NULL | 3 | w | +------+------+------+------+ - Предложение USINGможно переписать как ONпредложение, которое сравнивает соответствующие столбцы. Однако, хотя USINGи ONпохожи, они не совсем то же самое. Рассмотрим следующие два вопроса: a LEFT JOIN b USING (c1, c2, c3) a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3 Что касается определения того, какие строки удовлетворяют условию соединения, оба соединения семантически идентичны. Что касается определения столбцов, отображаемых для SELECT *раскрытия, два соединения семантически не идентичны. Объединение USINGвыбирает объединенные значения соответствующих столбцов, тогда как ONобъединение выбирает все столбцы из всех таблиц. Для USINGобъединения SELECT *выбирает эти значения: COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3) Для ONобъединения SELECT *выбирает эти значения: a.c1, a.c2, a.c3, b.c1, b.c2, b.c3 При внутреннем соединении COALESCE(a.c1, b.c1)это то же самое, что a.c1или, b.c1потому что оба столбца имеют одинаковое значение. При внешнем соединении (например, LEFT JOIN) один из двух столбцов может быть NULL. Этот столбец не указывается в результате. Предложение ONможет относиться только к своим операндам. Пример: CREATE TABLE t1 (i1 INT); CREATE TABLE t2 (i2 INT); CREATE TABLE t3 (i3 INT); SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3; Оператор завершается с Unknown column 'i3' in 'on clause'ошибкой, потому что i3это столбец в t3, который не является операндом ONпредложения. Чтобы разрешить обработку соединения, перепишите инструкцию следующим образом: SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3); - JOINимеет более высокий приоритет, чем оператор запятой ( ,), поэтому выражение соединения t1, t2 JOIN t3интерпретируется как (t1, (t2 JOIN t3)), а не как ((t1, t2) JOIN t3). Это влияет на операторы, которые используют ONпредложение, поскольку это предложение может относиться только к столбцам в операндах соединения, а приоритет влияет на интерпретацию того, что это за операнды. Пример: CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1, 1); INSERT INTO t2 VALUES(1, 1); INSERT INTO t3 VALUES(1, 1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3); Оператор JOIN-запятая имеет приоритет над оператором, поэтому операнды для ON предложения - t2и t3. Поскольку t1.i1ни один из операндов не является столбцом, результатом будет Unknown column 't1.i1' in 'on clause'ошибка. Чтобы разрешить обработку соединения, используйте любую из этих стратегий: Сгруппируйте первые две таблицы явно круглыми скобками, чтобы операнды для ON предложения были (t1, t2)и t3: SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3); Избегайте использования оператора запятой и используйте JOINвместо него: SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3); Же толкование старшинства также относится к отчетности, смешивать оператор запятой с INNER JOIN, CROSS JOIN, LEFT JOINи RIGHT JOIN, все из которых имеют более высокий приоритет , чем оператор запятой. Расширение MySQL по сравнению со стандартом SQL: 2003 заключается в том, что MySQL позволяет вам квалифицировать общие (объединенные) столбцы NATURALили USING объединения, тогда как стандарт этого не позволяет. |