一、視圖
1、什么是視圖
???虛擬表：在硬盤中沒有的，通過查詢在內存中拼接的表
???視圖：通過查詢得到一張虛擬表，保存下來，下次可直接使用
?
存儲過程和觸發器的異同點、2、為什么要用視圖
???如果要頻繁使用一張虛擬表，可以不用重復查詢
?
3、如何用視圖
create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id;
?
數據庫觸發器的主要作用是。4、刪除視圖
drop view teacher2course;
?
5、強調
1、在硬盤中，視圖只有表結構文件(.frm)，沒有表數據文件(.idb); 其在后臺對應的是一條sql語句
2、視圖通常是用于查詢，盡量不要修改視圖中的數據
數據庫觸發器工作原理，?
二、觸發器
1、觸發器：在滿足對某張表數據的增、刪、改的情況下，自動觸發的功能稱之為觸發器
?
2、為何要用觸發器？
???觸發器專門針對我們對某一張表數據增insert、刪delete、改update的行為，這類行為一旦執行就會觸發觸發器的執行，即自動運行另外一段sql代碼
mysql觸發器和存儲過程。?
3、創建觸發器語法
# 針對插入
create trigger tri_after_insert_t1 after insert on 表名 for each row ?#after
begin ?#begin end 在sql中用于標志同一執行級別的代碼；相當于python中的縮進
sql代碼。。。
可以為視圖建立索引觸發器，end
?
create trigger tri_before_insert_t2 before insert on 表名 for each row??#before
begin
????sql代碼。。。
end
觸發器只能用于一個表或視圖、?
# 針對刪除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
????sql代碼。。。
end
邏輯視圖定義。?
create trigger tri_before_delete_t2 before delete on 表名 for each row
begin
????sql代碼。。。
end
?
對視圖創建觸發器。# 針對修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
????sql代碼。。。
end
?
觸發器可以調用存儲過程嗎？create trigger tri_before_update_t2 before update on 表名 for each row
begin
????sql代碼。。。
end
?
#刪除觸發器
數據庫 觸發器，drop trigger tri_after_insert_cmd;
?
4、案例
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
#修改sql中的默認分隔符，從；改成
#修改sql中的默認分隔符，從；改成
create trigger tri_after_insert_cmd after insert on cmd for each row ??#觸發器名稱要盡可能詳細如例
begin
????if NEW.success?= 'no' then ? # NEW為mysql對新插入記錄的封裝名； 如果不用mysql的觸發器，可以在自己的應用程序中加if判斷
????????insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
????end if; ???# mysql中固定用法
end
#
#
表一行語句終結
delimiter ;????# 將sql中的默認分隔符改回；
?
三、事務（重要）
1、什么是事務
???開啟一個事務可以包含一些sql語句，這些sql語句要么同時成功；要么一個都別想成功，稱之為事務的原子性
?
2、事務的作用：轉賬等，屏蔽因網絡傳輸部分失效而帶來的影響
?
3、如何用 （以下為運行邏輯，非代碼）
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
?
insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);
?
try:
????update user set balance=900 where name='wsb'; #買支付100元
????update user set balance=1010 where name='egon'; #中介拿走10元
????update user set balance=1090 where name='ysb'; #賣家拿到90元
except 異常:
????rollback;???#如果異常，回滾到前一個狀態，即balance都為1000
else:
commit;???#如果無異常，修改數據庫（硬盤上的數據）
?
四、存儲過程（重要）
#在mysql中函數是不能單獨使用的，必須放在sql語句中使用；但存儲過程是可以單獨使用的
1、存儲過程：存儲過程包含了一系列可執行的sql語句，存儲過程存放于MySQL中，通過調用它的名字可以執行其內部的一堆sql
?
2、三種開發模型
????a、（如果不考慮非技術因素，各方面效率最高的選擇；但考慮到實際情況，一般不被選擇）
????????應用程序：只需要開發應用程序的邏輯
????????mysql：編寫好存儲過程，以供應用程序調用
????????優點：開發效率，執行效率都高
????????缺點：考慮到人為因素、跨部門溝通等問題，會導致擴展性差
?
????b、（與c的思路一致，會被使用）
????????應用程序：除了開發應用程序的邏輯，還需要編寫原生sql
????????mysql：
????????優點：比方式1，擴展性高(非技術性的)
????????缺點：
????????????1、開發效率，執行效率都不如方式1
????????????2、編寫原生sql太過于復雜，而且需要考慮到sql語句的優化問題
?
????c、（考慮到非技術因素，最常見的選擇）
????????應用程序：開發應用程序的邏輯，不需要編寫原生sql，基于別人編寫好的框架來處理數據，ORM（object relationship matching）
????????mysql：
????????優點：不用再編寫純生sql，這意味著開發效率比方式2高，同時兼容方式2擴展性高的好處
????????缺點：執行效率連方式2都比不過
?
3、創建存儲過程
delimiter $$
create procedure p1( ? #創建存儲過程p1
????in m int, ???#mysql中參數必須先規定類型和用途（in，out，inout）
????in n int,
????out res int ?#res用于接收返回值
)
begin
????select tname from teacher where tid > m and tid < n;
????set res=0;
end $$
delimiter;
?
4、如何用存儲過程
a、直接在mysql中調用
set @res=10 ??#mysql中變量的定義要用@abc的形式
call p1(2,4,@res);#調用p1存儲過程
?
select @res; ?#查看結果
?
b、在python程序中調用
import pymysql
?
conn=pymysql.connect(
????host='127.0.0.1',
????port=3306,
????user='root',
????password='123',
????charset='utf8',
????database='db42'
)
?
cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p1',(2,4,10)) #@_p1_0=2,@_p1_1=4,@_p1_2=10 ?#pymysql幫助對傳入變量進行以上變形
print(cursor.fetchall())
cursor.execute('select @_p1_2;') ?#查看返回值，確認執行結果
print(cursor.fetchone())
?
cursor.close()
conn.close()
?
5、事務的使用 （事務+存儲過程）
delimiter //
create PROCEDURE p5(
????OUT p_return_code tinyint
)
BEGIN
????DECLARE exit handler for sqlexception ?#如果出現錯誤，執行
????BEGIN
????????-- ERROR
????????set p_return_code = 1;
????????rollback;
????END;
?
????DECLARE exit handler for sqlwarning ???#如果出現警告，執行
????BEGIN
????????-- WARNING
????????set p_return_code = 2;
????????rollback;
????END;
?
????START TRANSACTION; ????????????????????#事務的應用
????????update user set balance=900 where id =1;
????????update user123 set balance=1010 where id = 2;
????????update user set balance=1090 where id =3;
????COMMIT;
?
????-- SUCCESS
????set p_return_code = 0; #0代表執行成功
?
END //
delimiter ;
?
#在python中調用存儲過程
import pymysql
?
conn=pymysql.connect(
????host='127.0.0.1',
????port=3306,
????user='root',
????password='123',
????charset='utf8',
????database='db44'
)
?
cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p6',(100,)) #@_p5_0 = 100
cursor.execute('select @_p6_0')
print(cursor.fetchone())
?
cursor.close()
conn.close()
?
五、函數
1、強調：mysql內置的函數只能在sql語句中使用
mysql> select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
?
2、補充
a、select * from s1 \G???#表字段太多（字段行顯示不全）時，用\G將表豎著顯示出來： row1 哪些字段： 對應的內容，row2。。。
b、視圖、觸發器、事務、存儲過程、函數、流程控制皆是在庫下面建立
?
六、流程控制 （if，while，case）
#case
select
case
when name = 'egon' then
????name
when name = 'alex' then
????concat(name,'_BIGSB')
else
????concat(name,'_SB')
end
?
from emp;
?
七、索引
1、為什么要用索引
???對于一個應用來說，對數據庫的讀寫比例基本上是10:1,即讀多寫少
???而且對于寫來說極少出現性能問題，大多數性能問題都是慢查詢
???提到加速查詢，就必須用到索引
?
2、什么是索引
???索引就相當于書的目錄，是mysql中一種專門的數據結構，稱為key（primary key，unique，index key）
???索引的本質原理就是通過不斷地縮小查詢范圍，來降低io次數從而提升查詢性能
???強調：一旦為表創建了索引，以后的查詢都會先查索引，再根據索引定位的結果去找數據（同一種方式）
?
3、索引的影響（先有數據，后有索引；索引，亦占硬盤空間）
???a、在表中有大量數據的前提下，創建索引速度會很慢
???b、在索引創建完畢后，對表的查詢性能會大幅度提升，但是寫性能會降低
?
4、B+樹：只有葉子節點才存放真實數據，其他的（根節點、枝節點）都是虛擬數據
?
???a、一次IO走一個block塊（磁盤塊）
???b、淺藍-block塊； 深藍-數據項； 黃-指針
???c、為降低IO次數-》降低樹的高度-》數據項越小越好（block塊大小一定的情況下，數據項越小，存放的數據量最多，占用的樹葉少）-》采用占空間少的做索引（比如id）
?
5、索引的最左匹配特性（從左到右一次匹配）
g、應該對哪些字段做索引：
1、應該對數據量小的字段做索引（數據量小，比對查詢的快）
2、應該對區分度高的字段做索引
3、索引字段不要參與運算
?
6、聚集索引（primary key）：組織數據時就按照此索引組織
???特點：葉子節點存放的一整條數據的對應的關系（聚集在一起）
?
7、輔助索引（unique，index）
???特點：如果是按照這個字段創建的索引，那么葉子節點存放的是：{名字：名字所在那條記錄的主鍵的值（名字與其在聚集索引中存放的數據的綁定關系）}
根據這個關系再去聚集索引里面找對應的值。
????????innodb的索引存放在表數據里（.idb）
????a、覆蓋索引：只在輔助索引的葉子節點中就已經找到了所有我們想要的數據
????select name from user where name='egon';
????b、回表操作
????select age from user where name='egon';
?
8、補充： 權限管理
???a、授權
???grant all on *.* to 'egon' @'192.168.12.%' identified by '123';???#all指的是授予grant以外的全部權限
???flush privileges; #立即刷新權限
???exist
???b、刪除權限
???revoke select on db1.* from 'egon'@'%';
?
9、其他：
???a、對區別度低的數據不要建索引，且索引不是越多越好
???b、注意范圍問題，或者說條件不明確的問題 （條件中出現這些符號或關鍵字：>、>=、<、<=、!= 、between...and...、like）
???c、mysql中對 a and b and c。。的形式，會先從有索引或區分度高的數據入手開始查詢，以減少查詢范圍
???d、mysql中對 a or b or c。。的形式，會從左至右依次查詢
???e、索引列不要參與計算，如果非要運算 a\12=3 ===> a=3\12
???f、聯合索引的原則：
?????區別度高、范圍小的放左邊，區別度低、范圍大的放右邊，按從左到右的順序查詢
?????（email, name, gender, id）：可以搜(email)、(email,name, gender)等（含email），不能搜（id）等。
---------------------
作者：樂石風華
來源：CSDN
原文：https://blog.csdn.net/qq_35540539/article/details/81271367
版權聲明：本文為博主原創文章，轉載請附上博文鏈接！
