Skip to content

lesson02.md

Latest commit

 

History

History
309 lines (215 loc) · 16.9 KB

lesson02.md

File metadata and controls

309 lines (215 loc) · 16.9 KB

Урок 2. SQLiteStudio и первая база данных

SQLiteStudio — инструмент для работы с SQLite

SQLiteStudio — это бесплатная программа с графическим интерфейсом для работы с SQLite-базами данных. Она позволяет создавать и открывать файлы БД, выполнять SQL-запросы, просматривать таблицы и данные в них.

Установка

Скачайте актуальную версию с официального сайта: sqlitestudio.pl

Программа не требует установки — достаточно распаковать архив и запустить исполняемый файл SQLiteStudio (Windows: SQLiteStudio.exe).

Интерфейс

После запуска вы увидите три основные области:

┌─────────────────┬──────────────────────────────────────────┐
│                 │                                          │
│  Панель баз     │         Редактор SQL                     │
│  данных         │                                          │
│                 │                                          │
│  - shop.db      ├──────────────────────────────────────────┤
│    ├ categories │                                          │
│    ├ products   │         Результаты запроса               │
│    ├ users      │                                          │
│    └ ...        │                                          │
└─────────────────┴──────────────────────────────────────────┘
  • Панель баз данных (слева) — список подключённых БД и их таблиц
  • Редактор SQL (справа сверху) — место для написания и выполнения запросов
  • Панель результатов (справа снизу) — вывод результатов запроса

Создание файла базы данных

Файл базы данных SQLite — это обычный файл на диске с расширением .db или .sqlite3. Вся база данных хранится в этом одном файле.

Шаги для создания новой БД в SQLiteStudio

  1. В меню выберите Database → Add a database
  2. В открывшемся окне нажмите иконку папки рядом с полем "File"
  3. Выберите папку для проекта и введите имя файла: shop.db
  4. Нажмите OK

В панели баз данных появится shop.db. База данных создана — пока она пустая.

Важно: файл shop.db создаётся прямо на диске в той папке, которую вы выбрали. Его можно скопировать, перенести, отправить коллеге — это просто файл.

Первый CREATE TABLE

Прежде чем запускать готовые данные, напишем первую таблицу вручную. Это важно — нужно понять синтаксис CREATE TABLE своими руками.

Синтаксис

CREATE TABLE имя_таблицы (
    имя_столбца  ТИП  [ограничения],
    имя_столбца  ТИП  [ограничения],
    ...
);

Базовые типы данных SQLite

SQLite поддерживает четыре основных типа:

Тип Для чего Пример значения
INTEGER Целые числа 1, 42, -7
REAL Числа с плавающей точкой 3.14, 75000.00
TEXT Строки любой длины 'Москва', 'alice@mail.ru'
BLOB Бинарные данные (файлы, изображения) x'89504e47'

SQLite — гибкая система. Типы данных в ней носят рекомендательный характер: в столбец INTEGER технически можно записать текст. Но мы будем работать с типами правильно — это важная привычка перед переходом на PostgreSQL, где типизация строгая.

Первичный ключ и AUTOINCREMENT

Каждая таблица должна иметь первичный ключ — уникальный идентификатор строки.

id  INTEGER  PRIMARY KEY  AUTOINCREMENT
  • PRIMARY KEY — объявляет столбец первичным ключом. SQLite гарантирует уникальность.
  • AUTOINCREMENT — значение генерируется автоматически: 1, 2, 3... При вставке новой строки указывать id не нужно.

Пример: создаём тестовую таблицу

Напишем таблицу для хранения категорий товаров. Откройте редактор SQL в SQLiteStudio (убедитесь что выбрана ваша БД shop.db) и выполните:

CREATE TABLE categories (
    id    INTEGER  PRIMARY KEY AUTOINCREMENT,
    name  TEXT     NOT NULL
);

Нажмите F9 или кнопку "Execute" для выполнения. В панели слева в разделе shop.db появится таблица categories.

Что здесь происходит:

  • id INTEGER PRIMARY KEY AUTOINCREMENT — уникальный числовой идентификатор, заполняется автоматически
  • name TEXT NOT NULL — название категории, текстовое, обязательное для заполнения (NOT NULL означает "не может быть пустым")

Проверяем таблицу

Добавим несколько строк вручную чтобы убедиться что таблица работает:

INSERT INTO categories (name) VALUES ('Электроника');
INSERT INTO categories (name) VALUES ('Книги');

Теперь выполним запрос чтобы посмотреть на результат:

SELECT * FROM categories;

Вы увидите:

┌────┬─────────────┐
│ id │ name        │
├────┼─────────────┤
│  1 │ Электроника │
│  2 │ Книги       │
└────┴─────────────┘

id заполнился автоматически — 1 и 2. При следующей вставке будет 3.

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

Перед тем как запустить полный seed-файл, нужно удалить тестовую таблицу categories — в seed.sql она будет создана заново с правильной структурой.

DROP TABLE categories;

DROP TABLE удаляет таблицу полностью вместе со всеми данными. Команда необратима — отмены нет. В реальной разработке эту команду используют с осторожностью.

Запуск seed.sql — получаем рабочую БД

Файл seed.sql — это набор SQL-команд, которые создают все таблицы курса и заполняют их данными. Запускать его каждый раз заново не придётся — только один раз в начале.

Что находится внутри seed.sql

