Вивчіть SQL за допомогою цих 5 простих рецептів

SQL (мова структурованих запитів) - це потужна і виразна мова для роботи з даними з реляційних баз даних. Але для непосвячених це може здатися страшним.

"Рецепти", якими я сьогодні поділюсь з вами, - це кілька основних прикладів із простої бази даних. Але моделі, які ви дізнаєтесь тут, можуть допомогти вам написати точні запити. Вони змусять вас почуватись як дані, еквівалентні MasterChef, в найкоротші терміни.

Примітка щодо синтаксису: Більшість запитів, наведених нижче, написані в стилі, що використовується для PostgreSQL з командного рядка psql. Різні механізми SQL можуть використовувати трохи різні команди.

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

Блюдо 1: Поверніть усіх користувачів, створених протягом певного діапазону дат

Інгредієнти

  • ВИБЕРІТЬ
  • ВІД
  • ДЕ
  • І

Метод

SELECT * FROM "Users" WHERE "created_at" > "2020-01-01" AND "created_at" < "2020-02-01";

Ця проста страва - універсальний основний продукт. Тут ми повертаємо користувачів, які відповідають двом конкретним умовам, прив’язуючи WHEREумови до ANDзаяви. Ми можемо продовжити це ще більше ANDзаявами.

Хоча приклад тут стосується конкретного діапазону дат, більшість запитів вимагають певної умови для корисної фільтрації даних.

Блюдо 2: Знайдіть усі коментарі до книги, включаючи користувача, який дав коментар

(Нове) Інгредієнти

  • ПРИЄДНАЙТЕСЬ

Метод

SELECT "Comments"."comment", "Users"."username" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" WHERE "Comments"."bookId" = 1;

Цей запит передбачає таку структуру таблиці:

Однією з речей, яка може заплутати початківців із SQL, є використання JOIN для пошуку даних із пов'язаних таблиць.

Наведена вище ERD (діаграма взаємозв’язків) показує три таблиці: Користувачі, Книги та Коментарі та їх асоціації.

Кожна таблиця має idякий напівжирний на діаграмі , щоб показати , що він є первинним ключем для таблиці. Цей первинний ключ завжди є унікальним значенням і використовується для розмежування записів у таблицях.

У курсивних іменах стовпців userIdі bookIdв таблиці Коментарів зовнішні ключі, а це означає , що вони є первинним ключем в інших таблицях і використовуються тут для посилання на ці таблиці.

З’єднувачі в ERD також показують характер взаємозв’язків між 3 таблицями.

Одноточковий кінець на з'єднувачі означає "один", а розділений кінець на з'єднувачі означає "багато", тому таблиця Користувача має зв'язок "один до багатьох" із таблицею Коментарі.

Наприклад, користувач може мати багато коментарів, але коментар може належати лише одному користувачеві. Книги та коментарі мають однакові відносини на схемі вище.

Запит SQL повинен мати сенс на основі того, що ми зараз знаємо. Ми повертаємо лише названі стовпці, тобто стовпець коментарів із таблиці Коментарі та ім’я користувача з асоційованої таблиці Користувачів (на основі зовнішнього ключа, на який посилається). У наведеному вище прикладі ми обмежуємо пошук однією книгою, знову ж на основі зовнішнього ключа в таблиці Коментарі.

Блюдо 3: Підрахуйте кількість коментарів, доданих кожним користувачем

(Нове) Інгредієнти

  • РАХУВАТИ
  • ЯК
  • ГРУПА ЗА

Метод

SELECT "Users"."username", COUNT("Comments"."id") AS "CommentCount" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" GROUP BY "Users"."id";

Цей маленький запит містить кілька цікавих речей. Найпростішим для розуміння є ASтвердження. Це дозволяє нам довільно та тимчасово перейменовувати стовпці у даних, що повертаються. Тут ми перейменуємо похідний стовпець, але це також корисно , коли у вас є кілька idстовпців, так як ви можете перейменувати їх речі , як userIdабо commentIdі так далі.

