Предикат SQL BETWEEN — поиск значений из указанного интервала
С помощью предиката SQL BETWEEN можно извлечь из таблицы строки, в которых значения некоторого проверяемого столбца находятся в интервале, границы которого обозначены некоторым выражением. Границы интервала также включены в него.
Запросы с предикатом SQL BETWEEN имеют следующий синтаксис:
Если перед предикатом BETWEEN поставить ключевое слово NOT, то в выборку попадут строки, в которых значение проверяемого столбца находится за пределами интервала: до ВЫРАЖЕНИЯ_1 и после ВЫРАЖЕНИЯ_2.
Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .
В первых примерах работаем с базой данных «Компания 2», содержащей данные о заработной плате сотрудников. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .
Запросы с BETWEEN: интервал задан указанными числами
В самых простых запросах с предикатом BETWEEN границы интервала для проверяемого столбца заданы непосредственным указанием числовых значений.
Пример 1. Итак, работаем с базой данных фирмы и её таблицей Staff, содержащей данные о заработной плате сотрудников.
| ID | Name | Salary |
| 1 | Sanders | 18357.5 |
| 2 | Pernal | 15430.0 |
| 3 | Marenghi | 17506.8 |
| 4 | Doctor | 12322.4 |
| 5 | Factor | 16228.7 |
| 6 | Junkers | 16232.8 |
| 7 | Moonlight | 21500.6 |
| 8 | Aisen | 19540.7 |
| 9 | MacGregor | 15790.8 |
Требуется вывести сотрудников, заработная плата которых находится в интервале от 16 000 до 18 000. Пишем следующий запрос с предикатом BETWEEN:
Запрос выведет следующую результирующую таблицу:
| ID | Name | Salary |
| 3 | Marenghi | 17506.8 |
| 5 | Factor | 16228.7 |
| 6 | Junkers | 16232.8 |
Пример 2. База данных и таблица — те же, что в примере 1. Требуется вывести сотрудников, заработная плата которых находится вне интервала от 16 000 до 18 000. Пишем следующий запрос с предикатом BETWEEN, перед которым ставим ключевое слово NOT:
Запрос выведет следующую результирующую таблицу:
| ID | Name | Salary |
| 1 | Sanders | 18357.5 |
| 2 | Pernal | 15430.0 |
| 4 | Doctor | 12322.4 |
| 7 | Moonlight | 21500.6 |
| 8 | Aisen | 19540.7 |
| 9 | MacGregor | 15790.8 |
Запросы с BETWEEN: интервал задан вложенными запросами
В запросах с BETWEEN границы интервала для проверяемого столбца можно не указывать явно, задав его некоторыми условиями, которые определены вложенными запросами.
Пример 3. Продолжаем работать с базой данных Staff. Требуется вывести сотрудников, заработная плата которых находится в интервале между средней и максимальной. Пишем следующий запрос, в котором границы заданы вложенными запросами:
Результатом выполнения запроса будет следующая таблица:
| ID | Name | Salary |
| 1 | Sanders | 18357.5 |
| 3 | Marenghi | 17506.8 |
| 7 | Moonlight | 21500.6 |
| 8 | Aisen | 19540.7 |
Пример 4. База данных и таблица — те же. Требуется вывести сотрудников, заработная плата которых находится в интервале между заработной платой сотрудника MacGregor и заработной платой сотрудика Marenghi. Пишем следующий запрос, в котором границы заданы вложенными запросами:
Результатом выполнения запроса будет следующая таблица:
| ID | Name | Salary |
| 3 | Marenghi | 17506.8 |
| 5 | Factor | 16228.7 |
| 6 | Junkers | 16232.8 |
| 9 | MacGregor | 15790.8 |
Как видим, граничные значения интервала попали в результирующую таблицу.
Написать запрос с предикатом BETWEEN самостоятельно, а затем посмотреть решение
Пример 5. Продолжаем работать с таблицей Staff. Требуется вывести сотрудников с ID между ID сотрудника Marenghi и ID сотрудника Moonlight.
Запросы с BETWEEN: интервал задан значениями даты и времени
Интервал для извлечения строк с помощью предиката BETWEEN может быть задан не только значениями числового типа данных, но и значениями даты и времени.
Далее работаем с базой данных «Недвижимость». Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .
Пример 6. Итак, работаем с базой данных «Недвижимость».
Таблица Deal содержит данные о сделках. Нам потребуется столбец Date, в котором указывается дата совершения сделки. Для упрощения будем считать, что в таблице представлены сделки, заключённые в течение одного года. Требуется вывести сделки, заключённые в августе и сентябре. Пишем следующий запрос:
Примеры запросов к базе данных «Недвижимость» есть также в уроках по оператору GROUP BY, предикату EXISTS, функциям ALL и ANY и LIMIT.
Команда INTERVAL
Команда INTERVAL позволяет прибавлять к дате и отнимать от нее определенные промежутки времени.
Синтаксис
После команды INTERVAL можно указывать определенную часть даты (день, месяц или год и тп), к примеру, так INTERVAL 1 DAY или INTERVAL 3 MONTH, или сразу несколько частей.
В этом случае значения даты берутся в кавычки, пример: давайте прибавим 1 год и 3 месяца — это будет выглядеть так: INTERVAL ‘1-3’ YEAR_MONTH.
Мы указываем, что хотим год и месяц (вот так: YEAR_MONTH), и перед этим в кавычках пишем сначала значение для года, потом значение для месяца. Между ними ставим разделитель (он может быть абсолютно любым).
Смотрите список возможных комбинаций (разделители указаны для примера, они могут быть любыми):
| Значение | Ожидаемый формат |
|---|---|
| SECOND | секунды |
| MINUTE | минуты |
| HOUR | часы |
| DAY | дни |
| MONTH | месяцы |
| YEAR | года |
| MINUTE_SECOND | «минуты:секунды» |
| HOUR_MINUTE | «часы:минуты» |
| DAY_HOUR | «дни часы» |
| YEAR_MONTH | «года-месяцы» |
| HOUR_SECOND | «часы:минуты:секунды» |
| DAY_MINUTE | «дни часы:минуты» |
| DAY_SECOND | «дни часы:минуты:секунды» |
Примеры
Все примеры будут по этой таблице workers, если не сказано иное:
| id айди |
name имя |
date дата |
|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 |
Пример
В данном примере к дате прибавляется 1 день:
SQL запрос выберет следующие строки:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2010-03- 02 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2011-05- 03 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2012-06- 04 14:03:04 |
Пример
В данном примере от даты отнимается 1 день:
SQL запрос выберет следующие строки:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2010- 02-28 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2011-05- 01 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2012-06- 02 14:03:04 |
Пример
В данном примере к дате прибавляется 1 год и 2 месяца:
SQL запрос выберет следующие строки:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2011-05 -01 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2012-06 -02 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2013-07 -03 14:03:04 |
Разделитель в запросе не имеет значения. Он может быть любым символом:
Можно использовать даже несколько символов:
Можно использовать и пробел:
Пример
В данном примере к дате прибавляется 1 год, 2 месяца и 1 день:
SQL запрос выберет следующие строки:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | Дима | 2010-03-01 12:01:02 | 2011-05-02 12:01:02 |
| 2 | Петя | 2011-04-02 13:02:03 | 2012-06-03 13:02:03 |
| 3 | Вася | 2012-05-03 14:03:04 | 2013-07-04 14:03:04 |
Запрос можно переписать таким образом:
Пример
В данном примере к дате прибавляется 1 год, отнимаются 2 месяца и прибавляется 1 день:
Как сравнивать даты в MySQL
MySQL предоставляет возможность сравнивать различные даты между собой, или с каким-то определенным выражением. В этой статье мы обсудим, как работать с датой в Mysql, как из сравнивать и строить запросы с учетом дат.
Когда вам нужно сравнить дату какого-то столбца с произвольной датой, вы можете использовать функцию DATE() , которая извлекает дату из переданного аргумента (без учета времени) и сравнить ее со строкой нужной вам даты.
В прошлой своей статье я писал про работу с индексами. А в этой затрону не менее важную тему работы с датой в MySQL, понимание которое необходимо каждому разработчику.
Например, предположим, что у вас есть таблица MySQL под названием users со следующими строками:
Теперь вам нужно выбрать все строки из таблицы users , у которых значение last_update больше 2021-08-01 .
И это достаточно просто сделать:
Результат запроса выше будет следующим:
При сравнении столбца типа DATETIME или TIMESTAMP с датой в виде строки (как в запросе выше), MySQL автоматически преобразует значения к единому формату для сравнения и веронет подходящзие результаты.
Чтобы проверить результат запроса добавим оператор ORDER BY в приведенный выше запрос.
Вы можете сразу определить, удовлетворяет ли запрос вашему требованию, взглянув на первую строку. Как и в приведенном выше примере, самое раннее значение столбца last_update должно быть 2021-08-02 .
Вы также можете использовать оператор BETWEEN , чтобы выбрать все строки, в которых столбец даты находится между двумя указанными выражениями даты:
Приведенный выше запрос выведет следующий результат:
НО! MySQL допускает только один формат даты: yyyy-mm-dd , поэтому вам нужно форматировать любое строковое выражение даты по которым вы хотите выбирать данные к нужному формату.
Зачем использовать функцию DATE() для сравнения дат
Функция MySQL DATE() извлекает часть даты из столбца DATETIME или TIMESTAMP и приводит её к формату строки, как показано ниже:
Эта функция используется для того, чтобы MySQL использовал для сравнения только часть даты вашего столбца, без учета времени.
Если не использовать функцию DATE() , то MySQL также будет сравнивать и время столбца с вашим строковым выражением. Таким образом, результатом будут записи, полностью совпадаюащие до секунды.
Возвращаясь к приведенному выше примеру таблицы, следующий запрос:
Вернет такие результаты:
Как видно из результатов, условие сравнения last_update > "2021-08-01" становится last_update > "2021-08-01 00:00:00" , и MySQL возвращает соответствующий набор результатов.
Если вы собираетесь сравнивать записи включая время, то вам нужно добавить часть времени в ваше строковое выражение.
Следующий запрос из той же таблицы:
Даст следующий результат:
Сравнение дат между двумя столбцами дат
Если у вас уже есть два столбца даты, то вы можете сразу же сравнить их с помощью операторов < , <= , = , >= , > или BETWEEN .
Предположим, что в вашей таблице есть столбцы last_update и last_login , как показано ниже:
Чтобы получить все строки, в которых столбец last_login находится позже столбца last_update , вы можете использовать следующий запрос:
Результат будет следующим:
Вот каким образом вы можете выполнять сравнение дат в MySQL.
Не забудьте, что вам нужно иметь строковое выражение даты, отформатированное как yyyy-mm-dd или yyyy-mm-dd hh:mm:ss , если вы хотите сравнить и временную часть.
Выбор дат за определнный интервал
Очень часто приходится выбирать записи с датой за определнный интервал. Для этих целей в MySql существуют встроенная функции INTERVAL , упрощающая работу с датами за определенный период.
Например, для того, чтобы выбрать записи, обновленные за последний год, выполним запрос:
При том, что INTERVAL может работать с различными интервалами времени:
- SECOND — секунды
- MINUTE — минуты
- HOUR — часы
- DAY — дни
- MONTH — месяцы
- YEAR — года
Те же самые действия применимы и к UPDATE-операциям:
Резюме
В этой статье поговорили про работу с датами в Mysql. Как можно выбрать даты определенной даты, или по указанному интервалу. А так же, как работать с колонками дат разных типов.
Если вы хотите поупражняться с запросами, то вот вам демонстрационная таблица: