Язык SQL Последовательности Представления Индексы

Последовательность Последовательность – это объект базы данных, предназначенный для генерации последовательных целых чисел. SEQUENCE

Создание CREATE SEQUENCE имя [INCREMENT BY n] [START WITH n] [CACHE n | NOCACHE];

Пример CREATE SEQUENCE seq1; CREATE SEQUENCE seq2 INCREMENT BY -3 START WITH 25 CACHE 5;

Последовательность Иногда бывают zЦиклические (CYCLED) zС мин и макс значениями (MINVALUE, MAXVALUE) Данные хранятся: zUSER_SEQUENCES (ORACLE) zINFORMATION_SCHEMA.SEQUENCES (H2)

Использование zCURVAL zNEXTVAL Можно: z в списке SELECT команды SELECT; zв предложении VALUES команды INSERT; zв предложении SET команды UPDATE.

Использование CURVAL, NEXTVAL НЕЛЬЗЯ: z в подзапросе; z в запросе к представлению; z SELECT с ключевым словом DISTINCT; z SELECT с предложением GROUP BY, HAVING или ORDER BY; z WHERE команды SELECT; z в (DEFAULT) значении столбца в предложении CREATE TABLE или ALTER TABLE; zв условии ограничения CHECK.

Пример SELECT seq2.CURRVAL FROM dual; Ошибка или предыдущее значение (СУБД !?!?) SELECT seq2.NEXTVAL FROM dual; В следующий раз вернет 22.

Пример В таблице s_emp все значения столбца id обновить, используя последовательность seq1. UPDATE s_emp SET id=seq1.nextval;

Каждый раз или один раз ??? SELECT seq1.CURRVAL, seq1.NEXTVAL, seq1.NEXTVAL, seq1.NEXTVAL*3, seq1.CURRVAL*3, id FROM s_region; SELECT seq1.CURRVAL, seq1.NEXTVAL, seq1.NEXTVAL, seq1.NEXTVAL*3, seq1.CURRVAL*3, id FROM s_region; (5 строки, 0 ms) N1 N2 N3 N4 N5 ID

Изменение ALTER SEQUENCE имя [RESTART WITH long] [INCREMENT BY n]

Пример: Созданную ранее последовательность seq2 нужно изменить таким образом, чтобы следующий элемент был 100 а интервал –4. ALTER SEQUENCE seq2 RESTART WITH 100 INCREMENT BY -4; Проверим: SELECT seq2.NEXTVAL FROM dual;

Удаление DROP SEQUENCE имя; Пример: DROP SEQUENCE seq2;

Индексы zИндекс – объект базы данных, с помощью которого можно ускорить выполнение некоторых запросов. INDEX

Создание индекса zиндекс в Вашей схеме и индексируется таблица Вашей схемы привилегия CREATE INDEX. z индекс в Вашей схеме, но индексируется таблица схемы другого пользователя, CREATE INDEX и INDEX по индексируемой таблице. zиндексы в своей схеме, индексируя при этом таблицы любых пользователей CREATE ANY INDEX. zиндекс в другой схеме CREATE ANY INDEX.

Создание индекса CREATE INDEX индекс ON таблица (столбец, …); CREATE INDEX s_emp_dept_id_idx ON s_emp (dept_id);

Когда и зачем? z Столбец часто используется в предложении WHERE или условии соединения. z Столбец имеет широкий диапазон значений. zСтолбец содержит большое количество неопределенных значений. zТаблица большого размера.

Когда не стОит? z Таблица небольшого размера. z Столбцы не очень часто используются как условие в запросе. z Таблица часто обновляется.

Удаление индекса DROP INDEX имя_индекса; DROP INDEX s_emp_dept_id_idx;

Представления Представление (view – представление, обзор, вид) – логическая таблица, созданная на основе реальных таблиц или других представлений, называемых базовыми таблицами представления. VIEW

Создание CREATE [FORCE | NOFORCE] VIEW представление [(псевдоним, …)] AS подзапрос;

Пример CREATE VIEW dept_45 AS (SELECT * FROM s_emp WHERE dept_id=45);

Пример CREATE VIEW dept_sal (name, minsal, maxsal, avgsal) AS (SELECT d.name, MIN(e.salary), MAX(e.salary), AVG(e.salary) FROM s_emp e, s_dept d WHERE e.dept_id=d.id GROUP BY d.name); SELECT * FROM dept_sal;

Создать представление, содержащее номер, фамилию, имя, зарплату и номер начальника для каждого подчиненного сотрудника 1. CREATE VIEW man_1 AS (SELECT id, last_name, first_name, salary, manager_id FROM s_emp WHERE manager_id=1);

Изменение данных Можно удалять и обновлять строки, если нет: z условий соединения; z групповых функций и предложения GROUP BY; zключевого слова DISTINCT. Можно добавлять если нет NOT NULL*

Всем сотрудникам, имеющим зарплату 1400 через представление man_1 увеличить ее до UPDATE man_1 SET salary=1800 WHERE salary=1400;

Служащих с зарплатой 1450 посредством представления man_1 перевести к начальнику 2. UPDATE man_1 SET manager_id=2 WHERE salary=1450;

Удаление представления DROP VIEW имя; Удалить представление man_1: DROP VIEW man_1;