mysql 视图触发器_mysql-函数，视图，触发器

存储过程函数

-- 设置数据库的结束符为 ///

mysql> delimiter ///

-- 创建过程函数test

mysql> create procedure test(a int)

-> begin

-> select * from student where id=a;

-> end

-> ///

Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

--调用函数 test

mysql> call test(2);

+----+------+-----+-----+

| id | name | age | sex |

+----+------+-----+-----+

| 2 | lisi | 31 | 0 |

+----+------+-----+-----+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- 查看函数 test 的状态信息

mysql> show procedure status like 'test' \G

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

Db: shop

Name: test

Type: PROCEDURE

Definer: root@localhost

Modified: 2018-04-24 12:42:43

Created: 2018-04-24 12:42:43

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

-- 删除函数 test

mysql> drop procedure test;

视图

-- 创建视图

mysql> create view test_view(name,age) as select name,age from student where id =5;

Query OK, 0 rows affected (0.00 sec)

-- 视图调用

mysql> select * from test_view;

+--------+-----+

| name | age |

+--------+-----+

| tianqi | 42 |

+--------+-----+

1 row in set (0.01 sec)

-- 查看视图的创建过程

mysql> show create view test_view \G

-- 删除视图

mysql> drop view test_view;

触发器

-- artcle表结构，article表开始是空的

mysql> desc article;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| title | varchar(20) | NO | | NULL | |

+-------+------------------+------+-----+---------+----------------+

mysql> desc total_num;

+-------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| type | tinyint(3) unsigned | NO | | NULL | |

| num | int(10) unsigned | NO | | NULL | |

+-------+---------------------+------+-----+---------+----------------+

-- type=1代表文章数 type=2代表用户的数量

mysql> select * from total_num;

+----+------+-----+

| id | type | num |

+----+------+-----+

| 1 | 1 | 0 |

| 2 | 2 | 0 |

+----+------+-----+

创建触发器，向article表中每增加一条记录，total_num表中的type=1对应num值就加1

mysql> delimiter //

mysql> create trigger add_total_num after insert on article for each row

-> begin

-> update total_num set num=num+1 where type=1;

-> end

-> //

创建触发器，向article表中每删除一条记录，total_num表中的type=1对应num值就减1

mysql> create trigger delete_total_num after delete on article for each row

-> begin

-> update total_num set num=num-1 where type=1;

-> end

-> //

向article表中插入2条数据，再查看total_num表中的变化

mysql> insert into article(title)values('title1'),('title2');

-> //

向article表中删除一条记录时，查看total_num表中的变化

mysql> delete from article where id=1;

-> //

触发器语法

create trigger trigger_name after|before insert|update|delete on table_name for each row

begin

sql语句

end;