Day19 PythonWeb全栈课程课堂内容

1. 视图

1.1 视图介绍

• 视图(VIEW)是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不是实际存在于数据库中，行和列主要用来自定义视图的查询中使用的表，并且是使用视图时动态生成的。
• 作用：使用视图映射你需要查询的数据表。是对于若干张数据表的引用。

1.2 视图操作

CREATE VIEW 视图名称 AS SELECT 语句;

视图的一般命名规则为V_XXX_xxxx。

-- 创建一张表 view_demo
CREATE TABLE `view_demo` (
	`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建该表的视图
CREATE VIEW `v_demo` AS SELECT * FROM `view_demo`;

• 现在插入数据insert into view_demo values(1,'Sam');，会发现查询数据表和视图都会显示相对应的数据。

---

• 视图的修改会影响原表的数据，且在通常情况下一般都不是直接修改视图中的数据。update v_demo set name='Jame' where id=1;，原数据表中的数据同样被修改。

---

视图操作	代码
定义视图	CREATE VIEW 视图名称 AS SELECT 语句;
查看视图	SHOW TABLES;
使用视图	SELECT * FROM 视图名称;
删除视图	DROP VIEW 视图名称;

---

1.3 视图案例

• 创建一张数据表provinces。

CREATE TABLE `provinces` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `provinceid` INT(11) NOT NULL,
  `province` VARCHAR(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=392 DEFAULT CHARSET=utf8;

INSERT  INTO `provinces`(`id`,`provinceid`,`province`) VALUES 
    (1,110000,'北京市'),
    (2,120000,'天津市'),
    (3,130000,'河北省'),
    (4,140000,'山西省'),
    (5,150000,'内蒙古自治区'),
    (6,210000,'辽宁省'),
    (7,220000,'吉林省'),
    (8,230000,'黑龙江省'),
    (9,310000,'上海市'),
    (10,320000,'江苏省'),
    (11,330000,'浙江省'),
    (12,340000,'安徽省'),
    (13,350000,'福建省'),
    (14,360000,'江西省'),
    (15,370000,'山东省'),
    (16,410000,'河南省'),
    (17,420000,'湖北省'),
    (18,430000,'湖南省'),
    (19,440000,'广东省'),
    (20,450000,'广西壮族自治区'),
    (21,460000,'海南省'),
    (22,500000,'重庆市'),
    (23,510000,'四川省'),
    (24,520000,'贵州省'),
    (25,530000,'云南省'),
    (26,540000,'西藏自治区'),
    (27,610000,'陕西省'),
    (28,620000,'甘肃省'),
    (29,630000,'青海省'),
    (30,640000,'宁夏回族自治区'),
    (31,650000,'新疆维吾尔自治区'),
    (32,710000,'台湾省'),
    (33,810000,'香港特别行政区'),
    (34,820000,'澳门特别行政区');

• 创建一张数据表cities。

CREATE TABLE `cities` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `cityid` CHAR(6) NOT NULL COMMENT '城市编码',
  `city` VARCHAR(40) NOT NULL COMMENT '城市名称',
  `provinceid` CHAR(6) NOT NULL COMMENT '所属省份编码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=346 DEFAULT CHARSET=utf8 COMMENT='城市信息表';

insert  into `cities`(`id`,`cityid`,`city`,`provinceid`) values 
(1,'110100','北京市','110000'),(2,'1102xx','北京下属县','1100xx'),
(3,'120100','天津市','120000'),(4,'1202xx','天津下属县','1200xx'),
(5,'130100','石家庄市','130000'),(6,'130200','唐山市','130000'),
(7,'130300','秦皇岛市','130000'),(8,'130400','邯郸市','130000'),
(9,'130500','邢台市','130000'),(10,'130600','保定市','130000'),
(11,'130700','张家口市','130000'),(12,'130800','承德市','130000'),
(13,'130900','沧州市','130000'),(14,'131000','廊坊市','130000'),
(15,'131100','衡水市','130000'),(16,'140100','太原市','140000'),
(17,'140200','大同市','140000'),(18,'140300','阳泉市','140000'),
(19,'140400','长治市','140000'),(20,'140500','晋城市','140000'),
(21,'140600','朔州市','140000'),(22,'140700','晋中市','140000'),
(23,'140800','运城市','140000'),(24,'140900','忻州市','140000'),
(25,'141000','临汾市','140000'),(26,'141100','吕梁市','140000'),
(27,'150100','呼和浩特市','150000'),(28,'150200','包头市','150000'),
(29,'150300','乌海市','150000'),(30,'150400','赤峰市','150000'),
(31,'150500','通辽市','150000'),(32,'150600','鄂尔多斯市','150000'),
(33,'150700','呼伦贝尔市','150000'),(34,'150800','巴彦淖尔市','150000'),
(35,'150900','乌兰察布市','150000'),(36,'152200','兴安盟','150000'),
(37,'152500','锡林郭勒盟','150000'),(38,'152900','阿拉善盟','150000'),
(39,'210100','沈阳市','210000'),(40,'210200','大连市','210000'),
(41,'210300','鞍山市','210000'),(42,'210400','抚顺市','210000'),
(43,'210500','本溪市','210000'),(44,'210600','丹东市','210000'),
(45,'210700','锦州市','210000'),(46,'210800','营口市','210000'),
(47,'210900','阜新市','210000'),(48,'211000','辽阳市','210000'),
(49,'211100','盘锦市','210000'),(50,'211200','铁岭市','210000'),
(51,'211300','朝阳市','210000'),(52,'211400','葫芦岛市','210000'),
(53,'220100','长春市','220000'),(54,'220200','吉林市','220000'),
(55,'220300','四平市','220000'),(56,'220400','辽源市','220000'),
(57,'220500','通化市','220000'),(58,'220600','白山市','220000'),
(59,'220700','松原市','220000'),(60,'220800','白城市','220000'),
(61,'222400','延边朝鲜族自治州','220000'),(62,'230100','哈尔滨市','230000'),
(63,'230200','齐齐哈尔市','230000'),(64,'230300','鸡西市','230000'),
(65,'230400','鹤岗市','230000'),(66,'230500','双鸭山市','230000'),
(67,'230600','大庆市','230000'),(68,'230700','伊春市','230000'),
(69,'230800','佳木斯市','230000'),(70,'230900','七台河市','230000'),
(71,'231000','牡丹江市','230000'),(72,'231100','黑河市','230000'),
(73,'231200','绥化市','230000'),(74,'232700','大兴安岭地区','230000'),
(75,'310100','上海市','310000'),(76,'3102xx','上海下属县','3100xx'),
(77,'320100','南京市','320000'),(78,'320200','无锡市','320000'),
(79,'320300','徐州市','320000'),(80,'320400','常州市','320000'),
(81,'320500','苏州市','320000'),(82,'320600','南通市','320000'),
(83,'320700','连云港市','320000'),(84,'320800','淮安市','320000'),
(85,'320900','盐城市','320000'),(86,'321000','扬州市','320000'),
(87,'321100','镇江市','320000'),(88,'321200','泰州市','320000'),
(89,'321300','宿迁市','320000'),(90,'330100','杭州市','330000'),
(91,'330200','宁波市','330000'),(92,'330300','温州市','330000'),
(93,'330400','嘉兴市','330000'),(94,'330500','湖州市','330000'),
(95,'330600','绍兴市','330000'),(96,'330700','金华市','330000'),
(97,'330800','衢州市','330000'),(98,'330900','舟山市','330000'),
(99,'331000','台州市','330000'),(100,'331100','丽水市','330000'),
(101,'340100','合肥市','340000'),(102,'340200','芜湖市','340000'),
(103,'340300','蚌埠市','340000'),(104,'340400','淮南市','340000'),
(105,'340500','马鞍山市','340000'),(106,'340600','淮北市','340000'),
(107,'340700','铜陵市','340000'),(108,'340800','安庆市','340000'),
(109,'341000','黄山市','340000'),(110,'341100','滁州市','340000'),
(111,'341200','阜阳市','340000'),(112,'341300','宿州市','340000'),
(113,'341400','巢湖市','340000'),(114,'341500','六安市','340000'),
(115,'341600','亳州市','340000'),(116,'341700','池州市','340000'),
(117,'341800','宣城市','340000'),(118,'350100','福州市','350000'),
(119,'350200','厦门市','350000'),(120,'350300','莆田市','350000'),
(121,'350400','三明市','350000'),(122,'350500','泉州市','350000'),
(123,'350600','漳州市','350000'),(124,'350700','南平市','350000'),
(125,'350800','龙岩市','350000'),(126,'350900','宁德市','350000'),
(127,'360100','南昌市','360000'),(128,'360200','景德镇市','360000'),
(129,'360300','萍乡市','360000'),(130,'360400','九江市','360000'),
(131,'360500','新余市','360000'),(132,'360600','鹰潭市','360000'),
(133,'360700','赣州市','360000'),(134,'360800','吉安市','360000'),
(135,'360900','宜春市','360000'),(136,'361000','抚州市','360000'),
(137,'361100','上饶市','360000'),(138,'370100','济南市','370000'),
(139,'370200','青岛市','370000'),(140,'370300','淄博市','370000'),
(141,'370400','枣庄市','370000'),(142,'370500','东营市','370000'),
(143,'370600','烟台市','370000'),(144,'370700','潍坊市','370000'),
(145,'370800','济宁市','370000'),(146,'370900','泰安市','370000'),
(147,'371000','威海市','370000'),(148,'371100','日照市','370000'),
(149,'371200','莱芜市','370000'),(150,'371300','临沂市','370000'),
(151,'371400','德州市','370000'),(152,'371500','聊城市','370000'),
(153,'371600','滨州市','370000'),(154,'371700','荷泽市','370000'),
(155,'410100','郑州市','410000'),(156,'410200','开封市','410000'),
(157,'410300','洛阳市','410000'),(158,'410400','平顶山市','410000'),
(159,'410500','安阳市','410000'),(160,'410600','鹤壁市','410000'),
(161,'410700','新乡市','410000'),(162,'410800','焦作市','410000'),
(163,'410900','濮阳市','410000'),(164,'411000','许昌市','410000'),
(165,'411100','漯河市','410000'),(166,'411200','三门峡市','410000'),
(167,'411300','南阳市','410000'),(168,'411400','商丘市','410000'),
(169,'411500','信阳市','410000'),(170,'411600','周口市','410000'),
(171,'411700','驻马店市','410000'),(172,'420100','武汉市','420000'),
(173,'420200','黄石市','420000'),(174,'420300','十堰市','420000'),
(175,'420500','宜昌市','420000'),(176,'420600','襄樊市','420000'),
(177,'420700','鄂州市','420000'),(178,'420800','荆门市','420000'),
(179,'420900','孝感市','420000'),(180,'421000','荆州市','420000'),
(181,'421100','黄冈市','420000'),(182,'421200','咸宁市','420000'),
(183,'421300','随州市','420000'),(184,'422800','恩施土家族苗族自治州','420000'),
(185,'429000','省直辖行政单位','420000'),(186,'430100','长沙市','430000'),
(187,'430200','株洲市','430000'),(188,'430300','湘潭市','430000'),
(189,'430400','衡阳市','430000'),(190,'430500','邵阳市','430000'),
(191,'430600','岳阳市','430000'),(192,'430700','常德市','430000'),
(193,'430800','张家界市','430000'),(194,'430900','益阳市','430000'),
(195,'431000','郴州市','430000'),(196,'431100','永州市','430000'),
(197,'431200','怀化市','430000'),(198,'431300','娄底市','430000'),
(199,'433100','湘西土家族苗族自治州','430000'),(200,'440100','广州市','440000'),
(201,'440200','韶关市','440000'),(202,'440300','深圳市','440000'),
(203,'440400','珠海市','440000'),(204,'440500','汕头市','440000'),
(205,'440600','佛山市','440000'),(206,'440700','江门市','440000'),
(207,'440800','湛江市','440000'),(208,'440900','茂名市','440000'),
(209,'441200','肇庆市','440000'),(210,'441300','惠州市','440000'),
(211,'441400','梅州市','440000'),(212,'441500','汕尾市','440000'),
(213,'441600','河源市','440000'),(214,'441700','阳江市','440000'),
(215,'441800','清远市','440000'),(216,'441900','东莞市','440000'),
(217,'442000','中山市','440000'),(218,'445100','潮州市','440000'),
(219,'445200','揭阳市','440000'),(220,'445300','云浮市','440000'),
(221,'450100','南宁市','450000'),(222,'450200','柳州市','450000'),
(223,'450300','桂林市','450000'),(224,'450400','梧州市','450000'),
(225,'450500','北海市','450000'),(226,'450600','防城港市','450000'),
(227,'450700','钦州市','450000'),(228,'450800','贵港市','450000'),
(229,'450900','玉林市','450000'),(230,'451000','百色市','450000'),
(231,'451100','贺州市','450000'),(232,'451200','河池市','450000'),
(233,'451300','来宾市','450000'),(234,'451400','崇左市','450000'),
(235,'460100','海口市','460000'),(236,'460200','三亚市','460000'),
(237,'469000','省直辖县级行政单位','460000'),(238,'500100','重庆市','500000'),
(239,'5002xx','重庆下属县','5000xx'),(240,'5003xx','重庆下属市','5000xx'),
(241,'510100','成都市','510000'),(242,'510300','自贡市','510000'),
(243,'510400','攀枝花市','510000'),(244,'510500','泸州市','510000'),
(245,'510600','德阳市','510000'),(246,'510700','绵阳市','510000'),
(247,'510800','广元市','510000'),(248,'510900','遂宁市','510000'),
(249,'511000','内江市','510000'),(250,'511100','乐山市','510000'),
(251,'511300','南充市','510000'),(252,'511400','眉山市','510000'),
(253,'511500','宜宾市','510000'),(254,'511600','广安市','510000'),
(255,'511700','达州市','510000'),(256,'511800','雅安市','510000'),
(257,'511900','巴中市','510000'),(258,'512000','资阳市','510000'),
(259,'513200','阿坝藏族羌族自治州','510000'),(260,'513300','甘孜藏族自治州','510000'),
(261,'513400','凉山彝族自治州','510000'),(262,'520100','贵阳市','520000'),
(263,'520200','六盘水市','520000'),(264,'520300','遵义市','520000'),
(265,'520400','安顺市','520000'),(266,'522200','铜仁地区','520000'),
(267,'522300','黔西南布依族苗族自治州','520000'),(268,'522400','毕节地区','520000'),
(269,'522600','黔东南苗族侗族自治州','520000'),(270,'522700','黔南布依族苗族自治州','520000'),
(271,'530100','昆明市','530000'),(272,'530300','曲靖市','530000'),
(273,'530400','玉溪市','530000'),(274,'530500','保山市','530000'),
(275,'530600','昭通市','530000'),(276,'530700','丽江市','530000'),
(277,'530800','思茅市','530000'),(278,'530900','临沧市','530000'),
(279,'532300','楚雄彝族自治州','530000'),(280,'532500','红河哈尼族彝族自治州','530000'),
(281,'532600','文山壮族苗族自治州','530000'),(282,'532800','西双版纳傣族自治州','530000'),
(283,'532900','大理白族自治州','530000'),(284,'533100','德宏傣族景颇族自治州','530000'),
(285,'533300','怒江傈僳族自治州','530000'),(286,'533400','迪庆藏族自治州','530000'),
(287,'540100','拉萨市','540000'),(288,'542100','昌都地区','540000'),
(289,'542200','山南地区','540000'),(290,'542300','日喀则地区','540000'),
(291,'542400','那曲地区','540000'),(292,'542500','阿里地区','540000'),
(293,'542600','林芝地区','540000'),(294,'610100','西安市','610000'),
(295,'610200','铜川市','610000'),(296,'610300','宝鸡市','610000'),
(297,'610400','咸阳市','610000'),(298,'610500','渭南市','610000'),
(299,'610600','延安市','610000'),(300,'610700','汉中市','610000'),
(301,'610800','榆林市','610000'),(302,'610900','安康市','610000'),
(303,'611000','商洛市','610000'),(304,'620100','兰州市','620000'),
(305,'620200','嘉峪关市','620000'),(306,'620300','金昌市','620000'),
(307,'620400','白银市','620000'),(308,'620500','天水市','620000'),
(309,'620600','武威市','620000'),(310,'620700','张掖市','620000'),
(311,'620800','平凉市','620000'),(312,'620900','酒泉市','620000'),
(313,'621000','庆阳市','620000'),(314,'621100','定西市','620000'),
(315,'621200','陇南市','620000'),(316,'622900','临夏回族自治州','620000'),
(317,'623000','甘南藏族自治州','620000'),(318,'630100','西宁市','630000'),
(319,'632100','海东地区','630000'),(320,'632200','海北藏族自治州','630000'),
(321,'632300','黄南藏族自治州','630000'),(322,'632500','海南藏族自治州','630000'),
(323,'632600','果洛藏族自治州','630000'),(324,'632700','玉树藏族自治州','630000'),
(325,'632800','海西蒙古族藏族自治州','630000'),(326,'640100','银川市','640000'),
(327,'640200','石嘴山市','640000'),(328,'640300','吴忠市','640000'),
(329,'640400','固原市','640000'),(330,'640500','中卫市','640000'),
(331,'650100','乌鲁木齐市','650000'),(332,'650200','克拉玛依市','650000'),
(333,'652100','吐鲁番地区','650000'),(334,'652200','哈密地区','650000'),
(335,'652300','昌吉回族自治州','650000'),(336,'652700','博尔塔拉蒙古自治州','650000'),
(337,'652800','巴音郭楞蒙古自治州','650000'),(338,'652900','阿克苏地区','650000'),
(339,'653000','克孜勒苏柯尔克孜自治州','650000'),(340,'653100','喀什地区','650000'),
(341,'653200','和田地区','650000'),(342,'654000','伊犁哈萨克自治州','650000'),
(343,'654200','塔城地区','650000'),(344,'654300','阿勒泰地区','650000'),
(345,'659000','省直辖行政单位','650000');

• 内连接provinces和cities这两张表。

-- 查询'黑龙江省'下的所有的城市。
select provinces.id,provinces.`province`,cities.`city` 
from provinces inner join cities 
on provinces.`provinceid` = cities.provinceid 
having provinces.`province` = '黑龙江省';

-- 查询'哈尔滨'下的所有的区。
SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` 
HAVING p.`name`='哈尔滨';

-- 查询'黑龙江省'下的所有的城市。
-- 创建视图
CREATE VIEW v_pro_city AS select provinces.id,provinces.`province`,cities.`city` 
from provinces inner join cities 
on provinces.`provinceid` = cities.provinceid 
having provinces.`province` = '黑龙江省';

-- 查询视图
SELECT * FROM v_pro_city;

-- 查询'哈尔滨'下的所有的区。
CREATE VIEW v_area AS SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` 
HAVING p.`name`='哈尔滨';
-- 报错，原因是视图作为表，里面的字段是不能重复的。
ERROR 1060 (42S21): Duplicate column name 'id'


-- 修改视图
CREATE VIEW v_area AS SELECT p.*,c.name AS cname FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` 
HAVING p.`name`='哈尔滨';

• 尝试修改视图中的数据，视图内的数据不能修改update v_pro_city set city='哈尔滨' where city='哈尔滨市';

  update v_pro_city set city='哈尔滨' where city='哈尔滨市';
  -- 无法修改，原因视图
  

1.4 视图的修改限制

有下列内容之一，视图不能做修改

• select子句中包含distinct
• select字句中包含组函数
• select语句中包含group by子句
• selecy语句红包含order by子句
• where子句中包含相关子查询
• from字句中包含多个表
• 如果视图中有计算列，则不能更新
• 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作。

---

视图的目的：方便查询操作，减少复杂的SQL语句，增强可读性。

---

1.5 视图的作用

• 简单：提高了重用性，就像一个函数。

• 安全：提高了安全性能，可以针对不同的用户，设定不同的视图。

• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

---

2. 事务

2.1 为何要有事务

事务广泛的运用于订单系统、银行系统等多种场景

例如：

A用户和B用户是银行的储户，现在A要给B转账500元，那么需要做以下几件事：

1. 检查A的账户余额>500元；
2. A 账户中扣除500元;
3. B 账户中增加500元;

正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。

那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。

以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此。

2.2 事务案例

所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

假如一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：

-- 支票表
CREATE TABLE `checking` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `money` float NOT NULL,
  `name` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 储蓄表
CREATE TABLE `savings` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `money` float NOT NULL,
  `name` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.检查支票账户的余额高于或者等于200美元。

2.从支票账户余额中减去200美元。

3.在储蓄帐户余额中增加200美元。

# 模拟转账
# 1.检查支票账户的余额高于或者等于200美元。
SELECT * FROM checking WHERE `name`='Sam';

# 2.从支票账户余额中减去200美元。
UPDATE checking SET money = money-200 WHERE `name`='Sam';

# 3.在储蓄帐户余额中增加200美元。
UPDATE savings SET money = money+200 WHERE `name`='Sam';

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

# 开启事务
start transaction; -- 高级
begin;

# 1.检查支票账户的余额高于或者等于200美元。
SELECT * FROM checking WHERE `name`='Sam';

# 2.从支票账户余额中减去200美元。
UPDATE checking SET money = money-200 WHERE `name`='Sam';

# 3.在储蓄帐户余额中增加200美元。
UPDATE savings SET money = money+200 WHERE `name`='Sam';

# 提交事务
COMMIT;

# 回滚；出现错误
ROLLBACK;

---

2.3 事务四大特性(简称ACID)

• 原子性(Atomicity)

  一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

• 一致性(Consistency)

  数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

• 隔离性(Isolation)

  通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
  
  
  

  两边的数据都COMMIT之后数据保持一致，没有COMMIT之前，这些修改的数据保存在内存中，并不是保存在硬盘之中。

• 持久性(Durability)

  一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

3. 事务状态

我们现在知道事务是一个抽象的概念，它其实对应着一个或多个数据库操作，设计数据库的大叔根据这些操作所执行的不同阶段把事务大致上划分成了这么几个状态：

• 活动的（active）

• • 事务对应的数据库操作正在执行过程中时，我们就说该事务处在活动的状态。

• 部分提交的（partially committed）

• • 当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处在部分提交的状态。

• 失败的（failed）

• • 当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在失败的状态。

• 中止的（aborted）

• • 如果事务执行了半截而变为失败的状态，当狗哥账户的钱被扣除，但是猫爷账户的钱没有增加时遇到了错误，从而当前事务处在了失败的状态，那么就需要把已经修改的狗哥账户余额调整为未转账之前的金额，换句话说，就是要撤销失败事务对当前数据库造成的影响。书面一点的话，我们把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处在了中止的状态。

• 提交的（committed）

• • 当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，我们就可以说该事务处在了提交的状态。

随着事务对应的数据库操作执行到不同阶段，事务的状态也在不断变化，一个基本的状态转换图如下所示

4. 事务命令

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

4.1 开启事务，命令如下

• 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

begin;
或者
start transaction;

4.2 提交事务，命令如下

• 将缓存中的数据变更维护到物理表中

commit;

4.3 回滚事务，命令如下

• 放弃缓存中变更的数据

rollback;

---

5. start transaction的其他用法

名称	代码
只读事务	start transaction read only;
读写事务（默认）	start transaction read write;

6. MySQL 自动提交

show variabls like 'autocommit'; 查看自动提交

set autocommit=off; 关闭自动提交

关掉之后需要commit;进行提交。

---

7. 保存点

如果你开启了一个事务，并且已经敲了很多语句，忽然发现上一条语句有点问题，你只好使用ROLLBACK语句来让数据库状态恢复到事务执行之前的样子，然后一切从头再来，总有一种一夜回到解放前的感觉。所以设计数据库的大神们提出了一个保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。

SAVEPOINT 保存点名称;

当我们想回滚到某个保存点时，可以使用下边这个语句（下边语句中的单词WORK和SAVEPOINT是可有可无的）：

ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

不过如果ROLLBACK语句后边不跟随保存点名称的话，会直接回滚到事务执行之前的状态。

如果我们想删除某个保存点，可以使用这个语句：

RELEASE SAVEPOINT 保存点名称;

• 注意
  • 修改数据的命令会自动的触发事务，包括insert、update、delete
  • 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

---

8. 事务练习-模拟银行转账

-- 创建表
CREATE TABLE `money` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 添加数据
insert into money values(1,100),(2,200),(3,0);

---

9. 事务的隔离级别

9.1隔离级别（ISOLATION LEVEL）

• 隔离性其实比想象要复杂。在SQL中定义了四种隔离的级别，每一种隔离级别都规定了一个事务中的修改，哪些是在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常来说能承受更高的并发，系统的开销也会更小。

9.2 查看当前事物级别

SELECT @@tx_isolation;

9.3 设置mysql的隔离级别

基本语法

set session transaction isolation level 设置事务隔离级别

9.4 READ UNCOMMITTED（未提交读）

• 在READ UNCOMMITTED级别，事务的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别的隔离会导致很多问题，虽然在性能方面是最优的，但是缺乏其他级别的很多好处，所以这种隔离的级别很少在实际中应用。

• READ UNCOMMITTED实践 开启两个MySQL SESSION，并将MySQL的默认隔离级别设置为READ UNCOMMITTED

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

9.5 READ COMMITTED（读已提交）

• 大多数数据库系统默认的隔离级别都是READ COMMITTED(但MySQL不是)，"读已提交"简单的定义：一个事务只能看见已经提交的事务的修改结果。**换句话说，一个事务从开启事务到提交事务之前，对其他事务都是不可见的，因此在同一个事务中的两次相同查询结果可能不一样。**故这种隔离级别有时候也叫不可重复读（NONREPEATABLE READ）。

• READ COMMITTED 实践

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

9.6 REPEATABLE READ（可重复读）

• "可重复读"是MySQL的默认事务隔离级别。REPEATABLE READ解决了脏读的问题，该级别保证了在同一次事务中多次查询相同的语句结果是一致的。但是"可重复读"隔离级别无法避免产生幻行（Phantom Row）的问题，MySQL的InnoDB引擎通过多版本并发控制（MVCC，Multiversion Concurrency Controller）解决了幻读的问题。

• REPEATABLE READ 产生幻行的实践

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• 从上面GIF图显示的过程我们可以看到，最后SESSION A查询语句的结果只有一条id为1的数据，但是我们在插入id=2的数据的时候产生了报错

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

• 从报错中很容易就能看到是因为id=2的行已经存在了，前面读取行数据的结果就是幻读。

• REPEATABLE READ 实践

9.7 SERIALIZABLE（可串行化）

• SERIALIZABLE是最高的隔离级别，它通常通过强制事务串行，避免了前面说的幻读问题。简单来说，"可串行化"会在读取的每一行数据上都加锁，所以可能会导致大量的锁等待和超时问题，所以在实际的生产环境中也很少会用到这个隔离级别，只有在非常需要确保数据的一致性切可以接受没有并发的情况下，才会考虑使用这个隔离级别。

• SERIALIZABLE实践

• 从上面的过程我们可以看到，"可串行化"是通过对每一行数据都加锁的方式来避免幻行问题，这种方式效率非常的低，很容易造成较长时间的锁等待。

9.8 对比

隔离级别	脏读	不可重复读	幻影读
未提交读	√	√	√
提交读	×	√	√
可重复读	×	×	√
可串行化	×	×	×

9.9 好的事务习惯

• 循环写入的情况，如果循环次数不是太多，建议在循环前开启一个事务，循环结束后统一提交。
• 优化事务里的语句顺序，减少锁时间。
• 创建事务之前，关注事务隔离级别。
• 不在事务中混合使用存储引擎