17-dars. Views

View nima?

Oddiy qilib:

View — oldindan yozib qo‘yilgan SELECT query'ga berilgan nom.

Masalan, biz doim:

SELECT
    CustomerID,
    FirstName,
    Country
FROM Customers
WHERE Country = 'USA';

query'sini ishlatamiz. Uni View qilib saqlashimiz mumkin:

CREATE VIEW vw_USACustomers
AS
SELECT
    CustomerID,
    FirstName,
    Country
FROM Customers
WHERE Country = 'USA';

Keyinchalik:

SELECT *
FROM vw_USACustomers;

deb foydalanamiz.

View nega kerak?

Tasavvur qiling, kompaniyada quyidagi query tez-tez ishlatiladi:

SELECT
    c.CustomerID,
    c.FirstName,
    c.Country,
    o.OrderID,
    o.Sales
FROM Customers c
JOIN Orders o
    ON c.CustomerID = o.CustomerID;

Agar bu query'ni har safar boshidan yozsak, vaqt ketadi.

Buning o‘rniga:

CREATE VIEW vw_CustomerOrders
AS
SELECT
    c.CustomerID,
    c.FirstName,
    c.Country,
    o.OrderID,
    o.Sales
FROM Customers c
JOIN Orders o
    ON c.CustomerID = o.CustomerID;

Keyinchalik:

SELECT *
FROM vw_CustomerOrders;

Demak:

View ko‘p ishlatiladigan query'ni qayta-qayta yozmaslikka yordam beradi.

Microsoft ham View'larni ma'lumotni soddalashtirish va foydalanuvchiga kerakli ko‘rinishda taqdim etish uchun ishlatishni ko‘rsatadi. (Microsoft Learn)

View — Table emas

Table

TABLE
 ↓
Ma'lumotlar saqlanadi

View

VIEW
 ↓
SELECT query saqlanadi
 ↓
Natija table ko‘rinishida olinadi

Birinchi View'ni yaratamiz

Vazifa:

Faqat Delivered orderlarni ko‘rsatadigan View yarating.

Oddiy query:

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Sales
FROM Orders
WHERE Status = 'Delivered';

Bu query yaxshi ishlayapti.

Endi uni View qilamiz:

CREATE VIEW vw_DeliveredOrders
AS
SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Sales
FROM Orders
WHERE Status = 'Delivered';
GO

Endi View'dan foydalanamiz:

SELECT *
FROM vw_DeliveredOrders;

Mana shu joyda o‘quvchiga asosiy fikrni bering:

Biz yangi ma'lumot yaratmadik. Mavjud query'ga vw_DeliveredOrders degan nom berdik.

View'dan foydalanib yana filter qilish

Endi:

USA customerlarning orderlarini ko‘rsat.

SELECT *
FROM vw_CustomerOrders
WHERE Country = 'USA';

Yoki:

1000 dan katta orderlarni top.

SELECT *
FROM vw_CustomerOrders
WHERE Sales > 1000;

Bu yerda muhim fikr:

View
 ↓
tayyor query natijasi
 ↓
SELECT
 ↓
yana WHERE / ORDER BY / GROUP BY ...

Ya'ni View'ni oddiy table'ga o‘xshab query qilishimiz mumkin.

View bilan amaliyotlar

ALTER VIEW

Agar View'ni yaratib bo‘lgandan keyin uning query'sini o‘zgartirmoqchi bo‘lsak, ALTER VIEW ishlatamiz.

Masalan, vw_DeliveredOrders View'iga Status ustunini ham qo‘shmoqchimiz.

ALTER VIEW vw_DeliveredOrders
AS
SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Sales,
    Status
FROM Orders
WHERE Status = 'Delivered';
GO

Keyin:

SELECT * FROM vw_DeliveredOrders;

Muhim:

CREATE VIEW
→ yangi View yaratadi

ALTER VIEW
→ mavjud View'ni o‘zgartiradi

DROP VIEW

View endi kerak bo‘lmasa:

DROP VIEW vw_DeliveredOrders;

Keyin:

SELECT * FROM vw_DeliveredOrders;

desak, View mavjud bo‘lmagani uchun xatolik chiqadi.

Eslab qolish:

CREATE → yaratish
ALTER  → o‘zgartirish
DROP   → o‘chirish

View'ni qachon ishlatamiz?

1. Query tez-tez ishlatilsa

Masalan:

Customer + Orders

haqidagi query'ni doim yozish kerak bo‘lsa.

2. Query murakkab bo‘lsa

Masalan:

JOIN
 ↓
GROUP BY
 ↓
SUM
 ↓
CASE
 ↓
filter

kabi query'ni View qilib nomlash qulay.

3. Foydalanuvchiga faqat kerakli ma'lumotni ko‘rsatish

Masalan, asosiy table'da:

CustomerID
FirstName
LastName
Phone
Email
Address
Salary
...

bo‘lishi mumkin.

Lekin boshqa xodimga faqat:

CustomerID
FirstName
Country

kerak bo‘lsa, shu ustunlarni View orqali berish mumkin.

4. Hisobotlar uchun

Masalan:

vw_CustomerSales
vw_MonthlySales
vw_ProductPerformance
vw_EmployeePerformance

kabi View'lar yaratish mumkin.

Keyinchalik Power BI, Excel yoki boshqa reporting vositalari shu View'lardan foydalanishi mumkin.

View va CTE farqi

View va Table farqi

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli:

⬇️ Uyga vazifa .sql fayli: