卷妹帶你學資料庫---5天沖刺Day5

卷妹帶你學資料庫—5天沖刺Day5

👩‍💻部落格首頁：京與舊鋪的部落格首頁

✨歡迎關注🖱點贊🎀收藏⭐留言✒

🔮本文由京與舊鋪原創，

😘系列專欄：java學習

👕參考網課：尚矽谷

💻首發時間：🎞2022年6月15日🎠

🎨你做三四月的事，八九月就會有答案，一起加油吧

🀄如果覺得部落客的文章還不錯的話，請三連支援一下部落客哦

🎧最後的話，作者是一個新人，在很多方面還做的不好，歡迎大佬指正，一起學習哦，沖沖沖

💬推薦一款模拟面試、刷題神器👉點選進入網站

🛒導航小助手🎪

文章目錄

卷妹帶你學資料庫---5天沖刺Day5
🛒導航小助手🎪

@[toc]

索引失效的情況
索引分類
視圖
DBA常用指令
資料庫設計三範式

學生編号學生姓名聯系方式
學生編号(pk) 學生姓名郵箱位址聯系電話
學生編号學生姓名教師編号教師姓名
學生編号+教師編号(pk) 學生姓名教師姓名
學生編号(pk) 學生名字
教師編号(pk) 教師姓名
id(pk) 學生編号(fk) 教師編号(fk)
學生編号（PK）學生姓名班級編号班級名稱
班級編号(pk) 班級名稱
學生編号（PK）學生姓名班級編号(fk)

囑咐

索引失效的情況

索引有失效的時候，什麼時候索引失效呢？

失效的第1種情況：

select * from emp where ename like ‘%T’;

ename上即使添加了索引，也不會走索引，為什麼？

原因是因為模糊比對當中以“%”開頭了！

盡量避免模糊查詢的時候以“%”開始。

這是一種優化的手段/政策。

mysql> explain select * from emp where ename like ‘%T’;

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

失效的第2種情況：

使用or的時候會失效，如果使用or那麼要求or兩邊的條件字段都要有

索引，才會走索引，如果其中一邊有一個字段沒有索引，那麼另一個

字段上的索引也會實作。是以這就是為什麼不建議使用or的原因。

mysql> explain select * from emp where ename = ‘KING’ or job = ‘MANAGER’;

±—±------------±------±-----±----------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±----------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±----------------±-----±--------±-----±-----±------------+

失效的第3種情況：

使用複合索引的時候，沒有使用左側的列查找，索引失效

什麼是複合索引？

兩個字段，或者更多的字段聯合起來添加一個索引，叫做複合索引。

create index emp_job_sal_index on emp(job,sal);

mysql> explain select * from emp where job = ‘MANAGER’;

±—±------------±------±-----±------------------±------------------±--------±------±-----±------------+

±—±------------±------±-----±------------------±------------------±--------±------±-----±------------+

| 1 | SIMPLE | emp | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | Using where |

±—±------------±------±-----±------------------±------------------±--------±------±-----±------------+

mysql> explain select * from emp where sal = 800;

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

失效的第4種情況：

在where當中索引列參加了運算，索引失效。

mysql> create index emp_sal_index on emp(sal);

explain select * from emp where sal = 800;

±—±------------±------±-----±--------------±--------------±--------±------±-----±------------+

±—±------------±------±-----±--------------±--------------±--------±------±-----±------------+

| 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where |

±—±------------±------±-----±--------------±--------------±--------±------±-----±------------+

mysql> explain select * from emp where sal+1 = 800;

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

失效的第5種情況：

在where當中索引列使用了函數

explain select * from emp where lower(ename) = ‘smith’;

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

±—±------------±------±-----±--------------±-----±--------±-----±-----±------------+

索引分類

單一索引：一個字段上添加索引。

複合索引：兩個字段或者更多的字段上添加索引。

主鍵索引：主鍵上添加索引。

唯一性索引：具有unique限制的字段上添加索引。

…

注意：唯一性比較弱的字段上添加索引用處不大。

視圖

什麼是視圖？

視圖：站在不同的角度去看待同一份資料。

視圖建立和删除

建立視圖對象：

create view dept2_view as select * from dept2;

删除視圖對象：

drop view dept2_view;

注意：隻有DQL語句才能以view的形式建立。

create view view_name as 這裡的語句必須是DQL語句;

視圖的用途

《友善，簡化開發，利于維護》

我們可以面向視圖對象進行增删改查，對視圖對象的增删改查，會導緻

原表被操作！（視圖的特點：通過對視圖的操作，會影響到原表資料。）

//面向視圖查詢

select * from dept2_view;

// 面向視圖插入

insert into dept2_view(deptno,dname,loc) values(60,‘SALES’, ‘BEIJING’);

// 查詢原表資料

mysql> select * from dept2;

±-------±-----------±---------+

| DEPTNO | DNAME | LOC |

±-------±-----------±---------+

| 10 | ACCOUNTING | NEW YORK |

| 20 | RESEARCH | DALLAS |

| 30 | SALES | CHICAGO |

| 40 | OPERATIONS | BOSTON |

| 60 | SALES | BEIJING |

±-------±-----------±---------+

// 面向視圖删除

mysql> delete from dept2_view;

// 查詢原表資料

mysql> select * from dept2;

Empty set (0.00 sec)

假設有一條非常複雜的SQL語句，而這條SQL語句需要在不同的位置上反複使用。

每一次使用這個sql語句的時候都需要重新編寫，很長，很麻煩，怎麼辦？

可以把這條複雜的SQL語句以視圖對象的形式建立。

在需要編寫這條SQL語句的位置直接使用視圖對象，可以大大簡化開發。

并且利于後期的維護，因為修改的時候也隻需要修改一個位置就行，隻需要

修改視圖對象所映射的SQL語句。

我們以後面向視圖開發的時候，使用視圖的時候可以像使用table一樣。

可以對視圖進行增删改查等操作。視圖不是在記憶體當中，視圖對象也是

存儲在硬碟上的，不會消失。

再提醒一下：

視圖對應的語句隻能是DQL語句。

但是視圖對象建立完成之後，可以對視圖進行增删改查等操作。

小插曲：

增删改查，又叫做：CRUD。

CRUD是在公司中程式員之間溝通的術語。一般我們很少說增删改查。

一般都說CRUD。

C:Create（增）

R:Retrive（查：檢索）

U:Update（改）

D:Delete（删）

DBA常用指令

重點掌握：

資料的導入和導出（資料的備份）

其它指令了解一下即可。

資料導出？

注意：在windows的dos指令視窗中：

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456

可以導出指定的表嗎？

mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

資料導入？

注意：需要先登入到mysql資料庫伺服器上。

然後建立資料庫：create database bjpowernode;

使用資料庫：use bjpowernode

然後初始化資料庫：source D:\bjpowernode.sql

資料庫設計三範式

什麼是資料庫設計範式？

資料庫表的設計依據。教你怎麼進行資料庫表的設計。

資料庫設計範式共有？

3個。

第一範式：要求任何一張表必須有主鍵，每一個字段原子性不可再分。

第二範式：建立在第一範式的基礎之上，要求所有非主鍵字段完全依賴主鍵，

不要産生部分依賴。

第三範式：建立在第二範式的基礎之上，要求所有非主鍵字段直接依賴主鍵，

不要産生傳遞依賴。

聲明：三範式是面試官經常問的，是以一定要熟記在心！

設計資料庫表的時候，按照以上的範式進行，可以避免表中資料的備援，空間的浪費。

第一範式

最核心，最重要的範式，所有表的設計都需要滿足。

必須有主鍵，并且每一個字段都是原子性不可再分。

學生編号學生姓名聯系方式

1001 張三 [email protected],1359999999

1002 李四 [email protected],13699999999

1001 王五 [email protected],13488888888

以上是學生表，滿足第一範式嗎？

不滿足，第一：沒有主鍵。第二：聯系方式可以分為郵箱位址和電話

學生編号(pk) 學生姓名郵箱位址聯系電話

1001 張三 [email protected] 1359999999

1002 李四 [email protected] 13699999999

1003 王五 [email protected] 13488888888

第二範式：

建立在第一範式的基礎之上，

要求所有非主鍵字段必須完全依賴主鍵，不要産生部分依賴。

學生編号學生姓名教師編号教師姓名

1001 張三 001 王老師

1002 李四 002 趙老師

1003 王五 001 王老師

1001 張三 002 趙老師

