PART 2 - http://habrahabr.ru/post/255523/
PART 3 - habrahabr.ru/post/255825
PART 4 - habrahabr.ru/post/256045
Часть пятая — habrahabr.ru/post/256169
В данной части мы рассмотрим
Здесь мы в общих чертах рассмотрим работу с операторами модификации данных:
- INSERT – вставка новых данных
- UPDATE – обновление данных
- DELETE – удаление данных
- SELECT … INTO … – сохранить результат запроса в новой таблице
- MERGE – слияние данных
- TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы
Операции модификации данных очень сильно связаны с конструкциями оператора SELECT, т.к. по сути выборка модифицируемых данных идет при помощи них. Поэтому для понимания данного материала, важное место имеет уверенное владение конструкциями оператора SELECT.
Данная часть, как я и говорил, будет больше обзорная. Здесь я буду описывать только те основные формы операторов модификации данных, которыми я сам регулярно пользуюсь. Поэтому на полноту изложения рассчитывать не стоит, здесь будут показан только необходимый минимум, который новички могут использовать как направление для более глубокого изучения. За более подробной информацией по каждому оператору обращайтесь в MSDN. Хотя кому-то возможно и в таком объеме информации будет вполне достаточно.
Т.к. прямая модификация информации в РБД требует от человека большой ответственности, а также потому что пользователи обычно модифицируют информацию БД посредством разных АРМ, и не имеют полного доступа к БД, то данная часть больше посвящается начинающим ИТ-специалистам, и я буду здесь очень краток. Но конечно, если вы смогли освоить оператор SELECT, то думаю, и операторы модификации вам будут под силу, т.к. после оператора SELECT здесь нет ничего сверхсложного, и по большей части должно восприниматься на интуитивном уровне. Но порой сложность представляют не сами операторы модификации, а то что они должны выполняться группами, в рамках одной транзакции, т.е. когда дополнительно нужно учитывать целостность данных. В любом случае можете почитать и попытаться проделать примеры в ознакомительных целях, к тому же в итоге вы сможете получить более детальную базу, на которой можно будет отработать те или иные конструкции оператора SELECT.
Проведем изменения в структуре нашей БД
Давайте проведем небольшое обновление структуры и данных таблицы Employees:
А также для демонстрационных целей расширим схему нашей БД, а за одно повторим DDL. Назначения таблиц и полей указаны в комментариях:
Вот такой полигон мы должны были получить в итоге:
Кстати, потом этот полигон (когда он будет наполнен данными) вы и можете использовать для того чтобы опробовать на нем разнообразные запросы – здесь можно опробовать и разнообразные JOIN-соединения, и UNION-объединения, и группировки с агрегированием данных.
INSERT – вставка новых данных
Данный оператор имеет 2 основные формы:
- INSERT INTO таблица(перечень_полей) VALUES(перечень_значений) – вставка в таблицу новой строки значения полей которой формируются из перечисленных значений
- INSERT INTO таблица(перечень_полей) SELECT перечень_значений FROM … – вставка в таблицу новых строк, значения которых формируются из значений строк возвращенных запросом.
В диалекте MS SQL слово INTO можно отпускать, что мне очень нравится и я этим всегда пользуюсь.
К тому же стоит отметить, что первая форма в диалекте MS SQL с версии 2008, позволяет вставить в таблицу сразу несколько строк:
INSERT – форма 1. Переходим сразу к практике
Наполним таблицу EmployeesSalaryHistory предоставленными нам данными:
Таким образом мы вставили в таблицу EmployeesSalaryHistory 11 новых записей.
| EmployeeID | DateFrom | DateTo | Salary |
|---|---|---|---|
| 1000 | 2013-11-01 | 2014-05-31 | 4000.00 |
| 1000 | 2014-06-01 | 2014-12-30 | 4500.00 |
| 1000 | 2015-01-01 | NULL | 5000.00 |
| 1001 | 2013-11-01 | 2014-06-30 | 1300.00 |
| 1001 | 2014-07-01 | 2014-09-30 | 1400.00 |
| 1001 | 2014-10-01 | NULL | 1500.00 |
| 1002 | 2014-01-01 | NULL | 2500.00 |
| 1003 | 2014-06-01 | NULL | 2000.00 |
| 1004 | 2014-07-01 | 2015-01-31 | 1400.00 |
| 1004 | 2015-02-01 | 2015-01-31 | 1500.00 |
| 1005 | 2015-01-01 | NULL | 2000.00 |
Хоть мы в этом случае могли и не указывать перечень полей, т.к. мы вставляем данные всех полей и в таком же виде, как они перечислены в таблице, т.е. мы могли бы написать:
Но я бы не рекомендовал использовать такой подход, особенно если данный запрос будет использоваться регулярно, например, вызываясь из какого-то АРМ. Опять же это чревато тем, что структура таблицы может изменяться, в нее могут быть добавлены новые поля, или же последовательность полей может быть изменена, что еще опасней, т.к. это может привести к появлению логических ошибок во вставленных данных. Поэтому лучше лишний раз не полениться и перечислить явно все поля, в которые вы хотите вставить значение.
Несколько заметок про INSERT:
- Порядок перечисления полей не имеет значения, вы можете написать и (EmployeeID,DateFrom,DateTo,Salary) и (DateFrom,DateTo, EmployeeID,Salary). Здесь важно только то, чтобы он совпадал с порядком значений, которые вы перечисляете в скобках после ключевого слова VALUES.
- Так же важно, чтобы при вставке были заданы значения для всех обязательных полей, которые помечены в таблице как NOT NULL.
- Можно не указывать поля у которых была указана опция IDENTITY или же поля у которых было задано значение по умолчанию при помощи DEFAULT, т.к. в качестве их значения подставится либо значение из счетчика, либо значение, указанное по умолчанию. Такие вставки мы уже делали в первой части.
- В случаях, когда значение поля со счетчиком нужно задать явно используйте опцию IDENTITY_INSERT.
В предыдущих частях мы периодически использовали опцию IDENTITY_INSERT. Давайте и здесь воспользуемся данной опцией для создания строк в таблице BonusTypes, у которой поле ID указано с опцией IDENTITY:
Давайте вставим информацию по начислению сотрудникам ЗП, любезно предоставленную нам бухгалтером:
Думаю, приводить содержимое таблицы уже нет смысла.
INSERT – форма 2
Данная форма позволяет вставить в таблицу данные полученные запросом.
Для демонстрации наполним таблицу с начислениями бонусов одним большим запросом:
В таблицу EmployeesBonus должно было вставиться 50 записей.
Результат каждого запроса объединенных конструкциями UNION ALL вы можете проанализировать самостоятельно. Если вы хорошо изучили базовые конструкции, то вам должно быть все понятно, кроме возможно конструкции с VALUES (конструктор табличных значений), которая появилась с MS SQL 2008.
Пара слов про конструкцию VALUES
В случае необходимости, данную конструкцию можно заменить, аналогичным запросом, написанным через UNION ALL:
Думаю, комментарии излишни и вам не составит большого труда разобраться с этим самостоятельно.
Так что, идем дальше.
INSERT + CTE-выражения
Совместно с INSERT можно применять CTE выражения. Для примера перепишем тот же запрос перенеся все подзапросы в блок WITH.
Для начала полностью очистим таблицу EmployeesBonus при помощи операции TRUNCATE TABLE:
Теперь перепишем запрос вынеся запросы в блок WITH:
Как видим вынос больших подзапросов в блок WITH упростил основной запрос – сделал его более понятным.
UPDATE – обновление данных
Данный оператор в MS SQL имеет 2 формы:
- UPDATE таблица SET … WHERE условие_выборки – обновлении строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут обновлены все строки. Это можно сказать классическая форма оператора UPDATE.
- UPDATE псевдоним SET … FROM … – обновление данных таблицы участвующей в предложении FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.
Давайте при помощи первой формы приведем даты приема каждого сотрудника в порядок. Выполним 6 отдельных операций UPDATE:
Вторую форму, где применялся псевдоним, мы уже тоже успели использовать в первой части, когда обновляли поля PositionID и DepartmentID, на значения возвращаемые подзапросами:
Сейчас конечно данный и следующий запрос не сработают, т.к. поля Position и Department мы удалили из таблицы Employees. Вот так можно было бы представить этот запрос при помощи операций соединений:
Надеюсь суть обновления здесь понятна, тут обновляться будут строки таблицы Employees.
Сначала вы можете сделать выборку, чтобы посмотреть какие данные будут обновлены и на какие значения:
А потом переписать это в UPDATE:
Эх, не могу я так, все-таки давайте посмотрим, как это работает наглядно.
Для этого опять вспомним DDL и временно создадим поля Position и Department в таблице Employees:
Зальем в них данные, предварительно посмотрев при помощи SELECT, что получится:
Теперь перепишем и выполним обновление:
Посмотрите, что получилось (должны были появиться значения в 2-х полях – Position и Department, находящиеся в конце таблицы):
Теперь и этот запрос:
И этот:
Отработают успешно.
Не забудьте только предварительно посмотреть (это очень полезная привычка):
И конечно же можете использовать здесь условие WHERE:
Все, убедились, что все работает. Если хотите, то можете снова удалить поля Position и Department.
Вторую форму можно так же использовать с подзапросом:
В данном случае подзапрос должен возвращать в явном виде строки таблицы Employees, которые будут обновлены. В подзапросе нельзя использовать группировки или предложения DISTINCT, т.к. в этом случае мы не получим явных строк таблицы Employees. И соответственно все обновляемые поля должны содержаться в предложении SELECT, если конечно вы не указали «SELECT *».
Так же с UPDATE вы можете использовать CTE-выражения. Для примера перенесем наш подзапрос в блок WITH:
Идем дальше.
DELETE – удаление данных
Принцип работы DELETE похож на принцип работы UPDATE, и так же в MS SQL можно использовать 2 формы:
- DELETE таблица WHERE условие_выборки – удаление строк таблицы, для которых выполняется условие_выборки. Если предложение WHERE не указано, то будут удалены все строки. Это можно сказать классическая форма оператора DELETE (только в некоторых СУБД нужно писать DELETE FROM таблица WHERE условие_выборки).
- DELETE псевдоним FROM … – удаление данных таблицы участвующей в предложения FROM, которая задана указанным псевдонимом. Конечно, здесь можно и не использовать псевдонимов, используя вместо них имена таблиц, но с псевдонимом на мой взгляд удобнее.
Для примера при помощи первого варианта:
При помощи второго варианта удалим остальные неиспользуемые должности. В целях демонстрации запрос намеренно излишне усложнен. Сначала посмотрим, что именно удалиться (всегда старайтесь делать проверку, а то ненароком можно удалить лишнее, а то и всю информацию из таблицы):
Убедились, что все нормально. Переписываем запрос на DELETE:
В качестве таблицы Positions может выступать и подзапрос, главное, чтобы он однозначно возвращал строки, которые будут удаляться. Давайте добавим для демонстрации в таблицу Positions мусора:
Теперь для демонстрации используем вместо таблицы Positions, подзапрос, в котором отбираются только определенные строки из таблицы Positions:
Так же мы можем использовать CTE выражения (подзапросы, оформленные в блоке WITH). Давайте снова добавим для демонстрации в таблицу Positions мусора:
И посмотрим на тот же запрос с CTE-выражением:
Заключение по INSERT, UPDATE и DELETE
Вот по сути и все, что я хотел рассказать вам про основные операторы модификации данных – INSERT, UPDATE и DELETE.
Я считаю, что данные операторы очень легко понять интуитивно, когда умеешь пользоваться конструкциями оператора SELECT. Поэтому рассказ о операторе SELECT растянулся на 3 части, а рассказ о операторах модификации был написан в такой беглой форме.
И как вы увидели, с операторами модификации тоже полет фантазии не ограничен. Но все же старайтесь писать, как можно проще и понятней, обязательно предварительно проверяя, какие записи будут обработаны при помощи SELECT, т.к. обычно модификация данных, это очень большая ответственность.
В дополнение скажу, что в диалекте MS SQL cо всеми операциями модификации можно использовать предложение TOP (INSERT TOP …, UPDATE TOP …, DELETE TOP …), но мне пока ни разу не приходилось прибегать к такой форме, т.к. здесь непонятно какие именно TOP записей будут обработаны.
Если уж нужно обработать TOP записей, то я, наверное, лучше воспользуюсь указанием опции TOP в подзапросе и применю в нем нужным мне образом ORDER BY, чтобы явно знать какие именно TOP записей будут обработаны. Для примера снова добавим мусора:
И удалим 2 последние записи:
Я здесь привожу примеры больше в целях демонстрации возможностей языка SQL. В реальных запросах старайтесь выражать свои намерения очень точно, дабы выполнение вашего запроса не привело к порче данных. Еще раз скажу – будьте очень внимательны, и не ленитесь делать предварительные проверки.
SELECT … INTO … – сохранить результат запроса в новой таблице
Данная конструкция позволяет сохранить результат выборки в новой таблице. Она представляет из себя что-то промежуточное между DDL и DML.
Типы колонок созданной таблицы будут определены на основании типов колонок набора, полученного запросом SELECT. Если в выборке присутствуют результаты выражений, то им должны быть заданы псевдонимы, которые будут служить в роли имен колонок.
Давайте отберем следующие данные и сохраним их в таблице EmployeesBonusTarget (перед FROM просто пишем INTO и указываем имя новой таблицы):
Можете обновить список таблиц в инспекторе объектов и увидеть новую таблицу EmployeesBonusTarget:
На самом деле я специально создал таблицу EmployeesBonusTarget, я ее буду использовать для демонстрации оператора MERGE.
Еще пара слов про конструкцию SELECT … INTO …
Данную конструкцию иногда удобно применять при формировании очень сложных отчетов, которые требуют выборки из множества таблиц. В этом случае данные обычно сохраняют во временных таблицах (#). Т.е. предварительно при помощи запросов, мы сбрасываем данные во временные таблицы, а затем используем эти временные таблицы в других запросах, которые формируют окончательный результат:
Иногда данную конструкцию удобно использовать, чтобы сделать полную копию всех данных текущей таблицы:
Это можно использовать, например, для подстраховки, перед тем как вносить серьезные изменения в структуру таблицы Employees. Вы можете сохранить копию либо всех данных таблицы, либо только тех данных, которых коснется модификация. Т.е. если что-то пойдет не так, вы сможете восстановить данные таблицы Employees с этой копии. В таких случаях конечно хорошо сделать предварительный бэкап БД на текущий момент, но это бывает не всегда возможно из-за огромных объемов, срочности и т.п.
Чтобы не засорять основную базу, можно создать новую БД и сделать копию таблицы туда:
Для того чтобы увидеть новую БД TestTemp, соответственно, обновите в инспекторе объектов список баз данных, в ней и уже можете найти данную таблицу.
На заметку.
В БД Oracle так же есть конструкция для сохранения результата запроса в новую таблицу, выглядит она следующим образом:
MERGE – слияние данных
Данный оператор хорошо подходит для синхронизации данных 2-х таблиц. Такая задача может понадобится при интеграции разных систем, когда данные передаются порциями из одной системы в другую.
В нашем случае, допустим, что стоит задача синхронизации таблицы EmployeesBonusTarget с таблицей EmployeesBonus.
Давайте добавим в таблицу EmployeesBonusTarget какого-нибудь мусора:
Теперь при помощи оператора MERGE добьемся того, чтобы данные в таблице EmployeesBonusTarget стали такими же, как и в EmployeesBonus, т.е. сделаем синхронизацию данных.
Синхронизацию мы будем осуществлять на основании сопоставления данных входящих в первичный ключ таблицы EmployeesBonus (EmployeeID, BonusDate, BonusTypeID):
- Если для строки таблицы EmployeesBonusTarget соответствия по ключу не нашлось, то нужно сделать удаление таких строк из EmployeesBonusTarget
- Если соответствие нашлось, то нужно обновить строки EmployeesBonusTarget данными соответствующей строки из EmployeesBonus
- Если строка есть в EmployeesBonus, но ее нет в EmployeesBonusTarget, то ее нужно добавить в EmployeesBonusTarget
Сделаем реализацию всей этой логики при помощи инструкции MERGE:
Данная конструкция должна оканчиваться «;».
После выполнения запроса сравните 2 таблицы, их данные должны быть одинаковыми.
Конструкция MERGE чем-то напоминает условный оператор CASE, она так же содержит блоки WHEN, при выполнении условий которых происходит то или иное действие, в данном случае удаление (DELETE), обновление (UPDATE) или добавление (INSERT). Модификация данных производится в таблице приемнике.
В качестве источника может выступать запрос. Например, синхронизируем только данные по отделу 3 и для примера исключаем блок «NOT MATCHED BY SOURCE», чтобы данные не удались в случае не совпадения:
Я показал работу конструкции MERGE в самом общем ее виде. При помощи нее можно реализовывать более разнообразные схемы для слияния данных, например, можно включать в блоки WHEN дополнительные условия (WHEN MATCHED AND … THEN). Это очень мощная конструкция, позволяющая в подходящих случаях сократить объем кода и совместить в рамках одного оператора функционал всех трех операторов – INSERT, UPDATE и DELETE.
И естественно с конструкцией MERGE так же можно применять CTE-выражения:
В общем, я постарался вам задать направление, более подробнее, в случае необходимости, изучайте уже самостоятельно.
TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы
Данный оператор является DDL-операцией и служит для быстрой очистки таблицы – удаляет все строки из нее. За более детальными подробностями обращайтесь в MSDN.
Некоторые вырезки из MSDN. TRUNCATE TABLE – удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.
Если таблица содержит столбец идентификаторов (столбец с опцией IDENTITY), счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.
Инструкцию TRUNCATE TABLE нельзя использовать если на таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.
Пример:
Заключение по операциям модификации данных
Здесь я наверно повторю, все что писал ранее.
Старайтесь в первую очередь написать запрос на модификацию как можно проще, в первую очередь попытайтесь выразить свое намерение при помощи базовых конструкций и в последнюю очередь прибегайте к использованию подзапросов.
Прежде чем запустить запрос на модификацию данных по условию, убедитесь, что он выбирает именно необходимые записи, а не больше и не меньше. Для этой цели воспользуйтесь операцией SELECT.
Не забывайте перед очень серьезными изменениями делать резервные копии, хотя бы той информации, которая будет подвергнута модификации, это можно сделать при помощи SELECT … INTO …
Помните, что модификация данных это очень серьезно.
Заключение
Вот и все, уважаемые читатели, на этом я оканчиваю свой учебник по SQL (DDL, DML).
Надеюсь, что вам было интересно провести время за прочтением данного материала, а главное надеюсь, что он принес вам понимание самых важных базовых конструкций языка SQL.
Учитесь, практикуйтесь, добивайтесь получения правильных результатов.
Спасибо за внимание! На этом пока все.
No comments:
Post a Comment