SQL — урок 3. Практика

Базы данных SQL — урок 3. Практика

Помечено: ,

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

      Создание таблицы

      Для создания простой таблицы используется команда CREATE TABLE. Ниже приведен пример для Oracle, для других тип number надо заменить на integer или double.

      CREATE TABLE tblname (
        id number,      
        num number, -- число
        -- фиксированная строка
        fstr char(45),  
        -- строка с переменным размером, но не больше 45
        str varchar(45), 
        birth date  -- дата
      );

      значения полей по умолчанию

      После типа столбца можно задать дополнительные свойства столбца как значение по
      умолчанию и указать, возможно ли значение null. Для одних БД порядок принципиален
      (Oracle) для других нет (MySQL).

      CREATE TABLE tblname (
        id number,      
        num number DEFAULT 3.14 NOT NULL, 
        fstr char(45),  
        str varchar(45), 
        birth date    
      );

      ключи и ограничения

      Обычно после перечисления столбцов в определении таблицы указывают дополнительные
      ограничения, например, ключи. Естественно, таблица, на которую ссылается внешний ключ
      должна существовать, а тип внешнего ключа должен совпадать с типом столбца на который
      ссылается. Например, для MySQL, если столбец внешнего ключа был определен как
      fk_key1 INTEGER, а первичный ключ таблицы tb как id INTEGER UNSIGNED, то таблица просто
      не будет создана. В некоторых базах ограничения можно задавать как свойство столбца, но
      на мой взгляд слишком длинно получается.

      -- правильно для большинства реализаций SQL
      CREATE TABLE tblname (
        id integer, 
        fstr char(45), 
        str varchar(45),
        -- в MySQL precision можно опустить
        num double precision, 
        birth date,
        fk_key1 integer, 
      
      -- столбец id используем в качестве первичного ключа
        CONSTRAINT pk_tblname PRIMARY KEY (id), 
      
      -- уникальный ключ, т.е. значения в столбце fstr
      -- будут уникальны
        CONSTRAINT uk_tblname1 UNIQUE (fstr), 
      
      -- ограничение на значение
        CONSTRAINT tblname_numcheck CHECK (num<100.34), 
      
      -- внешний ключ, в скобка столбец нашей таблицы
      -- tb таблица, id столбец на которые ссылается
      -- внешний ключ
        CONSTRAINT fk_tblname FOREIGN KEY (fk_key1) 
        REFERENCES tb(id) 
      );
      
      -- именовать ограничения не обязательно
      -- в этом случае СУБД сгенерирует имена сама
      CREATE TABLE tblname (
        id integer, 
        fstr char(45), 
        str varchar(45),
        num double,
        birth date,
        fk_key1 integer, 
      
        PRIMARY KEY (id), 
        UNIQUE (fstr), 
        CHECK (num<100.34), 
        FOREIGN KEY (fk_key1) 
        REFERENCES tb(id) 
      );

      настройка внешнего ключа

      Что произойдет, если послана команда на удаление записи в родительской таблице на
      которую ссылается внешний ключ? По умолчанию СУБД не позволит удалить такую запись и
      выведет сообщение об ошибке. Однако есть еще как минимум два возможных варианта, которые
      задаются при определении внешнего ключа. Первый, это установить значение внешнего ключа
      в null, естественно при определении столбца не должно быть указано NOT NULL. А второй
      вариант, удалить все записи в дочерней таблице, которые ссылаются на удаляемую запись
      в родительской таблице. Такое удаление называется каскадным.

      -- определение ключа, с установкой в null 
      -- при удалении записи на которую происходит ссылка
      FOREIGN KEY (fk_key1) 
      REFERENCES tb(id) 
      ON DELETE SET NULL
      
      -- определение ключа с каскадным удалением
      FOREIGN KEY (fk_key1) 
      REFERENCES tb(id) 
      ON DELETE CASCADE

      создание по выборке

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

      -- сохраняем данные tblname в tblname1
      create table tblname1 as select * from tblname

      дополнительные параметры таблицы

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

      -- пример для Oracle
      CREATE TABLE tblname (
      ...
      )
      PCTFREE 10
      PCTUSED 40
      TABLESPACE users
      STORAGE 
       (INITIAL 50K
        NEXT 50K
        MAXEXTENTS 10
        PCTINCREASE 25 
       );
      
      --  пример для MySQL
      CREATE TABLE tblname (
      ...
      )
      ENGINE = InnoDB;

      Изменение, удаление таблицы

      Команда ALTER позволяет изменить объект, команда DROP удалить объект из БД.

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

      -- переименование таблицы
      ALTER TABLE tblname RENAME TO tblname3;
      
      -- переименование столбца oldname в newname
      ALTER TABLE tblname RENAME oldname TO newname;

      добавление/удаление столбца

      Во время развития БД иногда требуется добавить новый или удалить старый столбец в той или иной таблице. В приведенном ниже примере по стандарту ключевое слово COLUMN не обязательно, а в Oracle при добавлении столбца оно запрещено вовсе. В MySQL при добавлении столбца можно указать местоположение ключевыми словами FIRST или AFTER имя_столбца.

      -- добавление
      ALTER TABLE tblname ADD COLUMN num1 integer; 
      
      -- удаление
      ALTER TABLE tblname DROP COLUMN num1; 

      первичный ключ

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

      -- добавление
      ALTER TABLE tblname 
          ADD CONSTRAINT pk_tblname PRIMARY KEY (id);  
      -- удаление
      ALTER TABLE tblname 
          DROP PRIMARY KEY; 
      
      -- с удалением всех записей в других таблицах,
      -- ссылающихся на этот ключ
      ALTER TABLE tblname DROP PRIMARY KEY CASCADE;

      уникальный ключ

      -- добавление
      ALTER TABLE tblname 
          ADD CONSTRAINT uk_tblname2 UNIQUE (birth) 
      
      --удаление
      ALTER TABLE tblname DROP CONSTRAINT uk_tblname2;
      
      -- удаление для MySQL
      ALTER TABLE tblname DROP KEY uk_tblname2; 

      внешний ключ

      MySQL автоматически создает одноименный индекс для внешнего ключа,
      но при удалении ключа индекс не удаляет.

      -- добавление 
      ALTER TABLE tblname  
        ADD CONSTRAINT fk_tblname2 FOREIGN KEY(num1)
        REFERENCES tb (id); 
      
      -- удаление 
      ALTER TABLE tblname DROP CONSTRAINT fk_tblname2; 
      
      -- удаление для MySQL
      ALTER TABLE tblname DROP FOREIGN KEY fk_tblname2;
      ALTER TABLE tblname DROP INDEX fk_tblname2;
      
      -- Ниже приведен пример добавление удаление ограничения на значение. 
      ALTER TABLE tblname -- добавление
         ADD CONSTRAINT chk_tblname1 CHECK (num>10);
      
      -- удаление, не для MySQL
      ALTER TABLE tblname 
         DROP CONSTRAINT chk_tblname1; 
      
      -- Для уничтожения таблицы служит команда DROP TABLE. 
      -- если уверены в уничтожении таблицы
      DROP TABLE tblname; 

      безошибочное удаление таблиц

      В выше приведенном примере, если уничтожаемая таблица не существует, то появится
      сообщение об ошибке. Некоторые СУБД поддерживают расширенный синтаксис, позволяющий
      избежать данную ошибку. Обычно это используется в скриптах, где перед созданием новой
      таблицы на всякий случай уничтожают таблицу с таким же именем.

      -- если таблица есть, она уничтожится,
      -- иначе ничего не произойдет
      DROP TABLE IF EXISTS tblname;

      Добавление записей

      Для добавления записей в таблицу используется команда INSERT INTO. Пусть существует следующая таблица.

      CREATE TABLE tblname (
        id integer default 0,
        num double precision default 3.14,
        CONSTRAINT pk_tblname PRIMARY KEY (id)
      );

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

      -- вставка записи
      insert into tblname(id,num) values(2,2.9); 
      -- применяем внесенные в БД изменения
      commit; 
      -- вывод всех данных таблицы для проверки
      select * from tblname; 

      Если задаются значения всех столбцов, то их не обязательно перечислять после имени
      таблицы.

      INSERT INTO tblname VALUES(5,2.9);

      значения по умолчанию

      Если для новой записи в качестве значения поля достаточно значения по умолчанию,
      то его можно не указывать в списке столбцов, либо в качестве значения указать default.

      -- не указываем поле num в списке столбцов
      INSERT INTO tblname(id) VALUES(3); 
      
      -- используем default
      INSERT INTO tblname(id,num) VALUES(4,DEFAULT);
      
      -- если бы определение столбца в MySQL было бы таким
      -- id integer default 0 AUTO_INCREMENT,
      -- или мы написали тригер генерирующий 
      -- значения первичного ключа в Oracle
      INSERT INTO tblname(num) VALUES(DEFAULT);
      
      -- короткая запись предыдущей команды для MySQL
      INSERT INTO tblname VALUES();

      подзапросы

      В качестве значений можно использовать подзапрос. Как и в предыдущих примерах, число столбцов в выборке должно соответствовать перечислению столбцов в команде insert into.

      -- число столбцов в выборке соответствует 
      -- числу столбцов в таблице
      INSERT INTO tblname SELECT ...
      
      -- в выборке должен быть один столбец
      INSERT INTO tblname(id) SELECT ...

      вставка по условию

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

      -- вставка в таблицы tbl1, tbl2, tbl3
      -- по условию
      INSERT ALL
      -- в таблицу tbl1 если поле val в выборке меньше 100
         WHEN val > 100 THEN 
            INTO tbl1
         WHEN val > 100 AND val < 200 THEN
            INTO tbl2
         ELSE
            INTO tbl3
         SELECT ..., my_val AS val, ...;

      Обновление записей

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

      -- тестовая таблица
      create table tblname (
      id integer not null,
      num  double precision default 0.0,
      num2 double precision default 5.5,
      
      constraint pk_tblname primary key (id)
      );
      
      -- тестовые записи
      insert into tblname(id,num) values(1,1.0);
      insert into tblname(id,num) values(2,2.0);
      insert into tblname(id,num) values(3,3.0);
      insert into tblname(id,num) values(4,4.0);
      insert into tblname(id,num) values(5,5.0);
      insert into tblname(id,num) values(6,6.0);
      commit;
      
      -- просмотр результата вставки
      select * from tblname 

      Ниже приведен пример обновления полей num и num2 всех записей таблицы, у которых значение первичного ключа (т.е. поле id) четное число.

      UPDATE tblname SET 
         -- задаем значение по умолчанию
         num = default,
         num2 = num2*2
      WHERE id%2=0; -- для Oracle: WHERE mod(id,2)=0;
      
      commit;
      
      -- просмотр результата обновления
      select * from tblname order by id

      обновление подзапросом

      В качестве значения можно использовать подзапрос с одним столбцом.
      Ниже приведен пример обновления нечетных записей. В качестве подзапроса для простоты
      используется выборка констант.

      UPDATE tblname SET 
         num = (select 2 ) 
      WHERE id%2!=0;
      
      -- для Oracle
      UPDATE tblname SET 
         num = (select 2 from dual)
      WHERE mod(id,2)!=0;

      обновление по данным другой таблицы

      Очень часто необходимо обновить поля данными из других таблиц.
      Для этого создадим еще одну таблицу с данными tblname1 таким же образом как tblname.
      Новая таблица будет служить источником данных.
      В качестве соответствия между записями двух таблиц выберем условие, при котором первичный ключ первой таблицы больше первичного ключа новой таблицы на 1.
      Также для удобства в запросах используем локальные синонимы (алиасы) для имен таблиц.

      -- для Oracle
      -- пользуемся тем, что в этой СУБД можно 
      -- обновить группу столбцов одним запросом.
      -- предикат exist предотвращает изменение 
      -- первой записи значениями null 
      UPDATE tblname a SET
         (num, num2)= 
             (
             select num,num2 
             from tblname1 b 
             where a.id=b.id+1
             )
      WHERE exists (
                    select 1 
                    from tblname1 b 
                    where a.id=b.id+1
                    );
      
      -- для PostgreSQL
      UPDATE tblname as a SET
         num=b.num,
         num2=b.num2
      FROM tblname1 as b
      WHERE a.id=b.id+1;
      
      -- для MySQL
      UPDATE tblname a, tblname1 b SET
      a.num=b.num,
      a.num2=b.num2
      WHERE a.id=b.id+1;

      Oracle позволяет обновить одну таблицу, входящую в выборку,
      если она жестко связана с другими таблицами выборки первичными или уникальными ключами.
      Если в следующем примере попробовать указать условие a.id=b.id+1 для решения предыдущей задачи,
      то получим ошибку.

      UPDATE 
         (
         select a.*, b.num srcnum, b.num2 srcnum2 
         from tblname a,  tblname1 b 
         where  a.id=b.id
         ) a
      SET 
         a.num=a.srcnum,
         a.num2=a.srcnum2;

      Удаление записей

      Для удаления записей из таблицы служит команда DELETE FROM. В конструкции WHERE
      указывается условие, по которому отбираются удаляемые записи.

      -- удалить определенные записи (четные)
      DELETE FROM tblname
         WHERE id%2 = 0; -- для Oracle: WHERE mod(id,2)=0;
         
      -- удаление всех записей таблицы
      DELETE FROM tblname;

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