Laboratoriya ishi Mavzu: sql da funksiyalar yaratish. Ishdan maqsad

21-22-23.Laboratoriya ishi

Mavzu: SQL da funksiyalar yaratish.

Ishdan maqsad: SQL da funksiyalar 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: Funksiyalar (FUNCTION) - protseduraga o'xshash, bu biz

qayta-qayta ishlatmoqchi bo'lgan ba'zi SQL kod. Funktsiya va protsedura o'rtasidagi

farq shundan iboratki, funktsiya faqat bitta parametrni qaytaradi va funktsiya "Call"

qilmasdan nomi bilan chaqiriladi. Shuning uchun funktsiya SQL so'rovlarida

ishlatilishi mumkin.

1.

NATIJA:



2.

PLUS FUNKSIYASI

MINUS FUNKSIYASI

BO’LISH FUNKSIYASI



KO’PAYTMA FUNKSIYASI

3.

24-25-26.Laboratoriya ishi

Mavzu: SQL triggerlar yaratish









2)



mysql> INSERT INTO kitobxon_bollar SET

-> Familiyasi='Dadajonova',Ism='Busharofat',O_i='Abdusamatovna',pass='13052002',login='2777';

-> //

Query OK, 1 row affected (0.15 sec)



3)



mysql> DELIMITER ;

mysql> UPDATE kitobxon_bollar SET

-> Familiyasi='Alijonov',Ism='Shoxrux',O_i='Sherzod',pass='12345678',login='2002';

Query OK, 1 row affected (0.13 sec)

Rows matched: 1 Changed: 1 Warnings: 0



4)

mysql> SHOW TRIGGERS\G;

*************************** 1. row ***************************

Trigger: Kitob

Event: INSERT

Table: kitobxon_bollar

Statement: BEGIN

SET NEW.Familiyasi = LEFT(NEW.Familiyasi,1);

SET NEW.Ism = LEFT(NEW.Ism,1);

SET NEW.O_i = LEFT(NEW.O_i,1);

SET NEW.pass= LEFT(NEW.pass,1);

SET NEW.login = LEFT(NEW.login,1);

END

Timing: BEFORE

Created: NULL

sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Definer: root@localhost

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: latin1_swedish_ci

*************************** 2. row ***************************

Trigger: kitobxon_bollar2

Event: UPDATE

Table: kitobxon_bollar

Statement: BEGIN

SET NEW.Ism = LEFT(NEW.Ism,1);

SET NEW.Familiyasi = LEFT(NEW.Familiyasi,1);

SET NEW.O_i = LEFT(NEW.O_i,1);

SET NEW.pass= LEFT(NEW.pass,1);

SET NEW.login = LEFT(NEW.login,1);

END

Timing: BEFORE

Created: NULL

sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Definer: root@localhost

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: latin1_swedish_ci

*************************** 3. row ***************************

Trigger: kitobxon_vaqti

Event: INSERT

Table: kitobxonlar

Statement: BEGIN

SET NEW.olingan_kitob_sanasi = NOW();

END

Timing: BEFORE

Created: NULL

sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Definer: root@localhost

character_set_client: latin1

collation_connection: latin1_swedish_ci

Database Collation: latin1_swedish_ci

3 rows in set (0.03 sec)

Triggerni o’chirish

mysql> DROP TRIGGER kitobxon_bollar2;

Query OK, 0 rows affected (0.21 sec)