一、視圖
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
版權聲明:本文為博主原創文章,轉載請附上博文鏈接!