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

Mavzu: Murakkab so‘rovlar yaratish. SQL tilida INDEX yaratish. Tasavvur, VIEW yaratish va undan foydalanish. SQL da funksiyalar yaratish. SQL da triggerlar yaratish

Ishdan maqsad: Murakkab so’rovlar yaratish bo`yicha ko`nikmaga ega bo`lish.

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

Uslubiy ko`rsatmalar: Murakkab SQL so'rovlari bu oddiy so'rovlarning kombinatsiyasi. Amalga oshirilganda oddiy so'rovlar jadvallariga guruhlangan ma'lumotlar to'plamlarini qaytaradi.

mysql> select samalyot, chiqish_joyi, borar_joyi

-> from parvozlar
-> where sam_id = (select sam_id from samalyotlar
-> where yuk_hajmi = 'Kam');
+----------+--------------+------------+
| samalyot | chiqish_joyi | borar_joyi |
+----------+--------------+------------+
| AB_71 | uzbekiston | italiya |
+----------+--------------+------------+
1 row in set (0.00 sec)

mysql>

mysql> select samalyot, chiqish_joyi, borar_joyi

-> from parvozlar
-> where sam_id = some(select sam_id from samalyotlar
-> where yuk_hajmi = 'Ko`p');
+----------+--------------+------------+
| samalyot | chiqish_joyi | borar_joyi |
+----------+--------------+------------+
| AB_73 | rossiya | parij |
| AB_69 | uzbekiston | dubay |
| AZ_68 | xitoy | tatariston |
| AD_56 | qoqon | uzbekiston |
+----------+--------------+------------+
4 rows in set (0.00 sec)

mysql> select samalyot, chiqish_joyi, borar_joyi

-> from parvozlar
-> where sam_id != all(select sam_id from samalyotlar
-> where orindiqlar_soni in(140, 150));
+----------+--------------+------------+
| samalyot | chiqish_joyi | borar_joyi |
+----------+--------------+------------+
| AB_69 | uzbekiston | dubay |
| AZ_68 | xitoy | tatariston |
| AD_56 | qoqon | uzbekiston |
+----------+--------------+------------+
3 rows in set (0.00 sec)

mysql>

mysql> select samalyotlar.sam_id, samalyotlar.ishlab_chiqarish_yili, parvozlar.samalyot, parvozlar.chiqish

-> from samalyotlar
-> join parvozlar on samalyotlar.sam_id = parvozlar.sam_id;
+--------+-----------------------+----------+------------+
| 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)

19 – laboratoriya ishi

Mavzu: SQL da INDEXlar yaratish.

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

Masalani qo`yilishi: Berilgan predmet soha ma`lumotlar bazasidagi barcha ob`yektlarni ustida INDEXlar yaratish asosida amallarini bajarish.
Uslubiy ko`rsatmalar: CREATE INDEX ko'rsatmasi jadvallarda indekslarni yaratish uchun ishlatiladi. Indekslar ma'lumotlar bazasidan ma'lumotlarni tezda olish uchun ishlatiladi. Foydalanuvchilar indekslarni ko'ra olmaydilar, ular oddiygina qidirish / so'rovlarni tezlashtirish uchun ishlatiladi.

mysql> create index sam_ind on parvozlar(samalyot);

Query OK, 5 rows affected (0.27 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> create index parvoz on parvozlar(samalyot, chiqish);

Query OK, 5 rows affected (0.25 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> create index mar_index on parvozlar(marshrut);

Query OK, 5 rows affected (0.19 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> show index from parvozlar;

+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| parvozlar | 1 | sam_ind | 1 | samalyot | A | 2 | NULL | NULL | YES | BTREE | |
| parvozlar | 1 | parvoz | 1 | samalyot | A | 2 | NULL | NULL | YES | BTREE | |
| parvozlar | 1 | parvoz | 2 | chiqish | A | 2 | NULL | NULL | YES | BTREE | |
| parvozlar | 1 | mar_index | 1 | marshrut | A | 2 | NULL | NULL | YES | BTREE | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.06 sec)

mysql> alter table parvozlar drop index sam_ind;

Query OK, 5 rows affected (0.27 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> show index from parvozlar;

+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| parvozlar | 1 | parvoz | 1 | samalyot | A | 2 | NULL | NULL | YES | BTREE | |
| parvozlar | 1 | parvoz | 2 | chiqish | A | 2 | NULL | NULL | YES | BTREE | |
| parvozlar | 1 | mar_index | 1 | marshrut | A | 2 | NULL | NULL | YES | BTREE | |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql>

20 – laboratoriya ishi

Mavzu: 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.