CREATE TABLE score (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    stu_id INT NOT NULL,
    subject VARCHAR(20) NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (stu_id) REFERENCES student(stu_id)
);
INSERT INTO score (stu_id, subject, score) VALUES
(1, '語(yǔ)文', 85),
(1, '數(shù)學(xué)', 92),
(2, '語(yǔ)文', 78),
(3, '數(shù)學(xué)', 88); -- 趙六（stu_id=4）無(wú)成績(jī)

CREATE TABLE score (
    score_id INT PRIMARY KEY AUTO_INCREMENT,
    stu_id INT NOT NULL,
    subject VARCHAR(20) NOT NULL,
    score INT NOT NULL,
    FOREIGN KEY (stu_id) REFERENCES student(stu_id)
);
INSERT INTO score (stu_id, subject, score) VALUES
(1, '語(yǔ)文', 85),
(1, '數(shù)學(xué)', 92),
(2, '語(yǔ)文', 78),
(3, '數(shù)學(xué)', 88); -- 趙六（stu_id=4）無(wú)成績(jī)

-- 顯式內(nèi)連接：查詢(xún)有成績(jī)的學(xué)生信息+成績(jī)
SELECT 
    s.stu_id, s.stu_name, s.class, 
    sc.subject, sc.score
FROM student s
INNER JOIN score sc 
    ON s.stu_id = sc.stu_id;

-- 左連接：以學(xué)生表為基準(zhǔn)，查詢(xún)所有學(xué)生（含無(wú)成績(jī)的趙六）
SELECT 
    s.stu_id, s.stu_name, s.class, 
    sc.subject, sc.score
FROM student s
LEFT JOIN score sc 
    ON s.stu_id = sc.stu_id;

-- 右連接：以成績(jī)表為基準(zhǔn)，查詢(xún)所有成績(jī)（無(wú)學(xué)生的成績(jī)不會(huì)出現(xiàn)，本例無(wú)此場(chǎng)景）
SELECT 
    s.stu_id, s.stu_name, s.class, 
    sc.subject, sc.score
FROM student s
RIGHT JOIN score sc 
    ON s.stu_id = sc.stu_id;

-- 左連接+WHERE：查詢(xún)一班所有學(xué)生（含無(wú)成績(jī)）
SELECT 
    s.stu_id, s.stu_name, s.class, 
    sc.subject, sc.score
FROM student s
LEFT JOIN score sc 
    ON s.stu_id = sc.stu_id
WHERE s.class = '一班';

-- 復(fù)合條件：左連接+多條件，查詢(xún)一班成績(jī)>80的學(xué)生，無(wú)成績(jī)也顯示
SELECT 
    s.stu_id, s.stu_name, s.class, 
    sc.subject, sc.score
FROM student s
LEFT JOIN score sc 
    ON s.stu_id = sc.stu_id 
    AND sc.score > 80  -- 連接時(shí)過(guò)濾成績(jī)>80
WHERE s.class = '一班'; -- 過(guò)濾班級(jí)

-- 錯(cuò)誤！無(wú)關(guān)聯(lián)條件，笛卡爾積
SELECT * FROM student, score;

-- 錯(cuò)誤！WHERE sc.score>80 會(huì)過(guò)濾掉趙六的NULL行，左連接變內(nèi)連接
SELECT * FROM student s LEFT JOIN score sc ON s.stu_id=sc.stu_id WHERE sc.score>80;

-- 錯(cuò)誤！stu_id 不明確屬于哪個(gè)表
SELECT stu_id FROM student s JOIN score sc ON s.stu_id=sc.stu_id;
-- 正確：加表別名
SELECT s.stu_id FROM student s JOIN score sc ON s.stu_id=sc.stu_id;

-- 需求：查詢(xún)所有學(xué)生的總分，無(wú)成績(jī)的學(xué)生總分顯示0，按班級(jí)分組
SELECT 
    s.class,
    s.stu_name,
    IFNULL(SUM(sc.score), 0) AS total_score  -- IFNULL 把NULL轉(zhuǎn)為0
FROM student s
LEFT JOIN score sc 
    ON s.stu_id = sc.stu_id
GROUP BY s.stu_id, s.stu_name, s.class
ORDER BY total_score DESC;