Язык программирования систем управления реляционными базами данных

Аналитик данных. Часть 9: Введение в SQL

SQL (Structured Query Language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных. Иначе говоря — это набор стандартов и методик для создания, хранения, изменения и извлечения данных из реляционных БД.

Простыми словами: SQL — это основной инструмент работы с реляционными базами данных (о них чуть ниже). С его помощью можно делать запросы к БД

С помощью SQL можно:

• Управлять и определять данные в базе данных, описывать их структуру,
• Создавать, изменять и удалять таблицы БД, а также управлять доступом к ним.
• Создавать функции и процедуры в БД
• Взаимодействовать с другими языками через модули

Основы БД и СУБД

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

Что за реляционная базы данных? Бывают другие?

Реляционные базы данных представляют собой базы данных, которые используются для хранения и предоставления доступа к взаимосвязанным элементам информации. Таким элементом может быть строка, таблица, запись. Каждая запись при этом имеет уникальный ID (еще называется ключом) к которому вы можете обратиться.

Простыми словами: Реляционная БД — это «табличная» база данных. Все данные организованны в виде набора таблиц, состоящих из столбцов и строк. В таблицах хранится информация об объектах, представленных в базе данных. Если применить аналогию, то всё прям как в привычных таблицах Excel. У каждой ячейки есть свой «адрес» и вы всегда сможете обратиться к ней напрямую.

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

Реляционные базы данных имеют под собой крайне важный аспект — целостность данных. Целостность данных – это полнота, точность и единообразие данных. Это позволяет гарантировать гарантировать точность и надежность данных.

Помимо реляционных БД есть и т.н. нереляционные . Такие NoSQL базы данных могут хранить деструктированную информацию, не иметь ограничений на типы хранимых данных (SQL-like БД их имеют) и создавать новые типы данных с произвольным набором атрибутов. Одним из самых известных продуктов NoSQL является MongoDB

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

Основные функции СУБД

• Управление данными во внешней памяти (на дисках);
• Управление данными в оперативной памяти с использованием дискового кэша;
• Журнализация изменений (сохранение истории), резервное копирование и восстановление базы данных после сбоев;
• Поддержка языков БД (язык определения данных, язык манипулирования данными).

Архитектура СУБД

СУБД реализованы на клиент-серверной архитектуре. Серверная часть у некоторых СУБД несколько отличается. Например, в PostgreSQL за управление файлами БД, подключение клиентских приложений и обработку запросов отвечает специальный сервисный процесс postgres.

В Oracle за это отвечают несколько фоновых фоновых процессов, сегментов памяти (называемых инстансами — instance) и файлов, содержащих конфигурации, транзакционные (или архивные) логи, данные и индексную информацию.

Что касается клиентских приложений, то здесь есть куда разгуляться. Это могут быть текстовые утилиты, графические приложения, веб-сервер, использующий базу данных для отображения веб-страниц, или специализированный инструмент для обслуживания БД.

Как и в других типичных клиент-серверных приложениях, клиент и сервер могут располагаться на разных компьютерах. В этом случае они взаимодействуют по сети TCP/IP. Важно не забывать это и понимать, что файлы, доступные на клиентском компьютере, могут быть недоступны (или доступны только под другим именем) на компьютере-сервере.

Хранение данных в СУБД. Что такое база данных?

Данные СУБД — это специальные объекты базы данных, именуемые таблицами (tables). Да, те самые таблицы из колонок и строк, которые всем известны. Иными словами, база данных в SQL — это таблицы, коллекции связанных между собой данных определенного количества, состоящих из колонок и строк . Типичный пример:

Внутри таблицы есть поля (fields). Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице. В нашем случае это id, name, countrycode, district, population

Запись — она же строка (row). Это горизонтальное вхождение в таблице. Колонка (column) — это вертикальное вхождение в таблице, содержащее всю информацию, связанную с определенным полем

С чем будем работать?

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

Многие команды и операции аналогичны и для других СУБД — Oracle или Microsoft SQL Server. Еще один плюс — PostgreSQL поддерживается для всех основных операционных систем — Windows, Linux, MacOS.

Установить PostgreSQL можно отсюда (для Windows). В качестве клиента используем DBeaver. Бесплатную Community-версию можно скачать на официальном сайте

Есть мнение, что начинать изучение SQL лучше сразу в консоли unix-like систем. Действительно, это имеет под собой основания — практически весь продашкн в современном мире работает под Linux. Но если вы совсем начинаете с нуля, рекомендуем всё же использовать GUI-инструменты.

При установке DBeaver устанавливайте все компоненты, которые просит установить мастер. Так же оставляйте порт (5423). В процессе у вас запросит пароль суперпользователя (если мастер не попросил ввести пароль — переустановите клиент в другую папку). Запомните его — данный пароль нужно будет вписать в свойства соединения, когда установка будет завершена

Самые нужные горячие клавиши DBeaver

И сразу запомните парочку хоткеев, которые вы будете использовать особенно часто:

Комментирование: CTRL+/ . Раскладка при этом должна быть английская. Если стоит русский язык, то ничего не произойдет.

Выполнение кода в соседнем окне: CTRL+\ .По умолчанию код выполняется в одном и том же окне. Если вам необходимо, чтобы часть вашего кода выполнилась в новом окне поможет эта комбинация клавиш

Ложка дёгтя в SQL

Единственное, в чем SQL реально неудобен, так это в поиске ошибок в коде

Примерно это выглядит так:

Источник

Зачем нужен язык SQL и как ему обучиться

Когда вы ищете информацию в поисковике или регистрируетесь в соцсети, вы сталкиваетесь с базами данных и SQL. Этот язык находится «под капотом» у многих сайтов и приложений. Рассказываем, зачем нужен SQL, как он работает и с чего начать изучение

Как работает язык SQL

SQL — язык запросов, с помощью которого можно получать информацию из базы данных. Пример использования SQL — отображение личного профиля в соцсети «ВКонтакте»:

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

Что такое база данных в SQL

База данных — это большие таблицы с данными, примерно как в Excel. С той разницей, что Excel может хранить до нескольких сотен мегабайт, а база данных — сотни гигабайт или терабайты.

Язык SQL предназначен для работы с реляционными базами данных — множеством таблиц, которые связаны между собой.

Таблица в базе данных SQL состоит из следующих элементов:

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

У каждой сущности в таблице свой первичный ключ. Например, ИНН человека — идеальный пример первичного ключа. Не может быть двух разных людей с одинаковым ИНН. Внешний ключ связывает разные таблицы и сущности в таблицах. К примеру, таблицу с фильмами и таблицу с актёрами, которые в этих фильмах снимались.

Потенциальный ключ — альтернативное значение, которое наряду с первичным ключом может идентифицировать сущность в таблице. Допустим, человека можно идентифицировать по ИНН, а можно — по ФИО и дате рождения.

Владимир Самойлов, тимлид в продуктовой разработке

2. Атрибут — столбец с одним параметром. Например, «Название», «Дата» или «Стоимость».

3. Домен — тип значения атрибута. Например, в столбце «Цена» стоимость должна быть указана числом с десятичной частью.

4. Кортеж — пронумерованная строка в таблице.

5. Значение — содержимое одной ячейки.

6. Индекс — объект базы данных, который упрощает поиск по ней. В таблице содержится множество строк в произвольном порядке, поиск нужной информации может занимать много времени. Индекс ускоряет процесс.

Представим, что у нас есть телефонный справочник. У него индексы — буквы. По букве можно легко найти фамилию человека. Если индекса нет, придётся долго искать нужный контакт, перебирая все строки в таблице.

Ещё есть уникальные индексы. Мы просим реляционную базу данных поддерживать какие-то ограничения: например, что не может быть паспортов с одинаковым номером. Тогда база при попытке записать в неё что-то нарушающее просто вернёт ошибку.

Владимир Самойлов, тимлид в продуктовой разработке

Для чего нужны операторы SQL

Операторы SQL — это слова и символы для выполнения разных операций с базой данных. Они делятся по типам задач, которые решают. Например, есть операторы чтения и операторы записи:

1. Операторы чтения помогают запросить информацию из базы данных. К таким относится SELECT.
2. Операторы записи добавляют новую информацию или обновляют старую в базе данных. К ним относятся INSERT, UPDATE.

Допустим, нужно выбрать всех белых котов из таблицы с домашними животными. Тогда запрос в базу данных будет таким:

Теперь нужно добавить в таблицу с животными новую породу собак — корги. Запрос будет таким:

Что такое СУБД и чем они различаются

СУБД, система управления базами данных, — программа, с помощью которой можно работать с базой: создавать таблицы, изменять их, получать из них информацию.

Это отдельная программа, которая где-то запущена, обычно на сервере. Однако вы можете запустить её и на своём компьютере, чтобы попрактиковаться с ней. То есть СУБД — фактически как оператор кол-центра, который работает 24/7. Ему можно позвонить и сказать: «Назови мне все адреса филиалов компании в Санкт-Петербурге». Только вы обращаетесь к СУБД с помощью SQL-запросов.

Владимир Самойлов, тимлид в продуктовой разработке

Выделяют несколько видов СУБД:

• Универсальные. Хорошо работают с быстрыми командами: внести изменения, добавить информацию, сделать выборку. Однако после достижения определённого объёма данных могут перегружаться и подвисать. Примеры универсальных СУБД: MySQL, MSSQL, PostgreSQL, Oracle.

Из этих систем хорошо масштабируется Oracle. Если у других СУБД проблемы с производительностью могут начаться на терабайте, у Oracle они начнутся на сотне терабайт.

Владимир Самойлов, тимлид в продуктовой разработке

• Колоночные. Такой тип СУБД особенно подходит для аналитики. Если обычные базы хранят данные построчно, то колоночные — по колонкам. Даже если нет индекса, такая СУБД сможет быстро собрать нужную информацию.

Известная колоночная СУБД — ClickHouse. Это продукт Яндекса, который вырос из базы данных для Яндекс Метрики. Она отлично подходит для аналитики. Однако не очень удобна для скоростного решения задач, а это, например, требуется в банкинге для проведения транзакций. В таких случаях лучше использовать универсальные базы данных.

Владимир Самойлов, тимлид в продуктовой разработке

С чего начать изучение SQL

Ознакомиться с теорией. Например, с вводным учебным пособием по SQL, где есть объяснения базовых понятий, основы работы с базами данных, операторами. Кроме туториала читайте профильные книги: «SQL. Полное руководство», «Введение в системы баз данных», «Программирование баз данных SQL. Типичные ошибки и их устранение».

Отработать на практике. На портале HackerRank есть много готовых задач по SQL. Задачи варьируются от простых до очень сложных. Особенно удобно, что есть решения: можно сверить ответы.

Общаться с сообществом. В Telegram есть сообщество начинающих дата-инженеров. Там обсуждают любые вопросы по SQL — от решения задач до полезных ресурсов для изучения.

Источник