SQL — урок 5. Ключи, индексы, представления

Базы данных SQL — урок 5. Ключи, индексы, представления

Помечено: ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 1 год, 8 месяцев назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #5195
      @admin

      Обеспечение уникальности первичного ключа

      В некоторых СУБД как Oracle и PostgreSQL для обеспечения уникальности первичного ключа можно воспользоваться объектом последовательность, генерирующий последовательность целых чисел. По умолчанию 1027 максимальное значение для восходящей последовательности в Oracle и 1063 в PostgreSQL. Так что одной последовательности вполне хватит на несколько таблиц. В Oracle последовательность имеет два псевдостолбца:

      • CURRVAL — текущее значение последовательности;
      • NEXTVAL — увеличивает текущее значение на 1, а затем возвращает его.

      В PostgreSQL для этой цели используются одноименные функции.
      Кроме этого есть функция setval, позволяющая задать текущее значение последовательности.
      Это можно использовать, например, при нумерации записей в выборке.

      -- создаем последовательность
      -- с параметрами по умолчанию
      CREATE SEQUENCE seq;
      
      -- создаем тестовую таблицу
      create table tblseq(
      id integer not null,
      num  double precision, 
      constraint pk_tblseq primary key (id)
      );
      
      -- вставляем данных для Oracle
      insert into tblseq values(seq.nextval,3.14);
      insert into tblseq values(seq.nextval,6.14);
      insert into tblseq values(seq.nextval,7.14);
      commit;
      
      -- вставляем данные для PostgreSQL
      insert into tblseq values(nextval('seq'),3.14);
      insert into tblseq values(nextval('seq'),6.14);
      insert into tblseq values(nextval('seq'),7.14);
      commit;
      
      -- проверяем 
      select * from tblseq;
      
      -- уничтожение последовательности
      DROP SEQUENCE seq;

      Чтобы сделать присваивание значения первичного ключа автоматическим, в PostgreSQL
      надо подкорректировать значение столбца по умолчанию в определении таблицы,
      а для Oracle написать тригер.

      -- для PostgreSQL
      create table tblseq(
      id integer not null DEFAULT nextval('seq'),
      num  double precision, 
      constraint pk_tblseq primary key (id)
      );
      
      -- для Oracle, создаем тригер tblseq_autoid
      create or replace trigger tblseq_autoid
        before insert on tblseq  
        for each row
      declare
      begin
        select seq.nextval into  :new.id from dual;
      end tblseq_autoid;
      
      -- теперь значение id будет устанавливаться
      -- автоматически следующим значением 
      -- последовательности seq
      insert into tblseq(num) values(3.14);
      insert into tblseq(num) values(6.14);
      insert into tblseq(num) values(7.14);
      commit;

      столбцы с автоинкрементом

      Как в MS Access введен специальный тип idenity или counter, так в MySQL и PostgreSQL можно использовать тип serial для автоматического создания последовательности связанной со столбцом таблицы. В MySQL также можно воспользоваться атрибутом столбца AUTO_INCREMENT в определении таблицы. Фактически, тип serial эквивалент определения BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. В PostgreSQL для последовательности типа serial используется целое в 4 байта (serial4). Если это кажется малым, можно воспользоваться типом serial8.

      -- для MySQL
      CREATE TABLE tblname (
        id integer unsigned AUTO_INCREMENT,
      ...
      
      -- для MySQL и PostgreSQL
      create table tblseq(
      id serial,
      num  double precision,
      constraint pk_tblseq primary key (id)
      );
      
      -- значение id будет устанавливаться автоматически
      -- следующим значением
      insert into tblseq(num) values(3.14);
      insert into tblseq(num) values(6.14);
      insert into tblseq(num) values(7.14);
      commit;

      При разработке клиентских программ необходимо знать значение первичного ключа
      последней вставленной записи для позиционирования на нее. В PostgreSQL для этого есть функция lastval(), возвращающая самое последнее сгенерированное значение для какой-либо последовательности (не важно созданной через SEQUENCE, или через тип serial) в текущей сессии. В MySQL аналогичная функция называется

      LAST_INSERT_ID().

      Индексы

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

      -- пусть есть некая таблица tblname
      -- со столбцом tblcol
      create table tblname(
      ...
      tblcol date;
      ...
      );
      
      -- создаем индекс ind_tblname_d для этой таблицы
      CREATE INDEX ind_tblname_d ON tblname (tblcol);

      В большинстве СУБД позволяется индексация по нескольким столбцам. Первичные и уникальные ключи обычно индексируются автоматически.

      Представления

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

      -- создаем первую таблицу
      create table dicevents(
      id integer,
      s varchar(45),
      
      constraint pk_dicevents primary key(id)
      );
      
      -- вставка данных
      insert into dicevents
          values(1,'пожар');
      insert into dicevents
          values(2,'авария');
      insert into dicevents
          values(3,'взрыв газа');
      
      -- создаем вторую таблицу
      create table evt(
      id integer,
      d date,
      summ double precision,
      evtname integer,
      
      constraint pk_evt primary key(id),
      constraint fk_evt foreign key(evtname)
          references dicevents(id)
      );
      
      -- вставка данных
      insert into evt
          values(1,date '2008-01-02',102000,1);
      insert into evt
          values(2,date '2007-12-31',67000,3);
      insert into evt
          values(3,date '2008-01-05',32020.3,2);
      insert into evt
          values(4,date '2008-01-06',99000,null);

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

      CREATE OR REPLACE VIEW evtlst
      AS -- далее следует запрос
      select t.d as "дата",
             dt.s as "событие",
             t.summ as "ущерб"
      from evt t left join dicevents dt on (t.evtname=dt.id)
      order by t.d;
      
      -- названия столбцов можно задать после
      -- имени представления
      -- в MySQL двойные кавычки возможны
      -- только в режиме ANSI SQL
      CREATE OR REPLACE VIEW evtlst
      ("дата","событие","ущерб")
      as
      select t.d, dt.s, t.summ
      from evt t left join dicevents dt on (t.evtname=dt.id)
      order by t.d;

      Выборка из представления не отличается от выборки из обычной таблицы.

      -- выборка из представления
      select * from evtlist;

      Для уничтожения представления используется команда DROP VIEW. Некоторые СУБД поддерживают расширенный синтаксис с IF EXISTS, позволяющий избежать ошибки, если уничтожаемого представления не существует.

      -- уничтожение представления
      DROP VIEW eventlist; 
      
      -- уничтожение представления, если оно
      -- существует
      DROP VIEW IF EXISTS eventlist;

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×