事務(wù)（Transaction）

3.1、什么是事務(wù)？

一個(gè)事務(wù)是一個(gè)完整的業(yè)務(wù)邏輯單元，不可再分。

比如：銀行賬戶轉(zhuǎn)賬，從A賬戶向B賬戶轉(zhuǎn)賬10000.需要執(zhí)行兩條update語句：
update t_act set balance = balance - 10000 where actno = ‘a(chǎn)ct-001’;
update t_act set balance = balance + 10000 where actno = ‘a(chǎn)ct-002’;

以上兩條DML語句必須同時(shí)成功，或者同時(shí)失敗，不允許出現(xiàn)一條成功，一條失敗。

要想保證以上的兩條DML語句同時(shí)成功或者同時(shí)失敗，那么就需要使用數(shù)據(jù)庫的“事務(wù)機(jī)制”。

3.2、和事務(wù)相關(guān)的語句只有：DML語句。（insert delete update）
為什么？因?yàn)樗鼈冞@三個(gè)語句都是和數(shù)據(jù)庫表當(dāng)中的“數(shù)據(jù)”相關(guān)的。
事務(wù)的存在是為了保證數(shù)據(jù)的完整性，安全性。

3.3、假設(shè)所有的業(yè)務(wù)都能使用1條DML語句搞定，還需要事務(wù)機(jī)制嗎？
不需要事務(wù)。

但實(shí)際情況不是這樣的，通常一個(gè)“事兒（事務(wù)【業(yè)務(wù)】）”需要多條DML語句共同聯(lián)合完成。

3.4、事務(wù)的特性？
事務(wù)包括四大特性：ACID

• A: 原子性：事務(wù)是最小的工作單元，不可再分。
• C: 一致性：事務(wù)必須保證多條DML語句同時(shí)成功或者同時(shí)失敗。
• I：隔離性：事務(wù)A與事務(wù)B之間具有隔離。
• D：持久性：持久性說的是最終數(shù)據(jù)必須持久化到硬盤文件中，事務(wù)才算成功的結(jié)束。

事務(wù)中存在一些概念：

事務(wù)（Transaction）：一批操作（一組DML）

開啟事務(wù)（Start Transaction）

回滾事務(wù)（rollback）

提交事務(wù)（commit）

SET AUTOCOMMIT：禁用或啟用事務(wù)的自動(dòng)提交模式

當(dāng)執(zhí)行DML語句是其實(shí)就是開啟一個(gè)事務(wù)

關(guān)于事務(wù)的回滾需要注意：只能回滾insert、delete和update語句，不能回滾select（回滾select沒有任何意義），對(duì)于create、drop、alter這些無法回滾.
事務(wù)只對(duì)DML有效果。

注意：rollback，或者commit后事務(wù)就結(jié)束了。回滾就是回到上一次提交的位置

關(guān)于事務(wù)之間的隔離性
事務(wù)隔離性存在隔離級(jí)別，理論上隔離級(jí)別包括4個(gè)：
第一級(jí)別：讀未提交（read uncommitted）

• 對(duì)方事務(wù)還沒有提交，我們當(dāng)前事務(wù)可以讀取到對(duì)方未提交的數(shù)據(jù)。
• 讀未提交存在臟讀（Dirty Read）現(xiàn)象：表示讀到了臟的數(shù)據(jù)。

第二級(jí)別：讀已提交（read committed）

• 對(duì)方事務(wù)提交之后的數(shù)據(jù)我方可以讀取到。
• 這種隔離級(jí)別解決了: 臟讀現(xiàn)象沒有了
• 讀已提交存在的問題是：不可重復(fù)讀。

第三級(jí)別：可重復(fù)讀（repeatable read）

• 這種隔離級(jí)別解決了：不可重復(fù)讀問題。
• 這種隔離級(jí)別存在的問題是：讀取到的數(shù)據(jù)是幻象。

第四級(jí)別：序列化讀/串行化讀（serializable）

• 解決了所有問題。

• 效率低。需要事務(wù)排隊(duì)。

  oracle數(shù)據(jù)庫默認(rèn)的隔離級(jí)別是：讀已提交。mysql數(shù)據(jù)庫默認(rèn)的隔離級(jí)別是：可重復(fù)讀。

索引

