База данных (БД) — это организованная структура для хранения, изменения и обработки взаимосвязанной информации. Обычно базы данных используют для взаимодействия с большими объёмами данных.
Базы данных существуют двух видов:
1. Реляционные - все данных хранятся в виде таблиц
2. Нереляционные - тут таблиц нет, данные хранятся в виде дерева или в формате ключ-значение
Реляционные базы данных
Таблицы в БД состоят из строк и столбцов. Каждый столбец имеет своё уникальное название и отмечает вид хранимой в нём информации. В каждой строке хранится информация об одном объекте. Таблица содержит определенное число столбцов, но может иметь любое количество строк.
Система Управления Базами Данных (СУБД, DBMS — DataBase Management System) — это комплекс программных средств, необходимых для создания структуры новой базы, ее наполнения, редактирования содержимого и отображения информации.
Для работы с данными, хранящимися в БД, используется специальный язык — SQL , который мы и будем изучать в данном курсе.
СУБД бывают разные, и языки для них тоже отличаются, являясь диалектами SQL. Наиболее распространенными СУБД являются MySQL, PostgreSQL, Oracle, Microsoft SQL Server. Для очень большого объёма данных также используют ClickHouse, Hadoop и другие СУБД.
В качестве нашей учебной СУБД используем PostgreSQL версии 11.
Для взаимодействия с базами данных через СУБД часто используется язык запросов SQL (Structured Query Language). Он применяется для создания, модификации и управления данными.
Таблица — это совокупность связанных данных, хранящихся в структурированном виде в БД. Она состоит из столбцов и строк.
В реляционных БД таблица — это набор элементов данных (значений), использующий модель вертикальных столбцов (имеющих уникальное имя) и горизонтальных строк.
Ячейка — место, где строка и столбец пересекаются. Таблица содержит определенное число столбцов, но может иметь любое количество строк.
Если в таблице каждая строка однозначно определяется одним или несколькими уникальными значениями, которые принимают её ячейки из определенного подмножества столбцов, то говорят, что у таблицы есть первичный ключ .
Иными словами, первичный ключ — это подмножество столбцов, которое уникально идентифицирует строку. Часто в качестве первичного ключа берут просто ID объекта, например, ID пользователя или ID покупки.
Запросы SQL
При использовании SQL очень важно соблюдать порядок ключевых слов.
- SELECT - отвечает за то, какие колонки будут выбираться из данных, так же здесь мы можем делать вычисления. Названия столбцов перечисляются через запятую. Что бы вывести все столбцы используют знак * - select *
- FROM - отвечает за то, из какой таблицы эти данные
- WHERE - отвечает за фильтрацию строк в данных
- LIMIT - вывод некоторого количества первых строк
- OFFSET - обрезание некоторого количество первых строк. LIMIT и OFFSET можно использовать вместе, их порядок не важен
- GROUP BY - определение групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM). можно использовать и без агрегатных функций, тогда его действие будет равносильно действию DISTINCT. Можно использовать для любого количества столбцов (комбинаций столбцов) таблицы. Идёт после where, указывается в начале строки
- HAVING - применяется после группировки (GROUP BY) для определения аналогичной фильтрации, которая выполняется в WHERE, но по значениям агрегатных функций в группах. Это необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM, а из групп таких строк. Поэтому такая проверка не может содержаться в предложении WHERE. Условия в HAVING можно комбинировать также, как и в Where , то есть с использованием скобок, and и or
- ORDER BY - сортировка данных. Оно указывается в самом конце запроса. После ORDER BY через запятую передаются названия колонок, по которым должна проходить сортировка. По умолчанию сортировка происходит по возрастанию - asc. Для сортировка по убыванию после названия колонки добавляется desc. Параметр NULLS FIRST / NULLS LAST - показывает, в начало списка или в конец ставить пустые значения сортируемой колонки
NB! Агрегатные функции, включенные в предложение SELECT запроса, не содержащего GROUP BY , исполняются над всеми результирующими строками этого запроса. Если же запрос содержит GROUP BY , то каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляют группу, и агрегатные функции выполняются для каждой группы отдельно.
NB! В самом SELECT не обязательно указывать агрегатную функцию, которая используется в HAVING . Также заметим, что в предложении HAVING нельзя использовать псевдоним (например, books_count), используемый для именования значений агрегатной функции в предложении SELECT , так как обработка названий столбцов для вывода на экран производится позже, чем фильтрация значений агрегатных функций.
Операторы
- AND - и
- OR - или
- AS - переименование столбцов, указывается в SELECT после столбца, который мы хотим переименовать, после AS указываем новое название
- BETWEEN - отрицание условия. Он указывает интервал, внутри которого должны быть элементы: book_rating between 3 and 4 — это то же самое, что и book_rating >= 3 and book_average_rating <= 4. Используется в поле where
- NULL - проверка на пустые строки. Можно выводить непустые данные с помощью is not null , либо пустые с помощью is null . Ставится после названия столбца. Используется в поле where
- DISTINCT - вывести уникальные значения столбца. Можно применять сразу к нескольким столбцам - получим все уникальные комбинации столбцов в этих строках. Ставится перед названием столбца в поле select
Для численных типов данных в SQL используются традиционные арифметические операторы:
- + сложение
- - вычитание
- * умножение
- / деление
- % остаток от деления
NB! Число с плавающей запятой записывается через запятую точку.
Ситуация: деление на 0
Если мы будем делить на 0 - система вернёт нам ошибку. Есть функция NULLIF - она в случае деления на 0 вернет отсутствие значения NULL. NULLIF(expr1, expr2) - эта функция при равенстве expr1 и expr2 возвращает NULL, а иначе — expr1.
Агрегатные функции
Основная суть агрегатной функции в том, что она позволяет вычислить единственное значение, обработав множество строк. Например, есть агрегатные функции, вычисляющие:
- count - количество не пустых значений
- sum - сумма
- avg - среднее
- max - максимум
- min - минимум
для набора строк.
Основные типы данных в SQL
- integer (или bigint) — используется для больших чисел;
- numeric — он особенно рекомендуется для хранения денежных сумм и других величин, где важна точность;
- serial — используется для создания первичных ключей таблицы (обычно для создания ID строки). Он очень удобен, так как при добавлении строк в таблицу сам проставляет им уникальный ID на 1 больше, чем предыдущий проставленный ID;
- real — подходит для чисел, где хорошая точность не очень нужна.
Самые распространенные символьные типы данных
- varchar для коротких строк (до 255 символов)
character varying(n ) , varchar( n ) - строка ограниченной переменной длины
character(n ) , char( n ) - строка фиксированной длины, дополненная пробелами
- text для строк, длина которых заранее не известна, но известно, что она может быть больше 255 символов.
NB! Для обозначения символьных типов данных используют одинарные кавычки. Двойные кавычки же используют, когда используют название колонок, в котором есть пробелы, либо символы из другого языка, либо когда они конфликтуют с ключевыми словами СУБД.
Также существует бинарный тип данных bool , данные в котором хранятся в виде true или false . Ещё один интересный тип — json . В json данные хранятся в формате key : value .
Однотипные данные можно сравнивать друг с другом. Перевод одного типа данных в другой превосходит через двойное двоеточие 2 .13 ::int - Перевод real в int
Кортеж
В реляционных БД данные хранятся в виде таблиц, которые состоят из строк и столбцов. Данные в строках связаны друг с другом и вместе формируют кортеж .
В случае реляционных БД кортеж — это строка в таблице. Для поиска по строкам (кортежам) используется ключевое слово WHERE , после которого перечисляются условия на кортежи данных. Where идёт после ключевого слова FROM .
P.S. На основе конспектов из магистратуры. Если, нашли ошибки, недочёты или хотите дополнить сказанное, всегда рада конструктивному мнению специалистов.