11-Tajriba Murakkab so‘rovlar yaratish

11-Tajriba
Murakkab so‘rovlar yaratish
MySQL qism so’rovi - bu boshqa so'rovga kiritilgan so'rov, masalan, SELECT, INSERT, UPDATE yoki DELETE larga. Bundan tashqari, qism so’rov boshqa qism so’rov ichida joylashtirilishi mumkin.
MySQL qism so'rovi ichki so'rov, qism so'rovini o'z ichiga olgan so'rov esa tashqi so'rov deb ataladi. Qism so’rov ifoda bo’lishi mumkin bo’lgan har qanday joyda ishlatilishi mumkin va qavs ichida joylashishi kerak.
SELECT
lastName, firstName
FROM
employees
WHERE
officeCode IN (SELECT
officeCode
FROM
offices
WHERE
country = 'USA');

MySQL qism so’rovlari bilan taqqoslash operatorlarini ishlatish.
=,>,< kabi operatorlarni qism so’rov natijasi yagona qiymatga ega bo’lganda ishlatishingiz mumkin:
SELECT
customerNumber,
checkNumber,
amount
FROM
payments
WHERE
amount = (SELECT MAX(amount) FROM payments);
SELECT
customerNumber,
checkNumber,
amount
FROM
payments
WHERE
amount > (SELECT
AVG(amount)
FROM
payments);
MySQL qism so’rovlari bilan IN va NOT IN operatorlarini ishlatish.
Agar qism so’rov birdan ko’p qiymat qaytarsa, IN yoki NOT IN kabi operatorlardan foydalanishingiz mumkin.

SELECT
customerName
FROM
customers
WHERE
customerNumber NOT IN (SELECT DISTINCT
customerNumber
FROM
orders);
MySQL qism so’rovlari bilan FROM iborasi bilan.
FROM bandida qism so’rovdan foydalanganda, qism so’rovdan qaytarilgan natija to'plami vaqtinchalik jadval sifatida ishlatiladi. Ushbu jadval olingan jadval yoki moddiy qism so’rov deb nomlanadi.
SELECT
MAX(items),
MIN(items),
FLOOR(AVG(items))
FROM
(SELECT
orderNumber, COUNT(orderNumber) AS items
FROM
orderdetails
GROUP BY orderNumber) AS lineitems;
O'zaro bog'liq qism so'rovlar
Qism so’rov tashqi so’rovdagi jadvalga murojaat qilsa o’zaroq bog’liq qism so’rov hisoblanadi. Misol uchun:
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
MySQL qism so’rovi EXISTS va NOT EXISTS lar bilan ishlatish.
EXISTS yoki NOT EXISTS operatori bilan qism so'rov ishlatilganda, qism so'rov TRUE yoki FALSE mantiqiy qiymatini qaytaradi. Quyidagi so'rovda EXISTS operatorida ishlatiladigan qism so’rov tasvirlangan:
SELECT
*
FROM
table_name
WHERE
EXISTS( subquery );
Yuqoridagi so'rovda, agar qism so'rov biron bir qatorni qaytarsa, EXISTS qism so'rovi TRUE qaytaradi, aks holda FALSE.


Shu jadvallarni tanladik.