Перевод первой части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».
Весь интернет и все приложения это по сути просто данные.
Каждый email, твит, селфи, банковская транзакция — просто данные, хранящиеся где-то в базе данных.
Чтобы от этих данных был какой-то прок, мы должны иметь возможность получать их. Но только лишь получения данных недостаточно: данные также должны быть полезными и подходящими для нашей ситуации.
На уровне базы данных мы запрашиваем из нее какую-либо информацию путем написания SQL-запроса. Этот SQL-запрос конкретизирует, какие именно данные и в каком формате мы хотим получить.
В этой статье мы поговорим обо всех самых распространенных способах фильтрации SQL-запросов. В первой части мы рассмотрим следующие темы:
- Настройка базы данных
- Создание пользователей
- Вставка пользователей
- Фильтрация данных при помощи WHERE
- Логические операторы (AND / OR / NOT)
- Операторы сравнения (<, >, <=, >=)
- Арифметические операторы (+, -, *, /, %)
Настройка базы данных
Чтобы иметь возможность фильтровать данные, нужно для начала этими данными обзавестись.
В наших примерах мы будем использовать PostgreSQL, но запросы и концепции, которые мы будем показывать, легко применимы в любой другой современной СУБД (например, в MySQL, SQL Server и т. п.).
Для работы с нашей базой данных PostgreSQL мы будем пользоваться интерактивной cli-программой psql. Если у вас установлен другой клиент, вы прекрасно можете работать с ним!
Для начала давайте создадим нашу базу данных. Поскольку у нас уже установлена PostgreSQL, для создания новой базы данных мы можем запустить в терминале psql-команду createdb <имя-базы-данных>
. Свою базу я назвал fcc
:
$ createdb fcc
Теперь давайте воспользуемся интерактивной консолью (запустив команду psql
) и подключимся к только что созданной базе данных при помощи команды \c <имя-базы-данных>
:
$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=#
Создание пользователей
Теперь, когда у нас есть база данных, давайте создадим в ней таблицу для моделирования потенциального пользователя в нашей придуманной системе.
Эту таблицу мы назовем users
, каждая ее строка будет представлять одного из наших пользователей.
Таблица users
будет содержать столбцы, описывающие пользователя. В столбцах будут указаны такие вещи как имя, адрес электронной почты и возраст.
Давайте в рамках нашей psql-сессии создадим таблицу users
:
CREATE TABLE users( id SERIAL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL, age INTEGER NOT NULL );
В выводе мы видим CREATE TABLE
: это означает, что создание таблицы прошло успешно.
Примечание: в примерах я подчистил вывод, чтобы их было легче читать. Поэтому не волнуйтесь, что показанный здесь output не в точности совпадает с тем, что вы видите в своем терминале.
Давайте посмотрим содержимое нашей таблицы с пользователями:
SELECT * FROM users; id | first_name | last_name | email | age ----+------------+-----------+-------+----- (0 rows)
Мы не вставили в таблицу никаких данных, поэтому видим просто пустую структуру.
Если вы не знакомы с SQL-запросами, этот запрос — SELECT * FROM users
— один из самых простых, которые вы можете написать.
Ключевое слово SELECT
указывает, какие именно столбцы вы хотите вернуть (*
означает «все столбцы»), а ключевое слово FROM
указывает, о какой таблице идет речь (в нашем случае — users
).
Таким образом, SELECT * FROM users
на самом деле означает «верни все строки и все столбцы из таблицы users».
Если бы мы хотели вернуть конкретные столбцы из таблицы users, мы могли бы заменить *
на названия столбцов, которые нам нужны. Например, SELECT id, name FROM users
.
Вставка (добавление) пользователей
Пустая таблица — не слишком интересная вещь, так что давайте добавим в нее кое-какие данные, чтобы попрактиковаться в составлении запросов:
INSERT INTO users(first_name, last_name, email, age) VALUES ('John', 'Smith', 'johnsmith@gmail.com', 25), ('Jane', 'Doe', 'janedoe@Gmail.com', 28), ('Xavier', 'Wills', 'xavier@wills.io', 35), ('Bev', 'Scott', 'bev@bevscott.com', 16), ('Bree', 'Jensen', 'bjensen@corp.net', 42), ('John', 'Jacobs', 'jjacobs@corp.net', 56), ('Rick', 'Fuller', 'fullman@hotmail.com', 16);
Если вы запустим эту инструкцию вставки в нашей psql-сессии, мы увидим в выводе INSERT 0 7
. Это означает, что мы успешно добавили в нашу таблицу 7 новых строк.
Запустив запрос SELECT * FROM users
еще раз, мы увидим внесенные данные:
SELECT * FROM users; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | jjacobs@corp.net | 56 2 | Rick | Fuller | fullman@hotmail.com | 16 3 | Bree | Jensen | bjensen@corp.net | 42 4 | Bev | Scott | bev@bevscott.com | 16 5 | Xavier | Wills | xavier@wills.io | 35 6 | Jane | Doe | janedoe@Gmail.com | 28 7 | John | Smith | johnsmith@gmail.com | 25 (7 rows)
Фильтрация данных при помощи WHERE
Пока что мы возвращали все строки из таблицы. Это поведение запроса по умолчанию. Чтобы вернуть более конкретный набор строк, нам нужно отфильтровать их, используя оборот WHERE
.
Есть много способов фильтрации с использованием WHERE
. Самый простой — применить оператор равенства =
.
Скажем, нам нужно найти пользователей, чье имя — John:
SELECT * FROM users WHERE first_name = 'John'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | jjacobs@corp.net | 56 7 | John | Smith | johnsmith@gmail.com | 25 (2 rows)
Здесь мы добавили в наш запрос ключевое слово WHERE
и таким образом определили, какие данные хотим получить. СУБД читает запрос, видит FROM users
, переходит к этой таблице и выбирает из нее все строки.
Когда из таблицы users получены все строки, по каждой из них отрабатывает оборот WHERE
. Он возвращает только те строки, где в столбце first_name
задано значение «John».
В наших данных есть две строки, отвечающие этому условию.
Если мы хотим найти в нашей системе конкретного Джона, мы можем составить запрос, основываясь на столбце, который, как мы знаем, является уникальным. Например, на столбце id
.
Чтобы найти строку Джона Якобса («John Jacobs»), мы можем запросить данные по его ID:
SELECT * FROM users WHERE id = 1; id | first_name | last_name | email | age ----+------------+-----------+------------------+----- 1 | John | Jacobs | jjacobs@corp.net | 56 (1 row)
Условию id = 1
отвечает только одна строка, ее-то мы и получим.
Логические операторы (AND / OR / NOT)
Фильтровать запросы можно не только при помощи оператора равенства. Мы также можем использовать булевы логические операторы, имеющиеся в большинстве языков программирования: and
, or
и not
.
Во многих языках программирования and и or представлены символами &&
и ||
. В SQL это просто AND
и OR
.
Давайте попробуем найти нужную запись для человека по имени Джон Смит («John Smith»), не указывая конкретный ID. Воспользуемся оператором AND в обороте WHERE, чтобы искать и по имени, и по фамилии:
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Smith'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 7 | John | Smith | johnsmith@gmail.com | 25 (1 row)
Поиск людей с именем John или с фамилией Doe:
SELECT * FROM users WHERE first_name = 'John' OR last_name = 'Doe'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | jjacobs@corp.net | 56 6 | Jane | Doe | janedoe@Gmail.com | 28 7 | John | Smith | johnsmith@gmail.com | 25 (3 rows)
В результате мы видим строки с обоими Джонами и одной Джейн Доу.
Условия AND
и OR
можно комбинировать. Допустим, мы хотим найти кого-то по имени John Smith или кого-то с фамилией Doe (т. е., все равно, с каким именем):
SELECT * FROM users WHERE ( first_name = 'John' AND last_name = 'Smith' ) OR last_name = 'Doe'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 6 | Jane | Doe | janedoe@Gmail.com | 28 7 | John | Smith | johnsmith@gmail.com | 25 (2 rows)
Если мы хотим инвертировать это условие и найти пользователей, чье имя не John Smith и фамилия не Doe, можно добавить оператор NOT
:
SELECT * FROM users WHERE NOT ( ( first_name = 'John' AND last_name = 'Smith' ) OR last_name = 'Doe' ); id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 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 3 | Xavier | Wills | xavier@wills.io | 35 (5 rows)
Примечание: у всех свой личный стиль форматирования запросов, так что делайте так, как удобно вам!
Операторы сравнения (<, >, <=, >=)
Подобно языкам программирования, SQL имеет операторы сравнения: <
, >
, <=
, >=
.
Давайте попрактикуемся в их использовании на столбце age
.
Допустим, нам нужно найти пользователей, которым уже исполнилось 18 лет (т. е., 18 и старше):
SELECT * FROM users WHERE age >= 18; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | jjacobs@corp.net | 56 3 | Bree | Jensen | bjensen@corp.net | 42 5 | Xavier | Wills | xavier@wills.io | 35 6 | Jane | Doe | janedoe@Gmail.com | 28 7 | John | Smith | johnsmith@gmail.com | 25 (5 rows)
Как насчет пользователей до 35 (включительно), которым при этом больше 25?
SELECT * FROM users WHERE age > 25 AND age <= 35; id | first_name | last_name | email | age ----+------------+-----------+-------------------+----- 5 | Xavier | Wills | xavier@wills.io | 35 6 | Jane | Doe | janedoe@Gmail.com | 28 (2 rows)
Арифметические операторы (+, -, *, /, %)
Кроме всего прочего, мы можем совершать математические действия с нашими данными.
В нашей таблице users
есть столбец age
. Что, если мы хотим найти половину возраста каждого человека?
SELECT *, age / 2 AS half_of_their_age FROM users; id | first_name | last_name | email | age | half_of_their_age ----+------------+-----------+---------------------+-----+------------------- 1 | John | Jacobs | jjacobs@corp.net | 56 | 28 2 | Rick | Fuller | fullman@hotmail.com | 16 | 8 3 | Bree | Jensen | bjensen@corp.net | 42 | 21 4 | Bev | Scott | bev@bevscott.com | 16 | 8 5 | Xavier | Wills | xavier@wills.io | 35 | 17 6 | Jane | Doe | janedoe@Gmail.com | 28 | 14 7 | John | Smith | johnsmith@gmail.com | 25 | 12 (7 rows)
Здесь мы выбрали из таблицы все столбцы (используя SELECT *
), а также выбрали новое агрегатное вычисление: age / 2
. Кроме того, при помощи ключевого слова AS
мы дали этому значению описательное имя (half_of_their_age
).
При помощи оператора деления по модулю (%
) мы также можем находить людей, чей возраст выражается четным числом.
SELECT * FROM users WHERE (age % 2) = 0; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | jjacobs@corp.net | 56 2 | Rick | Fuller | fullman@hotmail.com | 16 3 | Bree | Jensen | bjensen@corp.net | 42 4 | Bev | Scott | bev@bevscott.com | 16 6 | Jane | Doe | janedoe@Gmail.com | 28 (5 rows)
Если поменять условие равенства на неравенство (=
меняем на !=
или <>
), можно найти людей, чей возраст выражается нечетным числом:
SELECT * FROM users WHERE (age % 2) <> 0; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 5 | Xavier | Wills | xavier@wills.io | 35 7 | John | Smith | johnsmith@gmail.com | 25 (2 rows)
Во второй части статьи мы рассмотрим следующие темы:
- Операторы для проверки существования (IN / NOT IN)
- Частичное совпадение — использование LIKE
- Работа с отсутствующими данными (NULL)
- Использование IS NULL и IS NOT NULL
- Операторы сравнения при работе с датами и временем
- Проверка существования с использованием EXISTS / NOT EXISTS
- Поразрядные операторы
[customscript]techrocks_custom_after_post_html[/customscript]
[customscript]techrocks_custom_script[/customscript]