Введение в язык SQL

 

 

 

 

 

INSERT

 

 

Команда INSERT используется для ввода строк в таблицу. При использовании синтаксиса указанного ниже вы в один момент сможете ввести только одну строку:

 

INSERT INTO table [(column1 [, column2 [, ... columnn]])]

VALUES (value1[, value2[,...valuen]])

 

где

table     – название таблицы, в которую будут вставлены строки

column  – это название столбцов

value     – это значения, которые будут вставлены в соотвествующие столбцы

 

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

Также Вы можете опустить тот или иной столбец при этом в этот столбец будет вставлена null или же указать самим в разделе VALUES в качестве значения тому или иному столбцу значение null. Также ваши данные обязательно должны соотвествовать типу столбцов или же в крайнем случаи должны иметь возможность подвергаться автоматическому преобразованию.

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

 

INSERT INTO scott.dept (deptno, dname, loc)

VALUES (50, 'IT', 'Baku');

 

Выполните этот запрос и в ответ увидите, что 1 rows inserted, то есть все прошло успешно и информация добавлена. Конечно мы могли и не писать имена всех столбцов как и было сказано выше

 

INSERT INTO scott.dept

VALUES (60, 'HR', 'Moscow')

 

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

 

INSERT INTO table [(column1 [, column2 [, ... columnn]])]

SUBQUERY

 

где

table                – название таблицы, в которую будут вставлены строки

column              – это название столбцов

SUBQUERY         – подзапрос при помощи, которого  полученные данные будут

введены в таблицу

 

INSERT INTO scott.bonus (ename, job, sal ,comm)

SELECT ename, job, sal ,comm

FROM scott.emp

WHERE job = 'MANAGER'

 

На этом примере видно, что имена столбцов двух таблиц совпадает, но это не является обязательным условием, обязательное условие является, то чтобы типы у них были подходящими.

 

 

UPDATE

 

 

Мы с вами говорили, что в DML есть команда, которая может изменять данные. Так вот это команда UPDATE, которая изменяет данные при этом она может за одну команду изменить как одну так и все строки таблицы, а в некоторых случаях она и не изменить какую-либо строку, все зависит от тех условиях, которые мы назначем.

 

Разберем ее синтаксис.

 

UPDATE  table

SET column1 = value1 [, columnn = valuen]

[WHERE condition]

 

где

table     – это названия таблицы, которую необходимо изменить

column – это названия столбцов, которые будут зменены

value     – это те значения, которые будет принимать столбцы

WHERE condition – это раздел условия, которым должны соотвествоать

изменяемые данные

 

Стоит отметить, если убрать раздел WHERE из команды, то это будет означать изменения значения по всем строкам таблицы. Также если не одна строка не будет удовлетворять условиям, то ничего не будет изменено.

 

UPDATE scott.emp

SET sal='2500'

WHERE empno=7369

 

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

UPDATE  table

SET column1 = subquery1[, column2 = subquery2 ]

[WHERE  expression1=subquery3, expression2=subquery4]

 

 

UPDATE scott.emp

SET sal=(select 3000

from dual)

WHERE empno in (select empno

from scott.emp

where ename = 'SMITH')

 

 

 

DELETE

 

 

А сейчас пришел черед последней из команд DML – это команда удаления DELETE При помощи этой команды, вы можете за один раз удалить все строки таблицы или только те, которые будут отвечать вашим условиям или вообще никакие, опять таки все зависит от условий, которые мы выставляем.

 

Синтаксис этой команды таков:

 

DELETE FROM TABLE

[WHERE condition]

 

Как видно это команда имеет очень простой синтаксис, если еы обратили внимания здесь даже невозможно указать имена столбцов. Естественно ведь комада удаляет строку целиком, а не чистит значения определенных столбцов Для чистки определенных столбцов вы можете вооружится командой UPDATE  и значением null

 

DELETE FROM scott.emp

WHERE ename = 'SMITH'

 

Если убрать условия из этого запроса – вы почистите, то есть удалите все строки из таблицы emp. Не советуем этого делать так как нам еще эта таблица понадобится в следующих главах. Естественно в качестве условия вы можете использовать определенный подзапрос.

 

 

 

MERGE

 

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

Первое, что приходит в голову, что мог быть бы код, который при помощи SELECT проверял бы в случаи нахождения выполнял бы UPDATE иначе INSERT. Но в SQL есть уже готовая команда, которая создана специально именно для этих целей – это команда MERGE. Разберем ее синтаксис:

 

