# SQL基本指令

資料表關聯圖

SQL詳細教學，[參考網站](https://www.runoob.com/sql/sql-select.html)
線上SQL練習網站，[參考網站](https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all)

---
## 0、基本介紹

SELECT 欄位清單 (可以用*代表所有欄位)
FROM 資料表
WHERE 條件

---
## 1、SELECT – 查詢資料
取得資料表中的欄位與紀錄。

-- 查詢所有客戶
SELECT * FROM Customers;

-- 查詢客戶與城市
SELECT CustomerID, CustomerName, City 
FROM Customers;

---

## 2、WHERE – 條件過濾

篩選符合條件的資料。

-- 查詢位於 USA 的客戶
SELECT CustomerID, CustomerName, Country
FROM Customers
WHERE Country = 'USA';

-- 查詢價格大於 50 的產品
SELECT ProductName, Price
FROM Products
WHERE Price > 50;

---

## 3、ORDER BY – 排序

依指定欄位升冪或降冪排列。

-- 依價格由低到高排序
SELECT ProductName, Price
FROM Products
ORDER BY Price ASC;

-- 依價格由高到低排序
SELECT ProductName, Price
FROM Products
ORDER BY Price DESC;

---

## 4、DISTINCT – 移除重複值

找出唯一值。

-- 找出不同的供應商國家
SELECT DISTINCT Country
FROM Suppliers;

---

## 5、LIMIT / TOP – 限制筆數

不同資料庫語法略有差異。

-- MySQL / SQLite: 只取前 5 筆產品
SELECT ProductName, Price
FROM Products
LIMIT 5;

-- SQL Server: 取前 5 筆產品
SELECT TOP 5 ProductName, Price
FROM Products;

---

## 6、聚合函數 (Aggregate Functions)

常見的統計計算：COUNT, SUM, AVG, MAX, MIN。

-- 計算產品數量
SELECT COUNT(*) AS ProductCount
FROM Products;

-- 平均產品價格
SELECT AVG(Price) AS AvgPrice
FROM Products;

-- 最高與最低價格
SELECT MAX(Price) AS MaxPrice,
       MIN(Price) AS MinPrice
FROM Products;

---

## 7、GROUP BY – 分組

與聚合函數搭配使用。

-- 各國客戶數量
SELECT Country, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country;

-- 各分類的平均產品價格
SELECT CategoryID, AVG(Price) AS AvgPrice
FROM Products
GROUP BY CategoryID;

---

## 8、HAVING – 群組過濾

篩選 GROUP BY 的結果。

-- 只顯示客戶數量大於 5 的國家
SELECT Country, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country
HAVING CustomerCount > 5;

---

## 9、JOIN – 資料表連接

常見 JOIN 種類：INNER JOIN, LEFT JOIN, RIGHT JOIN。

-- 內連接：訂單與客戶
SELECT Orders.OrderID, Customers.CompanyName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
    ON Orders.CustomerID = Customers.CustomerID;

-- 左連接：產品與分類
SELECT Products.ProductName, Categories.CategoryName
FROM Products
LEFT JOIN Categories
    ON Products.CategoryID = Categories.CategoryID;

各種JOIN的差別解釋，[參考網站](https://docfunc.com/posts/56/mysql-%E4%B8%AD%E5%90%84%E7%A8%AE-join-%E7%9A%84%E5%B7%AE%E5%88%A5-post)

---
另一種比較舊的JOIN方式，透過WHERE去做連接

SELECT o.OrderID, od.OrderDetailID, p.ProductID, p.ProductName
FROM Orders AS o, OrderDetails AS od, Products AS p
WHERE o.OrderID = od.OrderID AND od.ProductID = p.ProductID

用INNER JOIN就會變

SELECT o.OrderID, od.OrderDetailID, p.ProductID, p.ProductName
FROM Orders AS o
INNER JOIN OrderDetails AS od
    ON o.OrderID = od.OrderID
INNER JOIN Products AS p
    ON od.ProductID = p.ProductID;

---

## 10、子查詢 (Subquery)

查詢結果嵌套在另一查詢中。

-- 找出價格大於平均價的產品
SELECT ProductName, Price
FROM Products
WHERE Price > (
    SELECT AVG(Price) FROM Products
);

---

## 小練習

1. 查詢來自 **Germany** 的客戶名稱與城市。
<details>
<summary>參考答案</summary>

SELECT CustomerName, City 
FROM Customers 
WHERE Country = 'Germany';

</details>

2. 列出 **前 10 筆最貴的產品**。
<details>
<summary>參考答案</summary>

SELECT * FROM Products 
ORDER BY Price DESC 
LIMIT 10;

</details>

3. 統計 **每位員工處理的訂單數量**。

<details>

SELECT EmployeeID, COUNT(*) AS `OrderCount`
FROM Orders
GROUP BY EmployeeID;

</details>

4. 找出 **訂單數量最多的客戶**。

<details>

SELECT CustomerID, COUNT(*) AS `OrderCount` 
FROM Orders 
GROUP BY CustomerID
ORDER BY `OrderCount` DESC
LIMIT 1;

</details>