Створення, модифікація і видалення таблиць

Розглянемо основні функції, що виконуються над таблицями.

Створення таблиці виконується за допомогою оператора CREATE, спрощений синтаксис має такий вигляд:

CREATE TABLE ім'я_таблиці

{({ ім'я_стовпця тип_данных [DEFAULT значення]

[обмеження_стовпця] | обмеження_таблицы}

[, {ім'я_стовпця тип_данных [DEFAULT значення]

[ограничения_столбца] | ограничение_таблицы}]...) |

AS підзапит};

Таблиця може бути створена або стандартним чином через опис її компонентів, або в результаті виконання деякого подзапроса. Підзапит – це звичайний запит на вибірку інформації, що реалізується оператором SELECT. При створенні таблиці задаються імена таблиць і стовпців повинні відповідати правилам, предписываемым ідентифікаторів. При цьому природно, що імена, що визначені в таблицях, повинні бути унікальними в схемі користувача, а імена стовпців – в рамках однієї таблиці. Для кожного стовпця вказується тип даних і, якщо необхідно, значення, що вставляється в стовпець за замовчуванням (DEFAULT значення). Важливим елементом при створенні таблиць є завдання обмежень цілісності даних, які дозволяють відслідковувати правильність модифікації наявних даних або вставляються в таблицю нових даних. Обмеження цілісності даних діляться на два типу: обмеження і обмеження стовпця таблиці. Обмеження стовпця дозволяють визначити умови, яким повинні задовольняти значення відповідного стовпця; обмеження цілісності, що накладаються на таблицю, дозволяють перевірити правильність всіх доданих або модифікуються рядків таблиці. Обмеження може бути іменованим або безіменним. Зручніше використовувати іменовані обмеження, оскільки при видачі інформації, пов'язаної з виниклим порушенням одного з обмежень, видається й ім'я цього обмеження, що дуже корисно для виправлення помилок надалі. Завдання обмежень на

стовпець або обмежень на таблицю здійснюється з наступного синтаксису:

[CONSTRAINT ім.’я_обмеження] тип_обмеження

Є такі типи обмежень, що накладаються на один стовпець:

1) PRIMARY KEY – це обмеження вимагає, щоб вводяться в стовпець значення були унікальними і відмінними від порожніх, оскільки вони будуть використовуватися в якості первинного ключа, однозначно ідентифікує запис; первинний ключ визначається з таблиці тільки один раз;

2) UNIQUE – це обмеження вимагає, щоб вводяться в стовпець значення в рамках однієї таблиці були унікальними;

3) NOT NULL – це обмеження вимагає обов'язкової присутності в стовпці деякого значення;

4) CHECK(вираз) – це обмеження дозволяє піддати певної перевірки вставляється в стовпець значення; якщо умови, накладені на вставляються значення, не виконуються, значення в стовпець не поміщаються;

5) REFERENCES – це обмеження дозволяє встановити взаємозв'язок значень даного стовпця зі значеннями іншого стовпця таблиці. Взаємозв'язок забезпечується використанням наступної конструкції:

REFERENCES ім`я_таблиці[(ім`я_стовпця)][ON DELETE CASCADE]

При внесенні значення у стовпці створюваної таблиці система буде автоматично перевіряти наявність аналогічного значення в зазначеному стовпчику зазначеної таблиці. При цьому природно, що для забезпечення однозначності встановлюється взаємозв'язку всі значення, які перебувають у зазначеному стовпчику, на які здійснюється посилання, повинні мати обмеження UNIQUE або PRIMARY KEY. Якщо в якості імені стовпця зазначеної таблиці використовується первинний ключ, то ім'я стовпця можна не вказувати. Таблиця, на чий стовпець посилається інша таблиця, називається головною, а таблиця, яка посилається на неї, – підлеглою. Конструкція ON DELETE CASCADЕ вказує, що при видаленні рядків у головній таблиці автоматично здійснюється видалення відповідних рядків і у підпорядкованій таблиці.

