MySQL快速入门12----删除数据 & 备份还原 & 视图 & 事务 & 触发器-优快云博客

本文链接：https://blog.youkuaiyun.com/qianqin_2014/article/details/52151722

一删除数据

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)

此时two表中的数据：

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: 0

mysql>
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 changed
mysql> 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 changed
mysql> 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.sql
Enter 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.sql
mysqldump -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 View


acter_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)

更改了myview视图中的列：

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的处理算法：就是会自动在合并和临时表内进行选择。

五事务

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)

设置自动提交属性（0为不自动提交）：

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)

更改表one的数据内容：

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: 0

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)

此时打开另外一个数据段，查看表one的数据内容：

mysql> use student_1;
Database changed
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> 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: 0

mysql> 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;

commit：提交

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: 0

mysql> 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: 0

mysql> 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和new代替旧的和新的数据

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: 0

mysql> 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: 0

mysql> 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