來自拉鉤教育-JAVA就業(yè)集訓(xùn)營

1.DQL操作單表

2. SQL約束

3.數(shù)據(jù)庫事務(wù)

----------------------------------------------------------

1.DQL操作單表

1.1 排序

• 通過 ORDER BY 子句,可以將查詢出的結(jié)果進行排序(排序只是顯示效果,不會影響真實數(shù)據(jù))

ASC 表示升序排序(默認)
DESC 表示降序排序

1.2.1 排序方式

1. 單列排序
   只按照某一個字段進行排序, 就是單列排序

需求1:
使用 salary 字段,對emp 表數(shù)據(jù)進行排序 (升序/降序)

 # 默認升序排序 ASC
 SELECT * FROM emp ORDER BY salary;

 # 降序排序
 SELECT * FROM emp ORDER BY salary DESC;

2. 組合排序
   同時對多個字段進行排序, 如果第一個字段相同 就按照第二個字段進行排序,以此類推

需求2:
在薪水排序的基礎(chǔ)上,再使用id進行排序, 如果薪水相同就以id 做降序排序

 # 組合排序
 SELECT * FROM emp ORDER BY salary DESC, eid DESC;

1.3 聚合函數(shù)

之前我們做的查詢都是橫向查詢加矛，它們都是根據(jù)條件一行一行的進行判斷鉴未，而使用聚合函數(shù)查詢是縱向查詢谈山，它是對某一列的值進行計算闪彼，然后返回一個單一的值(另外聚合函數(shù)會忽略null空值胯究。)淌哟；

語法結(jié)構(gòu)
SELECT 聚合函數(shù)(字段名) FROM 表名;

5個常用的聚合函數(shù)

需求1:

1 查詢員工的總數(shù)
2 查看員工總薪水岸蜗、最高薪水埃难、最小薪水擅腰、薪水的平均值
3 查詢薪水大于4000員工的個數(shù)
4 查詢部門為'教學(xué)部'的所有員工的個數(shù)
5 查詢部門為'市場部'所有員工的平均薪水

SQL實現(xiàn)

 #1 查詢員工的總數(shù)
 -- 統(tǒng)計表中的記錄條數(shù) 使用 count()
 SELECT COUNT(eid) FROM emp; -- 使用某一個字段
 SELECT COUNT(*) FROM emp; -- 使用 *
 SELECT COUNT(1) FROM emp; -- 使用 1,與 * 效果一樣

 -- 下面這條SQL 得到的總條數(shù)不準確,因為count函數(shù)忽略了空值
 -- 所以使用時注意不要使用帶有null的列進行統(tǒng)計
 SELECT COUNT(dept_name) FROM emp;

#2 查看員工總薪水蟋恬、最高薪水、最小薪水趁冈、薪水的平均值
 -- sum函數(shù)求和, max函數(shù)求最大, min函數(shù)求最小, avg函數(shù)求平均值
 SELECT
 SUM(salary) AS '總薪水',
 MAX(salary) AS '最高薪水',
 MIN(salary) AS '最低薪水',
 AVG(salary) AS '平均薪水'
 FROM emp;

 #3 查詢薪水大于4000員工的個數(shù)
 SELECT COUNT(*) FROM emp WHERE salary > 4000;

 #4 查詢部門為'教學(xué)部'的所有員工的個數(shù)
 SELECT COUNT(*) FROM emp WHERE dept_name = '教學(xué)部';

 #5 查詢部門為'市場部'所有員工的平均薪水
 SELECT
 AVG(salary) AS '市場部平均薪資'
 FROM emp
 WHERE dept_name = '市場部';

1.4 分組

分組查詢指的是使用 GROUP BY 語句,對查詢的信息進行分組,相同數(shù)據(jù)作為一組

語法格式
SELECT 分組字段/聚合函數(shù) FROM 表名 GROUP BY 分組字段 [HAVING 條件];

• 需求1:通過性別字段 進行分組

 #   按照性別進行分組
 select * from emp group by sex  --注意這樣分組并沒有什么意義

分析group by是如何分組的

image.png

