Урок 2. Создание таблицы и типы данных

В этом уроке появится первая таблица учебного магазина — products, товары. Откройте свою базу: sqlite3 shop.db или файл shop.db в DB Browser for SQLite (как их поставить — в первом уроке).

Команда CREATE TABLE

CREATE TABLE products (
    id       INTEGER PRIMARY KEY,
    name     TEXT    NOT NULL,
    category TEXT,
    price    INTEGER NOT NULL CHECK (price >= 0),
    qty      INTEGER NOT NULL DEFAULT 0
);

После CREATE TABLE — имя таблицы, в скобках через запятую — столбцы. У каждого столбца имя, тип и, если нужно, ограничения — правила, которые СУБД проверяет при каждой записи:

• id INTEGER PRIMARY KEY — номер товара, первичный ключ. Он уникален, и SQLite сама ставит следующий номер, если его не указать;
• name TEXT NOT NULL — название, текст; NOT NULL запрещает оставлять его пустым;
• category TEXT — категория, может отсутствовать;
• price INTEGER NOT NULL CHECK (price >= 0) — цена в рублях, обязательна и не может быть отрицательной;
• qty INTEGER NOT NULL DEFAULT 0 — остаток на складе; если его не указать, будет 0.

Запятая ставится между столбцами, после последнего её нет. Лишняя запятая перед ) — частая причина ошибки syntax error.

Типы данных

В SQLite всего пять видов значений: INTEGER (целое), REAL (дробное), TEXT (текст), BLOB (байты, например картинка) и NULL (пусто). Серверные СУБД различают типы строже и подробнее:

DECIMAL(10,2) — точное число: всего 10 цифр, из них 2 после запятой. Для денег берут его, а не DOUBLE: дробные типы хранят числа приближённо, и 0.1 + 0.2 у них не равно точно 0.3.

VARCHAR(100) — строка не длиннее 100 знаков. В MySQL у текстового столбца почти всегда указывают длину, в PostgreSQL можно писать просто text.

Типы в SQLite гибкие

SQLite принимает и чужие имена типов — VARCHAR(100), DATE, DECIMAL(10,2) — и сводит их к своим. Более того, обычная таблица SQLite позволяет записать текст в столбец INTEGER: тип там — пожелание, а не запрет. Строгую проверку включает слово STRICT после скобок (SQLite 3.37 и новее):

CREATE TABLE prices (
    product TEXT NOT NULL,
    price   INTEGER NOT NULL
) STRICT;

В такой таблице допустимы только типы INTEGER, INT, REAL, TEXT, BLOB и ANY, а попытка записать 'abc' в price закончится ошибкой. Как это выглядит — в следующем уроке.

Ограничения

Ограничения — защита от ошибок в данных: товар без названия или с ценой −5 просто не попадёт в таблицу. Подробно о ключах — в уроке Ключи, связи и индексы.

Та же таблица в MySQL и PostgreSQL

MySQL и MariaDB:

CREATE TABLE products (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price    DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    qty      INT NOT NULL DEFAULT 0
);

PostgreSQL:

CREATE TABLE products (
    id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     varchar(100) NOT NULL,
    category varchar(50),
    price    numeric(10,2) NOT NULL CHECK (price >= 0),
    qty      integer NOT NULL DEFAULT 0
);

Главное отличие — автоматический номер: в SQLite его даёт INTEGER PRIMARY KEY, в MySQL — AUTO_INCREMENT, в PostgreSQL — GENERATED … AS IDENTITY или старый тип serial. Подробнее — в справке Автоинкремент в SQLite, MySQL и PostgreSQL.

Имена таблиц и столбцов

Пишите имена латиницей, строчными буквами, слова — через подчёркивание: products, order_date, customer_id. Пробелы и русские буквы технически возможны, но такие имена придётся всё время брать в кавычки.

Нельзя без кавычек называть таблицу или столбец служебным словом SQL: order, group, select, table, а в PostgreSQL ещё и user. Запрос CREATE TABLE order (…) закончится ошибкой syntax error. Поэтому заказы в курсе — orders. Если имя уже занято таким словом, его берут в кавычки: в SQLite и PostgreSQL — в двойные, в MySQL — в обратные (знак на клавише с буквой Ё):

SELECT * FROM "order";   -- SQLite, PostgreSQL
SELECT * FROM `order`;   -- MySQL, MariaDB

Посмотреть, что получилось

В DB Browser for SQLite таблицы видны на вкладке «Database Structure». Есть и запрос SQL, который работает в любой программе для SQLite:

PRAGMA table_info(products);

Если таблица уже есть

Повторный CREATE TABLE products выдаст ошибку table products already exists. Чтобы создать таблицу только тогда, когда её нет, добавьте IF NOT EXISTS:

CREATE TABLE IF NOT EXISTS products (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

Учтите: если таблица уже есть, команда ничего не сделает — даже если столбцы в ней другие. Изменить готовую таблицу можно командой ALTER TABLE, об этом — справка Как добавить столбец.

Удалить таблицу

DROP TABLE IF EXISTS products;

Если вы удалили products, создайте её снова первым запросом урока: она понадобится дальше.

Коротко

• CREATE TABLE имя (столбец тип ограничения, …); — новая таблица.
• Основные типы SQLite: INTEGER, REAL, TEXT, BLOB; в MySQL и PostgreSQL есть ещё VARCHAR(n), DECIMAL, DATE, BOOLEAN и другие.
• Деньги — в точном типе (DECIMAL, numeric) или целым числом в копейках.
• Ограничения NOT NULL, DEFAULT, CHECK, UNIQUE, PRIMARY KEY защищают данные от ошибок.
• IF NOT EXISTS — создать, если нет; DROP TABLE — удалить таблицу навсегда.

Проверьте себя