Получение частотного распределения

Задача
Вы хотите узнать частоту вхождения каждого значения в таблицу.

Решение
Сгенерируйте частотное распределение, которое является сводной характеристикой содержимого вашего множества данных.

Обсуждение
Методы вычисления сводных характеристик группы часто используются для разбиения значений на категории согласно количеству их вхождений во множество, то есть для получения частотного распределения. Для таблицы testscore частотное распределение выглядит так:

mysql> SELECT score, COUNT(score) AS occurrence
-> FROM testscore GROUP BY score;


+-------+------------+
| score | occurrence |
+-------+------------+
| 4 | 2 |
| 5 | 1 |
| 6 | 4 |
| 7 | 4 |
| 8 | 2 |
| 9 | 5 |
| 10 | 2 |
+-------+------------+

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

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT score, (COUNT(score)*100)/@n AS percent
-> FROM testscore GROUP BY score;


+------+---------+
| score | percent |
+------+---------+
| 4 | 10 |
| 5 | 5 |
| 6 | 20 |
| 7 | 20 |
| 8 | 10 |
| 9 | 25 |
| 10 | 10 |
+------+---------+

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

Частотное распределение часто применяется при экспорте результатов в графическую программу. В отсутствие такой программы для визуального представления распределения вы можете сформировать простую ASCII-диаграмму и в MySQL. Например, чтобы вывести ASCII-гистограмму для счетчиков результатов тестов, преобразуйте счетчики в строки символов *:

mysql> SELECT score, REPEAT('*',COUNT(score)) AS occurrences
-> FROM testscore GROUP BY score;


+-------+-------------+
| score | occurrences |
+-------+-------------+
| 4 | ** |
| 5 | * |
| 6 | **** |
| 7 | **** |
| 8 | ** |
| 9 | ***** |
| 10 | ** |
+-------+-------------+

Для вывода диаграммы относительного частотного распределения используйте процентное соотношение:

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT score, REPEAT('*',(COUNT(score)*100)/@n) AS percent
-> FROM testscore GROUP BY score;


+------+-------------------------------------+
| score | percent |
+------+-------------------------------------+
| 4 | ********** |
| 5 | ***** |
| 6 | ******************** |
| 7 | ******************** |
| 8 | ********** |
| 9 | ************************* |
| 10 | ********** |
+------+--------------------------------------+

Конечно, ASCII-диаграммы далеки от совершенства, но они обеспечивают быстрый способ получения общей картины распределения наблюдений без дополнительного инструментария.

Если вы формируете частотное распределение для набора категорий, часть которых не присутствует в ваших наблюдениях, то такие категории не будут отражены в выводе. Для того чтобы принудительно отобразить каждую категорию, используйте справочную таблицу и левое объединение (метод обсуждался в рецепте 12.9). Для таблицы testscore можно было бы ввести диапазон баллов от 0 до 10, тогда справочная таблица должна была бы содержать все значения диапазона:

mysql> CREATE TABLE ref (score INT);
mysql> INSERT INTO ref (score)
-> VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);


Теперь объединяем справочную таблицу с результатами тестов и формируем частотное распределение:

mysql> SELECT ref.score, COUNT(testscore.score) AS occurrences
-> FROM ref LEFT JOIN testscore ON ref.score = testscore.score
-> GROUP BY ref.score;


+-------+--------------+
| score | occurrences |
+-------+--------------+
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 2 |
| 5 | 1 |
| 6 | 4 |
| 7 | 4 |
| 8 | 2 |
| 9 | 5 |
| 10 | 2 |
+-------+--------------+

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

Тот же принцип используется и при получении относительных распределений частот:

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT ref.score, (COUNT(testscore.score)*100)/@n AS percent
-> FROM ref LEFT JOIN testscore ON ref.score = testscore.score
-> GROUP BY ref.score;


+------+---------+
| score | percent |
+------+---------+
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 10 |
| 5 | 5 |
| 6 | 20 |
| 7 | 20 |
| 8 | 10 |
| 9 | 25 |
| 10 | 10 |
+------+---------+

Добавлено: 23 Июля 2018 20:30:10 Добавил: Андрей Ковальчук

Групповые описательные статистические показатели

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

Решение
Применяйте агрегирующие функции, используя инструкцию GROUP BY для разбиения наблюдений на соответствующие группы.

Обсуждение
В предыдущем разделе было показано, как вычислять описательные статистические показатели для всего множества наблюдений таблицы testscore. Чтобы получить более точную информацию, можно использовать GROUP BY для распределения наблюдений по группам и вычисления статистических показателей для каждой группы в отдельности. Например, для испытуемых в таблице testscore приводятся такие параметры, как пол и возраст, так что можно вычислить статистические показатели для каждого возраста или пола (или сразу для того и другого):

По возрасту:

mysql> SELECT age, COUNT(score) AS n,
-> SUM(score) AS sum,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> AVG(score) AS mean,
-> STD(score) AS 'std. dev.'
-> FROM testscore
-> GROUP BY age;


+----+---+-----+-----------+-----------+-------+-----------+
| age | n | sum | minimum | maximum | mean | std. dev. |
+----+---+-----+----------+------------+-------+-----------+
| 5 | 4 | 22 | 4 | 7 | 5.5000 | 1.1180 |
| 6 | 4 | 27 | 4 | 9 | 6.7500 | 1.9203 |
| 7 | 4 | 30 | 6 | 9 | 7.5000 | 1.1180 |
| 8 | 4 | 32 | 6 | 10 | 8.0000 | 1.5811 |
| 9 | 4 | 35 | 7 | 10 | 8.7500 | 1.0897 |
+----+---+-----+----------+------------+-------+-----------+

По полу:

mysql> SELECT sex, COUNT(score) AS n,
-> SUM(score) AS sum,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> AVG(score) AS mean,
-> STD(score) AS 'std. dev.'
-> FROM testscore
-> GROUP BY sex;


+----+---+-----+----------+-----------+--------+-----------+
| sex | n | sum | minimum | maximum | mean | std. dev. |
+----+---+-----+----------+-----------+--------+-----------+
| M | 10 | 71 | 4 | 9 | 7.1000 | 1.7000 |
| F | 10 | 75 | 4 | 10 | 7.5000 | 1.8574 |
+----+---+-----+----------+------------+--------+----------+

По возрасту и полу:

mysql> SELECT age, sex, COUNT(score) AS n,
-> SUM(score) AS sum,
-> MIN(score) AS minimum,-> MAX(score) AS maximum,
-> AVG(score) AS mean,
-> STD(score) AS 'std. dev.'
-> FROM testscore
-> GROUP BY age, sex;


+-----+-----+---+------+---------+---------+--------+-----------+
| age | sex | n | sum | minimum | maximum | mean | std. dev. |
+----+-----+---+------+---------+---------+--------+-----------+
| 5 | M | 2 | 9 | 4 | 5 | 4.5000 | 0.5000 |
| 5 | F | 2 | 13 | 6 | 7 | 6.5000 | 0.5000 |
| 6 | M | 2 | 17 | 8 | 9 | 8.5000 | 0.5000 |
| 6 | F | 2 | 10 | 4 | 6 | 5.0000 | 1.0000 |
| 7 | M | 2 | 14 | 6 | 8 | 7.0000 | 1.0000 |
| 7 | F | 2 | 16 | 7 | 9 | 8.0000 | 1.0000 |
| 8 | M | 2 | 15 | 6 | 9 | 7.5000 | 1.5000 |
| 8 | F | 2 | 17 | 7 | 10 | 8.5000 | 1.5000 |
| 9 | M | 2 | 16 | 7 | 9 | 8.0000 | 1.0000 |
| 9 | F | 2 | 19 | 9 | 10 | 9.5000 | 0.5000 |
+----+-----+---+------+---------+---------+--------+-----------+

Добавлено: 23 Июля 2018 20:28:53 Добавил: Андрей Ковальчук

Получение описательных статистических показателей

Задача
Вы хотите охарактеризовать набор данных, используя общие описательные или сводные статистические показатели.

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

Обсуждение
Предположим, что у вас есть таблица testscore с результатами тестирования: идентификатор испытуемого (subject), возраст (age), пол (sex) и количество баллов (score):

mysql> SELECT subject, age, sex, score FROM testscore ORDER BY subject;


+--------+-----+-----+-------+
| subject | age | sex | score |
+--------+-----+-----+-------+
| 1 | 5 | M | 5 |
| 2 | 5 | M | 4 |
| 3 | 5 | F | 6 |
| 4 | 5 | F | 7 |
| 5 | 6 | M | 8 |
| 6 | 6 | M | 9 |
| 7 | 6 | F | 4 |
| 8 | 6 | F | 6 |
| 9 | 7 | M | 8 |
| 10 | 7 | M | 6 |
| 11 | 7 | F | 9 |
| 12 | 7 | F | 7 |
| 13 | 8 | M | 9 |
| 14 | 8 | M | 6 |
| 15 | 8 | F | 7 |
| 16 | 8 | F | 10 |
| 17 | 9 | M | 9 |
| 18 | 9 | M | 7 |
| 19 | 9 | F | 10 |
| 20 | 9 | F | 9 |
+--------+-----+-----+-------+

Анализ набора наблюдений разумно начать с получения некоторых описательных статистических показателей, обобщающих характеристики данных. Подобные статистические показатели включают:

• Количество наблюдений, сумма баллов и диапазон (минимум и максимум).

• Параметры сдвига (central tendency), такие как среднее значение (mean), медиана и мода.

• Параметры вариации, такие как стандартная девиация или дисперсия (variance).

Все эти величины, за исключением медианы и моды, могут быть получены путем вызова агрегирующих функций:

mysql> SELECT COUNT(score) AS n,
-> SUM(score) AS sum,
-> MIN(score) AS minimum,
-> MAX(score) AS maximum,
-> AVG(score) AS mean,
-> STD(score) AS 'std. dev.'
-> FROM testscore;


+---+------+---------+---------+--------+-----------+
| n | sum | minimum | maximum | mean | std. dev. |
+----+------+---------+---------+--------+-----------+
| 20 | 146 | 4 | 10 | 7.3000 | 1.7916 |
+---+------+---------+---------+--------+-----------+

Агрегирующие функции в запросе обрабатывают только значения не-NULL наблюдений. Если вы используете NULL для представления отсутствующих значений, то можете захотеть получить дополнительно оценку того, как много значений отсутствует.

Запрос не затрагивает дисперсию, в MySQL нет функции для ее вычисления. Но дисперсия – это просто квадрат стандартной девиации, так что ее легко получить так:

STD(score) * STD(score)
STDDEV() – это синоним STD().


Стандартную девиацию можно использовать для идентификации выбросов (outliers) – значений, которые расположены нехарактерно далеко от среднего.

Например, чтобы выбрать значения, которые отделены от среднего более чем тремя стандартными девиациями, сделаем что-то типа:

SELECT @mean := AVG(score), @std := STD(score) FROM testscore;
SELECT score FROM testscore WHERE ABS(score-@mean) > @std * 3;


Для множества из n значений стандартная девиация, выдаваемая STD(), вычисляется для n степеней свободы. Это эквивалентно такому вычислению стандартной девиации без использования агрегирующей функции (@ss представляет сумму квадратов):

mysql> SELECT
-> @n := COUNT(score),
-> @sum := SUM(score),
-> @ss := SUM(score*score)
-> FROM testscore;
mysql> SELECT @var := ((@n * @ss) - (@sum * @sum)) / (@n * @n);
mysql> SELECT SQRT(@var);


+---------------+
| SQRT(@var) |
+--------------+
| 1.791647 |
+--------------+

Чтобы получить стандартную девиацию для n–1 степеней свободы, сделайте следующее:

mysql> SELECT
-> @n := COUNT(score),
-> @sum := SUM(score),
-> @ss := SUM(score*score)
-> FROM testscore;
mysql> SELECT @var := ((@n * @ss) - (@sum * @sum)) / (@n * (@n - 1));
mysql> SELECT SQRT(@var);


+---------------+
| SQRT(@var) |
+--------------+
| 1.838191 |
+--------------+

Или, что проще:

mysql> SELECT @n := COUNT(score) FROM testscore;
mysql> SELECT STD(score)*SQRT(@n/(@n-1)) FROM testscore;


+------------------------------------+
| STD(score)*SQRT(@n/(@n-1)) |
+------------------------------------+
| 1.838191 |
+------------------------------------+

В MySQL нет встроенных функций для вычисления моды и медианы набора данных, но вы можете получить их самостоятельно. Мода – это наиболее часто встречающееся значение. Чтобы выявить его, подсчитаем количество вхождений в множество каждого значения и посмотрим, какое из них самое большое:

mysql> SELECT score, COUNT(score) AS count
-> FROM testscore GROUP BY score ORDER BY count DESC;


+------+-------+
| score | count |
+------+-------+
| 9 | 5 |
| 6 | 4 |
| 7 | 4 |
| 4 | 2 |
| 8 | 2 |
| 10 | 2 |
| 5 | 1 |
+------+-------+

В данном случае модальным значением является 9.

Медиана множества упорядоченных значения вычисляется так:

• Если значений нечетное количество, то медиана – это центральное значение.

• Если значений четное количество, то медиана – это среднее двух центральных значений множества.

На основе этого определения вычислим медиану множества наблюдений, хранящихся в базе данных:

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

• Создаем запрос с инструкцией ORDER BY для упорядочивания наблюдений и инструкцией LIMIT для извлечения центрального значения (значений).

• Вычисляем среднее для выбранного значения или значений.

Например, если таблица t содержит столбец score с 37 значениями (нечетное количество), необходимо выбрать одно значение, используя такой запрос:

SELECT score FROM t ORDER BY 1 LIMIT 18,1


Если столбец содержит 38 значений (четное количество), запрос будет таким:

SELECT score FROM t ORDER BY 1 LIMIT 18,2


Теперь выбираем значение или значения, возвращенные запросом, и вычисляем медиану как их среднее.

Приведем сценарий на Perl, реализующий вычисление медианы множества. Он принимает дескриптор базы данных и имена таблицы и столбца, содержащих наблюдения, затем генерирует запрос, извлекающий соответствующие значения, и возвращает их среднее:

sub median
{
my ($dbh, $tbl_name, $col_name) = @_;
my ($count, $limit);
$count = $dbh->selectrow_array ("SELECT COUNT($col_name) FROM $tbl_name");
return undef unless $count > 0;
if ($count % 2 == 1) # нечетное количество значений,
# вернуть центральное значение
{
$limit = sprintf ("LIMIT %d,1", ($count-1)/2);
}
else # четное количество значений, вернуть два центральных значения
{$limit = sprintf ("LIMIT %d,2", $count/2 - 1);
}
my $sth = $dbh->prepare (
"SELECT $col_name FROM $tbl_name ORDER BY 1 $limit");
$sth->execute ();
my ($n, $sum) = (0, 0);
while (my $ref = $sth->fetchrow_arrayref ())
{
++$n;
$sum += $ref->[0];
}
return ($sum / $n);
}


Этот прием работает для множества значений, хранящихся в базе данных.

Если же вы уже выбрали упорядоченное множество данных в массив @val, то медиану можно вычислить по-другому:

if (@val == 0) # если массив пуст, то медиана не определена
{
$median = undef;
}
elsif (@val % 2 == 1) # если количество элементов массива нечетное,
# медиана – это его центральный элемент
{
$median = $val[(@val-1)/2];
}
else # если количество элементов массива четное, медиана – это
# среднее значение двух его центральных элементов
$median = ($val[@val/2 - 1] + $val[@val/2]) / 2;
}


Код написан для массивов, нумерация элементов в которых начинается с 0. Если же вы используете язык с индексом массивов, начинающимся с 1, то подкорректируйте алгоритм соответствующим образом.

Добавлено: 23 Июля 2018 20:27:57 Добавил: Андрей Ковальчук

Одновременное использование нескольких серверов MySQL

Задача
Вы хотите выполнить запрос, который использует таблицы в базах данных, расположенных на разных серверах MySQL.

