Как писать простые запросы

Вы когда-нибудь слышали о SQL? Возможно, вы слышали об этом в контексте анализа данных, но никогда не думали, что это применимо к вам как к маркетологу. Или, возможно, вы подумали: «Это для продвинутых пользователей данных. Я бы никогда не смог этого сделать».

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

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

Зачем использовать SQL?

SQL (часто произносится как «sequel») расшифровывается как «язык структурированных запросов» и используется, когда у компаний есть масса данных, которыми они хотят манипулировать. Прелесть SQL в том, что его может использовать любой, кто работает в компании, которая хранит данные в реляционной базе данных. (И, скорее всего, у вас так и есть.)

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

Подумайте об этом так: вы когда-нибудь открывали очень большой набор данных в Excel только для того, чтобы ваш компьютер зависал или даже выключался? SQL позволяет вам получить доступ только к определенным частям ваших данных за раз, поэтому вам не нужно загружать все данные в CSV, манипулировать ими и, возможно, перегружать Excel. Другими словами, SQL позаботится об анализе данных, который вы, возможно, привыкли делать в Excel.

Как писать простые SQL-запросы

Прежде чем мы начнем, убедитесь, что у вас есть приложение для управления базой данных, которое позволит вам извлекать данные из вашей базы данных. Некоторые варианты включают MySQL или Sequel Pro.

Начните с загрузки одного из этих вариантов, а затем поговорите с ИТ-отделом вашей компании о том, как подключиться к вашей базе данных. Выбранный вами вариант будет зависеть от серверной части вашего продукта, поэтому проконсультируйтесь с командой по продукту, чтобы убедиться, что вы выбрали правильный вариант.

Понимание иерархии вашей базы данных

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

Например, давайте представим, что мы работаем с несколькими базами данных о людях в Соединенных Штатах. Введите запрос «ПОКАЗАТЬ БАЗЫ ДАННЫХ;». Результаты могут показать, что у вас есть несколько баз данных для разных местоположений, в том числе одна для Новой Англии.

В вашей базе данных у вас будут разные таблицы, содержащие данные, с которыми вы хотите работать. Используя тот же пример выше, предположим, что мы хотим узнать, какая информация содержится в одной из баз данных. Если мы воспользуемся запросом «ПОКАЗАТЬ ТАБЛИЦЫ в Новой Англии;», мы обнаружим, что у нас есть таблицы для каждого штата Новой Англии: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland и people_vermont.

Наконец, вам нужно узнать, какие поля есть в таблицах. Поля — это определенные фрагменты данных, которые вы можете извлечь из своей базы данных. Например, если вы хотите получить чей-то адрес, имя поля может быть не просто «адрес» — оно может быть разделено на адрес_город, адрес_штат, адрес_zip. Чтобы это выяснить, воспользуйтесь запросом «Describe people_massachusetts;». Это предоставляет список всех данных, которые вы можете получить с помощью SQL.

Давайте сделаем быстрый обзор иерархии, используя наш пример Новой Англии:

• Наша база данных: Новая Англия.
• Наши таблицы в этой базе данных:
• Наши поля в таблице people_massachusetts включают в себя: address_city, address_state, address_zip, hair_color, age, first_name и last_name.

Теперь давайте напишем несколько простых SQL-запросов для извлечения данных из нашей базы данных NewEngland.

Базовые SQL-запросы

Чтобы научиться писать SQL-запрос, давайте воспользуемся следующим примером:

Кто эти рыжеволосые жители Массачусетса, родившиеся в 2003 году, расположенные в алфавитном порядке?

ВЫБРАТЬ

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

Вот наш SQL-запрос:

ВЫБРАТЬ

Имя,

фамилия

;

ИЗ

FROM указывает таблицу, из которой вы хотите извлечь данные. В предыдущем разделе мы узнали, что для каждого из шести штатов Новой Англии существует шесть таблиц: people_connecticut, people_maine, people_massachusetts, people_newhampshire, people_rhodeisland и people_vermont. Поскольку мы ищем людей именно в Массачусетсе, мы будем извлекать данные из этой конкретной таблицы.

Вот наш SQL-запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

;

КУДА

WHERE позволяет отфильтровать запрос, чтобы сделать его более конкретным. В нашем примере мы хотим отфильтровать наш запрос, чтобы включить только людей с рыжими волосами, которые родились в 2003 году. Начнем с фильтра рыжих волос.

Вот наш SQL-запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = «красный»

;

hair_color мог бы быть частью вашего первоначального оператора SELECT, если бы вы хотели посмотреть на всех людей в Массачусетсе вместе с их цветом волос. Но если вы хотите отфильтровать только людей с рыжими волосами, вы можете сделать это с помощью инструкции WHERE.

