襄陽網(wǎng)站建設(shè)楚翼網(wǎng)絡(luò)大數(shù)據(jù)精準(zhǔn)獲客軟件
1.事務(wù)
1.一個事務(wù)其實就是一個完整的業(yè)務(wù)邏輯
如:轉(zhuǎn)賬,從A賬戶向B賬戶轉(zhuǎn)賬10000,將A賬戶的錢減去10000(update),將B賬戶的錢加上10000(update),這就是一個完整的業(yè)務(wù)邏輯
以上操作是一個最小的工作單元,要么同時成功,要么同時失敗,不可再分
只有DML語句才會有事務(wù)(insert,delete,update),因為這些語句是數(shù)據(jù)庫表中數(shù)據(jù)進(jìn)行增刪改的,只要一旦涉及增刪改,一定要考慮安全問題
2.對事務(wù)的理解
假設(shè)所有業(yè)務(wù)只要一條DML語句就能完成,還有必要存在事務(wù)機(jī)制嗎?
不必要,沒有存在的價值,正是做某件事需要多條DML語句聯(lián)合才能完成,所以需要事務(wù)的存在
說到底,一個事務(wù)其實就是多條DML語句同時成功,或同時失敗
3.事務(wù)如何實現(xiàn)批量DML語句同時成功或失敗?
InnoDB存儲引擎:提供一組用來記錄事務(wù)性活動的日志文件
事務(wù)開啟:
insert
delete
update
update
....
事務(wù)結(jié)束
在事務(wù)執(zhí)行過程中,每一條DML的操作都會記錄到’事務(wù)性活動的日志文件‘中,在這個過程中,可以提交事務(wù),也可以回滾事務(wù)
提交事務(wù)?
清空事務(wù)性活動的日志文件,將數(shù)據(jù)全部徹底持久化到數(shù)據(jù)庫表中,提交事務(wù)標(biāo)志著事務(wù)的結(jié)束,且是一種全部成功的結(jié)束
回滾事務(wù)?
將之前所有的DML操作全部撤銷,且清空事務(wù)性活動的日志文件,標(biāo)志著事務(wù)的結(jié)束,且是一種全部失敗的結(jié)束
4.怎么提交事務(wù)?怎么回滾事務(wù)?
提交事務(wù):commit;
回滾事務(wù):rollback;(只能回滾到上一次的提交點)
事務(wù)對應(yīng)的英文:transaction
在MySQL默認(rèn)的事務(wù)行為?
默認(rèn)情況下支持自動提交事務(wù),每執(zhí)行一條DML語句,則提交一次
自動提交不符合開發(fā)習(xí)慣,因為一個業(yè)務(wù)通常需要多條DML語句共同執(zhí)行,為了保證數(shù)據(jù)的安全,必須要求同時成功之后再提交,所以不能執(zhí)行一條就提交一次
將MySQL自動提交機(jī)制關(guān)閉?
start transaction;開啟事務(wù)
演示事務(wù):
start transaction;
insert into dept_bak values(10,'abc','aa');
insert into dept_bak values(10,'abc','bb');
select * from dept_bak;//有數(shù)據(jù)
rollback;
select * from dept_bak;//Empty
5.事務(wù)的四個特性
原子性:說明事務(wù)是最小的工作單元,不可再分
一致性:在同一個事務(wù)中,所有操作必須同時成功或同時失敗,保證數(shù)據(jù)一致性
隔離性:A事務(wù)和B事務(wù)之間具有一定隔離。
持久性:事務(wù)最終結(jié)束的一個保障,事務(wù)提交就相當(dāng)于將沒有保存到硬盤上的數(shù)據(jù)保存到硬盤上
6.隔離級別
A教室和B教室中有一道墻,可以很厚也可以很薄,這就是事務(wù)的隔離級別
4個隔離級別?
讀未提交:read uncommitted(最低隔離級別)《沒有提交就讀到》
? ? ? ? 事務(wù)A可以讀取到事務(wù)B未提交的數(shù)據(jù),存在臟讀(Dirty read)現(xiàn)象,即讀到了臟數(shù)據(jù)
這種隔離級別一般都是理論上的,大多數(shù)數(shù)據(jù)庫隔離級別都是二檔起步
讀已提交:read committed《提交后讀到》
? ? ? ? 事務(wù)A只能讀取到事務(wù)B提交之后的數(shù)據(jù),解決了臟讀現(xiàn)象,存在不可重復(fù)讀取數(shù)據(jù)問題,
這種隔離級別是比較真實的數(shù)據(jù),每一次讀到的數(shù)據(jù)絕對真實,Oracle數(shù)據(jù)庫默認(rèn)的隔離級別
? ? ? ? 不可重復(fù)讀取數(shù)據(jù)?
? ? ? ? 在事務(wù)開啟后,第一次讀到的數(shù)據(jù)是3條,當(dāng)前事務(wù)還沒有結(jié)束,可能第二次再讀取時,讀到的數(shù)據(jù)是4條,稱為不可重復(fù)讀取
可重復(fù)讀:repeatable read《提交后也讀不到》
? ? ? ? 事務(wù)A開啟之后,每一次在事務(wù)A中讀取到的數(shù)據(jù)都是一致的。即使事務(wù)B將數(shù)據(jù)已經(jīng)修改且提交,事務(wù)A讀取到的數(shù)據(jù)還是沒發(fā)生改變,解決了不可重復(fù)讀的問題,存在了幻影讀問題,每一次讀取的數(shù)據(jù)都是幻象,不夠真實,永遠(yuǎn)讀取的都是剛開啟事務(wù)時的數(shù)據(jù)。MySQL默認(rèn)的隔離級別
序列化/串行化:serializable(最高隔離級別)
最高隔離級別,效率最低,解決了所有問題
這種隔離級別表示事務(wù)排隊,不能并發(fā)
7.演示各種隔離級別
查看隔離級別:select @@tx_isolation
1.演示read uncommitted
set global transaction isolation level read uncommitted;設(shè)置全局隔離級別為讀未提交
事務(wù)A:
start transaction;
select * from t_user;//能查詢到事務(wù)B未提交數(shù)據(jù)
事務(wù)B:
start transaction;
insert into t_user values('zhangsan');//未提交
2.演示read committed
set global transaction isolation level read committed;
事務(wù)A:
start transaction;
select * from t_user;//不能查詢到
select * from t_user;//commit后能查詢到
事務(wù)B:
start transaction;
insert into t_user values('zhangsan');//未提交
commit;//提交
3.演示repeatable read
set global transaction isolation level?repeatable read;
事務(wù)A:???????
start transaction;
select * from t_user;//不能查詢到
select * from t_user;//commit后也不能查詢到
事務(wù)B:
start transaction;
insert into t_user values('zhangsan');//未提交
commit;//提交
4.演示serializable
set global transaction isolation level serializable;
事務(wù)A:???????
start transaction;
select * from t_user;//光標(biāo)卡著不動,等待另一個事務(wù)提交
select * from t_user;//提交后可查詢到
事務(wù)B:
start transaction;
insert into t_user values('zhangsan');//未提交
commit;//提交
2.索引
索引(index)是在數(shù)據(jù)庫表的字段上添加,為了提高查詢效率存在的一種機(jī)制,一張表的一個字段可以添加一個索引,多個字段聯(lián)合起來也可以添加索引,相當(dāng)于一本書的目錄,為了縮小掃描范圍而存在的一種機(jī)制
在MySQL數(shù)據(jù)庫當(dāng)中索引也需排序,索引排序和TreeSet數(shù)據(jù)結(jié)構(gòu)相同,底層是一個自平衡二叉樹,在MySQL當(dāng)中索引是一個B-Tree數(shù)據(jù)結(jié)構(gòu),遵循左小右大原則,采用中序遍歷
索引是各種數(shù)據(jù)庫進(jìn)行優(yōu)化的重要手段,優(yōu)化時優(yōu)先考慮的因素就是索引
1.索引的實現(xiàn)原理
假設(shè)有一張用戶表t_user
id(pk) | name | 硬盤上物理存儲編號 |
100 | zhangsan | 0x1111 |
120 | lisi | 0x2222 |
99 | wangwu | 0x8888 |
130 | zhaoliu | 0x9999 |
55 | jack | 0x6666 |
idIndex(id字段的索引對象)
select * from t_user where id = 130;
MySQL發(fā)現(xiàn)id字段上有索引對象,會通過索引對象idIndex進(jìn)行查找
通過索引對象定位到:130,得出物理編號:0x9999,此時SQL語句轉(zhuǎn)換:
select * from t_user where 物理編號 = 0x9999;
注1:在任何數(shù)據(jù)庫當(dāng)中,主鍵上都會自動添加索引對象,即id字段上自動有索引,在MySQL中,一個字段上如果有unique約束,也會自動創(chuàng)建索引對象
注2:在任何數(shù)據(jù)庫當(dāng)中,任何一張表的任何一條記錄在硬盤存儲上都有一個硬盤的物理存儲編號
注3:在MySQL中,索引是一個單獨的對象,不同的存儲引擎以不同形式存在。在MyISAM存儲引擎中,索引存儲在一個.MYI文件中,在InnoDB存儲引擎中,索引存儲在tablespace中,在MEMORY存儲引擎中,索引存儲在內(nèi)存里。
什么條件下會考慮給字段添加索引?
1.數(shù)據(jù)量龐大
2.該字段經(jīng)常出現(xiàn)在where后面,以條件形式存在,即這個字段總是被掃描
3.該字段很少DML(增刪改)操作(因為DML后索引需重新排序)
2.索引的創(chuàng)建和刪除
創(chuàng)建索引:
create index emp_ename_index on emp(ename);
給emp表的ename字段添加索引,起名:emp_ename_index
刪除索引:
drop index emp_ename_index on emp;
將emp表上的emp_ename_index索引對象刪除
查看一個SQL語句是否使用索引進(jìn)行檢索?
explain select * from emp where ename = 'Jack';
//type=ALL,掃描了14條記錄,說明沒有使用索引
create index emp_ename_index on emp(ename);
explain select * from emp where ename = 'Jack';
//type=ref,掃描了1條記錄,使用了索引
3.索引失效
失效1:
select * from emp where ename like '%T';
//即使添加了索引,也不會走索引,因為模糊匹配當(dāng)中以%開頭了,應(yīng)盡量避免模糊查詢%開頭
失效2:
select * from emp where ename = ‘jack’ or job = 'manager';
//使用or,要求兩邊條件字段都要有索引,否則不走索引
失效3:
creat index emp_job_sal_index on emp(job,sal);
select * from emp where job = 'manager';//使用索引
select * from emp where sal?= 3000;//失效
//使用復(fù)合索引,沒有使用左側(cè)的列查找,索引失效
失效4:
create index emp_sal_index on emp(sal);
select * from emp where sal+1 = 3000;
//在where當(dāng)中索引列參加了運算,索引失效
失效5:
select * from emp where lower(ename) = 'jack';
//在where當(dāng)中索引列使用了函數(shù)
失效6,7.....
4.索引的分類
單一索引:一個字段上添加索引
復(fù)合索引:兩個字段或多個字段上添加索引
主鍵索引:主鍵上添加索引
唯一性索引:具有unique約束的字段添加索引
.....
注:唯一性比較弱的字段上添加索引用處不大
3.視圖
view:站在不同的角度去看待同一份數(shù)據(jù)
1.創(chuàng)建視圖,刪除視圖?
創(chuàng)建:
create view dept_view as select * from dept;
刪除:
drop view dept_view;
注:只有DQL語句才能以view的形式創(chuàng)建
create view dept_view as DQL語句
2.用視圖做什么?
可以面向視圖對象進(jìn)行增刪改查,會導(dǎo)致原表被操作(即對視圖操作,影響原表數(shù)據(jù))
create view dept_view as select * from dept;
insert into dept_view(deptno,dname,loc) values(1,'sales','beijing');//原表插入數(shù)據(jù)
delete from dept_view;//原表被刪除
在實際開發(fā)中的作用?
假設(shè)有一條非常復(fù)雜的SQL語句,而這條語句需要在不同位置上反復(fù)使用,每一次使用都需要重新編寫,很麻煩,這時可把這條SQL語句以視圖對象形式新建,在需要編寫SQL語句時直接使用視圖對象,大大簡化開發(fā),且利于后期維護(hù),只需要修改視圖對象所映射的SQL語句
視圖不是在內(nèi)存當(dāng)中,也存儲在硬盤上,不會消失
再次注意:視圖對應(yīng)的語句只能是DQL語句,但視圖對象創(chuàng)建完成后,可對視圖進(jìn)行增刪改查
補充:增刪改查又叫CRUD
C:create(增)
R:retrieve(查,檢索)
U:update(改)
D:delete(刪)
4.DBA命令(了解)
1.新建用戶
create user xxx identified by '123456';
2.授權(quán)
....
3.回收
..
4.數(shù)據(jù)的導(dǎo)入和導(dǎo)出(掌握)
數(shù)據(jù)導(dǎo)出:
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p***
導(dǎo)出指定的表:
mysqldump bjpowernode emp>...
//在dos命令窗口中導(dǎo)出
數(shù)據(jù)導(dǎo)入
創(chuàng)建數(shù)據(jù)庫:create database bjpowernode;
使用數(shù)據(jù)庫:use bjpowernode;
初始化數(shù)據(jù)庫:source D:\bjpowernode.sql
//先登錄到MySQL數(shù)據(jù)庫服務(wù)器上
5.數(shù)據(jù)庫設(shè)計三范式
數(shù)據(jù)庫設(shè)計范式?
數(shù)據(jù)庫的設(shè)計依據(jù),教你怎么進(jìn)行數(shù)據(jù)庫表的設(shè)計
第一范式:要求如何一張表必須有主鍵,每一個字段原子性不可再分
第二范式:建立在第一范式的基礎(chǔ)上,要求所有非主鍵字段完全依賴主鍵,不要產(chǎn)生部分依賴
第三范式:建立在第二范式的基礎(chǔ)上,要求所有非主鍵字段直接依賴主鍵,不要產(chǎn)生傳遞依賴
設(shè)計數(shù)據(jù)庫表時,按照以上范式,可避免表中數(shù)據(jù)冗余,空間浪費
1.第一范式
最核心,最重要的范式,所有表的設(shè)計都需滿足
必須有主鍵,且每個字段都是原子性不可再分
編號 | 姓名 | 聯(lián)系方式 |
1000 | 張三 | zs@123.com,13959999999 |
1001 | 李四 | ls@123.com,13956666666 |
1002 | 王五 | ww@123.com,13958888888 |
以上學(xué)生表不滿足第一范式:
1.沒有主鍵
2.聯(lián)系方式可再分為郵箱和電話
編號(pk) | 姓名 | 郵箱 | 電話 |
1000 | 張三 | zs@123.com | 13959999999 |
1001 | 李四 | ls@123.com | 13956666666 |
1002 | 王五 | ww@123.com | 13958888888 |
口訣?一對一若表很龐大,拆分為兩張表
外鍵+unique約束
一對一,外鍵唯一
2.第二范式
建立在第一范式基礎(chǔ)上,要求所有非主鍵字段必須完全依賴主鍵,不產(chǎn)生部分依賴
學(xué)生與老師關(guān)系表(1個學(xué)生可能有多個老師,1個老師可能有多個學(xué)生)
復(fù)合主鍵:學(xué)生編號+教師編號(pk)
學(xué)生編號 | 教師編號 | 學(xué)生姓名 | 教師姓名 |
1001 | 001 | 張三 | 王老師 |
1002 | 002 | 李四 | 趙老師 |
1003 | 001 | 王五 | 王老師 |
1001 | 002 | 張三 | 趙老師 |
不滿足第二范式:
‘張三’依賴1001,‘王老師’依賴001,產(chǎn)生了部份依賴
部份依賴缺點:數(shù)據(jù)冗余,空間浪費,‘張三’‘王老師’重復(fù)
為了滿足第二范式,使用三張表表示多對多關(guān)系
學(xué)生表
學(xué)生編號(pk) | 學(xué)生名字 |
1001 | 張三 |
1002 | 李四 |
1003 | 王五 |
教師表
教師編號(pk) | 教師名字 |
001 | 王老師 |
002 | 趙老師 |
學(xué)生教師關(guān)系表
id(pk) | 學(xué)生編號(fk) | 教師編號(fk) |
1 | 1001 | 001 |
2 | 1002 | 002 |
3 | 1003 | 001 |
4 | 1001 | 002 |
口訣:多多對怎么設(shè)計?
多對多,三張表,關(guān)系表兩個外鍵!
3.第三范式
建立在第二范式基礎(chǔ)上,要求所有非主鍵字段必須直接依賴主鍵,不產(chǎn)生傳遞依賴
學(xué)生編號(pk) | 學(xué)生姓名 | 班級編號 | 班級名稱 |
1001 | 張三 | 01 | 高三一班 |
1002 | 李四 | 02 | 高三二班 |
1003 | 王五 | 03 | 高三三班 |
1004 | 趙六 | 03 | 高三三班 |
以上表格滿足第一范式(有主鍵),滿足第二范式(主鍵不是復(fù)合主鍵,沒有產(chǎn)生部分依賴)
不滿足第三范式:
高三一班依賴01,01依賴1001,產(chǎn)生了傳遞依賴,產(chǎn)生了數(shù)據(jù)冗余
設(shè)計一對多?
班級表:一
班級編號(pk) | 班級名稱 |
01 | 高三一班 |
02 | 高三二班 |
03 | 高三三班 |
學(xué)生表:多
學(xué)生編號(pk) | 學(xué)生姓名 | 班級編號(fk) |
1001 | 張三 | 01 |
1002 | 李四 | 02 |
1003 | 王五 | 03 |
1004 | 趙六 | 03 |
口訣?
一對多,兩張表,多的表加外鍵
注:數(shù)據(jù)庫設(shè)計三范式是理論上的,實踐上有偏差,最終目的是為了滿足客戶需求,有時會拿冗余換執(zhí)行速度,因為SQL中,表和表之間連接次數(shù)多,效率越低(笛卡爾積),有時可能存在冗余,但為了減少表的連接次數(shù),這樣做也合理,對于開發(fā)人員來說,SQL語句的編寫難度也降低