Як думати на SQL?

Треба "
SELECT * WHERE a=b FROM c
" або "
SELECT WHERE a=b FROM c ON *
" ?
Якщо ви схожі на мене, то погодьтеся: SQL — це одна з тих речей, які на перший погляд здаються легкими (читається як ніби по-англійськи!), але чомусь доводиться гуглити кожен простий запит, щоб знайти правильний синтаксис.
А потім починаються джойны, агрегування, підпорядкований, і виходить зовсім белиберда. Начебто такий:
SELECT members.firstname || '' || members.lastname
AS "Full Name"
FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid
WHERE borrowings.bookid IN (SELECT bookid
FROM books
WHERE stock>(SELECT avg(stock)
FROM books))
GROUP BY members.firstname, members.lastname;

Буе! Таке злякає будь-якого новачка, або навіть розробника середнього рівня, якщо він бачить SQL вперше. Але не все так погано.
Легко запам'ятати те, що інтуїтивно зрозуміло, і з допомогою цього керівництва я сподіваюся знизити поріг входу в SQL для новачків, а вже досвідченим запропонувати по-новому поглянути на SQL.
Не дивлячись на те, що синтаксис SQL майже не відрізняється в різних базах даних, в цій статті для запитів використовується PostgreSQL. Деякі приклади будуть працювати у MySQL та інших баз.
1. Три чарівних слова
В SQL багато ключових слів, але
SELECT
,
FROM
та
WHERE
присутні практично в кожному запиті. Трохи пізніше ви зрозумієте, що ці три слова являють собою найбільш фундаментальні аспекти побудови запитів до бази, а інші, більш складні запити, є лише надбудовою над ними.
2. Наша база
Давайте поглянемо на базу даних, яку ми будемо використовувати як приклад у цій статті:



У нас є книжкова бібліотека і люди. Також є спеціальна таблиця для обліку виданих книг.
  • У таблиці "books" зберігається інформація про заголовок, автора, дату публікації та наявності книги. Все просто.
  • У таблиці «members» — імена і прізвища всіх записалися до бібліотеки людей.
  • У таблиці «borrowings» зберігається інформація про взяті з бібліотеки книгах. Колонка
    bookid
    відноситься до ідентифікатора взятої книги в таблиці «books», а колонка
    memberid
    відноситься до відповідного людині з таблиці «members». У нас також є дата видачі та дата, коли книгу потрібно повернути.
3. Простий запит
Давайте почнемо з простого запиту: нам потрібні імена і ідентифікатори (id) усіх книг, написаних автором «Dan Brown»
Запит буде таким:
SELECT bookid AS "id", title
FROM books
WHERE author='Dan Brown';

А результат такий:





id заголовок 2 The Lost Symbol 4 Inferno
Досить просто. Давайте розберемо запит щоб зрозуміти, що відбувається.
3.1 FROM — звідки беремо дані
Зараз це може здатися очевидним, але
FROM
буде дуже важливий пізніше, коли ми перейдемо до з'єднань і подзапросам.
FROM
вказує на таблицю, за якою потрібно робити запит. Це може бути вже існуюча таблиця (як у прикладі вище), або таблиця, створювана на льоту через з'єднання або підпорядкований.
3.2 WHERE — які дані показуємо
WHERE
просто-напросто веде себе як фільтр рядків, які ми хочемо вивести. У нашому випадку ми хочемо бачити тільки ті рядки, де значення в колонці
author
— це «Dan Brown».
3.3 SELECT — як показуємо дані
Тепер, коли у нас є всі потрібні нам колонки з потрібної нам таблиці, потрібно вирішити, як саме показувати ці дані. У нашому випадку потрібні лише назви і ідентифікатори книг, так що саме це ми і виберемо з допомогою
SELECT
. Заодно можна перейменувати колонку використовуючи
AS
.
Весь запит можна візуалізувати за допомогою простої діаграми:

