SQL Команда UPDATE

Команда UPDATE

Команда UPDATE — производит изменения в уже существующей записи или во множестве записей в таблице SQL. Изменяет существующие значения в таблице или в основной таблице представления.

Команда UPDATE Синтаксис команды

Синтаксис команды UPDATE

Команда UPDATE. Основные ключевые слова и параметры команды UPDATE

• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя
• table view — имя таблицы SQL, в которой изменяются данные; если определяется представление, данные изменяются в  основной таблице SQL представления
• subquery_1 — подзапрос, который сервер обрабатывает тем же самым способом как представление
• сolumn — столбец таблицы SQL или представления SQL, значение которого изменяется; если столбец таблицы из предложения SET опускается, значение столбца остается неизменяемым
• expr — новое значение, назначаемое соответствующему столбцу; это выражение может содержать главные переменные и необязательные  индикаторные переменные
• subquery_2 — новое значение, назначаемое соответствующему столбцу
• subquery_3 — новое значение, назначаемое соответствующему столбцу

WHERE — определяет диапазон изменяемых строк теми, для которых определенное условие является TRUE; если опускается эта фраза, модифицируются все строки в таблице или представлении.
При выдаче утверждения UPDATE включается любой UPDATE-триггер, определенный на таблице.
Подзапросы. Если предложение SET содержит подзапрос, он возвращает точно одну строку для каждой модифицируемой строки. Каждое значение в результате подзапроса назначается соответствующим столбцам списка в круглых скобках. Если подзапрос не возвращает никакие строки, столбцу назначается NULL. Подзапросы могут выбирать данные из модифицируемой таблицы. Предложение SET может совмещать выражения и подзапросы.

Команда UPDATE Пример 1
Изменение для всех покупателей рейтинга на значение, равное 200:

UPDATE Customers SET rating = 200;

Команда UPDATE Пример 2
Замена значения столбца во всех строках таблицы, как правило, используется редко. Поэтому в команде UPDATE, как и в команде DELETE, можно использовать предикат. Для выполнения указанной замены значений столбца rating, для всех покупателей, которые обслуживаются продавцом Giovanni (snum = 1003), следует ввести:

UPDATE Customers SET rating = 200 WHERE snum = 1001;

Команда SQL UPDATE Пример 3
В предложении SET можно указать любое количество значений для столбцов, разделенных запятыми:

UPDATE emp SET job = ‘MANAGER’, sal = sal + 1000, deptno = 20 WHERE ename = ‘JONES’;

Команда UPDATE Пример 4
В предложении SET можно указать значение NULL без использования какого-либо специального синтаксиса (например, такого как IS NULL). Таким образом, если нужно установить все рейтинги покупателей из Лондона (city = ‘London’) равными NULL-значению, необходимо ввести:

UPDATE Customers SET rating = NULL WHERE city = ‘London’;

Команда UPDATE Пример 5
Поясняет использование следующих синтаксических конструкций команды UPDATE:

• Обе формы предложения SET вместе в одном утверждении.
• Подзапрос.
• Предложение WHERE, ограничивающее диапазон  модифицируемых строк.

UPDATE emp a SET deptno =
(SELECT deptno FROM dept WHERE loc = ‘BOSTON’), (sal, comm) = (SELECT1.1*AVG(sal), 1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = ‘DALLAS’ OR loc = ‘DETROIT’);

Вышеупомянутое утверждение UPDATE выполняет следующие операции:

• Модифицирует только тех служащих, кто работают в Dallas или Detroit
• Устанавливает значение колонки deptno для служащих из Бостона
• Устанавливает жалованье каждого служащего в 1.1 раз больше среднего жалованья всего отдела
• Устанавливает комиссионные каждого служащего в 1.5 раза больше  средних комиссионных всего отдела

Команда DELETE

Команда DELETE

Команда DELETE удаляет строки из таблицы или представления основной таблицы базы данных, например, в MySQL, Oracle.

Команда DELETE. Синтаксис команды DELETE

Синтаксис команды DELETE

Команда DELETE. Основные ключевые слова и параметры команды DELETE

• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя
• table view — имя таблицы, из которой удаляются строки; если определяется представление, сервер удаляет строки из основной таблицы представления
• subquery — подзапрос, с помощью которого выбираются строки для удаления; сервер выполняет подзапрос и использует строки его результата как таблицу фразы FROM
• WHERE — удаляет только строки, которые удовлетворяют условию; условие может ссылаться на таблицу и содержать подзапрос.

