SQL 查詢語句大全：WHERE 條件、子查詢、多表連接實戰範例

WHERE 條件與統計函數

-- 總行數
SELECT COUNT(1) FROM student;

-- 模糊查詢：姓張
SELECT COUNT(*) FROM student WHERE realName LIKE '張%';

-- 模糊查詢：張姓兩字
SELECT COUNT(*) FROM student WHERE realName LIKE '張_';

-- 籍貫包含杭州
SELECT COUNT(*) FROM student WHERE home LIKE '%杭州%';

-- 計算年齡
SELECT realName, YEAR(NOW()) - YEAR(birthday) AS age FROM student;

-- 90 後學生
SELECT realName FROM student WHERE YEAR(birthday) >= 1990;

-- 1987-1990 年出生
SELECT realName FROM student WHERE YEAR(birthday) BETWEEN 1987 AND 1990;

-- 性別分組統計
SELECT sex, COUNT(*) FROM student GROUP BY sex;

-- IN 子句：B 型或 O 型血
SELECT realName, blood FROM student WHERE blood IN ('B','O');

子查詢

子查詢（嵌套查詢）分單行與多行：

-- 單行子查詢：找出比 ALLEN 薪資高的員工
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'ALLEN');

-- 多行子查詢：SOME/ANY/ALL
SELECT * FROM emp WHERE sal > SOME (SELECT sal FROM emp WHERE deptno = 20);

• 子查詢優先執行，需用括號包裹。
• 習慣置於條件右側。

多表連接

內連接

-- 查詢學生各科成績
SELECT a.realname, c.courseName, b.score
FROM stu_student a
INNER JOIN stu_score b ON a.sid = b.sid
INNER JOIN stu_course c ON b.cid = c.cid;

-- 等價舊語法
SELECT a.realname, c.courseName, b.score
FROM student a, score b, course c
WHERE a.sid = b.sid AND c.cid = b.cid;

外連接

• LEFT JOIN：返回左表所有行 + 右表匹配行，無匹配則 NULL。
• RIGHT JOIN：返回右表所有行 + 左表匹配行。

SELECT a.realname, b.score
FROM stu_student a
LEFT OUTER JOIN stu_score b ON a.sid = b.sid;