Решение
Эту задачу невозможно решить средствами только SQL. Можно обойти ее, открывая отдельные соединения с каждым сервером и самостоятельно свя-зывая информацию из двух таблиц. Есть и другой способ – копировать одну из таблиц с одного сервера на другой, чтобы работать с обеими таблицами на одном сервере.

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

Рассмотрим задачу на примере таблиц artist и painting. Предположим, что вы хотите определить названия картин да Винчи. Необходимо определить идентификатор да Винчи в таблице artist и сопоставить его записям таблицы painting. Если обе таблицы расположены в одной базе данных, то можно найти картины, используя запрос, выполняющий объединение таблиц:

mysql> SELECT painting.title
-> FROM artist, painting
-> WHERE artist.name = 'Da Vinci' AND artist.a_id = painting.a_id;


+-------------------+
| title |
+-------------------+
| The Last Supper |
| The Mona Lisa |
+-------------------+

Если таблицы относятся к разным базам данным, но управляются одним сервером MySQL, необходимо лишь слегка изменить запрос, включив спецификаторы базы данных (см. рецепт 12.2). Для наших таблиц запрос был бы таким:

mysql> SELECT db2.painting.title
-> FROM db1.artist, db2.painting
-> WHERE db1.artist.name = 'Da Vinci'
-> AND db1.artist.a_id = db2.painting.a_id;


+------------------+
| title |
+------------------+
| The Last Supper |
| The Mona Lisa |
+------------------+

Если же таблицы artist и painting находятся на разных серверах, невозможно выдать один запрос для выполнения соединения между ними. Вы должны отправить запрос на один сервер, чтобы извлечь идентификатор художника:

mysql> SELECT a_id FROM artist WHERE name = 'Da Vinci';


+-----+
| a_id |
+-----+
| 1 |
+-----+

Затем использовать значение a_id (1) для формирования второго запроса, отправляемого другому серверу:

mysql> SELECT title FROM painting WHERE a_id = 1;


+-------------------+
| title |
+-------------------+
| The Last Supper |
| The Mona Lisa |
+-------------------+

Для простого примера предложено достаточно простое решение. Простота объясняется тем, что извлекается всего одно значение из первой таблицы, а информация выводится только из второй таблицы. Если же потребуется выводить фамилию художника вместе с названием картины, и выполнить такую операцию нужно будет для нескольких художников, то задача соответственно усложнится. Можно решить ее, написав программу, имитирующую соединение:

1. Открыть отдельное соединение с каждым сервером базы данных.

2. Запустить цикл, выбирающий идентификаторы и фамилии художников для сервера, хранящего таблицу artist.

3. При каждом проходе цикла использовать текущий идентификатор художника для формирования запроса, который ищет в таблице painting строки, соответствующие значению идентификатора. Отправить запрос серверу, управляющему таблицей painting. По мере извлечения названий картин выводить их вместе с фамилией текущего художника.

При таком подходе имитируется соединение между таблицами, расположенными на разных серверах. Кстати, этот же способ можно использовать при работе с таблицами разных СУБД. (Например, именно так вы можете имитировать соединение таблицы MySQL с таблицей PostgreSQL.) В этом есть, однако, некоторое трюкачество, так что в подобной ситуации вы можете предпочесть копирование одной из таблиц с одного сервера на другой. Тогда с таблицами можно будет работать как с расположенными на одном сервере, выполняя необходимое соединение между ними.

Добавлено: 23 Июля 2018 20:26:36 Добавил: Андрей Ковальчук

Выявление и удаление несвязанных записей

Задача
У вас есть связанные таблицы (например, имеющие связь «главная-подчиненная»). Но вы подозреваете, что некоторые строки ни с чем не связаны и могут быть удалены.

Решение
Используйте LEFT JOIN для определения отсутствия соответствий и удалите выявленные значения, применяя приемы из рецепта 12.20. Или используйте процедуру замены таблицы, которая выбирает связанные записи в новую таблицу и заменяет ею исходную.

Обсуждение
В предыдущем разделе было рассказано о том, как одновременно удалять связанные записи из нескольких таблиц, используя связь, существующую между таблицами. Иногда возникает обратная задача: необходимо удалить записи на основе отсутствия связи. Такие ситуации обычно возникают, если у вас есть таблицы, которые предполагаются сопоставленными друг другу, но некоторые из записей не связаны ни с какими записями другой таблицы.Это может произойти случайно, например, при удалении родительской записи без удаления соответствующих дочерних, или наоборот. А может быть и ожидаемым последствием какого-то умышленного действия. Предположим, что форум он-лайн использует родительскую таблицу, в которой перечислены все темы обсуждений, и дочернюю таблицу, в которой хранятся все сообщения по заданной теме. Если удалить из дочерней таблицы старые записи, это может привести к тому, что у какой-то родительской записи больше не будет ни одной дочерней. Отсутствие недавних сообщений по теме, вероятно, означает, что дискуссия затихла, и родительскую запись можно удалять из таблицы тем. Тогда вы удаляете все множество дочерних записей, четко осознавая, что операция может оставить родительские записи без дочерних и сделает их кандидатами на удаление.

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

• Для выявления несвязанных записей используйте LEFT JOIN, так как это задача отсутствия соответствий.

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

В примерах будут использоваться таблицы дистрибутивов программного обеспечения swdist_head и swdist_item. Создайте таблицы в их исходном состоянии, используя сценарий swdist_create.sql из каталога joins дистрибутива recipes. Они будут выглядеть так:

mysql> SELECT * FROM swdist_head;


+--------+--------------+-----------+---------------+
| dist_id | name | ver_num | rel_date |
+--------+--------------+-----------+---------------+
| 1 | DB Gadgets | 1.59 | 1996-03-25 |
| 2 | NetGizmo | 3.02 | 1998-11-10 |
| 3 | DB Gadgets | 1.60 | 1998-12-26 |
| 4 | DB Gadgets | 1.61 | 1998-12-28 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+-------+---------------+-----------+---------------+

mysql> SELECT * FROM swdist_item;


+--------+-------------------+
| dist_id | dist_file |
+--------+-------------------+
| 1 | README |
| 1 | db-gadgets.sh |
| 3 | README |
| 3 | README.linux |
| 3 | db-gadgets.sh |
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 4 | db-gadgets.sh |
| 2 | README.txt |
| 2 | NetGizmo.exe |
| 5 | README.txt |
| 5 | NetGizmo.exe |
+--------+-------------------+

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

Чтобы нарушить целостность связи для наших целей, удалим несколько строк из каждой таблицы:

mysql> DELETE FROM swdist_head WHERE dist_id IN (1,4);
mysql> DELETE FROM swdist_item WHERE dist_id IN (2,5);


В результате в обеих таблицах появятся несвязанные записи:

mysql> SELECT * FROM swdist_head;


+--------+--------------+-----------+---------------+
| dist_id | name | ver_num | rel_date |
+--------+--------------+-----------+---------------+
| 2 | NetGizmo | 3.02 | 1998-11-10 |
| 3 | DB Gadgets | 1.60 | 1998-12-26 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+--------+--------------+-----------+---------------+

mysql> SELECT * FROM swdist_item;


+-------+-------------------+
| dist_id | dist_file |
+-------+-------------------+
| 1 | README |
| 1 | db-gadgets.sh |
| 3 | README |
| 3 | README.linux |
| 3 | db-gadgets.sh |
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 4 | db-gadgets.sh |
+-------+-------------------+

Беглый осмотр показывает, что только для дистрибутива 3 есть записи в двух таблицах. Дистрибутивы 2 и 5 из таблицы swdist_head не сопоставлены никаким записям из таблицы swdist_item. И наоборот, дистрибутивам 1 и 4 таблицы swdist_item не соответствуют никакие записи из таблицы swdist_head.

Теперь необходимо выявить несвязанные записи (каким-то способом, отличным от визуального контроля) и удалить их. Это задача для LEFT JOIN. Например, чтобы найти родительские записи без дочерних в таблице swdist_head, используем такой запрос:

mysql> SELECT swdist_head.dist_id AS 'unmatched swdist_head IDs'
-> FROM swdist_head LEFT JOIN swdist_item
-> ON swdist_head.dist_id = swdist_item.dist_id
-> WHERE swdist_item.dist_id IS NULL;


+--------------------------------+
| unmatched swdist_head IDs |
+--------------------------------+
| 2 |
| 5 |
+--------------------------------+

И наоборот, чтобы найти идентификаторы «осиротевших» дочерних записей из таблицы swdist_item, поменяйте таблицы местами:

mysql> SELECT swdist_item.dist_id AS 'unmatched swdist_item IDs'
-> FROM swdist_item LEFT JOIN swdist_head
-> ON swdist_item.dist_id = swdist_head.dist_id
-> WHERE swdist_head.dist_id IS NULL;


+------------------------------+
| unmatched swdist_item IDs |
+------------------------------+
| 1 |
| 1 |
| 4 |
| 4 |
| 4 |
| 4 |
+------------------------------+

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

mysql> SELECT DISTINCT swdist_item.dist_id AS 'unmatched swdist_item IDs'
-> FROM swdist_item LEFT JOIN swdist_head
-> ON swdist_item.dist_id = swdist_head.dist_id
-> WHERE swdist_head.dist_id IS NULL;


+-------------------------------+
| unmatched swdist_item IDs |
+-------------------------------+
| 1 |
| 4 |
+-------------------------------+

После выявления несвязанных записей остается только избавиться от них. Можно применить один из способов:

• Использование идентификаторов в многотабличном предложении DELETE. Вы будете одновременно удалять строки только из одной таблицы, но синтаксис этой формы DELETE все же удобен, так как он позволяет идентифицировать удаляемые записи путем соединения связанных таблиц.

• Запуск программы, которая выбирает несвязанные идентификаторы и использует их для формирования предложений DELETE.Чтобы использовать многотабличное предложение DELETE для удаления несвязанных записей, просто возьмите предложение SELECT, используемое для выявления этих записей, и замените все начало до ключевого слова FROM на DELETE имя_таблицы. Например, предложение SELECT, выбирающее родительские записи без дочерних, выглядит так:

SELECT swdist_head.dist_id AS 'unmatched swdist_head IDs'
FROM swdist_head LEFT JOIN swdist_item
ON swdist_head.dist_id = swdist_item.dist_id
WHERE swdist_item.dist_id IS NULL;


Соответствующее предложение DELETE будет таким:

DELETE swdist_head
FROM swdist_head LEFT JOIN swdist_item
ON swdist_head.dist_id = swdist_item.dist_id
WHERE swdist_item.dist_id IS NULL;


Запрос, определяющий потомков без родителей, выглядит так:

SELECT swdist_item.dist_id AS 'unmatched swdist_item IDs'
FROM swdist_item LEFT JOIN swdist_head
ON swdist_item.dist_id = swdist_head.dist_id
WHERE swdist_head.dist_id IS NULL;


Соответствующее предложение DELETE удаляет их:

DELETE swdist_item
FROM swdist_item LEFT JOIN swdist_head
ON swdist_item.dist_id = swdist_head.dist_id
WHERE swdist_head.dist_id IS NULL;


Чтобы удалить несвязанные записи из программы, выберите список идентификаторов и преобразуйте его в набор предложений DELETE. Рассмотрим программу на Perl, которая делает это сначала для родительской таблицы, а затем для дочерней:

#! /usr/bin/perl -w
use strict;
use lib qw(/usr/local/apache/lib/perl);
use Cookbook;
my $dbh = Cookbook::connect ();
# Определение идентификаторов родительских записей без дочерних
my $ref = $dbh->selectcol_arrayref (
"SELECT swdist_head.dist_id
FROM swdist_head LEFT JOIN swdist_item
ON swdist_head.dist_id = swdist_item.dist_id
WHERE swdist_item.dist_id IS NULL");
# selectcol_arrayref() возвращает ссылку на список. Преобразуем ссылку в список,
# который будет пуст, если $ref – это undef или указывает на пустой список.
my @val = ($ref ? @{$ref} : ());# Используем список идентификаторов для удаления записей по всем идентификаторам
# сразу. Если список пуст – не волнуемся, удалять нечего.
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @val) . ")";
$dbh->do ("DELETE FROM swdist_head $where", undef, @val);
}
# Повторяем процедуру для дочерней таблицы. Используем SELECT DISTINCT,
# чтобы каждый идентификатор выбирался один раз.
$ref = $dbh->selectcol_arrayref (
"SELECT DISTINCT swdist_item.dist_id
FROM swdist_item LEFT JOIN swdist_head
ON swdist_item.dist_id = swdist_head.dist_id
WHERE swdist_head.dist_id IS NULL");
@val = ($ref ? @{$ref} : ());
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @val) . ")";
$dbh->do ("DELETE FROM swdist_item $where", undef, @val);
}
$dbh->disconnect ();
exit (0);


Программа использует IN() для удаления всех необходимых записей из указанной таблицы разом.

Для формирования предложений DELETE также можно использовать mysql; сценарий, показывающий, как это делается, приведен в каталоге joins дистрибутива recipes.

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

Процедура замены таблицы работает так. Для таблицы swdist_head создаем новую таблицу с той же структурой:

CREATE TABLE tmp
(
dist_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # идентификатор дистрибутива
name VARCHAR(40), # название дистрибутиваver_num NUMERIC(5,2), # номер версии
rel_date DATE NOT NULL, # дата выпуска
PRIMARY KEY (dist_id)
);


Затем выбираем в таблицу tmp те записи swdist_head, для которых есть соответствия в таблице swdist_item:

INSERT IGNORE INTO tmp
SELECT swdist_head.*
FROM swdist_head, swdist_item
WHERE swdist_head.dist_id = swdist_item.dist_id;


Обратите внимание на то, что запрос использует INSERT IGNORE; родительская запись может соответствовать нескольким дочерним, но нам нужен только один экземпляр ее идентификатора. (Признаком неиспользования IGNORE является завершение запроса с ошибкой типа «duplicate key».)

Наконец, заменяем исходную таблицу новой:

DROP TABLE swdist_head;
ALTER TABLE tmp RENAME TO swdist_head;


Процедура замены дочерней таблицы таблицей, содержащей только связанные дочерние записи, аналогична, но без необходимости использования IGNORE – каждому потомку соответствует только один предок:

CREATE TABLE tmp
(
dist_id INT UNSIGNED NOT NULL, # идентификатор родительского дистрибутива
dist_file VARCHAR(255) NOT NULL # имя файла дистрибутива
);
INSERT INTO tmp
SELECT swdist_item.*
FROM swdist_head, swdist_item
WHERE swdist_head.dist_id = swdist_item.dist_id;
DROP TABLE swdist_item;
ALTER TABLE tmp RENAME TO swdist_item;

Добавлено: 23 Июля 2018 20:25:36 Добавил: Андрей Ковальчук

Удаление связанных строк в нескольких таблицах

Задача
Вы хотите удалить связанные строки из нескольких таблиц. Например, если у вас есть таблицы, связанные как «главная-подчиненная» или «предок-потомок», то удаление родительской записи обычно требует удаления всех соответствующих дочерних записей.

Решение
Есть несколько вариантов решения задачи. MySQL 4.0 поддерживает каскадное удаление, разрешая многотабличный синтаксис DELETE. Можно заменять таблицу новыми версиями, которые содержат только те записи, которые не должны быть удалены. Можно написать программу, формирующую соответствующие предложения DELETE для каждой таблицы, или использовать для этого mysql.

Обсуждение
Приложения, использующие связанные таблицы, часто должны обрабатывать их одновременно. Предположим, что вы используете MySQL для записи информации о содержимом имеющихся у вас дистрибутивов программ.

Главная (родительская) таблица хранит название дистрибутива, номер версии и дату выпуска. Подчиненная (или дочерняя) таблица приводит информацию о файлах дистрибутива, представляя собой ведомость содержимого дистрибутива. Для обеспечения соответствия родительской и дочерней записей в каждой родительской записи имеется уникальный идентификатор, который хранится и в дочерней записи. Таблицы можно определить так:

