Создание, изменение и удаление таблиц

Операции по изменению таблиц имеют более стандартизированное название - DDL (Data Definition Language) и в данном уроке мы разберем все основные DDL операции, присутствующие в SQL.

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

Как мы ранее и говорили сама по себе БД является хранилищем данных. Но так как данные могут быть в абсолютно разных форматах и хранить разные сущности (заказы, товары, аккаунты), то каждую сущность хранят в отдельной таблице (Table).

Что такое таблица ? Самое простое представление это обычная таблица Excel или CSV, где у нас есть колонки, названия колонок и строки со значениями для этих колонок.

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

CREATE TABLE [IF NOT EXISTS] название_таблицы(
  название_колонки ТИП_КОЛОНКИ [ПАРАМЕТРЫ],
  название_другой_колонки ТИП_КОЛОНКИ [ПАРАМЕТРЫ]
);

Основные типы данных в SQL

В отличии от Ruby, в SQL каждая колонка имеет строгий тип определяемый при создании таблицы, а значит при попытке добавить туда значение неверного типа мы будем получать ошибку несоответсвия типов. Рассмотрим основные типы данных:

Числа

Числовые значения передаются без каких-либо дополнительных атрибутов.

  • INT - Натуральное число (например 100)

  • DOUBLE - число с плавающей запятой запятой (например 125.83)

Строки

Строковые значения при добавлении обрамляются одинарными ковычками (например 'John')

  • CHAR(макс_кол-во_символов) - строка с фиксированным максимальным размером. Если размер строки меньше указанного, то строка дополняется пробелами. Максимум символов - 8000

  • VARCHAR(макс_кол-во_символов) - строка с нефиксированным размером. Если размер меньше установленного, то размер не увеличивается и добавляется как есть.Максимум символов - 8000

  • TEXT - строка с максимальным размером значения в 2GB.

Метка времени

  • DATE - дата в формате YYYY-MM-DD. Значение передается с помощью обрамления в одинарные ковыки (например '2023-03-02')

  • TIME - время в формате HH:MI:SS. Значение передается с помощью обрамления в одинарные ковыки (например '00:00:00')

  • DATETIME - дата и время в формате YYYY-MM-DD HH:MI:SS. Значение передается с помощью обрамления в одинарные ковыки (например '2023-03-02 00:00:00')

  • TIMESTAMP - POSIX-время, количество секунд прошедшее с 00:00:00 UTC 1 января, 1970 года. Значение передается в цифровом формате без каких либо дополнительных атрибутов (например 1683107624)

Первичный ключ (PK)

Первичный ключ — особенное поле в SQL-таблице, которое позволяет однозначно идентифицировать каждую запись в ней. Первичный ключ всегда уникален. Благодаря нему БД может определять дубли. Например если вы добавили в таблицу сущность с ключом равным 3, то при попытке добавить еще одну сущнсть с таким же ключом, даже если все остальные поля отличаются, БД вернет нам ошибку и сущность добавлена не будет.

При этом сам первичный ключ может быть двух вариантов: односоставный и композитный.

Односоставный ключ

Односоставный ключ, судя по названию, является ключом таблицы состоящим из одной колонки. Создать односоставный ключ можно 2 способами:

  • Добавить PRIMARY KEY в качестве параметра у атрибута:

CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ PRIMARY KEY
);

  • Указать первичный ключ в качестве отдельного атрибута:

CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ,
  PRIMARY KEY (название_колонки)
);

Композитный ключ

Композитный ключ - ключ, состоящий из двух двух или более таблиц. Создается данный ключ в виде отдельного атрибута:

CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ,
  название_другой_колонки ТИП_ДАННЫХ
  PRIMARY KEY (название_колонки, название_другой_колонки)
);

Внешний ключ (FK)

Если первичный ключ является идентификатором нашей таблицы, то внешние ключи (Foreign Keys) предсталвяют из себя указатели на перевичный ключ другой таблицы. Отсюда и происходит название "реляционная". Когда мы объявляем внешний ключ, то мы всегда указываем на таблицу к которой он привязан, а также первичный ключ этой таблицы, к которому будет привязан наш внешний ключ.

Синтаксис внешнего ключа:

CREATE TABLE [IF NOT EXISTS] название_таблицы (
  название_колонки ТИП_ДАННЫХ,
  FOREIGN KEY (название_колонки) REFERENCES название_связанной_таблицы(название_колонки_связяанной_таблицы)
);

