Базы данных (БД) и SQL

Введение

Данные - это ядро любого хорошего веб-приложения, и знание SQL необходимо хорошему разработчику. Это позволит не только понять принципы работы с данными таких инструментов как Active Record, но также чувствовать себя гораздо свободнее при составлении более сложных запросов к вашим данным. Собственно, для этого SQL и предназначен - запрашивать базу данных, а также иногда добавлять или что-то изменять в ней. Запросы к БД могут оказаться невероятно полезными.

В простых случаях, вам может понадобиться отобразить всех пользователей, которые зарегистрировались на сайте в декабре, использовав промо-код "FREESTUFF". Или же отобразить все комментарии, созданные текущим пользователем и отсортированные по теме и дате создания. В более сложных - получить список заказов в какую-то страну от более чем 1000 пользователей и вычислить общую их стоимость. Или же, для маркетингового запроса вам необходимо будет узнать, какие рекламные акции привлекли пользователей для чтения более 5 статей в течении рабочей недели.

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

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

Этот урок подразумевает, что вы уже выполнили Введение в базы данных. Мы двинемся дальше таких простых запросов как SELECT "users".* FROM "users" LIMIT 1 (он используется, когда вы запрашиваете Rails о первом пользователе командой User.first) к связыванию таблиц вместе, выполнению вычислений и группировке результатов различным образом.

Все это используется Rails на заднем плане, и понимание этого материала сильно облегчит вам написание запросов в Rails. Именно поэтому мы приступаем к изучению баз данных перед изучением Active Record. То же самое мы проделаем с формами - вы посмотрите как создавать их в HTML, и только затем узнаете как заставить Rails создавать их для вас.

Примечание об источниках

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

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

Пункты для размышления

Постарайтесь ответить на предложенные вопросы. После выполнения задания попробуйте ответить на них ещё раз

Важные понятия:

  • Что такое первичный ключ (Primary Key)?
  • Что такое внешние ключи (Foreign Keys)?
  • Что такое схема данных (Schema)?

Операторы:

  • SELECT
  • CREATE TABLE
  • DROP TABLE
  • CREATE INDEX
  • DROP INDEX
  • UPDATE
  • DELETE
  • INSERT INTO
  • CREATE DATABASE
  • DROP DATABASE
  • COMMIT (concept)
  • ROLLBACK (concept)

Условия:

  • DISTINCT
  • WHERE
  • IN
  • AND
  • OR
  • BETWEEN
  • LIKE
  • ORDER BY
  • COUNT

Функции

  • GROUP BY
  • HAVING
  • AVG
  • COUNT
  • MIN
  • MAX
  • SUM

Прочее

  • Для чего нужны индексы?
  • В чем разница между WHERE и HAVING?

Самое быстрое в мире объяснение SQL

Это крайне сжатое пояснение SQL. Здесь не объясняются какие-то особенные приемы работы, а просто дается информация, чтобы у вас сложилось общее понимание об SQL. Поехали..

SQL - это язык, предназначенный для обращения ко многим реляционным БД. Эти базы содержат множество таблиц для хранения различных видов данных (например, таблицы "users" и "posts"). Таблицы - это длинные списки, где каждая строка является отдельной записью (или объектом, как например единичным пользователем), а каждый столбец - отдельный атрибут этой записи (имя, e-mail и т.п.). Тот столбец, который присутствует во всех таблицах - это "ID". Он обеспечивает уникальность нумерации строк, и называется "первичным ключом" таблицы.

Вы можете "связать" таблицы, указав одному из столбцов таблицы на столбец (поле) ID другой таблицы. К примеру, поле "user_id" в таблице "posts" может содержать ID автора поста. Из-за того, что таблица "posts" имеет ссылку на поле ID другой таблицы, столбец "user_id" называется "внешним ключом".

Приступая к работе с БД

SQL позволяет вам делать все. Первая категория команд предназначена для создания БД (CREATE DATABASE), создания таблицы (CREATE TABLE) и подобных, для изменения или удаления их. Информация о структуре БД хранится в специальном файле, называемом "Schema", и он изменяется всякий раз, когда вы меняете структуру БД. Думайте о схеме как "здесь наша БД и у нее есть две таблицы. Одна из них 'users' и состоит из столбцов 'ID' (с типом integer), 'name' (это набор символов), 'email' (это тоже набор символов)".

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

