Mavzu: Murakkab so‘rovlar yaratish. Sql tilida index yaratish. Tasavvur, view yaratish va undan foydalanish

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)