SELECT

定義:

列一個列的SQL指令。

---

指令語法:

1.普通列取

SELECT [DISTINCT] field1_name [AS field1_new_name],field2_name... 
FROM table_name [table_new_name]
[WHERE criteria]
[GROUP BY fieldA_name,fieldB_name...] [HAVING criteria]
[ORDER BY fieldG_name DESC/[ASC], fieldH_name DESC/[ASC]...];

• 可以使用函數
• WHERE條件句(criteria)語法可參考: WHERE - SQL語句
• 執行順序:
  FROM>WHERE>GROUP BY
  >彙總函數(Aggregation Functions)>HAVING
  >SELECT>DISTINCT>ORDER BY

2.相等連接(equi-join)列取

SELECT [DISTINCT] table1_name.field1_name [AS field1_new_name],table2_name.field2_name...
FROM table1_name [table1_new_name],table2_name [table2_new_name],table3_name [table3_new_name]...
[WHERE table1_name.fieldA_name = table2_name.fieldB_name AND table1_name.fieldD_name = table3_name.fieldC_name AND criteria]
[GROUP BY fieldA_name,fieldB_name...]
[HAVING criteria] [ORDER BY fieldG_name DESC/[ASC], fieldH_name DESC/[ASC]...];

• 連接的資料表可能有重複的欄位名稱，所以最好在考試中寫出欄位全名
  (例如 abc.field_name或table_name.field_name)
• 可以使用函數
• WHERE條件句(criteria)語法可參考: WHERE - SQL語句
• Executing order:
  FROM>WHERE>GROUP BY
  >彙總函數(Aggregation Functions)HAVING
  >SELECT>DISTINCT>ORDER BY

3.複雜列取

SELECT [DISTINCT] table1_name.field1_name [AS field1_new_name],table2_name.field2_name...
FROM table1_name [table1_new_name] LEFT JOIN/RIGHT JOIN/FULL JOIN/INNER JOIN table2_name [table2_new_name] 
ON table1_name.fieldA_name = table2_name.fieldB_name 
LEFT JOIN/RIGHT JOIN/FULL JOIN/INNER JOIN table3_name [table3_new_name] ON table1_name.fieldD_name = table3_name.fieldC_name ... 
[WHERE criteria] 
[GROUP BY table2_name.fieldA_name,table1_name.fieldB_name...]
[HAVING criteria] [ORDER BY table1_name.fieldG_name DESC/[ASC], table2_name.fieldH_name DESC/[ASC]...]
[UNION/UNION ALL/INTERSECT/(EXCEPT/MINUS) SELECT...]
[UNION/UNION ALL/INTERSECT/(EXCEPT/MINUS) SELECT...]...;

• EXCEPT和MINUS為等效指令，分別在於MySQL只支援EXCEPT，教科書依據MySQL，而HKDSE ICT SQL指令參考表只列出MINUS
• 連接的資料表可能有重複的欄位名稱，所以最好在考試中寫出欄位全名
  (例如 abc.field_name或table_name.field_name)
• 可以使用函數
• WHERE條件句(criteria)語法可參考: WHERE - SQL語句
• Executing order:
  FROM>ON>OUTER JOIN>WHERE>GROUP BY
  >彙總函數(Aggregation Functions)>HAVING
  >SELECT>DISTINCT>ORDER BY

---

其他參考:

SQL order of execution:Link
MySQL:Link

---

「長。」某位中六學生說道