Стоит ли понимать, что происходит под капотом? Да! В этой статье рассматриваются некоторые основы SQL.

Примечание. Команды SQL в этой статье в основном используются с PostgreSQL, эти команды могут отличаться от других баз данных. В статье также предполагается, что у вас есть практические знания Ruby/Rails.

Active Record — это замечательная абстракция, предоставленная нам Rails, которая позволяет нам легко и осмысленно взаимодействовать с нашей базой данных. Это, однако, может быть проблематичным для тех, кто плохо разбирается в SQL как таковом. Я был виновен в этом и всегда думал о том, чтобы дополнить эту область своих знаний. Недавно я прошел курс начальной подготовки по SQL на Udemy и понял, что SQL не так пугает, как я сначала думал, и есть несколько важных аспектов, о которых вам нужно знать. Я буду обсуждать некоторые аспекты SQL, чтобы не только укрепить свои знания, но и пролить свет на тот факт, что иметь прочную основу в SQL важно для того, чтобы быть разработчиком, особенно в роли полного стека или серверной части. .

Как человек, который плохо знаком с SQL, вы можете бояться взаимодействия с операторами SQL. Что вы можете сделать, пока вы наращиваете свои навыки, так это активировать регистратор ActiveRecord, чтобы вы могли видеть SQL-запросы, выполняемые за вашими методами ActiveRecord. Добавление приведенной ниже команды в файл среды активирует регистратор.

ActiveRecord::Base.logger = Logger.new(STDOUT)

Как только вы это сделаете, вы сможете видеть журналы в своем терминале, как это, где вы можете видеть, что контент и избранное для пользователя с идентификатором пользователя 1 выбираются:

Соответствующий рубиновый код, как говорит регистратор, находится в строке 16 моего контроллера аутентификации. На изображении ниже показано, что методы, которые мы вызывали для вызова этого запроса, были @user.content и @user.favorites. Я могу вызывать эти методы благодаря отношениям, которые я создал в своих моделях. Даже схема, созданная с помощью ActiveRecord Migrations, выполняет операторы создания таблиц в SQL! Мы можем иметь эти отношения через внешние ключи и соединять таблицы.

С этого момента я собираюсь разобрать некоторые важные аспекты SQL.

Основы SQL

Самый простой оператор — это оператор SELECT. Формат SELECT col1 FROM table;. Это в значительной степени говорит само за себя, вы выбираете конкретный столбец из конкретной таблицы. Возможно, вы также видели оператор SELECT *…, который запрашивает все записи определенной таблицы. Как правило, это не рекомендуется, поскольку потенциально вы можете запрашивать тысячи записей без необходимости. Вы также можете запросить отдельные значения в столбце, добавив ключевое слово DISTINCT перед именем столбца. Чтобы запросить определенные строки в вашей таблице, вы должны использовать такой синтаксис: SELECT col1 FROM table WHERE condition;. Часть условия будет включать условие, основанное на следующих операторах:

  1. = (равно)
  2. > (Лучше чем)
  4. ›= (больше или равно)
  5. ‹= (меньше или равно)
  6. ‹› ИЛИ != (Не равно)
  7. И (логический оператор И)
  8. ИЛИ (логический оператор ИЛИ)

Одно замечание об операторе И заключается в том, что вы не можете использовать его в одном и том же столбце, однако вы можете использовать ИЛИ в том же столбце таблицы.

Вы также можете получить подсчет количества строк определенного столбца в таблице, используя следующее: SELECT COUNT(col1) FROM table;. COUNT — это агрегатная функция, о которой я расскажу чуть дальше.

Ключевое слово LIMIT используется для ограничения количества возвращаемых строк. Примером может служить SELECT col1 FROM table LIMIT 5;. Это отлично работает, когда вы хотите, например, найти самую высокую цену чего-либо или самую низкую цену. Потенциально вы можете расположить строки в порядке ASC или DESC и ограничиться 1. Оператор ORDER BY используется для определения порядка, и его синтаксис следующий: SELECT col1 FROM table ORDER BY col1 ASC;.

В дополнение к логическим операторам мы также можем использовать BETWEEN для указания диапазона значений. Например, мы могли бы захотеть вернуть цены товаров, которые находятся в определенном диапазоне, мы могли бы использовать следующий оператор: ВЫБЕРИТЕ цены МЕЖДУ 5 И 10 ОТ товаров;. Мы также можем использовать синтаксис IN, чтобы проверить, имеет ли столбец определенное значение среди набора значений. Например, синтаксис будет таким: ВЫБЕРИТЕ цены ИЗ предметов, ГДЕ цены В (4, 10, 2);

Еще одно мощное ключевое слово, которое используется для сопоставления строк с образцом, — это ключевое слово LIKE. Операторы LIKE используют подстановочные знаки для указания шаблона совпадающих строк.

  1. % (используется для сопоставления любого количества символов)
  2. _ (используется для сопоставления одного символа)

Например, у вас может быть следующий оператор SELECT name FROM customer WHERE name LIKE ‘Jen%’;. Этот оператор потенциально может возвращать совпадения с такими именами, как Jen, Jennifer и Jenny.

Группировка SQL и агрегатные функции

В SQL есть ключевое слово GROUP BY, которое является довольно мощным и требует особого внимания. Оператор GROUP BY делит строки на группы, что позволяет выполнять агрегатные функции. Оператор GROUP BY подобен выполнению DISTINCT для столбца, поскольку он возвращает только уникальные экземпляры в этом столбце. Например, если вы используете GROUP BY для идентификатора клиента, он вернет уникальные идентификаторы в этом столбце. Операторы GROUP BY часто используются в сочетании с агрегатными функциями, потому что вы можете узнать совокупные значения, скажем, для каждого идентификатора клиента. Ниже я перечислю агрегатные функции и объясню, как их можно использовать вместе с GROUP BY.

  1. AVG(col1) — возвращает среднее значение для этого столбца.
  2. MIN(col1) — вернет минимальное значение для этого столбца.
  3. MAX(col1) — вернет максимальное значение для этого столбца.
  4. SUM(col1) — вернет сумму значений в этом столбце.
  5. COUNT(col1) — как упоминалось ранее, count вернет количество записей в этом столбце.

Синтаксис оператора GROUP BY следующий: SELECT col1 FROM table GROUP BY col1;. Реальным примером использования GROUP BY может быть попытка найти среднюю цену, по которой каждый клиент берет фильмы напрокат. Заявление будет выглядеть примерно так: ВЫБЕРИТЕ customer_id, AVG(цена) ОТ аренды GROUP BY customer_id;

При использовании оператора GROUP BY мы также можем использовать оператор HAVING, который используется для указания определенного условия в GROUP BY. Синтаксис следующий: SELECT col1 FROM table GROUP BY col1 HAVING condition;.

SQL-соединения

В реальном мире нам часто приходится связывать одну часть данных с другой. В SQL у нас есть операторы соединения, которые позволяют нам связывать данные из одной таблицы с другой. Операторы соединения выполняются в фоновом режиме, когда мы используем ActiveRecord для получения информации об отношениях, которые мы определили в наших моделях и схеме БД. Существует три типа соединений: внутреннее, внешнее и самосоединение. Работа оператора соединения заключается в том, что вы ссылаетесь на первичные/внешние ключи в двух разных таблицах. Синтаксис соединения следующий: SELECT A.pka, A.c1, B.pkb, B.c2 FROM A INNER JOIN B ON A.pka = B.fka;. Я распаковываю заявление, чтобы вы могли понять все его части.

  1. В этом случае A будет таблицей, pka будет первичным ключом из этой таблицы. Например, это может быть customer_id для таблицы клиентов.
  2. A.c1 — это просто еще один столбец из таблицы A.
  3. B.pkb ссылается на первичный ключ из второй таблицы, B.
  4. B.c2 — еще один столбец из второй таблицы.

По сути, вы указываете столбцы, из которых вы хотите получить данные, затем вы указываете основную таблицу, в-третьих, вы указываете таблицу, к которой присоединяется основная таблица, используя общий первичный/внешний ключ.

Различные типы объединений и их ключевые слова приведены ниже:

  1. ВНУТРЕННЕЕ СОЕДИНЕНИЕ — в результате создаются строки, соответствующие как таблице А, так и таблице Б.
  2. FULL OUTER JOIN — создает все строки в обеих таблицах и заполняет нулевым значением там, где нет соответствующей информации.
  3. LEFT JOIN — возвращает все строки в таблице A независимо от того, есть совпадение или нет, но не возвращает таблицу B, в которой нет совпадающих записей.
  4. RIGHT JOIN — возвращает все строки в таблице B независимо от того, есть ли совпадение или нет, но не возвращает таблицу A, в которой нет совпадающих записей.

Несколько советов по использованию соединений.

  1. Если нам нужны записи в таблице A, которых нет в таблице B, мы делаем LEFT JOIN WHERE tableB.id IS NULL;.
  2. Если нам нужны записи, которые уникальны как для таблицы A, так и для таблицы B, мы выполняем ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, ГДЕ tableA.id IS NULL OR tableB.id IS NULL;.

Вы также можете соединить все записи таблицы с другой таблицей с помощью ключевого слова UNION. Синтаксис следующий:

SELECT col1, col2, FROM table1 UNION SELECT col1, col2 FROM table2

Ключевое слово UNION может пригодиться в реальной ситуации, когда у вас есть несколько таблиц, содержащих данные о продажах за все кварталы финансового года. Вы можете использовать UNION, чтобы объединить записи и выполнить оператор GROUP BY, чтобы узнать, у какого сотрудника было больше всего продаж в течение года.

Комбинируя различные ключевые слова, которые вы выучили до этого момента, вы сможете составить несколько мощных запросов, которые помогут вам лучше понять ваши данные. Более глубокие знания о том, как работают операторы SQL, также помогут вам, когда придет время отладки ошибок ActiveRecord!

Эта статья была написана, чтобы осветить некоторые основы SQL и помочь вам освоиться с ним. Оставайтесь с нами для pt. 2 статьи, в которой рассматриваются расширенные команды SQL и создание таблиц.