MySQL中的事物、索引、视图、触发器

于 2019-04-11 19:37:49 发布
阅读量217 收藏 4
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/rainy_killer/article/details/89218337
版权
本文深入探讨了数据库事务的ACID特性，包括原子性、一致性、隔离性和持久性，以及如何使用事务处理来确保数据的完整性和一致性。此外，还详细讲解了索引的创建、使用和维护，包括不同类型的索引，如普通索引、唯一索引和主键索引，以及如何通过索引来提高查询效率。

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

事物

定义：事务是由一组DML语句组成，完成一个相对完整的功能,这一组DML语句要么全部执行，要么全部放弃执行。

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

start transaction(begin)
DML语句;
savepoint s1;
DML语句;
savepoint s2;
......
rollback t1; -- 回滚到t1
-- 回滚到本事物执行之前
rollback;

commit; 提交事务

example：

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');
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;

commit;

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

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

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

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

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

索引

定义：索引在Mysql中是存储引擎用于快速找到记录的一种数据结构（BTree，平衡树）
使用索引的目的：提高查询速度。

一、语法（格式）

语法1：创建表时，添加索引。
create table 表名(
字段名1 数据类型，
字段名2 数据类型,
…，
Index | key [索引名称] (字段名)
);
语法2：创建完表后，添加索引。
create table 表名(
字段名1 数据类型，
字段名2 数据类型,
…，
);
Alter table 表名 add {unique | primay key| index…索引种类} 索引名(字段名)

二、分类

分为普通索引（index|key）、唯一索引（unique）、主键索引（primary key）,主键都是唯一索引。

-- 普通索引

create table t1(id int,name varchar(20 ));

show create table t1;

-- 结果:
-- CREATE TABLE `t1` (
--   `id` int(11) DEFAULT NULL,
--   `name` varchar(20) DEFAULT NULL
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

alter table t1 add INDEX index_name(name);

show create table t1;

-- 结果：
-- CREATE TABLE `t1` (
--   `id` int(11) DEFAULT NULL,
--   `name` varchar(20) DEFAULT NULL,
--   KEY `index_name` (`name`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

alter table t1 add KEY key_id(id);

-- 唯一索引

-- （主键）
alter table t1 add primary key index_id(id);

-- UNIQUE

show create table t1;

-- CREATE TABLE `t1` (
--   `id` int(11) NOT NULL DEFAULT '0',
--   `name` varchar(20) DEFAULT NULL,
--   `age` int(3) DEFAULT NULL,
--   PRIMARY KEY (`id`),
--   KEY `index_name` (`name`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- unnique 索引 同上

**扩展：多列索引。
ALTER TABLE teacher ADD INDEX(column1,column2,column3);
为多个字段同时设置索引。

三、删除索引

格式：Drop index 索引名 on 表名；

视图

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

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

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

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

-- 创建视图

select name,money from account;

create view v_account as select name,money from account;

select * from v_account;
-- 删除视图
drop view v_account;

eg:

-- eg：

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)
 );
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;
create view v_t_employee as select * from t_employee;

-- 2.将员工的所有信息做成一个视图

select * from t_employee tn NATURAL JOIN t_employee_detail tm;

create view v_employee as select * from t_employee tn NATURAL JOIN t_employee_detail tm;

更新视图

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

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

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

注意

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

（所以并不推荐利用视图来更新数据）

触发器（trigger）

定义：监视某种情况，并触发某种操作，它的执行是由事件来触发的，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

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

一、语法

创建触发器：

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,
	constraint c_gid_fk foreign key(oid) references g(id);
);

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 trigger01 after update on g for each row
BEGIN
	INSERT into o(gid,much) values(1,3);
END $
delimiter ;

update g  set num = num-3 where id = 1;

update g  set num = num-3 where id = 1;

drop trigger trigger01;

-- 练习3;创建对任意产品的触发器

delimiter $ -- 修改结束符
create trigger trigger01 after update on g for each row
BEGIN
	INSERT into o(gid,much) values(new.id,new.much);
END $
delimiter ;

update g  set num = num-4 where id = 2;

数据库的备份和恢复

备份

c:/>mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql

在这里插入图片描述
在这里插入图片描述
注意:

  1. 使用的是window的命令行
  2. mysqldump 是 myslq的可执行命令
  3. localhost 表示网址

  4. d:\mydb1.sql 表示 保存的位置。 /表示Linux路径, \ windows路径。
    导入恢复

先创建数据库名，并选择数据库.
SOURCE d:/mydb1.sql;

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值