CREATE TABLE swdist_head
(
dist_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # идентификатор дистрибутива
name VARCHAR(40), # название дистрибутива
ver_num NUMERIC(5,2), # номер версии
rel_date DATE NOT NULL, # дата выпуска
PRIMARY KEY (dist_id)
);
CREATE TABLE swdist_item
(
dist_id INT UNSIGNED NOT NULL, # идентификатор родительского дистрибутива
dist_file VARCHAR(255) NOT NULL # имя файла дистрибутива
);


В примерах данного раздела будем полагать, что таблицы содержат такие записи:

mysql> SELECT * FROM swdist_head ORDER BY name, ver_num;


+--------+--------------+-----------+---------------+
| dist_id | name | ver_num | rel_date |
+--------+--------------+-----------+---------------+
| 1 | DB Gadgets | 1.59 | 1996-03-25 |
| 3 | DB Gadgets | 1.60 | 1998-12-26 |
| 4 | DB Gadgets | 1.61 | 1998-12-28 |
| 2 | NetGizmo | 3.02 | 1998-11-10 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+-------+---------------+------------+--------------+

mysql> SELECT * FROM swdist_item ORDER BY dist_id, dist_file;


+--------+-------------------+
| dist_id | dist_file |
+--------+-------------------+
| 1 | db-gadgets.sh |
| 1 | README |
| 2 | NetGizmo.exe |
| 2 | README.txt |
| 3 | db-gadgets.sh |
| 3 | README |
| 3 | README.linux |
| 4 | db-gadgets.sh |
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 5 | NetGizmo.exe |
| 5 | README.txt |
+--------+-------------------+

Таблицы описывают дистрибутивы трех версий DB Gadgets и двух версий NetGizmo. Но таблицы трудно воспринимать по отдельности, так что давайте используем соединение строк двух таблиц для вывода информации об указанном дистрибутиве. Например, следующий запрос выводит данные, хранимые для DB Gadgets версии 1.60:

mysql> SELECT swdist_head.dist_id, swdist_head.name,
-> swdist_head.ver_num, swdist_head.rel_date, swdist_item.dist_file
-> FROM swdist_head, swdist_item
-> WHERE swdist_head.name = 'DB Gadgets' AND swdist_head.ver_num = 1.60
-> AND swdist_head.dist_id = swdist_item.dist_id;


+--------+-------------+------------+---------------+-----------------+
| dist_id | name | ver_num | rel_date | dist_file |
+--------+--------------+----------+---------------+------------------+
| 3 | DB Gadgets | 1.60 | 1998-12-26 | README |
| 3 | DB Gadgets | 1.60 | 1998-12-26 | README.linux |
| 3 | DB Gadgets | 1.60 | 1998-12-26 | db-gadgets.sh |
+-------+---------------+-----------+--------------+------------------+

Аналогично при удалении дистрибутива необходимо обработать обе таблицы. DB Gadgets 1.60 имеет идентификатор 3, поэтому одним из способов избавления от него был бы запуск вручную таких предложений DELETE для каждой из таблиц:

mysql> DELETE FROM swdist_head WHERE dist_id = 3;
mysql> DELETE FROM swdist_item WHERE dist_id = 3;


Это просто и быстро, но если вы забудете о том, что необходимо выполнить удаление в двух таблицах (что гораздо вероятнее, чем вы можете подумать), то могут возникнуть проблемы. Нарушится ссылочная целостность: возникнут родительские записи без дочерних или дочерние записи, ссылающиеся на несуществующих родителей. Кроме того, ручное удаление неудобно в тех случаях, когда нужно удалить большое количество дистрибутивов или когда вы заранее не знаете, какие дистрибутивы необходимо удалить. Предположим, вы хотите произвести чистку и удалить все старые записи, оставив только последнюю версию каждого дистрибутива. (Например, из таблицы, содержащей данные о дистрибутивах DB Gadgets версий 1.59, 1.60 и 1.61, будут удалены записи для версий 1.59 и 1.60.) Вероятно, вы будете определять, какие именно дистрибутивы должны быть удалены, при помощи запроса, выбирающего идентификаторы старых дистрибутивов. Но что делать дальше? Запрос может вывести множество идентификаторов, и вы вряд ли захотите удалять каждый дистрибутив вручную. Этого и не нужно. Есть ряд возможностей удаления записей из нескольких таблиц:• Использовать многотабличный синтаксис DELETE, доступный в версии MySQL 4.0.0. Вы сможете написать запрос, который занимается идентификацией и удалением записей двух таблиц одновременно. Не нужно помнить о необходимости запуска нескольких предложений DELETE при каждом удалении записей из связанных таблиц.

• Подойти к задаче с другой стороны: выбрать записи, которые не должны быть удалены, в новые таблицы, затем заменить исходные таблицы новыми. Результат будет таким же, как при удалении ненужных записей.

• Использовать программу для определения идентификаторов дистрибутивов, которые подлежат удалению, и формирования соответствующих предложений DELETE. Можно использовать самостоятельно написанную программу или уже существующую, например, mysql.

В оставшейся части раздела подробно описаны все упомянутые способы и их применение для решения задачи удаления старых дистрибутивов. Каждый пример будет удалять записи из таблиц swdist_head и swdist_item, поэтому необходимо создавать их и заполнять записями перед опробованием каждого из методов, чтобы всегда начинать работу с одной и той же точки. Для этого можно воспользоваться сценарием swdist_create.sql из каталога joins дистрибутива recipes. Сценарии, реализующие все способы удаления записей из нескольких таблиц, находятся в этом же каталоге.

Каскадное удаление при помощи многотабличного предложения DELETE
Начиная с MySQL версии 4.0.0 предложение DELETE поддерживает синтаксис, позволяющий идентифицировать записи для удаления в нескольких таблицах и удалить их все в одном предложении. Чтобы использовать эту возможность для удаления дистрибутивов программ из таблиц swdist_head и swdist_item, определим идентификаторы удаляемых дистрибутивов, затем применим список к таблицам.

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

mysql> CREATE TABLE tmp
-> SELECT name, MAX(ver_num) AS newest
-> FROM swdist_head
-> GROUP BY name;


Получившаяся таблица выглядит так:

mysql> SELECT * FROM tmp;


+--------------+---------+
| name | newest |
+--------------+---------+
| DB Gadgets | 1.61 |
| NetGizmo | 4.00 |
+--------------+---------+

Теперь определим идентификаторы тех дистрибутивов, которые старше перечисленных в таблице tmp:

mysql> CREATE TABLE tmp2
-> SELECT swdist_head.dist_id, swdist_head.name, swdist_head.ver_num
-> FROM swdist_head, tmp
-> WHERE swdist_head.name = tmp.name AND swdist_head.ver_num < tmp.newest;


Обратите внимание на то, что на самом деле необходимо выбрать только столбец dist_id в tmp2. Пример выбирает и название, и номер версии, чтобы вы, посмотрев на tmp2, могли легко убедиться в том, что выбранные идентификаторы действительно соответствуют старым дистрибутивам, которые следует удалить:

mysql> SELECT * FROM tmp2;


+--------+--------------+-----------+
| dist_id | name | ver_num |
+--------+--------------+-----------+
| 1 | DB Gadgets | 1.59 |
| 3 | DB Gadgets | 1.60 |
| 2 | NetGizmo | 3.02 |
+--------+--------------+----------+

Таблица не содержит идентификаторов для DB Gadgets 1.61 и NetGizmo 4.00, представляющих последние дистрибутивы.

Теперь применим список идентификаторов таблицы tmp2 к таблице дистрибутивов, используя многотабличное предложение DELETE. Общая форма этого предложения такова:

DELETE список_таблиц1 FROM список_таблиц2 WHERE условия;


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

Каждый из списков таблиц может включать одно или несколько имен, разделенных запятыми. В нашем случае таблицы, из которых удаляются записи, – это swdist_head и swdist_item. Таблицы, используемые для идентификации удаляемых записей, – это те же swdist_head и swdist_item, а также tmp2:

mysql> DELETE swdist_head, swdist_item
-> FROM tmp2, swdist_head, swdist_item
-> WHERE tmp2.dist_id = swdist_head.dist_id
-> AND tmp2.dist_id = swdist_item.dist_id;

Результирующие таблицы будут такими:
mysql> SELECT * FROM swdist_head;


+--------+--------------+-----------+--------------+
| dist_id | name | ver_num | rel_date |
+--------+--------------+-----------+--------------+
| 4 | DB Gadgets | 1.61 | 1998-12-28 |
| 5 | NetGizmo | 4.00 | 2001-08-04 |
+--------+--------------+-----------+--------------+

mysql> SELECT * FROM swdist_item;


+--------+-------------------+
| dist_id | dist_file |
+--------+-------------------+
| 4 | README |
| 4 | README.linux |
| 4 | README.solaris |
| 4 | db-gadgets.sh |
| 5 | README.txt |
| 5 | NetGizmo.exe |
+-------+--------------------+

В рассматриваемых таблицах предложение DELETE работает так, как и предполагалось. Но знайте, что если таблицы будут содержать родительские записи, которые следует удалить, но для которых нет соответствующих дочерних записей, то ничего не получится. Инструкция WHERE не найдет соответствий для родительской записи в дочерней и, следовательно, не выберет родительскую запись для удаления. Чтобы обеспечить выбор и удаление родительской записи даже при отсутствии у нее дочерних записей, используйте LEFT JOIN:

mysql> DELETE swdist_head, swdist_item
-> FROM tmp2 LEFT JOIN swdist_head ON tmp2.dist_id = swdist_head.dist_id
-> LEFT JOIN swdist_item ON swdist_head.dist_id = swdist_item.dist_id;


Выполнение многотабличного удаления путем замены таблицы
Можно удалить связанные строки нескольких таблиц, выбрав только записи, которые не подлежат удалению, в новые таблицы и затем заменив исходные таблицы новыми. Это особенно удобно, если вы хотите удалить больше записей, чем останется для хранения.
Начнем с создания двух таблиц tmp_head и tmp_item, имеющих ту же структуру, что и таблицы swdist_head и swdist_item:

CREATE TABLE tmp_head
(
dist_id INT UNSIGNED NOT NULL AUTO_INCREMENT, # идентификатор дистрибутива
name VARCHAR(40), # название дистрибутива
ver_num NUMERIC(5,2), # номер версии
rel_date DATE NOT NULL, # дата выпуска
PRIMARY KEY (dist_id)
);
CREATE TABLE tmp_item
(
dist_id INT UNSIGNED NOT NULL, # идентификатор родительского дистрибутива
dist_file VARCHAR(255) NOT NULL # имя файла дистрибутива
);


Затем определим идентификаторы дистрибутивов, которые вы хотите хранить (то есть последнюю версию каждого дистрибутива). Идентификаторы определяются при помощи запросов, похожих на только что описанные в разделе про многотабличные удаления:

mysql> CREATE TABLE tmp
-> SELECT name, MAX(ver_num) AS newest
-> FROM swdist_head
-> GROUP BY name;
mysql> CREATE TABLE tmp2
-> SELECT swdist_head.dist_id
-> FROM swdist_head, tmp
-> WHERE swdist_head.name = tmp.name AND swdist_head.ver_num = tmp.newest;


Теперь выберем в новые таблицы те записи, которые должны остаться:

mysql> INSERT INTO tmp_head
-> SELECT swdist_head.*
-> FROM swdist_head, tmp2
-> WHERE swdist_head.dist_id = tmp2.dist_id;
mysql> INSERT INTO tmp_item
-> SELECT swdist_item.*
-> FROM swdist_item, tmp2
-> WHERE swdist_item.dist_id = tmp2.dist_id;


Наконец, заменим исходные таблицы новыми:

mysql> DROP TABLE swdist_head;
mysql> ALTER TABLE tmp_head RENAME TO swdist_head;
mysql> DROP TABLE swdist_item;
mysql> ALTER TABLE tmp_item RENAME TO swdist_item;


Выполнение многотабличного удаления из программы
Два предыдущих способа удаления связанных строк из нескольких таблиц используют только средства SQL. Другой подход заключается в написании программы, формирующей для вас предложения DELETE. Программа должна определить ключевые значения (идентификаторы дистрибутивов) для удаляемых записей и обработать ключи, преобразовав их в соответствующие предложения DELETE. Определять идентификаторы можно тем же способом, что и в предыдущих разделах, но у вас появляется некоторая свобода при выборе того, как использовать их для удаления записей:

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

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

• Если список идентификаторов очень велик, разбить его на более мелкие группы для создания менее громоздких инструкций IN().• Можно решить задачу и подойдя к ней с другой стороны. Выберем идентификаторы тех дистрибутивов, которые следует сохранить, и используем их для формирования инструкции NOT IN(), удаляющей все остальные дистрибутивы. Обычно это менее эффективно, поскольку MySQL не применяет индекс для операций NOT IN().

Я покажу, как реализовать каждый из методов на Perl.
Для каждого из трех первых вариантов начнем с формирования списка идентификаторов дистрибутивов для удаляемых записей:

# Определение последней версии каждого дистрибутива
$dbh->do ("CREATE TABLE tmp
SELECT name, MAX(ver_num) AS newest
FROM swdist_head
GROUP BY name");
# Определение идентификаторов более старых версий, чем указанная.
my $ref = $dbh->selectcol_arrayref (
"SELECT swdist_head.dist_id
FROM swdist_head, tmp
WHERE swdist_head.name = tmp.name
AND swdist_head.ver_num < tmp.newest");
# selectcol_arrayref() возвращает ссылку на список. Преобразуем ссылку в список,
# который будет пуст, если $ref – это undef или указывает на пустой список.
my @val = ($ref ? @{$ref} : ());


На данный момент @val содержит список значений идентификаторов удаляемых записей. Для их индивидуальной обработки выполните такой цикл:

# Используем список идентификаторов для удаления записей,
# по одному идентификатору единовременно.
foreach my $val (@val)
{
$dbh->do ("DELETE FROM swdist_head WHERE dist_id = ?", undef, $val);
$dbh->do ("DELETE FROM swdist_item WHERE dist_id = ?", undef, $val);
}

Цикл генерирует предложения вроде таких:
DELETE FROM swdist_head WHERE dist_id = '1'
DELETE FROM swdist_item WHERE dist_id = '1'
DELETE FROM swdist_head WHERE dist_id = '3'
DELETE FROM swdist_item WHERE dist_id = '3'
DELETE FROM swdist_head WHERE dist_id = '2'
DELETE FROM swdist_item WHERE dist_id = '2'


Недостаток этого способа в том, что для больших таблиц список идентификаторов может быть весьма объемистым, и придется формировать множество предложений DELETE. Для повышения эффективности объедините идентификаторы в одну инструкцию IN(), где укажите их все сразу. Сгенерируйтесписок идентификаторов тем же способом, что и раньше, затем обрабатывайте список так:

# Используем список идентификаторов для удаления записей по всем идентификаторам
# сразу. Если список пуст – не волнуемся, удалять нечего.
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @val) . ")";
$dbh->do ("DELETE FROM swdist_head $where", undef, @val);
$dbh->do ("DELETE FROM swdist_item $where", undef, @val);
}


Теперь формируется всего одно предложение DELETE для каждой таблицы:

DELETE FROM swdist_head WHERE dist_id IN ('1','3','2')
DELETE FROM swdist_item WHERE dist_id IN ('1','3','2')


Если список идентификаторов невероятно велик, может возникнуть опасность формирования предложения DELETE, длина которого будет превышать максимальную разрешенную для запроса (по умолчанию – один мегабайт).

В данном случае можно разбить список идентификаторов на мелкие группы и использовать каждую из них для построения короткой инструкции IN():

# Используем список идентификаторов для удаления записей,
# используя единовременно часть списка.
my $grp_size = 1000; # количество идентификаторов для одновременного удаления
for (my $i = 0; $i < @val; $i += $grp_size)
{
my $j = (@val < $i + $grp_size ? @val : $i + $grp_size);
my @group = @val[$i .. $j-1];
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
my $where = "WHERE dist_id IN (" . join (",", ("?") x @group) . ")";
$dbh->do ("DELETE FROM swdist_head $where", undef, @group);
$dbh->do ("DELETE FROM swdist_item $where", undef, @group);
}


Все предыдущие программные методы находят идентификаторы удаляемых записей, затем удаляют их. Можно получить тот же результат, используя обратную логику: выбирать идентификаторы записей, которые планируется хранить, и удалять все остальное. Такой подход удобен в тех случаях, когда вы предполагаете, что останется меньше записей, чем будет удалено. Для реализации такой логики определяем новейшую версию каждого дистрибутива и находим соответствующие идентификаторы. Затем используем список идентификаторов для формирования инструкции NOT IN():

# Определяем новейшую версию для каждого дистрибутива
$dbh->do ("CREATE TABLE tmp
SELECT name, MAX(ver_num) AS newest
FROM swdist_head
GROUP BY name");
# Определяем идентификаторы этих версий.
my $ref = $dbh->selectcol_arrayref (
"SELECT swdist_head.dist_id
FROM swdist_head, tmp
WHERE swdist_head.name = tmp.name
AND swdist_head.ver_num = tmp.newest");
# selectcol_arrayref() возвращает ссылку на список. Преобразуем ссылку в список,
# который будет пуст, если $ref – это undef или указывает на пустой список.
my @val = ($ref ? @{$ref} : ());
# используем список идентификаторов для удаления записей всех "остальных"
# идентификаторов сразу. Инструкция WHERE пуста, если пуст список
# (в этом случае ни одну запись не нужно хранить, все могут быть удалены).
my $where = "";
if (@val)
{
# формируем список заполнителей "?", разделенных запятыми, по одному на значение
$where = "WHERE dist_id NOT IN (" . join (",", ("?") x @val) . ")";
}
$dbh->do ("DELETE FROM swdist_head $where", undef, @val);
$dbh->do ("DELETE FROM swdist_item $where", undef, @val);


Обратите внимание, что при использовании такой обратной логики необходимо использовать весь список идентификаторов в одной инструкции NOT IN(). При попытке разбить список на более мелкие группы и использовать NOT IN() для каждой из них содержимое таблиц будет полностью уничтожено, даже если вы этого не хотите.

Выполнение многотабличного удаления с помощью mysql
Если ключи, указывающие, какие записи нужно удалить, не содержат кавычек и других специальных символов, то вы можете формировать предложения DELETE при помощи mysql. Ключи таблиц дистрибутивов (значения dist_id) целые, так что они допускают применение этого решения. Сформируйте список идентификаторов с помощью тех же запросов, которые описаны в разделе, посвященном многотабличному предложению DELETE, затем используйте этот список для создания предложений DELETE:

CREATE TABLE tmp
SELECT name, MAX(ver_num) AS newest
FROM swdist_headGROUP BY name;
CREATE TABLE tmp2
SELECT swdist_head.dist_id
FROM swdist_head, tmp
WHERE swdist_head.name = tmp.name AND swdist_head.ver_num < tmp.newest;
SELECT CONCAT('DELETE FROM swdist_head WHERE dist_id=',dist_id,';') FROM tmp2;
SELECT CONCAT('DELETE FROM swdist_item WHERE dist_id=',dist_id,';') FROM tmp2;


Если эти предложения содержатся в файле swdist_mysql_delete.sql, выполните его, чтобы вывести ряд предложений DELETE:

% mysql -N cookbook < swdist_mysql_delete.sql > tmp


Файл tmp будет таким:

DELETE FROM swdist_head WHERE dist_id=1;
DELETE FROM swdist_head WHERE dist_id=3;
DELETE FROM swdist_head WHERE dist_id=2;
DELETE FROM swdist_item WHERE dist_id=1;
DELETE FROM swdist_item WHERE dist_id=3;
DELETE FROM swdist_item WHERE dist_id=2;


Выполним его содержимое так:

% mysql cookbook < tmp

Добавлено: 23 Июля 2018 20:23:52 Добавил: Андрей Ковальчук

Создание справочной таблицы с помощью соединения

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

Решение
Используйте один из способов обновления связанной таблицы.

Обсуждение
Общий подход для экономии места – хранить в таблице идентификационные номера или коды вместо описательных строк. Повышается и производительность, поскольку числа быстрее индексировать и извлекать, чем строки.

(Для запросов, в которых необходим вывод имен, объедините значения идентификаторов со справочной таблицей вида идентификатор-название.) Когда вы создаете новую таблицу, помните об этой стратегии и сразу проектируйте таблицу так, чтобы ее можно было использовать со справочной таблицей. Но может случаться так, что у вас уже есть таблица с описательными строками, которую можно преобразовать так, чтобы она использовала значения идентификаторов. В этом разделе описано, как создать справочную таблицу, сопоставляющую каждому описанию его идентификатор, и как преобразовать описания в идентификаторы в исходной таблице. Будем использовать ALTER TABLE в сочетании с приемами обновления связанной таблицы.

Предположим, вы коллекционируете монеты и решили отслеживать свою коллекцию с помощью базы данных, используя такую таблицу:

CREATE TABLE coin
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date CHAR(5) NOT NULL, # 4 цифры года + буква1
denom CHAR(20) NOT NULL, # название номинала (например, Lincoln cent)
PRIMARY KEY (id)
);


Каждой монете автоматически присваивается идентификатор как значение AUTO_INCREMENT; кроме того, записываются год выпуска каждой монеты и ее название номинала (denomination name). Введенные записи выглядят так:

mysql> SELECT * FROM coin;


+----+-------+---------------------+
| id | date | denom |
+---+-------+------------------------+
| 1 | 1944s | Lincoln cent |
| 2 | 1977 | Roosevelt dime |
| 3 | 1955d | Lincoln cent |
| 4 | 1938 | Jefferson nickel |
| 5 | 1964 | Kennedy half dollar |
| 6 | 1959 | Lincoln cent |
| 7 | 1945 | Jefferson nickel |
| 8 | 1905 | Buffalo nickel |
| 9 | 1924 | Mercury head dime |
| 10 | 2001 | Roosevelt dime |
| 11 | 1937 | Mercury head dime |
| 12 | 1977 | Kennedy half dollar |
+---+--------+-----------------------+

Таблица хранит информацию, которая вам необходима, но вы замечаете, что на указание названий номиналов монет в каждой записи тратится много места, и все станет еще серьезнее, когда в таблице будет много записей. Было бы эффективнее хранить в таблице coin идентификаторы названий номиналов монет, и при необходимости находить название номинала в таблице denom, содержащей все названия номиналов монет и их идентификаторы.

(Преимущество такого метода для небольшой таблицы может быть неочевидным, но когда коллекция вырастет до 10 000 экземпляров, экономия пространства от хранения номеров вместо строк станет весьма ощутимой.)

Процедура создания справочной таблицы и преобразования исходной таблицы coin такова:

1. Создайте справочную таблицу denom для хранения соответствий идентификаторов названиям.

2. Заполните таблицу denom названиями номиналов из исходной таблицы coin.

3. Замените названия номиналов монет в таблице coin на соответствующие значения идентификаторов.

Таблица denom должна включать названия всех номиналов монет и соответствующие идентификаторы, поэтому ее структура может быть такой:

CREATE TABLE denom
(
denom_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
PRIMARY KEY (denom_id)
);


Чтобы заполнить таблицу данными, вставьте в нее те названия номиналов, которые присутствуют в таблице coin. Используйте SELECT DISTINCT, поскольку каждое название номинала должно быть введено ровно один раз:

INSERT INTO denom (name) SELECT DISTINCT denom FROM coin;


Предложение INSERT добавляет в таблицу denom только название номинала; denom_id – это столбец AUTO_INCREMENT, так что MySQL автоматически присвоит ему последовательные значения. Результирующая таблица будет такой:

+------------+-----------------------+
| denom_id | name |
+-----------+------------------------+
| 1 | Lincoln cent |
| 2 | Roosevelt dime |
| 3 | Jefferson nickel |
| 4 | Kennedy half dollar |
| 5 | Buffalo nickel |
| 6 | Mercury head dime |
+-----------+------------------------+

В MySQL версии 3.23 и выше вы можете создать таблицу denom и заполнить ее данными в одном предложении CREATE TABLE ... SELECT:

CREATE TABLE denom
(
denom_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (denom_id)
)
SELECT DISTINCT denom AS name FROM coin;


Следующим шагом после создания таблицы denom является преобразование названий номиналов в таблице coin в соответствующие идентификаторы:

• Создайте таблицу tmp, похожую на coin, но содержащую столбец denom_id вместо столбца denom.

• Заполните tmp результатами соединения таблиц coin и denom.

• Используйте таблицу tmp для замены исходной таблицы coin.

Для создания таблицы tmp выполните предложение CREATE TABLE, которое похоже на первоначальное предложение для создания coin, но подставьте в нем столбец denom_id вместо столбца denom:

CREATE TABLE tmp
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date CHAR(5) NOT NULL, # 4 цифры года + буква
denom_id INT UNSIGNED NOT NULL, # идентификатор названия номинала
PRIMARY KEY (id)
);


Затем заполните tmp, используя соединение coin и denom:

INSERT INTO tmp (id, date, denom_id)
SELECT coin.id, coin.date, denom.denom_id
FROM coin, denom
WHERE coin.denom = denom.name;


Наконец, замените исходную таблицу coin таблицей tmp:

DROP TABLE coin;
ALTER TABLE tmp RENAME TO coin;


В MySQL версии 3.23 и выше вы можете создать и заполнить таблицу tmp в одном предложении:

CREATE TABLE tmp
(
PRIMARY KEY (id)
)
SELECT coin.id, coin.date, denom.denom_id
FROM coin, denom
WHERE coin.denom = denom.name;


Теперь, как и раньше, замените coin на tmp.
В качестве еще одного способа преобразования таблицы coin после создания таблицы denom можно предложить изменение coin на месте, без использования таблицы tmp:

1. Добавьте столбец denom_id в таблицу coin при помощи ALTER TABLE.

2. Заполните значение denom_id каждой строки идентификатором, соответствующим ее названию номинала denom.

3. Удалите столбец denom.

Чтобы выполнить процедуру, добавьте в coin столбец для хранения идентификаторов:

ALTER TABLE coin ADD denom_id INT UNSIGNED NOT NULL;


Затем заполните столбец denom_id соответствующими идентификаторами, используя соответствие названий номиналов идентификаторам, хранящимся в таблице denom. Приведем небольшой сценарий, обновляющий значения идентификаторов в таблице coin для одного номинала за один проход:

#! /usr/bin/perl -w
# update_denom.pl – Для каждого названия номинала из таблицы denom обновить записи
# таблицы coin, содержащие такое название номинала, соответствующим идентификатором.
use strict;
use lib qw(/usr/local/apache/lib/perl);
use Cookbook;
my $dbh = Cookbook::connect ();
my $sth = $dbh->prepare ("SELECT denom_id, name FROM denom");
$sth->execute ();
while (my ($denom_id, $name) = $sth->fetchrow_array ())
{
# Для записей таблицы coin с указанным названием номинала добавить
# соответствующее значение denom_id из таблицы denom
$dbh->do ("UPDATE coin SET denom_id = ? WHERE denom = ?",
undef, $denom_id, $name);
}
$dbh->disconnect ();
exit (0);


Этот сценарий извлекает каждую пару идентификатор/название номинала из таблицы denom и формирует предложение UPDATE для изменения всех строк таблицы coin, содержащих название номинала, путем установки их значений denom_id в соответствующий идентификатор. Когда сценарий завершит работу, все строки таблицы coin будут содержать обновленные значения denom_id. Теперь столбец denom больше не нужен, и от него можно избавиться:

ALTER TABLE coin DROP denom;

Какой бы способ вы ни использовали для преобразования таблицы coin, ее содержимое в результате будет выглядеть так:mysql> SELECT * FROM coin;

+---+--------+------------+
| id | date | denom_id |
+---+--------+------------+
| 1 | 1944s | 1 |
| 2 | 1977 | 2 |
| 3 | 1955d | 1 |
| 4 | 1938 | 3 |
| 5 | 1964 | 4 |
| 6 | 1959 | 1 |
| 7 | 1945 | 3 |
| 8 | 1905 | 5 |
| 9 | 1924 | 6 |
| 10 | 2001 | 2 |
| 11 | 1937 | 6 |
| 12 | 1977 | 4 |
+---+--------+------------+

Если вам нужно вывести в результате запроса строки coin с названиями номиналов, а не их идентификаторами, выполните соединение, используя denom как справочную таблицу:

mysql> SELECT coin.id, coin.date, denom.name
-> FROM coin, denom
-> WHERE coin.denom_id = denom.denom_id;


+---+--------+------------------------+
| id | date | name |
+---+--------+------------------------+
| 1 | 1944s | Lincoln cent |
| 2 | 1977 | Roosevelt dime |
| 3 | 1955d | Lincoln cent |
| 4 | 1938 | Jefferson nickel |
| 5 | 1964 | Kennedy half dollar |
| 6 | 1959 | Lincoln cent |
| 7 | 1945 | Jefferson nickel |
| 8 | 1905 | Buffalo nickel |
| 9 | 1924 | Mercury head dime |
| 10 | 2001 | Roosevelt dime |
| 11 | 1937 | Mercury head dime |
| 12 | 1977 | Kennedy half dollar |
+---+--------+------------------------+

Это напоминает содержимое исходной таблицы coin, хотя таблица уже не хранит длинное описание в каждой строке.

А как насчет вставки новых элементов в таблицу coin? При работе с исходной таблицей вы бы вводили в каждую строку название номинала монеты. Теперь же, когда номинал преобразован в значения идентификаторов, используйте предложение INSERT INTO ... SELECT для поиска идентификатора номинала по названию. Например, для того чтобы ввести монету 10 центов (dime) 1962 года с Рузвельтом, используйте такое предложение:

INSERT INTO coin (date, denom_id)
SELECT 1962, denom_id FROM denom WHERE name = 'Roosevelt dime';

Добавлено: 23 Июля 2018 20:21:20 Добавил: Андрей Ковальчук

Обновление одной таблицы на основе значений другой

Задача
Вам нужно обновить существующие записи одной таблицы на основе содержимого записей другой таблицы, но MySQL еще не позволяет использовать соединения в инструкции WHERE предложений UPDATE. Так что у вас нет способа сопоставить две таблицы.

Решение
Создайте новую таблицу, заполненную данными результата соединения исходной таблицы и таблицы, содержащей новую информацию. Затем замените исходную таблицу новой. Или напишите программу, выбирающую информацию из связанной таблицы, и выполните необходимые запросы для обновления исходной таблицы. Или используйте mysql для формирования и выполнения запросов.

Обсуждение
Иногда при обновлении записей одной таблицы необходимо сослаться на записи другой таблицы. Вспомним таблицу states, использовавшуюся в нескольких предыдущих рецептах и содержащую строки типа:

mysql> SELECT * FROM states;


+----------------+--------+------------+----------+
| name | abbrev | statehood | pop |
+----------------+--------+------------+----------+
| Alaska | AK | 1959-01-03 | 550043 |
| Alabama | AL | 1819-12-14 | 4040587 |
| Arkansas | AR | 1836-06-15 | 2350725 |
| Arizona | AZ | 1912-02-14 | 3665228 |
...

Теперь предположим, что вы хотите добавить в эту таблицу несколько новых столбцов, используя информацию из другой таблицы city, содержащей такие данные о каждом штате, как его столица и крупнейший (по количеству жителей) город:

mysql> SELECT * FROM city;


+-----------+---------------+--------------+
| state | capital | largest |
+-----------+---------------+-------------+
| Alabama | Montgomery | Birmingham |
| Alaska | Juneau | Anchorage |
| Arizona | Phoenix | Phoenix |
| Arkansas | Little Rock | Little Rock |
...

Достаточно просто добавить в таблицу states новые столбцы capital и largest с помощью предложения ALTER TABLE. Но как тогда изменять строки, чтобы заполнить новые столбцы соответствующими значениями? Удобнее всего выполнить запрос UPDATE, использующий синтаксис соединения в инструкции WHERE:

UPDATE states,city
SET states.capital = city.capital, states.largest = city.largest
WHERE states.name = city.state;