Ограничения

Помимо параметра первичного ключа, мы можем также указывать параметры, которые будут добавлять дополнительные проверки на стороне самой базы данных. Рассмотрим основные ограничения

  • NULL - параметр, обозначающий, что значение данного столбца может содержать в себе пустое значение NULL. По аналогии nil в Ruby.

Синтаксис:

CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ NULL
);

На самом деле NULL не является обязательным параметром. По-умолчанию, все колонки при создании могут содержать NULL.

  • NOT NULL - параметр, обозначающий, что значение данного столбца не может содержать в себе пустое значение NULL.

Синтаксис:

CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ NOT NULL
);

Если вы объявляете колонку NOT NULL, то, соответственно, вы переопределяете поведение по умолчанию. PRIMARY KEY по-умолчанию включает в себя параметр NOT NULL.

  • UNIQUE - параметр, обозначающий, что каждое значение в данной колонке - уникальное, т.е. если вы добавили значение 3, то другого такого же значения здесь быть не может.

Синтаксис:

CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ UNIQUE
);

PRIMARY KEY по-умолчанию включает в себя параметр UNIQUE.

  • AUTOINCREMENT - параметр, обозначающий, что каждое значение в данной колонке будет иметь на одну единицу больше, чем предыдущее добавленное. Например, вы добавили значение 3, при попытке добавить еще одно значение в эту же таблицу со значением для этой колонки NULL, оно будет равняться 4, а не NULL.

Синтаксис:

CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ AUTOINCREMENT
);

  • DEFAULT - параметр, обозначающий, что в случае передачи в качестве значения NULL, будет подставляться значение по-умолчанию, которое было заранее определено при создании таблицы.

Синтаксис:

CREATE TABLE название_таблицы (
  название_колонки ТИП_ДАННЫХ DEFAULT значение_по_умолчанию
);

Помимо статичного значения можно использовать динамичное, например current_timestamp, которое показывает каждый раз текущее время в формате POSIX.

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

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

ALTER TABLE название_таблицы ПАРАМЕТР

Добавление колонок

Добавление колонки происходит с помощью добавления параметра ADD COLUMN.

Синтаксис:

ALTER TABLE название_таблицы ADD COLUMN название_добавляемой_колонки ТИП_КОЛОНКИ

Кроме того мы можем добавлять ограничения для новых колонок, например NOT NULL, UNIQUE или добавить значение по умолчанию.

Синтексис:

ALTER TABLE название_таблицы ADD COLUMN название_добавляемой_колонки ТИП_КОЛОНКИ ПАРАМЕТР

Удаление колонок

Синтаксис:

ALTER TABLE название_таблицы DROP COLUMN название_колонки

Переименнование колонок

Синтаксис:

ALTER TABLE название_таблицы RENAME COLUMN исходное_название_столбца TO итоговое_название_столбца

Удаление таблиц

Синтаксис:

DROP TABLE название_таблицы

Пример из реальной жизни

Давайте предположим, что мы занимаемся разработкой интернет-магазина. Преджде чем приступить к созданию базы данных, определимся какие сущности необходимы для нашего магазина:

  • Товары. Любой интернет магазин занимается продажей вещей, соответственно у любого интернет магазина есть товары.

  • Покупатели. Покупатель может зарегистрироваться на сайте, совершать покупки и тд.

  • Заказы. Заказы слушат некой связкой покупателя и товара. По заказу мы модем определить кто покупает и что покупает.

Товар

Какие атрибуты могут быть у товара ?

  • номер товара или его идентификатор. Значение, которое будет служить для отличия товаров друг от друга.

  • Производитель. Нам магазин может продать разные модели от одного и того же производителя

  • модель. Модель конкретного производителя

  • цвет. В магазине у нас может продаваться модель в разных цветах.

  • Дата добавления. Полезный атрибут, позволяющий отличить старый товар от нового.

  • цена

Соответсвенно мы можем все эти атрибуты превратить в таблицу:

id
manufacture
model
color
date_added
price

1

apple

iphone 14

black

01.12.2022

100000

2

samsung

a54

white

01.04.2023

30000

3

apple

iphone 14

white

21.12.2022

1010000

Как будет выглядеть создание такой таблицы:

CREATE TABLE IF NOT EXISTS items (
  id INTEGER PRIMARY KEY,
  manufacture TEXT NOT NULL,
  model TEXT NOT NULL,
  color TEXT NOT NULL,
  price INTEGER NOT NULL,
  date_added TIMESTAMP NOT NULL
);

Здесь мы указали, что нашим первичным ключом будет служить поле id, а все остальные атрибуты будут иметь параметр NOT NULL, что означает, что все поля обязательны для заполнения при добавлении нового товара.

Покупатели

Теперь подумаем какие атрибуты необходимы для покупателя:

  • идентификатор. Тоже самое, что и с товарами

  • имя. Часть персональных данных, необходимая для идентификации человека

  • фамилия. Другая часть персональных данных, необходимая для идентификации человека

  • телефон. Во время доставки нам важно иметь возможность иметь связь с покупателем для уточнения деталей.

  • email. Почта позволит оповещать нашего клиента о новом статусе заказа или рассылать рекламные предложения.

Итоговая таблица покупателей:

id
first_name
last_name
delivery_address
phone
email

1

Иван

Петров

Moscow, Lenina st, appt 12

79999999999

ivan.petrov@mail.ru

2

Ксения

Андреева

Vladimir, Aviation st, appt. 123, 23

79991111111

ksenya.andreeva@ya.ru

SQL синтаксис:

CREATE TABLE IF NOT EXISTS customers (
  id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  phone INTEGER NOT NULL UNIQUE,
  delivery_address TEXT NOT NULL,
  email TEXT
);

В данном случае у нас id снова служит первичным ключом, а все остальные поля обязательны, кроме email, что позволяет пользователю при регистрации его не вводить.

Заказы

Ну и наконец разберем атрибуты заказов:

  • идентификатор товара. Мы должны знать что покупает покупатель.

  • идентификатор покупателя. Мы должны знать кто покупает товар.

  • дата заказа. Благодаря дате мы сможем понимать является ли заказ одного и того же товара одним и тем же покупателем новым или старым заказом.

  • адрес доставки. Так как мы интернет магазин, то нам необходимо знать куда доставить заказ.

  • описание. Во время подтверждения заказа клиент модет дать нам дополнительную информацию, например время доставки и тд.

Итоговая таблица покупателей:

item_id
customer_id
date_added
description

1

2

2023-03-25 15:21:11

Доставка 2023.03.31 после 16:00

2

1

2023-02-12 10:32:16

Доставка 2023.02.13 после 08:00

1

2

2023-01-11 19:29:46

Доставка 2023.01.13 после 18:00

Синтаксис создания таблицы:

CREATE TABLE orders (
  item_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL,
  date_added TIMESTAMP NOT NULL,
  description TEXT,
  PRIMARY KEY (item_id, customer_id, date_created),
  FOREIGN KEY (item_id)  REFERENCES items(id),
  FOREIGN KEY (customer_id)  REFERENCES customers(id)
);

В данном случае нашим первичным ключом является композитный ключ состоящий из 3 колонок: item_id, customer_id и date_created. Так как:

  • Допустим мы бы сделали односоставный ключ с идентификатором товара или пользователя, тогда мы сталкиваемся с проблемой, что у нас либо может быть куплен только один товар, либо пользователь может совершить только одну покупку.

  • Если бы мы сделали композитный ключ состоящий из идентификатора пользователя и товара только, тогда бы пользователь не смог купить один и тот же товар более 1 раза.

Помимо первичного ключа у нас также присуствуют внешние ключи из которых:

  • item_id является значением столбца id из таблицы items.

  • customer_id является значением столбца id из таблицы customers.

Таким образом у нас не может появиться заказ с товаром, которого не существует или от пользователя, который не зарегистрировался.

Задание university

В конце урока будет приложена ER диаграмма базы данных university. Вам необходимо:

  • Написать для каждой таблицы отдельный файл в формате .sql с командой для создания этой таблицы. Файл должен называться create_название_таблицы_table.sql.

  • Запустить все эти скрипты для проверки работоспособности.

Рекомендации:

  • Будьте внимательны с очередностью выполнения .sql скриптов, так как часть таблиц зависят от других таблиц, то зависимые таблицы должны быть созданы вначале.

Дополнительный материал

PreviousУстановка SQL сервераNextПолучение, добавление, удаление и изменение данных в таблицах

Last updated