MySQL數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)點(diǎn)有哪些

這篇文章主要介紹“MySQL數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)點(diǎn)有哪些”，在日常操作中，相信很多人在MySQL數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)點(diǎn)有哪些問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MySQL數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)點(diǎn)有哪些”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

一、數(shù)據(jù)庫(kù)

1.1 事務(wù)

1.1.1 事務(wù)四個(gè)特性(ACID)

• 原子性 （Atomicity）
  整個(gè)事務(wù)中的所要操作要么全部提交成功，要么全部失敗回滾。

• 一致性（Consistency）
  保證數(shù)據(jù)庫(kù)中的數(shù)據(jù)操作之前和操作之后的一致性。（比如用戶多個(gè)賬戶之間的轉(zhuǎn)賬，但是用戶的總金額是不變的）

• 隔離性（Isolation）
  隔離性要求一個(gè)事務(wù)對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的修改，在未提交完成前對(duì)于其它事務(wù)是不可見(jiàn)的。（即事務(wù)之間要串行執(zhí)行）

• 持久性（Durability）
  持久性是指一個(gè)事務(wù)一旦被提交了，那么對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)改變就是永久性的，即便是在數(shù)據(jù)庫(kù)系統(tǒng)遇到故障的情況下也不會(huì)丟失提交事務(wù)的操作。

SQL標(biāo)準(zhǔn)定義了四種隔離性：（下面隔離性是由低到高，并發(fā)性由高到低）

• 未提交讀。
  最低的隔離等級(jí)，允許其他事務(wù)看到?jīng)]有提交的數(shù)據(jù)，會(huì)導(dǎo)致臟讀。

• 已提交讀。
  由于數(shù)據(jù)庫(kù)是讀寫分離，事務(wù)讀取的時(shí)候獲取讀鎖，但是在讀完之后立即釋放，釋放讀鎖之后，就可能被其他事務(wù)修改數(shù)據(jù)，再進(jìn)行讀是就發(fā)現(xiàn)前后讀取數(shù)據(jù)的結(jié)果不同，造成不可重復(fù)讀。（讀鎖不需要事務(wù)提交后釋放，而寫鎖需要事務(wù)提交后釋放。）

• 可重復(fù)讀。
  所有被select獲取的數(shù)據(jù)都不能被修改，這樣就可以避免一個(gè)事務(wù)前后讀取不一致的情況。但是沒(méi)有辦法控制幻讀，因?yàn)檫@個(gè)時(shí)候其他事務(wù)不能更改所選的數(shù)據(jù)，但是可以增加數(shù)據(jù)；

• 可串行化。
  所有事務(wù)一個(gè)接著一個(gè)執(zhí)行，這樣可以避免幻讀，對(duì)于基于鎖來(lái)實(shí)現(xiàn)并發(fā)控制的數(shù)據(jù)庫(kù)來(lái)說(shuō)，串行化要求在執(zhí)行范圍查詢的時(shí)候，需要獲取范圍鎖，如果不是基于鎖實(shí)現(xiàn)并發(fā)控制的數(shù)據(jù)庫(kù)，則檢查到有違反串行操作的事務(wù)時(shí)，需回滾該事務(wù)。

總結(jié)：四個(gè)級(jí)別逐漸增強(qiáng)，每個(gè)級(jí)別解決問(wèn)題，事務(wù)級(jí)別越高，性能越差。

隔離級(jí)別????????????臟讀??不可重復(fù)讀??幻讀
未提交讀（read uncommitted）?可能??可能?????可能
已提交讀（read committed）?不可能??可能?????可能
可重復(fù)讀（repeatable read）?不可能 ?不可能?????可能
可串行化（serializable）???不可能??不可能????不可能

總結(jié)：未提交讀會(huì)造成臟讀—>已提交讀解決臟讀，但會(huì)造成不可重復(fù)讀—>可重復(fù)讀解決讀取結(jié)果前后不一致的情況，但是造成幻讀（以前沒(méi)有，現(xiàn)在有）—>可串行化解決了幻讀，但是增加很多范圍鎖，可能會(huì)造成鎖超時(shí)；

1.1.2 臟讀、不可重復(fù)讀和幻讀

