Базы данных и весь SQL для начинающих за 50 минут

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

Слово реляционный произошло от английского слова relation. Которое переводится как связь.

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

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

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

И мы на самом деле можем вписать в таблицу  дополнительными поля.

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

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

Или если мы где-то ошиблись, например в имени автора, нам нужно будет изменить информацию в каждой строчке, в которой присутствует автор с ошибочным именем. А таких строчек может быть очень много.

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

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

Поэтому целесообразнее будет создавать две таблицы с книгами и авторами

Название книги
Автор
Трансферинг
реальности
3
Идиот
1
Преступление
и наказание
1
Финансист
4
Титан
4
Мастер и Маргарита
2
ФИО
Год рождения
Жанр книг
Количество проданных книг
Достоевский
Федор Михайлович
1821
Роман, повесть, рассказ
200
Булгаков Михаил Афанасьевич
1891
Роман, повесть, рассказ
300
Зеланд
Вадим
1963
Саморазвитие
100
Теодор Герман Альберт Драйзер
1871
Критический реализм
250

Обратите внимание, что в таблице с авторами у нас есть  столбец id c айдишником автора и в таблице с книгами  в столбце с авторами указаны эти номера.

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

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

Именно поэтому они и называются реляционными.

В реляционных базах данных есть таблицы которые состоят из столбцов и строк.

Строки называются записями и столбцы полями.

Реляционные базы данных также называют sql базами данных.

Почему именно sql

  • Простота: SQL – это декларативный язык, то есть он описывает, что нужно сделать, а не как это сделать. Это делает его понятным и легким в изучении.
  • Стандартизация: SQL – это международный стандарт, который поддерживается большинством реляционных БД.
  • Мощность: SQL позволяет выполнять сложные операции с данными.
  • Эффективность: SQL оптимизирован для работы с большими объемами информации.

SQL  (Structured Query Language)что в переводе означает язык структурированных запросов – это язык программирования, предназначенный для работы с реляционными базами данных (РБД). Он позволяет:

  • Определять структуру БД (создавать таблицы, столбцы, связи).
  • Добавлять новые данные в БД.
  • Изменять существующие данные.
  • Удалять ненужные данные.
  • Выбирать данные из БД по заданным критериям.
  • Сортировать и фильтровать данные.
  • Группировать данные по заданным критериям.
  • Выполнять вычисления над данными.
  • Предоставлять доступ к данным различным пользователям

Какие бывают реляционные базы данных

  • MySQL и PostgreSQL — Они используются в большинстве веб приложений.
  • MSSql —  популярна среди тех, кто работает со стеком Microsoft
  • SQLite — популярна в мобильных приложениях
  • Oracle — используется в крупных корпоративных системах например в большинстве банков

Нереляционные базы данных

Нереляционные базы данных (НРБД) – это альтернатива реляционным базам данных (РБД), не ограниченная табличной структурой. НРБД более гибкие и масштабируемые, чем РБД, и могут использоваться для хранения различных типов данных, включая JSON, XML, графы и т.д.

Основные типы НРБД:

1. Ключ-значение:

  • Хранят данные в виде пар ключ-значение.
  • Подходят для кэширования, хранения метаданных, геопространственных данных.
  • Примеры: Redis, Memcached,.

2. Документно-ориентированные:

  • Хранят данные в виде документов JSON, XML
  • Подходят для хранения слабоструктурированных данных,
  • Примеры: MongoDB

3. Колонные:

  • Хранят данные в виде столбцов, а не строк.
  • Подходят для аналитических приложений и Big Data.
  • Пример такой базы данных например: Cassandra, Amazon Redshift.

4. Графовые:

  • Хранят данные в виде графов, где узлы представляют собой сущности, а ребра – связи между ними.
  • Подходят для социальных сетей, рекомендательных систем, геопространственных данных.
  • Примеры: Amazon Neptune.

Применение НРБД:

  • NoSQL-приложения: НРБД идеально подходят для разработки NoSQL-приложений, где требуется высокая производительность, масштабируемость и гибкость.
  • Big Data: НРБД часто используются для хранения и обработки больших объемов данных.
  • Мобильные приложения: НРБД могут использоваться для хранения данных мобильных приложений.

Важно:

  • Не существует универсального решения. Выбор типа НРБД зависит от конкретных задач и требований проекта.
  • РБД и НРБД могут использоваться совместно для достижения оптимального результата

Команды SQL

