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.
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)
Topshiriq: Har bir talaba o’ziga berilgan predmet soha mavzusi bo’yicha
VIEWlar yaratishi va natijasi bilan ko’rsatib o’tishi kerak.
Do'stlaringiz bilan baham: |