1、存储过程:
创建存储过程:
create produre 名称(参数1,参数2)
begin
sql 语句;
end
//
例:存储过程的创建和调用
创建:
mysql> delimiter //
mysql> create procedure selCg()
-> begin
-> select * from category;
-> end //
Query OK, 0 rows affected (0.04 sec)
调用:
mysql> call selCg() //
+---------+---------------+
| bTypeId | bTypeName |
+---------+---------------+
| 1 | windows应用 |
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
| 10 | AutoCAD技术 |
+---------+---------------+
10 rows in set (0.04 sec)
Query OK, 0 rows affected (0.04 sec)
存储过程调用内部参数:
into 赋值;
out 传出参数;
例:内部传参:
创建:mysql> create procedure getonebook(in b int)
-> begin
-> select * from books where bId=b;
-> end
-> //
调用:mysql> call getonebook(3) \G //
*************************** 1. row ***************************
bId: 3
bName: 网络程序与设计-asp
bTypeId: 2
publishing: 北方交通大学出版社
price: 43
pubDate: 2005-02-01
author: 王玥
ISBN: 75053815x
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
存储过程变量的用法
例:mysql> create procedure demo1()
-> begin
-> declare str varchar(200);
-> set str = (select bName from books where bId=12);
-> select str;
-> end//
Query OK, 0 rows affected (0.02 sec)
mysql> call demo1()//
+-------------------------------+
| str |
+-------------------------------+
| Fireworks 4网页图形制作 |
+-------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
************************************触发器*******************************************
语法: create trigger 触发器名称 触发器的时机 触发器的动作
on 表名 for each row 触发器状态
触发的时机: before / after 在执行之前还是之后
触发器的语句类型
insert
update
delete
创建触发器:
mysql> create trigger delCategory after delete on category for each row
-> delete from books where bTypeId='2';
执行之前查看是否有文件存在:
mysql> select * from books where bTypeId='2';
+-----+---------------------------------------------------------+---------+-----------------------------+-------+------------+-----------+-------------+
| bId | bName | bTypeId | publishing | price | pubDate | author | ISBN |
+-----+---------------------------------------------------------+---------+-----------------------------+-------+------------+-----------+-------------+
| 1 | 网站制作直通车 | 2 | 电脑爱好者杂志社 | 34 | 2004-10-01 | 苗壮 | 7505380796 |
删除一条记录看下触发器是否生效
mysql> delete from category where bTypeId='2';
Query OK, 1 row affected (0.00 sec)
查看记录是否为空:
mysql> select bName,bTypeId from books where bTypeId='2';
Empty set (0.00 sec)
删除触发器:
mysql> drop trigger delCategory;
Query OK, 0 rows affected (0.00 sec)
**************************************事物*****************************************
作用:通过将一组操作组成一个,执行时,要么全部成功,要么全部失败的单元。使程序更可靠,简化错误恢复。
开启事务: start transaction;
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
提交当前事务:commit
设置数据库引擎为inondb:
mysql> alter table books engine=innodb;
Query OK, 22 rows affected (0.02 sec)
Records: 22 Duplicates: 0 Warnings: 0
mysql> alter table category engine=innodb;
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
创建事物并提交:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update books set bName="lb" where bId=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select bName from books where bId=1 or bId=2;
+-------+
| bName |
+-------+
| lb |
+-------+
1 row in set (0.00 sec)
现在我们创建事物不提交:
mysql> start transaction ;
update books set bName="bl" where bId=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看是否执行成;
mysql> select bName from books where bId=1 or bId=2;
+-------+
| bName |
+-------+
| bl |
+-------+
1 row in set (0.00 sec)
因为没有提交,我们回滚下,并查看
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select bName from books where bId=1 or bId=2;
+-------+
| bName |
+-------+
| lb |
+-------+
1 row in set (0.00 sec)