一厨诸、范式與關(guān)系
假設(shè)有一個(gè)名為employee的員工表镶殷，它有九個(gè)屬性：id(員工編號(hào))、name(員工名稱)微酬、mobile(電話)绘趋、zip(郵編)颤陶、province(省份)、city(城市)陷遮、district(區(qū)縣)滓走、deptNo(所屬部門編號(hào))、deptName(所屬部門名稱)帽馋、表總數(shù)據(jù)如下：
| id | name | mobile | zip | province | city | district | deptNo | deptName |
|---|---|---|---|---|---|---|---|---|
| 101 | 張三 | 13910000001 13910000002 | 100001 | 北京 | 北京 | 海淀區(qū) | D1 | 部門1 |
| 101 | 張三 | 13910000001 13910000002 | 100001 | 北京 | 北京 | 海淀區(qū) | D2 | 部門2 |
| 102 | 李四 | 13910000003 | 200001 | 上海 | 上海 | 靜安區(qū) | D3 | 部門3 |
| 103 | 王五 | 13910000004 | 510001 | 廣東省 | 廣州 | 白云區(qū) | D4 | 部門4 |
| 103 | 王五 | 13910000004 | 510001 | 廣東省 | 廣州 | 白云區(qū) | D5 | 部門 5 |
由于此員工表是非規(guī)范化的搅方，我們將面對(duì)如下的問題。
- 修改異常：上表中張三有兩條記錄绽族，因?yàn)樗`屬于兩個(gè)部門姨涡。如果我們要修改張三的地址，必修修改兩行記錄吧慢。假如一個(gè)部門得到了張三的新地址并進(jìn)行了更新绣溜，而另一個(gè)部門沒有，那么此時(shí)張三在表中會(huì)存在兩個(gè)不同的地址娄蔼，導(dǎo)致了數(shù)據(jù)不一致
- 新增異常： 假如一個(gè)新員工假如公司怖喻，他正處于入職培訓(xùn)階段，還沒有被正式分配到某個(gè)部門岁诉，如果
deptNo字段不允許為空锚沸，我們就無法向employee表中新增該員工的數(shù)據(jù)。- 刪除異常： 假設(shè)公司撤銷了D3部門涕癣，那么在刪除
deptNo為D3的行時(shí)哗蜈，會(huì)將李四的信息也一并刪除。因?yàn)樗`屬于D3這一部門坠韩。
第一范式(1NF)
表中的列只能含有原子性(不可再分)的值距潘。
表中的張三有兩個(gè)手機(jī)號(hào)存儲(chǔ)在mobile列中，違反了 1NF 規(guī)則只搁。為了使表滿足 1NF音比，數(shù)據(jù)應(yīng)該修改如下：
| id | name | mobile | zip | province | city | district | deptNo | deptName |
|---|---|---|---|---|---|---|---|---|
| 101 | 張三 | 13910000001 | 100001 | 北京 | 北京 | 海淀區(qū) | D1 | 部門1 |
| 101 | 張三 | 13910000002 | 100001 | 北京 | 北京 | 海淀區(qū) | D1 | 部門1 |
| 101 | 張三 | 13910000001 | 100001 | 北京 | 北京 | 海淀區(qū) | D2 | 部門2 |
| 101 | 張三 | 13910000002 | 100001 | 北京 | 北京 | 海淀區(qū) | D2 | 部門2 |
| 102 | 李四 | 13910000003 | 200001 | 上海 | 上海 | 靜安區(qū) | D3 | 部門3 |
| 103 | 王五 | 13910000004 | 510001 | 廣東省 | 廣州 | 白云區(qū) | D4 | 部門4 |
| 103 | 王五 | 13910000004 | 510001 | 廣東省 | 廣州 | 白云區(qū) | D5 | 部門 5 |
第二范式(2NF)
第二范式要同時(shí)滿足下面兩個(gè)條件
- 滿足第一范式
- 沒有部分依賴
例如，員工表的一個(gè)候選鍵是{id氢惋，mobile洞翩，deptNo}，而deptName依賴于deptNo焰望，同樣 name 依賴于 id骚亿，因此不是 2NF的。為了滿足第二范式的條件熊赖，需要將這個(gè)表拆分成employee来屠、dept、employee_dept、employee_mobile四個(gè)表俱笛。如下：
員工表 employee
| id | name | zip | province | city | district |
|---|---|---|---|---|---|
| 101 | 張三 | 100001 | 北京 | 北京 | 海淀區(qū) |
| 102 | 李四 | 200001 | 上海 | 上海 | 靜安區(qū) |
| 103 | 王五 | 510001 | 廣東省 | 廣州 | 白云區(qū) |
部門表 dept
| deptNo | deptName |
|---|---|
| D1 | 部門1 |
| D2 | 部門2 |
| D3 | 部門3 |
| D4 | 部門4 |
| D5 | 部門5 |
員工部門關(guān)系表 employee_dept
| id | deptNo |
|---|---|
| 101 | D1 |
| 101 | D2 |
| 102 | D3 |
| 103 | D4 |
| 104 | D5 |
員工電話表 employee_mobile
| id | mobile |
|---|---|
| 101 | 13910000001 |
| 101 | 13910000002 |
| 102 | 13910000003 |
| 103 | 13910000004 |
第三范式(3NF)
第三范式要同時(shí)滿足下面兩個(gè)條件
- 滿足第二范式
- 沒有傳遞依賴
例如捆姜，員工表的province、city嫂粟、district依賴于zip娇未，而zip依賴于id，換句話說星虹，province零抬、city、district傳遞依賴于id宽涌，違反了 3NF 規(guī)則平夜。為了滿足第三范式的條件，可以將這個(gè)表拆分成employee和zip兩個(gè)表卸亮，如下
employee
| id | name | zip |
|---|---|---|
| 101 | 張三 | 100001 |
| 102 | 李四 | 200001 |
| 103 | 王五 | 510001 |
地區(qū)表area
| zip | province | city | district |
|---|---|---|---|
| 100001 | 北京 | 北京 | 海淀區(qū) |
| 200001 | 上海 | 上海 | 靜安區(qū) |
| 51000 | 廣東省 | 廣州 | 白云區(qū) |
在關(guān)系數(shù)據(jù)庫(kù)模型設(shè)計(jì)中忽妒，一般需要滿足第三范式的要求。如果一個(gè)表具有良好的主外鍵設(shè)計(jì)兼贸，就應(yīng)該是滿足3NF的表段直。規(guī)范化帶來的好處是通過減少數(shù)據(jù)冗余提高更新數(shù)據(jù)的效率，同時(shí)保證數(shù)據(jù)完整性溶诞。然而鸯檬，我們?cè)趯?shí)際應(yīng)用中也要防止過度規(guī)范化的問題。規(guī)范化程度越高螺垢，劃分的表就越多喧务，在查詢數(shù)據(jù)時(shí)越有可能使用表連接操作。而如果連接的表過多枉圃，會(huì)影響查詢性能功茴。關(guān)鍵的問題是要依據(jù)業(yè)務(wù)需求，仔細(xì)權(quán)衡數(shù)據(jù)查詢和數(shù)據(jù)更新關(guān)系孽亲，指定最合適的規(guī)范化程度坎穿。不要為了遵循嚴(yán)格的規(guī)范化規(guī)則而修改業(yè)務(wù)需求
數(shù)據(jù)庫(kù)一對(duì)一、一對(duì)多墨林、多對(duì)多設(shè)計(jì)
數(shù)據(jù)庫(kù)實(shí)體間有三種對(duì)應(yīng)關(guān)系：一對(duì)一赁酝、一對(duì)多、多對(duì)多
一對(duì)一關(guān)系示例：
一個(gè)學(xué)生對(duì)應(yīng)一個(gè)學(xué)生檔案材料 每個(gè)人都有唯一的身份證號(hào)
一對(duì)多關(guān)系示例：
一個(gè)學(xué)生只屬于一個(gè)班旭等，但這個(gè)班有多名學(xué)生
多對(duì)多關(guān)系示例：
一個(gè)學(xué)生可以選擇多門課，一門課也可以有多名學(xué)生
一個(gè)人可以有多個(gè)角色衡载，一個(gè)角色可以有多個(gè)人
關(guān)系
-
創(chuàng)建成績(jī)表scores搔耕，結(jié)構(gòu)如下
- id
- 學(xué)生
- 科目
- 成績(jī)
思考：學(xué)生列應(yīng)該存什么信息呢？
答：學(xué)生列的數(shù)據(jù)不是在這里新建的，而應(yīng)該從學(xué)生表引用過來弃榨，關(guān)系也是一條數(shù)據(jù)菩收；根據(jù)范式要求應(yīng)該存儲(chǔ)學(xué)生的編號(hào)，而不是學(xué)生的姓名等其它信息
同理鲸睛，科目表也是關(guān)系列娜饵，引用科目表中的數(shù)據(jù)
創(chuàng)建表的語(yǔ)句如下
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2)
);
外鍵
思考：怎么保證關(guān)系列數(shù)據(jù)的有效性呢？任何整數(shù)都可以嗎官辈？
答：必須是學(xué)生表中id列存在的數(shù)據(jù)箱舞，可以通過外鍵約束進(jìn)行數(shù)據(jù)的有效性驗(yàn)證
為stuid添加外鍵約束
alter table scores add constraint stu_sco foreign key(stuid) references students(id);
此時(shí)插入或者修改數(shù)據(jù)時(shí)，如果stuid的值在students表中不存在則會(huì)報(bào)錯(cuò)
在創(chuàng)建表時(shí)可以直接創(chuàng)建約束
create table scores(
id int primary key auto_increment,
stuid int,
subid int,
score decimal(5,2),
foreign key(stuid) references students(id),
foreign key(subid) references subjects(id)
);
外鍵的級(jí)聯(lián)操作
- 在刪除students表的數(shù)據(jù)時(shí)拳亿，如果這個(gè)id值在scores中已經(jīng)存在晴股，則會(huì)拋異常
- 推薦使用邏輯刪除，還可以解決這個(gè)問題
- 可以創(chuàng)建表時(shí)指定級(jí)聯(lián)操作肺魁，也可以在創(chuàng)建表后再修改外鍵的級(jí)聯(lián)操作
- 語(yǔ)法
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
注：外鍵的添加會(huì)比較消耗性能
級(jí)聯(lián)操作的類型包括：
- restrict（限制）：默認(rèn)值电湘，拋異常（常用）
- cascade（級(jí)聯(lián)）：如果主表的記錄刪掉，則從表中相關(guān)聯(lián)的記錄都將被刪除（一般不使用）
- set null：將外鍵設(shè)置為空（一般不使用）
- no action：什么都不做（一般不使用）
二鹅经、連表查詢
-
連接查詢分類如下：
- 表A inner join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中
- 表A left join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中寂呛，外加表A中獨(dú)有的數(shù)據(jù)，未對(duì)應(yīng)的數(shù)據(jù)使用null填充
- 表A right join 表B：表A與表B匹配的行會(huì)出現(xiàn)在結(jié)果中瘾晃，外加表B中獨(dú)有的數(shù)據(jù)贷痪，未對(duì)應(yīng)的數(shù)據(jù)使用null填充
在查詢或條件中推薦使用“表名.列名”的語(yǔ)法
如果多個(gè)表中列名不重復(fù)可以省略“表名.”部分
如果表的名稱太長(zhǎng)，可以在表名后面使用' as 簡(jiǎn)寫名'或' 簡(jiǎn)寫名'酗捌，為表起個(gè)臨時(shí)的簡(jiǎn)寫名稱
練習(xí)
- 查詢學(xué)生的姓名呢诬、平均分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.sname;
- 查詢男生的姓名、總分
select students.sname,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.sname;
- 查詢科目的名稱胖缤、平均分
select subjects.stitle,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.stitle;
- 查詢未刪除科目的名稱尚镰、最高分、平均分
select subjects.stitle,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isdelete=0
group by subjects.stitle;
三哪廓、子查詢
- 查詢支持嵌套使用
- 查詢各學(xué)生的語(yǔ)文狗唉、數(shù)學(xué)、英語(yǔ)的成績(jī)
什么是子查詢
當(dāng)一個(gè)查詢是另一個(gè)查詢的條件時(shí),這個(gè)查詢稱之為子查詢(內(nèi)層查詢)
什么時(shí)候用涡真？
當(dāng)查詢需求比較復(fù)雜分俯，一次性查詢無法得到結(jié)果，需要多次查詢時(shí)哆料，
例如：給出一個(gè)部門名稱缸剪，需要獲得該部門所有的員工信息
分析：
1.需要先確定部門的id
2.然后才能通過id確定員工
解決問題的方式是把一個(gè)復(fù)雜的問題拆分為若干個(gè)簡(jiǎn)單的問題
2. 如何使用？
首先明確子查詢就是一個(gè)普通的查詢,當(dāng)一個(gè)查詢需要作為子查詢使用時(shí),用括號(hào)包裹即可
3. 需要注意
in中的子查詢只能包含一個(gè)列
例如：查詢財(cái)務(wù)部有哪些人
正確的寫法：select name from emp where dept_id in (select id from dept where name = "財(cái)務(wù)");
錯(cuò)誤的寫法：select name from emp where dept_id in (select * from dept where name = "財(cái)務(wù)");
關(guān)鍵字：exists
exists后跟子查詢东亦，子查詢有結(jié)果是為True杏节，沒有結(jié)果時(shí)為False。為True時(shí)外層執(zhí)行，為False外層不執(zhí)行
如何使用奋渔？
select from emp where exists (select from emp where salary > 1000);
前面 exists 后面 如果 后面 查詢有結(jié)果時(shí)镊逝，前面 才會(huì)執(zhí)行
今天特別累！
日期：2021/11/25
學(xué)習(xí)參考視頻：*https://www.bilibili.com/video/BV1i7411G7kW?p=35&t=765.0
學(xué)習(xí)參考文檔參考部分相關(guān)視頻文案和課件嫉鲸，僅供個(gè)人學(xué)習(xí)和記錄
