Будь-яка таблиця має свій логічний початок та свій логічний кінець. Так само будь-яка таблиця складається з даних, які часто, а у випадку з базами даних більшою мірою, розташовані в розкид. Як зазначалося в першому записі щодо SQL і баз даних, дані можуть вноситися регулярно протягом усього життєвого циклу баз даних. За цей час може бути внесено не один десяток даних. У SQL, як у іншому та у всіх комп'ютерних програмах, можна впорядковувати дані. Робиться це за кошти команди ORDER BY. Для цієї команди існують два аргументи

• ASC визначає порядок зростання (від меншого до більшого)
• DESC - за спаданням (від більшого до меншого)

Загалом у SQL за замовчуванням встановлено впорядкування за зростанням (від меншого до більшого).
Спробуємо впорядкувати таблицю боржників на прізвище - стовпець Sname - на спадання

Debtors

Num	Month	Year	Sname	City	Address	Penalty	Debt
0001	Липень	2012	Іванов	Ставропіль	Ставропольська, 1	4891.00	50000
0002	грудень	2019	Кононов	Татарка	Заміська, 254	19565.00	684068
0003	Травень	2013	Ямшин	Михайлівськ	Сільська, 48	9868.00	165840
0004	Серпень	2012	Дебати	Ставропіль	Центральна, 16	4659.00	46580
...	...	...	...	...	...	...	...
9564	Березень	2015	Улієва	Деміно	Міжнародна, 156	6846.00	435089
9565	Жовтень	2012	Павлова	Ставропіль	Вокзальна, 37	685.00	68059
9566	Січень	2012	Урюпа	Михайлівськ	Фонтанна, 19	1235.00	51238
9567	Листопад	2017	Вальєтов	Татарка	Виїзна, 65	13698.00	789654

Виконуємо запит
SELECT *
FROM Debtors
ORDER BY Sname DESC;

Debtors

Num	Month	Year	Sname	City	Address	Penalty	Debt
0003	Травень	2013	Ямшин	Михайлівськ	Сільська, 48	9868.00	165840
9566	Січень	2012	Урюпа	Михайлівськ	Фонтанна, 19	1235.00	51238
9564	Березень	2015	Улієва	Деміно	Міжнародна, 156	6846.00	435089
0004	Серпень	2012	Дебати	Ставропіль	Центральна, 16	4659.00	46580
9565	Жовтень	2012	Павлова	Ставропіль	Вокзальна, 37	685.00	68059
0002	грудень	2019	Кононов	Татарка	Заміська, 254	19565.00	684068
0001	Липень	2012	Іванов	Ставропіль	Ставропольська, 1	4891.00	50000
9567	Листопад	2017	Вальєтов	Татарка	Виїзна, 65	13698.00	789654

Так само впорядкування може виконуватися і в даних, що групуються, за допомогою команди . Але в такому разі команда ORDER BY у запиті має стояти останньою, тобто
SELECT *
FROM Debtors
GROUP BY Sname
ORDER BY Sname DESC;

Якщо ж у полях, що впорядковуються, є осередки з порожніми значеннями (NULL), то, залежно від програм, що працюють з БД, такі осередки можуть розташовуватися або в кінці списку, або на початку.
Існує ще одна хитрість щодо виконання впорядкування даних. Розглянемо запит
SELECT Num, Year, Sname
FROM Debtors
ORDER BY 2 DESC;
Тут під двійкою мається на увазі порядковий номер стовпця з рядка "SELECT Num, Year, Sname" і саме з цього стовпця виконуватиметься ранжування за спаданням у результаті

Debtors

Як видно, значення упорядкувалися по стовпцю Year, де на початку списку пізніша дата.
Отже, впорядкування в SQL має самі принципи ранжування, як у будь-яких комп'ютерних програмах. Все залежить від адміністратора БД та його потреб у тій чи іншій інформації, яку він хоче отримати.

SQL - Оператор SELECT

