17 KiB
Работа с данными: Реляционные базы данных
 |
|---|
| Работа с данными: Реляционные базы данных - Скетчноут от @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" и "первичный ключ" взаимозаменяемо в этом уроке. Эти концепции применимы к DataFrame, которые вы изучите позже. Хотя DataFrame не используют терминологию "первичный ключ", вы заметите, что они ведут себя аналогично.
Создав таблицу города, давайте сохраним данные об осадках. Вместо дублирования полной информации о городе мы можем использовать 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 в новой таблице осадки. Этот столбец содержит значения, которые ссылаются на ID в таблице города. В технических терминах реляционных данных это называется внешний ключ; это первичный ключ из другой таблицы. Вы можете думать об этом как о ссылке или указателе. city_id 1 ссылается на Токио.
[!NOTE] Внешний ключ часто сокращается как FK
Извлечение данных
С разделением данных на две таблицы вы можете задаться вопросом, как их извлечь. Если мы используем реляционную базу данных, такую как MySQL, SQL Server или Oracle, мы можем использовать язык под названием Structured Query Language или SQL. SQL (иногда произносится как "сиквел") — это стандартный язык для извлечения и изменения данных в реляционной базе данных.
Для извлечения данных используется команда SELECT. В своей основе вы выбираете столбцы, которые хотите увидеть, из таблицы, в которой они находятся. Если вы хотите отобразить только названия городов, вы можете использовать следующий запрос:
SELECT city
FROM cities;
-- Output:
-- Tokyo
-- Atlanta
-- Auckland
SELECT указывает, какие столбцы вы хотите выбрать, а FROM указывает, из какой таблицы.
[NOTE] Синтаксис SQL нечувствителен к регистру, то есть
selectиSELECTозначают одно и то же. Однако, в зависимости от типа базы данных, столбцы и таблицы могут быть чувствительны к регистру. Поэтому рекомендуется всегда считать, что в программировании все чувствительно к регистру. В SQL принято писать ключевые слова заглавными буквами.
Приведенный выше запрос отобразит все города. А теперь представьте, что мы хотим отобразить только города в Новой Зеландии. Нам нужен какой-то фильтр. В SQL для этого используется ключевое слово WHERE, или "где что-то истинно".
SELECT city
FROM cities
WHERE country = 'New Zealand';
-- Output:
-- Auckland
Объединение данных
До сих пор мы извлекали данные из одной таблицы. Теперь мы хотим объединить данные из таблиц города и осадки. Это делается с помощью объединения (join). Вы фактически создаете связь между двумя таблицами, сопоставляя значения из столбца одной таблицы со столбцом другой.
В нашем примере мы сопоставим столбец city_id в таблице осадки со столбцом city_id в таблице города. Это позволит сопоставить данные об осадках с соответствующим городом. Тип объединения, который мы будем использовать, называется внутренним (inner join), что означает, что строки, не имеющие соответствия в другой таблице, не будут отображаться. В нашем случае у каждого города есть данные об осадках, поэтому все строки будут отображены.
Давайте извлечем данные об осадках за 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. Несмотря на наши усилия обеспечить точность, автоматические переводы могут содержать ошибки или неточности. Оригинальный документ на его родном языке следует считать авторитетным источником. Для получения критически важной информации рекомендуется профессиональный перевод человеком. Мы не несем ответственности за любые недоразумения или неправильные интерпретации, возникающие в результате использования данного перевода.