А Б В Г Д Е Ж З И К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Э Ю Я
0-9 A B C D I F G H IJ K L M N O P Q R S TU V WX Y Z #


Чтение книги "MySQL 5.0. Библиотека программиста" (страница 13)

   Глава 3
   Операторы и функции языка SQL

   В этой главе вы познакомитесь с функциями и операторами, с помощью которых можно создавать выражения – формулы, вычисляющие какое-либо значение (числовое, логическое, символьное и др.). Наиболее часто выражения используются в SQL-запросах: как для вычисления значений, выводимых запросом, так и в условиях отбора. С помощью выражений можно также задавать условия отбора в SQL-командах UPDATE и DELETE, значения, добавляемые в таблицу, в командах INSERT и UPDATE и многое другое.
   Отличие операторов от функций заключается, по существу, только в форме записи. Аргументы функции записываются после имени функции в скобках через запятую, в то время как аргументы оператора (операнды) могут располагаться по обе стороны от значка или имени оператора. Поэтому, рассматривая операторы и функции, мы будем подразделять их на группы, руководствуясь их назначением, а не внешними различиями.
   В первую очередь мы рассмотрим наиболее часто используемую группу операторов – операторы, осуществляющие проверку какого-либо условия.

   3.1. Операторы и функции проверки условий

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

   Операторы сравнения

   Операторы сравнения позволяют сравнивать между собой значения столбцов таблиц, значения выражений и константы, относящиеся к любым типам данных. Результатом сравнения является логическое значение:
   • 1 (TRUE) – истинное значение, которое свидетельствует о том, что сравнение верно, условие выполнено;
   • 0 (FALSE) – ложное значение, которое свидетельствует о том, что сравнение неверно, условие не выполнено;
   • NULL – неопределенное значение, которое свидетельствует о том, что проверить условие невозможно, поскольку один из операндов равен NULL.
...
   Примечание
   Иногда проверить условие можно несмотря на то, что один из операндов равен NULL (см., например, описание операторов BETWEEN и IN в этом подразделе); в этом случае возвращается значение 1 или 0.
   Начнем с рассмотрения оператора, проверяющего равенство двух операндов.
Оператор x = y
   Оператор «равно» возвращает следующие значения:
   • 1 (TRUE) – если х и y совпадают;
   • 0 (FALSE) – если х и y различны;
   • NULL – если по крайней мере один из операндов равен NULL.
   Например, выберите из таблицы Customers (Клиенты) строки, в которых значение в столбце name равно «Крылов»:

   SELECT * FROM Customers WHERE name = 'КРЫЛОВ';

   Результат этого запроса представлен в табл. 3.1.
Таблица 3.1. Результат выполнения запроса
   Как вы видите, при сравнении строк с помощью этого оператора регистр символов не учитывается.
   Следующий оператор также проверяет равенство двух операндов.
Оператор x < = > у
   В случае, когда оба операнда не равны NULL, данный оператор аналогичен оператору «равно». Если один из операндов равен NULL, оператор < = > возвращает значение 0 (FALSE), а если оба операнда равны NULL – значение 1 (TRUE).
   Например, запрос

   SELECT 100 = NULL, 100< = >NULL, NULL = NULL, NULL< = >NULL;

   возвращает результат (табл. 3.2) и наглядно иллюстрирует различие между операторами = и < = >.
Таблица 3.2. Результат выполнения запроса
   Следующие операторы проверяют равенство операнда какому-либо логическому значению.
Оператор x IS y, где y – TRUE, FALSE, UNKNOWN или NULL
   Выражением IS TRUE возвращает 1 (TRUE), если x – отличное от нулячисло или отличная от нулевой («0000-00-00 00:00:00») дата и/или время, и 0 (FALSE) – в остальных случаях.
   Выражением IS FALSE возвращает 1 (TRUE), еслихравен нулю либо нулевой дате и/или времени, и 0 (FALSE) – в остальных случаях.
