Вкладені запити (Частина перша та друга)

 

Вкладені запити (Частина перша)

Вкладений запит дозволяє використовувати результат, що повертається одним запитом, в іншому запиті. Можливість застосування одного запиту всередині іншого і була причиною появи слова "структурований" в назві "структурована мова запитів" (SQL). Так як результат повертає лише оператор SELECT, то в якості "вкладеного" запиту завжди виступає SELECT-запит (інформаційні SQL-запити з використанням нестандартних операторів SHOW, DESCRIBE до уваги не приймаються). В якості зовнішнього запиту може виступати запит з участю будь-якого SQL-оператора: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE та ін

Зауваження

1 Термін "вкладений запит" має різні варіанти в україномовній літературі, такого роду запити часто називають підлеглими запитами і підзапитами.

2. Вкладені запити з'явилися в СУБД MySQL, починаючи з версії 4.1.

Нехай потрібно вивести назву товарних позицій з таблиці products для розділу "Процесори" таблиці catalogs. Вирішити це завдання можна, наприклад, за допомогою двох SQL-запитів. Перший запит витягує первинний ключ (id_catalog) запис з таблиці catalogs, відповідного розділу "процесори". Другий запит виводить назви товарних позицій і ціни для даного каталогу.

 

Приклад

SELECT @ id: = id_catalog FROM catalogs WHERE name = 'Процесори';

SELECT name, price FROM products WHERE id catalog = @id ORDER BY price;

 

name                             price

Celeron 1.8                   155.00

Celeron D 315 2.26GHz         180.00

 

Як видно з прикладу, проміжне значення міститься в змінну @id.

Вкладені запити дозволяють здійснити подібну вибірку в одному запиті.

 

Приклад

SELECT name, price

FROM products WHERE id_catalog = (SELECT id_catalog FROM catalogs WHERE name = 'Процессоры')  ORDER BY price;

 

name                             price

Celeron 1.8                   155.00

Celeron D 315 2.26GHz         180.00

 

Тут в якості зовнішнього запиту виступає SELECT name, price FROM products ав якості вкладеного запиту використовується запит SELECT id catalog FROM catalogs WHERE name = 'Процесори'. Таким чином, результат вкладеного запиту (id_catalog), не використовує проміжні змінні MySQL, або зовнішньої програми, а виконується як елемент зовнішнього запиту. Вкладений запит завжди поміщається в круглі дужки

Отримати аналогічний результат можна за допомогою багатотабличного запиту.

 

Приклад

SELECT products.name, products.price FROM catalogs, products WHERE catalogs.id_catalog = products.id_catalog AND catalogs.name = 'Процесори' ORDER BY price;

 

name                             price

Celeron 1.8                   155.00

Celeron D 315 2.26GHz         180.00

 

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

Зауваження

Вкладений запит може застосовуватися не тільки з умовою WHERE, але також в конструкції distinct, group by, order by, limit, join, union, в функціях і т. д.

 

Вкладені запити як скалярний операнд

 

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

 

Приклад

SELECT name FROM catalogs

WHERE id_catalog = (SELECT id_catalog FROM products

WHERE price = (SELECT MAX(price) FROM products));

 

name

Процесори

 

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

 

SELECT MAX (price) FROM products

 

Зовнішній по відношенню до нього запит визначає значення первинного ключа (id_catalog) для товарної позиції з максимальним значенням ціни (725.00):

 

SELECT id catalog FROM products WHERE price = 725.00

 

Отримане значення поля id catalog (рівне 1) підставляється у зовнішній запит

 

SELECT name FROM catalogs WHERE id_catalog = 1

 

Зауваження

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

 

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

 

Використання вкладеного запиту в списку стовпців

 

SELECT id_catalog, (SELECT MAX(price) FROM products) FROM catalogs;

 

id_catalog  (SELECT MAX(price) FROM products)

1                 7259.00

2                 7259.00

3                 7259.00

 

Вкладений запит select max (price) from products повертає максимальнезначення ціни з таблиці products (725.00) і підставляє це значення на місцезапиту, тому запит можна представити як запит із скалярною величиною в якостістовпця

 

SELECT id_catalog, 7259.00 FROM catalogs;

 

id_catalog  (SELECT MAX(price) FROM products)

1                 7259.00

2                 7259.00

3                 7259.00

 

Допустимі також запити такого виду: вкладений запит повертає назву каталогу зпервинним ключем id_catalog = 1, а зовнішній запит, що складається з одногооператора select, виводить його.

 

SELECT (SELECT name FROM catalogs WHERE id catalog = 1);

 

(SELECT name FROM catalogs WHERE id_catalog = 1)

Процесори

 

При використанні вкладених запитів в якості аргументів вбудованих функційMySQL слід пам'ятати про необхідність розміщення вкладеного запиту в додаткові круглі дужки. У прикладі назва каталогу з первинним ключемid_catalog = 1 з таблиці catalogs передається в якості аргументу функції UPPER,яка переводить рядок у верхній регістр. При цьому використовуються подвійнікруглі дужки - одні для позначення вкладеного запиту, інші - для позначення ознаки функції.

 

SELECT UPPER((SELECT name FROM catalogs WHERE id_catalog=1));

 

UPPER((SELECT name FROM catalogs WHERE id_catalog=1));

ПРОЦЕСОРИ

 

Найбільше часто вкладені запити використовуються в операціях порівняння вумовах які задаються ключовими словами where, having або on. Для цьогозадіюються шість операторів порівняння ("=", "<>", "<", "<=" ">" ">="). Вкладенийзапит, який бере участь в операції порівняння, повинен повертати в якостірезультату одиничне значення.

 

Приклад

SELECT name FROM catalogs

WHERE id_catalog > (SELECT id_catalog FROM products WHERE id_product = 10);

 

name

Відеоадаптери

Жорсткіе диски

Оперативна пам’ять

 

SELECT name FROM catalogs

WHERE (SELECT id_catalog FROM products

WHERE id_product = 10) < id_catalog;

name

Відеоадаптери

Жорсткіе диски

Оперативна пам’ять

 

Повертаються назви каталогів, значення первинного ключа id_catalog для яких більше значення первинного ключа каталогу, в якому знаходиться товарнапозиція з id_product = 10.

Примітка

У прикладі показано, що вкладений запит може розташовуватися як справа так і зліва від оператора порівняння. Однак краще розташовувати його в правій частині, як це продемонстровано в першому запиті, для сумісності з іншимиСУБД, які не задовольняють стандарту SQL2, в якому було усунуто подібне обмеження.

 

Вкладені запити, які повертають кілька рядків

 

У попередньому були розглянуті запити, де вкладений запит повертає єдине значення. Якщо вкладений запит повертає кілька рядків, СУБД MySQL повертаєпомилку 1242 - вкладений запит повертає більш ніж один рядок (приклад).

 

SELECT name FROM catalogs

WHERE id_catalog = (SELECT id_catalog FROM products);

 

ERROR 1242: Subquery returns more than 1 row

 

Оператор IN

Для того щоб вибрати рядки з таблиці catalogs, у яких первинний ключ id_catalogзбігається з одним зі значень, що повертаються вкладеним запитом, слідскористатися конструкцією in.

 

Приклад

SELECT name FROM catalogs

WHERE id_catalog IN (SELECT id_catalog FROM products

GROUP BY id catalog);

 

name

Процесори

Материнські плати

Відеоадаптери

Жорсткі диски

Оперативна пам’ять

 

Запит, представлений в прикладі, аналогічний запиту

 

SELECT name FROM catalogs WHERE id_catalog IN (1,2,3,4,5);

 

name

Процесори

Материнські плати

Відеоадаптери

Жорсткі диски

Оперативна пам’ять

 

Для того щоб повернути рядки, які відсутні в результуючій таблиці, яка повертається вкладеним запитом, слід скористатися оператором NOT in, представленим у прикладі

 

SELECT name FROM catalogs

WHERE id catalog NOT IN (SELECT DISTINCT id_catalog

FROM products WHERE id_catalog<3);

 

name

Відеоадаптери

Жорсткі диски

Оперативна пам’ять

 

Оператор ANY (SOME)

 

Конструкція in дозволяє здійснити пошук величини в списку і висловлює логікузапиту, поданого у прикладі. Однак на місці оператора в цьому запиті можестояти інший оператор порівняння (приклад). Для цього використовується оператор any

Зауваження

Оператор any має синонім some, проте краще використовувати саме any.

 

SELECT id_catalog, name FROM catalogs

WHERE id_catalog > ANY (SELECT id_catalog FROM products);

 

id_catalog  name

2                 Материнскі плати

3                 Відеоадаптери

4                 Жорсткі диски

5                 Оперативна пам’ять

 

Ключове слово ANY може застосовуватися для порівняння значень звикористанням одного з шести операторів порівняння ("=", "<>", "<", "<=", ">",">="). Перевіряється значення id_catalog по черзі порівнюється з кожнимелементом, який повертає вкладений запит. Якщо хоча б одне з порівняньповертає 1 (істина), рядок виводиться запитом. У прикладі відбувається порівняння значень первинного ключа id_catalog (1, 2, 3, 4, 5), які присутні втаблиці catalogs, зі значеннями поля id_catalog (1, 2, 3, 4, 5) з таблиці products.Значення id_catalog = 1 не задовольняє ні одній умові:

 

1> 1 - 0 (брехня)

1> 2 - 0 (брехня)

1> 3 - 0 (брехня)

1> 4 - 0 (брехня)

1> 5 - 0 (брехня)

 

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

 

Тобто запит вигляду

"Where X> ANY (SELECT Y. ..)"

Можна інтерпретувати як "де X більше хоча б одного обраного Y".

 

А запит вигляду

"Where x <any (select y. ..)"
Слід читати "де X менше, ніж будь-який обраний Y. .."

 

Запит, поданий у прикладі, повертає ім'я та прізвище клієнтів з таблиці users, які зробили хоча б одну покупку.

 

Приклад

SELECT name, surname FROM users

WHERE id_user = ANY (SELECT id_user FROM orders);

 

name          surname

Олександр Іванов

Ігор            Сімідянов

Олександр Корнеев

 

На практиці порівняння з використанням ключового слова any може призводитидо помилок, які важко виявити, особливо коли застосовується операторпорівняння "не дорівнює" ("<>" або "!="). Так, якщо буде потрібно повернутисписок клієнтів, не здійснювали жодної покупки, виникне спокуса замінитиоператор рівності "=" (попередній приклад) на оператор нерівності "<>" (приклад).

 

SELECT name, surname FROM users

WHERE id_user <> any (SELECT id_user FROM orders);

 

 name                   surname

Олександр          Іванов

Сергій                 Лосєв

Ігор            Сімідянов

Максим      Кузнєцов

Анатолій   Поганець

Олександр Корнєєв

 

Результатом є повний список покупців як здійснювали, так і не здійснювалипокупок, оскільки значення id_user порівнюється з кожним значенням, якеповертає вкладений запит. Серед цих значень завжди знайдеться число, яке недорівнюватиме поточному значенню id_user. Вирішити цю задачу можна за допомогою операторів exists і not exists, які розглянуті в далі.

 

Оператор ALL

Замість ключового слова any може бути використано оператор all, який точнотак само використовується спільно з одним з шести операторів порівняння("=","<>", "<", "<=", ">", "> =" ). У цьому випадку перевіряєме значення також по черзі порівнюється з кожним елементом, який повертає вкладений запит, але рядки повертається тільки тоді, коли всі порівняння повертають 1 (істина).

Зауваження

Якщо у виразах з ключовим словом any використовується логіка АБО, тобтодостатньо, щоб спрацьовувало хоча б одна з багатьох умов, то в разі allвикористовується логіка І - повинні спрацьовувати всі умови.

 

У прикладі представлений запит, який повертає всі товарні позиції з таблиціproducts бази даних shop, ціна яких перевищує середню ціну кожного з каталогів.

 

Приклад

SELECT name, price FROM products

WHERE price > ALL (SELECT AVG(price) FROM products В

GROUP BY id_catalog);

 

name                                               price

lintel Pentium 4 3.2GHz                           725.00

Intel Pentium 4 3.0GHz                            614.00

Intel Pentium 4 3.0GHz                            567.00

Asustek P4C80C-E Delux               539.00

ASUSTEK A9600XT/TD               515.00

GIGABYTE AGP GV-N59X128D 588.00

 

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

 

SELECT AVG(price) FROM products GROUP BY id_catalog;

 

AVG(price)

348.333333

280.166667

384.500000

274.00000C

141.000000

 

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

 

Тобто запит вигляду

"WHERE> all (select Y. ..)"

Можна тлумачити як "де X більше будь-якого обраного Y".

 

А запит вигляду

"Where X <all (select Y. ..)"
Слід читати "де X менше, ніж всі вибрані Y. ..".

Помилки, які можуть виникнути, якщо оператор any містить умову "<>", відбуваються і при all.

 

Перевірка на існування

 

Результуюча таблиця, яка повертається вкладеним запитом, може бути порожньою, тобто не містити жодного рядка. Для перевірки даного фактувикористовуються ключові слова exists і not exists. Дані ключові слова непотребують лівого операнда, вони просто повідомляють, чи є в результуючій таблиці рядки чи нема. Якщо вкладений запит повертає більше одного рядка,exists повертає 1 (істина), в іншому випадку оператор повертає 0 (брехня).Оператор not exists діє протилежним чином.

 

Зауваження

Перевірка на існування допустима лише у вкладених запитах.

 

Нехай потрібно вивести назви товарних позицій з таблиці products, які буливибрані покупцями, тобто для яких є відмітка про купівлю в таблиці orders(приклад).

 

SELECT id_product, name, price FROM products

WHERE EXISTS (SELECT * FROM orders

WHERE orders.id_product = products.id_product);

 

id_product name                                      price

8                 Intel Pentium 4 3.0GHz          614.00

10               Gigabyte GA-8I848P-RS        189.00

20               Maxtor 6Y120P0                   245.00

 

Зовнішній запит послідовно перебирає всі рядки таблиці products і і для кожноїтоварної позиції виконує вкладений запит, який перевіряє, чи є в таблиці ordersпоточна товарна позиція. Число записів, які повертає вкладений запит, можебути більше однієї (див. вміст таблиці).

 

SELECT id_product FROM orders;

id_product

         8

         10

         20

         20

         20

 

як видно з прикладу, для перших двох рядків результуючої таблиці вкладений запит поверне по одному рядку (id_product = 8, 10), для останньої їхбуде три (id_product = 20). Тобто оператор exists насправді невикористовує результати вкладеного запиту, перевіряється тільки кількість рядків. Це означає, що в списку стовпців, наступних після оператора select вкладеного запиту, замість символу "*" може бути розташоване будь припустиме ім'я - назва колонки або просто цифра, на результатах це не відіб'ється (приклад).

 

SELECT idjproduct, name, price FROM products

WHERE EXISTS (SELECT 7 FROM orders

WHERE orders.id_product = products.id_product);

 

id_product name                                      price

8                 Intel Pentium 4 3.0GHz          614.00

10               Gigabyte GA-8I848P-RS        189.00

20               Maxtor 6Y120P0                   245.00

 

Важливо відзначити, що в where-умови вкладеного запиту допускаєтьсявикористання стовпців зовнішнього запиту при зверненні до них за повниміменем. У прикладі до поточної товарної позиції з зовнішнього запиту у вкладеному запиті можна звернутися по імені products.id_product.

 

Зауваження

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

 

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

 

SELECT name, surname FROM users

WHERE EXISTS (SELECT * FROM orders

WHERE orders.id_user = users.id_user);

 

name          surname

Олександр Іванов

Ігор            Сімідянов

Олександр Корнеев

 

Зворотну задачу, тобто отримання списку покупців, які ще не здійснювалижодної покупки, можна легко вирішити, скориставшись запереченням-not exists(приклад) (на відміну оператора any (і all)).

 

Приклад

SELECT name, surname FROM users

WHERE NOT EXISTS (SELECT * FROM orders

WHERE orders.id_user = users.id_user);

 

name          surname

Сергій                 Лосев

Максим      Кузнецов

Анатолій   Поганець

 

Корельовані запити

 

У вкладеному запиті часто потрібно посилатися на значення стовпця впоточному рядку зовнішнього запиту. Розглянемо запит, що витягає з таблиціproducts назви придбаних товарів, для яких число куплених товарних позиційзбігається із запасами на складі (приклад).

 

SELECT name, count FROM products

WHERE count = (SELECT SUM(count) FROM orders

WHERE orders. id_product = products. id_product);

 

name                                      count

Intel Pentium 4 3.0GHz                   1

Gigabyte GA-8I848P-RS       4

 

