MySQL 5.0. Библиотека программиста
Шрифт:
ALTER TABLE Orders DROP FOREIGN KEY orders_ibfk_1;
Затем изменим тип таблицы Products на MyISAM:
ALTER TABLE Products ENGINE MyISAM;
И наконец, создадим полнотекстовый индекс для столбца description:
ALTER TABLE Products ADD FULLTEXT (description);
После этого можно выполнять полнотекстовый поиск по столбцу description. Например, запрос
SELECT * FROM Products WHERE MATCH (description) AGAINST (\'Чайник Мосбытприбор\');
возвращает единственную строку (табл. 3.19). Таблица 3.19. Результат выполнения запроса
В других наименованиях товаров также присутствует ключевое слово «Мосбытприбор», однако программа MySQL игнорирует те слова из критерия поиска, которые встречаются более чем в половине строк. Игнорируются
Если необходимо выполнить поиск по словам, которые могут встречаться более чем в 50 % строк, необходимо использовать поиск в логическом режиме. Для этого необходимо включить в выражение MATCH. AGAINST. параметр IN BOOLEAN MODE. Управлять поиском в логическом режиме можно с помощью следующих спецсимволов:
• + – перед словом означает, что будут найдены только строки, содержащие это слово;
• – – перед словом означает, что будут найдены только строки, не содержащие это слово;
• < – перед словом уменьшает «вес» этого слова при вычислении релевантности;
• > – перед словом увеличивает «вес» этого слова при вычислении релевантности;
• ~ – перед словом делает «вес» слова отрицательным (уменьшающим релевантность);
• * — после слова означает произвольное окончание; например, запрос по слову +чай* выведет строки, содержащие слова «чайник», «чайница», «чайка» и т. п.;
• " – сочетание слов, заключенное в двойные кавычки, рассматривается как единое слово;
• (\' и \') – круглые скобки позволяют создавать вложенные выражения.
Например, запросSELECT * FROM Products
WHERE MATCH (description)
AGAINST (\'-Чайник +Мосбытприбор\' IN BOOLEAN MODE);возвращает строки, содержащие слово «Мосбытприбор», но не имеющие слова «Чайник» (табл. 3.20). Таблица 3.20. Результат выполнения запроса
Еще один режим полнотекстового поиска – расширенный режим. Он отличается от обычного тем, что в результат запроса, помимо строк, отвечающих заданному критерию поиска, включаются строки, найденные по принципу схожести с несколькими первыми строками, наиболее релевантными исходному критерию. Расширенный режим полезен при поиске «наугад», когда заранее неясно, по какому критерию искать нужную строку. Для поиска в логическом режиме необходимо включить в выражение MATCH… AGAINST… параметр WITH QUERY EXPANSION:
SELECT * FROM Products
WHERE MATCH (description)
AGAINST (\'Чайник Мосбытприбор\' WITH QUERY EXPANSION);Итак, мы рассмотрели основные операторы сравнения, на которых базируются условия отбора в запросах и командах изменения и удаления строк. В следующем подразделе мы рассмотрим группу операторов и ключевых слов, которые также используются для сравнения, только одним из операндов служит результат вложенного запроса.
Операторы сравнения с результатами вложенного запроса
В этом разделе вы познакомитесь с операторами и ключевыми словами, используемыми для обработки результатов вложенного запроса. Перечислим их.
EXISTSОператор EXISTS возвращает значение 1 (TRUE), если результат подзапроса содержит хотя бы одну строку, и значение 0 (FALSE), если подзапрос выдает пустой результат.
Например, получить список товаров, заказанных по крайней мере одним клиентом, можно с помощью запросаSELECT * FROM Products
WHERE EXISTS
(SELECT * FROM Orders
WHERE product_id = Products.id
AND customer_id IS NOT NULL);Обратите внимание, что в этом примере мы столкнулись с новой разновидностью вложенного запроса. В примерах, которые мы рассматривали ранее (см. подраздел «Вложенные запросы»), вложенный запрос не использовал данные из внешнего запроса и поэтому выполнялся только один раз, после чего найденные вложенным запросом данные обрабатывались внешним запросом. Однако в текущем примере вложенный запрос связан с внешним: в нем используется значение столбца id (идентификатор) таблицы Products (Заказы) – таблицы, которая участвует во внешнем запросе. Следовательно, вложенный запрос выполняется отдельно для каждой строки таблицы Products, каждый раз с новым значением столбца id.
Таким образом, для каждого товара запускается вложенный запрос, который выбирает заказы с этим товаром, сделанные каким-либо клиентом (то есть в столбце customer_id таблицы Orders должно быть значение, отличное от NULL). Если этот вложенный запрос выдал хотя бы одну строку (то есть заказ с такими параметрами существует), то условие отбора во внешнем запросе выполняется и текущая запись о товаре включается в результат, выводимый внешним запросом. В итоге мы получим следующий список товаров (табл. 3.21).
Таблица 3.21. Результат выполнения запросаДалее мы рассмотрим
Оператор возвращает результат, противоположный результату выполнения оператора EXISTS: 1 (TRUE), если результат подзапроса не содержит ни одной строки, и 0 (FALSE), если результат подзапроса непустой.
Например, получить список клиентов, заказавших все виды товаров, можно с помощью следующего запроса:SELECT * FROM Customers WHERE NOT EXISTS
(SELECT * FROM Products WHERE NOT EXISTS
(SELECT * FROM Orders
WHERE product_id = Products.id
AND customer_id = Customers.id));В этом запросе для каждого клиента и каждого товара самый «глубоко вложенный» подзапрос отбирает заказы, в которых фигурируют этот клиент и этот товар. Если ни одного такого заказа не найдено (то есть данный клиент не заказывал данный товар), то выполнено условие отбора в «среднем» подзапросе. Следовательно, «средний» подзапрос выдает непустой список товаров, которые не были заказаны данным клиентом, условие внешнего запроса не выполняется и запись об этом клиенте не попадет в результат запроса. Если же оказывается, что данный клиент заказывал данный товар, то, наоборот, условие отбора в «среднем» подзапросе не выполняется, «средний» подзапрос возвращает пустой результат, а значит, условие отбора во внешнем запросе выполнено и запись об этом клиенте будет включена в результат запроса.
В нашей базе данных нет ни одного клиента, который бы заказал все наименования товаров (см. листинги 2.5 и 2.6), поэтому рассмотренный нами запрос возвращает пустой результат.
Далее мы рассмотрим операторы IN и NOT IN применительно к вложенным запросам.
IN и NOT IN Операторы IN и NOT IN, с которыми вы познакомились в подразделе «Операторы сравнения», позволяют проверить, содержится ли некоторое значение в результате подзапроса. Рассмотрим еще один пример использования оператора IN.SELECT * FROM Customers WHERE \'2007-12-12\' IN (SELECT date FROM Orders WHERE Customers.id = customer_id);
Для каждого клиента, то есть строки таблицы Customers, вложенный запрос выдает даты заказов этого клиента. Если дата «2007-12-12» есть среди этих дат, то строка таблицы Customers включается в результат запроса. Таким образом, запрос выводит информацию о тех клиентах, которые сделали заказ 12 декабря 2007 г. Результат этого запроса представлен в табл. 3.11.
Вложенный запрос, результат которого обрабатывается с помощью оператора IN, может возвращать несколько столбцов, но в этом случае и значение слева от оператора должно быть составным с таким же количеством компонентов (составные значения мы рассмотрели в пункте «Оператор х IN (<Список значений>)».
Далее мы разберем ключевые слова ANY и SOME.
ANY, SOME Ключевое слово ANY («какой-либо») используется совместно с операторами сравнения, описанными в подразделе «Операторы сравнения». При использовании ANY результат сравнения будет верным, если он верен хотя бы для одного из значений, выданных подзапросом. Другими словами, результатом вычисления выраженияx <Оператор сравнения> ANY (<Вложенный запрос>)
может быть одно из следующих значений:
• 1 (TRUE) – если среди выданных подзапросом значений есть хотя бы одно значение y, для которого выполнено условие x <Оператор сравнения> y ;
• 0 (FALSE) – если среди выданных подзапросом значений нет ни одного такого значения y, для которого выражение x <Оператор сравнения> y истинно (TRUE) или не определено (NULL), в том числе, если подзапрос возвращает пустой результат;
• NULL – если среди выданных подзапросом значений нет ни одного такого значения y, для которого выражение x <Оператор сравнения> y истинно TRUE), но в то же время есть одно или несколько значений y, для которых это выражение не определено (NULL).
Например, вывести информацию о клиентах, которые сделали хотя бы один заказ на сумму более 5000, можно с помощью запросаSELECT * FROM Customers WHERE 5000 < ANY (SELECT amount FROM Orders WHERE Customers.id = customer_id);
Для каждого клиента вложенный подзапрос получает из таблицы Orders (Заказы) суммы заказов (столбец amount) этого клиента. Затем эти суммы сравниваются с величиной 5000, и запись о клиенте попадет в результат запроса, если хотя бы одна из этих сумм превышает 5000. Таким образом, запрос возвращает результат, представленный в табл. 3.11. Отметим, что вложенный запрос может быть только правым операндом для оператора сравнения: например, рассмотренный выше запрос нельзя переписать в виде
SELECT * FROM Customers WHERE
ANY (SELECT amount FROM Orders
WHERE Customers.id = customer_id)
> 5000;Ключевое слово SOME является синонимом ключевого слова ANY. Далее мы рассмотрим ключевое слово ALL. ALL Ключевое слово ALL («все»), как и ANY, используется совместно с операторами сравнения, описанными в подразделе «Операторы сравнения». При использовании ALL результат сравнения будет верным, если он верен для всех значений, выданных подзапросом. Другими словами, результатом вычисления выражения