...
   Примечание
   Если x является символьной строкой, то перед сравнением с TRUE или FALSE эта строка преобразуется в число. Для этого отбрасываются все символы, начиная с первого, недопустимого в числовом значении, а начальная подстрока рассматривается как число. Если первый символ в значении – буква или пустая строка («»), то x приравнивается к нулю.
   Выражениях IS UNKNOWN их IS NULL возвращают 1 (TRUE), еслихравен NULL, и 0 (FALSE) – в остальных случаях.
   Например, запрос

   SELECT 100 IS TRUE, 0 IS TRUE, '2007-12-12' IS TRUE,
   '0000-00-00' IS TRUE, NULL IS TRUE;

   возвращает результат, представленный в табл. 3.3.
Таблица 3.3. Результат выполнения запроса
   Запрос

   SELECT 100 IS FALSE, 0 IS FALSE, '2007-12-12' IS FALSE,
   '0000-00-00' IS FALSE, NULL IS FALSE;

   возвращает результат, представленный в табл. 3.4.
Таблица 3.4. Результат выполнения запроса
   Запрос

   SELECT 100 IS NULL, 0 IS NULL, '2007-12-12' IS NULL,
   '0000-00-00' IS NULL, NULL IS NULL;

   возвращает результат, представленный в табл. 3.5.
Таблица 3.5. Результат выполнения запроса
...
   Примечание
   Если столбец определен как DATE NOT NULL (или DATETIME NOT NULL), то значение этого столбца, равное «0000-00-00» (или «0000-00-00 00:00:00»), рассматривается оператором IS NULL как NULL. Например, если при создании таблицы Orders (Заказы) (см. листинг 2.4 в главе 2) задать для столбца date (дата) свойство NOT NULL, то запрос SELECT * FROM Orders WHERE date IS NULL; выведет строки, в которых дата заказа равна «0000-00-00».
   Следующие операторы проверяют несовпадение двух операндов.
Операторы x! = y, x <> y
   Оператор «не равно» возвращает следующие значения:
   • 1 (TRUE) – если х и y различны;
   • 0 (FALSE) – если х и y совпадают;
   • NULL – если по крайней мере один из операндов равен NULL.
   Например, запрос

   SELECT * FROM Customers WHERE name! = 'КРЫЛОВ';

   возвращает результат, обратный приведенному в табл. 3.1, то есть все строки, кроме строк с фамилией «Крылов» (табл. 3.6).
Таблица 3.6. Результат выполнения запроса
   Следующие операторы проверяют несовпадение операнда с каким-либо логическим значением.
Оператор x IS NOT y, где y – TRUE, FALSE, UNKNOWN или NULL
   Выражение х IS NOT TRUE возвращает 0 (FALSE), если х– отличное от нуля число или отличная от нулевой («0000-00-00 00:00:00») дата и/или время, и 1 (TRUE) – в остальных случаях.
   Выражением IS NOT FALSE возвращает 0 (FALSE), еслиxравен нулю, нулевой дате и/или времени, и 1 (TRUE) – в остальных случаях.
   Выражениях IS NOT UNKNOWN их IS NOT NULL возвращают 0 (FALSE), если x равен NULL, и 1 (TRUE) – в остальных случаях.
   Например, запрос

   SELECT 100 IS NOT TRUE, 0 IS NOT TRUE,
   '2007-12-12' IS NOT TRUE, '0000-00-00' IS NOT TRUE,
   NULL IS NOT TRUE;

   возвращает результат, представленный в табл. 3.7.
Таблица 3.7. Результат выполнения запроса
   Запрос

   SELECT 100 IS NOT FALSE, 0 IS NOT FALSE,
   '2007-12-12' IS NOT FALSE, '0000-00-00' IS NOT FALSE,
   NULL IS NOT FALSE;

   возвращает результат, представленный в табл. 3.8.
Таблица 3.8. Результат выполнения запроса
   Запрос

   SELECT 100 IS NOT NULL, 0 IS NOT NULL,
   '2007-12-12' IS NOT NULL, '0000-00-00' IS NOT NULL,
   NULL IS NOT NULL;

   возвращает результат, представленный в табл. 3.9.
Таблица 3.9. Результат выполнения запроса
   Как вы видите, операторы х IS NOT у и х IS у возвращают противоположные результаты.
   Следующий оператор проверяет, меньше ли первый операнд, чем второй.
Оператор x < y
   Оператор «меньше» возвращает следующие значения:
   • 1 (TRUE) – если х меньше у;
   • 0 (FALSE) – если х равен у или х больше у;
   • NULL – если по крайней мере один из операндов равен NULL.
   Например, запрос

   SELECT * FROM Customers WHERE name<'КРЫЛОВ';

   возвращает пустой результат, поскольку «Крылов» – наименьшее в алфавитном порядке значение в столбце name (имя) таблицы Customers (Клиенты). Предшествующих ему значений в столбце нет, и, следовательно, ни одна строка не удовлетворяет условию отбора.
   Следующий оператор проверяет, не превосходит ли первый операнд второго.
Оператор x < = y
   Оператор «меньше либо равно» возвращает следующие значения:
   • 1 (TRUE) – если х равно y или х меньше у;
   • 0 (FALSE) – если х больше у;
   • NULL – если по крайней мере один из операндов равен NULL.
   Например, запрос

   SELECT * FROM Customers WHERE name< = 'КРЫЛОВ';

   возвращает результат, представленный в табл. 3.1.
   Следующий оператор проверяет, больше ли первый операнд, чем второй.
Оператор x > y
   Оператор «больше» возвращает следующие значения:
   • 1 (TRUE) – если х больше у;
   • 0 (FALSE) – если х равно у или х меньше у;
   • NULL – если по крайней мере один из операндов равен NULL.
   Например, запрос

   SELECT * FROM Customers WHERE name>'КРЫЛОВ';

   возвращает результат, представленный в табл. 3.6.
   Следующий оператор проверяет, является ли первый операнд большим либо равным по отношению ко второму.
Оператор x > = y
   Оператор «больше либо равно» возвращает следующие значения:
   • 1 (TRUE) – если х равно у или х больше у;
   • 0 (FALSE) – если х меньше у;
   • NULL – если по крайней мере один из операндов равен NULL.
   Например, запрос

   SELECT * FROM Customers WHERE name> = 'КРЫЛОВ';

   возвращает все строки таблицы Customers (Клиенты) (табл. 3.10).
Таблица 3.10. Результат выполнения запроса
   Следующий оператор проверяет, находится ли первый операнд в промежутке между вторым и третьим.
Оператор x BETWEEN a AND b
   Оператор «между» возвращает следующие значения:
   • 1 (TRUE) – если a < х < b;
   • 0 (FALSE) – если х меньше a или больше b;
   • NULL – в остальных случаях.
   Например, запрос

   SELECT * FROM Customers
   WHERE name BETWEEN 'КРЫЛОВ' AND 'OOO «Кускус»';

   возвращает следующие строки таблицы Customers (Клиенты) (табл. 3.11).
Таблица 3.11. Результат выполнения запроса
   Следующий оператор проверяет, находится ли первый операнд за пределами промежутка между вторым и третьим операндом.
Оператор x NOT BETWEEN a AND b
   Оператор возвращает результат, противоположный результату оператора «между»:
   • 1 (TRUE) – если х меньше a или больше b;
   • 0 (FALSE) – если a < х < b:
   • NULL – в остальных случаях.
   Например, запрос

   SELECT * FROM Customers
   WHERE name NOT BETWEEN 'КРЫЛОВ' AND 'ООО «Кускус»;

   возвращает следующие строки таблицы Customers (Клиенты) (табл. 3.12).
Таблица 3.12. Результат выполнения запроса
   Следующий оператор проверяет наличие первого операнда в списке значений, который является вторым операндом.
Оператор x IN (<Список значений>)
   Оператор «содержится в списке» возвращает следующие значения:
   • 1 (TRUE) – если х совпадает с одним из элементов списка;
   • 0 (FALSE) – если х не совпадает ни с одним из элементов списка;
   • NULL – если х равен NULL, а также в тех случаях, когда в списке присутствует значение NULL и при этом х не совпадает ни с одним из элементов списка.
   Например, запрос

   SELECT * FROM Customers WHERE rating IN (500,1500,2500);

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

   SELECT * FROM Orders WHERE (date,product_id) IN
   (('2007-12-12',1),('2007-12-12',2),
   ('2007-12-13',1),('2007-12-13',2));

   сравнивает каждую пару, состоящую из даты заказа (date) и номера товара (customer_id), со списком пар, и если оба компонента в паре совпадают с соответствующими компонентами какой-либо пары из списка, то строка таблицы Orders (Заказы) будет включена в результат запроса. Таким образом, запрос отбирает заказы товаров № 1 и № 2, сделанные 12 и 13 декабря 2007 г. (табл. 3.13).