這張表描述了學生和老師的關系：（1個學生可能有多個老師，1個老師有多個學生）

這是非常典型的：多對多關系！

分析以上的表是否滿足第一範式？

不滿足第一範式。

怎麼滿足第一範式呢？修改

學生編号+教師編号(pk) 學生姓名教師姓名

1001 001 張三王老師

1002 002 李四趙老師

1003 001 王五王老師

1001 002 張三趙老師

學生編号教師編号，兩個字段聯合做主鍵，複合主鍵（PK: 學生編号+教師編号）

經過修改之後，以上的表滿足了第一範式。但是滿足第二範式嗎？

不滿足，“張三”依賴1001，“王老師”依賴001，顯然産生了部分依賴。

産生部分依賴有什麼缺點？

資料備援了。空間浪費了。“張三”重複了，“王老師”重複了。

為了讓以上的表滿足第二範式，你需要這樣設計：

使用三張表來表示多對多的關系！！！！

學生表

學生編号(pk) 學生名字

1001 張三

1002 李四

1003 王五

教師表

教師編号(pk) 教師姓名

001 王老師

002 趙老師

學生教師關系表

id(pk) 學生編号(fk) 教師編号(fk)

1 1001 001

2 1002 002

3 1003 001

4 1001 002

背口訣：

多對多怎麼設計？

多對多，三張表，關系表兩個外鍵！！！！！！！！！！！！！！！

第三範式

第三範式建立在第二範式的基礎之上

要求所有非主鍵字典必須直接依賴主鍵，不要産生傳遞依賴。

學生編号（PK）學生姓名班級編号班級名稱

1001 張三 01 一年一班

1002 李四 02 一年二班

1003 王五 03 一年三班

1004 趙六 03 一年三班

以上表的設計是描述：班級和學生的關系。很顯然是1對多關系！

一個教室中有多個學生。

分析以上表是否滿足第一範式？

滿足第一範式，有主鍵。

分析以上表是否滿足第二範式？

滿足第二範式，因為主鍵不是複合主鍵，沒有産生部分依賴。主鍵是單一主鍵。

分析以上表是否滿足第三範式？

第三範式要求：不要産生傳遞依賴！

一年一班依賴01，01依賴1001，産生了傳遞依賴。

不符合第三範式的要求。産生了資料的備援。

那麼應該怎麼設計一對多呢？

班級表：一

班級編号(pk) 班級名稱

01 一年一班

02 一年二班

03 一年三班

學生表：多

學生編号（PK）學生姓名班級編号(fk)

1001 張三 01

1002 李四 02

1003 王五 03

1004 趙六 03

背口訣：

一對多，兩張表，多的表加外鍵！！！！！！！！！！！！

總結表的設計

一對多：

一對多，兩張表，多的表加外鍵！！！！！！！！！！！！

多對多：

多對多，三張表，關系表兩個外鍵！！！！！！！！！！！！！！！

一對一：

一對一放到一張表中不就行了嗎？為啥還要拆分表？

在實際的開發中，可能存在一張表字段太多，太龐大。這個時候要拆分表。

一對一怎麼設計？

沒有拆分表之前：一張表

t_user

id login_name login_pwd real_name email address…

---------------------------------------------------------------------------

1 zhangsan 123 張三 zhangsan@xxx

2 lisi 123 李四 lisi@xxx

…

這種龐大的表建議拆分為兩張：

t_login 登入資訊表

id(pk) login_name login_pwd

---------------------------------

1 zhangsan 123

2 lisi 123

t_user 使用者詳細資訊表

id(pk)    real_name   email       address........ login_id(fk+unique)
-----------------------------------------------------------------------------------------

100     張三        zhangsan@xxx                1
200     李四        lisi@xxx                  2

口訣：一對一，外鍵唯一！！！！！！！！！！

囑咐

資料庫設計三範式是理論上的。

實踐和理論有的時候有偏差。

最終的目的都是為了滿足客戶的需求，有的時候會拿備援換執行速度。

因為在sql當中，表和表之間連接配接次數越多，效率越低。（笛卡爾積）

有的時候可能會存在備援，但是為了減少表的連接配接次數，這樣做也是合理的，

并且對于開發人員來說，sql語句的編寫難度也會降低。

卷妹帶你學資料庫---5天沖刺Day5