Использование Функций с Датами. Использование даты в sql примеры
SQL Server и MySQL в функции Date
SQL Дата (даты)
Когда мы имеем дело с датой, это, вероятно, самая сложная задача для того, чтобы вставить формат даты соответствует формату даты в столбце базы данных.
До тех пор пока данные содержат только часть даты, ваши запросы пойдет не так. Тем не менее, если время компонент участвует, ситуация немного сложнее.
Прежде чем обсуждать сложность даты запроса, давайте посмотрим на наиболее важных встроенных функций обработки даты.
Функции даты MySQL
В следующей таблице перечислены наиболее важные встроенные функции MySQL даты:
| NOW() | 返回当前的日期和时间 |
| CURDATE() | 返回当前的日期 |
| CURTIME() | 返回当前的时间 |
| DATE() | 提取日期或日期/时间表达式的日期部分 |
| EXTRACT() | 返回日期/时间的单独部分 |
| 向日期添加指定的时间间隔 | |
| DATE_SUB() | 从日期减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的天数 |
| DATE_FORMAT() | 用不同的格式显示日期/时间 |
SQL Дата сервера Функции
В следующей таблице перечислены SQL Server в наиболее важных встроенных функций даты:
| GETDATE() | 返回当前的日期和时间 |
| DATEPART() | 返回日期/时间的单独部分 |
| DATEADD() | 在日期中添加或减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的时间 |
| CONVERT() | 用不同的格式显示日期/时间 |
SQL Дата типы данных
MySQL использует следующие типы данных , хранящихся в значения даты или даты / времени в базе данных:
- ДАТА - Формат: YYYY-MM-DD
- DATETIME - Формат: YYYY-MM-DD HH: MM: SS
- TIMESTAMP - Формат: YYYY-MM-DD HH: MM: SS
- ГОД - Формат: YYYY или YY
SQL Server использует следующие типы данных , хранящихся в значения даты или даты / времени в базе данных:
- ДАТА - Формат: YYYY-MM-DD
- DATETIME - Формат: YYYY-MM-DD HH: MM: SS
- SMALLDATETIME - Формат: YYYY-MM-DD HH: MM: SS
- TIMESTAMP - Формат: Единственный цифровой
Примечание: При создании новой таблицы в базе данных, вам необходимо выбрать тип данных для столбца!
Для всех доступных типов данных, пожалуйста , посетите наши полные типы данных Справочное руководство .
Дата обработки SQL
Если деталь не включает в себя время, то можно легко сравнить две даты!
Предположим, мы имеем следующие "Заказы" таблица:
| 1 | Geitost | 2008-11-11 |
| 2 | Camembert Pierrot | 2008-11-09 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
| 4 | Mascarpone Fabioli | 2008-10-29 |
Теперь мы хотим выбрать OrderDate как "2008-11-11" записей из таблицы.
Мы используем следующую ЗЕЬЕСТ:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
Результат устанавливается следующим образом:
| 1 | Geitost | 2008-11-11 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
Теперь предположим, что "Заказы" таблица выглядит следующим образом (обратите внимание на "OrderDate" колонка неполный рабочий день):
| 1 | Geitost | 2008-11-11 13:23:44 |
| 2 | Camembert Pierrot | 2008-11-09 15:45:21 |
| 3 | Mozzarella di Giovanni | 2008-11-11 11:12:01 |
| 4 | Mascarpone Fabioli | 2008-10-29 14:56:59 |
Если мы используем один и тот же оператор SELECT, как описано выше:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
Тогда мы получим результаты! Это связано с датой запроса не содержит временную часть.
Совет: Если вы хотите , чтобы сделать запросы простой и легкий в обслуживании, то , пожалуйста , не используйте компоненты времени ваши даты!
| %d | День месяца, число от 00 до 31. | ||
| %e | День месяца, число от 0 до 31. | ||
| %m | Месяц, число от 01 до 12. | ||
| %c | Месяц, число от 1 до 12. | ||
| %Y | Год, число, 4 цифры. | ||
| %y | Год, число, 2 цифры. | ||
| %j | День года, число от 001 до 366. | ||
| %H | Час, число от 00 до 23. | ||
| %k | Час, число от 0 до 23. | ||
| %h | Час, число от 01 до 12. | ||
| %I | Час, число от 01 до 12. | ||
| %l | Час, число от 1 до 12. | ||
| %i | Минуты, число от 00 до 59. | ||
| %S | Секунды, число от 00 до 59. | ||
| %s | Секунды, число от 00 до 59. | ||
| %w | День недели (0 - воскресенье, 1 - понедельник). | ||
| %W | Название дня недели по-английски. | ||
| %a | Сокращенный день недели по-английски. | ||
| %M | Название месяца по-английски. | ||
| %b | Сокращенный месяц по-английски. | ||
| %D | День месяца с английским суффиксом (1st, 2nd, 3rd и т.д.). | ||
| %r | Время, 12-часовой формат (hh:mm:ss [AP]M). | ||
| %T | Время, 24-часовой формат (hh:mm:ss). | ||
| %p | AM или PM. | ||
| %U | Неделя, где воскресенье считается первым днем недели, число от 00 до 53. | ||
| %u | Неделя, где понедельник считается первым днем недели, число от 00 до 53. | ||
| %V | Неделя, где воскресенье считается первым днем недели, число от 01 до 53.Используется с `%X'. | ||
| %v | Неделя, где понедельник считается первым днем недели, число от 01 до 53.Используется с `%x'. | ||
| %X | Год для недели, где воскресенье считается первым днем недели, число, 4 цифры.Используется с '%V'. | ||
| %x | Год для недели, где воскресенье считается первым днем недели, число, 4 разряда.Используется с '%v'. | ||
| %% | Символ `%'. |
| 1 | Дима | 2010-03-01 12:01:02 | 01.03.2010 |
| 2 | Петя | 2011-04-02 13:02:03 | 02.04.2011 |
| 3 | Вася | 2012-05-03 14:03:04 | 03.05.2012 |
code.mu
|
Содержат текущую дату |
Date() |
Возвращает элементы с текущей датой. Если текущая дата — 02.02.2012, отображаются элементы с датой 2 февраля 2012 г. |
|
Содержат вчерашнюю дату |
Date()-1 |
Возвращает элементы со вчерашней датой. Если текущая дата — 02.02.2012, отображаются элементы с датой 1 февраля 2012 г. |
|
Содержат завтрашнюю дату |
Date() + 1 |
Возвращает элементы с завтрашней датой. Если текущая дата — 02.02.2012, отображаются элементы с датой 3 февраля 2012 г. |
|
Содержат даты, которые приходятся на текущую неделю |
DatePart("ww"; [ДатаПродажи]) = DatePart("ww"; Date()) and Year([ДатаПродажи]) = Year(Date()) |
Возвращает элементы с датами, которые приходятся на текущую неделю. Неделя в Access начинается в воскресенье и заканчивается в субботу. |
|
Содержат даты, которые приходятся на предыдущую неделю |
Year([ДатаПродажи])* 53 + DatePart("ww"; [ДатаПродажи]) = Year(Date())* 53 + DatePart("ww"; Date()) - 1 |
Возвращает элементы с датами, которые приходятся на прошедшую неделю. Неделя в Access начинается в воскресенье и заканчивается в субботу. |
|
Содержат даты, которые приходятся на следующую неделю |
Year([ДатаПродажи])* 53+DatePart("ww"; [ДатаПродажи]) = Year(Date())* 53+DatePart("ww"; Date()) + 1 |
Возвращает элементы с датами, которые приходятся на следующую неделю. Неделя в Access начинается в воскресенье и заканчивается в субботу. |
|
Содержат дату, которая приходится на один из последних 7 дней |
Between Date() and Date()-6 |
Возвращает элементы с датами в течение последних 7 дней. Если текущая дата — 02.02.2012, отображаются элементы за период с 24 января 2012 г. по 2 февраля 2012 г. |
|
Содержат дату, которая приходится на текущий месяц |
Year([ДатаПродажи]) = Year(Now()) And Month([ДатаПродажи]) = Month(Now()) |
Возвращает элементы с датами текущего месяца. Если сегодняшняя дата — 02.02.2012, отображаются элементы с датами в феврале 2012 г. |
|
Содержат дату, которая приходится на предыдущий месяц |
Year([ДатаПродажи])* 12 + DatePart("m"; [ДатаПродажи]) = Year(Date())* 12 + DatePart("m"; Date()) - 1 |
Возвращает элементы с датами предыдущего месяца. Если сегодняшняя дата — 02.02.2012, отображаются элементы с датами января 2012 г. |
|
Содержат дату, которая приходится на следующий месяц |
Year([ДатаПродажи])* 12 + DatePart("m"; [ДатаПродажи]) = Year(Date())* 12 + DatePart("m"; Date()) + 1 |
Возвращает элементы с датами следующего месяца. Если сегодняшняя дата — 02.02.2012, отображаются элементы с датами в марте 2012 г. |
|
Содержат дату, которая попадает в диапазон последних 30 дней или 31 дня |
Between Date( ) And DateAdd("M", -1, Date( )) |
Возвращает элементы за месяц. Если текущая дата — 02.02.2012, отображаются элементы за период со 2 января 2012 г. по 2 февраля 2012 г. |
|
Содержат дату, которая приходится на текущий квартал |
Year([ДатаПродажи]) = Year(Now()) And DatePart("q";[ДатаПродажи]) = DatePart("q"; Now()) |
Возвращает элементы за текущий квартал. Если текущая дата — 02.02.2012, отображаются элементы за первый квартал 2012 г. |
|
Содержат дату, которая приходится на предыдущий квартал |
Year([ДатаПродажи])*4+DatePart("q";[ДатаПродажи]) = Year(Date())*4+DatePart("q";Date())- 1 |
Возвращает элементы за предыдущий квартал. Если текущая дата — 02.02.2012, отображаются элементы за последний квартал 2011 г. |
|
Содержат дату, которая приходится на следующий квартал |
Year([ДатаПродажи])*4+DatePart("q";[ДатаПродажи]) = Year(Date())*4+DatePart("q";Date())+1 |
Возвращает элементы за следующий квартал. Если текущая дата — 02.02.2012, отображаются элементы за второй квартал 2012 г. |
|
Содержат дату, которая приходится на текущий год |
Year([ДатаПродажи]) = Year(Date()) |
Возвращает элементы за текущий год. Если текущая дата — 02.02.2012, отображаются элементы за 2012 год. |
|
Содержат дату, которая приходится на предыдущий год |
Year([ДатаПродажи]) = Year(Date()) - 1 |
Возвращает элементы за предыдущий год. Если текущая дата — 02.02.2012, отображаются элементы за 2011 год. |
|
Содержат дату, которая приходится на следующий год |
Year([ДатаПродажи]) = Year(Date()) + 1 |
Возвращает элементы с датой в следующем году. Если текущая дата — 02.02.2012, отображаются элементы с датой в 2013 году. |
|
Содержат дату, которая приходится на период с 1 января до текущей даты (элементы с начала года до настоящего момента) |
Year([ДатаПродажи]) = Year(Date()) and Month([ДатаПродажи]) <= Month(Date()) and Day([ДатаПродажи]) <= Day (Date()) |
Возвращает элементы с датами между 1 января текущего года и текущей датой. Если текущая дата — 02.02.2012, отображаются элементы за период с 1 января 2012 г. по 2 февраля 2012 г. |
|
Содержат прошедшую дату |
< Date() |
Возвращает элементы с датами до текущей. |
|
Содержат будущую дату |
> Date() |
Возвращает элементы с датами после текущей. |
support.office.com
Использование Функций с Датами | ora-sql.ru
В примере на рисунке функция ADD_MONTHS добавляет один месяц к предоставленному значению даты “31-JAN-96” и возвращает "29-FEB-96".
Функция распознает 1996 год как високосный год и, поэтому, возвращается последний день февраля. Если Вы изменяете входное значение даты на "31-JAN-95", функция возвращает "28-FEB-95".
Например, выведите на экран номер сотрудника, дату приема, число месяцев занятости, дату приема плюс 6 месяцев, первую пятницу после даты приема и последний день месяца приема для всех сотрудников, которые были приняты на работу менее 150 месяцев назад.
SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE, ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date) FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 150;
Использование Функций ROUND и TRUNC с Датами
Предположим, что SYSDATE = '25-JUL-03':
Функции ROUND и TRUNC могут использоваться для числовых значений и дат. Когда они используются с датами, эти функции округляют или усекают дату к указанной модели формата. Поэтому, Вы можете округлять даты к самому близкому году или месяцу. Если модель формата является месяцем, даты 1-15 результируются к первому дню текущего месяца. Даты 16-31 результируются к первому дню следующего месяца. Если модель формата является годом, месяцы 1-6 дают в результате 1 января текущего года. Месяцы 7-12 результируются к 1 января следующего года.
Пример:
Сравнение дат приема всех сотрудников, которые начали работать в 1997. Вывод на экран номера сотрудника, даты приема и начала месяца, используя функции TRUNC и ROUND.
| SELECT employee_id, hire_date, ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH') FROM employees WHERE hire_date LIKE '%97'; |
Далее: Использование Функции NULLIF
ora-sql.ru
Справочник по операторам SQL-запросов
При создании отчетов Configuration Manager 2007 можно использовать много полезных инструкций Microsoft SQL Server, которые кратко описаны в этом разделе. Чтобы следить за ходом этого обсуждения, нужно иметь базовый уровень знаний инструкций запросов SQL и уметь писать запросы, такие как следующие:
SELECT Name, Comment, CollectionID
FROM v_Collection
WHERE Name LIKE 'All Windows%'
ORDER BY Name
Для получения сведений о том, как написать основные запросы, ищите раздел по основам составления запросов в электронной документации по Microsoft SQL Server 2005.
Статистические функции
Статистические функции (такие как SUM, AVG, COUNT, COUNT(*), MAX и MIN) генерируют итоговые значения в выходных наборах запроса. Статистическая функция (за исключением COUNT (*)) обрабатывает все выбранные значения в одном столбце, чтобы произвести одно значение результата. Статистические функции могут быть применены ко всем строками в представлении, к подмножеству представления, определенному предложением WHERE, или к одной или более группам строк в представлении. При применении статистической функции из каждого набора строк генерируется одно значение.
| Учтите, что значения NULL исключаются до вычисления результата статистической функции. Например, если есть 100 записей и для 8 из них есть значение NULL в столбце свойства, с которым производится вычисление, то результаты вычисления будут возвращены только для 92 записей. |
Пример использования статистической функции COUNT(*) отображен в следующем запросе (из встроенного отчета Подсчет клиентов для каждого сайта) и наборе результатов примера.
SELECT v_Site.SiteCode, v_Site.SiteName, v_Site.ReportingSiteCode,
Count(SMS_Installed_Sites0) AS 'Count'
FROM v_Site, v_RA_System_SMSInstalledSites InsSite
WHERE v_Site.SiteCode = InsSite.SMS_Installed_Sites0
GROUP BY SiteCode, SiteName, ReportingSiteCode
ORDER BY SiteCode
| ABC | Сайт ABC |
| 928 |
| 123 | Сайт 123 | ABC | 1010 |
Функции даты и времени
Многие встроенные отчеты используют функции даты и времени. Самые используемые общие функции — GETDATE, DATEADD, DATEDIFF и DATEPART.
GETDATE ()
Функция GETDATE выдает текущую дату и время во внутреннем формате SQL Server для значений datetime. GETDATE получает NULL параметр ().
В следующем примере результатами являются текущая системная дата и время:
SELECT GETDATE()
| 2005-05-29 10:10:03.001 |
DATEADD (datepart, number, date)
Функция DATEADD возвращает новое значение даты и времени datetime на основе добавления интервала к указанной дате.
Datepart — параметр, определяющий, в какую часть даты надо возвратить новое значение (например, год, месяц, день, час, минута и т.д), number — значение, используемое для увеличения datepart и date — начальная дата.
В следующем примере результатом является дата, спустя два дня после 29 мая 2005 г.:
SELECT DATEADD([day], 2, '2005-05-29 10:10:03.001')
| 2005-05-31 10:10:03.001 |
DATEDIFF (datepart, startdate, enddate)
Функция DATEDIFF возвращает число пересеченных границ даты и времени между двумя указанными датами.
Datepart — параметр, определяющий, в какую часть даты надо возвратить новое значение (например, год, месяц, день, час, минута и т.д), startdate — начальная дата,enddate — конечная дата.
В следующем примере результатом является число минут между первой и второй датами:
SELECT DATEDIFF (minute, '2005-05-29 10:10:03.001',
'2005-06-12 09:28:11.111')
| 20118 |
DATEPART (datepart, date)
Функция DATEPART возвращает целое число, представляющее указанную часть datepart из указанной даты.
Datepart — параметр, определяющий, в какую часть даты надо возвратить новое значение (например, год, месяц, день, час, минута и т.д), и date — указанная дата.
В следующем примере результатом является месяц в указанной дате:
SELECT DATEPART (month, '2005-05-29 10:10:03.001')
| 5 |
Сочетание функций даты и времени
В отчетах Configuration Manager обычно используется сочетание функций даты и времени.
В следующем примере результатом являются текущие дата и время (2005-05-29 10:10:03.001 в этом примере) минус 100 дней:
SELECT DATEADD([day], - 100, GETDATE())
| 2005-02-18 10:10:03.001 |
Пример запроса с использованием функций даты и времени
В следующем запросе результатом является общее число сообщений об изменении состояния за однодневный период. В этом запросе функции COUNT, GETDATE и DATEADD используются вместе с логическим оператором BETWEEN и предложениями GROUP BY и ORDER BY.
SELECT SiteCode, MessageID, COUNT(MessageID) AS [count],
GETDATE() AS [End Date]
FROM vStatusMessages
WHERE ([Time] BETWEEN DATEADD([day], -1, GETDATE()) AND GETDATE())
AND (MessageID BETWEEN '0' AND '10000')
GROUP BY SiteCode, MessageID
ORDER BY SiteCode, MessageID
| ABC | 500 | 190 | 2005-05-29 10:10:03.001 |
| ABC | 501 | 130 | 2005-05-29 10:10:03.001 |
| ABC | 502 | 190 | 2005-05-29 10:10:03.001 |
| ABC | 1105 | 85 | 2005-05-29 10:10:03.001 |
| ABC | 1106 | 5 | 2005-05-29 10:10:03.001 |
| … | … | … | … |
СОЕДИНЕНИЯ
Чтобы создавать эффективные отчеты в Configuration Manager, необходимо знать, как соединить различные представления, чтобы получить ожидаемые данные. Есть три типа соединений: внутреннее, внешнее и перекрестное. Кроме того, есть три типа внешних соединений: левое, правое и полное. Самосоединение использует любое из вышеупомянутых соединений, но соединяет записи из одного и того же представления.
Внутренние соединения
При внутреннем соединении записи из двух представлений объединяются и добавляются к результатам запроса только в том случае, если значения соединяемых полей соответствуют определенным указанным критериям. При внутреннем соединении с использованием ResourceID для соединения представлений v_R_System и v_GS_WORKSTATION_STATUS результатом должен быть список всех систем с их датами последнего сканирования оборудования.
SELECT v_R_System.Netbios_Name0 AS MachineName,
v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]
FROM v_R_System INNER JOIN v_GS_WORKSTATION_STATUS
ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
| Client1 | 2005-05-29 10:10:03.001 |
| Client3 | 2005-06-12 09:28:11.110 |
Внешние соединения
Внешнее соединение возвращает все строки из соединяемых представлений без учета соответствия строк между ними. Предложение ON дополняет данные, а не фильтрует их. Три типа внешних соединений (левое, правое и полное) указывают основной источник данных. Внешние соединения могут быть особенно полезными, когда в представлении есть значения NULL.
Левые внешние соединения
Когда используется левое внешнее соединение, чтобы объединить два представления, все строки в левом представлении включаются в результаты. В следующем запросе представления v_R_System и v_GS_WORKSTATION_STATUS соединяются с использованием левого внешнего соединения. Представление v_R_System указано в запросе при перечислении первым, что делает его левым представлением. Результат будет содержать список всех систем с их датами последнего сканирования оборудования. В отличие от внутреннего соединения, системы, на которых не было приведено сканирование оборудования, будут все же перечислены со значением NULL (как видно в наборе результата).
SELECT v_R_System.Netbios_Name0 AS MachineName,
v_GS_WORKSTATION_STATUS.LastHWScan AS [Last HW Scan]
FROM v_R_System LEFT OUTER JOIN v_GS_WORKSTATION_STATUS
ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
| Client1 | 2005-05-29 10:10:03.001 |
| Client2 | NULL |
| Client3 | 2005-06-12 09:28:11.110 |
Правые внешние соединения
Правое внешнее соединение принципиально является таким же, как и левое внешнее соединение, за исключением того, что все строки из правого представления включаются в результаты.
Полное внешнее соединение
Полное внешнее соединение извлекает все строки из обоих соединяемых представлений. Оно возвращает все парные строки, где условие соединения выполнено (истина), плюс непарные строки из каждого представления, сцепленные со строками NULL из другого представления. Обычно нет потребности использовать этот тип внешнего соединения.
Перекрестное соединение
Перекрестное соединение возвращает произведение двух представлений, а не сумму. Каждая строка левого представления сопоставляется с каждой строкой правого представления. Получается набор всех возможных комбинаций строк без какой-либо фильтрации. Однако, если добавить предложение WHERE, перекрестное соединение будет функционировать как внутреннее соединение, используя условие для фильтрации и выбора из всех возможных комбинаций строк тех, которые требуются.
Самосоединение
Самосоединение использует любой из вышеупомянутых типов соединения, но является представлением, которое соединяется само с собой. В схемах базы данных самосоединение называется рефлексивной связью.
Ключевая фраза NOT IN
Вложенные запросы с ключевой фразой NOT IN очень полезны при поиске сведений о наборе данных, которые не отвечают определенным критериям. В следующем примере запрос возвращает имя NetBIOS всех компьютеров, на которых НЕ установлен Notepad.exe. Сначала нужно создать запрос, который может обнаружить все компьютеры с установленным выбранным файлом, как показано ниже:
SELECT DISTINCT v_R_System.Netbios_Name0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
WHERE v_GS_SoftwareFile.FileName ='Notepad.exe'
После подтверждения, что первый запрос отображает все компьютеры, на которых установлен Notepad.exe, следующая инструкция вложенного запроса будет использовать ключевую фразу NOT IN, чтобы найти все имена компьютеров, на которых НЕ установлен файл Notepad.exe:
SELECT DISTINCT Netbios_Name0
FROM v_R_System
WHERE Netbios_Name0 NOT IN
(SELECT DISTINCT v_R_System.Netbios_Name0
FROM v_R_System INNER JOIN v_GS_SoftwareFile
ON (v_GS_SoftwareFile.ResourceID = v_R_System.ResourceId)
WHERE v_GS_SoftwareFile.FileName ='Notepad.exe')
ORDER by Netbios_Name0
См. также
systemscenter.ru
Between SQL: примеры, описание
При работе с реляционными СУБД, в которых данные хранятся в табличном виде, пользователи часто сталкиваются с задачей выборки значений, входящих (не входящих) в определенный диапазон. Язык SQL позволяет задать множество, которому должно (не должно) принадлежать значение различными вариантами – оператором In, оператором Like, комбинацией условий больше – меньше, а также оператором SQL Between. Описание и примеры в данной статье будут посвящены последнему варианту.
Оператор «Между» в SQL: синтаксис, ограничения
Дословно оператор between SQL так и переводится – «между». Его использование позволяет задать ограничение «От и До» к конкретному полю, и если очередное значение попадет в диапазон, то предикат примет значение «Истина», и значение попадет в итоговую выборку.
Синтаксис у оператора предельно простой:
Select * From table t1 Where t1.n between 0 and 7 |
Как видим, после ключевого слова between необходимо указать значение нижней границы диапазона, затем AND и значение верхней границы.
Перечислим, с какими типами данных может работать оператор between SQL:
- С числами – целыми и дробными.
- С датами.
- С текстом.
У данного оператора between SQL есть определенные особенности. Познакомимся с ними:
- При работе с числами и датами значения ограничений «От и До» включаются в выборку.
- Значение нижней границы диапазона должно быть меньше значения верхней границы, иначе не будет выведено ничего, ведь условие логически не верно. Особенно внимательным нужно быть, когда вместо конкретных значений в условие включаются переменные.
При работе с текстом значение верхней границы диапазона не будет включено в выборку, если оно не указано предельно точно. В следующих разделах рассмотрим данную особенность подробнее.
Выборка чисел и дат в определенном диапазоне
Подготовим таблицу с данными по менеджерам, работающим в организации. Таблица будет иметь следующую структуру:
Имя поля | Тип данных | Описание |
Код | Счетчик | Уникальный идентификатор сотрудника |
Фамилия | Текстовый | Фамилия сотрудника |
Имя | Текстовый | Имя сотрудника |
Отчество | Текстовый | Отчество сотрудника |
Пол | Текстовый | Пол сотрудника (М/Ж) |
Дата_приема | Дата/время | Дата приема сотрудника на работу |
Число_детей | Числовой | Количество детей у сотрудника |
Заполним таблицу следующими данными:
Код | Фамилия | Имя | Отчество | Пол | Дата_приема | Число_детей |
1 | Александрова | Ирина | Николаевна | Ж | 01.05.2014 | 1 |
2 | Боровой | Андрей | Степанович | М | 21.09.2013 | 0 |
3 | Виноградов | Сергей | Павлович | М | 15.06.1998 | 1 |
4 | Шумилин | Александр | Борисович | М | 25.12.2004 | 2 |
5 | Вишняков | Леонид | Александрович | М | 09.10.2007 | 0 |
6 | Тропников | Василий | Сергеевич | М | 12.01.2016 | 3 |
7 | Жемчугов | Никита | Васильевич | М | 11.01.2017 | 1 |
8 | Авдеева | Ника | Константиновна | Ж | 31.03.2001 | 2 |
9 | Яковлев | Леонид | Николаевич | М | 16.02.2009 | 0 |
Составим sql запрос between, который поможет нам выбрать всех сотрудников, имеющих 2 или 3 ребенка:
SELECT Менеджеры.* FROM Менеджеры WHERE Менеджеры.Число_детей between 2 and 3 |
Результатом станет три строки с данными по сотрудникам с фамилиями Шумилин, Тропников и Авдеева.
Теперь выберем сотрудников, принятых с 1 января 2005 года по 31 декабря 2016 года. Следует отметить, что разные СУБД по-разному позволяют записывать в условия даты. В большинстве случаев дату просто принудительно приводят к виду день-месяц-год (или как удобнее) и записывают в одинарные или двойные кавычки. В СУБД MS Access дату заключают в знак «#». Выполним пример как раз на ее основе:
SELECT Менеджеры.*, Менеджеры.Дата_приема FROM Менеджеры WHERE Менеджеры. Дата_приема Between #1/1/2005# And #31/12/2016# |
Результатом станут пять сотрудников, принятых на работу в указанный период включительно.
Далее посмотрим, как работает between SQL со строками.
Работа в between со строками
Очень частая задача, которую приходится решать при работе с фамилиями сотрудников, – это необходимость выбрать только тех, чьи фамилии начинаются на определенную букву. Попробуем и мы выполнить запрос и выбрать сотрудников, чьи фамилии начинаются на фамилии с А до В:
SELECT Менеджеры.* FROM Менеджеры WHERE Менеджеры.Фамилия between "А" and "В" ORDER BY 2 |
Результат следующий:
Код | Фамилия | Имя | Отчество | Пол | Дата_приема | Число_детей |
8 | Авдеева | Ника | Константиновна | Ж | 31.03.2001 | 2 |
1 | Александрова | Ирина | Николаевна | Ж | 01.05.2014 | 1 |
2 | Боровой | Андрей | Степанович | М | 21.09.2013 | 0 |
Как видим, двое сотрудников, имеющих фамилию на букву В, в список не попали. С чем это связано? Дело в том, каким именно образом оператор сравнивает строки неравной длины. Строка «В» короче строки «Виноградов» и дополняется пробелами. Но при сортировке по алфавиту пробелы окажутся опережающими символами, и фамилия в выборку не попадет. Разные СУБД по-разному предлагают решать данную проблему, но зачастую проще всего для надежности указывать следующую букву алфавита в диапазоне:
SELECT Менеджеры.* FROM Менеджеры WHERE Менеджеры.Фамилия between "А" and "Г" ORDER BY 2 |
При выполнении данного запроса результат нас полностью удовлетворит.
Такой нюанс существует только при работе с символьными данными, однако он показывает, что при работе даже с такими простыми операторами, как between, надо быть внимательными.
fb.ru
Функция EXTRACT - части даты или даты-времени
Функция EXTRACT извлекает отдельные части из даты или даты-времени.
См. также функции DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, DATE, DATE_FORMAT.
Синтаксис
SELECT EXTRACT(что_извлечь FROM дата) FROM имя_таблицы WHERE условиеВместо 'что_извлечь' можно написать, к примеру, DAY - тогда из даты будет извлечен день, или, к примеру, YEAR - тогда будет извлечен год. Если же я напишу так: YEAR_MONTH - то будет извлечен год и месяц (слитно, без разделителя).
Если вам нужно извлекать несколько частей не слитно, а используя разделитель - используйте DATE_FORMAT.
Форматы вывода, которые допустимо использовать (см. примеры для понимания):
| SECOND | секунды |
| MINUTE | минуты |
| HOUR | часы |
| DAY | дни |
| MONTH | месяцы |
| YEAR | года |
| MINUTE_SECOND | минуты и секунды слитно |
| HOUR_MINUTE | часы и минуты слитно |
| DAY_HOUR | дни и часы слитно |
| YEAR_MONTH | года и месяцы слитно |
| HOUR_SECOND | часы, минуты, секунды слитно |
| DAY_MINUTE | дни, часы, минуты слитно |
| DAY_SECOND | дни, часы, минуты, секунды слитно |
Примеры
Все примеры будут по этой таблице workers, если не сказано иное:
| 1 | Дима | 1988-03-01 |
| 2 | Петя | 1989-04-02 |
| 3 | Вася | 1990-05-03 |
Пример
В данном примере из даты извлекается день месяца:
SELECT *, EXTRACT(DAY FROM date) as day FROM workersSQL запрос выберет следующие строки:
| 1 | Дима | 1988-03-01 | 1 |
| 2 | Петя | 1989-04-02 | 2 |
| 3 | Вася | 1990-05-03 | 3 |
Пример
В данном примере из даты извлекается год:
SELECT *, EXTRACT(YEAR FROM date) as year FROM workersSQL запрос выберет следующие строки:
| 1 | Дима | 1988-03-01 | 1988 |
| 2 | Петя | 1989-04-02 | 1989 |
| 3 | Вася | 1990-05-03 | 1990 |
Пример
В данном примере из даты извлекается год и месяц (слитно):
SELECT *, EXTRACT(YEAR_MONTH FROM date) as yearmonth FROM workersSQL запрос выберет следующие строки:
| 1 | Дима | 1988-03-01 | 198803 |
| 2 | Петя | 1989-04-02 | 198904 |
| 3 | Вася | 1990-05-03 | 199005 |
code.mu
