alan_beaulieu-learning_sql-ru (865932), страница 27
Текст из файла (страница 27)
е. year_month),поэтому 9 лет 11 месяцев были преобразованы в 119 месяцев.Пользователи Oracle Database могут для данного примера при менить функцию add_months():UPDATE employeeSET birth_date = ADD_MONTHS(birth_date, 119)WHERE emp_id = 4789;Иногда требуется добавить интервал времени к определенной дате, приэтом известна конечная дата выполнения, но неизвестно, сколько днейосталось до этой даты. Например, клиент банка регистрируется в сете вой банковской системе и планирует перевод на конец месяца.
Вместотого чтобы писать какой то код, определяющий текущий месяц и вы числяющий количество дней в этом месяце, можно вызвать функциюlast_day() (последний день), которая сделает всю работу (и MySQL,и Oracle Database включают функцию last_day(); в SQL Server сопос тавимой функции нет). Если клиент запрашивает перевод 25 марта2005 года, последний день марта можно найти следующим образом:mysql> SELECT LAST_DAY('20050325');++| LAST_DAY('20050325') |++| 20050331|++1 row in set (0.04 sec)Временные данные149Независимо от того, предоставляется ли значение типа date или datetime, функция last_day() всегда возвращает значение типа date.
Хотя,может быть, и не заметно, что эта функция существенно экономит вре мя, но если требуется найти последний день февраля и для этого выяс нить, високосный этот год или нет, логика вычисления может быть до вольно сложной.Еще одна временная функция, возвращающая дату, преобразует зна чение типа datetime из одного временного пояса в другой. Для этогоMySQL включает функцию convert_tz(), а Oracle Database – функциюnew_time() (новое время). Например, если требуется преобразовать те кущее местное время в UTC, можно сделать следующее:mysql> SELECT CURRENT_TIMESTAMP( ) current_est,> CONVERT_TZ(CURRENT_TIMESTAMP( ), 'US/Eastern', 'UTC') current_utc;+++| current_est| current_utc|+++| 20050418 21:23:25 | 20050419 01:23:25 |+++1 row in set (0.50 sec)Эта функция очень полезна при получении дат из других часовых поя сов, отличных от того, в котором хранится база данных.Временные функции, возвращающие строкиБольшинство временных функций, возвращающих строковые значе ния, используются для получения отдельной части даты или времени.Например, MySQL включает функцию dayname() (название дня), опре деляющую, на какой день недели приходится определенная дата:mysql> SELECT DAYNAME('20050322');++| DAYNAME('20050322') |++| Tuesday|++1 row in set (0.12 sec)В MySQL много таких функций, предназначенных для извлечения ин формации из значений дат, но я рекомендую пользоваться функциейextract() (извлечь), поскольку проще запомнить несколько разновид ностей одной функции, чем десяток разных функций.
Кроме того, extract() является частью стандарта SQL:2003 и была реализована нетолько в MySQL, но и в Oracle Database.Для определения интересующего элемента даты функция extract() ис пользует те же типы интервалов, что и функция date_add() (см.табл. 7.5). Например, если из значения типа datetime требуется из влечь только год, можно поступить так:150Глава 7.
Создание, преобразование и работа с даннымиmysql> SELECT EXTRACT(YEAR FROM '20050322 22:19:05');++| EXTRACT(YEAR FROM '20050322 22:19:05') |++|2005 |++1 row in set (0.02 sec)В SQL Server нет реализации extract(), но есть функция datepart() (часть даты). Вот как можно извлечь год из значения datetime с помощью datepart():SELECT DATEPART(YEAR, GETDATE( ))Временные функции, возвращающие числаРанее в этой главе была представлена функция, используемая для до бавления заданного интервала к значению даты и формирующая, та ким образом, другую дату.
Другая распространенная операция при ра боте с датами – определение количества интервалов (дней, недель, лет)между двумя датами. MySQL включает предназначенную для этогофункцию datediff(), которая возвращает количество полных дней ме жду двумя датами. Например, чтобы узнать, сколько дней будут про должаться школьные каникулы этим летом, можно сделать так:mysql> SELECT DATEDIFF('20050905', '20050622');++| DATEDIFF('20050905', '20050622') |++|75 |++1 row in set (0.00 sec)Итак, до благополучного возвращения детей в школу мне предстоит75 дневная пытка ядовитым плющом, комариными укусами и разби тыми коленками. Функция datediff() в своих аргументах не учитыва ет время дня.
Даже если включить время, задавая для первой даты од ну секунду до полуночи и для второй даты одну секунду после полуно чи, эти данные никак не отразятся на вычислениях:mysql> SELECT DATEDIFF('20050905 23:59:59', '20050622 00:00:01');++| DATEDIFF('20050905 23:59:59', '20050622 00:00:01') |++|75 |++1 row in set (0.00 sec)Если переставить аргументы, поместив первой более раннюю дату,datediff() вернет отрицательное число:mysql> SELECT DATEDIFF('20050622', '20050905');Функции преобразования151++| DATEDIFF('20050622', '20050905') |++|75 |++В SQL Server тоже есть функция datediff(), но более гибкая, чемв MySQL.
В ней можно задавать тип интервала (т. е. год, месяц,день, час), а не только вычислять количество дней между двумядатами. Вот как был бы выполнен предыдущий пример для SQLServer:SELECT DATEDIFF(DAY, '20050622','20050905')Oracle Database позволяет определять число дней между двумядатами простым вычитанием одной даты из другой.Функции преобразованияРанее в этой главе было показано, как использовать функцию cast()для преобразования строки в значение типа datetime. Хотя у всех сер веров БД есть собственные функции преобразования данных из одноготипа в другой, я рекомендую использовать функцию cast(), включен ную в стандарт SQL:2003 и реализованную в MySQL, Oracle Databaseи Microsoft SQL Server.Применяя функцию cast(), необходимо задать значение или выраже ние, ключевое слово as и тип, в который должно быть преобразованозначение.
Вот пример преобразования строки в целое:mysql> SELECT CAST('1456328' AS SIGNED INTEGER);++| CAST('1456328' AS SIGNED INTEGER) |++|1456328 |++1 row in set (0.01 sec)При преобразовании строки в число функция cast() попытается преоб разовать всю строку слева направо. Если в строке встретиться любойнечисловой символ, преобразование будет остановлено без формирова ния ошибки.
Рассмотрим следующий пример:mysql> SELECT CAST('999ABC111' AS UNSIGNED INTEGER);++| CAST('999ABC111' AS UNSIGNED INTEGER) |++|999 |++1 row in set (0.00 sec)152Глава 7. Создание, преобразование и работа с даннымиВ данном случае преобразуются первые три цифры в строке, все ос тальные символы строки отбрасываются. В результате получаем зна чение 999.Для преобразования строки в значение типа date, time или datetime по надобится придерживаться форматов по умолчанию для каждого типа,поскольку передать строку формата в функцию cast() невозможно. Ес ли формат строки даты не соответствует применяемому по умолчанию(т.
е. YYYY MM DD HH:MI:SS для типов datetime), придется прибег нуть к другой функции, например к функции MySQL str_to_date(),описанной ранее в этой главе.УпражненияЭти упражнения позволяют проверить понимание читателем некото рых встроенных функций, упомянутых в данной главе.
Ответы приве дены в приложении С.7.1Напишите запрос, возвращающий с 17 го по 25 й символы строки'Please find the substring in this string' (Пожалуйста, найдите под строку в этой строке).7.2Напишите запрос, возвращающий абсолютную величину и знак (1, 0или 1) числа –25,768 23. Также возвратите число, округленное до сотых.7.3Напишите запрос, возвращающий только значение месяца текущейдаты.Группировка и агрегатыОбычно данные хранятся с самым низким уровнем детализации, кото рый может понадобиться какому либо пользователю базы данных. ЕслиЧаку (Chuck) для бухгалтерского учета требуется посмотреть операцииодного клиента, в БД должна быть таблица, хранящая операции отдель ного клиента. Однако это не означает, что все пользователи должны ра ботать с данными в том виде, в каком они хранятся в БД.
Основное вни мание уделено группировке и агрегированию данных, которые обеспе чивают пользователям возможность работать с данными на более вы соком уровне детализации, чем тот, с которым они хранятся в БД.Принципы группировкиИногда необходимо выявить в данных некоторые тенденции, что по требует от сервера некоторой подготовки данных, прежде чем можнобудет получить искомые результаты. Например, вы отвечаете за опе рации в банке и хотели бы выяснить, сколько счетов открывает каж дый операционист банка. Можно было бы создать простой запрос дляпросмотра необработанных данных:mysql> SELECT open_emp_id> FROM account;++| open_emp_id |++|1 ||1 ||1 ||1 ||1 ||1 ||1 ||1 |154Глава 8. Группировка и агрегаты|10 ||10 ||10 ||10 ||10 ||10 ||10 ||13 ||13 ||13 ||16 ||16 ||16 ||16 ||16 ||16 |++24 rows in set (0.01 sec)В таблице account всего 24 строки, поэтому относительно просто уви деть, что счета открывались четырьмя сотрудниками и что сотрудникс ID 16 открыл шесть счетов.
Но для банка с десятками сотрудникови тысячами открываемых счетов этот подход оказался бы очень уто мительным и подверженным ошибкам.Вместо этого можно с помощью блока group by (группировать по) попро сить сервер БД сгруппировать данные. Вот тот же запрос, но с примене нием блока group by для группировки данных о счетах по ID сотрудника:mysql> SELECT open_emp_id> FROM account> GROUP BY open_emp_id;++| open_emp_id |++|1 ||10 ||13 ||16 |++4 rows in set (0.00 sec)Результирующий набор содержит по одной строке для каждого от дельного значения столбца open_emp_id, что в результате дает четыре,а не 24 строки.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
