Ключевое слово join имеет два синонима: CROSS JOIN и INNER JOIN.
Помимо перекрестного объединения таблиц, предусмотрено левое и правое объединение таблиц, которое осуществляется при помощи конструкций left join right join соответственно.
В листинге 22.21 продемонстрировано перекрестное объединение таблиц tbll и tb.При этом результирующая таблица содержит комбинации строк обеих таблиц, удовлетворяющих условию tbll.id = tbi2.id. Левое объединение (left join) позволяет включить в результирующую таблицу строки "левой" таблицы tbll, которым не нашлось соответствия в "правой" таблице tbl2 (листинг 22.23).
Как видно из листинга 22.23, записи в таблице tbll со значением id = 1 не нашло соответствия в таблице tbl2, т. к. поле id в ней принимает значения 2, 3, 4. Тем менее в результирующую таблицу запись включена, при этом значения полей из таблицы tbl2 принимают значение null. Следует заметить, что для задания условия вместо ключевого слова where при левом и правом объединениях используется ключе! слово on.
В листинге 22.24 демонстрируется "правое" объединение при помощи конструктора
RIGHT JOIN.
Как видно из листинга 22.24, при правом объединении возвращаются строки, удовлетворяющие условию tbii.id = tbi2.id, и строки "правой" таблицы tbi2, которым не нашлось соответствия в "левой" таблице tbil.
Замечание________________________________________
Ключевые слова left join и right join имеют синонимы left outer join и right outer join соответственно.
Другим способом установки связи между таблицами tbil и tbl2 при правом и левом объединениях является использование ключевого слова usingo. В круглых скобках, следующих за этим ключевым словом, перечисляются имена столбцов, которые должны присутствовать в обеих таблицах и для которых необходимо соблюдение равенства. Данный оператор предназначен для создания более компактных SQL-запросов. Так, следующие два выражения идентичны:
Принимая во внимание синтаксис ключевого слова usingo, левое и правое объединения, показанные в листингах 22.23 и 22.24, можно представить так, как это сделано в листинге 22.25.
Для каждой из таблиц, участвующих в объединении с использованием SQL-оператора join, можно ввести подсказку о том, как СУБД MySQL должна использовать индексы при извлечении данных из таблицы. Указав после имени таблицы ключевое слово USE index (list), в скобках можно задать список индексов list, которые СУБД MySQL должна использовать при поиске записей в таблице. Ключевое слово ignore index (list) предназначено для того, чтобы запретить СУБД MySQL использовать какой-то отдельный индекс. Ключевое слово force index (list) подобно use index (list), но с тем отличием, что сканирование таблицы расценивается как очень дорогая операция. Это учитывается оптимизатором MySQL, и полное сканирование таблицы производится только в том случае, если нет возможности использовать индекс.
Возвращаясь к учебной базе данных shop, рассмотрим несколько запросов к таблицам, входящим в ее состав. В листинге 22.26 при помощи конструкции join ... using () извлекается число товарных позиций в каталогах.
Допустим, происходит расширение ассортимента товарных позиций и в списке каталогов появляется новый каталог 'Периферия. SQL-запрос, добавляющий данный каталог, представлен в листинге 22.27.
Однако запрос из листинга 22.26 не отражает наличие нового каталога в электронном магазине, т. к. таблица products еще не содержит ни одной записи, относящейся к новому каталогу. Для того чтобы название каталога 'Периферия' появилось в результирующей таблице, необходимо провести левое объединение таблиц catalogs и products, причем таблица catalogs должна выступать в качестве "левой" таблицы.
Как видно из листинга 22.28, левое объединение позволило включить каталог 'Периферия', товарные позиции в котором пока отсутствуют.
Пусть требуется вывести список покупателей и число осуществленных ими покупок, причем покупателей необходимо отсортировать в порядке убывания числа оплаченных ими заказов. Для решения этой задачи можно воспользоваться запросом, представленным в листинге 22.29.
Как видно из листинга 22.29, в списке присутствуют только те покупатели, которые оплатили хотя бы одну покупку, покупатели, на счету у которых нет ни одной покупки, в список не входят. Для того чтобы вывести полный список покупателей, необходимо вместо перекрестного объединения таблиц users и orders воспользоваться левым объединением, где в качестве "левой" таблицы выступит таблица users (листинг 22.30).
Помимо условия on или using, в запросах на объединение могут использоваться традиционные конструкции условия. Например, запрос, представленный в листинге 22.31, извлекает список покупателей и число их покупок при условии, что покупателя зовут "Александр".
Для этого используется условие WHERE name = 'Александр'. Однако использовать столбец total в условии where уже не получится, т. к. это групповой столбец, сформированный агрегатной функцией count () и конструкцией group by. Для формирования условия с его участием необходимо использовать условие having. Пусть требуется извлечь всех покупателей магазина, число покупок (total) у которых меньше трех (листинг 22.32)
Обновление нескольких таблиц
Помимо оператора select, в многотабличных запросах можно использовать оператор update. Для этого имена таблиц перечисляются через запятую. Пусть требуется изменить первичный ключ id^catalog таблицы catalogs для каталога 'Оперативная память' с 5 на 10. Для этого можно использовать запрос, представленный в листинге 22.33.
Однако изменения коснулись только таблицы catalogs, значения внешних ключей в таблице products не изменились. Исправить ситуацию может многотабличный запрос, представленный в листинге 22.34.
Синтаксис оператора update описывается в главе 9.
Пусть в таблицу products требуется добавить столбец catalog, который необходимо заполнить названиями каталогов из таблицы catalogs. Добавить новый столбец можно при помощи оператора alter table (листинг 22.35), синтаксис которого подробно рассматривается в главе 10.
Однако поле catalog внастоящий момент является пустым, для того чтобы заполнить его, можно прибегнуть к многотабличному запросу update (листинг 22.36).
Как видно из листинга 22.36, таблица catalogs не подвергается изменениям, но активно участвует в многотабличном запросе с участием оператора update.
В многотабличных запросах с участием SQL-оператора update не допускается использование конструкций ORDER BY И LIMIT.
Замечание _________________________
Многотабличные запросы с участием SQL-оператора UPDATE введены в СУБД MySQL, начиная с версии 4.0.0.
Рассмотренные выше примеры используют перекрестное объединение, но многотабличные запросы с участием оператора update допускают любой тип объединения, продемонстрируемте ранее на примере оператора select. В листинге 22.37 приводятся три запроса, идентичные представленному в листинге 22.36.
Точно так же, как и в случае оператора select, допускается использование псевдонимов для таблиц, которые назначаются при помощи оператора as (листинг 22.38).
22.4. Удаление из нескольких таблиц
Многотабличное удаление из таблиц при помощи оператора delete во многом аналогично операторам select и update.
Замечание________________________________________
В многотабличных запросах с участием SQL-оператора delete не допускается использование конструкций ORDER BY И LIMIT.
Замечание________________________________________
Многотабличные запросы с участием SQL-оператора DELETE введены в СУБД MySQL, начиная с версии 4.0.0.
При использовании многотабличных запросов с помощью оператора delete допустимы две формы, представленные в листинге 22.39, где из таблицы products удаляются все записи, для которых имеется соответствие в таблице catalogs.
Следует отметить, что записи касаются только таблицы products — удаление производится лишь из таблиц, перечисленных после ключевого слова delete в первой форме запроса, и после ключевого слова from во второй форме запроса. Запросы, представленные в листинге 22.40, затронут уже обе таблицы.
Продемонстрированные выше примеры используют перекрестное объединение, хотя многотабличные запросы с участием оператора delete допускают любой тип объединения, рассмотренные ранее на примере оператора select.
Для достижения совместимости с Access в многотабличных операторах delete допускается использование символа ".*" после имени таблицы (листинг22.41).
При использовании псевдонимов таблиц, назначаемых оператором as, в СУБД MySQL 4.0 необходимо обращаться к таблицам с помощью реальных имен (листинг 22.42).
Начиная с версии СУБД MySQL 4.1, псевдонимы требуется использовать в тексте всего оператора delete (листинг 22.43).
Математические функции MySQL
Описанные ниже функции выполняют различные математические операции. В качестве аргументов большинство из них принимает числа с плавающей запятой и возвращает результат аналогичного типа.
ABS (число)
Эта функция возвращает модуль числа
На рис. 8.1(а) и 8.1(б) приведены примеры работы с функцией ABS.
Рис. 8.1(а). Модуль числа
Рис. 8.1(б). Модуль числа
ASIN (число)
Эта функция возвращает арксинус числа. Диапазон допустимых значений – от -1 до 1. Вне этого диапазона значение арксинуса не определено.
На рис. 8.2(а), 8.2(б) и 8.2(в) приведены примеры работы с функцией ASIN.
Рис. 8.2(а). Арксинус числа
Рис. 8.2(б). Арксинус числа
Рис. 8.2(в). Арксинус числа
ACOS (число)
Эта функция возвращает арккосинус числа. Диапазон допустимых значений – от 1 до 1. Вне этого диапазона значение арккосинуса не определено.
На рис. 8.3(а), 8.3(б) и 8.3(в) приведены примеры работы с функцией ACOS.
Рис. 8.3(а). Арккосинус числа
Рис. 8.3(б). Арккосинус числа
Рис. 8.3(в). Арккосинус числа
ATAN (число)
Эта функция возвращает арктангенс числа.
На рис. 8.4(а), 8.4(б) и 8.4(в) приведены примеры работы с функцией ATAN.
Рис. 8.4(а). Арктангенс числа
Рис. 8.4(б). Арктангенс числа
Рис. 8.4(в). Арктангенс числа
ATAN2 (число1, число2)
Эта функция возвращает угол в радианах точки с заданными координатами.
На рис. 8.5(а), 8.5(б) и 8.5(в) приведены примеры работы с функцией ATAN2.
Рис. 8.5(а). Угол по координатам точки
Рис. 8.5(б). Угол по координатам точки
Рис. 8.5(в). Угол по координатам точки
CEILING (число)
CEIL(число)
Эта функция округляет число до ближайшего большего целого числа.
На рис. 8.6(а), 8.6(б) и 8.6(в) приведены примеры работы с функцией CEIL.
Рис. 8.6(а). Функция CEIL
Рис. 8.6(б). Функция CEIL
Рис. 8.6(в). Функция CEIL
COS (число)
Возвращает косинус числа
На рис. 8.7 приведен пример работы с функцией COS.
Рис. 8.7. Косинус числа
COT (число)
Возвращает котангенс числа.
На рис. 8.8(а) и 8.8(б) приведены примеры работы с функцией COT.
Рис. 8.8(а). Котангенс числа
Рис. 8.8(б). Котангенс числа
CRC32 (выражение)
Вычисляет проверочное значение в циклическом избыточном коде и возвращает 32-разрядное целое. Результат равен NULL, если передается аргумент NULL. Ожидается, что аргумент будет строкой, и будет рассматриваться в качестве таковой в противном случае.
На рис. 8.9 приведен пример работы с функцией CRC32.
Рис. 8.9. Циклический избыточный код
DEGREES(число)
Возвращает аргумент, преобразованный из радианов в градусы.
На рис. 8.10 приведен пример работы с функцией DEGREES.
Рис. 8.10. Преобразование из радианов в градусы
ЕХР (число)
Эта функция возводит число e (основание натурального логарифма) в заданную степень.
На рис. 8.11(а) и 8.11(б) приведены примеры работы с функцией EXP.
Рис. 8.11(а). Экспонента
Рис. 8.11(б). Экспонента
FLOOR (число)
Эта функция округляет число до ближайшего меньшего целого числа.
На рис. 8.12(а), 8.12(б) и 8.12(в) приведены примеры работы с функцией FLOOR.
Рис. 8.12(а). Функция FLOOR
Рис. 8.12(б). Функция FLOOR
Рис. 8.12(в). Функция FLOOR
GREATEST (...)
Эта функция возвращает наибольшее значение из списка. Она может работать как с числами, так и со строками.
На рис. 8.13 приведен пример работы с функцией GREATEST.
Рис. 8.13. Наибольшее значение из списка
LEAST (...)
Функция возвращает наименьшее значение из списка.
На рис. 8.14 приведен пример работы с функцией LEAST.
Рис. 8.14. Наименьшее значение из списка
LN (число)
LOG (число)
Эта функция возвращает натуральный логарифм числа.
На рис. 8.15(а) и 8.15(б) приведены примеры работы с функцией LN.
Рис. 8.15(а). Натуральный логарифм числа
Рис. 8.15(б). Натуральный логарифм числа
LOG(число1, число2)
При вызове с одним параметром функция LOG возвращает натуральный логарифм числа, а при вызове с двумя параметрами - возвращает логарифм числа2 по основанию число1.
На рис. 8.16(а) и 8.16(б) приведены примеры работы с функцией LOG.
На рис. 8.17(а) и 8.17(б) приведены примеры работы с функцией LOG.
Рис. 8.17(а). Логарифм числа по основанию 2
Рис. 8.17(б). Логарифм числа по основанию 2
Функция LOG2 () удобна для того, чтобы определить, сколько бит потребуется для сохранения числа. Вместо нее можно использовать LOG (число) /LOG (2).
LOG10 (число)
Возвращает логарифм числа по основанию 10.
На рис. 8.18(а), 8.18(б) и 8.18(в) приведены примеры работы с функцией LOG10.
Рис. 8.18(а). Десятичный логарифм
Рис. 8.18(б). Десятичный логарифм
Рис. 8.18(в). Десятичный логарифм
MOD(число1, число2)
число1 % число2
число1 MOD число2
Эта функция возвращает остаток от деления первого числа на второе подобно оператору %.
На рис. 8.19(а), 8.19(б), 8.19(в) и 8.19(г) приведены примеры работы с функцией MOD.
Рис. 8.19(а). Остаток от деления
Рис. 8.19(б). Остаток от деления
Рис. 8.19(в). Остаток от деления
Рис. 8.19(г). Остаток от деления
PI()
Возвращает значение числа . По умолчанию отображается пять знаков после десятичной запятой, но внутренне MySQL использует полное представление действительного числа двойной точности.
На рис. 8.20(а) и 8.20(б) приведены примеры работы с функцией PI.
Рис. 8.20(а). Число Пи
Рис. 8.20(б). Число Пи
POW(число1, число2)
POWER(число1, число2)
Возвращает значение число1, возведенное в степень число2.
На рис. 8.21(а), 8.21(б) и 8.21(в) приведены примеры работы с функцией POW.
Рис. 8.21(а). Возведение числа в степень
Рис. 8.21(б). Возведение числа в степень
Рис. 8.21(в). Возведение числа в степень
RADIANS(число)
Возвращает аргумент, преобразованный из градусов в радианы.
На рис. 8.22(а) и 8.22(б) приведены примеры работы с функцией RADIANS.
Рис. 8.22(а). Преобразование из градусов в радианы
Рис. 8.22(б). Преобразование из градусов в радианы
RAND ([число])
Возвращает случайное число двойной точности в диапазоне от 0 до 1. Если указан целочисленный аргумент, он служит начальным числом для генератора случайных чисел (генерируя повторяющуюся последовательность). Если аргумент отсутствует, используется значение системных часов.
На рис. 8.23(а) и 8.23(б) приведены примеры работы с функцией RAND.
Рис. 8.23(а). Создание случайных чисел
Рис. 8.23(б). Создание случайных чисел
Функцию можно использовать для извлечения строк в случайном порядке.
mysql> SELECT * FROM имя_таблицы ORDER BY RAND();
ORDER BY RAND() в комбинации с LIMIT удобно для выбора случайного примера из набора строк:
mysql> SELECT * FROM tablel, table2 WHERE a=b AND c<d-> ORDER BY RAND() LIMIT 1000;
Следует отметить, что RAND() в конструкции WHERE вычисляется заново при каждом выполнении WHERE.
ROUND (число [, точность])
Эта функция округляет число с плавающей запятой до целого числа или, если указан второй аргумент, до заданного количества цифр после запятой. Если точность отрицательная, обнуляется целая часть числа.
На рис. 8.24(а), 8.24(б), 8.24(в), 8.24(г), 8.24(д) и 8.24(е) приведены примеры работы с функцией ROUND.
Рис. 8.24(а). Округление числа
Рис. 8.24(б). Округление числа
Рис. 8.24(в). Округление числа
Рис. 8.24(г). Округление числа
Рис. 8.24(д). Округление числа
Рис. 8.24(е). Округление числа
Следует отметить, что поведение ROUND(), когда аргумент точно на середине отрезка между двумя целыми зависит от реализации библиотеки С. Различные реализации округляют до ближайшего четного, либо всегда в большую сторону, либо всегда в меньшую сторону, либо в сторону ближайшего нуля. Если вам нужно иметь предсказуемое поведение в этом случае, применяйте вместо этой функции TRUNCATE() ИЛИ FLOOR().
SIGN (число)
Возвращает знак аргумента как -1,0 или 1, в зависимости от того, число отрицательное, нуль или положительное.
На рис. 8.25(а), 8.25(б) и 8.25(в) приведены примеры работы с функцией SIGN.
Рис. 8.25(а). Знак числа
Рис. 8.25(б). Знак числа
Рис. 8.25(в). Знак числа
SIN (число)
Эта функция возвращает синус числа в радианах.
На рис. 8.26(а) и 8.26(б) приведены примеры работы с функцией SIN.
Рис. 8.26(а). Синус числа
Рис. 8.26(б). Синус числа
SQRT (число)
Эта функция возвращает квадратный корень числа
На рис. 8.27(а), 8.27(б) и 8.27(в) приведены примеры работы с функцией SQRT.
Рис. 8.27(а). Квадратный корень
Рис. 8.27(б). Квадратный корень
Рис. 8.27(в). Квадратный корень
TAN(число)
Возвращает тангенс числа.
На рис. 8.28 приведен пример работы с функцией TAN.
Рис. 8.28. Тангенс числа
TRUNCATE(число1, число2)
Возвращает число1 с дробной частью, усеченной до число2 десятичных разрядов. Если число2 равно 0, результат не имеет точки и дробной части. Если число2 отрицательное, целая часть числа длиной число2 обнуляется.
На рис. 8.29(а), 8.29(б), 8.29(в), 8.29(г) и 8.29(д) приведены примеры работы с функцией TRUNCATE.
Рис. 8.29(а). Усечение числа
Рис. 8.29(б). Усечение числа
Рис. 8.29(в). Усечение числа
Рис. 8.29(г). Усечение числа
Рис. 8.29(д). Усечение числа
Все числа округляются в сторону нуля. Следует отметить, что десятичные числа обычно не хранятся в компьютерах именно в виде чисел, а в виде двоичных значений двойной точности, поэтому иногда результат может вызвать удивление (рис. 8.29(е))
Рис. 8.29(е). Усечение числа
Это происходит потому, что 10.28 на самом деле сохраняется как 10.27999999999999...
. По умолчанию отображается пять знаков после десятичной запятой, но внутренне MySQL использует полное представление действительного числа двойной точности.
