4-Laboratoriya ishi Mavzu: So’rovlar yordamida taqdimotlarni yaratish(viewlar yaratish). Ishdan maqsad

4-Laboratoriya ishi

Mavzu: So’rovlar yordamida taqdimotlarni yaratish(VIEWlar yaratish).

Ishdan maqsad: SQL da VIEWlar yaratish bo`yicha ko`nikmaga ega bo`lish.

Masalani qo`yilishi: Berilgan predmet soha ma`lumotlar bazasidagi barcha ob`yektlarni ustida VIEWlar yaratish asosida amallarini bajarish.

Uslubiy ko`rsatmalar: Shunday qilib, SQL-dagi VIEWlar odatdagi jadvallardan SELECT so'rovi orqali olingan ma'lumotlarni o'z ichiga olgan maxsus ob'ektdir. Bu virtual jadval bo'lib, unga oddiy jadvallar kabi kirish va saqlangan ma'lumotlarni olish mumkin. SQL ko'rinishida bitta jadval va ikkala jadvalning ikkala ma'lumotlari bo'lishi mumkin.

Ma'lumotlar bazasi bilan ishlashni soddalashtirish va serverning javob vaqtini tezlashtirish uchun vakillar kerak. VIEW allaqachon SELECT-dan foydalanib ba'zi ma'lumotlarni saralash natijasi bo'lganligi sababli, keyingi safar bir nechta jadvallarni so'rashning o'rniga shunchaki yaratilgan ko'rinishga murojaat qilish kifoya. Ushbu ob'ektning ishlashi quyidagi rasm bilan tavsiflanadi:

SQL-da VIEW yaratish

VIEWni yaratish quyidagi buyruq yordamida amalga oshiriladi:
CREATE VIEW info_order

AS SELECT onum, amt, cname

FROM orders, customers

WHERE orders.cnum = customers.cnum;

SELECT-dan foydalanib, ma'lumotlar tanlanadi va VIEWga joylashtiriladi. Yana bir bor, dastlabki jadvallardagi ma'lumotlar o'zgarganda, ular VIEWda o'zgaradi.

SQL-da ko'rinishga kirish

Ko'rinishdagi ma'lumotlarni ko'rish uchun siz avvalgi darslarda o'rganilgan buyruqlardan foydalanishingiz kerak - masalan, SQL-dagi SELECT-dan foydalanish.

SELECT * FROM info_order

Misollar:

Select concat_WS(' ',fam,ism,o_i) as FIO, bal.ballar from abiturent join bal on abiturent.A_id=bal.a_id;

+------------------------------------+--------+

| concat_WS(' ',fam,ism,o_i) | ballar |

+------------------------------------+--------+

| Kamolova Malika Salimovna | 56.0 |

| Olimov Jasur Avazovich | 56.0 |

| Asilov Jasur Daliyevich | 50.0 |

| Olimov Jasur Avazovich | 50.0 |

| Kamolova Malika Salimovna | 56.0 |

| Asilov Farruh Burhanovich | 50.0 |

| Olimov Jasur Avazovich | 56.0 |

| Asilov Jasur Daliyevich | 50.0 |

| Asilov Farruh Burhanovich | 50.0 |

| Kamolova Malika Salimovna | 50.0 |

| Kamolova Malika Salimovna | 56.0 |

| Tojiyev Asil Ergashevich | 50.0 |

| Tojiyev Asil Ergashevich | 56.0 |

| Asilov Farruh Burhanovich | 50.0 |

| Iminov Temur Qobilovich | 56.0 |

| Asilov Farruh Burhanovich | 50.0 |

| Olimov Jasur Avazovich | 56.0 |

+-----------------------------------+--------+

17 rows in set (0.05 sec)
create view Kirgan_talabalar as select concat_WS(' ',fam,ism,o_i) as FIO, bal.ballar from abiturent join bal on abiturent.A_id=bal.a_id where ballar>=56;
mysql> select * from kirgan_talabalar;

+-------------------------------------+--------+

| FIO | ballar |

+-------------------------------------+--------+

| Kamolova Malika Salimovna | 56.0 |

| Olimov Jasur Avazovich | 56.0 |

| Kamolova Malika Salimovna | 56.0 |

| Olimov Jasur Avazovich | 56.0 |

| Kamolova Malika Salimovna | 56.0 |

| Tojiyev Asil Ergashevich | 56.0 |

| Iminov Temur Qobilovich | 56.0 |

| Olimov Jasur Avazovich | 56.0 |

+------------------------------------+--------+

mysql> show tables;

+------------------------+

| Tables_in_imtihon |

+------------------------+

| abiturent |

| bal |

| imtihon |

| kirgan_talabalar |

+------------------------+

VIEW larni o’chirish:

drop view kirmagan_abiturentlar;

Misol 2:

create view Kirmagan_abiturentlar as select concat_WS(' ',fam,ism,o_i) as FIO, ballar from abiturent, bal where abiturent.a_id=bal.a_id having bal.ballar<=55;

Query OK, 0 rows affected (0.15 sec)
mysql> select * from kirmagan_abiturentlar;

+---------------------------+--------+

| FIO | ballar |

+---------------------------+--------+

| Asilov Jasur Daliyevich | 50.0 |

| Olimov Jasur Avazovich | 50.0 |

| Asilov Farruh Burhanovich | 50.0 |

| Asilov Jasur Daliyevich | 50.0 |

| Asilov Farruh Burhanovich | 50.0 |

| Kamolova Malika Salimovna | 50.0 |

| Tojiyev Asil Ergashevich | 50.0 |

| Asilov Farruh Burhanovich | 50.0 |

| Asilov Farruh Burhanovich | 50.0 |

+---------------------------+--------+

9 rows in set (0.00 sec)

Create VIEW bugun_sotildi

AS SELECT nomi.mebel fish.haridor fish hodim

WHERE id.MEBEL=mebel_id.SOTILDI AND sotilgan_sana.SOTILDI=CURDATE()

Bu so’rov orqali har kunlik sotilgan maxsulot nomini, sotgan hodimni, olgan haridorni ko’rishimiz mumkin bo’ladi.

CURDATE() buyrug’ini o’rniga istalgan sanani qo’yish orqali, biz o’sha kungi haridlarni bilib olishimiz mumkin bo’ladi.