Назва SQL (Structured Query Language – мова структурованих запитів) відображає той факт, що запити є найчастіше використовуваним елементом SQL. Запит – це оператор, який надсилає команду Системі Управління Базою Даних (СУБД) здійснити маніпуляцію або відобразити певну інформацію. Всі запити щодо вибірки даних у SQL конструюються за допомогою оператора SELECT. Він дозволяє виконувати досить складні перевірки та обробку даних.

Запит може виводити дані з певного стовпця або зі всіх стовпців таблиці. Щоб створити найпростіший SELECT запит, необхідно вказати ім'я стовпця та назву таблиці.

Синтаксис оператора SELECT

SELECT column_list FROM table_name

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

Column_listСписок стовпців таблиці, які вибираються запитом. Стовпці, які не вказані в операторі, не будуть включені в результат. Якщо потрібно вивести дані всіх стовпців, можна використати скорочений запис. Зірочка (*) означає повний список стовпців.

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

Код у дужках не є обов'язковим в операторі SELECT. Він потрібний для більш точного визначення запиту.

Також необхідно сказати, що SQL код є реєстронезалежним. Це означає, що запис SELECT можна написати як select. СУБД не відрізнить ці два записи, проте радять всі оператори SQL писати великими літерами, щоб його можна було відрізнити від іншого коду.

Приклади розглянемо на таблиці Salespeople (продавці) із класичного підручника з SQL Мартіна Грабера.
Ось MySQL код для створення тестової таблиці:

CREATE TABLE `salespeople` (`snum` INTEGER(11) NOT NULL AUTO_INCREMENT, `sname` VARCHAR(20) COLLATE cp1251_general_ci DEFAULT NULL, `city` VARCHAR(20) DEFAULT NULL, PRIMARY KEY (`snum`))ENGINE=InnoDB CHARACTER SET "cp1251" COLLATE "cp1251_general_ci" COMMENT="InnoDB free: 5120 kB"; INSERT INTO `salespeople` (`snum`, `sname`, `city`, `comm`) VALUES (1001, "Peel", "London", 0.120), (1002, "Serres", "San Jose", 0.130 ), (1003, "Axelrod", "New York", 0.100), (1004, "Motika", "London", 0.110), (1007, "Rifkin", "Barcelona", 0.150); COMMIT;

Таблиця виглядає так:

Приклад використання оператора SELECT

1. Необхідно вивести список продавців та відобразити їх імена (sname)

SELECT sname FROM Salespeople

Результат:

Тут після оператора SELECT перераховуються стовпці, які потрібно вивести. Імена стовпців пишуться через кому.

3. Необхідно вивести всю таблицю

Для цього можна використовувати різні синтаксиси написання запитів. Перелічуємо кожен стовпець після оператора SELECT:

SELECT snum , sname , city , comm FROM Salespeople

Або можна досягти того ж результату, використовуючи скорочений запис:

SELECT * FROM Salespeople Результат:

snum	sname	city	comm
1001	Peel	London	0,12
1002	Serres	San Jose	0,13
1003	Axelrod	New York	0,1
1004	Motika	London	0,11
1007	Rifkin	Barcelona	0,15

Також хочеться дати невелику пораду. Для зручності налагодження запитів деякі люди пишуть перелік стовпців, які необхідно вивести, в окремому рядку. Це полегшує коментування коду. Для коментування коду SQL використовується такий синтаксис - /* закоментований код */ .Приклад: SELECT snum ,sname , city , comm FROM Salespeople

Так будуть виведені 4 стовпці, проте за такого запису можна легко закоментувати непотрібний стовпець. Наприклад так:

SELECT /* snum , */ sname , city , comm FROM Salespeople

Тепер стовпець snum не буде виводитися, тому що він закоментований. Дуже швидко та зручно. Як писати SQL код, вирішувати, звичайно, Вам, але знати такі речі іноді буває корисно.

Використання виразів (expressions) в операторі SELECT

Багато СУБД надають спеціальні можливості для обробки результатів запиту. Набір таких засобів у різних СУБД різний, проте є деякі стандартні можливості, такі як висловлювання. Наприклад, можливо, потрібно виконати прості математичні операції над даними, щоб представити їх у більш зручному вигляді, або вставити додатковий текст у результат запиту. SQL дозволяє розміщувати серед вибраних стовпців скалярні вирази та константи, які можуть доповнювати або замінювати стовпці у реченнях SELECT. Розглянемо приклад.

4. Необхідно вивести комісійні (comm) продавців у вигляді відсотків, а не десяткових чисел

SELECT snum, sname, city, comm * 100 FROM Salespeople

Результат:

snum	sname	city	comm
1001	Peel	London	12
1002	Serres	San Jose	13
1003	Axelrod	New York	10
1004	Motika	London	11
1007	Rifkin	Barcelona	15

В останньому стовпці всі отримані дані множаться на 100 і виводяться у вигляді відсотків.

Цей стовпець немає назви, оскільки містить змінені дані і тому називається на розсуд СУБД (наприклад MySQL називає стовпець comm * 100, у прикладах М.Граббера стовпець має ім'я 4, тобто його номер).

У таких випадках зручно використовувати найменування стовпців. Наприклад, можна назвати останній стовпець percent. Для цього після імені стовпця необхідно вказати ключове слово AS, а потім ім'я стовпця в результуючій сторінці.

SELECT snum, sname, city, comm * 100 AS "%" FROM Salespeople Результат:

snum	sname	city	percent
1001	Peel	London	12
1002	Serres	San Jose	13
1003	Axelrod	New York	10
1004	Motika	London	11
1007	Rifkin	Barcelona	15

В результаті запиту останній стовпець називається рядком "percent", що полегшує розуміння.

Так як виводиться число у вигляді відсотків, то непогано позначити це в результаті. На допомогу приходить можливість SQL додавати текст до результату. Виглядає це так:

SELECT snum, sname, city, comm * 100 AS percent , "%" FROM Salespeople Результат:

snum	sname	city	percent	%
1001	Peel	London	12	%
1002	Serres	San Jose	13	%
1003	Axelrod	New York	10	%
1004	Motika	London	11	%
1007	Rifkin	Barcelona	15	%

Видно, що після виведення рядка з БД з'явився новий стовпець, заповнений знаком відсотка (%).

Якщо Вас не влаштовує виведення даних і тексту, що доповнює, в різних стовпцях, то можна використовувати спеціальні функції Вашої СУБД, для об'єднання в один стовпець.

У MySQL для цього використовується функція CONCAT . Ось її визначення із довідника:

CONCAT(str1,str2,...)
Повертає рядок, який є результатом конкатенації аргументів. Якщо хоча б один із аргументів дорівнює NULL, повертається NULL. Може приймати більше ніж 2 аргументи. Числовий аргумент перетворюється на еквівалентну рядкову форму.

Приклад: SELECT snum, sname, city, CONCAT(comm * 100, "%") AS "persent" FROM salespeople Результат:

snum	sname	city	persent
1001	Peel	London	12.000%
1002	Serres	San Jose	13.000%
1003	Axelrod	New York	10.000%
1004	Motika	London	11.000%
1007	Rifkin	Barcelona	15.000%

У цьому запиті, функція CONCAT приймає два аргументи, це comm * 100 і знак відсотка ("%"). Після цього за допомогою AS ми називаємо стовпець.

Важливо знати, що використання функцій погіршує швидкодію. Це не єдиний мінус, але дуже важливий. Тому, якщо можна обійтися стандартним кодом SQL, краще не використовувати функції. Про них варто згадувати лише у крайніх випадках.

Виняток надлишкових даних

Часто буває необхідним виключити значення, що повторюються, з результатів запиту. Для цього використовується ключове слово DISTINCT. Протилежний ефект дає застосування слова ALL, яке використовується за умовчанням (тобто його не потрібно вказувати).

5. Необхідно вивести міста (city), де є продавці

Запит без винятків:

SELECT city FROM salespeople Результат:

Значення London, що повторюються, виключені з результату, що і вимагалося.

Сортування результату за значенням стовпцем

Оператор SELECT виводить дані у довільній послідовності. Для сортування результату за певним стовпцем, SQL використовується оператор ORDER BY (тобто. впорядкувати по….). Цей оператор дозволяє змінити порядок виведення даних. ORDER BY впорядковує результат запиту відповідно до значень одного або кількох стовпців, вибраних у пропозиції SELECT. При цьому для кожного стовпця можна задати сортування за зростанням – ascending (ASC) (цей параметр використовується за умовчанням) або за спаданням – descending (DESC).

Відсортуємо результат по стовпцю sname. Після оператора ORDER BY вказуємо за яким стовпцем сортувати, потім необхідно вказати спосіб сортування

Приклад – сортування за зростанням:

SELECT * FROM Salespeople ORDER BY sname ASC

Результат:

Приклад – сортування за кількома стовпцями:

SELECT snum, sname, city FROM Salespeople ORDER BY sname DESC, city DESC Результат:

snum	sname	city
1002	Serres	San Jose
1007	Rifkin	Barcelona
1001	Peel	London
1004	Motika	London
1003	Axelrod	New York

Декілька важливих зауважень:
- стовпець, за яким відбувається сортування, обов'язково має бути вказаний у SELECT (можна використовувати *)
- оператор ORDER BY завжди пишеться наприкінці запиту

Вітаю вас на моєму блогу сайт. Сьогодні поговоримо про SQL запити для початківців. У деяких веб-майстрів може виникнути питання. Навіщо вивчати SQL? Хіба не можна обійтися?

Виявляється, що для створення професійного інтернет-проекту цього буде замало. Sql використовується для роботи з БД і створення додатків для Вордпрес. Розглянемо як використовувати запити докладніше.

Що це таке

Sql – мова структурованих запитів. Створено визначення типу даних, надання доступу до них та обробці інформації за короткі проміжки часу. Він описує компоненти або результати, які ви хочете бачити на інтернет-проекті.

Якщо говорити по-простому, ця мова програмування дозволяє додавати, змінювати, шукати і відображати інформацію в БД. Популярність mysql пов'язана з тим, що він використовується для створення динамічних інтернет-проектів, основою яких є база даних. Тому для розробки функціонального блогу вам необхідно вивчити цю мову.

Що може робити

Мова sql дозволяє:

• створювати таблиці;
• змінювати отримувати та зберігати різні дані;
• об'єднувати інформацію у блоки;
• захистити дані;
• створювати запити у access.

Важливо! Розібравшись із sql ви зможете писати програми для Вордпрес будь-якої складності.

Яка структура

БД складається з таблиць, які можна подати у вигляді Ексель файлу.

У неї є ім'я, колонки та ряд з якоюсь інформацією. Створювати подібні таблиці можна за допомогою запитів sql.

Що потрібно знати

Основні моменти щодо Sql

Як зазначалося вище, запити застосовуються для обробки та введення нової інформації в БД, що складається з таблиць. Кожен її рядок – це окремий запис. Отже, створимо БД. Для цього напишіть команду:

Create database ‘bazaname’

У лапках пишемо ім'я БД на латиниці. Намагайтеся вигадати для неї зрозуміле ім'я. Не створюйте бази типу «111», «www» тощо.

Після створення БД встановлюємо:

SET NAMES 'utf-8'

Це потрібно, щоб контент на сайті правильно відображатися.

Тепер створюємо таблицю:

CREATE TABLE 'bazaname'. ‘table’ (

id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,

log VARCHAR(10),

pass VARCHAR(10),

date DATE

У другому рядку ми прописали три атрибути. Подивимося, що вони означають:

• Атрибут NOT NULL означає, що осередок не буде порожнім (поле обов'язкове для заповнення);
• Значення AUTO_INCREMENT - автозаповнення;
• PRIMARY KEY – первинний ключ.

Як додати інформацію

Для заповнення поля створеної таблиці значеннями використовується оператор INSERT. Пишемо такі рядки коду:

INSERT INTO ‘table’

(login, pass, date) VALUES

('Vasa', '87654321', '2017-06-21 18:38:44');

У дужках вказуємо назву стовпців, а наступної - значення.

Важливо! Дотримуйтесь послідовності назв і значень стовпців.

Як оновити інформацію

Для цього використовується команда UPDATE. Подивимося, як змінити пароль конкретного користувача. Пишемо такі рядки коду:

UPDATE 'table' SET pass = '12345678' WHERE id = '1'

Тепер поміняйте пароль '12345678'. Зміни відбуваються у рядку з id=1. Якщо не писати команду WHERE – поміняються всі рядки, а не конкретні.

Рекомендую вам придбати книгу « SQL для чайників ». За її допомогою ви зможете крок за кроком професійно працювати з БД. Вся інформація побудована за принципом від простого до складного і добре сприйматиметься.

Як видалити запис

Якщо ви написали щось не так, виправте це за допомогою DELETE. Працює так само, як і UPDATE. Пишемо такий код:

DELETE FROM 'table' WHERE id = '1'

Вибірка інформації

Для отримання значень з БД використовується команда SELECT. Пишемо такий код:

SELECT * FROM 'table' WHERE id = '1'

У даному прикладіу таблиці вибираємо всі наявні поля. Це відбувається, якщо прописати в команді зірочку «*». Якщо потрібно вибрати якесь вибіркове значення, пишемо так:

SELECT log , pass FROM table WHERE id = ‘1’

Необхідно відзначити, що вміння працювати з базами даних буде недостатньо. Для створення професійного інтернет-проекту доведеться навчитися додавати на сторінки дані з БД. Для цього ознайомтеся з мовою веб-програмування php. У цьому вам допоможе класний курс Михайла Русакова .

Видалення таблиці

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

DROP TABLE table;

Виведення запису з таблиці за певною умовою

Розглянемо такий код:

SELECT id, countri, city FROM table WHERE people>150000000

Він відобразить записи країн, де населення більше ста п'ятдесяти мільйонів.

Об'єднання

Зв'язати разом кілька таблиць можна за допомогою Join. Як це працює подивіться докладніше у цьому відео:

PHP та MySQL

Ще раз хочу наголосити, що запити при створенні інтернет-проекту – це звичайна справа. Щоб їх використовувати у php-документах, виконайте такий алгоритм дій:

• Поєднуємося з БД за допомогою команди mysql_connect();
• Використовуючи mysql_select_db(), вибираємо потрібну БД;
• Обробляємо запит за допомогою mysql_fetch_array();
• Закриваємо з'єднання командою mysql_close().

Важливо! Працювати з БД не складно. Головне – правильно написати запит.

Початківці вебмайстра подумають. А що почитати з цієї теми? Хотілося б порекомендувати книгу Мартіна Грабера « SQL для простих смертних ». Вона написана так, що новачкам все буде зрозуміло. Використовуйте її як настільну книгу.

Але це теорія. Яка ж справа на практиці? Насправді інтернет-проект потрібно не тільки створити, а ще й вивести в ТОП Гугла та Яндекса. У цьому вас допоможе відеокурс Створення та розкрутка сайту ».

Відео інструкція

Чи залишилися ще питання? Перегляньте детальніше онлайн відео.

Висновок

Отже, розібратися з написанням SQL запитів не так важко, як здається, але зробити це потрібно будь-якому вебмайстру. У цьому допоможуть відеокурси, описані вище. Підпишіться на мою групу ВКонтакте щоб першими дізнаватись про появу нової цікавої інформації.

Запити написані без лапок, що екранують, так як у MySQL, MS SQLі PostGreeвони різні.

SQL запит: отримання вказаних (потрібних) полів із таблиці

SELECT id, country_title, count_people FROM table_name

Отримуємо список записів: ВСІ країни та їх населення. Назва потрібних полів вказуються через кому.

SELECT * FROM table_name

* Позначає всі поля. Тобто будуть покази АБСОЛЮТНО ВСІполя даних.

SQL запит: виведення записів з таблиці за винятком дублікати

SELECT DISTINCT country_title FROM table_name

Отримуємо список записів: країни, де є наші користувачі. Користувачів може бути багато з однієї країни. В цьому випадку це ваш запит.

SQL запит: виведення записів із таблиці за заданою умовою

SELECT id, country_title, city_title FROM table_name WHERE count_people>100000000

Отримуємо список записів: країни, де кількість людей більша за 100 000 000.

SQL запит: виведення записів із таблиці з упорядкуванням

SELECT id, city_title FROM table_name ORDER BY city_title

Отримуємо список записів: міста за абеткою. На початку А, наприкінці Я.

SELECT id, city_title FROM table_name ORDER BY city_title DESC

Отримуємо список записів: міста у зворотному ( DESC) порядку. На початку Я, наприкінці А.

SQL запит: підрахунок кількості записів

SELECT COUNT(*) FROM table_name

Отримуємо число (кількість) записів у таблиці. В даному випадку НІ списку записів.

SQL запит: виведення потрібного діапазону записів

SELECT * FROM table_name LIMIT 2, 3

Отримуємо 2 (другий) та 3 (третій) запис з таблиці. Запит корисний під час створення навігації на WEB сторінках.

SQL запити з умовами

Виведення записів із таблиці за заданою умовою з використанням логічних операторів.

SQL запит: конструкція AND (І)

SELECT id, city_title FROM table_name WHERE country="Росія" AND oil=1

Отримуємо список записів: міста з Росії Імають доступ до нафти. Коли використовується оператор AND, то повинні збігатися обидві умови.

SQL запит: конструкція OR (АБО)

SELECT id, city_title FROM table_name WHERE country="Росія" OR country="США"

Отримуємо список записів: усі міста з Росії АБОСША. Коли використовується оператор OR, то має збігатися Хоча б одна умова.

SQL запит: конструкція AND NOT (І НЕ)

SELECT id, user_login FROM table_name WHERE country="Росія" AND NOT count_comments<7

Отримуємо список записів: всі користувачі з Росії Ізробили НЕ МЕНШЕ 7 коментарів.

SQL запит: конструкція IN (В)

SELECT id, user_login FROM table_name WHERE country IN ("Росія", "Болгарія", "Китай")

Отримуємо список записів: всі користувачі, які проживають в ( IN) (Росії, або Болгарії, або Китаю)

SQL запит: конструкція NOT IN (НЕ В)

SELECT id, user_login FROM table_name WHERE country NOT IN ("Росія", "Китай")

Отримуємо список записів: всі користувачі, які проживають не ( NOT IN) (Росії чи Китаї).

SQL запит: конструкція IS NULL (порожні чи НЕ порожні значення)

SELECT id, user_login FROM table_name WHERE status IS NULL

Отримуємо список записів: усі користувачі, де status не визначено. NULL – це окрема тема і тому вона перевіряється окремо.

SELECT id, user_login FROM table_name WHERE state IS NOT NULL

Отримуємо список записів: усі користувачі, де status визначений (НЕ НУЛЬ).

SQL запит: конструкція LIKE

SELECT id, user_login FROM table_name WHERE surname LIKE "Іван%"

Отримуємо список записів: користувачі, у яких прізвище починається із комбінації «Іван». Знак % означає будь-яку кількість будь-яких символів. Щоб знайти знак % потрібно використовувати екранування "Іван\%".

SQL запит: конструкція BETWEEN

SELECT id, user_login FROM table_name WHERE salary BETWEEN 25000 AND 50000

Отримуємо список записів: користувачі, які одержують зарплату від 25000 до 50000 включно.

Логічних операторів дуже багато, тому детально вивчіть документацію по SQL серверу.

Складні SQL запити

SQL запит: об'єднання кількох запитів

(SELECT id, user_login FROM table_name1) UNION (SELECT id, user_login FROM table_name2)

Отримуємо список записів: користувачі, які зареєстровані в системі, а також користувачі, які зареєстровані на форумі окремо. Оператор UNION може об'єднати кілька запитів. UNION діє як SELECT DISTINCT, тобто відкидає повторювані значення. Щоб отримати абсолютно всі записи, необхідно використовувати оператор UNION ALL.

SQL запит: підрахунки значень поля MAX, MIN, SUM, AVG, COUNT

Висновок одного, максимального значення лічильника у таблиці:

SELECT MAX(counter) FROM table_name

Висновок одного, мінімального значення лічильника в таблиці:

SELECT MIN(counter) FROM table_name

Висновок суми всіх значень лічильників у таблиці:

SELECT SUM(counter) FROM table_name

Висновок середнього значення лічильника у таблиці:

SELECT AVG(counter) FROM table_name

Виведення кількості лічильників у таблиці:

SELECT COUNT(counter) FROM table_name

Виведення кількості лічильників у цеху №1, у таблиці:

SELECT COUNT(counter) FROM table_name WHERE office="Цех №1"

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

SQL запит: групування записів

SELECT continent, SUM(country_area) FROM country GROUP BY continent

Отримуємо список записів: з назвою континенту та із сумою площ усіх їхніх країн. Тобто, якщо є довідник країн, де кожна країна має її площу, то за допомогою конструкції GROUP BY можна дізнатися розмір кожного континенту (на основі угруповання по континентах).

SQL запит: використання кількох таблиць через аліас (alias)

SELECT o.order_no, o.amount_paid, c.company FROM orders AS o, customer AS з WHERE o.custno=c.custno AND c.city="Тюмень"

Отримуємо список записів: замовлення від покупців, які мешкають тільки в Тюмені.

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

SELECT o.order_no, o.amount_paid, z.company FROM orders AS o LEFT JOIN customer AS z ON (z.custno=o.custno)

Вкладені підзапити

SELECT * FROM table_name WHERE salary=(SELECT MAX(salary) FROM employee)

Отримуємо один запис: інформацію про користувача з максимальним окладом.

Увага!Вкладені підзапити є одним із найвужчих місць у SQL серверах. Спільно зі своєю гнучкістю та потужністю, вони також суттєво збільшують навантаження на сервер. Що призводить до катастрофічного уповільнення роботи інших користувачів. Дуже часті випадки рекурсивних дзвінків при вкладених запитах. Тому рекомендую НЕ використовувати вкладені запити, а розбивати їх на дрібніші. Або використовувати вищеописану комбінацію LEFT JOIN. Крім цього цього запити є підвищеним осередком порушення безпеки. Якщо вирішили використовувати вкладені підзапити, проектувати їх потрібно дуже уважно і початкові запуски зробити на копіях баз (тестові бази).

SQL запити, що змінюють дані

SQL запит: INSERT

Інструкція INSERTдозволяють вставляти записи до таблиці. Простими словами, створити рядок з даними у таблиці.

Варіант №1. Часто використовується інструкція:

INSERT INTO table_name (id, user_login) VALUES (1, "ivanov"), (2, "petrov")

У таблицю « table_name» буде вставлено 2 (два) користувача відразу.

Варіант №2. Зручніше використовувати стиль:

INSERT table_name SET id=1, user_login="ivanov"; INSERT table_name SET id=2, user_login="petrov";

У цьому є свої переваги та недоліки.

Основні недоліки:

• Безліч дрібних SQL запитів виконуються трохи повільніше, ніж один великий SQL запит, але інші запити стоятимуть у черзі обслуговування. Тобто, якщо великий SQL запит буде виконуватися 30 хвилин, то весь цей час інші запити будуть курити бамбук і чекати своєї черги.
• Запит виходить більш масивним, ніж попередній варіант.

Основні переваги:

• Під час дрібних запитів SQL, інші SQL запити не блокуються.
• Зручність у читанні.
• Гнучкість. У цьому варіанті можна не дотримуватися структури, а додавати тільки необхідні дані.
• При формуванні подібним чином архівів можна легко скопіювати один рядок і запустити його через командний рядок (консоль), тим самим не відновлюючи АРХІВ повністю.
• Стиль запису схожий на інструкцію UPDATE, що легше запам'ятовується.

SQL запит: UPDATE

UPDATE table_name SET user_login="ivanov", user_surname="Іванів" WHERE id=1

В таблиці " table_name» у записі з номером id=1, буде змінено значення полів user_login та user_surname на вказані значення.

SQL запит: DELETE

DELETE FROM table_name WHERE id=3

У таблиці table_name буде видалено запис із id номером 3.

1. Всі назви полів рекомендуються писати маленькими літерами і, якщо треба, розділяти їх через примусову прогалину «_» для сумісності з різними мовами програмування, таких як Delphi, Perl, Python та Ruby.
2. SQL команди писати ВЕЛИКИМИ літерами для зручності читання. Пам'ятайте завжди, що після вас можуть читати код та інші люди, а найімовірніше ви самі через N кількість часу.
3. Називати поля з початку іменник, а потім дію. Наприклад: city_status, user_login, user_name.
4. Намагатися уникати резервних слів у різних мовах які можуть викликати проблеми в мовах SQL, PHP або Perl, типу (name, count, link). Наприклад: link можна використовувати в MS SQL, але MySQL зарезервовано.

Цей матеріал є короткою довідкою для повсякденної роботи і не претендує на супер мега авторитетне джерело, яким є першоджерело SQL запитів тієї чи іншої бази даних.

• Переклад
• Tutorial

Потрібно "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 ) 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	title
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";

Результат:

Title	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 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="

Що дасть нам:

First Name	Last Name
Міке	Willis
Ellen	Horton
Ellen	Horton

Чудово! Але імена повторюються (вони унікальні). Ми скоро це виправимо.

5. Агрегування

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

Давайте продовжимо наш приклад, в якому з'являються імена, що повторюються. Видно, що Ellen Horton взяла більше за одну книгу, але це не самий кращий спосібпоказати цю інформацію. Можна зробити інший запит:

SELECT members.firstname AS "First Name", members.lastname AS "Last Name", count(*) AS "Number of books rowed" FROM borrowings JOIN books ON borrowings.bookid=books.bookid JOIN members ON members.memberid= .memberid WHERE books.author="Dan Brown" GROUP BY members.firstname, members.lastname;

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

First Name	Last Name	Номер books borrowed
Міке	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";

Результат:

Можна записати як: ["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);

Результат:

title	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;

Що дає нам:

7. Операції запису

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

7.1 Update

Синтаксис запиту UPDATE семантично збігається із запитом читання. Єдина відмінність у тому, що замість вибору колонок SELECT "ом, ми задаємо знання SET".

Якщо всі книги Дена Брауна загубилися, потрібно обнулити значення кількості. Запит для цього буде таким:

UPDATE books SET stock=0 WHERE author="Dan Brown";

WHERE робить те саме, що раніше: вибирає рядки. Замість SELECT , який використовувався під час читання, ми тепер використовуємо SET . Однак тепер потрібно вказати не тільки ім'я колонки, але й нове значення для цієї колонки у вибраних рядках.

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,"Inferno" "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 ) FROM books)) GROUP BY members.firstname, members.lastname;

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

Результат:

Full Name
Lida Tyler

Сподіваюся, вам удалося розібратися без проблем. Але якщо ні, то буду радий вашим коментарям та відгукам, щоб я міг покращити цей пост.

Теги: Додати теги