Базы данных. Лекция 2 презентация

Другие типы данных
Массивы(arrays)
JSON
XML
Custom - типы
Null - отсутствие данных

Primary key
Первичный ключ — это столбец или группа столбцов, которые используются для уникальной идентификации строки в таблице.
Первичные ключи определяются посредством ограничений первичного ключа. Технически ограничение первичного ключа представляет собой комбинацию ограничения notnull и ограничения UNIQUE.
Таблица может иметь один и только один первичный ключ. Хорошей практикой является добавление первичного ключа в каждую таблицу. Когда вы добавляете первичный ключ в таблицу, PostgreSQL создает уникальный индекс B-дерева для столбца или группы столбцов, используемый для определения первичного ключа.

Определите первичный ключ при создании таблицы
Обычно мы добавляем первичный ключ в таблицу, когда определяем структуру таблицы с помощью оператора CREATE TABLE.
Следующий оператор создает таблицу заголовков заказа на покупку (purchase order, PO) с именем po_headers.

Po_no — это первичный ключ таблицы po_headers, который однозначно идентифицирует заказ на покупку в таблице po_headers.
Если первичный ключ состоит из двух или более столбцов, вы определяете ограничение первичного ключа следующим образом:
Например, следующий оператор создает таблицу позиций заказа на покупку, первичным ключом которой является комбинация номера заказа на покупку ( po_no) и номера позиции ( item_no).

Определите первичный ключ при изменении существующей структуры таблицы.
Первичный ключ для существующей таблицы определяется редко. Если вам нужно это сделать, вы можете использовать оператор ALTER TABLE, чтобы добавить ограничение первичного ключа.
Следующий оператор создает таблицу с именем Products без определения какого-либо первичного ключа.
Предположим, вы хотите добавить ограничение первичного ключа в таблицу продуктов, вы можете выполнить следующий оператор:

Как добавить автоматический первичный ключ в существующую таблицу
Предположим, у нас есть таблица поставщиков, у которой нет первичного ключа.
И мы добавляем несколько строк в таблицу поставщиков, используя оператор INSERT:
Чтобы проверить операцию вставки, мы запрашиваем данные из таблицы поставщиков, используя следующий оператор SELECT:
Теперь, если мы хотим добавить первичный ключ с именем id в таблицу поставщиков, а поле id автоматически увеличивается на единицу, мы используем следующий оператор:
Давайте еще раз проверим таблицу поставщиков.

Удалить первичный ключ
Чтобы удалить существующее ограничение первичного ключа, вы также используете оператор ALTER TABLE со следующим синтаксисом:
Например, чтобы удалить ограничение первичного ключа таблицы продуктов, вы используете следующий оператор:

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

Синтаксис ограничения внешнего ключа PostgreSQL
В этом синтаксисе:
Сначала укажите имя ограничения внешнего ключа после ключевого слова CONSTRAINT. Предложение CONSTRAINT является необязательным. Если вы его опустите, PostgreSQL присвоит автоматически сгенерированное имя.
Во-вторых, укажите один или несколько столбцов внешнего ключа в круглых скобках после ключевых слов FOREIGN KEY.
В-третьих, укажите родительскую таблицу и столбцы родительского ключа, на которые ссылаются столбцы внешнего ключа, в предложении REFERENCES.
Наконец, укажите действия по удалению и обновлению в предложениях ON DELETE и ON UPDATE.
Действия удаления и обновления определяют поведение при удалении и обновлении первичного ключа в родительской таблице. Поскольку первичный ключ редко обновляется, действие ON UPDATE на практике используется нечасто. Мы сосредоточимся на действии ON DELETE.PostgreSQL поддерживает следующие действия:

Примеры ограничений внешнего ключа PostgreSQL
В этом примере таблица клиентов является родительской таблицей, а таблица контактов — дочерней. Каждый клиент имеет ноль или несколько контактов, и каждый контакт принадлежит нулю или одному клиенту. Столбец customer_id в таблице контактов — это столбец внешнего ключа, который ссылается на одноименный столбец первичного ключа в таблице клиентов.
Следующее ограничение внешнего ключа fk_customer в таблице контактов определяет customer_id как внешний ключ:
Поскольку ограничение внешнего ключа не имеет действий ON DELETE и ON UPDATE, по умолчанию для них установлено значение NO ACTION.

NO ACTION
Следующие действия вставляют данные в таблицы customers и contacts:
Следующий оператор удаляет идентификатор клиента 1 из таблицы клиентов:
Из-за ON DELETE NO ACTION PostgreSQL выдает нарушение ограничения, поскольку ссылающиеся строки с customer id 1 все еще существуют в таблице contacts :
Действие RESTRICT(ограничить) аналогично действию NO ACTION. Разница возникает только тогда, когда вы определяете ограничение внешнего ключа как DEFERRABLE(отсроченный) с режимом INITIALLY DEFERRED или INITIALLY IMMEDIATE (непосредственный, ближайший).

SET NULL
SET NULL автоматически устанавливает NULL для столбцов внешнего ключа в ссылающихся строках дочерней таблицы, когда ссылочные строки в родительской таблице удаляются.
Следующие инструкции удаляют примеры таблиц и воссоздают их с помощью внешнего ключа, который использует действие SET NULL в предложении ON DELETE:

Следующие инструкции вставляют данные в таблицы клиентов и контактов:
Чтобы увидеть, как работает SET NULL, давайте удалим клиента с id 1 из таблицы клиентов:
Из-за действия ON DELETE SET NULL для ссылающихся строк в таблице контактов установлено значение NULL. Следующий оператор отображает данные в таблице контактов:
Как ясно видно из выходных данных, строки с customer_id 1 теперь имеют значение customer_id, равное NULL.

CASCADE
ON DELETE CASCADE автоматически удаляет все ссылающиеся строки в дочерней таблице при удалении ссылочных строк в родительской таблице. На практике вариант ON DELETE CASCADE является наиболее часто используемым.
Следующие инструкции воссоздают примеры таблиц. Однако действие удаления fk_customer изменится на CASCADE:
Следующий оператор удаляет customer id 1:
Из-за действия ON DELETE CASCADE все ссылающиеся строки в таблице контактов автоматически удаляются:

SET DEFAULT
ON DELETE SET DEFAULT устанавливает значение по умолчанию для столбца внешнего ключа ссылающихся строк в дочерней таблице, когда ссылочные строки из родительской таблицы удаляются.
Добавление ограничения внешнего ключа в существующую таблицу. Чтобы добавить ограничение внешнего ключа в существующую таблицу, вы используете следующую форму оператора ALTER TABLE:
Когда вы добавляете ограничение внешнего ключа с опцией ON DELETE CASCADE в существующую таблицу, вам необходимо выполнить следующие шаги:
Во-первых, отбросьте существующие ограничения внешнего ключа:
Сначала добавьте новое ограничение внешнего ключа с помощью действия ON DELETE CASCADE:

SQL - Ограничение DEFAULT
Ограничение DEFAULT используется для предоставления значения по умолчанию для столбца. Значение по умолчанию будет добавлено ко всем новым записям, если другое значение не указано.
Следующий SQL устанавливает значение DEFAULT для столбца «country», когда создается таблица «users»:
Чтобы создать ограничение DEFAULT в столбце «country», когда таблица уже создана, используйте следующее:
Чтобы удалить ограничение DEFAULT, используйте следующее:

Что такое язык определения данных (DDL)?
Data Definition Language (DDL)
Это поможет вам определить структуру или схему базы данных.
Поможет вам создать структуру базы данных и других объектов базы данных.
Его команды фиксируются автоматически, изменения сохраняются в базе данных навсегда.

Команды для DDL
CREATE
• DROP
• ALTER
• TRUNCATE

CREATE
CREATE операторы используются для определения схемы структуры базы данных
Syntax:
• CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Пример:

DROP
Команды Drops удаляют таблицы и базы данных из СУБД.
Syntax:
• DROP TABLE TABLE_NAME;
Пример:

ALTER
Команда Alters позволяет изменить структуру базы данных.
Syntax:
Добавление нового столбца в таблицу
• ALTER TABLE table_name ADD column_name COLUMN-definition;

ALTER
Удаление столбца из таблицы:
Example:

