小辉python
posts - 50,comments - 0,views - 22327
MySQL之 视图，触发器，存储过程，函数，事物，数据库锁，数据库备份

1.视图

视图： 是一个虚拟表，其内容由查询定义；

视图有如下特点; 
　　1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。 
　　2. 视图是由基本表（实表）产生的表（虚表）。 
　　3. 视图的建立和删除不影响基本表。 
　　4. 对视图内容的更新（添加、删除和修改）直接影响基本表。 
　　5. 当视图来自多个基本表时，不允许添加和删除数据。

?
1
2
3
4
5
#视图
create view 视图名称 as sql查询语句
-- ALTER VIEW views as select name,num,id from goods  #更新视图
-- drop VIEW views
#使用视图，就跟正常的表一样的查询;

　

2.触发器-trigger

触发器：监视某种情况，并触发某种操作；

触发器四要素：

1.监视地点（on table）

2.监视事件（insert, update, delete）

3.触发时间（after, before）

4.触发事件（insert, update, delete）

?
1
2
3
4
5
6
7
create trigger triggerName  after/before  insert/update/delete
     on 表名 for each row #这句话是固定的
 begin
     #需要执行的sql语句
 end
注意1:after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发
注意2:insert/update/delete:只能选一个

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#触发器
#三种监听的方式，INSERT，update,delete
-- INSERT into order_table VALUES(1,1,5); #买了东西，产生了一个订单；
-- #即原商品的数量要对应订单的数量而变化；
-- create TRIGGER tg1 AFTER INSERT ON order_table for EACH ROW
-- BEGIN
--  UPDATE goods SET num = num -new.much where id=new.gid; 
-- # new就是新添加的数据，而old是以前旧的数据
-- END
-- UPDATE goods SET num = num -5 where id=1
-- UPDATE order_table SET much=much -3 where oid=1;
-- #商品一退还3个，即订单的数量减3，原商品的数量加3
 
-- #三种监听的方式，INSERT，update,delete
-- create TRIGGER tg2 AFTER UPDATE ON order_table for EACH ROW
-- BEGIN
-- UPDATE goods SET num = num + old.much - new.much where id=new.gid; 
-- # new就是新添加的数据，而old是以前旧的数据
-- END
 
-- delete from  order_table WHERE gid=1;
--
-- CREATE TRIGGER tg3 AFTER DELETE ON order_table for each ROW
-- BEGIN
--  UPDATE goods SET num= num + old.much where id= old.gid;
-- END

　　

3.存储过程

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
存储过程
-- CREATE PROCEDURE p()
-- BEGIN
--  SELECT * from goods;
-- END
--
-- CALL p()
#存储过程中有3种参数，in(入参类型) out(出参类型 ) INOUT(出入参类型 )
#存储过程就类似把很多的操作封装为一个函数了，然后用 call 来调用函数。
-- CREATE PROCEDURE p1(in i INT,OUT s VARCHAR(20))
-- BEGIN
--  SELECT name INTO s from goods where id=i; 
-- #into 关键字 可以将前面的字段的查询结果 执行INTO 赋值于后面的变量。
-- END
--
-- set @n = null; #@为设置一个变量
-- call p1(2,@n)
-- SELECT @n
 
#查看现有的存储过程；
SHOW procedure status;
#删除存储过程
drop procedure
 
 
 
#计算1到100累加的和，并且返回计算结果；
 
CREATE PROCEDURE p4(INOUT n INT)
BEGIN
    DECLARE sum INT DEFAULT 0; #先声明一个变量，用作接收返回值
    DECLARE i INT;
    set i = 0;
    while i<=n DO #DO 循环开始
        SET sum = sum + i;
        set i=i+1;
    end WHILE; #结束循环
 
--  SELECT sum;
    set n = sum;  #将结果赋值于 出参数 n ，把结果输出出去
END;
 
SET @n =100; #设置变量
 
call p4(@n)   #调用这个存储过程
 
SELECT @n     #查询存储过程返回回来的结果

　　

存储过程优点：
        1、存储过程增强了SQL语言灵活性。

　　　　存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
        2、减少网络流量，降低了网络负载。

　　　　　　存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
        3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。

　　　　　　一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点：     

　 　1、扩展功能不方便

　　 2、不便于系统后期维护

 

函数

MySQL提供的内建函数:

　　

事物

事物： 一组sql语句批量执行,要么全部执行成功,要么全部执行失败。

事物的特性：

1.原子性： 对于其数据修改，要么全部执行，要么全部都不执行；

2.一致性： 事物执行前后，约束没有变化；

3.隔离性： 多个事物之前没有影响

4.持久性：即使出现致命的系统故障也将一直保持

 

另外需要注意:

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

#开启一个事物的时候，系统就不会自动的提交数据了，只有commit后才提交；即把缓存区的数据存放在物理区；

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#执行转账，鲁班转账给后羿，首先应该是 减钱，而不是先加钱；
#不开启事物
#后羿 转 1000 给鲁班，分两部；
#1.先是在后羿的账户上减1000，然后在给鲁班的账户上加1000；这样的顺序保证了在突发的情况下，公司不会亏损；
#1
-- select * from account where id=2;
-- UPDATE account SET money = money -1000 where id =2;
-- select money from account where id=2;
#2
-- select * from account where id=1;
-- UPDATE account SET money = money +1000 where id =1;
-- select money from account where id=1;
 
#但是如果中途发生了特殊的情况，导致转账失败了，这样先减的1000，要在给用户加上，这样就显得很繁琐了。
#因此 有了事物这一概念，事物： 一直sql语句批量的执行，要么全部执行成功，要么全部执行失败；
 
-- START TRANSACTION; #开启一个事物；后就不会自动帮你把数据提交给物理区了，需要手动commit;
--  UPDATE account SET money = money -100 where id =2 ;
--  SAVEPOINT s1 #设置保存点；
--  UPDATE account SET money = money +100 where id =1;
-- #mysql数据库有两个部分，一个是物理区，一个是缓存区，正常执行sql语句后的结果在缓存区，
-- # 然后 数据库 内部 自动的把 缓存区里面的数据copy一份给物理区（真实的表），执行完后，缓存区里面的数据就消失了。
-- SELECT * from account;  #执行事物的时候，数据的变化存放 在 缓存区了
-- commit; #要手动的提交事物，给物理区；
-- ROLLBACK to s1; #回滚事物，就是回到执行事情之前的 缓存区的 数据
-- SELECT * from account;

 

数据锁

在并发的时候，同一个数据可以会被多个人同时进行修改，即会造成数据的不安全；因此需要加锁；

在实际开发中有两种锁；

1.悲观锁，即只能在同一时间 内一个人处理数据；就是在查询语句后面加上 for update;

注意：1.在使用悲观锁的时候，需要指定主键，不然就会锁整个表，造成死锁；

　　2.悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。

2.乐观锁

使用数据版本（Version）来记录机制实现；当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。

当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，

如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
#乐观锁：是用数据的版本（Version)记录机制实现；
set @m = 0; -- 账户余额
select money into @m from account where id = 1 ;
select @m;
-- 2.查询版本号
set @version = 0; -- 版本号
select version into @version from account where id = 1 ;
select @version;
  
-- 3.修改账户余额
update account set money = @m -100,version=version+1 where id = 1 and version = @version;
  
select * FROM account where id = 1;

　　

悲观锁与乐观锁的优缺点:

　　两种锁各有其有点缺点,不能单纯的讲哪个更好.

　　　　乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

　　　　但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.

　

　

数据库备份

mysqldump 命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

　　mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
  
#示例：
#单库备份
mysqldump -uroot -p123456 db1 > c:/db1.sql
mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql
  
#多库备份
mysqldump -uroot -p123456 --databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql
  
#备份所有库
mysqldump -uroot -p123456 --all-databases > c:/all.sql

　　

posted on   小辉python  阅读(244)  评论(0)  编辑  收藏  举报
编辑推荐：
· 理解Rust引用及其生命周期标识（下）
· 从二进制到误差：逐行拆解C语言浮点运算中的4008175468544之谜
· .NET制作智能桌面机器人：结合BotSharp智能体框架开发语音交互
· 软件产品开发中常见的10个问题及处理方法
· .NET 原生驾驭 AI 新基建实战系列：向量数据库的应用与畅想
阅读排行：
· 2025成都.NET开发者Connect圆满结束
· 后端思维之高并发处理方案
· 千万级大表的优化技巧
· 在 VS Code 中，一键安装 MCP Server！
· 10年+ .NET Coder 心语 ── 继承的思维：从思维模式到架构设计的深度解析
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

Copyright © 2025 小辉python
Powered by .NET 9.0 on Kubernetes Powered By博客园
点击右上角即可分享
微信分享提示