Файл содержит:

  1. DROP TABLE IF EXISTS — удаление таблиц если они уже существуют (для безопасного повторного запуска)
  2. CREATE TABLE для каждой из пяти таблиц
  3. INSERT INTO с данными: категории, товары, пользователи, заказы, позиции заказов

Как запустить

  1. В меню SQLiteStudio выберите Tools → Execute SQL from file
  2. Выберите файл seed.sql
  3. Убедитесь что в выпадающем списке выбрана ваша БД shop.db
  4. Нажмите OK

В панели слева появятся пять таблиц: categories, products, users, orders, order_items.

Проверяем результат

Выполните несколько запросов чтобы убедиться что данные на месте:

SELECT * FROM categories;
┌────┬─────────────┐
│ id │ name        │
├────┼─────────────┤
│  1 │ Электроника │
│  2 │ Периферия   │
│  3 │ Мебель      │
│  4 │ Книги       │
│  5 │ Одежда      │
└────┴─────────────┘

SELECT * FROM products;
SELECT * FROM users;

Структура нашей базы данных

Теперь посмотрим на схему целиком — пять таблиц со связями между ними:

categories                products
┌──────────────┐          ┌──────────────────────┐
│ id      INT  │◄─────────│ category_id  INT      │
│ name    TEXT │          │ id           INT  (PK)│
└──────────────┘          │ name         TEXT     │
                          │ price        REAL     │
                          │ stock        INT      │
                          └──────────────────────┘

users                     orders
┌──────────────────┐      ┌──────────────────────┐
│ id    INT   (PK) │◄─────│ user_id   INT        │
│ name  TEXT       │      │ id        INT   (PK) │
│ email TEXT       │      │ status    TEXT       │
│ city  TEXT       │      │ created_at TEXT      │
│ created_at TEXT  │      └──────────┬───────────┘
└──────────────────┘                 │
                                     │
                          order_items│
                          ┌──────────▼───────────┐
                          │ id           INT (PK) │
                          │ order_id     INT      │
                          │ product_id   INT      │
                          │ quantity     INT      │
                          │ price_at_time REAL    │
                          └──────────────────────┘

Что здесь важно понять уже сейчас:

  • У каждой таблицы есть id — первичный ключ
  • Таблицы связаны через числовые идентификаторы: orders.user_id указывает на users.id
  • order_items связана сразу с двумя таблицами: она знает к какому заказу относится и какой товар содержит
  • Данные о цене товара в момент покупки хранятся в price_at_time — потому что цена товара в products может измениться, а история заказа должна оставаться точной

С этой базой данных мы будем работать во всех последующих уроках модуля.

Вопросы

  1. Что представляет собой файл базы данных SQLite физически?
  2. Для чего используется ключевое слово AUTOINCREMENT при объявлении первичного ключа?
  3. Какой тип данных SQLite подходит для хранения цены товара? Почему не INTEGER?
  4. Что произойдёт если выполнить DROP TABLE categories когда в таблице есть данные?
  5. Зачем в начале seed.sql стоят команды DROP TABLE IF EXISTS?
  6. Почему в таблице order_items есть столбец price_at_time, если цена товара уже есть в таблице products?
  7. Какой тип данных SQLite использовать для хранения электронной почты пользователя?
  8. Чем seed.sql отличается от файла базы данных shop.db?
  9. Посмотрите на схему БД. Как таблица order_items "знает" к какому заказу и к какому товару относится каждая строка?
  10. Нужно сохранить в БД аватар пользователя в виде бинарных данных. Какой тип данных SQLite для этого подходит?

Задачи

Задача 1.

Напишите команду CREATE TABLE для таблицы reviews (отзывы о товарах). Таблица должна содержать: уникальный идентификатор, идентификатор товара (целое число), текст отзыва, рейтинг (целое число от 1 до 5), дату публикации (текст).

Задача 2.

Напишите команду для создания таблицы employees (сотрудники) со столбцами: id, full_name (текст, обязательный), position (должность, текст, обязательный), salary (зарплата, число с плавающей точкой), hire_date (дата найма, текст).

Задача 3.

Создайте таблицу cities с полями id и name. Добавьте в неё три города на ваш выбор с помощью INSERT INTO. Затем выполните SELECT * FROM cities и убедитесь что данные добавились корректно.

Результат:

┌────┬────────┐
│ id │ name   │
├────┼────────┤
│  1 │ Москва │
│  2 │ Казань │
│  3 │ Сочи   │
└────┴────────┘

Задача 4.

Напишите команду CREATE TABLE для таблицы blog_posts (статьи блога): идентификатор, заголовок (текст, обязательный), содержимое статьи (текст), количество просмотров (целое число, по умолчанию 0), дата публикации (текст). Значение по умолчанию: DEFAULT 0 для views — новая статья начинает с нуля просмотров автоматически, без явного указания при вставке.

Задача 5.

Используя данные из seed.sql, выполните три отдельных запроса SELECT * FROM ... для таблиц users, orders и order_items. Посчитайте: сколько строк в каждой таблице? Для подсчета нужно использовать функцию COUNT(). Эта функция будет подробно рассмотрена в Уроке 5.

Задача 6.

Создайте таблицу sessions для хранения пользовательских сессий: id, user_id (целое, обязательное), token (текст, обязательный), created_at (текст), expires_at (текст). После создания — удалите её с помощью DROP TABLE.

Предыдущий урок | Следующий урок