При выдаче утверждения DELETE включается любой DELETE-триггер, определенный на таблице.
Команда DELETE Пример №1
Удаление всех строк из таблицы:

DELETE FROM temp_assign;

В данном примере команда DELETE удаляет все строки без исключения.
Команда DELETE Пример №2.
Удаляет из таблицы всех продавцов, у которых комиссионные меньше 100 у.е. в месяц:

DELETE FROM emp WHERE JOB = ‘SALESMAN’ AND COMM < 100;

В данном примере команда DELETE удаляет все строки, которые попадают под условие JOB = ‘SALESMAN’ AND COMM < 100;
Команда DELETE Пример №3
Предыдущий пример можно записать по-другому:

DELETE FROM (select * from emp) WHERE job = ‘SALESMAN’ AND comm < 100;

Для удаления всех записей в MySQL можно использовать следующую команду DELETE:

DELETE * FROM table_nam;

INSERT

SQL INSERT

Команда INSERT добавляет строки в таблицу или представление основной таблицы.

Синтаксис команды Sql INSERT

Синтаксис команды Insert

Основные ключевые слова и параметры команды INSERT

• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя
• table view — имя таблицы, в которую строки должны быть вставлены; если указано представление, то строки вставляются в основную таблицу представления
• subquery_1 — подзапрос, который сервер обрабатывает тем же самым способом как представление
• column — столбец таблицы или представления, в который для каждой вставленной строки вводится значение из фразы VALUES или подзапроса; если один из столбцов таблицы опускается из этого списка, значением столбца для вставленной строки является значение по умолчанию столбца, определенное при создании таблицы. Если полностью опускается список столбца, предложение VALUES или запрос должен определить значения для всех столбцов в таблице
• VALUES — определяет строку значений, которые будут вставлены в таблицу или представление; значение должно быть определено в предложении VALUES для каждого столбца в списке столбцов
• subquery_2 — подзапрос, который возвращает строки, вставляемые в таблицу; выборочный список этого подзапроса должен иметь такое же количество столбцов, как в списке столбцов утверждения INSERT

Утверждение INSERT с фразой VALUES добавляет одиночную строку к таблице. Эта строка содержит значения, определенные фразой VALUES.
Утверждение INSERT с подзапросом вместо фразы VALUES добавляет к таблице все строки, возвращенные подзапросом. Сервер обрабатывает подзапрос и вставляет каждую возвращенную строку в таблицу. Если подзапрос не выбирает никакие строки, сервер не вставляет никакие строки в таблицу.
Подзапрос может обратиться к любой таблице или представлению, включая целевую таблицу утверждения INSERT. Сервер назначает значения полям в новых строках, основанных на внутренней позиции столбцов в таблице и порядке значений фразы VALUESили в списке выбора запроса. Если какие-либо столбцы пропущены в списке столбцов, сервер назначает им значения по умолчанию, определенные при создании таблицы. Если любой из этих столбцов имеет NOT NULL ограничение то сервер возвращает ошибку, указывающую, что ограничение было нарушено и отменяет утверждение INSERT.
При выдаче утверждения INSERT включается любой INSERT — триггер, определенный на таблице.

INSERT INTO

INSERT INTO Пример 1

INSERT INTO dept VALUES (50, «ПРОДУКЦИЯ», «САН-ФРАНЦИСКО»);

INSERT INTO Customers (city, cname, cnum) VALUES (‘London’, ‘Hoffman’, 2001);

INSERT INTO Пример 2
Нижеприведенная команда копирует данные сотрудников фирмы, комисионные которых превышают 25% от дохода в таблицу bonus:

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal;

INSERT INTO Пример 3
Если нужно вставить NULL-значение, необходимо указать его как обычное значение следующим образом:

INSERT INTO Salespeople VALUES (1001,’Peel’,NULL,12);

INSERT INTO Пример 4
Команду INSERT можно применить для того, чтобы извлечь значения из одной таблицы и разместить их в другой, воспользовавшись для этого запросом. Для этого достаточно заменить предложение VALUES на соответствующий запрос:

INSERT INTO Londonstaff SELECT * FROM Salespeople WHERE city = ‘London’;

MySQL INSERT

Для вставки новых строк в базу данных MySQL используется команда INSERT, примеры команды INSERT приведены ниже:
INSERT INTO Пример 1.
Вставка новой строки в таблицу table_name.

INSERT INTO table_name VALUES (‘1′,’165′,’0′,’name’);

INSERT INTO Пример 2.
Вставка новой строки в таблицу table_name с указанием вставки данных в нужные нам колонки.

INSERT INTO table_name VALUES (‘1′,’165′,’0′,’name’);