4. З'єднання (джойны)
Тепер ми хочемо побачити назви (не обов'язково унікальні) усіх книг Дена Брауна, які були взяті з бібліотеки, і коли ці книги потрібно повернути:
SELECT books.title AS "Title", borrowings.returndate AS "Return Date"
FROM borrowings JOIN books ON borrowings.bookid=books.bookid
WHERE books.author='Dan Brown';

Результат:






Заголовок Return Date The Lost Symbol 2016-03-23 00:00:00 Inferno 2016-04-13 00:00:00 The Lost Symbol 2016-04-19 00:00:00
По більшій частині запит схожий на попередній за винятком секції
FROM
. Це означає, що ми запитуємо дані з іншої таблиці. Ми не звертаємося до таблиці «books», ні до таблиці «borrowings». Замість цього ми звертаємося до новій таблиці, яка створилася з'єднанням цих двох таблиць.
borrowings JOIN books ON borrowings.bookid=books.bookid
— це, вважай, нова таблиця, яка була сформована комбінуванням всіх записів з таблиць "books" і "borrowings", в яких значення
bookid
збігаються. Результатом такого злиття буде:

А потім ми робимо запит до цієї таблиці так само, як у прикладі вище. Це означає, що при з'єднанні таблиць потрібно піклуватися тільки про те, як провести це з'єднання. А потім запит стає таким же зрозумілим, як у випадку з «простим запитом» пункту 3.
Давайте спробуємо трохи більш складне з'єднання з двома таблицями.
Тепер ми хочемо отримати імена і прізвища людей, які взяли з бібліотеки книги автора «Dan Brown».
На цей раз давайте підемо знизу вгору:
Step Step 1 — звідки беремо дані? Щоб отримати потрібний нам результат, потрібно з'єднати таблиці «member» і «books» з таблицею «borrowings». Секція JOIN буде виглядати так:
borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid

Результат з'єднання можна побачити по посиланню.
Крок 2 — які дані показуємо? Нас цікавлять тільки ті дані, де автор книги — «Dan Brown»
WHERE books.author='Dan Brown'

Крок 3 — як показуємо дані? Тепер, коли дані отримані, потрібно просто вивести ім'я та прізвище тих, хто взяв книги:
SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"

Супер! Залишилося лише об'єднати три складові частини і зробити потрібний нам запит:
SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown';

Що дасть нам:






First Name Last Name Mike Willis Ellen Horton Ellen Horton
Відмінно! Але імена повторюються (вони не унікальні). Ми скоро це виправимо.
5. Агрегування
Грубо кажучи, агрегування потрібні для конвертації декількох рядків в один. При цьому, під час агрегування для різних колонок використовується різна логіка.
Давайте продовжимо наш приклад, в якому з'являються повторювані імена. Видно, що Ellen Horton взяла більше однієї книги, але це не найкращий спосіб показати цю інформацію. Можна зробити інший запит:
SELECT
members.firstname AS "First Name",
members.lastname AS "Last Name",
count(*) AS "Number of books borrowed"
FROM borrowings
JOIN books ON borrowings.bookid=books.bookid
JOIN members ON members.memberid=borrowings.memberid
WHERE books.author='Dan Brown'
GROUP BY members.firstname, members.lastname;

Що дасть нам потрібний результат:





First Name Last Name Number of books borrowed Mike Willis 1 Ellen Horton 2
Майже всі агрегації йдуть разом з виразом
GROUP BY
. Ця штука перетворює таблицю, яку можна було б отримати за запитом у групи таблиць. Кожна група відповідає унікальному значенню (або групі значень колонки, яку ми вказали в
GROUP BY
. У нашому прикладі ми конвертуємо результат з минулого вправи в групу рядків. Ми також проводимо агрегування з
count
, яка конвертує кілька рядків в ціле значення (у нашому випадку це кількість рядків). Потім це значення приписується кожній групі.
Кожен рядок в результаті являє собою результат агрегування кожної групи.

Можна прийти до логічного висновку, що всі поля в результаті повинні бути вказані в
GROUP BY
, або за ним повинно проводитися агрегування. Тому що всі інші поля можуть відрізнятися один від одного в різних рядках, і якщо вибирати їх
SELECT
'ом, то незрозуміло, які з можливих значень потрібно брати.
У прикладі вище функція
count
обробляла всі рядки (так як ми рахували кількість рядків). Інші функції начебто
sum
або
max
обробляють тільки зазначені рядки. Наприклад, якщо ми хочемо дізнатися кількість книг, написаних кожним автором, то потрібен такий запит:
SELECT author, sum(stock)
FROM books
GROUP BY author;

Результат:







author sum Robin Sharma 4 Dan Brown 6 John Green 3 Amish Tripathi 2
Тут функція
sum
обробляє тільки колонку
stock
і вважає суму всіх значень в кожній групі.
6. Підпорядкований

Підпорядкований це звичайні SQL-запити, вбудовані в більш великі запити. Вони поділяються на три види за типом результату, що повертається.
6.1 Двовимірна таблиця
Є запити, які повертають кілька колонок. Хороший приклад це запит з минулого вправи по агрегированию. Будучи підзапит, він просто поверне ще одну таблицю, за якою можна робити нові запити. Продовжуючи попередню вправу, якщо ми хочемо дізнатися кількість книг, написаних автором «Robin Sharma», то один з можливих способів — використовувати підпорядкований:
SELECT *
FROM (SELECT author, sum(stock)
FROM books
GROUP BY author) AS results
WHERE author='Robin Sharma';

Результат:




author sum Robin Sharma 4
6.2 Одновимірний масив
Запити, які повертають кілька рядків однієї колонки, можна використовувати не тільки як двовимірні таблиці, але і як масиви.
Припустимо, ми хочемо дізнатися назви та ідентифікатори всіх книг, написаних певним автором, але тільки якщо в бібліотеці таких книг більше трьох. Розіб'ємо це на два кроки:
  1. Отримуємо список авторів з кількістю книг більше 3. Доповнюючи наш минулий приклад:
    SELECT author
    FROM (SELECT author, sum(stock)
    FROM books
    GROUP BY author) AS results
    WHERE sum > 3;

    Результат:



    author Robin Sharma Dan Brown
    Можна записати як:
    ['Robin Sharma', 'Dan Brown']

  2. Тепер використовуємо цей результат в новому запиті:
    SELECT title, bookid
    FROM books
    WHERE author IN (SELECT author
    FROM (SELECT author, sum(stock)
    FROM books
    GROUP BY author) AS results
    WHERE sum > 3);
    

    Результат:






    заголовок bookid The Lost Symbol 2 Who Will Cry When You Die? 3 Inferno 4
    Це те ж саме, що:
    SELECT title, bookid
    FROM books
    WHERE author IN ('Robin Sharma', 'Dan Brown');
    

6.3 Окремі значення
Бувають запити, результатом яких є всього один рядок і одна колонка. До них можна ставитися як до константным значенням, і їх можна використовувати скрізь, де використовуються значення, наприклад, операторів порівняння. Їх також можна використовувати в якості двовимірних таблиць або масивів, що складаються з одного елемента.
Давайте, наприклад, отримаємо інформацію про всіх книгах, кількість яких у бібліотеці перевищує середнє значення в даний момент.
Середня кількість можна отримати таким чином:
select avg(stock) from books;

Що дає нам:


avg 3.000
І це можна використовувати в якості скалярної величини
3
.
Тепер, нарешті, можна написати весь запит:
SELECT *
FROM books
WHERE stock>(SELECT avg(stock) FROM books);

Це те ж саме, що:
SELECT *
FROM books
WHERE stock>3.000

І результат:




bookid заголовок author published stock 3 Who Will Cry When You Die? Robin Sharma 2006-06-15 00:00:00 4
7. Операції запису
Більшість операцій запису в базі даних досить прості, якщо порівнювати з більш складними операціями читання.
7.1 Update
Синтаксис запиту
UPDATE
семантично збігається з запитом на читання. Єдина відмінність в тому, що замість вибору колонок
SELECT
'ом, ми задаємо значення
SET
'ом.
Якщо всі книги Дена Брауна загубилися, то потрібно обнулити значення кількості. Запит для цього буде таким:
UPDATE books
SET stock=0
WHERE author='Dan Brown';

WHERE
робить те ж саме, що раніше: вибирає рядки. Замість
SELECT
, який використовувався при читанні, ми тепер використовуємо
SET
. Однак, тепер потрібно вказати не тільки назву колонки, але й нове значення для цієї колонки в обраних рядках.
img
7.2 Delete
Запит
DELETE
це просто запит
SELECT
або
UPDATE
без назв колонок. Серйозно. Як і у випадку з
SELECT
та
UPDATE
, блок
WHERE
залишається такою ж: він вибирає рядки, які потрібно видалити. Операція видалення знищує весь рядок, тому що не має сенсу вказувати окремі колонки. Так що, якщо ми вирішимо не обнуляти кількість книг Дена Брауна, а взагалі видалити всі записи, то можна зробити такий запит:
DELETE FROM books
WHERE author='Dan Brown';

7.3 Insert
Мабуть, єдине, що відрізняється від інших типів запитів, це
INSERT
. Формат такий:
INSERT INTO x
(a,b,c)
VALUES
(x, y, z);

Де
a
,
b
,
c
це назви колонок, а
x
,
y
та
z
це значення, які потрібно вставити в ці колонки, в тому ж порядку. Ось, в принципі, і все.
Поглянемо на конкретний приклад. Ось запит з
INSERT
, який заповнює всю таблицю "books":
INSERT INTO books
(bookid,title,author,published,stock)
VALUES
(1,'Scion of Ikshvaku','Amish Tripathi','06-22-2015',2),
(2,'The Lost Symbol','Dan Brown','07-22-2010',3),
(3,'Who Will Cry When You Die?','Robin Sharma','06-15-2006',4),
(4,'Дерево','Dan Brown','05-05-2014',3),
(5,'The Fault in our Stars','John Green','01-03-2015',3);

8. Перевірка
Ми підійшли до кінця, пропоную невеликий тест. Подивіться на той запит на самому початку статті. Можете розібратися в ньому? Спробуйте розбити його на секції
SELECT
,
FROM
,
WHERE
,
GROUP BY
, і розглянути окремі компоненти підзапитів.
Ось він у більш зручному для читання вигляді:
SELECT members.firstname || '' || members.lastname AS "Full Name"

FROM borrowings
INNER JOIN members
ON members.memberid=borrowings.memberid
INNER JOIN books
ON books.bookid=borrowings.bookid

WHERE borrowings.bookid IN (SELECT bookid FROM books WHERE stock> (SELECT avg(stock) FROM books) )

GROUP BY members.firstname, members.lastname;

Цей запит виводить список людей, які взяли з бібліотеки книгу, у якій загальна кількість вище середнього значення.
Результат:


Full Name Lida Tyler
Сподіваюся, вам вдалося розібратися без проблем. Але якщо ні, то буду радий вашим коментарям та відгуками, щоб я міг поліпшити цей пост.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.