Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 22: обновление данных
2.3.2. Пример 22: обновление данных
Задача 2.3.2.a{190}: у выдачи с идентификатором 99 изменить дату возврата на текущую и отметить, что книга возвращена.
Задача 2.3.2.b{191}: изменить ожидаемую дату возврата для всех книг, которые читатель с идентификатором 2 взял в библиотеке 25-го января 2016-го года, на «плюс два месяца» (т.е. читатель будет читать их на два месяца дольше, чем планировал).
Ожидаемый результат 2.3.2.a: строка с первичным ключом, равным 99, примет примерно такой вид (у вас дата в поле sb_finish будет другой).
sb_id |
|
sb_subscriber |
|
sb_book |
sb_start |
sb_finish |
sb_is_active |
|||||
99 |
|
4 |
|
|
4 |
|
2015-10-08 |
2016-01-06 |
N |
|||
|
|
Ожидаемый результат 2.3.2.b: следующие строки примут такой вид. |
||||||||||
|
|
|
|
|
|
|
|
|||||
sb_id |
sb_subscriber |
|
sb_book |
sb_start |
sb_finish |
sb_is_active |
|
|||||
102 |
2 |
|
1 |
|
2016-01-25 |
2016-06-30 |
N |
|
|
|||
103 |
2 |
|
3 |
|
2016-01-25 |
2016-06-30 |
N |
|
|
|||
104 |
2 |
|
5 |
|
2016-01-25 |
2016-06-30 |
N |
|
|
|||
Решение 2.3.2.a{190}:
Значение текущей даты можно получить на стороне приложения и передать в СУБД, тогда решение будет предельно простым (см. вариант 1), но текущую дату можно получить и на стороне СУБД (см. вариант 2), что не сильно усложняет запрос.
MySQL Решение 2.3.2.a
1-- Вариант 1: прямая подстановка текущей даты в запрос.
2UPDATE `subscriptions`
3 |
|
SET |
`sb_finish` = '2016-01-06', |
4 |
|
|
`sb_is_active` = 'N' |
5 |
|
WHERE |
`sb_id` = 99 |
6 |
|
|
|
7-- Вариант 2: получение текущей даты на стороне СУБД.
8UPDATE `subscriptions`
9 |
|
SET |
`sb_finish` = CURDATE(), |
10 |
|
|
`sb_is_active` = 'N' |
11 |
|
WHERE |
`sb_id` = 99 |
MS SQL Решение 2.3.2.a
1-- Вариант 1: прямая подстановка текущей даты в запрос.
2UPDATE [subscriptions]
3 |
|
SET |
[sb_finish] = CAST(N'2016-01-06' AS DATE), |
4 |
|
|
[sb_is_active] = N'N' |
5 |
|
WHERE |
[sb_id] = 99 |
6 |
|
|
|
7-- Вариант 2: получение текущей даты на стороне СУБД.
8UPDATE [subscriptions]
9 |
|
SET |
[sb_finish] = CONVERT(date, GETDATE()), |
10[sb_is_active] = N'N'
11WHERE [sb_id] = 99
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 190/545
Пример 22: обновление данных
Oracle Решение 2.3.2.a
1-- Вариант 1: прямая подстановка текущей даты в запрос.
2UPDATE "subscriptions"
3 |
|
SET |
"sb_finish" = TO_DATE('2016-01-06', 'YYYY-MM-DD'), |
4"sb_is_active" = 'N'
5WHERE "sb_id" = 99
6
7-- Вариант 2: получение текущей даты на стороне СУБД.
8UPDATE "subscriptions"
9 |
SET |
"sb_finish" = TRUNC(SYSDATE), |
10"sb_is_active" = 'N'
11WHERE "sb_id" = 99
Применение функции TRUNC в 9-й строке запроса нужно для получения из полного формата представления даты-времени только даты.
Внимательно следите за тем, чтобы ваши запросы на обновление данных содержали условие (в этой задаче — строки 5 и 11 всех трёх запросов). UPDATE без условия обновит все записи в таблице, т.е. вы «покалечите» данные.
Существует ещё две очень распространённых ошибки при решении любой задачи, связанной с понятием «текущая дата»:
1)Если пользователь и сервер с СУБД находятся в разных часовых поясах, каждые сутки возникает отрезок времени, когда «текущая дата» у пользователя и на сервере отличается. Это следует учитывать, внося соответствующие поправки либо на уровне отображения данных пользователю, либо на уровне хранения данных.
2)Если дата хранит в себе не только информацию о годе, месяце и дне, но и о часах, минутах, секундах (дробных долях секунд), операции сравнения могут дать неожиданный результат (например, что с 01.01.2011 по 01.01.2012 не прошёл год, если в базе данных первая дата представлена как «2011-01-01 11:23:17» и «сегодня» представлено как «2012-01-01 15:28:27» — до прохождения года остаётся чуть больше четырёх часов).
Универсального решения этих ситуаций не существует. Требуется комплексный подход, начиная с выбора оптимального формата хранения, заканчивая реализацией и глубоким тестированием алгоритмов обработки данных.
Решение 2.3.2.b{190}.
В отличие от предыдущей задачи, здесь крайне нерационален вариант с получением новой даты на стороне приложения и подстановкой готового значения в запрос (мы ведь не знаем, сколько записей нам придётся обработать, и у разных записей вполне могут быть разные исходные значения обновляемой даты). Таким образом, придётся добавлять два месяца к дате средствами СУБД.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 191/545
Пример 22: обновление данных
Типичная ошибка: разобрать дату на строковые фрагменты, получить числовое представление месяца, добавить к нему нужное «смещение», преобразовать назад в строку, и «собрать» новую дату. Представьте, что к 15-му декабря 2011-го года надо добавить шесть месяцев: 2011-12-15 2011-18-15. Получился 18-й месяц, что несколько нелогично. С отрицательными смещениями получается ещё более «забавная» картина.
Вывод: операции с добавлением или вычитанием временных интервалов нужно производить с помощью специальных средств, умеющих учитывать годы, месяцы, дни, часы, минуты, секунды и т.д.
|
MySQL |
|
Решение 2.3.2.b |
||||
|
1 |
|
UPDATE |
`subscriptions` |
|
||
|
2 |
|
SET |
|
`sb_finish` = DATE_ADD(`sb_finish`, INTERVAL 2 MONTH) |
|
|
|
3 |
|
WHERE |
`sb_subscriber` = 2 |
|
||
|
4 |
|
|
|
|
AND `sb_start` = '2016-01-25'; |
|
|
|
|
|||||
|
MS SQL |
|
Решение 2.3.2.b |
||||
|
1 |
|
UPDATE |
[subscriptions] |
|
||
|
2 |
|
SET |
|
[sb_finish] = DATEADD(month, 2, [sb_finish]) |
|
|
|
3 |
|
WHERE |
[sb_subscriber] = 2 |
|
||
|
4 |
|
|
|
|
AND [sb_start] = CONVERT(date, '2016-01-25'); |
|
|
|
|
|
|
|||
|
Oracle |
|
|
Решение 2.3.2.b |
|||
|
1 |
|
UPDATE |
"subscriptions" |
|
||
|
2 |
|
SET |
|
"sb_finish" = ADD_MONTHS("sb_finish", 2) |
|
|
|
|
|
|
|
|
|
|
3WHERE "sb_subscriber" = 2
4AND "sb_start" = TO_DATE('2016-01-25', 'YYYY-MM-DD');
Во всех трёх СУБД решения достаточно просты и реализуются идентичным образом, за исключением специфики функций приращения даты.
Для повышения надёжности операций обновления рекомендуется после их выполнения получать на уровне приложения информацию о том, какое количество рядов было затронуто операцией. Если полученное число не совпадает с ожидаемым, где-то произошла ошибка.
Да, мы не всегда можем заранее знать, сколько записей затронет обновление, но всё же существуют случаи, когда это известно (например, библиотекарь отметил чек-боксами некие три выдачи и нажал «Книги возвращены», значит, обновление должно затронуть три записи).
Задание 2.3.2.TSK.A: отметить все выдачи с идентификаторами ≤50 как возвращённые.
Задание 2.2.2.TSK.B: для всех выдач, произведённых до 1-го января 2012-го года, уменьшить значение дня выдачи на 3.
Задание 2.2.2.TSK.C: отметить как невозвращённые все выдачи, полученные читателем с идентификатором 2.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 192/545
Пример 23: удаление данных
2.3.3. Пример 23: удаление данных
Задача 2.3.3.a{193}: удалить информацию о том, что читатель с идентификатором 4 взял 15-го января 2016-го года в библиотеке книгу с идентификатором 3.
Задача 2.3.3.b{193}: удалить информацию обо всех посещениях библиотеки читателем с идентификатором 3 по воскресеньям.
Ожидаемый результат 2.3.3.a: следующая запись должна быть удалена.
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
101 |
4 |
3 |
2016-01-15 |
2016-01-30 |
N |
|
Ожидаемый результат 2.3.3.b: следующие записи должны быть удалены. |
||||
|
|
|
|
|
|
sb_id |
sb_subscriber |
sb_book |
sb_start |
sb_finish |
sb_is_active |
62 |
3 |
5 |
2014-08-03 |
2014-10-03 |
Y |
86 |
3 |
1 |
2014-08-03 |
2014-09-03 |
Y |
Решение 2.3.3.a{193}.
В данном случае решение тривиально и одинаково для всех трёх СУБД за исключением синтаксиса формирования значения поля sb_start.
MySQL Решение 2.3.3.a
1 DELETE FROM `subscriptions`
2 WHERE `sb_subscriber` = 4
3AND `sb_start` = '2016-01-15'
4AND `sb_book` = 3
MS SQL Решение 2.3.3.a
1 DELETE FROM [subscriptions]
2 WHERE [sb_subscriber] = 4
3AND [sb_start] = CONVERT(date, '2016-01-15')
4AND [sb_book] = 3
Oracle Решение 2.3.3.a
1DELETE FROM "subscriptions"
2WHERE "sb_subscriber" = 4
3AND "sb_start" = TO_DATE('2016-01-15', 'YYYY-MM-DD')
4AND "sb_book" = 3
Внимательно следите за тем, чтобы ваши запросы на удаление данных содержали условие (в этой задаче — строки 2-4 всех трёх запросов). DELETE без условия удалит все записи в таблице, т.е. вы потеряете все данные.
Решение 2.3.3.b{193}.
В решении{39} задачи 2.1.7.b{39} мы подробно рассматривали, почему в условиях, затрагивающих дату, выгоднее использовать диапазоны, а не извлекать фрагменты даты, и оговаривали, что могут быть исключения, при которых диапазонами обойтись не получится. Данная задача как раз и является таким исключением: нам
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 193/545
Пример 23: удаление данных
придётся получать информацию о номере дня недели на основе исходного значения даты.
MySQL Решение 2.3.3.b
1 DELETE FROM `subscriptions`
2 WHERE `sb_subscriber` = 3
3 AND DAYOFWEEK(`sb_start`) = 1
Обратите внимание: функция DAYOFWEEK в MySQL возвращает номер дня,
начиная с воскресенья (1) и заканчивая субботой (7).
MS SQL Решение 2.3.3.b
1 DELETE FROM [subscriptions]
2 WHERE [sb_subscriber] = 3
3AND DATEPART(weekday, [sb_start]) = 1
ВMS SQL Server функция DATEPART также нумерует дни недели, начиная с воскресенья.
Oracle Решение 2.3.3.b
1DELETE FROM "subscriptions"
2WHERE "sb_subscriber" = 3
3AND TO_CHAR("sb_start", 'D') = 1
Oracle ведёт себя аналогичным образом: получая номер дня недели, функция TO_CHAR начинает нумерацию с воскресенья.
Логика поведения функций, определяющих номер дня недели, может различаться в разных СУБД и зависеть от настроек СУБД, операционной системы и иных факторов. Не полагайтесь на то, что такие функции всегда и везде будут работать так, как вы привыкли.
Для повышения надёжности операций удаления рекомендуется после их выполнения получать на уровне приложения информацию о том, какое количество рядов было затронуто операцией. Если полученное число не совпадает с ожидаемым, где-то произошла ошибка.
Да, мы не всегда можем заранее знать, сколько записей затронет удаление, но всё же существуют случаи, когда это известно (например, библиотекарь отметил чек-боксами некие три выдачи и нажал «Удалить», значит, удаление должно затронуть три записи).
Задание 2.3.3.TSK.A: удалить информацию обо всех выдачах читателям книги с идентификатором 1.
Задание 2.2.3.TSK.B: удалить все книги, относящиеся к жанру «Классика».
Задание 2.2.3.TSK.C: удалить информацию обо всех выдачах книг, произведённых после 20-го числа любого месяца любого года.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 194/545