mysql設(shè)計概念多表查詢和事務(wù)制作實例分析

2024-07-24 12:32:56

字體：大中小

供稿：網(wǎng)友

　　本篇內(nèi)容介紹了“mysql設(shè)計概念、多表查詢和事務(wù)操作實例分析”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

　　數(shù)據(jù)庫設(shè)計簡介

　　1.數(shù)據(jù)庫設(shè)計概念

　　數(shù)據(jù)庫設(shè)計就是根據(jù)業(yè)務(wù)系統(tǒng)具體需求，結(jié)合我們所選用的DBMS，為這個業(yè)務(wù)系統(tǒng)構(gòu)造出最優(yōu)的數(shù)據(jù)存儲模型。

　　建立數(shù)據(jù)庫中的表結(jié)構(gòu)以及表與表之間的關(guān)聯(lián)關(guān)系的過程。

　　有哪些表？表里有哪些字段？表和表之間有什么關(guān)系？

　　2.數(shù)據(jù)庫設(shè)計步驟

　　需求分析：數(shù)據(jù)庫是什么？數(shù)據(jù)具體有哪些屬性？數(shù)據(jù)與屬性的特點是什么？

　　邏輯分析：通過ER圖對數(shù)據(jù)庫進(jìn)行邏輯建模，不需要考慮我們所選用的數(shù)據(jù)庫管理系統(tǒng)。

　　物理設(shè)計：根據(jù)數(shù)據(jù)庫自身的特點把邏輯設(shè)計轉(zhuǎn)換為物理設(shè)計。

　　維護(hù)設(shè)計：對新的需求進(jìn)行建表和對表的優(yōu)化。

　　3.表關(guān)系簡介

　　在真實的開發(fā)中，一個項目中的數(shù)據(jù)，一般都會保存在同一個數(shù)據(jù)庫中，但是不同的數(shù)據(jù)需要保存在不同的數(shù)據(jù)表中。這時不能把所有的數(shù)據(jù)都保存在同一張表中。

　　那么在設(shè)計保存數(shù)據(jù)的數(shù)據(jù)表時，我們就要根據(jù)具體的數(shù)據(jù)進(jìn)行分析，然后把同一類數(shù)據(jù)保存在同一張表中，不同的數(shù)據(jù)進(jìn)行分表處理。

　　數(shù)據(jù)之間必然會有一定的聯(lián)系，我們把不同的數(shù)據(jù)保存在不同的數(shù)據(jù)表中之后，同時還要在數(shù)據(jù)表中維護(hù)這些數(shù)據(jù)之間的關(guān)系。這時就會導(dǎo)致表和表之間必然會有一定的聯(lián)系。這時要求設(shè)計表的人員，就需要考慮不同表之間的具體關(guān)系。

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

　　4.外鍵級聯(lián)

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

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

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

　　5.總結(jié)

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

　　讓表的數(shù)據(jù)有效性，正確性。提高查詢效率。

　　2.添加外鍵約束語法？

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

　　3.有了外鍵約束操作數(shù)據(jù)注意事項?

　　要求添加數(shù)據(jù)需要先添加主表，然后添加從表。

　　要求刪除數(shù)據(jù)需要先刪除從表，然后再刪除主表。

　　表關(guān)系（一對多）

　　一對多（多對一）

　　如：部門表和員工表

　　一個部門對應(yīng)多個員工，一個員工對應(yīng)一個部門

　　實現(xiàn)方式：在多的一方建立外鍵，指向一的一方的主鍵

　　mysql設(shè)計概念、多表查詢和事務(wù)操作實例分析

　　表關(guān)系之一對一

　　一對一

　　如：用戶和用戶信息

　　一對一關(guān)系多用于表拆分，將一個實體中經(jīng)常使用的字段放一張表，不經(jīng)常使用的字段放另一張表，用于提升查詢性能

　　實現(xiàn)方式：在任意一方加入外鍵，關(guān)聯(lián)另一方主鍵，并且設(shè)置外鍵為唯一(UNIQUE)

　　mysql設(shè)計概念、多表查詢和事務(wù)操作實例分析

　　多表查詢

　　mysql設(shè)計概念、多表查詢和事務(wù)操作實例分析

　　準(zhǔn)備數(shù)據(jù)

　　-- 價格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));-- 數(shù)據(jù)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);

　　內(nèi)連接查詢

　　1.什么是內(nèi)連接

　　內(nèi)連接查詢又稱為交集查詢，也就是查詢只顯示滿足條件的數(shù)據(jù)

　　2.顯示內(nèi)連接

　　顯示內(nèi)連接：使用INNER JOIN...ON語句，可以省略INNER關(guān)鍵字

　　-- 語法核心select * from 表名1 inner join 表名2 on 條件;-- 或者select * from 表名1 join 表名2 on 條件;

　　3.隱式內(nèi)連接

　　看不到JOIN關(guān)鍵字，條件使用WHERE指定

　　select 列名,列名,... from 表名1,表名2 where 表名1.列名=表名2.列名;

　　4.示例

　　查詢水果的價格

　　-- 隱式內(nèi)連接select * from fruit,price where fruit.price_id=price.id;-- 顯式內(nèi)連接select * from fruit inner join price on fruit.price_id=price.id;

　　mysql設(shè)計概念、多表查詢和事務(wù)操作實例分析

　　查詢蘋果的信息，顯示蘋果的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.總結(jié)

　　1.內(nèi)連接作用？

　　過濾笛卡爾積

　　獲取兩表的交集部分(都滿足條件的部分)

　　2.什么是隱式內(nèi)連接和顯示內(nèi)連接？

　　隱式內(nèi)連接：看不到JOIN：select 列名,列名....from 表名1,表名2 where 表名1.列名=表名2.列名;

　　顯示內(nèi)連接：看得到JOIN：select * from 表名1 inner join 表名2 on 條件;

　　3.內(nèi)連接查詢步驟？

　　1）確定查詢幾張表

　　2）確定表連接條件

　　3）根據(jù)需要在操作

　　外連接查詢

　　1.左外連接

　　左表的記錄全部顯示出來

　　外表只會顯示符合搜索條件的記錄

　　語法格式：

　　select * from 表1 left [outer] join 表2 on 條件;

　　說明：

　　left關(guān)鍵字左邊的表定義為左表，left關(guān)鍵字右邊的表定義為右表，查詢的內(nèi)容以左表為主

　　如果左表有數(shù)據(jù)，而右表沒有數(shù)據(jù)對應(yīng)的數(shù)據(jù)，仍然會把左表數(shù)據(jù)進(jìn)行顯示

　　outer關(guān)鍵字可以省略

　　練習(xí)：

　　不管能否查到水果對應(yīng)價格，都要把水果顯示出來

　　-- 左外連接查詢select * from fruit left outer join price on fruit.price_id=price.id;

　　2.右外連接

　　右表的記錄全部表示出來

　　左表只會顯示符合搜索條件的記錄

　　語法格式：

　　select * from 表名1 right [outer] join 表名2 on 條件;

　　說明：

　　right關(guān)鍵字左邊的表定義為左表，right關(guān)鍵字右邊的表定義為右表，查詢的內(nèi)容以右表為主

　　如果右表沒有數(shù)據(jù)，而左表沒有對應(yīng)的數(shù)據(jù)，仍然會把右表數(shù)據(jù)進(jìn)行顯示

　　outer關(guān)鍵字可以省略

　　練習(xí)：

　　不管能否查到價格對應(yīng)的水果，都要把價格顯示出來

　　select * from fruit right outer join price on fruit.price_id=price.id;

　　總結(jié)：

　　1.掌握左外連接查詢格式？

　　select * from 表1 left outer join 表2 on 條件;

　　表1看作為左表，表2看做為右表

　　2.左外連接查詢特點？

　　在滿足要求的基礎(chǔ)上保證左表的數(shù)據(jù)全部顯示

　　3.掌握右外連接查詢格式？

　　select * from 表1 right outer join 表2 on 條件;

　　4.右外連接查詢特點？

　　在滿足要求的基礎(chǔ)上,保證右表的數(shù)據(jù)全部顯示

　　嵌套查詢（子查詢）

　　1.什么是子查詢

　　一條查詢語句結(jié)果作為另一條查詢語法一部分。

　　SELECT 查詢字段 FROM 表 WHERE 條件;舉例：SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

　　說明：子查詢需要放在（）中

　　三種子查詢情況：單行單列、多行單列、多行多列。

　　2.單行單列

　　子查詢結(jié)果是單列，在WHERE后面作為條件

　　SELECT 查詢字段 FROM 表 WHERE 字段=（子查詢）;

　　通常使用比較運算符: > 、>= 、<、<=、=等

　　3.多行單列

　　子查詢結(jié)果是多行單列，結(jié)果集類似于一個數(shù)組，在WHERE后面作為條件，父查詢使用IN運算符

　　-- IN表示在數(shù)值中SELECT 查詢字段 FROM 表 WHERE 字段 IN （子查詢）;

　　4.多行多列

　　子查詢結(jié)果是多列，在FROM后面作為表

　　SELECT 查詢字段 FROM （子查詢）表別名 WHERE 條件;

　　注意：子查詢作為表需要取別名，使用as，可以省略，否則這張表沒用名稱無法訪問表中的字段

　　事務(wù)操作

　　事務(wù)的概念

　　什么是事務(wù)

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

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

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

　　事務(wù)的應(yīng)用場景聲明

　　關(guān)于事務(wù)在實際中的應(yīng)用場景：

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

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

　　MySQL中可以有兩種方式進(jìn)行事務(wù)的操作：

　　手動提交事物：先開啟，在提交

　　自動提交事物（默認(rèn)的）：即執(zhí)行一條sql語句提交一次事物

　　數(shù)據(jù)準(zhǔn)備

　　# 創(chuàng)建賬號表create table account(

　　 id int primary key auto_increment,

　　 name varchar(20),

　　 money double);# 初始化數(shù)據(jù)insert into account values (null,'a',1000);insert into account values (null,'b',1000);

　　手動提交事務(wù)

　　手動提交事務(wù)有關(guān)的sql語句

　　SQL語句描述

　　commit 提交事物

　　rollback 回滾事物

　　手動提交事務(wù)使用步驟

　　開啟事務(wù)–>執(zhí)行SQL語句–>成功–>提交事務(wù)

　　開啟事務(wù)–>執(zhí)行SQL語句–>失敗–>回滾事務(wù)

　　演示案例：演示提交事務(wù)，a給b轉(zhuǎn)賬100元

　　-- 1.開啟事務(wù)start transaction;-- 2.執(zhí)行sql語句update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 3.提交事務(wù)commit;

　　案例演示：演示回滾事務(wù)，a給b轉(zhuǎn)賬100元

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

　　注意：

　　提交事務(wù)(commit) ：事務(wù)提交之后，sql語句對數(shù)據(jù)庫產(chǎn)生的操作才會被永久的保存

　　事務(wù)的回滾(rollback)：撤銷已經(jīng)成功執(zhí)行的sql語句，回到開啟事務(wù)之前的狀態(tài)

　　只要提交事務(wù)，那么數(shù)據(jù)就會長久保存了，就不能回滾事務(wù)了。即提交或者回滾事務(wù)都是代表結(jié)束當(dāng)前事務(wù)的操作

　　自動提交事務(wù)

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

　　通過以下命令可以查看當(dāng)前autocommit模式

　　show variables like '%commit%';

　　設(shè)置自動提交的參數(shù)為OFF

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

　　案例演示

　　-- 自動提交事務(wù)：每條sql語句就是一個事務(wù)，那么執(zhí)行一條sql語句就會提交一次事務(wù)-- mysql數(shù)據(jù)庫就是自動提交事務(wù)-- a給b轉(zhuǎn)賬100元update account set money=money-100 where name='a';update account set money=money+100 where name='b';-- 查看mysql是否自動提交事務(wù)-- autocommit的值是on表示自動提交事務(wù)，值是off表示關(guān)閉自動提交事務(wù)show variables like '%commit%';-- 我們可以使用命令臨時設(shè)置mysql變?yōu)槭謩犹峤皇聞?wù)，即將自動提交事務(wù)關(guān)閉-- 下次重新連接mysql依然是自動提交事務(wù)set autocommit = 0; -- 0 表示關(guān)閉自動提交事務(wù) 1表示開啟自動事務(wù)update account set money=money-100 where name='a'

　　注意：

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

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

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

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

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

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

　　那么此時就表示上述修改數(shù)據(jù)庫的sql語句都在同一個事務(wù)中，此時必須手動提交事務(wù)，即commit；

