MySQL数据库04 高级特性,事务,视图,索引,存储过程_哪一项高级特性通过对数据进行重新组织,松姐数据量实现了存储空间的节省。-优快云博客

本文深入探讨MySQL的高级特性，包括事务处理的四大特性、隔离级别及其在银行转账业务中的应用，视图的概念、优缺点与创建优化，索引的作用、类型及优化策略，以及存储过程的语法和实际应用案例。通过对这些特性的理解，可以提升数据库操作效率和数据安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL的事务处理

概念

事务就是将一组SQL语句放在同一批次内去执行

如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行

银行转账

A==> B 转账,

A的账户余额减去 1000 ,

B的账户余额加上 1000

该两个步骤全部完成, 才可以说, 转账业务完成

事务的特性

原子性不可分割的最小单位
一致性(整体性) 遵循能量守恒定律
隔离性
持久性 (持续性/ 永久性) 一旦确认数据没有问题, 提交到数据库, 永久保存

案例实现

create table if not exists account(
    id varchar(5),
    name varchar(50),
    balance double
)
insert into account values('10086','老毕',30000);
insert into account values('10010','凤姐',0);

转账业务

# 老毕 - 10000
update account set balance = balance - 10000 where id = 10086;
# 凤姐 + 10000
update account set balance = balance + 10000 where id = 10010;

问题

如果两条语句 , 第一条正常执行, 第二条出现异常, 转账业务只完成一半,业务是不能算作成功的, 应该把已经减去的钱 , 加回去

mysql 事务的实现

1- 改变mysql 的事务提交方式 (默认是自动提交, 修改为手动提交)
	set autocommit = 0; 关闭自动提交
	set autocommit = 1; 开启自动提交
2- 开启一个事务 标记事务的起始点
	start Transaction; 
3- commit 提交一个事务给数据库 , 重新设定事务的起点
4- rollback 事务回滚, 数据回到本次事务的初始状态

set autocommit = 0;
start Transaction;
# 老毕 - 10000
update account set balance = balance - 10000 where id = 10086;
# 凤姐 + 10000
update account set balance = balance1 + 10000 where id = 10010;
rollback; # 事务起始点之后的所有操作, 都取消
# 如果没有任何异常, 就可以提交 , 每一次提交都是一个新的起始点
commit;

隔离性

一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

四种隔离级别

第一类丢失更新：在没有事务隔离的情况下，两个事务都同时更新一行数据，但是第二个事务却中途失败退出， 导致对数据的两个修改都失效了。
例如：
 张三的工资为5000，事务A中获取工资为5000，事务B获取工资为5000，汇入100，并提交数据库，工资变为5100，
 随后
 事务A发生异常，回滚了，恢复张三的工资为5000，这样就导致事务B的更新丢失了。

Read Uncommitted（读取未提交内容）

脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，
事务B正在读取张三的工资，读取到张三的工资为8000。
随后，
事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，
事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

Read Committed（读取提交内容）

不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。(主要修改)

不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样

在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

Repeatable Read（可重读）

幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。（针对insert）

目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读

Serializable（可串行化）

传播行为

当多个事务相互发生关联的时候, 事务如何嵌套使用

视图

概念

概念本质就是一个查询语句, 是对查询语句执行结果的引用，不存储具体的数据

入门案例

子查询案例

查询出每个部门的编号,名称,位置,部门人数,和平均工资

# 1- 先查询出 部门人数,和平均工资
select deptno , count(1) ct ,avg(sal) asal
from emp 
group by deptno
# 2- 多表查询
select d.deptno,d.dname,d.loc,nt.ct,nt.asal
from dept d join (select deptno , count(1) ct ,avg(sal) asal
    from emp 
    group by deptno) nt 
on d.deptno = nt.deptno

分析案例

使用子查询, 语句较为复杂, 可以进行一定程度上的优化

优化1

使用新表的方式优化把子查询的结果作为一张实体表保存到数据库

create table nt as 
select deptno , count(1) ct ,avg(sal) asal
from emp 
group by deptno

以上案例就可以简写为

select d.deptno,d.dname,d.loc,nt.ct,nt.asal
from dept d join nt nt 
on d.deptno = nt.deptno

该优化采取的方式, 是把子查询的结果保存到数据库中, 好处是子查询的结果刻意直接作为表使用, 弊端, 占用数据库存储空间

优化2

采取视图的方式优化

create view vnt as 
select deptno , count(1) ct ,avg(sal) asal
from emp 
group by deptno

视图和新建实体表的区别

买东西, 
可以去超市商场买实际的商品 通过实体表
通过使用手机.电脑可以去网上商城,  下单所看到的商品  通过视图

学校开设分校

主校区 和 分校区
主校区 有自己的数据库 (所有资源)
分校区 也有自己的数据库(需要查看主校区数据)

分校如何查看主校数据 
1- 通过网络访问主校区数据数据库 (主校区给分校区提供视图)
2- 把主校区相应数据 拷贝一份到本地 (新建表)
问题 如果主校区数据发生变化
	使用视图 可以访问到 变化之后的数据
	使用本地表 不能随着 主校区数据的变化而变化

1- 视图本质就是一条SQL 语句, 占用空间非常小

新建数据表, 是要保存到数据库中的, 占用较多空间

2- 视图内容随着所查询的表的内容的改变而改变

新建数据表. 不会发生变化

优势

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；

语法

创建方式

create view 视图名 as  查询语句 
# 把查询到的结果保存到视图中, 该视图指向该条语句的查询结果

查看方式

视图就是虚拟的表，因此，查看视图的方法和查看表的方法是一样的：
show tables;
select * from 视图名;
查看视图字段详情的方法有两种，
一种是和查看表详情一样使用desc 视图名，
另外一种方法是show fields from 视图名：

删除视图

删除视图是指删除数据库中已存在的视图，删除视图时，只能删除视图的定义，不会删除数据，也就是说不动基表：
DROP VIEW [IF EXISTS]   
view_name [, view_name] ...

案例

# 部门人数, 平均工资 视图
create view vt as 
select deptno , count(1) ct ,avg(sal) asal
from emp 
group by deptno
# 查看
show tables;
desc vt;
# 删除
drop view if exists vt ;

案例2

# 所有员工的姓名和工资以及部门编号 视图
create view ns as select ename,sal from emp;
# 通过视图 更改emp 表的数据
SELECT * FROM ns;
UPDATE ns SET sal = 900 WHERE ename = 'SMITH'
# emp 表也发生了变化

案例3

查询20号部门员工的姓名和工资以及部门编号 
create view ns1 as  select empno,ename,sal ,deptno from emp where deptno = 20;

INSERT INTO ns1 VALUES(10086,'老毕',8000,20);
SELECT * FROM ns1;
INSERT INTO ns1 VALUES(10010,'老张',8000,30);
SELECT * FROM ns1;

用户插入的数据和视图中所显示的数据不相关

可以在创建视图的时候添加 WITH CHECK OPTION

保证更新视图是在该视图的权限范围之内
如果在创建视图的时候制定了“WITH CHECK OPTION”，那么更新数据时不能插入或更新不符合视图限制条件的记录。

重复演示

drop view if exists ns1 ;
create view ns1 as  select empno,ename,sal ,deptno from emp where deptno = 20 WITH CHECK OPTION;

INSERT INTO ns1 VALUES(10086,'老毕',8000,20);
SELECT * FROM ns1;
INSERT INTO ns1 VALUES(10010,'老张',8000,30);
SELECT * FROM ns1;

索引

概念

图书馆找一本书经历 , 文学类 - 古典文学

在书中找一些具体内容, 每一本书都有自己的目录

作用

提高查询速度
使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
全文检索字段进行搜索优化

分类

主键索引（PRIMARY KEY）
唯一索引（UNIQUE）
常规索引（INDEX）
全文索引（FULLTEXT）

常规索引

语法

index和key关键字都可设置常规索引

案例

