共計 9516 個字符，預計需要花費 24 分鐘才能閱讀完成。
本篇內容介紹了“mysql 設計概念、多表查詢和事務操作實例分析”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
數據庫設計簡介
1. 數據庫設計概念
數據庫設計就是根據業務系統具體需求，結合我們所選用的 DBMS，為這個業務系統構造出最優的數據存儲模型。
建立數據庫中的表結構以及表與表之間的關聯關系的過程。
有哪些表？表里有哪些字段？表和表之間有什么關系？
2. 數據庫設計步驟
需求分析：數據庫是什么？數據具體有哪些屬性？數據與屬性的特點是什么？
邏輯分析：通過 ER 圖對數據庫進行邏輯建模，不需要考慮我們所選用的數據庫管理系統。
物理設計：根據數據庫自身的特點把邏輯設計轉換為物理設計。
維護設計：對新的需求進行建表和對表的優化。
3. 表關系簡介
在真實的開發中，一個項目中的數據，一般都會保存在同一個數據庫中，但是不同的數據需要保存在不同的數據表中。這時不能把所有的數據都保存在同一張表中。
那么在設計保存數據的數據表時，我們就要根據具體的數據進行分析，然后把同一類數據保存在同一張表中，不同的數據進行分表處理。
數據之間必然會有一定的聯系，我們把不同的數據保存在不同的數據表中之后，同時還要在數據表中維護這些數據之間的關系。這時就會導致表和表之間必然會有一定的聯系。這時要求設計表的人員，就需要考慮不同表之間的具體關系。
在數據庫中，表總共存在三種關系，真實的數據表之間的關系：多對多關系、一對多（多對一）、一對一(極少)，(一對一關系就是我們之前學習的 Map 集合的 key-value 關系)
表關系（多對多）
1. 多對多
如：訂單 和 商品
一個商品對應多個訂單，一個訂單對應多個商品
實現方式：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵
說明：如果兩張表是多對多的關系，需要創建第三張表，并在第三張表中增加兩列，引入其他兩張表的主鍵作為自己的外鍵。
2. 外鍵約束
外鍵用來讓兩個表的數據之間建立鏈接，保證數據的一致性和完整性（例如上述多對多中的訂單商品表來維護訂單表和商品表之間的關系）
使用之間表的目的是維護兩表之間多對多的關系：中間表插入的數據，必須在多對多的主表中存在，如果主表的記錄在中間表中維護了關系，就不能隨意的刪除。如果要刪除，必須先要刪除中間表關聯的數據
3. 外鍵約束語法
-- 關鍵字解釋：constraint: 添加約束，可以不寫 foreign key(當前表中的列名): 將某個字段作為外鍵 references 被引用表名(被引用表的列名) : 外鍵引用主表的主鍵 -- 創建表時添加外鍵約束 CREATE TABLE 表名(
列名 數據類型,
… [CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵列名) REFERENCES 主表(主表列名) ); -- 建完表后添加外鍵約束 ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱);-- 刪除約束 ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;4. 創建外鍵約束
-- 訂單表 CREATE TABLE tb_orders(
id int primary key auto_increment,
payment double(10, 2),
payment_type TINYINT, -- 0 微信支付 1 支付寶支付
status TINYINT -- 0 未付款 1 已經支付);-- 商品表 CREATE TABLE tb_goods(
id int primary key auto_increment,
title varchar(100),
price double(10, 2));-- 訂單商品中間表 CREATE TABLE tb_order_goods(
id int primary key auto_increment,
order_id int, -- 外鍵，來自于訂單表的主鍵
goods_id int, -- 外鍵，來自于商品表的主鍵
count int, -- 購買商品數量
foreign key(order_id) references tb_orders(id),
foreign key(goods_id) references tb_goods(id));5. 外鍵級聯
在修改和刪除主表的主鍵時，同時更新或刪除從表的外鍵值，稱為級聯操作
ON UPDATE CASCADE – 級聯更新，主鍵發生更新時，外鍵也會更新
ON DELETE CASCADE – 級聯刪除，主鍵發生刪除時，外鍵也會刪除
6. 總結
1. 為何要引用外鍵約束？
讓表的數據有效性，正確性。提高查詢效率。
2. 添加外鍵約束語法？
constraint 外鍵約束名 foreign key(當前表的字段名) references 主表(主鍵)
3. 有了外鍵約束操作數據注意事項?
要求添加數據需要先添加主表，然后添加從表。
要求刪除數據需要先刪除從表，然后再刪除主表。
表關系（一對多）
一對多（多對一）
如：部門表 和 員工表
一個部門對應多個員工，一個員工對應一個部門
實現方式：在多的一方建立外鍵，指向一的一方的主鍵
表關系之一對一
一對一
如：用戶和 用戶信息
一對一關系多用于表拆分，將一個實體中經常使用的字段放一張表，不經常使用的字段放另一張表，用于提升查詢性能
實現方式：在任意一方加入外鍵，關聯另一方主鍵，并且設置外鍵為唯一(UNIQUE)
多表查詢
準備數據
-- 價格 create table price(
id int primary key auto_increment,
price double);-- 水果 create table fruit(
id int primary key auto_increment,
name varchar(20) not null,
price_id int,
foreign key (price_id) references price (id));-- 數據 insert into pricevalues (1, 2.30);insert into pricevalues (2, 3.50);insert into pricevalues (4, null);insert into fruitvalues (1, 蘋果 , 1);insert into fruitvalues (2, 橘子 , 2);insert into fruitvalues (3, 香蕉 , null);笛卡爾積現象
1. 什么是笛卡爾積現象
笛卡爾積問題：把多張表放在一起，同時去查詢，會得到一個結果，而這結果并不是我們想要的數據，這個結果稱為笛卡爾積。
笛卡爾積缺點：查詢到的結果冗余了，里面有很多錯誤的數據，需要過濾。
多表查詢語法：select * from 表名 1, 表名 2;
需求：查詢兩張表中關于水果的信息，要顯示水果名稱和水果價格
表設計原則：將價格的主鍵作為水果的外鍵
-- 多表查詢語法(同時查詢多個表獲取到需要的數據)select * from 表名 1, 表名 2;-- 查詢價格(我們向查詢水果對應的價格, 需要將水果表和價格表同時進行查詢;)select * from fruit,price;查詢結果：
2. 笛卡爾積產生原因
fruit 表中的每一條記錄，都和 price 表中的每一條進行匹配連接。所得到的最終結果是：fruit 表中的條目數乘以 price 表中的數據的條目數。
將 fruit 表的每行記錄和 price 表的每行記錄組合的結果就是笛卡爾積
3. 如何避免笛卡爾積
解決上述查詢的方案：在查詢兩張表的同時添加條件進行過濾，比如 fruit 表的 id 和必須和 price 表的 id 相同
-- 條件過濾笛卡爾積 select * from fruit,price where fruit.price_id=price.id;內連接查詢
1. 什么是內連接
內連接查詢又稱為交集查詢，也就是查詢只顯示滿足條件的數據
2. 顯示內連接
顯示內連接：使用 INNER JOIN…ON 語句，可以省略 INNER 關鍵字
-- 語法核心 select * from 表名 1 inner join 表名 2 on 條件;-- 或者 select * from 表名 1 join 表名 2 on 條件;3. 隱式內連接
看不到 JOIN 關鍵字，條件使用 WHERE 指定
select 列名, 列名,... from 表名 1, 表名 2 where 表名 1. 列名 = 表名 2. 列名;4. 示例
查詢水果的價格
-- 隱式內連接 select * from fruit,price where fruit.price_id=price.id;-- 顯式內連接 select * from fruit inner join price on fruit.price_id=price.id;查詢蘋果的信息，顯示蘋果的 id，名字，價格
-- 方式 1select fruit.id, fruit.name, price.pricefrom fruit,
pricewhere fruit.price_id = price.id and fruit.name = 蘋果 -- 方式 2select fruit.id, fruit.name, price.pricefrom fruit inner join
price on fruit.price_id = price.id and fruit.name = 蘋果
5. 總結
1. 內連接作用？
過濾笛卡爾積
獲取兩表的交集部分(都滿足條件的部分)
2. 什么是隱式內連接和顯示內連接？
隱式內連接：看不到 JOIN：select 列名, 列名 ….from 表名 1, 表名 2 where 表名 1. 列名 = 表名 2. 列名;
顯示內連接：看得到 JOIN：select * from 表名 1 inner join 表名 2 on 條件;
3. 內連接查詢步驟？
1）確定查詢幾張表
2）確定表連接條件
3）根據需要在操作
外連接查詢
1. 左外連接
左表的記錄全部顯示出來
外表只會顯示符合搜索條件的記錄
語法格式：
select * from 表 1 left [outer] join 表 2 on 條件;說明：
left 關鍵字左邊的表定義為左表，left 關鍵字右邊的表定義為右表，查詢的內容以左表為主
如果左表有數據，而右表沒有數據對應的數據，仍然會把左表數據進行顯示
outer 關鍵字可以省略
練習：
不管能否查到水果對應價格，都要把水果顯示出來
-- 左外連接查詢 select * from fruit left outer join price on fruit.price_id=price.id;
2. 右外連接
右表的記錄全部表示出來
左表只會顯示符合搜索條件的記錄
語法格式：
select * from 表名 1 right [outer] join 表名 2 on 條件;說明：
right 關鍵字左邊的表定義為左表，right 關鍵字右邊的表定義為右表，查詢的內容以右表為主
如果右表沒有數據，而左表沒有對應的數據，仍然會把右表數據進行顯示
outer 關鍵字可以省略
練習：
不管能否查到價格對應的水果，都要把價格顯示出來
select * from fruit right outer join price on fruit.price_id=price.id;
總結：
1. 掌握左外連接查詢格式？
select * from 表 1 left outer join 表 2 on 條件;
表 1 看作為左表，表 2 看做為右表
2. 左外連接查詢特點？
在滿足要求的基礎上保證左表的數據全部顯示
3. 掌握右外連接查詢格式？
select * from 表 1 right outer join 表 2 on 條件;
4. 右外連接查詢特點？
在滿足要求的基礎上, 保證右表的數據全部顯示
嵌套查詢（子查詢）
1. 什么是子查詢
一條查詢語句結果作為另一條查詢語法一部分。
SELECT 查詢字段 FROM 表 WHERE 條件; 舉例：SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);說明：子查詢需要放在（）中
三種子查詢情況：單行單列、多行單列、多行多列。
2. 單行單列
子查詢結果是單列，在 WHERE 后面作為條件
SELECT 查詢字段 FROM 表 WHERE 字段 =（子查詢）;通常使用比較運算符:、=、、=、= 等
3. 多行單列
子查詢結果是多行單列，結果集類似于一個數組，在 WHERE 后面作為條件，父查詢使用 IN 運算符
-- IN 表示在數值中 SELECT 查詢字段 FROM 表 WHERE 字段 IN （子查詢）;4. 多行多列
子查詢結果是多列，在 FROM 后面作為表
SELECT 查詢字段 FROM （子查詢） 表別名 WHERE 條件;注意：子查詢作為表需要取別名，使用 as，可以省略，否則這張表沒用名稱無法訪問表中的字段
事務操作事務的概念
什么是事務
在實際的業務開發中，有些業務操作要多次訪問數據庫。一個業務要發送多條 SQL 語句給數據庫執行。需要將多次訪問數據庫的操作視為一個整體來執行，要么所有的 SQL 語句全部執行成功。如果其中有一條 SQL 語句失敗，就進行事務的回滾，所有的 SQL 語句全部執行失敗。
簡而言之，事務指的是邏輯上的一組操作, 組成這組操作的各個單元要么全都成功, 要么全都失敗。
事務作用：保證在一個事務中多次操作數據庫表中數據時，要么全都成功, 要么全都失敗。
事務的應用場景聲明
關于事務在實際中的應用場景：
假設我在淘寶買了一部手機，然后當我付完款，錢已經從我的賬戶中扣除。正當此時，淘寶轉賬系統宕機了，那么此時淘寶還沒有收到錢，而我的賬戶的錢已經減少了，這樣就會導致我作為買家錢已經付過，而賣家還沒有收到錢，他們不會發貨物給我。這樣做顯然是不合理。實際生活中是如果淘寶出問題，作為用戶的賬戶中錢是不應該減少的。這樣用戶就不會損失錢。
還有種情況，就是當我付完款之后，賣家看到我付款成功，然后直接發貨了，我如果有權限操作，我可以撤銷，這樣就會導致我的錢沒有減少，但是賣家已經發貨，同樣這種問題在實際生活中也是不允許出現的。
MySQL 中可以有兩種方式進行事務的操作：
手動提交事物：先開啟，在提交
自動提交事物（默認的）：即執行一條 sql 語句提交一次事物
數據準備
# 創建賬號表 create table account(
id int primary key auto_increment,
name varchar(20),
money double);# 初始化數據 insert into account values (null, a ,1000);insert into account values (null, b ,1000);
手動提交事務
手動提交事務有關的 sql 語句
SQL 語句描述 start transaction 開啟手動控制事物 commit 提交事物 rollback 回滾事物
手動提交事務使用步驟
開啟事務– 執行 SQL 語句– 成功– 提交事務
開啟事務– 執行 SQL 語句– 失敗– 回滾事務
演示案例：演示提交事務，a 給 b 轉賬 100 元
-- 1. 開啟事務 start transaction;-- 2. 執行 sql 語句 update account set money=money-100 where name= a update account set money=money+100 where name= b -- 3. 提交事務 commit;
案例演示：演示回滾事務，a 給 b 轉賬 100 元
-- 1. 開啟事務 start transaction;-- 2. 執行 sql 語句 update account set money=money-100 where name= a update account set money=money+100 where name= b -- 3. 回滾事務 rollback;
注意：
提交事務(commit)：事務提交之后，sql 語句對數據庫產生的操作才會被永久的保存
事務的回滾(rollback)：撤銷已經成功執行的 sql 語句，回到開啟事務之前的狀態
只要提交事務，那么數據就會長久保存了，就不能回滾事務了。即提交或者回滾事務都是代表結束當前事務的操作
自動提交事務
MySQL 的每一條 DML(增刪改)語句都是一個單獨的事務，每條語句都會自動開啟一個事務，執行完畢自動提交事務，MySQL 默認開始自動提交事務。自動提交，通過修改 mysql 全局變量 autocommit 進行控制。
通過以下命令可以查看當前 autocommit 模式
show variables like %commit%
設置自動提交的參數為 OFF
set autocommit = 0; -- 0:OFF 1:ON
案例演示
-- 自動提交事務：每條 sql 語句就是一個事務，那么執行一條 sql 語句就會提交一次事務 -- mysql 數據庫就是自動提交事務 -- a 給 b 轉賬 100 元 update account set money=money-100 where name= a update account set money=money+100 where name= b -- 查看 mysql 是否自動提交事務 -- autocommit 的值是 on 表示自動提交事務，值是 off 表示關閉自動提交事務show variables like %commit%-- 我們可以使用命令臨時設置 mysql 變為手動提交事務，即將自動提交事務關閉 -- 下次重新連接 mysql 依然是自動提交事務 set autocommit = 0; -- 0 表示關閉自動提交事務 1 表示開啟自動事務 update account set money=money-100 where name= a
注意：
1）MySql 默認自動提交。即執行一條 sql 語句提交一次事務。
2）設置 autocommit 為 off 狀態，只是臨時性的，下次重新連接 mysql，autocommit 依然變為 on 狀態。
3）如果設置 autocommit 為 off 狀態，那么當我們執行一條 sql 語句，就不會自動提交事務，重新啟動可視化工具，數據并沒有改變。
4）如果設置 autocommit 為 on 狀態，如果我們先執行 start transaction 然后在執行修改數據庫的語句：
update account set money = money-100 where name= a
update account set money = money+100 where name= b
那么此時就表示上述修改數據庫的 sql 語句都在同一個事務中，此時必須手動提交事務，即 commit；
換句話說，如果我們手動開啟事務 start transaction 那么此時 mysql 就不會自動提交事務，必須手動提交事務。
5）如果設置 autocommit 為 on 狀態，如果我們不執行 start transaction 直接執行修改數據庫的語句：
update account set money = money-100 where name= a update account set money = money+100 where name= b那么此時 mysql 就會自動提交事務，即上述每條 sql 語句就是一個事務
事務原理和四大特征事務原理
原理說明
一個用戶登錄成功以后，服務器會創建一個臨時日志文件。日志文件用來保存用戶事務狀態。
如果沒有使用事務，則所有的操作直接寫到數據庫中，不會使用日志文件。
如果開啟事務，將所有的寫操作寫到日志文件中。
如果這時用戶提交了事務，則將日志文件中所有的操作寫到數據庫中。
如果用戶回滾事務，則日志文件會被清空，不會影響到數據庫的操作。
事務的四大特征
事務的四大特征（ACID）
數據庫的事務必須具備 ACID 特征，ACID 是指 Atomicity（原子性）、Consistensy（一致性）、Isolation（隔離性）和 Durabiliyt（持久性）
隔離性（Isolation）
多個用戶并發的訪問數據庫時，一個用戶的事務不能被其他用戶的事物干擾，多個并發的事務之間相互隔離
持久性（Durability）
指一個事務一旦被提交，它對數據庫的改變是永久性的，哪怕數據庫發生異常，重啟之后數據依然會存在
原子性（Atomicity）
指事務包裝的一組 sql 語句（一組業務邏輯）是一個不可分割的工作單位，事務中的操作要么都發生，要么都不發生
一致性（Consistency）
一致性是指數據處于一種語義上有意義且正確的狀態；
事務一致性是指事務執行的結果必須是使數據從一個一致性狀態變到另一個一致性狀態。
事務的成功與失敗，最終數據庫的數據都是符合實際生活的業務邏輯。一致性絕大多數依賴業務邏輯和原子性
事務的并發訪問引發的三個問題(面試)
事務在操作時的理想狀態：多個事務之間互不影響，如果隔離級別設置不當就可能引發并發訪問問題
并發訪問的問題含義臟讀一個事務讀取到了另一個事務中尚未提交的數據。最嚴重，杜絕發生。不可重復讀一個事務中兩次讀取的數據內容不一致，要求的是一個事務中多次讀取時數據是不一致的，這是事務 update 時引發的問題幻讀 (虛讀) 一個事務內讀取到了別的事務插入或者刪除的數據，導致前后讀取記錄行數不同。這是 insert 或 delete 時引發的問題
1. 臟讀：指一個事務讀取了另外一個事務未提交的數據。(非常危險)
2. 不可重復讀：在一個事務內多次讀取表中的數據，多次讀取的結果不同。
3. 幻讀（虛讀）: 一個事務內讀取到了別的事務插入或者刪除的數據，導致前后讀取記錄行數不同
4. 總結
贓讀：一個事務讀取另一個事務還沒有提交的數據, 一定避免。
不可重復讀：一個事務讀取多次數據內容不一樣，主要是 update 語句。事務已經提交了。可以發生的。
幻讀（虛讀）：一個事務讀取多次數量不一樣，主要是 delete 或者 insert 語句。事務已經提交了。可以發生的。
事務的隔離級別
通過以上問題演示，我們發現如果不考慮事務的隔離性，會遇到臟讀、不可重復讀和虛讀等問題。所以在數據庫中我們要對上述三種問題進行解決。MySQL 數據庫規范規定了 4 種隔離級別，分別用于描述兩個事務并發的所有情況。
事物隔離級別
上面的級別最低，下面的級別最高。是表示會出現這種問題，否表示不會出現這種問題。
級別名字隔離級別臟讀不可重復讀幻讀數據庫默認隔離級別 1 讀未提交 read uncommitted 是是是
2 讀已提交 read committed 否是是 Oracle 和 SQL Server3 可重復讀 repeatable read 否否是 MySQL4 串行化 serializable 否否否
安全和性能對比
安全: 串行化 可重復讀 讀已提交 讀未提交
性能: 串行化 可重復讀 讀已提交 讀未提交
其實三個問題中，最嚴重的就是臟讀（讀取了錯誤數據），這個問題一定要避免；
關于不可重復讀和虛讀其實并不是邏輯上的錯誤，而是數據的時效性問題，所以這種問題并不屬于很嚴重的錯誤；
如果對于數據的時效性要求不是很高的情況下，我們是可以接受不可重復讀和虛讀的情況發生的
“mysql 設計概念、多表查詢和事務操作實例分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
