Создаём простые связи в базе данных

Создаём простые связи в базе данных

Немного практики по SQL-запросам перед серьёзным проектом

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

Сегодня мы сделаем то же самое, но уже в настоящей базе данных и с таблицами. 

Создаём простые связи в базе данных

Что понадобится

Это простой проект, поэтому всё, что нам будет нужно, — это установленная MySQL на домашнем компьютере или на сервере. Удалённо подключаться к самой базе мы пока не будем, а вместо этого попрактикуемся в SQL-запросах. Если базы нет ни там ни там, поработайте в онлайн-компиляторе SQL — главное, не перезагружайте страницу.

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

Таблица с товарами

В таблице с товарами у нас будет три столбца, причём главным будет название товара:

  1. Название ← по этому параметру мы будем связывать эту таблицу с другой.
  2. Количество ← остаток на складе.
  3. Цена.

Связь между таблицами нам понадобится для связи товаров с покупками — при продаже мы будем брать из товаров цену и уменьшать остаток на складе.

Важная оговорка: мы намеренно делаем связь по названию, а не по id товара или другому служебному полю, как это принято при создании связей. А всё потому, что мы хотим повторить схему связей как на рисунке в начале — чтобы можно было в любой момент посмотреть на схему и понять, что откуда берётся и как что связывается. В боевом проекте мы бы делали связи строго по ID товаров.

Чтобы сделать такую таблицу, откроем консоль MySQL командой mysql -u root и выберем нашу учебную базу thecodeDB командой USE thecodeDB:

Создаём простые связи в базе данных
Появилось сообщение о смене базы данных, значит, можно продолжать

Теперь создадим в базе таблицу с товарами:

CREATE TABLE goods (
product VARCHAR(20) PRIMARY KEY,
count INT,
price INT
);

Создаём простые связи в базе данных
Query OK означает, что запрос выполнился без ошибок

Разберём команду подробнее:

  • CREATE TABLE goods  ← создать таблицу с названием goods;
  • product VARCHAR(20) PRIMARY KEY ← первое поле будет называться product, название товара может состоять из 20 символов, а ещё это поле у нас будет уникальным и мы будем использовать его для связи с другой таблицей;
  • count INT ←  второе поле с названием count, в нём будем хранить количество товаров, а для этого нам понадобится целочисленный тип данных INT.
  • price INT ← поле с названием price, где будет цена за штуку.

Заполняем товары

Сейчас таблица пустая — мы в этом убедимся, выполнив команду SELECT * FROM goods;, что означает «Выбери все записи из таблицы goods»:

Создаём простые связи в базе данных

Заполним таблицу товарами, используя расширенную версию команды INSERT, — в ней нужно явно указать, какое значение в какое поле отправляется:

INSERT INTO goods SET
product = 'стол',
count = 2,
price = 3000;

Создаём простые связи в базе данных

Проверим, добавилась ли запись — выведем всё содержимое таблицы с товарами:

Создаём простые связи в базе данных

Точно так же добавим два остальных товара:

Создаём простые связи в базе данных

Заполняем таблицу с клиентами

Теперь, когда мы знаем, как создавать и заполнять таблицы, сделаем таблицу с клиентами, причем поле «номер телефона» пометим как UNIQUE, потому что номер телефона будет уникальным для каждого покупателя. 

Ещё мы сделаем поле id, где будет храниться код покупателя. Обратите внимание на параметр AUTO INCREMENT — он означает, что при каждом добавлении нового клиента в базу код покупателя будет автоматически увеличиваться на единицу.

CREATE TABLE clients (
name VARCHAR(40),
phone VARCHAR(10) UNIQUE,
id INT AUTO_INCREMENT PRIMARY KEY
);

Заполним таблицу первыми клиентами, при этом id нам указывать не нужно — база сама будет вести нумерацию клиентов:

INSERT INTO clients SET
name = 'Миша',
phone = 9208381096;

INSERT INTO clients SET
name = 'Наташа',
phone = 9307265198;

INSERT INTO clients SET
name = 'Саша',
phone = 9307281096;

Создаём простые связи в базе данных

Cоздаём таблицу с покупками и связываем всё вместе

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

Чтобы это сделать, нам понадобится параметр FOREIGN KEY, который отвечает за связь главной и зависимой таблицы. Работает он так: 

  1. В новой таблице мы хотим использовать название товара и код клиента.
  2. Эти два поля будут связаны с двумя таблицами — одна с товарами, а другая с клиентами.
  3. Чтобы это сделать, мы создаём два поля, а потом внизу указываем с помощью параметра FOREIGN KEY, из какой таблицы их брать.

Создаём простые связи в базе данных

Сделаем тестовую покупку — добавим в таблицу с заказами запись о том, что Миша купил 2 табурета:

INSERT INTO orders SET
product = 'табурет',
amount = 2,
client_id = 1;

Создаём простые связи в базе данных

Но если мы попробуем добавить в таблицу запись о покупке товара, которого нет в таблице с товарами, база выдаст ошибку. Всё дело в том, что параметр FOREIGN KEY сначала проверит, есть ли указанный товар в таблице с товарами, и если его нет — не даст ничего записать в таблицу:

Создаём простые связи в базе данных
Дивана нет в списке товаров, поэтому запись в таблицу не добавилась

Что дальше

Чтобы посмотреть информацию о клиенте, который сделал заказ, можно использовать команду SELECT * FROM orders, clients WHERE orders.client_id = clients.id;

Распарсим этот запрос: 

SELECT — «выбери», то есть «выведи», «достань»;

* — «всё»;

FROM orders, clients — из таблиц orders и clients;

WHERE — если оно подходит под условие, что…;

orders.client_id = clients.id; — …айдишник клиента в таблице orders совпадает с айдишником клиента в таблице clients.

В ответ на такой запрос база данных выведет все записи из обеих таблиц с заказами и клиентами, где совпадает id клиента:

Создаём простые связи в базе данных
Видно, что табурет купил клиент Миша с номером телефона 9208381096 и порядковым номером 1 в таблице клиентов

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

Текст:

Михаил Полянин

Редактор:

Максим Ильяхов

Художник:

Алексей Сухов

Корректор:

Ирина Михеева

Вёрстка:

Мария Дронова

Соцсети:

Виталий Вебер

Читаете «Код»? Зарабатывайте на коде
Сфера ИТ и разработки постоянно растёт и требует новых кадров. Компании готовы щедро платить даже начинающим разработчикам, а опытных вообще отрывают с руками. Обучиться на разработчика можно в «Яндекс Практикуме».
Начать карьеру
Читаете «Код»? Зарабатывайте на коде Читаете «Код»? Зарабатывайте на коде Читаете «Код»? Зарабатывайте на коде Читаете «Код»? Зарабатывайте на коде
Получите ИТ-профессию
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.
Начать карьеру в ИТ
Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию
Еще по теме
Что означает предел в математике
Что означает предел в математике

Сага о погрешностях при участии слова lim

medium
Делаем собственный таймер для спорта
Делаем собственный таймер для спорта

Без рекламы и встроенных покупок.

hard
Как легко понять знаки Σ и П с помощью программирования
Как легко понять знаки Σ и П с помощью программирования

Для тех, кто подзабыл матешу

medium
ReferenceError: math is not defined — что это означает

Противная и неочевидная ошибка, которую очень легко исправить.

medium
Что означает ошибка OverflowError: math range error
Что означает ошибка OverflowError: math range error

Это ошибка переполнения из-за математических операций

easy
Что будет, если переменную разделить и сразу умножить на одно и то же число
Что будет, если переменную разделить и сразу умножить на одно и то же число

Будет ошибка, которую чертовски сложно отловить

easy
Моделируем игру в рулетку на Python
Моделируем игру в рулетку на Python

И строим наглядные графики.

easy
Пинг-понг на JavaScript

Да, вдвоём тоже можно.

easy
Шифр Вернама на JavaScript
Шифр Вернама на JavaScript

Невзламываемый шифр за 4 строчки кода.

medium
Как очень быстро и яростно добавить музыкальный трек на страницу
Как очень быстро и яростно добавить музыкальный трек на страницу

Всё для семьи

easy
medium