Операторы манипулирования данными в SQL

Data Manipulation Language (DML) (язык управления [манипулирования] данными) - это семейство компьютерных языков, используемых в компьютерных программах или пользователями баз данных для получения, вставки, удаления или изменения данных в базах данных.

На текущий момент наиболее популярным языком DML является SQL, используемый для получения и манипулирования данными в РСУБД. Другие формы DML использованы в IMS/DL1, базах данных CODASYL (таких как IDMS), и других.

Языки DML изначально использовались только компьютерными программами, но с появлением SQL стали также использоваться и людьми.

Функции языков DML определяются первым словом в предложении (часто называемом запросом), которое почти всегда является глаголом. В случае с SQL эти глаголы - "select" ("выбрать"), "insert" ("вставить"), "update" ("обновить"), и "delete" ("удалить"). Это превращает природу языка в ряд обязательных утверждений (команд) к базе данных.

Языки DML могут существенно различаться у различных производителей СУБД. Существует стандарт SQL, установленный ANSI, но производители СУБД часто предлагают свои собственные "расширения" языка.

1. Добавление информации в таблицу.

Insert into имя_таблицы (список полей)

values (список выражений)

добавление одной записи,

2. Чтобы добавить несколько значений из другой таблицы можно использовать следующий оператор –

Insert into имя_таблицы [(список добавляемых полей)]

или использовать подзапрос

select список_полей into целевая_таблица from список_таблиц where условие

- из таблиц будут выбраны поля, удовлетворяющие некоторому условию, и добавлены в новую таблицу.

3. Оператор обновления.

Update имя_таблицы set поле=значение [where условие]

(все что записано в квадратных скобках это не обязательно, может быть, а может и не быть).

4. Удаление записи из таблицы.

Delete from имя_таблицы [where условие]

Если where условие опущено, то удаляются все записи.

5. Оператор поиска данных в таблицах

Select {all | distinct} список_полей

from список_таблиц

[where условие]

[group by список_полей]

[having условие]

[order by поля[asc,desc]]

[union [all] подзапрос]

o distinct – режим, исключающий повторяющиеся записи в ответе.

o All – режим, при котором в ответ включаются все записи. По умолчанию принято all, его можно не указывать.

o Если одновременно присутствует where и having, то сначала будет выполняться where.

o Если присутствует where, having, group by, то сначала выполнится where, потом group by, а потом having.

o Select список_полей - указывает имена полей, которые должны содержаться в ответе;

o from список_таблиц – указывает имена таблиц, которые участвуют в запросе;

o where условие – задает условие отбора записей в ответ;

o group by список_полей – задает условие группировки записей, группировка – это операция разбиения на группы, каждая из которых содержит одинаковые значения в отмеченных столбцах;

o having условие задает условие отбора групп, если нет group by, то это условие применяется ко всей таблице;

o order by поля[asc,desc] – задает тип сортировки записей в ответе, asc – это сортировка по возрастанию, desc – по убыванию, по умолчанию ставится asc;

o union [all] подзапрос позволяет объединить главный Select с результатом подзапроса, подзапросом называется вложенный оператор Select.

SELECT [[ALL] | DISTINCT]{ * | элемент_SELECT [,элемент_SELECT]...}

FROM {базовая_таблица | представление} [псевдоним]

[,{базовая_таблица | представление} [псевдоним]]...

[WHERE фраза]

[GROUP BY фраза [HAVING фраза]];

Элемент_SELECT - это одна из следующих конструкций:

[таблица.]* | значение | SQL_функция | системная_переменная

где значение – это:

[таблица.]столбец | (выражение) | константа | переменная

Синтаксис выражений имеет вид

({[ [+] | - ] {значение | функция_СУБД} [ + | - | * | ** ]}...)

а синтаксис SQL_функций – одна из следующих конструкций:

{SUM|AVG|MIN|MAX|COUNT} ([[ALL]|DISTINCT][таблица.]столбец)

{SUM|AVG|MIN|MAX|COUNT} ([ALL] выражение)

COUNT(*)

Фраза WHERE включает набор условий для отбора строк:

WHERE [NOT] WHERE_условие [[AND|OR][NOT] WHERE_условие]...

где WHERE_условие – одна из следующих конструкций:

значение { = | <> | < | <= | > | >= } { значение | (подзапрос) }

значение_1 [NOT] BETWEEN значение_2 AND значение_3