CREATE TABLE authors (

id INT NOT NULL AUTO_INCREMENT,

firstname VARCHAR(255) NOT NULL,

lastname VARCHAR(255) NOT NULL,

PRIMARY KEY (id)

);

  • CREATE TABLE — это команда для создания новой таблицы.
  • authors — это имя новой таблицы.
  • id INT NOT NULL AUTO_INCREMENT — это поле id, которое будет иметь тип INT (целое число), не может быть пустым (NOT NULL) и будет автоматически увеличиваться (AUTO_INCREMENT) при добавлении новых записей.
  • firstname VARCHAR(255) NOT NULL — это поле firstname, которое будет иметь тип VARCHAR (строка) длиной 255 символов и не может быть пустым (NOT NULL).
  • lastname VARCHAR(255) NOT NULL — это поле lastname, которое будет иметь тип VARCHAR (строка) длиной 255 символов и не может быть пустым (NOT NULL).
  • PRIMARY KEY (id) — это первичный ключ таблицы. Он должен быть уникальным для каждой записи.

INSERT INTO authors (firstname, lastname) VALUES (‘Вадим’, ‘Зеланд’);

  • INSERT INTO — это команда для добавления новой записи в таблицу.
  • authors — это имя таблицы, в которую мы добавляем запись.
  • firstname, lastname — это имена столбцов, в которые мы добавляем значения.
  • ‘Вадим’, ‘Зеланд’ — это значения, которые мы добавляем в столбцы firstname и lastname.

SELECT firstname FROM authors WHERE lastname=‘Зеланд’;

  • SELECT — это команда для выборки данных из таблицы.
  • firstname — это имя столбца, из которого будут выбираться данные.
  • FROM — это ключевое слово, которое указывает таблицу, из которой будут выбираться данные.
  • authors — это имя таблицы, из которой будут выбираться данные.
  • WHERE — это ключевое слово, которое используется для определения условия, которому должны соответствовать записи.
  • lastname=’Зеланд’ — это условие, которое означает, что фамилия автора должна быть «Зеланд».

UPDATE authors

SET firstname = ‘Михаил’, lastname = ‘Булгаков’

WHERE firstname = ‘Дмитрий’ AND lastname = ‘Троцкий’;

  • UPDATE — это команда для обновления существующих записей в таблице.
  • authors — это имя таблицы, в которой мы обновляем записи.
  • SET firstname = ‘Михаил’, lastname = ‘Булгаков’ — это изменение значений полей firstname и lastname на «Михаил» и «Булгаков» соответственно.
  • WHERE firstname = ‘Дмитрий’ AND lastname = ‘Троцкий’ — это условие, которое определяет, какие записи будут обновлены. В данном случае будут обновлены только те записи, где поле firstname равно «Дмитрий» и поле lastname равно «Троцкий».

DELETE FROM authors

WHERE firstname = ‘Феодор’ AND lastname = ‘Достоевский’;

  • DELETE — это команда для удаления записей из таблицы.
  • FROM — это ключевое слово, которое указывает таблицу, из которой будут удаляться записи.
  • authors — это имя таблицы, из которой будут удаляться записи.
  • WHERE — это ключевое слово, которое используется для определения условия, которому должны соответствовать записи.
  • firstname = ‘Феодор’ AND lastname = ‘Достоевский’ — это условие, которое означает, что записи будут удалены, если имя автора («firstname») равно «Феодор», а фамилия («lastname») — «Достоевский».
  • Команда DELETE удаляет только те записи, которые соответствуют условию WHERE.
  • Если условие не задать, то команда удалит все
  • И если условию WHERE не соответствует ни одна запись, то команда не удалит ни одной записи.

DELETE FROM authors

WHERE firstname IN (‘Вадим’, ‘Теодор’) AND lastname IN (‘Зеланд’, ‘Драйзер’);

  • DELETE — это команда для удаления записей из таблицы.
  • FROM — это ключевое слово, которое указывает таблицу, из которой будут удаляться записи.
  • authors — это имя таблицы, из которой будут удаляться записи.
  • WHERE — это ключевое слово, которое используется для определения условия, которому должны соответствовать записи.
  • firstname IN (‘Вадим’, ‘Теодор’) AND lastname IN (‘Зеланд’, ‘Драйзер’) — это условие, которое означает, что записи будут удалены, если имя автора («firstname») находится в списке «Вадим», «Теодор», а фамилия («lastname») — в списке «Зеланд», «Драйзер».

