Мова Визначення Даних

DDL - це підмножина мови SQL, яка використовується для визначення та модифікації структури бази даних. Розуміння DDL є фундаментальним для роботи з СКБД. Це дозволяє вам визначати структуру ваших даних і вносити необхідні зміни в міру розвитку ваших вимог.

  • CREATE використовується для створення нових об’єктів у СКБД

  • ALTER використовується для зміни існуючих об’єктів

  • DROP використовується для видалення об’єктів бази даних

  • TRUNCATE використовується для видалення всіх записів з таблиці

Створення об’єктів

Створення ролей бази даних

Примітка

SQLite - це невелика база даних в одному файлі, і вона не реалізує рольовий доступ до даних. Будь-який користувач, який має доступ до файлу бази даних, також має доступ до даних всередині бази даних.

У СКБД ролі використовуються для керування правами доступу до бази даних. Вони є невід’ємною частиною безпеки бази даних та управління користувачами.

У PostgreSQL роль - це сутність, яка може володіти об’єктами бази даних і мати привілеї до бази даних; роль може розглядатися як користувач, група або і те, і інше, залежно від того, як вона використовується.

Оператор CREATE ROLE визначає нову роль бази даних.

CREATE ROLE name;

Це твердження також може мати варіанти:

CREATE ROLE name WITH LOGIN SUPERUSER PASSWORD 'password';

Опції пояснюють, чи може роль входити на сервер бази даних, підключатися до певних баз даних, створювати інші ролі тощо. Повний список опцій доступний в офіційній документації postgres.

Створення баз даних

Оператор CREATE DATABASE створює нову базу даних PostgreSQL. Для створення бази даних ви повинні бути супер-користувачем або мати привілей CREATEDB.

CREATE DATABASE name;

Бази даних на сервері PostgreSQL мають власників. За замовчуванням власником бази даних призначається користувач, який її створив. Щоб вказати іншого користувача як власника бази даних, можна скористатися опціями:

CREATE DATABASE name OWNER role_name;

В офіційній документації postgres є багато варіантів цього твердження, доступних в офіційній документації postgres.

Створення таблиць

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

CREATE TABLE table_name
(
    first_column  INTEGER,
    second_column NUMERIC,
    third_column  VARCHAR(32),
    fourth_column TEXT
);

Значення за замовчуванням

Обмеження також можуть бути застосовані до стовпців під час створення таблиці. У цьому розділі буде описано лише найпоширеніші обмеження.

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

У визначенні таблиці значення за замовчуванням перераховані після типу даних.

CREATE TABLE product
(
    product_no INTEGER,
    name       TEXT
    price      NUMERIC DEFAULT 9.99
);

Значення за замовчуванням може бути виразом, який буде обчислюватися щоразу, коли вставляється значення за замовчуванням (не під час створення таблиці). Типовим прикладом є стовпці з позначками часу, що відображають час створення запису, та генератор «серійних номерів».

CREATE TABLE product
(
    product_no INTEGER   DEFAULT nextval('product_no_seq'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Існує скорочення для генерації серійних номерів:

product_no SERIAL

Згенеровані стовпці

Якщо вам потрібно обчислити значення якогось стовпця на основі інших стовпців, ви можете використовувати згенеровані стовпці. Згенерований стовпець - це спеціальний стовпець, який завжди обчислюється на основі інших стовпців. Таким чином, для стовпців це те саме, що подання для таблиць. Існує два типи згенерованих стовпців: збережені та віртуальні.

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

CREATE TABLE person
(
    height_cm NUMERIC,
    height_in NUMERIC GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

Примітка

Існує кілька обмежень, що застосовуються до визначень згенерованих стовпців і таблиць, що містять згенеровані стовпці (не всі з них перераховані):

  • Вираз генерації може використовувати лише незмінні функції і не може використовувати підзапити або посилатися на щось, крім поточного рядка.

  • Вираз генерації не може посилатися на інший згенерований стовпець.

  • Створений стовпець не може мати стовпець за замовчуванням.

Обмеження

Обмеження перевірки є найбільш загальним типом обмежень. Воно дозволяє вказати, що значення в певному стовпчику має задовольняти булевому (істинно-неістинному) виразу.

CREATE TABLE product
(
    product_no       INTEGER,
    name             TEXT,
    price            NUMERIC CONSTRAINT price_positive CHECK (price > 0),
    discounted_price NUMERIC CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

Обмеження не-нуль просто вказує, що стовпець не повинен приймати нульове значення.

CREATE TABLE product
(
    product_no SERIAL NOT NULL,
    name       TEXT   NOT NULL CHECK(length(name) > 0)
);

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

CREATE TABLE example
(
    a INTEGER,
    b INTEGER UNIQUE,
    c INTEGER,
    UNIQUE (a, c)
);

Системні колонки

Кожна таблиця має декілька системних стовпців, які неявно визначені системою. Тому ці назви не можуть бути використані як назви стовпців, визначених користувачем. Ці стовпці не можуть бути використані у створених стовпцях (крім tableoid). Стовпці можна:

  • tableoid

  • xmin

  • cmin

  • xmax

  • cmax

  • ctid

Модифікація об’єктів

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

PostgreSQL надає сімейство команд для внесення змін до існуючих таблиць. Ви можете це робити:

  • додати стовпці

  • видалити стовпці

  • додати обмеження

  • зняти обмеження

  • змінити значення за замовчуванням

  • змінити типи даних стовпців

  • перейменувати стовпці

  • перейменовувати таблиці

Всі ці дії виконуються за допомогою команди ALTER TABLE.

Додавання стовпця

Щоб додати стовпець, використовуйте команду накшатл:

ALTER TABLE tbl_name ADD COLUMN cln_name cln_type

CREATE TABLE book
(
    id               SERIAL PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    synopsis         TEXT,
    isbn             VARCHAR(16),
    publisher_id     INTEGER      NOT NULL REFERENCES publisher ON DELETE RESTRICT,
    publication_date DATE,
    language         VARCHAR(64),
    page_count       INTEGER,
    keywords         TEXT
);

ALTER TABLE book
    ADD COLUMN genre book_genre;

Примітка

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

Фактично, тут можна використовувати всі опції, які можна застосувати до опису стовпця у CREATE TABLE. Однак майте на увазі, що значення за замовчуванням має задовольняти заданим обмеженням, інакше команда ADD не спрацює.

Додавання обмеження

Для додавання обмеження використовується синтаксис обмеження таблиці.

CREATE TABLE borrow_request
(
    book_id       INTEGER REFERENCES book ON DELETE CASCADE,
    member_id     INTEGER REFERENCES member ON DELETE SET NULL,
    borrow_date   DATE             DEFAULT NOW(),
    due_date      DATE    NOT NULL DEFAULT NOW() + INTERVAL '2 weeks',
    complete_date DATE,
    PRIMARY KEY (book_id, member_id, borrow_date)
);

COMMENT
    ON TABLE borrow_request IS 'book borrow requests';
COMMENT
    ON COLUMN borrow_request.book_id IS 'book reference, composite pk';
COMMENT
    ON COLUMN borrow_request.member_id IS 'member reference, composite pk';
COMMENT
    ON COLUMN borrow_request.borrow_date IS 'composite pk';

ALTER TABLE borrow_request
    ADD CONSTRAINT
        check_complete_date CHECK (complete_date >= borrow_date);

ALTER TABLE borrow_request
    OWNER TO libms;

Інші приклади:

ALTER TABLE tbl_name
    ADD CONSTRAINT cst_name UNIQUE (cln_name);

ALTER TABLE tbl_name
    ADD FOREIGN KEY (cln_name) REFERENCES tbl_another;

Щоб додати ненульове обмеження, яке не можна записати як обмеження таблиці, використовуйте цей синтаксис:

ALTER TABLE tbl_name
    ALTER COLUMN cln_name SET NOT NULL;

Зміна значення стовпця за замовчуванням

Щоб встановити нове значення за замовчуванням для стовпця, скористайтеся такою командою:

ALTER TABLE tbl_name
    ALTER COLUMN cln_name SET DEFAULT 0.0;

Щоб видалити будь-яке значення за замовчуванням, використовуйте:

ALTER TABLE tbl_name
    ALTER COLUMN cln_name DROP DEFAULT;

Це фактично те саме, що встановити значення за замовчуванням NULL.

Зміна типу даних стовпця

Щоб перетворити стовпець в інший тип даних, скористайтеся такою командою:

ALTER TABLE tbl_name
    ALTER COLUMN cln_name TYPE NUMERIC(10, 2);

Це вдасться лише у тому випадку, якщо кожен існуючий запис у стовпчику може бути перетворено до нового типу неявним приведенням. Якщо потрібне більш складне перетворення, ви можете додати речення ВИКОРИСТАННЯ, яке вказує, як обчислювати нові значення зі старих.

PostgreSQL спробує перетворити значення стовпця за замовчуванням (якщо таке є) до нового типу, а також будь-які обмеження, пов’язані зі стовпцем. Але ці перетворення можуть виявитися невдалими або призвести до несподіваних результатів. Часто краще зняти будь-які обмеження зі стовпця перед зміною його типу, а потім додати назад відповідним чином змінені обмеження.

Перейменування об’єктів

Для перейменування об’єктів використовуйте команду RENAME, наприклад, так:

ALTER TABLE tbl_name
    RENAME COLUMN cln_name TO new_cln_name;
ALTER TABLE tbl_name
    RENAME CONSTRAINT cst_name TO new_cst_name;

Перейменувати таблицю:

ALTER TABLE tbl_name
    RENAME TO new_tbl_name;

Видалення об’єктів

Видалення столів

Команда DROP використовується для видалення об’єктів.

DROP TABLE tbl_name;

Пам’ятайте, що спроба видалити неіснуючу таблицю є помилкою. Тим не менш, у файлах скриптів SQL часто зустрічається безумовна спроба видалити кожну таблицю перед її створенням, ігноруючи будь-які повідомлення про помилки, щоб скрипт працював незалежно від того, чи існує таблиця чи ні. варіант ЯКЩО ІСНУЄ можна використовувати для уникнення повідомлення про помилку, але це не є стандартним для SQL.

DROP TABLE IF EXISTS book_author;
DROP TABLE IF EXISTS borrow_request;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS publisher;
DROP TABLE IF EXISTS author;
DROP TABLE IF EXISTS revenue;
DROP TABLE IF EXISTS member;
DROP TABLE IF EXISTS contact;

Якщо є інші об’єкти, що посилаються на об’єкт, який потрібно вилучити, CASCADE може бути використано для вилучення всіх об’єктів, що посилаються на об’єкт, який потрібно вилучити.

Видалення стовпця

Щоб видалити стовпець, використовуйте команду DROP ось так:

ALTER TABLE book
    DROP COLUMN genre;

Усі дані, що були у стовпчику, зникають. Обмеження таблиці, що стосуються стовпця, також зникають. Однак, якщо на стовпець посилається обмеження зовнішнього ключа іншої таблиці, PostgreSQL не буде мовчки вилучати це обмеження. Ви можете дозволити вилучення всього, що залежить від стовпця, додавши CASCADE:

ALTER TABLE tbl_name
    DROP COLUMN cln_name CASCADE;

Зняття обмеження

Щоб видалити обмеження, вам потрібно знати його ім’я. Команда psql \d table_name може бути корисною для отримання детальної інформації про конкретну таблицю, включно з іменами всіх обмежень, призначених для неї.

ALTER TABLE tbl_name
    DROP CONSTRAINT cst_name;