значение [NOT] IN { (константа [,константа]...) | (подзапрос)

значение IS [NOT] NULL

[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']

EXISTS (подзапрос)

Кроме традиционных операторов сравнения (= | <> | < | <= | > | >=) в WHERE фразе используются условия BETWEEN (между), LIKE (похоже на), IN (принадлежит), IS NULL (не определено) и EXISTS (существует), которые могут предваряться оператором NOT (не). Критерий отбора строк формируется из одного или нескольких условий, соединенных логическими операторами:

AND - когда должны удовлетворяться оба разделяемых с помощью AND условия; OR - когда должно удовлетворяться одно из разделяемых с помощью OR условий; AND NOT - когда должно удовлетворяться первое условие и не должно второе; OR NOT - когда или должно удовлетворяться первое условие или не должно удовлетворяться второе, причем существует приоритет AND над OR (сначала выполняются все операции AND и только после этого операции OR). Для получения желаемого результата WHERE условия должны быть введены в правильном порядке, который можно организовать введением скобок.

Наконец, синтаксис фразы GROUP BY имеет вид

GROUP BY [таблица.]столбец [,[таблица.]столбец]... [HAVING фраза]

GROUP BY инициирует перекомпоновку формируемой таблицы по группам, каждая из которых имеет одинаковое значение в столб-цах, включенных в перечень GROUP BY. Далее к этим группам применяются агрегирующие функции, указанные во фразе SELECT, что приводит к замене всех значений группы на единственное значение (сумма, количество и т.п.).

С помощью фразы HAVING (синтаксис которой почти не отличается от синтаксиса фразы WHERE)

HAVING [NOT] HAVING_условие [[AND|OR][NOT] HAVING_условие]...

можно исключить из результата группы, не удовлетворяющие заданным условиям:

значение { = | <> | < | <= | > | >= } { значение | (подзапрос)

| SQL_функция }

{значение_1 | SQL_функция_1} [NOT] BETWEEN

{значение_2 | SQL_функция_2} AND {значение_3 | SQL_функция_3}

{значение | SQL_функция} [NOT] IN { (константа [,константа]...)

| (подзапрос) }

{значение | SQL_функция} IS [NOT] NULL

[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']

EXISTS (подзапрос)

Соединение - это процесс, когда две или более таблицы объединяются в одну. Способность объединять информацию из нескольких таблиц или запросов в виде одного логического набора данных обусловливает широкие возможности SQL.

В языке SQL для задания типа соединения таблиц в логический набор записей, из которого будет выбираться необходимая информация, используется операция JOIN в предложении FROM.

Формат операции:

FROM имя_таблицы_1 {INNER | LEFT | RIGHT}

JOIN имя_таблицы_2

ON условие_соединения

Операция тета-соединения в языке SQL называется INNER JOIN (внутреннее соединение) и используется, когда нужно включить все строки из обеих таблиц, удовлетворяющие условию объединения.

Внешнее соединение похоже на внутреннее, но в результирующий набор данных включаются также записи ведущей таблицы соединения, которые объединяются с пустым множеством записей другой таблицы.

Какая из таблиц будет ведущей, определяет вид соединения. LEFT - левое внешнее соединение, ведущей является таблица, расположенная слева от вида соединения; RIGHT - правое внешнее соединение, ведущая таблица расположена справа от вида соединения.

Пример:

Определить суммарную стоимость каждого товара первого сорта за каждый месяц.

SELECT Товар.Название, Month(Сделка.Дата)

AS Месяц, Sum(Товар.Цена*Сделка.Количество) AS Стоимость

FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара

WHERE Товар.Сорт="Первый"

GROUP BY Товар.Название, Month(Сделка.Дата)

Для товаров первого сорта установить цену в значение 140 и остаток – в значение 20 единиц.

UPDATE Товар SET Товар.Цена=140, Товар.Остаток=20

WHERE Товар.Сорт=" Первый "

Вывести информацию о всех товарах. Для проданных товаров будет указана дата сделки и количество. Для непроданных эти поля останутся пустыми.

SELECT Товар.*, Сделка.*

FROM Товар LEFT JOIN Сделка

ON Товар.КодТовара=Сделка.КодТовара;

 

Управление транзакциями

Под транзакцией понимается неделимая с точки зрения воздействия на БД последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации) такая, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует.

Операция считается транзакцией, если она удовлетворяет требованиям ACID-теста (Atomicity, Consistency, Isolation, Durability атомарность, согласованность, изолированность, долговечность).

Атомарность - лозунг транзакции - "Все или ничего": при завершении транзакции оператором COMMIT результаты гарантированно фиксируются во внешней памяти (смысл слова commit - "зафиксировать" результаты транзакции); при завершении транзакции оператором ROLLBACK результаты гарантированно отсутствуют во внешней памяти (смысл слова rollback - ликвидировать результаты транзакции).

Согласованность – по окончанию транзакции целостность должна быть установлена, но в ходе выполнения транзакции целостность может временно нарушаться.

Изолированность означает, что параллельно выполняющиеся транзакции не мешают друг другу.

Долговечность означает, что все подтвержденные в ходе выполнения изменения должны быть внесены в базу данных даже в случае сбоев системы.

Поддержание механизма транзакций - показатель уровня развитости СУБД. Корректное механизм поддержания транзакций одновременно является основой обеспечения целостности баз данных и также является базисом изолированности пользователей в многопользовательских системах. Часто эти два аспекта рассматриваются по отдельности, но на самом деле они взаимосвязаны, что и будет показано ниже.