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 игнорирует те слова из критерия поиска, которые встречаются более чем в половине строк. Игнорируются также слишком короткие слова (из трех и менее символов) и общеупотребительные слова (список этих слов – стоп-лист – приводится на веб-страницеrefman/5.0/en/fulltext-stopwords.html).

Если необходимо выполнить поиск по словам, которые могут встречаться более чем в 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. Результат выполнения запроса

Далее мы рассмотрим оператор NOT EXISTS.

NOT EXISTS

Оператор возвращает результат, противоположный результату выполнения оператора 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, можно с помощью запроса