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

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

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

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

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

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

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

7 Использование 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.

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

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

10 Каждый раз или один раз ??? 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

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

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

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

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

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

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

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

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

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

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

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

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

23 Пример 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;

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

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

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

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

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