Рубрики
Страницы блога

WHERE, AND, OR, XOR, NOT. Условный и логические операторы в MySQL

11 Фев 2019
Admin
MySQL, Справочник по языку SQL
348

Тестовая БД

Для всех примеров далее будем использовать базу данных книжного магазина - Bookstore.

Выведем все строки которые содержаться в таблице books.

Получим следующие данные.

mysql> SELECT id, title, author, price, amount FROM books;
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  2 | Нос (Акция)               | Николай Гоголь     | 255.20 |      7 |
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 |      8 |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 |      3 |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  6 | Война и мир               | Лев Толстой        | 341.00 |      1 |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 |      0 |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 |      3 |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 |     10 |
| 10 | Бесы                      | Фёдор Достоевский  | 212.00 |      8 |
+----+---------------------------+--------------------+--------+--------+
10 rows in set (0.00 sec)

Команда WHERE

Команда WHERE предназначена для создания условий (фильтров).

WHERE используется тогда, когда нужно вывести только те строки, которые соответствуют одному или нескольким условиям.

WHERE похожа на условный оператор if, который используется во многих языках программирования для создания условий.

WHERE применяется только совместно с командами SELECT, UPDATE и DELETE.

Синтаксис команды WHERE

Рассмотрим несколько примеров создания условий.

Выведем все книги, которые написал Лев Толстой.

mysql> SELECT id, title, author, price, amount FROM books WHERE author='Лев Толстой';
+----+---------------------------+-----------------------+--------+--------+
| id | title                     | author                | price  | amount |
+----+---------------------------+-----------------------+--------+--------+
|  6 | Война и мир               | Лев Толстой           | 341.00 |      1 |
|  7 | Анна Каренина             | Лев Толстой           | 346.00 |      0 |
+----+---------------------------+-----------------------+--------+--------+
2 rows in set (0.00 sec)

mysql>

Выведем все книги, доступное количество которых равно трём.

mysql> SELECT id, title, author, price, amount FROM books WHERE amount=3;
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 |      3 |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 |      3 |
+----+---------------------------+--------------------+--------+--------+
3 rows in set (0.00 sec)

mysql>

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

Команда WHERE, помимо оператора =, использует также целый набор других операторов сравнения.

Далее представлена таблица операторов сравнения с примерами их использования.

Оператор Значение
= Равенство
mysql> SELECT id, title, author, price, amount FROM books WHERE amount = 0;
+----+----------------+--------------+--------+--------+
| id | title          | author       | price  | amount |
+----+----------------+--------------+--------+--------+
|  7 | Анна Каренина  | Лев Толстой  | 346.00 |      0 |
+----+----------------+--------------+--------+--------+
1 row in set (0.00 sec)
<>, != Неравенство
mysql> SELECT id, title, author, price, amount FROM books WHERE author != 'Николай Гоголь';
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 |      8 |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  6 | Война и мир               | Лев Толстой        | 341.00 |      1 |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 |      0 |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 |      3 |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 |     10 |
| 10 | Бесы                      | Фёдор Достоевский  | 212.00 |      8 |
+----+---------------------------+--------------------+--------+--------+
8 rows in set (0.00 sec)
> Больше
mysql> SELECT id, title, author, price, amount FROM books WHERE price > 245;
+----+----------------------+------------------+--------+--------+
| id | title                | author           | price  | amount |
+----+----------------------+------------------+--------+--------+
|  2 | Нос (Акция)          | Николай Гоголь   | 255.20 |      7 |
|  3 | Мастер и Маргарита   | Михаил Булгаков  | 263.00 |      8 |
|  6 | Война и мир          | Лев Толстой      | 341.00 |      1 |
|  7 | Анна Каренина        | Лев Толстой      | 346.00 |      0 |
|  8 | Отцы и дети          | Иван Тургенев    | 371.00 |      3 |
+----+----------------------+------------------+--------+--------+
5 rows in set (0.00 sec)
< Меньше
mysql> SELECT id, title, author, price, amount FROM books WHERE price < 245;
+----+----------------------- ---+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+----------------------- ---+--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 |      3 |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 |     10 |
| 10 | Бесы                      | Фёдор Достоевский  | 212.00 |      8 |
+----+----------------------- ---+--------------------+--------+--------+
4 rows in set (0.00 sec)
>= Больше либо равно
mysql> SELECT id, title, author, price, amount FROM books WHERE price >= 245;
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  2 | Нос (Акция)               | Николай Гоголь     | 255.20 |      7 |
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 |      8 |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  6 | Война и мир               | Лев Толстой        | 341.00 |      1 |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 |      0 |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 |      3 |
+----+---------------------------+--------------------+--------+--------+
6 rows in set (0.00 sec)
<= Меньше либо равно
mysql> SELECT id, title, author, price, amount FROM books WHERE price <= 245;
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 |      3 |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 |     10 |
| 10 | Бесы                      | Фёдор Достоевский  | 212.00 |      8 |
+----+---------------------------+--------------------+--------+--------+
5 rows in set (0.00 sec)
BETWEEN Между двумя значениями
mysql> SELECT id, title, author, price, amount FROM books WHERE id BETWEEN 1 AND 5;
+----+------------------------------------------------+-----------------------------------+--------+--------+
| id | title                                          | author                            | price  | amount |
+----+------------------------------------------------+-----------------------------------+--------+--------+
|  1 | Дубровский (Акция)                             | Александр Пушкин                  | 230.00 |     20 |
|  2 | Нос (Акция)                                    | Николай Гоголь                    | 255.20 |      7 |
|  3 | Мастер и Маргарита                             | Михаил Булгаков                   | 263.00 |      8 |
|  4 | Мёртвые души (Акция)                           | Николай Гоголь                    | 173.00 |      3 |
|  5 | Преступление и наказание                       | Фёдор Достоевский                 | 245.00 |      3 |
+----+------------------------------------------------+-----------------------------------+--------+--------+
5 rows in set (0.00 sec)
LIKE Соответствует шаблону
mysql> SELECT id, title, author, price, amount FROM books WHERE title LIKE '%Акция%';
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  2 | Нос (Акция)               | Николай Гоголь     | 255.20 |      7 |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 |      3 |
+----+---------------------------+--------------------+--------+--------+
3 rows in set (0.00 sec)
IN Соответствует набору значений
mysql> SELECT id, title, author, price, amount FROM books WHERE price IN (341,230,263,173);
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 |      8 |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 |      3 |
|  6 | Война и мир               | Лев Толстой        | 341.00 |      1 |
+----+---------------------------+--------------------+--------+--------+
4 rows in set (0.00 sec)

Логические операторы AND, OR, NOT, XOR в MySQL

Наравне с языками программирования MySQL имеет свои логические операторы.

Операторы AND, OR, NOT, XOR используются для создания нескольких условий (фильтров) вывода и обработки записей таблиц базы данных.

Все логические операторы могут использоваться не только с командами SELECT и WHERE, но и с многими другими.

Оператор AND

Выводит запись если оба условия принимают истинное значение.

Синтаксис оператора AND

mysql> SELECT column1, column2, ... , columnN
    -> FROM table_name
    -> WHERE condition1 AND condition2 AND conditionN;

В примере ниже мы выбираем книги, количество которых больше либо равно 10 и их цена ниже 245.

mysql> SELECT id, title, author, price, amount
    -> FROM books
    -> WHERE price < 245 AND amount >= 10;
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 |     10 |
+----+---------------------------+--------------------+--------+--------+
2 rows in set (0.00 sec)

mysql>

Оператор OR

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

Синтаксис оператора OR

mysql> SELECT column1, column2, ... , columnN
    -> FROM table_name
    -> WHERE condition1 OR condition2 OR conditionN;

В примере ниже мы выбираем книги, за авторством Льва Толстого или Антона Чехова. Так как книг Антона Чехова в нашей БД нет, то в выводе получим только книги Льва Толстого.

mysql> SELECT id, title, author, price, amount
    -> FROM books 
    -> WHERE author = 'Лев Толстой' OR author = 'Антон Чехов';
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  6 | Война и мир               | Лев Толстой        | 341.00 |      1 |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 |      0 |
+----+---------------------------+--------------------+--------+--------+
2 rows in set (0.00 sec)

mysql>

Оператор XOR

XOR (исключающее ИЛИ), также как OR выводит записи, если хотя бы одно из условий истинно, но не выводит запись если оба условия либо истинны, либо ложны.

XOR аналогичен следующей конструкции: (a AND (NOT b)) OR ((NOT a) AND b).

Синтаксис оператора XOR

mysql> SELECT column1, column2, ... , columnN
    -> FROM table_name
    -> WHERE condition1 XOR condition2 XOR conditionN;

В примере ниже выведем все книги, автором которых будет Михаил Булгаков или цена книги будет больше 241. Однако, если автором книги будет Михаил Булгаков и при этом ее цена будет выше 241, то такая запись не выведется.

Запись с id = 3 не была выведена, т.к. автор книги - Михаил Булгаков и ее цена = 263.

mysql> SELECT id, title, author, price, amount
    -> FROM books
    -> WHERE author = 'Михаил Булгаков' XOR price > 241;
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  2 | Нос (Акция)               | Николай Гоголь     | 255.20 |      7 |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  6 | Война и мир               | Лев Толстой        | 341.00 |      1 |
|  7 | Анна Каренина             | Лев Толстой        | 346.00 |      0 |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 |      3 |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 |     10 |
+----+---------------------------+--------------------+--------+--------+
6 rows in set (0.01 sec)

mysql>

Оператор NOT

С помощью оператора NOT можно вывести все записи, которые не соответствуют условию.

Синтаксис оператора NOT

mysql> SELECT column1, column2, ... , columnN
    -> FROM table_name
    -> WHERE NOT condition;

В примере ниже выведем все книги, кроме тех, которых нет в наличии.

mysql> SELECT id, title, author, price, amount FROM books WHERE NOT amount=0;
+----+-------------------------- +--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+-------------------------- +--------------------+--------+--------+
|  1 | Дубровский (Акция)        | Александр Пушкин   | 230.00 |     20 |
|  2 | Нос (Акция)               | Николай Гоголь     | 255.20 |      7 |
|  3 | Мастер и Маргарита        | Михаил Булгаков    | 263.00 |      8 |
|  4 | Мёртвые души (Акция)      | Николай Гоголь     | 173.00 |      3 |
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  6 | Война и мир               | Лев Толстой        | 341.00 |      1 |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 |      3 |
|  9 | Собачье сердце            | Михаил Булгаков    | 232.00 |     10 |
| 10 | Бесы                      | Фёдор Достоевский  | 212.00 |      8 |
+----+-------------------------- +--------------------+--------+--------+
9 rows in set (0.00 sec)

mysql>

Комбинации логических операторов

Используя круглые скобки, все логические операторы можно комбинировать и получать более сложные и многоуровневые условия.

Выведем все записи книг за авторством Ивана Тургенева и Фёдора Достоевсткого с ценой меньше 240, и которые при этом не имеют слова "Акция" в названии.

mysql> SELECT id, title, author, price, amount
    -> FROM books
    -> WHERE (price > 241 AND (author='Иван Тургенев' OR author='Фёдор Достоевский'))
    -> AND title NOT LIKE '%Акция%';
+----+---------------------------+--------------------+--------+--------+
| id | title                     | author             | price  | amount |
+----+---------------------------+--------------------+--------+--------+
|  5 | Преступление и наказание  | Фёдор Достоевский  | 245.00 |      3 |
|  8 | Отцы и дети               | Иван Тургенев      | 371.00 |      3 |
+----+---------------------------+--------------------+--------+--------+
2 rows in set (0.00 sec)

mysql>