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

Операции по изменению таблиц имеют более стандартизированное название - 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

first_name

last_name

delivery_address

phone

Иван

Петров

Moscow, Lenina st, appt 12

79999999999

ivan.petrov@mail.ru

Ксения

Андреева

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 1 year ago