CREATE TABLE `emp1` (
    `empno` INT(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` INT(4),  
    `hiredate` DATE,  
    `sal` FLOAT(7,2),  
    `comm` FLOAT(7,2),  
    `deptno` INT(2)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO EMP1 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP1 VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP1 VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP1 VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 
INSERT INTO EMP1 VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP1 VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP1 VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP1 VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP1 VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP1 VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP1 VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP1 VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP1 VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP1 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

# 查询工资为 1250 的所有 员工
select * from emp1 where sal = 1250;

使用关键字 分析sql 语句执行的性能
EXPLAIN select * from emp1 where sal = 1250;

在这里插入图片描述

使用索引优化查询之后, 对比显示效果

给emp1 表的 sal 字段添加索引
alter table emp1 add index index_sal (sal);
使用关键字 分析sql 语句执行的性能
EXPLAIN select * from emp1 where sal = 1250;

在这里插入图片描述

注意

索引应加在查找条件的字段 
(给书籍添加索引,一般是类别索引,很少页数索引, 原因很少人专门根据页数查找书籍)
不宜添加太多常规索引，影响数据的插入、删除和修改操作
(添加索引之后, 图书馆每新增一本书, 都需要把这本书挂在索引上, 需要更新索引)

索引的添加方式

alter table 表名 add index 索引名 (需要添加索引的字段, 可以写多个);

在创建表的同时
create table 表名(
	字段 类型 属性,
	index / key 索引名 (字段,可以写多个)
)

全文索引

fulltext
只能用于MyISAM类型的数据表
只能用于 CHAR 、 VARCHAR、TEXT数据列类型
适合大型数据集

语法格式

CREATE TABLE  表名 (
       #省略一些SQL语句
       FULLTEXT (字段名) 
)ENGINE=MYISAM;

ALTER TABLE 表名 ADD FULLTEXT (字段名);

索引的查看

show index (keys) from 表名

索引的删除

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;

索引准则

1- 索引不是越多越好
2- 不要对经常变动的数据加索引
3- 小数据量的表建议不要加索引
4- 索引一般应加在查找条件的字段 (在WHERE、ORDER BY 子句中经常使用的字段
)

sql查询优化

1- 索引字段上进行运算会使索引失效。
尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2

2- 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符.
因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists.

3- 尽量使用数字型字段
一部分开发人员和数据库管理人员喜欢把包含数值信息的字段 设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

4- 必要时强制查询优化器使用某个索引
SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45) 改成： SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45) 则查询优化器将会强行利用索引IX_ProcessID 执行查询。

5-  尽量避免在索引过的字符数据中，不要使用 like '%L%' 推荐 like 'L%'这也使得引擎无法利用索引。
见如下例子： SELECT * FROM T1 WHERE NAME LIKE ‘%L%’ SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’ SELECT * FROM T1 WHERE NAME LIKE ‘L%’ 即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作，不要习惯性的使用 ‘%L%’这种方式(会导致全表扫描)，如果可以使用`L%’相对来说更好;

数据库的备份和恢复

数据库备份操作

C:\Users\Administrator>mysqldump -uroot -p emp > d:/emp.sql
Enter password: ******
# mysqldump -uroot -p 要备份的数据库 > d:/emp.sql (要备份到的目录文件)

数据库的恢复

第一种

# mysql -uroot -p123456; # 登录数据库
USE test;  # 进入要恢复的数据库
source d:/emp.sql; #使用 source 恢复数据库

第二种

C:\Users\Administrator>mysql -uroot -p emp <  d:/emp.sql
Enter password: ******
# mysql -uroot -p 要恢复到的数据库名(必须存在) <  d:/emp.sql(源文件)

数据库数据的备份和恢复

#备份数据库myschool中的student表中的studentno及studentname列到文件myschool3.sql中
USE myschool;
SELECT  studentno,  studentname  INTO  OUTFILE  'e:/myschool3.sql'  FROM  student;

#恢复文件myschool3.sql中的数据到test数据库的t2表中来
USE test;
CREATE TABLE t2(
	id INT(4),
	sname VARCHAR(20)
)
LOAD  DATA  INFILE  'e:/myschool3.sql'  INTO  TABLE  t2(id, sname);

SELECT * FROM t2;

存储过程

资料 https://www.cnblogs.com/chenhuabin/p/10142190.html

语法格式

delimiter $
create procedure 存储过程名 ([参数的输入输出类型 参数名 参数数据类型] ...)
begin 
end $
delimiter;

参数的输入输出类型 in out inout

银行转账案例

create table if not exists account(
	id int(5) primary key,
	name varchar(50) not null,
	balance double 
);
insert  into account values (10086,'移动',2000);
insert  into account values (10010,'联通',2000);

案例1

要求 : 写一个存储过程完成转账案例

# 三个参数  转出人账号,  转入人账号,  金额,
DELIMITER $$
USE `emp`$$
DROP PROCEDURE IF EXISTS `transfer`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `transfer`(
	in acc_out int,
	in acc_in int , 
	in money double)
begin 
	update account set balance = balance - money where id = acc_out;
	update account set balance = balance + money where id = acc_in;
end$$
DELIMITER ;

调用该存储过程

set @acc_out = 10086;
set @acc_in = 10010;
set @money = 1000;
call transfer(@acc_out,@acc_in,@money);

案例2

创建一个带参数的存储过程，删除emp表中empno为指定值得记录，并返回最高最高月薪，也返回大于指定月薪的人数。

DELIMITER $$
CREATE PROCEDURE `emp_pro`(
	in id int, # 要删除的id
	out max1 int , # 返回值 最高月薪
	inout p_sal int # 传参为 指定月薪, 返回值为高于此月薪的人数
	)
begin
	delete from emp where empno = id;
	select max(sal) into max1 from emp;
	select count(1) into p_sal from emp where sal > p_sal;
end $$
DELIMITER ;

调用

set @p_sal = 1250 ;
call emp_pro(7369 , @max , @p_sal);
select @max , @p_sal ;

触发器

类似于 JavaScript 中的事件
当执行某种操作的时候, 触发某种事件

参考 https://www.cnblogs.com/fps2tao/p/10400936.html

MySQL数据库04 高级特性,事务,视图,索引,存储过程

文章目录

MySQL的事务处理

概念

事务的特性

案例实现

mysql 事务的实现

隔离性

Read Uncommitted（读取未提交内容）

Read Committed（读取提交内容）

Repeatable Read（可重读）

Serializable（可串行化）

传播行为

视图

概念

入门案例

优化1

优化2

视图和新建实体表的区别

学校开设分校

优势

语法

创建方式

查看方式

删除视图

案例

案例2

案例3

索引

概念

常规索引

索引的添加方式

全文索引

索引的查看

索引的删除

索引准则

sql查询优化

数据库的备份和恢复

数据库备份操作

数据库的恢复

数据库数据的备份和恢复

存储过程

语法格式

银行转账案例

案例1

案例2

触发器