Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторна робота №7
Тема: робота з багатотабличними запитами
Мета: навчитися формувати та застосовувати багатотабличні запити SQL.
Теоретичні відомості:
Синтаксис підзапитів
Підзапит - це оператор SELECT усередині іншого оператора.
Починаючи з MySQL 4.1, підтримуються усі форми підзапитів, яких вимагає стандарт SQL.
Приклад:
SELECT * FROM tl WHERE columnl = (SELECT columnl FROM t2);
В даному прикладі SELECT * FROM tl є зовнішнім запитом (чи зовнішнім оператором), a (SELECT columnl FROM t2) - підзапитом. Говорять, що підзапит вкладений в зовнішній запит. Фактично, можна вкладати підзапити в запити на велику глибину. Підзапит завжди повинен братися в дужки.
Основні переваги підзапитів:
дозволяють писати структуровані запити таким чином, що можна ізолювати частини оператора;
представляють альтернативний спосіб виконання операцій, які вимагають застосування складних з'єднань і злиття (JOIN і UNION);
більш читабельні.
Нижче наведений приклад оператора, який демонструє основні поняття про синтаксис підзапитів, підтримуваних MySQL, як того вимагає стандарт SQL:
DELETE FROM tl WHERE sll > ANY
((SELECT COUNT(*) /* без підказок */ FROM t2 WHERE NOT EXISTS
((SELECT * FROM t3 WHERE ROW(5*t2.sl, 77) =
((SELECT 50,11*51 FROM t4 UNION SELECT 50,77 FROM (SELECT * FROM t5) AS t5)));
Підзапит, як скалярний операнд
У своїй простій формі (скалярний підзапит є протилежністю рядковим або табличним підзапитам, описаним нижче), підзапит - це простий операнд. Він має ті ж характеристики, які мають усі операнди: тип даних, довжину, ознаку того, чи може він набувати значення NULL, і так далі. Наприклад:
CREATE TABLE tl (si INT, s2 CHAR(5) NOT NULL);
SELECT (SELECT s2 FROM tl);
Підзапит в цьому запиті має тип даних CHAR, довжину 5, набір символів і порядок зіставлення за замовчуванням такі, як були при виконанні CREATE TABLE, і ознака того, що значення стовпця допускає значення NULL. Фактично майже усі підзапити можуть бути NULL, оскільки якщо таблиця порожня, як в даному прикладі, значення підзапиту буде рівне NULL. Існує декілька обмежень.
Зовнішній оператор підзапиту може бути одним з наступних : SELECT, INSERT, UPDATE, DELETE, SET АБО DO.
Підзапит може містити будь-які ключові слова і конструкції, які допустимі в звичайному операторі SELECT: DISTINCT, GROUP BY, ORDER BY, LIMIT, JOIN, UNION, підказки, коментарі, функції і так далі.
Наприклад, нехай створено дві таблиці:
CREATE TABLE tl (si INT); INSERT INTO tl VALUES (1); CREATE TABLE t2 (si INT); INSERT INTO t2 VALUES (2);
Потім виконується такий запит:
SELECT (SELECT si FROM t2) FROM tl;
Результатом буде 2, тому що існує рядок в t2, що містить стовпець si, який має значення 2.
Підзапит може бути частиною виразу. Якщо це операнд функції, потрібно вказати дужки. Наприклад:
SELECT UPPER((SELECT si FROM tl)) FROM t2;
Порівняння з використанням підзапитів
Найчастіше підзапити застосовуються в такій формі: операнд_не_підзапиту операція_порівняння (підзапит) Тут операція_порівняння - це одна з наступних операцій:
Підзапити з ANY, IN і SOME
Синтаксис:
операнд операція_порівняння ANY (підзапит)
операнд IN (підзапит)
операнд операція_порівняння SOME (підзапит)
Ключове слово ANY, яке повинне йти за операцією порівняння, означає "повернути TRUE, якщо порівняння дає TRUE для будь-якого з рядків, які повертає підзапит". Наприклад:
SELECT si FROM tl WHERE si > ANY (SELECT si FROM t2);
Припустимо, що в таблиці tl є рядок, який містить (10). Вираз істинний, якщо таблиця t2 містить (21,14,7), оскільки в t2 є значення 7, яке менше 10. Вираз помилковий, якщо таблиця t2 містить (20,10), або таблиця t2 порожня. Вираз дорівнює UNKNOWN, якщо таблиця t2 містить значення (NULL, NULL, NULL).
Слово IN - це псевдонім для = ANY, тому наступні два оператори однакові:
SELECT si FROM tl WHERE si = ANY (SELECT si FROM t2);
SELECT si FROM tl WHERE si IN (SELECT si FROM t2);
Слово SOME - це псевдонім для ANY, тому показані нижче два оператори однакові:
SELECT si FROM tl WHERE si <> ANY (SELECT si FROM t2); SELECT si FROM tl WHERE si <> SOME (SELECT si FROM t2);
Підзапити з ALL
Синтаксис:
операнд операція_порівняння ALL (підзапит)
Слово ALL, яке повинне йти за операцією порівняння, означає "повернути TRUE, якщо порівняння дає TRUE для усіх рядків, які повертає підзапит". Наприклад:
SELECT si FROM tl WHERE si > ALL (SELECT si FROM t2);
Припустимо, що в таблиці tl є рядок, який містить (10). Вираз істинний, якщо таблиця t2 містить (-5, 0, +5), тому що 10 більше, ніж усі три значення з таблиці t2. Вираз помилковий, якщо таблиця t2 містить (12, NULL, -100), оскільки значення 12 в таблиці t2 більше 10. Вираз невизначений (UNKNOWN), якщо таблиця t2 містить (0, NULL, 1).
Нарешті, якщо таблиця t2 порожня, результат рівний TRUE:
SELECT * FROM tl WHERE 1 > ALL (SELECT si FROM t2);
Проте наступний оператор поверне UNKNOWN, якщо таблиця t2 порожня:
SELECT * FROM tl WHERE 1 > (SELECT si FROM t2);
І на додаток наступний оператор також поверне UNKNOWN, якщо таблиця t2 порожня:
SELECT * FROM tl WHERE 1 > ALL (SELECT MAX(si) FROM t2);
У загальному випадку, таблиці зі значеннями NULL і порожні таблиці - це крайні випадки. Тому при написанні коду підзапитів завжди потрібно враховувати дві згадані можливості.
Корельовані підзапити
Корельований підзапит - це такий підзапит, який містить посилання на стовпець, який є в зовнішньому запиті. Наприклад:
SELECT * FROM tl WHERE columnl = ANY
((SELECT columnl FROM t2 WHERE t2.column2 = tl.column2);
Зверніть увагу, що підзапит містить посилання на стовпець таблиці tl, навіть не дивлячись на те, що конструкція FROM підзапиту не згадує таблицю tl. Таким чином, MySQL виконує перегляд за межами підзапиту і знаходить tl в зовнішньому запиті.
Правило видимості: MySQL обчислює вирази від внутрішнього до зовнішнього.
EXISTS і NOT EXISTS
Якщо підзапит взагалі повертає які-небудь значення, то EXISTS підзапит повертає TRUE, a NOT EXISTS підзапит - FALSE, наприклад:
SELECT columnl FROM tl WHERE EXISTS (SELECT * FROM t2);
Традиційно підзапит в EXISTS починається з SELECT *, але він може починатися з SELECT 5 або SELECT columnl, або з ще чого-небудь. MySQL ігнорує список SELECT в такому підзапиті, тому це не важливо.
Для попереднього прикладу, якщо t2 містить будь-які рядки, навіть рядки, в яких немає нічого окрім значень NULL, то умова EXISTS істинно. Взагалі це неправдоподібний приклад, оскільки майже завжди підзапит [NOT] EXISTS містить кореляцію. Нижче представлені реалістичніші приклади:
Які типи магазинів є в одному або більше містах?
SELECT DISTINCT store_type FROM Stores
WHERE EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);
Яких типів магазинів немає ні в одному місті?
SELECT DISTINCT store_type FROM Stores
WHERE NOT EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);
Який тип магазинів є в усіх містах?
SELECT DISTINCT store_type FROM Stores SI WHERE NOT EXISTS (
SELECT * FROM Cities WHERE NOT EXISTS ( SELECT * FROM Cities_Stores
WHERE Cities_Stores.city = Cities.city
AND Cities_Stores.store_type = Stores.store_type));
У останньому прикладі представлений двічі вкладений підзапит NOT EXISTS. Тобто, конструкція NOT EXISTS міститься усередині іншої конструкції NOT EXISTS. Формально він відповідає на питання чи "є місто з магазином, якого немає в Stores"?. Але простіше сказати, що вкладений NOT EXISTS відповідає на питання чи "істинне х для усіх у"?.
Підзапити, що повертають рядок
Досі ми обговорювали підзапити, що повертають стовпець (скалярні), тобто підзапити, що повертають єдине значення стовпця. Рядкові підзапити -це варіант підзапитів, які повертають більш за одне значення стовпця. Нижче представлено два приклади:
SELECT * FROM tl WHERE(SELECT columnl, column2 FROM t2);
SELECT * FROM tl WHERE ROW(SELECT columnl, column2 FROM t2);
Обоє ці запити істинні, якщо в таблиці t2 присутній рядок, в якому columnl = 1и column2 = 2.
Вирази (1,2) і ROW(1,2) іноді називають конструктором рядка. Ці два вирази еквівалентні. Вони цілком коректні і в інших контекстах. Наприклад, наступні два оператори семантично еквівалентні (не дивлячись на те, що тільки другою з них може бути оптимізований) :
SELECT * FROM tl WHERE(1,1);
SELECT * FROM tl WHERE columnl = 1 AND column2 = 1;
Як правило, конструктори рядка використовуються для порівняння з підзапитами, що повертають два або більше стовпців. Наприклад, представлений нижче запит виконує наступну директиву: "знайти усі рядки таблиці tl, які є також і в таблиці t2" :
SELECT columnl, column2, column3 FROM tl WHERE (columnl, column2, column3) IN
((SELECT columnl, column2, column3 FROM tl);
Підзапити в конструкції FROM
Підзапити дозволені і в конструкції FROM оператора SELECT. Їх синтаксис виглядає таким чином:
SELECT .. FROM (підзапит) AS ім'я ..
Конструкція AS ім'я є обов'язковим, оскільки кожна таблиця в конструкції FROM повинна мати ім'я. Усі стовпці в списку підзапиту підзапит також повинні мати унікальні імена.
Для того, щоб проілюструвати це, припустимо, що є така таблиця:
CREATE TABLE tl (si INT, s2 CHAR(5), s3 FLOAT);
Так потрібно використовувати підзапити в конструкції from для цього прикладу таблиці :
INSERT INTO tl VALUES (l,’l’, 1.0);
INSERT INTO tl VALUES (2,’2’, 2.0);
SELECT sbl, sb2, sb3 FROM (SELECT si AS sbl, s2 AS sb2, s3*2 AS sb3 FROM tl) AS sb WHERE sbl > 1;
Результат: 2, '2 ', 4.0.
А ось інший приклад. Припустимо, що ви хочете знати середнє значення сум в згрупованій таблиці. Наступний варіант працювати не буде: SELECT AVG(SUM(columnl)) FROM tl GROUP BY columnl; проте приведений нижче запит видасть потрібну інформацію:
SELECT AVG(sum_columnl)FROM (SELECT SUM(columnl) AS sum_columnl FROM tl GROUP BY columnl) AS tl;
Відмітимо, що ім'я стовпця, використовуване в підзапиті (sum_columnl) розпізнається в зовнішньому запиті.
На даний момент підзапити в конструкції FROM не можуть бути корельованими підзапитами.
Підзапит в конструкції FROM буде виконаний (тобто буде створена тимчасова таблиця) навіть для оператора EXPLAIN, тому що запити верхнього рівня потребують інформації про усі таблиці на стадії оптимізації.
Хід роботи
Виконати нижче подані завдання з використанням підзапитів.
Контрольні запитання