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 |
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 |