Таблица 3.13. Результат выполнения запроса
   В отличие от функций LEAST, GREATEST, INTERVAL и COALESCE, списком значений для оператора IN может быть не только фиксированный перечень аргументов, но и результат подзапроса (соответствующий пример мы рассматривали в подразделе «Вложенные запросы» главы 2).
   Следующий оператор проверяет отсутствие первого операнда в списке значений, который является вторым операндом.
Оператор x NOT IN (<Список значений>)
   Оператор «не содержится в списке» возвращает результат, противоположный результату оператора IN:
   • 1 (TRUE), если х не совпадает ни с одним из элементов списка;
   • 0 (FALSE) – если х совпадает с одним из элементов списка;
   • NULL, если х равен NULL, а также в тех случаях, когда в списке присутствует значение NULL и при этом х не совпадает ни с одним из элементов списка.
   Например, запрос

   SELECT * FROM Customers WHERE rating NOT IN (500,1500);

   возвращает результат, представленный в табл. 3.11.
   Этот оператор, как и оператор IN, может работать с составными значениями, а также со списком, полученным в результате подзапроса.
   Следующий оператор проверяет соответствие первого операнда шаблону, который является вторым операндом.
Оператор x LIKE y
   Оператор сравнения с шаблоном возвращает следующие значения:
   • 1 (TRUE) – если х соответствует шаблону у;
   • 0 (FALSE) – если х не соответствует шаблону у;
   • NULL – если х или у равен NULL.
   В шаблоне можно использовать два специальных подстановочных символа:
   • % – на месте знака процента может быть любое количество произвольных символов операнда х;
   • _ – на месте знака подчеркивания может быть ровно один произвольный символ операнда х.
   Например, следующий запрос выводит данные о тех клиентах, чьи имена содержат кавычки:

   SELECT * FROM Customers WHERE name LIKE '%''%''%';

   Результат этого запроса представлен в табл. 3.14.
Таблица 3.14. Результат выполнения запроса
   Если требуется включить в шаблон знак процента или подчеркивания, которые должны рассматриваться не как подстановочные, а как обычные символы, перед ними нужно поставить обратную косую черту («%», «_»). Если же шаблон должен содержать символ обратной косой черты, то ее нужно удвоить («\»). Например, значение выражения _% LIKE \_% истинное.
   По умолчанию сравнение с помощью оператора LIKE выполняется без учета регистра символов (то есть заглавная и строчная буквы рассматриваются как одинаковые). Для сравнения с учетом регистра (чтобы заглавная и строчная буквы рассматривались как разные) необходимо указать ключевое слово BINARY или правило сравнения (COLLATE). Например, выражение 'Крылов' LIKE 'крылов' истинно, а выражения 'Крылов' LIKE BINARY 'крылов' и 'Крылов' LIKE 'крылов' COLLATE utf8_bin ложны (правило сравнения должно соответствовать кодировке, в которой работает ваше клиентское приложение; правила сравнения мы рассмотрели в разделе «Создание базы данных» главы 2).
...
   Примечание
   Более сложные шаблоны вы можете создавать с помощью регулярных выражений. Регулярные выражения представляют собой универсальный язык описания текстов. Информацию о синтаксисе регулярных выражений вы можете найти на веб-странице http://ru.wikipedia.org/wiki/Регулярные_выражения. Для сравнения строки с шаблоном, содержащим регулярные выражения, необходимо вместо оператора LIKE использовать оператор REGEXP.
   Следующий оператор проверяет несоответствие первого операнда шаблону, который является вторым операндом.
Оператор x NOT LIKE y
   Оператор NOT LIKE возвращает результат, противоположный результату выполнения оператора LIKE:
   • значение 0 (FALSE) – если х соответствует шаблону у;
   • значение 1 (TRUE) – если х не соответствует шаблону;
   • значение NULL – если х или у равен NULL.
   Например, следующий запрос выводит данные о тех клиентах, чьи имена не содержат кавычек:

   SELECT * FROM Customers WHERE name NOT LIKE '%»%';

   Результат этого запроса представлен в табл. 3.15.
Таблица 3.15. Результат выполнения запроса
   К операторам сравнения близка функция STRCMP(), которую мы также рассмотрим в этом разделе, несмотря на то что она может возвращать, помимо значений 1 (TRUE), 0 (FALSE) и NULL, значение – 1 (TRUE).
Оператор STRCMP(x,y)
   Функция STRCMP() сравнивает строки х и у в соответствии с текущими правилами сравнения и возвращает:
   • – 1 – если х предшествует у в алфавитном порядке;
   • 0 – если х и у совпадают;
   • 1 – если х следует после у в алфавитном порядке;
   • NULL – если по крайней мере один из аргументов равен NULL.
   Например, зададим для таблицы Customers (Клиенты) правило сравнения, не учитывающее регистр:

   ALTER TABLE Customers
   CONVERT TO CHARACTER SET cp1251 COLLATE cp1251_general_ci;

   В этом случае запрос

   SELECT name, STRCMP(name,'крылов') FROM Customers;

   возвращает результат, представленный в табл. 3.16.
Таблица 3.16. Результат выполнения запроса
   Зададим для таблицы Customers правило сравнения, учитывающее регистр:

   ALTER TABLE Customers
   CONVERT TO CHARACTER SET cp1251 COLLATE cp1251_general_cs;
   SELECT name, STRCMP(name,'крылов') FROM Customers;

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

   ALTER TABLE Customers
   CONVERT TO CHARACTER SET cp1251 COLLATE cp1251_bin;
   SELECT name, STRCMP(name,'крылов') FROM Customers;

   Результат этого запроса представлен в табл. 3.18.
Таблица 3.18. Результат выполнения запроса
   Наконец, рассмотрим оператор полнотекстового поиска.
Оператор MATCH (<Список столбцов>) AGAINST (<Критерий поиска>)
   Оператор MATCH… AGAINST… выполняет поиск по заданным ключевым словам в значениях указанных столбцов. При этом для столбцов должен быть создан полнотекстовый индекс (о полнотекстовых индексах вы узнали из главы 2). Для каждой строки таблицы оператор MATCH. AGAINST. возвращает величину релевантности, которая характеризует степень соответствия строки критерию поиска. Если оператор используется в параметре WHERE команды SELECT, то результатом запроса будут строки с отличной от нуля релевантностью, упорядоченные по убыванию релевантности (подобно результату поиска в интернете с помощью поисковых систем).
   Например, создадим полнотекстовый индекс для столбца description (наименование) таблицы Products (Товары). Полнотекстовый индекс можно создать только для таблиц с типом MyISAM, который не поддерживает связи между таблицами. Поэтому вначале удалим связь между таблицами Products и Orders (Заказы), удалив внешний ключ из таблицы Orders:

   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 игнорирует те слова из критерия поиска, которые встречаются более чем в половине строк. Игнорируются также слишком короткие слова (из трех и менее символов) и общеупотребительные слова (список этих слов – стоп-лист – приводится на веб-странице http://dev.mysql.com/doc/ refman/5.0/en/fulltext-stopwords.html).
   Если необходимо выполнить поиск по словам, которые могут встречаться более чем в 50 % строк, необходимо использовать поиск в логическом режиме. Для этого необходимо включить в выражение MATCH. AGAINST. параметр IN BOOLEAN MODE. Управлять поиском в логическом режиме можно с помощью следующих спецсимволов:
   • + – перед словом означает, что будут найдены только строки, содержащие это слово;
   • – – перед словом означает, что будут найдены только строки, не содержащие это слово;
   • < – перед словом уменьшает «вес» этого слова при вычислении релевантности;
   • > – перед словом увеличивает «вес» этого слова при вычислении релевантности;
   • ~ – перед словом делает «вес» слова отрицательным (уменьшающим релевантность);
   • * — после слова означает произвольное окончание; например, запрос по слову +чай* выведет строки, содержащие слова «чайник», «чайница», «чайка» и т. п.;
   • " – сочетание слов, заключенное в двойные кавычки, рассматривается как единое слово;
   • (' и ') – круглые скобки позволяют создавать вложенные выражения.
   Например, запрос

   SELECT * FROM Products
Чтение онлайн



1 2 3 4 5 6 7 8 9 10 11 12 [13] 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

Навигация по сайту


Читательские рекомендации

Информация