# H03_SQL Language

## Basic Operating of Database

:::warning
選擇資料 SELECT column FORM table;
:::

:::warning
鍵入資料 INSERT INTO table (column1, column2) VALUES (value1, value2);
:::

:::warning
刪除資料 DELETE FROM table WHERE your_condition (ex : id = 02);
:::

:::warning
更新資料 UPDATE FROM table (column1, column2) VALUES (value1, value2);
:::

---

## This is a sample1 for tutoring (Backery's menu)

| item | price | date |
| ----------------- | ----- | ---------- |
| Taro Bread | 25 | 2023-03-28 |
| Danish pineapple | 45 | 2023-03-30 |
| Green Onion Bread | 35 | 2023-04-02 |
| Milk Bread | 20 | 2023-03-08 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |

## This is a sample2 for tutoring (Backery's bread March sales volume)
| item | sales |
| ----------------- | ----- |
| Taro Bread | 12,000 |
| Danish pineapple | 7,800 |
| Green Onion Bread | 5,688 |
| Milk Bread | 25,330 |
| Ice Coffee | 77,112 |
| Black Tea | 66,881 |

---
## SELECT 選擇

>查詢資料表內所有資料
`SELECT * FROM menu;`

| item | price | date |
| ----------------- | ----- | ---------- |
| Taro Bread | 25 | 2023-03-28 |
| Danish pineapple | 45 | 2023-03-30 |
| Green Onion Bread | 35 | 2023-04-02 |
| Milk Bread | 20 | 2023-03-08 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |

>查詢有條件式單筆資料(有指定欄位、純數字不用單引號)
`SELECT std_name FROM menu WHERE price = 35;`

| item | price | date |
| ----------------- | ----- | ---------- |
| Green Onion Bread | 35 | 2023-04-02 |
| Black Tea | 35 | 2023-03-27 |

`SELECT std_name FROM menu WHERE item = 'Black Tea';`

| item | price | date |
| ----------------- | ----- | ---------- |
| Black Tea | 35 | 2023-03-27 |

>查尋多重條件式
`SELECT * FORM menu WHERE price = 75 AND price = 25;`

| item | price | date |
| ----------------- | ----- | ---------- |
| Taro Bread | 25 | 2023-03-28 |
| Ice Coffee | 75 | 2023-03-27 |

>查詢資料表內唯一的值
`SELECT DISTINCT date FORM menu;`

| date |
| ---------- |
| 2023-03-28 |
| 2023-03-30 |
| 2023-04-02 |
| 2023-03-08 |
| 2023-03-27 |

>查詢資料表涵蓋多重值
`SELECT * FORM menu WHERE date in ('2023-03-28', '2023-04-02');`

| item | price | date |
| ----------------- | ----- | ---------- |
| Taro Bread | 25 | 2023-03-28 |
| Green Onion Bread | 35 | 2023-04-02 |

>查詢有範圍之資料
`SELECT * FORM menu WHERE date BETWEEN '2023-03-01' AND '2023-03-31';`

| item | price | date |
| ----------------- | ----- | ---------- |
| Taro Bread | 25 | 2023-03-28 |
| Danish pineapple | 45 | 2023-03-30 |
| Milk Bread | 20 | 2023-03-08 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |

>萬用字元使用
`SELECT * FORM menu WHERE item LIKE 'B%'`
`A_Z` `所有以 'A' 起頭，另一個任何值的字原，且以 'Z' 為結尾的字串`
`ABC%` `所有以 'ABC' 起頭的字串`
`%XYZ` `所有以 'XYZ' 結尾的字串`
`%AN%` `所有含有 'AN'這個模式的字串`
`_AN%` `所有第二個字母為 'A' 和第三個字母為 'N' 的字串`

>排序指令(ASC從頭排序、DESC從尾排序)
`SELECT * FORM menu ORDER BY date ASC;`

| item | price | date |
| ----------------- | ----- | ---------- |
| Milk Bread | 20 | 2023-03-08 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |
| Taro Bread | 25 | 2023-03-28 |
| Danish pineapple | 45 | 2023-03-30 |
| Green Onion Bread | 35 | 2023-04-02 |

`SELECT * FORM menu ORDER BY item DESC;`

| item | price | date |
| ----------------- | ----- | ---------- |
| Green Onion Bread | 35 | 2023-04-02 |
| Danish pineapple | 45 | 2023-03-30 |
| Taro Bread | 25 | 2023-03-28 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |
| Milk Bread | 20 | 2023-03-08 |

>函數值計算(GROUP BY, SUM() )
`SELECT item, SUM(price) FORM menu GROUP BY item HAVING SUM(price) > 30;`

| item | SUM(price) |
| ----------------- | ----- |
| Green Onion Bread | 35 |
| Danish pineapple | 45 |
| Ice Coffee | 75 |
| Black Tea | 35 |

---
## JOIN 串聯兩個或以上的表格
>INNER JOIN
`SELECT * FROM menu INNER JOIN volume ON menu.item = volume.item;`

>LEFT OUTER JOIN
`SELECT * FROM menu LEFT OUTER JOIN volume ON menu.item = volume.item;`

>RIGHT OUTER JOIN
`SELECT * FROM volume RIGHT OUTER JOIN menu ON menu.item = volume.item;`

>FULL OUTER JOI
`SELECT * FROM menu FULL OUTER JOIN volume ON menu.item = volume.item;`

---
## INSERT 鍵入資料
>INNER INTO
`INSERT INTO menu (item, price, date) VALUES ('Hot dog', 40, '2023-04-01');`

| item | price | date |
| ----------------- | ----- | ---------- |
| Taro Bread | 25 | 2023-03-28 |
| Danish pineapple | 45 | 2023-03-30 |
| Green Onion Bread | 35 | 2023-04-02 |
| Milk Bread | 20 | 2023-03-08 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |
| Hot dog | 40 | 2023-04-01 |

---
## DELETE 刪除資料
>DELETE FROM
`DELETE FROM menu WHERE item = 'Hot dog' AND item = 'Taro Bread';`

| item | price | date |
| ----------------- | ----- | ---------- |
| Danish pineapple | 45 | 2023-03-30 |
| Green Onion Bread | 35 | 2023-04-02 |
| Milk Bread | 20 | 2023-03-08 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |

---

## UPDATE 更新資料
>DELETE FROM
`UPDATE menu SET price = 55 WHERE item = 'Danish pineapple';`

| item | price | date |
| ----------------- | ----- | ---------- |
| Danish pineapple | 55 | 2023-03-30 |
| Green Onion Bread | 35 | 2023-04-02 |
| Milk Bread | 20 | 2023-03-08 |
| Ice Coffee | 75 | 2023-03-27 |
| Black Tea | 35 | 2023-03-27 |