一、視圖
視圖是將SQL陳述句的查詢結果當做虛擬表物體化保存起來，以后可以反復使用
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
drop view teacher2course;
-- 視圖使用頻率不高
二、觸發器(trigger)
觸發器：滿足特點條件之后自動執行，
在MySQL只有三種情況下可以觸發：
- 針對表的增
- 增前
- 增后
- 針對表的改
- 改前
- 改后
- 針對表的刪
- 刪前
- 刪后
語法結構
create trigger 觸發器的名字 before/after insert/update/delete on 表名 for each row
begin
sql陳述句
end
觸發器名字在命名的時候推薦使用如下方式：
tri_after_insert_t1、tri_before_delete_t1
如何臨時修改SQL陳述句的結束符：
delimiter $$
在MySQL中NEW特指資料物件可以通過點的方式獲取欄位對應的資料
id name pwd hobby
1 jason 123 read
NEW.name >>> jason
案例：
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代表執行失敗
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
delimiter $$ -- 將mysql默認的結束符由;換成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then -- 新記錄都會被MySQL封裝成NEW物件
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; -- 結束之后記得再改回來，不然后面結束符就都是$$了
-- 往表cmd中插入記錄，觸發觸發器，根據IF的條件決定是否插入錯誤日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('tony','0755','ls -l /etc',NOW(),'yes'),
('tony','0755','cat /etc/passwd',NOW(),'no'),
('tony','0755','useradd xxx',NOW(),'no'),
('tony','0755','ps aux',NOW(),'yes');
-- 查詢errlog表記錄
select * from errlog;
-- 查看觸發器
show triggers;
-- 洗掉觸發器
drop trigger tri_after_insert_cmd;
三、事務（重要）
3.1 四大特性（ACID）
-
A：原子性
每個事務都是不可分割的最小單位(同一個事務內的多個操作要么同時成功要么同時失敗)
-
C：一致性
事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態，一致性與原子性是密切相關的，
-
I：隔離性
事務與事務之間彼此不干擾，即一個事務內部的操作及使用的資料對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾，
-
D：持久性
持久性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的；接下來的其他操作或故障不應該對其有任何影響
3.2 事務相關操作
start transcation; -- 開啟事務
諸多SQL操作
rollback; -- 回滾到操作之前的狀態
commit; -- 確認事務操作，之后不能回滾
示例如下 ：
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);
-- 修改資料之前先開啟事務操作
start transaction;
-- 修改操作
update user set balance=900 where name='jason'; -- 買支付100元
update user set balance=1010 where name='egon'; -- 中介拿走10元
update user set balance=1090 where name='tank'; -- 賣家拿到90元
-- 回滾到上一個狀態
rollback;
-- 開啟事務之后，只要沒有執行commit操作，資料其實都沒有真正重繪到硬碟
commit;
"""開啟事務檢測操作是否完整，不完整主動回滾到上一個狀態，如果完整就應該執行commit操作"""
站在python代碼的角度，應該實作的偽代碼邏輯：
try:
update user set balance=900 where name='jason'; #買支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #賣家拿到90元
except 例外:
rollback;
else:
commit;
四、存盤程序
類似于python中的自定義函式，
4.1 無參存盤程序
delimiter $$
create procedure p1()
begin
select * from user;
end $$
delimiter ;
-- 呼叫
call p1();
4.2 有參存盤程序
delimiter $$
create procedure p2(
in m int, -- in表示這個引數必須只能是傳入不能被回傳出去
in n int,
out res int -- out表示這個引數可以被回傳出去，還有一個inout表示即可以傳入也可以被回傳出去
)
begin
select * from user where id > m and id < n;
set res=0; -- 用來標志存盤程序是否執行
end $$
delimiter ;
4.3 python代碼操作存盤程序
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
passwd='456852',
db='db6',
charset='utf8',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p2',(1,3,10))
# @_p1_0=1,@_p1_1=3,@_p1_2=10;
print(cursor.fetchall())
五、函式
相當于Python中的內置函式，
ps：可以通過
help 函式名查看幫助資訊!
-
移除指定字符
Trim()、LTrim()、RTrim() -
大小寫轉換
Lower()、Upper() -
獲取左右起始指定個數字符
Left()、Right() -
回傳讀音相似值
Soundex() """ eg: 客戶表中有一個顧客登記的用戶名為J.Lee 但如果這是輸入錯誤真名其實叫J.Lie,可以使用soundex匹配發音類似的 where Soundex(name)=Soundex('J.Lie') """ -
日期格式
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'); -- 更多日期處理相關函式 where Date(sub_time) = '2015-03-01' where Year(sub_time)=2016 AND Month(sub_time)=07; adddate 增加一個日期 addtime 增加一個時間 datediff計算兩個日期差值 ...
六、流程控制
-- if判斷
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
-- while回圈
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
七、索引與慢查詢優化（重要）
索引可以簡單的理解為幫助你加快資料查詢速度的工具，也可以把索引比喻成書的目錄，
索引的建立涉及到幾種資料結構：
- B樹
- B+樹（葉子節點添加了指標）
- B*樹（基于B+樹在枝節點也添加了指標）
將某個欄位添加成索引就相當于依據該欄位建立了一顆B+樹，從而加快查詢速度，
如果某個欄位沒有添加索引，那么依據該欄位查詢資料會非常的慢（遍歷查找），
7.1 索引分類
-
primary key主鍵索引除了有加速查詢的效果之外，還具有一定的約束條件；
-
unique key唯一鍵索引，除了有加速查詢的效果之外，還具有一定的約束條件；
-
index key普通索引，只有加速查詢的效果，沒有額外約束條件；
注意外鍵
foreign key不是索引，它僅僅是用來創建表與表之間關系的，
7.2 創建索引
-- 創建唯一索引需要提前排查是否有重復資料
select count(欄位) from 表名;
select count(distinct(欄位)) from 表名;
-- 查看當前表內部索引值
show index from 表名;
-- 創建主鍵索引
alter table t1 add primary key pri_id(id);
-- 創建唯一索引
alter table t1 add unique key uni_age(age);
-- 創建普通索引
alter table t1 add index idx_name(name);
-- 前綴索引(屬于普通索引)
"""
避免對大列建索引,如果有就使用前綴索引
eg:博客內容 百度搜索內容等
"""
alter table t1 add index idx_name(name(4));
-- 聯合索引(屬于普通索引，遵循最左匹配原則)
alter table t1 add index idx_all(name,age,sex);
-- 洗掉索引
alter table t1 drop index 索引名(idx_name、idx_all...);
7.3 explain句式
- 全表掃描：不走索引，遍歷表查找資料，效率極低，生產環境下盡量不要書寫類似SQL；
- 索引掃描：走索引，加快資料查詢，建議書寫該型別SQL；
explain 就是幫助我們查看SQL陳述句屬于那種掃描，
常見的索引掃描型別：
indexrangerefeq_refconstsystemnull
從上到下，性能從最差到最好，生產環境下認為至少要達到
range級別，
不走索引情況（熟悉四條及以上）：
-
沒有查詢條件，或者查詢條件沒有建立索引；
-
查詢結果是原表中的大部分資料（%25以上）；
-
索引本身失效，統計資料不真實；
-
查詢條件使用函式在索引列上或者對索引列進行運算，運算包括(+，-，*等)；
-
隱式轉換導致索引失敗；
eg：欄位是字串型別，查詢使用整形
-
<> ，not in不走索引單獨的>,<,in 有可能走，也有可能不走，和結果集有關，盡量結合業務添加limit、or或in盡量改成union，
-
like "%_"百分號在最前面不走； -
單獨參考聯合索引里非第一位置的索引列（不遵循最左匹配原則）；
索引的創建會加快資料的查詢速度，但是一定程度會拖慢資料的插入和洗掉速度，
八、隔離級別
在SQL標準中定義了四種隔離級別，每一種級別都規定了一個事務中所做的修改，
InnoDB支持所有隔離級別：set transaction isolation level
-
read uncommitted(未提交讀)事務中的修改即使沒有提交，對其他事務也都是可見的，事務可以讀取未提交的資料，這一現象也稱之為
"臟讀" -
read committed(提交讀)—大多數資料庫系統默認的隔離級別一個事務從開始直到提交之前所作的任何修改對其他事務都是不可見的，這種級別也叫做
"不可重復讀" -
repeatable read(可重復讀)—MySQL默認隔離級別能夠解決
"臟讀"問題，但是無法解決"幻讀"所謂
"幻讀"指的是當某個事務在讀取某個范圍內的記錄時另外一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀取該范圍的記錄會產生幻行，InnoDB和XtraDB通過多版本并發控制(MVCC)及間隙鎖策略解決了該問題， -
serializable(可串行讀)強制事務串行執行，很少使用該級別
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/431449.html
標籤：MySQL