МЕЖДУ

Помимо равенства (=), BETWEEN — это еще один оператор, который вы можете использовать для условных запросов. Оператор BETWEEN верен для значений, которые находятся между указанными минимальным и максимальным значениями.

В нашем случае мы можем использовать BETWEEN для извлечения записей за определенный год, например, за 2003 год. Вот запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

;

А ТАКЖЕ

И позволяет вам добавить дополнительные критерии в оператор WHERE. Помните, что мы хотим отфильтровать людей с рыжими волосами в дополнение к людям, родившимся в 2003 году. Поскольку наше утверждение WHERE используется по критериям рыжих волос, как мы можем фильтровать также и по определенному году рождения?

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

Вот наш SQL-запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = «красный»

А ТАКЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

;

ИЛИ ЖЕ

OR также может использоваться с оператором WHERE. При использовании AND оба условия должны быть истинными, чтобы они отображались в результатах (например, цвет волос должен быть рыжим, а дата рождения — 2003 г.). При использовании OR любое условие должно быть истинным, чтобы оно отображалось в результатах (например, цвет волос должен быть рыжим или вы должны родиться в 2003 году).

Вот как выглядит оператор ИЛИ в действии:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = 'рыжий'

ИЛИ ЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

;

НЕТ

NOT используется в операторе WHERE для отображения значений, в которых указанное условие не соответствует действительности. Если бы мы хотели подтянуть всех жителей Массачусетса без рыжих волос, мы могли бы использовать следующий запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

ГДЕ НЕТ

hair_color = 'рыжий'

;

СОРТИРОВАТЬ ПО

Вычисления и организация также могут быть выполнены в рамках запроса. Вот тут-то и появляются функции ORDER BY и GROUP BY. Сначала мы рассмотрим наши SQL-запросы с функциями ORDER BY, а затем GROUP BY. Затем мы кратко рассмотрим разницу между ними.

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

Вот наш SQL-запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = 'рыжий'

А ТАКЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

СОРТИРОВАТЬ ПО

фамилия

;

ГРУППА ПО

GROUP BY аналогичен ORDER BY, но объединяет сходные данные. Например, если в ваших данных есть дубликаты, вы можете использовать GROUP BY для подсчета количества дубликатов в ваших полях.

Вот ваш SQL-запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = 'рыжий'

А ТАКЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

ГРУППА ПО

фамилия

;

ЗАКАЗАТЬ ПО VS. ГРУППА ПО

Чтобы показать разницу между оператором ORDER BY и оператором GROUP BY, давайте ненадолго отвлечемся от нашего примера с Массачусетсом и рассмотрим очень простой набор данных. Ниже приведен список идентификационных номеров и имен четырех сотрудников.

Если бы мы использовали оператор ORDER BY в этом списке, имена сотрудников были бы отсортированы в алфавитном порядке. Результат будет выглядеть следующим образом:

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

Со мной до сих пор? Хорошо, давайте вернемся к созданному нами SQL-запросу о рыжеволосых людях из Массачусетса, родившихся в 2003 году.

ПРЕДЕЛ

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

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

Вот наш SQL-запрос:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = 'рыжий'

А ТАКЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

СОРТИРОВАТЬ ПО

фамилия

ПРЕДЕЛ

100

;

ВСТАВИТЬ В

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

Во-первых, это оператор INSERT INTO, который предназначен для помещения новых значений в вашу базу данных. Если мы хотим добавить нового человека в таблицу Massachusetts, мы можем сделать это, сначала указав имя таблицы, которую мы хотим изменить, и поля в таблице, в которую мы хотим добавить. Затем мы пишем VALUE для каждого соответствующего значения, которое хотим добавить.

Вот как может выглядеть этот запрос:

ВСТАВИТЬ В

people_massachusetts (address_city, address_state, address_zip, hair_color, age, first_name, last_name)

ЦЕННОСТИ

(Кембридж, Массачусетс, 02139, блондинка, 32 года, Джейн, Доу)

;

В качестве альтернативы, если вы добавляете значение в каждое поле таблицы, вам не нужно указывать поля. Значения будут добавлены в столбцы в том порядке, в котором они перечислены в запросе.

ВСТАВИТЬ В

люди_массачусетс

ЦЕННОСТИ

(Кембридж, Массачусетс, 02139, блондинка, 32 года, Джейн, Доу)

;

Если вы хотите добавить значения только в определенные поля, вы должны указать эти поля. Скажем, мы хотим вставить только запись с first_name, last_name и address_state — мы можем использовать следующий запрос:

ВСТАВИТЬ В

people_massachusetts (first_name, last_name, address_state)

ЦЕННОСТИ

(Джейн, Доу, Массачусетс)

;

ОБНОВИТЬ

Если вы хотите заменить существующие значения в вашей базе данных другими значениями, вы можете использовать UPDATE. Что, если, например, кто-то зарегистрирован в базе данных как рыжеволосый, хотя на самом деле у него каштановые волосы? Мы можем обновить эту запись с помощью операторов UPDATE и WHERE:

ОБНОВИТЬ

люди_массачусетс

УСТАНОВЛЕН

hair_color = 'коричневый'

КУДА

first_name = 'Джейн'

А ТАКЖЕ

last_name = 'Доу'

;

Или, скажем, в вашей таблице есть проблема, из-за которой некоторые значения для «address_state» отображаются как «Массачусетс», а другие — как «MA». Чтобы изменить все экземпляры «MA» на «Массачусетс», мы можем использовать простой запрос и обновить сразу несколько записей:

ОБНОВИТЬ

люди_массачусетс

УСТАНОВЛЕН

address_state = 'Массачусетс'

КУДА

address_state = МА

;

Будьте осторожны при использовании UPDATE. Если вы не укажете, какие записи следует изменить с помощью оператора WHERE, вы измените все значения в таблице.

УДАЛИТЬ

DELETE удаляет записи из вашей таблицы. Как и в случае с UPDATE, не забудьте включить оператор WHERE, чтобы случайно не удалить всю таблицу.

Или, если нам случится найти несколько записей в нашей таблице people_massachusetts, которые на самом деле жили в штате Мэн, мы можем быстро удалить эти записи, нацелившись на поле address_state, например так:

УДАЛИТЬ ИЗ

люди_массачусетс

КУДА

address_state = 'Мэн'

;

Бонус: расширенные советы по SQL

Теперь, когда вы научились создавать простой SQL-запрос, давайте обсудим некоторые другие приемы, которые вы можете использовать, чтобы поднять свои запросы на ступеньку выше, начиная со звездочки.

* (звездочка)

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

В примере с Массачусетсом, который мы использовали, у нас было только два имени столбца: first_name и last_name. Но предположим, что у нас есть 15 столбцов данных, которые мы хотим видеть в наших результатах — было бы сложно ввести имена всех 15 столбцов в операторе SELECT. Вместо этого, если вы замените имена этих столбцов звездочкой, запрос будет знать, что все столбцы должны быть включены в результаты.

Вот как будет выглядеть SQL-запрос:

ВЫБРАТЬ

*

ИЗ

люди_массачусетс

КУДА

hair_color = «красный»

А ТАКЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

СОРТИРОВАТЬ ПО

фамилия

ПРЕДЕЛ

100

;

% (символ процента)

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

Например, если мы хотим получить имена всех людей в нашей таблице, чей почтовый индекс начинается с «02», мы можем написать такой запрос:

ВЫБРАТЬ

Имя,

фамилия

КУДА

address_zip НРАВИТСЯ '02%'

;

Здесь «%» заменяет любую группу цифр, следующую за «02», поэтому этот запрос выдает любую запись со значением для address_zip, начинающимся с «02».

ПОСЛЕДНИЕ 30 ДНЕЙ

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

Предположим, что сегодня 1 декабря 2021 года. Вы можете создать эти параметры, задав диапазон даты рождения между 1 ноября 2021 года и 30 ноября 2021 года. Этот SQL-запрос будет выглядеть следующим образом:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = «красный»

А ТАКЖЕ

рождения_дата МЕЖДУ '2021-11-01' И '2021-11-30'

СОРТИРОВАТЬ ПО

фамилия

ПРЕДЕЛ

100

;

Но для этого потребуется подумать о том, какие даты охватывают последние 30 дней, и вам придется постоянно обновлять этот запрос.

Вместо этого, чтобы даты автоматически охватывали последние 30 дней, независимо от того, какой сегодня день, вы можете ввести это в поле AND:birth_date >= (DATE_SUB(CURDATE(),INTERVAL 30))

(Примечание: вам следует перепроверить этот синтаксис вместе с командой разработчиков, потому что он может отличаться в зависимости от программного обеспечения, которое вы используете для извлечения SQL-запросов.)

Таким образом, ваш полный SQL-запрос будет выглядеть так:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КУДА

hair_color = «красный»

А ТАКЖЕ

Дата_рождения >= (DATE_SUB(CURDATE(),INTERVAL 30))

СОРТИРОВАТЬ ПО

фамилия

ПРЕДЕЛ

100

;

СЧИТАТЬ

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

Вот как будет выглядеть этот SQL-запрос:

ВЫБРАТЬ

цвет волос,

COUNT(цвет_волос)

ИЗ

люди_массачусетс

А ТАКЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

ГРУППА ПО

цвет волос

;

СРЕДНИЙ

AVG вычисляет среднее значение атрибута в результатах вашего запроса, исключая значения NULL (пустые). В нашем примере мы могли бы использовать AVG для расчета среднего возраста жителей Массачусетса в нашем запросе.

Вот как может выглядеть наш SQL-запрос:

ВЫБРАТЬ

СРЕДНЕЕ (возраст)

ИЗ

люди_массачусетс

;

СУММА

SUM — еще одно простое вычисление, которое можно выполнить в SQL. Он вычисляет общее значение всех атрибутов из вашего запроса. Итак, если мы хотим сложить все возрасты жителей Массачусетса, мы можем использовать этот запрос:

ВЫБРАТЬ

СУММ(возраст)

ИЗ

люди_массачусетс

;

МИН. и МАКС.

MIN и MAX — это две функции SQL, которые дают вам наименьшее и наибольшее значения заданного поля. Мы можем использовать его для определения самых старых и самых молодых членов нашей таблицы Массачусетса:

Этот запрос даст нам запись самого старого:

ВЫБРАТЬ

МИН(возраст)

ИЗ

люди_массачусетс

;

И этот запрос дает нам самое старое:

ВЫБРАТЬ

МАКС(возраст)

ИЗ

люди_массачусетс

;

ПРИСОЕДИНИТЬСЯ

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

(Для тех, кто знаком с формулами Excel, это похоже на использование формулы ВПР, когда вам нужно объединить информацию из двух разных листов в Excel.)

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

Если мы хотим определить цвет волос жителей Массачусетса, родившихся в 2003 году, нам потребуется получить доступ к информации из обеих таблиц и объединить их. Это работает, потому что обе таблицы имеют одинаковый столбец: идентификаторы пользователей.

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

Чтобы указать поле из конкретной таблицы, все, что нам нужно сделать, это объединить имя таблицы с именем поля. Например, в нашем операторе SELECT будет указано «table.field» — с точкой, разделяющей имя таблицы и имя поля.

Мы также предполагаем несколько вещей в этом случае:

1. Таблица даты рождения штата Массачусетс включает следующие поля: имя_имя, фамилия_имя, идентификатор_пользователя, дата рождения.
2. Таблица цвета волос штата Массачусетс включает следующие поля: user_id, hair_color.

Таким образом, ваш SQL-запрос будет выглядеть так:

ВЫБРАТЬ

дата_рождения_массачусетс.first_name,

дата_рождения_массачусетс.last_name

ИЗ

Birthdaydate_massachusetts ПРИСОЕДИНЯЙТЕСЬ haircolor_massachusetts, ИСПОЛЬЗУЯ (user_id)

КУДА

hair_color = «красный»

А ТАКЖЕ

рождения_дата МЕЖДУ '2003-01-01' И '2003-12-31'

СОРТИРОВАТЬ ПО

фамилия

;

Этот запрос объединит две таблицы, используя поле «user_id», которое появляется как в таблице Birthday_massachusetts, так и в таблице haircolor_massachusetts. Затем вы сможете увидеть таблицу людей, родившихся в 2003 году, с рыжими волосами.

КЕЙС

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

Вы можете включить условие ELSE в конце, если никакие условия не выполняются. Без ELSE запрос вернет NULL, если никакие условия не выполняются.

Вот пример использования CASE для возврата строки на основе запроса:

ВЫБРАТЬ

Имя,

фамилия

ИЗ

люди_массачусетс

КЕЙС

WHEN hair_color = 'brown' THEN 'У этого человека каштановые волосы.'

WHEN hair_color = 'blonde' THEN 'У этого человека светлые волосы.'

WHEN hair_color = 'red' THEN 'У этого человека рыжие волосы.'

ЕЩЕ 'Цвет волос неизвестен'.

КОНЕЦ

;

Основные SQL-запросы, которые должны знать маркетологи

Поздравляем. вы готовы выполнять свои собственные SQL-запросы! Хотя с помощью SQL можно сделать гораздо больше, я надеюсь, что этот обзор основ был вам полезен, и вы могли запачкать руки. Имея прочную основу основ, вы сможете лучше ориентироваться в SQL и работать над некоторыми более сложными примерами.

Примечание редактора: этот пост был первоначально опубликован 25 марта и обновлен для полноты информации.