О‘zbеkistоn rеspublikаsi raqamli texnologiyalar vаzirligi muhаmmаd аl-xоrаzmiy nоmidаgi tоshkеnt аxbоrоt tеxnоlоgiyаlаri univеrsitеti

Tasavvur, VIEWlar yaratish va undan foydalanish.

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.


Create view
mysql> create view info_samalyot
-> as select parvozlar.samalyot, samalyotlar.orindiqlar_soni, samalyotlar.yuk_hajmi
-> from samalyotlar, parvozlar
-> where samalyotlar.sam_id = parvozlar.sam_id
-> ;
Query OK, 0 rows affected (0.16 sec)
mysql> show full tables;

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

| Tables_in_aeroport | Table_type |
+----------------------------+------------+
| aeroport_hodimlari | BASE TABLE |
| chipta_savdosi_bayonnomasi | BASE TABLE |
| info_samalyot | VIEW |
| parvozlar | BASE TABLE |
| samalyot_ekipajlari | BASE TABLE |
| samalyotlar | BASE TABLE |
+----------------------------+------------+
6 rows in set (0.00 sec)

Use view
mysql> select * from info_samalyot;
+----------+-----------------+-----------+
| samalyot | orindiqlar_soni | yuk_hajmi |
+----------+-----------------+-----------+
| AB_73 | 150 | Ko`p |
| AB_69 | 180 | Ko`p |
| AZ_68 | 200 | Ko`p |
| AD_56 | 160 | Ko`p |
| AB_71 | 140 | Kam |
+----------+-----------------+-----------+
5 rows in set (0.01 sec)
mysql>

mysql> create view murakkab

-> as
-> select samalyotlar.sam_id, samalyotlar.ishlab_chiqarish_yili, parvozlar.samalyot, parvozlar.chiqish
-> from samalyotlar
-> join parvozlar on samalyotlar.sam_id = parvozlar.sam_id;
Query OK, 0 rows affected (0.08 sec)
mysql> show full tables;

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

| Tables_in_aeroport | Table_type |
+----------------------------+------------+
| aeroport_hodimlari | BASE TABLE |
| chipta_savdosi_bayonnomasi | BASE TABLE |
| info_samalyot | VIEW |
| murakkab | VIEW |
| parvozlar | BASE TABLE |
| samalyot_ekipajlari | BASE TABLE |
| samalyotlar | BASE TABLE |
+----------------------------+------------+
7 rows in set (0.00 sec)

mysql> select * from murakkab

-> ;
+--------+-----------------------+----------+------------+
| sam_id | ishlab_chiqarish_yili | samalyot | chiqish |
+--------+-----------------------+----------+------------+
| 1 | 2000 | AB_73 | 01.02.2002 |
| 2 | 2000 | AB_69 | 04.03.2005 |
| 3 | 2000 | AZ_68 | 01.05.2006 |
| 4 | 2000 | AD_56 | 01.04.2007 |
| 5 | 2000 | AB_71 | 07.03.2021 |
+--------+-----------------------+----------+------------+
5 rows in set (0.00 sec)