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

1.

Лекция 2

2.

3.

4.

5.

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

6.

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

7.

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

8.

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

9.

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

10.

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

11.

12.

13.

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

14.

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

15.

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

16.

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

17.

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

18.

19.

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

20.

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

21.

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

22.

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

23.

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

24.

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

25.

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

26.

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

27.

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

28.

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

29.

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

30.

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

31.

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

32.

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

33.

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

34.

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

35.

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

36.

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

37.

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

38.

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

39.

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

40.

Удалить все данные из таблицы,
содержащей ссылки на внешние ключи.
• На практике таблица, которую вы хотите усечь, часто содержит
ссылки на внешние ключи из других таблиц, которые не указаны
в инструкции TRUNCATE TABLE.
• По умолчанию инструкция TRUNCATE TABLE не удаляет данные из
таблицы, содержащей ссылки на внешние ключи. Чтобы удалить
данные из таблицы и других таблиц, имеющих внешний ключ,
ссылающихся на таблицу, вы используете опцию CASCADE в
инструкции TRUNCATE TABLE следующим образом:
• В следующем примере удаляются данные из таблицы счетовфактур и других таблиц, которые ссылаются на таблицу счетовфактур через ограничения внешнего ключа:
• Опцию CASCADE следует использовать при дальнейшем
рассмотрении, иначе вы потенциально можете удалить данные
из таблиц, которые вам не нужны.
• По умолчанию инструкция TRUNCATE TABLE использует опцию
RESTRICT, которая предотвращает усечение таблицы, содержащей
ссылки на ограничения внешнего ключа.