Перевод второй части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».
В первой части статьи мы рассмотрели такие темы:
- Настройка базы данных
- Создание пользователей
- Вставка пользователей
- Фильтрация данных при помощи WHERE
- Логические операторы (AND / OR / NOT)
- Операторы сравнения (<, >, <=, >=)
- Арифметические операторы (+, -, *, /, %)
В этой части мы рассмотрим:
- Операторы для проверки существования (IN / NOT IN)
- Частичное совпадение — использование LIKE
- Работа с отсутствующими данными (NULL)
- Использование IS NULL и IS NOT NULL
- Операторы сравнения при работе с датами и временем
- Проверка существования с использованием EXISTS / NOT EXISTS
- Поразрядные операторы
Операторы для проверки существования (IN / NOT IN)
Если мы хотим проверить, есть ли определенное значение в списке значений, мы можем воспользоваться операторами IN
или NOT IN
:
SELECT * FROM users WHERE first_name IN ('John', 'Jane', 'Rick'); id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | johnsmith@gmail.com | 25 2 | Jane | Doe | janedoe@Gmail.com | 28 6 | John | Jacobs | jjacobs@corp.net | 56 7 | Rick | Fuller | fullman@hotmail.com | 16 (4 rows)
Аналогично, для отрицания используется NOT IN
:
SELECT * FROM users WHERE first_name NOT IN ('John', 'Jane', 'Rick'); id | first_name | last_name | email | age ----+------------+-----------+------------------+----- 3 | Xavier | Wills | xavier@wills.io | 35 4 | Bev | Scott | bev@bevscott.com | 16 5 | Bree | Jensen | bjensen@corp.net | 42 (3 rows)
Частичное совпадение — использование LIKE
Иногда нам нужно найти строки, основываясь на частичном совпадении.
Допустим, мы хотим найти всех пользователей, которые зарегистрировались в нашем приложении при помощи адреса Gmail. Мы можем поискать частичное совпадение в столбце, используя ключевое слово LIKE
. Также при этом можно использовать групповой символ — %
.
Поиск пользователей, чей email оканчивается на gmail.com
:
SELECT * FROM users WHERE email LIKE '%gmail.com'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | johnsmith@gmail.com | 25 (1 row)
Строка %gmail.com
означает «совпадение со всем, что кончается на gmail.com».
Если мы посмотрим на данные наших пользователей, мы заметим, что среди них только у двоих адрес электронной почты кончается на gmail.com
:
('John', 'Smith', 'johnsmith@gmail.com', 25), ('Jane', 'Doe', 'janedoe@Gmail.com', 28),
Но в email Джейн указана заглавная «G». Предыдущий запрос не выберет эту запись, потому что мы ищем точное совпадение с gmail.com, а там «g» в нижнем регистре.
Чтобы поиск не зависел от регистра, нужно заменить LIKE
на ILIKE
:
SELECT * FROM users WHERE email ILIKE '%gmail.com'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | johnsmith@gmail.com | 25 2 | Jane | Doe | janedoe@Gmail.com | 28 (2 rows)
Групповой символ %
в начале строки означает, что вернуть нужно все, что заканчивается на «gmail.com». Это может быть и ob.jones+12345@gmail.com, и asdflkasdflkj@gmail.com — главное, чтобы в конце стояло «gmail.com».
Мы также можем использовать столько групповых символов, сколько нам нужно.
Например, поиск %j%o%
вернет любой email-адрес, соответствующий шаблону «<все-что-угодно>, за чем следует j, за чем следует <все-что-угодно>, за чем следует o, за чем следует <все-что-угодно>»:
SELECT * FROM users WHERE email ILIKE '%j%o%'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | johnsmith@gmail.com | 25 2 | Jane | Doe | janedoe@Gmail.com | 28 5 | Bree | Jensen | bjensen@corp.net | 42 6 | John | Jacobs | jjacobs@corp.net | 56 (4 rows)
От редакции Techrocks: также рекомендуем статью «Топ-30 вопросов по SQL на технических собеседованиях».
Работа с отсутствующими данными (NULL)
Давайте посмотрим, как быть со столбцами и строками, где нет данных.
Для этого давайте добавим в нашу таблицу users еще один столбец: first_paid_at
.
Этот новый столбец будет TIMESTAMP
(подобно datetime
в других языках) и будет представлять дату и время, когда пользователь впервые заплатил нам за наше приложение. Может, мы хотим послать ему открытку и цветы в честь годовщины.
Мы могли бы стереть нашу таблицу users
, введя DROP TABLE users
, и пересоздать ее заново, но таким образом мы удалили бы все данные в таблице.
Чтобы изменить таблицу, не стирая ее и не лишаясь данных, можно использовать ALTER TABLE
:
ALTER TABLE users ADD COLUMN first_paid_at TIMESTAMP;
Эта команда возвращает результат ALTER TABLE
, так что наш запрос ALTER
сработал успешно.
Если мы теперь запросим нашу таблицу users
, мы заметим, что теперь в ней появился новый столбец без данных:
SELECT * FROM users; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 1 | John | Smith | johnsmith@gmail.com | 25 | 2 | Jane | Doe | janedoe@Gmail.com | 28 | 3 | Xavier | Wills | xavier@wills.io | 35 | 4 | Bev | Scott | bev@bevscott.com | 16 | 5 | Bree | Jensen | bjensen@corp.net | 42 | 6 | John | Jacobs | jjacobs@corp.net | 56 | 7 | Rick | Fuller | fullman@hotmail.com | 16 | (7 rows)
Наш столбец first_paid_at
пуст, и результат нашего psql-запроса показывает, что это пустой столбец. Технически он не пустой: в нем содержится специальное значение, которое psql просто не показывает в выводе — NULL
.
NULL это специальное значение в базах данных. Это отсутствие значения, и оно ведет себя не так, как можно было бы ожидать.
Чтобы это продемонстрировать, давайте посмотрим на простой SELECT
:
SELECT 1 = 1, 1 = 2; ?column? | ?column? ----------+---------- t | f (1 row)
Здесь мы просто выбрали 1 = 1
и 1 = 2
. Как мы и ожидали, результат этих двух предложений — t
и f
(или TRUE
и FALSE
). 1 равен 1, но 1 не равен 2.
Теперь давайте попробуем проделать то же самое с NULL
:
SELECT 1 = NULL; ?column? ---------- (1 row)
Мы могли ожидать, что значением будет FALSE
, но на деле возвращается значение NULL
.
Чтобы еще лучше визуализировать NULL
, давайте при помощи опции \pset
посмотрим, как psql отображает NULL-значения:
fcc=# \pset null 'NULL' Null display is "NULL".
Если мы запустим этот запрос еще раз, мы увидим в выводе ожидаемый нами NULL
:
SELECT 1 = NULL; ?column? ---------- NULL (1 row)
Итак, 1 не равен NULL
, а как насчет NULL = NULL
?
SELECT NULL = NULL; ?column? ---------- NULL (1 row)
Довольно странно, однако NULL
не равен NULL
.
NULL
лучше представлять себе как неизвестное значение. Равно ли неизвестное значение единице? Мы не знаем, оно же неизвестное. Равно ли неизвестное значение неизвестному значению? Опять же, мы этого не знаем. Это немного лучше поясняет, что такое NULL
.
Использование IS NULL и IS NOT NULL
Мы не можем использовать с NULL оператор равенства, но мы можем пользоваться двумя специально созданными для этого операторами: IS NULL
и IS NOT NULL
.
SELECT NULL IS NULL, NULL IS NOT NULL; ?column? | ?column? ----------+---------- t | f (1 row)
Эти значения ожидаемы: NULL IS NULL
— истина, NULL IS NOT NULL
— ложь.
Это все прекрасно и очень интересно, но как это применять на практике?
Что ж, для начала давайте заведем какие-то данные в нашем столбце first_paid_at
:
UPDATE users SET first_paid_at = NOW() WHERE id = 1; UPDATE 1 UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 month') WHERE id = 2; UPDATE 1 UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 year') WHERE id = 3; UPDATE 1
В приведенной выше инструкции UPDATE
мы задали значения для столбца first_paid_at
у троих разных пользователей: пользователю с ID 1 — текущее время (NOW()
), пользователю с ID 2 — текущее время минус месяц, а пользователю с ID 3 — текущее время минус год.
Во-первых, давайте найдем пользователей, которые нам уже платили, и пользователей, которые пока этого не делали:
SELECT * FROM users WHERE first_paid_at IS NULL; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 4 | Bev | Scott | bev@bevscott.com | 16 | NULL 5 | Bree | Jensen | bjensen@corp.net | 42 | NULL 6 | John | Jacobs | jjacobs@corp.net | 56 | NULL 7 | Rick | Fuller | fullman@hotmail.com | 16 | NULL (4 rows) SELECT * FROM users WHERE first_paid_at IS NOT NULL; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | johnsmith@gmail.com | 25 | 2020-08-11 20:49:17.230517 2 | Jane | Doe | janedoe@Gmail.com | 28 | 2020-07-11 20:49:17.233124 3 | Xavier | Wills | xavier@wills.io | 35 | 2019-08-11 20:49:17.23488 (3 rows)
Операторы сравнения при работе с датами и временем
Теперь, когда у нас есть кое-какие данные, давайте используем те же операторы сравнения применительно к новому полю TIMESTAMP
.
Попробуем найти пользователей, которые совершили платеж на протяжении последней недели. Для этого мы можем взять текущее время (NOW()
) и вычесть из него одну неделю при помощи ключевого слова INTERVAL
:
SELECT * FROM users WHERE first_paid_at > (NOW() - INTERVAL '1 week'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | johnsmith@gmail.com | 25 | 2020-08-11 20:49:17.230517 (1 row)
Мы также можем использовать другой интервал, например, последние три месяца:
SELECT * FROM users WHERE first_paid_at < (NOW() - INTERVAL '3 months'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+-----------------+-----+--------------------------- 3 | Xavier | Wills | xavier@wills.io | 35 | 2019-08-11 20:49:17.23488 (1 row)
Теперь давайте найдем пользователей, которые совершали платеж в промежутке от одного до шести месяцев назад.
Мы можем скомбинировать наши условия, используя AND
, но вместо использования операторов <
и >
давайте используем ключевое слово BETWEEN
:
SELECT * FROM users WHERE first_paid_at BETWEEN (NOW() - INTERVAL '6 month') AND (NOW() - INTERVAL '1 month'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+-------------------+-----+---------------------------- 2 | Jane | Doe | janedoe@Gmail.com | 28 | 2020-07-11 20:49:17.233124 (1 row)
Проверка существования с использованием EXISTS / NOT EXISTS
Другой способ проверить существование (наличие) значения — использовать EXISTS
и NOT EXISTS
.
Эти операторы фильтруют строки, проверяя существование или несуществование условия. Это условие обычно является запросом к другой таблице.
Чтобы это продемонстрировать, давайте создадим новую таблицу под названием posts
. В этой таблице будут содержаться посты, котоыре пользователь может делать в нашей системе.
CREATE TABLE posts( id SERIAL PRIMARY KEY, body TEXT NOT NULL, user_id INTEGER REFERENCES users NOT NULL );
Это простая таблица. Она содержит только ID, поле для хранения текста поста (body
) и ссылку на пользователя, который написал этот пост (user_id
).
Давайте добавим в новую таблицу некоторые данные:
INSERT INTO posts(body, user_id) VALUES ('Here is post 1', 1), ('Here is post 2', 1), ('Here is post 3', 2), ('Here is post 4', 3);
Согласно добавленным данными, у пользователя с ID 1 есть два поста, у пользователя с ID 2 — один пост, у пользователя с ID 3 — тоже один пост.
Чтобы найти пользователей, у которых есть посты, мы можем использовать ключевое слово EXISTS
.
EXISTS
принимает подзапрос. Если этот подзапрос возвращает что-либо (даже строку со значением NULL
), база данных включит эту строку в результат.
EXISTS
проверяет лишь существование строки из подзапроса, ему не важно, что именно содержится в этой строке.
Вот пример выборки пользователей, имеющих посты:
SELECT * FROM users WHERE EXISTS ( SELECT 1 FROM posts WHERE posts.user_id = users.id ); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | johnsmith@gmail.com | 25 | 2020-08-11 20:49:17.230517 2 | Jane | Doe | janedoe@Gmail.com | 28 | 2020-07-11 20:49:17.233124 3 | Xavier | Wills | xavier@wills.io | 35 | 2019-08-11 20:49:17.23488 (3 rows)
Как и ождилалось, мы получили пользователей с ID 1, 2 и 3.
Наш подзапрос EXISTS
проверяет записи в таблице posts, где user_id
поста совпадает со столбцом id таблицы users. Мы вернули 1 в нашем SELECT
, потому что здесь мы можем вернуть что угодно: база данных просто хочет видеть, что что-то вернулось.
Аналогично, мы можем найти пользователей, у которых нет постов. Для этого нужно заменить EXISTS
на NOT EXISTS
:
SELECT * FROM users WHERE NOT EXISTS ( SELECT 1 FROM posts WHERE posts.user_id = users.id ); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 4 | Bev | Scott | bev@bevscott.com | 16 | NULL 5 | Bree | Jensen | bjensen@corp.net | 42 | NULL 6 | John | Jacobs | jjacobs@corp.net | 56 | NULL 7 | Rick | Fuller | fullman@hotmail.com | 16 | NULL (4 rows)
Наконец, мы можем переписать наш запрос и использовать IN
или NOT IN
вместо EXISTS
или NOT EXISTS
:
SELECT * FROM users WHERE users.id IN ( SELECT user_id FROM posts );
Технически это сработает, но вообще, если вы проверяете существование другое записи, более производительно будет использовать EXISTS
. Операторы IN
и NOT IN
в целом лучше применять для проверки значения в статическом списке, как мы делали ранее:
SELECT * FROM users WHERE first_name IN ('John', 'Jane', 'Rick');
Поразрядные операторы
Хотя на практике поразрядные операторы используются нечасто, для полноты картины давайте рассмотрим простой пример.
Если мы по какой-то причине хотим посмотреть возраст наших пользователей в бинарном виде и поиграться с перестановкой битов, мы можем использовать поразрядные операторы.
В качестве примера давайте рассмотрим поразрядный оператор «and»: &
.
SELECT age::bit(8) & '11111111' FROM users; ?column? ---------- 00010000 00101010 00111000 00010000 00011001 00011100 00100011 (7 rows)
Чтобы осуществить поразрядную операцию, нам сначала нужно преобразовать значения в нашем столбце age из целых чисел в бинарный формат. В данном случае мы использовали ::bit(8)
и получили восьмибитовые строки.
Далее мы можем «сложить» результат в бинарном формате с другой строкой в бинарном формате — 11111111
. Поскольку бинарный AND возвращает единицу только если оба бита это единицы, эта добавочная строка делает вывод интересным.
Практически все остальные поразрядные операторы используют тот же формат:
SELECT age::bit(8) | '11111111' FROM users; -- поразрядный OR SELECT age::bit(8) # '11111111' FROM users; -- поразрядный XOR SELECT age::bit(8) << '00000001' FROM users; -- поразрядный сдвиг влево SELECT age::bit(8) >> '00000001' FROM users; -- поразрядный сдвиг вправо
Поразрядный оператор «not» (~
) немного отличается. Он применяется к одному термину, так же, как и обычный оператор NOT
:
SELECT ~age::bit(8) FROM users; ?column? ---------- 11101111 11010101 11000111 11101111 11100110 11100011 11011100 (7 rows)
И, наконец, самый полезный из поразрядных операторов: конкатенация.
Этот оператор обычно используется для склейки вместе строк текста. Например, если мы хотим составить вычисленное «полное имя» для пользователей, мы можем воспользоваться конкатенацией:
SELECT first_name || ' ' || last_name AS name FROM users; name -------------- Bev Scott Bree Jensen John Jacobs Rick Fuller John Smith Jane Doe Xavier Wills (7 rows)
Здесь мы для создания значения name сконкатенировали (скомбинировали) first_name
, пробел (' '
) и last_name
.
Заключение
Итак, мы рассмотрели практически все операторы фильтрации, котоыре вам могут понадобиться в работе!
Есть еще несколько, о которых мы не упоминали, но они либо используются не слишком часто, либо используются точно так же, как те, что мы разобрали, так что у вас не должно возникнуть проблем с ними.
От редакции Techrocks: возможно, вам будет интересна еще одна статья того же автора: SQL JOIN: руководство по объединению таблиц.
[customscript]techrocks_custom_after_post_html[/customscript]
[customscript]techrocks_custom_script[/customscript]
Кул. Будет классно если напишите еще одну статью с примерами INNER JOIN, LEFT, RIGHT, OUGHTER
Спасибо на добром слове. И вот никто не может сказать, что мы невнимательны к нашим читателям: https://techrocks.ru/2020/09/04/sql-join-tutorial-1/
Единственно — это не мы писали, это перевод другой статьи того же автора.