SQL-операторы: руководство с примерами запросов. Часть 2

От /

Перевод второй части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».

В первой части статьи мы рассмотрели такие темы:

В этой части мы рассмотрим:

Операторы для проверки существования (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]

2 комментария к “SQL-операторы: руководство с примерами запросов. Часть 2”

  1. evg

    Кул. Будет классно если напишите еще одну статью с примерами INNER JOIN, LEFT, RIGHT, OUGHTER

    Ответить

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Прокрутить вверх