ИТ.03 — 06 — Особый тип данных NULL. Операторы LIKE, ORDER BY, LIMIT

Введение

В предыдущих темах мы научились выбирать и фильтровать данные при помощи SQL-запросов.

Сегодня разберём ещё несколько важных инструментов, которые позволяют гибко управлять выборкой: поиск по шаблону (LIKE), сортировку (ORDER BY), ограничение количества строк (LIMIT), а также познакомимся с особым значением — NULL.

Пример таблицы employees

CREATE TABLE
  employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    salary INT NOT NULL,
    job_title VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    bonus VARCHAR(255) NULL,
    gender CHAR(1) NULL,
    department VARCHAR(255) NULL
  )
;

INSERT INTO
  employees (id, first_name, last_name, salary, job_title, email, bonus, gender, department)
VALUES
  (1, 'Дмитрий', 'Петров', 25000, 'офис-менеджер', 'd.petrov@company.ru', NULL, 'М', 'Администрация'),
  (2, 'Ольга', 'Антонова', 41000, 'дизайнер', 'designer_olga@nemail.ru', NULL, 'Ж', 'Дизайн'),
  (3, 'Сергей', 'Васильев', 40000, 'программист', NULL, '+5000 руб. за проект', 'М', 'IT'),
  (4, 'Константин', 'Сергеев', 30000, 'водитель', 'k.sergeev@company.net', 'компенсация топлива', 'М', 'Логистика'),
  (5, 'Алена', 'Голубева', 53000, 'фотограф', 'alena_photo@negmail.com', NULL, 'Ж', 'Маркетинг'),
  (6, 'Василиса', 'Иванова', 28000, 'программист', 'v.ivanova@company.ru', NULL, 'Ж', 'IT'),
  (7, 'Александр', 'Петров', 120000, 'ведущий программист', 'a.petrov@company.ru', '+3% к окладу', 'М', 'IT'),
  (8, 'алина', 'Антонова', 40000, 'программист', 'a.antonova@company.ru', '+2% к окладу', 'Ж', 'IT'),
  (9, 'Федор', 'Яковлев', 27000, 'программист-стажер', NULL, NULL, 'М', 'IT')
;

Что такое NULL

В SQL значение NULL означает отсутствие данных.

Это не ноль, не пустая строка и не пробел — это именно неизвестное значение.

Чтобы работать с NULL, используются специальные операторы:

SELECT NULL = NULL;    -- Результат: NULL
SELECT NULL <=> NULL;  -- Результат: 1 (TRUE)

SELECT NULL IS NULL;      -- Результат: 1 (TRUE)
SELECT NULL IS NOT NULL;  -- Результат: 0 (FALSE)

Работа с NULL

-- в интерактивной консоли:
.nullvalue 'NULL'

-- дальше может идти ваш код:
##CODE##

Выведем всех сотрудников, у которых не указана электронная почта:

SELECT first_name, last_name, email
FROM employees
WHERE email IS NULL;

Результат:
Показаны только те сотрудники, у которых значение email отсутствует.

А теперь наоборот — сотрудники, у которых почта указана:

SELECT first_name, last_name, email
FROM employees
WHERE email IS NOT NULL;

Результат:
Показаны только сотрудники с заполненным полем email.

Поиск по шаблону (оператор LIKE)

Оператор LIKE используется для поиска строк, которые соответствуют заданному шаблону.

Например, у нас есть таблица employees, в которой у каждого сотрудника есть поле email. Допустим, мы хотим найти всех пользователей, чьи почтовые ящики находятся на домене компании company. Т.е. нужно отобрать только те записи, что отвечают условию:

• после символа @ следует company
• после company следует символ «.» и далее любая последовательность символов (т.к. есть и другие доменные зоны кроме ru)

Для таких нетривиальных поисков по строковым полям и нужен оператор LIKE.

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%@company.%';

Результат:
Все пользователи, чьи почтовые ящики находятся на домене компании company.

SELECT first_name
FROM employees
WHERE first_name LIKE 'а%';

SELECT first_name
FROM employees
WHERE
  first_name LIKE 'А%'
    OR
  first_name LIKE 'а%'
;

Вывести сотрудников, чьи имена содержат букву «а»:

SELECT first_name
FROM employees
WHERE first_name LIKE '%а%';

Результат:
Выводятся сотрудники, в имени которых встречается буква а.

Вывести сотрудников, чьи адреса электронной почты находятся в зоне .ru:

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%.ru';

Результат:
Выводятся сотрудники, адреса электронной почты которых находятся в зоне .ru.

Вывести сотрудников, получающих шестизначную зарплату:

SELECT first_name, last_name, job_title, salary
FROM employees
WHERE salary LIKE '______';

Результат:
Выводятся сотрудники, в цифре зарплаты которых ровно 6 знаков.

ESCAPE-символ

ESCAPE-символ используется для экранирования специальных символов (%, _, /). В случае если вам нужно найти строки, содержащие их, вы можете использовать ESCAPE-символ.

Например, вы хотите получить информацию по бонусам к окладу в процентах:

SELECT first_name, last_name, bonus
FROM employees
WHERE bonus LIKE '%!%%' ESCAPE '!';

Результат:
Выводятся сотрудники, в условиях бонуса которых содержится символ %. Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё.

Сортировка результатов (ORDER BY)

При выполнении запроса SELECT, строки по умолчанию возвращаются в неопределенном порядке. Фактический порядок строк в этом случае зависит от того порядка в котором данные попадали в базу. Для упорядочивания записей используется конструкция ORDER BY.

Например, выведем всех сотрудников, отсортировав их по зарплате от наименьшей к наибольшей:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary;

Результат:
Сотрудники, отсортированные по зарплате — от самой высокой к самой низкой.

Для сортировки результатов по двум или более столбцам их следует указывать через запятую.

...ORDER BY столбец_1 [ASC | DESC], столбец_2 [ASC | DESC];

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

При этом стоит учесть что правило сортировки применяется только к тому столбцу, за которым оно следует.

ORDER BY столбец_1, столбец_2 DESC

не то же самое, что

ORDER BY столбец_1 DESC, столбец_2 DESC

Например, выведем всех сотрудников, отсортировав по алфавиту их сначала по фамилии а потом по имени:

SELECT last_name, first_name
FROM employees
ORDER BY last_name ASC, first_name ASC;

Результат:
Сотрудники упорядочены по алфавиту сначала по фамилии, а потом — по имени.

Также порядок сортировки каждого столбца может различаться, например если мы хотим вывести сотрудников, упорядочив их сначала по должности, а внутри должностей — по убыванию зарплаты:

SELECT first_name, last_name, job_title, salary
FROM employees
ORDER BY job_title ASC, salary DESC;

Результат:
Сотрудники упорядочены сначала по должности, а внутри должностей — по убыванию зарплаты.

Ограничение количества строк (LIMIT)

Оператор LIMIT ограничивает количество строк, возвращаемых запросом.

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

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

Комбинация условий

Можно комбинировать WHERE, LIKE, ORDER BY и LIMIT для более точных выборок:

SELECT salary, last_name, first_name, department, job_title, email
FROM employees
WHERE
  department = 'IT'
    AND
  job_title LIKE '%программист%'
    AND
  (email LIKE '%@company.ru' OR email IS NULL)
ORDER BY salary DESC, last_name ASC
LIMIT 3;

Результат:
Три сотрудника из отдела IT с должностями, содержащими «программист», у кого либо есть корпоративная почта на домене @company.ru, либо её нет; отсортированы по убыванию зарплаты (при равенстве — по фамилии)

Практические задания

1. Выведите всех сотрудников, у которых нет электронной почты.
2. Найдите сотрудников, чьи фамилии начинаются на «П».
3. Выведите сотрудников, чьи адреса электронной почты находятся в домене @company.ru, отсортировав их по зарплате по убыванию.
4. Покажите три сотрудника с самыми низкими зарплатами.
5. Найдите всех сотрудников, у которых в должности встречается слово «программист», и выведите их в порядке возрастания зарплаты.
6. Выведите сотрудников, у которых зарплата шестизначная, и отсортируйте их по фамилии в алфавитном порядке.

-- Ваш код можете писать тут