Dml что это sql
Что такое DDL, DML, DCL и TCL в языке SQL
Приветствую всех посетителей сайта Info-Comp.ru! В этом материале я расскажу Вам о том, что такое DDL, DML, DCL и TCL в языке SQL. Если Вы не знаете, что означают эти непонятные наборы букв и при этом работаете с языком SQL, то Вам обязательно необходимо прочитать данный материал.
Для начала давайте вспомним, что такое SQL, и для чего он нужен.
SQL – Structured Query Language
Structured Query Language (SQL) — язык структурированных запросов, с помощью него пишутся специальные запросы (SQL инструкции) к базе данных с целью получения этих данных из базы и для манипулирования этими данными.
Иными словами, язык SQL нужен для работы с базами данных, более подробно о языке SQL можете почитать в отдельной моей статье – Что такое SQL. Назначение и основа.
С точки зрения реализации язык SQL представляет собой набор операторов, которые делятся на определенные группы и у каждой группы есть свое назначение. В сокращенном виде эти группы называются DDL, DML, DCL и TCL.
Таким образом, эти непонятные буквы представляют собой аббревиатуру
названий групп операторов языка SQL.
DDL – Data Definition Language
Data Definition Language (DDL) – это группа операторов определения данных. Другими словами, с помощью операторов, входящих в эту группы, мы определяем структуру базы данных и работаем с объектами этой базы, т.е. создаем, изменяем и удаляем их.
В эту группу входят следующие операторы:
DML – Data Manipulation Language
Data Manipulation Language (DML) – это группа операторов для манипуляции данными. С помощью этих операторов мы можем добавлять, изменять, удалять и выгружать данные из базы, т.е. манипулировать ими.
В эту группу входят самые распространённые операторы языка SQL:
DCL – Data Control Language
Data Control Language (DCL) – группа операторов определения доступа к данным. Иными словами, это операторы для управления разрешениями, с помощью них мы можем разрешать или запрещать выполнение определенных операций над объектами базы данных.
TCL – Transaction Control Language
Transaction Control Language (TCL) – группа операторов для управления транзакциями. Транзакция – это команда или блок команд (инструкций), которые успешно завершаются как единое целое, при этом в базе данных все внесенные изменения фиксируются на постоянной основе или отменяются, т.е. все изменения, внесенные любой командой, входящей в транзакцию, будут отменены.
Группа операторов TCL предназначена как раз для реализации и управления транзакциями. Сюда можно отнести:
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL.
На сегодня это все, надеюсь, материал был Вам полезен, удачи!
Что такое SQL и как он работает
Википедия гласит, что SQL — это декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Не самое удобоваримое определение. Чтобы понять, о чём вообще речь, разберём его.
Декларативный язык программирования говорит, что должно быть сделано, а не как это необходимо сделать. Ещё один пример декларативного языка — HTML. Рассмотрим такой код:
С его помощью мы заявляем (declaration — заявление) браузеру, что хотим увидеть блок с классом className и кнопкой с текстом «Ясно. Понятно.» внутри. Для этого мы не создаём каких-либо переменных, циклов, условий. Мы знаем, что браузер нас понял, сам разберёт команду и вернёт результат или ошибку.
Здесь смысл довольно прост: мы даём команду и получаем результат. Мы не описываем, как эту команду выполнять. Чтобы понять, что такое реляционная база данных, разберём, что такое база данных в принципе. Декомпозируем это понятие на «база» и «данные».
Данные
В контексте баз данных под данными понимают набор значений, который собирается в строки и столбцы, тем самым представляя таблицу. Представим, что у нас есть каталог мебельного магазина. Нам нужно сохранить все данные из раздела «Шкафы» этого каталога в таблицу. Мы решили, что все шкафы отличаются друг от друга характеристиками:
Составим таблицу и вобьём в неё выдуманные данные.
У нас есть таблица с данными. Столбцами мы показываем, как они будут храниться. В примере я указал, что мы будем хранить информацию в структуре: производитель, модель, высота, длина, цвет, количество дверей. Иными словами, я создал структуру таблицы.
Добавляя в таблицу строки, я вводил в неё данные, ориентируясь на структуру, заданную в столбцах. Чем больше строк, тем больше данных. Чем больше столбцов, тем подробнее будут эти данные.
Ещё есть такое понятие, как «значение» — это пересечение столбца и строки. Например, у последней строки в столбце «Цвет» написано «хаки». Здесь «хаки» — значение. Если мы начнём группировать таблицы и добавим возможность манипулирования ими, то получим базу данных.
Теперь про базы
Получается, что БД — это совокупность данных, представленных определённым образом (в нашем случае — таблицей), и набор инструментов для манипулирования ими.
Данные могут быть сгруппированы не только в таблицы, но и в коллекции. У каждой базы есть свой инструмент для создания таблиц/коллекций, добавления, удаления или изменения данных, а также для составления выборки. В статье мы рассмотрим базы, которые состоят из таблиц, а инструментом манипулирования данными будет язык SQL.
Таблицы между собой могут объединяться в схемы — в одной базе данных их может быть несколько, а может и не быть деления на схемы вообще. Это зависит от БД.
Вернёмся к определению из Википедии и вспомним про слово «реляционные». Реляционные (от англ. relation — отношения) — это базы данных, таблицы которых могут выстраиваться в различных отношениях. Возьмём предыдущий пример и добавим в него тех самых «отношений». Создадим таблицу «Производитель», а ту, что в примере, обозначим как «Каталог».
Теперь таблицу «Каталог» можно оформить в другом виде:
Получилось так, что у таблиц «Каталог» и «Прозводитель» появились отношения. Значения из столбца «Каталог» ссылаются на строки из таблицы «Производитель». Добавлением отношения мы решили нескольких проблем:
Это не все проблемы, которые мы решили добавлением отношений. Для понимания других проблем необходимо углубиться в тему баз данных. Разделение данных на таблицы с отношениями — это процесс нормализации. Так можно достигать различных нормализованных форм данных. При достижении каждой из нормализованных форм мы избавляем данные от дополнительных проблем.
Вернёмся к SQL
Если читателю показалось, что мы ушли в сторону от SQL, так оно и есть. Но очень трудно понять, что такое SQL, не зная, с чем он работает.
Выходит, что SQL — это язык программирования, необходимый для написания команд к БД, после выполнения которых она вернёт результат. Результат будет зависеть от команды, написанной на SQL. Как в любом другом языке программирования, в SQL есть операторы для работы с данными, из которых складываются команды. Операторы распределены по четырём языкам:
DDL (Data Definition Language, язык описания данных) — язык, включающий операторы для работы со структурой данных. Операторы DDL нужны для реализации этих возможностей:
DDL используется, когда нужно создать структуру для хранения данных. Он не отвечает за сами данные — только за то, как они будут разделены по таблицам и схемам.
DML (Data Manipulation Language, язык манипуляции данными) — язык, который нужен для добавления, удаления, изменения данных и для выборки их из базы. Иными словами, для манипулирования данными. Пройдёмся по операторам:
DCL (Data Control Language, язык управления доступом к данным) — набор операторов, необходимых для предоставления доступа к данным. Кроме данных, в БД есть такие сущности, как пользователи. Нужно обязательно иметь возможность ограничить пользователям доступ к данным. Например, мы не хотим, чтобы менеджер проекта мог редактировать данные или их структуру. Для этого есть три группы операторов.
Есть такое понятие, как транзакции. Это набор команд (там может быть и всего одна), который завершается успешно тогда, когда правильно выполнены все команды из него. В случае неудачного завершения одной команды из транзакции, она вся откатывается (отменяются результаты выполнения предыдущих команд), реализуя принцип атомарности. Обычно в транзакцию включаются DML-команды.
Для управления транзакциями существует TCL (Transaction Control Language — язык управления транзакциями). Операторы здесь следующие:
TCL есть только в тех БД, которые поддерживают транзакции. Самое время поговорить о видах БД.
Виды СУБД
Познакомимся с новым понятием — СУБД, системой управления базой данных.
Сергей Кузнецов в книге «Основы баз данных» описал СУБД как комплекс программ, позволяющих создать базу данных (БД) и манипулировать данными (вставлять, обновлять, удалять и выбирать). Система обеспечивает безопасность, надёжность хранения и целостность данных, а также предоставляет средства для администрирования БД.
Получается что, СУБД — это SQL плюс комплекс программного обеспечения. Очень часто базы данных путают с системой управления базой данных. Это нормально: понятия неразрывны, сама по себе БД без системы управления мало чем отличается от текстового файла со строчками. Важно не только хранить данные, но и управлять ими. СУБД применяются везде, где нужно структурировано хранить данные — от простого блога до проектов Data Science.
Есть много популярных СУБД, рассмотрим несколько из них.
MySQL
MySQL — свободная реляционная СУБД. Разрабатывалась как легковесная замена тяжёлым СУБД, которую можно было установить на маломощный сервер, без сильных потерь в возможностях. MySQL трудится под капотом таких гигантов, как YouTube, Facebook, Twitter, GitHub.
СУБД написана на C и C++. MySQL породил множество ответвлений, которые сейчас стали самостоятельными СУБД, например Percona и MariaDB.
Oracle Database
История Oracle Database начинается с 1977 года. Это объектно-реляционная система управления данными. Это довольно тяжёлая СУБД, поддерживает системы любой сложности, например, в банковской или финансовой сферах. У неё нет бесплатной лицензии. Процедурный SQL — PL/SQL. Языки написания СУБД — Java/C/С++
Microsoft SQL Server
Microsoft SQL Server — система управления реляционными базами данных, разработанная Microsoft. Первая версия SQL Server появилась 29 апреля 1989 года. Это конкурент Oracle Database. Есть бесплатная лицензия для разработчиков, но не для коммерческого использования. Процедурный SQL — Transact-SQL. СУБД написана на C/C++/C#.
PostgreSQL
PostgreSQL — свободная объектно-реляционная система управления базами данных. Эта СУБД увидела свет 8 июля 1996 года. Конкурент MySQL в веб-разработке проектов любой сложности, также соперничает с базами от Oracle и Microsoft в промышленной разработке. У неё прекрасная русскоязычная документация. Как и MySQL, имеет бесплатную лицензию для коммерческой разработки, за что так же, как и MySQL, горячо любима. Процедурный SQL — PL/pgSQL. Разработана на языке С.
Каждая из приведённых СУБД работает на своём расширении SQL. У каждой — своя ниша применения, плюсы и минусы.
Что после знакомства?
Если вы не знаете, какая конкретно СУБД вам нужна, выбирайте MySQL. Она лишена изысканных возможностей, которые будут только сбивать начинающего разработчика. Большое комьюнити не оставит в беде и уже решило 95% проблем. Разнообразие графических клиентов для всех операционных систем хорошо помогает на ранних этапах. MySQL позволит набраться опыта и понять, чем она хуже или лучше других СУБД. Когда вы поймёте принципы работы MySQL, для вас не составит труда переключиться на работу с PostgreSQL или другой СУБД. Цель работы у всех СУБД одна — рациональное и надёжное хранение данных и быстрое их извлечение или изменение.
После того как вы определитесь с выбором, хорошо будет посмотреть практики других разработчиков на YouTube-каналах «Технострим Mail.ru Group» или HighLoad Channel, почитать замечательный портал ruhighload.com, где, кроме статей про базы данных, рассматриваются проблемы больших нагрузок. А для тех, кто любит почитать больше, подойдёт книга «MySQL по максимуму. 3-е издание» Бэрона Шварца, Петра Зайцева и Вадима Ткаченко. Узнать больше вы, конечно, можете и в GeekBrains — приходите ко мне или моим коллегам на курс «Основы баз данных».
Освоить востребованную профессию в Data Science можно всего за полтора года на курсах GeekBrains. После учёбы вы сможете работать по специальностям Data Scientist, Data Analyst, Machine Learning, Engineer Computer Vision-специалист или NLP-специалист.
Википедия гласит, что SQL — это декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Не самое удобоваримое определение. Чтобы понять, о чём вообще речь, разберём его.
Декларативный язык программирования говорит, что должно быть сделано, а не как это необходимо сделать. Ещё один пример декларативного языка — HTML. Рассмотрим такой код:
С его помощью мы заявляем (declaration — заявление) браузеру, что хотим увидеть блок с классом className и кнопкой с текстом «Ясно. Понятно.» внутри. Для этого мы не создаём каких-либо переменных, циклов, условий. Мы знаем, что браузер нас понял, сам разберёт команду и вернёт результат или ошибку.
Здесь смысл довольно прост: мы даём команду и получаем результат. Мы не описываем, как эту команду выполнять. Чтобы понять, что такое реляционная база данных, разберём, что такое база данных в принципе. Декомпозируем это понятие на «база» и «данные».
Данные
В контексте баз данных под данными понимают набор значений, который собирается в строки и столбцы, тем самым представляя таблицу. Представим, что у нас есть каталог мебельного магазина. Нам нужно сохранить все данные из раздела «Шкафы» этого каталога в таблицу. Мы решили, что все шкафы отличаются друг от друга характеристиками:
Составим таблицу и вобьём в неё выдуманные данные.
У нас есть таблица с данными. Столбцами мы показываем, как они будут храниться. В примере я указал, что мы будем хранить информацию в структуре: производитель, модель, высота, длина, цвет, количество дверей. Иными словами, я создал структуру таблицы.
Добавляя в таблицу строки, я вводил в неё данные, ориентируясь на структуру, заданную в столбцах. Чем больше строк, тем больше данных. Чем больше столбцов, тем подробнее будут эти данные.
Ещё есть такое понятие, как «значение» — это пересечение столбца и строки. Например, у последней строки в столбце «Цвет» написано «хаки». Здесь «хаки» — значение. Если мы начнём группировать таблицы и добавим возможность манипулирования ими, то получим базу данных.
Теперь про базы
Получается, что БД — это совокупность данных, представленных определённым образом (в нашем случае — таблицей), и набор инструментов для манипулирования ими.
Данные могут быть сгруппированы не только в таблицы, но и в коллекции. У каждой базы есть свой инструмент для создания таблиц/коллекций, добавления, удаления или изменения данных, а также для составления выборки. В статье мы рассмотрим базы, которые состоят из таблиц, а инструментом манипулирования данными будет язык SQL.
Таблицы между собой могут объединяться в схемы — в одной базе данных их может быть несколько, а может и не быть деления на схемы вообще. Это зависит от БД.
Вернёмся к определению из Википедии и вспомним про слово «реляционные». Реляционные (от англ. relation — отношения) — это базы данных, таблицы которых могут выстраиваться в различных отношениях. Возьмём предыдущий пример и добавим в него тех самых «отношений». Создадим таблицу «Производитель», а ту, что в примере, обозначим как «Каталог».
Теперь таблицу «Каталог» можно оформить в другом виде:
Получилось так, что у таблиц «Каталог» и «Прозводитель» появились отношения. Значения из столбца «Каталог» ссылаются на строки из таблицы «Производитель». Добавлением отношения мы решили нескольких проблем:
Это не все проблемы, которые мы решили добавлением отношений. Для понимания других проблем необходимо углубиться в тему баз данных. Разделение данных на таблицы с отношениями — это процесс нормализации. Так можно достигать различных нормализованных форм данных. При достижении каждой из нормализованных форм мы избавляем данные от дополнительных проблем.
Вернёмся к SQL
Если читателю показалось, что мы ушли в сторону от SQL, так оно и есть. Но очень трудно понять, что такое SQL, не зная, с чем он работает.
Выходит, что SQL — это язык программирования, необходимый для написания команд к БД, после выполнения которых она вернёт результат. Результат будет зависеть от команды, написанной на SQL. Как в любом другом языке программирования, в SQL есть операторы для работы с данными, из которых складываются команды. Операторы распределены по четырём языкам:
DDL (Data Definition Language, язык описания данных) — язык, включающий операторы для работы со структурой данных. Операторы DDL нужны для реализации этих возможностей:
DDL используется, когда нужно создать структуру для хранения данных. Он не отвечает за сами данные — только за то, как они будут разделены по таблицам и схемам.
DML (Data Manipulation Language, язык манипуляции данными) — язык, который нужен для добавления, удаления, изменения данных и для выборки их из базы. Иными словами, для манипулирования данными. Пройдёмся по операторам:
DCL (Data Control Language, язык управления доступом к данным) — набор операторов, необходимых для предоставления доступа к данным. Кроме данных, в БД есть такие сущности, как пользователи. Нужно обязательно иметь возможность ограничить пользователям доступ к данным. Например, мы не хотим, чтобы менеджер проекта мог редактировать данные или их структуру. Для этого есть три группы операторов.
Есть такое понятие, как транзакции. Это набор команд (там может быть и всего одна), который завершается успешно тогда, когда правильно выполнены все команды из него. В случае неудачного завершения одной команды из транзакции, она вся откатывается (отменяются результаты выполнения предыдущих команд), реализуя принцип атомарности. Обычно в транзакцию включаются DML-команды.
Для управления транзакциями существует TCL (Transaction Control Language — язык управления транзакциями). Операторы здесь следующие:
TCL есть только в тех БД, которые поддерживают транзакции. Самое время поговорить о видах БД.
Виды СУБД
Познакомимся с новым понятием — СУБД, системой управления базой данных.
Сергей Кузнецов в книге «Основы баз данных» описал СУБД как комплекс программ, позволяющих создать базу данных (БД) и манипулировать данными (вставлять, обновлять, удалять и выбирать). Система обеспечивает безопасность, надёжность хранения и целостность данных, а также предоставляет средства для администрирования БД.
Получается что, СУБД — это SQL плюс комплекс программного обеспечения. Очень часто базы данных путают с системой управления базой данных. Это нормально: понятия неразрывны, сама по себе БД без системы управления мало чем отличается от текстового файла со строчками. Важно не только хранить данные, но и управлять ими. СУБД применяются везде, где нужно структурировано хранить данные — от простого блога до проектов Data Science.
Есть много популярных СУБД, рассмотрим несколько из них.
MySQL
MySQL — свободная реляционная СУБД. Разрабатывалась как легковесная замена тяжёлым СУБД, которую можно было установить на маломощный сервер, без сильных потерь в возможностях. MySQL трудится под капотом таких гигантов, как YouTube, Facebook, Twitter, GitHub.
СУБД написана на C и C++. MySQL породил множество ответвлений, которые сейчас стали самостоятельными СУБД, например Percona и MariaDB.
Oracle Database
История Oracle Database начинается с 1977 года. Это объектно-реляционная система управления данными. Это довольно тяжёлая СУБД, поддерживает системы любой сложности, например, в банковской или финансовой сферах. У неё нет бесплатной лицензии. Процедурный SQL — PL/SQL. Языки написания СУБД — Java/C/С++
Microsoft SQL Server
Microsoft SQL Server — система управления реляционными базами данных, разработанная Microsoft. Первая версия SQL Server появилась 29 апреля 1989 года. Это конкурент Oracle Database. Есть бесплатная лицензия для разработчиков, но не для коммерческого использования. Процедурный SQL — Transact-SQL. СУБД написана на C/C++/C#.
PostgreSQL
PostgreSQL — свободная объектно-реляционная система управления базами данных. Эта СУБД увидела свет 8 июля 1996 года. Конкурент MySQL в веб-разработке проектов любой сложности, также соперничает с базами от Oracle и Microsoft в промышленной разработке. У неё прекрасная русскоязычная документация. Как и MySQL, имеет бесплатную лицензию для коммерческой разработки, за что так же, как и MySQL, горячо любима. Процедурный SQL — PL/pgSQL. Разработана на языке С.
Каждая из приведённых СУБД работает на своём расширении SQL. У каждой — своя ниша применения, плюсы и минусы.
Что после знакомства?
Если вы не знаете, какая конкретно СУБД вам нужна, выбирайте MySQL. Она лишена изысканных возможностей, которые будут только сбивать начинающего разработчика. Большое комьюнити не оставит в беде и уже решило 95% проблем. Разнообразие графических клиентов для всех операционных систем хорошо помогает на ранних этапах. MySQL позволит набраться опыта и понять, чем она хуже или лучше других СУБД. Когда вы поймёте принципы работы MySQL, для вас не составит труда переключиться на работу с PostgreSQL или другой СУБД. Цель работы у всех СУБД одна — рациональное и надёжное хранение данных и быстрое их извлечение или изменение.
После того как вы определитесь с выбором, хорошо будет посмотреть практики других разработчиков на YouTube-каналах «Технострим Mail.ru Group» или HighLoad Channel, почитать замечательный портал ruhighload.com, где, кроме статей про базы данных, рассматриваются проблемы больших нагрузок. А для тех, кто любит почитать больше, подойдёт книга «MySQL по максимуму. 3-е издание» Бэрона Шварца, Петра Зайцева и Вадима Ткаченко. Узнать больше вы, конечно, можете и в GeekBrains — приходите ко мне или моим коллегам на курс «Основы баз данных».
Освоить востребованную профессию в Data Science можно всего за полтора года на курсах GeekBrains. После учёбы вы сможете работать по специальностям Data Scientist, Data Analyst, Machine Learning, Engineer Computer Vision-специалист или NLP-специалист.
Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть пятая
Предыдущие части
В данной части мы рассмотрим
Операции модификации данных очень сильно связаны с конструкциями оператора SELECT, т.к. по сути выборка модифицируемых данных идет при помощи них. Поэтому для понимания данного материала, важное место имеет уверенное владение конструкциями оператора SELECT.
Данная часть, как я и говорил, будет больше обзорная. Здесь я буду описывать только те основные формы операторов модификации данных, которыми я сам регулярно пользуюсь. Поэтому на полноту изложения рассчитывать не стоит, здесь будут показан только необходимый минимум, который новички могут использовать как направление для более глубокого изучения. За более подробной информацией по каждому оператору обращайтесь в MSDN. Хотя кому-то возможно и в таком объеме информации будет вполне достаточно.
Т.к. прямая модификация информации в РБД требует от человека большой ответственности, а также потому что пользователи обычно модифицируют информацию БД посредством разных АРМ, и не имеют полного доступа к БД, то данная часть больше посвящается начинающим ИТ-специалистам, и я буду здесь очень краток. Но конечно, если вы смогли освоить оператор SELECT, то думаю, и операторы модификации вам будут под силу, т.к. после оператора SELECT здесь нет ничего сверхсложного, и по большей части должно восприниматься на интуитивном уровне. Но порой сложность представляют не сами операторы модификации, а то что они должны выполняться группами, в рамках одной транзакции, т.е. когда дополнительно нужно учитывать целостность данных. В любом случае можете почитать и попытаться проделать примеры в ознакомительных целях, к тому же в итоге вы сможете получить более детальную базу, на которой можно будет отработать те или иные конструкции оператора SELECT.
Проведем изменения в структуре нашей БД
Давайте проведем небольшое обновление структуры и данных таблицы Employees:
А также для демонстрационных целей расширим схему нашей БД, а за одно повторим DDL. Назначения таблиц и полей указаны в комментариях:
Вот такой полигон мы должны были получить в итоге:
Кстати, потом этот полигон (когда он будет наполнен данными) вы и можете использовать для того чтобы опробовать на нем разнообразные запросы – здесь можно опробовать и разнообразные JOIN-соединения, и UNION-объединения, и группировки с агрегированием данных.
INSERT – вставка новых данных
В диалекте 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 |
Хоть мы в этом случае могли и не указывать перечень полей, т.к. мы вставляем данные всех полей и в таком же виде, как они перечислены в таблице, т.е. мы могли бы написать:
Но я бы не рекомендовал использовать такой подход, особенно если данный запрос будет использоваться регулярно, например, вызываясь из какого-то АРМ. Опять же это чревато тем, что структура таблицы может изменяться, в нее могут быть добавлены новые поля, или же последовательность полей может быть изменена, что еще опасней, т.к. это может привести к появлению логических ошибок во вставленных данных. Поэтому лучше лишний раз не полениться и перечислить явно все поля, в которые вы хотите вставить значение.
В предыдущих частях мы периодически использовали опцию 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 – обновление данных
Давайте при помощи первой формы приведем даты приема каждого сотрудника в порядок. Выполним 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:
В качестве таблицы 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, т.е. сделаем синхронизацию данных.
Данная конструкция должна оканчиваться «;».
После выполнения запроса сравните 2 таблицы, их данные должны быть одинаковыми.
Конструкция MERGE чем-то напоминает условный оператор CASE, она так же содержит блоки WHEN, при выполнении условий которых происходит то или иное действие, в данном случае удаление (DELETE), обновление (UPDATE) или добавление (INSERT). Модификация данных производится в таблице приемнике.
В качестве источника может выступать запрос. Например, синхронизируем только данные по отделу 3 и для примера исключаем блок «NOT MATCHED BY SOURCE», чтобы данные не удались в случае не совпадения:
Я показал работу конструкции MERGE в самом общем ее виде. При помощи нее можно реализовывать более разнообразные схемы для слияния данных, например, можно включать в блоки WHEN дополнительные условия (WHEN MATCHED AND … THEN). Это очень мощная конструкция, позволяющая в подходящих случаях сократить объем кода и совместить в рамках одного оператора функционал всех трех операторов – INSERT, UPDATE и DELETE.
И естественно с конструкцией MERGE так же можно применять CTE-выражения:
В общем, я постарался вам задать направление, более подробнее, в случае необходимости, изучайте уже самостоятельно.
Использование конструкции OUTPUT
Конструкция OUTPUT дает возможность получить информацию по строкам, которые были добавлены, удалены или изменены в результате выполнения DML команд INSERT, DELETE, UPDATE и MERGE. Данная конструкция, представляет расширение для операций модификации данных и в каждой СУБД может быть реализовано по-своему, либо вообще отсутствовать.
Рассмотрим первую форму
Добавим в таблицу Positions новые записи:
После выполнения данной операции, записи будут вставлены в таблицу Positions и в добавок мы увидим информацию по добавленным строкам на экране.
Ключевое слово «inserted» дает нам доступ к значениям добавленных строк. В данном случае использование «inserted.*» вернет нам информацию по всем полям, которые есть в таблице Positions (ID и Name).
Так же после OUTPUT вы можете явно указать возвращаемый на экран перечень полей посредством «inserted.имя_поля», также вы можете использовать разные выражения:
При использовании DML команды DELETE, доступ к значениям измененных строк получается при помощи ключевого слова «deleted»:
Продемонстрируем на таблице Employees:
| ID | Старая Фамилия | Старое Имя | ID | Новая Фамилия | Новое Имя |
|---|---|---|---|---|---|
| 1005 | NULL | NULL | 1005 | Александров | Александр |
В случае MERGE мы можем так же использовать «inserted» и «deleted» для доступа к значениям обработанных строк.
Давайте для примера создадим таблицу PositionsTarget, на которой после будет показан пример с MERGE:
Добавим в PositionsTarget мусора:
Выполним команду MERGE с конструкцией OUTPUT:
| Old_ID | Old_Name | New_ID | New_Name | OperType |
|---|---|---|---|---|
| NULL | NULL | 1 | Бухгалтер | I |
| 2 | Директор-old | 2 | Директор | U |
| NULL | NULL | 3 | Программист | I |
| NULL | NULL | 4 | Старший программист | I |
| 100 | Qwert | NULL | NULL | D |
| 101 | Asdf | NULL | NULL | D |
Думаю, назначение первой формы понятно – сделать модификацию и получить результат в виде набора, который можно вернуть пользователю.
Рассмотрим вторую форму
У конструкции OUTPUT, есть и более важное предназначение – она позволяет не только получить, но и зафиксировать (OUTPUT … INTO …) информацию о том, что уже произошло по факту, то есть после выполнения операции модификации. Она может оказаться полезна в случае логированния произошедших действий. В некоторых случаях, ее можно использовать как хорошую альтернативу тригерам (для прозрачности действий).
Давайте создадим демонстрационную таблицу, для логирования изменений по таблице Positions:
А теперь сделаем при помощи конструкции (OUTPUT … INTO …) запись в эту таблицу:
Посмотрите, что получилось:
TRUNCATE TABLE – DDL-операция для быстрой очистки таблицы
Данный оператор является DDL-операцией и служит для быстрой очистки таблицы – удаляет все строки из нее. За более детальными подробностями обращайтесь в MSDN.
Некоторые вырезки из MSDN. TRUNCATE TABLE – удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.
Если таблица содержит столбец идентификаторов (столбец с опцией IDENTITY), счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.
Инструкцию TRUNCATE TABLE нельзя использовать если на таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.
Заключение по операциям модификации данных
Здесь я наверно повторю, все что писал ранее.
Старайтесь в первую очередь написать запрос на модификацию как можно проще, в первую очередь попытайтесь выразить свое намерение при помощи базовых конструкций и в последнюю очередь прибегайте к использованию подзапросов.
Прежде чем запустить запрос на модификацию данных по условию, убедитесь, что он выбирает именно необходимые записи, а не больше и не меньше. Для этой цели воспользуйтесь операцией SELECT.
Не забывайте перед очень серьезными изменениями делать резервные копии, хотя бы той информации, которая будет подвергнута модификации, это можно сделать при помощи SELECT … INTO …
Помните, что модификация данных это очень серьезно.
Приложение 1 – бонус по оператору SELECT
Подумав, я решил дописать этот раздел для тех, кто дошел до конца.
Получение сводных отчетов при помощи GROUP BY+CASE и конструкции PIVOT
Для начала давайте посмотрим, как можно создать сводный отчет при помощи конструкции GROUP BY и CASE-условий. Можно сказать, это классический способ создания сводных отчетов:
Теперь рассмотрим, как получить эти же данные при помощи конструкции PIVOT:
В конструкции PIVOT кроме SUM, как вы думаю догадались, можно использовать и другие агрегатные функции (COUNT, AVG, MIN, MAX, …).
Давайте теперь рассмотрим, как работает конструкция UNPIVOT. Для демонстрации сбросим сводный результат в таблицу DemoPivotTable:
Первым делом посмотрите, как у нас выглядят данные в данной таблице:
Теперь применим к данной таблице конструкцию UNPIVOT:
Обратите внимание, что NULL значения не войдут в результат.
Как вы наверно догадались, на месте таблицы может стоять и подзапрос с заданным для него псевдонимом.
GROUP BY ROLLUP и GROUP BY GROUPING SETS
Данные конструкции позволяют подбить промежуточные итоги по строкам.
Чтобы понять, как работает функции GROUPING, раскомментируйте поля g1, g2 и g3, чтобы они попали в результирующий набор, а также закомментируйте предложение HAVING.
Здесь для понимания, можете так же раскомментировать поле gID и закомментировать предложение HAVING.
При помощи GROUPING SET можно явно указать какие именно итоги нам нужны, поэтому здесь можно обойтись без предложения HAVING.
Т.е. можно сказать, что GROUP BY ROLLUP частный случай GROUP BY GROUPING SETS, когда делается вывод всех итогов.
Пример использования FULL JOIN
Здесь для примера выведем для каждого сотрудника сводные данные по начислениям бонусов и ЗП, поквартально:
Попробуйте самостоятельно разобрать, почему я здесь применил именно FULL JOIN. Посмотрите на результаты, которые дают запросы размещенные в блоке WITH.
Приложение 2 – OVER и аналитические функции
Предложение OVER служит для проведения дополнительных вычислений, на окончательном наборе, полученном оператором SELECT (в подзапросах или запросах). Поэтому предложения OVER может быть применено только в блоке SELECT, т.е. его нельзя использовать, например, в блоке WHERE.
Выражения с использованием OVER могут в некоторых ситуациях значительно сократить запрос. В данном приложении я постарался привести самые основные моменты с использованием данной конструкции. Надеюсь, что самостоятельная проработка каждого приведенного здесь запроса и их результатов, поможет вам разобраться с особенностями конструкции OVER и вы сможете применять ее по назначению (не злоупотребляя ими чрезмерно там, где можно обойтись без них и наоборот) при написании своих запросов.
Для демонстрационных целей, для получения более наглядных результатов, добавим немного новых данных:
Предложение OVER дает возможность делать агрегатные вычисления, без применения группировки
| ID | Name | DepartmentID | Salary | AllSalary | DepartmentSalary | SalaryPercentOfDepSalary | AllEmplCount | DepEmplCount |
|---|---|---|---|---|---|---|---|---|
| 1005 | Александров А.А. | NULL | 2000.00 | 19900.00 | 2000.00 | 100.000 | 10 | 1 |
| 1000 | Иванов И.И. | 1 | 5000.00 | 19900.00 | 5000.00 | 100.000 | 10 | 1 |
| 1002 | Сидоров С.С. | 2 | 2500.00 | 19900.00 | 2500.00 | 100.000 | 10 | 1 |
| 1003 | Андреев А.А. | 3 | 2000.00 | 19900.00 | 5000.00 | 40.000 | 10 | 3 |
| 1004 | Николаев Н.Н. | 3 | 1500.00 | 19900.00 | 5000.00 | 30.000 | 10 | 3 |
| 1001 | Петров П.П. | 3 | 1500.00 | 19900.00 | 5000.00 | 30.000 | 10 | 3 |
| 1006 | Антонов А.А. | 4 | 1800.00 | 19900.00 | 1800.00 | 100.000 | 10 | 1 |
| 1007 | Максимов М.М. | 5 | 1200.00 | 19900.00 | 3600.00 | 33.333 | 10 | 3 |
| 1008 | Данилов Д.Д. | 5 | 1200.00 | 19900.00 | 3600.00 | 33.333 | 10 | 3 |
| 1009 | Остапов О.О. | 5 | 1200.00 | 19900.00 | 3600.00 | 33.333 | 10 | 3 |
Предложение «PARTITION BY» позволяет сделать разбиение данных по группам, можно сказать выполняет здесь роль «GROUP BY».
Можно задать группировку по нескольким полям, использовать выражения, например, «PARTITION BY DepartmentID,PositionID», «PARTITION BY DepartmentID,YEAR(HireDate)».
Поэкспериментируйте и с другими агрегатными функциями, которые мы разбирали – AVG, MIN, MAX, COUNT с DISTINCT.
Нумерация и ранжирование строк
Для цели нумерации строк используется функция ROW_NUMBER.
Пронумеруем сотрудников по полю Name и по нескольким полям LastName,FirstName,MiddleName:
| ID | Name | EmpNoByName | EmpNoByFullName |
|---|---|---|---|
| 1005 | Александров А.А. | 1 | 6 |
| 1003 | Андреев А.А. | 2 | 7 |
| 1006 | Антонов А.А. | 3 | 1 |
| 1008 | Данилов Д.Д. | 4 | 2 |
| 1000 | Иванов И.И. | 5 | 8 |
| 1007 | Максимов М.М. | 6 | 3 |
| 1004 | Николаев Н.Н. | 7 | 4 |
| 1009 | Остапов О.О. | 8 | 5 |
| 1001 | Петров П.П. | 9 | 9 |
| 1002 | Сидоров С.С. | 10 | 10 |
Здесь для задания порядка в OVER используется предложение «ORDER BY».
Для разбиения на группы, здесь так же в OVER можно использовать предложение «PARTITION BY»:
| ID | EmpName | DepName | EmpNoInDepByName |
|---|---|---|---|
| 1005 | Александров А.А. | NULL | 1 |
| 1000 | Иванов И.И. | Администрация | 1 |
| 1002 | Сидоров С.С. | Бухгалтерия | 1 |
| 1003 | Андреев А.А. | ИТ | 1 |
| 1004 | Николаев Н.Н. | ИТ | 2 |
| 1001 | Петров П.П. | ИТ | 3 |
| 1008 | Данилов Д.Д. | Логистика | 1 |
| 1007 | Максимов М.М. | Логистика | 2 |
| 1009 | Остапов О.О. | Логистика | 3 |
| 1006 | Антонов А.А. | Маркетинг и реклама | 1 |
Ранжирование строк – это можно сказать нумерация, только группами. Есть 2 вида нумерации, с дырками (RANK) и без дырок (DENSE_RANK).
| ID | EmpName | PositionID | EmpCountInPos | RankValue | DenseRankValue |
|---|---|---|---|---|---|
| 1005 | Александров А.А. | NULL | 1 | 1 | 1 |
| 1002 | Сидоров С.С. | 1 | 1 | 2 | 2 |
| 1000 | Иванов И.И. | 2 | 1 | 3 | 3 |
| 1001 | Петров П.П. | 3 | 2 | 4 | 4 |
| 1004 | Николаев Н.Н. | 3 | 2 | 4 | 4 |
| 1003 | Андреев А.А. | 4 | 1 | 6 | 5 |
| 1006 | Антонов А.А. | 10 | 1 | 7 | 6 |
| 1007 | Максимов М.М. | 11 | 3 | 8 | 7 |
| 1008 | Данилов Д.Д. | 11 | 3 | 8 | 7 |
| 1009 | Остапов О.О. | 11 | 3 | 8 | 7 |
Аналитические функции: LAG() и LEAD(), FIRST_VALUE() и LAST_VALUE()
Данные функции позволяют получить значения другой строки относительно текущей строки.
Рассмотрим LAG() и LEAD():
| CurrEmpID | CurrEmpName | PrevEmpID | PrevEmpName | PrevPrevEmpID | PrevPrevEmpName | NextEmpID | NextEmpName | NextNextEmpID | NextNextEmpName |
|---|---|---|---|---|---|---|---|---|---|
| 1000 | Иванов И.И. | NULL | NULL | NULL | not found | 1001 | Петров П.П. | 1002 | Сидоров С.С. |
| 1001 | Петров П.П. | 1000 | Иванов И.И. | NULL | not found | 1002 | Сидоров С.С. | 1003 | Андреев А.А. |
| 1002 | Сидоров С.С. | 1001 | Петров П.П. | 1000 | Иванов И.И. | 1003 | Андреев А.А. | 1004 | Николаев Н.Н. |
| 1003 | Андреев А.А. | 1002 | Сидоров С.С. | 1001 | Петров П.П. | 1004 | Николаев Н.Н. | 1005 | Александров А.А. |
| 1004 | Николаев Н.Н. | 1003 | Андреев А.А. | 1002 | Сидоров С.С. | 1005 | Александров А.А. | 1006 | Антонов А.А. |
| 1005 | Александров А.А. | 1004 | Николаев Н.Н. | 1003 | Андреев А.А. | 1006 | Антонов А.А. | 1007 | Максимов М.М. |
| 1006 | Антонов А.А. | 1005 | Александров А.А. | 1004 | Николаев Н.Н. | 1007 | Максимов М.М. | 1008 | Данилов Д.Д. |
| 1007 | Максимов М.М. | 1006 | Антонов А.А. | 1005 | Александров А.А. | 1008 | Данилов Д.Д. | 1009 | Остапов О.О. |
| 1008 | Данилов Д.Д. | 1007 | Максимов М.М. | 1006 | Антонов А.А. | 1009 | Остапов О.О. | NULL | not found |
| 1009 | Остапов О.О. | 1008 | Данилов Д.Д. | 1007 | Максимов М.М. | NULL | NULL | NULL | not found |
В данных функциях вторым параметром можно указать сдвиг относительно текущей строки, а третьим параметром можно указать возвращаемое значение для случая если для указанного смещения строки не существует.
Для разбиения данных по группам, попробуйте самостоятельно добавить предложение «PARTITION BY» в OVER, например, «OVER(PARTITION BY emp.DepartmentID ORDER BY emp.ID)».
Рассмотрим FIRST_VALUE() и LAST_VALUE():
| CurrEmpID | CurrEmpName | DepartmentID | FirstEmpID | FirstEmpName | LastEmpID | LastEmpName |
|---|---|---|---|---|---|---|
| 1005 | Александров А.А. | NULL | 1005 | Александров А.А. | 1005 | Александров А.А. |
| 1000 | Иванов И.И. | 1 | 1000 | Иванов И.И. | 1000 | Иванов И.И. |
| 1002 | Сидоров С.С. | 2 | 1002 | Сидоров С.С. | 1002 | Сидоров С.С. |
| 1001 | Петров П.П. | 3 | 1001 | Петров П.П. | 1004 | Николаев Н.Н. |
| 1003 | Андреев А.А. | 3 | 1001 | Петров П.П. | 1004 | Николаев Н.Н. |
| 1004 | Николаев Н.Н. | 3 | 1001 | Петров П.П. | 1004 | Николаев Н.Н. |
| 1006 | Антонов А.А. | 4 | 1006 | Антонов А.А. | 1006 | Антонов А.А. |
| 1007 | Максимов М.М. | 5 | 1007 | Максимов М.М. | 1009 | Остапов О.О. |
| 1008 | Данилов Д.Д. | 5 | 1007 | Максимов М.М. | 1009 | Остапов О.О. |
| 1009 | Остапов О.О. | 5 | 1007 | Максимов М.М. | 1009 | Остапов О.О. |
Думаю, здесь все понятно. Стоит только объяснить, что такое RANGE.
Параметры RANGE и ROWS
При помощи дополнительных параметров «RANGE» и «ROWS», можно изменить область работы функции, которая работает с предложением OVER. У каждой функции по умолчанию используется какая-то своя область действия. Такая область обычно называется окном.
Важное замечание. В разных СУБД для одних и тех же функций область по умолчанию может быть разной, поэтому нужно быть внимательным и смотреть справку конкретной СУБД по каждой отдельной функции.
Общий синтаксис этих опций выглядит следующим образом:
Вариант 1:
< PRECEDING | CURRENT ROW>
Здесь проще понять если проанализировать в Excel результат запроса:
| ID | Salary | Sum1 | Sum2 | Sum3 | Sum4 | Sum5 | Sum6 | Sum7 | Sum8 |
|---|---|---|---|---|---|---|---|---|---|
| 1000 | 5000.00 | 19900.00 | 19900.00 | 5000.00 | 19900.00 | 6000.00 | 6500.00 | 5000.00 | 5000.00 |
| 1001 | 1500.00 | 19900.00 | 19900.00 | 6500.00 | 14900.00 | 6000.00 | 9000.00 | 6500.00 | 6500.00 |
| 1002 | 2500.00 | 19900.00 | 19900.00 | 9000.00 | 13400.00 | 5500.00 | 6000.00 | 9000.00 | 9000.00 |
| 1003 | 2000.00 | 19900.00 | 19900.00 | 11000.00 | 10900.00 | 5300.00 | 6000.00 | 11000.00 | 11000.00 |
| 1004 | 1500.00 | 19900.00 | 19900.00 | 12500.00 | 8900.00 | 5000.00 | 5500.00 | 7500.00 | 12500.00 |
| 1005 | 2000.00 | 19900.00 | 19900.00 | 14500.00 | 7400.00 | 4200.00 | 5300.00 | 8000.00 | 14500.00 |
| 1006 | 1800.00 | 19900.00 | 19900.00 | 16300.00 | 5400.00 | 3600.00 | 5000.00 | 7300.00 | 16300.00 |
| 1007 | 1200.00 | 19900.00 | 19900.00 | 17500.00 | 3600.00 | 2400.00 | 4200.00 | 6500.00 | 17500.00 |
| 1008 | 1200.00 | 19900.00 | 19900.00 | 18700.00 | 2400.00 | 1200.00 | 3600.00 | 6200.00 | 18700.00 |
| 1009 | 1200.00 | 19900.00 | 19900.00 | 19900.00 | 1200.00 | NULL | 2400.00 | 5400.00 | 19900.00 |
С RANGE все тоже самое, только здесь смещения идут не относительно строк, а относительно их значений. Поэтому в данном случае в ORDER BY допустимы значения только типа дата или число.
| PositionID | Salary | Sum1 | Sum2 | Sum3 | Sum4 | Sum8 |
|---|---|---|---|---|---|---|
| NULL | 2000.00 | 2000.00 | 19900.00 | 2000.00 | 19900.00 | 2000.00 |
| 1 | 2500.00 | 2500.00 | 19900.00 | 4500.00 | 17900.00 | 4500.00 |
| 2 | 5000.00 | 5000.00 | 19900.00 | 9500.00 | 15400.00 | 9500.00 |
| 3 | 1500.00 | 3000.00 | 19900.00 | 12500.00 | 10400.00 | 12500.00 |
| 3 | 1500.00 | 3000.00 | 19900.00 | 12500.00 | 10400.00 | 12500.00 |
| 4 | 2000.00 | 2000.00 | 19900.00 | 14500.00 | 7400.00 | 14500.00 |
| 10 | 1800.00 | 1800.00 | 19900.00 | 16300.00 | 5400.00 | 16300.00 |
| 11 | 1200.00 | 3600.00 | 19900.00 | 19900.00 | 3600.00 | 19900.00 |
| 11 | 1200.00 | 3600.00 | 19900.00 | 19900.00 | 3600.00 | 19900.00 |
| 11 | 1200.00 | 3600.00 | 19900.00 | 19900.00 | 3600.00 | 19900.00 |
Заключение
Вот и все, уважаемые читатели, на этом я оканчиваю свой учебник по SQL (DDL, DML).
Надеюсь, что вам было интересно провести время за прочтением данного материала, а главное надеюсь, что он принес вам понимание самых важных базовых конструкций языка SQL.
Учитесь, практикуйтесь, добивайтесь получения правильных результатов.
Спасибо за внимание! На этом пока все.
PS. Отдельное спасибо всем, кто помогал сделать данный материал лучше, указывая на опечатки или давая дельные советы!