MySQL快速入门12----删除数据 & 备份还原 & 视图 & 事务 & 触发器
来源:互联网 发布:windows 获取最高权限 编辑:程序博客网 时间:2024/06/06 02:40
作者:qianqin_2014-----感谢原创,感谢作者!!
一 删除数据
Delete
指定删除的最多记录数。Limit
可以通过排序条件删除。Order by + limit
支持多表删除,使用类似连接语法。
删除重建表
区别:
1,truncate 是删除表再创建,delete 是逐条删除
2,truncate 重置auto_increment的值。而delete不会
3,truncate不知道删除了几条,而delete知道。
DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]按照条件删除
指定删除的最多记录数。Limit
可以通过排序条件删除。Order by + limit
支持多表删除,使用类似连接语法。
Delete from 需要删除数据多表1,表2 using 表连接操作 条件。
Truncate,TRUNCATE [TABLE] tbl_name清空数据
删除重建表
区别:
1,truncate 是删除表再创建,delete 是逐条删除
2,truncate 重置auto_increment的值。而delete不会
3,truncate不知道删除了几条,而delete知道。
1.1 允许使用limit条件
限制删除的记录数, limit n;
test_1表中的数据内容:
mysql> select * from test_1;+----+-------+--------+| id | name | sex |+----+-------+--------+| 1 | Apple | male || 2 | Green | male || 3 | Qian | secret || 4 | Apple | male || 5 | Lin | female || 6 | Qian | secret || 7 | Apple | male || 8 | Green | male || 9 | Qian | secret || 10 | Apple | male || 11 | Lin | female || 12 | Qian | secret |+----+-------+--------+12 rows in set (0.02 sec)
mysql> delete from test_1 limit 2;Query OK, 2 rows affected (0.03 sec)
mysql> select * from test_1;+----+-------+--------+| id | name | sex |+----+-------+--------+| 3 | Qian | secret || 4 | Apple | male || 5 | Lin | female || 6 | Qian | secret || 7 | Apple | male || 8 | Green | male || 9 | Qian | secret || 10 | Apple | male || 11 | Lin | female || 12 | Qian | secret |+----+-------+--------+10 rows in set (0.00 sec)
1.2 order by 和 limit结合
先将结果进行排序,再删除固定数量的记录。
删除id值最高的三个数:
mysql> delete from test_1 order by id desc limit 3;Query OK, 3 rows affected (0.02 sec)mysql> select * from test_1;+----+-------+--------+| id | name | sex |+----+-------+--------+| 3 | Qian | secret || 4 | Apple | male || 5 | Lin | female || 6 | Qian | secret || 7 | Apple | male || 8 | Green | male || 9 | Qian | secret |+----+-------+--------+
1.3 连接删除数据
允许使用类似的join同时删除多个表内的记录。需要先提供表名在提提供连接条件。
此时one表中的数据:
mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | a | 10 || 2 | b | 20 || 3 | c | 30 |+--------+----------+--------------+3 rows in set (0.01 sec)
mysql> select * from two;+--------+----------+--------------+| two_id | two_data | public_field |+--------+----------+--------------+| 2 | B | 20 || 3 | C | 30 || 4 | D | 40 |+--------+----------+--------------+3 rows in set (0.00 sec)
如何一次性同时删除one表和two表中id号为2的记录?
首先我们先看一下两个表中数据的联合:
mysql> select * from one inner join two using (public_field);+--------------+--------+----------+--------+----------+| public_field | one_id | one_data | two_id | two_data |+--------------+--------+----------+--------+----------+| 20 | 2 | b | 2 | B || 30 | 3 | c | 3 | C |+--------------+--------+----------+--------+----------+2 rows in set (0.00 sec)
那么我们就可以删除掉联合表中id为2的数据了,想要one表和two表中的数据同时删除id为2的记录,应该这么操作:
mysql> delete from one, two using one inner join two using (public_field) where one_id = 2;Query OK, 2 rows affected (0.04 sec)mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | a | 10 || 3 | c | 30 |+--------+----------+--------------+
此时表中的数据:
mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | a | 10 || 3 | c | 30 |+--------+----------+--------------+2 rows in set (0.00 sec)mysql> select * from two;+--------+----------+--------------+| two_id | two_data | public_field |+--------+----------+--------------+| 3 | C | 30 || 4 | D | 40 |+--------+----------+--------------+2 rows in set (0.00 sec)
当然了,若想删除两个表中id相同的记录,也可以将两个表中的记录分别删除:
delect from one where id = 2;
delect from two where id = 2;这样可以达到同样的效果!
1.4 清空表truncate
第一种方法:
mysql> delete from one;Query OK, 2 rows affected (0.02 sec)mysql> select * from one;Empty set (0.00 sec)使用这种语法会逐行删除表中的记录,最后返回被清空的行数,效率比较低!而且不会重新创建自动增长的主键,新插入的值会在原来主键最大值的基础上加1!!!
第二种方法使用truncate:
mysql> truncate table two;Query OK, 0 rows affected (0.02 sec)mysql> select * from two;Empty set (0.00 sec)
使用truncate不会反悔删除的记录数目,并且会重建自动增长的主键!!!它的实现原理实际上是删除了一个表,并新建立一个和刚刚被删除的表结构完全相同的表。
二 更新数据
因为上方已经将表one和two都清空了,所以我们现在先将one和two插入一些数据:
mysql> insert into one values -> (1,'A', 10), -> (2, 'B', 20), -> (3, 'C', 30);Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0mysql>mysql> insert into two values -> (2,'b', 20), -> (3, 'c', 30), -> (4, 'd', 40);Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0
此时表one和two中的数据为:
mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 10 || 2 | B | 20 || 3 | C | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)mysql> select * from two;+--------+----------+--------------+| two_id | two_data | public_field |+--------+----------+--------------+| 2 | b | 20 || 3 | c | 30 || 4 | d | 40 |+--------+----------+--------------+3 rows in set (0.00 sec)
多表更新
首先看一下当两个表联合的时候的结果:
mysql> select * from one inner join two using (public_field);+--------------+--------+----------+--------+----------+| public_field | one_id | one_data | two_id | two_data |+--------------+--------+----------+--------+----------+| 20 | 2 | B | 2 | b || 30 | 3 | C | 3 | c |+--------------+--------+----------+--------+----------+2 rows in set (0.00 sec)
其次在联合的基础上进行更新数据内容:
mysql> update one join two using (public_field) set one_data = 'X', two_data = 'Y' where one_id = 3;Query OK, 2 rows affected (0.02 sec)Rows matched: 2 Changed: 2 Warnings: 0同时将表one中的one_data更新为‘X’,将表two中的two_data更新为‘Y’;
数据展示:
mysql> select * from one inner join two using (public_field);+--------------+--------+----------+--------+----------+| public_field | one_id | one_data | two_id | two_data |+--------------+--------+----------+--------+----------+| 20 | 2 | B | 2 | b || 30 | 3 | X | 3 | Y |+--------------+--------+----------+--------+----------+2 rows in set (0.00 sec)mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 10 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)mysql> select * from two;+--------+----------+--------------+| two_id | two_data | public_field |+--------+----------+--------------+| 2 | b | 20 || 3 | Y | 30 || 4 | d | 40 |+--------+----------+--------------+3 rows in set (0.00 sec)
三 备份还原
1. 导出一张表
Mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表
Mysqldump -u用户名 -p密码 库名 表名1 表名2 表名3 > 文件名(D:/a.sql)
3. 导出所有表
Mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库
Mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件
导入:
1. 在登录mysql的情况下:
Source 备份文件
2. 在不登录的情况下
Mysql -u用户名 -p密码 库名 < 备份文件
方式一,只适用于myisam表
直接将tbl_name.frm、tbl_name.myd、tbl_name.myi三个文件拷贝保存备份即可!需要的时候直接移动到相应的数据库目录内即可!
在我的student_1数据库中有一个表名为room的myisam格式的表:
现在将student_1数据库中room.rfm、room.myd、room.myi拷贝到student_2数据库中:
mysql> use student_2;Database changedmysql> show tables;+---------------------+| Tables_in_student_2 |+---------------------+| exam_student || room |+---------------------+2 rows in set (0.02 sec)mysql> select * from room;Empty set (0.00 sec)备份成功!
注意:如果是同样的Innodb表结构的文件,则使用show tables时,也可以看到表名,但是不能使用该表!;
此时我将student_1中的teacher_class.frm拷贝到了studetn_2数据库中,则:
mysql> use student_2;Database changedmysql> show tables;+---------------------+| Tables_in_student_2 |+---------------------+| exam_student || room || teacher_class |+---------------------+3 rows in set (0.00 sec)mysql> select * from teacher_class;ERROR 1146 (42S02): Table 'student_2.teacher_class' doesn't exist也就是说,同样的方法对于innodb表结构来说是无效的。
方法二:
方法二是一条通用的方案,不管是myisam表格式还是innodb表格式,都可以使用该方案!
思路:将建表结构与插入数据的sql语句生成并保存,下次如果需要改结构和数据,直接将数据语句执行即可。
利用mysql提供的工具mysqldump完成:
注意:不需要再mysql命令行客户端执行,直接运行即可!
1 备份student_1数据库:
C:\Users\Administrator>mysqldump -uroot -p student_1 > E:/back.sqlEnter password: ******
则在E盘新形成了一个back.sql的文件,文件中的内容为一系列sql语句!
2 将备份的数据还原
所谓备份数据的还原,就是将刚刚生成的sql语句,执行即可!
首先创建一个新的数据库:
mysql> create database student_3;Query OK, 1 row affected (0.02 sec)
然后选中该数据库:
mysql> use student_3;Database changed
然后在该数据库中还原数据,则原先的数据库中的表都会备份到该数据库中:
mysql> source E:/back.sql;Query OK, 0 rows affected (0.00 sec)
3 常用的备份操作:
1 备份整个数据内的表:
Mysqldump -uroot -p db_name > bak.sql
2 备份数据库内的某张表:
mysqldump -uroot -p student_1 teacher_class > e:/teacher_class.sqlmysqldump -uroot -p student_1 teacher_class tbl_name1 tbl_name2 tbl_name3 > e:/php_one_teacher_class.sql
四 视图
4.1 什么是视图
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。 对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。视图是存储在数据库中的查询的sql 语句,它主要出于两种原因:安全原因, 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。
4.2 创建视图:
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement视图名必须唯一,同时不能与表重名。
视图可以使用select语句查询到的列名,也可以自己指定相应的列名。
可以指定视图执行的算法,通过algorithm指定。
创建teacher_class的一个视图(挑选出一个表的一部分内容,另一部分隐藏):
mysql> create view myView as select id,t_name,gender,c_name,room,begin_date,end_date from teacher_class;Query OK, 0 rows affected (0.03 sec)
mysql> select * from myview;+----+--------+--------+---------+------+------------+------------+| id | t_name | gender | c_name | room | begin_date | end_date |+----+--------+--------+---------+------+------------+------------+| 1 | 韩信 | male | php0115 | 207 | 2013-01-15 | 2013-02-20 || 2 | 韩信 | male | php0228 | 106 | 2013-02-28 | 2013-03-30 || 3 | 韩信 | male | php0331 | 102 | 2013-03-31 | 2013-05-05 || 4 | 李白 | male | php0115 | 207 | 2013-02-22 | 2013-03-25 || 5 | 李白 | male | php0228 | 204 | 2013-03-31 | 2013-04-29 || 6 | 韩非 | secret | php0115 | 207 | 2013-03-27 | 2013-04-18 || 7 | 韩信 | male | php0115 | 207 | 2013-01-15 | 2013-02-20 || 8 | 李宁 | male | php0331 | 102 | 2013-03-31 | 2013-05-05 |+----+--------+--------+---------+------+------------+------------+8 rows in set (0.01 sec)
创建视图的目的无非就是隐藏一些数据不让客户看到,或者将一些表联合起来,以便于以后的增删改查!
创建另外一个视图(合并两个表)
mysql> create view myview3 as select * from one inner join two using (public_field);Query OK, 0 rows affected (0.03 sec)mysql> select * from myview3;+--------------+--------+----------+--------+----------+| public_field | one_id | one_data | two_id | two_data |+--------------+--------+----------+--------+----------+| 20 | 2 | B | 2 | b || 30 | 3 | X | 3 | Y |+--------------+--------+----------+--------+----------+2 rows in set (0.00 sec)
视图就是一个存在于数据库中的虚拟表!
视图本身并没有数据,只是通过执行相应的select语句来完成获得相应的数据,也就是说视图只是保存了相应的select语句!
4.3 查看结构
SHOW CREATE VIEW view_name
mysql> show create view myview;+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+| View | Create Viewacter_set_client | collation_connection |+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+| myview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`S `id`,`teacher_class`.`t_name` AS `t_name`,`teacher_c`.`room` AS `room`,`teacher_class`.`begin_date` AS `be | gbk_chinese_ci |+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+1 row in set (0.00 sec)
4.4 删除视图
DROP VIEW [IF EXISTS] view_name [, view_name];
mysql> drop view myview3;Query OK, 0 rows affected (0.00 sec)
4.5 修改视图结构
ALTER VIEW view_name [(column_list)] AS select_statement
mysql> alter view myview as select id, t_name, c_name, days from teacher_class;Query OK, 0 rows affected (0.04 sec)
mysql> select * from myview;+----+--------+---------+------+| id | t_name | c_name | days |+----+--------+---------+------+| 1 | 韩信 | php0115 | 21 || 2 | 韩信 | php0228 | 18 || 3 | 韩信 | php0331 | 24 || 4 | 李白 | php0115 | 20 || 5 | 李白 | php0228 | 21 || 6 | 韩非 | php0115 | 15 || 7 | 韩信 | php0115 | 21 || 8 | 李宁 | php0331 | 24 |+----+--------+---------+------+8 rows in set (0.00 sec)
修改视图内所使用的字段的名称(别名用逗号隔开,用小括号包裹起来!):
mysql> alter view myview (v_id, v_name, vc_name, v_days) as select id, t_name, c_name, days from teacher_class;Query OK, 0 rows affected (0.04 sec)
mysql> select * from myview;+------+--------+---------+--------+| v_id | v_name | vc_name | v_days |+------+--------+---------+--------+| 1 | 韩信 | php0115 | 21 || 2 | 韩信 | php0228 | 18 || 3 | 韩信 | php0331 | 24 || 4 | 李白 | php0115 | 20 || 5 | 李白 | php0228 | 21 || 6 | 韩非 | php0115 | 15 || 7 | 韩信 | php0115 | 21 || 8 | 李宁 | php0331 | 24 |+------+--------+---------+--------+8 rows in set (0.00 sec)
4.6 更新视图数据
可以通过视图来修改原始表的数据。
如通过视图将李白的天数设置为100:
mysql> update myview set v_days = 100 where v_name = '李宁' and v_days = 24;Query OK, 1 row affected (0.03 sec)Rows matched: 1 Changed: 1 Warnings: 0
视图中的数据:
mysql> select * from myview;+------+--------+---------+--------+| v_id | v_name | vc_name | v_days |+------+--------+---------+--------+| 1 | 韩信 | php0115 | 21 || 2 | 韩信 | php0228 | 18 || 3 | 韩信 | php0331 | 24 || 4 | 李白 | php0115 | 20 || 5 | 李白 | php0228 | 21 || 6 | 韩非 | php0115 | 15 || 7 | 韩信 | php0115 | 21 || 8 | 李宁 | php0331 | 24 |+------+--------+---------+--------+8 rows in set (0.00 sec)
原始表中的数据:
mysql> select * from teacher_class;+----+--------+--------+---------+------+------+------------+------------+| id | t_name | gender | c_name | room | days | begin_date | end_date |+----+--------+--------+---------+------+------+------------+------------+| 1 | 韩信 | male | php0115 | 207 | 21 | 2013-01-15 | 2013-02-20 || 2 | 韩信 | male | php0228 | 106 | 18 | 2013-02-28 | 2013-03-30 || 3 | 韩信 | male | php0331 | 102 | 24 | 2013-03-31 | 2013-05-05 || 4 | 李白 | male | php0115 | 207 | 20 | 2013-02-22 | 2013-03-25 || 5 | 李白 | male | php0228 | 204 | 21 | 2013-03-31 | 2013-04-29 || 6 | 韩非 | secret | php0115 | 207 | 15 | 2013-03-27 | 2013-04-18 || 7 | 韩信 | male | php0115 | 207 | 21 | 2013-01-15 | 2013-02-20 || 8 | 李宁 | male | php0331 | 102 | 100 | 2013-03-31 | 2013-05-05 |+----+--------+--------+---------+------+------+------------+------------+8 rows in set (0.00 sec)
4.7 视图的执行算法
视图中存在两种执行算法:merge和temptable,指的是一个视图是在什么时候执行,依据哪些方式执行。
merge:合并的执行方式,每当执行的时候,现将我们视图的sql语句与外部查询视图的sql语句,混合在一起。最终执行:
Temptable:临时表,模式,每当查询的时候,将视图所使用select语句生成一个结果的临时表。再在当前的临时表内进行查询。
当用户创建视图时,mysql默认使用一种 undefine的处理算法:就是会自动在合并和临时表内进行选择。Temptable:临时表,模式,每当查询的时候,将视图所使用select语句生成一个结果的临时表。再在当前的临时表内进行查询。
五 事务
5.1 事务的概念
事务:事务是指逻辑上的一组操作,组成这组操作的各个单元,要不全成功要不全失败。
一组sql语句操作单元,组内所有sql语句完成一个业务,如果整组成功:意味着全部sql都实现;如果其中任何一个失败:”意味着整个操作都失败。失败,意味着整个过程都是没有意义的。应该是数据库回到 操作前的初始状态。
5.2 事务的特性
- 原子性(Atomicity):事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
- 一致性(Consistency):事务前后数据的完整性必须保持一致。
- 隔离性(Isolation):多个用户并发访问数据库时,一个用户的事务不能被其它用户的事物所干扰,多个并发事务之间的数据要相互隔离。
- 持久性(Durability):一个事务一旦被提交,它对数据库中的数据改变就是永久性的。
5.3 如何处理
- 失败后,可以回到开始的位置;
- 没成功之前,别的用户(进程、回话)是不能看到操作内的数据的修改的。
思路:就是在一组操作之间,设计一个标号(备份点)。
实现:利用innodb存储引擎的事务日志功能!
SQL的执行分成两个阶段:
- 执行阶段
- 将执行结果,提交到数据库的阶段。
其中我们的事务日志,就是保存执行阶段的结果,如果用于选择提交,则才将执行将结果提交到数据库。
MySQL中默认的提交方式是自动提交,执行完毕,自动完成提交工作,因此需要自动关闭自动提交功能。
5.4 变量设置
mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec)
mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+1 row in set (0.00 sec)
在此窗口中,我们查看表one的数据内容:
mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 10 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)
mysql> update one set public_field =100 where one_id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 100 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)
mysql> use student_1;Database changedmysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 10 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)我们发现,另外一个表中的数据内容并没有发生改变,这是因为数据没有提交的原因。
将数据提交之后,
另外一个客户端就能看到更新后的内容。
mysql> commit;Query OK, 0 rows affected (0.02 sec)
另外一个客户端就能看到更新后的内容。
使用rollback的话就能回退到更改之前的状态!
mysql> update one set public_field =0 where one_id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 0 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)
mysql> rollback;Query OK, 0 rows affected (0.03 sec)mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 100 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)
5.5 常见的事务指令
开启事务(此时设置autocommit为开启状态):
start transaction;
rollback:失败回滚
提交之后其他客户才能得到结果
此时one表中的数据:
mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 111 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)
mysql> start transaction;Query OK, 0 rows affected (0.00 sec)
更新数据:
mysql> update one set public_field=222 where one_id = 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 222 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)
mysql> select * from one;+--------+----------+--------------+| one_id | one_data | public_field |+--------+----------+--------------+| 1 | A | 111 || 2 | B | 20 || 3 | X | 30 |+--------+----------+--------------+3 rows in set (0.00 sec)
这是因为没有提交的原因。
commit;之后另外一个客户端就可以查看到相应的更改数据。
建议:使用start transaction,因为这样只改变一次的行为,而不会像autocommit一样始终改变其行为。
六 触发器
触发程序是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对象。
监听数据进行操作:在当前的表上,设置了一个队每行数据的一个监听器,监听相关事件,每当时间发生的时候,会执行一段由sql完成的一段功能代码。
6.1 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
其中:
- trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER,以指明触发程序是在激活它的语句之前或之后触发。
- trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:
- INSERT:将新行插入表时激活触发程序,例如,通过INSERT、LOAD DATA和REPLACE语句。
- UPDATE:更改某一行时激活触发程序,例如,通过UPDATE语句。
- DELETE:从表中删除某一行时激活触发程序,例如,通过DELETE和REPLACE语句。
由时机和事件在一起就形成了六种事件:
before insert、before update、before delete
after insert、after update、after delete.
注意:
- 触发器不能重名;
- 目前mysql只支持一类事件设置一个触发器。
首先创建学生类(包含id,姓名和所携带的钱):
mysql> drop table if exists money_stu;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create table money_stu( -> id int primary key auto_increment, -> name varchar(20), -> money int -> );Query OK, 0 rows affected (0.04 sec)
再创建班费类(存放学生的班费):
mysql> drop table if exists money_cls;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create table money_cls( -> money int -> );Query OK, 0 rows affected (0.10 sec)
向班费中提供初始值2000;
mysql> insert into money_cls values(2000);Query OK, 1 row affected (0.03 sec)
mysql> select * from money_cls;+-------+| money |+-------+| 2000 |+-------+1 row in set (0.00 sec)
创建触发器jiaobanfei1,每向学生表中插入一个学生,就需要缴费50元:
mysql> create trigger jiaobanfei1 after insert on money_stu for each row -> update money_cls set money = money+50;Query OK, 0 rows affected (0.08 sec)
mysql> insert into money_stu values(null, 'Apple', 5000-50);Query OK, 1 row affected (0.04 sec)mysql> select * from money_stu;+----+-------+-------+| id | name | money |+----+-------+-------+| 1 | Apple | 4950 |+----+-------+-------+1 row in set (0.00 sec)mysql> select * from money_cls;+-------+| money |+-------+| 2050 |+-------+1 row in set (0.00 sec)每插入一个学生,就要向班级中缴费50元,班费表中自动增加50元,。
新建一个触发器jiaobanfei2,每当更新学生中的数据,就将学生变化的钱数交到班费中:
mysql> create trigger jiaobanfei2 after update on money_stu for each row -> update money_cls set money = money +(old.money-new.money);Query OK, 0 rows affected (0.07 sec)
mysql> update money_stu set money = money-50 where id =1;Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from money_stu;+----+-------+-------+| id | name | money |+----+-------+-------+| 1 | Apple | 4900 |+----+-------+-------+1 row in set (0.00 sec)mysql> select * from money_cls;+-------+| money |+-------+| 2100 |+-------+1 row in set (0.00 sec)班费钱的变化量就等于学生钱的变化量,不用再人为的控制!
Old:监听事件所在表上的 数据,在事件发生之前时的数据。旧的数据。代表触发该触发程序的记录也就是(on)代表的记录。
New:监听表上,事件发生之后,新处理完毕的数据。
数据,就是触发该事件的记录。
事件是insert呢? 不能使用old
事件是 delete呢?不能使用new
6.2 包含多条sql语句的触发器
1 语句组成语句块(begin...end)用来标识语句块
2 语句块的语句需要独立的语句结束符,分号
命令行:由于触发器程序内使用分号作为语句结束符,那么当命令行客户端碰到分号的时候,就应该理解成出发程序内子语句结束,而不是整个创建触发器的语句结束。
此时应该通过修改命令行的语句结束符达到目的,dilimiter语句可以完成设置语句结束符。最后别忘了改回去。
一个触发器同时处理两条以上的语句:
mysql> delimiter $$mysql> create trigger jiaobanfei4 after update on money_stu for each row -> begin -> update money_cls set money=money + new.money-old.money; -> update money_cls set count=count+1; -> end -> $$Query OK, 0 rows affected (0.07 sec)mysql>mysql> delimiter ;一开始的delimiter $$设置结束符,最后的还原默认的结束符;
更新语句用begin....end包围着。
mysql> update money_stu set money = money-50;Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from money_cls;+-------+-------+| money | count |+-------+-------+| 2050 | 1 |+-------+-------+1 row in set (0.00 sec)mysql> update money_stu set money = money-50;Query OK, 1 row affected (0.02 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from money_cls;+-------+-------+| money | count |+-------+-------+| 2000 | 2 |+-------+-------+1 row in set (0.00 sec)
6.2 删除触发器
DROP TRIGGER [schema_name.]trigger_name
mysql> drop trigger jiaobanfei2;Query OK, 0 rows affected (0.04 sec
特殊的执行:
Insert into on duplicate key update 语法会触发:
如果没有重复记录,会触发before insert, after insert;如果有重复记录并更新会触发before insert, before update, after update。如果有重复记录但是没有发生更新:则触发before insert,befor update
Replace 语法 如果有记录,则执行before insert, before delete, after delete, after insert
阅读全文
0 0
- MySQL快速入门12----删除数据 & 备份还原 & 视图 & 事务 & 触发器
- MySQL快速入门12----删除数据 & 备份还原 & 视图 & 事务 & 触发器
- 06、MySQL视图及数据备份还原
- mysql事务视图触发器
- mysql 数据备份、还原
- mysql触发器+权限管理+备份还原
- Mysql入门(九)之数据备份与还原
- mysql 数据备份和还原
- MySQL数据备份与还原
- mysql数据备份与还原
- MySQL:数据备份与还原
- MySQL数据备份与还原
- mysql数据备份与还原
- mysql数据备份与还原
- mysql数据备份与还原
- Mysql 数据备份与还原
- MySQL 数据备份与还原
- MySQL 数据备份与还原
- lib和dll文件的区别和联系
- 架构师之路(十四)
- Android端与PC端同步绘画板(二)-适配手机和PC屏幕大小
- Python 包管理工具解惑
- 500 G JAVA视频网盘分享(JEECG开源社区)
- MySQL快速入门12----删除数据 & 备份还原 & 视图 & 事务 & 触发器
- Http 中Connection: close和Connection: keep-alive有什么区别?
- /proc文件内容详解
- 嵌入式学习笔记(第一天) ----基本命令
- android代码混淆_小总结
- Android 关于多点触摸抛出ArrayIndexOutOfBoundsException异常问题的解决
- php的数组
- 使用DB2top查看最慢的sql语句
- centos环境下,maven的安装以及在eclipse中的配置(2017/07/06)