Но, к сожалению, так поступить нельзя, поскольку MySQL еще не поддерживает эту возможность. Вторым решением могло бы быть использование подзапроса в инструкции WHERE, но подзапросы планируется включить только в версию MySQL 4.1. Что же остается? Конечно, не хотелось бы обновлять каждую строку вручную. Это невероятно трудоемко, да и глупо, учитывая то, что вся новая информация уже хранится в таблице city. Таблицы states и city содержат общий ключ (названия штатов), и эту информацию можно использовать для связывания таблиц и выполнения обновления. Есть несколько путей достижения того же результата, что и многотабличное обновление:

• Создайте новую таблицу, похожую на исходную, но содержащую дополнительные столбцы, добавленные из связанной таблицы city. Заполнитеновую таблицу данными, используя результат соединения таблиц states и city, затем замените исходную таблицу новой.

• Напишите программу, использующую информацию из таблицы city для формирования и выполнения предложений UPDATE, которые обновляют по одному штату в каждом запросе в таблице states.

• Используйте mysql для формирования предложений UPDATE. Обновление связанной таблицы путем ее замены Замена таблицы выполняется так. Чтобы расширить таблицу states, включив в нее столбцы capital и largest из таблицы city, создайте таблицу tmp, аналогичную states, и добавьте в нее столбцы capital и largest:

CREATE TABLE tmp
(
name VARCHAR(30) NOT NULL, # название штата
abbrev CHAR(2) NOT NULL, # двухсимвольная аббревиатура
statehood DATE, # дата вступления в Союз
pop BIGINT, # население на 4/1990
capital VARCHAR(30), # столица
largest VARCHAR(30), # наиболее населенный город
PRIMARY KEY (abbrev)
);


Затем заполните tmp данными, используя результат соединения между states и city, которое сопоставляет строки по названиям штатов:

INSERT INTO tmp (name, abbrev, statehood, pop, capital, largest)
SELECT
states.name, states.abbrev, states.statehood, states.pop,
city.capital, city.largest
FROM
states LEFT JOIN city ON states.name = city.state;


Заметьте, что запрос использует соединение LEFT JOIN. Предположим, что таблица city не полная и не содержит по строке для каждого штата. Тогда обычное соединение не сможет сформировать строку вывода для штатов, отсутствующих в таблице city, в результате в таблице tmp не будет записей для таких штатов, даже если они присутствуют в таблице states. Нехорошо! LEFT JOIN обеспечивает формирование строки вывода SELECT для каждой строки states вне зависимости от ее соответствия строке таблицы city. Любой штат, отсутствующий в таблице city, будет содержать значения NULL в столбцах capital и largest таблицы tmp, что вполне подходит для тех случаев, когда вам неизвестны названия городов, а формирование неполной строки, конечно же, предпочтительнее полной потери строки.

В результате таблица tmp похожа на исходную, но содержит два новых столбца, capital и largest (можете проверить). Убедившись в том, что таблица tmp нас устраивает, используем ее для замены исходной таблицы states:

DROP TABLE states;
ALTER TABLE tmp RENAME TO states;


Если вы хотите исключить возможность даже кратковременной недоступности таблицы states, выполните замену таким способом:

RENAME TABLE states TO states_old, tmp TO states;
DROP TABLE states_old;


Обновление связанной таблицы из программы
Метод замены таблиц эффективен, так как всю работу делает сервер. Но он больше всего подходит для тех случаев, когда изменяются все или почти все строки таблицы. Если же обновляется всего несколько строк, проще изменить таблицу «на месте» там, где необходимо. Кроме того, замена таблицы требует как минимум удвоения исходного пространства таблицы states на время выполнения процедуры обновления. Если обновляется большая таблица, то вы можете не захотеть использовать все это пространство.

Второй способ обновления таблицы на основе информации из связанной таблицы заключается в считывании данных из связанной таблицы и использования их для формирования предложений UPDATE. Например, чтобы обновить таблицу states информацией, хранящейся в таблице city, прочитайте названия городов и используйте их для создания и запуска ряда таких запросов:

UPDATE states SET capital = 'Montgomery', largest = 'Birmingham'
WHERE name = 'Alabama';
UPDATE states SET capital = 'Juneau', largest = 'Anchorage'
WHERE name = 'Alaska';
UPDATE states SET capital = 'Phoenix', largest = 'Phoenix'
WHERE name = 'Arizona';
UPDATE states SET capital = 'Little Rock', largest = 'Little Rock'
WHERE name = 'Arkansas';
...


Для выполнения такой процедуры сначала измените таблицу states так, чтобы она содержала новые столбцы:

ALTER TABLE states ADD capital VARCHAR(30), ADD largest VARCHAR(30);


Затем напишите программу, которая читает таблицу city и использует ее содержимое для формирования предложений UPDATE, изменяющих таблицу states. Рассмотрим пример сценария update_cities.pl, выполняющего такие операции:

#! /usr/bin/perl -w
# update_cities.pl – обновление столбцов capital и largest таблицы states
# с использованием содержимого таблицы city. Предполагается, что таблица states
# была изменена так, что теперь содержит столбцы capital и largest.
use strict;
use lib qw(/usr/local/apache/lib/perl);
use Cookbook;my $dbh = Cookbook::connect ();
my $sth = $dbh->prepare ("SELECT state, capital, largest FROM city");
$sth->execute ();
while (my ($state, $capital, $largest) = $sth->fetchrow_array ())
{
$dbh->do ("UPDATE states SET capital = ?, largest = ? WHERE name = ?",
undef, $capital, $largest, $state);
}
$dbh->disconnect ();
exit (0);


Все имена таблиц и столбцов встроены в сценарий, что делает его узко специальным. Вы можете обобщить процесс, написав функцию, которая принимает параметры, указывающие имена таблиц, столбцы, которые должны использоваться для сопоставления записей в двух таблицах, и столбцы для обновления строк. Один из путей решения задачи реализован в сценарии update_related.pl из каталога joins дистрибутива recipes.

Обновление связанной таблицы с помощью mysql
Если ваши значения данных не требуют какой-то специальной обработки внутренних кавычек или других специальных символов, то формировать и обрабатывать предложения UPDATE можно с помощью mysql. Похожая техника была описана в рецепте 12.17 при использовании mysql для имитации подзапроса.

Поместите следующее предложение в файл update_cities.sql:

SELECT CONCAT('UPDATE states SET capital = \'',capital,
'\', largest = \'',largest,'\' WHERE name = \'',state,'\';')
FROM city;


Запрос читает строки таблицы city и использует их для формирования предложения, обновляющего таблицу states. Выполним запрос и сохраним результат в tmp:

% mysql -N cookbook < update_cities.sql > tmp


Файл tmp будет содержать предложения, похожие на запросы, сформированные сценарием update_cities.pl. Считая, что столбцы capital и largest добавлены в таблицу states, вы можете выполнить предложения для обновления таблицы так:

% mysql cookbook < tmp

Добавлено: 23 Июля 2018 20:19:32 Добавил: Андрей Ковальчук

Вставка записей в таблицу, включающую значения из другой

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

Решение
Если предположить, что у вас есть справочная таблица, сопоставляющая имена идентификаторам, то можно создать запись с помощью предложения INSERT INTO ... SELECT, в котором SELECT выполняет просмотр имен для получения значения соответствующего идентификатора.

Обсуждение
Справочные таблицы часто использовались в этой главе в соединениях, обычно для сопоставления идентификаторам или кодам значений описательных имен. Но справочные таблицы полезны не только для предложенийSELECT, они могут помочь и при создании новых записей. Давайте вернемся к таблицам artist и painting, содержащим информацию о вашей коллекции произведений искусства. Предположим, что вы едете в Миннесоту, где удается приобрести всего за 51 доллар репродукцию картины «Жонглеры» («Les jongleurs») Ренуара. Ренуар уже отмечен в таблице artist, так что нет необходимости добавлять туда новую запись. Но нужно добавить запись в таблицу painting. Чтобы создать ее, необходимо сохранить идентификатор художника, название картины, штат покупки и цену. Вы знаете все, кроме идентификатора автора, и не хотите сами искать его в таблице artist. Раз Ренуар там уже есть, почему бы не позволить MySQL найти для вас его идентификатор? Для добавления новой записи выполним предложение INSERT ...SELECT. Укажем все известные значения в списке столбцов вывода SELECT и используем инструкцию WHERE для поиска идентификатора художника по его фамилии:

mysql> INSERT INTO painting (a_id, title, state, price)
-> SELECT a_id, 'Les jongleurs', 'MN', 51
-> FROM artist WHERE name = 'Renoir';


Естественно, вам не захочется писать вручную полный текст этого запроса при каждой новой покупке. Но легко можно написать небольшой сценарий, который по фамилии художника, названию картины, штату и стоимости покупки формировал бы запрос и выполнял его для вас. Также можно написать код для проверки вхождения художника в таблицу artist и, в случае его отсутствия, генерирования нового значения идентификатора для художника перед выполнением вставки новой записи в painting. Просто выполните следующий запрос перед созданием новой строки в таблице painting:

INSERT IGNORE INTO artist (name) VALUES('фамилия художника');

Добавлено: 23 Июля 2018 19:22:52 Добавил: Андрей Ковальчук

Параллельный выбор записей из нескольких таблиц

Задача
Вы хотите выбирать строки одну за другой из нескольких таблиц или выбирать несколько наборов строк из одной таблицы – все в одно результирующее множество.

Решение
Используйте операцию UNION для соединения нескольких результирующих множеств в одно.

Обсуждение
Соединение JOIN удобно для расположения столбцов из разных таблиц вместе бок о бок. Но оно не подходит, если вас интересует результирующее множество, которое содержит набор строк из нескольких таблиц, следующих одна за другой, или несколько наборов строк одной таблицы. Для таких операций удобно использовать объединение – UNION. Объединение UNION позволяет запустить несколько предложений SELECT и «вертикально» склеить их результаты. Вы получаете вывод в одном результирующем множестве вместо того, чтобы запускать несколько запросов и получать несколько результирующих множеств.

Операция UNION появилась в версии MySQL 4.0. В этом разделе показано, как ее использовать, и описано несколько способов решения задач в старых версиях MySQL.

Предположим, что у вас есть две таблицы со списками потенциальных и фактических клиентов (prospect и customer) и третья (vendor), перечисляющая ваших поставщиков. Вы хотите создать единый список рассылки, объединив имена и адреса из всех трех таблиц. UNION обеспечивает решение такой задачи. Предположим, что таблицы имеют такое содержимое:

mysql> SELECT * FROM prospect;


+--------+--------+----------------------------+
| fname | lname | addr |
+--------+--------+----------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
+--------+---------+----------------------------+

mysql> SELECT * FROM customer;


+------------+--------------+--------------------------+
| last_name | first_name | address |
+------------+-------------+---------------------------+
| Peterson | Grace | 16055 Seminole Ave. |
| Smith | Bernice | 916 Maple Dr. |
| Brown | Walter | 8602 1st St. |
+------------+--------------+--------------------------+

mysql> SELECT * FROM vendor;


+---------------------+--------------------------+
| company | street |
+---------------------+--------------------------+
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+--------------------+---------------------------+

Столбцы таблиц похожи, но не идентичны: prospect и customer используют разные имена для столбцов имени и фамилии, а в таблице vendor всего один столбец для названия компании. Все это не имеет никакого значения для UNION; единственное, в чем необходимо убедиться, – изо всех таблиц должно выбираться одинаковое количество столбцов и в одинаковом порядке. Следующий запрос показывает, как выбирать имена и адреса из трех таблиц одновременно:

mysql> SELECT fname, lname, addr FROM prospect
-> UNION
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;

+-------------------+------------+---------------------------+
| fname | lname | addr |
+-------------------+------------+---------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
| Grace | Peterson | 16055 Seminole Ave. |
| Walter | Brown | 8602 1st St. |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | | 213B Commerce Park. |
+--------------------+-----------+----------------------------+

Имена и типы в результирующем множестве определяются именами и типами столбцов, извлеченных первым предложением SELECT. Обратите внимание, что по умолчанию UNION удаляет повторения; Bernice Smith присутствует и в таблице prospect, и в таблице customer, но результирующее множество содержит только одну такую запись. Если вы хотите выбрать все записи, включая повторения, укажите после первого ключевого слова UNION ключевое слово ALL:

mysql> SELECT fname, lname, addr FROM prospect
-> UNION ALL
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;


+--------------------+----------+-----------------------------+
| fname | lname | addr |
+--------------------+----------+-----------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
| Grace | Peterson | 16055 Seminole Ave. |
| Bernice | Smith | 916 Maple Dr. |
| Walter | Brown | 8602 1st St. |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | | 213B Commerce Park. |
+--------------------+-----------+---------------------------+

Поскольку необходимо выбирать одинаковое количество столбцов из всех таблиц, предложение SELECT для таблицы vendor (в которой всего один столбец для названия компании) извлекает фиктивный (пустой) столбец названия. Можно было бы обеспечить одинаковое количество столбцов и другим способом: объединив столбцы имени и фамилии таблиц prospect и customer в один столбец:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
-> UNION
-> SELECT CONCAT(last_name,', ',first_name), address FROM customer
-> UNION
-> SELECT company, street FROM vendor;


+--------------------+----------------------------+
| name | addr |
+--------------------+----------------------------+
| Jones, Peter | 482 Rush St., Apt. 402 |
| Smith, Bernice | 916 Maple Dr. |
| Peterson, Grace | 16055 Seminole Ave. |
| Brown, Walter | 8602 1st St. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+--------------------+-----------------------------+

Для того чтобы упорядочить все результирующее множество, добавьте инструкцию ORDER BY после последнего предложения SELECT. Если вы в инструкции ORDER BY ссылаетесь на столбцы по имени, то это должны быть имена из первого предложения SELECT, так как именно они используются в результирующем множестве. Например, для сортировки по полю name выполните следующее:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
-> UNION
-> SELECT CONCAT(last_name,', ',first_name), address FROM customer
-> UNION
-> SELECT company, street FROM vendor
-> ORDER BY name;


+--------------------+----------------------------+
| name | addr |
+--------------------+----------------------------+
| Brown, Walter | 8602 1st St. |
| Jones, Peter | 482 Rush St., Apt. 402 |
| Parts-to-go, Ltd. | 213B Commerce Park. |
| Peterson, Grace | 16055 Seminole Ave. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Smith, Bernice | 916 Maple Dr. |
+--------------------+----------------------------+

В MySQL есть возможность сортировки результатов отдельных предложений SELECT внутри UNION. Для этого заключим указанное предложение SELECT (включая его инструкцию ORDER BY) в скобки:

mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr
-> FROM prospect ORDER BY 1)
-> UNION
-> (SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer ORDER BY 1)
-> UNION
-> (SELECT company, street FROM vendor ORDER BY 1);


+--------------------+----------------------------+
| name | addr |
+--------------------+----------------------------+
| Jones, Peter | 482 Rush St., Apt. 402 |
| Smith, Bernice | 916 Maple Dr. |
| Brown, Walter | 8602 1st St. |
| Peterson, Grace | 16055 Seminole Ave. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
+--------------------+-----------------------------+

Похожий синтаксис можно использовать и для инструкции LIMIT. То есть вы можете ограничить все результирующее множество, используя инструкцию LIMIT в самом конце предложения, а можете применять ее к отдельным запросам SELECT, заключая их в скобки. В некоторых случаях разумно исполь зовать ORDER BY в сочетании с LIMIT. Предположим, вы хотите выбрать счастливого призера какой-то рекламной кампании. Чтобы выбрать победителя случайным образом из всего результирующего множества для трех таблиц, сделайте следующее:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
-> UNION
-> SELECT CONCAT(last_name,', ',first_name), address FROM customer
-> UNION
-> SELECT company, street FROM vendor
-> ORDER BY RAND() LIMIT 1;


+-------------------+--------------------------+
| name | addr |
+-------------------+--------------------------+
| Peterson, Grace | 16055 Seminole Ave. |
+-------------------+--------------------------+

Чтобы выбрать победителя в каждой таблице, сделайте так:

mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr
-> FROM prospect ORDER BY RAND() LIMIT 1)
-> UNION
-> (SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer ORDER BY RAND() LIMIT 1)
-> UNION
-> (SELECT company, street-> FROM vendor ORDER BY RAND() LIMIT 1);


+---------------------+----------------------+
| name | addr |
+---------------------+----------------------+
| Smith, Bernice | 916 Maple Dr. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
+---------------------+----------------------+

Если вас удивит результат (почему выбрано не три строки?), вспомните о присутствии Bernice в двух таблицах и о том, что UNION удаляет повторения.

Если случилось так, что и первый и второй запросы SELECT выбрали Bernice, одна из строк результата будет удалена. (Если в трех таблицах нет повторяющихся строк, запрос всегда будет возвращать три строки.) Естественно, вы можете обеспечить вывод трех записей, используя UNION ALL или запуская предложения SELECT по отдельности.

Если у вас более ранняя версия MySQL, чем 4.0, вы не можете использовать UNION. Но можете получить те же результаты, создав временную таблицу, сохраняя результаты нескольких запросов SELECT в этой таблице и выбирая ее содержимое. В MySQL 3.23 можно создать временную таблицу (CREATE TABLE ... SELECT для первого предложения SELECT), а затем последовательно извлекать в нее другие результирующие множества:

mysql> CREATE TABLE tmp SELECT CONCAT(lname,', ',fname) AS name, addr
-> FROM prospect;
mysql> INSERT INTO tmp (name, addr)
-> SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer;
mysql> INSERT INTO tmp (name, addr)
-> SELECT company, street FROM vendor;


Если ваша версия MySQL старше, чем 3.23, сначала создайте таблицу, затем выберите в нее каждую запись:

mysql> CREATE TABLE tmp (name CHAR(40), addr CHAR(40));
mysql> INSERT INTO tmp (name, addr)
-> SELECT CONCAT(lname,', ',fname), addr
-> FROM prospect;
mysql> INSERT INTO tmp (name, addr)
-> SELECT CONCAT(last_name,', ',first_name), address
-> FROM customer;
mysql> INSERT INTO tmp (name, addr)
-> SELECT company, street FROM vendor;


После вставки отдельных результатов во временную таблицу выберите ее содержимое:

mysql> SELECT * FROM tmp;


+--------------------+----------------------------+
| name | addr |
+--------------------+----------------------------+
| Jones, Peter | 482 Rush St., Apt. 402 |
| Smith, Bernice | 916 Maple Dr. |
| Peterson, Grace | 16055 Seminole Ave. |
| Smith, Bernice | 916 Maple Dr. |
| Brown, Walter | 8602 1st St. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+---------------------+---------------------------+

Обратите внимание на то, что результат больше похож на UNION ALL, а не на UNION, так как повторения не удалены. Чтобы удалить из вывода дубликаты, создадим таблицу с уникальным индексом для столбцов name и addr:

mysql> CREATE TABLE tmp (name CHAR(40), addr CHAR(40), UNIQUE (name, addr));
mysql> INSERT INTO ...
...
mysql> SELECT * FROM tmp;


+---------------------+----------------------------+
| name | addr |
+---------------------+----------------------------+
| Brown, Walter | 8602 1st St. |
| Jones, Peter | 482 Rush St., Apt. 402 |
| Parts-to-go, Ltd. | 213B Commerce Park. |
| Peterson, Grace | 16055 Seminole Ave. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Smith, Bernice | 916 Maple Dr. |
+---------------------+----------------------------+

Если таблица создается без уникального индекса, то можно удалить повторения при извлечении, используя DISTINCT, хотя это менее эффективно.

Добавлено: 23 Июля 2018 19:22:18 Добавил: Андрей Ковальчук

Преобразование подзапросов в операции соединения

Задача
Вы хотите использовать запрос, который включает в себя подзапрос, но MySQL будет поддерживать подзапросы только с версии 4.1.

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

Обсуждение
Предположим, что у вас есть две таблицы t1 и t2 с таким содержимым:

mysql> SELECT col1 FROM t1;


+-----+
| col1 |
+-----+
| a |
| b |
| c |
+-----+

mysql> SELECT col2 FROM t2;


+-----+
| col2 |
+-----+
| b |
| c |
| d |
+-----+

Теперь предположим, что вы хотите найти те значения t1, которые содержатся и в t2, или значения t1, которые не содержатся в t2. На такие вопросы можно ответить при помощи подзапросов – вложения одного предложения SELECT внутрь другого, но MySQL не поддерживает подзапросы в версиях, предшествующих 4.1. Этот раздел посвящен тому, как обойти эту проблему.

Следующий запрос содержит подзапрос IN(), который выводит строки t1, значения столбца col1 которых совпадает со значениями col2 таблицы t2:

SELECT col1 FROM t1 WHERE col1 IN (SELECT col2 FROM t2);


Фактически это запрос поиска соответствий, так что его можно переписать при помощи простого соединения:

mysql> SELECT t1.col1 FROM t1, t2 WHERE t1.col1 = t2.col2;


+-----+
| col1 |
+-----+
| b |
| c |
+-----+

На обратный вопрос (строки t1, не имеющие соответствий в t2) можно ответить, используя подзапрос NOT IN():

SELECT col1 FROM t1 WHERE col1 NOT IN (SELECT col2 FROM t2);


Это задача отсутствия соответствий, которую можно решить при помощи LEFT JOIN, разновидности соединения. В нашем случае подзапрос NOT IN() эквивалентен такому левому соединению:

mysql> SELECT t1.col1 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2
-> WHERE t2.col2 IS NULL;


+-----+
| col1 |
+-----+
| a |
+-----+

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

SELECT * FROM t1 WHERE col1 IN (SELECT col2 FROM t2);


Если вы ожидаете, что внутренний SELECT вернет достаточно небольшое количество значений col2, то того же результата, что и подзапрос, можно достичь путем извлечения этих значений и формирования инструкции IN(), которая будет искать их в col1. Например, запрос SELECT col2 FROM t2 выводит значения b, c и d. Используя этот результат, можно выбрать соответствующие значения col1 таким запросом:

SELECT col1 FROM t1 WHERE col1 IN ('b','c','d')


Рассмотрим фрагмент кода на Python:

cursor = conn.cursor ()
cursor.execute ("SELECT col2 FROM t2")
if cursor.rowcount > 0: # ничего не делать, если нет значений
val = [] # список для хранения значений данных
s = "" # строка для хранения заполнителей
# сформировать строку с заполнителями: %s,%s,%s,...
for (col2,) in cursor.fetchall (): # извлечь значение col2 из каждой строки
if s != "":
s = s + "," # разделить заполнители запятыми
s = s + "%s" # добавить заполнитель
val.append (col2) # добавить значение в список значений
stmt = "SELECT col1 FROM t1 WHERE col1 IN (" + s + ")"
cursor.execute (stmt, val)
for (col1,) in cursor.fetchall (): # извлечь значения col1 из результата
print col1
cursor.close ()


Если вы предполагаете, что значений col2 будет очень много, можно сформировать отдельные предложения SELECT для каждого из них:

SELECT col1 FROM t1 WHERE col1 = 'b'
SELECT col1 FROM t1 WHERE col1 = 'c'
SELECT col1 FROM t1 WHERE col1 = 'd'

В программе это можно сделать так:
cursor = conn.cursor ()
cursor2 = conn.cursor ()
cursor.execute ("SELECT col2 FROM t2")
for (col2,) in cursor.fetchall (): # извлечь значение col2 из каждой строки
stmt = "SELECT col1 FROM t1 WHERE col1 = %s"
cursor2.execute ("SELECT col1 FROM t1 WHERE col1 = %s", (col2,))
for (col1,) in cursor2.fetchall (): # извлечь значения col1 из результата
print col1
cursor.close ()
cursor2.close ()


Если у вас так много значений col2, что вы не хотите писать одну огромную инструкцию IN(), но не хотите и выдавать несметное множество отдельных предложений SELECT, то можно скомбинировать два этих способа. Разбейте множество значений col2 на более мелкие группы и создайте для каждой из них инструкцию IN(). У вас появится ряд менее громоздких запросов, каждый из которых ищет несколько значений:

SELECT col1 FROM t1 WHERE col1 IN (первая группа значений col2)
SELECT col1 FROM t1 WHERE col1 IN (вторая группа значений col2)
SELECT col1 FROM t1 WHERE col1 IN (третья группа значений col2)
...


Комбинированный подход можно реализовать так:

grp_size = 1000 # количество идентификаторов, выбираемых одновременно
cursor = conn.cursor ()
cursor.execute ("SELECT col2 FROM t2")
if cursor.rowcount > 0: # ничего не делать, если нет значений
col2 = [] # список для хранения значений данных
for (val,) in cursor.fetchall (): # извлечь значение col2 из каждой строки
col2.append (val)
nvals = len (col2)
i = 0
while i < nvals:
if nvals < i + grp_size:
j = nvals
else:
j = i + grp_size
group = col2[i : j]
s = "" # строка для хранения заполнителей
val_list = []
# сформировать строку с заполнителями: %s,%s,%s,...
for val in group:
if s != "":s = s + "," # разделить заполнители запятыми
s = s + "%s" # добавить заполнитель
val_list.append (val) # добавить значение в список значений
stmt = "SELECT col1 FROM t1 WHERE col1 IN (" + s + ")"
print stmt
cursor.execute (stmt, val_list)
for (col1,) in cursor.fetchall (): # извлечь значение col1 из каждой строки
print col1
i = i + grp_size # перейти к следующей группе значений
cursor.close ()


Имитировать в программе подзапрос NOT IN() несколько сложнее, чем подзапрос IN(). Подзапрос выглядит так:

SELECT col1 FROM t1 WHERE col1 NOT IN (SELECT col2 FROM t2);


Описываемый метод лучше всего работает для небольшого количества значений col1 и col2, так как приходится в хранить в памяти как минимум значения, возвращенные внутренним SELECT, чтобы можно было сравнивать их со значениями, возвращенными внешним SELECT. Приведенный пример хранит в памяти оба множества. Сначала извлечем значения col1 и col2:

cursor = conn.cursor ()
cursor.execute ("SELECT col1 FROM t1")
col1 = []
for (val, ) in cursor.fetchall ():
col1.append (val)
cursor.execute ("SELECT col2 FROM t2")
col2 = []
for (val, ) in cursor.fetchall ():
col2.append (val)
cursor.close ()


Затем проверим каждое значение col1 на предмет присутствия в наборе значений col2. Если не присутствует, то оно удовлетворяет условию NOT IN() подзапроса:

for val1 in col1:
present = 0
for val2 in col2:
if val1 == val2:
present = 1
break
if not present:
print val1


Код выполняет просмотр (lookup) значений col2 в содержащем их массиве. Можно повысить эффективность этой операции, используя ассоциативную структуру данных. Например, в Perl или Python можно поместить значения col2 в хеш или словарь. Еще одним способом имитации подзапросов (по крайней мере, подзапросов типа IN()), является формирование необходимых предложений SQL внутриодного экземпляра mysql и передача их другому экземпляру на исполнение.

Посмотрите на результат такого запроса:

mysql> SELECT CONCAT('SELECT col1 FROM t1 WHERE col1 = \'', col2, '\';')
-> FROM t2;


+------------------------------------------------------------------------+
| CONCAT('SELECT col1 FROM t1 WHERE col1 = \'', col2, '\';') |
+------------------------------------------------------------------------+
| SELECT col1 FROM t1 WHERE col1 = 'b'; |
| SELECT col1 FROM t1 WHERE col1 = 'c'; |
| SELECT col1 FROM t1 WHERE col1 = 'd'; |
+------------------------------------------------------------------------+

Этот запрос извлекает значения col2 из t2 и использует их для формирования набора предложений SELECT, которые ищут соответствующие значения col1 в t1. Если вы выдаете запрос в пакетном режиме и подавляете вывод заголовков, то mysql выводит только текст предложений SQL безо всякого обрамления. Этот вывод можно передать другому экземпляру mysql для выполнения запросов. Результат будет таким же, как при выполнении подзапроса. Предлагаю один из способов выполнения процедуры, в котором предполагается, что предложение SELECT, содержащее выражение CONCAT(), хранится в файле make_select.sql:

% mysql -N cookbook < make_select.sql > tmp


Для mysql указана опция -N, определяющая необходимость подавления вывода заголовков, так что они не попадут в файл вывода tmp. Содержимое tmp будет таким:

SELECT col1 FROM t1 WHERE col1 = 'b';
SELECT col1 FROM t1 WHERE col1 = 'c';
SELECT col1 FROM t1 WHERE col1 = 'd';


Чтобы выполнить запросы из файла и сформировать вывод, имитируя подзапрос, используйте такую команду:

% mysql -N cookbook < tmp

b
c

Второй экземпляр mysql также содержит опцию -N, поскольку в противном случае вывод будет содержать строку заголовка каждого выполненного предложения SELECT (попробуйте не указывать -N и посмотрите, что получится). Важным ограничением использования mysql для формирования предложений SQL является следующее: если значения col2 содержат кавычки или другие специальные символы, то генерируемые запросы будут построены некорректно.

Добавлено: 23 Июля 2018 19:20:50 Добавил: Андрей Ковальчук

Управление порядком вывода запроса с помощью соединения

Задача
Вы хотите упорядочить вывод запроса, используя ту его характеристику, которую невозможно указать в инструкции ORDER BY. Например, вы хотите отсортировать строки по подгруппам, выводя первыми те группы, в которых больше всего строк, а последними – группы с наименьшим количеством строк. Но «количество строк группы» – это не свойство отдельных строк, поэтому вы не можете упорядочивать по нему.

Решение
Извлеките информацию о порядке вывода и сохраните в другой таблице. Затем объедините исходную таблицу с производной, используя производную для управления порядком сортировки.

Обсуждение
Обычно при сортировке результата запроса применяется инструкция ORDER BY (или GROUP BY) для указания имени столбца или столбцов, по которым нужно производить сортировку. Но иногда требуется выполнить сортировку по значениям, которые не содержатся в упорядочиваемых строках. Именно так дело обстоит с использованием групповых характеристик для упорядочивания строк. Следующий пример использует записи таблицы driver_log для иллюстрации вышесказанного:

mysql> SELECT * FROM driver_log ORDER BY id;


+-------+-------+--------------+-------+
| rec_id | name | trav_date | miles |
+-------+-------+--------------+-------+
| 1 | Ben | 2001-11-30 | 152 |
| 2 | Suzi | 2001-11-29 | 391 |
| 3 | Henry | 2001-11-29 | 300 |
| 4 | Henry | 2001-11-27 | 96 |
| 5 | Ben | 2001-11-29 | 131 |
| 6 | Henry | 2001-11-26 | 115 |
| 7 | Suzi | 2001-12-02 | 502 |
| 8 | Henry | 2001-12-01 | 197 |
| 9 | Ben | 2001-12-02 | 79 |
| 10 | Henry | 2001-11-30 | 203 |
+-------+--------+--------------+------+

Такой запрос сортирует записи по столбцу идентификаторов, содержащемуся в строках. А если нам нужно вывести список и упорядочить его на основе суммарного значения, которое не присутствует в строках? Это немного сложнее. Предположим, что вы хотите вывести записи каждого водителя в порядке возрастания даты, при этом начать с тех водителей, которые проехалисамое больше расстояние. Выполнить подобную операцию при помощи суммарного запроса невозможно, так как тогда нельзя будет вывести записи для отдельных водителей. Но и обойтись без суммарного запроса невозможно, так как для сортировки необходимо итоговое значение. Выйдем из затруднительного положения, создав новую таблицу, содержащую суммарные значения, и объединим ее с исходной. Так мы сможем и вывести индивидуальные записи, и отсортировать их по суммарным значениям.

Чтобы сохранить итоговую информацию о километраже водителей, выполним следующие предложения:

mysql> CREATE TABLE tmp
-> SELECT name, SUM(miles) AS driver_miles FROM driver_log GROUP BY name;

Сформируем значения, которые необходимы для размещения имен (name) в нужном порядке:

mysql> SELECT * FROM tmp ORDER BY driver_miles DESC;


+-------+--------------+
| name | driver_miles |
+-------+--------------+
| Henry | 911 |
| Suzi | 893 |
| Ben | 362 |
+-------+--------------+

Затем используем значения name для соединения суммарной таблицы с таблицей driver_log, пользуясь значениями driver_miles для упорядочивания результата. Приведенный ниже запрос выводит общее количество километров. Это сделано только для того, чтобы было понятнее, как сортируются записи, нет никакой необходимости в их выводе. Нужны они только в инструкции ORDER BY.

mysql> SELECT tmp.driver_miles, driver_log.*
-> FROM driver_log, tmp
-> WHERE driver_log.name = tmp.name
-> ORDER BY tmp.driver_miles DESC, driver_log.trav_date;


+--------------+--------+--------+--------------+-------+
| driver_miles | rec_id | name | trav_date | miles |
+--------------+--------+--------+--------------+-------+
| 911 | 6 | Henry | 2001-11-26 | 115 |
| 911 | 4 | Henry | 2001-11-27 | 96 |
| 911 | 3 | Henry | 2001-11-29 | 300 |
| 911 | 10 | Henry | 2001-11-30 | 203 |
| 911 | 8 | Henry | 2001-12-01 | 197 |
| 893 | 2 | Suzi | 2001-11-29 | 391 |
| 893 | 7 | Suzi | 2001-12-02 | 502 |
| 362 | 5 | Ben | 2001-11-29 | 131 |
| 362 | 1 | Ben | 2001-11-30 | 152 |
| 362 | 9 | Ben | 2001-12-02 | 79 |
+--------------+--------+--------+--------------+-------+

Добавлено: 23 Июля 2018 19:18:34 Добавил: Андрей Ковальчук

Нарастающий итог и скользящее среднее

Задача
У вас есть ряд наблюдений, сделанных на протяжении какого-то периода времени, и вы хотите вычислить сумму нарастающим итогом (cumulative sum) для каждой точки измерения. Или же хотите получить скользящее среднее (running average) в каждой точке.

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

Обсуждение
В рецепте 12.12 было показано, как самосоединение может формировать относительные значения из абсолютных. Самосоединение может делать и обратное, формируя накапливаемые значения для каждого последовательного этапа серии наблюдений. В следующей таблице приведены результаты измерений количества осадков, сделанных в течение нескольких дней. Значения каждой строки представляют дату наблюдения и количество осадков в дюймах:mysql> SELECT date, precip FROM rainfall ORDER BY date;

+--------------+--------+
| date | precip |
+--------------+--------+
| 2002-06-01 | 1.50 |
| 2002-06-02 | 0.00 |
| 2002-06-03 | 0.50 |
| 2002-06-04 | 0.00 |
| 2002-06-05 | 1.00 |
+--------------+--------+

Чтобы вычислить совокупное количество осадков на указанный день, сложим значение количества осадков в данный день со значениями всех предыдущих дней. Например, совокупное количество осадков на 2002-06-03 определяется так:

mysql> SELECT SUM(precip) FROM rainfall WHERE date <= '2002-06-03';

+---------------+
| SUM(precip) |
+---------------+
| 2.00 |
+---------------+

Если вы хотите получить совокупные значения для всех дней, представленных в таблице, то подсчет всех этих значений по отдельности будет весьма трудоемким. Самосоединение может получить совокупные значения для всех дней в одном запросе. Используйте один экземпляр таблицы rainfall как справочную таблицу и определите для даты каждой строки сумму значений precip для всех строк с датами, вплоть до данной даты в другом экземпляре таблицы. Следующий запрос выводит дневное и совокупное количества осадков для каждого дня таблицы:

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;


+--------------+--------------+--------------+
| date | daily precip | cum. precip |
+--------------+--------------+--------------+
| 2002-06-01 | 1.50 | 1.50 |
| 2002-06-02 | 0.00 | 1.50 |
| 2002-06-03 | 0.50 | 2.00 |
| 2002-06-04 | 0.00 | 2.00 |
| 2002-06-05 | 1.00 | 3.00 |
+--------------+--------------+--------------+

Самосоединение можно расширить так, чтобы оно выводило количество дней, прошедших до указанной даты, и вычисляло скользящее среднее для количества осадков за каждый день:

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip',-> COUNT(t2.precip) AS days,
-> AVG(t2.precip) AS 'avg. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;


+--------------+--------------+--------------+------+--------------+
| date | daily precip | cum. precip | days | avg. precip |
+--------------+--------------+--------------+------+--------------+
| 2002-06-01 | 1.50 | 1.50 | 1 | 1.500000 |
| 2002-06-02 | 0.00 | 1.50 | 2 | 0.750000 |
| 2002-06-03 | 0.50 | 2.00 | 3 | 0.666667 |
| 2002-06-04 | 0.00 | 2.00 | 4 | 0.500000 |
| 2002-06-05 | 1.00 | 3.00 | 5 | 0.600000 |
+--------------+--------------+-------------+-------+--------------+

В предыдущем запросе количество прошедших дней и средний объем осадков можно легко получить при помощи COUNT() и AVG(), так как в таблице нет пропущенных дней. Если же было бы разрешено пропускать дни, вычисление усложнилось бы, так как в этом случае количество дней, прошедших до указанной даты, не было бы равно количеству записей. Давайте удалим из таблицы записи для тех дней, в которые не было осадков, создав тем самым две «дыры»:

mysql> DELETE FROM rainfall WHERE precip = 0;
mysql> SELECT date, precip FROM rainfall ORDER BY date;


+--------------+--------+
| date | precip |
+--------------+--------+
| 2002-06-01 | 1.50 |
| 2002-06-03 | 0.50 |
| 2002-06-05 | 1.00 |
+--------------+--------+

Удаление таких записей не изменяет накапливаемую сумму и скользящее среднее, но изменяет способ их вычисления. Если попытаться опять выполнить самосоединение, то результат для количества прошедших дней и среднего количества осадков будет неверным:

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip',
-> COUNT(t2.precip) AS days,
-> AVG(t2.precip) AS 'avg. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;


+--------------+--------------+--------------+------+--------------+
| date | daily precip | cum. precip | days | avg. precip |
+--------------+--------------+--------------+------+--------------+
| 2002-06-01 | 1.50 | 1.50 | 1 | 1.500000 |
| 2002-06-03 | 0.50 | 2.00 | 2 | 1.000000 |
| 2002-06-05 | 1.00 | 3.00 | 3 | 1.000000 |
+--------------+--------------+-------------+-------+--------------+

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

TO_DAYS(MAX(t2.date)) - TO_DAYS(MIN(t2.date)) + 1


Это значение должно применяться для столбца количества дней и для вычисления среднего. В результате запрос будет таким:

mysql> SELECT t1.date, t1.precip AS 'daily precip',
-> SUM(t2.precip) AS 'cum. precip',
-> TO_DAYS(MAX(t2.date)) - TO_DAYS(MIN(t2.date)) + 1 AS days,
-> SUM(t2.precip) / (TO_DAYS(MAX(t2.date)) - TO_DAYS(MIN(t2.date)) + 1)
-> AS 'avg. precip'
-> FROM rainfall AS t1, rainfall AS t2
-> WHERE t1.date >= t2.date
-> GROUP BY t1.date;


+--------------+--------------+--------------+------+--------------+
| date | daily precip | cum. precip | days | avg. precip |
+--------------+--------------+--------------+------+--------------+
| 2002-06-01 | 1.50 | 1.50 | 1 | 1.5000 |
| 2002-06-03 | 0.50 | 2.00 | 3 | 0.6667 |
| 2002-06-05 | 1.00 | 3.00 | 5 | 0.6000 |
+--------------+--------------+--------------+------+---------------+

Как видно из примера, для вычисления накопленных значений по относительным необходим лишь столбец, позволяющий расположить строки в правильном порядке. (В таблице rainfall это столбец date.) Значения такого столбца не должны быть последовательными и даже не обязаны быть числами. В этом отличие от вычисления разностей значений для накопленных значений, которое требует наличия в таблице столбца с непрерывной последовательностью.

Скользящие средние в примере про осадки вычисляются делением суммы количества осадков за определенное количество дней на количество дней. Если в таблице нет разрывов, количество дней соответствует количеству суммируемых записей, поэтому находить последовательные средние значения очень просто. Если же какие-то записи отсутствуют, вычисления усложняются. То есть необходимо внимательно исследовать данные и выбрать подходящий тип вычисления средних. Следующий пример похож на предыдущие тем, что он тоже вычисляет суммы нарастающим итогом и скользящее среднее, но вычисления он выполняет по-другому.

Следующая таблица приводит характеристики марафонца на каждом этапе 26-километровой дистанции. Значения каждой строки показывают длину этапа в километрах и время, затраченное бегуном на его преодоление. Другими словами, величины относятся к интервалам внутри марафона, то есть являются относительными:

mysql> SELECT stage, km, t FROM marathon ORDER BY stage;


+-------+----+----------+
| stage | km | t |
+-------+----+----------+
| 1 | 5 | 00:15:00 |
| 2 | 7 | 00:19:30 |
| 3 | 9 | 00:29:20 |
| 4 | 5 | 00:17:50 |
+-------+----+----------+

Чтобы вычислить накапливаемые расстояния в километрах для каждого этапа, выполним самосоединение:

mysql> SELECT t1.stage, t1.km, SUM(t2.km) AS 'cum. km'
-> FROM marathon AS t1, marathon AS t2
-> WHERE t1.stage >= t2.stage
-> GROUP BY t1.stage;


+-------+----+----------+
| stage | km | cum. km |
+-------+----+----------+
| 1 | 5 | 5 |
| 2 | 7 | 12 |
| 3 | 9 | 21 |
| 4 | 5 | 26 |
+-------+----+----------+

Получить накопленные расстояния легко, так как их можно просто складывать. Накопление времени чуть сложнее. Необходимо преобразовать время в секунды, суммировать полученные значения, затем преобразовать сумму обратно в значение времени. Чтобы вычислить среднюю скорость бегуна на каждом этапе, вычислим отношение накопленного расстояния к накопленному времени. Теперь соберем все вместе и напишем такой запрос:

mysql> SELECT t1.stage, t1.km, t1.t,
-> SUM(t2.km) AS 'cum. km',
-> SEC_TO_TIME(SUM(TIME_TO_SEC(t2.t))) AS 'cum. t',
-> SUM(t2.km)/(SUM(TIME_TO_SEC(t2.t))/(60*60)) AS 'avg. km/hour'
-> FROM marathon AS t1, marathon AS t2
-> WHERE t1.stage >= t2.stage
-> GROUP BY t1.stage;


+-------+----+-----------+----------+-----------+-----------------+
| stage | km | t | cum. km | cum. t | avg. km/hour |
+-------+----+-----------+----------+-----------+-----------------+
| 1 | 5 | 00:15:00 | 5 | 00:15:00 | 20.0000 |
| 2 | 7 | 00:19:30 | 12 | 00:34:30 | 20.8696 |
| 3 | 9 | 00:29:20 | 21 | 01:03:50 | 19.7389 |
| 4 | 5 | 00:17:50 | 26 | 01:21:40 | 19.1020 |
+-------+----+-----------+----------+------------+----------------+

Видно, что бегун прибавил темп на втором этапе гонки, но потом, видимо, в результате усталости, только снижал его.

Добавлено: 23 Июля 2018 19:17:46 Добавил: Андрей Ковальчук

Вычисление разности между последовательными строками

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

Решение
Используйте самосоединение, сопоставляющее пары соседних строк и вычисляющее разность между членами пары.

Обсуждение
Самосоединения полезны, если у вас есть набор абсолютных (или накопленных) значений, которые вы хотите преобразовать в относительные значения, представляющие разности последовательных пар строк. Например, если вы едете в путешествие на автомобиле и записываете количество миль на каждой остановке, то можете вычислить разность расстояний до последовательных точек, чтобы определить расстояние между ними. Рассмотрим таблицу, предствляющую остановки на пути из Сан-Антонио, штат Техас, в Мэдисон, штат Висконсин. Каждая строка содержит общее количество миль, пройденных до данной остановки:

mysql> SELECT seq, city, miles FROM trip_log ORDER BY seq;


+----+--------------------+-------+
| seq | city | miles |
+----+--------------------+-------+
| 1 | San Antonio, TX | 0 |
| 2 | Dallas, TX | 263 |
| 3 | Benton, AR | 566 |
| 4 | Memphis, TN | 745 |
| 5 | Portageville, MO | 878 |
| 6 | Champaign, IL | 1164 |
| 7 | Madison, WI | 1412 |
+-----+-------------------+-------+

Самосоединение может преобразовать эти накапливаемые значения в последовательные разности, представляющие расстояния от каждого города до следующего. Напишем запрос, который использует номера последовательности в записях для нахождения пар соседних строк и вычисления разностей для каждой пары значений расстояний:

mysql> SELECT t1.seq AS seq1, t2.seq AS seq2,
-> t1.city AS city1, t2.city AS city2,
-> t1.miles AS miles1, t2.miles AS miles2,
-> t2.miles-t1.miles AS dist
-> FROM trip_log AS t1, trip_log AS t2-> WHERE t1.seq+1 = t2.seq
-> ORDER BY t1.seq;


+------+------+--------------------+-------------------+--------+--------+------+
| seq1 | seq2 | city1 | city2 | miles1 | miles2 | dist |
+------+------+--------------------+-------------------+--------+--------+------+
| 1 | 2 | San Antonio, TX | Dallas, TX | 0 | 263 | 263 |
| 2 | 3 | Dallas, TX | Benton, AR | 263 | 566 | 303 |
| 3 | 4 | Benton, AR | Memphis, TN | 566 | 745 | 179 |
| 4 | 5 | Memphis, TN | Portageville, MO | 745 | 878 | 133 |
| 5 | 6 | Portageville, MO | Champaign, IL | 878 | 1164 | 286 |
| 6 | 7 | Champaign, IL | Madison, WI | 1164 | 1412 | 248 |
+------+------+--------------------+--------------------+-------+--------+------+

Наличие столбца seq в таблице trip_log необходимо для вычисления последовательных разностей: с его помощью устанавливается, какая строка предшествует другой строке, и выполняется сопоставление строки с номером n строке n+1. При выполнении вычислений разностей абсолютных (или накапливаемых) значений подразумевается, что таблица должна содержать столбец последовательности без разрывов (gap). Если таблица содержит столбец последовательности с разрывами, перенумеруйте его. Если в таблице вообще нет такого столбца, добавьте его.

Более сложная ситуация возникает, если нужно вычислить последовательные разности для нескольких столбцов, а затем использовать результаты в вычислении. Таблица player_stats предлагает несколько суммарных показателей для бейсбольного игрока в конце каждого месяца игрового сезона: ab означает общее количество выступлений в качестве отбивающего (at-bat), а h – общее количество отбитых подач (hit) на указанную дату. (Первая запись относится к началу сезона игрока, поэтому значения ab и h равны нулю.)

mysql> SELECT id, date, ab, h, TRUNCATE(IFNULL(h/ab,0),3) AS ba
-> FROM player_stats ORDER BY id;

+----+------------+-----+----+-------+
| id | date | ab | h | ba |
+----+------------+-----+----+-------+
| 1 | 2001-04-30 | 0 | 0 | 0.000 |
| 2 | 2001-05-31 | 38 | 13 | 0.342 |
| 3 | 2001-06-30 | 109 | 31 | 0.284 |
| 4 | 2001-07-31 | 196 | 49 | 0.250 |
| 5 | 2001-08-31 | 304 | 98 | 0.322 |
+----+------------+-----+----+-------+

Последний столбец результата запроса приводит среднее число очков отбивающего игрока (batting average) на каждую дату. Этот столбец не хранится в таблице, но легко вычисляется как отношение числа отбитых подач к числу выступлений в качестве отбивающего. Результат дает общее представление об изменении частоты попаданий игрока в течение сезона, но при этом не очень понятно, как она менялась от месяца к месяцу. Необходимо вычислить относительные разности между парами строк. Это легко сделать при помощи самосоединения, которое сопоставляет каждой строке n строку n +1 для вы-числения разностей между парами поданных и отбитых подач. Эти разности позволяют вычислить среднее число очков отбивающего за каждый месяц:

mysql> SELECT
-> t1.id AS id1, t2.id AS id2,
-> t2.date,
-> t1.ab AS ab1, t2.ab AS ab2,
-> t1.h AS h1, t2.h AS h2,
-> t2.ab-t1.ab AS abdiff,
-> t2.h-t1.h AS hdiff,
-> TRUNCATE(IFNULL((t2.h-t1.h)/(t2.ab-t1.ab),0),3) AS ba
-> FROM player_stats AS t1, player_stats AS t2
-> WHERE t1.id+1 = t2.id
-> ORDER BY t1.id;


+---+-----+--------------+-----+-----+----+----+-------+------+-------+
| id1 | id2 | date | ab1 | ab2 | h1 | h2 | abdiff | hdiff | ba |
+---+-----+--------------+-----+-----+----+----+-------+------+-------+
| 1 | 2 | 2001-05-31 | 0 | 38 | 0 | 13 | 38 | 13 | 0.342 |
| 2 | 3 | 2001-06-30 | 38 | 109 | 13 | 31 | 71 | 18 | 0.253 |
| 3 | 4 | 2001-07-31 | 109 | 196 | 31 | 49 | 87 | 18 | 0.206 |
| 4 | 5 | 2001-08-31 | 196 | 304 | 49 | 98 | 108 | 49 | 0.453 |
+---+-----+---------------+-----+----+----+----+-------+------+-------+

Результаты гораздо нагляднее, чем исходная таблица, показывают, что игрок начал сезон хорошо, потом, особенно в июле, у него наблюдался резкий спад. А вот в августе его результаты стали просто замечательными.

Добавлено: 23 Июля 2018 19:16:39 Добавил: Андрей Ковальчук

Сравнение таблицы с самой собой

Задача
Вы хотите сравнить записи таблицы с другими записями той же самой таблицы. Например, вы хотите найти в вашей коллекции все картины автора, написавшего «Едоки картофеля». Или хотите узнать, какие штаты из таблицы states вступили в Союз в том же году, что и Нью-Йорк (New York).Или хотите узнать, кто из людей, перечисленных в таблице profile, любит одни и те же блюда.

Решение
Задачи, требующие соединения таблицы с ней же самой, решаются с помощью операции самосоединения (self-join). Она во многом похожа на другие соединения, только вы должны всегда использовать псевдонимы таблиц, чтобы по-разному ссылаться на одну и ту же таблицу в запросе.

Обсуждение
Специальным случаем соединения двух таблиц является соединение таблицы с самой собой. Такая операция называется самосоединением. Хотя многим подобная идея может показаться странной, на самом деле она абсолютно законна. После того, как вы свыкнитесь с этой идеей, более чем вероятно, что вы начнете использовать самосоединения достаточно часто, поскольку они очень полезны.

Указанием на необходимость самосоединения является вопрос о парах элементов таблицы, удовлетворяющих некоторым условиям. Например, предположим, что ваше любимая картина – это «Едоки картофеля» («The Potato Eaters»), и вы хотите определить все остальные экспонаты вашей коллекции, созданные тем же автором. Вы можете поступить так:

1. Идентифицируйте строку таблицы painting, содержащую название «The Potato Eaters», чтобы можно было ссылаться на ее значение a_id.

2. Используйте значение a_id для выявления других строк таблицы, имеющих такое же значение a_id.

3. Выведите названия картин из найденных строк. Идентификаторы художников и названия картин, с которых мы начинаем работу, выглядят так:

mysql> SELECT a_id, title FROM painting ORDER BY a_id;


+-----+----------------------+
| a_id | title |
+-----+----------------------+
| 1 | The Last Supper |
| 1 | The Mona Lisa |
| 3 | Starry Night |
| 3 | The Potato Eaters |
| 3 | The Rocks |
| 5 | Les Deux Soeurs |
+-----+----------------------+

Двухэтапный способ извлечения нужных названий без применения соединения заключается в поиске идентификатора художника в одном запросе и использовании этого идентификатора во втором запросе для выбора соответствующих ему записей:

mysql> SELECT @id := a_id FROM painting WHERE title = 'The Potato Eaters';


+-------------+
| @id := a_id |
+-------------+
| 3 |
+-------------+

mysql> SELECT title FROM painting WHERE a_id = @id;


+----------------------+
| title |
+----------------------+
| Starry Night |
| The Potato Eaters |
| The Rocks |
+----------------------+

Другое решение, требующее выполнения всего одного запроса, состоит в применении самосоединения. Обратите особое внимание на использование правильной нотации. Многие поначалу пытаются написать запрос, соединяющий таблицу с ней самой, так:

mysql> SELECT title FROM painting, painting
-> WHERE title = 'The Potato Eaters' AND a_id = a_id;
ERROR 1066 at line 1: Not unique table/alias: 'painting'


Проблема такого запроса в том, что ссылки на столбцы неоднозначны. MySQL не может определить, на какой экземпляр таблицы painting ссылается каждый из столбцов. Решение состоит в присвоении хотя бы одному экземпляру таблицы псевдонима, чтобы можно было отличать столбцы с помощью спецификаторов таблиц. Покажем, как это можно сделать, используя псевдонимы p1 и p2 для ссылок на таблицы painting:

mysql> SELECT p2.title
-> FROM painting AS p1, painting AS p2
-> WHERE p1.title = 'The Potato Eaters'
-> AND p1.a_id = p2.a_id;


+----------------------+
| title |
+----------------------+
| Starry Night |
| The Potato Eaters |
| The Rocks |
+----------------------+

Результат запроса типичен для самосоединения: если вы используете некоторое значение в одном экземпляре таблицы («The Potato Eaters») для нахождения соответствующих записей в другом экземпляре (картины того же художника), то вывод содержит и первоначальное значение. В этом есть смысл – в конце концов, значение соответствует самому себе. Если же вы хотите найти только другие картины того же автора, необходимо явно исключить исходное значение из вывода:

mysql> SELECT p2.title-> FROM painting AS p1, painting AS p2
-> WHERE p1.title = 'The Potato Eaters' AND p2.title != 'The Potato Eaters'
-> AND p1.a_id = p2.a_id;


+--------------+
| title |
+--------------+
| Starry Night |
| The Rocks |
+--------------+

Более общий способ исключения исходного значения – указать, что вы не хотите, чтобы значение в выводимой строке совпадало с исходным:

mysql> SELECT p2.title
-> FROM painting AS p1, painting AS p2
-> WHERE p1.title = 'The Potato Eaters' AND p1.title != p2.title
-> AND p1.a_id = p2.a_id;


+--------------+
| title |
+--------------+
| Starry Night |
| The Rocks |
+--------------+

Рассмотренные запросы использовали значения идентификаторов для сопоставления записей двух экземпляров таблиц, но можно использовать и любые другие типы значений. Например, чтобы при помощи таблицы states ответить на вопрос: «Какие штаты вступили в Союз в том же году, что и Нью-Йорк?», будем выполнять попарное сравнение времен, используя составляющую года даты из столбца statehood:

mysql> SELECT s2.name, s2.statehood
-> FROM states AS s1, states AS s2
-> WHERE s1.name = 'New York'
-> AND YEAR(s1.statehood) = YEAR(s2.statehood)
-> ORDER BY s2.name;


+------------------+---------------+
| name | statehood |
+------------------+---------------+
| Connecticut | 1788-01-09 |
| Georgia | 1788-01-02 |
| Maryland | 1788-04-28 |
| Massachusetts | 1788-02-06 |
| New Hampshire | 1788-06-21 |
| New York | 1788-07-26 |
| South Carolina | 1788-05-23 |
| Virginia | 1788-06-25 |
+------------------+---------------+

Исходное значение (New York) снова попадает в вывод. Если вы хотите изменить ситуацию, добавьте в инструкцию WHERE выражение, явно исключающее такое значение:

mysql> SELECT s2.name, s2.statehood
-> FROM states AS s1, states AS s2
-> WHERE s1.name = 'New York' AND s1.name != s2.name
-> AND YEAR(s1.statehood) = YEAR(s2.statehood)
-> ORDER BY s2.name;


+------------------+--------------+
| name | statehood |
+------------------+--------------+
| Connecticut | 1788-01-09 |
| Georgia | 1788-01-02 |
| Maryland | 1788-04-28 |
| Massachusetts | 1788-02-06 |
| New Hampshire | 1788-06-21 |
| South Carolina | 1788-05-23 |
| Virginia | 1788-06-25 |
+------------------+----------------+

Как и задача нахождения других картин художника, написавшего «Едоков картофеля», задача о государственности штатов может быть решена с помощью переменной SQL и двух запросов. Это верно для любых поисков соответствия конкретной строке таблицы. Но бывают задачи, требующие установления соответствия нескольких пар строк, для которых двухэтапный метод не работает. Предположим, что вы хотите определить, какие пары людей, перечисленных в таблице profile, любят одинаковые блюда. В этом случае вывод теоретически может содержать любую пару из таблицы. Фиксированного исходного значения нет, поэтому его невозможно сохранить в переменной.

Для решения такой задачи отлично подходит самосоединение, хотя и предстоит решить вопрос о том, как определить, какие значения foods используют общие элементы. Столбец foods содержит значения типа SET, каждое из которых может указывать несколько любимых блюд, поэтому простого сравнения недостаточно:

• Результат сравнения истинен, только если оба значения foods указывают одинаковые наборы блюд, а нам достаточно совпадения элементов.

• Два пустых значения воспринимаются сравнением как равные, хотя на самом деле им не соответствуют общие блюда.

Чтобы выявить значения SET, имеющие общие элементы, используем тот факт, что MySQL представляет их как битовые поля и выполняет сравнение при помощи оператора & (побитовое «И»), ища пары, имеющие ненулевое пересечение:

mysql> SELECT t1.name, t2.name, t1.foods, t2.foods
-> FROM profile AS t1, profile AS t2
-> WHERE t1.id != t2.id AND (t1.foods & t2.foods) != 0
-> ORDER BY t1.name, t2.name;


+------+-------+-----------------------+------------------------+
| name | name | foods | foods |
+------+-------+-----------------------+------------------------+
| Alan | Brit | curry,fadge | burrito,curry,pizza |
| Alan | Fred | curry,fadge | lutefisk,fadge,pizza |
| Alan | Mara | curry,fadge | lutefisk,fadge |
| Alan | Sean | curry,fadge | burrito,curry |
| Brit | Alan | burrito,curry,pizza | curry,fadge |
| Brit | Carl | burrito,curry,pizza | eggroll,pizza |
| Brit | Fred | burrito,curry,pizza | lutefisk,fadge,pizza |
| Brit | Sean | burrito,curry,pizza | burrito,curry |
| Carl | Brit | eggroll,pizza | burrito,curry,pizza |
| Carl | Fred | eggroll,pizza | lutefisk,fadge,pizza |
| Fred | Alan | lutefisk,fadge,pizza | curry,fadge |
| Fred | Brit | lutefisk,fadge,pizza | burrito,curry,pizza |
| Fred | Carl | lutefisk,fadge,pizza | eggroll,pizza |
| Fred | Mara | lutefisk,fadge,pizza | lutefisk,fadge |
| Mara | Alan | lutefisk,fadge | curry,fadge |
| Mara | Fred | lutefisk,fadge | lutefisk,fadge,pizza |
| Sean | Alan | burrito,curry | curry,fadge |
| Sean | Brit | burrito,curry | burrito,curry,pizza |
+-------+-------+------------------------+------------------------+

Некоторые задачи с участием самосоединений относятся к разновидности: «Для каких значений нет соответствий?». Примером такого вопроса может быть: «Кто из отправителей сообщений, указанных в таблице mail, ни разу не писал самому себе?». Сначала определим, кто кому отправлял сообщения:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
-> ORDER BY srcuser, dstuser;


+---------+---------+
| srcuser | dstuser |
+---------+---------+
| barb | barb |
| barb | tricia |
| gene | barb |
| gene | gene |
| gene | tricia |
| phil | barb |
| phil | phil |
| phil | tricia |
| tricia | gene |
| tricia | phil |
+---------+---------+

Некоторые из этих пар показывают, что люди писали сами себе:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
-> WHERE srcuser = dstuser;


+---------+---------+
| srcuser | dstuser |
+---------+---------+
| phil | phil |
| barb | barb |
| gene | gene |
+--------+----------+

Нахождение людей, которые не отправляют письма самим себе, – это задача отсутствия соответствий, для решения которой обычно требуется использовать LEFT JOIN. В данном случае необходимо выполнить левое соединение таблицы mail с ней самой:

mysql> SELECT DISTINCT m1.srcuser
-> FROM mail AS m1 LEFT JOIN mail AS m2
-> ON m1.srcuser = m2.srcuser AND m2.srcuser = m2.dstuser
-> WHERE m2.dstuser IS NULL;


+---------+
| srcuser |
+---------+
| tricia |
+---------+

Для каждой записи из таблицы mail запрос подбирает соответствия – совпадающих отправителя и получателя. Для записей, которым не найдено такого соответствия, LEFT JOIN включает в вывод строку, все столбцы m2 которой установлены в NULL. Такие строки определяют отправителей, которые не посылали себе писем.

Используя LEFT JOIN для соединения таблицы с ней самой, можно еще одним способом ответить на вопросы о нахождении максимума в группе, без использования второй временной таблицы. В том рецепте мы находили самую дорогую картину каждого художника, используя временную таблицу:

mysql> CREATE TABLE tmp
-> SELECT a_id, MAX(price) AS max_price FROM painting GROUP BY a_id;
mysql> SELECT artist.name, painting.title, painting.price
-> FROM artist, painting, tmp
-> WHERE painting.a_id = tmp.a_id
-> AND painting.price = tmp.max_price
-> AND painting.a_id = artist.a_id;


+-----------+-----------------------+-------+
| name | title | price |
+-----------+-----------------------+-------+
| Da Vinci | The Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+-----------+-----------------------+-------+

А можно поступить по-другому, распознавая картины и извлекая значения из каждой такой строки при помощи LEFT JOIN. Следующий запрос идентифицирует картины:

mysql> SELECT p1.a_id, p1.title, p1.price
-> FROM painting p1
-> LEFT JOIN painting p2
-> ON p1.a_id = p2.a_id AND p1.price < p2.price
-> WHERE p2.a_id IS NULL;


+-----+----------------------+-------+
| a_id | title | price |
+-----+----------------------+-------+
| 1 | The Mona Lisa | 87 |
| 3 | The Potato Eaters | 67 |
| 5 | Les Deux Soeurs | 64 |
+-----+----------------------+-------+

Для вывода фамилий художников результат объединяется с таблицей artist:

mysql> SELECT artist.name, p1.title, p1.price
-> FROM (painting p1
-> LEFT JOIN painting p2
-> ON p1.a_id = p2.a_id AND p1.price < p2.price), artist
-> WHERE p2.a_id IS NULL AND p1.a_id = artist.a_id;


+-----------+----------------------+-------+
| name | title | price |
+-----------+----------------------+-------+
| Da Vinci | The Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+-----------+-----------------------+------+

Знайте, что задача сравнения таблицы с ней самой не обязательно требует использования самосоединения, даже если можно решить ее таким способом. Рассмотрим, например, таблицу mail. Можно определить, кто посылал письма самому себе, используя самосоединение:

mysql> SELECT DISTINCT m1.srcuser, m2.dstuser
-> FROM mail AS m1, mail AS m2
-> WHERE m1.srcuser = m2.srcuser AND m2.dstuser = m1.srcuser;

+---------+---------+
| srcuser | dstuser |
+---------+---------+
| phil | phil |
| barb | barb |
| gene | gene |
+---------+---------+

Но это неразумно. Запросу совсем не нужно сравнивать записи со всеми остальными. Достаточно просто сравнить различные столбцы внутри каждой строки, поэтому вполне можно выполнить обычный запрос без соединения:

mysql> SELECT DISTINCT srcuser, dstuser FROM mail
-> WHERE srcuser = dstuser;


+---------+---------+
| srcuser | dstuser |
+---------+---------+
| phil | phil |
| barb | barb |
| gene | gene |
+---------+---------+

Добавлено: 23 Июля 2018 19:15:53 Добавил: Андрей Ковальчук