數據庫設計概念

數據庫設計簡介

1.數據庫設計概念

• 數據庫設計就是根據業(yè)務系統(tǒng)具體需求，結合我們所選用的DBMS，為這個業(yè)務系統(tǒng)構造出最優(yōu)的數據存儲模型。
• 建立數據庫中的表結構以及表與表之間的關聯(lián)關系的過程。
• 有哪些表？表里有哪些字段？表和表之間有什么關系？

2.數據庫設計步驟

• 需求分析：數據庫是什么？數據具體有哪些屬性？數據與屬性的特點是什么？
• 邏輯分析：通過ER圖對數據庫進行邏輯建模，不需要考慮我們所選用的數據庫管理系統(tǒng)。
• 物理設計：根據數據庫自身的特點把邏輯設計轉換為物理設計。
• 維護設計：對新的需求進行建表和對表的優(yōu)化。

3.表關系簡介

• 在真實的開發(fā)中，一個項目中的數據，一般都會保存在同一個數據庫中，但是不同的數據需要保存在不同的數據表中。這時不能把所有的數據都保存在同一張表中。
• 那么在設計保存數據的數據表時，我們就要根據具體的數據進行分析，然后把同一類數據保存在同一張表中，不同的數據進行分表處理。
• 數據之間必然會有一定的聯(lián)系，我們把不同的數據保存在不同的數據表中之后，同時還要在數據表中維護這些數據之間的關系。這時就會導致表和表之間必然會有一定的聯(lián)系。這時要求設計表的人員，就需要考慮不同表之間的具體關系。

在數據庫中，表總共存在三種關系，真實的數據表之間的關系：多對多關系、一對多（多對一）、一對一(極少)，(一對一關系就是我們之前學習的Map集合的key-value關系)

表關系（多對多）

1.多對多

• 如：訂單 和 商品
• 一個商品對應多個訂單，一個訂單對應多個商品
• 實現方式：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯(lián)兩方主鍵

說明：如果兩張表是多對多的關系，需要創(chuàng)建第三張表，并在第三張表中增加兩列，引入其他兩張表的主鍵作為自己的外鍵。

2.外鍵約束

• 外鍵用來讓兩個表的數據之間建立鏈接，保證數據的一致性和完整性（例如上述多對多中的訂單商品表來維護訂單表和商品表之間的關系）
• 使用之間表的目的是維護兩表之間多對多的關系：中間表插入的數據，必須在多對多的主表中存在，如果主表的記錄在中間表中維護了關系，就不能隨意的刪除。如果要刪除，必須先要刪除中間表關聯(lián)的數據

3.外鍵約束語法

-- 關鍵字解釋：
constraint: 添加約束，可以不寫
foreign key(當前表中的列名): 將某個字段作為外鍵
references 被引用表名(被引用表的列名) : 外鍵引用主表的主鍵

-- 創(chuàng)建表時添加外鍵約束
CREATE TABLE 表名(
   列名 數據類型,
   …
   [CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵列名) REFERENCES 主表(主表列名) 
); 
-- 建完表后添加外鍵約束
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱);
-- 刪除約束
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

4.創(chuàng)建外鍵約束

-- 訂單表
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.外鍵級聯(lián)

在修改和刪除主表的主鍵時，同時更新或刪除從表的外鍵值，稱為級聯(lián)操作

• ON UPDATE CASCADE – 級聯(lián)更新，主鍵發(fā)生更新時，外鍵也會更新
• ON DELETE CASCADE – 級聯(lián)刪除，主鍵發(fā)生刪除時，外鍵也會刪除

6.總結

1.為何要引用外鍵約束？

讓表的數據有效性，正確性。提高查詢效率。

2.添加外鍵約束語法？

constraint 外鍵約束名 foreign key(當前表的字段名) references 主表(主鍵)

3.有了外鍵約束操作數據注意事項?

要求添加數據需要先添加主表，然后添加從表。要求刪除數據需要先刪除從表，然后再刪除主表。

表關系（一對多）

一對多（多對一）

• 如：部門表 和 員工表
• 一個部門對應多個員工，一個員工對應一個部門
• 實現方式：在多的一方建立外鍵，指向一的一方的主鍵

表關系之一對一

一對一