У вкладеному запиті замість значення products.id_roduct підставляється поточне значення id_product із зовнішнього запиту. Вкладений запит міститьпосилання на стовпець таблиці products, незважаючи на те, що конструкціяfrom вкладеного запиту не згадує таблицю products. Такий зв'язок зовнішнього запиту з внутрішнім називається зовнішнім посиланням. Вкладений запит, який містить зовнішнє посилання, називаеться корельованим вкладеним запитом,тому що його результати корельовані з кожним рядком таблиці в зовнішньомузапиті. З тієї ж причини зовнішнє посилання називається іноді корелюючимпосиланням.

 

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

 

Вкладені запити, які повертають декілька стовпців

 

До цих пір розглядалися вкладені запити, які повертають єдиний стовпець,проте в СУБД MySQL реалізовані так звані рядкові запити, які повертаютьбільше одного стовпця. Приклади таких запитів представлені в прикладі

 

Приклад

SELECT * FROM tbll WHERE (1,2) = (SELECT coll, col2 FROM tbl2);

 SELECT * FROM tbll WHERE ROW(l,2) = (SELECT coll, col2 FROM tbl2);

 

Запит повертає рядок, якщо в таблиці tbl2 присутній запис, для якогосправедлива рівність coll = 1 і со12 = 2.

 

Вирази "(1,2)" і "row (1,2)" називаються конструкторами рядка. Обидва вирази єеквівалентними, але з метою збільшення читабельності запиту переважно використовують другий варіант.

 

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

 

Приклад

SELECT name, price, count, mark FROM products

WHERE (6,4) = (count,mark);

 

name                                                         price           count          mark

Mustek P4P80C-VM\L 1665G                 251.00        6                 4.0

MusSTEK V9S20X                         160.00        6                 4.0

 

Запит з прикладу можна переписати так

SELECT name, price, count, mark FROM products  

WHERE count = 5 and mark = 4;

 

name                             price           count          mark

Epox EP-4PDA3I                  228.00                 5       4.0

Samsung SP0812C       209.00                 5       4.0

 

Нехай потрібно з'ясувати час оформлення замовлення на товар, ціна якогоперевищує 100 грн., Всіма відвідувачами зі статусом gold.

 

SELECT ordertime FROM orders

WHERE (id_user, id_product) IN (SELECT users.id_user,

products. id__product

FROM users, products

WHERE products.price > 1000 AND users.status = 'gold');

 

ordertime

2005-02-10 09:40:29

 

Як видно з запиту, поданого в прикладі, після конструктора рядка використовується конструкція in. Це пов'язано з тим, що вкладений запит можеповернути більше одного рядка і застосування знака рівності "=" призведе до виникнення помилки 1242 - вкладений запит повернув більше одного рядка.

 

Зауваження

При порівнянні конструктора рядка і результатів, які повернув вкладений запит, не слід застосовувати оператори, відмінні від рівності "=" і in, т як. результатможе бути непередбачуваним.

 

Рядкові запити часто використовуються для порівняння таблиць один з одним.Нехай таблиці tbl1 і tbl2 складаються з трьох стовпців col1, соl2 і соl3. Тодізнайти рядки таблиці tbl1, які є також і в таблиці tbl2, можна за допомогоюпредставленого запиту.

 

SELECT coll, col2r col3 FROM tbll

Where ROW(coll, col2, col3) IN (SELECT coll, col2, col3 FROM tbl2);

 

Підзапити в конструкції FROM

 

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

 

select usr.surname, usr.name, orders .ordertime

from orders,

(select * from users) as usr

where orders.id_user = usr.id_user;

 

surname               name                   ordertime

Вимдянов           Ігор                     2005-01-04 10:39:38

Корнеев              Олександр          2005-02-10 09:40:29

Іванов                 Олександр          2005-02-18 13:41:05

Шимдянов          Ігор                     2005-03-10 18:20:00

Вимдянов           Ігор                     2005-03-17 19:15:36

 

Ключовий оператор as, що призначаютє псевдонім результуючої таблиці, єобов'язковим, тому що кожна таблиця в конструкції from повинна мати ім'я.Наприклад, запит, що виводить список всіх угод, з прізвищами та ініціаламипокупців, найменуванням товарних позицій і каталогу, може виглядати так, якце представлено у слідуючому прикладі. Запит з трьох таблиць, причому уякості двох таблиць (users та goods) виступають вкладені запити. Результуючатаблиця users містить лише два стовпці: id_user і name, в які заноситьсяпрізвище покупця і його ініціали, виділені за допомогою функції substring () іоб'єднані у один рядок за допомогою функції concat (). Таблиця goods має триполя:

 