MERGE INTO table_name AS table_alias

USING (table | view | subquery) AS alias

ON (join condition)

WHEN MATCHED THEN

UPDATE SET

col1 = col_val1,

col2 = col_val2

WHEN NOT MATCHED THEN

INSERT (column_list)

VALUES (column_values)

 

где

INTO     –  раздел отвечающий за то, в  какой таблице будет изменены или

добавлены данные

USING  –  раздел, в котором помещаются связывающие  таблицы, подзапросы,

представление с таблицей из раздела выше

ON        –  раздел, в котором указывается  условия, от которого и зависит будет ли

изменение данных или вставка

WHEN MATCHED | NOT MATCHED  – выполняется первое команда, если условие

из раздела выше сработало и соотвественно второе противном случаи

 

MERGE INTO emp e

USING dept d

ON (e.deptno=d.deptno)

WHEN MATCHED THEN

UPDATE SET

sal=sal + sal * 0.20

WHEN NOT MATCHED THEN

INSERT (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (8125, 'BOB', 'ANALYST', 7566, sysdate, '2500', 10, 10)

 

 

 

Транзакция

 

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

Транзакция начинается с выполнения операций и заканчивается при запуске команд COMMIT или ROLLBACK. СOMMIT – зафиксировать результат транзакции, ROLLBACK – откатить результат транзакции.

Основное требование к транзакции – это сохранение целостности данных. Каждая транзакция обладает свойством АСИД (атомарность, согласованность, изолированность,  долговечность):

  Атомарность - это  когда все входящие в транзакцию  операторы SQL рассматриваются, как единая  неделимая группа. То есть либо  она выполняется полностью все  операторы, либо никакой из операторов  не выполнится.

 Согласованность - это означает, что состояние  БД остается согласованным(целестном) и непротиворечивым до и после завершения транзакции.

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

 Долговременность - после фиксации транзакции (если  мы получили потверждение выполнение COMMIT) изменения в БД сохраняются и в не коем случае не откатятся не при каких условиях.

 

А сейчас рассмотрим, что же может быть транзакцией из всего нами рассмотренного. Как по вашему оператор SELECT может ли быть транзакцией или входить в нее ?

Для того чтобы найти ответ на этот вопрос рассмотрим SELECT через призму свойств транзакций. И увидим несколько противоречий, например написано, что после фиксации транзакции – так что же мы будем фиксировать после SELECT-а. Ничего – так как мы ничего и не изменяли, чтобы еще что-то фиксировать Следовательно SELECT никак не может быть транзакцией. Ну, а вот нами разобранные все операторы DML могут быть транзакциями. Потому что они все подвергают изменениям базу данных.

 

Таким образом в SQL транзакция представляется ввиде:

 

Одного или нескольких операторов DML которые не будут противоречить целосности данных

Одна команда DDL (DDL будет описано в следующих главах)

Одна команда DCL (DCL будет описано в следующих главах)

 

Давайте разберем когда же у нас начинаются и заканчиваются транзакции. Прочитав выше описанное, можно придти к выводу, что транзакция может начатся после любого оператора DML. Оно также может сразу же начаться и тут же завершится после любого DDL или DCL оператора. А когда же наши DML операторы могут завершится:

 

Если после DML операторов будет выполнен любой DDL оператор

Если после DML операторов будет выполнен любой DCL оператор

Если после DML операторов приложение будет завершенно аварийно. Тогда изменения не будут сохранены и база данных попытается откатить транзакции, которые выполнялись во время аварийного завершения.

Если после DML операторов следует команды COMMIT или ROLLBACK

 

Так что же такое COMMIT или ROLLBACK. COMMIT – это команда фиксации изменений. Своего рода SAVE на уровне базе данных. ROLLBACK - это команда отмены изменений на уровне базе данных.

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

 

SAVEPOINT точка сохранения

 

 

Бывают случаи, когда вы выполняете несколько изменений в базе данных и не фиксируете их. Вдруг вам может захочется вернутся на некоторый шаг, который был 5 команд назад. Конечно если вы запустите команду ROLLBACK, вы потеряете все изменения. Это конечно же вам не можт подойти – для этого было придуманы SAVEPOINT своеобразные флашки, которыми вы помечаете промежутки внутри одной транзакции и при желании можете вернутся имеено на этот момент. Чтобы поставить флажок необходимо набрать команду SAVEPOINT имя флажка, чтобы откатить изменения на этот флажок необходимо набрать команду ROLLBACK TO SAVEPOINT имя флажка.

 

На рисунке отображена схема работы Savepoint связке с Commit и Rollback-ом.

 

Давайте при помощи примеров попытаемся реализовать эту схему:

 

Сперва выполним данный скрипт

 

INSERT INTO scott.BONUS (ename, job, sal, comm)

VALUES ('BOB', 'SysAdmin','650', '18')

 

А сейчас откроем новое приложение и проверим появились ли эти данные из таблицы BONUS при этом не закрывая окно приложение, на котором мы выполнили этот скрипт.

 

SELECT *

FROM bonus

 

У меня результат 0 строк. Принципе это и следовало ожидать, потому что изменив данные таблице мы их не зафиксировали. А сейчас зафиксируем внесенную строку в том приложение в котором оно набиралось.

 

COMMIT

 

Давайте проверим эти данные скриптом указанным выше в том приложении, котором до этого мы не видели эти данные. Если в результате вышло те данные, которые мы вводили в таблицу, то вы все сделали правильно.

 

А сейчас почистим все таблицу с помощью команды DELETE:

 

DELETE FROM bonus

 

И сразу поставим флажок A

 

SAVEPOINT A

 

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

 

INSERT INTO BONUS (ename, job, sal, comm)

VALUES ('ALEX', 'DBA','1500', '20')

 

INSERT INTO BONUS (ename, job, sal, comm)

VALUES ('DMITRIY', 'HR','1050', '15')

 

UPDATE BONUS

SET sal = 1300

WHERE ename = 'DMITRIY'

 

SAVEPOINT B

 

 

INSERT INTO BONUS (ename, job, sal, comm)

VALUES ('MIHAIL', 'SysAdmin','950', '25')

 

А сейчас проверим, что же у нас получилось для этого выполним этот запрос.

 

SELECT *

FROM bonus

 

А сейчас самое интереное, будем проверять правильно ли работает схема. Для этого выполним переходы назад при помощи команды ROLLBACK TO SAVEPOINT

 

ROLLBACK TO SAVEPOINT B

 

И проверим, что изменилось увидим

 

SELECT *

FROM bonus

 

Что на одну строку стало меньше смотрим на то, когда стоял флажок B – как видим после флашка B был выполнен ввод третей строки.

 

Сейчас откатимся на флажок А.

 

ROLLBACK TO SAVEPOINT A

 

Проверив, что изменилось увидим, что в одной таблице нет ни одной строки. Опять просмотрев схему увидим, что это правильно согласно схеме.  А сейчас выполним ROLLBACK , что в таблице появилось одна строка как раз ту, которую мы зафиксировали в самом начале эксперимента.

 DDL - язык определения данных

 

Как мы увидели все наши данные находятся в таблицах. Но мы с вами до сих пор не разобрали, как же можно создать, изменять или удалять таблицы. В этой главе мы с вами разберем эти возмощности благодаря языку DDL (Data Definition Language) – язык определения данных. DDL работает не с данными, а объектами базы данных.

Язык DDL имеет свойства немедленного выполнения, выполнив операцию DDL автоматом запускается команда COMMIT, то есть вы не сможете вернуть измененное обратно за редким исключением. А если учесть, что среди DDL есть команды удаления, то важность этого языка увеличивается, ровно как должно и увеличится ваше внимания при работе с командами данного языка. Как уже отметили язык DDL не работает с данными, а работает с объектами базы данных. К объетам базы даных относятся:

Таблица - TABLE

Представление - VIEW

Последовательность – SEQUENCE

Индекс - INDEX

Синоним – SYNONYM

 

 

CREATE TABLE

 

Сейчас мы с вами разберем возможности DDL на основе объекта базы данных таблицы. А все остальные объекты будут расмотрены в следующих главах. Но прежде изучением DDL разберем какие же имена могут принимать объекты базы данных:

Должны начинатся на буквы

Должны быть длиной от 1 до 30 символов

Могут содержать только символы A-Z, a-z, 0-9, _, $, #

У одного пользователя базы данных не может быть два объекта с одинаковыми именами. То есть в одной схеме все имена объектов базы данных должны быть уникальными.