mysql 事务和视图

雨点打透心脏的1/2处 2022-02-20 00:42 200阅读 0赞

一、事物（Transaction）  
1、什么是事物  
事务是由一组DML语句组成，完成一个相对完整的功能,这一组DML语句要么全部执行，要么全部放弃执行。  
例如，赵四给刘能转账，这对于数据库而言就是一个事务。该事务由两部分单独的操作组成，赵四账户减少钱，刘能账户增加钱，作为数据库，必须保证组成事务的两部分操作必须全部完成，才能维护数据的一致性。当因为某种原因，导致其中的一个操作失败，那么其他的操作也会被撤销。

2、数据库开启事务命令  
start transaction 或者 begin 开启事务  
commit 提交事务，提交未存储的事务  
rollback 回滚事务,即撤销指定的sql语句(回退insert delete update语句)  
savepoint 保留点 ，事务处理中设置的临时占位符 你可以对它发布回退。

练习1：zs 给 lisi 转账 100块钱。

start TRANSACTION;

update account set money=money-100 where name =‘zs’;

update account set money = money+100 where name = ‘ls’;

COMMIT;

练习2：zs 给lisi 转账100块钱，中间出差。

start TRANSACTION;

update account set money=money-100 where name =‘zs’;  
– 错误语句  
insert into account(id,name,money) values(3,‘ww’,1000,333);

update account set money = money+100 where name = ‘ls’;

COMMIT;

select \* from account;

练习3：向t5表格中添加两条数据，然后再回滚到原始值。  
– create table t5(id int PRIMARY key auto\_increment,name varchar(20),gender varchar(20));  
– insert into t5(name,gender) values(‘zs’,‘man’);  
– insert into t5(name,gender) values(‘ls’,‘man’);  
– insert into t5(name,gender) values(‘ww’,‘man’);  
– insert into t5(name,gender) values(‘zq’,‘man’);

start TRANSACTION;  
insert into t5(name,gender) values(‘张飞’,‘man’);  
insert into t5(name,gender) values(‘关羽’,‘man’);  
select \* from t5;  
ROLLBACK;  
select \* from t5;

注意：默认回滚到开启事务位置。

练习4：设置回滚点  
start TRANSACTION;  
insert into t5(name,gender) values(‘张飞’,‘man’);  
SAVEPOINT s1;

insert into t5(name,gender) values(‘关羽’,‘man’);  
SAVEPOINT s2;

insert into t5(name,gender) values(‘刘备’,‘man’);  
SAVEPOINT s3;  
insert into t5(name,gender) values(‘赵云’,‘man’);

select \* from t5;

ROLLBACK to s2;

select \* from t5;

3、事物的特性  
事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这四个特性也简称ACID性。

（1）原子性：是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。就如原子是自然界最小颗粒，具有不可再分的特征一样。

（2）一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。（双方账户的总和不能少也不能多）

（3）隔离性：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。（简单的理解 就是你做你的，我做我的！）

（4）持续性：持续性也称为持久性，指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库。（转账记录成功了，不能过一会变成了出错了）

隔离性:  
客户端1:开启事务后进行操作，但是没有提交事务。

客户端2：在客户端1操作的时候，查看account表格中的数据，发现没有改变，这就是隔离性。

二、索引  
索引在Mysql中是存储引擎用于快速找到记录的一种数据结构（BTree，平衡树），索引对于数据库的良好性能是非常关键的，尤其是当表中的数据量越来越大的时候，索引对于性能的影响愈发重要。  
索引相当于字典的目录，如果查找某个字的时候可以通过目录，提高查询速度，否则需要翻阅字典的每一页。  
使用索引的目的：提高查询速度。  
索引特点：创建和维护索引会消耗很多时间与磁盘空间，但是查询速度大大提高。  
（创建字典目录需要时间，字典目录需要占字典大小）  
（一）、语法  
语法1：创建表时，添加索引。  
create table 表名(  
字段名1 数据类型，  
字段名2 数据类型,  
…，  
Index | key \[索引名称\] (字段名)  
);

语法2：创建完表后，添加索引。  
create table 表名(  
字段名1 数据类型，  
字段名2 数据类型,  
…，  
);  
Alter table 表名 add \{unique | primay key| index…索引种类\} 索引名(字段名)  
（二）、索引分类  
（1）、普通索引 就是一个普通的索引，可以为空，可以重复。  
ALTER TABLE teacher ADD INDEX name\_index(column);

（2）、唯一索引 可以为空，不可以重复。  
ALTER TABLE teacher ADD UNIQUE(column); \# 添加唯一索引  
ALTER TABLE teacher ADD column type UNIQUE; \# 添加列并且添加索引。

alter table t2 add UNIQUE phone\_index(phone);  
show create table t2;  
/\*\*  
CREATE TABLE `t2` (  
`id` int(11) DEFAULT NULL,  
`name` varchar(20) DEFAULT NULL,  
`phone` varchar(20) DEFAULT NULL,  
UNIQUE KEY `phone_index` (`phone`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
\*\*/

（3）、主键索引 不可以为空，不可以重复。  
ALTER TABLE teacher ADD PRIMARY KEY(column);  
只要是主键，就是索引。  
alter table t2 add PRIMARY key(id);  
/\*\*  
CREATE TABLE `t2` (  
`id` int(11) NOT NULL DEFAULT ‘0’,  
`name` varchar(20) DEFAULT NULL,  
`phone` varchar(20) DEFAULT NULL,  
PRIMARY KEY (`id`),  
UNIQUE KEY `phone_index` (`phone`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
\*\*/  
（4）、多列索引。  
ALTER TABLE teacher ADD INDEX(column1,column2,column3);

/\*\*  
CREATE TABLE `t2` (  
`id` int(11) NOT NULL DEFAULT ‘0’,  
`name` varchar(20) DEFAULT NULL,  
`phone` varchar(20) DEFAULT NULL,  
`chinese` int(11) DEFAULT NULL,  
`math` int(11) DEFAULT NULL,  
PRIMARY KEY (`id`),  
UNIQUE KEY `phone_index` (`phone`),  
KEY `chinese` (`chinese`,`math`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8  
\*\*/

（三）、删除索引  
Drop index 索引名 on 表名；  
drop index phone\_index on t2;  
show create table t2;

使用索引：  
1、少量数据不使用数据  
2、查询次数少不使用索引  
3、查询要携带索引字段，通常放在select之后第一个  
4、索引提高了查询的效率，占有跟多的资源。

注意：  
1、索引不是越多越好。（一本书，半本书是目录不可以）。

三、视图  
1、什么是视图  
视图就是一条SELECT语句执行后返回的结果集,  
视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表。

2、视图作用  
（1）简化用户的操作  
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，  
简化操作；

（2）对机密数据提供保护作用  
不希望用户访问表中某些含敏感信息的列，比如salary…

3、操作视图  
（1）创建视图:  
Create view 视图名称 as DQL语句(select …);  
（2）删除视图:  
Drop view 视图名称;

练习  
有 t\_employee和 t\_employee\_detail 表格

t\_temployee 表格:  
CREATE TABLE t\_employee(  
ID INT PRIMARY KEY AUTO\_INCREMENT,  
NAME CHAR(30) NOT NULL,  
GENDER CHAR(2) NOT NULL,  
AGE INT NOT NULL,  
DEPARTMENT CHAR(10) NOT NULL,  
SALARY INT NOT NULL,  
HOME CHAR(30),  
MARRY CHAR(2) NOT NULL DEFAULT ‘否’,  
HOBBY CHAR(30)  
);

t\_employee\_detail 表格:

create TABLE t\_employee\_detail(  
ID INT PRIMARY KEY,  
POS CHAR(10) NOT NULL,  
EXPERENCE CHAR(10) NOT NULL,  
CONSTRAINT `FK_ID` FOREIGN KEY(ID) REFERENCES t\_employee(ID)  
)

INSERT INTO t\_employee(NAME, GENDER, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(‘张飞’,‘女’,21,‘人事部’,‘9000’,‘北京’,‘否’,‘网球’);

INSERT INTO t\_employee(NAME, GENDER, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(‘刘备’,‘男’,22,‘研发部’,‘8000’,‘上海’,‘否’,‘音乐’);

INSERT INTO t\_employee(NAME, GENDER, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(‘关羽’,‘女’,23,‘研发部’,‘9000’,‘重庆’,‘否’,‘无’);

INSERT INTO t\_employee(NAME, GENDER, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(‘马超’,‘女’,24,‘研发部’,‘9000’,‘四川’,‘是’,‘足球’);

INSERT INTO t\_employee(NAME, GENDER, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(‘赵云’,‘男’,25,‘销售部’,‘6000’,‘福建’,‘否’,‘游戏’);

INSERT INTO t\_employee(NAME, GENDER, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(‘诸葛亮’,‘女’,26,‘销售部’,‘5000’,‘山西’,‘否’,‘篮球’);

INSERT INTO t\_employee\_detail(ID,POS,EXPERENCE) VALUES(1,‘人事管理’,‘工作二年’);

INSERT INTO t\_employee\_detail(ID,POS,EXPERENCE) VALUES(2,‘人事招聘’,‘工作二年’);

INSERT INTO t\_employee\_detail(ID,POS,EXPERENCE) VALUES(3,‘初级工程师’,‘工作一年’);

INSERT INTO t\_employee\_detail(ID,POS,EXPERENCE) VALUES(4,‘中级工程师’,‘工作二年’);

INSERT INTO t\_employee\_detail(ID,POS,EXPERENCE) VALUES(5,‘高级工程师’,‘工作三年’);

INSERT INTO t\_employee\_detail(ID,POS,EXPERENCE) VALUES(6,‘销售代表’,‘工作二年’);

练习1: 创建一个员工基本信息的视图。  
– 查看表格中的所有数据  
select \* from t\_employee;  
– 查看表格中员工的基本信息。  
select name,gender,age,home,marry,hobby from t\_employee;  
– 创建视图。  
create view v\_employee as select name,gender,age,home,marry,hobby from t\_employee;  
– 查看视图中的内容  
select \* from v\_employee;

练习2：将员工所有的信息做成一个视图。  
– 查询出所有员工信息  
select \* from t\_employee as t1 LEFT JOIN t\_employee\_detail as t2 ON [t1.id=t2.id][t1.id_t2.id];

– 去掉 重复的id  
select t1.\*,t2.pos,t2.experence from t\_employee as t1 LEFT JOIN t\_employee\_detail as t2 ON [t1.id=t2.id][t1.id_t2.id];

– 创建视图  
create view v\_employee\_detail as select t1.\*,t2.pos,t2.experence from t\_employee as t1 LEFT JOIN t\_employee\_detail as t2 ON [t1.id=t2.id][t1.id_t2.id];

– 显示所有数据  
select \* from v\_employee\_detail;  
select name,gender,age from v\_employee\_detail;

4、更新视图  
在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。  
更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

练习1：将张飞性别改成男  
update v\_employee\_detail set gender =‘男’ where name=‘张飞’;  
注意:对应 的真实表上的数据也发生改变 了

不可更新的视图：  
某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：

· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。  
· DISTINCT  
· GROUP BY  
· HAVING  
· UNION  
· 位于选择列表中的子查询  
· Join  
· FROM子句中的不可更新视图  
· WHERE子句中的子查询，引用FROM子句中的表。

注意

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。  
四、触发器trigger  
1、什么是触发器  
触发器（trigger）：监视某种情况，并触发某种操作，它的执行是由事件来触发的，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：  
1.监视地点(table)  
2.监视事件(insert/update/delete)  
3.触发时间(after/before)  
4.触发事件(insert/update/delete)

2、触发器基本语法  
创建触发器：  
Create trigger trigger\_name trigger\_time trigger\_event on tb\_name for each row trigger\_stmt;

trigger\_time ：触发器的触发事件，可以为before（在检查约束前触发）  
或after（在检查约束后触发）；

trigger\_event：是触发器的触发事件，包括insert、update和delete；

tb\_name： 表示建立触发器的表名，就是在哪张表上建立触发器

trigger\_stmt： 触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句；

FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器。

删除触发器：  
Drop trigger 触发器名称。

案例:  
有两张表格分别为 商品表 和订单表  
– 创建商品表  
create table g(  
id int primary key auto\_increment,  
name varchar(20),  
num int  
);  
– 创建订单表  
create table o(  
oid int primary key auto\_increment,  
gid int,  
much int  
);  
– 插入商品  
insert into g(name,num) values(‘苹果’,10),(‘香蕉’,10),(‘桔子’,10);

练习1: 卖了3个苹果，请更新商品表和订单表中的数据。  
– 1.往订单表插入一条记录  
– insert into o(gid,much) values(1,3);  
– 2.更新商品表商品1的剩余数量  
– update g set num=num-3 where id=1;

练习2：使用触发器来完成练习1的效果。  
– 创建触发器  
– 修改结束符  
delimiter $

create TRIGGER tg1 after insert on o for each row  
begin  
update g set num = num-3 where id = 1;  
end $

delimiter ;

insert into o(gid,much) VALUES(1,3);

注: 会发现苹果的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。

产生的问题:  
不管我们买哪个商品，最终更新的都是商品1的数量。  
insert INTO o(gid,much) VALUES(2,2);

练习3：创建对任意商品改变的触发器  
扩展:  
new 和old 的使用：

NEW.columnname：新增行的某列数据  
OLD.columnname：删除行的某列数据

代码:  
delimiter $  
create trigger tg2 after insert on o for each row  
BEGIN  
update g set num = num-new.much where id = new.gid;  
END $

delimiter ;

INSERT into o(gid,much) values(2,3);

drop trigger tg2;

练习4：当用户撤销一个订单的时候，直接删除一个订单，对应的商品数量再加回去

delimiter $  
CREATE TRIGGER tg3 after delete on o for each ROW  
BEGIN

UPDATE g set num=num+old.much where id = old.gid;

END $

delimiter ;

drop TRIGGER tg3;

DELETE from o where oid = 3;

练习5：当新增一条订单记录时，判断订单的商品数量，如果数量大于了库存数量，就默认改为库存数量。(有可能订单的数据大于库存数据因此会有问题)

扩展: before 和after 的区别：  
after是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作；也就是说先插入订单记录，再更新商品的数量；  
before是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作；  
Delimiter $  
create trigger tg6

before insert on o

for each row

begin

if new.much > 10 then

set new.much = 10;

end if;

update g set num = num - new.much where id = new.gid;

end$

Delimiter $

insert into o(gid,much) valus(1,20);

执行完会发现订单记录的数量变为10，商品1的数量变为0了，就不会出现负数了。

五、数据库的备份和回复  
1.1备份  
c:/>mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql

注意:  
1.使用的是window的命令行  
2.mysqldump 是 myslq的可执行命令  
3.localhost 表示网址  
4.>d:\\mydb1.sql 表示 保存的位置。 /表示Linux路径, \\ windows路径。  
1.2导入  
先创建数据库名，并选择数据库.  
SOURCE d:/mydb1.sql;

[t1.id_t2.id]: http://t1.id=t2.id