Alter table
Чтобы изменить структуру существующей таблицы, вы используете оператор PostgreSQL ALTER TABLE.
Ниже показан основной синтаксис оператора ALTER TABLE:
PostgreSQL предоставляет множество действий:
•Add a column
•Drop a column
•Change the data type of a column
•Rename a column
•Set a default value for the column. (Установить значение по умолчанию)
•Add a constraint to a column.
•Rename a table

Alter table
Чтобы добавить новый столбец в таблицу, вы используете оператор ALTER TABLE ADD COLUMN:
Чтобы удалить столбец из таблицы, вы используете оператор ALTER TABLE DROP COLUMN:
Чтобы переименовать столбец, вы используете оператор ALTER TABLE RENAME COLUMN TO:
Чтобы изменить значение столбца по умолчанию, вы используете ALTER TABLE ALTER COLUMN SET DEFAULT или DROP DEFAULT:

Чтобы изменить ограничение NOT NULL, вы используете оператор ALTER TABLE ALTER COLUMN:
Чтобы добавить ограничение CHECK, вы используете оператор ALTER TABLE ADD CHECK:
Обычно, чтобы добавить ограничение в таблицу, вы используете оператор ALTER TABLE ADD CONSTRAINT:
Чтобы переименовать таблицу, вы используете оператор ALTER TABLE RENAME TO:

Давайте создадим новую таблицу под названиемlinks для тренировки работы с оператором ALTER TABLE.
Чтобы добавить новый столбец с именем active, вы используете следующий оператор:
Следующий оператор удаляет активный столбец из таблицы ссылок:
Чтобы изменить имя столбца заголовка на link_title, вы используете следующий оператор:

Следующий оператор добавляет в таблицу ссылок новый столбец с именем target:
Чтобы установить _blank в качестве значения по умолчанию для целевого столбца в таблице ссылок, используйте следующий оператор:
Если вы вставите новую строку в таблицу ссылок, не указав значение для целевого столбца, целевой столбец примет _blank в качестве значения по умолчанию. Например:
Следующий оператор выбирает данные из таблицы связей:

Следующий оператор добавляет условие CHECK к целевому столбцу, чтобы целевой столбец принимал только следующие значения: _self, _blank, _parent и _top:
Если вы попытаетесь вставить новую строку, которая нарушает ограничение CHECK, установленное для целевого столбца, PostgreSQL выдаст ошибку, как показано в следующем примере:
Следующий оператор добавляет ограничение UNIQUE в столбец URL таблицы ссылок:
Следующий оператор пытается вставить уже существующий URL-адрес:

Это вызывает ошибку из-за ограничения unique_url:
Следующий оператор изменяет имя таблицы ссылок на URL-адреса:

Truncate(Усечь)
Чтобы удалить все данные из таблицы, используйте оператор DELETE. Однако использование инструкции DELETE для удаления всех данных из таблицы, содержащей много данных, неэффективно. В этом случае вам нужно использовать оператор TRUNCATE TABLE:
Инструкция TRUNCATE TABLE удаляет все данные из таблицы без ее сканирования. Именно по этой причине он работает быстрее, чем оператор DELETE. Кроме того, оператор TRUNCATE TABLE сразу же освобождает память, поэтому вам не нужно выполнять последующую операцию VACUMM, что полезно в случае больших таблиц.

Удалить все данные из одной таблицы
Простейшая форма оператора TRUNCATE TABLE выглядит следующим образом:
В следующем примере используется оператор TRUNCATE TABLE для удаления всех данных из таблицы счетов:
Помимо удаления данных, вы можете сбросить значения в столбце идентификаторов, используя опцию RESTART IDENTITY, например:
Например, следующий оператор удаляет все строки из таблицы счетов-фактур и сбрасывает последовательность, связанную со столбцом «voice_no»:
По умолчанию инструкция TRUNCATE TABLE использует опцию CONTINUE IDENTITY. Эта опция по сути не перезапускает значение в последовательности, связанной со столбцом в таблице.

Удалить все данные из нескольких таблиц
Чтобы удалить все данные из нескольких таблиц одновременно, разделите каждую таблицу запятой (,) следующим образом:
Например, следующий оператор удаляет все данные из таблиц счетов и клиентов: