Shulou(Shulou.com)06/01 Report--

Triggers and stored procedures

1. Trigger

During dml operations (insert,update,delete), events can be listened for and responded to, a mechanism called triggers in the database.

Triggers are associated with permanent tables and can only be built on top of permanent tables, not temporary tables or view, and there are only three insert,update,delete events.

Mysql-u root-paixocm

Show triggers

Show triggers\ G

Mysql > delimiter / /

= change the Terminator

Automatically generate temporary new and old tables in memory, and destroy them automatically after the trigger is executed.

The data from the insert operation is placed in the new table

Data deleted by the delete operation is placed in the old table

The update operation is placed in the old table with the new data, and the updated data is placed in the new table.

The old table is read-only, while the new table can be reassigned in the trigger program

Timing of trigger:

Before: the statement to run before performing the operation

After: the statement to run after the operation is performed

Example: create a trigger that is set to 0 when the entered student score is less than 0 and to 100 when it is greater than 100

Mysql > create trigger tr_stu_bf

-> before insert on stu

-> for each row

-> begin

-> if new.score

< 0 then ->

Set new.score=0

-> elseif new.score > 100 then

-> set new.score=100

-> end if

-> end

-> / /

Example: when the student's score is greater than or equal to 90, write the student's id and name into the yxstu table

Mysql > create trigger tr_ins_af

-> after insert on stu

-> for each row

-> begin

-> if new.score > = 90 then

-> insert into yxstu values (new.id,new.name)

-> end if

-> end

-> / /

Exercise: create a new sales table sales, including four fields: commodity id, commodity name, commodity type (code), and sales quantity; create a new inventory table store, including commodity id

The four fields of commodity name and inventory quantity are required to add four pieces of data to the inventory table, and the inventory quantity is 100; then for each item sold, the corresponding commodity

Inventory reduces the sales of goods; when the records in the sales table are deleted, the corresponding records in the inventory table are also deleted.

Inventory table

1 Huawei mobile phone p001 100

2 Xiaomi mobile phone p002 100

3 ZTE mobile phone p003 100

4 vivi mobile phone p004 100

After delete on sales

For each row

Begin

Delete from store where store_number=old.sales_number

End

/ /

Exercise: create a student score sheet score, including recording ID, student number (S001), student name, math, Chinese, and English scores; new students

Situation table scond, including record ID, student number (S001), student name, student performance four fields, each insert a score sheet record

Insert the corresponding information into the student situation table. If the student's average score in three courses is greater than or equal to 90, the student's performance field is "excellent", such as

Results the average score of the three courses is > 70 and create table score (id int primary key,sno varchar (6), sname varchar (20), math float (4mem1), chinese float (4mem1), english float (4mem1))

Mysql > create table scond (id int primary key,sno varchar (6), sname varchar (20), perform varchar (10))

Mysql > create trigger tr_sc_ins_af

-> after insert on score

-> for each row

-> begin

-> declare ascore float (4pl)

-> set ascore= (select (new.math+new.chinese+new.english) / 3 from score limit 1)

-> if ascore > = 90 then

-> insert into scond values (new.id,new.sno,new.sname,' excellent')

-> elseif ascore > = 70 & & ascore

< 90 then ->

Insert into scond values (new.id,new.sno,new.sname,' is good')

-> elseif ascore > = 60 & & ascore

< 70 then ->

Insert into scond values (new.id,new.sno,new.sname,' pass')

-> elseif ascore insert into scond values (new.id,new.sno,new.sname,' is not qualified')

-> end if

-> end

-> / /

Exercise: if you delete a student record in the score table, delete the corresponding student record in the scond table as well.

After delete on score

For each row

Begin

Delete from store where id=old.id

End

/ /

Exercise: when updating the math, Chinese and English scores in the score table, check whether it is reasonable. If the score is below 0, it will be 0 if the score is higher than 100.

Mysql > create trigger

-> before update on score

-> for each row

-> begin

-> if new.chinese

< 0 then ->

Set new.chinese=0

-> elseif new.chinese > 100 then

-> set new.chinese=100

-> elseif new.math

< 0 then ->

Set new.math=0

-> elseif new.math > 100 then

-> set new.math=100

-> elseif new.english

< 0 then ->

Set new.englisn=0

-> elseif new.english > 100 then

-> set new.english=100

-> end if

-> end

-> / /

View trigger information

Mysql > show triggers\ G

Mysql > select * from information_schema.triggers\ G

Delete trigger

Mysql > drop trigger tg_sc_up_bf

two。 Stored procedure procedure

Mysql > show procedure status

Variables:

Global variable: starts with @, such as @ var1, and sets the method to set @ var1 = 1000 position select @ var1: = 'HelloJournal testworthy'

System variable

System variables are referenced using @ @

Local variables:

Local variables are used in begin...end statements, declaring local variables

Delare var1 int

Set var1=100

Example: stored procedure example

Mysql > create procedure sp1 ()

-> begin

-> declare var1 int

-> declare var2 int default 0; = default== assigns initial value

