通過教師-學生-課程-分數經典四表模型,演示從基礎查詢到多表連接、子查詢、聚合統計的常用 SQL 模式,適合開發調試快速參考。
CREATE DATABASE studb;
USE studb;
CREATE TABLE teacher (
tid INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20),
tage INT
);
CREATE TABLE student (
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
sage INT,
tid INT REFERENCES teacher(tid)
);
CREATE TABLE course (
cid INT PRIMARY KEY,
cname VARCHAR(20),
tid INT REFERENCES teacher(tid)
);
CREATE TABLE sc (
scid INT PRIMARY KEY,
sid INT REFERENCES student(sid),
cid INT REFERENCES course(cid),
score INT
);
SELECT c.cname FROM course c, student s, sc WHERE s.sname = '小張' AND s.sid = sc.sid AND c.cid = sc.cid;
SELECT s.sname FROM student s, course c, sc WHERE c.cname = 'android' AND sc.score >= 60 AND s.sid = sc.sid AND c.cid = sc.cid;
DELETE FROM sc WHERE sid = (SELECT sid FROM student WHERE sname = '小張');
注意:子查詢關聯字段通常為主外鍵。
UPDATE sc
SET score = score + 5
WHERE cid = (
SELECT cid FROM course
WHERE cname = (
SELECT cname FROM course
WHERE tid = (SELECT tid FROM teacher WHERE tname = '李老師')
)
);
實際建議使用 JOIN 改寫提升性能與可讀性。
tid REFERENCES teacher(tid)。IN/EXISTS 而非 =。WHERE col = '2000' 應為 WHERE col = 2000。