Обмеження на таблицю багато в чому нагадують обмеження стовпця, але при цьому зазвичай використовують, як правило, декілька стовпців. Наприклад, можна задати обмеження PRIMARY KEY, вказавши список імен стовпців, тим самим визначивши складовий первинний ключ. При цьому для стовпців, зазначених у списку, повинні бути задані обмеження UNIQUE і NOT NULL.

Використовуючи наступну форму запису:

FOREIGN KEY (список_імен_стовпців)

REFERENCES ім'я _таблиці(список_імен_стовпців)

[ON DELELTE CASCADE]

можна визначити складовий зовнішній ключ для таблиці. Природно, що в разі складеного ключа перелік стовпців у підпорядкованій таблиці та перелік стовпців в головній таблиці повинні збігатися за кількістю, типом даних і порядку прямування. Конструкція ON DELETE CASCADE дозволяє забезпечити цілісність і несуперечність даних при змінах, які зачіпають значення стовпців головної таблиці є зовнішнім ключем по відношенню до підлеглої таблиці.

Якщо обмеження CHECK зачіпає значення декількох стовпців, пов'язуючи їх в деякий достатньо складний умова, то таке обмеження також зручно визначити як обмеження на таблицю.

Для генерації і вставити стовпець таблиці унікальних значень можна створити спеціально призначений для цих цілей об'єкт – послідовність. Створення послідовності виконується за допомогою оператора CREATE за наступним спрощеним синтаксису:

CREATE SEQUENCE ім'я _послідовності

[START WITH початкове_значення] [INCREMENT BY крок];

В найпростішому випадку генерується послідовність цілих чисел від 1 до 10²⁷ з кроком одиниця. Конструкція INCREMENT BY дозволяє вказати крок зміни значень послідовності. Конструкція START WITH дозволяє задати початкове значення генерованої послідовності, яка при її відсутності встановлюється рівним одиниці. Для вставки в стовпець поточного значення послідовності потрібно вказати имя_последовательности.СURRVAL, а для вставки в стовпець зміненого за правилами формування послідовності наступного значення використовується имя_последовательности.NEXTVAL. Послідовності є самостійними об'єктами БД, одна і та ж послідовність може бути використана для завдання унікальних значень стовпців декількох таблиць; при видаленні або модифікації послідовності значення, створені нею, зберігаються в таблицях БД.

Вставка рядків у таблицю здійснюється за допомогою оператора INSERT, який має наступний синтаксис:

INSERT INTO ім`я_таблиці [(список_стовпців)]

{VALUES (значення1 [, значення2]...) | підзапит}

Якщо список стовпців не вказується, то список значень в конструкції VALUES повинен містити значення для всіх стовпців таблиці, причому порядок їх прямування повинен відповідати порядку їх розташування в рядку. Використання подзапроса дозволяє перенести рядки з деякої таблиці в створювану таблицю. Видалення рядків з таблиці здійснюється за допомогою оператора DELETE, який має наступний синтаксис:

DELETE [FROM] ім'я_таблиці [WHERE умова];

За відсутності ключового слова WHERE з таблиці видаляються всі рядки, але сама таблиця залишається. Модифікація рядків таблиці реалізується з допомогою оператора UPDATE, форма запису якого наведена нижче. При відсутності умови модифікуються всі рядки таблиці для зазначеного списку стовпців.

UPDATE ім'я_таблиці

SET {(имя_столбца1 [, имя_столбца2]...) = (підзапит) |

имя_столбца1=значення1, имя_столбца2={значення2 | (підзапит)}}

[WHERE умова];

Зміна структури таблиці виконується оператором ALTER TABLE, за допомогою якого можна здійснити додавання одного або кількох нових стовпців у таблицю, зміна характеристик у одного або декількох стовпців, додавання обмеження стовпця використання наведені нижче.

Видалення таблиці можна виконати за допомогою наступного оператора:

DROP TABLE ім'я_таблиці [CASCADE CONSTRAINTS];

За наявності конструкції CASCADE CONSTRAINTS разом з видаленням таблиці знищуються обмеження зовнішнього ключа в інших таблицях.

Приклади

Постійно працюючий магазин безпосередньо контактує з видавництвами і має постійний штат або таблиці або видалення обмежень стовпця або таблиці. Приклади його продавців. Директор магазину повинен мати відомості:

1) про надійновші книги: назву книги, прізвище автора, ціна, видавництво, жанр; 2) про розподіл книг серед продавців: назву книги, прізвище продавця, кількість примірників, дата надходження.

• Необхідно створити таблиці: BOOKS, BOOKS_DELIVERY, вказавши всі необхідні обмеження цілісності даних.

Перелік, назви і тип даних стовпців таблиці BOOKS:

--------------------------------------------------------------------------

Код книги CODE_BOOK Number(5) Назва книги TITLE Varchar2(25) ПІБ автора AUTHOR Varchar2(20) Ціна книги PRICE Number(7)

Видавництво PUBLISH_HOUSE Varchar2(15) Жанр GENRE Varchar2(15)

--------------------------------------------------------------------------

Інформація таблиці BOOKS:

Код кн. Назв. книги ПІБ автора Ціна Изд-во Жанр –

--------------------------------------------------------------------------------------------- 1 1 Загибель Богів Перумов Н. 345 Аст Фантастика

2 Козаки Толстой Л. 5568 Нова Роман 3 Лють Перумов Н. 1385 Аст Детектив 4 Дюна Герберт Ф. 2668 Нова Фантастика 5 Загибель Титану Крісті А. 2345 Аст Роман 6 Діти Дюни Герберт Ф. 2500 Аст Фантастика ---------------------------------------------------------------------------------------------- Перелік, назви і тип даних стовпців таблиці BOOKS_DELIVERY: ------------------------------------------------------------------------------------ Код операції CODE_OPERATION Number(10) Код книги CODE_BOOK Number(5) ПІБ продавця SALESMAN Varchar2(20) Кількість одиниць QUANTITY Number(4) Дата поставки DATE_DELIVERY Date ------------------------------------------------------------------------------------ Інформація таблиці BOOKS_DELIVERY: Код опер. Код книги ПІБ Продавця Кількість од. Дата поставки ----------------------------------------------------------------------------------------- 1 1 Іванов В. І. 5 20-01-2006 2 3 Іванов В. І. 5 10-02-2006 3 2 Петров П.п. 4 25-01-2006 4 4 Петров П.п. 4 20-02-2006

5 4 Іванов В. І. 4 20-02-2006 -----------------------------------------------------------------------------------------

Для створення таблиці BOOKS скористаємося оператором CREATE TABLE. Стовпець CODE_BOOK, що містить унікальний код книги, є ключовим, і по відношенню до його значенням встановлюємо обмеження PRIMARY KEY. За значеннями для цього стовпця користувач повинен стежити сам. Обмеження іменується як PK_BOOKS. Оскільки стовпець TITLE не може мати пусте значення, на нього накладаємо обмеження NOT NULL. На значення стовпця PRICE накладаємо обмеження, пов'язане з вартістю книги, вона повинна бути не менше 100 руб. Обмеження отримує ім'я PRICE_BOOKS. Якщо таблиця BOOKS була вже створена раніше, то перед повторним створенням її слід видалити наступним оператором:

DROP TABLE BOOKS;

Наступний оператор CREATE мови SQL створює таблицю BOOKS з необхідними обмеженнями цілісності даних: CREATE TABLE BOOKS (CODE_BOOK NUMBER(5) CONSTRAINT PK_BOOKS PRIMARY KEY, TITLE VARCHAR2(25) CONSTRAINT TITLE_BOOKS NOT NULL, AUTHOR VARCHAR2(20), PRICE NUMBER(7) CONSTRAINT PRICE_BOOKS CHECK(PRICE > 100), PUBLISH_HOUSE VARCHAR2(15), GENRE VARCHAR2(15)); додавання рядків у таблицю BOOKS здійснюється наступною сукупністю операторів: INSERT INTO BOOKS VALUES(1, 'Загибель Богів', 'Перумов Н.', 345, 'Аст', 'Фантастика'); INSERT INTO BOOKS VALUES(2, 'Козаки', 'Толстой Л.', 5568, 'Нова', 'Роман'); INSERT INTO BOOKS VALUES(3, 'Лють', 'Перумов Н.', 1385, 'Аст', 'Детектив'); INSERT INTO BOOKS VALUES(4, 'Дюна', 'Герберт Ф.', 2668, 'Нова', 'Фантастика'); INSERT INTO BOOKS VALUES(5, 'Загибель Титану', 'Крісті А.', 2345, 'Аст', 'Роман'); INSERT INTO BOOKS VALUES(6, 'Діти Дюни', 'Герберт Ф.', 2500, 'Аст', 'Фантастика');

Перегляд введених значень можна виконати за допомогою наступного оператора SQL: SELECT * FROM BOOKS; Для створення таблиці BOOKS_DELIVERY також скористаємося оператором CREATE TABLE. Стовпець CODE_OPERATION, що містить унікальний код операції, є ключовим, і по відношенню до його значенням встановлюємо обмеження PRIMARY KEY. Це обмеження отримує ім'я DELIVERY_PR. Для генерації унікальних значень цього стовпця використовуємо попередньо створену за допомогою оператора CREATE SEQUENCE послідовність CODE_OP: CREATE SEQUENCE CODE_OP; Оскільки стовпець CODE_BOOK повинен містити тільки ті значення, які присутні у відповідному стовпці таблиці BOOKS, необхідно задати відповідне обмеження на значення стовпця CODE_BOOK. Це обмеження можна зробити як обмеженням стовпця, так і обмеженням таблиці, задавши йому ім'я DELIVERY_FK. Конструкція ON DELETE CASCADE забезпечить при видаленні з таблиці BOOKS рядків, що містять значення зовнішнього ключа, автоматичне видалення рядків з таблиці BOOKS_DELIVERY. Для стовпця DATE_DELIVERY значенням за замовчуванням встановлюємо поточну дату (SYSDATE). Якщо таблиця BOOKS_DELIVERY була вже створена раніше, то перед повторним створенням її слід видалити наступним оператором: DROP TABLE BOOKS_DELIVERY; Наступний оператор створює таблицю BOOKS_DELIVERY з необхідними обмеженнями цілісності даних: CREATE TABLE BOOKS_DELIVERY (CODE_OPERATION NUMBER(10) CONSTRAINT DELIVERY_PR PRIMARY KEY, CODE_BOOK NUMBER(5), SALESMAN VARCHAR2(20), QUANTITY NUMBER(4), DATE_DELIVERY DATE DEFAULT SYSDATE, CONSTRAINT DELIVERY_FK FOREIGN KEY(CODE_BOOK) REFERENCES BOOKS(CODE_BOOK) ON DELETE CASCADE); додавання рядків у таблицю BOOKS_DELIVERY здійснюється використанням наступної сукупності операторів INSERT:

INSERT INTO BOOKS_DELIVERY VALUES(CODE_OP.NEXTVAL, 1, 'Іванов В. І.', 5, '20-01-2006'); INSERT INTO BOOKS_DELIVERY VALUES(CODE_OP.NEXTVAL, 3, 'Іванов В. І.', 5, '10-02-2006'); INSERT INTO BOOKS_DELIVERY VALUES(CODE_OP.NEXTVAL, 2, 'Петров П.п.', 4, '25-01-06'); INSERT INTO BOOKS_DELIVERY VALUES(CODE_OP.NEXTVAL, 4, 'Петров П.п.', 4, '20-02-06'); INSERT INTO BOOKS_DELIVERY VALUES(CODE_OP.NEXTVAL, 4, 'Іванов В. І.', 4, '20-02-06'); Перегляд введених значень можна виконати за допомогою наступного оператора SQL: SELECT * FROM BOOKS_DELIVERY; 2. Створити таблицю BOOKS1, структура якого ідентична структурі таблиці BOOKS, і перенести в неї рядки з інформацією про книги жанру «Фантастика» і «Роман» з таблиці BOOKS. Наступний набір операторів SQL створює таблицю BOOKS1 і переносить рядки з таблиці BOOKS: CREATE TABLE BOOKS1 (CODE_BOOK NUMBER(5) CONSTRAINT PK_BOOKS1 PRIMARY KEY, TITLE VARCHAR2(25) CONSTRAINT TITLE_BOOKS1 NOT NULL, AUTHOR VARCHAR2(15), PRICE NUMBER(7) CONSTRAINT PRICE_BOOKS1 CHECK(PRICE > 100), PUBLISH_HOUSE VARCHAR2(15), GENRE VARCHAR2(15)); INSERT INTO BOOKS1 SELECT CODE_BOOK, TITLE, AUTHOR, PRICE, PUBLISH_HOUSE, GENRE FROM BOOKS WHERE GENRE = 'Фантастика' OR GENRE = 'Роман'; Аналогічні дії виконуються наступним оператором: CREATE TABLE BOOKS1 AS SELECT CODE_BOOK, TITLE, AUTHOR, PRICE, PUBLISH_HOUSE, GENRE FROM BOOKS WHERE GENRE = 'Фантастика' OR GENRE = 'Роман'; Перегляд введених значень можна виконати за допомогою наступного оператора SQL: SELECT * FROM BOOKS1;

3. Виконати за допомогою оператора UPDATE наступні зміни в таблиці BOOKS: а) замінити в таблиці BOOKS рядком з прізвищем автора «Крісті А.» у стовпці AUTHOR прізвище «Крісті А.» на «Агата Крісті» у стовпці GENRE жанр «Роман» на жанр «Детектив»; б) замінити в таблиці BOOKS ціну книги «Загибель Титану» на ціну книги «Козаки». Наступний набір операторів виконає вказані дії: UPDATE BOOKS SET GENRE='Детектив', AUTHOR='Агата Крісті' WHERE AUTHOR='Крісті А.'; UPDATE BOOKS SET PRICE= (SELECT PRICE FROM BOOKS WHERE TITLE='Козаки') WHERE TITLE='Загибель Титану'; 4. Видалити з таблиці BOOKS всі рядки, які містять інформацію про книги в жанрі «Роман». Використовуємо наступний оператор: DELETE BOOKS WHERE GENRE = 'Роман'; 5. Додати в таблицю BOOKS новий стовпець PRICE_У_Е з типом даних NUMBER(7, 2) і перерахувати ціну книг в умовних одиницях. Наступні оператори виконають спочатку додавання нового стовпця у таблицю, а потім заповнять його відповідними значеннями: ALTER TABLE BOOKS ADD PRICE_У_Е NUMBER(7, 2); UPDATE BOOKS SET PRICE_У_Е=PRICE/2155; Зазначимо, що при додаванні декількох нових стовпців у таблицю вони беруться в дужки: ALTER TABLE BOOKS ADD (YEAR_PUBLISH CHAR(4), TOWN_PUBLISH CHAR(15)); Для зміни характеристик стовпця вказується ключове слово MODIFY, ім'я стовпця і нові характеристики: ALTER TABLE BOOKS MODIFY (YEAR_PUBLISH NUMBER(4), TOWN_PUBLISH CHAR(25)); Для додавання обмеження цілісності даних необхідно вказати ключове слово CONSTRAINT, ім'я обмеження і саме обмеження. При цьому необхідно пам'ятати, що додається обмеження цілісності даних не повинно суперечити даними, що знаходяться в таблиці.

ALTER TABLE BOOKS ADD CONSTRAINT ZZ CHECK(PRICE> 200); Для тимчасового відключення перевірки обмеження цілісності з ім'ям ZZ необхідно використовувати команду ALTER TABLE BOOKS DISABLE CONSTRAINT ZZ; Для включення перевірки цього обмеження цілісності можна використовувати команду ALTER TABLE BOOKS ENABLE CONSTRAINT ZZ; Для видалення обмеження цілісності з ім'ям ZZ необхідно вказати ключові слова DROP і CONSTRAINT і ім'я видаляється обмеження ALTER TABLE BOOKS DROP CONSTRAINT ZZ;

INSERT INTO имя_таблицы [(список_столбцов)]

{VALUES (значение1 [, значение2]...) | подзапрос}