這篇文章為大家帶來了關於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 索引特點
- 可以加快資料庫檢索速度;
- 只能創建在表上,不能創建到視圖上;
- 既可以直接創建又可以間接創建;
- 可以在優化隱藏中使用索引;
- 使用查詢處理器執行sql語句,在一個表上,一次只能使用一個索引。
2.1.1 索引優點
- #建立唯一性索引,保證資料庫表中每一行資料的唯一性;
- 大大加快資料檢索速度,這是創建索引的最主要原因;
- 加速資料庫表之間的鏈接,特別是在實現資料庫參考完整性方面特別有意義;
- 在使用分組和排序子句進行檢索時,同樣可以顯著減少查詢中分組和排序的時間;
- 透過使用索引,可以在查詢中使用最佳化隱藏器,提高系統效能;
#2.1.2 索引缺點
- 建立和維護索引耗費時間,這種時間隨著數量的增加而增加;
- #索引需要佔用實體空間,除了資料表佔用資料空間之外,每一個索引還要佔用一定的實體空間,如果建立聚集索引,那麼需要的空間就會更大;
- #當對資料表中的資料進行增加、刪除和修改的時候,索引也需要維護,降低資料維護速度;
#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 各引擎支援索引
MyISAM,
InnoDB,
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指針即當做葉子結點,只是沒畫出來而已)
B 樹
在什麼情況下適合建立索引?
(1)為經常出現在關鍵字order by, group by, distinct後面的字段,建立索引;
(2)在union等集合操作的結果集字段上建立索引,其建立索引的目的同上;
(3)為經常用作查詢選擇的字段,建立索引;
(4)在經常用做表鏈接的屬性上,建立索引;
(5)考慮使用索引覆蓋,對數據很少被更新的表,如果用戶經常只查詢其中的幾個字段,可以考慮在這幾個字段上建立索引,從而將表的掃描更改為索引的掃描。
三、Mysql語法順序
即當sql中存在下面的關鍵字時,它們要保持這樣的順序:
##select[distinct ]、from、join(如left join)、on、where、group四、Mysql執行順序即在執行時sql依照下面的順序執行:by、having、union、order by、limit;
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);
7.1 MYISAM和InnoDB引擎的區別
#主要區別:- MYISAM
是非事務安全型的,而
InnoDB是事務安全型;
- NYISAM
鎖定的粒度是表格級鎖定,而
InnoDB支援行級鎖定;
- MYISAM
支援全文本索引,而
InnoDB不支援全文索引
- MYISAM
相對簡單,所以在效率上優於
InnoDB,小型應用可以考慮使用
MYISAM;
- #MYISAM
表是保存成檔案的形式,在跨平台的資料轉移中使用
MYISAM儲存會省去不少的麻煩;
(6) - InnoDB
表比
MYISAM表更安全,可以在保證資料不在遺失的情況下,切換非交易表到交易表;
-
MYISAM
管理非事務表,它提供高速儲存和檢索,以及全文搜尋能力,如果應用程式中需要執行大量的select
查詢,那麼MYISAM
是更好的選擇。 -
InnoDB
用於事務處理應用程序,具有眾多特性,包括ACID
事務支援。如果應用程式中需要執行大量的insert
或update
操作,則應該使用innodb
,這樣可以提高多用戶並發操作的效能。
八、資料庫範式
目前關聯式資料庫有6種範式:第一個範式{1NF},第二範式{2NF},第三範式{3NF},巴斯—科德範式{BCNF},第四範式{4NF},第五範式{5NF,又稱完美範式}。滿足最低要求的範式是第一個範式。在第一範式的基礎上進一步滿足更多規範要求的稱為第二範式{2NF},其餘範式依次類推,一般來說,資料庫只需滿足第三範式(3NF)就OK了。
範式:
- 1NF:確保每列保持原子性;
- 2NF:確保表中的每列都和主鍵相關(聯合主鍵);
- #3NF:確保表中的每列都和主鍵直接相關(外鍵);
- BCNF:在1NF基礎上,任何非主屬性不能對主鍵子集依賴(在3NF基礎上消除對主碼子集的依賴);
- 4NF:要求把同一表內的多對多關係刪除;
- 5NF:從最終結構重新建立原始結構;
推薦學習:mysql影片教學
以上是MySQL資料庫基礎知識點儲備(整理總結)的詳細內容。更多資訊請關注PHP中文網其他相關文章!

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

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

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

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

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

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

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

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


熱AI工具

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

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

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

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

VSCode Windows 64位元 下載
微軟推出的免費、功能強大的一款IDE編輯器

Dreamweaver Mac版
視覺化網頁開發工具

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

記事本++7.3.1
好用且免費的程式碼編輯器