MySQL（五）

標籤：驗證   asc   mys   存在   刪掉   ade   分數   scores   sel   

關係

• 建立成績表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.scorefrom scoresinner join students on scores.stuid=students.idinner 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 scoresinner join students on scores.stuid=students.idgroup by students.sname;

• 查詢男生的姓名、總分

select students.sname,avg(scores.score)from scoresinner join students on scores.stuid=students.idwhere students.gender=1group by students.sname;

• 查詢科目的名稱、平均分

select subjects.stitle,avg(scores.score)from scoresinner join subjects on scores.subid=subjects.idgroup by subjects.stitle;

• 查詢未刪除科目的名稱、最高分、平均分

select subjects.stitle,avg(scores.score),max(scores.score)from scoresinner join subjects on scores.subid=subjects.idwhere subjects.isdelete=0group by subjects.stitle;

 

MySQL（五）