【一】視圖

(1)視圖須知概念

1.什么是視圖？
視圖就是通過查詢得到一張虛擬表，然后保存下來，下次可以直接使用
2.為什么要用視圖？
如果要頻繁操作一張虛擬表(拼表組成)，就可以制作成視圖，后續直接操作
注意：視圖所獲得的虛擬表與原表數據無關

(2)視圖相關語法(和表的語法一致)

1.創建視圖
create view 視圖名(表名) as 虛擬表的查詢SQL語句
eg:
CREATE VIEW my_view AS SELECT column1, column2 FROM my_table WHERE condition;
2.查
SELECT * FROM my_view;
3.改
UPDATE my_view SET column1 = value1 WHERE condition;
4.刪
DROP VIEW my_view;

【二】觸發器

(1)觸發器概念

1.什么是觸發器
滿足對表數據進行增刪改的情況下，自動觸發的功能，稱為觸發器
2.觸發器的六種使用情況
● 增前
● 增后
● 刪前
● 刪后
● 改前
● 改后

(2)語法結構

1.
create trigger 觸發器的名字 
before/after insert/update/delete 
on 表名 for each row 
beginSQL語句
end
2.查看當前庫下所有的觸發器信息
show triggers\G;
3.刪除當前庫下指定的觸發器信息
drop trigger 觸發器名稱;
4.觸發器的名字一般情況下建議采用下列布局形式tri_after_insert_t1tri_before_update_t2tri_before_delete_t3

(3)使用實例

表：
CREATE TABLE cmd (id INT PRIMARY KEY auto_increment,USER CHAR (32),priv CHAR (10),cmd CHAR (64),sub_time datetime, #提交時間success enum ('yes', 'no') #0代表執行失敗
);
需求：cmd表插入數據的success如果值為no 則去errlog表中插入一條記錄
delimiter $$  # 將mysql默認的結束符由;換成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
beginif NEW.success = 'no' then  # 新記錄都會被MySQL封裝成NEW對象insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);end if;
end $$
delimiter ;  # 結束之后記得再改回來，不然后面結束符就都是$$了

【三】事務(掌握)

(1)事務概念

"""當讓多條SQL語句保持一致性的時候(要么同時成功，要么同事失敗)，可以考慮使用事務"""
1.什么是事務
事務可以包含諸多SQL語句并且這些SQL語句要么同時執行成功 要么同時執行失敗 這是事務的原子性特點
事務的作用
2.四大特性(ACID)
ACIDA:原子性一個事務是一個不可分割的整體 里面的操作要么都成立要么都不成立C:一致性事務必須使數據庫從一個一致性狀態變到另外一個一致性狀態I:隔離性并發編程中 多個事務之間是相互隔離的 不會彼此干擾D:持久性事務一旦提交 產生的結果應該是永久的 不可逆的

(2)事務的使用

 1.創建表及錄入數據create table user(id int primary key auto_increment,name char(32),balance int);insert into user(name,balance)values('jason',1000),('kevin',1000),('tank',1000);2.事務操作開啟一個事務的操作start transaction;編寫SQL語句(同屬于一個事務)update user set balance=900 where name='jason';update user set balance=1010 where name='kevin'; update user set balance=1090 where name='tank';事務回滾(返回執行事務操作之前的數據庫狀態)rollback; ?# 執行完回滾之后 事務自動結束事務確認(執行完事務的主動操作之后 確認無誤之后 需要執行確認命令)commit; ?# 執行完確認提交之后 無法回滾 事務自動結束

【四】存儲過程

(1)存儲過程的概念

1.什么是存儲過程
存儲過程就類似于Python中的自定義函數

?

(2)如何使用存儲過程

1.定義
# 相當于定義函數
delimiter $$
create procedure 存儲過程的名字(形參1,形參2...)
beginsql 代碼
end $$
delimiter ;
2.相當于調用函數
call p1()
3.查看存儲過程具體信息
show create procedure pro1;
4.查看所有存儲過程
show procedure status;
5.刪除存儲過程
drop procedure pro1;

(3)使用實例

類似于有參函數delimiter $$create procedure p1(in m int,  # in表示這個參數必須只能是傳入不能被返回出去in n int,  out res int  # out表示這個參數可以被返回出去，還有一個inout表示即可以傳入也可以被返回出去)beginselect tname from userinfo where id > m and id < n;set res=0;  # 用來標志存儲過程是否執行end $$delimiter ;# 針對res需要先提前定義set @res=10;  定義select @res;  查看call p1(1,5,@res)  調用select @res  查看

【五】函數

(1)函數概念

1.什么是函數
跟存儲過程是有區別的，存儲過程是自定義函數，函數就類似于內置函數

(2)語法

# 1.移除指定字符
Trim、LTrim、RTrim# 2.大小寫轉換
Lower、Upper# 3.獲取左右起始指定個數字符
Left、Right# 4.返回讀音相似值(對英文效果)
Soundex
"""
eg:客戶表中有一個顧客登記的用戶名為J.Lee但如果這是輸入錯誤真名其實叫J.Lie,可以使用soundex匹配發音類似的where Soundex(name)=Soundex('J.Lie')
"""# 5.日期格式:date_format
'''在MySQL中表示時間格式盡量采用2022-11-11形式'''
CREATE TABLE blog (id INT PRIMARY KEY auto_increment,NAME CHAR (32),sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES('第1篇','2015-03-01 11:31:21'),('第2篇','2015-03-11 16:31:21'),('第3篇','2016-07-01 10:21:31'),('第4篇','2016-07-22 09:23:21'),('第5篇','2016-07-23 10:11:11'),('第6篇','2016-07-25 11:21:31'),('第7篇','2017-03-01 15:33:21'),('第8篇','2017-03-01 17:32:21'),('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

【六】流程控制

# python if判斷if 條件:子代碼elif 條件:子代碼else:子代碼
# js if判斷if(條件){子代碼}else if(條件){子代碼}else{子代碼}
# MySQL if判斷if 條件 then子代碼elseif 條件 then子代碼else子代碼end if;# MySQL while循環DECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECT num ;SET num = num + 1 ;END WHILE ;

【七】索引

(1)索引概念

1.什么是索引
索引（在MySQL中也叫做“鍵（key）”）是存儲引擎用于快速找到記錄的一種數據結構，這也是索引
2.MySQL中索引的類型
創建主鍵約束（PRIMARY KEY）、唯一約束（UNIQUE）、外鍵約束（FOREIGN KEY）時，會自動創建 對應列的索引。
3.索引的本質
通過不斷的縮小想要的數據范圍篩選出最終的結果 
4.id	name	pwd		post_comment  addr  age 基于id查找數據很快 但是基于addr查找數據就很慢 解決的措施可以是給addr添加索引
'''索引雖然好用 但是不能無限制的創建!!!'''
5.**索引的影響:*** 在表中有大量數據的前提下，創建索引速度會很慢* 在索引創建完畢后，對表的查詢性能會大幅度提升，但是寫的性能會降低
6.索引的底層數據結構是b+樹b樹 紅黑樹 二叉樹 b*樹 b+樹上述結構都是為了更好的基于樹查找到相應的數據

(2)索引語法用法

1.查看索引
show index from 表名;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | sn         |            1 | sn          | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | classes_id |            1 | classes_id  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)
2.創建索引
對于非主鍵、非唯一約束、非外鍵的字段，可以創建普通索引
create index  索引名 on 表名(字段名);# 索引名的命名規則一般是：index_表名_列名
3.刪除索引
drop index 索引名 on 表名

(3)聚集索引(主鍵索引)

  【1】什么是聚集索引● 聚集索引（Clustered Index）是關系型數據庫中的一種索引類型，它決定了表中數據的物理存儲順序。##### ● 在一個表中，只能有一個聚集索引。● 聚集索引對表進行了重新組織，使得數據按照聚集索引的鍵值順序存儲在磁盤上。● 由于聚集索引決定了數據的物理存儲順序，因此通過聚集索引可以快速地找到特定范圍內的數據● MySQL的聚簇索引是基于B+樹的數據結構實現的，它會把數據存儲在索引的葉子節點上，葉子節點之間按順序鏈接，使得按主鍵進行搜索時速度最快。● 如果沒有主鍵，如果按主鍵搜索，速度是最快的。【2】聚集索引的特點● 數據的邏輯順序和物理順序是一致的，通過聚集索引可以直接訪問特定行，因此聚集索引的查詢速度很快。##### ● 聚集索引的鍵值必須是唯一的，不允許重復值存在。● 當表中的數據發生插入、刪除或更新操作時，聚集索引需要進行相應的調整以保持數據的有序性，這可能會對性能產生一定影響。#####  ● 如果表中沒有定義聚集索引，那么表的數據存儲順序將按照物理地址來存儲。● 表不建立主鍵，也會有個隱藏字段是主鍵，是主鍵索引● 主鍵索引對于按照主鍵進行查詢的性能非常高。
創建：ALTER TABLE table_name ADD PRIMARY KEY (column);

(4)輔助索引(普通索引)

查詢數據的時候不可能都是用id作為篩選條件，也可能會用name，password等字段信息，那么這個時候就無法利用到聚集索引的加速查詢效果。就需要給其他字段建立索引，這些索引就叫輔助索引
ALTER TABLE table_name ADD INDEX index_name (column);

(5)唯一索引（unique）

【1】什么是唯一索引
● 唯一索引是指該索引的所有值都是唯一的，不允許出現重復值。
【2】語法
● MySQL中可以通過以下語法創建唯一索引
ALTER TABLE table_name ADD UNIQUE KEY index_name (column);
【3】注意事項
● 與普通索引不同的是，如果嘗試向包含唯一索引的列中插入重復的值，則會引發錯誤。
● 唯一索引可以用于確保數據的一致性和完整性，并且可以幫助提高查詢性能。

(6)全文索引

【1】什么是全文索引
● 全文索引是一種特殊的索引，它可以用來存儲和檢索文本數據。
● 全文索引可以包含單詞、短語和其他類型的文本內容，并支持模糊匹配和近似匹配。
【2】語法
● MySQL中可以通過以下語法創建全文索引
CREATE FULLTEXT INDEX index_name ON table_name (column);
【3】注意事項
● 需要注意的是，只有MyISAM和InnoDB存儲引擎支持全文索引。
● 此外，創建全文索引可能會增加索引維護的成本，并且可能會降低其他類型的查詢性能。
● 因此，在創建全文索引時需要權衡其利弊。

(7)覆蓋索引

只在輔助索引的葉子節點中就已經找到了所有我們想要的數據select name from user where name='jason';

(8)非覆蓋索引

雖然查詢的時候命中了索引字段name，但是要查的是age字段，所以還需要利用主鍵才去查找select age from user where name='jason';

【八】隔離機制

(1)臟讀

1.臟讀是指當一個事務讀取了其他事務尚未提交的數據時發生的現象。
2.意思就是比如說你一個事務，沒有commit前，讀的就是臟數據，只要回滾就不存在了

(2)不可重復讀

1.不可重復讀指的是在一個事務內，最開始讀到的數據和事務結束前的任意時刻讀到的同一批數據出現不一致的情況
2.就是在每次讀同一個數據時，可能數據被修改讀出來就不一樣

(3)幻讀

1.幻讀的本質在于某一次select操作得到的結果無法支撐后續的業務操作。
2.就是你第一次讀的時候是這么多數據，但是當你插入數據，發現不能插入了，因為已經存在了

(4)解決辦法

[1]Read uncommitted（讀未提交）
最低的隔離級別，在這個級別下，一個事務可以讀取到另一個事務尚未提交的數據
可能導致臟讀（Dirty Read）問題，即讀取到未經驗證的數據。[2]Read committed（讀已提交）
在這個級別下，一個事務只能讀取到已經提交的數據，避免了臟讀問題。
但是可能會出現不可重復讀（Non-repeatable Read）問題
即同一事務中，兩次讀取相同的記錄可能得到不同的結果，因為其他事務修改了這些記錄。[3]Repeatable read（可重復讀取）
在這個級別下，事務開始讀取數據后，其他事務無法修改這些數據，保證了同一個事務內兩次讀取相同記錄的一致性。
但是可能會出現幻讀（Phantom Read）問題，
即同一查詢在同一事務中兩次執行可能返回不同的結果，因為其他事務插入或刪除了符合查詢條件的記錄。[4]Serializable（串行化）
最高級別的隔離級別，要求事務串行執行，事務之間完全隔離，避免了臟讀、不可重復讀和幻讀問題。
但是這會犧牲并發性能，因為并發事務被限制為順序執行。

【九】鎖機制

目的：提升數據安全性
分類：按粒度分細---》粗行級鎖表級鎖頁級鎖

(1)行級鎖1.?級鎖是Mysql中鎖定粒度最細的?種鎖○ 表示只針對當前操作的?進?加鎖。
2.?級鎖能??減少數據庫操作的沖突。○ 其加鎖粒度最?，但加鎖的開銷也最?。
3. ?級鎖分為共享鎖和排他鎖。
4.通俗的說就是在你寫的時候必須要加鎖，只能一個一個寫入，如果一起寫數據就會錯亂
5.行級鎖鎖的是索引命中索引以后才會鎖行如果沒有命中索引會把整張表都鎖起來。
6.流程：
命中主鍵索引就鎖定這條語句命中的主鍵索引命中輔助索引就會先鎖定這條輔助索引再鎖定相關的主鍵索引考慮到性能，innodb默認支持行級鎖但是只有在命中索引的情況下才鎖行，否則鎖住所有行本質還是行鎖但是此刻相當于鎖表了

【十】三大范式

三大范式是數據庫設計的基礎，用于確保數據的準確性、完整性和一致性，避免數據的冗余和不一致性。以下是三大范式的詳細解釋：

1.第一范式（1NF）
定義：數據庫表的每一列都是不可分割的基本數據項，同一列中不能有多個值或重復的屬性。
特點：
強調列的原子性，即列不可再分。
每一列都是獨立的、不可分割的。
保證了數據的準確性，避免了數據的冗余和不一致性。
實際應用：
通常用于記錄簡單、固定的實體關系。

2.第二范式（2NF）
完全依賴”指的是非主屬性不能僅依賴于主鍵的一部分
定義：在滿足第一范式的基礎上，非主屬性必須完全依賴于整個主鍵，而不是主鍵的一部分。
特點：
表必須有一個主鍵。
沒有包含在主鍵中的列必須完全依賴于主鍵。
保證了數據的完整性，避免了數據的冗余和不一致性。
實際應用：
通常用于記錄復雜、動態的實體關系。

3.第三范式（3NF）
定義：在滿足第二范式的基礎上，任何非主屬性不依賴于其他非主屬性（即消除傳遞依賴）。
特點：
非主鍵列必須直接依賴于主鍵。
不能存在非主鍵列A依賴于非主鍵列B，非主鍵列B依賴于主鍵的情況（即傳遞依賴）。
進一步保證了數據的完整性，避免了數據的冗余和不一致性。
實際應用：
通常用于記錄復雜、動態的實體關系，尤其是當數據表中存在大量數據冗余時。

4.總結：
三大范式理論是數據庫設計的基礎，它們通過確保數據的原子性、完整性和一致性，避免了數據的冗余和不一致性。在實際應用中，根據數據的復雜性和需求，可以選擇不同的范式進行數據庫設計。隨著數據庫技術的發展，新的范式理論也在不斷涌現，為數據庫設計提供了更多的選擇。