搜尋
首頁資料庫mysql教程MySQL資料庫基礎知識點儲備(整理總結)

這篇文章為大家帶來了關於mysql資料庫的相關知識,主要整理了資料庫基礎的一下知識點,包括索引、語法順序、執行順序、預存程序等等相關問題,希望對大家有幫助。

MySQL資料庫基礎知識點儲備(整理總結)

推薦學習:mysql教學

#一、資料庫

##1.1 交易

1.1.1 事務四個特性(ACID)

    #原子性(
  • Atomicity 整個事務中的所要操作要麼全部提交成功,要麼全部失敗回滾。
  • 一致性(
  • Consistency 保證資料庫中的資料操作之前和操作之後的一致性。 (例如用戶多個帳戶之間的轉賬,但是用戶的總金額是不變的)
  • 隔離性(
  • Isolation 隔離性要求一個交易對資料庫中數據的修改,在未提交完成前對於其它事務是不可見的。 (即事務之間要串行執行)
  • 持久性(
  • Durability 持久性是指一個事務一旦被提交了,那麼對資料庫中的資料改變就是永久性的,即便是在資料庫系統遇到故障的情況下也不會遺失提交交易的操作。

SQL標準定義了四個隔離:(下面隔離性是由低到高,並發性由高到低)

    #未提交讀。
  • 最低的隔離等級,允許其他交易看到沒有提交的數據,會導致髒讀。
  • 已提交讀取。
  • 由於資料庫是讀寫分離,事務讀取的時候獲取讀鎖,但是在讀完之後立即釋放,釋放讀鎖之後,就可能被其他事務修改數據,再進行讀是就發現前後讀取數據的結果不同,造成不可重複讀。 (讀鎖不需要交易提交後釋放,而寫鎖需要事務提交後釋放。)
  • 可重複讀取。
  • 所有被
    select取得的資料都不能被修改,這樣就可以避免一個事務前後讀取不一致的情況。但是沒有辦法控制幻讀,因為這個時候其他事務不能更改所選的數據,但是可以增加數據;
  • 可串行化。
  • 所有事務一個接著一個執行,這樣可以避免幻讀,對於基於鎖來實現並發控制的資料庫來說,串行化要求在執行範圍查詢的時候,需要獲取範圍鎖,如果不是基於鎖實現並發控制的資料庫,則檢查到有違反串行操作的事務時,需回滾該事務。
總結:四個層級逐漸增強,每個層級解決問題,事務層級越高,效能越差。

隔離等級      不可重複讀取  幻讀

未提交讀取(read uncommitted) _  幻讀
未提交讀取(read uncommitted) 檢測可能  幻讀
⃀⃀  可能     可能
可重複讀取(repeatable read) 不可能 不可能     可能
可串行化(serializable)   不可能  不可能    不可能被提交總結# ,但會造成不可重複讀—>可重複讀取解決讀取結果前後不一致的情況,但是造成幻讀(以前沒有,現在有)—>可串行化解決了幻讀,但是增加很多範圍鎖,可能會造成鎖定逾時;

1.1.2 髒讀、不可重複讀取和幻讀

  • 髒讀(針對回滾的操作):交易T1更新了一行記錄的內容,但是並沒有提交所做的修改,事務T2讀取更新後的行,然後T1執行了回滾操作,取消了剛才所做的修改。現在T2讀取的行數就無效了(一個事務讀取了另一個事務);
  • 不可重複讀取(針對修改的操作):事務T1讀取了一行記錄,緊接著T2修改了T1剛才讀取的那一行記錄,然後T1又再讀這行記錄,發現與剛才讀取的結果不同。
  • 幻讀(針對更新的操作):事務T1讀取一條指定的where子句所傳回的結果集,然後T2事務新插入一行記錄,這行記錄恰好可以滿足T1所使用的查詢條件。然後T1再次對錶進行檢索,但又看到了T2插入的資料。 (第一次沒看到,第二次看到了)

二、索引

#2.1 索引特點

  1. 可以加快資料庫檢索速度;
  2. 只能創建在表上,不能創建到視圖上;
  3. 既可以直接創建又可以間接創建;
  4. 可以在優化隱藏中使用索引;
  5. 使用查詢處理器執行sql語句,在一個表上,一次只能使用一個索引。

2.1.1 索引優點

  1. #建立唯一性索引,保證資料庫表中每一行資料的唯一性;
  2. 大大加快資料檢索速度,這是創建索引的最主要原因;
  3. 加速資料庫表之間的鏈接,特別是在實現資料庫參考完整性方面特別有意義;
  4. 在使用分組和排序子句進行檢索時,同樣可以顯著減少查詢中分組和排序的時間;
  5. 透過使用索引,可以在查詢中使用最佳化隱藏器,提高系統效能;

#2.1.2 索引缺點

  1. 建立和維護索引耗費時間,這種時間隨著數量的增加而增加;
  2. #索引需要佔用實體空間,除了資料表佔用資料空間之外,每一個索引還要佔用一定的實體空間,如果建立聚集索引,那麼需要的空間就會更大;
  3. #當對資料表中的資料進行增加、刪除和修改的時候,索引也需要維護,降低資料維護速度;

#2.2 索引分類

##(1)普通索引(它沒有任何限制。)

(2)唯一性索引(索引列的值必須唯一,但允許有空值。)
(3)主鍵索引(一種特殊的唯一索引,不允許有空值。一般是在建表的時候同時創建主鍵索引。)
(4)組合索引
(5)聚集索引按照每張表的主鍵構造一顆B 樹,並且葉節點中存放著整張表的行記錄數據,因此也讓聚集索引的葉節點成為數據頁。
(6)非聚集索引(輔助索引)(頁節點不存放一整行記錄)。

2.3 索引失效

(1)如果條件中有or,即使其中有條件帶索引,也不會使用(盡量少用or);

(2)Like查詢是以%開頭,例如SELECT * FROM mytable WHEREt Name like'�min';
(3)如果列類型是字串,那一定要在條件中使用引號引起來,否則不會使用索引;

2.4 各引擎支援索引

MyISAMInnoDB,Memonry三個常用MySQL引擎類型比較: 索引   MyISAM索引   InnoDB索引   Memonry索引
B-tree索引  支援   ⃀指標⃀⃀⃀支援     不支援     支援
R-Tree索引  支援     不支援     不支援
Full-text索引 不支援    暫不支援        暫不支援        暫不支援#pac ,由於二元樹的深度過大而造成I/O讀寫過於頻繁,進而導致查詢效率低。因此採用多路樹結構,B樹的各種操作能使B樹保持在較低的高度。

B樹又叫平衡多路查找樹,一棵m階的B樹特性如下:

  • 1.樹中每個結點最多含有m個孩子(m>=2);
  • 2.除根結點和葉子結點外,其他每個結點至少有(ceil(m/2))個孩子(其中ceil(x)是一個取上限的函數);
  • 3.根結點至少有2個孩子(除非B樹只包含一個結點:根結點);
  • 4.所有葉子結點都出現在同一層,葉子結點不包含任何關鍵字資訊(可以看做是外部結點或查詢失敗的結點,指向這些結點的指針都為null);(註:葉子結點只是沒有孩子和指向孩子的指針,這些結點也存在,也有元素,類似紅黑樹中,每一個null指針即當做葉子結點,只是沒畫出來而已)
    MySQL資料庫基礎知識點儲備(整理總結)
    B 樹
    MySQL資料庫基礎知識點儲備(整理總結)
    在什麼情況下適合建立索引?
    (1)為經常出現在關鍵字order by, group by, distinct後面的字段,建立索引;
    (2)在union等集合操作的結果集字段上建立索引,其建立索引的目的同上;
    (3)為經常用作查詢選擇的字段,建立索引;
    (4)在經常用做表鏈接的屬性上,建立索引;
    (5)考慮使用索引覆蓋,對數據很少被更新的表,如果用戶經常只查詢其中的幾個字段,可以考慮在這幾個字段上建立索引,從而將表的掃描更改為索引的掃描。

三、Mysql語法順序

即當sql中存在下面的關鍵字時,它們要保持這樣的順序:

##select[distinct ]、from、join(如left join)、on、where、group

by、having、union、order by、limit;

四、Mysql執行順序

即在執行時sql依照下面的順序執行:

from、on、join、where、group by、having、select、distinct、union、order by

 group by要和聚合函數一起使用,

例如:

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer=’Bush’ or a.Customer = ‘Adams’ group by a.Customer;
實作多表查詢(內連接)

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

select from where同樣可以實現

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;
五、預存程序

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

呼叫預存程序

call procedure_bill();
查看預存程序

show procedure status like 'procedure_bill';
六、建立多對多資料表關係

在資料庫中,如果兩個表的之間的關係為多對多的關係,如:“學生表和課程表”,一個學生可以選多門課,一門課也可以被多個學生選;根據資料庫的設計原則,應形成第三張關聯表。

步驟1:建立三張資料表Student ,Course,Stu_Cour

/**学生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
CREATE TABLE Course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**学生课程关联表*/
CREATE TABLE Stu_Cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)
第二步:為Stu_Cour關聯表新增外鍵

/*添加外键约束*/
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);
完成建立!

    註:為已經新增好的資料表新增外鍵:
  • -語法:
    alter table 表名add constraint FK_ID foreign key(你的外鍵欄位名稱) REFERENCES 外表表名(對應的表的主鍵字段名);
例:

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

七、資料庫引擎(儲存引擎)

當你訪問資料庫時,不管是手動訪問,還是程式訪問,都不是直接讀寫資料庫文件,而是透過資料庫引擎去存取資料庫文件。

以關聯式資料庫為例,發SQL語句給資料庫引擎,資料庫引擎解釋SQL語句,提取出你需要的資料回傳給你。因此,對訪客來說,資料庫引擎就是SQL語句的解釋器。

7.1 MYISAM和InnoDB引擎的區別

#主要區別:

  • MYISAM 是非事務安全型的,而InnoDB是事務安全型;
  • NYISAM鎖定的粒度是表格級鎖定,而InnoDB支援行級鎖定;
  • MYISAM支援全文本索引,而InnoDB不支援全文索引
  • MYISAM相對簡單,所以在效率上優於InnoDB,小型應用可以考慮使用MYISAM;
  • #MYISAM表是保存成檔案的形式,在跨平台的資料轉移中使用MYISAM儲存會省去不少的麻煩;
  • (6)
  • InnoDB表比MYISAM表更安全,可以在保證資料不在遺失的情況下,切換非交易表到交易表;
應用場景:

  • MYISAM管理非事務表,它提供高速儲存和檢索,以及全文搜尋能力,如果應用程式中需要執行大量的select查詢,那麼 MYISAM是更好的選擇。
  • InnoDB用於事務處理應用程序,具有眾多特性,包括ACID事務支援。如果應用程式中需要執行大量的insertupdate操作,則應該使用innodb,這樣可以提高多用戶並發操作的效能。

八、資料庫範式

目前關聯式資料庫有6種範式:第一個範式{1NF},第二範式{2NF},第三範式{3NF},巴斯—科德範式{BCNF},第四範式{4NF},第五範式{5NF,又稱完美範式}。滿足最低要求的範式是第一個範式。在第一範式的基礎上進一步滿足更多規範要求的稱為第二範式{2NF},其餘範式依次類推,一般來說,資料庫只需滿足第三範式(3NF)就OK了。
範式:

  • 1NF:確保每列保持原子性;
  • 2NF:確保表中的每列都和主鍵相關(聯合主鍵);
  • #3NF:確保表中的每列都和主鍵直接相關(外鍵);
  • BCNF:在1NF基礎上,任何非主屬性不能對主鍵子集依賴(在3NF基礎上消除對主碼子集的依賴);
  • 4NF:要求把同一表內的多對多關係刪除;
  • 5NF:從最終結構重新建立原始結構;

推薦學習:mysql影片教學

以上是MySQL資料庫基礎知識點儲備(整理總結)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述
本文轉載於:CSDN。如有侵權,請聯絡admin@php.cn刪除
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

带你把MySQL索引吃透了带你把MySQL索引吃透了Apr 22, 2022 am 11:48 AM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了mysql高级篇的一些问题,包括了索引是什么、索引底层实现等等问题,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前By尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
1 個月前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
4 週前By尊渡假赌尊渡假赌尊渡假赌

熱工具

MantisBT

MantisBT

Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

VSCode Windows 64位元 下載

VSCode Windows 64位元 下載

微軟推出的免費、功能強大的一款IDE編輯器

Dreamweaver Mac版

Dreamweaver Mac版

視覺化網頁開發工具

SublimeText3 英文版

SublimeText3 英文版

推薦:為Win版本,支援程式碼提示!

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器