Оператор COUNT- це функція SQL, яка, як і слід було очікувати, враховує речі. Тут ми підраховуємо кількість коментарів, пов’язаних з користувачем. Як це працює? Ну GROUP BYце важливий кінцевий інгредієнт.

Давайте коротко уявімо дещо інший запит:

SELECT "Users"."username", "Comments"."comment" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id";

Зверніть увагу, відсутність підрахунку чи групування. Ми просто хочемо кожен коментар і хто його зробив.

Вихід може виглядати приблизно так:

|----------|-----------------------------| | username | comment | |----------|-----------------------------| | jackson | it's good, I liked it | | jackson | this was ok, not the best | | quincy | excellent read, recommended | | quincy | not worth reading | | quincy | I haven't read this yet | ------------------------------------------

А тепер уявіть, що ми хотіли порахувати коментарі Джексона та Квінсі - їх тут легко побачити з першого погляду, але важче з більшим набором даних, як ви можете собі уявити.

GROUP BYЗаява по суті говорить запит обробити всі jacksonзаписи в одній групі, і все quincyзаписи , як інший. Потім COUNTфункція підраховує записи в цій групі і повертає це значення:

|----------|--------------| | username | CommentCount | |----------|--------------| | jackson | 2 | | quincy | 3 | ---------------------------

Блюдо 4: Знайдіть користувачів, які не зробили коментар

(Нове) Інгредієнти

  • ЛІВО ПРИЄДНАЙТЕСЬ
  • НІЩЕ

Метод

SELECT "Users"."username" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId" WHERE "Comments"."id" IS NULL;

Різні об’єднання можуть стати дуже заплутаними, тому я не буду розпаковувати їх тут. Їх тут чудово розбито: Візуальні подання об’єднань SQL, що також враховує деякі синтаксичні відмінності між різними варіантами або SQL.

Давайте швидко уявимо альтернативну версію цього запиту:

SELECT "Users"."username", "Comments"."id" AS "commentId" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId";

We still have the LEFT JOIN but we've added a column and removed the WHERE clause.

The return data might look something like this:

|----------|-----------| | username | commentId | |----------|-----------| | jackson | 1 | | jackson | 2 | | quincy | NULL | | abbey | 3 | ------------------------

So Jackson is responsible for comments 1 and 2, Abbey for 3, and Quincy has not commented.

The difference between a LEFT JOIN and an INNER JOIN (what we've been calling just a JOIN until now, which is valid) is that the inner join only shows records where there are values for both tables. A left join, on the other hand, returns everything from the first, or left, table (the FROM one) even if there is nothing in the right table. An inner join would therefore only show the records for Jackson and Abbey.

Now that we can visualize what the LEFT JOIN returns, it's easier to reason about what the WHERE...IS NULL part does. We return only those users where the commentId is a null value, and we don't actually need the null value column included in the output, hence its original omission.

Dish 5: List all comments added by each user in a single field, pipe separated

(New) Ingredients

  • GROUP_CONCAT or STRING_AGG

Method (MySQL)

SELECT "Users"."username", GROUP_CONCAT("Comments"."comment" SEPARATOR " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

Method (Postgresql)

SELECT "Users"."username", STRING_AGG("Comments"."comment", " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

This final recipe shows a difference in syntax for a similar function in two of the most popular SQL engines.

Here is a sample output we might expect:

|----------|---------------------------------------------------| | username | comments | |----------|---------------------------------------------------| | jackson | it's good, I liked it | this was ok, not the best | | quincy | excellent read, recommended | not worth reading | ----------------------------------------------------------------

We can see here that the comments have been grouped and concatenated / aggregated, that is joined together in a single record field.

BonAppetit

Now that you have some SQL recipes to fall back on, get creative and serve up your own data dishes!

I like to think of WHERE, JOIN, COUNT, GROUP_CONCAT as the Salt, Fat, Acid, Heat of database cooking. Once you know what you're doing with these core elements, you are well on your way to mastery.

If this has been a useful collection, or you have other favourite recipes to share, drop me a comment or follow on Twitter: @JacksonBates.