SQL da View yaratish va ishlatish

View — saqlangan so'rov bo'lib, virtual jadval sifatida ishlatiladi.

View yaratish

CREATE VIEW faol_talabalar AS
SELECT 
    t.id,
    t.ism,
    t.email,
    k.kurs_nomi,
    AVG(b.baho) AS ortacha_baho
FROM talabalar t
JOIN yozilishlar y ON t.id = y.talaba_id
JOIN kurslar k ON y.kurs_id = k.id
LEFT JOIN baholar b ON y.id = b.yozilish_id
WHERE y.holat = 'faol'
GROUP BY t.id, t.ism, t.email, k.kurs_nomi;

View dan foydalanish

-- Oddiy jadval kabi
SELECT * FROM faol_talabalar;
SELECT * FROM faol_talabalar WHERE ortacha_baho > 80;

View ni yangilash

CREATE OR REPLACE VIEW faol_talabalar AS
SELECT ...;   -- yangi so'rov

Materialized View (PostgreSQL)

Natijalar diskda saqlanadi — tezroq ishlaydi:

CREATE MATERIALIZED VIEW oylik_hisobot AS
SELECT 
    DATE_TRUNC('month', sana) AS oy,
    COUNT(*) AS buyurtmalar,
    SUM(summa) AS jami
FROM buyurtmalar
GROUP BY DATE_TRUNC('month', sana);

-- Yangilash
REFRESH MATERIALIZED VIEW oylik_hisobot;

View ni o'chirish

DROP VIEW IF EXISTS faol_talabalar;
DROP MATERIALIZED VIEW IF EXISTS oylik_hisobot;