• 如：用戶和 用戶信息
• 一對一關系多用于表拆分，將一個實體中經常使用的字段放一張表，不經常使用的字段放另一張表，用于提升查詢性能
• 實現方式：在任意一方加入外鍵，關聯(lián)另一方主鍵，并且設置外鍵為唯一(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 price
values (1, 2.30);
insert into price
values (2, 3.50);
insert into price
values (4, null);
insert into fruit
values (1, '蘋果', 1);
insert into fruit
values (2, '橘子', 2);
insert into fruit
values (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，名字，價格

-- 方式1
select fruit.id, fruit.name, price.price
from fruit,
     price
where fruit.price_id = price.id
  and fruit.name = '蘋果';
-- 方式2
select fruit.id, fruit.name, price.price
from 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，可以省略，否則這張表沒用名稱無法訪問表中的字段

事務操作

事務的概念

什么是事務

在實際的業(yè)務開發(fā)中，有些業(yè)務操作要多次訪問數據庫。一個業(yè)務要發(fā)送多條SQL語句給數據庫執(zhí)行。需要將多次訪問數據庫的操作視為一個整體來執(zhí)行，要么所有的SQL語句全部執(zhí)行成功。如果其中有一條SQL語句失敗，就進行事務的回滾，所有的SQL語句全部執(zhí)行失敗。

簡而言之，事務指的是邏輯上的一組操作,組成這組操作的各個單元要么全都成功,要么全都失敗。

事務作用：保證在一個事務中多次操作數據庫表中數據時，要么全都成功,要么全都失敗。

事務的應用場景聲明

關于事務在實際中的應用場景：

假設我在淘寶買了一部手機，然后當我付完款，錢已經從我的賬戶中扣除。正當此時，淘寶轉賬系統(tǒng)宕機了，那么此時淘寶還沒有收到錢，而我的賬戶的錢已經減少了，這樣就會導致我作為買家錢已經付過，而賣家還沒有收到錢，他們不會發(fā)貨物給我。這樣做顯然是不合理。實際生活中是如果淘寶出問題，作為用戶的賬戶中錢是不應該減少的。這樣用戶就不會損失錢。

還有種情況，就是當我付完款之后，賣家看到我付款成功，然后直接發(fā)貨了，我如果有權限操作，我可以撤銷，這樣就會導致我的錢沒有減少，但是賣家已經發(fā)貨，同樣這種問題在實際生活中也是不允許出現的。

MySQL中可以有兩種方式進行事務的操作：

• 手動提交事物：先開啟，在提交
• 自動提交事物（默認的）：即執(zhí)行一條sql語句提交一次事物

數據準備

# 創(chuàng)建賬號表
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語句

手動提交事務使用步驟

• 開啟事務–>執(zhí)行SQL語句–>成功–>提交事務
• 開啟事務–>執(zhí)行SQL語句–>失敗–>回滾事務

演示案例：演示提交事務，a給b轉賬100元

-- 1.開啟事務
start transaction;
-- 2.執(zhí)行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.執(zhí)行sql語句
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
-- 3.回滾事務
rollback;

注意：

• 提交事務(commit) ：事務提交之后，sql語句對數據庫產生的操作才會被永久的保存
• 事務的回滾(rollback)：撤銷已經成功執(zhí)行的sql語句，回到開啟事務之前的狀態(tài)
• 只要提交事務，那么數據就會長久保存了，就不能回滾事務了。即提交或者回滾事務都是代表結束當前事務的操作

自動提交事務

MySQL的每一條DML(增刪改)語句都是一個單獨的事務，每條語句都會自動開啟一個事務，執(zhí)行完畢自動提交事務，MySQL默認開始自動提交事務。自動提交，通過修改mysql全局變量autocommit進行控制。

通過以下命令可以查看當前autocommit模式

show variables like '%commit%';

設置自動提交的參數為OFF

set autocommit = 0;  -- 0:OFF  1:ON

案例演示

-- 自動提交事務：每條sql語句就是一個事務，那么執(zhí)行一條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變?yōu)槭謩犹峤皇聞?，即將自動提交事務關閉
-- 下次重新連接mysql依然是自動提交事務
set autocommit = 0; -- 0 表示關閉自動提交事務 1表示開啟自動事務
update account set money=money-100 where name='a'

注意：

1）MySql默認自動提交。即執(zhí)行一條sql語句提交一次事務。

2）設置autocommit為off狀態(tài)，只是臨時性的，下次重新連接mysql，autocommit依然變?yōu)?code>on狀態(tài)。

3）如果設置autocommit為off狀態(tài)，那么當我們執(zhí)行一條sql語句，就不會自動提交事務，重新啟動可視化工具，數據并沒有改變。

4）如果設置autocommit為on狀態(tài)，如果我們先執(zhí)行start transaction 然后在執(zhí)行修改數據庫的語句：

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狀態(tài)，如果我們不執(zhí)行start transaction 直接執(zhí)行修改數據庫的語句：

update account set money = money-100 where name='a';
update account set money = money+100 where name='b';

那么此時mysql就會自動提交事務，即上述每條sql語句就是一個事務

事務原理和四大特征

事務原理

原理說明

• 一個用戶登錄成功以后，服務器會創(chuàng)建一個臨時日志文件。日志文件用來保存用戶事務狀態(tài)。
• 如果沒有使用事務，則所有的操作直接寫到數據庫中，不會使用日志文件。
• 如果開啟事務，將所有的寫操作寫到日志文件中。
• 如果這時用戶提交了事務，則將日志文件中所有的操作寫到數據庫中。
• 如果用戶回滾事務，則日志文件會被清空，不會影響到數據庫的操作。

事務的四大特征

事務的四大特征（ACID）

數據庫的事務必須具備ACID特征，ACID是指Atomicity（原子性）、Consistensy（一致性）、Isolation（隔離性）和Durabiliyt（持久性）

隔離性（Isolation）

多個用戶并發(fā)的訪問數據庫時，一個用戶的事務不能被其他用戶的事物干擾，多個并發(fā)的事務之間相互隔離

持久性（Durability）

指一個事務一旦被提交，它對數據庫的改變是永久性的，哪怕數據庫發(fā)生異常，重啟之后數據依然會存在

原子性（Atomicity）

指事務包裝的一組sql語句（一組業(yè)務邏輯）是一個不可分割的工作單位，事務中的操作要么都發(fā)生，要么都不發(fā)生

一致性（Consistency）

一致性是指數據處于一種語義上有意義且正確的狀態(tài)；

事務一致性是指事務執(zhí)行的結果必須是使數據從一個一致性狀態(tài)變到另一個一致性狀態(tài)。

事務的成功與失敗，最終數據庫的數據都是符合實際生活的業(yè)務邏輯。一致性絕大多數依賴業(yè)務邏輯和原子性

事務的并發(fā)訪問引發(fā)的三個問題(面試)

事務在操作時的理想狀態(tài)：多個事務之間互不影響，如果隔離級別設置不當就可能引發(fā)并發(fā)訪問問題

1.臟讀：指一個事務讀取了另外一個事務未提交的數據。(非常危險)

2.不可重復讀：在一個事務內多次讀取表中的數據，多次讀取的結果不同。

3.幻讀（虛讀）:一個事務內讀取到了別的事務插入或者刪除的數據，導致前后讀取記錄行數不同

4.總結

• 贓讀：一個事務讀取另一個事務還沒有提交的數據,一定避免。
• 不可重復讀：一個事務讀取多次數據內容不一樣，主要是update語句。事務已經提交了。 可以發(fā)生的。
• 幻讀（虛讀）：一個事務讀取多次數量不一樣，主要是delete或者insert語句。事務已經提交了?？梢园l(fā)生的。

事務的隔離級別

通過以上問題演示，我們發(fā)現如果不考慮事務的隔離性，會遇到臟讀、不可重復讀和虛讀等問題。所以在數據庫中我們要對上述三種問題進行解決。MySQL數據庫規(guī)范規(guī)定了4種隔離級別，分別用于描述兩個事務并發(fā)的所有情況。

事物隔離級別

上面的級別最低，下面的級別最高。是表示會出現這種問題，否表示不會出現這種問題。

安全和性能對比

• 安全: 串行化>可重復讀>讀已提交>讀未提交
• 性能: 串行化<可重復讀<讀已提交<讀未提交

其實三個問題中，最嚴重的就是臟讀（讀取了錯誤數據），這個問題一定要避免；

關于不可重復讀和虛讀其實并不是邏輯上的錯誤，而是數據的時效性問題，所以這種問題并不屬于很嚴重的錯誤；

如果對于數據的時效性要求不是很高的情況下，我們是可以接受不可重復讀和虛讀的情況發(fā)生的

到此這篇關于MySQL數據庫設計概念及多表查詢;事物操作的文章就介紹到這了,更多相關mysql數據庫設計內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論