• 臟讀（針對(duì)回滾的操作）：事務(wù)T1更新了一行記錄的內(nèi)容，但是并沒(méi)有提交所做的修改，事務(wù)T2讀取更新后的行，然后T1執(zhí)行了回滾操作，取消了剛才所做的修改?，F(xiàn)在T2讀取的行數(shù)就無(wú)效了（一個(gè)事務(wù)讀取了另一個(gè)事務(wù)）;

• 不可重復(fù)讀（針對(duì)修改的操作）：事務(wù)T1讀取了一行記錄，緊接著T2修改了T1剛才讀取的那一行記錄，然后T1又再次讀取這行記錄，發(fā)現(xiàn)與剛才讀取的結(jié)果不同。

• 幻讀（針對(duì)更新的操作）：事務(wù)T1讀取一條指定的where子句所返回的結(jié)果集，然后T2事務(wù)新插入一行記錄，這行記錄恰好可以滿足T1所使用的查詢條件。然后T1再次對(duì)表進(jìn)行檢索，但又看到了T2插入的數(shù)據(jù)。（第一次沒(méi)看到，第二次看到了）

二、索引

2.1 索引特點(diǎn)

1. 可以加快數(shù)據(jù)庫(kù)檢索速度；

2. 只能創(chuàng)建在表上，不能創(chuàng)建到視圖上；

3. 既可以直接創(chuàng)建又可以間接創(chuàng)建；

4. 可以在優(yōu)化隱藏中使用索引；

5. 使用查詢處理器執(zhí)行sql語(yǔ)句，在一個(gè)表上，一次只能使用一個(gè)索引。

2.1.1 索引優(yōu)點(diǎn)

1. 創(chuàng)建唯一性索引，保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性；

2. 大大加快數(shù)據(jù)檢索速度，這是創(chuàng)建索引的最主要原因；

3. 加速數(shù)據(jù)庫(kù)表之間的鏈接，特別是在實(shí)現(xiàn)數(shù)據(jù)庫(kù)參考完整性方面特別有意義；

4. 在使用分組和排序子句進(jìn)行檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間；

5. 通過(guò)使用索引，可以在查詢中使用優(yōu)化隱藏器，提高系統(tǒng)性能；

2.1.2 索引缺點(diǎn)

1. 創(chuàng)建和維護(hù)索引耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)量的增加而增加；

2. 索引需要占用物理空間，除了數(shù)據(jù)表占用數(shù)據(jù)空間之外，每一個(gè)索引還要占用一定的物理空間，如果建立聚集索引，那么需要的空間就會(huì)更大；

3. 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也需要維護(hù)，降低數(shù)據(jù)維護(hù)速度；

2.2 索引分類

（1）普通索引(它沒(méi)有任何限制。)
（2）唯一性索引(索引列的值必須唯一，但允許有空值。)
（3）主鍵索引(一種特殊的唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引。)
（4）組合索引
（5）聚集索引 按照每張表的主鍵構(gòu)造一顆B+樹(shù)，并且葉節(jié)點(diǎn)中存放著整張表的行記錄數(shù)據(jù)，因此也讓聚集索引的葉節(jié)點(diǎn)成為數(shù)據(jù)頁(yè)。
（6）非聚集索引(輔助索引)(頁(yè)節(jié)點(diǎn)不存放一整行記錄)。

2.3 索引失效

（1）如果條件中有or，即使其中有條件帶索引，也不會(huì)使用（盡量少用or）;
（2）Like查詢是以%開(kāi)頭，例如SELECT * FROM mytable WHEREt Name like’%admin’;
（3）如果列類型是字符串，那一定要在條件中使用引號(hào)引起來(lái)，否則不會(huì)使用索引;

2.4 各引擎支持索引

MyISAM，InnoDB,Memonry三個(gè)常用MySQL引擎類型比較:
索引???MyISAM索引???InnoDB索引???Memonry索引
B-tree索引??支持??????支持??????支持
Hash索引 ??不支持?????不支持?????支持
R-Tree索引??支持?????不支持?????不支持
Full-text索引?不支持????暫不支持?????不支持

2.5 數(shù)據(jù)庫(kù)中的索引結(jié)構(gòu)

