query X UNION ( query Y UNION query Z );
ko‘rsatishingiz mumkin. CHunki UNION bitta dublikatlarni yo‘qotib boshqasini qoldirishi mumkin. Quyidagi ikki ifoda
( query X UNION ALL query Y )UNION query Z;
query X UNION ALL( query Y UNION query Z );
bir xil natija qaytarishi shart emas, agar ikkilangan satrlar unda o‘chirilgan bo‘lsa.
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>
|