SQL использует точку с запятой в конце строк и апострофы (') вместо двойных кавычек (").

Наполнение данными

Как только вы создали БД с пустыми таблицами, вы начинаете их заполнять данными, используя операторы SQL. Основными действиями будут известные нам CRUD - Create, Read, Update и Destroy. Большинство используемых вами команд будут подпадать под категорию "Read", так как большую часть времени вы будете запрашивать данные для последующего их отображения.

Каждая из команд CRUD состоит из нескольких частей - действия ("statement"), наименования таблицы, к которой относится команда, и условий ("clauses"). Если вы забудете указать условия, то действие применится ко всей таблице, возможно, изменив лишние строки.

Для запросов "Destroy", классической ошибкой является написание DELETE * FROM users без указания условия WHERE, что приводит к удалению абсолютно всех записей из таблицы. Возможно, вам было нужно удалить только одного пользователя на основании какого-нибудь (надеемся, уникального) атрибута, такого как "name" или "id" как части вашего условия в запросе, например DELETE * FROM users WHERE users.id = 1. Вы можете использовать различные операторы сравнения, такие как >, <, <= и другие, для указания нужного вам набора строк, или AND, OR, NOT для объединения нескольких условий, например `DELETE * FROM users WHERE id > 12 AND name = 'foo'.

Запросы "Create" используют INSERT INTO, и вам будет необходимо указать перечень столбцов, а также указать, какие данные необходимо вставить. Пример такого запроса - INSERT INTO Users (name, email) VALUES ('foobar','foo@bar.com');. Это один из тех запросов, где не надо волноваться об условиях выборки данных, так как тут вы просто добавляете данные в таблицу.

Запросы "Update" используют UPDATE - здесь вам необходимо указать, какие данные необходимо изменить с помощью SET (используя пары key="value"), и для каких строк это надо применить. Здесь снова следует быть осторожным с условием WHERE, так как изменятся все записи, подпадающие под него. Вот пример запроса для изменения пользовательского email (хотя в реальном мире для поиска пользователя обычно используется его уникальный ID):

    UPDATE Users
    SET name='barfoo', email='bar@foo.com'
    WHERE email='foo@bar.com';`

Запросы "Read", использующие SELECT, наиболее популярны. В примере SELECT * FROM users WHERE created_at < '2013-12-11 15:35:59 -0800' символ * означает "все столбцы". Указывайте столбец всегда вместе с наименованием таблицы. Для простых запросов можно конечно обойтись и просто именем столбца, но как только таблиц становится несколько, SQL укажет вам на неоднозначность запроса, так что всегда указывайте название таблицы: SELECT users.id, users.name FROM users.

Если вы хотите получить только уникальные значения из столбца, то воспользуйтесь SELECT DISTINCT вместо SELECT. Скажем, вы хотите получить только уникальные имена пользователей, без всяких задвоений - используйте SELECT DISTINCT users.name FROM users.

Объединение таблиц

Если надо получить посты одного пользователя, то надо указать, какие столбцы должен связать SQL, используя условие ON. "Связывание" выполняется командой JOIN. Но постойте, если мы соединим две таблицы, данные в которых не совпадают идеально (например, для одного пользователя существует несколько постов), что мы получим в результате? Здесь есть 4 различных варианта:

Примечание: Таблица "слева" - исходная таблица (та, на которую накладывается условие FROM), как "users" из примера ниже.

Посмотрите "Визуальное пояснение о соединении таблиц в SQL" от Джеффа Этвуда.

  1. INNER JOIN, также известный как JOIN - ваш лучший друг, и вы будете его использовать в 95% случаев. Оставляет только те записи, по которым есть совпадения. Если запросить все посты всех пользователей (SELECT * FROM users JOIN posts ON users.id = posts.user_id), то вы получите пользователей, которые создавали посты, и только те посты, которые содержат значение автора в столбце user_id. Если кто-то создал несколько постов, запрос возвратит несколько постов (в столбце с ID пользователя данные в этом случае будут повторяться).
  2. LEFT OUTER JOIN - возвратит все записи из левой таблицы и добавит все записи из правой, по которым есть совпадения. Пустые записи вернутся как NULL. Например, мы запрашиваем всех пользователей, независимо от того, создавали ли они посты или нет. Если создавали, то посты отобразятся как в примере выше. Если нет, данные из столбцов таблицы "posts" отобразятся как NULL.
  3. RIGHT OUTER JOIN - противоположно предыдущему, возвратит все записи из правой таблицы.
  4. FULL OUTER JOIN - вернет все записи из всех таблиц, даже если есть случаи несовпадения. Такие случаи отобразятся как NULL.

Здесь вы также можете указывать условия выборки, например для отдельного пользователя: SELECT * FROM users JOIN posts ON users.id = posts.user_id WHERE users.id = 42.

Для лучшего понимания, прочтите Урок от W3 Schools.

Использование функций для агрегирования данных

При использовании "ванильных" запросов, вы часто получаете много записей. Иногда же необходимо получить единственное значение, которое объединяет столбец. К примеру, чтобы получить количество постов пользователя, можно использовать COUNT. SQL предлагает несколько таких функций, которые называются "агрегатными" (наличие большинства из них можно было бы ожидать - SUM, MIN, MAX и т.п.). Вы включаете функцию как часть оператора SELECT, например SELECT MAX(users.age) FROM users. Функция работает только с одним столбцом пока не указано *, что работает только для нескольких функций, таких как COUNTMAX будет работать только для одного столбца).

