Практическое руководство по SQL

SQL - это язык программирования, который используется большинством систем управления реляционными базами данных (СУБД) для управления данными, хранящимися в табличной форме (то есть таблицами).

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

Существует множество различных систем управления реляционными базами данных (например, MySQL, PostgreSQL, SQL Server). Синтаксис SQL, который они адаптируют, может немного отличаться. Однако разница очень мала, поэтому, если вы научитесь использовать один, вы легко сможете переключиться на другой.

В этом посте мы разработаем простую реляционную базу данных для розничного бизнеса и создадим ее с помощью MySQL.

Ниже представлена ​​схема базы данных продаж. Он содержит 4 реляционные таблицы. Первая строка - это название таблицы. Остальные строки представляют столбцы в таблице.

  • Покупатель: содержит данные о покупателе и идентификатор магазина, в который покупатель чаще всего ходит.
  • Покупка: содержит сведения о покупке (в нашем случае каждый товар регистрируется как отдельная покупка).
  • Предмет: содержит описание предметов и цены. У этого розничного бизнеса есть несколько магазинов. Хотя предметы продаются во всех местах, для каждого предмета есть собственный магазин. Столбец «store_id» в таблице предметов указывает магазин, которому принадлежит этот предмет.
  • Магазин: содержит информацию, относящуюся к магазину.

Вы могли заметить инициалы «PK» (первичный ключ) и «FK» (внешний ключ) рядом с именами некоторых столбцов.

  • Первичный ключ - это столбец, который однозначно определяет каждую строку. Это похоже на индекс фрейма данных pandas.
  • Внешний ключ - это то, что связывает одну таблицу с другой. Внешний ключ содержит первичный ключ другой таблицы. Например, «item_id» в таблице покупок - это внешний ключ. Он хранит строки из первичного ключа в таблице элементов.

Внешние ключи имеют основополагающее значение при создании реляционных таблиц. Рассмотрим случай, когда мы храним описания товаров и цены в таблице покупок, а не в отдельной таблице товаров.

Если цена предмета меняется, нам нужно изменить цену для всех строк в таблице покупок, которые содержат этот предмет. Это большая работа. Когда цены на товары хранятся в отдельной таблице, нам просто нужно сделать одно обновление.

Поскольку таблица покупок связана с таблицей предметов с помощью «item_id» в качестве внешнего ключа, нам не нужно беспокоиться об обновлении таблицы покупок.

Задан дизайн базы данных. Мы можем приступить к его строительству прямо сейчас. Я буду использовать терминал для написания синтаксиса SQL. Есть также другие варианты, такие как MySQL Workbench.

Сначала нам нужно подключиться к серверу MySQL из терминала:

~$ sudo mysql -u root

Следующим шагом будет создание базы данных продаж и ее ввод:

mysql> create database sales;
mysql> use sales;

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

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

mysql> CREATE TABLE store(
    -> store_id INT PRIMARY KEY,
    -> address VARCHAR(20) NOT NULL,
    -> manager VARCHAR(20) NOT NULL
    -> );

Синтаксис оператора CREATE TABLE состоит из имени таблицы и описаний столбцов в скобках. Фраза первичного ключа используется для обозначения столбца первичного ключа.

Описание каждого столбца содержит имя столбца и тип данных. Вот типы данных, используемые SQL:

  • Int: целое число
  • Десятичный (M, N): число с плавающей запятой. M - общее количество цифр, N - количество десятичных цифр.
  • Varchar (N): строка (текст) длины N
  • Blob: большой двоичный объект
  • Дата: «ГГГГ-ММ-ДД»
  • Отметка времени: «ГГГГ-ММ-ДД ЧЧ: ММ: СС»

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

mysql> CREATE TABLE customer(
    -> cust_id INT PRIMARY KEY,
    -> f_name VARCHAR(20) NOT NULL,
    -> l_name VARCHAR(20) NOT NULL,
    -> gender CHAR(1),
    -> store_id INT,
    -> FOREIGN KEY (store_id) REFERENCES store(store_id) 
    -> ON DELETE SET NULL
    -> );

Вы могли заметить, что мы использовали фразу «NOT NULL» в описании некоторых столбцов. Это указывает на то, что строки в этом столбце не могут содержать никаких значений NULL (т.е. не могут быть пустыми).

Примечание. Столбцы первичного ключа не могут иметь никаких значений NULL, но нам не нужно указывать это явно, используя фразу «NOT NULL».

Столбец store_id в таблице клиентов является внешним ключом. Сначала нам нужно создать, а затем установить в качестве внешнего ключа.

В операторе внешнего ключа также необходимо указать ссылочную таблицу и имена столбцов.

Оператор ON DELETE используется для указания, что произойдет со значениями в столбце внешнего ключа, когда значения, на которые они ссылаются, будут удалены. Например, столбец store_id в таблице клиентов является внешним ключом. Он относится к столбцу store_id в таблице store (первичный ключ таблицы store). Если магазин удаляется из таблицы магазина, это также влияет на некоторые значения в таблице клиентов. ON DELETE SET NULL указывает, что эти значения будут заменены на NULL.

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

mysql> CREATE TABLE item(
    -> item_id INT,
    -> description VARCHAR(20),
    -> price DECIMAL(6,2) NOT NULL,
    -> store_id INT,
    -> PRIMARY KEY(item_id),
    -> FOREIGN KEY(store_id) REFERENCES store(store_id) 
    -> ON DELETE SET NULL
    -> );

Как видите, мы также можем объявить столбец в качестве первичного ключа после его создания.

Последняя таблица - это таблица покупок, которая содержит 6 столбцов, три из которых являются внешними ключами.

mysql> CREATE TABLE purchase(
    -> purchase_id INT PRIMARY KEY,
    -> item_qty INT NOT NULL,
    -> date DATE NOT NULL,
    -> item_id INT,
    -> cust_id INT,
    -> store_id INT,
    -> FOREIGN KEY(item_id) REFERENCES item(item_id)
    -> ON DELETE SET NULL,
    -> FOREIGN KEY(cust_id) REFERENCES customer(cust_id)
    -> ON DELETE SET NULL,
    -> FOREIGN KEY(store_id) REFERENCES store(store_id)
    -> ON DELETE SET NULL
    -> );

Теперь в нашей базе данных 4 таблицы. Мы можем проверить их с помощью оператора SHOW TABLES.

mysql> show tables;
+-----------------+
| Tables_in_sales |
+-----------------+
| customer        |
| item            |
| purchase        |
| store           |
+-----------------+

Мы успешно создали простую реляционную базу данных. Структуру таблицы можно просмотреть с помощью оператора DESCRIBE.

mysql> describe customer;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| cust_id  | int(11)     | NO   | PRI | NULL    |       |
| f_name   | varchar(20) | NO   |     | NULL    |       |
| l_name   | varchar(20) | NO   |     | NULL    |       |
| gender   | char(1)     | YES  |     | NULL    |       |
| store_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.11 sec)

Таблицы сейчас не содержат данных. В следующем посте я объясню, как заполнить эти таблицы соответствующими данными и выполнить запросы для получения данных.

Оператор SELECT используется для создания запросов и извлечения данных из таблиц. Если вы являетесь или планируете стать специалистом по обработке данных, вы, вероятно, будете использовать оператор select гораздо чаще, чем другие.

Запросы могут быть такими же простыми, как получение всех данных в таблице. Однако мы, скорее всего, будем выполнять сложные запросы для частичного извлечения данных. Оператор SELECT позволяет нам создавать запросы на основе условий, отношений и статистических показателей.

Спасибо за чтение. Пожалуйста, дайте мне знать, если у вас есть какие-либо отзывы.