因?yàn)樵谑褂枚鏄?shù)的時(shí)候，由于二叉樹(shù)的深度過(guò)大而造成I/O讀寫過(guò)于頻繁，進(jìn)而導(dǎo)致查詢效率低下。因此采用多路樹(shù)結(jié)構(gòu)，B樹(shù)的各種操作能使B樹(shù)保持較低的高度。

B樹(shù)又叫平衡多路查找樹(shù)，一棵m階的B樹(shù)特性如下：

• 1.樹(shù)中每個(gè)結(jié)點(diǎn)最多含有m個(gè)孩子（m>=2）;

• 2.除根結(jié)點(diǎn)和葉子結(jié)點(diǎn)外，其他每個(gè)結(jié)點(diǎn)至少有（ceil（m/2））個(gè)孩子（其中ceil（x）是一個(gè)取上限的函數(shù)）；

• 3.根結(jié)點(diǎn)至少有2個(gè)孩子（除非B樹(shù)只包含一個(gè)結(jié)點(diǎn)：根結(jié)點(diǎn)）;

• 4.所有葉子結(jié)點(diǎn)都出現(xiàn)在同一層，葉子結(jié)點(diǎn)不包含任何關(guān)鍵字信息（可以看做是外部結(jié)點(diǎn)或查詢失敗的結(jié)點(diǎn)，指向這些結(jié)點(diǎn)的指針都為null）;（注：葉子結(jié)點(diǎn)只是沒(méi)有孩子和指向孩子的指針，這些結(jié)點(diǎn)也存在，也有元素，類似紅黑樹(shù)中，每一個(gè)null指針即當(dāng)做葉子結(jié)點(diǎn)，只是沒(méi)畫出來(lái)而已）
  
  B+樹(shù)
  
  在什么情況下適合建立索引?
  （1）為經(jīng)常出現(xiàn)在關(guān)鍵字order by, group by, distinct后面的字段，建立索引;
  （2）在union等集合操作的結(jié)果集字段上建立索引，其建立索引的目的同上;
  （3）為經(jīng)常用作查詢選擇的字段，建立索引;
  （4）在經(jīng)常用做表鏈接的屬性上，建立索引;
  （5）考慮使用索引覆蓋，對(duì)數(shù)據(jù)很少被更新的表，如果用戶經(jīng)常只查詢其中的幾個(gè)字段，可以考慮在這幾個(gè)字段上建立索引，從而將表的掃描改變?yōu)樗饕膾呙琛?/p>

三、Mysql語(yǔ)法順序

即當(dāng)sql中存在下面的關(guān)鍵字時(shí)，它們要保持這樣的順序：

select[distinct]、from、join（如left join）、on、where、group
by、having、union、order by、limit;

四、Mysql執(zhí)行順序

即在執(zhí)行時(shí)sql按照下面的順序進(jìn)行執(zhí)行：

from、on、join、where、group by、having、select、distinct、union、order by
?group by要和聚合函數(shù)一起使用，

例如:

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer=’Bush’ or a.Customer = ‘Adams’ group by a.Customer;

實(shí)現(xiàn)多表查詢(內(nèi)連接)

select u.uname,a.addr from lm_user u inner join lm_addr a on u.uid = a.uid;

使用select from where同樣可以實(shí)現(xiàn)

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

五、存儲(chǔ)過(guò)程

delimiter $$
create procedure procedure_bill()
comment '查詢所有銷售情況'
begin
select billid, tx_time, amt from lm_bill;
end $$
delimiter ;

調(diào)用存儲(chǔ)過(guò)程

call procedure_bill();

查看存儲(chǔ)過(guò)程

show procedure status like 'procedure_bill';

六、建立多對(duì)多數(shù)據(jù)表關(guān)系

在數(shù)據(jù)庫(kù)中，如果兩個(gè)表的之間的關(guān)系為多對(duì)多的關(guān)系，如：“學(xué)生表和課程表”，一個(gè)學(xué)生可以選多門課，一門課也可以被多個(gè)學(xué)生選;根據(jù)數(shù)據(jù)庫(kù)的設(shè)計(jì)原則，應(yīng)當(dāng)形成第三張關(guān)聯(lián)表。
步驟1:創(chuàng)建三張數(shù)據(jù)表Student ,Course,Stu_Cour

/**學(xué)生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*學(xué)生課程表*/
CREATE TABLE Course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**學(xué)生課程關(guān)聯(lián)表*/
CREATE TABLE Stu_Cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)

第二步:為Stu_Cour關(guān)聯(lián)表添加外鍵

/*添加外鍵約束*/
ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES Student(stu_id);
ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES Course(cour_id);

完成創(chuàng)建！

• 注:為已經(jīng)添加好的數(shù)據(jù)表添加外鍵:
  -語(yǔ)法:alter table 表名 add constraint FK_ID foreign key(你的外鍵字段名) REFERENCES 外表表名(對(duì)應(yīng)的表的主鍵字段名);

例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);

七、數(shù)據(jù)庫(kù)引擎（存儲(chǔ)引擎）

當(dāng)你訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，不管是手工訪問(wèn)，還是程序訪問(wèn)，都不是直接讀寫數(shù)據(jù)庫(kù)文件，而是通過(guò)數(shù)據(jù)庫(kù)引擎去訪問(wèn)數(shù)據(jù)庫(kù)文件。

以關(guān)系型數(shù)據(jù)庫(kù)為例，發(fā)SQL語(yǔ)句給數(shù)據(jù)庫(kù)引擎，數(shù)據(jù)庫(kù)引擎解釋SQL語(yǔ)句，提取出你需要的數(shù)據(jù)返回給你。因此，對(duì)訪問(wèn)者來(lái)說(shuō)，數(shù)據(jù)庫(kù)引擎就是SQL語(yǔ)句的解釋器。

7.1 MYISAM和InnoDB引擎的區(qū)別

主要區(qū)別：

• MYISAM 是非事務(wù)安全型的，而InnoDB是事務(wù)安全型；

• NYISAM鎖的粒度是表級(jí)鎖，而InnoDB支持行級(jí)鎖；

• MYISAM支持全文本索引，而InnoDB不支持全文索引

• MYISAM相對(duì)簡(jiǎn)單，所以在效率上要優(yōu)于InnoDB,小型應(yīng)用可以考慮使用MYISAM;

• MYISAM表是保存成文件的形式，在跨平臺(tái)的數(shù)據(jù)轉(zhuǎn)移中使用MYISAM存儲(chǔ)會(huì)省去不少的麻煩;

• （6）InnoDB表比MYISAM表更安全，可以在保證數(shù)據(jù)不丟失的情況下，切換非事務(wù)表到事務(wù)表；

應(yīng)用場(chǎng)景：

• MYISAM管理非事務(wù)表，它提供高速存儲(chǔ)和檢索，以及全文搜索能力，如果應(yīng)用中需要執(zhí)行大量的select查詢，那么MYISAM是更好的選擇。

• InnoDB用于事務(wù)處理應(yīng)用程序，具有眾多特性，包括ACID事務(wù)支持。如果應(yīng)用中需要執(zhí)行大量的insert或update操作，則應(yīng)該使用innodb，這樣可以提高多用戶并發(fā)操作的性能。

八、數(shù)據(jù)庫(kù)范式

目前關(guān)系數(shù)據(jù)庫(kù)有6種范式：第一范式{1NF}，第二范式{2NF}，第三范式{3NF}，巴斯—科德范式{BCNF}，第四范式{4NF}，第五范式{5NF，又稱完美范式}。滿足最低要求的范式是第一范式。在第一范式的基礎(chǔ)上進(jìn)一步滿足更多規(guī)范要求的稱為第二范式{2NF},其余范式依次類推，一般來(lái)說(shuō)，數(shù)據(jù)庫(kù)只需滿足第三范式（3NF）就OK了。
范式：

• 1NF:確保每列保持原子性；

• 2NF:確保表中的每列都和主鍵相關(guān)(聯(lián)合主鍵)；

• 3NF:確保表中的每列都和主鍵直接相關(guān)(外鍵)；

• BCNF:在1NF基礎(chǔ)上，任何非主屬性不能對(duì)主鍵子集依賴（在3NF基礎(chǔ)上消除對(duì)主碼子集的依賴）；

• 4NF:要求把同一表內(nèi)的多對(duì)多關(guān)系刪除；

• 5NF:從最終結(jié)構(gòu)重新建立原始結(jié)構(gòu)；

到此，關(guān)于“MySQL數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)點(diǎn)有哪些”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！