4.1、什么是索引？有什么用？
索引就相當(dāng)于一本書的目錄，通過目錄可以快速的找到對(duì)應(yīng)的資源。
在數(shù)據(jù)庫方面，查詢一張表的時(shí)候有兩種檢索方式：
第一種方式：全表掃描
第二種方式：根據(jù)索引檢索（效率很高）

索引為什么可以提高檢索效率呢？其實(shí)最根本的原理是縮小了掃描的范圍。索引雖然可以提高檢索效率，但是不能隨意的添加索引，因?yàn)樗饕彩菙?shù)據(jù)庫當(dāng)中的對(duì)象，也需要數(shù)據(jù)庫不斷的維護(hù)。是有維護(hù)成本的。比如，表中的數(shù)據(jù)經(jīng)常被修改這樣就不適合添加索引，因?yàn)閿?shù)據(jù)一旦修改，索引需要重新排序，進(jìn)行維護(hù)。添加索引是給某一個(gè)字段，或者說某些字段添加索引。select ename,sal from emp where ename = 'SMITH';當(dāng)ename字段上沒有添加索引的時(shí)候，以上sql語句會(huì)進(jìn)行全表掃描，掃描ename字段中所有的值。當(dāng)ename字段上添加索引的時(shí)候，以上sql語句會(huì)根據(jù)索引掃描，快速定位。4.2、怎么創(chuàng)建索引對(duì)象？怎么刪除索引對(duì)象？創(chuàng)建索引對(duì)象：create index 索引名稱 on 表名(字段名);刪除索引對(duì)象：drop index 索引名稱 on 表名;4.3、什么時(shí)候考慮給字段添加索引？（滿足什么條件）* 數(shù)據(jù)量龐大。（根據(jù)客戶的需求，根據(jù)線上的環(huán)境）* 該字段很少的DML操作。（因?yàn)樽侄芜M(jìn)行修改操作，索引也需要維護(hù)）* 該字段經(jīng)常出現(xiàn)在where子句中。（經(jīng)常根據(jù)哪個(gè)字段查詢）4.4、注意：主鍵和具有unique約束的字段自動(dòng)會(huì)添加索引。根據(jù)主鍵查詢效率較高。盡量根據(jù)主鍵檢索。

4.5、查看sql語句的行計(jì)劃：

mysql> explain select ename,sal from emp where sal = 5000;

給薪資sal字段添加索引：

create index emp_sal_index on emp(sal);

重新查看sql語句的行計(jì)劃

explain select ename,sal from emp where sal = 5000;

刪除剛才創(chuàng)建的索引：

drop index 索引名稱 on 表名 drop index emp_sal_index on emp;

4.6、索引底層采用的數(shù)據(jù)結(jié)構(gòu)是：B Tree

4.7、索引的實(shí)現(xiàn)原理？

• 通過B Tree縮小掃描范圍，底層索引進(jìn)行了排序，分區(qū)，索引會(huì)攜帶數(shù)據(jù)在表中的“物理地址”，

• 最終通過索引檢索到數(shù)據(jù)之后，獲取到關(guān)聯(lián)的物理地址，通過物理地址定位表中的數(shù)據(jù)，效率是最高的。

select ename from emp where ename = ‘SMITH’;
通過索引轉(zhuǎn)換為：
select ename from emp where 物理地址 = 0x3;

4.8、索引的分類？
單一索引：給單個(gè)字段添加索引
復(fù)合索引: 給多個(gè)字段聯(lián)合起來添加1個(gè)索引
主鍵索引：主鍵上會(huì)自動(dòng)添加索引
唯一索引：有unique約束的字段上會(huì)自動(dòng)添加索引
…

4.9、索引什么時(shí)候失效？

select ename from emp where ename like '%A%';

模糊查詢的時(shí)候，第一個(gè)通配符使用的是%，這個(gè)時(shí)候索引是失效的。

視圖(view)

5.1、什么是視圖？站在不同的角度去看到數(shù)據(jù)。（同一張表的數(shù)據(jù)，通過不同的角度去看待）。5.2、怎么創(chuàng)建視圖？怎么刪除視圖？create view myview as select empno,ename from emp;drop view myview;注意：只有DQL語句才能以視圖對(duì)象的方式創(chuàng)建出來。5.3、對(duì)視圖進(jìn)行增刪改查，會(huì)影響到原表數(shù)據(jù)。（通過視圖影響原表數(shù)據(jù)的，不是直接操作的原表） 可以對(duì)視圖進(jìn)行CRUD操作。5.4、面向視圖操作？mysql> select * from myview;+-------+--------+| empno | ename |+-------+--------+| 7369 | SMITH || 7499 | ALLEN || 7521 | WARD || 7566 | JONES || 7654 | MARTIN || 7698 | BLAKE || 7782 | CLARK || 7788 | SCOTT || 7839 | KING || 7844 | TURNER || 7876 | ADAMS || 7900 | JAMES || 7902 | FORD || 7934 | MILLER |+-------+--------+create table emp_bak as select * from emp;create view myview1 as select empno,ename,sal from emp_bak;update myview1 set ename='hehe',sal=1 where empno = 7369; // 通過視圖修改原表數(shù)據(jù)。delete from myview1 where empno = 7369; // 通過視圖刪除原表數(shù)據(jù)。5.5、視圖的作用？視圖可以隱藏表的實(shí)現(xiàn)細(xì)節(jié)。保密級(jí)別較高的系統(tǒng)，數(shù)據(jù)庫只對(duì)外提供相關(guān)的視圖，java程序員只對(duì)視圖對(duì)象進(jìn)行CRUD。

DBA命令

常用的命令DNA命令有：

新建用戶

授權(quán)

回收權(quán)限

數(shù)據(jù)導(dǎo)入導(dǎo)出（主要介紹）

6.1、將數(shù)據(jù)庫當(dāng)中的數(shù)據(jù)導(dǎo)出
在windows的dos命令窗口中執(zhí)行：（導(dǎo)出整個(gè)庫）
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p333

在windows的dos命令窗口中執(zhí)行：（導(dǎo)出指定數(shù)據(jù)庫當(dāng)中的指定表）mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123

6.2、導(dǎo)入數(shù)據(jù)
create database bjpowernode;
use bjpowernode;
source D:\bjpowernode.sql

數(shù)據(jù)庫設(shè)計(jì)三范式（重點(diǎn)內(nèi)容，面試經(jīng)常問）

7.1、什么是設(shè)計(jì)范式？

設(shè)計(jì)表的依據(jù)。按照這個(gè)三范式設(shè)計(jì)的表不會(huì)出現(xiàn)數(shù)據(jù)冗余。

7.2、三范式都是哪些？第一范式：任何一張表都應(yīng)該有主鍵，并且每一個(gè)字段原子性不可再分。第二范式：建立在第一范式的基礎(chǔ)之上，所有非主鍵字段完全依賴主鍵，不能產(chǎn)生部分依賴。多對(duì)多？三張表，關(guān)系表兩個(gè)外鍵。t_student學(xué)生表sno(pk) sname-------------------1 張三2 李四3 王五t_teacher 講師表tno(pk) tname---------------------1 王老師2 張老師3 李老師t_student_teacher_relation 學(xué)生講師關(guān)系表id(pk) sno(fk) tno(fk)----------------------------------1 1 32 1 13 2 24 2 35 3 16 3 3第三范式：建立在第二范式的基礎(chǔ)之上，所有非主鍵字段直接依賴主鍵，不能產(chǎn)生傳遞依賴。一對(duì)多？兩張表，多的表加外鍵。班級(jí)t_classcno(pk) cname--------------------------1 班級(jí)12 班級(jí)2學(xué)生t_studentsno(pk) sname classno(fk)---------------------------------------------101 張1 1102 張2 1103 張3 2104 張4 2105 張5 2提醒：在實(shí)際的開發(fā)中，以滿足客戶的需求為主，有的時(shí)候會(huì)拿冗余換執(zhí)行速度。7.3、一對(duì)一怎么設(shè)計(jì)？一對(duì)一設(shè)計(jì)有兩種方案：主鍵共享t_user_login 用戶登錄表id(pk) username password--------------------------------------1 zs 1232 ls 456t_user_detail 用戶詳細(xì)信息表id(pk+fk) realname tel ....------------------------------------------------1 張三 11111111112 李四 1111415621一對(duì)一設(shè)計(jì)有兩種方案：外鍵唯一。t_user_login 用戶登錄表id(pk) username password--------------------------------------1 zs 1232 ls 456t_user_detail 用戶詳細(xì)信息表id(pk) realname tel userid(fk+unique)....-----------------------------------------------------------1 張三 1111111111 22 李四 1111415621 1

總結(jié)

以上是生活随笔為你收集整理的SQL(七) - 事务、索引、视图的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。