注意:
分組時可以查詢要分組的字段, 或者使用聚合函數(shù)進行統(tǒng)計操作.查詢其他字段沒有意義

• 需求2: 通過性別字段 進行分組,求各組的平均薪資

  select sex , avg(salary) '平均薪資' from emp group by sex

• 需求3

1.查詢所有部門信息
2.查詢每個部門的平均薪資
3.查詢每個部門的平均薪資, 部門名稱不能為null

 #1. 查詢有幾個部門
 SELECT dept_name AS '部門名稱' FROM emp GROUP BY dept_name;

 #2.查詢每個部門的平均薪資
 SELECT
 dept_name AS '部門名稱',
 AVG(salary) AS '平均薪資'
 FROM emp GROUP BY dept_name;

 #3.查詢每個部門的平均薪資, 部門名稱不能為null
 SELECT
 dept_name AS '部門名稱',
 AVG(salary) AS '平均薪資'
 FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;

• 需求4 查詢平均薪資大于6000的部門.

分析:
a. 需要在分組后,對數(shù)據(jù)進行過濾,使用 關(guān)鍵字 hiving
b.分組操作中的having子語句歼争，是用于在分組后對數(shù)據(jù)進行過濾的，作用類似于where條件渗勘。

  # 查詢平均薪資大于6000的部門
 -- 需要在分組后再次進行過濾,使用 having
 SELECT
 dept_name ,
 AVG(salary)
 FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING 
 AVG(salary) > 6000 ;

where 與 having的區(qū)別

1.5 limit關(guān)鍵字

limit 關(guān)鍵字的作用

• limit是限制的意思,用于 限制返回的查詢結(jié)果的行數(shù) (可以通過limit指定查詢多少行數(shù)據(jù))
• limit 語法是 MySql的方言,用來完成分頁

語法結(jié)構(gòu)
SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

• 需求1:

查詢emp表中的前 5條數(shù)據(jù)
查詢emp表中 從第4條開始,查詢6條

 #查詢emp表中的前5條數(shù)據(jù)
 # 參數(shù)1 起始值,默認是0 , 參數(shù)2 要查詢的條數(shù)

 select * from emp limit 5;
 select * from emp limit 0,5;

 #查詢emp表中 從第4條開始 查詢6條
 # 起始值默認是從0開始的
 select * from emp limit 3,6;

• 需求2: 分頁操作 每頁顯示3條數(shù)據(jù)

 #分頁操作 每頁顯示3條數(shù)據(jù)
 select * from emp limit 0,3; -- 第1頁
 select * from emp limit 3,3; -- 第2頁 2-1=1 1*3=3
 select * from emp limit 6,3; -- 第三頁
 # 分頁公式 起始索引 = (當(dāng)前頁 - 1) * 每頁條數(shù)
 # limit是MySql中的方言

2. SQL約束

1. 約束的作用:
   對表中的數(shù)據(jù)進行進一步的限制沐绒，從而保證數(shù)據(jù)的正確性、有效性旺坠、完整性.違反約束的不正確數(shù)據(jù),將無法插入到表中

2. 常見的約束

2.1 主鍵約束

特點: 不可重復(fù) 唯一 非空
作用: 用來表示數(shù)據(jù)庫中的每一條記錄

3.1.1 添加主鍵約束

語法格式
`字段名 字段類型 primary key

1. 需求1:創(chuàng)建一個帶主鍵的表

 # 方式1 創(chuàng)建一個帶主鍵的表
 create table emp2(
  eid int  primary key ,
  ename  varchar(20),
  sex  char(1)
);

# 方式2 創(chuàng)建一個帶主鍵的表
 create table emp2(
  eid  int,
  ename  varchar(20),
  sex  char(1),
  # 指定主鍵為 eid字段  
  primary key(eid)
)
#  方式3 創(chuàng)建一個帶主鍵的表
 CREATE TABLE emp2(
  eid INT ,
  ename VARCHAR(20),
  sex CHAR(1)
)
 # 創(chuàng)建的時候不指定主鍵,然后通過 DDL語句進行設(shè)置
 alert table emp2 add primary key(eid);

image.png

2. 測試主鍵的唯一性 非空性

 # 正常插入一條數(shù)據(jù)
 insert into emp2 values(1,'宋江','男');

 # 插入一條數(shù)據(jù),主鍵為空
 # Column 'eid' cannot be null 主鍵不能為空
 insert into emp2 values(NULL,'李逵','男');

 # 插入一條數(shù)據(jù),主鍵為 1
 # Duplicate entry '1' for key 'PRIMARY' 主鍵不能重復(fù)
 INSERT INTO emp2 VALUES(1,'孫二娘','女');

3. 哪些字段可以作為主鍵 ?
   通常針對業(yè)務(wù)去設(shè)計主鍵,每張表都設(shè)計一個主鍵id
   主鍵是給數(shù)據(jù)庫和程序使用的,跟最終的客戶無關(guān),所以主鍵沒有意義沒有關(guān)系,只要能夠保證不重復(fù)就好,比如 身份證就可以作為主鍵.

3.1.2 刪除主鍵約束

• 刪除 表中的主鍵約束 (了解)
  alert table emp2 drop primary key(eid);

3.1.3 主鍵的自增

注: 主鍵如果讓我們自己添加很有可能重復(fù),我們通常希望在每次插入新記錄時,數(shù)據(jù)庫自動生成主鍵字段的值.

 關(guān)鍵字:
AUTO_INCREMENT 表示自動增長(字段類型必須是整數(shù)類型)

1.創(chuàng)建主鍵自增的表

 create table emp2(
  eid int primary key auto_increment,
  ename varchar(20),
  sex char(1)
)

2.添加數(shù)據(jù) 觀察主鍵的自增

insert into emp2(ename,sex) VALUES('張三','男');
insert into emp2(ename,sex) VALUES('李四','男');
insert into emp2 VALUES(NULL,'翠花','女');
insert into emp2 VALUES(NULL,'艷秋','女');

image.png

3.1.4 修改主鍵自增的起始值

默認地 AUTO_INCREMENT 的開始值是 1乔遮，如果希望修改起始值,請使用下面的方式

 # 創(chuàng)建主鍵自增的表,自定義自增其實值
 create table emp2(
  eid int primary key,
  ename varchar(20),
  sex  char(1)
)auto_increment = 100;

 -- 插入數(shù)據(jù),觀察主鍵的起始值
 insert into emp2(ename,sex) values('張百萬','男');
 insert into emp2(ename,sex) values('艷秋','女');

image.png

3.1.5 DELETE和TRUNCATE對自增長的影響

• 刪除表中所有數(shù)據(jù)有兩種方式

測試1: delete 刪除表中所有數(shù)據(jù)

 # 目前最后的主鍵值是 101
 select * from emp2;
 # delete 刪除表中數(shù)據(jù),對自增沒有影響
 delete from emp2;
 # 插入數(shù)據(jù) 查看主鍵
 insert into emp2(ename,sex) values('張百萬','男');
 insert into emp2(ename,sex) values('艷秋','女');

image.png

測試2: truncate刪除 表中數(shù)據(jù)

# 使用 truncate 刪除表中所有數(shù)據(jù),
truncate table emp2;
# 插入數(shù)據(jù) 查看主鍵
insert into emp2(ename,sex) values('張百萬','男');
insert into emp2(ename,sex) values('艷秋','女');

image.png

2.2 非空約束

• 非空約束的特點: 某一列不予許為空
  語法格式
  字段名 字段類型 not null

需求1:為ename字段添加非空字段

 create emp2(
  eid int primary key auto_increment,
  #添加非空約束,ename字段不能為空
  ename varchar(20) not null,
  sex char(1)
);

image.png

2.3 唯一約束

唯一約束的特點: 表中的某一列的值不能重復(fù)( 對null不做唯一的判斷 )
字段名 字段類型 unique
1.添加唯一約束

 create table emp3(
    eid int primary key auto_increment,
    ename varchar(20)  unique,
    sex char(1)
);

2.測試唯一約束

# 測試唯一約束 添加一條數(shù)據(jù)
insert into emp3 (ename,sex) values('張百萬','男');

# 添加一條 ename重復(fù)的 數(shù)據(jù)
# Duplicate entry '張百萬' for key 'ename' ename不能重復(fù)
insert into emp3 (ename,sex) vaules('張百萬','女');

image.png

2.4 外鍵約束

FOREIGN KEY 表示外鍵約束，將在多表中學(xué)習(xí)取刃。

2.5 默認值

默認值約束 用來指定某列的默認值
語法格式
字段名 字段類型 default 默認值

1. 創(chuàng)建emp4表, 性別字段默認 女

# 創(chuàng)建帶有默認值的表
  create table emp4(
  eid int primary key auto_increment,
  # 為ename 字段添加默認值
  ename varchar(20) default '奧利給',
  sex char(1)
);

2. 測試 添加數(shù)據(jù)使用默認值

 # 添加數(shù)據(jù) 使用默認值
 insert into emp4(ename,sex) values (DEFAULT,'男');
 insert into emp4(sex) values ('女');
 # 不使用默認值
 insert into emp4(ename,sex) values('艷秋','女');

3.數(shù)據(jù)庫事務(wù)

3.1 什么是事務(wù)

事務(wù)是一個整體,由一條或者多條SQL 語句組成,這些SQL語句要么都執(zhí)行成功,要么都執(zhí)行失敗, 只要有一條SQL出現(xiàn)異常,整個操作就會回滾,整個業(yè)務(wù)執(zhí)行失敗

比如: 銀行的轉(zhuǎn)賬業(yè)務(wù),張三給李四轉(zhuǎn)賬500元 , 至少要操作兩次數(shù)據(jù)庫, 張三 -500, 李四 + 500,這中
間任何一步出現(xiàn)問題,整個操作就必須全部回滾, 這樣才能保證用戶和銀行都沒有損失.

3.2 模擬轉(zhuǎn)賬操作

1. 創(chuàng)建 賬戶表

# 創(chuàng)建賬戶表
 create table account(
   # 主鍵
   id int primary key auto_increment,
   # 姓名
   name varchar(10),
   # 余額
   money double
 );
 # 添加兩個用戶
 insert into account (name, money) values('tom', 1000), ('jack', 1000);

2. 模擬tom 給 jack 轉(zhuǎn) 500 元錢蹋肮，一個轉(zhuǎn)賬的業(yè)務(wù)操作最少要執(zhí)行下面的 2 條語句：

 #tom賬號 -500
  update account set money = 500 where name = 'tom';
 #jack賬號 +500
  update account set money = 1500 where name = 'jack';

注:

假設(shè)當(dāng)tom 賬號上 -500 元,服務(wù)器崩潰了。jack 的賬號并沒有+500 元璧疗，數(shù)據(jù)就出現(xiàn)問題了坯辩。
我們要保證整個事務(wù)執(zhí)行的完整性,要么都成功, 要么都失敗. 這個時候我們就要學(xué)習(xí)如何操作事務(wù).

3.3 MySQL事務(wù)操作

• MySQL中可以有兩種方式進行事務(wù)的操作
  1.手動提交事務(wù)
  2.自動提交事務(wù)

3.3.1 手動提交事務(wù)

3.3.1.1語法格式

• start tansaction
  這個語句顯示的標記了一個事務(wù)的起始點。
• commit
  表示提交事務(wù),即提交事務(wù)的所有操作,具體的說,就是將事務(wù)中所有對數(shù)據(jù)庫的更新都寫到磁盤上的數(shù)據(jù)庫中,事務(wù)正常結(jié)束崩侠。
• rollback
  表示撤銷事務(wù),即在事務(wù)運行的過程中發(fā)生了某種故障,事務(wù)不能繼續(xù)執(zhí)行,系統(tǒng)將事務(wù)種對數(shù)據(jù)的所有已完成的操作全部撤銷,回滾到事務(wù)開始的狀態(tài)

3.3.1.2 手動提交事務(wù)流程

執(zhí)行成功的情況： 開啟事務(wù) -> 執(zhí)行多條 SQL 語句 -> 成功提交事務(wù)
執(zhí)行失敗的情況： 開啟事務(wù) -> 執(zhí)行多條 SQL 語句 -> 事務(wù)的回滾

image.png

3.3.1.3 成功案例 演示

 #開啟事務(wù)
  start transaction;
 #tom賬號 -500
  update account set money = 500 where name = 'tom';
 #jack賬號 +500
  update account set money = 1500 where name = 'jack';

此時我們發(fā)現(xiàn)數(shù)據(jù)并沒有改變

在控制臺執(zhí)行 commit 提交事務(wù)

 #提交
 commit;

發(fā)現(xiàn)數(shù)據(jù)在事務(wù)提交之后,發(fā)生改變

image.png

3.3.1.4 事務(wù)回滾演示

• 如果事務(wù)中漆魔，有某條sql語句執(zhí)行時報錯了，我們沒有手動的commit却音，那整個事務(wù)會自動回滾
  1.命令行 開啟事務(wù)

 #開啟事務(wù)
 start transaction

2.插入兩條數(shù)據(jù)

 insert into account vaules(NULL,'張百萬',3000);
 insert into account values(NULL,'有財',3500);

3. 不去提交事務(wù) 直接關(guān)閉窗口,發(fā)生回滾操作,數(shù)據(jù)沒有改變

   
   
   zhu
   
   

   注意:

如果事務(wù)中 SQL 語句沒有問題改抡，commit 提交事務(wù)，會對數(shù)據(jù)庫數(shù)據(jù)的數(shù)據(jù)進行改變系瓢。 如果事務(wù)中 SQL 語句有問題阿纤，rollback 回滾事務(wù)，會回退到開啟事務(wù)時的狀態(tài)八拱。

3.3.2 自動提交事務(wù)

• MySQL 默認每一條 DML(增刪改)語句都是一個單獨的事務(wù)阵赠，每條語句都會自動開啟一個事務(wù)涯塔，語句執(zhí)行完畢 自動提交事務(wù)，MySQL 默認開始自動提交事務(wù)
• MySQL默認是自動提交事務(wù)

3.3.2.1 自動提交事務(wù)演示

1.將tom賬戶金額 +500

image.png

2. 查看數(shù)據(jù)庫：發(fā)現(xiàn)數(shù)據(jù)已經(jīng)改變

   
   
   image.png

3.3.2.2 取消自動提交

• MySQL默認是自動提交事務(wù),設(shè)置為手動提交
  1.登錄mysql 查看autocommit的狀態(tài)

 show variables like 'autocommit';

image.png

2. 把 autocommit 改成 off;
   set @@autocommit = off;
   
   image.png
3. 再次修改,需要提交之后才生效

• 將jack 賬戶金額 -500元

 # 選擇數(shù)據(jù)庫
 use db2;
 # 修改數(shù)據(jù)
 update account set money = money - 500 where name = 'jack';
 # 手動提交
 commit;

3.4 事務(wù)的四大特性 ACID

3.5.1 數(shù)據(jù)并發(fā)訪問

一個數(shù)據(jù)庫可能擁有多個訪問客戶端,這些客戶端都可以并發(fā)方式訪問數(shù)據(jù)庫. 數(shù)據(jù)庫的相同數(shù)據(jù)可能被多個事務(wù)同時訪問,如果不采取隔離措施,就會導(dǎo)致各種問題, 破壞數(shù)據(jù)的完整性

3.5.2 并發(fā)訪問會產(chǎn)生的問題

事務(wù)在操作時的理想狀態(tài)： 所有的事務(wù)之間保持隔離尔觉，互不影響。因為并發(fā)操作芥吟，多個用戶同時訪問同一個 數(shù)據(jù)侦铜。可能引發(fā)并發(fā)訪問的問題

3.5.3 四種隔離級別

通過設(shè)置隔離級別,可以防止上面的三種并發(fā)問題.
MySQL數(shù)據(jù)庫有四種隔離級別 上面的級別最低钟鸵，下面的級別最高钉稍。

3.5.4隔離級別相關(guān)的命令

1.查看隔離級別

 select @@tx_isolation;

image.png

2.設(shè)置事務(wù)隔離級別，需要退出 MySQL 再重新登錄才能看到隔離級別的變化

 set global transaction isolation level 隔離級別
 read uncommitted 讀未提交
 read committed 讀已提交
 repeatable read 可重復(fù)讀
 serializable 串行化

例如: 修改隔離級別為 讀未提交

 set global transaction isolation level read uncommitted;

3.6 隔離性問題演示

臟讀: 一個事務(wù)讀取到了另一個事務(wù)中尚未提交的數(shù)據(jù)

1. 打開窗口登錄 MySQL棺耍，設(shè)置全局的隔離級別為最低 讀未提交

set global transaction isolation level read uncommitted;

image.png

2. 關(guān)閉窗口,開一個新的窗口A ,再次查詢隔離級別

• 開啟新的 窗口A

  
  
  image.png
• 查詢隔離級別

select @@tx_isolation;

image.png

3. 再開啟一個新的窗口 B

• 登錄數(shù)據(jù)庫

  
  
  image.png
• 開啟事務(wù)

 start transaction;

• 查詢

select * from account;

image.png

1.A窗口執(zhí)行

• 開啟事務(wù)

start transaction;

• 執(zhí)行修改操作

 # tom賬戶 -500元
 update account set money = money - 500 where name = 'tom';
 # jack賬戶 + 500元
 update account set money = money + 500 where name= 'jack';

2. B 窗口查詢數(shù)據(jù)

• 查詢賬戶信息

select * from account;

image.png

3. A窗口轉(zhuǎn)賬異常,進行回滾

rollback;

4. B 窗口再次查詢 賬戶

 select * from account;

image.png

3.6.2 解決臟讀問題

臟讀非常危險的贡未，比如張三向李四購買商品，張三開啟事務(wù)蒙袍，向李四賬號轉(zhuǎn)入 500 塊俊卤，然后打電話給李四說錢 已經(jīng)轉(zhuǎn)了。李四一查詢錢到賬了左敌，發(fā)貨給張三瘾蛋。張三收到貨后回滾事務(wù)，李四的再查看錢沒了矫限。

• 解決方案
  將全局的隔離級別進行提升為: read committed

1. 在 A 窗口設(shè)置全局的隔離級別為 read committed

 set global transaction isolation level read committed;

2. 重新開啟A窗口, 查看設(shè)置是否成功.

select @@tx_isolation;

3. 開啟B 窗口, A 和 B 窗口選擇數(shù)據(jù)庫后, 都開啟事務(wù)

   
   
   image.png

4. A 窗口 只是更新兩個人的賬戶, 不提交事務(wù)

 # tom賬戶 -500元
 update account set money = money - 500 where name = 'tom';
 # jack賬戶 + 500元
 update account set money = money + 500 where name = 'jack';

5. B 窗口進行查詢,沒有查詢到未提交的數(shù)據(jù)

select * from account;

image.png

6. A窗口commit提交數(shù)據(jù)

 commit;

7.B窗口查看數(shù)據(jù)

 select * from account;

image.png

3.6.3 不可重復(fù)讀演示

不可重復(fù)讀: 同一個事務(wù)中,進行查詢操作,但是每次讀取的數(shù)據(jù)內(nèi)容是不一樣的

1. 恢復(fù)數(shù)據(jù) (把數(shù)據(jù)改回初始狀態(tài))

   
   
   image.png
2. 打開兩個 窗口A 和 窗口B,選擇數(shù)據(jù)庫后 開啟事務(wù)

 use db2;
 start transaction;

image.png

1. B 窗口開啟事務(wù)后, 先進行一次數(shù)據(jù)查詢

select * from account;

image.png

2. 在 A 窗口開啟事務(wù)后哺哼，將用戶tom的賬戶 + 500 ,然后提交事務(wù)

 # 修改數(shù)據(jù)
 update account set money = money + 500 where name = 'tom';
 # 提交事務(wù)
 commit;

image.png

1. B 窗口再次查詢數(shù)據(jù)

   
   
   image.png

• 兩次查詢輸出的結(jié)果不同，到底哪次是對的叼风？
• 不知道以哪次為準取董。 很多人認為這種情況就對了，無須困惑无宿， 當(dāng)然是后面的為準茵汰。
  我們可以考慮這樣一種情況:

比如銀行程序需要將查詢結(jié)果分別輸出到電腦屏幕和發(fā)短信給客 戶，結(jié)果在一個事務(wù)
中針對不同的輸出目的地進行的兩次查詢不一致孽鸡，導(dǎo)致文件和屏幕中的結(jié)果不一致蹂午，銀
行工作 人員就不知道以哪個為準了

3.6.4 解決不可重復(fù)讀問題

• 將全局的隔離級別進行提升為： repeatable read

1. 恢復(fù)數(shù)據(jù)

 update account set money = 1000;

2. 打開A 窗口, 設(shè)置隔離級別為：repeatable read

 # 查看事務(wù)隔離級別
select @@tx_isolation;
# 設(shè)置事務(wù)隔離級別為 repeatable read
set global transaction isolation level repeatable read;

image.png

3. 重新開啟 A,B 窗口 選擇數(shù)據(jù)庫 ,同時開啟事務(wù)

   
   
   image.png
4. B 窗口事務(wù) 先進行第一次查詢

 select * from account;

image.png

5. A 窗口更新數(shù)據(jù), 然后提交事務(wù)

 # 修改數(shù)據(jù)
update account set money = money + 500 where name = 'tom';
# 提交事務(wù)
commit;

6. B 窗口 再次查詢

 select * from account;

image.png

• 同一個事務(wù)中為了保證多次查詢數(shù)據(jù)一致栏豺，必須使用 repeatable read 隔離級別

3.6.5 幻讀演示

幻讀: select 某記錄是否存在，不存在豆胸，準備插入此記錄奥洼，但執(zhí)行 insert 時發(fā)現(xiàn)此記錄已存在，無法插入晚胡，此時就發(fā)生了幻讀灵奖。

1. 打開 A B 窗口, 選擇數(shù)據(jù)庫 開啟事務(wù)

   
   
   image.png
2. A 窗口 先執(zhí)行一次查詢操作

 # 假設(shè)要再添加一條id為3的 數(shù)據(jù),在添加之前先判斷是否存在
select * from account where id = 3;

image.png

3. B 窗口 插入一條數(shù)據(jù) 提交事務(wù)

insert into account values(3,'lucy',1000);
commit;

image.png

4. A 窗口執(zhí)行 插入操作, 發(fā)現(xiàn)報錯. 出現(xiàn)幻讀

   
   
   image.png

• 我剛才讀到的結(jié)果應(yīng)該可以支持我這樣操作才對啊，為什么現(xiàn)在不可以

3.6.6 解決幻讀問題

• 將事務(wù)隔離級別設(shè)置到最高 SERIALIZABLE 估盘，以擋住幻讀的發(fā)生

如果一個事務(wù)瓷患，使用了SERIALIZABLE——可串行化隔離級別時，在這個事務(wù)沒有被提交之前 , 其他的線程遣妥，只能等到當(dāng)前操作完成之后擅编，才能進行操作，這樣會非常耗時燥透，而且沙咏，影響數(shù)據(jù)庫的性能辨图，數(shù)據(jù)庫不會使用這種隔離級別

1. 恢復(fù)數(shù)據(jù)

 delete from account where id = 3;

2. 打開A 窗口 將數(shù)據(jù)隔離級別提升到最高

 set global transaction isolation level SERIALIZABLE;

image.png

3. 打開 A B 窗口, 選擇數(shù)據(jù)庫 開啟事務(wù)

   
   
   image.png
4. A 窗口 先執(zhí)行一次查詢操作

SELECT * FROM account WHERE id = 3;

5. B 窗口插入一條數(shù)據(jù)

 insert into account values(3,'lucy',1000);

image.png

6. A 窗口執(zhí)行 插入操作, 提交事務(wù) 數(shù)據(jù)插入成功.

insert into account values(3,'lucy',1000);
commit;

image.png

7. B 窗口在 A窗口提交事務(wù)之后, 再執(zhí)行,但是主鍵沖突出現(xiàn)錯誤

   
   
   image.png

• 總結(jié):
  serializable 串行化可以徹底解決幻讀,但是 事務(wù)只能排隊執(zhí)行,嚴重影響效率,
  數(shù)據(jù)庫不會使用這種隔離級別