В статье про виды баз данных мы рисовали простую схему базы для интернет-магазина — в ней товары, клиенты и покупки были связаны между собой, как в примере ниже. Зайдя в товары, можно посмотреть, сколько чего продано и кто это купил.
Сегодня мы сделаем то же самое, но уже в настоящей базе данных и с таблицами.
Что понадобится
Это простой проект, поэтому всё, что нам будет нужно, — это установленная MySQL на домашнем компьютере или на сервере. Удалённо подключаться к самой базе мы пока не будем, а вместо этого попрактикуемся в SQL-запросах. Если базы нет ни там ни там, поработайте в онлайн-компиляторе SQL — главное, не перезагружайте страницу.
Мы уже коротко писали о том, что такое SQL-запрос и как он выглядит, поэтому, чтобы было проще, перечитайте статью про язык SQL, а потом возвращайтесь сюда.
Таблица с товарами
В таблице с товарами у нас будет три столбца, причём главным будет название товара:
- Название ← по этому параметру мы будем связывать эту таблицу с другой.
- Количество ← остаток на складе.
- Цена.
Связь между таблицами нам понадобится для связи товаров с покупками — при продаже мы будем брать из товаров цену и уменьшать остаток на складе.
Важная оговорка: мы намеренно делаем связь по названию, а не по id товара или другому служебному полю, как это принято при создании связей. А всё потому, что мы хотим повторить схему связей как на рисунке в начале — чтобы можно было в любой момент посмотреть на схему и понять, что откуда берётся и как что связывается. В боевом проекте мы бы делали связи строго по ID товаров.
Чтобы сделать такую таблицу, откроем консоль MySQL командой mysql -u root и выберем нашу учебную базу thecodeDB командой USE thecodeDB:
Теперь создадим в базе таблицу с товарами:
CREATE TABLE goods (
product VARCHAR(20) PRIMARY KEY,
count INT,
price INT
);
Разберём команду подробнее:
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, который отвечает за связь главной и зависимой таблицы. Работает он так:
- В новой таблице мы хотим использовать название товара и код клиента.
- Эти два поля будут связаны с двумя таблицами — одна с товарами, а другая с клиентами.
- Чтобы это сделать, мы создаём два поля, а потом внизу указываем с помощью параметра 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 клиента:
SELECT — одна из основных команд в SQL-запросах, и с ней мы будем работать чаще всего. У неё много параметров и возможностей для конструирования запросов, поэтому в следующий раз мы займемся только ей.