В базе данных MySQL имеется возможность вставлять множество новых строк, используя одну команду INSERT.
INSERT INTO Пример 3.
Вставка несколько строк в таблицу table_name.

INSERT INTO table_name (tbl_id, chislo, chislotwo, name) VALUES (‘1′,’159′,’34’,’name1′), (‘2′,’14’,’61’,’name2′), (‘3′,’356′,’8′,’name3’);

SQL ALTER TABLE

Команда ALTER TABLE используется для добавления, удаления или модификацииколонки в уже существующей таблице.

Команда ALTER TABLE

Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:

• добавляет столбец
• добавляет ограничение целостности
• переопределяет столбец (тип данных, размер, умалчиваемое значение)
• удаляет столбец
• модифицирует характеристики памяти или иные параметры
• включает, выключает или удаляет ограничение целостности или триггер.

Условие: Таблица должна быть в схеме пользователя, или пользователь должен иметь системную привилегию ALTER ANY TABLE.

Добавляя столбец с ограничением NOT NULL, разработчик или администратор БД должны учесть ряд обстоятельств. Сначала нужно создать столбец без ограничения, а затем ввести значения во все его строки. После того как все значения столбца станут не NULL-значениями, к нему можно применить ограничение NOT NULL. Если столбец с ограничением NOT NULL пытается добавить пользователь, возвращается сообщение об ошибке, говорящее о том, что либо таблица должна быть пустой, либо в столбце должны содержаться значения для каждой существующей строки (напомним, что после наложения на столбец ограничения NOT NULL в нем не могут присутствовать NULL-значения ни в одной из существующих строк). В СУБД Oracle, начиная с версии 8i, можно удалять столбцы.

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

• Увеличение размера столбца CHAR или VARCHAR2
• Увеличение размера столбца NUMBER
• Добавление новых столбцов в таблицу

Уменьшение различных характеристик таблицы, в том числе некоторых типов данных столбцов и реального числа столбцов таблицы, требует особых действий. Часто перед внесением изменения нужно убедиться в том, что в соответствующем столбце или столбцах все значения являются NULL-значениями. Для выполнения подобных операций над столбцами таблицы, содержащими данные, разработчик должен найти или создать какую-то область для временного хранения этих данных. Например, создать таблицу с помощью команды CREATE TABLE AS SELECT, в которой извлекаются данные из первичного ключа и изменяемого столбца или столбцов. Допустимые изменения:

• Уменьшение размера столбца NUMBER (только при пустом столбце для всех строк)
• Уменьшение размера столбца CHAR или VARCHAR2 (только при пустом столбце для всех строк)
• Изменение типа данных столбца (только при пустом столбце для всех строк)

ALTER TABLE Пример 1

Добавление столбца в таблицу:

ALTER TABLE t1(pole1 char(10));

ALTER TABLE Пример 2

Изменение размера столбца таблицы:

ALTER TABLE t1 MODIFY (pole1 char(20));

ALTER TABLE Пример 3

Удаление столбца таблицы:

ALTER TABLE t1 DROP COLUMN pole1;

С помощью команды ALTER TABLE можно изменить имя таблицы без реального переноса физической информации в БД:

ALTER TABLE t1 RENAME TO t2;

Аналогичную операцию можно выполнить с помощью команды RENAME:

RENAME t1 TO t2;

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

ALTER TABLE Пример 4

Модификация структуры таблицы

ALTER TABLE t1 MODIFY (pole1 NOT NULL);

CREATE TABLE t2

(pole1 CHAR(10) PRIMARY KEY);

ALTER TABLE t1 ADD

(CONSTRAINT fk_t1 FOREIGN KEY (pole1)

REFERENCES t2 (pole1));

ALTER TABLE t1 ADD (UNIQUE (p_name));

ALTER TABLE t1 ADD (p_size CHAR(4) CHECK

(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));

В первой из приведенных выше команд для добавления ограничения NOT NULL для столбца используется конструкция MODIFY, а для добавления всех табличных ограничений целостности других типов – конструкция ADD. Столбец, для которого добавляется ограничение, должен уже существовать в таблице БД; в противном случае создать ограничение не удастся.

ALTER TABLE Пример 5

Для добавления ограничений целостности можно не указывать имя создаваемого ограничения с помощью ключевого слова  CONSTRAINT. В этом случае команда будет выглядеть следующим образом:

ALTER TABLE t1 ADD  FOREIGN KEY (pole1) REFERENCES t2 (pole1);

Существует ряд условий создания ограничений:

• Первичные ключи: в столбцах не могут содержаться NULL-значения, и все значения должны быть уникальны.
• Внешние ключи: в тех столбцах других таблиц, на которые производятся ссылки, должны содержаться значения, соответствующие всем значениям ссылающихся столбцов, либо значения этих последних должны быть NULL-значениями.
• Ограничения UNIQUE: все значения столбцов должны быть уникальными или NULL-значениями.
• Ограничения CHECK: новое ограничение будет применяться только по отношению к данным, добавляемым или модифицируемым после его создания.
• NOT NULL: NULL-значения в столбцах запрещены.

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

ALTER TABLE Пример 6

Запрещение ограничений:

ALTER TABLE t1 DISABLE PRIMARY KEY;
ALTER TABLE t1 DISABLE UNIQUE (p_name);

ALTER TABLE Пример 7

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

ALTER TABLE t2 DISABLE PRIMARY KEY;

Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)

Для удаления первичного ключа при наличии зависящих от него внешних ключей в команде ALTER TABLE DISABLE <ограничения> обязательна конструкция CASCADE:

ALTER TABLE t2 DISABLE PRIMARY KEY CASCADE;

ALTER TABLE Пример 8

Запрещенное ограничение разрешается следующим образом:

ALTER TABLE t1 ENABLE PRIMARY KEY;

ALTER TABLE t1 ENABLE UNIQUE (p_name);

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

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

ALTER TABLE t1 DROP UNIQUE (p_name);

CREATE SYNONYM

Объекты базы данных принадлежат тем пользователям, которые их создали, и доступны лишь в схеме конкретного пользователя, если только пользователь явным образом не предоставил право на доступ к объектам другим пользователям или ролям, назначенным другим пользователям. Но даже предоставив полномочия на использование объекта, пользователь должен отдавать себе отчет в том, что владение схемой ограничивает доступ к объектам данных Oracle. Допустим, что таблица STUDENT существует в схеме пользователя STUD, а пользователь USER1 пытается обратиться к этой таблице:

SELECT * FROM student  WHERE kod_stud = 256;

В этом случае появляется следующее сообщение

SELECT * FROM student

*

ORA-00942: table or view does not exist

(таблица или представление не существует)

Oracle не возвращает данные, связанные с KOD_STUD= 256, а сообщает пользователю о том, что объект не существует. Причина, по которой USER1 не может видеть таб­лицу в схеме STUD, заключается в том, что пользователь USER1 не обратился к таблице как к находящейся в схеме STUD. Покажем, как оператор select может быть успешно выполнен:

SELECT * FROM stud.student WHERE kod_stud = 256;

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

Синоним — это альтерна­тивный способ обращения к существующей таблице со стороны пользователей. Синонимы позволяют обращаться к объекту базы данных или по другому имени, или без обязательной ссылки на владельца объекта. Однако синоним не изменяет харак­теристики описания таблицы. Таким образом, с помощью синонимов пользователи могут обращаться к таблице, не предваряя ее имени именем владельца.

Синонимы могут быть общими или частными. К частному (private) синониму может обращаться только тот пользователь, который создал его и владеет им. Если же синоним общий (public), он доступен любому пользователю базы данных.  Частный и общий синонимы создаются с помощью команд  CREATE SYNONYM и  CREATE PUBLIC SYNONYM. Причем на создание общих синонимов нужно иметь специальную привилегию.

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

Пример: Пользователь STUD может создать общий синоним для таблицы STUDENT.

— Выполняется пользователем STUD

CREATE PUBLIC SYNONYM student FOR stud.student;

После создания этого синонима пользователь USER1 может обратиться к таблице с его помощью:

—Выполняется пользователем USER1

SELECT * FROM student WHERE kod_stud = 256;

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

Пример: В следующем программном блоке пользователь USER1 получает тот же результат, что и раньше, но с помощью частного синонима.

—Выполняется пользователем USER1

CREATE SYNONYM student FOR stud.student;

—Выполняется пользователем USER1

SELECT * FROM student WHERE kod_stud = 256;

Для удаления синонимов используется команда DROP SYNONYM:

DROP SYNONYM student;

DROP PUBLIC SYNONYM student;

CREATE SEQUENCE

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

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

Последовательность создается командой CREATE SEQUENCE.

CREATE SEQUENCE

Синтаксис команды CREATE SEQUENCE

Синтаксис команды CREATE SEQUENCE

Основные ключевые слова и параметры CREATE SEQUENCE:

• schema —схема, в которой создается последовательность. Если schema опущена, Oracle создает последовательность в схеме пользователя.
• sequence — имя создаваемой последовательности
• start with — позволяет создателю последовательности указать первое генерируемое ею значение. После создания последовательность генерирует указанное в  start with значение при первой ссылке на ее виртуальный столбец NEXTVAL
• increment by n — определяет приращение последовательности при каждой ссылке на виртуальный столбец NEXVAL. Если значение не указано явно, по умолчанию устанавливается 1. Для возрастающих последовательностей устанавливается положительное n, для убывающих, или последовательностей с обратным отсчетом — отрицательное
• minvalue — определяет минимальное значение, создаваемое последовательностью. Если оно не указано, Oracle применяет значение по умолчанию NOMINVALUE
• nominvalue — указывает, что минимальное значение равно 1, если последовательность возрастает, или -10²⁶, если последовательность убывает
• maxvalue — определяет максимальное значение, создаваемое последовательностью. Если оно не указано, Oracle применяет значение по умолчанию NOMAXVALUE
• nomaxvalue — указывает, что максимальное значение равно 10²⁷, если последовательность возрастает, или -1, если последовательность убывает. По умолчанию принимается NOMAXVALUE
• cycle — позволяет последовательности повторно использовать созданные значения при достижении MAXVALUE или MINVALUE. Т.е. последовательность будет продолжать генерировать значения после достижения   своего максимума или минимума. Возрастающая последовательность после достижения своего максимума генерирует свой минимум. Убывающая последовательность после достижения своего минимума генерирует свой максимум. Если циклический режим нежелателен или не установлен явным образом, Oracle применяет значение по умолчанию – NOCYCLE. Указывать CYCLEвместе с NOMAXVALUE или NOMINVALUE нельзя. Если нужна циклическая последовательность, необходимо указать MAXVALUE для возрастающей последовательности или MINVALUE – для убывающей
• nocycle — указывает, что последовательность не может продолжать генерировать значения после достижения своего максимума или минимума
• cache n — указывает, сколько значений последовательности ORACLE распределяет заранее и поддерживает в памяти для быстрого доступа. Минимальное значение этого параметра равно 2. Для циклических последовательностей это значение должно быть меньше, чем количество значений в цикле. Если кэширование нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию – 20 значений.
• order — гарантирует, что номера последовательности генерируются в порядке запросов. Эта опция может использоваться, к примеру, когда номера последовательности предстают в качестве отметок времени. Гарантирование порядка обычно не существенно для тех последовательностей, которые используются для генерации первичных ключей. Если упорядочение нежелательно или не установлено явным образом, Oracle применяет значение по умолчанию NOORDER
• noorder — не гарантирует, что номера последовательности генерируются в порядке запросов

Пример 1 CREATE SEQUENCE
Создание последовательности sequence_1.s Первое обращение к этой последовательности возвратит 1. Второе обращение возвратит 11. Каждое следующее обращение возвратит значение, на 10 большее предыдущего:

• CREATE SEQUENCE sequence_1 INCREMENT BY 10;

Пример 2 CREATE SEQUENCE
Создание последовательности sequence_2. Последовательность убывающая, циклическая, при достижении нуля последовательность вновь обращается к старшему числу. Такой последовательностью удобно пользоваться в тех программах, где до наступления некоторого события должен быть выполнен обратный отсчет:

CREATE SEQUENCE sequence_2
START WITH 20
INCREMENT BY –1
MAXVALUE 20
MINVALUE 0
CYCLE
ORDER
CACHE 2;

После создания последовательности к ней можно обращаться через псевдостолбцы CURRVAL (возвращает текущее значение последовательности) и NEXTVAL (выполняет приращение последовательности и возвращает ее следующее значение). Текущее и следующее значения последовательности пользователи базы данных получают, выполняя команду SELECT. Последовательности – не таблицы, а простые объекты, генерирующие целые числа с помощью виртуальных столбцов, поэтому нужна общедоступная таблица словаря данных DUAL, из которой будут извлекаться данные виртуальных столбцов.

Первое обращение к NEXTVAL возвращает начальное значение последовательности. Последующие обращения к NEXTVAL изменяют значение последовательности на приращение, которое было определено, и возвращают новое значение. Любое обращение к CURRVAL всегда возвращает текущее значение последовательности, а именно, то значение, которое было возвращено последним обращением к NEXTVAL. Прежде чем обращаться к CURRVAL в текущем сеансе работы, необходимо хотя бы один раз выполнить обращение к NEXTVAL.

В одном предложении SQL приращение последовательности может быть выполнено только один раз. Если предложение содержит несколько обращений к NEXTVAL для одной и той же последовательности, то ORACLE наращивает последовательность один раз, и возвращает одно и то же значение для всех вхождений NEXTVAL. Если предложение содержит обращения как к CURRVAL, так и к NEXTVAL, то ORACLE наращивает последовательность и возвращает одно и то же значение как для CURRVAL, так и для NEXTVAL, независимо от того, в каком порядке они встречаются в предложении.

К одной и той же последовательности могут обращаться одновременно несколько пользователей, без какого-либо ожидания или блокировки:

< имя последовательности >.CURRVAL

< имя последовательности >.NEXTVAL

Чтобы обратиться к текущему или следующему значению последовательности, принадлежащей схеме другого пользователя, пользователь должен иметь либо объектную привилегию SELECT по этой последовательности, либо системную привилегию SELECT ANY SEQUENCE, и должен дополнительно квалифицировать эту последовательностьименем содержащей ее схемы:

<имя схемы>.<имя последовательности >.CURRVAL

<имя схемы>.<имя последовательности >.NEXTVAL

Значения CURRVAL и NEXTVAL используются в следующих местах:

• в списке SELECT предложения SELECT
• в фразе VALUES предложения INSERT
• в фразе SET предложения UPDATE.

Нельзя использовать значения CURRVAL и NEXTVAL в следующих местах:

• в подзапросе
• в предложении SELECT с оператором DISTINCT
• в предложении SELECT с фразой GROUP BY или ORDER BY
• в предложении SELECT, объединенном с другим предложением SELECT оператором множеств UNION
• в фразе WHERE предложения SELECT
• в умалчиваемом (DEFAULT) значении столбца в предложении CREATE TABLE или ALTER TABLE
• в условии ограничения CHECK.

SELECT SEQUENCE. Пример 3.
Действие циклической последовательности sequence_2 при достижении ею значения MINVALUE:

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

20

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

19

…..

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

1

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

0

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

20

CREATE SEQUENCE. Пример 4.
В следующем примере SEQUENCE после ссылки на столбец NEXVAL значение CURRVALобновляется так, чтобы соответствовать значению NEXVAL, а предыдущее значение CURRVAL теряется:

SQL> SELECT sequence_2.CURRVAL FROM dual;

CURRVAL

—————

20

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

19

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

18

SQL> SELECT sequence_2.NEXTVAL FROM dual;

NEXTVAL

—————

17

SQL> SELECT sequence_2.CURRVAL FROM dual;

CURRVAL

—————

17

CREATE SEQUENCE. Пример 5.
Ссылка на последовательности при изменении данных:

INSERT INTO emp VALUES (empseq.nextval, ‘LEWIS’, ‘CLERK’, 7902, SYSDATE, 1200, NULL, 20);
UPDATE emp SET deptno = empseq.currval WHERE ename = ‘Jones’

ALTER SEQUENCE. Пример 6.
Любой параметр последовательности можно изменить командой ALTER SEQUENCE. Новое значение вступает в силу немедленно. Все параметры последовательности, не указанные в команде ALTER SEQUENCE, остаются без изменений:

ALTER SEQUENCE sequence_2

INCREMENT BY –4;

Когда последовательность больше не нужна, ее можно удалить. Для этого администратор базы данных или владелец последовательности должен выполнить команду DROP SEQUENCE. В результате виртуальные столбцы последовательности NEXVAL и CURRVAL  — переводятся в разряд неиспользуемых. Но, если последовательность применялась для генерации значений первичных ключей, созданные ею значения останутся в базе данных. Каскадного удаления значений, сгенерированных последовательностью, при ее удалении не происходит.

DROP SEQUENCE. Пример 7.
Удаление последовательности SEQUENCE:

DROP SEQUENCE sequence_2

CREATE VIEW

CREATE VIEW Oracle

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

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

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

Синтаксис команды CREATE VIEW Oracle

Синтаксис команды Create View

Основные ключевые слова и параметры CREATE VIEW Oracle:
OR REPLACE, FORCE, NOFORCE, Sсhema, View, Alias, AS subquery, WITH CHECK OPTION, Constraint

OR REPLACE — пересоздает представление, если оно уже существует. Можно использовать эту опцию для изменения определения представления без того, чтобы удалять его, создавать заново и вновь назначать все объектные привилегии, которые были назначены по данному представлению;

FORCE — создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо чтобы оба названных условия были удовлетворены, прежде чем по данному представлению можно будет выдавать любые предложения SELECT, INSERT, UPDATE или DELETE. По умолчанию применяется параметр NOFORCE;

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

Sсhema — схема, в которой создается представление. Если СХЕМА опущена, то ORACLEсоздает представление в схеме пользователя;

View — ключевое слово view это имя создаваемого представления;

Alias — специфицирует имена для выражений, выбираемых запросом представления. Число алиасов должно совпадать с количеством выражений, выбираемых подзапросом. Алиасы должны удовлетворять правилам именования объектов схем. Алиасы должны быть уникальны внутри представления. Если алиасы опускаются, то ORACLE определяет их по именам или алиасам столбцов в запросе представления. Поэтому использовать алиасы нужно, если запрос представления содержит, помимо имен столбцов, выражения;

AS subquery — идентифицирует столбцы и строки таблиц, на которых базируется представление. Запрос представления может быть любым предложением SELECT, не содержащим фраз ORDER BY или FOR UPDATE. Его список выборки может содержать до 254 выражений;

WITH CHECK OPTION — указывает, что вставки и обновления, которые будут осуществляться через этот запрос, должны давать в результате только такие строки, которые могут быть выбраны запросом этого же представления. Опция CHECK OPTION не может гарантировать этого, если существует подзапрос в запросе этого представления или любого представления, на котором базируется  данное представление. Другими словами, при указании параметра WITH CHECK OPTION пользователь не может вводить, удалять и обновлять информацию таблицы, из которой он не имеет возможности считать информацию через простое представление (создаваемое из данных одной таблицы). Обновляемое представление, использующее несколько связанных таблиц, нельзя создавать с данным параметром;

Constraint — имя, которое присваивается ограничению CHECK OPTION. Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению имя следующего вида:

SYS_Cn , где n — целое, которое делает имя ограничения уникальным внутри базы данных.

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

Если команды обновления DML (INSERT, UPDATE, DELETE) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only). Представление является обновляемым, если при его создании учитывались следующие критерии:

• представление должно включать первичный ключ таблицы
• не должно содержать полей, полученных в результате применения функций агрегирования
• не должно содержать DISTINCT, GROUP BY, HAVING в своем определении
• может быть определено на другом представлении, но это представление должно быть обновляемым
• не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей

SQL CREATE VIEW. Пример 1.
CREATE VIEW Oracle.
Простое представление, которое создается из данных одной таблицы:

CREATE VIEW London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;

SQL CREATE VIEW. Пример 2.
CREATE VIEW Oracle.
При создании представления можно можно задать новые имена полей:

CREATE VIEW Rating_view(rating,number) AS SELECT rating, COUNT(*) FROMCustomers GROUP BY rating;

SQL CREATE VIEW. Пример 3.
CREATE VIEW Oracle.
Представления могут получать информацию из любого количества базовых таблиц:

CREATE VIEW Nameorders AS SELECT onum, amt,a.snum, sname, cname FROMOrders a, Customers b, Salespeople C WHERE a.cnum = b.cnum AND a.snum = c.snum;

SQL CREATE VIEW. Пример 4.
CREATE VIEW Oracle.
При создании представлений можно использовать подзапросы, включая и связанные подзапросы:

CREATE VIEW Sales_view AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX(amt) FROMOrders c WHERE c.odate = b.odate);

SQL CREATE VIEW. Пример 5.
CREATE VIEW Oracle.

CREATE VIEW empl_v04 AS SELECT e.eid, e.sname, e.fname, e.otch, p.pname, d.dname FROM posts p, departments d, employees e WHERE e.did = d.did AND e.pid = p.pid;

Create View

object view clause

XMLType view clause

XMLSchema spec

CREATE USER

SQL, MySQL, Access, Oracle CREATE USER
Схема пользователя – логическая совокупность объектов БД, например таблиц, принадлежащих определенному владельцу.

CREATE USER

Для создания схемы пользователя БД, используемой для доступа к базе данных и работы с ней, предназначена команда CREATE USER.

Для схемы пользователя (CREATE USER) при ее создании могут быть указаны следующие свойства:

• Имя области данных в базе данных, используемой по умолчанию при работе данного пользователя с базой данных;
• Имя области данных в базе данных, используемой для хранения временных данных при работе данного пользователя с базой данных;
• Размеры области данных, выделенной для работы с базой данных данному пользователю;
• Профили пользователя, определяющие ограничения на использование ресурсов базы данных.

Условия, необходимые для использования команды (CREATE USER):

• Пользователь, создающий другого пользователя, должен иметь системную привилегию CREATE USER;
• Чтобы указать имя области данных, используемой для хранения временных данных, или используемой по умолчанию, необходимо, чтобы уровень в СУБД создающего пользователя был старше уровня создания области данных;
• Чтобы назначить создаваемой схеме пользователя некоторый профиль пользователя, необходимо, чтобы уровень в СУБД создающего пользователя был старше уровня создания данного профиля пользователя.

Синтаксис команды CREATE USER

Синтаксис команды Create User

Ключевые слова и выражения команды CREATE USER

Слова и выражения CREATE USER	Описание
user	Является именем пользователя (USER), который будет создан.
IDENTIFIED	Определяет способ доступа к Oracle: BY password — доступ определяется по вводимому паролю (password); пароль должен соответствовать правилам именования объектов в СУБД. EXTERNALLY — доступ определяется именем операционной системы устанавливаемого соединения
DEFAULT TABLESPACE	Устанавливает имя области данных, в которой по умолчанию бу­дут создаваться объекты пользователя. Если имя области данных не указано, то объекты пользователя по умолчанию создаются в области данных SYSTEM
TEMPORARY TABLESPACE	Устанавливает имя областиданных для временных сегментов пользователя. Если имя области данных не указано, то времен­ные сегменты создаются в области данных SYSTEM.
QUOTA	Позволяет пользователю использовать область (области) дан­ных, а также указать (необязательно) размер используемого про­странства в байтах, килобайтах(К) или мегабайтах(М). Возможно указание различного значения QUOTА для нескольких областей данных. Ключевое слово UNLIMITED используется при предос­тавлении неограниченного права на использование областей дан­ных.
PROFILE	Определяет профиль пользователя, определяющий ограничения на использование ресурсов базы данных. По умолчанию пользо­вателю назначается профиль DEFAULT

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

Oracle CREATE USER Пример 1

CREATE USER st IDENTIFIED BY st

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

QUOTA 150K ON users

QUOTA UNLIMITED ON temp;

Созданный пользователь обладает следующими характеристиками:

Имя – st

Пароль – st

Область данных – users

Размер области данных — 150 килобайт

Область временных данных – temp

Размер области временных данных  — неограниченное использование

CREATE USER Пример 2

CREATE USER st IDENTIFIED BY st DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 3000K ON users QUOTA UNLIMITED ON temp;

CREATE USER Пример 3

CREATE USER user_name IDENTIFIED BY user_pass DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 300K ON users QUOTA UNLIMITED ON temp;

Create Table

Create Table SQL

Команда Create Table SQL предназначена для описания структуры таблицы. Команда SQL Create Table создает пустую таблицу (без строк).

Если вы знакомы с SQL Create Table, то наверняка знаете о пунктах, которые мы перечислим ниже, однако если всё-таки вы что-то подзабыли, то милости просим – как говорится, повторение мать учения. Итак, Create Table MySQL, прежде всего, поддерживает опцию определения локальных столбцов, которые, в свою очередь, определяют тип данных, а также имя данных в столбце. Также определяется опция вычисляемых столбцов, вычисление происходит в тех случаях, когда организуется доступ к таблице. Что касается столбцов, которые основаны на доменах, то для них определение может включать каждое значение по умолчанию. Оно также определяет порядок сортировки. В связи с этим определение домена отменяется автоматически. Есть ряд описаний, которые включают предложение CHARACTER SET, определяя кодировку, присущую одному столбцу. При неиспользовании описаний, кодировка остаётся прежней (базовой). Стоит отметить, что текущие столбцы никак не меняются. Что же касается Create Table Oracle, то оно определяет фрагментацию таблицы.

CREATE TABLE MySQL

Create Table. Пример №1.

CREATE TABLE Persons (
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Create Table. Пример №2.

CREATE TABLE `blocklist` (
`email` text NOT NULL,
`login` text NOT NULL,
`remote_addr` text NOT NULL,
`browser` text NOT NULL,
`payment_wmid` text NOT NULL,
`payment_account` text NOT NULL,
`payment_raccount` text NOT NULL
);

Oracle Create Table

Синтаксис команды Create Table

Синтаксис команды CREATE TABLE

Определение ограничений целостности таблицы SQL. Чтобы предохранить поле от разрешения в нем пустых (NULL) указателей для столбца задается ограничение NOT NULL.

Create Table. Пример №3.

CREATE TABLE Student (
Kod_stud integer NOT NULL,
Fam char (30) NOT NULL ,
Adres char (50),
Ball decimal
);

При создании, с помощью CREATE TABLE, любому столбцу с ограничением NOT NULL должно быть установлено значение в каждом предложении INSERT, воздействующем на таблицу.