CREATE TABLE books (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(255) NOT NULL,

author_id INT NOT NULL,

price DECIMAL(10,2) NOT NULL,

FOREIGN KEY (author_id) REFERENCES authors(id)

);

  • CREATE TABLE books: Создает таблицу с именем books.
  • id INT AUTO_INCREMENT PRIMARY KEY:
    • Определяет столбец id как первичный ключ (PRIMARY KEY).
    • Значения id будут автоматически увеличиваться (AUTO_INCREMENT).
    • INT указывает, что тип данных столбца id — целое число.
  • title VARCHAR(255) NOT NULL:
    • Определяет столбец title для хранения названия книги.
    • VARCHAR(255) указывает, что тип данных title — строка длиной до 255 символов.
    • NOT NULL означает, что значение title не может быть пустым.
  • author_id INT NOT NULL:
    • Определяет столбец author_id для хранения ID автора книги.
    • INT указывает, что тип данных author_id — целое число.
    • NOT NULL означает, что значение author_id не может быть пустым.
  • price DECIMAL(10,2) NOT NULL:
    • Определяет столбец price для хранения цены книги.
    • DECIMAL(10,2) указывает, что тип данных price — десятичное число с 10 цифрами в целом и 2 цифрами после запятой.
    • NOT NULL означает, что значение price не может быть пустым.
  • FOREIGN KEY (author_id) REFERENCES authors(id):
    • Определяет внешний ключ (FOREIGN KEY) в столбце author_id таблицы books.
    • Ссылается на первичный ключ (id) таблицы authors.
    • Это гарантирует, что author_id в таблице books всегда будет соответствовать существующему ID автора в таблице authors.

Join

SELECT books.title, authors.firstname, authors.lastname

FROM authors

INNER JOIN books

ON authors.id = books.author_id;

  • SELECT: Это ключевое слово, которое говорит базе данных, что вы хотите извлечь данные.
  • books.title:
    • books — это название таблицы, в которой хранится информация о книгах.
    • title — это столбец в таблице books, который содержит названия книг.
    • Таким образом, эта часть говорит базе данных, что вы хотите извлечь столбец title из таблицы books.
  • authors.firstname:
    • authors — это название таблицы, в которой хранится информация об авторах.
    • firstname — это столбец в таблице authors, который содержит имена авторов.
    • Таким образом, эта часть говорит базе данных, что вы хотите извлечь столбец firstname из таблицы authors.
  • authors.lastname:
    • authors — это название таблицы, в которой хранится информация об авторах.
    • lastname — это столбец в таблице authors, который содержит фамилии авторов.
    • Таким образом, эта часть говорит базе данных, что вы хотите извлечь столбец lastname из таблицы authors.
  • FROM: Это ключевое слово, которое говорит базе данных, из каких таблиц вы хотите извлечь данные.
  • authors: Это название таблицы, из которой вы хотите извлечь данные. В данном случае вы хотите извлечь данные из таблицы authors, которая содержит информацию об авторах.
  • INNER JOIN: Это тип соединения таблиц.
    • INNER JOIN означает, что будут объединены только те строки из двух таблиц, которые имеют совпадающие значения в указанном условии.
  • books: Это название таблицы, которую вы хотите объединить с таблицей authors.
  • ON:
    • Это ключевое слово, которое указывает условие для объединения строк.
  • authors.id = books.author_id:
    • Это условие для объединения строк.
    • Оно говорит, что строки из таблицы authors должны быть объединены со строками из таблицы books, где значение id автора (authors.id) совпадает со значением author_id книги (books.author_id).

У нас есть еще LEFT JOIN:

показает все книги, даже если у них нет автора в таблице authors:

SELECT books.title, authors.firstname, authors.lastname

FROM authors

LEFT JOIN books

ON authors.id = books.author_id;

 RIGHT JOIN:

Показываем всех авторов и их книги (даже если у них нет книг):

SELECT authors.firstname, authors.lastname, books.title

FROM authors

RIGHT JOIN books

ON authors.id = books.author_id;

Агрегатные функциях в sql

Агрегатные функции, это мощные инструменты для работы с группами. Они позволяют

  • Подсчитывать количество записей (COUNT),
  • Суммировать значения (SUM),
  • Вычислять средние значения (AVG),
  • Находить минимальные (MIN) и максимальные (MAX) значения в группах данных.

И давайте сразу на примере, попробуем написать следующий запрос. Начало запроса стадртное, которое мы уже писали с джоином

SELECT books.title, books.price, authors.firstname, authors.lastname

FROM authors

INNERJOIN books

ON authors.id = books.author_id

WHERE books.price > 500;

Он показывает все книги, которые стоят дороже 500 рублей

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

SELECT authors.firstname, authors.lastname, SUM(books.price) AS total_price

FROM authors

INNER JOIN books

ON authors.id = books.author_id

GROUP BY authors.id

HAVING total_price > 500;

SQL-запросы с использованием SUM и HAVING (без AS)

  • SUM:
    • Подсчитывает сумму значений в указанном столбце.
  • HAVING:
    • Фильтрует группы строк, полученные в результате запроса, на основе условия.
    • Используется после GROUP BY для применения условий к группам, а не к отдельным строкам.
  1. SELECT authors.firstname, authors.lastname, SUM(books.price) AS total_price:
    • Выбирает имена и фамилии авторов (authors.firstname, authors.lastname), а также суммарную цену книг каждого автора (SUM(books.price) AS total_price).
  2. FROM authors INNER JOIN books ON authors.id = books.author_id:
    • Объединяет таблицы authors и books по id автора.
  3. GROUP BY authors.id:
    • Группирует результаты по id автора,
    • что означает, что суммарная цена будет рассчитана для каждого автора.
  4. HAVING total_price > 500:
    • Фильтрует полученные группы,
    • оставляя только те, где суммарная цена (total_price) больше 500.
  5. SELECT authors.firstname, authors.lastname, SUM(books.price) AS total_price:
    • Выбирает имена и фамилии авторов (authors.firstname, authors.lastname), а также суммарную цену книг каждого автора (SUM(books.price) AS total_price).
  6. FROM authors INNER JOIN books ON authors.id = books.author_id:
    • Объединяет таблицы authors и books по id автора.
  7. GROUP BY authors.id:
    • Группирует результаты по id автора,
    • что означает, что суммарная цена будет рассчитана для каждого автора.
  8. HAVING total_price > 500:
    • Фильтрует полученные группы,
    • оставляя только те, где суммарная цена (total_price) больше 500.

А теперь, давайте попробуем вывести количество авторов с книгами дороже 500 рублей:

Напишем следующий запрос

SELECT COUNT(*) AS author_count

FROM authors

INNER JOIN books

ON authors.id = books.author_id

GROUP BY authors.id

HAVING SUM(books.price) > 500;

  1. SELECT COUNT(*) AS author_count:
    • Подсчитывает количество авторов (COUNT(*) AS author_count).
  2. FROM authors INNER JOIN books ON authors.id = books.author_id:
    • Объединяет таблицы authors и books по id автора.
  3. GROUP BY authors.id:
    • Группирует результаты по id автора.
  4. HAVING SUM(books.price) > 500:
    • Фильтрует полученные группы,
    • оставляя только те, где суммарная цена (SUM(books.price) каждой группы) больше 500

Основное различие между HAVING и WHERE в SQL заключается в том, когда они применяются в запросе и какие данные они фильтруют.

WHERE:

  • Используется перед группировкой данных с помощью GROUP BY.
  • Фильтрует отдельные строки на основе заданных условий.
  • Гарантирует, что в результатах будут только те строки, которые соответствуют критериям WHERE, до любой группировки или агрегирования.

HAVING:

  • Используется после группировки данных с помощью GROUP BY.
  • Фильтрует группы строк на основе условия, применяемого к агрегированным данным (таким как SUM, COUNT или AVG).
  • Гарантирует, что в конечных результатах будут только те группы, которые удовлетворяют условию HAVING, после группировки и агрегирования.

ALTER

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

ALTER TABLE books

ADD genre VARCHAR(255) NOT NULL;

 

UPDATE books

SET genre =

CASE

  WHEN title LIKE ‘%Преступление%’ THEN ‘Детектив’

  WHEN title LIKE ‘%Идиот%’ THEN ‘Роман’

  WHEN title LIKE ‘%Три товарища%’ THEN ‘Военный роман’

  WHEN title LIKE ‘%Триумфальная арка%’ THEN ‘Военный роман’

  WHEN title LIKE ‘%Мастер%’ THEN ‘Фэнтези’

  WHEN title LIKE ‘%Собачье%’ THEN ‘Сатира’

  WHEN title LIKE ‘%Пока-я-не-я%’ THEN ‘Фэнтези’

  WHEN title LIKE ‘%Финансист%’ THEN ‘Драма’

  WHEN title LIKE ‘%Титан%’ THEN ‘Драма’

  WHEN title LIKE ‘%Пространство%’ THEN ‘Фантастика’

  WHEN title LIKE ‘%Шелест%’ THEN ‘Фантастика’

  ELSE ‘Не определено’

END;

  1. ALTER TABLE books ADD genre VARCHAR(255) NOT NULL;:
  • Эта строка добавляет новый столбец с названием «genre» в таблицу «books».
  • Тип столбца — VARCHAR(255), что означает, что он может хранить текстовые значения длиной до 255 символов.
  • NOT NULL указывает, что столбец не может содержать пустые значения.
  1. UPDATE books SET genre = …:
  • Эта строка обновляет столбец «genre» для каждой строки в таблице «books».
  • CASE используется для определения жанра каждой книги на основе ее названия (title).
  • WHEN используется для проверки соответствия названия книги заданному шаблону (LIKE).
  • THEN используется для указания жанра, который будет назначен книге, если совпадение найдено.
  • ELSE используется для назначения жанра «Не определено» книгам, которые не соответствуют ни одному из шаблонов.

 

 

Понравилась статья? Поделиться с друзьями: