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

最新推荐文章于 2023-06-02 09:20:45 发布
最新推荐文章于 2023-06-02 09:20:45 发布
阅读量260 收藏
点赞数

作者：qianqin_2014-----感谢原创，感谢作者！！

一 删除数据




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

其中：
  1. trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
  2. 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.

注意：
  1. 触发器不能重名；
  2. 目前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



meizheming
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL新手入门指南--快速参考
09-11
MySQL新手入门指南--快速参考
第七节：MySQL触发器及备份与还原
qq_43220836的博客
06-21 1159
​ 触发器是为了响应某个事件执行的任务，事件包括添加修改删除操作，任何前后触发器不能手动调用，创建好以后由SQL数据库自动触发。
触发器+日志+备份与恢复
m0_69309069的博客
10-29 695
提示：在MySQL服务运行的情况下，为了保证数据的一致性，需要特别注意的是：备份MyISAM存储引擎类型的表时，要在mysqldump命令中加上--lock-tables参数，用于将所有的数据表加上读锁，这样在备份期间，所有表将只能读取而不能进行数据更新；该值默认为10，以秒为单位，可以精确到微秒。1、ROW_COUNT()函数用于记录更新操作影响的行数，如果其值不等于1，就说明订单中商品的数量大于库存数量，goods表没有更新，此时将执行下面的语句，将“商品名+库存不足”赋给变量msg。
关于 MySQL 中 Update 使用
小羊的博客
06-02 4万+
本文介绍了 MySQL 中 Update 命令的基本语法以及一些特殊的用法，包括多表联合更新和使用子查询。通过本文的学习，您应该能够使用 Update 命令来更新表格中的记录，并根据需要修改它们的值。总体而言，Update 命令是 MySQL 中非常常用的一种命令，掌握它对于进行数据更新操作是非常重要的。
mysql中触发器 删除表数据_MySQL删除表数据
weixin_32208021的博客
01-28 4205
在本教程中，您将学习如何使用MySQL DELETE语句从单个表中删除数据。1. MySQL DELETE语句介绍要从表中删除数据，请使用MySQL DELETE语句。下面说明了DELETE语句的语法：DELETE FROM table_nameWHERE condition;在上面查询语句中 -首先，指定删除数据的表(table_name)。其次，使用条件来指定要在WHERE子句中删除的行记录。...
课设&大作业&毕设-实现的网上书店前后台系统
03-30
基于Structs+Hibernate+Spring+mahout+bootstrap+junit+tomacat+mysql 实现的网上书店前后台系统， 毕业设计。
课设&大作业&毕设-基于PHP+MySQL的图书管理系统设计与实现.zip
04-12
2、适用人群：主要针对计算机相关专业(如计科、信息安全、数据科学与大数据技术、人工智能、通信、物联网、数学、电子信息等)的同学或企业员工下载使用，具有较高的学习借鉴价值。 3、不仅适合小白学习实战练习，也...
课设&大作业&毕设-车辆管理系统本科毕业设计，php+mysql+python.zip
最新发布
04-12
2、适用人群：主要针对计算机相关专业(如计科、信息安全、数据科学与大数据技术、人工智能、通信、物联网、数学、电子信息等)的同学或企业员工下载使用，具有较高的学习借鉴价值。 3、不仅适合小白学习实战练习，也...
大数据时代数据库-云HBase架构&生态&实践
02-24
起初，一个创业公司的基本思路就是首先架构一个或者几个ECS，后面加入MySQL,如果有图片需求还可加入磁盘，该架构的基本能力包括事务、存储、索引和计算力。随着公司的慢慢发展，数据量在不断地增大，其通过MySQL及...
【MySQL触发器】触发器的使用、创建、修改及删除
热门推荐
qq_54169998的博客
02-02 5万+
触发器的概念 当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用sql语句进行更新，将需要执行多条操作语句！ 触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。 创建触发器 在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现，其语法形式如下： CR...
mysql update多条_用 Python 操作 MySQL
weixin_39628247的博客
11-21 662
欢迎关注公众号 学习资料不会少MySQLdb与PyMySQL 都是在Python 中操作 MySOL 数据库的包，只不过二者所支持的Python版本不同。MySQLdb支持Python 2, PyMySOL 支持Python 3.这是读者需要注意的地方。对Python处理MySQL数据库的代码进行分析_目前Python的版本主要有两个－Python 2和Python 3.这两个版本的语法...
mysql中update多条相同数据时，如何只做到只更新其中一部分？
festone000的专栏
12-08 1万+
这个问题就非常有意思了，不常遇到，遇到的话网上也是各种瞎说。 假如有如图所示的一个表，在没有id字段的情况下，怎样做到只更新第三条，而不更新前两条。 在mysql 5.7 community上已经尝试过，切实可行。 思路就是将update与limit结合使用。 update test_table test set test.aaa = 'xxx' where tes
Mysql的update结合select更新数据
u010790621的博客
05-24 841
结合inner join更新表字段 update tableOne inner join tableTwo on tableOne.id=tableTwo.id set tableOne.date=tableTwo.date;
数据库（insert-one和update）
weixin_42336606的博客
06-14 1801
def save_to_mongodb(data):    # insert_one: 覆盖式的    db['article'].insert_one(data)    # 更新的方法：    # 参数1：指定根据什么字段去数据库中进行查询，字段的值。    # 参数2：如果经过参数1的查询，查询到这条数据，执行更新的操作；反之，执行插入的操作；$set是一个固定的写法。    # 参数3：是否...
mysql update 和select 配合使用
wzs535131的博客
05-15 5029
mysql 中update 可以和select配合使用，即更新的数据是用select查出来的； 举例： update b inner join(select x,y from m) n on b.x = n.x set b.y = n.y;
mysql update不更新_记一次MySQL更新语句update的踩坑
weixin_32184669的博客
01-19 1921
背景最近在一次线上作业过程中执行了一句DML语句，本以为万无一失，结果应用反馈说没有更新，数据还是没有变，最后经过排查才发现是我语句写错了，导致update语句执行的结果与预期不符。情景再现为了方便演示，建立一张用户表，同时插入五条数据。create table user(id int(12) comment '用户主键id',name varchar(36) comment '用户名',age ...
如何恢复被删除的视图
翼向天开
08-09 6545
误删视图的恢复方法。
mysql触发器删除表数据时将删除的数据插入到备份表中
qq_36677358的博客
10-25 4873
使用触发器 CREATE TRIGGER '触发器名称' BEFORE DELETE ON '删除的主表名称' FOR EACH ROW BEGIN INSERT INTO '备份表名称' SELECT * FROM '主表名称' where id=old.id; END
mysql -u root -p还原文件无效
06-03
如果你使用 `mysql -u root -p` 命令还原文件无效，可能有以下几个原因： 1. 文件路径错误：请确保你输入的文件路径是正确的，并且你有权限访问该文件。 2. 文件格式错误：请确保你要还原的文件是正确的格式，如.sql格式。 3. 数据库不存在：请确保你要还原的数据库已经存在，如果不存在，请先创建该数据库。 4. 数据库表已存在：如果你要还原的数据库表已经存在，那么你需要先删除该表再还原。 5. 文件编码不匹配：请确保你要还原的文件编码与MySQL服务器的编码匹配。 如果你排除了以上几个原因，但还原文件仍然无效，你可以尝试使用其他工具或方法来还原MySQL数据库。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值