-> declare var3 varchar (20) charset utf8 default 'Hunan University of Technology'

-> set var1=10000

-> set var2=111

-> select var1,var2,var3

-> end

-> / /

1. Use stored procedures to pass parameters

In input parameters

Out outgoing parameters

Inout incoming and outgoing parameters

Outgoing parameters:

Example: use stored procedures to count the number of records in a specified table, and the number of records can be used outside

Mysql > create procedure sp2 (out num int)

-> begin

-> select count (*) into num from employees

-> end

-> / /

Call sp2 (@ var1) = variable name can be different

Pass in parameters:

Example: use stored procedures to create a users table that contains three fields: id,name and sex

Mysql > create procedure sp3 (id int,name varchar (20), sex enum ('man','woman')) = in can be omitted

-> begin

-> create table if not exists users

-> (

-> id int primary key

-> name varchar (20)

-> sex enum ('man','woman')

->) engine=innodb charset=utf8

-> insert into users values (id,name,sex)

-> select * from users

-> end

-> / /

Mysql > call sp3 (1) 'Zhang San', 'male')

Incoming and outgoing parameters:

Mysql > create procedure sp3 (inout va int)

-> begin

-> set va: = va + 10

-> set va=va+10

-> select va+10 into va

-> end

-> / /

Mysql > set @ var1=100

Mysql > call sp4 (@ var1)

Mysql > select @ var1

Stored value method:

Select.... Into var | @ var

Select @ var: = 100

Select emp_no,first_name into var1,var2 from employees\\ must use declare

Make a declaration

Select emp_no,first_name into @ var1,@var2 from employees

Select @ var1: = emp_no,@var2: = first_name from employees

2. Mysql programming

(1) loop cycle (infinite loop)

Label:loop

Cyclic body

If exit condition

Leave label

End if

End loop

Example: use stored procedures to calculate the sum of specified numbers starting at 1

Mysql > create procedure sp4 (in snum int)

-> begin

-> declare sum,i int

-> set sum=0,i=0

-> sxjy:loop

-> if i set sum: = sum + I

-> set I: = I + 1

-> else

-> leave sxjy

-> end if

-> end loop

-> select sum

-> end

-> / /

(2) while cycle

Leave jumps out of cycle

Iterate skips this cycle

Label:while conditional do

Cyclic body

End while

Mysql > create procedure sp5 (in snum int)

-> begin

-> declare sum,i int

-> set sum=0,i=0

-> while i set sum: = sum + I

-> set I: = I + 1

-> end while

-> select sum

-> end

-> / /

Exercise: create a new test1 table under the test database, including

Id integer, primary key

Num1 integers, random numbers from 1 to 200

Dt datetime type, which is the current system date time

Then insert 200 records using a while loop

Mysql > create procedure sp6 () begin declare iMagazine rand int declare time datetime set iMag0 dandy select ceil (200*RAND () + 1) limit 1), time= (select NOW () linit 1); while i create procedure sp6 (in snum int)

-> begin

-> declare sum,i int

-> set sum=0,i=0

-> repeat

-> set sum: = sum + I

-> set I: = I + 1

-> until I > snum

-> end repeat

-> select sum

-> end

-> / /

(4) case statement

Case Operand

When conditional then execution statement

When conditional then execution statement

When conditional then execution statement

Else execution statement

End case

Example: use stored procedures to add students, insert students into C01 class when their id is divided by 3 0, insert C02 class at remaining 1, and insert c03 at remaining 2

Class, the field of each class is id,name,age,sex four fields.

Mysql > create procedure sp6 (in id int,in name varchar (20), in age int,in sex enum ('man','woman'))

-> begin

-> declare num int

-> set num=mod (id,3)

-> case num

-> when 0 then insert into C01 values (id,name,age,sex)

-> when 1 then insert into CO2 values (id,name,age,sex)

-> when 2 then insert into c03 values (id,name,age,sex)

-> else

> insert into C01 values (id,name,age,sex)

-> end case

-> end

-> / /

Select case age

When 20 then statement

When 30 then statement

....

Else

Statement

End case from table name

Select case

When age > = 20 & & age

< 25 then 语句; when age >

= 25 & & age

< 30 then 语句; .... else 语句; end case from 表名; 练习:将employees数据库中,员工的入职时间在1985年到1990年的加薪15%向上取整,入职时间 在1990年到1995年的加薪10%向上取整,入职时间在1995年到2000年的加薪5%向上取整, 3、mysql备份和恢复 mysqldump备份工具 备份数据库下的表 # mysqldump -u root -paixocm --opt test c01 c02 c03 >

/ employees.sql

# mysql-u root-paixocm-- database test

< employees.sql 备份指定数据库 # mysqldump -u root -paixocm --opt --database test >

/ employees.sql

# mysql-u root-paixocm

< employees.sql 备份所有数据库 # mysqldump -u root -paixocm --opt --all-databases >

/ employees.sql

Lock and unlock all tables

Mysql > FLUSH TABLES WITH READ LOCK

Mysql > unlock tables

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.