 |
|---|
| Работа с данными: реляционные базы данных - Скетчноут от @nitya |
Возможно, вы уже использовали электронную таблицу для хранения информации. У вас был набор строк и столбцов, где строки содержали информацию (или данные), а столбцы описывали эту информацию (иногда называемую метаданными). Реляционная база данных построена на этом основном принципе столбцов и строк в таблицах, позволяя иметь информацию, распределённую по нескольким таблицам. Это позволяет работать с более сложными данными, избегать дублирования и иметь гибкость в способах исследования данных. Давайте изучим концепции реляционной базы данных.
В основе реляционной базы данных лежат таблицы. Как и в электронной таблице, таблица — это набор столбцов и строк. Строка содержит данные или информацию, с которой мы хотим работать, например, название города или количество осадков. Столбцы описывают данные, которые они хранят.
Начнём наше исследование с создания таблицы для хранения информации о городах. Мы можем начать с их названия и страны. Это можно сохранить в таблице следующим образом:
| Город | Страна |
|---|---|
| Токио | Япония |
| Атланта | Соединённые Штаты |
| Окленд | Новая Зеландия |
Обратите внимание, что имена столбцов город, страна и население описывают хранимые данные, а каждая строка содержит информацию об одном городе.
Возможно, таблица выше кажется вам достаточно знакомой. Давайте добавим дополнительные данные в нашу растущую базу — годовое количество осадков (в миллиметрах). Мы сосредоточимся на 2018, 2019 и 2020 годах. Если добавить данные для Токио, это может выглядеть так:
| Город | Страна | Год | Количество |
|---|---|---|---|
| Токио | Япония | 2020 | 1690 |
| Токио | Япония | 2019 | 1874 |
| Токио | Япония | 2018 | 1445 |
Что вы замечаете в нашей таблице? Вы, возможно, заметили, что мы дублируем название и страну города снова и снова. Это может занимать много места и в основном не нужно иметь несколько копий. В конце концов, у Токио только одно имя, которое нас интересует.
Хорошо, попробуем другой подход. Добавим новые столбцы для каждого года:
| Город | Страна | 2018 | 2019 | 2020 |
|---|---|---|---|---|
| Токио | Япония | 1445 | 1874 | 1690 |
| Атланта | Соединённые Штаты | 1779 | 1111 | 1683 |
| Окленд | Новая Зеландия | 1386 | 942 | 1176 |
Хотя это избегает дублирования строк, это добавляет несколько других проблем. Нам пришлось бы изменять структуру таблицы каждый раз, когда появляется новый год. Кроме того, по мере роста данных, наличие годов в виде столбцов усложнит извлечение и вычисление значений.
Вот почему нам нужны несколько таблиц и связи между ними. Разделяя данные, мы можем избежать дублирования и получить большую гибкость в работе с данными.
Вернёмся к нашим данным и решим, как их разделить. Мы знаем, что хотим хранить название и страну для наших городов, поэтому это, вероятно, лучше всего сделать в одной таблице.
| Город | Страна |
|---|---|
| Токио | Япония |
| Атланта | Соединённые Штаты |
| Окленд | Новая Зеландия |
Но прежде чем создавать следующую таблицу, нам нужно понять, как ссылаться на каждый город. Нам нужна какая-то форма идентификатора, ID или (в технических терминах баз данных) первичный ключ. Первичный ключ — это значение, используемое для идентификации одной конкретной строки в таблице. Хотя это может быть основано на самом значении (например, можно использовать название города), он почти всегда должен быть числом или другим идентификатором. Мы не хотим, чтобы id когда-либо менялся, так как это нарушит связь. В большинстве случаев первичный ключ или id — это автоматически сгенерированное число.
✅ Первичный ключ часто сокращают как PK
| city_id | Город | Страна |
|---|---|---|
| 1 | Токио | Япония |
| 2 | Атланта | Соединённые Штаты |
| 3 | Окленд | Новая Зеландия |
✅ Вы заметите, что в этом уроке мы используем термины "id" и "первичный ключ" взаимозаменяемо. Эти концепции применимы и к DataFrames, которые вы изучите позже. DataFrames не используют терминологию "первичный ключ", однако вы заметите, что они ведут себя очень похоже.
Создав таблицу городов, давайте сохраним данные об осадках. Вместо того чтобы дублировать полную информацию о городе, мы можем использовать id. Также следует убедиться, что в новой таблице есть столбец id, так как все таблицы должны иметь id или первичный ключ.
| rainfall_id | city_id | Год | Количество |
|---|---|---|---|
| 1 | 1 | 2018 | 1445 |
| 2 | 1 | 2019 | 1874 |
| 3 | 1 | 2020 | 1690 |
| 4 | 2 | 2018 | 1779 |
| 5 | 2 | 2019 | 1111 |
| 6 | 2 | 2020 | 1683 |
| 7 | 3 | 2018 | 1386 |
| 8 | 3 | 2019 | 942 |
| 9 | 3 | 2020 | 1176 |
Обратите внимание на столбец city_id в новой таблице rainfall. Этот столбец содержит значения, которые ссылаются на ID в таблице cities. В технических терминах реляционных данных это называется внешним ключом; это первичный ключ из другой таблицы. Вы можете просто думать о нём как о ссылке или указателе. city_id 1 ссылается на Токио.
Note
Внешний ключ часто сокращают как FK
Разделив данные на две таблицы, вы, возможно, задаётесь вопросом, как их извлечь. Если мы используем реляционную базу данных, такую как MySQL, SQL Server или Oracle, мы можем использовать язык, называемый структурированным языком запросов или SQL. SQL (иногда произносится как "сиквел") — это стандартный язык для извлечения и изменения данных в реляционной базе данных.
Для извлечения данных используется команда SELECT. В основе вы выбираете столбцы, которые хотите видеть, из таблицы, в которой они содержатся. Если вы хотите отобразить только названия городов, можно использовать следующий запрос:
SELECT city
FROM cities;
-- Output:
-- Tokyo
-- Atlanta
-- AucklandSELECT — это место, где вы перечисляете столбцы, а FROM — где указываете таблицы.
Note
Синтаксис SQL нечувствителен к регистру, то есть select и SELECT означают одно и то же. Однако в зависимости от типа используемой базы данных столбцы и таблицы могут быть чувствительны к регистру. Поэтому лучшая практика — всегда считать всё в программировании чувствительным к регистру. При написании SQL-запросов общепринято писать ключевые слова заглавными буквами.
Приведённый выше запрос отобразит все города. Представим, что мы хотим отобразить только города в Новой Зеландии. Нам нужен какой-то фильтр. Ключевое слово SQL для этого — WHERE, или "где что-то истинно".
SELECT city
FROM cities
WHERE country = 'New Zealand';
-- Output:
-- AucklandДо сих пор мы извлекали данные из одной таблицы. Теперь мы хотим объединить данные из таблиц cities и rainfall. Это делается путём соединения их вместе. Вы фактически создадите шов между двумя таблицами и сопоставите значения из столбца каждой таблицы.
В нашем примере мы сопоставим столбец city_id в rainfall со столбцом city_id в cities. Это сопоставит значение осадков с соответствующим городом. Тип соединения, который мы выполним, называется внутренним соединением, что означает, что если какие-то строки не совпадают с чем-либо из другой таблицы, они не будут отображаться. В нашем случае у каждого города есть данные об осадках, поэтому всё будет отображено.
Давайте извлечём данные об осадках за 2019 год для всех наших городов.
Мы сделаем это поэтапно. Первый шаг — соединить данные, указав столбцы для шва — city_id, как было выделено ранее.
SELECT cities.city
rainfall.amount
FROM cities
INNER JOIN rainfall ON cities.city_id = rainfall.city_idМы выделили два столбца, которые хотим получить, и указали, что хотим соединить таблицы по city_id. Теперь мы можем добавить оператор WHERE, чтобы отфильтровать только 2019 год.
SELECT cities.city
rainfall.amount
FROM cities
INNER JOIN rainfall ON cities.city_id = rainfall.city_id
WHERE rainfall.year = 2019
-- Output
-- city | amount
-- -------- | ------
-- Tokyo | 1874
-- Atlanta | 1111
-- Auckland | 942Реляционные базы данных основаны на разделении информации между несколькими таблицами, которые затем объединяются для отображения и анализа. Это обеспечивает высокую степень гибкости для выполнения вычислений и других операций с данными. Вы познакомились с основными концепциями реляционной базы данных и узнали, как выполнить соединение между двумя таблицами.
В интернете доступно множество реляционных баз данных. Вы можете исследовать данные, используя навыки, которые вы изучили выше.
На Microsoft Learn доступно несколько ресурсов для продолжения изучения SQL и концепций реляционных баз данных
- Описание концепций реляционных данных
- Начало работы с запросами на Transact-SQL (Transact-SQL — это версия SQL)
- SQL-контент на Microsoft Learn
Отказ от ответственности:
Этот документ был переведен с помощью сервиса автоматического перевода Co-op Translator. Несмотря на наши усилия по обеспечению точности, имейте в виду, что автоматический перевод может содержать ошибки или неточности. Оригинальный документ на его исходном языке следует считать авторитетным источником. Для получения критически важной информации рекомендуется использовать профессиональный перевод, выполненный человеком. Мы не несем ответственности за любые недоразумения или неправильные толкования, возникшие в результате использования данного перевода.