　　換句話說，如果我們手動開啟事務(wù)start transaction那么此時mysql就不會自動提交事務(wù)，必須手動提交事務(wù)。

　　5）如果設(shè)置autocommit為on狀態(tài)，如果我們不執(zhí)行start transaction 直接執(zhí)行修改數(shù)據(jù)庫的語句：

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

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

　　事務(wù)原理和四大特征

　　事務(wù)原理

　　原理說明

　　一個用戶登錄成功以后，服務(wù)器會創(chuàng)建一個臨時日志文件。日志文件用來保存用戶事務(wù)狀態(tài)。

　　如果沒有使用事務(wù)，則所有的操作直接寫到數(shù)據(jù)庫中，不會使用日志文件。

　　如果開啟事務(wù)，將所有的寫操作寫到日志文件中。

　　如果這時用戶提交了事務(wù)，則將日志文件中所有的操作寫到數(shù)據(jù)庫中。

　　如果用戶回滾事務(wù)，則日志文件會被清空，不會影響到數(shù)據(jù)庫的操作。

　　事務(wù)的四大特征

　　事務(wù)的四大特征（ACID）

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

　　隔離性（Isolation）

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

　　持久性（Durability）

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

　　原子性（Atomicity）

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

　　一致性（Consistency）

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

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

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

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

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

　　并發(fā)訪問的問題含義

　　臟讀一個事務(wù)讀取到了另一個事務(wù)中尚未提交的數(shù)據(jù)。最嚴(yán)重，杜絕發(fā)生。

　　不可重復(fù)讀一個事務(wù)中兩次讀取的數(shù)據(jù)內(nèi)容不一致，要求的是一個事務(wù)中多次讀取時數(shù)據(jù)是不一致的，這是事務(wù)update時引發(fā)的問題

　　幻讀(虛讀) 一個事務(wù)內(nèi)讀取到了別的事務(wù)插入或者刪除的數(shù)據(jù)，導(dǎo)致前后讀取記錄行數(shù)不同。這是insert或delete時引發(fā)的問題

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

　　2.不可重復(fù)讀：在一個事務(wù)內(nèi)多次讀取表中的數(shù)據(jù)，多次讀取的結(jié)果不同。

　　3.幻讀（虛讀）:一個事務(wù)內(nèi)讀取到了別的事務(wù)插入或者刪除的數(shù)據(jù)，導(dǎo)致前后讀取記錄行數(shù)不同

　　4.總結(jié)

　　贓讀：一個事務(wù)讀取另一個事務(wù)還沒有提交的數(shù)據(jù),一定避免。

　　不可重復(fù)讀：一個事務(wù)讀取多次數(shù)據(jù)內(nèi)容不一樣，主要是update語句。事務(wù)已經(jīng)提交了。可以發(fā)生的。

　　幻讀（虛讀）：一個事務(wù)讀取多次數(shù)量不一樣，主要是delete或者insert語句。事務(wù)已經(jīng)提交了。可以發(fā)生的。

　　事務(wù)的隔離級別

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

　　事物隔離級別

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

　　級別名字隔離級別臟讀不可重復(fù)讀幻讀數(shù)據(jù)庫默認(rèn)隔離級別

　　1 讀未提交 read uncommitted 是是是

　　2 讀已提交 read committed 否是是 Oracle和SQL Server

　　3 可重復(fù)讀 repeatable read 否否是 MySQL

　　4 串行化 serializable 否否否

　　安全和性能對比

　　安全: 串行化>可重復(fù)讀>讀已提交>讀未提交

　　性能: 串行化<可重復(fù)讀<讀已提交<讀未提交

　　其實三個問題中，最嚴(yán)重的就是臟讀（讀取了錯誤數(shù)據(jù)），這個問題一定要避免；

　　關(guān)于不可重復(fù)讀和虛讀其實并不是邏輯上的錯誤，而是數(shù)據(jù)的時效性問題，所以這種問題并不屬于很嚴(yán)重的錯誤；

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

　　“mysql設(shè)計概念、多表查詢和事務(wù)操作實例分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。

（編輯：武林網(wǎng)）

上一篇：MySQL主從延遲讀寫分離問題怎樣解決

下一篇：mysql5.7如何更換root密碼