關係

• 創建成績表scores，結構如下
  • id
  • 學生
  • 科目
  • 成績
• 思考：學生列應該存什麼信息呢？
• 答：學生列的數據不是在這裡新建的，而應該從學生表引用過來，關係也是一條數據；根據範式要求應該存儲學生的編號，而不是學生的姓名等其它信息
• 同理，科目表也是關係列，引用科目表中的數據

• 創建表的語句如下

  create table scores(
  id int primary key auto_increment,
  stuid int,
  subid int,
  score decimal(5,2)
  );

  外鍵

  • 思考：怎麼保證關係列數據的有效性呢？任何整數都可以嗎？
  • 答：必須是學生表中id列存在的數據，可以通過外鍵約束進行數據的有效性驗證
  • 為stuid添加外鍵約束

  alter table scores add constraint stu_sco foreign key(stuid) references students(id);
  

• 此時插入或者修改數據時，如果stuid的值在students表中不存在則會報錯
• 在創建表時可以直接創建約束

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)
);

外鍵的級聯操作

• 在刪除students表的數據時，如果這個id值在scores中已經存在，則會拋異常
• 推薦使用邏輯刪除，還可以解決這個問題
• 可以創建表時指定級聯操作，也可以在創建表後再修改外鍵的級聯操作
• 語法

alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;

級聯操作的類型包括：

• restrict（限制）：預設值，拋異常
• cascade（級聯）：如果主表的記錄刪掉，則從表中相關聯的記錄都將被刪除
• set null：將外鍵設置為空
• no action：什麼都不做

先看個問題

• 問：查詢每個學生每個科目的分數
• 分析：學生姓名來源於students表，科目名稱來源於subjects，分數來源於scores表，怎麼將3個表放到一起查詢，並將結果顯示在同一個結果集中呢？
• 答：當查詢結果來源於多張表時，需要使用連接查詢
• 關鍵：找到表間的關係，當前的關係是
  • students表的id---scores表的stuid
  • subjects表的id---scores表的subid
• 則上面問題的答案是：

  select students.sname,subjects.stitle,scores.score
  from scores
  inner join students on scores.stuid=students.id
  inner join subjects on scores.subid=subjects.id;

結論：當需要對有關係的多張表進行查詢時，需要使用連接join

連接查詢

• 連接查詢分類如下：
  • 表A inner join 表B：表A與表B匹配的行會出現在結果中
  • 表A left join 表B：表A與表B匹配的行會出現在結果中，外加表A中獨有的數據，未對應的數據使用null填充
  • 表A right join 表B：表A與表B匹配的行會出現在結果中，外加表B中獨有的數據，未對應的數據使用null填充
• 在查詢或條件中推薦使用“表名.列名”的語法
• 如果多個表中列名不重覆可以省略“表名.”部分
• 如果表的名稱太長，可以在表名後面使用' as 簡寫名'或' 簡寫名'，為表起個臨時的簡寫名稱

練習

• 查詢學生的姓名、平均分

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;