+ Id_products - унікальний номер товарної позиції;

 

+ Productname - назва товарної позиції;

 

+ Сatalogname - назва каталогу.

 

Цікаво відмітити, що таблиця goods сама сформована за допомогою двохтабличного запиту з таблиць products і catalogs.

 

SELECT users.name,

goods.productname,

goods.catalogname

FROM orders,

(

SELECT id_user,

CONCAT(surname, " SUBSTRING(name,1,1),

SUBSTRING(patronymic,1,1), ".") AS name

FROM users

) AS users,

(

SELECT products.id_product AS id_product,

products.name AS productname,

catalogs.name AS catalogname

FROM products, catalogs

WHERE products.id_catalog = catalogs.id_catalog

) AS goods

WHERE orders.id user = users.id_user AND

orders.id_product = goods.id_product;

 

name                    productname                          catalogname

Симдянов И.В.   Intel Pentium 4 3.0GHz          Процесори

Корнеев A.A.      Gigabyte GA-8I848P-RS        Материнскі плати

Иванов А.В.       Maxtor 6Y120P0                   Жорсткі диски

Симдянов І.В.     Maxtor 6Y120P0                   Жорсткі диски

Симдянов І.В.     Maxtor 6Y120P0                   Жорсткі диски

 

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

 

SELECT id_catalog, SUM(count) FROM products GROUP BY id_catalog;

id_catalog  SUM(count)

1                 47

2                 27

3                 17


Вкладені запити (Частина друга)

 

Вкладені запити в операторі CREATE TABLE

 

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

У прикладі представлений запит, який створює таблицю new orders, в якій на відміну від таблиці orders замість зовнішніх ключів, що пов'язують таблицю зіншими таблицями (users, products), розміщені стовпці для прізвища (surname), імені (name), по батькові (patronymic) клієнта та назви товарноїпозиції (product).

 

CREATE TABLE new_orders (

id_order INT(11) NOT NULL AUTO INCREMENT,

user TINYTEXT,

ordertime DATETTME not null DEFAULT '0000-00-00 00:00:00',

number INT (11) not null DEFAULT '0',

product, TINYTEXT,

PRIMARY KEY (id_order))

ENGINE=MyISAM DEFAULT CHARSET=cpl251

SELECT orders.id_order AS id_order,

CONCAT(users.surname, " ", SUBSTRING(users.name,1,1), ".",

SUBSTRING(users.patronymic,1,1), ".") AS user,

orders.ordertime AS ordertime,

orders.number AS number,

products.name AS product

FROM orders, users, products

WHERE orders.id_user = users.id_user AND

orders.id_product = products. id_product;

 

SELECT * FROM new_orders;

 

id_order     user                      ordertime                      number       product

1                 Сімідянов И.В.   2005-01-04 10:39:38              1       Intel Pentium 4

2                Корнєєв A.A.               2005-02-10 09:40:29    2       Gigabyte GA

 

Як видно з прикладу для того щоб заповнити тільки що створену таблицюnew_orders, достатньо в кінці оператора create table помістити вкладенийзапит. Стовпцям у вкладеному запиті необхідно призначати псевдоніми за допомогою оператора as. Якщо цього не зробити, запит може завершитисяпомилкою або ж будуть отримані небажані імена в кінцевій таблиці.

 

Зауваження

Даний вид запитів з'явився у версії 3.23, тому синтаксис не вимагає поміщативкладений запит в обов'язкові круглі дужки.

 

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

 

CREATE TABLE new_orders (

id_order INT(11) NOT NULL AUTO_INCREMENT, -> user TINYTEXT,

ordertime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

number INT(11) NOT NULL DEFAULT '0',

product TINYTEXT,

PRIMARY KEY (id_order))

ENGINE=MyISAM DEFAULT CHARSET=cpl251

SELECT orders.id_order AS id order,

CONCAT(users.surname, " ", SUBSTRING(users.name,1,1),

SUBSTRING(users.patronymic,1,1), ".") AS user,

orders.ordertime AS ordertime,

products.name AS product

FROM orders, users, products

WHERE orders.id_user = users.id_user AND

orders.id_product = products.id_product;

 

id_order     user                      ordertime             number       produc

1                 Сімідянов И.В.   2005-01-04 10:39:38    0       Intel Pentium|

2                 Корнєєв A.A.      2005-02-10 09:40:29    0       Gigabyte GA-8I848P

 

У прикладі в структурі таблиці new_order присутній стовпець number, однак у вкладеному запиті, який заповнює щойно створену таблицю, стовпець numberпропущений. Це призводить до того, що він отримує значення за замовчуванням - 0.

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

 

CREATE TABLE new_orders (

id_order INT(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id_order))

ENGINE=MyISAM DEFAULT CHARSET=cpl251

SELECT orders.id_order AS id_order,

CONCAT(users.surname, " ", SUBSTRING(users.name,1,1), ".",

SUBSTRING(users.patronymic,1,1), ".") AS user,

orders.ordertime AS ordertime,

products. name AS product

FROM orders, users, products

WHERE orders.id_user = users.id_user AND

orders.id_produсt = products.id_product;

 

SELECT * FROM new_orders;

 

id_order     user                      ordertime                      product

2                 Корнєєв A.A.      2005-02-10 09:40:29    Gigabyte GA-8I848P

3                 Іванов А.В.                   2001-10-08 13:41:05    Maxtor 6Y120P0

 

Незважаючи на те, що оператор create table визначає один-єдиний стовпецьid_order, створена таблиця new_orders містить чотири стовпці, три з яких з'явилися завдяки присутності в результуючій таблиці вкладеного select запиту.

Вкладений select-запр можна доповнювати операторами ignore або replace, щоб вказати, як слід обробляти записи з дубльованими значеннямиунікальних ключів (у разі їх присутності в таблиці). Якщо вказується ключовеслово ignore, нові записи відкидаються при виявленні в таблиці записів заналогічним значенням унікального ключа. Якщо ж вказано ключове словоreplace, нові записи замінюють собою існуючі. Якщо не вказано ні ignore, ніreplace, то поява записів з дубльованими значеннями ключових полів призводить до помилки.

 

Вкладені запити у операторі INSERT

 

За допомогою вкладеного select-запиту оператор insert дозволяє швидковставляти безліч записів з іншої (інших) таблиць.

 

Зауваження

Даний вид запитів з'явився у версії 3.23, тому синтаксис не вимагає записувативкладений запит в обов'язкові круглі дужки.

 

Представлені в попередньому розділі вкладені запити дозволяли заповнититаблицю new_orders тільки в момент створення. Однак якщо вже після створення таблиці клієнти оформляють угоди, то інформація в таблиціnew_orders втрачає свою актуальність. Виходом із ситуації буде створеннятаблиці new_orders за допомогою звичайного оператора create table(наступний приклад) з подальшим її оновленням.

 

CREATE TABLE neworders (

id_order INT(11) NOT NULL AUTO_INCREMENT,

 user TINYTEXT,

ordertime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',

number INT(11) NOT NULL DEFAULT '0',

product TINYTEXT,

PRIMARY KEY (id order))

ENGINE MyISAM DEFAULT CHARSET=cpl251;

 

У пропозиції from вкладеного запиту може з'являтися ім'я таблиці, в яку проводиться додавання нових записів. Ця можливість з'явилася в СУБДMySQL, починаючи з версії 4.0.14

 

Якщо число стовпців в таблиці, в яку відбувається вставка нових записів, і у вкладеному запиті не збігається і частина стовпців отримує значення заумовчанням або якщо порядок слідування стовпців у вкладеному запиті незбігається з порядком слідування стовпців в цільовій таблиці, можнаскористатися конструкцією values ??оператора insert , яка дозволяє задати іменастовпців і їх порядок при додаванні (приклад).

 

insert into new_orders

values (id_order, ordertime, product, user)

select orders.id_order as id_order,

orders .ordertirne as ordertime,

products.name as product

concat(users.surname, " ", substring(users.name, 1,1), " ",

substring(users.patronymic,1,1), ".") as user

from orders, users, products

where orders.id_user = users.id_user and

orders.id_product = products.id_product;