Алиасы (AS) используются для переименования столбцов или агрегатных функций, чтобы вы затем могли их вызывать по этому алиасу, например SELECT MAX(users.age) AS highest_age FROM users вернет столбец highest_age с содержащимся в нем максимальным возрастом.

Мы подходим к интересному моменту. Такие функции как COUNT, возвращающие единственные значения, хороши, но они становятся действительно полезны, когда необходимо определенным образом сгруппировать полученные данные, например отобразить количество постов для КАЖДОГО пользователя (в отличие от количества всех постов всех пользователей). Запрос в этом случае может быть такой:

    SELECT users.name, COUNT(posts.*) AS posts_written
    FROM users
    JOIN posts ON users.id = posts.user_id
    GROUP BY users.name;

Посмотрите w3 schools и поиграйте с кодом SQL (попробуйте удалить строку с GROUP BY и агрегатную функцию), анализируя результаты.

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

    SELECT users.name, COUNT(posts.*) AS posts_written
    FROM users
    JOIN posts ON users.id = posts.user_id
    GROUP BY users.name
    HAVING posts_written >= 10;

Вернитесь на пример и добавьте строку HAVING NumberOfOrders > 60;, чтобы увидеть результат (и удалите точку с запятой в предыдущей строке).

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

SQL быстрее, чем Ruby!

Изучение этого материала важно потому, что использование разумного кода SQL НАМНОГО быстрее, чем просто забрать большой пул данных из базы, а потом обрабатывать его с помощью Ruby. Например, для получения всех уникальных имен пользователей вы МОГЛИ БЫ использовать SQL запрос SELECT users.name FROM users (который Active Record сформировал бы при использовании User.select(:name)), а затем удалить задвоения, используя метод #uniq, например User.select(:name).uniq - но это означает, что вы запрашиваете все эти данные из базы, помещаете их в память, а затем проводите итерацию с помощью Ruby. Использование вместо этого SELECT DISTINCT users.name FROM users делает все в один шаг.

SQL создан чтобы быть быстрым. Существует оптимизатор запросов, который смотрит на ваш запрос и выясняет, какие таблицы необходимо объединить и как максимально быстро выполнить запрос. Различие во времени выполнения между SELECT и SELECT DISTINCT практически незаметно на Ruby. Изучение вашего SQL поможет вам составлять оптимизированные запросы Active Record, делающие ваше приложение гораздо быстрее.

Ваши задания

  1. Если вы пропустили, выполните Урок по базам данных. Возможно, следует пересмотреть видео из курса Coursera - на тот момент, они вероятно были слишком сложными, но сейчас вам будет намного легче.
  2. Прочтите это руководство по SQL, оно поясняет основы.
  3. Если не последовали по предыдущей ссылке, почитайте о продвинутом использовании оператора SELECT в комбинации с агрегатными функциями и GROUP BY во второй части руководства.

Заключение

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

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

Следующим шагом, после того, как у вас будет шанс попрактиковаться с этим материалом в проекте, будет применить знания в связке с Active Record. Вы сразу поймете, НАСКОЛЬКО Active Record облегчает вам работу. Просто не забывайте старый SQL, когда доберетесь до этих лучших и сияющих штучек, хорошо?

Дополнительные ресурсы

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

Поделиться уроком: